使用data.table进行重复键的滚动连接

Question

使用data.table进行重复键的滚动连接

15

我正尝试理解`data.table`中的滚动连接（rolling joins）。下面是可用于复现此操作的数据。

假设有一个记录机场交易的`data.table`，记录了某一特定时间的数据：

> dt
   t_id airport thisTime
1:    1       a      5.1
2:    3       a      5.1
3:    2       a      6.2

（注意，t_ids 1和3具有相同的机场和时间）

以及一个包含出发机场航班信息的查找表：

> dt_lookup
   f_id airport thisTime
1:    1       a        6
2:    2       a        6
3:    1       b        7
4:    1       c        8
5:    2       d        7
6:    1       d        9
7:    2       e        8

> tables()
     NAME      NROW NCOL MB COLS                  KEY             
[1,] dt           3    3  1 t_id,airport,thisTime airport,thisTime
[2,] dt_lookup    7    3  1 f_id,airport,thisTime airport,thisTime

我希望将所有交易与从该机场出发的所有可能的下一班航班匹配，以得到:

   t_id airport thisTime f_id
      1       a        6    1
      1       a        6    2
      3       a        6    1
      3       a        6    2

所以我认为这会起作用：

> dt[dt_lookup, nomatch=0,roll=Inf]
   t_id airport thisTime f_id
1:    3       a        6    1
2:    3       a        6    2

但它未返回交易t_id == 1。

根据文档所述：

通常，x的键中不应有重复项，...

然而，在我的“x键”中存在重复项（即airport和thisTime），我无法看到/理解为什么会导致t_id = 1从输出中移除。

有人能否解释一下为什么未返回t_id = 1，以及当我有重复项时如何使连接正常工作？

数据

library(data.table)
dt <- data.table(t_id = seq(1:3),
                 airport = c("a","a","a"),
                 thisTime = c(5.1,6.2, 5.1), key=c( "airport","thisTime"))

dt_lookup <- data.table(f_id = c(rep(1,4),rep(2,3)),
                        airport = c("a","b","c","d",
                                 "a","d","e"),
                        thisTime = c(6,7,8,9,
                                 6,7,8), key=c("airport","thisTime"))

- tospig

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jaap · Accepted Answer

t_id = 1在输出中没有显示的原因是，滚动连接会获取键组合最后出现的行。根据文档（重点说明）：适用于最后一个连接列，通常是日期，但可以是任何有序变量，不规则且包括间隙。如果roll=TRUE且i的行与所有但最后x个连接列匹配，并且其最后一个i连接列中的值落在间隙中（包括该组中x的最后观察值之后），则向前滚动x中的当前值。使用修改后的二进制搜索进行此操作特别快速。该操作也称为向前延伸最后一次观察（LOCF）。让我们考虑一些较大的数据集：

> DT
   t_id airport thisTime
1:    1       a      5.1
2:    4       a      5.1
3:    3       a      5.1
4:    2       d      6.2
5:    5       d      6.2

> DT_LU
   f_id airport thisTime
1:    1       a        6
2:    2       a        6
3:    2       a        8
4:    1       b        7
5:    1       c        8
6:    2       d        7
7:    1       d        9

当您执行滚动连接（rolling join）时，就像在您的问题中：

DT[DT_LU, nomatch=0, roll=Inf]

您将获得：

   t_id airport thisTime f_id
1:    3       a        6    1
2:    3       a        6    2
3:    3       a        8    2
4:    5       d        7    2
5:    5       d        9    1

如您所见，从键组合 a, 5.1 和 d, 6.2 中，最后一行用于连接的数据表。因为您使用Inf作为滚动值，所有未来的值都将包含在结果数据表中。当您使用：

DT[DT_LU, nomatch=0, roll=1]

你会发现只有未来的第一个值被包含在内：

   t_id airport thisTime f_id
1:    3       a        6    1
2:    3       a        6    2
3:    5       d        7    2

如果你想获得所有airport和thisTime组合的f_id，并且DT$thisTime小于DT_LU$thisTime，你可以通过使用ceiling函数创建一个新变量（或替换现有的thisTime）来实现。以下是一个例子，在这个例子中我创建了一个新的变量thisTime2，然后使用DT_LU进行普通连接：

DT[, thisTime2 := ceiling(thisTime)]
setkey(DT, airport, thisTime2)[DT_LU, nomatch=0]

它提供：

   t_id airport thisTime thisTime2 f_id
1:    1       a      5.1         6    1
2:    4       a      5.1         6    1
3:    3       a      5.1         6    1
4:    1       a      5.1         6    2
5:    4       a      5.1         6    2
6:    3       a      5.1         6    2
7:    2       d      6.2         7    2
8:    5       d      6.2         7    2

应用于您提供的数据：

> dt[, thisTime2 := ceiling(thisTime)]
> setkey(dt, airport, thisTime2)[dt_lookup, nomatch=0]

   t_id airport thisTime thisTime2 f_id
1:    1       a      5.1         6    1
2:    3       a      5.1         6    1
3:    1       a      5.1         6    2
4:    3       a      5.1         6    2

当您想包括所有未来的值而不仅仅是第一个值时，您需要一个稍微不同的方法，这需要使用i.col功能（尚未记录）：

1：首先将键设置为仅airport列：

setkey(DT, airport)
setkey(DT_LU, airport)

2: 使用j中尚未记录的i.col功能，以以下方式获得所需内容：

DT1 <- DT_LU[DT, .(tid = i.t_id,
                   tTime = i.thisTime,
                   fTime = thisTime[i.thisTime < thisTime],
                   fid = f_id[i.thisTime < thisTime]),
             by=.EACHI]

这会给你：

> DT1
    airport tid tTime fTime fid
 1:       a   1   5.1     6   1
 2:       a   1   5.1     6   2
 3:       a   1   5.1     8   2
 4:       a   4   5.1     6   1
 5:       a   4   5.1     6   2
 6:       a   4   5.1     8   2
 7:       a   3   5.1     6   1
 8:       a   3   5.1     6   2
 9:       a   3   5.1     8   2
10:       d   2   6.2     7   2
11:       d   2   6.2     9   1
12:       d   5   6.2     7   2
13:       d   5   6.2     9   1

一些解释：当您加入两个列名相同的数据表时，您可以通过在列名前加上 i. 来引用 i 数据表中的列。现在可以比较来自 DT 和 DT_LU 的 thisTime。使用 by = .EACHI 确保包括满足条件的所有组合在结果数据表中。

或者，您也可以使用以下代码实现相同效果：

DT2 <- DT_LU[DT, .(airport=i.airport,
                   tid=i.t_id,
                   tTime=i.thisTime,
                   fTime=thisTime[i.thisTime < thisTime],
                   fid=f_id[i.thisTime < thisTime]),
             allow.cartesian=TRUE]

这将得到相同的结果：

> identical(DT1, DT2)
[1] TRUE

当您只想包含特定边界内的未来值时，可以使用：

DT1 <- DT_LU[DT, 
             {
               idx = i.thisTime < thisTime & thisTime - i.thisTime < 2
               .(tid  = i.t_id,
                 tTime = i.thisTime,
                 fTime = thisTime[idx],
                 fid = f_id[idx])
               },
             by=.EACHI]

这将产生：

> DT1
   airport tid tTime fTime fid
1:       a   1   5.1     6   1
2:       a   1   5.1     6   2
3:       a   4   5.1     6   1
4:       a   4   5.1     6   2
5:       a   3   5.1     6   1
6:       a   3   5.1     6   2
7:       d   2   6.2     7   2
8:       d   5   6.2     7   2

与之前的结果相比，现在第3、6、9、10和12行已被删除。

Data:

DT <- data.table(t_id = c(1,4,2,3,5),
                 airport = c("a","a","d","a","d"),
                 thisTime = c(5.1, 5.1, 6.2, 5.1, 6.2),
                 key=c("airport","thisTime"))

DT_LU <- data.table(f_id = c(rep(1,4),rep(2,3)),
                    airport = c("a","b","c","d","a","d","e"),
                    thisTime = c(6,7,8,9,6,7,8),
                    key=c("airport","thisTime"))