如何在data.table中创建基于每周日期的移动平均值，并按多列分组？

Question

我正在读入一个非常大的数据集，作为速度的data.table。相关列有DATE (以年-月-日字符串表示的每周数据，例如"2017-12-25")、V1 (整数)、V2 (字符串)、V3 (数字)。我想生成V4，这是过去3周V3的移动平均值(DATE，DATE-7和DATE-14)这里是一个天真的尝试/解决方案，效率非常低：

dt <- fread("largefile.csv")

dt$DATE <- as.IDate(dt$DATE) //convert dates to date format

V1_list <- sort(unique(dt$V1))

V2_list <- sort(unique(dt$V2))

DATE_list <- sort(unique(dt$DATE))

for(i in 1:length(V1_list)){
for(j in 1:length(V2_list)){
for(k in 3:length(DATE_list){
dt[which(dt$V1 == V1_list[i] && dt$V2 == V2_list[j] && dt$DATE == DATE_list[k]),"V4"] 
<- mean(dt[which(dt$V1 == V1_list[i] && dt$V2 == V2_list[j] && dt$DATE %in% DATE_list[k-2:k]),"V3"])
}
}
}

我避免使用plyr，部分原因是考虑到我正在使用的5000万行的计算限制。我研究了setkey()和zoo / rolling函数的选项，但我无法弄清楚如何在date组件中分层(假设我按V1、V2和average V3分组)。很抱歉没有提供示例代码。

Answer 1

OP请求追加一个新列，该列是过去3周内V3的滚动平均值，按V1和V2分组，得到50M行的data.table。

如果 DATE 值没有gap，即所有组中都没有丢失周，一种可能的方法是使用zoo程序包中的rollmeanr()函数：

DT[order(DATE), V4 := zoo::rollmeanr(V3, 3L, fill = NA), by = .(V1, V2)]
DT[order(V1, V2, DATE)]

DATE V1 V2 V3 V4 1: 2017-12-04 1 A 1 NA 2: 2017-12-11 1 A 2 NA 3: 2017-12-18 1 A 3 2 4: 2017-12-25 1 A 4 3 5: 2017-12-04 1 B 5 NA 6: 2017-12-11 1 B 6 NA 7: 2017-12-18 1 B 7 6 8: 2017-12-25 1 B 8 7 9: 2017-12-04 2 A 9 NA 10: 2017-12-11 2 A 10 NA 11: 2017-12-18 2 A 11 10 12: 2017-12-25 2 A 12 11 13: 2017-12-04 2 B 13 NA 14: 2017-12-11 2 B 14 NA 15: 2017-12-18 2 B 15 14 16: 2017-12-25 2 B 16 15

请注意，我们特意引入了NA，因为我们没有每个组中前两行的DATE__-7和DATE__-14值。

还要注意，这种方法不需要对字符日期进行类型转换。

数据

根据OP的描述，data.table有4列：DATE是标准明确格式%Y-%m-%d的每周字符日期，V1是integer类型，V2是character类型，V3是double (数字)类型。使用V1和V2进行分组。

library(data.table)
# create data
n_week = 4L
n_V1 = 2L
# cross join
DT <- CJ(
  DATE = as.character(rev(seq(as.Date("2017-12-25"), length.out = n_week, by = "-1 week"))),
  V1 = seq_len(n_V1),
  V2 = LETTERS[1:2]
)
DT[order(V1, V2, DATE), V3 := as.numeric(seq_len(.N))][]

DATE V1 V2 V3 1: 2017-12-04 1 A 1 2: 2017-12-04 1 B 5 3: 2017-12-04 2 A 9 4: 2017-12-04 2 B 13 5: 2017-12-11 1 A 2 6: 2017-12-11 1 B 6 7: 2017-12-11 2 A 10 8: 2017-12-11 2 B 14 9: 2017-12-18 1 A 3 10: 2017-12-18 1 B 7 11: 2017-12-18 2 A 11 12: 2017-12-18 2 B 15 13: 2017-12-25 1 A 4 14: 2017-12-25 1 B 8 15: 2017-12-25 2 A 12 16: 2017-12-25 2 B 16

Answer 2

所以我试着用dplyr包中的两个inner_joins来解决你的问题：

首先，我创建了一个示例data.frame (1.000.000行)：

V3 <- seq(from=1, to=1000000, by =1 )
DATE <- seq(from=1, to= 7000000, by =7)
dt <- data.frame(V3, DATE)

它看起来正确吗？我删除了所有不必要的内容，并忽略了日期格式(您可以用与整数相同的方法减去日期)。

接下来，我在DATE列上执行了两个内部连接，但第二个data.frame包含DATE +7和DATE +14，因此您可以在正确的日期进行连接。最后，我选择了3个有趣的列并计算了rowMean。我在我那糟糕的MacBook上花了大概5秒钟。

inner_join(
    inner_join(x= dt, y=mutate(dt, DATE=DATE+7), by= 'DATE'),
    y = mutate(dt, DATE= DATE+14), by= 'DATE')  %>% 
    select(V3 , V3.y, V3.x) %>% 
    rowMeans()

如果您想将其添加到dt中，请记住前两个日期没有平均值，因为不存在日期-14和日期-7。

dt$V4 <-   c(NA, NA, inner_join(
        inner_join(x= dt, y=mutate(dt, DATE=DATE+7), by= 'DATE'),
        y = mutate(dt, DATE= DATE+14), by= 'DATE')  %>% 
        select(V3 , V3.y, V3.x) %>% 
        rowMeans())