1

I have some IDs, dates and Values. I want to sum values for each ID for the last 10 days.

This code can reproduce my dataset:

date<-as.Date(c("05/03/2017","05/10/2017","05/14/2017","05/19/2017","05/22/2017"),format="%m/%d/%Y")
value<-rpois(20,50)
id<-NULL
for(i in 1:4){id<-c(id,rep(i,5))}

mydata<-data.frame(id,date=rep(date,4),value)

So, the output would be the dataset above with a new column. This column would have the sum of values of lasts 10 days.

Thanks in advance.

Luis Otavio Fernandes
  • 161
  • 12
  • How are the last 10 days defined? Starting at the earliest date in the data? – erocoar Feb 26 '18 at 16:24
  • 2
    Fwiw, there's `library(data.table); setDT(mydata); mydata[, v := .SD[.(id = id, d_dn = date - 10, d_up = date), on=.(id, date >= d_dn, date < d_up), sum(value), by=.EACHI]$V1]` I guess there is a dupe of this post somewhere.. – Frank Feb 26 '18 at 16:26
  • It depends on each "date" value in the row. Example: for the 3rd row, date=2017-05-14, so 10 days would be from 2017-05-05 until 2017-05-14. – Luis Otavio Fernandes Feb 26 '18 at 16:28
  • So your sum will differ within each group? Sounds like @Frank's solution should work perfectly for this – Mike H. Feb 26 '18 at 16:29
  • Yes, each row has one different sum, because depends on date and ids. @Frank solution fits perfect! – Luis Otavio Fernandes Feb 26 '18 at 16:33

0 Answers0