使用data.table中的滚动连接进行多个匹配

Question

我对滚动连接有一个备注/问题
设X、Y为：

set.seed(123);
X <- data.table(x=c(1,1,1,2,2),y=c(T,T,F,F,F),t=as.POSIXct("08:00:00.000",format="%H:%M:%OS")+sample(0:999,5,TRUE)/1e3)
Y <- copy(X)
set.seed(123)
Y[,`:=`(IDX=.I,t=t+sample(c(-5:5)/1e3,5,T))]
Y <- rbindlist(list(Y, X[5,][,IDX:=6][,t:=t+0.001], X[5,][,IDX:=7][,t:=t+0.002]))
setkey(X,x,y,t)
setkey(Y,x,y,t)

这里的X和Y是通过x,y,t进行排序的。

R) X
   x     y                       t
1: 1 FALSE 2013-06-20 08:00:00.407
2: 1  TRUE 2013-06-20 08:00:00.286
3: 1  TRUE 2013-06-20 08:00:00.788
4: 2 FALSE 2013-06-20 08:00:00.882
5: 2 FALSE 2013-06-20 08:00:00.940
R) Y
   x     y                       t IDX
1: 1 FALSE 2013-06-20 08:00:00.407   3
2: 1  TRUE 2013-06-20 08:00:00.284   1
3: 1  TRUE 2013-06-20 08:00:00.791   2
4: 2 FALSE 2013-06-20 08:00:00.886   4
5: 2 FALSE 2013-06-20 08:00:00.940   6
6: 2 FALSE 2013-06-20 08:00:00.942   7
7: 2 FALSE 2013-06-20 08:00:00.945   5

执行Y[X, roll=-0.005]会得到以下结果：

R) Y[X, roll=-0.005]
       x     y                       t IDX
    1: 1 FALSE 2013-06-20 08:00:00.407  NA => due to precision the roll is no match
    2: 1  TRUE 2013-06-20 08:00:00.286  NA => ok 
    3: 1  TRUE 2013-06-20 08:00:00.788   2 => ok (x,y) matched and 788-791=-3
    4: 2 FALSE 2013-06-20 08:00:00.882   4 => same
    5: 2 FALSE 2013-06-20 08:00:00.940   6 => NOT AN EXACT MATCH (precision)

所以我本来期望最后一行会有更多的匹配结果，因为“mult”的默认行为是“all”，而X的最后一行被Y的第5,6,7行所匹配。

- statquant

---

我对这个感兴趣是因为如果我们能够获得那些额外的行，我们就可以处理窗口连接并计算滚动中位数、平均值或任何其他操作。 - statquant

这对我来说听起来像是一个功能请求（不过我会使用一个简单得多的例子，比如 d = data.table(a = c(1.0,2.0,2.0), by = c(1:3), key = 'a'); d[J(2.1), ...]，因为这个例子非常难理解）。 - eddi

Answer 1

第一行

对于X的第一行，只有与x和y匹配的Y的第一行才能匹配，因此Y的第一行将匹配当且仅当Y$t[1]在X$t[1]和X$t[1] + 0.005之间，但实际上Y$t[1] < X$t[1] 如下所示:

> X$t[1] - Y$t[1]
Time difference of 0.0009999275 secs

要在第一行得到非 NA 值，需要一个正的 roll= 值，其大小至少等于上述差值。例如：

> Y[X, roll=0.001]
   x     y                   t IDX
1: 1 FALSE 2013-06-20 08:00:00   3
2: 1  TRUE 2013-06-20 08:00:00  NA
3: 1  TRUE 2013-06-20 08:00:00  NA
4: 2 FALSE 2013-06-20 08:00:00  NA
5: 2 FALSE 2013-06-20 08:00:00  NA

请注意，您可以使用rollends=来强制执行，如下所示：

> Y[X, roll = -0.005, rollends = TRUE]
   x     y                   t IDX
1: 1 FALSE 2013-06-20 08:00:00   3
2: 1  TRUE 2013-06-20 08:00:00  NA
3: 1  TRUE 2013-06-20 08:00:00   2
4: 2 FALSE 2013-06-20 08:00:00   4
5: 2 FALSE 2013-06-20 08:00:00   6

最后一行

对于X的最后一行，只有Y的第5行匹配，而不是5、6和7行，因为只有最近的符合条件的行才匹配。 mult=仅适用于多个匹配，并且通常不适用于roll=（请参见底部的示例）：

还要注意，Y的第5、6和7行没有相同的时间。它们具有递增的时间，因此没有可能全部匹配：

> dput(Y[["t"]])
structure(c(1371729600.407, 1371729600.285, 1371729600.791, 1371729600.887, 
1371729600.941, 1371729600.942, 1371729600.945), class = c("POSIXct", 
"POSIXt"))

即使Y的第5、6和7行具有相同的时间，如果这些时间与X的最后一行的时间不同，则只能获得一行输出。

> # times in rows 5, 6 and 7 of Y2 are same
> Y2 <- copy(Y)
> Y2[, t:= t[c(1:4, 5, 5, 5)]]
> setkey(Y2, x, y, t)
> Y2[X, roll = -0.005]
   x     y                   t IDX
1: 1 FALSE 2013-06-20 08:00:00  NA
2: 1  TRUE 2013-06-20 08:00:00  NA
3: 1  TRUE 2013-06-20 08:00:00   2
4: 2 FALSE 2013-06-20 08:00:00   4
5: 2 FALSE 2013-06-20 08:00:00   6

只有当 'Y' 的第5、6和7行具有相同的时间并且 'X' 的最后一行也具有该时间时，才会产生多个进出次数，在这种情况下，可以使用mult=：

> # time in row 5 of X2 same as the times in rows 5, 6 and 7 of Y2
> X2 <- copy(X)
> X2[, t:=c(t[1:4], Y2[["t"]][5])]
> Y2[X2, roll = -0.005]
   x     y                   t IDX
1: 1 FALSE 2013-06-20 08:00:00  NA
2: 1  TRUE 2013-06-20 08:00:00  NA
3: 1  TRUE 2013-06-20 08:00:00   2
4: 2 FALSE 2013-06-20 08:00:00   4
5: 2 FALSE 2013-06-20 08:00:00   6
6: 2 FALSE 2013-06-20 08:00:00   7
7: 2 FALSE 2013-06-20 08:00:00   5
> 
> Y2[X, roll = -0.005, mult = "first"]
   x     y                   t IDX
1: 1 FALSE 2013-06-20 08:00:00  NA
2: 1  TRUE 2013-06-20 08:00:00  NA
3: 1  TRUE 2013-06-20 08:00:00   2
4: 2 FALSE 2013-06-20 08:00:00   4
5: 2 FALSE 2013-06-20 08:00:00   6

从文档上来看，它的工作原理并不是很清楚，我不得不通过试错来发现它的功能。?data.table确实说过："通常情况下，x的键中不应该有重复项"（在我们的例子中，x是Y），因此开发人员可能希望将其未定义为这种情况，并留待未来更改。

你所描述的使用mult=的想法似乎是非常有趣的，但它似乎并不是目前的工作方式。也许在未来会有所改变。

- G. Grothendieck

---

已将我的评论移动到答案中。 - G. Grothendieck

我认为OP认为既然在d = data.table(a = c(1,2,2), b = c(1:3), key = 'a')中，d[J(2)]匹配了两个2，所以当mult设置为“all”时，d[J(2.1), roll = 1]也应该匹配两个2。 - eddi

我已经添加了一些额外的探索性代码，以查看在各种情况下会发生什么。请参见最后一个ADDED。 - G. Grothendieck