This is what my data frame looks like:
library(data.table)
df <- fread('
Name EventType Date SalesAmount RunningTotal Runningtotal(prior365Days)
John Email 1/1/2014 0 0 0
John Sale 2/1/2014 10 10 10
John Sale 7/1/2014 20 30 30
John Sale 4/1/2015 30 60 50
John Webinar 5/1/2015 0 60 50
Tom Email 1/1/2014 0 0 0
Tom Sale 2/1/2014 15 15 15
Tom Sale 7/1/2014 10 25 25
Tom Sale 4/1/2015 25 50 35
Tom Webinar 5/1/2015 0 50 35
')
df[,Date:= as.Date(Date, format="%m/%d/%Y")]
The last column was my desired column which is the cumulative sum of SalesAmount(for each Name) in the last 365 days rolling window and I performed this with the help of @6pool. His solution was:
df$EventDate <- as.Date(df$EventDate, format="%d/%m/%Y")
df <- df %>%
group_by (Name) %>%
arrange(EventDate) %>%
mutate(day = EventDate - EventDate[1])
f <- Vectorize(function(i)
sum(df[df$Name[i] == df$Name & df$day[i] - df$day >= 0 &
df$day[i] - df$day <= 365, "SalesAmount"]), vec="i")
df$RunningTotal365 <- f(1:nrow(df))
However,df$RunningTotal365 <- f(1:nrow(df)) is taking a long time(over 1.5 days so far) as my dataframe is over 1.5 million rows. I was suggested "rollapply" in my initial question but I have struggled to figure out how to use it in this instance. Kindly help.