0

I'm trying to calculate a count of days by group within a rolling five day window using data.table. Sample code and input: 

library(data.table)
library(magrittr)

DT <- structure(list(
    id = c(1, 1, 1, 1, 1, 1, 2, 2, 2, 2), 
    date = structure(c(16740, 16790, 16791, 16792, 16793, 16794, 16815, 16961, 16962, 16963
  ), class = "Date")), 
  row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"), 
  .Names = c("id", "date")) %>% 
  as.data.table()

id       date
 1:  1 2015-11-01
 2:  1 2015-12-21
 3:  1 2015-12-22
 4:  1 2015-12-23
 5:  1 2015-12-24
 6:  1 2015-12-25
 7:  2 2016-01-15
 8:  2 2016-06-09
 9:  2 2016-06-10
10:  2 2016-06-11

But I'm at a loss of how to do so. I know you can do cumulative sum by group using 

DT[, cumsum := cumsum(.N), by = .(id)]

, but how can I make it a rolling sum conditional on date? What I'm looking for is this, where the count is rolling and limited to include records within the last five days:

      id date       count
   <dbl> <date>     <dbl>
 1     1 2015-11-01     1
 2     1 2015-12-21     1
 3     1 2015-12-22     2
 4     1 2015-12-23     3
 5     1 2015-12-24     4
 6     1 2015-12-25     5
 7     2 2016-01-15     1
 8     2 2016-06-09     1
 9     2 2016-06-10     2
10     2 2016-06-11     3
Frank
  • 66,179
  • 8
  • 96
  • 180
T. Shiftlet
  • 158
  • 13
  • CHeck with https://stackoverflow.com/questions/15960352/optimized-rolling-functions-on-irregular-time-series-with-time-based-window – BENY Mar 25 '19 at 14:33
  • Additionally, you can check: https://stackoverflow.com/questions/44314594/sum-of-last-x-days-for-when-day-x – heck1 Mar 25 '19 at 14:35
  • 2
    In particular, you can do `DT[, N := DT[.(d_dn = date - 5, d_up = date), on=.(date >= d_dn, date <= d_up), .N, by=.EACHI]$N]` – Frank Mar 25 '19 at 15:02
  • 1
    Frank's solution works like a charm. Thanks! – T. Shiftlet Mar 25 '19 at 15:55

0 Answers0