问题描述
此代码生成类似于我自己的数据集：
This code generates a dataset similar to my own:
df <- c(seq(as.Date("2012-01-01"), as.Date("2012-01-10"), "days"))
df <- as.data.frame(df)
df <- rbind(df, df)
id <- c(rep.int(1, 10), rep.int(2, 10))
id <- as.data.frame(id)
cnt <- c(1:3, 0, 0, 4, 5:8, 0, 1, 0, 1:7)
cnt <- as.data.frame(cnt)
df <- cbind(id, df, cnt)
names(df) <- c("id", "date", "cnt")
df$date[df$date == "2012-01-10"] <- "2012-01-20"
我试图找到变量' cnt，在过去7天内发生。有时候日期不是连续的（见前面的'df'中的最后一个日期） - 按id。
I'm trying to find the sum of variable 'cnt' that has occurred within the last 7 days. Sometimes dates are not continuous (see the last date in the preceeding 'df') -- by id.
这是循环：
system.time(
for(i in 1:length(df$date)) {
df$cnt.weekly[i] <-
sum(df$cnt[which((df$date == df$date[i] - 1) & df$id == df$id[i])],
df$cnt[which((df$date == df$date[i] - 2) & df$id == df$id[i])],
df$cnt[which((df$date == df$date[i] - 3) & df$id == df$id[i])],
df$cnt[which((df$date == df$date[i] - 4) & df$id == df$id[i])],
df$cnt[which((df$date == df$date[i] - 5) & df$id == df$id[i])],
df$cnt[which((df$date == df$date[i] - 6) & df$id == df$id[i])])})
我最终在一个800万行的data.frame（成千上万的id）运行这个，所以虽然玩具是快速的，这是很慢的实践。
I'm ultimately running this on an 8 million row data.frame (thousands of ids), so while the toy is fast here it is very slow in practice.
我已经在代码的其他部分使用data.table包运行了很好的运气，但我不知道如何让它在这里工作。
I've had very good luck with the data.table package in other parts of the code, but I can't figure out how to get it to work here. Maybe lapply inside of data.table?
推荐答案
如何：
> DT = as.data.table(df)
> DT
id date cnt
[1,] 1 2012-01-01 1
[2,] 1 2012-01-02 2
[3,] 1 2012-01-03 3
[4,] 1 2012-01-04 0
[5,] 1 2012-01-05 0
[6,] 1 2012-01-06 4
[7,] 1 2012-01-07 5
[8,] 1 2012-01-08 6
[9,] 1 2012-01-09 7
[10,] 1 2012-01-20 8
[11,] 2 2012-01-01 0
[12,] 2 2012-01-02 1
[13,] 2 2012-01-03 0
[14,] 2 2012-01-04 1
[15,] 2 2012-01-05 2
[16,] 2 2012-01-06 3
[17,] 2 2012-01-07 4
[18,] 2 2012-01-08 5
[19,] 2 2012-01-09 6
[20,] 2 2012-01-20 7
然后在组内累积。这个步骤目前是丑陋的，但：= 按组（很快就在1.8.1）将整理这个。
Then cumulate within group. This step is currently ugly, but := by group (soon to be in 1.8.1) will tidy this up.
> DT[,cumcnt:=DT[,cumsum(cnt),by=id][[2]]]
id date cnt cumcnt
[1,] 1 2012-01-01 1 1
[2,] 1 2012-01-02 2 3
[3,] 1 2012-01-03 3 6
[4,] 1 2012-01-04 0 6
[5,] 1 2012-01-05 0 6
[6,] 1 2012-01-06 4 10
[7,] 1 2012-01-07 5 15
[8,] 1 2012-01-08 6 21
[9,] 1 2012-01-09 7 28
[10,] 1 2012-01-20 8 36
[11,] 2 2012-01-01 0 0
[12,] 2 2012-01-02 1 1
[13,] 2 2012-01-03 0 1
[14,] 2 2012-01-04 1 2
[15,] 2 2012-01-05 2 4
[16,] 2 2012-01-06 3 7
[17,] 2 2012-01-07 4 11
[18,] 2 2012-01-08 5 16
[19,] 2 2012-01-09 6 22
[20,] 2 2012-01-20 7 29
现在加入7天以前，允许不规律的日期：
Now join to 7 days ago, allowing for irregular dates :
> setkey(DT,id,date)
> DT[,before7dayago:=DT[SJ(id,date-7),cumcnt,roll=TRUE,mult="last"]]
id date cnt cumcnt before7dayago
[1,] 1 2012-01-01 1 1 NA
[2,] 1 2012-01-02 2 3 NA
[3,] 1 2012-01-03 3 6 NA
[4,] 1 2012-01-04 0 6 NA
[5,] 1 2012-01-05 0 6 NA
[6,] 1 2012-01-06 4 10 NA
[7,] 1 2012-01-07 5 15 NA
[8,] 1 2012-01-08 6 21 1
[9,] 1 2012-01-09 7 28 3
[10,] 1 2012-01-20 8 36 28
[11,] 2 2012-01-01 0 0 NA
[12,] 2 2012-01-02 1 1 NA
[13,] 2 2012-01-03 0 1 NA
[14,] 2 2012-01-04 1 2 NA
[15,] 2 2012-01-05 2 4 NA
[16,] 2 2012-01-06 3 7 NA
[17,] 2 2012-01-07 4 11 NA
[18,] 2 2012-01-08 5 16 0
[19,] 2 2012-01-09 6 22 1
[20,] 2 2012-01-20 7 29 22
最后从另一个中减去。
> DT[,`7daysum`:=cumcnt-before7dayago]
id date cnt cumcnt before7dayago 7daysum
[1,] 1 2012-01-01 1 1 NA NA
[2,] 1 2012-01-02 2 3 NA NA
[3,] 1 2012-01-03 3 6 NA NA
[4,] 1 2012-01-04 0 6 NA NA
[5,] 1 2012-01-05 0 6 NA NA
[6,] 1 2012-01-06 4 10 NA NA
[7,] 1 2012-01-07 5 15 NA NA
[8,] 1 2012-01-08 6 21 1 20
[9,] 1 2012-01-09 7 28 3 25
[10,] 1 2012-01-20 8 36 28 8
[11,] 2 2012-01-01 0 0 NA NA
[12,] 2 2012-01-02 1 1 NA NA
[13,] 2 2012-01-03 0 1 NA NA
[14,] 2 2012-01-04 1 2 NA NA
[15,] 2 2012-01-05 2 4 NA NA
[16,] 2 2012-01-06 3 7 NA NA
[17,] 2 2012-01-07 4 11 NA NA
[18,] 2 2012-01-08 5 16 0 16
[19,] 2 2012-01-09 6 22 1 21
[20,] 2 2012-01-20 7 29 22 7
这应该很快。
这篇关于如何加速这个_for_循环？ with data.table + lapply？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！