如何在R中为同组连续衔接的日期记录生成事件ID

需求说明 #

现有包含分组、开始日期、结束日期的数据集，规则为同组内若前一条记录的结束日期与后一条记录的开始日期衔接，则这些记录属于同一事件，需要新增eventID字段标记，后续可基于eventID生成分组-事件联合ID，按该ID汇总outcome字段。

示例数据 #

library(tidyverse)
A <- data.frame(group = c("A", "A", "B", "C", "C", "C", "D", "D", "D", "E", "E", "E", "E"),
           startdate = c("2019-01-01", "2019-01-03", "2019-01-25", "2019-01-19", "2019-01-20", "2019-01-22", "2019-01-09", "2019-01-11", "2019-01-27", "2019-01-02", "2019-01-03", "2019-01-09", "2019-01-12"),
           enddate = c("2019-01-03", "2019-01-05", "2019-01-26", "2019-01-20", "2019-01-22", "2019-01-23", "2019-01-11", "2019-01-12", "2019-02-01", "2019-01-03", "2019-01-05", "2019-01-12", "2019-01-13")) %>% 
  mutate(outcome = c(runif(nrow(.))))
A

打印输出如下：

group  startdate    enddate     outcome
1      A 2019-01-01 2019-01-03 0.955011991
2      A 2019-01-03 2019-01-05 0.476095975
3      B 2019-01-25 2019-01-26 0.005301758
4      C 2019-01-19 2019-01-20 0.179261809
5      C 2019-01-20 2019-01-22 0.688228826
6      C 2019-01-22 2019-01-23 0.248906204
7      D 2019-01-09 2019-01-11 0.151737360
8      D 2019-01-11 2019-01-12 0.503649969
9      D 2019-01-27 2019-02-01 0.310691439
10     E 2019-01-02 2019-01-03 0.152001235
11     E 2019-01-03 2019-01-05 0.434751253
12     E 2019-01-09 2019-01-12 0.510411369
13     E 2019-01-12 2019-01-13 0.214607322

预期输出 #

新增eventID列后的数据如下：

group  startdate    enddate     outcome eventID
1      A 2019-01-01 2019-01-03 0.955011991       1
2      A 2019-01-03 2019-01-05 0.476095975       1
3      B 2019-01-25 2019-01-26 0.005301758       1
4      C 2019-01-19 2019-01-20 0.179261809       1
5      C 2019-01-20 2019-01-22 0.688228826       1
6      C 2019-01-22 2019-01-23 0.248906204       1
7      D 2019-01-09 2019-01-11 0.151737360       1
8      D 2019-01-11 2019-01-12 0.503649969       1
9      D 2019-01-27 2019-02-01 0.310691439       2
10     E 2019-01-02 2019-01-03 0.152001235       1
11     E 2019-01-03 2019-01-05 0.434751253       1
12     E 2019-01-09 2019-01-12 0.510411369       2
13     E 2019-01-12 2019-01-13 0.214607322       2

现有问题 #

现有代码无法处理同组内有多段连续衔接日期的场景（比如示例中的E组），原有代码如下：

library(tidyverse)
A %>% 
  group_by(group) %>% 
  mutate(datediff = as.Date(startdate) - lag(as.Date(enddate))) %>% 
  mutate(eventID = ifelse(is.na(datediff)|datediff == 0, 1,n())) %>% 
  mutate(newID = paste0(group, eventID)) %>% 
  group_by(newID) %>% 
  summarise(outcome = sum(outcome))

正确实现方案 #

核心思路是用cumsum()累加不衔接的标记，每出现一次不衔接的情况，事件ID就+1：

A %>%
  # 先按分组排序，保证日期顺序正确
  group_by(group) %>%
  arrange(startdate, .by_group = TRUE) %>%
  # 计算当前行开始日期和上一行结束日期的差值
  mutate(
    startdate = as.Date(startdate),
    enddate = as.Date(enddate),
    date_gap = startdate - lag(enddate, default = as.Date("1970-01-01"))
  ) %>%
  # 只要gap大于0就说明是新事件，累加计数生成eventID
  mutate(eventID = cumsum(date_gap > 0)) %>%
  # 生成联合ID并汇总
  mutate(newID = paste0(group, eventID)) %>%
  group_by(newID) %>%
  summarise(
    group = first(group),
    eventID = first(eventID),
    total_outcome = sum(outcome),
    .groups = "drop"
  )

如果只需要生成带eventID的明细数据，不需要后续汇总，只需要执行到mutate(eventID = cumsum(date_gap > 0))这一步即可。

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