如何用R的Tidyverse按多列汇总立法证人数据并生成宽表？

针对你的需求，结合Tidyverse工具包，我们可以通过数据清洗→分组统计→格式转换三步实现目标，以下是具体方案：

方案一：直接分组统计（适合固定类别） #

这种方式逻辑直观，适合类别固定的场景：

library(tidyverse)

# 加载原数据
df <- data.frame(bill_number = c(1, 1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 4, 4),
                 witness_id= c("Name1", "Name2", "Name3", "Name4", "Name5", "Name6", "Name7", "Name8", "Name9", "Name10", "Name11", "Name12", "Name13"),
                 witness_race = c("Black", "White", "White", "Black", "White", "White", "White", "Asian", "White", "White White", "Black", "Black", "White"),
                 witness_gender = c("Male", "Male", "Male", "Female", "Male", "Female", "Male", "Male", "Male", "Female", "Female", "Female", "Female"))

# 数据处理与汇总
df1 <- df %>%
  # 修正种族列的异常值：将"White White"改为"White"，保证统计准确
  mutate(witness_race = str_replace(witness_race, "White White", "White")) %>%
  # 按法案编号分组，后续统计基于单个法案
  group_by(bill_number) %>%
  # 统计各类别证人数量
  summarize(
    number_white_witnesses = sum(witness_race == "White"),
    number_black_witnesses = sum(witness_race == "Black"),
    number_asian_witnesses = sum(witness_race == "Asian"),
    number_female_witnesses = sum(witness_gender == "Female"),
    number_male_witnesses = sum(witness_gender == "Male")
  ) %>%
  # 取消分组，恢复普通数据框格式
  ungroup()

# 查看最终结果
df1

步骤说明： #

• mutate(str_replace(...))：修复原始数据中witness_race列的错误值，避免统计偏差。
• group_by(bill_number)：指定按法案编号分组，确保后续统计都是针对单个法案的。
• summarize()：通过sum(条件判断)统计每组内符合要求的证人数量，比如sum(witness_gender == "Female")就是统计当前法案的女性证人总数。
• ungroup()：取消分组标记，避免后续操作受分组状态影响。

方案二：动态转换格式（适合类别可能新增的场景） #

如果后续可能新增种族或性别类别，这种方式无需修改统计逻辑，自动适配新类别：

df1_alternative <- df %>%
  # 先修正异常值
  mutate(witness_race = str_replace(witness_race, "White White", "White")) %>%
  # 将种族、性别列转成长格式，统一处理统计
  pivot_longer(cols = c(witness_race, witness_gender),
               names_to = "category_type",
               values_to = "category_value") %>%
  # 按法案、类别类型、具体值分组计数
  count(bill_number, category_type, category_value) %>%
  # 生成目标列名：比如把"witness_race-White"转为"number_white_witnesses"
  mutate(col_name = str_c("number_", str_to_lower(str_remove(category_type, "witness_")), "_witnesses")) %>%
  # 转成宽表，缺失的类别填充0
  pivot_wider(id_cols = bill_number,
              names_from = col_name,
              values_from = n,
              values_fill = 0) %>%
  # 调整列顺序与期望结果一致
  select(bill_number, number_white_witnesses, number_black_witnesses, number_asian_witnesses, number_female_witnesses, number_male_witnesses)

优势： #

当新增种族（如拉丁裔）或性别类别时，不需要修改统计代码，运行后自动生成对应的统计列。

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