r data.table更新联接中的多个列 [英] r data.table update multiple columns in join

问题描述

我有两个具有相同结构的data.table。两个关键列，后跟多个数据列。数据列的数量可能会有所不同。
我想将第二个data.table中的值添加到第一个data.table中的相应行/列中。

I have two data.table with the same structure. Two key columns followed by a number of data columns. The number of data columns may vary. I want to add the values from the second data.table to the corresponding rows/columns in the first data.table.

DT1 <- cbind(data.table(loc=c("L1","L2","L3"), product=c("P1","P2","P1")), matrix(10,nrow=3,ncol=12))
setkey(DT1, loc, product)
DT1
   loc product V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12
1:  L1      P1 10 10 10 10 10 10 10 10 10  10  10  10
2:  L2      P2 10 10 10 10 10 10 10 10 10  10  10  10
3:  L3      P1 10 10 10 10 10 10 10 10 10  10  10  10
DT2 <- cbind(data.table(loc=c("L2","L3"), product=c("P2","P1")), matrix(1:24,nrow=2,ncol=12))
setkey(DT2, loc, product)
   loc product V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12
1:  L2      P2  1  3  5  7  9 11 13 15 17  19  21  23
2:  L3      P1  2  4  6  8 10 12 14 16 18  20  22  24

到目前为止，我最好的选择是

My best bet so far is the following

DT1[DT2, 3:14 := as.data.table(DT1[DT2, 3:14, with=FALSE] + DT2[, 3:14, with=FALSE]), with=FALSE]
   loc product V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12
1:  L1      P1 10 10 10 10 10 10 10 10 10  10  10  10
2:  L2      P2 11 13 15 17 19 21 23 25 27  29  31  33
3:  L3      P1 12 14 16 18 20 22 24 26 28  30  32  34

请注意，nrow和ncol以及loc和product条目都是可变的，具体取决于源数据。

Note that nrow and ncol and the loc and product entries are all variable depending on the source data.

如果DT2中的每一行都与DT1中的每一行匹配，则此方法有效，否则将产生意外结果。
是否有更严格/优雅的方法来表示RHS来完成同时引用DT1和DT2的可变数量的列分配？

This works if every row in DT2 matches one in DT1, but otherwise will have unexpected results. Is there a more rigorous/elegant way to express the RHS to do this variable number of column assignments referring to both DT1 and DT2?

推荐答案

怎么样：

cols = paste0('V', 1:12)

DT1[DT2, (cols) := setDT(mget(cols)) + mget(paste0('i.', cols))]
DT1
#   loc product V1 V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12
#1:  L1      P1 10 10 10 10 10 10 10 10 10  10  10  10
#2:  L2      P2 11 13 15 17 19 21 23 25 27  29  31  33
#3:  L3      P1 12 14 16 18 20 22 24 26 28  30  32  34

这篇关于r data.table更新联接中的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！