title: R Data.table for computing summary stats across multiple columns
body: <p>I have a similar question to: <a href="https://stackoverflow.com/questions/20592636/r-data-table-searching-on-multiple-columns-and-setting-data-type">R: data.table : searching on multiple columns AND setting data type</a> , but this question did not get fully answered. I have a pairwise table that looks conceptually like the one below. The table is the result of converting a very large distance matrix into a data.table (> 100,000,000 rows), such that the comparison a,b is the same as b,a. However a and b may appear in either column V1 or V2. I want to compute simple summary statistics using data.table's querying style, but i haven't quite figured out how to select keys in either column. Is this possible? </p>
  
  <p>I've tried setting keys in either direction, but this returns just the data for that column. I also tried using list(), but that returns the intersection (understandably), i hoped for a by=key1|key2, but no such luck. </p>
  
  <hr>
  
  <pre><code>&gt; set.seed(123)
  &gt; 
  &gt; #create pairwise data
  &gt; a&lt;-data.table(t(combn(3,2)))
  &gt; #create column that is equal both ways, 1*2 == 2*1
  &gt; dat&lt;-a[,data:=V1*V2]
  &gt; dat
     V1 V2 data
  1:  1  2    2
  2:  1  3    3
  3:  2  3    6
  #The id ==2 is the problem here, the mean should be 4 ((2+6)/2)
  
  &gt; #set keys
  &gt; setkey(dat,V1,V2)
  &gt; 
  &gt; #One way data
  &gt; dat[,c("MEAN","VAR"):=list(mean(data),var(data)),by=V1]
  &gt; dat
     V1 V2 data MEAN VAR
  1:  1  2    2  2.5 0.5
  2:  1  3    3  2.5 0.5
  3:  2  3    6  6.0  NA
  
  &gt; #The other way
  &gt; dat[,c("MEAN","VAR"):=list(mean(data),var(data)),by=V2]
  &gt; dat
     V1 V2 data MEAN VAR
  1:  1  2    2  2.0  NA
  2:  1  3    3  4.5 4.5
  3:  2  3    6  4.5 4.5
  &gt; 
  &gt; #The intersect just produces the original data
  &gt; dat[,c("MEAN","VAR"):=list(mean(data),var(data)),by=list(V1,V2)]
  &gt; dat
     V1 V2 data MEAN VAR
  1:  1  2    2    2  NA
  2:  1  3    3    3  NA
  3:  2  3    6    6  NA
  &gt; 
  &gt; #Meaningless but hopefull attempt. 
  &gt; dat[,c("MEAN","VAR"):=list(mean(data),var(data)),by=V1|V2]
  &gt; dat
     V1 V2 data     MEAN      VAR
  1:  1  2    2 3.666667 4.333333
  2:  1  3    3 3.666667 4.333333
  3:  2  3    6 3.666667 4.333333
  #The goal is to create a table would look like this (using mean as an example)
  ID MEAN
   1  2.5
   2  4.0
   3  4.5
  </code></pre>
  
  <p>My default ideas would be too loop through a dat[V1==x|V2==x] statement, but i don't think i'm harnessing the full power of data.table to return a <em>single</em> column of ids with mean the var as summary columns. </p>
  
  <p>Thank you!</p>