R语言按分组提取每组首个与末尾元素对应日期的实现方法

核心思路是先为每个Element下连续相同的Event生成唯一分组ID，再按天、Element、Event、分组ID聚合提取起止时间即可。

依赖包 #

需要用到dplyr做数据处理、lubridate做日期提取，也可根据偏好使用data.table实现。

完整实现代码 #

dplyr版本 #

# 构造测试数据
df <- structure(list(Date = structure(c(1577836800, 1577837100,1577837400, 1577837700, 1577838000, 1577838300, 1577838600, 1577836800, 1577837100, 
1577837400, 1577837700, 1577838000, 1577838300, 1577838600), class = c("POSIXct", 
"POSIXt"), tzone = "UTC"), Element = c("A", "A", "A", "A", "A", 
"A", "A", "B", "B", "B", "B", "B", "B", "B"), Event = c("OK", 
"OK", "OK", "Alarm", "Alarm", "Alarm", "OK", "OK", "OK", "Alarm", 
"Alarm", "Alarm", "OK", "OK")), row.names = c(NA, -14L), class = c("tbl_df", 
"tbl", "data.frame"))

# 加载依赖
library(dplyr)
library(lubridate)

result <- df %>%
  # 保证数据按Element、时间顺序排列
  arrange(Element, Date) %>%
  # 提取日期维度
  mutate(Day = as.Date(Date)) %>%
  # 按Element分组，生成连续事件分组ID：Event变化时ID加1
  group_by(Element) %>%
  mutate(grp_id = cumsum(Event != lag(Event, default = first(Event)))) %>%
  # 按目标维度分组，取每个连续块的起止时间
  group_by(Day, Element, Event, grp_id) %>%
  summarise(
    Begin = min(Date),
    End = max(Date),
    .groups = "drop"
  ) %>%
  # 移除辅助分组列
  select(-grp_id)

data.table版本（运行效率更高，适合大数据量） #

library(data.table)
library(lubridate)

# 转换为data.table格式
setDT(df)
# 排序、生成日期列
df[order(Element, Date), Day := as.Date(Date)]
# 生成连续事件分组ID
df[, grp_id := cumsum(Event != shift(Event, fill = first(Event))), by = Element]
# 聚合提取结果，移除辅助列
result <- df[, .(Begin = min(Date), End = max(Date)), by = .(Day, Element, Event, grp_id)][, !"grp_id"]

输出结果 #

运行上述代码后得到的result和要求的结构完全一致：

Day Element Event               Begin                 End
1 2020-01-01       A    OK 2020-01-01 00:00:00 2020-01-01 00:10:00
2 2020-01-01       A Alarm 2020-01-01 00:15:00 2020-01-01 00:25:00
3 2020-01-01       A    OK 2020-01-01 00:30:00 2020-01-01 00:30:00
4 2020-01-01       B    OK 2020-01-01 00:00:00 2020-01-01 00:05:00
5 2020-01-01       B Alarm 2020-01-01 00:10:00 2020-01-01 00:20:00
6 2020-01-01       B    OK 2020-01-01 00:25:00 2020-01-01 00:30:00

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