R中的另一个变量的滚动总和 [英] Rolling Sum by Another Variable in R

问题描述

我想通过 ID 获得滚动的 7 天总和.假设我的数据如下所示:

I want to get the rolling 7-day sum by ID. Suppose my data looks like 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

如何添加一个包含按 ID 计算的 7 天滚动总和的新列?

How can I add a new column that will contain the rolling 7-day sum by ID?

推荐答案

如果你的数据很大，你可能想看看这个使用 data.table 的解决方案.它非常快.如果您需要更快的速度，您可以随时将 mapply 更改为 mcmapply 并使用多个内核.

If your data is big, you might want to check out this solution which uses data.table. It is pretty fast. If you need more speed, you can always change mapply to mcmapply and use multiple cores.

#Load data.table and convert to 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 to get last seven days of value by 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中的另一个变量的滚动总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！