Cumulated sum by variable values

Question

I have the following data and I'd like to accumulate the values of 'SubstResp' by 'Soil' and 'Added'. Finally I'd like to get the new variable 'SubstRespAcc'.

Input data

Stock   Soil    Added   Acctime SubstResp
A   Clayey  70.2    1   0.21300216
A   Sandy   70.2    1   4.93108145
A   Clayey  701.8   1   7.21646586
A   Sandy   701.8   1   0.63856242
A   Clayey  7017.8  1   4.78288286
A   Sandy   7017.8  1   2.67393943
B   Clayey  70.2    8   1.32209361
B   Sandy   70.2    8   4.48684094
B   Clayey  701.8   8   70.97613913
B   Sandy   701.8   8   1.68302792
B   Clayey  7017.8  8   8.8713402
B   Sandy   7017.8  8   89.58014609
C   Clayey  70.2    23  0.09295716
C   Sandy   70.2    23  3.04399183
C   Clayey  701.8   23  50.01275949
C   Sandy   701.8   23  0.71204003
C   Clayey  7017.8  23  3.23769206
C   Sandy   7017.8  23  38.18923005

Desired output

Stock   Soil    Added   Acctime SubstResp   SubstRespAcc
A   Clayey  70.2    1   0.21300216  0.21300216
A   Sandy   70.2    1   4.93108145  4.93108145
A   Clayey  701.8   1   7.21646586  7.21646586
A   Sandy   701.8   1   0.63856242  0.63856242
A   Clayey  7017.8  1   4.78288286  4.78288286
A   Sandy   7017.8  1   2.67393943  2.67393943
B   Clayey  70.2    8   1.32209361  1.53509577
B   Sandy   70.2    8   4.48684094  9.41792239
B   Clayey  701.8   8   70.97613913 78.19260499
B   Sandy   701.8   8   1.68302792  2.32159034
B   Clayey  7017.8  8   8.8713402   13.65422306
B   Sandy   7017.8  8   89.58014609 92.25408552
C   Clayey  70.2    23  0.09295716  1.62805293
C   Sandy   70.2    23  3.04399183  12.46191422
C   Clayey  701.8   23  50.01275949 128.2053645
C   Sandy   701.8   23  0.71204003  3.03363037
C   Clayey  7017.8  23  3.23769206  16.89191512
C   Sandy   7017.8  23  38.18923005 130.4433156

Thanks a lot for your help in advance!!

Answer 1

You can use the ddply function from the plyr package:

library(plyr)
ddply(dat, .(Soil, Added), mutate, SubstRespAcc = cumsum(SubstResp))

---

You can also use data.table:

library(data.table)
datDT <- as.data.table(dat)
datDT[ , SubstRespAcc := cumsum(SubstResp), by = c("Soil", "Added")]

---

If you want a solution in base R, you can use ave:

transform(dat, SubstRespAcc = ave(SubstResp, Soil, Added, FUN = cumsum))

Answer 2

zz <- "Stock   Soil    Added   Acctime SubstResp
A   Clayey  70.2    1   0.21300216
A   Sandy   70.2    1   4.93108145
A   Clayey  701.8   1   7.21646586
A   Sandy   701.8   1   0.63856242
A   Clayey  7017.8  1   4.78288286
A   Sandy   7017.8  1   2.67393943
B   Clayey  70.2    8   1.32209361
B   Sandy   70.2    8   4.48684094
B   Clayey  701.8   8   70.97613913
B   Sandy   701.8   8   1.68302792
B   Clayey  7017.8  8   8.8713402
B   Sandy   7017.8  8   89.58014609
C   Clayey  70.2    23  0.09295716
C   Sandy   70.2    23  3.04399183
C   Clayey  701.8   23  50.01275949
C   Sandy   701.8   23  0.71204003
C   Clayey  7017.8  23  3.23769206
C   Sandy   7017.8  23  38.18923005"
Data <- read.table(text=zz, header = TRUE)  
library(reshape)
mydata <- aggregate(Data$SubstResp, list(Data$Soil,Data$Added), cumsum)
melt(mydata, id=(c("Group.1", "Group.2")))
   Group.1 Group.2 variable       value
1   Clayey    70.2        x   0.2130022
2    Sandy    70.2        x   4.9310814
3   Clayey   701.8        x   7.2164659
4    Sandy   701.8        x   0.6385624
5   Clayey  7017.8        x   4.7828829
6    Sandy  7017.8        x   2.6739394
7   Clayey    70.2        x   1.5350958
8    Sandy    70.2        x   9.4179224
9   Clayey   701.8        x  78.1926050
10   Sandy   701.8        x   2.3215903
11  Clayey  7017.8        x  13.6542231
12   Sandy  7017.8        x  92.2540855
13  Clayey    70.2        x   1.6280529
14   Sandy    70.2        x  12.4619142
15  Clayey   701.8        x 128.2053645
16   Sandy   701.8        x   3.0336304
17  Clayey  7017.8        x  16.8919151
18   Sandy  7017.8        x 130.4433156