在R中提取Qualtrics调查数据时保留ID，整理渔获物种与数量

从Qualtrics导出的垂钓者调查数据中，需要整理出包含渔获鱼种、数量及原调查ID的harv数据框。现有数据结构如下：

df<-structure(list(ID = 1:3, Q7 = c("Other species (please list species name; i.e. Tarpon),Other species (please list species name; i.e. Amberjack)", 
"Red Drum (a.k.a. Redfish or Red),Other species (please list species name; i.e. Tarpon),Other species (please list species name; i.e. Amberjack)", 
"Red Drum (a.k.a. Redfish or Red),Other species (please list species name; i.e. Tarpon)"
), Q7_7_TEXT = c("Tink", "Blue", "Blue"), Q7_8_TEXT = c("Chii", 
"Red", NA), Q8_1 = c(NA, "2", "7"), Q8_7 = c("1", "4", "9"), 
    Q8_8 = c("2", "5", NA)), class = "data.frame", row.names = 3:5)

原处理代码仅提取了鱼种（spp）和数量（num），但丢失了原ID：

library(tidyverse)
library(stringi)
harv<-data.frame(spp=unlist(strsplit(df$Q7, ",")))
oth<-na.omit(data.frame(spp=stri_remove_empty(c(unlist(t(df[,3:4]))))))

idx <- grep('Other', harv$spp)
n <- min(length(idx), nrow(oth))
harv[idx, 'spp'] <- oth

harv.num<-na.omit(as.numeric(stri_remove_empty(c(unlist(t(df[,5:7]))))))

harv$num<-harv.num

harv

期望输出的harv数据框格式如下：

spp num ID
1                             Tink   1  1
2                             Chii   2  1
3 Red Drum (a.k.a. Redfish or Red)   2  2
4                             Blue   4  2
5                              Red   5  2
6 Red Drum (a.k.a. Redfish or Red)   7  3
7                             Blue   9  3

通过按ID分组拆分数据，同步关联对应ID下的鱼种和数量，修改后的代码如下：

library(tidyverse)
library(stringi)

# 1. 处理鱼种列，保留ID并替换"Other"条目
harv_spp <- df %>%
  select(ID, Q7, Q7_7_TEXT, Q7_8_TEXT) %>%
  # 拆分Q7为多行，保留对应ID
  mutate(spp = str_split(Q7, ",")) %>%
  unnest(spp) %>%
  # 收集当前ID下的自定义鱼种文本
  rowwise() %>%
  mutate(other_spp = list(c(Q7_7_TEXT, Q7_8_TEXT))) %>%
  ungroup() %>%
  # 替换"Other"开头的鱼种为对应自定义文本
  mutate(
    spp = ifelse(str_detect(spp, "Other"), 
                 map2_chr(spp, other_spp, ~.y[which(str_detect(.x, "Other"))]), 
                 spp),
    spp = stri_remove_empty(spp)
  ) %>%
  drop_na(spp) %>%
  select(spp, ID)

# 2. 处理数量列，保留ID并转为长格式
harv_num <- df %>%
  select(ID, Q8_1, Q8_7, Q8_8) %>%
  pivot_longer(cols = -ID, values_to = "num") %>%
  mutate(num = as.numeric(num)) %>%
  drop_na(num) %>%
  arrange(ID) %>%
  select(num)

# 3. 合并鱼种、数量和ID，得到目标数据框
harv <- bind_cols(harv_spp, harv_num) %>%
  arrange(ID)

harv

关键说明： #

• 拆分鱼种时用str_split+unnest，确保每个鱼种都绑定原ID，避免关联关系丢失；
• 按ID收集自定义鱼种文本，精准替换"Other"类条目，避免全局替换的混乱；
• 数量列用pivot_longer转长格式，过滤空值后和鱼种列按ID顺序合并，保证对应关系正确。

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