Calculate the number of occurrences of a specific event in the past AND future with groupings

Question

this question is a modification of a problem I posted here where I have occurrences of a specific type on different days, but this time they are assigned to multiple users, for example:

df = data.frame(user_id = c(rep(1:2, each=5)),
            cancelled_order = c(rep(c(0,1,1,0,0), 2)),
            order_date = as.Date(c('2015-01-28', '2015-01-31', '2015-02-08', '2015-02-23',  '2015-03-23',
                                   '2015-01-25', '2015-01-28', '2015-02-06', '2015-02-21',  '2015-03-26')))


user_id cancelled_order order_date
      1               0 2015-01-28
      1               1 2015-01-31
      1               1 2015-02-08
      1               0 2015-02-23
      1               0 2015-03-23
      2               0 2015-01-25
      2               1 2015-01-28
      2               1 2015-02-06
      2               0 2015-02-21
      2               0 2015-03-26

I'd like to calculate

1) the number of cancelled orders that each customer is going to have in the next x days (e.g. 7, 14), excluding the current one and

1) the number of cancelled orders that each customer had in the past x days (e.g. 7, 14) , excluding the current one.

The desired output would look like this:

solution
user_id cancelled_order order_date plus14 minus14
      1               0 2015-01-28      2       0
      1               1 2015-01-31      1       0
      1               1 2015-02-08      0       1
      1               0 2015-02-23      0       0
      1               0 2015-03-23      0       0
      2               0 2015-01-25      2       0
      2               1 2015-01-28      1       0
      2               1 2015-02-06      0       1
      2               0 2015-02-21      0       0
      2               0 2015-03-26      0       0

The solution that is perfectly fit for this purpose was presented by @joel.wilson using data.table

library(data.table)
vec <- c(14, 30) # Specify desired ranges
setDT(df)[, paste0("x", vec) := 
        lapply(vec, function(i) sum(df$cancelled_order[between(df$order_date, 
                                                 order_date, 
                                                 order_date + i, # this part can be changed to reflect the past date ranges
                                                 incbounds = FALSE)])),
        by = order_date]

However, it does not take into account grouping by user_id. When I tried to modify the formula by adding this grouping as by = c("user_id", "order_date") or by = list(user_id, order_date), it did not work. It seems it is something very basic, any hints on how to get around this detail?

Also, keep in mind that I'm after a solution that works, even if it is not based on the above code or data.table at all!

Thanks!

hi, @Frank, thanks, now I added desired output, hope this helps — Kasia Kulma, Jan 12 '17 at 15:20

score 3 · Accepted Answer · answered Jan 12 '17 at 16:24

Here's one way:

library(data.table)
orderDT = with(df, data.table(id = user_id, completed = !cancelled_order, d = order_date))

vec = list(minus = 14L, plus = 14L)
orderDT[, c("dplus", "dminus") := .(
    orderDT[!(completed)][orderDT[, .(id, d_plus = d + vec$plus, d_tom = d + 1L)], on=.(id, d <= d_plus, d >= d_tom), .N, by=.EACHI]$N
    ,
    orderDT[!(completed)][orderDT[, .(id, d_minus = d - vec$minus, d_yest = d - 1L)], on=.(id, d >= d_minus, d <= d_yest), .N, by=.EACHI]$N
)]


    id completed          d dplus dminus
 1:  1      TRUE 2015-01-28     2      0
 2:  1     FALSE 2015-01-31     1      0
 3:  1     FALSE 2015-02-08     0      1
 4:  1      TRUE 2015-02-23     0      0
 5:  1      TRUE 2015-03-23     0      0
 6:  2      TRUE 2015-01-25     2      0
 7:  2     FALSE 2015-01-28     1      0
 8:  2     FALSE 2015-02-06     0      1
 9:  2      TRUE 2015-02-21     0      0
10:  2      TRUE 2015-03-26     0      0

(I found OP's column names cumbersome and so shortened them.)

How it works

Each of the columns can be run on its own, like

orderDT[!(completed)][orderDT[, .(id, d_plus = d + vec$plus, d_tom = d + 1L)], on=.(id, d <= d_plus, d >= d_tom), .N, by=.EACHI]$N

And this can be broken down into steps by simplifying:

orderDT[!(completed)][
  orderDT[, .(id, d_plus = d + vec$plus, d_tom = d + 1L)], 
  on=.(id, d <= d_plus, d >= d_tom), 
  .N, 
  by=.EACHI]$N
# original version

orderDT[!(completed)][
  orderDT[, .(id, d_plus = d + vec$plus, d_tom = d + 1L)], 
  on=.(id, d <= d_plus, d >= d_tom), 
  .N, 
  by=.EACHI] 
# don't extract the N column of counts

orderDT[!(completed)][
  orderDT[, .(id, d_plus = d + vec$plus, d_tom = d + 1L)], 
  on=.(id, d <= d_plus, d >= d_tom)]
# don't create the N column of counts

orderDT[!(completed)]
# don't do the join

orderDT[, .(id, d_plus = d + vec$plus, d_tom = d + 1L)]
# see the second table used in the join

This uses a "non-equi" join, taking inequalities to define the date ranges. For more details, see the documentation page found by typing ?data.table.

Hi @Frank, thanks for your answer! I tried to reproduce your solution without changing variables names and without defining `completed = !cancelled` for simplicity. Also, I explicitly defined `d_plus = order_date + 14L`. The result does not calculate future orders at all and makes mistakes calculating past orders. Where could I post the code I've used for you to verify it? Thanks again! — Kasia Kulma, Jan 12 '17 at 18:06
Might as well use a chat room: http://chat.stackoverflow.com/rooms/133025/room-for-frank-and-kasia-kulma Or a github gist if you prefer @Kasia — Frank, Jan 12 '17 at 18:08

score 1 · Answer 2 · answered Jan 12 '17 at 15:57

I might have made this solution a bit complex:

library(dplyr)
library(tidyr)

vec <- c(7,14)

reslist <- lapply(vec, function(x){
df %>% merge(df %>% rename(cancelled_order2 = cancelled_order, order_date2 = order_date)) %>% 
  filter(abs(order_date-order_date2)<=x) %>%
  group_by(user_id, order_date) %>% arrange(order_date2) %>% mutate(cumcancel = cumsum(cancelled_order2)) %>%
  mutate(before = cumcancel - cancelled_order2,
         after = max(cumcancel) - cumcancel) %>%
  filter(order_date == order_date2) %>% 
    select(user_id, cancelled_order, order_date, before, after) %>% 
    mutate(within = x)})

do.call(rbind, reslist) %>% gather(key, value, -user_id, -cancelled_order, -order_date, -within) %>%
  mutate(col = paste0(key,"_",within)) %>% select(-within, - key) %>% spread(col, value) %>% arrange(user_id, order_date)

PS: I did spot a mistake in your output example (user_id 1, order_date 2015-02-23 ,minus14 should be 0, since there are 15 days between 02/08 and 02/23)

thank you, @Wietze314, for spotting the typo, I've corrected it now. Let me test your solution on a bigger dataset... — Kasia Kulma, Jan 12 '17 at 16:02

score 1 · Answer 3 · answered Oct 27 '19 at 12:54

I recommend to use runner package. There is a function runner which executes any R function within sliding window.

To obtain sum from current 7-days window and 14-days window excluding current element one can use sum(x[length(x)]) for each window.

library(runner)
df %>%
  group_by(user_id) %>%
  mutate(
    minus_7 = runner(cancelled_order, k = 7, idx = order_date, 
                     f = function(x) sum(x[length(x)])),
    minus_14 = runner(cancelled_order, k = 14, idx = order_date, 
                      f = function(x) sum(x[length(x)])))


# A tibble: 10 x 5
# Groups:   user_id [2]
   user_id cancelled_order order_date minus_7 minus_14
     <int>           <dbl> <date>       <dbl>    <dbl>
 1       1               0 2015-01-28       0        0
 2       1               1 2015-01-31       1        1
 3       1               1 2015-02-08       1        1
 4       1               0 2015-02-23       0        0
 5       1               0 2015-03-23       0        0
 6       2               0 2015-01-25       0        0
 7       2               1 2015-01-28       1        1
 8       2               1 2015-02-06       1        1
 9       2               0 2015-02-21       0        0
10       2               0 2015-03-26       0        0

For future elements it's bit tricky, because it's still 7-days window but lagged by -6 days (i:(i+6) = 7 days). Also in this case, first element of each window is excluded with sum(x[-1]).

df %>%
  group_by(user_id) %>%
  mutate(
    plus_7   = runner(cancelled_order, k = 7, lag = -6, idx = order_date, 
                      f = function(x) sum(x[-1])),
    plus_14  = runner(cancelled_order, k = 14, lag = -13, idx = order_date, 
                      f = function(x) sum(x[-1]))
  )


# A tibble: 10 x 5
# Groups:   user_id [2]
   user_id cancelled_order order_date plus_7 plus_14
     <int>           <dbl> <date>      <dbl>   <dbl>
 1       1               0 2015-01-28      1       2
 2       1               1 2015-01-31      0       1
 3       1               1 2015-02-08      0       0
 4       1               0 2015-02-23      0       0
 5       1               0 2015-03-23      0       0
 6       2               0 2015-01-25      1       2
 7       2               1 2015-01-28      0       1
 8       2               1 2015-02-06      0       0
 9       2               0 2015-02-21      0       0
10       2               0 2015-03-26      0       0

More information in package and function documentation.

Calculate the number of occurrences of a specific event in the past AND future with groupings

3 Answers3

Linked