计算不同行的列之间的时间差（difftime） [英] Calculate time difference (difftime) between columns of different rows

问题描述

我有关于不同工作的开始和结束时间的数据，按所有者分组：

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.

如何使用 difftime（）来计算特定行和不同列中的时间之间的时间差？

How do I use difftime() to calculate this time difference between specific rows and times in different columns?

结果应如下所示：

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
...

推荐答案

以下是使用 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

或使用 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

这篇关于计算不同行的列之间的时间差（difftime）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！