如何在R中筛选匹配指定字符串向量的DataFrame行与列并去重

需求说明 #

现有大型DataFrame，部分列存储逗号分隔的字符向量，列数和向量内字符数量不固定。需要与目标向量vals_to_find <- c("Argentina","USA","Mexico")匹配，实现：

• 保留包含任一目标字符串的行与列
• 去除重复值和不匹配内容
• 输出指定格式结果

一、原始多列逗号分隔数据处理 #

原始数据 #

df <- structure(list(ID = 1:5, Country1 = c("Argentina, Japan,USA,Poland,", 
"Colombia, Mexico,Uruguay,Dutch", "Argentina, Japan,USA,NA", 
"Colombia, Mexico,Uruguay,Dutch", "India, China"), Country2 = c("Argentina,USA", 
"Mexico,Uruguay", "Japan", "Colombia, Dutch", "China"), Country3 = c("Pakistan", 
"Afganisthan", "Khazagistan", "North Korea", "Iran")), class = "data.frame", row.names = c(NA, 
-5L))

期望输出 #

df_out <- structure(list(ID = 1:4, Countries.found = c("Argentina, USA", 
"Mexico", "Argentina, USA", "Mexico")), class = "data.frame", row.names = c(NA, 
-4L))

解决方案代码 #

library(dplyr)
library(tidyr)

vals_to_find <- c("Argentina","USA","Mexico")

df_processed <- df %>%
  # 转换为长格式并拆分逗号分隔字符串
  pivot_longer(cols = starts_with("Country"), names_to = "col", values_to = "country") %>%
  separate_rows(country, sep = ",") %>%
  # 清理空格与空值，筛选目标国家
  mutate(country = trimws(country)) %>%
  filter(country != "", country %in% vals_to_find) %>%
  # 按ID去重，保留唯一匹配项
  distinct(ID, country, .keep_all = FALSE) %>%
  # 转回宽格式并合并国家字符串
  pivot_wider(id_cols = ID, names_from = NULL, values_from = country, 
              values_fn = list(country = ~paste(unique(.), collapse = ", "))) %>%
  rename(Countries.found = country) %>%
  arrange(ID)

# 查看结果
df_processed

二、单列单值格式数据的重复值优化 #

用户现有代码及问题 #

用户已将数据转为单列单值格式，但输出存在重复值：

df_single_val <- structure(list(ID = 1:5, X1 = c("Argentina", "Colombia", "Argentina", 
"Colombia", "India"), X2 = c("Japan", "Mexico", "Japan", "Mexico", "China"), 
X3 = c("USA", "Uruguay", "USA", "Uruguay", NA), X4 = c("Poland", "Dutch", NA, "Dutch", NA), 
X5 = c("Argentina", "Mexico", "Japan", "Colombia", "China"), X6 = c("USA", "Uruguay", NA, "Dutch", NA), 
X7 = c("Pakistan", "Afganisthan", "Khazagistan", "North Korea", "Iran")), 
class = "data.frame", row.names = c(NA, -5L))

# 用户现有代码
vals_to_find <- c("Argentina","USA","Mexico")
current_output <- df_single_val %>%
  select(where(~ !all(is.na(.x)))) %>%
  select(c(1, where(~ any(.x %in% vals_to_find)))) %>% 
  mutate(across(starts_with("X"), ~ vals_to_find[match(., vals_to_find)])) %>%
  tidyr::unite("countries_found", starts_with("X"), sep = " | ", remove = TRUE, na.rm = TRUE)

当前输出（存在重复） #

ID                   countries_found
1  1 Argentina | USA | Argentina | USA
2  2                   Mexico | Mexico
3  3                   Argentina | USA
4  4                            Mexico

优化代码（去除重复值） #

通过行级处理提取唯一匹配项，解决重复问题：

optimized_output <- df_single_val %>%
  select(where(~ !all(is.na(.x)))) %>%
  select(c(1, where(~ any(.x %in% vals_to_find)))) %>% 
  mutate(across(starts_with("X"), ~ vals_to_find[match(., vals_to_find)])) %>%
  # 按行提取非NA目标值，去重后合并
  rowwise() %>%
  mutate(countries_found = paste(unique(na.omit(c_across(starts_with("X")))), collapse = ", ")) %>%
  ungroup() %>%
  select(ID, countries_found) %>%
  # 过滤无匹配的行
  filter(countries_found != "")

# 查看优化后结果
optimized_output

优化后输出 #

# A tibble: 4 × 2
     ID countries_found
  <int> <chr>          
1     1 Argentina, USA 
2     2 Mexico         
3     3 Argentina, USA 
4     4 Mexico         

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