Calculating time elapsed within grouped sets of rows

user_10427113 0
Viewed 307

I know there are many stackoverflow pages asking about calculating date-time differences between rows but I have not found one that answers my particular question. Most are interested in data between subsequent rows but I need to calculate for a series of individuals how much time has elapsed for each individual. For example

id      time (of the day)
1       12:00:40
1       12:06:35
1       12:12:00
2       14:59:22
2       15:10:40

I would like to calculate something that looks like this:

   id      total time (seconds)
    1       760
    2       678

Thanks in advance for help and I apologize if this has been answered somewhere else, I have not been able to find it.

r datetime
2 Answers

A complete base R approach

aggregate(time~id, transform(df, time = as.POSIXct(time, format = "%H:%M:%S")), 
                   function(x) sum(as.numeric(diff(x), "secs")))

#  id time
#1  1  680
#2  2  678

Using dplyr and lubridate, we first convert time to Period class and convert it into seconds and take the sum of its difference.

library(dplyr)
library(lubridate)

df %>%
  group_by(id) %>%
  summarise(total_time = sum(diff(seconds(hms(time)))))


#    id   total_time
#   <int>      <dbl>
#1     1        680
#2     2        678
user_39629140

data.table approach

First set the time-string to a POSIXct-format, so you can use functions like difftime. This comes in handy when an id had a timespan over more than 1 day (say from 22h untill 02h) Second step is to calculate the difftime (in seconds) between the minimum time-value and the maximum time-value (by id-group)

library( data.table )

dt <- fread("id      time
1       12:00:40
1       12:06:35
1       12:12:00
2       14:59:22
2       15:10:40", header = TRUE)

#set time to POSIXct
dt[, time := as.POSIXct( paste0( "2019-01-01 ", time ), format = "%Y-%m-%d %H:%M:%S" ) ]
dt[, .( total_time = difftime( max( time ), min( time ), units = "secs" )), by = .( id ) ]

#    id total_time
# 1:  1   680 secs
# 2:  2   678 secs
user_63562780
Related