R语言按ID内episode标识生成新ID并计算滚动累计和

所需依赖 #

仅需dplyr包即可完成所有需求，无需额外复杂函数。

核心逻辑 #

你的数据已经提前完成「按ID分组、按日期排序」的预处理，三个字段的生成逻辑可直接基于现有排序规则实现：

1. ID_3：全数据集范围内，每遇到episode = "new"就序号+1，天然就是全局唯一的就诊阶段ID
2. ID_2：在每个患者ID分组内，每遇到episode = "new"就序号+1，就是患者内部的就诊阶段序号
3. sum_roll：在每个全局唯一就诊阶段分组内，对duration做累加求和，就是阶段内滚动累计时长

之前用rleid不符合需求的核心原因是：rleid仅在变量值发生变化时生成新分组，如果你有连续两条记录的episode都是"new"（比如上一个患者最后一条记录是new，下一个患者第一条记录也是new），rleid会把这两条分到同一个组，完全不符合阶段划分规则。而用cumsum判断是否为new的逻辑，只要出现new就自增1，完美匹配需求。

完整代码 #

# 加载依赖
library(dplyr)

# 原始数据集构造
ID <- c(1,2,3,3,3,3,3,3,3,4,4,5,6)
episode <- c("new","new","new","same","new","same","same","new","same","new","same","new","new")
duration <- c(10,13,11,2,45,66,2,4,45,33,23,12,16)
data1 <- cbind(ID,episode,duration)
data1 <- data.frame(data1)
data1$ID <- factor(data1$ID)
data1$episode <- factor(data1$episode)

# 生成目标字段
data_output <- data1 %>%
  # 生成全局唯一就诊阶段ID ID_3
  mutate(ID_3 = as.factor(cumsum(episode == "new"))) %>%
  # 按患者ID分组，生成患者内部阶段序号ID_2
  group_by(ID) %>%
  mutate(ID_2 = as.factor(cumsum(episode == "new"))) %>%
  # 按全局阶段ID分组，生成滚动累计时长
  group_by(ID_3) %>%
  mutate(sum_roll = cumsum(as.numeric(as.character(duration)))) %>%
  ungroup()

# 验证结果是否匹配预期
ID <- c(1,2,3,3,3,3,3,3,3,4,4,5,6)
episode <- c("new","new","new","same","new","same","same","new","same","new","same","new","new")
duration <- c(10,13,11,2,45,66,2,4,45,33,23,12,16)
ID_2 <- c(1,1,1,1,2,2,2,3,3,1,1,1,1)
ID_3 <- c(1,2,3,3,4,4,4,5,5,6,6,7,8)
sum_roll <- c(10,13,11,13,45,111,113,4,49,33,56,12,16)
data2 <- cbind(ID,episode,duration,ID_2,ID_3,sum_roll)
data2 <- data.frame(data2)
data2$ID <- factor(data2$ID)
data2$episode <- factor(data2$episode)
data2$ID_2 <- factor(data2$ID_2)
data2$ID_3 <- factor(data2$ID_3)

# 对比结果，返回TRUE说明完全匹配
all.equal(data_output, data2, check.attributes = FALSE)

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