如何在R中按列分组统计多列长度不同的分类数据

需求说明 #

拥有49000×118的数据集，需按test_number列分组，统计第2-4列（question1_response、question2_response、question3_response）的分类数据，处理各列统计结果长度不一致的问题，用NA补全不等长结构。

示例数据 #

dat<- data.frame(test_number= as.factor(c("test1", "test1", "test1","test1","test1","test1", "test2","test2","test2", "test3","test3","test3","test3","test3","test3")), 
                 question1_response= as.factor(c("yes", NA, "no","not answered", "yes", "yes", NA, "no","yes","yes","yes","yes","yes","yes","yes")),
                 question2_response= as.factor(c("yes","yes","yes","yes","yes","yes","yes","yes","yes","yes","yes","yes","yes","yes","no")),
                 question3_response= as.factor(c("yes", NA, "no","yes", NA, "no","yes", NA, "no","yes", NA, "no","yes", NA, "no")))

尝试过的无效代码 #

• 代码1：

summary1<- dat %>%
  group_by(test_number) %>%
  group_map(~summarize(.x, across(everything(), summary)))

• 代码2：

lapply(dat[-1], 
       FUN = function(x) { group_by(test_number) %>% summary(factor(x)) })

• 代码3：

dat %>%
  group_by(test_number) %>%
  lapply(dat[c(2:4)], FUN = function(x) summary(x))

解决方案 #

下面提供两种可行的实现方式，均能自动用NA补全不等长的统计结果：

方法一：长格式转宽格式（推荐，代码简洁） #

利用tidyr的格式转换功能，先将数据转为长格式统计计数，再转回宽格式自动补全缺失值：

library(dplyr)
library(tidyr)

result <- dat %>%
  # 把多列问题转为长格式，统一处理
  pivot_longer(
    cols = question1_response:question3_response,
    names_to = "question",
    values_to = "response"
  ) %>%
  # 按分组、问题、响应类别统计数量
  group_by(test_number, question, response) %>%
  summarise(count = n(), .groups = "drop") %>%
  # 转回宽格式，缺失的类别自动填充NA
  pivot_wider(
    names_from = c(question, response),
    values_from = count,
    values_fill = NA
  )

方法二：自定义统计函数+展开整合 #

通过自定义函数生成每个列的统计结果，再展开并整理为宽表：

library(dplyr)
library(purrr)
library(tidyr)

# 自定义统计函数，返回包含类别和计数的 tibble（包含NA的统计）
stat_count <- function(col) {
  tab <- table(col, useNA = "ifany")
  tibble(category = names(tab), count = as.integer(tab))
}

result <- dat %>%
  group_by(test_number) %>%
  # 对每个问题列应用统计函数，保存为列表
  summarise(
    across(question1_response:question3_response, ~list(stat_count(.x))),
    .groups = "drop"
  ) %>%
  # 展开每个问题的统计结果，保留类别信息
  unnest_longer(
    cols = c(question1_response, question2_response, question3_response),
    indices_to = "category"
  ) %>%
  # 转为宽表，缺失值填充NA
  pivot_wider(
    names_from = c(name, category),
    values_from = count,
    values_fill = NA
  )

运行上述代码后，result即为符合需求的统计表格，不同列的不同分类会自动用NA补全长度差异。

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