R - the total amount under the condition

Question

So, I have a dataset that simplifies something like this:

Year ID Sum 2009 999 100 2009 123 85 2009 666 100 2009 999 100 2009 123 90 2009 666 85 2010 999 100 2010 123 100 2010 666 95 2010 999 75 2010 123 100 2010 666 85 

I would like to add a column with the total amount by year and ID. Like this:

 Year ID Sum Cum.Sum 2009 999 100 100 2009 123 85 85 2009 666 100 100 2009 999 100 200 2009 123 90 175 2009 666 85 185 2010 999 100 100 2010 123 100 100 2010 666 95 95 2010 999 75 175 2010 123 100 200 2010 666 85 180 

I think it should be fairly straightforward, but somehow I could not figure it out. How can I do it? Thanks for the help!

Answer 1

Using data.table :

 require(data.table) DT <- data.table(DF) DT[, Cum.Sum := cumsum(Sum), by=list(Year, ID)] Year ID Sum Cum.Sum 1: 2009 999 100 100 2: 2009 123 85 85 3: 2009 666 100 100 4: 2009 999 100 200 5: 2009 123 90 175 6: 2009 666 85 185 7: 2010 999 100 100 8: 2010 123 100 100 9: 2010 666 95 95 10: 2010 999 75 175 11: 2010 123 100 200 12: 2010 666 85 180 

Answer 2

Another way

1) use ddply to sum the variable by group (similar to SQL group)

 X <- ddply ( dataset, .(Year,ID), sum) 

2) combine the result with the data set

 Y <- merge( dataset, X, by=('Year','ID')