使用dplyr::count后，如何按性别计算吸烟状态的列总计百分比？

我需要按性别计算各类吸烟状态的百分比（例如男性/女性群体中，不吸烟者、偶尔吸烟者、不愿透露、经常吸烟者等的占比），但当前代码是基于行总计计算的，不符合需求，求解决方法。

数据框示例 #

structure(list(sex = c("Female", "Male", "Female", "Female"), 
    cigarettes_smoking_status = c("Non-smoker", "Non-smoker", 
    "Non-smoker", "Non-smoker")), row.names = c(NA, 4L), class = "data.frame")

尝试过的代码 #

smoking_status_by_sex <- smoking_data %>% 
  group_by(sex) %>% 
  dplyr::count(cigarettes_smoking_status) %>% 
  pivot_wider(names_from = sex, values_from = n) %>% # 增加列数、减少行数
  adorn_totals(c("row", "col") )

smoking_status_by_sex_per <- smoking_status_by_sex %>% 
   mutate(female_pct = round((100*.[[2]]/Total),digits =2),
          male_pct = round((100*.[[3]]/Total),digits =2),
          prefer_not_to_say_pct = round((100*.[[4]]/Total), digits=2),
          unknown_pct = round((100*.[[5]]/Total),digits =2),
          total_pct = round((100*.[[6]]/Total), digits=2))

目标效果 #

目标表格以吸烟状态为行，每列对应男性、女性的计数和百分比（百分比为该吸烟状态人数占对应性别总人数的比例），同时包含总计行，展示各状态的总计数和总占比。

解决方案 #

使用dplyr、tidyr和janitor包可高效实现需求，步骤如下：

1. 加载依赖包 #

library(dplyr)
library(tidyr)
library(janitor)

2. 完整处理代码 #

# 补充测试数据（原示例仅含非吸烟者，添加其他状态方便演示）
smoking_data <- structure(list(sex = c("Female", "Male", "Female", "Female", "Male", "Male", "Female", "Male"), 
                               cigarettes_smoking_status = c("Non-smoker", "Non-smoker", "Non-smoker", "Occasional smoker", 
                                                            "Regular smoker", "Prefer not to say", "Non-smoker", "Unknown")), 
                          row.names = c(NA, 8L), class = "data.frame")

# 计算各性别下吸烟状态的计数与百分比
smoking_status_by_sex <- smoking_data %>%
  # 分组统计每个性别下各吸烟状态的人数
  group_by(sex, cigarettes_smoking_status) %>%
  count(name = "count") %>%
  ungroup() %>%
  # 按性别计算百分比：该状态人数占对应性别总人数的比例
  group_by(sex) %>%
  mutate(pct = round(100 * count / sum(count), 2)) %>%
  ungroup() %>%
  # 转成宽表，拆分计数和百分比列
  pivot_wider(
    names_from = sex,
    values_from = c(count, pct),
    values_fill = list(count = 0, pct = 0) # 缺失状态填充为0
  ) %>%
  # 添加总计行
  adorn_totals("row") %>%
  # 计算总计行的计数和百分比
  mutate(
    Total_count = rowSums(select(., starts_with("count_"))),
    Total_pct = round(100 * Total_count / sum(Total_count), 2)
  ) %>%
  # 调整列顺序，让计数与百分比对应排列
  select(
    cigarettes_smoking_status,
    count_Female, pct_Female,
    count_Male, pct_Male,
    Total_count, Total_pct
  )

# 修改列名匹配目标表格样式
colnames(smoking_status_by_sex) <- c(
  "Smoking Status",
  "Female (n)", "Female (%)",
  "Male (n)", "Male (%)",
  "Total (n)", "Total (%)"
)

代码说明 #

• 先按性别和吸烟状态分组计数，得到基础人数统计
• 按性别分组计算百分比，确保比例基于该性别总人数（列总计）
• 通过pivot_wider将长表转为宽表，方便横向对比各性别数据
• 添加总计行并计算总计的计数与百分比，完善表格信息
• 最后调整列名和顺序，完全匹配目标表格的展示形式

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