Merging two data.tables with many to one realtionship in R?

1
votes

I have two data.table(s) where table 'DT1' is a master, and I want to merge data from a second table 'DT2' that has rows that match one or more of the rows in 'DT1'. I want to preserve all columns of DT1 and add additional columns from DT2(which are going to repeat eventually).

DT1:
ID          DEP CAT BCC value
ZCC81899ZZ  31,HH2  2,039   BCC1    0
ZZ02362D    2FF2,2F 02,06   BCC1    0
ZZ42716D    384,338 2   BCC1    0
ZZ64457TZZ  389,391 01,02   BCC1    1
ZCC81899ZZ  31,HH2  20,390  BCC2    1
ZZ02362D    2FF2,2  2   BCC2    1
ZZ42716D    384,338 2   BCC2    1
ZZ64457TZZ  389,391 01,02   BCC2    1
ZZ66595ZZ   14,191  11,072  BCC2    0
ZCC81899ZZ  31,HH2  203 BCC6    0
ZZ02362D    2FF2    2   BCC6    1
ZZ42716D    384,338 2   BCC6    0
ZZ64457TZZ  389,391 01,02   BCC6    0
ZZ66595ZZ   141,914 11,072  BCC6    0


DT2:
BCC    LABEL     CO     IN
BCC1   Dim1      0.47   1.904
BCC2   Dim2      0.535  0.575
BCC6   Dim3      0.44   0.344

So I want to merge these two data.table(s) with key BCC and result should be :

DT1:
ID          DEP CAT BCC value    LABEL    CO    IN
ZCC81899ZZ  31,HH2  2,039   BCC1    0        Dim1     0.47  1.904
ZZ02362D    2FF2,2F 02,06   BCC1    0        Dim1     0.47  1.904
ZZ42716D    384,338 2   BCC1    0        Dim1     0.47  1.904
ZZ64457TZZ  389,391 01,02   BCC1    1        Dim1     0.47  1.904
ZCC81899ZZ  31,HH2  20,390  BCC2    1        Dim2     0.535 0.575
ZZ02362D    2FF2,2  2   BCC2    1        Dim2     0.535 0.575
ZZ42716D    384,338 2   BCC2    1        Dim2     0.535 0.575
ZZ64457TZZ  389,391 01,02   BCC2    1        Dim2     0.535 0.575
ZZ66595ZZ   14,191  11,072  BCC2    0        Dim2     0.535 0.575
ZCC81899ZZ  31,HH2  203 BCC6    0        Dim3     0.44  0.34
ZZ02362D    2FF2    2   BCC6    1        Dim3     0.44  0.34
ZZ42716D    384,338 2   BCC6    0        Dim3     0.44  0.34
ZZ64457TZZ  389,391 01,02   BCC6    0        Dim3     0.44  0.34
ZZ66595ZZ   141,914 11,072  BCC6    0        Dim3     0.44  0.34

I am doing the following:

> setkey(DT1, BCC)
> setkey(DT2, BCC)
> DT1[DT2, `:=`(LABEL= i.LABEL, CO = i.CO), IN = i.IN)]

It is giving the results same as DT1 and I tried DT1[DT2] that also gave the same result. I have no idea where I am missing this. This should work I guess. Any help is appreciated.

* The issue is that I am generating DT1 after using "melt" operation and when I am calling BCC value (factor : datatype) and all coming as . When I passing both tables as command it is working fine. So I figured that this is the issue with the results after melt operation. Is there any other way of reshaping data in data.table?

1
rdata.table
try merge(DT1, DT2,by="BCC") – Dean MacGregor
sounds like you want DT2[DT1] – eddi
*** The issue is that I am generating DT1 after using "melt" operation and when I am calling BCC value (factor : datatype) and all coming as <NA>. When I passing both tables as command it is working fine. So I figured that this is the issue with the results after melt operation. Is there any other way of reshaping data in data.table? – nsDataSci
@nsDataSci I suggest using dput to share a small reproducible example of the problem you're having – eddi
would you do str(DT1) and str(DT2) and tell us what BCC says for each? – Dean MacGregor

1 Answers

0
votes

the merge command allows for one to many merges, by default it only keeps the inner join (when the 2 tables match), but using the commands all= ; all.x and all.y allow you to do any other option you want

the help page for merge makes this clear