如何用dplyr处理行数非3倍数的DataFrame按Chr每3行聚合求和

需求说明 #

按Chr字段分组，对每组内的数据按每3行进行求和聚合；若分组内行数无法被3整除：

• 剩余2行时，对这2行求和
• 剩余1行时，单独保留该行的求和值（即自身值）
  要求使用dplyr的mutate函数处理，且保留原始数据的所有列。

输入数据 #

df <- data.frame(Chr = c("chr1","chr1","chr1","chr1","chr1","chr2","chr2","chr2","chr2","chr2","chr3"),
           value = c(1,3,1,3,5,6,3,1,3,5,0),
           seq = c(1,2,3,4,5,1,2,3,4,5,6))

解决方案代码 #

library(dplyr)

df_result <- df %>%
  group_by(Chr) %>%
  mutate(
    # 创建分组内的分段标记：前n%/%3*3行每3个一组，剩余行按规则成组
    group_id = case_when(
      row_number() <= (n() %/% 3) * 3 ~ ceiling(row_number() / 3),
      n() %% 3 == 2 ~ (n() %/% 3) + 1,
      TRUE ~ (n() %/% 3) + row_number() - (n() %/% 3) * 3
    )
  ) %>%
  group_by(Chr, group_id) %>%
  mutate(agg = sum(value)) %>%
  ungroup() %>%
  select(-group_id) # 移除临时分组标记列

验证输出 #

运行上述代码后，结果与期望输出一致：

data.frame(Chr = c("chr1","chr1","chr1","chr1","chr1","chr2","chr2","chr2","chr2","chr2","chr3"),
           value = c(1,3,1,3,5,6,3,1,3,5,0),
           seq = c(1,2,3,4,5,1,2,3,4,5,6),
           agg = c(5,5,5,8,8,10,10,10,8,8,8))

思路说明 #

1. 先按Chr分组，计算每组内的行号和总行数
2. 通过case_when生成分段标记group_id：
   • 前n()%/%3*3行按每3行一组分配标记
   • 若剩余2行，将这两行标记为同一组；若剩余1行，单独标记
3. 再按Chr和group_id分组，用mutate计算每组的value求和值agg
4. 最后取消分组并移除临时标记列，保留所有原始列

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