基于R tidyverse实现分层统计并合并数据集的技术咨询

需求与疑问 #

• 在保留DATA3行数的前提下，将DATA2合并到DATA3中
• 能否通过管道操作实现分层统计：先在EL层级计算n()，再在EL分组下进一步计算EL与Black层级的n()，并将上层n()结果展示在下层统计结果中

原始代码 #

library(tidyverse)

DATA <- structure(list(EL = c("Former", "Current", "Former", "Current", 
"Former", "Former", "Current", "Current", "Current", "Current"
), Black = c("No", "No", "No", "No", "No", "No", "Yes", "Yes", 
"No", "No"), Language = c("Spanish", "Spanish", "English", "Vietnamese", 
"Chinese", "Vietnamese", "Spanish", "Amharic", "Romanian", "English"
)), row.names = c(1L, 2L, 6L, 9L, 35L, 38L, 50L, 54L, 61L, 71L
), class = "data.frame")


DATA2 <- DATA %>% group_by(EL) %>% 
  mutate(n_EL_Type = n())


DATA3 <- DATA %>% group_by(EL, Black, Language) %>% 
  summarise(Number = n()) %>% 
  mutate(Total = sum(Number), Percentage = paste0(round(Number/Total*100,2),"%")
  ) %>% 
  arrange(desc(Percentage),Language,EL) %>%
  filter(Language %in% c("Spanish","Amharic"))

解决方案 #

1. 合并DATA2到DATA3（保留DATA3行数） #

DATA3是聚合筛选后的结果，我们先对DATA2去重，保留每个EL-Black-Language组合的唯一统计值，再通过left_join合并，确保行数与DATA3一致：

# 去重DATA2，保留每个分组的唯一n_EL_Type值
DATA2_unique <- DATA2 %>% distinct(EL, Black, Language, .keep_all = TRUE)

# 合并到DATA3
DATA_combined <- DATA3 %>% left_join(DATA2_unique, by = c("EL", "Black", "Language"))

2. 管道实现分层统计 #

可以通过逐步叠加分组的方式，在管道中完成多层级统计，同时保留上层结果：

DATA_hierarchical <- DATA %>%
  # 第一层：按EL分组，计算EL层级总数
  group_by(EL) %>%
  mutate(n_EL = n()) %>%
  # 第二层：叠加Black分组，计算EL+Black层级总数
  group_by(EL, Black, .add = TRUE) %>%
  mutate(n_EL_Black = n()) %>%
  # 第三层：叠加Language分组，聚合统计并保留上层数值
  group_by(EL, Black, Language, n_EL, n_EL_Black, .add = TRUE) %>%
  summarise(Number = n()) %>%
  mutate(
    Total = sum(Number),
    Percentage = paste0(round(Number/Total*100, 2), "%")
  ) %>%
  arrange(desc(Percentage), Language, EL) %>%
  filter(Language %in% c("Spanish", "Amharic"))

最终结果会同时包含EL层级总数n_EL和EL+Black层级总数n_EL_Black，全程通过管道串联完成，无需拆分多个中间数据框。

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