基于R语言dplyr识别各ID连续阶段的起止日期

样本数据 #

首先构造符合需求的示例数据：

library(dplyr)

sample_data <- tibble(
  ID = c(1,1,1,1,1,2,2,2,2),
  Date = as.Date(c("2023-01-01", "2023-01-02", "2023-01-03", "2023-01-04", "2023-01-05",
                   "2023-02-01", "2023-02-02", "2023-02-03", "2023-02-04")),
  Stages = c("A", "A", "B", "B", "A", "X", "Y", "Y", "X")
)

解决方案 #

核心思路是为每个ID内连续相同的Stages创建分组，再对分组聚合计算起止日期。

方法1：结合dplyr与data.table的rleid函数 #

rleid()可以快速生成连续重复值的分组ID，代码简洁高效：

library(data.table)

result <- sample_data %>%
  group_by(ID) %>%
  mutate(group_id = rleid(Stages)) %>% # 生成连续阶段的分组ID
  group_by(ID, Stages, group_id) %>%
  summarise(
    Start_Date = min(Date),
    End_Date = max(Date),
    .groups = "drop"
  ) %>%
  select(-group_id) %>% # 移除临时分组ID
  arrange(ID, Start_Date)

print(result)

方法2：纯dplyr实现 #

如果不想依赖data.table，可以用dplyr的窗口函数手动标记分组：

result_dplyr <- sample_data %>%
  group_by(ID) %>%
  mutate(
    # 标记新分组的起点：第一行或当前Stages与前一行不同
    is_new_group = ifelse(row_number() == 1, TRUE, Stages != lag(Stages)),
    group_id = cumsum(is_new_group) # 累加生成分组ID
  ) %>%
  group_by(ID, Stages, group_id) %>%
  summarise(
    Start_Date = min(Date),
    End_Date = max(Date),
    .groups = "drop"
  ) %>%
  select(-group_id) %>%
  arrange(ID, Start_Date)

print(result_dplyr)

预期输出 #

两种方法都会得到以下结果：

# A tibble: 6 × 4
     ID Stages Start_Date End_Date  
  <dbl> <chr>  <date>     <date>    
1     1 A      2023-01-01 2023-01-02
2     1 B      2023-01-03 2023-01-04
3     1 A      2023-01-05 2023-01-05
4     2 X      2023-02-01 2023-02-01
5     2 Y      2023-02-02 2023-02-03
6     2 X      2023-02-04 2023-02-04

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