Generating a moving sum variable in R

data-manipulation, r

Solution

You can use `filter` in `ddply` (or any other function implementing the "split-apply-combine" approach):

library(plyr)
ddply(DF, .(country), transform, 
          x5yrsum2 = as.numeric(filter(x,c(0,rep(1,5)),sides=1)))

#    country year x x5yrsum x5yrsum2
# 1        A 1980 9      NA       NA
# 2        A 1981 3      NA       NA
# 3        A 1982 5      NA       NA
# 4        A 1983 6      NA       NA
# 5        A 1984 9      NA       NA
# 6        A 1985 7      32       32
# 7        A 1986 9      30       30
# 8        A 1987 4      36       36
# 9        B 1990 0      NA       NA
# 10       B 1991 4      NA       NA
# 11       B 1992 2      NA       NA
# 12       B 1993 6      NA       NA
# 13       B 1994 3      NA       NA
# 14       B 1995 7      15       15
# 15       B 1996 0      22       22

Problem

I suspect this is a somewhat simple question with multiple solutions, but I'm still a bit of a novice in R and an exhaustive search didn't yield answers that spoke well to what I'm wanting to do. I'm trying to create, for lack of better term, "moving sums" for a variable in my data frame. These would be 3-year and 5-year sums, lagged one year. So, a 5-year sum for an observation in 1986 would be the sum of all previous observations in 1981, 1982, 1983, 1984, and 1985. Here is an example of what I would like to do, where the sum variable is the sum of all `x` in the five years prior to the observation year. ``` country year x x5yrsum A 1980 9 NA A 1981 3 NA A 1982 5 NA A 1983 6 NA A 1984 9 NA A 1985 7 32 A 1986 9 30 A 1987 4 36 ..................... B 1990 0 NA B 1991 4 NA B 1992 2 NA B 1993 6 NA B 1994 3 NA B 1995 7 15 B 1996 0 22 ``` This is unbalanced panel data. I suspect `ddply` would be appropriate, but I wouldn't know the exact coding for it. Any input would be appreciated.

Original source