如何从POSIXct列提取Dry状态连续区间的起止时间？

需要从包含POSIXct类型Timestamp列的数据框中，获取每一段连续的'Dry'状态对应的首个和最后一个时间作为起止时间。

数据框定义 #

dat <- data.frame(
  Timestamp = as.POSIXct(
    c(
      "2021-07-30 19:00:00", "2021-07-30 19:30:00", "2021-07-30 20:00:00",
      "2021-07-30 20:30:00", "2021-07-30 21:00:00", "2021-07-30 21:30:00",
      "2021-07-30 22:00:00", "2021-07-30 22:30:00", "2021-07-30 23:00:00",
      "2021-07-30 23:30:00", "2021-07-31 00:00:00", "2021-07-31 00:30:00",
      "2021-07-31 01:00:00", "2021-07-31 01:30:00", "2021-07-31 02:00:00",
      "2021-07-31 02:30:00", "2021-07-31 03:00:00", "2021-07-31 03:30:00",
      "2021-07-31 04:00:00", "2021-07-31 04:30:00", "2021-07-31 05:00:00",
      "2021-07-31 05:30:00", "2021-07-31 06:00:00", "2021-07-31 06:30:00",
      "2021-07-31 07:00:00", "2021-07-31 07:30:00", "2021-07-31 08:00:00",
      "2021-07-31 08:30:00", "2021-07-31 09:00:00", "2021-07-31 09:30:00",
      "2021-07-31 10:00:00", "2021-07-31 10:30:00", "2021-07-31 11:00:00",
      "2021-07-31 11:30:00", "2021-07-31 12:00:00"
    )
  ),
  State = rep(c("Dry", "Wet", "Dry", "Wet", "Dry"), c(6L, 8L, 8L, 8L, 5L))
)

期望输出 #

data.frame(
  From = as.POSIXct(c("2021-07-30 19:00:00", "2021-07-31 02:00:00", "2021-07-31 10:00:00")),
  To = as.POSIXct(c("2021-07-30 21:30:00", "2021-07-31 05:30:00", "2021-07-31 12:00:00"))
)

已尝试代码 #

library(dplyr)
sms_data %>% 
  group_by(State)

解决方案 #

你之前的问题在于单纯按State分组会把所有'Dry'行归为一组，无法区分连续的Dry段。需要先创建一个标识连续状态的分组ID，再基于这个ID分组计算起止时间。

方法1：仅用dplyr实现 #

library(dplyr)

dat %>%
  # 过滤出Dry状态的行
  filter(State == "Dry") %>%
  # 生成连续Dry段的分组ID：当前行与上一行状态不同时，ID累加
  mutate(group_id = cumsum(c(TRUE, diff(as.integer(State)) != 0))) %>%
  # 按分组ID聚合，取每组的最早和最晚时间
  group_by(group_id) %>%
  summarise(
    From = min(Timestamp),
    To = max(Timestamp)
  ) %>%
  # 移除分组ID列
  select(-group_id)

方法2：结合data.table的rleid函数（更简洁） #

如果可以使用data.table包，rleid函数能直接生成连续状态的分组ID：

library(dplyr)
library(data.table)

dat %>%
  filter(State == "Dry") %>%
  mutate(group_id = rleid(State)) %>%
  group_by(group_id) %>%
  summarise(
    From = min(Timestamp),
    To = max(Timestamp)
  ) %>%
  select(-group_id)

两种方法都能得到你期望的输出结果。

内容的提问来源于stack exchange，提问作者Aravindan Kalai