R语言使用dplyr分组汇总数据框并统计跨组唯一值数量

问题描述 #

现有R数据框df，结构如下：

structure(list(CN = c("BR", "BR", "BR", "PL", "PL", "PL", 
"BR", "BR", "BR", "BR", "PL", "PL", "PL"), Year = c(2019, 
2019, 2019, 2019, 2019, 2019, 2020, 2020, 2020, 2020, 2020, 2020, 
2020), Squad = c("A", "B", "C", "A", "B", "C", "C", "F", "G", 
"I", "D", "E", "F"), X = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 
1), Y = c(1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1)), row.names = c(NA, 
-13L), class = c("tbl_df", "tbl", "data.frame"))

需要实现的计算逻辑：

• 按CN、Year两个字段分组，计算每组X与Y的总和、每组包含的Squad数量
• 在汇总结果中新增一列，统计仅按CN字段分组时，Squad字段的去重唯一值数量

期望输出结构如下：

structure(list(CN = c("BR", "BR", "PL", "PL"), Year = c(2019, 
2020, 2019, 2020), Sum = c(12, 14, 12, 12), n_squad = c(3, 4, 
3, 3), n_squad_distinct = c(6, 6, 6, 6)), row.names = c(NA, -4L
), class = c("tbl_df", "tbl", "data.frame"))

注：示例结果中n_squad_distinct全为6是因为样例数据里BR、PL两个CN下的Squad去重后刚好各有6个，不是固定常量，代码会根据实际数据自动计算对应值。

实现代码 #

写法1：单链路分组控制（dplyr 1.0.0+版本支持） #

不需要额外做表连接，直接通过分组参数控制计算层级：

library(dplyr)

result <- df %>%
  group_by(CN, Year) %>%
  summarise(
    Sum = sum(X, Y),
    n_squad = n(),
    .groups = "drop_last" # 双维度汇总后自动保留CN层级分组，丢弃Year分组
  ) %>%
  mutate(
    n_squad_distinct = n_distinct(df$Squad[df$CN == cur_group()$CN])
  ) %>%
  ungroup()

写法2：预计算关联（全版本兼容） #

如果使用较低版本dplyr，可以先计算CN维度的去重计数，再关联到双维度汇总结果上，逻辑更直观：

library(dplyr)

# 第一步：单独计算CN维度的Squad去重数
cn_squad_stat <- df %>%
  group_by(CN) %>%
  summarise(n_squad_distinct = n_distinct(Squad))

# 第二步：计算CN+Year维度的细粒度汇总，最后关联CN维度指标
result <- df %>%
  group_by(CN, Year) %>%
  summarise(
    Sum = sum(X, Y),
    n_squad = n(),
    .groups = "drop"
  ) %>%
  left_join(cn_squad_stat, by = "CN")

两种写法运行结果完全一致，和给出的期望输出结构、数值完全匹配。

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