title: Reshaping data.table with cumulative sum
body: <p>I want to reshape a data.table, and include the historic (cumulative summed) information for each variable. The <code>No</code> variable indicates the chronological order of measurements for object <code>ID</code>. At each measurement additional information is found. I want to aggregate the known information at each timestamp <code>No</code> for object <code>ID</code>.</p>
  
  <p>Let me demonstrate with an example:</p>
  
  <p>For the following data.table:</p>
  
  <pre><code>df &lt;- data.table(ID=c(1,1,1,2,2,2,2),
                   No=c(1,2,3,1,2,3,4), 
                   Variable=c('a','b', 'a', 'c', 'a', 'a', 'b'),
                   Value=c(2,1,3,3,2,1,5))
  df
     ID No Variable Value
  1:  1  1        a     2
  2:  1  2        b     1
  3:  1  3        a     3
  4:  2  1        c     3
  5:  2  2        a     2
  6:  2  3        a     1
  7:  2  4        b     5
  </code></pre>
  
  <p>I want to reshape it to this:</p>
  
  <pre><code>       ID No  a  b  c
      1:  1  1  2 NA NA
      2:  1  2  2  1 NA
      3:  1  3  5  1 NA
      4:  2  1 NA NA  3
      5:  2  2  2 NA  3
      6:  2  3  3 NA  3
      7:  2  4  3  5  3
  </code></pre>
  
  <p>So the summed values of <code>Value</code>, per <code>Variable</code> by <code>(ID, No)</code>, cumulative over <code>No</code>. </p>
  
  <p>I can get the result without the cumulative part by doing </p>
  
  <pre><code>dcast(df, ID+No~Variable, value.var="Value")
  </code></pre>
  
  <p>which results in the non-cumulative variant:</p>
  
  <pre><code>   ID No  a  b  c
  1:  1  1  2 NA NA
  2:  1  2 NA  1 NA
  3:  1  3  3 NA NA
  4:  2  1 NA NA  3
  5:  2  2  2 NA NA
  6:  2  3  1 NA NA
  7:  2  4 NA  5 NA
  </code></pre>
  
  <p>Any ideas how to make this cumulative? The original data.table has over 250,000 rows, so efficiency matters. </p>
  
  <p>EDIT: I just used a,b,c as an example, the original file has about 40 different levels. Furthermore, the <code>NA</code>s are important; there are also <code>Value</code>-values of 0, which means something else than <code>NA</code></p>
  
  <p><strong>POSSIBLE SOLUTION</strong></p>
  
  <p>Okay, so I've found a working solution. It is far from efficient, since it enlarges the original table.</p>
  
  <p>The idea is to duplicate each row <code>TotalNo - No</code> times, where <code>TotalNo</code> is the maximum <code>No</code> per <code>ID</code>. Then the original dcast function can be used to extract the dataframe. So in code:</p>
  
  <pre><code>df[,TotalNo := .N, by=ID]
  df2 &lt;- df[rep(seq(nrow(df)), (df$TotalNo - df$No + 1))] #create duplicates
  df3 &lt;- df2[order(ID, No)]#, No:= seq_len(.N), by=.(ID, No)]
  df3[,No:= seq(from=No[1], to=TotalNo[1], by=1), by=.(ID, No)]
  df4&lt;- dcast(df3, 
              formula = ID + No ~ Variable, 
              value.var = "Value", fill=NA, fun.aggregate = sum)
  </code></pre>
  
  <p>It is not really nice, because the creation of duplicates uses more memory. I think it can be further optimized, but so far it works for my purposes. In the sample code it goes from 7 rows to 16 rows, in the original file from 241,670 rows to a whopping 978,331. That's over a factor 4 larger.</p>
  
  <p><strong>SOLUTION</strong>
  Eddi has improved my solution in computing time in the full dataset (2.08 seconds of Eddi versus 4.36 seconds of mine). Those are numbers I can work with! Thanks everybody!</p>