Seeking an better way to add columns in data.table from lookup table

data.table, r

Solution

Since you want to join on index1 & index2, you can use `merge` as you have or if you assign these as keys to each of your tables, then you can simply use `[]` to join. (As in `DT[lookup]` )

 setkey(lookup, index1, index2)
 setkey(DT, index1, index2)

then you can iterate over each unique `key` from your `lookup` table as follows

 keyVals <- unique(lookup[, key])

 for (k in keyVals)
   DT[lookup[key==k], c(k) := value]

Results:

DT
#     index1 index2 value    a     b  c
#  1:      1      2 -5.00   NA -5.00 NA
#  2:      1      2 -7.50   NA -7.50 NA
#  3:      1      2 -8.75   NA -8.75 NA
#  4:      2      0  0.00   NA    NA NA
#  5:      2      1 15.00 15.0    NA NA
#  6:      2      1 12.50 12.5    NA NA
#  7:      2      2 15.00   NA    NA 15
#  8:      3      2 -5.00   NA -5.00 NA
#  9:      3      2 17.50   NA 17.50 NA
# 10:      3      2  3.75   NA  3.75 NA

Problem

I'd like to create a new column `key` in my data.table by merging with a lookup table by common columns `index1` & `index2`. Then from the values of this new `key` column (`a`,`b`,`c`), I'd like to generate 3 new columns (`a`,`b`,`c`) that indices the `value` column in the data.table. My data.table looks like this: ``` index1 index2 value 1 2 0 0.00 2 1 2 -5.00 3 3 2 -5.00 4 3 2 17.50 5 2 2 15.00 6 1 2 -7.50 7 3 2 3.75 8 1 2 -8.75 9 2 1 15.00 10 2 1 12.50 ``` The lookup table is this: ``` index1 index2 key 1 1 1 a 2 1 2 b 3 2 1 a 4 2 2 c 5 3 1 c 6 3 2 b ``` The end result is like this: ``` index1 index2 value key a b c 1 2 0 0.00 NA NA NA NA 2 1 2 -5.00 b NA -5.00 NA 3 3 2 -5.00 b NA -5.00 NA 4 3 2 17.50 b NA 17.50 NA 5 2 2 15.00 c NA NA 15.00 6 1 2 -7.50 b NA -7.50 NA 7 3 2 3.75 b NA 3.75 NA 8 1 2 -8.75 b NA -8.75 NA 9 2 1 15.00 a 15.00 NA NA 10 2 1 12.50 a 12.50 NA NA ``` I tried solving it by first merging the data.table and the lookup table by `merge()`, then used `J()` three separate times to achieve the above result. I'm pretty new to data.table, but would love learn a more elegant way to solve this instead of repeating the procedure several times. Here's my code: ``` DT <- merge(DT, lookup, by=c('index1', 'index2'), all.x=TRUE) DT <- data.table(DT) #Don't know why but DT became a data.frame after merge() DT[J("a"), a:=value] DT[J("b"), b:=value] DT[J("c"), c:=value] ```

Original source