R // Sum by based on date range

Question

Lets say I have a data frame as follows (only the first 3 columns), in which sum is for example the revenue generated by customer user on day date:

user    date    sum sum(previous5days)
A   2013-01-01  10  0
A   2013-01-02  20  10
A   2013-01-03  10  30
A   2013-01-05  5   40
A   2013-01-06  6   45
A   2013-01-08  7   21
A   2013-01-09  4   22
A   2013-01-10  0   22
B   2013-01-06  1   0
B   2013-01-07  1   1

Now I want to calculate column 4 [sum(previous5days)], which is the aggregated revenue for customer user during the previous 5 days (actual date is not included) on the specific date. This calculation has to be conducted for each row.

How can I do this without using a loop, which is not an option since the data size is rather big.

Many thanks in advance!

Answer 1

using data.table you can levearge keys:

library(data.table)
DT <- data.table(<yourdata>)
setkey(DT, user, date)

DT[, sumSum := DT[ .(.BY[[1]], .d+(-5:-1) )][, sum(sum, na.rm=TRUE)] , by=list(user, .d=date)]
DT
#      user       date sum sum.previous5days. sumSum
#   1:    A 2013-01-01  10                  0      0
#   2:    A 2013-01-02  20                 10     10
#   3:    A 2013-01-03  10                 30     30
#   4:    A 2013-01-05   5                 40     40
#   5:    A 2013-01-06   6                 45     45
#   6:    A 2013-01-08   7                 21     21
#   7:    A 2013-01-09   4                 22     18   <~~~ Discrepency
#   8:    A 2013-01-10   0                 22     22
#   9:    B 2013-01-06   1                  0      0
#  10:    B 2013-01-07   1                  1      1