Challenge: How can I calculate the time-lapse in this?

tidyverse
1

I have a group of operators that their job are to call a technician if an incident ocurred with machines and repeat those contacts till the alerts are closed. I have a record of each contacts they made.
I need to look upon those data and calculate how long (in minutes) they took to call or interact.

The clock starts with the first incident alert_time and restarts/reset for each contacts made. I have created the Id, the attempt_number and the attemp_seq to control the number of contacts for each Id. However I can't calculate the minutes between each the contacts starting by the first alert.

Any body can help me find a solution?

(For instance only and with dummy data)

code:

library(tidyverse)
my_a <- data.frame(machine_id = c(12, 14, 12, 12, 14), incident_alert = c("Error_01", 
    "Error_02", "Error_01", "Error_01", "Error_02"), alert_time = c("2019-12-23 07:10:10", 
    "2019-12-23 08:01:29", "2019-12-23 07:10:10", "2019-12-23 07:10:10", 
    "2019-12-23 08:01:29"), operator_contact_time = c("2019-12-23 08:05:13", 
    "2019-12-23 08:22:10", "2019-12-23 08:35:45", "2019-12-23 09:25:52", 
    "2019-12-23 09:38:01"))

my_b <- my_a %>% mutate(Id = paste(machine_id, incident_alert, 
    alert_time, sep = "")) %>% group_by(Id) %>% mutate(attempt_numb = n(), 
    attemp_seq = sequence(n())) %>% select(Id, everything())

Console result:

> my_a
  machine_id incident       incident_time operator_contact_time
1         12 Error_01 2019-12-23 07:10:10   2019-12-23 08:05:13
2         14 Error_02 2019-12-23 08:01:29   2019-12-23 08:22:10
3         12 Error_01 2019-12-23 07:10:10   2019-12-23 08:35:45
4         12 Error_01 2019-12-23 07:10:10   2019-12-23 09:25:52
5         14 Error_02 2019-12-23 08:01:29   2019-12-23 09:38:01

> my_b
# A tibble: 5 x 7
# Groups:   Id [2]
  Id    machine_id incident_alert alert_time operator_contac~ attempt_numb
  <chr>      <dbl> <fct>          <fct>      <fct>                   <int>
1 12Er~         12 Error_01       2019-12-2~ 2019-12-23 08:0~            3
2 14Er~         14 Error_02       2019-12-2~ 2019-12-23 08:2~            2
3 12Er~         12 Error_01       2019-12-2~ 2019-12-23 08:3~            3
4 12Er~         12 Error_01       2019-12-2~ 2019-12-23 09:2~            3
5 14Er~         14 Error_02       2019-12-2~ 2019-12-23 09:3~            2

Excel example for what I expect:

machine_id	incident_alert	alert_time	operator_contact_time	attempt_numb	attemp_seq	time_laps_minute
12	Error_01	2019-12-23 07:10:10	2019-12-23 08:05:13	3	1	55
14	Error_02	2019-12-23 08:01:29	2019-12-23 08:22:10	2	1	20
12	Error_01	2019-12-23 07:10:10	2019-12-23 08:35:45	3	2	30
12	Error_01	2019-12-23 07:10:10	2019-12-23 09:25:52	3	3	50
14	Error_02	2019-12-23 08:01:29	2019-12-23 09:38:01	2	2	15
1 Like
2

Could you repost as a reproducible example, called a reprex with incident_alert included, please?

3

Oh! Thank you. I have never used reprex before. I updated the post but I don't know if it is now more reproducible.

1 Like
4

This will allow you to do date arithmetic on your reprex

# minimum libraries needed
suppressPackageStartupMessages(library(dplyr)) 
suppressPackageStartupMessages(library(lubridate)) 
# OP's reprex
my_a <- data.frame(machine_id = c(12, 14, 12, 12, 14), incident_alert = c("Error_01", 
    "Error_02", "Error_01", "Error_01", "Error_02"), alert_time = c("2019-12-23 07:10:10", 
    "2019-12-23 08:01:29", "2019-12-23 07:10:10", "2019-12-23 07:10:10", 
    "2019-12-23 08:01:29"), operator_contact_time = c("2019-12-23 08:05:13", 
    "2019-12-23 08:22:10", "2019-12-23 08:35:45", "2019-12-23 09:25:52", 
    "2019-12-23 09:38:01"))
# the fields are all factors
str(my_a)
#> 'data.frame':    5 obs. of  4 variables:
#>  $ machine_id           : num  12 14 12 12 14
#>  $ incident_alert       : Factor w/ 2 levels "Error_01","Error_02": 1 2 1 1 2
#>  $ alert_time           : Factor w/ 2 levels "2019-12-23 07:10:10",..: 1 2 1 1 2
#>  $ operator_contact_time: Factor w/ 5 levels "2019-12-23 08:05:13",..: 1 2 3 4 5
# change to character, assuming no arithmetic to be done with machine_id
my_a[] <- lapply(my_a, as.character)
# make the two fields into datetime objects
my_a <- my_a %>% mutate(alert_time = as_datetime(alert_time), operator_contact_time = as_datetime(operator_contact_time))
str(my_a)
#> 'data.frame':    5 obs. of  4 variables:
#>  $ machine_id           : chr  "12" "14" "12" "12" ...
#>  $ incident_alert       : chr  "Error_01" "Error_02" "Error_01" "Error_01" ...
#>  $ alert_time           : POSIXct, format: "2019-12-23 07:10:10" "2019-12-23 08:01:29" ...
#>  $ operator_contact_time: POSIXct, format: "2019-12-23 08:05:13" "2019-12-23 08:22:10" ...
my_a[1,4] - my_a[1,3]
#> Time difference of 55.05 mins

Created on 2019-12-23 by the reprex package (v0.3.0)

See how that works and come back with a new reprex if needed for help on run length encoding if you add a success/failure on attempt field

5

Allrigh. I can see that. Thank you.
However, my goal is to calculate the time between each operator contact for each unique alert. Have you any idea on what would be a way to do it?

1 Like
6

Good! I'm not sure, though, that I correctly understand the second part. Let's see if I can explain my understanding clearly.

Ideally, I would like for each row a numeric field for whether contact was made (1) or not (0). I'd think that I'd want to sort by machine_id:incident_alert and find the number of 0until the first1, by using the rle` function. The vector of runs of 0 and 1 give indices to the df that you can use to identify the first attempt and the successful attempt, then calculate the time between them.

Am I at all close?

7

Yes you are. I'll try the rle function and see if come up with it.

1 Like
8

Take a look at a recent post for an example