CODEWITHSUNDEEP
rdata.table
Ricky
Ricky

Reputation: 2750

How to roll sum by conditon?

I have a data table as below:

I have to do a rolling forward sum and i do it by the below method

id <- c(rep(1,9))
date <- c('2018-01-01 15:00:33', '2018-01-01 15:05:31', '2018-01-01 15:10:12','2018-01-01 15:15:33', '2018-01-01 15:20:48', '2018-01-01 15:25:41','2018-01-01 15:30:27', '2018-01-01 15:35:18', '2018-01-01 16:40:08')
value=c(12,36,30,15,18,39,21,24,27)
dt=data.table(id,date,value)
dt[,rolled_sum_value:=Reduce(`+`,shift(value,0:5,type="lead")),by="id"]

Now I have a constraint that the rolling window should be 30 mins so in that case at fourth row instead of 144 I need it to be 117 (15+18+39+21+24) instead of 144 since the ninth row is greater than 30 min window 2018-01-01 16:40:08.

Any help is appreciated.

Upvotes: 3

Views: 70

Answers (1)

Frank
Frank

Reputation: 66819

You could do a non-equi join:

# use a datetime class so time differences make sense
dt[, date := as.POSIXct(date)]

dt[, v := 
  dt[.(id = id, d_dn = date, d_up = date + 30*60), on=.(id, date >= d_dn, date <= d_up), 
    sum(value), by=.EACHI]$V1
]

   id                date value   v
1:  1 2018-01-01 15:00:33    12 171
2:  1 2018-01-01 15:05:31    36 183
3:  1 2018-01-01 15:10:12    30 147
4:  1 2018-01-01 15:15:33    15 117
5:  1 2018-01-01 15:20:48    18 102
6:  1 2018-01-01 15:25:41    39  84
7:  1 2018-01-01 15:30:27    21  45
8:  1 2018-01-01 15:35:18    24  24
9:  1 2018-01-01 16:40:08    27  27

Upvotes: 1

Related Questions