Apply a rolling sum by group in R

Question

I need to calculate a rolling sum by group.

y<- 1:10
tmp<-data.frame(y)
tmp$roll<-NA
tmp$roll[2:10]<-rollapply (y, 2, sum)
tmp$g<-(c("a","a","a","a","a","b","b","b","b","b"))

tmp$roll calculates the rolling sum for tmp$y; I need to do this by tmp$g. I think I may need to split the data frame into a list of data frames by group and then bind back together but this seems like a long route. The result would be an additional column of the rolling sum by group a and b (this a simplified example of actual data frame):

roll_group
NA 
3
5
7
9
NA
13
15
17
19

See http://stackoverflow.com/questions/35249019/grouped-moving-average-in-r/35284632#35284632 — G. Grothendieck, May 08 '16 at 21:24

score 2 · Answer 1 · answered May 08 '16 at 21:25

2

Here is the data.table way:

library(data.table)
tmp.dt <- data.table(tmp)
tmp.dt <- tmp.dt[, .(y =y, roll = cumsum(y)), by = g]

You can do it with dplyr package as well.

answered May 08 '16 at 21:25

Bulat

6,869
1
29
52

Thanks, but I am looking for the rolling sum not the cumsum. Also I am using the dplyr command. This is my code, but the function doesn't apply on groups: – Sassafras May 11 '16 at 13:39
what is the difference between cumsum and rolling sum? – Bulat May 11 '16 at 15:19

score 2 · Accepted Answer · answered May 11 '16 at 13:55

Thanks but the answers provided in this post use the cumsum whereas I need the rolled sum with NA's if there aren't enough lagged values. I solved it this way:

#function to calculate rolled sum, returns a column vector
roll<-function(x,lags){
if (length(x)<lags) {
tmp=c(rep(NA,length(x)))  
}
else {
tmp=rollsum(x, lags, align = "right", fill = NA)
}
tmp=as.numeric(tmp)
return(tmp)
}

tmp1 <- tmp %>% 
group_by(g) %>%
mutate(roll_group = ave(y, g, FUN = function(x) roll(x, 2)))%>%
ungroup

score 0 · Answer 3 · answered May 08 '16 at 21:23

0

How about wrapping it in tapply (or lapply split):

tapply(y, tmp$g, cumsum)

answered May 08 '16 at 21:23

Raad

2,675
1
13
26

score 0 · Answer 4 · answered May 08 '16 at 21:57

0

Consider this base solution with sapply() combining running count and running sum:

tmp$roll <- sapply(1:nrow(tmp),
                   function(i)
                        sum((tmp[1:i, c("g")] == tmp$g[i]) * tmp[1:i,]$y)
                   )

answered May 08 '16 at 21:57

Parfait

104,375
17
94
125

Apply a rolling sum by group in R

4 Answers4

Linked