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

Question

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]

Answer 1

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