基于timestamp的data.table滚动左连接并填充左表所有行

我有两个data.table（对应同一实体的规范化数据行），行数不同且都包含timestamp字段。需要基于timestamp执行滚动左连接，保留dt1的所有行，并用dt2中最接近的最近值填充dt1的每一行。之前的尝试会在非匹配区间留下NA，但我需要dt1的每一行都匹配dt2的最近记录。

之前尝试的代码 #

library(data.table)

set.seed(42)

timestamp <- sort(rnorm(10, mean = 1, sd = 1))

dt1 <- data.table(
  id = letters[1:10],
  timestamp = timestamp,
  timestamp1 = timestamp,
  other1 = 1:10,
  other2 = 11:20
)

dt2 <- data.table(
  timestamp = timestamp[c(3, 5, 8)] + 0.1,
  timestamp2 = timestamp[c(3, 5, 8)] + 0.1,
  other3 = c("x", "y", "z"),
  other4 = c(333, 444, 555)
)

dt1[dt2,
    roll = -Inf,
    on = "timestamp", 
    `:=`(dt2_timestamp2 = i.timestamp2,
          other3 = i.other3,
         other4 = i.other4)]

实际输出 #

id timestamp timestamp1 other1 other2 dt2_timestamp2 other3 other4
    <char>     <num>      <num>  <int>  <int>          <num> <char>  <num>
 1:      a 0.4353018  0.4353018      1     11             NA   <NA>     NA
 2:      b 0.8938755  0.8938755      2     12             NA   <NA>     NA
 3:      c 0.9053410  0.9053410      3     13             NA   <NA>     NA
 4:      d 0.9372859  0.9372859      4     14             NA   <NA>     NA
 5:      e 1.3631284  1.3631284      5     15       1.005341      x    333
 6:      f 1.4042683  1.4042683      6     16             NA   <NA>     NA
 7:      g 1.6328626  1.6328626      7     17       1.463128      y    444
 8:      h 2.3709584  2.3709584      8     18             NA   <NA>     NA
 9:      i 2.5115220  2.5115220      9     19       2.470958      z    555
10:      j 3.0184237  3.0184237     10     20             NA   <NA>     NA

期望输出 #

id timestamp timestamp1 other1 other2 dt2_timestamp2 other3 other4
    <char>     <num>      <num>  <int>  <int>          <num> <char>  <num>
 1:      a 0.4353018  0.4353018      1     11             NA   <NA>     NA
 2:      b 0.8938755  0.8938755      2     12             NA   <NA>     NA
 3:      c 0.9053410  0.9053410      3     13             NA   <NA>     NA
 4:      d 0.9372859  0.9372859      4     14             NA   <NA>     NA
 5:      e 1.3631284  1.3631284      5     15       1.005341      x    333
 6:      f 1.4042683  1.4042683      6     16       1.005341      x    333
 7:      g 1.6328626  1.6328626      7     17       1.463128      y    444
 8:      h 2.3709584  2.3709584      8     18       1.463128      y    444
 9:      i 2.5115220  2.5115220      9     19       2.470958      z    555
10:      j 3.0184237  3.0184237     10     20       2.470958      z    555

解决方案 #

问题出在roll = -Inf的参数设置，它仅会在dt1的timestamp恰好匹配或小于dt2的timestamp时填充，且仅对匹配的单行生效，不会向下延续填充区间内的其他行。

调整roll和rollends参数即可实现需求：

dt1[dt2,
    on = "timestamp", 
    roll = TRUE,
    rollends = c(NA, TRUE),
    `:=`(dt2_timestamp2 = i.timestamp2,
          other3 = i.other3,
         other4 = i.other4)]

参数说明 #

• roll = TRUE：允许dt1的timestamp大于dt2的timestamp时，自动匹配dt2中最近的、timestamp小于等于当前dt1 timestamp的记录
• rollends = c(NA, TRUE)：控制两端的滚动行为：
  • 第一个值NA：dt1中timestamp小于dt2最小timestamp的行，保留NA（对应前4行）
  • 第二个值TRUE：dt1中timestamp大于dt2最大timestamp的行，自动填充dt2的最后一条记录（对应第10行）

运行上述代码后，dt1的每行都会填充dt2中最匹配的最近值，与期望输出完全一致。

内容的提问来源于stack exchange，提问作者Androviperian