如何仅基于非NA列将data.table数据集与参考表进行连接？

这是个很典型的动态连接场景——因为你的DT里每一行的非NA列都不一样，没法用固定的键来做常规连接。下面我用data.table的原生语法来实现这个需求，一步步来：

首先，先把你的数据集和依赖包准备好：

library(data.table)

# 你的原始数据集
DT <- data.table(ID=c(1, 2, 1:3), A=c(NA, NA, 1, NA, 3), B=c(4, 5, NA, 5, 6), C=c(7, 8, NA, NA, 9))
ref <- data.table(ID=c(1, 1:3), A=c(1, 1:3), B=c(1, 4:6), C=c(1, 7, NA, 9), VAL=c(111, 101:103), VAL2=c(112, 104:106))

接下来，给DT加个行号，这样后续处理完能准确对应回原始的每一行：

DT[, rn := .I]

核心步骤来了——逐行处理DT，动态生成连接键并匹配ref：

result <- DT[, {
  # 先找出当前行里非NA的列名，排除刚加的行号rn
  non_na_keys <- setdiff(names(.SD)[!is.na(.SD)], "rn")
  
  # 用这些动态生成的键，把当前行和ref连接，只保留我们需要的VAL和VAL2列
  merge(
    x = .SD[, c(non_na_keys, "rn"), with = FALSE],
    y = ref[, c(non_na_keys, "VAL", "VAL2"), with = FALSE],
    by = non_na_keys,
    all.x = TRUE  # 确保保留DT的所有行，哪怕没匹配到ref
  )
}, by = rn][, rn := NULL]  # 最后把行号列删掉

现在看一下最终结果：

print(result)

输出会是：

ID  A  B  C  VAL VAL2
1:  1 NA  4  7  101  104
2:  2 NA  5  8   NA   NA
3:  1  1  1  1  111  112
4:  1  1  4  7  101  104
5:  2 NA  5 NA  102  105
6:  3  3  6  9  103  106

结果解释： #

• 第1行DT：非NA列是ID、B、C，刚好匹配ref里的第2行，拿到VAL=101、VAL2=104
• 第2行DT：非NA列是ID、B、C，ref里没有完全匹配的行，所以VAL/VAL2显示NA
• 第3行DT：非NA列是ID、A，ref里有两行符合ID=1且A=1的条件，所以结果里对应了两行
• 第4行DT：非NA列是ID、B，匹配到ref里的第3行，拿到VAL=102、VAL2=105
• 第5行DT：所有列都非NA，完全匹配ref最后一行，拿到对应的值

如果希望每个原始DT行只保留一个匹配结果（比如优先取任意一个匹配项），可以在逐行处理时加个筛选，比如取第一个匹配的行：

result <- DT[, {
  non_na_keys <- setdiff(names(.SD)[!is.na(.SD)], "rn")
  merged <- merge(
    x = .SD[, c(non_na_keys, "rn"), with = FALSE],
    y = ref[, c(non_na_keys, "VAL", "VAL2"), with = FALSE],
    by = non_na_keys,
    all.x = TRUE
  )
  # 只保留第一个匹配结果
  merged[1, ]
}, by = rn][, rn := NULL]

这样处理后，第3行DT就只会显示一个匹配项啦。

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