如何高效通过连接与合并为data.table补充数据？

需求：主表dt_main的name列存在NA值，需要从dt_add中匹配id对应的name补充，保留原表非NA值。

初始数据：

dt_main <- data.table(id    = c(1:5)
                  , name = c("a", "b", NA,NA,NA)
                  , stuff = c(11:15))

dt_add <- data.table(id = c(4:5)
                  , name = c("aaa", "bbb"))

一、更直接的实现方式 #

不用先连接再处理，直接通过data.table的更新连接一步搞定：

dt_main[dt_add, name := fcoalesce(name, i.name), on = "id"]

执行后dt_main直接得到预期结果：

id name stuff
1:  1    a    11
2:  2    b    12
3:  3 <NA>    13
4:  4  aaa    14
5:  5  bbb    15

这个方法直接在主表上操作，利用fcoalesce优先保留原表非NA值，只填充需要补充的NA项，比原方法更简洁高效。

二、melt+dcast报错的解决办法 #

你碰到的报错，是因为dcast要求聚合函数必须接受向量输入，且返回单个值，直接用fcoalesce做聚合函数时，它没有正确处理分组内的向量输入。修正方案如下：

修正后的代码 #

首先避免列名重名，再自定义聚合函数处理每个分组的向量：

# 先做连接，把原表的name重命名避免冲突
dt <- dt_add[dt_main, on = "id"]
setnames(dt, "i.name", "name_original")

# 融化数据
dt_melt <- melt(dt, measure.vars = c("name", "name_original"), value.name = "name_value")

# 重塑时用自定义聚合函数，取第一个非NA值
dt_main_final <- dcast(dt_melt, 
                      id + stuff ~ variable, 
                      fun.aggregate = function(x) Reduce(fcoalesce, x),
                      value.var = "name_value")

# 合并得到最终的name列
dt_main_final[, name := fcoalesce(name, name_original)][, c("name_original") := NULL]

也可以用更简单的聚合函数代替，效果一致：

fun.aggregate = function(x) na.omit(x)[1]

报错原因说明 #

原代码里用patterns("name")会把重名的两个列都纳入，但重名后melt会导致变量名混乱；而且fcoalesce默认是按元素匹配，不是处理整个分组向量，不符合dcast对聚合函数的要求。通过重命名列避免冲突，再用自定义函数处理每个分组的向量，就能满足dcast的返回值长度要求。

需要注意的是，这种melt+dcast的方法属于绕路解法，实际场景中完全没必要用，远不如前面的更新连接高效。

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