1

I have two data.tables, and one has a subset of rows/columns of another. I'd like to increment values of the larger data.table by 1 for every non-zero value in smaller table:

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 increment values in DT2 so that I get

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

(This is similar to my previous question on adding DT1 and DT2: Adding values in two data.tables... I need to do both :) )

Community
  • 1
  • 1
Stan
  • 1,227
  • 12
  • 26

2 Answers2

5

Another way:

require(data.table) # v1.9.6+
xcols = c("a", "b")
icols = paste0("i.", xcols) # "i.*" to refer to DT1's cols
DT2[DT1, (xcols) := Map(function(x, y) x + (y > 0L), mget(xcols), mget(icols)), on="rn"]

How this should look like:

DT2[DT1, (xcols) := Map(function(x, y) x + (y > 0L), .SD, i.SD), .SDcols=xcols, i.SDcols = icols]

or even better would be:

DT2[DT1, (xcols) := .SD + (i.SD > 0L), .SDcols=xcols, i.SDcols=icols]
Frank
  • 66,179
  • 8
  • 96
  • 180
Arun
  • 116,683
  • 26
  • 284
  • 387
  • I'm a little confused by the the second part.. The first piece of code works great. The "event better" part looks clean and I would love to write it like that, but I get "unused argument (i.SDcols = icols)" when I do. – Stan Oct 03 '15 at 04:54
  • 1
    That's because it's not implemented yet. – Arun Oct 03 '15 at 09:06
2

I'd consider something like...

inc_em <- with(melt(DT1)[value != 0], split(rn, variable))

for (k in names(inc_em))
    DT2[.(rn = inc_em[[k]]), (k) := get(k) + 1, on="rn" ]

#    rn a b c
# 1:  a 0 3 1
# 2:  b 1 3 0
# 3:  c 1 1 3
Frank
  • 66,179
  • 8
  • 96
  • 180