data.table多匹配滚动连接的优化实现及多列适配问询

问题描述 #

我拥有两个data.table对象dt1和dt2，需按唯一ID与日期执行连接操作。因日期可能无法完全匹配，需借助data.table的roll特性，但dt2中存在同一ID和日期对应多行的情况，我希望匹配所有这类行。

数据示例 #

library(data.table)
library(lubridate) # 用于dmy日期转换函数

dt1 <- data.table(ID = 1:2,
                  Date = c(dmy("31122021"), dmy("31122022")),
                  Value1 = c("A", "B"))

dt2 <- data.table(ID = c(rep(1, times=6), rep(2, 8)),
                  Company = c(1:3, 1:3, 1:4, 1:4),
                  Date = c(rep(dmy("31122021"), times=3),
                           rep(dmy("31032022"), times=3),
                           rep(dmy("31032023"), times=4),
                           rep(dmy("30062023"), times=4)),
                  Value2 = 1:14)

setkey(dt1, ID, Date)
setkey(dt2, ID, Date)

dt1输出：

ID       Date Value1
1:  1 2021-12-31      A
2:  2 2022-12-31      B

dt2输出：

ID Company       Date Value2
 1:  1       1 2021-12-31      1
 2:  1       2 2021-12-31      2
 3:  1       3 2021-12-31      3
 4:  1       1 2022-03-31      4
 5:  1       2 2022-03-31      5
 6:  1       3 2022-03-31      6
 7:  2       1 2023-03-31      7
 8:  2       2 2023-03-31      8
 9:  2       3 2023-03-31      9
10:  2       4 2023-03-31     10
11:  2       1 2023-06-30     11
12:  2       2 2023-06-30     12
13:  2       3 2023-06-30     13
14:  2       4 2023-06-30     14

现有问题 #

直接执行滚动连接时，仅能返回部分匹配行：

dt2[dt1, roll = "nearest"]

输出：

ID Company       Date Value2 Value1
1:  1       1 2021-12-31      1      A
2:  1       2 2021-12-31      2      A
3:  1       3 2021-12-31      3      A
4:  2       1 2022-12-31      7      B

但ID=2时，Value2=7至10的行属于同一ID和日期，我需要返回所有这些行。

当前可行但繁琐的方案 #

以下代码可得到期望结果，但步骤冗余：

dt1[,Date1:=Date]
dt <- dt1[dt2, roll="nearest"]
dt[,DiffDate:=abs(as.numeric(Date1-Date))]
dt[,MinDiffDate:=min(DiffDate),by=ID]
dt <- dt[DiffDate==MinDiffDate]
dt

输出：

ID       Date Value1      Date1 Company Value2 DiffDate MinDiffDate
1:  1 2021-12-31      A 2021-12-31       1      1        0           0
2:  1 2021-12-31      A 2021-12-31       2      2        0           0
3:  1 2021-12-31      A 2021-12-31       3      3        0           0
4:  2 2023-03-31      B 2022-12-31       1      7       90          90
5:  2 2023-03-31      B 2022-12-31       2      8       90          90
6:  2 2023-03-31      B 2022-12-31       3      9       90          90
7:  2 2023-03-31      B 2022-12-31       4     10       90          90

其他方案的问题 #

尝试过相关滚动连接方案，但均无法得到期望结果：

# 某方案尝试
DT1 <- dt2[dt1, roll="nearest"]
DT2 <- dt1[dt2, roll = "nearest"]
dt_match <- unique(rbindlist(list(DT1, DT2), use.names=TRUE))
dt_match

输出会包含多余的行（如ID=1的2022-03-31数据、ID=2的2023-06-30数据），不符合需求。

后续更新：处理dt1新增列的情况 #

当dt1包含更多需要保留的列时：

dt1 <- data.table(ID = 1:2,
                  Date = c(dmy("31122021"), dmy("31122022")),
                  Value1 = c("A", "B"),
                  Info1  = c("Info x", "Info y"),
                  Info2  = c("Info 1", "Info 2"))

需要调整之前的连接代码，避免手动输入所有列名。之前尝试的代码无效：

colnames <- c("Date=Date1", names(dt1)[names(dt1)!="Date"])
dt2[
  unique(dt2[, Date1 := Date][dt1, colnames, with=FALSE, roll = -Inf]),
  on=.(ID, Date)]

优化解决方案 #

可以通过以下方式自动保留dt1的所有列，同时完成滚动匹配并关联dt2的多行数据：

# 第一步：获取每个ID对应的初始滚动匹配结果，保留dt1所有列
dt_matches <- dt2[, Date1 := Date][dt1, .SD, roll = "nearest"]
# 第二步：计算日期差，筛选每个ID对应的最近匹配日期
dt_matches[, DiffDate := abs(as.numeric(Date - Date1))]
dt_matches <- dt_matches[, .(MatchDate = Date[DiffDate == min(DiffDate)]), by = ID]
# 第三步：关联dt2中对应ID和匹配日期的所有行，同时保留dt1完整字段
result <- dt1[dt2[dt_matches, on = .(ID, Date = MatchDate)], on = .(ID)]
# 可选：整理列顺序，让结果更清晰
setcolorder(result, c("ID", "Date", "Date1", names(dt1)[!names(dt1) %in% c("ID", "Date")], names(dt2)[!names(dt2) %in% c("ID", "Date")]))
result

该方案无需手动指定列名，自动保留dt1的所有字段，同时正确匹配dt2中同一ID和最近日期的所有行。

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