Generating a moving sum variable in R
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
If DF
is the input three-column data frame then use ave
with rollapplyr
from zoo. Note that we use a width of k+1
and then drop the k+1st element from the sum so that the current value of x
is excluded and only the remaining k
values are summed:
library(zoo)
k <- 5
roll <- function(x) rollapplyr(x, k+1, function(x) sum(x[-k-1]), fill = NA)
transform(DF, xSyrsum = ave(x, country, FUN = roll))
which gives:
country year x xSyrsum
1 A 1980 9 NA
2 A 1981 3 NA
3 A 1982 5 NA
4 A 1983 6 NA
5 A 1984 9 NA
6 A 1985 7 32
7 A 1986 9 30
8 A 1987 4 36
9 B 1990 0 NA
10 B 1991 4 NA
11 B 1992 2 NA
12 B 1993 6 NA
13 B 1994 3 NA
14 B 1995 7 15
15 B 1996 0 22