title: Conditional merging with many variables
body: <p>I am wondering if there was a faster, more elegant data.table solution to my following question.</p>
  <p>Suppose we have two datasets</p>
  <pre><code>set.seed(1)
  library(data.table)
  
  DT1 &lt;- data.table(income = runif(10, 0,1),
                    ID = 1:10)
  DT2 &lt;- data.table(height = runif(20,0,1),
                    weight = runif(20,0,1),
                    V1 = runif(20,0,1),
                    V2 = runif(20,0,2),
                    type = rep(c(&quot;Parents&quot;, &quot;Children&quot;),10))
  DT2 &lt;- DT2[order(type)][, ID := rep(1:10,2)]
  </code></pre>
  <p>The first dataset is a &quot;household&quot; level dataset with household identifiers given by ID, from 1:10.</p>
  <p>There is a second dataset, <code>DT2</code> which has four variables for each parent and child, for each household ID. What I want to do is merge all the variables (height, weight, V1, V2) of the Parents and Children in each row/observation of DT1. So we would have eight variables to merge, four for the parents and four for the children.</p>
  <p>To do so, I can simply write the following:</p>
  <pre><code>
  DT1[DT2[type == &quot;Parents&quot;], c(&quot;height_parents&quot;, &quot;weight_parents&quot;,
                                &quot;V1_parents&quot;, &quot;V2_parents&quot;) := list(i.height, i.weight,
                                                                    i.V1, i.V2), on = c(ID = &quot;ID&quot;)]
  DT1[DT2[type == &quot;Children&quot;], c(&quot;height_children&quot;, &quot;weight_children&quot;,
                                 &quot;V1_children&quot;, &quot;V2_children&quot;) := list(i.height, i.weight,
                                                                       i.V1, i.V2), on = c(ID = &quot;ID&quot;)]
  
  </code></pre>
  <p>The output looks like:</p>
  <pre><code> income ID height_parents weight_parents V1_parents V2_parents height_children
  1: 0.70647001  1     0.49163534    0.164385214  0.6806198 1.72937701      0.04655907
  2: 0.07658058  2     0.06776809    0.234182275  0.4456820 1.76814822      0.45665042
  3: 0.49770601  3     0.23255515    0.709256017  0.3514867 1.83387012      0.24395311
  4: 0.51944306  4     0.30555999    0.974742471  0.0529102 0.06094086      0.22356168
  5: 0.23075737  5     0.51104028    0.007269433  0.4157508 1.00207079      0.98915308
  6: 0.86449990  6     0.75420198    0.211342425  0.9837331 0.03520897      0.86080818
     weight_children V1_children V2_Children
  1:       0.3398447   0.7454582   0.6761706
  2:       0.8475106   0.4716267   1.5231691
  3:       0.8895790   0.1561395   1.8721056
  4:       0.3503219   0.2663775   0.2408758
  5:       0.3902352   0.9332958   1.3532260
  6:       0.7648748   0.6969372   1.3289579
  
  </code></pre>
  <p>Note that if I had many different &quot;types&quot; (here there is only two) and/or many different variables to merge (here there is only 4), the above would get quickly laborious and verbose. The dataset I am using has many different variables and types. Thus, I am looking to do this in a more efficient way. In particular, I want to be able to define a vector:</p>
  <pre><code>merge_variables = c(&quot;height&quot;, &quot;weight&quot;, &quot;V1&quot;, &quot;V2&quot;) 
  </code></pre>
  <p>and in a data.table fashion, have all of these variables merge for both parents and children. I want the new variables to have an underscore in them with the types name (e.g.<code>height_parents</code> and <code>height_children</code>).</p>
  <p>I hope I've communicated my request clearly.</p>
  <p>Thanks!</p>