r - 如何在给定的时间间隔内计算小时数

Question

我有一个数据集，其中包含使用该服务的开始时间和结束时间。总共约 1000 行。
我需要计算任何给定时间间隔内的小时数。

数据集:

   court_number       start_time         end_time  service1:      court 2 2020-03-01 11:00:00 2020-03-01 12:30:00  booking2:      court 3 2020-03-01 12:30:00 2020-03-01 13:30:00 coaching3:      court 1 2020-03-01 11:00:00 2020-03-01 13:00:00  booking4:      court 5 2020-03-01 12:00:00 2020-03-01 16:00:00  booking5:      court 5 2020-03-01 16:30:00 2020-03-01 18:30:00 coaching

library(data.table)
dt <- data.table(court_number = c('court 2','court 3','court 1','court 5','court 5'),
            start_time = c('2020-03-01 11:00:00', '2020-03-01 12:30:00', '2020-03-01 11:00:00', '2020-03-01 12:00:00', '2020-03-01 16:30:00'),
            end_time = c('2020-03-01 12:30:00', '2020-03-01 13:30:00', '2020-03-01 13:00:00', '2020-03-01 16:00:00', '2020-03-01 18:30:00'),
            service = c('booking','coaching','booking','booking','coaching'))

例如，我想计算从“12:00”到“17:00”的时间间隔中的小时数。
所以，我需要创建一个小时数从 12 到 17 的列:

Court_number start_time end_time service interval_12_17
1:法院 2 2020-03-01 11:00:00 2020-03-01 12:30:00 预订 0,5
2:球场3 2020-03-01 12:30:00 2020-03-01 13:30:00 教练1
3:法院1 2020-03-01 11:00:00 2020-03-01 13:00:00 预约1
4:法院 5 2020-03-01 12:00:00 2020-03-01 16:00:00 预订 4
5: 球场 5 2020-03-01 16:30:00 2020-03-01 18:30:00 教练 0,5

我在 stackoverflow 上阅读了很多类似的已解决问题，但由于我是 R 的新手，这对我没有帮助 =)

Answer 1

我们将“时间”列转换为 Datetime类，获取 difftime的 pmin “结束时间”和“17:00:00”的“时间”以及 pmax 'start_time' 和 "12:00:00"

library(dplyr)
library(lubridate)
library(data.table)
dt %>%
    mutate_at(vars(ends_with('time')), ymd_hms) %>% 
    mutate(interval_12_17 = difftime(pmin(as.ITime(end_time), 
           as.ITime("17:00:00")),
         pmax(as.ITime(start_time),as.ITime("12:00:00")), unit = 'hour'))
#   court_number          start_time            end_time  service interval_12_17
#1:      court 2 2020-03-01 11:00:00 2020-03-01 12:30:00  booking      0.5 hours
#2:      court 3 2020-03-01 12:30:00 2020-03-01 13:30:00 coaching      1.0 hours
#3:      court 1 2020-03-01 11:00:00 2020-03-01 13:00:00  booking      1.0 hours
#4:      court 5 2020-03-01 12:00:00 2020-03-01 16:00:00  booking      4.0 hours
#5:      court 5 2020-03-01 16:30:00 2020-03-01 18:30:00 coaching      0.5 hours

或使用 data.table

dt[, interval_12_17 := difftime(pmin(as.ITime(end_time), as.ITime("17:00:00")),
        pmax(as.ITime(start_time),as.ITime("12:00:00")), unit = 'hour')][]
#    court_number          start_time            end_time  service interval_12_17
#1:      court 2 2020-03-01 11:00:00 2020-03-01 12:30:00  booking      0.5 hours
#2:      court 3 2020-03-01 12:30:00 2020-03-01 13:30:00 coaching      1.0 hours
#3:      court 1 2020-03-01 11:00:00 2020-03-01 13:00:00  booking      1.0 hours
#4:      court 5 2020-03-01 12:00:00 2020-03-01 16:00:00  booking      4.0 hours
#5:      court 5 2020-03-01 16:30:00 2020-03-01 18:30:00 coaching      0.5 hours