r - Rolling sum on an unbalanced time series -

r - Rolling sum on an unbalanced time series -

i have series of annual incident counts per category, no rows years in category did not see incident. add together column shows, each year, how many incidents occurred in previous 3 years.

one way handle add together empty rows years 0 incidents, utilize rollapply() left-aligned 4 year window, expand info set more want to. certainly there's way utilize ddply() , transform this?

the next 2 lines of code build dummy info set, execute simple plyr sum category:

dat <- data.frame( category=c(rep('a',6), rep('b',6), rep('c',6)), year=rep(c(2000,2001,2004,2005,2009, 2010),3), incidents=rpois(18, 3) ) ddply(dat, .(category) , transform, i_per_c=sum(incidents) )

that works, shows per-category total.

i want total that's year-dependent.

so seek expand ddply() phone call function() syntax, so:

ddply(dat, .(category) , transform, function(x) i_per_c=sum(ifelse(x$year >= year - 4 & x$year < year, x$incidents, 0) ) )

this returns original info frame, unmodified.

i must missing in plyr syntax, don't know is.

thanks, matt

this sorta ugly, works. nested ply calls:

ddply(dat, .(category), function(datc) adply(datc, 1, function(x) data.frame(run_incidents = sum(subset(datc, year>(x$year-2) & year<=x$year)$incidents))))

there might cleaner way it, , there ways execute much faster.

r time-series plyr