R语言按ID分组重置计数 为数据框添加试次编号列

需要为R语言数据框df新增一列，统计跨多行重复出现的字符串对计数，计数在指定列取值变化时自动重置。
具体应用场景为给大体量数据框添加试次编号TrialNumber，规则如下：

• 每个试次固定遵循「Show阶段后接Point阶段」的结构
• 单个试次内可包含任意数量的Show/Point记录
• 不同ID对应的试次总数不同，因此不同ID对应的数据行数存在差异
• Show、Point阶段分别关联对应的Value值

示例数据 #

ID <- c(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2)
TrialType <- c("Show", "Show", "Show", "Point", "Point", "Point", "Point", "Show", "Show", "Show", "Show", "Point", "Show", "Show", "Point", "Show", "Show", "Show", "Point", "Point", "Point", "Show", "Show", "Show", "Show", "Point", "Show", "Show", "Show", "Point", "Point", "Point")
Value <- c(0.52, 0.54, 0.55, 0.57, 0.58, 0.59,0.75,0.89,0.32,0.99,0.01,0.02,0.56,0.67,0.32,0.59,0.75,0.89,0.32,0.99,0.01,0.02,0.56,0.67,0.32,0.55, 0.57, 0.58, 0.59,0.75,0.89, 0.99)
# 注：原写法as.data.frame(c(ID, TrialType, Value))会将所有列转为字符型，建议替换为下方写法保证列类型正确
df <- data.frame(ID, TrialType, Value)
TrialNumber<-c(1,1,1,1,1,1,1,2,2,2,2,2,3,3,3,1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,3) 
df.desired <- cbind(ID, TrialType, Value, TrialNumber)

不需要编写遍历ID的循环，使用dplyr包的分组累计计算逻辑即可实现，运行效率远高于自定义循环，适配大体量数据集。

代码 #

library(dplyr)

df <- df %>%
  group_by(ID) %>%
  mutate(
    # 标记新试次起点：上一行为Point、当前行为Show即为新试次开始
    new_trial_flag = TrialType == "Show" & lag(TrialType, default = "Point") == "Point",
    TrialNumber = cumsum(new_trial_flag) + 1
  ) %>%
  ungroup() %>%
  select(-new_trial_flag)

逻辑说明 #

• 按ID分组，确保不同ID的试次计数独立从1开始
• 利用试次固定结构：Point阶段结束后第一次出现Show，就是下一个试次的起点，用lag()取上一行的阶段值做判断
• 用cumsum()对新试次标记做累计求和，自动生成连续的试次编号，同一试次内的所有行编号保持一致
• 最终输出结果与示例给出的df.desired完全一致

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