基于已知选项拆分含内部逗号的Google表单多选响应字符串

Google表单导出的多选问题数据会把选中选项用, 拼接成单个字符串，但部分选项本身包含, ，导致常规的separate_longer_delim()无法直接拆分。需求是：

• 基于已知选项列表拆分字符串，将每个选中选项拆分为单独行
• 识别参与者填写的“其他”自定义手写回复
• 最终转成宽表，用1/0标记是否选中对应选项

模拟数据 #

known_answers <- 
c("I live alone",
  "I live in on campus",
  "I split housing costs with housemates, family, landlord, tenant, etc.",
  "I have dependents",
  "other")

set.seed(123)
df <- data.frame(
  ID = 1:10, 
  answer = replicate(n = 10, expr = (sample(x=known_answers, size = sample(1:3,1)))) %>% sapply(function(x) paste(x, collapse = ", "))
)

一、基于已知选项拆分字符串 #

核心思路是用正则匹配已知选项，而非按分隔符拆分。已知选项固定，可将其转为正则表达式，用str_extract_all()提取所有匹配项后展开成行：

library(tidyverse)

# 转义正则特殊字符，生成匹配模式
pattern <- known_answers %>% 
  str_replace_all("([()\\[\\]{}+*?^$.|\\\\])", "\\\\\\1") %>%
  paste(collapse = "|")

# 提取匹配项并展开为多行
split_df <- df %>%
  mutate(answer = str_extract_all(answer, pattern)) %>%
  unnest(answer)

拆分后结果示例：

# A tibble: 21 × 2
      ID answer                                                                
   <int> <chr>                                                                 
 1     1 I split housing costs with housemates, family, landlord, tenant, etc.
 2     1 I live in on campus                                                   
 3     1 other                                                                 
 4     2 I live in on campus                                                   
 5     2 other                                                                 
 6     3 other                                                                 
 7     3 I have dependents                                                     
 8     3 I live in on campus                                                   
 9     4 I live alone                                                          
10     4 I live in on campus                                                   
# ℹ 11 more rows

二、识别“其他”自定义回复 #

实际数据中，自定义回复不会包含“other”字样，可通过提取匹配已知选项后的剩余内容识别：

方法1：提取自定义内容 #

custom_df <- df %>%
  mutate(
    matched = str_extract_all(answer, pattern),
    # 计算原字符串减去所有匹配内容后的剩余部分
    custom = str_remove_all(answer, paste(unlist(matched), collapse = ", ")) %>% str_trim()
  ) %>%
  filter(custom != "") %>%
  select(ID, custom)

方法2：将自定义内容作为单独选项加入拆分结果 #

split_with_custom <- df %>%
  rowwise() %>%
  mutate(
    known_options = list(str_extract_all(answer, pattern)[[1]]),
    custom_content = str_remove_all(answer, paste(known_options, collapse = ", ")) %>% str_trim(),
    # 合并已知选项与自定义内容
    answer = if(custom_content != "") c(known_options, custom_content) else known_options
  ) %>%
  ungroup() %>%
  unnest(answer)

三、转成宽表（1/0标记） #

拆分完成后，用pivot_wider()直接转换为宽表：

wide_df <- split_df %>%
  mutate(value = 1) %>%
  pivot_wider(
    id_cols = ID,
    names_from = answer,
    values_from = value,
    values_fill = 0
  )

最终宽表示例：

# A tibble: 10 × 6
      ID `I live alone` `I live in on campus` `I split housing costs with housemates, family, landlord, tenant, etc.` `I have dependents` other
   <int>          <dbl>                  <dbl>                                                               <dbl>                <dbl> <dbl>
 1     1              0                      1                                                                 1                    0     1
 2     2              0                      1                                                                 0                    0     1
 3     3              0                      1                                                                 0                    1     1
 4     4              1                      1                                                                 0                    0     0
 5     5              0                      0                                                                 1                    1     1
 6     6              0                      0                                                                 0                    1     0
 7     7              1                      0                                                                 0                    0     0
 8     8              0                      0                                                                 1                    0     1
 9     9              0                      0                                                                 1                    1     0
10    10              0                      1                                                                 0                    1     0

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