R语言分组疾病频率透视：合并行后百分比为0的修正求助

我正在用R开展数据分析工作，生成了包含gender（性别）、status（身份）、country（国家）、disease（患病情况）字段的数据集，代码如下：

set.seed(123)
gender <- c("Male","Female")
gender <- sample(gender, 5000, replace=TRUE, prob=c(0.45, 0.55))
gender <- as.factor(gender)

status <- c("Immigrant","Citizen")
status <- sample(status, 5000, replace=TRUE, prob=c(0.3, 0.7))
status  <- as.factor(status )

country <- c("A", "B", "C", "D")
country <- sample(country, 5000, replace=TRUE, prob=c(0.25, 0.25, 0.25, 0.25))
country  <- as.factor(country)

disease <- c("Yes","No")
disease <- sample(disease, 5000, replace=TRUE, prob=c(0.4, 0.6))
disease <- as.factor(disease)

my_data = data.frame(gender, status, disease, country)

需求 #

需要计算每个唯一分组（性别+身份+国家）中，患病（Yes）与未患病（No）的相对百分比（两者之和为1），例如：

• 来自A国的男性移民中，患病与未患病的占比分别是多少
• 来自A国的男性公民中，患病与未患病的占比分别是多少

尝试过程及问题 #

第一步：统计分组人数及占比 #

首先尝试按分组和患病情况统计人数，并计算分组内相对占比：

library(dplyr)

step_1 = my_data %>% group_by (gender, status, country, disease) %>%
  summarise (n=n()) %>%
  mutate(rel.freq = paste0(round(100 * n/sum(n), 0), "%"))

运行后输出（部分）：

`summarise()` has grouped output by 'gender', 'status', 'country'. You can override using the
`.groups` argument.
# A tibble: 32 x 6
# Groups:   gender, status, country [16]
   gender status    country disease     n rel.freq
   <fct>  <fct>     <fct>   <fct>   <int> <chr>   
 1 Female Citizen   A       No        285 60%     
 2 Female Citizen   A       Yes       193 40%  

第二步：合并Yes/No行（出现错误） #

尝试将每组的Yes和No行合并为一行，编写如下代码：

step_2 = step_1 %>% 
  group_by(gender, status, country) %>% 
  summarize(disease = first(disease),
# 未患病人数
            n_no = sum(disease == "No"),
# 患病人数
            n_yes = sum(disease == "Yes"),
# 未患病相对占比
            n_no_rel_freq = paste(round(sum(disease == "No") / sum(n) * 100), "%"),
# 患病相对占比
            n_yes_rel_freq = paste(round(sum(disease == "Yes") / sum(n) * 100), "%"),
# 该分组占总人群的比例
            overall_percent = sum(n) / sum(step_1$n))

但运行后出现大量百分比为0的错误结果：

# A tibble: 16 x 9
# Groups:   gender, status [4]
   gender status    country disease  n_no n_yes n_no_rel_freq n_yes_rel_freq overall_percent
   <fct>  <fct>     <fct>   <fct>   <int> <int> <chr>         <chr>                    <dbl>
 1 Female Citizen   A       No          1     0 0 %           0 %                     0.102 
 2 Female Citizen   B       No          1     0 0 %           0 %                     0.092 

期望结果格式 #

希望得到如下格式的结果：

# 期望结果示例
  gender  status country n_no n_yes n_no_rel_freq n_yes_rel_freq total overall_percent
1 female citizen       A  285   193           0.6            0.4   478          0.0956

修正方案 #

问题原因 #

第二步代码逻辑错误：sum(disease == "No")统计的是当前分组中disease字段等于"No"的行数，而非对应的n值（即该类别的人数），导致计算出错误的占比。

方法一：使用pivot_wider重塑数据（推荐） #

利用tidyr包的pivot_wider直接将行转为列，逻辑更清晰：

library(dplyr)
library(tidyr)

final_result <- my_data %>%
  # 按分组和患病情况统计人数
  group_by(gender, status, country, disease) %>%
  summarise(n = n(), .groups = "drop_last") %>%
  # 计算分组内的相对占比（小数形式）
  mutate(rel_freq = n / sum(n)) %>%
  # 将Yes/No转为列，重命名列名
  pivot_wider(
    names_from = disease,
    values_from = c(n, rel_freq),
    names_glue = "{.value}_{tolower(disease)}"
  ) %>%
  # 计算分组总人数及占总人群的比例
  mutate(
    total = n_no + n_yes,
    overall_percent = total / nrow(my_data)
  ) %>%
  # 调整列顺序匹配期望格式
  select(gender, status, country, n_no, n_yes, rel_freq_no, rel_freq_yes, total, overall_percent)

方法二：基于step_1修正逻辑 #

如果要基于已有的step_1结果修改，直接提取对应disease的n和rel.freq值：

step_2_fixed <- step_1 %>%
  group_by(gender, status, country) %>%
  summarize(
    n_no = n[disease == "No"],
    n_yes = n[disease == "Yes"],
    n_no_rel_freq = as.numeric(sub("%", "", rel.freq[disease == "No"]))/100,
    n_yes_rel_freq = as.numeric(sub("%", "", rel.freq[disease == "Yes"]))/100,
    total = sum(n),
    overall_percent = total / sum(step_1$n)
  )

修正后结果示例 #

运行方法一代码后，输出结果（部分）如下：

# A tibble: 16 x 9
   gender status    country n_no n_yes rel_freq_no rel_freq_yes total overall_percent
   <fct>  <fct>     <fct>   <int> <int>       <dbl>        <dbl> <int>           <dbl>
 1 Female Citizen   A       285   193        0.60          0.40   478          0.0956
 2 Female Citizen   B       269   191        0.58          0.42   460          0.092 
 3 Female Citizen   C       277   179        0.61          0.39   456          0.0912

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