R:使用第二个表合并+更新表的有效方法，其中来自相同列名的值填充 NA

Question

摘要 : 我想通过共享 id 合并两个表 key 为 all=true (全外连接)，而不是具有相同名称的列设置为 var1.x var2.y等，它们被合并为一列，其中左表中缺失的 (NA) 值由右表中的值填充(除了合并的标准行为，即附加具有不同 id 的行和列不同的名称)。

详情:

我要合并+更新table1与 table2基于共享 id关键列，这样:

1) 如果 table1和 table2具有相同名称的列(除了 id )，在 table1 中的值如果存在并被 table2 中的值替换，则将其保留。如果 table1 中的值是 NA。

2) 如果 table2 具有 table1 没有的列(不同名称)，则将它们合并(按 id)。

3) 如果 table1有一个 id不匹配 table2 ，来自 table2 的不同名称列的值不适用

4) 如果 table2有一个 id不匹配 table1 ，它被添加为一个新行和来自 table1 的不同列名的值。不适用。

3 和 4 与标准相同 merge与 all=TRUE .

我担心我把问题想得太多了，因为我找不到用 merge 做这件事的直接方法。或 join不涉及创建 ifelse检查每一列。真实数据有大约 1000 列，所以做 ifelse 的解决方案会非常长查找每个。

可重现的简化示例:

table1  <- data.table(id  =c("id1", "id2", "id3", "id4", "id5", "id6"),
                      var1=c(1,2,3,4,5, 6),
                      var2=c("a", "b", NA, "d", NA, "f"),
                      var3=c(NA, 12, 13, 14, 15, 16));

table2  <- data.table(id  =c("id1", "id2", "id3", "id4", "id5", "id8"),
                      var1=c(1,2,NA,4,5, 8),
                      var2=c(NA, "b", "c", "d", "e", "h"),
                      var4=c("foo", "bar", "oof", "rab", NA, "sna"));

desired <- data.table(id=c("id1", "id2", "id3", "id4", "id5", "id6", "id8"),
                      var1=c(1,2,3,4,5, 6, 8),
                      var2=c("a", "b", "c", "d", "e", "f", "h"),
                      var3=c(NA, 12, 13, 14, 15, 16, NA),
                      var4=c("foo", "bar", "oof", "rab", NA, NA, "sna"));

table1;
    id var1 var2 var3
1: id1    1    a   NA
2: id2    2    b   12
3: id3    3   NA   13
4: id4    4    d   14
5: id5    5    e   15
6: id6    6    f   16

table2;
    id var1 var2 var4
1: id1    1    a  foo
2: id2    2    b  bar
3: id3   NA    c  oof
4: id4    4    d  rab
5: id5    5    e   NA
6: id8    8    h  sna

desired
    id var1 var2 var3 var4
1: id1    1    a   NA  foo
2: id2    2    b   12  bar
3: id3    3    c   13  oof
4: id4    4    d   14  rab
5: id5    5    e   15   NA
6: id6    6    f   16   NA
7: id8    8    h   NA  sna

所需输出的说明:

对于列 var1 , table1有所有的值，所以它被单独留下，NA为 id3在 table2被忽略(请注意，这不包括下面描述的不同 id 的行合并)。

对于列 var2 , table缺少由 id3 索引的值，所以更新自 table2 (请注意，这不包括下面描述的不同 id 的行合并)。

对于列 var3 , table2 中没有匹配的列，所以它保持原样。

对于列 var4 ，没有列var4在 table1 ，所以它是从 table2 合并而来的来自 id关键变量。

对于带有 id6 的行在 table1 , 没有匹配的 id6在 table2 ，所以列的值 var4仅在 table2在 desired 中是 NA行输出 id6 .

对于带有 id8 的行在 table2没有匹配的 id8在 table1 ，所以列的值 var3仅在 table1在 desired 中是 NA行输出 id8 .

当然，使用 data.table 有一种直接的方法可以做到这一点。 ?考虑到真实数据的规模，高效的解决方案尤其受欢迎。 datamerge包显然曾经完全做到这一点，但它不再在 CRAN 上，我无法从 zip 让它在 R3.2.3 上工作。是否有另一个软件包加强了这项任务？还有许多其他线程专注于为具有已知名称的一列或几列解决此问题，但对于大量列，它们似乎并不实用。

Answer 1

这是一种方法:

com.cols    = setdiff(intersect(names(table1), names(table2)), "id")
com.cols.x  = paste0(com.cols, ".x")
com.cols.y  = paste0(com.cols, ".y")

# create combined table
DT = setkey(merge(table1, table2, by="id", all=TRUE), NULL)

# edit common columns where NAs are present
for (j in seq_along(com.cols)) 
  DT[is.na(get(com.cols.x[j])), (com.cols.x[j]) := get(com.cols.y[j])]

# remove unneeded columns
DT[, (com.cols.y) := NULL]

# rename kept columns
setnames(DT, com.cols.x, com.cols)

identical(DT, desired) # TRUE

创建和使用所有这些列名向量相当麻烦。

关于原问题...

这是另一种方式(不从 table2 导入新行，如原始帖子中所述):

com.cols    = setdiff(intersect(names(table1), names(table2)), "id")
i.com.cols  = paste0("i.", com.cols)
new.cols    = c(i.com.cols, setdiff(names(table2), c("id", com.cols)))

# grab columns from table2
table1[table2, (new.cols) := mget(new.cols), on="id"]

# edit common columns where NAs are present
for (j in seq_along(com.cols)) 
  table1[is.na(get(com.cols[j])), (com.cols[j]) := get(i.com.cols[j])]

# remove unneeded columns
table1[, (i.com.cols) := NULL]

这样所有的步骤都是对 table1 的修改引用。