title: Aggregating quarterly data sets for dynamic averages in R?
body: <p>I have quarterly expenditure data that look like this:</p>
  
  <p>Q1:</p>
  
  <pre><code>ID  TRANS
  1   454
  2   370
  3   40
  4   0
  </code></pre>
  
  <p>Q2:</p>
  
  <pre><code>ID  TRANS
  1   100
  2   400
  3   303
  4   43
  5   0
  6   100
  </code></pre>
  
  <p>Q3:</p>
  
  <pre><code>ID  TRANS
  3   20
  6   300
  7   400
  8   300
  9   100
  </code></pre>
  
  <p>Q4:</p>
  
  <pre><code>ID  TRANS
  3   231
  7   100
  8   240
  9   100
  10  102
  </code></pre>
  
  <p>Ultimately, I'd like to end up with a dataset that has columns for TRANS in each quarter, and then averages them based on the number of non-zero inputs in the code.</p>
  
  <p>So, for example, the final dataset would look something like this:</p>
  
  <pre><code>ID  T1  T2  T3  T4  TOT AVG
  1   454 100 0   0   554 554/2
  2   370 400 0   0   770 770/2
  3   40  303 20  231 594 594/4
  4   0   43  0   0   43  43/1
  5   0   0   0   0   0   0
  6   0   100 300 0   400 400/2
  7   0   0   400 100 500 500/2
  8   0   0   300 240 540 540/2
  9   0   0   100 100 200 200/2
  10  0   0   0   102 102 102/1
  </code></pre>
  
  <p>With T1 - T4 being the quarterly values (0 if 0 is written, or if no value is recorded), and AVG being the average of these values, dependent on the number of entries in T1 - T4.  </p>
  
  <p>Thus, if there were 2 values for ID #7, then the total would be divided by 2.  (I left the AVG category in dividend/divisor format so you could see the logic there).</p>
  
  <p>This seems a little complicated for a basic merge() function, in particular the tallying and dynamic averaging at the end.  Any ideas?</p>