8

I have two data.tables, and one has a subset of rows/columns of another. I'd like to add values of the smaller data.table to the values of the larger one:

DT1 <- as.data.table(matrix(c(0, 1, 2, 3), nrow=2, ncol=2, 
       dimnames=list(c("a", "b"), c("a", "b"))), keep=T)
DT2 <- as.data.table(matrix(c(0, 0, 1, 2, 2, 1, 1, 0, 3), nrow=3, ncol=3, 
       dimnames=list(c("a", "b", "c"), c("a", "b", "c"))), keep=T)

DT1
#   rn a b
#1:  a 0 2
#2:  b 1 3
DT2
#   rn a b c
#1:  a 0 2 1
#2:  b 0 2 0
#3:  c 1 1 3

I'd like to add DT1 to DT2 so that I get

#   rn a b c
#1:  a 0 4 1
#2:  b 1 5 0
#3:  c 1 1 3

I know I can overwrite values of DT2 with DT1 very easily:

DT2[DT1, names(DT1) := DT1, on="rn"]

I was hoping that something like this would work:

DT2[DT1, names(DT1) := DT1 + .SD, on="rn"]

...but it doesn't. There's probably some simple variation on this that would work, though, right?

Stan
  • 1,227
  • 12
  • 26
  • Non-`data.table` solutions to this same problem can be found here: [How to merge and sum two data frames](https://stackoverflow.com/questions/38468502/how-to-merge-and-sum-two-data-frames) – divibisan May 14 '19 at 17:43

2 Answers2

9

You can use rbindlist() to bring the two together, then sum the values based on rn

rbindlist(list(DT1, DT2), fill=TRUE)[, lapply(.SD, sum, na.rm = TRUE), by = rn]
#    rn a b c
# 1:  a 0 4 1
# 2:  b 1 5 0
# 3:  c 1 1 3
Rich Scriven
  • 97,041
  • 11
  • 181
  • 245
7

I prefer Richard's way, but here's an alternative that looks more like the OP's initial idea:

vs = setdiff(names(DT1),"rn")
DT2[DT1, (vs) := {
  x.SD = mget(vs) 
  i.SD = mget(paste0("i.",vs)) 
  Map("+", x.SD, i.SD)
}, on="rn", by=.EACHI]
#    rn a b c
# 1:  a 0 4 1
# 2:  b 1 5 0
# 3:  c 1 1 3
Frank
  • 66,179
  • 8
  • 96
  • 180
  • 2
    While this is maybe a bit more convoluted, it's significantly faster than Richard's answer -- thanks! – Stan Oct 04 '15 at 01:38
  • 1
    Huh. I ran both on a small data.table (1.3M x 70) and Rich's was 70 times faster! 1.5 seconds against 102 seconds. – Ankhnesmerira Apr 16 '19 at 08:26