title: R: Aggregating over several variables and observations (depending on values) and creating a new variable
body: <p>The data set has the following structure</p>
  
  <pre><code>    Key         Date         Mat    Amount
       &lt;int&gt;     &lt;date&gt;       &lt;chr&gt;  &lt;dbl&gt;
  1  1001056    2014-12-12    10025  0.10
  2  1001056    2014-12-23    10025  0.20
  3  1001056    2015-01-08    10025  0.10
  4  1001056    2015-04-07    10025  0.20
  5  1001056    2015-05-08    10025  0.20
  6  1001076    2013-10-29    10026  3.00
  7  1001140    2013-01-18    10026  0.72
  8  1001140    2013-04-11    10026  2.40
  9  1001140    2014-10-08    10026  0.24
  10 1001237    2015-02-17    10025  2.40
  11 1001237    2015-02-17    10026  3.40
  </code></pre>
  
  <p>Mat takes values in {10001,...,11000}, hence A:=|Mat|=1000.</p>
  
  <p>I would like to accomplish the following goals:</p>
  
  <p>1) (Intermediate step) For each Key-Date combination I would like to calculate for all materials, which are availabe at such a combination (which might vary from key to key), the differences in amount,
  e.g. for combination "1001237    2015-02-17"  this would be for materials 10025 and 10026 2.40-3.40=-1 (but might be more combinations). (How to store those values effienently?)
  This step might be skipped.</p>
  
  <p>2) Finally, I would like to construct a new matrix of dimension A=1000 where each entry (i,j) (Material combination i and j) contains the average of the values calculated in the step before.
  More formally, entry (i,j) is given by, </p>
  
  <p>1/|all key-date combinationas containing Mat i and Mat j| \sum_{all key-date combinationas containing Mat i and Mat j} Amount_i - Amount_j</p>
  
  <p>As the table is quite large efficiency of the computation is very important.</p>
  
  <p>Thank you very much for your help in advance!</p>