如何用R的data.table计算problem与all fine事件的时间间隔

我有一个结构为<time>, <event_tag>的表格日志文件，需要找出"problem"和"all fine"两种event_tag之间的时间间隔。规则是：找到第一个"problem"，再寻找其对应的下一个"all fine"，以此类推直到处理完最后一个"problem"及其后续的"all fine"。

样本数据集 #

library(data.table)
set.seed(156125)
DT <- data.table(time = seq(as.POSIXct(tz = "UTC", "2024-01-01"), 
                            as.POSIXct(tz = "UTC", "2024-01-10"), 
                            by = "12 hours"),
                 event_tag = c("problem", "all fine")[round(runif(19, 1.2, 2.49))])

日志内容：

time event_tag

1: 2024-01-01 00:00:00 all fine
2: 2024-01-01 12:00:00 all fine
3: 2024-01-02 00:00:00 problem
4: 2024-01-02 12:00:00 all fine
5: 2024-01-03 00:00:00 all fine
6: 2024-01-03 12:00:00 problem
7: 2024-01-04 00:00:00 problem
8: 2024-01-04 12:00:00 all fine
9: 2024-01-05 00:00:00 all fine
10: 2024-01-05 12:00:00 all fine
11: 2024-01-06 00:00:00 all fine
12: 2024-01-06 12:00:00 all fine
13: 2024-01-07 00:00:00 problem
14: 2024-01-07 12:00:00 all fine
15: 2024-01-08 00:00:00 all fine
16: 2024-01-08 12:00:00 problem
17: 2024-01-09 00:00:00 all fine
18: 2024-01-09 12:00:00 all fine
19: 2024-01-10 00:00:00 all fine

期望结果 #

data.table(problem_start = DT$time[c(3, 6, 13, 16)],
           problem_end = DT$time[c(4, 8, 14, 17)])

输出结果：

problem_start         problem_end
            <POSc>              <POSc>

1: 2024-01-02 00:00:00 2024-01-02 12:00:00
2: 2024-01-03 12:00:00 2024-01-04 12:00:00
3: 2024-01-07 00:00:00 2024-01-07 12:00:00
4: 2024-01-08 12:00:00 2024-01-09 00:00:00

尝试过的代码 #

DT[ , bool := ifelse(event_tag == "all fine", 0, 1)]
DT[ , cumsum(bool)]

但未完全实现需求，希望得到简洁的data.table解决方案，也可接受dplyr方案。

1. data.table 方案 #

核心思路：筛选所有"problem"记录，为每条记录匹配后续第一个"all fine"的时间。

library(data.table)

# 链式写法一步到位
result_dt <- DT[event_tag == "problem"][, 
  problem_end := DT[time > .BY$time & event_tag == "all fine", time[1]], 
  by = time
][, .(problem_start = time, problem_end)]

逻辑解释 #

• 第一步筛选出所有"problem"的记录；
• 按每条problem的时间分组，在原表中查找晚于当前problem时间的第一条"all fine"记录的时间；
• 最后整理成期望的两列结构。

2. dplyr 方案 #

利用分组标记，提取每组内的首个problem和首个恢复的all fine时间。

library(dplyr)

result_dplyr <- DT %>%
  # 为每个连续的problem事件创建组ID
  mutate(group_id = cumsum(event_tag == "problem")) %>%
  # 只保留存在problem的组
  filter(group_id > 0) %>%
  group_by(group_id) %>%
  summarise(
    problem_start = first(time[event_tag == "problem"]),
    problem_end = first(time[event_tag == "all fine"])
  ) %>%
  ungroup() %>%
  select(-group_id)

逻辑解释 #

• 用cumsum(event_tag == "problem")为每个problem开启新组；
• 过滤掉无problem的组；
• 按组提取组内最早的problem时间，以及该组内第一个出现的all fine时间；
• 最后移除组ID，保留需要的结果列。

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