title: Seeking an better way to add columns in data.table from lookup table
body: <p>I'd like to create a new column <code>key</code> in my data.table by merging with a lookup table by common columns <code>index1</code> &amp; <code>index2</code>. Then from the values of this new <code>key</code> column (<code>a</code>,<code>b</code>,<code>c</code>), I'd like to generate 3 new columns (<code>a</code>,<code>b</code>,<code>c</code>) that indices the <code>value</code> column in the data.table. </p>
  
  <p>My data.table looks like this: </p>
  
  <pre><code>    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
  </code></pre>
  
  <p>The lookup table is this:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>The end result is like this:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>I tried solving it by first merging the data.table and the lookup table by <code>merge()</code>, then used <code>J()</code> 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:</p>
  
  <pre><code>DT &lt;- merge(DT, lookup, by=c('index1', 'index2'), all.x=TRUE)
  DT &lt;- 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]
  </code></pre>