R语言嵌套结构数据表实现类别与区域交叉统计的最优方案

核心问题解答 #

• 不需要预先执行全表宽转换，最高效的实现路径是：展开列表类型的多选项列→按维度分组计数→按需转成宽格式交叉表

实现代码 #

library(tidyverse)

# 加载示例数据
df <- structure(list(resp_id = c("a", "b", "c", "d", "e", "f", "g", "h", "i", "j"), category = list(c("c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308"), c("c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641"), c("c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308"), c("c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641"), c("c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308"), c("c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641"), c("c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308"), c("c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641"), c("c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308"), c("c52057", "c43641", "c23745", "c58308", "c52057", "c43641", "c23745", "c58308", "c52057", "c43641")), region = c("NY", "CA", "NY", "TR", "HU", "CA", "CA", "TA", "TD", "HR")), row.names = c(NA, -10L), class = c("tbl_df", "tbl", "data.frame"))

# 生成交叉表
cross_tab <- df %>%
  # 展开嵌套的多选项列表，每行对应一个受访者的一个分类选择
  unnest_longer(category) %>%
  # 按地区、分类两个维度统计次数
  count(region, category) %>%
  # 转成要求的宽表格式，缺失值自动填充为0
  pivot_wider(names_from = category, values_from = n, values_fill = 0)

补充说明 #

• 如果需要统计去重受访者人数（同一受访者重复选择同一分类仅计1次），在count步骤前添加distinct(resp_id, region, category)做去重处理即可
• 此前unnest_longer输出不符合预期，大概率是展开后没有先做分组计数，直接执行宽转换导致值仍为嵌套状态

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