通过匹配行相减来组合两个R data.tables

问题描述

我想从另一个减去一个R data.table。我有一个匹配列的列表，以及一个要进行减法运算的列的列表。

I would like to subtract one R data.table from another. I have a list of matching columns, and a list of columns for the subtraction to operate on.

dt1 <- data.table(read.table(header=T, text=
"Date           Code       ColumnA    ColumnB Session
01/01/2013      AB         0          5       PRE
01/01/2013      CJ         15         25      PRE
01/01/2013      JJ         20         20      PRE
02/01/2013      JJ         25         15      PRE"))

dt2 <- data.table(read.table(header=T, text=
"Date           Code      ColumnA    ColumnB Session
01/01/2013      BB        15         25      POST
01/01/2013      AB        1          2       POST
02/01/2013      AB        25         15      POST
02/01/2013      JJ        35         15      POST"))

matchingCols <- c("Date", "Code")
mergingCols <- names(dt1)[3:4]

我想获取以下data.table：

I would like to get the following data.table:

         Date Time ColumnA ColumnB Session
1: 01/01/2013   AB      -1       3 PREPOST
2: 02/01/2013   JJ     -10       0 PREPOST

对于与matchCols相同的行，应从dt1中减去dt2中的mergingCols值，以便获得差值。

The mergingCols values in dt2 should be subtracted from dt1 for rows in which matchingCols are the same so I can obtain the difference values. There are too many mergingCols to specify them individually in the code.

这里有类似的问题，但是我无法将它们相减：

Here are similar questions, but I could not adapt them for subtraction:

是先rbind然后聚合方法还是加入方法？

Would either an rbind then aggregate approach or a joining approach work?

推荐答案

好吧，

dt1[
  dt2,
  on=matchingCols,
  lapply(
    setNames(mergingCols, mergingCols),
    function(x) get(x) - get(paste0("i.", x))
  ),
  nomatch=0L,
  by=.EACHI
]

#         Date Code ColumnA ColumnB
#1: 01/01/2013   AB      -1       3
#2: 02/01/2013   JJ     -10       0

这篇关于通过匹配行相减来组合两个R data.tables的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！