group by consecutive values in r

Question

I've got a dataset coming from a support ticketing system that logs each click made by an agent in classifying and responding to customer requests. The system assigns a new hist_id to each click, but an agent will click several fields, triggering several rows in the table, in what they consider a single "interaction".

My goal is to calculate a handle time for each of these interaction by doing a diff on the first and last modify_time values in each group.

I'm stuck currently because an agent will have multiple interactions with a case throughout the day.

Here's a sample dataframe:

hist_id <- c(1234, 2345, 3456, 4567, 5678, 6789, 7890)
case_id <- c(1, 1, 1, 1, 1, 1, 1)
agent_name <- c("John", "John", "John", "Paul", "Paul", "John", "John")
modify_time <- as.POSIXct(c(1510095120, 1510095180, 1510095240, 1510098600, 1510098720, 1510135200, 1510135320), origin = "1970-01-01")
df <- data.frame(hist_id, case_id, agent_name, modify_time)

Using group by on the case_id and agent_name groups all rows that match the criteria, as expected:

df %>% group_by(case_id, agent_name) %>% mutate(first = first(modify_time), last = last(modify_time), diff = min(difftime(last, first)))

Which gives me this:

    # A tibble: 7 x 7
# Groups:   case_id, agent_name [2]
  hist_id case_id agent_name         modify_time               first                last       diff
    <dbl>   <dbl>     <fctr>              <dttm>              <dttm>              <dttm>     <time>
1    1234       1       John 2017-11-07 16:52:00 2017-11-07 16:52:00 2017-11-08 04:02:00 40200 secs
2    2345       1       John 2017-11-07 16:53:00 2017-11-07 16:52:00 2017-11-08 04:02:00 40200 secs
3    3456       1       John 2017-11-07 16:54:00 2017-11-07 16:52:00 2017-11-08 04:02:00 40200 secs
4    4567       1       Paul 2017-11-07 17:50:00 2017-11-07 17:50:00 2017-11-07 17:52:00   120 secs
5    5678       1       Paul 2017-11-07 17:52:00 2017-11-07 17:50:00 2017-11-07 17:52:00   120 secs
6    6789       1       John 2017-11-08 04:00:00 2017-11-07 16:52:00 2017-11-08 04:02:00 40200 secs
7    7890       1       John 2017-11-08 04:02:00 2017-11-07 16:52:00 2017-11-08 04:02:00 40200 secs

Where John's true first and last modify_times are returned. However, I need to group the consecutive matches of case_id and agent_name, so that Paul's interaction is considered. So three interactions are recorded here: one from John, one from Paul, and a second by John.

Desired output would be something like this:

    # A tibble: 7 x 7
# Groups:   case_id, agent_name [2]
  hist_id case_id agent_name         modify_time               first                last       diff
    <dbl>   <dbl>     <fctr>              <dttm>              <dttm>              <dttm>     <time>
1    1234       1       John 2017-11-07 16:52:00 2017-11-07 16:52:00 2017-11-07 16:54:00 120 secs
2    2345       1       John 2017-11-07 16:53:00 2017-11-07 16:52:00 2017-11-07 16:54:00 120 secs
3    3456       1       John 2017-11-07 16:54:00 2017-11-07 16:52:00 2017-11-07 16:54:00 120 secs
4    4567       1       Paul 2017-11-07 17:50:00 2017-11-07 17:50:00 2017-11-07 17:52:00 120 secs
5    5678       1       Paul 2017-11-07 17:52:00 2017-11-07 17:50:00 2017-11-07 17:52:00 120 secs
6    6789       1       John 2017-11-08 04:00:00 2017-11-08 04:00:00 2017-11-08 04:02:00 120 secs
7    7890       1       John 2017-11-08 04:02:00 2017-11-08 04:00:00 2017-11-08 04:02:00 120 secs

Do you have any hints about the time span of a typical interaction? — amarchin, Nov 08 '17 at 00:27

leerssej · Accepted Answer · 2017-11-08T11:51:07.080

Here is a tidyverse approach that partitions the groups by the processing cluster identity, as well as case_id, and agent_name:

Arranging all the click in sequence, generate a new id flag for each time that a hist_id sequence encounters a transition to a new agent_name. cumsum those flags to generate a unique prcl_id per case, per agent, per cluster processing chunk. With all three id's you can then run your chosen mutations within the desired partitions.

df %>% 
    arrange(hist_id) %>%  # to ensure there are no wrinkles
    mutate(ag_chg_flg = ifelse(lag(agent_name) != agent_name, 1, 0) %>%
               coalesce(0) # to reassign the first click in a case_id to 0 (from NA)
           ) %>% 
    group_by(case_id, agent_name) %>%  
    mutate(prcl_id = cumsum(ag_chg_flg) + 1) %>%  # generate the proc_clst_id (starting at 1) 
    group_by(case_id, agent_name, prcl_id) %>%  # group by the complete composite id
    mutate(first = first(modify_time),
           last = last(modify_time),
           diff = min(difftime(last, first))
           )

Which gets you:

# A tibble: 7 x 9
# Groups:   case_id, agent_name, prcl_id [3]
  hist_id case_id agent_name         modify_time ag_chg_flg prcl_id               first                last   diff
    <dbl>   <dbl>     <fctr>              <dttm>      <dbl>   <dbl>              <dttm>              <dttm> <time>
1    1234       1       John 2017-11-07 14:52:00          0       1 2017-11-07 14:52:00 2017-11-07 14:54:00 2 mins
2    2345       1       John 2017-11-07 14:53:00          0       1 2017-11-07 14:52:00 2017-11-07 14:54:00 2 mins
3    3456       1       John 2017-11-07 14:54:00          0       1 2017-11-07 14:52:00 2017-11-07 14:54:00 2 mins
4    4567       1       Paul 2017-11-07 15:50:00          1       2 2017-11-07 15:50:00 2017-11-07 15:52:00 2 mins
5    5678       1       Paul 2017-11-07 15:52:00          0       2 2017-11-07 15:50:00 2017-11-07 15:52:00 2 mins
6    6789       1       John 2017-11-08 02:00:00          1       2 2017-11-08 02:00:00 2017-11-08 02:02:00 2 mins
7    7890       1       John 2017-11-08 02:02:00          0       2 2017-11-08 02:00:00 2017-11-08 02:02:00 2 mins

You're most welcome. Glad I could help. If this answer was useful would you mind up-voting it, and if a better approach isn't submitted, would you mind selecting it as the answer? — leerssej, Nov 08 '17 at 20:04

group by consecutive values in r

1 Answers1

Linked