在R中高效处理逗号分隔测试数据：拆分对应结果与状态

用tidyverse工具集里的tidyr::separate_rows()函数就能高效解决这个问题，它可以同时拆分多列并保持对应测试项、结果、状态的匹配关系，是处理这类数据最简洁的方法。

实现步骤 #

1. 加载tidyverse包（包含tidyr）
2. 给原始数据添加个体唯一标识（可选但推荐，方便追溯原数据）
3. 使用separate_rows()拆分目标列，自动对齐对应项

完整代码 #

# 加载依赖包
library(tidyverse)

# 加载你的样本数据
tb_sample_10 <- structure(list(test_name = c("TB Direct PCR & RIF Status,AFB Smear", 
"AFB Smear,Sensitivity,TB Direct PCR & RIF Status,TB Indirect PCR & RIF Status,Culture", 
"Culture,AFB Smear,Sensitivity,TB Direct PCR & RIF Status,TB Indirect PCR & RIF Status", 
"AFB Smear", "Culture,AFB Smear,Sensitivity,TB Direct PCR & RIF Status,TB Indirect PCR & RIF Status", 
"Culture,AFB Smear,Sensitivity,TB Direct PCR & RIF Status,TB Indirect PCR & RIF Status", 
"AFB Smear,TB Direct PCR & RIF Status", "Culture,AFB Smear,Sensitivity,TB Direct PCR & RIF Status,TB Indirect PCR & RIF Status", 
"AFB Smear,TB Direct PCR & RIF Status", "TB Direct PCR & RIF Status,Culture"
), test_result = c("MTB Detected / RIF Resistance Not Detected,No AFB Seen", 
"OTHER,Not Required,MTB Detected / RIF Resistance Not Detected,Not Required,No Growth", 
"Positive,No AFB Seen,Processed,Rejected,MTB Detected / RIF Resistance Not Detected (MTBC)", 
"AFB+", "Positive,OTHER,Processed,MTB Not Detected,Not Required", 
"Positive,AFB+,Not Required,MTB Detected / RIF Resistance Not Detected,Not Required", 
"No AFB Seen,MTB Detected / RIF Resistance Not Detected", "No Growth,No AFB Seen,Not Required,MTB Detected / RIF Resistance Not Detected,Not Required", 
"No AFB Seen,MTB Not Detected", "MTB Detected / RIF Resistance Not Detected,Positive"
), test_status = c("Final,Final", "Final,Final,Final,Final,Final", 
"Final,Final,Final,Final,Final", "Final", "Final,Final,Final,Final,Final", 
"Final,Final,Final,Final,Final", "Final,Final", "Final,Final,Final,Final,Final", 
"Final,Final", "Final,Final")), row.names = c(NA, -10L), class = c("tbl_df", 
"tbl", "data.frame"))

# 清理数据：添加个体ID并拆分列
tb_clean <- tb_sample_10 %>%
  mutate(individual_id = row_number()) %>%  # 生成个体唯一标识
  separate_rows(test_name, test_result, test_status, sep = ",", trim_ws = TRUE)

# 查看处理后的前6行结果
head(tb_clean)

效果说明 #

处理后的数据每条记录对应一个测试项，包含：

• individual_id：原始数据行号，用于定位特定个体的所有测试
• test_name：单独的测试名称
• test_result：对应测试的结果
• test_status：对应测试的状态

后续调取数据示例：

• 查看第3个个体的所有测试：tb_clean %>% filter(individual_id == 3)
• 查看所有AFB Smear测试的结果：tb_clean %>% filter(test_name == "AFB Smear")

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