-1

Using dcast from the reshape2 library in R I aggregate data (sum)

library('reshape2')

DF <- data.frame(Cohort=rep("", 9), Weeks=rep("", 9), myvalue=rep(0, 9), stringsAsFactors=FALSE )
DF[1, ] <- c("2012_30","0",0.02)
DF[2, ] <- c("2012_30","1",0.01)
DF[3, ] <- c("2012_30","2",0.1)
DF[4, ] <- c("2012_31","0",0.08)
DF[5, ] <- c("2012_31","1",0.0)
DF[6, ] <- c("2012_31","2",0.3)
DF[7, ] <- c("2012_32","0",0.26)
DF[8, ] <- c("2012_32","1",0.01)
DF[9, ] <- c("2012_32","2",0.01)

dcast(DF, Cohort ~ Weeks, value = myvalue, fill='')

This produces

Cohort   0    1    2      
2012_30  0.02 0.01 0.10
2012_31  0.08 0.00 0.30
2012_32  0.26 0.01 0.01
...

I would like to be able to get the accumulated value like this:

Cohort   0    1    2      
2012_30  0.02 0.03 0.13
2012_31  0.08 0.00 0.38
2012_32  0.26 0.27 0.28
...

I tried to find examples for the fun.aggregate in the dcast documentation from reshape2 and the original reshape paper

Cilvic
  • 3,417
  • 2
  • 33
  • 57

1 Answers1

2

Try ddply as interim step:

DF$myvalue <- as.numeric( DF$myvalue )
library( plyr )
DF <- ddply( DF, .(Cohort), transform, CUMSUM = cumsum( myvalue ) )
DF <- dcast( DF, Cohort ~ Weeks, value = CUMSUM, fill='' )
DF
   Cohort    0    1    2
1 2012_30 0.02 0.03 0.13
2 2012_31 0.08 0.08 0.38
3 2012_32 0.26 0.27 0.28
vaettchen
  • 7,299
  • 22
  • 41
  • Thx, would you know whether the order of the data is important for cumsum to return the correct numbers? – Cilvic Nov 17 '12 at 13:52
  • Not sure whether I understand your question but in general, the order of the data should not matter - `plyr` takes care of that, I would guess. Could you be more specific? – vaettchen Nov 17 '12 at 14:17
  • by the way, using `dput( head( DF, 20 ) )` or similar will give us the data structure you really are using... – vaettchen Nov 17 '12 at 14:20
  • when using ddply to calculate cumsum we don't specify which values should be accumulated. Same question as below suppose I have month in the DF how das ddply now whether to cumsum over weeks or months? – Cilvic Nov 17 '12 at 14:39
  • Real data please! We need to know the actual structure of your data! – vaettchen Nov 17 '12 at 14:56