R按ID和Outcome分组提取各阶段最早日期并转换为序列命名宽表

实现思路 #

1. 先将日期列转换为日期格式，按患者ID和日期排序，保证时间序列顺序正确
2. 按患者分组，标记出结局（Outcome）发生变化的节点，作为新阶段的起始标识
3. 按患者、阶段分组，提取每个阶段最早的日期作为该阶段的启动日期
4. 给同一位患者下相同类型的结局按出现顺序编号，拼接为CR1、Relapse1这类列名
5. 将长格式数据转为宽格式，得到最终结果

完整实现代码 #

# 未安装依赖包可先运行 install.packages("tidyverse")
library(tidyverse)

# 原始数据集
mydata = data.frame (Id =c (1,1,1,1,1,1,1,1,2,2,2,2),
                     Date = c("2001-01-31", "2001-02-13","2001-05-31",
                              "2001-06-02","2018-01-31","2018-03-31","2018-07-31",
                              "2019-04-04","2014-01-31","2014-02-02","2014-04-31",
                              "2014-05-18"),
                     Outcome = c("CR","CR","Relapse","Relapse",
                                 "CR","CR","CR","Relapse","CR", "CR","Relapse","CR"))

# 数据处理流程
final_result <- mydata %>%
  # 转换日期格式、按ID和日期排序保证顺序正确
  mutate(Date = as.Date(Date)) %>%
  arrange(Id, Date) %>%
  # 按患者分组标记新阶段
  group_by(Id) %>%
  mutate(phase_change = Outcome != lag(Outcome, default = first(Outcome)),
         phase_id = cumsum(phase_change)) %>%
  ungroup() %>%
  # 提取每个阶段的最早启动日期
  group_by(Id, phase_id, Outcome) %>%
  summarise(start_date = min(Date), .groups = "drop") %>%
  # 生成带序号的阶段名
  group_by(Id, Outcome) %>%
  mutate(phase_name = paste0(Outcome, row_number())) %>%
  ungroup() %>%
  # 转换为宽表得到最终结果
  pivot_wider(id_cols = Id,
              names_from = phase_name,
              values_from = start_date)

# 输出查看结果
print(final_result)

运行结果 #

# A tibble: 2 × 5
     Id CR1        Relapse1   CR2        Relapse2  
  <dbl> <date>     <date>     <date>     <date>    
1     1 2001-01-31 2001-05-31 2018-01-31 2019-04-04
2     2 2014-01-31 2014-04-30 2014-05-18 NA   

注：原数据中2014-04-31为无效日期，R会自动转换为2014-04-30，如果需要保留原始字符串格式的日期，可跳过as.Date转换步骤，直接用字符串排序取最小值即可。

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