为有序DataFrame的事件区间添加唯一event_id列（适配dplyr）

需求概述 #

• 为GPS事件数据的DataFrame添加event_id列，唯一标识每个从event_start到对应GPS_fix的完整事件区间
• 规则细节：
  • 正常结束（event_end）对应1-2个GPS_fix，event_id需覆盖从event_start到所有对应GPS_fix的行
  • 中断结束（event_endint）可能无GPS_fix或仅1个：无GPS_fix时，event_id延续至下一个GPS_fix；有1个时覆盖至该行
  • 需支持多设备区分，代码适配dplyr生态

示例数据 #

df <- structure(list(date = c("1/01/2000", "1/01/2000", "1/01/2000", 
"1/01/2000", "1/01/2000", "1/01/2000", "1/01/2000", "1/01/2000", 
"2/01/2000", "2/01/2000", "2/01/2000", "2/01/2000", "3/01/2000", 
"3/01/2000", "3/01/2000", "5/01/2000", "5/01/2000", "5/01/2000", 
"5/01/2000", "5/01/2000", "5/01/2000", "5/01/2000", "5/01/2000", 
"5/01/2000", "5/01/2000", "5/01/2000", "5/01/2000", "5/01/2000", 
"5/01/2000"), time = c("1:00", "2:00", "3:00", "4:00", "5:00", 
"6:00", "6:01", "6:02", "20:00", "21:00", "22:00", "23:00", "0:00", 
"1:00", "6:01", "14:00", "15:00", "16:00", "17:00", "18:00", 
"19:00", "14:00", "15:00", "16:00", "17:00", "18:00", "19:00", 
"19:01:00 am", "19:02:00 am"), datatype = c("event_start", "event", 
"event", "event", "event", "event_end", "GPS_fix", "GPS_fix", 
"event_start", "event", "event", "event", "event", "event_endint", 
"GPS_fix", "event_start", "event", "event", "event", "event", 
"event_endint", "event_start", "event", "event", "event", "event", 
"event_end", "GPS_fix", "GPS_fix"), lat = c(NA, NA, NA, NA, NA, 
NA, 42.1, 42.05, NA, NA, NA, NA, NA, NA, 43.1, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, 50, 50), long = c(NA, NA, NA, 
NA, NA, NA, 180.1, 180.1, NA, NA, NA, NA, NA, NA, 181, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 170, 170)), class = "data.frame", row.names = c(NA, 
-29L))

dplyr实现代码 #

library(dplyr)

# 若数据包含设备列（如device），需先按设备分组，示例无设备列故注释
# df <- df %>% group_by(device) %>% 
df <- df %>%
  # 生成每个event_start的递增种子值，作为event_id的基础
  mutate(event_seed = cumsum(datatype == "event_start")) %>%
  # 初始化event_id为种子值
  mutate(event_id = event_seed) %>%
  # 向下填充event_id，覆盖事件过程中的event行
  fill(event_id, .direction = "down") %>%
  # 标记事件结束点
  mutate(event_end_flag = datatype %in% c("event_end", "event_endint")) %>%
  # 按事件种子分组，为对应GPS_fix分配正确的event_id
  group_by(event_seed) %>%
  mutate(
    # 获取当前事件的结束位置索引
    end_idx = which(event_end_flag)[1],
    # 确定当前事件对应的GPS_fix行范围（结束点后1-2行）
    gps_indices = if (!is.na(end_idx)) {
      (end_idx + 1):min(end_idx + 2, n())
    } else {
      integer(0)
    },
    # 为GPS_fix行设置当前事件的ID
    event_id = ifelse(row_number() %in% gps_indices, event_seed, event_id)
  ) %>%
  ungroup() %>%
  # 清理临时辅助列
  select(-event_seed, -event_end_flag, -end_idx, -gps_indices)

代码逻辑说明 #

1. 生成事件种子：通过cumsum统计event_start出现次数，生成唯一递增的事件种子值
2. 填充事件过程ID：用fill将种子值向下覆盖所有事件过程中的event行
3. 关联GPS_fix：按事件分组，定位每个事件结束点后的1-2行（对应GPS_fix），将这些行的event_id绑定到当前事件种子，确保完整覆盖事件区间
4. 多设备适配：若数据包含设备标识列，添加group_by(device)即可实现不同设备间event_id独立递增

内容的提问来源于stack exchange，提问作者André.B