Conditionally calculating average time between events by group in R

Question

I am working with a call log data set from a telephone hotline service. There are three call outcomes: Answered, Abandoned & Engaged. I am trying to find out the average time taken by each caller to contact the hotline again if they abandoned the previous call. The time difference can be either seconds, minutes, hours or days but I would like to get all four if possible.

Here is some mock data with the variables I am working with:-

library(wakefield)#for generating the Status variable
library(dplyr)
library(stringi)
library(Pareto)
library(uuid)

n_users<-1300
n_rows <- 365000

set.seed(1)
#data<-data.frame()
Date<-seq(as.Date("2015-01-01"), as.Date("2015-12-31"), by = "1 day")
Date<-sample(rep(Date,each=1000),replace = T)

u <- runif(length(Date), 0, 60*60*12) # "noise" to add or subtract from some timepoint
CallDateTime<-as.POSIXlt(u, origin = paste0(Date,"00:00:00"))
CallDateTime

CallOutcome<-r_sample_factor(x = c("Answered", "Abandoned", "Engaged"), n=length(Date))
CallOutcome

data<-data.frame(Date,CallDateTime,CallOutcome)

relative_probs <- rPareto(n = n_users, t = 1, alpha = 0.3, truncation = 500) 
unique_ids <- UUIDgenerate(n = n_users)

data$CallerId <- sample(unique_ids, size = n_rows, prob = relative_probs, replace = TRUE)
data<-data%>%arrange(CallDateTime)

head(data)

So to reiterate, if a caller abandons their call (represented by "Abandoned" in the CallOutcome column), I would like to know the average time taken for the caller to make another call to the service, in the four time units I have mentioned. Any pointers on how I can achieve this would be great :)

Answer 1

Keep rows in the data where the current row is "Abandoned" and the next row is not "Abandoned" for each ID. Find difference in time between every 2 rows to get time required for the caller to make another call to service after it was abandoned, take average of each of the duration to get average time.

library(dplyr)

data %>%
  #Test the answer on smaller subset
  #slice(1:1000) %>%
  arrange(CallerId, CallDateTime) %>%
  group_by(CallerId) %>%
  filter(CallOutcome == 'Abandoned' & dplyr::lead(CallOutcome) != 'Abandoned' | 
          CallOutcome != 'Abandoned' & dplyr::lag(CallOutcome) == 'Abandoned') %>%
  mutate(group = rep(row_number(), each = 2, length.out = n())) %>%
  group_by(group, .add = TRUE) %>%
  summarise(avg_sec = difftime(CallDateTime[2], CallDateTime[1], units = 'secs')) %>%
  mutate(avg_sec = as.numeric(mean(avg_sec)), 
         avg_min = avg_sec/60, 
         avg_hour = avg_min/60, 
         avg_day = avg_hour/24) -> result

result