R语言中简化多选问题百分比频率表生成代码的方法

背景 #

我运营一家大型烘焙品牌，开展了客户购买历史调查：询问客户是否购买过macaron、cookie、scone、candy等产品，以及对应的购买渠道（online、local store、chain store），客户可勾选所有符合的选项。调查结果已整理成长格式数据集mockdf，生成代码如下：

set.seed(2)

record_id<- c(1:1000)
a_method_1 <- sample(c(1:2), replace = T, size = 1000)
b_method_1<-sample(c(1:2), replace = T, size = 1000)
c_method_1<- sample(c(1:2), replace = T, size = 1000)

a_method_2 <- sample(c(1:2), replace = T, size = 1000)
b_method_2<-sample(c(1:2), replace = T, size = 1000)
c_method_2<- sample(c(1:2), replace = T, size = 1000)

a_method_3 <- sample(c(1:2), replace = T, size = 1000)
b_method_3<-sample(c(1:2), replace = T, size = 1000)
c_method_3<- sample(c(1:2), replace = T, size = 1000)

a_method_4 <- sample(c(1:2), replace = T, size = 1000)
b_method_4<-sample(c(1:2), replace = T, size = 1000)
c_method_4<- sample(c(1:2), replace = T, size = 1000)

mockdf<- data.frame(record_id, a_method_1, b_method_1, c_method_1,
                    a_method_2, b_method_2, c_method_2, 
                    a_method_3, b_method_3, c_method_3,
                    a_method_4, b_method_4, c_method_4)

set.seed(2)
mockdf<- mockdf %>% mutate(
  across(.cols= (c(2:13)),
         .fns = ~ifelse(rbinom(n(), 1, 0.1), NA, .x))
)  # 添加NA模拟真实数据

mockdf<- mockdf %>% mutate(across(c(2:13), ~factor(.x, levels= c(1,2),
                                                   labels = c("checked", "unchecked"))))

mockdf<- mockdf %>% pivot_longer(c(2:13), names_to = "source_type") %>% 
  mutate(product = case_when(endsWith(source_type, "1")~ "macaron", 
                             endsWith(source_type, "2")~ "cookie",
                             endsWith(source_type, "3")~ "scone",
                             endsWith(source_type, "4")~ "candy",
                             TRUE~ NA), 
         method = case_when(startsWith(source_type, "a")~ "online", 
                            startsWith(source_type, "b")~ "local store", 
                            startsWith(source_type, "c")~"chain store", 
                            TRUE~ NA)) %>% 
  select(-source_type)

head(mockdf)
#  record_id value     product method     
#      <int> <fct>     <chr>   <chr>      
#1         1 checked   macaron online     
#2         1 unchecked macaron local store
#3         1 unchecked macaron chain store
#4         1 checked   cookie  online     
#5         1 unchecked cookie  local store
#6         1 checked   cookie  chain store

需求与现有代码问题 #

我需要生成带百分比的频率表，其中百分比的分母是每种产品的购买人数（而非总受访者或购买任意产品的人数）。目前的实现代码需要手动为每个产品定义购买人数变量，再用case_when匹配计算百分比，产品类型增多时代码会非常冗余：

n_macaron<- mockdf %>% filter(product == "macaron" & value == "checked") %>% distinct(record_id) %>% nrow()
n_cookie<- mockdf %>% filter(product == "cookie" & value == "checked") %>% distinct(record_id) %>% nrow()
n_scone<- mockdf %>% filter(product == "scone" & value == "checked") %>% distinct(record_id) %>% nrow()
n_candy<- mockdf %>% filter(product == "candy" & value == "checked") %>% distinct(record_id) %>% nrow()

mockdf %>% filter(value == "checked") %>%  group_by(product, method) %>% count() %>% 
  mutate(perc = case_when(product == "macaron"~ round((n/n_macaron)*100,0),
                          product == "cookie"~ round((n/n_cookie)*100,0),
                          product == "scone"~ round((n/n_scone)*100,0),
                          product == "candy"~ round((n/n_candy)*100,0)))

简化方案 #

利用dplyr的分组和聚合功能，无需手动定义单个产品的购买人数变量，代码可自动适配任意数量的产品类型：

方法一：先计算产品总购买人数再合并 #

# 第一步：计算每个产品的购买人数（去重后的受访者数量）
product_total <- mockdf %>%
  filter(value == "checked") %>%
  group_by(product) %>%
  summarise(total_purchasers = n_distinct(record_id), .groups = "drop")

# 第二步：生成频率表并计算百分比
freq_table <- mockdf %>%
  filter(value == "checked") %>%
  group_by(product, method) %>%
  count(name = "n") %>%
  left_join(product_total, by = "product") %>%
  mutate(perc = round((n / total_purchasers) * 100, 0)) %>%
  select(-total_purchasers)

# 查看结果
freq_table

方法二：嵌套分组一步完成 #

freq_table <- mockdf %>%
  filter(value == "checked") %>%
  group_by(product) %>%
  # 计算当前产品的总购买人数
  mutate(total_purchasers = n_distinct(record_id)) %>%
  # 嵌套分组：在产品分组基础上再按渠道分组
  group_by(product, method, .add = TRUE) %>%
  summarise(
    n = n(),
    perc = round((n / first(total_purchasers)) * 100, 0),
    .groups = "drop"
  )

# 查看结果
freq_table

两种方法都会输出和原代码一致的结果，且当新增产品类型时，无需修改代码，自动适配计算。

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