title: How to create a new data table based on pairwise combinations of a subset of column names?
body: <p>I am trying to define a function that takes a data frame or table as input with a specific number of ID columns (e.g., 2 or 3 ID columns), and the remaining columns are NAME1, NAME2, ..., NAMEK (numeric columns). The output should be a data table that consists of the same ID columns as before plus one additional ID column that groups each unique pairwise combination of the column names (NAME1, NAME2, ...). In addition, we must gather the actual values of the numeric columns into two new columns based on the ID column; an example with two ID columns and three numeric columns:</p>
  <pre><code>ID1 &lt;- c(&quot;A&quot;,&quot;A&quot;,&quot;A&quot;,&quot;B&quot;,&quot;B&quot;,&quot;B&quot;)
  ID2 &lt;- c(1,2,3,1,2,3)
  NAME1 &lt;- c(10,11,9,22,25,22)
  NAME2 &lt;- c(7,9,8,20,22,21)
  NAME3 &lt;- c(10,12,11,15,19,30)
  DT &lt;- data.table(ID1,ID2,NAME1,NAME2,NAME3)
  </code></pre>
  <p>I want the output of the function with DT as input to be</p>
  <pre><code>ID.new &lt;- c(&quot;NAME1 - NAME2&quot;,&quot;NAME1 - NAME2&quot;,&quot;NAME1 - NAME2&quot;, &quot;NAME1 - NAME2&quot;,
   &quot;NAME1 - NAME2&quot;,&quot;NAME1 - NAME2&quot;, &quot;NAME1 - NAME3&quot;, &quot;NAME1 - NAME3&quot;,
   &quot;NAME1 - NAME3&quot;,&quot;NAME1 - NAME3&quot;,&quot;NAME1 - NAME3&quot;,&quot;NAME1 - NAME3&quot;,
   &quot;NAME2 - NAME3&quot;,&quot;NAME2 - NAME3&quot;,&quot;NAME2 - NAME3&quot;,&quot;NAME2 - NAME3&quot;,
   &quot;NAME2 - NAME3&quot;, &quot;NAME2 - NAME3&quot;)
  ID1 &lt;- c(&quot;A&quot;,&quot;A&quot;,&quot;A&quot;,&quot;B&quot;,&quot;B&quot;,&quot;B&quot;,&quot;A&quot;,&quot;A&quot;,&quot;A&quot;,&quot;B&quot;,&quot;B&quot;,&quot;B&quot;,&quot;A&quot;,&quot;A&quot;,&quot;A&quot;,&quot;B&quot;,&quot;B&quot;,&quot;B&quot;)
  ID2 &lt;- c(1,2,3,1,2,3,1,2,3,1,2,3,1,2,3,1,2,3)
  value.left &lt;- c(10,11,9,22,25,22,10,11,9,22,25,22,7,9,8,20,22,21)
  value.right &lt;- c(7,9,8,20,22,21,10,12,11,15,19,30,10,12,11,15,19,30)
  DT.output &lt;- data.table(ID.new,ID1,ID2,value.left,value.right)
  </code></pre>
  <p>I have found that fun() (see below) does the job, but is too slow for my liking:</p>
  <pre><code>  fun &lt;- function(data, ID.cols){
     data &lt;- data.table(data)
     # Which of the columns are ID columns
     ids &lt;-  which(colnames(data) %in% ID.cols)
     # Obtain all pairwise combinations of numeric columns into a list
     numeric.combs &lt;- combn(x = data.table(data)[,!ids, with = FALSE], m = 2, simplify = FALSE)
     id.cols &lt;- data[,ids, with = FALSE]
     # bind the ID columns to each pairwise combination of numeric columns inside the list
     bind.columns.each.numeric.comb &lt;- lapply(X = numeric.combs, FUN = function(x) cbind(id.cols,x)) 
     # Create generic names for the numeric columns so that rbindlist() may be applied. In addition we make a new column that groups based on which columns we are considering
     generalize &lt;- suppressWarnings(lapply(X = bind.columns.each.numeric.comb, FUN = function(x) 
     setattr(x = x[,ID.NEW:=paste(colnames(x[,!ids,with=FALSE]),collapse=&quot; - &quot;)], name = 
     'names', value = c(ID.cols,&quot;value.left&quot;,&quot;value.right&quot;,&quot;ID.NEW&quot;))))
     return(rbindlist(l=generalize))
  }
  
  # Performance
  print(microbenchmark(fun(DT,ID.cols=c(&quot;ID1&quot;,&quot;ID2&quot;)),times=1000))
  </code></pre>
  <p>Is there a faster and more elegant way to do this?</p>