在R中处理复杂的“Select All That Apply”多选问题

问题背景 #

需要处理多选（Select All That Apply）问题的列数据，每行条目长度随受访者不同而变化。其中除了「Keeping Track of Time/Order」选项外，其余选项都带有括号和唯一标识。现有包含response_strength和response_challenge两个问题的数据集，目标是将其转换为长格式，统计每个选项的选择次数及占比。

示例输入数据集 #

df <- data.frame(ID = 1:6, 
                 response_strength = c("Language (L) Attention (A)", 
                                       "Movement Control (MC)", 
                                       "Language (L) Getting Along with Others (G) Attention (A) Memory (M)", 
                                       "Memory (M) Complex Thinking (C) Spatial Thinking (S)", 
                                       "Memory (M) Spatial Thinking (S)", 
                                       "Language (L) Attention (A)"), 
                 response_challenge = c("Movement Control (MC)", 
                                        "Language (L) Attention (A)", 
                                        "Complex Thinking (C)", 
                                        "Attention (A)", 
                                        "Getting Along with Others (G) Keeping Track of Time/Order", 
                                        "Keeping Track of Time/Order Movement Control (MC)"))

实现方案（基于tidyverse工具链） #

通过tidyr和dplyr组合完成格式转换与统计，步骤如下：

library(tidyverse)

# 1. 将宽格式转为长格式，区分不同问题
long_df <- df %>%
  pivot_longer(cols = c(response_strength, response_challenge),
               names_to = "survey_question",
               values_to = "response_raw")

# 2. 拆分每行的多个选项，避免拆分带空格的特殊选项「Keeping Track of Time/Order」
long_df <- long_df %>%
  separate_rows(response_raw, 
                sep = "(?<!Keeping Track of Time)/Order) (?=(?:[A-Za-z]+ \\([A-Z]+\\)|Keeping Track of Time/Order))")

# 3. 分组统计每个问题下各选项的选择次数，计算占比（占总受访者数的比例）
result_df <- long_df %>%
  group_by(survey_question, response_raw) %>%
  summarise(n = n(), .groups = "drop") %>%
  group_by(survey_question) %>%
  mutate(percent = round(n / n_distinct(df$ID), 2)) %>%
  rename(response = response_raw)

# 查看最终结果
print(result_df)

正则说明 #

拆分用的正则表达式(?<!Keeping Track of Time)/Order) (?=(?:[A-Za-z]+ \\([A-Z]+\\)|Keeping Track of Time/Order))，作用是仅在选项之间的空格处拆分：当空格后是带括号的常规选项，或是特殊选项「Keeping Track of Time/Order」时，才执行拆分，避免破坏特殊选项的完整性。

示例输出结果 #

# A tibble: 14 × 4
# Groups:   survey_question [2]
   survey_question     response                      n percent
   <chr>               <chr>                     <int>   <dbl>
 1 response_challenge  Attention (A)                2    0.33
 2 response_challenge  Complex Thinking (C)         1    0.17
 3 response_challenge  Getting Along with Others (G)1    0.17
 4 response_challenge  Keeping Track of Time/Order  2    0.33
 5 response_challenge  Language (L)                 1    0.17
 6 response_challenge  Movement Control (MC)        2    0.33
 7 response_strength   Attention (A)                3    0.5 
 8 response_strength   Complex Thinking (C)         1    0.17
 9 response_strength   Getting Along with Others (G)1    0.17
10 response_strength   Language (L)                 3    0.5 
11 response_strength   Memory (M)                   3    0.5 
12 response_strength   Movement Control (MC)        1    0.17
13 response_strength   Spatial Thinking (S)         2    0.33
14 response_strength   Keeping Track of Time/Order  0    0   

（注：原示例输出中的n和percent数值为演示用，实际统计结果以真实计算为准）

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