如何在R中结合lag与difftime实现集群时间重叠判断及差值计算

数据集示例 #

cluster_id  period_id   clus_start          clus_end
36          SAT119_1    2011-12-08 14:19    2011-12-10 14:18
37          SAT119_1    2011-12-08 18:18    2011-12-10 06:20
40          SAT119_1    2011-12-14 22:19    2011-12-16 22:18
41          SAT119_1    2011-12-17 22:18    2011-12-18 18:18
42          SAT119_1    2011-12-19 05:19    2011-12-30 14:18
47          SAT119_1    2011-12-30 18:19    2012-01-04 22:19
49          SAT119_1    2012-01-06 06:18    2012-01-07 22:19
59          SAT119_1    2012-01-20 02:19    2012-01-21 22:18
61          SAT119_1    2012-01-22 18:18    2012-01-24 14:18
63          SAT119_1    2012-01-26 06:18    2012-01-28 14:20

需求说明 #

• 按顺序检查第n+1个集群的clus_start是否落在第n个集群的clus_end或其12小时后范围内，生成overlap列（是/否），每个period_id的第一个集群默认值为“no”。
• 若第n+1个集群与第n个集群重叠（overlap为yes），则第n+2个集群需与第n个集群进行判断，即重叠集群不参与后续时间差计算的基准。

期望输出示例 #

cluster_id  period_id   clus_start          clus_end          overlap time_diff (days)
36          SAT119_1    2011-12-08 14:19    2011-12-10 14:18  no      NA
37          SAT119_1    2011-12-08 18:18    2011-12-10 06:20  yes     start_37 - end_36
40          SAT119_1    2011-12-14 22:19    2011-12-16 22:18  no      start_40 - end_36
41          SAT119_1    2011-12-17 22:18    2011-12-18 18:18  no      start_41 - end_40
42          SAT119_1    2011-12-19 05:19    2011-12-30 14:18  yes     start_42 - end_41
47          SAT119_1    2011-12-30 18:19    2012-01-04 22:19  no      start_47 - end_41
49          SAT119_1    2012-01-06 06:18    2012-01-07 22:19  no      start_49 - end_47
59          SAT119_1    2012-01-20 02:19    2012-01-21 22:18  no      start_59 - end_49
61          SAT119_1    2012-01-22 18:18    2012-01-24 14:18  no      start_61 - end_59
63          SAT119_1    2012-01-26 06:18    2012-01-28 14:20  no      start_63 - end_61

现有代码局限 #

目前仅能通过difftime()计算集群间时间差，但未考虑重叠逻辑：

df$time_diff <- as.numeric(with(df, difftime(clus_start, ave(clus_end, period_id,FUN=lag), units='days')))

由于需求需要动态调整基准集群（跳过重叠集群），需按period_id分组后逐个遍历处理，维护当前基准的clus_end变量。以下提供两种实现方式：

方式1：基础循环实现（易理解） #

# 先按period_id和clus_start排序数据
df <- df[order(df$period_id, df$clus_start), ]

# 初始化结果列
df$overlap <- "no"
df$time_diff <- NA_real_

# 按period_id分组处理
periods <- unique(df$period_id)
for(p in periods) {
  sub_rows <- df$period_id == p
  row_indices <- which(sub_rows)
  if(length(row_indices) <= 1) next
  
  # 初始基准为当前period第一个集群的结束时间
  base_end <- df$clus_end[row_indices[1]]
  
  for(i in 2:length(row_indices)) {
    current_idx <- row_indices[i]
    current_start <- df$clus_start[current_idx]
    # 计算基准结束后12小时的时间点
    time_limit <- base_end + 12*3600  # 转换为秒
    
    if(current_start <= time_limit) {
      df$overlap[current_idx] <- "yes"
    } else {
      df$overlap[current_idx] <- "no"
      # 更新基准为当前集群的结束时间
      base_end <- df$clus_end[current_idx]
    }
    # 计算时间差
    df$time_diff[current_idx] <- as.numeric(difftime(current_start, base_end, units = "days"))
  }
}

方式2：dplyr + purrr实现（更简洁） #

若熟悉dplyr和purrr包，可使用累积函数维护基准：

library(dplyr)
library(purrr)

df <- df %>%
  arrange(period_id, clus_start) %>%
  group_by(period_id) %>%
  mutate(
    # 累积计算基准结束时间：当前start在基准+12小时内则基准不变，否则更新为当前end
    base_end = accumulate2(clus_start[-1], clus_end[-1],
                          .init = first(clus_end),
                          ~ if(.y <= .x + 12*3600) .x else .z),
    # 判断是否重叠
    overlap = case_when(
      row_number() == 1 ~ "no",
      clus_start <= base_end + 12*3600 ~ "yes",
      TRUE ~ "no"
    ),
    # 计算时间差
    time_diff = case_when(
      row_number() == 1 ~ NA_real_,
      TRUE ~ as.numeric(difftime(clus_start, base_end, units = "days"))
    )
  ) %>%
  ungroup()

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