I have data on 'Start' and 'End' time for different jobs, grouped by 'owner':
Data <- data.frame(
job = c(1, 2, 3, 4, 5),
owner = c("name1", "name2", "name1", "name1", "name2"),
Start = as.POSIXct(c("2015-01-01 15:00:00", "2015-01-01 15:01:00", "2015-01-01 15:13:00", "2015-01-01 15:20:00", "2015-01-01 15:39:02"), format="%Y-%m-%d %H:%M:%S"),
End = as.POSIXct(c("2015-01-01 15:11:11", "2015-01-01 15:17:21", "2015-01-01 15:17:00", "2015-01-01 15:31:21", "2015-01-01 15:40:11"), format="%Y-%m-%d %H:%M:%S")
)
For each owner, I want to calculate the idle time between the jobs for each owner, i.e. the difference between the 'End' time of one job and the 'Start' time of the next job.
How do I use difftime() to calculate this time difference between specific rows and times in different columns?
The result should look something like this:
job, owner, idletime
1, name1, NA
2, name2, NA
3, name1, 1.816667 # End of row 1 minus Start of row 3
4, name1, 3.0 # End of row 3 minus Start of row 4
...
Here's a possible solution using data.table
library(data.table) # v 1.9.5+
setDT(Data)[, idletime := difftime(Start, shift(End), units = "mins"), by = owner]
# job owner Start End idletime
# 1: 1 name1 2015-01-01 15:00:00 2015-01-01 15:11:11 NA mins
# 2: 2 name2 2015-01-01 15:01:00 2015-01-01 15:17:21 NA mins
# 3: 3 name1 2015-01-01 15:13:00 2015-01-01 15:17:00 1.816667 mins
# 4: 4 name1 2015-01-01 15:20:00 2015-01-01 15:31:21 3.000000 mins
# 5: 5 name2 2015-01-01 15:39:02 2015-01-01 15:40:11 21.683333 mins
Or using dplyr
library(dplyr)
Data %>%
group_by(owner) %>%
mutate(idletime = difftime(Start, lag(End), units = "mins"))
# Source: local data frame [5 x 5]
# Groups: owner
#
# job owner Start End idletime
# 1 1 name1 2015-01-01 15:00:00 2015-01-01 15:11:11 NA mins
# 2 2 name2 2015-01-01 15:01:00 2015-01-01 15:17:21 NA mins
# 3 3 name1 2015-01-01 15:13:00 2015-01-01 15:17:00 1.816667 mins
# 4 4 name1 2015-01-01 15:20:00 2015-01-01 15:31:21 3.000000 mins
# 5 5 name2 2015-01-01 15:39:02 2015-01-01 15:40:11 21.683333 mins