按分组计算相对百分比：城市年度疫苗接种占比求解

数据集 #

my_data = structure(list(state = c("State A", "State A", "State A", "State A", 
"State B", "State B", "State B", "State B", "State A", "State A", 
"State A", "State A", "State B", "State B", "State B", "State B"
), city = c("city 1", "city 1", "city 2", "city 2", "city 3", 
"city 3", "city 4", "city 4", "city 1", "city 1", "city 2", "city 2", 
"city 3", "city 3", "city 4", "city 4"), vaccine = c("yes", "no", 
"yes", "no", "yes", "no", "yes", "no", "yes", "no", "yes", "no", 
"yes", "no", "yes", "no"), counts = c(1221, 2233, 1344, 887, 
9862, 2122, 8772, 2341, 1221, 2233, 1344, 887, 9862, 2122, 8772, 
2341), year = c(2021, 2021, 2021, 2021, 2021, 2021, 2021, 2021, 
2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022)), row.names = c(NA, 
-16L), class = "data.frame")

需求 #

计算每年每个城市中接种疫苗人群的占比，结果需包含state、city、vaccine、Relative_Percentage、year字段。

原代码问题分析 #

你提供的代码存在两个核心错误：

• 错误将vaccine纳入分组条件，导致sum(n)仅计算当前疫苗状态下的行数，而非整个城市的总人数，因此所有比例都为0.5。
• 使用n()统计行数而非数据中的counts列，你的数据是汇总后的统计数据（每行对应一个类别下的人数），而非原始个体数据，因此直接用行数计算完全不符合需求。

修正后的代码 #

library(dplyr)

# 计算每年每个城市的疫苗接种占比
my_data %>%
  # 仅按年、州、城市分组，确保sum(counts)是该城市该年的总人数
  group_by(year, state, city) %>%
  # 计算当前疫苗状态的人数占总人数的比例
  mutate(Relative_Percentage = counts / sum(counts)) %>%
  # 可选：取消分组，转为普通数据框
  ungroup() %>%
  # 按需求调整列顺序（可选）
  select(state, city, vaccine, Relative_Percentage, year)

部分结果示例 #

# 输出前4行结果
     state   city vaccine Relative_Percentage year
1  State A city 1     yes             0.3532 2021
2  State A city 1      no             0.6468 2021
3  State A city 2     yes             0.6033 2021
4  State A city 2      no             0.3967 2021

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