0

I have two data.tables DT1 and DT2. I want to perform DT1 = DT1[DT2] without duplicating the data.table.

More specifically, I'm trying to update a subset of DT1 by adding columns from DT2 where there are matches. The default output NA for rows where there is no match is perfectly fine (I want to retain all rows of DT1). If I order the two data.tables properly with the same keys, DT1[DT2,c("col1","col2"):=list(DT2$col1,DT2$col2)] works (in the cases I've looked at: ie. no duplicates), but would become tedious with many columns.

Is there an easy way to do this?

(merge-like scenario with two data.tables addresses adding a single column and Merging two data.tables with many to one realtionship in R? doesn't address memory efficiency.)

Example:

dt = data.table(col1 = 1:3)
dt2 = data.table(col1 = 2:5,col2 = 3:6,col3=c("a","b","c","d"))
setkey(dt,col1)
setkey(dt2,col1)

Join dt and dt2 to add col2 and col3 to dt without making a copy of dt.

Community
  • 1
  • 1
Alexander Li
  • 307
  • 1
  • 3
  • 9
  • 1
    Switch `list(DT2$col1,DT2$col2)` to `.(i.col1, i.col2)`. For more cols, I think @akrun's answer here has you covered: http://stackoverflow.com/a/32899606/1191259 – Frank Nov 16 '15 at 21:23
  • 2
    Also related: http://stackoverflow.com/q/19553005/1191259 Maybe a dupe. – Frank Nov 16 '15 at 21:31
  • Thanks for the links @Frank. I just tried out @akrun's answer but it didn't work. Looking into it, I found that `DT1[DT2,i.col1]` returns a column of `NA` for some reason. Besides that though, `DT1[DT2,col1:=i.col1]` didn't add a column to `DT1`. – Alexander Li Nov 16 '15 at 22:00
  • 4
    That's odd. Maybe you'll need to give an example. – Frank Nov 16 '15 at 22:01
  • 1
    @Frank, I just tried out a trivial example and the solution you linked worked. Going to try it out again on my actual case in a moment... – Alexander Li Nov 17 '15 at 13:58
  • Sure. You can click "close" beneath the question and then navigate a menu to select the duplicate. I'm not sure if that'll close it or just start a vote to close it; if the latter, I can cast my vote afterwards. – Frank Nov 17 '15 at 14:16
  • 1
    Ah, turns out I don't have enough rep to do that yet :) – Alexander Li Nov 17 '15 at 14:21

0 Answers0