R: Эффективный способ слияния + Обновить таблицу со второй таблицей, где значения из одинаковых имен столбцов заполняют NA

Сводка. Я хотел бы объединить две таблицы общим ключом id как all=true (полное внешнее соединение), где вместо столбцов с теми же именами, которые установлены как var1.x var2.y и т.д., они объединяются как один столбец, в котором значения отсутствующих (NA) в левой таблице заполняются значениями из правой таблицы (в дополнение к стандартным поведением слияния, то есть добавлением строк с различными идентификаторами и столбцами с четкими именами).

Детали:

Я хотел бы объединить + обновить table1 с помощью table2 на основе общего столбца ключа id, чтобы:

1) Если table1 и table2 имеют столбцы с тем же именем (кроме id), значение в table1 остается в покое, если оно существует и заменяется значением в table2, если значение в table1 равно NA.

2) Если таблица2 имеет столбцы, которые не имеют таблицы1 (разные имена), они объединяются (по id).

3) Если table1 имеет id, который не соответствует в table2, значения для разных столбцов имен из table2 равны NA

4) Если table2 имеет id, который не совпадает с table1, он добавляется как новая строка, а значения для разных имен столбцов из table1 равны NA.

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 игнорируется (обратите внимание, что это не включает строку слияние для разных идентификаторов, описанных ниже).

• Для столбца var2 в table отсутствует значение, проиндексированное id3, поэтому оно обновляется с table2 (обратите внимание, что это не включает слияние строк для разных идентификаторов, описанных ниже),

• Для столбца var3 в столбце table2 отсутствует соответствующий столбец, поэтому он сохраняется как есть.

• Для столбца var4 в table1 не было столбца var4, поэтому он объединяется из table2 с помощью ключевой переменной id.

• Для строки с id6 в table1 нет соответствия id6 в table2, поэтому значение для столбца var4, которое находится только в table2, равно NA в desired вывод для строки id6.

• Для строки с id8 в table2 нет соответствия id8 в table1, поэтому значение для столбца var3, которое находится только в table1, равно NA в desired вывод для строки id8.

Конечно, есть простой способ сделать это с помощью data.table? Эффективные решения особенно приветствуются, учитывая размер реальных данных. Пакет datamerge, по-видимому, использовался для этого, но он больше не работает на CRAN, и я не могу заставить его работать с R3.2.3 из zip. Был ли еще один пакет для этой задачи? Есть много других потоков, которые сосредоточены на решении этого для одной или нескольких столбцов с известными именами, но для большого количества столбцов они не кажутся практичными.

Ответы

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

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]

com.cols    = setdiff(intersect(names(table1), names(table2)), "id")
i.com.cols  = paste0("i.", com.cols)
# I'm using the same var names as Frank, but new.cols is strictly the new ones here
new.cols    = setdiff(names(table2), names(table1))

# this is easy - the previously absent cols
table1[table2, (new.cols) := mget(new.cols), on = 'id']

# now for the ones that need updating
table1[table2, on = 'id',
       (com.cols) := Map(function(col, i.col) pmin(col, i.col, na.rm = T),
                         mget(com.cols), mget(i.com.cols))]

table1 <- table1[.(id = union(id, table2$id)), on = "id"]

in_common <- parse(text=setdiff(intersect(names(table1), names(table2)), "id"))

for (ii in in_common)
  table1[is.na(eval(ii)), 
         as.character(ii) :=
           table2[.SD, eval(ii), on = "id"]]

new_cols <- setdiff(names(table2), names(table1))

for (jj in new_cols)
  table1[table2, (jj) := eval(parse(text = jj)), on = "id"]

in_common_c <- setdiff(intersect(names(table1), names(table2)), "id"))
in_common_q <- parse(text=in_common_c)

for (ii in seq_along(in_common_q))
  table1[is.na(eval(in_common_q[ii])), 
         in_common_c[ii] :=
           table2[.SD, eval(in_common_q[ii]), on = "id"]]