基于传感器分组过滤包含连续Alarm时间区间的Alert行
2026-8-9
问题需求
我有如下DataFrame：
Sensor Event Start.Time End.Time 1 1 Alert 05/01/2022 03:50:00 05/01/2022 04:10:00 2 1 Alarm 05/01/2022 03:55:00 05/01/2022 04:03:00 3 2 Alert 05/01/2022 03:56:00 05/01/2022 04:00:00 4 2 Alarm 06/02/2022 12:31:00 06/02/2022 12:54:00 5 1 Alert 14/01/2022 22:44:00 15/01/2022 00:40:00 6 1 Alarm 14/01/2022 22:36:00 15/01/2022 00:15:00 7 2 Alert 06/02/2022 12:25:00 06/02/2022 13:01:00 8 4 Alarm 05/03/2022 15:00:00 05/03/2022 15:30:00 9 3 Alert 07/04/2022 16:25:00 07/04/2022 16:38:00 10 3 Alarm 07/04/2022 16:27:00 07/04/2022 16:40:00 11 4 Alert 08/05/2022 19:00:00 08/05/2022 19:33:00 12 4 Alarm 08/05/2022 19:12:00 08/05/2022 19:21:00 13 1 Alarm 14/01/2022 22:46:00 15/01/2022 00:12:00 14 2 Alert 11/06/2022 19:00:00 11/06/2022 20:34:00 15 2 Alarm 11/06/2022 19:21:00 11/06/2022 20:12:00
该DataFrame由以下R代码生成：
st <- c(20220105035000,20220105035500,20220105035600,20220206123100,20220114224400,20220114223600,20220206122500, 20220305150000,20220407162500,20220407162700,20220508190000,20220508191200,20220114224600,20220611190000, 20220611192100) et <-c(20220105041000,20220105040300,20220105040000,20220206125400,20220115004000,20220115001500, 20220206130100,20220305153000,2020407163800,20220407164000,20220508193300, 20220508192100,20220115001200,20220611203400,20220611201200) df <- data.frame(Sensor=c(1,1,2,2,1,1,2,4,3,3,4,4,1,2,2), Event=c("Alert","Alarm","Alert","Alarm","Alert","Alarm","Alert","Alarm", "Alert","Alarm","Alert","Alarm","Alarm","Alert","Alarm"), Start.time=c(ymd_hms(st)), End.time=c(ymd_hms(et)))
需要删除所有满足以下条件的Alert行：按Sensor分组后，该Alert的时间区间包含了时间上连续的前一个/后一个Alarm行的时间区间。
期望得到的结果如下：
Sensor Event Start.Time End.Time 2 1 Alarm 05/01/2022 03:55:00 05/01/2022 04:03:00 3 2 Alert 05/01/2022 03:56:00 05/01/2022 04:00:00 4 2 Alarm 06/02/2022 12:31:00 06/02/2022 12:54:00 5 1 Alert 14/01/2022 22:44:00 15/01/2022 00:40:00 6 1 Alarm 14/01/2022 22:36:00 15/01/2022 00:15:00 8 4 Alarm 05/03/2022 15:00:00 05/03/2022 15:30:00 9 3 Alert 07/04/2022 16:25:00 07/04/2022 16:38:00 10 3 Alarm 07/04/2022 16:27:00 07/04/2022 16:40:00 11 4 Alert 08/05/2022 19:00:00 08/05/2022 19:33:00 13 1 Alarm 14/01/2022 22:46:00 15/01/2022 00:12:00 15 2 Alarm 11/06/2022 19:21:00 11/06/2022 20:12:00
解决方案
可以使用dplyr包按Sensor分组，通过lag()和lead()函数获取前后行的Alarm时间区间，判断当前Alert是否包含这些区间，最后过滤掉符合删除条件的行。
代码实现
library(dplyr) library(lubridate) # 修正原始数据笔误：et向量里的2020407163800应为20220407163800，否则会生成错误时间 et <-c(20220105041000,20220105040300,20220105040000,20220206125400,20220115004000,20220115001500, 20220206130100,20220305153000,20220407163800,20220407164000,20220508193300, 20220508192100,20220115001200,20220611203400,20220611201200) df <- data.frame(Sensor=c(1,1,2,2,1,1,2,4,3,3,4,4,1,2,2), Event=c("Alert","Alarm","Alert","Alarm","Alert","Alarm","Alert","Alarm", "Alert","Alarm","Alert","Alarm","Alarm","Alert","Alarm"), Start.time=c(ymd_hms(st)), End.time=c(ymd_hms(et))) result_df <- df %>% group_by(Sensor) %>% mutate( # 获取前一行的Alarm时间区间，非Alarm行则标记为NA prev_alarm_start = ifelse(lag(Event) == "Alarm", lag(Start.time), NA), prev_alarm_end = ifelse(lag(Event) == "Alarm", lag(End.time), NA), # 获取后一行的Alarm时间区间，非Alarm行则标记为NA next_alarm_start = ifelse(lead(Event) == "Alarm", lead(Start.time), NA), next_alarm_end = ifelse(lead(Event) == "Alarm", lead(End.time), NA), # 判断当前Alert是否需要删除：包含前一个或后一个Alarm的时间区间 to_remove = Event == "Alert" & ( (!is.na(prev_alarm_start) & Start.time <= prev_alarm_start & End.time >= prev_alarm_end) | (!is.na(next_alarm_start) & Start.time <= next_alarm_start & End.time >= next_alarm_end) ) ) %>% filter(!to_remove) %>% # 清理辅助计算列 select(-prev_alarm_start, -prev_alarm_end, -next_alarm_start, -next_alarm_end, -to_remove) %>% ungroup() # 查看最终结果 print(result_df)
代码说明
- 数据修正：原始生成代码中
et向量存在年份笔误，修正后才能生成正确的时间格式。 - 分组处理：按
Sensor分组，确保仅在同一传感器范围内判断前后事件的关联。 - 区间获取：利用
lag()和lead()函数分别提取当前行的前、后行Alarm时间区间，非Alarm行则标记为NA避免干扰判断。 - 删除逻辑：如果当前行是Alert，且其时间区间完全包含前一个或后一个Alarm的区间，则标记为待删除。
- 结果过滤：移除标记为待删除的行，清理辅助列后得到目标结果。
内容的提问来源于stack exchange，提问作者AeonRed
相关产品推荐
相关产品推荐
