CODEWITHSUNDEEP
rmergedata.tableaggregate
Kayle Sawyer
Kayle Sawyer

Reputation: 589

Subtraction to combine two R data.tables by matching rows

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?

Upvotes: 1

Views: 1667

Answers (2)

thelatemail
thelatemail

Reputation: 93938

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

Upvotes: 4

chinsoon12
chinsoon12

Reputation: 25223

Another suggestion:

dt1[dt2, on=matchingCols, nomatch=0L][,
    .(Date, Code, ColA=ColumnA - i.ColumnA, 
            ColB=ColumnB - i.ColumnB,
            Session=paste0(Session,i.Session))]

Upvotes: 1

Related Questions