分层数据中子合计校验的实现方法求助

需求说明 #

需要为多层级带子合计的数据创建check列，验证子合计的正确性：

• 层级1的合计需等于所有层级2的总和
• 每个上层级（如层级2）的合计需等于紧随其后、下一个更低层级出现前的所有下一层级（如层级3）的总和
• 非子合计行（无后续更高层级数据）标记为NA；子合计行若与对应层级总和匹配则标记TRUE，不匹配则标记FALSE

示例数据 #

df1 <- data.frame(Layer = c(1,2,3,2,3,4,4,3),
                  Total = c(100,40,40,60,40,30,5,20))

原始数据输出：

Layer Total
1     1   100
2     2    40
3     3    40
4     2    60
5     3    40
6     4    30
7     4     5
8     3    20

期望输出 #

Layer Total check
1     1   100  TRUE
2     2    40  TRUE
3     3    40    NA
4     2    60  TRUE
5     3    40 FALSE
6     4    30    NA
7     4     5    NA
8     3    20    NA

完整实现代码 #

使用dplyr包完成逻辑处理，步骤如下：

1. 标记子合计行：判断当前行是否有后续更高层级数据
2. 为每个子合计行确定需要汇总的后续行范围
3. 计算对应层级的总和，与当前行Total比较生成check列

library(dplyr)

df1 %>%
  # 标记子合计行：当前层级 < 下一行层级
  mutate(is_subtotal = Layer < lead(Layer, default = 0)) %>%
  # 为每个子合计行确定后续数据的终止位置（下一个层级≤当前层级的行号）
  mutate(end_pos = ifelse(is_subtotal, 
                          which(Layer <= first(Layer)[cur_group_rows()])[2], 
                          NA),
         .by = cumsum(Layer == first(Layer)[cur_group_rows()] & is_subtotal)) %>%
  # 计算对应层级的总和
  mutate(calculated_sum = ifelse(is_subtotal,
                                 sum(Total[(row_number()+1):(end_pos-1)]),
                                 NA),
         # 生成check验证列
         check = case_when(
           !is_subtotal ~ NA,
           Total == calculated_sum ~ TRUE,
           TRUE ~ FALSE
         )) %>%
  # 移除中间辅助列
  select(-is_subtotal, -end_pos, -calculated_sum)

代码逻辑说明 #

• is_subtotal：通过lead函数判断当前行是否为子合计行（下一行层级更高）
• end_pos：利用分组（cumsum标记子合计组）定位每个子合计对应的后续数据终止位置，即下一个更低层级的行
• calculated_sum：计算子合计行后续对应层级数据的总和
• check：根据Total与calculated_sum的匹配结果生成验证标记

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