1

I have a keyed data.table to which I would like to add rows from another table of the same key:

library(data.table)
key.cols <- c("ID", "Code")
set.seed(1)
DT1 = data.table(
  ID = c("b","b","b","a","a","c"),
  Code = LETTERS[seq(1,6)],
  Number = runif(6)
);DT1

DT2 = data.table(
  ID = c("a","a","c","b","b","b"),
  Code = LETTERS[seq(4,9)],
  Number = runif(6)
);DT2

I would like to only add to DT1 rows from DT2 of the keys that do not occur in DT1 i.e. rbind a relative complement:

https://en.wikipedia.org/wiki/Complement_(set_theory)#Relative_complement

I can try and use setops and just add the keys letting the non-keyed columns be filled NA and join them afterwards:

DT1 <- rbind(DT1, fsetdiff(DT2[,(key.cols), with=FALSE], DT1[,(key.cols), with=FALSE]), fill=TRUE)
DT1[DT2, Number:=ifelse(is.na(Number), i.Number, Number), on = key.cols];DT1

Is there a less cumbersome way to do it?

HannesZ
  • 599
  • 2
  • 5
  • 17
  • Filter DT2 and rbind? `rbind(DT1, DT2[ (!paste(ID, Code) %in% paste(DT1$ID, DT1$Code)), ])` – zx8754 Feb 06 '19 at 12:43
  • Sure. But I will have to figure out how I to deal with a different vector of columns. Using paste it could get ugly – HannesZ Feb 06 '19 at 12:53
  • Possible duplicate / Related post? https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right – zx8754 Feb 06 '19 at 13:00

1 Answers1

4

Slightly less cumbersome is:

rbind(DT1, DT2[!DT1, on = .(ID, Code)])
   ID Code     Number
1:  b    A 0.26550866
2:  b    B 0.37212390
3:  b    C 0.57285336
4:  a    D 0.90820779
5:  a    E 0.20168193
6:  c    F 0.89838968
7:  b    G 0.06178627
8:  b    H 0.20597457
9:  b    I 0.17655675

Perhaps more tractable would be to use unique():

unique(rbind(DT1, DT2), by = c("ID", "Code"))
s_baldur
  • 29,441
  • 4
  • 36
  • 69