CODEWITHSUNDEEP
rdatetimedplyrtidyverse
Harrison B. Pugh
Harrison B. Pugh

Reputation: 158

R - find occurrences of observation that happens in temporal proximity to another observation

I have two dfs, one of call dates and customer ids (logs), one with lapse dates and customer ids (gaps). How can I find if for any customer's call, that customer had a lapse within the next two days, two weeks, and two years?

id = unique customer ID

call_date = 1 signifies the observation is a call

lapse_date = 1 signifies the observation is a lapse

logs <- structure(list(id = c(4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 4968L, 5818L, 5818L, 5818L, 5818L, 7118L, 7118L, 7118L, 7118L, 7118L, 7293L, 9451L, 9451L, 9793L, 9793L, 9793L, 9793L, 9793L, 9793L, 9793L), call_date = structure(c(16108, 16262, 16297, 16367, 16414, 16465, 16612, 16661, 16738, 16769, 16829, 16982, 17032, 17112, 17200, 17347, 16174, 16174, 16174, 16174, 16212, 16232, 17242, 17242, 17245, 16084, 16301, 16301, 16020, 16133, 16414, 16657, 16899, 17227, 17228), class = "Date")), class = "data.frame", row.names = c(NA, -35L), .Names = c("id", "call_date"))
gaps <- structure(list(id = c(4968L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 5818L, 7118L, 7118L, 7118L, 7118L, 7293L, 9451L, 9451L, 9793L, 9793L, 9793L), lapse_date = structure(c(14910, 15329, 15394, 15516, 15649, 15775, 15915, 15976, 16066, 16134, 16199, 16272, 16431, 16542, 16637, 16722, 16789, 16917, 17084, 17144, 17224, 17308, 15085, 15331, 16041, 16637, 15533, 14764, 16195, 15405, 15534, 15749), class = "Date")), class = "data.frame", row.names = c(NA, -32L), .Names = c("id", "lapse_date"))

I prefer working in the tidyverse, I may name my first born Hadley.

Upvotes: 1

Views: 53

Answers (1)

bouncyball
bouncyball

Reputation: 10781

Here's how I might approach this problem.

library(tidyverse)

logs %>%
    inner_join(gaps) %>% 
    mutate(days_diff = as.numeric(lapse_date - call_date)) %>%
    mutate(two_days = as.numeric(days_diff %in% 0:2),
           two_weeks = as.numeric(days_diff %in% 0:14),
           two_years = as.numeric(days_diff %in% 0:730)) %>%
    select(-lapse_date, -days_diff) %>%
    group_by(id, call_date) %>%
    summarise_all(max)

      id call_date  two_days two_weeks two_years
   <int> <date>        <dbl>     <dbl>     <dbl>
 1  4968 2014-02-07        0         0         0
 2  4968 2014-07-11        0         0         0
 3  4968 2014-08-15        0         0         0
 4  4968 2014-10-24        0         0         0

We join by id, then create a days_diff variable. After that we create three indicator variables measuring the date difference, finally we take the max of these three indicator variables by id and call_date.

Upvotes: 2

Related Questions