仅连接那些非 NA 的列

我有一个数据集，某些列的某些行中包含 NA：

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))
DT
#    ID  A  B  C
# 1:  1 NA  4  7
# 2:  2 NA  5  8
# 3:  1  1 NA NA
# 4:  2 NA  5 NA
# 5:  3  3  6  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))
ref
#    ID A B  C VAL VAL2
# 1:  1 1 1  1 111  112
# 2:  1 1 4  7 101  104
# 3:  2 2 5 NA 102  105
# 4:  3 3 6  9 103  106

问：我如何离开加入DT with ref每行使用非 NA 列？

所需的输出（添加换行符以强调分组）：

   ID  A  B  C VAL VAL2
1:  1 NA  4  7 101  104

2:  2 NA  5  8  NA   NA

3:  1  1 NA NA 111  112
4:  1  1 NA NA 101  104

5:  2 NA  5 NA 102  105

6:  3  3  6  9 103  106

我尝试按如下方式逐行执行此操作：

newcols <- c("VAL", "VAL2")
resLs <- lapply(split(DT, by="ID"), function(x) {
    #find those non-NA columns
    nonNACols <- names(x)[sapply(x, Negate(is.na))]

    #left join with ref table after subsetting the columns of ref table
    ref[, c(nonNACols, newcols), with=FALSE][x, on=nonNACols]
})

#combine the list of row results
ans <- rbindlist(resLs, use.names=TRUE, fill=TRUE)
setcolorder(ans, names(ref))
ans

如果解决方案可以通过某种组而不是逐行来完成，那就更好了。有什么建议么？

编辑：经过这么多小时终于解决了。通过分组使用 data.table：

cols <- c("ID","A", "B", "C")
newcols <- c("VAL", "VAL2")
DT[, grp := paste(names(.SD)[sapply(.SD, Negate(is.na))], collapse=""), by=seq_len(nrow(DT)), .SDcols=cols]

rbindlist(
    DT[, {
        vec <- names(.SD)[sapply(.SD, function(x) !all(is.na(x)))]
        list(list(ref[.SD, on=vec,
            c(vec, newcols), with=FALSE]))
    }, by=.(grp)]$V1,
    use.names=TRUE, fill=TRUE)

编辑：另一种编码方式

cols <- c("ID","A", "B", "C")
newcols <- c("VAL", "VAL2")
DT[, grp := paste(names(.SD)[sapply(.SD, Negate(is.na))], collapse="_"),
    by=seq_len(nrow(DT)),
    .SDcols=cols]

setnames(DT[,
    ref[.SD, on=strsplit(.BY$grp, split="_")[[1L]], 
        c(paste0("i.", cols), paste0("x.",newcols)), with=FALSE], 
    by=.(grp)][,-1L], 
    c(cols, newcols))[]

一种选择是匹配类似的东西A = A OR is.na(A)等等。但是，我认为你不能使用OR合并条件data.tables。对于像这样复杂的合并情况，我喜欢使用sqldf反而：

library(sqldf)
sqldf("SELECT l.*, r.VAL, r.VAL2
       FROM       DT as l
       LEFT JOIN  ref as r
       ON         l.ID = r.ID AND (l.A = r.A OR l.A IS NULL)
                  AND (l.B = r.B OR l.B IS NULL)
                  AND (l.C = r.C OR l.C IS NULL)
                  AND (l.A IS NOT NULL OR l.B IS NOT NULL OR l.C IS NOT NULL)")

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

请注意，最后一个条件确保如果您的所有A, B, C are NA那么它不会匹配任何行。