How do I speed up this _for_ loop? With data.table + lapply?

Question

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"

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.

Here's the loop:

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])])})

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.

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?

Thanks in advance!

Try `rollapply`? Also, store your `df$id==df$id[i]` comparison so it doesn't get recalculated each time. Also, take advantage of the fact that if `i-6` is within a week, then `i-5`, `i-4` etc. are also. See also: http://stackoverflow.com/questions/2908822/speed-up-the-loop-operation-in-r/8474941#8474941 — Ari B. Friedman, May 23 '12 at 13:50

score 5 · Accepted Answer · answered May 23 '12 at 15:39

How about :

> 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

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

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

And finally subtract one from the other.

> 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

That should be very fast.

Bravo! Thank you, this works amazing. Looks like I need to dig into data.table deeper. I wasn't aware of the 'by' function, though I just started working with data.table. — Statwonk, May 23 '12 at 16:51

How do I speed up this _for_ loop? With data.table + lapply?

1 Answers1

Linked