data.table - Rolling Sum by Another Variable in R -

data.table - Rolling Sum by Another Variable in R -

i want rolling 7-day sum id. suppose info looks this:

data<-as.data.frame(matrix(na,42,3)) data$v1<-seq(as.date("2014-05-01"),as.date("2014-09-01"),by=3) data$v2<-rep(1:6,7) data$v3<-rep(c(1,2),21) colnames(data)<-c("date","usd","id") date usd id 1 2014-05-01 1 1 2 2014-05-04 2 2 3 2014-05-07 3 1 4 2014-05-10 4 2 5 2014-05-13 5 1 6 2014-05-16 6 2 7 2014-05-19 1 1 8 2014-05-22 2 2 9 2014-05-25 3 1 10 2014-05-28 4 2

how can add together new column contain rolling 7-day sum id?

if info big, might want check out solution uses data.table. pretty fast. if need more speed, can alter mapply mcmapply , utilize multiple cores.

#load data.table , convert data.table object require(data.table) setdt(data)[,id2:=.grp,by=c("id")] #build reference table ref <- data[,list(compare_value=list(i(usd)),compare_date=list(i(date))), by=c("id2")] #use mapply lastly 7 days of value id data[,roll.val := mapply(rd = date,num=id2, function(rd, num) { d <- as.numeric(ref$compare_date[[num]] - rd) sum((d <= 0 & d >= -7)*ref$compare_value[[num]])})]

r data.table xts