1

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]

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

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:

How to apply same function to every specified column in a data.table

Merge data frames whilst summing common columns in R

combine data.tables and sum the shared column

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

Kayle Sawyer
  • 549
  • 7
  • 22

2 Answers2

4

Okay, this should be totally automated, naming and all:

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
thelatemail
  • 91,185
  • 12
  • 128
  • 188
1

Another suggestion:

dt1[dt2, on=matchingCols, nomatch=0L][,
    .(Date, Code, ColA=ColumnA - i.ColumnA, 
            ColB=ColumnB - i.ColumnB,
            Session=paste0(Session,i.Session))]
chinsoon12
  • 25,005
  • 4
  • 25
  • 35
  • I like the simplicity of this approach, but there are too many mergingCols to specify them individually in the code (i.e., no typing out "ColumnA"). Many thanks to both chinsoon12 and thelatemail! – Kayle Sawyer Dec 14 '18 at 20:53