Use R to create start time and end time columns based on first occurrence of events in another column

Question

I was wondering how to synthesis starttime and endtime from the dataframe below for example The data shows a call handler record for a time period when calls are made

Id  CallTime    CallHandler CallStatus
1   01/01/2020 00:05    A   Busy
2   01/01/2020 00:10    A   Free
3   01/01/2020 00:25    A   Free
4   01/01/2020 00:57    A   Free
5   01/01/2020 01:30    A   Busy
6   01/01/2020 01:45    A   Busy
7   01/01/2020 02:20    A   Busy
8   01/01/2020 02:25    A   Busy
9   01/01/2020 02:50    A   Free
10  01/01/2020 02:25    A   Free
11  01/01/2020 02:55    A   Busy
12  01/01/2020 03:25    A   Busy
13  01/01/2020 04:55    A   Free
14  01/01/2020 05:25    A   Busy
15  01/01/2020 05:55    A   Free
16  01/01/2020 06:25    A   Busy

Expected Output

The output should return the start and end time in different columns based on busy and free call status of Free and Busy


CallHandler StartTime   EndTime
A   01/01/2020 00:05    01/01/2020 00:10
A   01/01/2020 01:30    01/01/2020 02:50
A   01/01/2020 02:55    01/01/2020 04:55
A   01/01/2020 05:25    01/01/2020 05:55
A   01/01/2020 06:25    N/A

I have used

df %>%
  group_by(CallStatus) %>%
  mutate(StartTime = ifelse(CallStatus == "Free", CallTime, 0), EndTime = ifelse(CallStatus == "Busy", CallTime, 0))

To get the following, but nit really what I wanted

Id      CallTime         CallHandler CallStatus StartTime        EndTime               
1       01/01/2020 00:05 A           Busy       0                01/01/2020 00:05
2       01/01/2020 00:10 A           Free       01/01/2020 00:10 0               
3       01/01/2020 00:25 A           Free       01/01/2020 00:25 0               
4       01/01/2020 00:57 A           Free       01/01/2020 00:57 0               
5       01/01/2020 01:30 A           Busy       0                01/01/2020 01:30
6       01/01/2020 01:45 A           Busy       0                01/01/2020 01:45
7       01/01/2020 02:20 A           Busy       0                01/01/2020 02:20
8       01/01/2020 02:25 A           Busy       0                01/01/2020 02:25
9       01/01/2020 02:50 A           Free       01/01/2020 02:50 0               
10      01/01/2020 02:25 A           Free       01/01/2020 02:25 0               
11      01/01/2020 02:55 A           Busy       0                01/01/2020 02:55
12      01/01/2020 03:25 A           Busy       0                01/01/2020 03:25
13      01/01/2020 04:55 A           Free       01/01/2020 04:55 0               
14      01/01/2020 05:25 A           Busy       0                01/01/2020 05:25
15      01/01/2020 05:55 A           Free       01/01/2020 05:55 0               
16      01/01/2020 06:25 A           Busy       0                01/01/2020 06:25

Helle jesuistm. Please add a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610). That way others can easily test suggestions and you are more likely to get a good answer! The example data you supplied is **not** easily usable. — dario, Feb 08 '20 at 12:04

score 1 · Accepted Answer · answered Feb 08 '20 at 12:10

We can first filter out rows where CallStatus is "Busy" or the previous value of CallStatus is "Busy", create groups of them and select first and last entry in each group. We replace EndTime with NA when StartTime and EndTime are same.

library(dplyr)

df %>%
  filter(CallStatus == "Busy" | lag(CallStatus) == "Busy") %>%
  group_by(CallHandler, gr = cumsum(lag(CallStatus != "Busy", default = TRUE))) %>%
  summarise(StartTime = first(CallTime), 
            EndTime = last(CallTime)) %>%
  mutate(EndTime = replace(EndTime, StartTime == EndTime, NA)) %>%
  select(-gr)


# CallHandler StartTime         EndTime        
# <fct>       <fct>             <fct>          
#1 A           01/01/2020 00:05 01/01/2020 00:10
#2 A           01/01/2020 01:30 01/01/2020 02:50
#3 A           01/01/2020 02:55 01/01/2020 04:55
#4 A           01/01/2020 05:25 01/01/2020 05:55
#5 A           01/01/2020 06:25 NA

Use R to create start time and end time columns based on first occurrence of events in another column

1 Answers1