基于时间差与重复FT值在R中创建序列列的技术求助

需求说明 #

现有包含相机陷阱标识FT、时间戳DATAORA、相邻记录时间差DIFFTIME的数据框，需要新增SEQ列实现以下分组规则：

• 相邻记录时间差不超过45秒的行归为同一组
• 若当前行的FT值与所在组内已有的FT值重复，则将该行归入新组

输入数据 #

input <- structure(list(FT = structure(c(4L, 4L, 1L, 2L, 1L, 4L, 3L, 3L, 
1L, 1L, 2L, 1L, 3L, 3L, 2L, 1L, 3L, 2L, 1L, 2L, 1L, 1L, 4L, 3L, 
2L, 1L), levels = c("T1", "C1a", "C1b", "C1c", "T2", "C2b", "C2c", 
"T3", "C3a", "C3b", "T4", "C4a", "C4b", "C4c", "T5", "C5a", "C5b", 
"C5c", "T6", "C6a", "C6b", "C6c", "T7", "C7a", "C7b", "C7c", 
"T8", "C8a", "C8b", "C8c", "T9", "C9a", "C9b", "C9c", "T10", 
"C10a", "C10b", "C10c", "T11", "C11a", "C11b", "C11c", "T12", 
"C12a"), class = "factor"), DATAORA = structure(c(1668717618, 
1668717644, 1668717750, 1668717790, 1668717806, 1668719092, 1668719110, 
1668719142, 1668719156, 1668719182, 1668719192, 1668719214, 1668800778, 
1668800808, 1668800832, 1668800846, 1668800856, 1668800860, 1668800872, 
1668800888, 1668800898, 1668800926, 1668809108, 1668809170, 1668809226, 
1668809238), tzone = "", class = c("POSIXct", "POSIXt")), DIFFTIME = c(0, 
26, 106, 40, 16, 1286, 18, 32, 14, 26, 10, 22, 81564, 30, 24, 
14, 10, 4, 12, 16, 10, 28, 8182, 62, 56, 12)), row.names = c(NA, 
-26L), class = c("tbl_df", "tbl", "data.frame"))

期望结果 #

expected <- structure(list(FT = c("C1c", "C1c", "T1", "C1a", "T1", "C1c", 
"C1b", "C1b", "T1", "T1", "C1a", "T1", "C1b", "C1b", "C1a", "T1", 
"C1b", "C1a", "T1", "C1a", "T1", "T1", "C1c", "C1b", "C1a", "T1"
), DATAORA = c("2022-11-17 21:40:18", "2022-11-17 21:40:44", 
"2022-11-17 21:42:30", "2022-11-17 21:43:10", "2022-11-17 21:43:26", 
"2022-11-17 22:04:52", "2022-11-17 22:05:10", "2022-11-17 22:05:42", 
"2022-11-17 22:05:56", "2022-11-17 22:06:22", "2022-11-17 22:06:32", 
"2022-11-17 22:06:54", "2022-11-18 20:46:18", "2022-11-18 20:46:48", 
"2022-11-18 20:47:12", "2022-11-18 20:47:26", "2022-11-18 20:47:36", 
"2022-11-18 20:47:40", "2022-11-18 20:47:52", "2022-11-18 20:48:08", 
"2022-11-18 20:48:18", "2022-11-18 20:48:46", "2022-11-18 23:05:08", 
"2022-11-18 23:06:10", "2022-11-18 23:07:06", "2022-11-18 23:07:18"
), DIFFTIME = c(0, 26, 106, 40, 16, 1286, 18, 32, 14, 26, 10, 
22, 81564, 30, 24, 14, 10, 4, 12, 16, 10, 28, 8182, 62, 56, 12
), SEQ = c(1, 2, 3, 3, 4, 5, 5, 6, 6, 7, 7, 8, 9, 10, 10, 10, 
11, 11, 11, 12, 12, 13, 14, 15, 16, 16)), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -26L))

解决方案 #

通过逐行累积追踪当前组的FT集合和分组编号，结合dplyr和purrr实现规则：

library(dplyr)
library(purrr)

# 处理数据生成SEQ列
result <- input %>%
  mutate(
    # 累积计算每组状态：包含当前组的FT集合和组号
    group_status = accumulate(
      1:nrow(.),
      function(prev_state, curr_row) {
        if (curr_row == 1) {
          # 第一行初始化第一组
          list(ft_set = FT[curr_row], seq_num = 1)
        } else {
          # 判断是否满足分组条件：时间差≤45秒，且当前FT不在组内
          time_condition <- DIFFTIME[curr_row] <= 45
          ft_condition <- !FT[curr_row] %in% prev_state$ft_set
          
          if (time_condition && ft_condition) {
            # 满足条件，加入当前组
            list(ft_set = c(prev_state$ft_set, FT[curr_row]), seq_num = prev_state$seq_num)
          } else {
            # 不满足条件，开启新组
            list(ft_set = FT[curr_row], seq_num = prev_state$seq_num + 1)
          }
        }
      },
      .init = NULL
    )
  ) %>%
  # 提取SEQ列并清理临时状态列
  mutate(SEQ = map_int(group_status, ~ .$seq_num)) %>%
  select(-group_status)

# 验证结果与期望是否一致
all.equal(result$SEQ, expected$SEQ)

逻辑说明 #

1. 使用accumulate函数逐行迭代，追踪每个分组的已包含FT集合和当前组号
2. 对每一行同时检查两个分组条件：相邻时间差≤45秒、当前FT未在组内出现
3. 满足双条件则留在当前组，否则创建新组
4. 最后从累积状态中提取SEQ列，得到目标结果

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