Group by Status and find difference of datetime being on this status

Question

 x                     Status   
14/09/2017 15:30:31     A           
14/09/2017 15:30:34     A           
14/09/2017 16:40:25     B           
14/09/2017 17:00:25     B           
15/09/2017 09:00:20     A            
15/09/2017 10:00:20     A

What I want is to group it by Status and find the difference between date times on each group also the cumsum of time. The desired out put would be

 x                     Status   diff.time
14/09/2017 15:30:31     A           
14/09/2017 15:30:34     A           3 mins
14/09/2017 16:40:25     B           
14/09/2017 17:00:25     B           20 mins
15/09/2017 09:00:20     A            
15/09/2017 10:00:20     A           60 mins

score 0 · Answer 1 · answered Mar 09 '21 at 15:44

library( data.table )

#sample data
DT <- fread("x                     Status   
14/09/2017T15:30:31     A           
14/09/2017T15:30:34     A           
14/09/2017T16:40:25     B           
14/09/2017T17:00:25     B           
15/09/2017T09:00:20     A            
15/09/2017T10:00:20     A")

DT[, x := as.POSIXct( x, format = "%d/%m/%YT%H:%M:%S" )]

#code
DT[, diff.time := max(x) - min(x),  by = .(rleid(Status)) ][]


# x                      Status      test
# 1: 2017-09-14 15:30:31      A    3 secs
# 2: 2017-09-14 15:30:34      A    3 secs
# 3: 2017-09-14 16:40:25      B 1200 secs
# 4: 2017-09-14 17:00:25      B 1200 secs
# 5: 2017-09-15 09:00:20      A 3600 secs
# 6: 2017-09-15 10:00:20      A 3600 secs

Group by Status and find difference of datetime being on this status

1 Answers1