Effective way to complete the total in the last window in 365 days

Question

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 sum of SalesAmount (for each name) over the last 365 days, and I accomplished this with @ 6pool. His decision 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)) takes a lot of time (more than 1.5 days), since my data frame is more than 1.5 million rows. I was offered "rollapply" in my original question, but I struggled to figure out how to use it in this case. Please help.

Answer 1

Try:

 DF <- read.table(text = "Name EventType EventDate 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", header = TRUE) fun <- function(x, date, thresh) { D <- as.matrix(dist(date)) #distance matrix between dates D <- D <= thresh D[lower.tri(D)] <- FALSE #don't sum to future R <- D * x #FALSE is treated as 0 colSums(R) } library(data.table) setDT(DF) DF[, EventDate := as.Date(EventDate, format = "%m/%d/%Y")] setkey(DF, Name, EventDate) DF[, RT365 := fun(SalesAmount, EventDate, 365), by = Name] # Name EventType EventDate SalesAmount RunningTotal Runningtotal.prior365Days. RT365 # 1: John Email 2014-01-01 0 0 0 0 # 2: John Sale 2014-02-01 10 10 10 10 # 3: John Sale 2014-07-01 20 30 30 30 # 4: John Sale 2015-04-01 30 60 50 50 # 5: John Webinar 2015-05-01 0 60 50 50 # 6: Tom Email 2014-01-01 0 0 0 0 # 7: Tom Sale 2014-02-01 15 15 15 15 # 8: Tom Sale 2014-07-01 10 25 25 25 # 9: Tom Sale 2015-04-01 25 50 35 35 #10: Tom Webinar 2015-05-01 0 50 35 35 

Answer 2

Here we use the approach using the foverlaps function from the data.table package:

 require(data.table) setDT(df)[, end := as.Date(EventDate, format="%d/%m/%Y") ][, start := end - 365L] setkey(df, Name, start, end) olaps = foverlaps(df, df, nomatch=0L, which=TRUE) olaps = olaps[xid >= yid, .(ans = sum(dt$SalesAmount[yid])), by=xid] df[olaps$xid, Runningtotal := olaps$ans] 

You can remove the start and end columns if necessary by doing:

 df[, c("start", "end") := NULL] 

It would be nice to know how fast / slower.

Answer 3

Using the newer join function without binding in data.table:

  df1 = df[.(iName=Name,start = Date - 365L, end = Date), on=.(Name=iName,Date >= start, Date <= end),nomatch = 0, allow.cart=TRUE][, .(MyTotal = sum(SalesAmount)), by=.(Name,Date = Date.1)] df[df1, on = .(Name,Date)]