r - 根据r中的开始时间和结束时间计算白天和晚上的小时数

Question

我想根据R中的开始时间和结束时间计算白天和晚上的分钟数。为了简化问题，我假设日出时间总是早上6点，日落时间总是下午6点，无论时区和位置如何。

以下是一些示例数据:

dat = structure(list(
  start_time = structure(c(1431096404, 1431107312, 1431124632, 1431163956, 1431170210, 1431180438, 1431225936, 1431431610, 1431434550, 1431450416, 1431457208), 
class = c("POSIXct", "POSIXt"), tzone = "America/Chicago"), 
  end_time = structure(c(1431104384, 1431119732, 1431126312, 1431168936, 1431179030, 1431193878, 1431240696, 1431432150, 1431447870, 1431455096, 1431465728),
class = c("POSIXct", "POSIXt"), tzone = "America/Chicago")), 
  row.names = c(NA, -11L), 
  class = "data.frame")

它看起来像下面的数据框:

            start_time            end_time
1  2015-05-08 09:46:44 2015-05-08 11:59:44
2  2015-05-08 12:48:32 2015-05-08 16:15:32
3  2015-05-08 17:37:12 2015-05-08 18:05:12
4  2015-05-09 04:32:36 2015-05-09 05:55:36
5  2015-05-09 06:16:50 2015-05-09 08:43:50
6  2015-05-09 09:07:18 2015-05-09 12:51:18
7  2015-05-09 21:45:36 2015-05-10 01:51:36
8  2015-05-12 06:53:30 2015-05-12 07:02:30
9  2015-05-12 07:42:30 2015-05-12 11:24:30
10 2015-05-12 12:06:56 2015-05-12 13:24:56
11 2015-05-12 14:00:08 2015-05-12 16:22:08

我想添加两列 minutes_day 和 minutes_night。 minutes_day 是这个特定时间段在白天(早上 6 点到下午 6 点)的分钟数，而 minutes_night 是这个特定时间段在晚上的分钟数(下午 6 点至次日早上 6 点)。所以我想要的 data.frame 如下所示:

            start_time            end_time minutes_day minutes_night
1  2015-05-08 09:46:44 2015-05-08 11:59:44         133             0
2  2015-05-08 12:48:32 2015-05-08 16:15:32         207             0
3  2015-05-08 17:37:12 2015-05-08 18:05:12          23             5
4  2015-05-09 04:32:36 2015-05-09 05:55:36           0            83
5  2015-05-09 06:16:50 2015-05-09 08:43:50         147             0
6  2015-05-09 09:07:18 2015-05-09 12:51:18         224             0
7  2015-05-09 21:45:36 2015-05-10 01:51:36           0           246
8  2015-05-12 06:53:30 2015-05-12 07:02:30           9             0
9  2015-05-12 07:42:30 2015-05-12 11:24:30         222             0
10 2015-05-12 12:06:56 2015-05-12 13:24:56          78             0
11 2015-05-12 14:00:08 2015-05-12 16:22:08         142             0

这个问题对我来说很难，因为 start_time 和 end_time 之间有些日期不同。

有人知道这个问题吗？谢谢!

Answer 1

library(lubridate)
library(dplyr)

此函数创建一个从start_time到end_time的分钟序列，提取结果时间的小时，将它们分为白天和黑夜，并得到两者之间的时差白天和晚上的最早和最晚时间。

get_minutes <- function(start_time, end_time) {
  mins_in_range <- seq(start_time, end_time, by = "mins")
  h_between <- hour(mins_in_range)

  hours_day <- mins_in_range[h_between >= 6 &
                     h_between < 18]
  hours_night <- mins_in_range[h_between < 6 |
                                 h_between >= 18]
  minutes_day <- tryCatch(as.numeric(difftime(max(hours_day),
                                              min(hours_day),
                                              units = "mins")),
                          warning = function(w) {
                            0
                          })

  minutes_night <- tryCatch(as.numeric(difftime(max(hours_night),
                                                min(hours_night),
                                                units = "mins")),
                            warning = function(w) {
                              0
                            })
  return(list(minutes_day = minutes_day, 
              minutes_night = minutes_night))
}

然后您可以遍历每行的数据，应用该函数(它返回一个 list 列)并将列表分隔为列(借助于 data.table 和 rbindlist:

dat %>% 
  rowwise() %>% 
  mutate(temp = list(get_minutes(start_time, end_time))) %>% 
  cbind(data.table::rbindlist(.$temp)) %>% 
  select(-temp)

最终结果是这样的:

             start_time            end_time minutes_day minutes_night
 1: 2015-05-08 09:46:44 2015-05-08 11:59:44         133             0
 2: 2015-05-08 12:48:32 2015-05-08 16:15:32         207             0
 3: 2015-05-08 17:37:12 2015-05-08 18:05:12          22             5
 4: 2015-05-09 04:32:36 2015-05-09 05:55:36           0            83
 5: 2015-05-09 06:16:50 2015-05-09 08:43:50         147             0
 6: 2015-05-09 09:07:18 2015-05-09 12:51:18         224             0
 7: 2015-05-09 21:45:36 2015-05-10 01:51:36           0           246
 8: 2015-05-12 06:53:30 2015-05-12 07:02:30           9             0
 9: 2015-05-12 07:42:30 2015-05-12 11:24:30         222             0
10: 2015-05-12 12:06:56 2015-05-12 13:24:56          78             0
11: 2015-05-12 14:00:08 2015-05-12 16:22:08         142             0