如何在R中跨多列搜索并筛选含指定字符串集的数据

需要在R中跨多列搜索一组编码，筛选出至少存在一个匹配项的数据集。尝试了以下代码，但仅能识别编码位于单元格字符串开头的情况，无法捕捉编码在字符串中间或末尾的场景：

codes <- c("1HY85","1HZ85","1GT85","1GR85", "1OA85","1PC85","1OJ85","1OK85","1NK85","1NP85")
sub_data <- og_data %>%
filter_at(.vars = vars(Code_1:Code_10),
              .vars_predicate = any_vars(str_detect(. , paste0("(", paste(codes, collapse = "|"), ")"))))

数据示例：

structure(list(unique_record_id = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 
10), Code_1 = c("1SC27JA", "1PC85LAXXJ", "1PC85LAXXK", "1PC85LAXXK", 
"1PC85LAXXJ", "1PC85LAXXK", "1PC85LAXXK", "XXJ1NK85YY", "1PC85LAXXJ", 
"1PC85LAXXJ"), Code_2 = c("NA", "1PG80LD", "1PZ21HQBR1OA85", 
"1PC85LAXXK", "1SY80LA", "1PC85LAXXK", "1PG87LA", "1OT53HATS", 
"1PC85LAXXJ", "1JQ55GRLG"), Code_3 = c("NA", "1PG50LABJ", "NA", 
"NA", "1PG80LA", "1PZ21HQBR", "1PG50LABJ", "1PZ21HPD4", "1PE76LA", 
"NA"), Code_4 = c("NA", "NA", "NA", "NA", "1PG51NK850LABJ", "1IS55GRLF", 
"1IS55GRLF", NA, "1PG50LABJ", "NA"), Code_5 = c("NA", "NA", "NA", 
"NA", "NA", "NA", "NA", "NA", "1PG50LABJ", "NA"), Code_6 = c("NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "NA", "2PC71LA", "NA"), Code_7 = c("NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "NA", "2PC71LA", "NA"), Code_8 = c("NA", 
"NA", "NA", "NA", "NA", "NA", "NA", "NA", "1IS53GRLF", "NA"), 
Code_9 = c("NA", "NA", "NA", "NA", "NA", "NA", "NA", "NA", 
"NA", "NA"), Code_10 = c("NA", "NA", "NA", "NA", "NA", "NA", 
"NA", "NA", "NA", "NA")), class = c("tbl_df", "tbl", "data.frame"
), row.names = c(NA, -10L))

你的核心逻辑没问题，问题出在数据里的"NA"是字符串类型而非R原生NA，同时filter_at属于旧版dplyr语法，推荐用更简洁的if_any替代。改进后的代码可正确匹配字符串任意位置的编码：

library(dplyr)
library(stringr)

codes <- c("1HY85","1HZ85","1GT85","1GR85", "1OA85","1PC85","1OJ85","1OK85","1NK85","1NP85")

# 先把字符串"NA"转为R原生空值，避免干扰匹配
og_data_cleaned <- og_data %>%
  mutate(across(Code_1:Code_10, ~ifelse(. == "NA", NA, .)))

# 筛选至少一列包含目标编码的行
sub_data <- og_data_cleaned %>%
  filter(if_any(Code_1:Code_10, ~str_detect(.x, paste(codes, collapse = "|"))))

关键细节： #

• paste(codes, collapse = "|")生成的正则表达式本身就支持匹配字符串任意位置的编码，无需额外加括号
• 用across统一处理字符串型"NA"，避免str_detect将其当作普通文本扫描
• if_any是dplyr 1.0.0+推荐的语法，比filter_at更直观且维护性更强

运行上述代码后，会正确筛选出所有包含目标编码的行（示例数据中除第1行外的所有行）。

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