在R中匹配另一数据框字符串后如何正确提取对应国家名称

定义两个数据框：

search_for <- tibble(country= c("US - Texas","CA","UK - London-123","US -NY",'MX','Japan' )) 
country_names <- tibble( names= c('GB','US','US','UK'))

已成功用以下代码检测search_for中是否存在country_names里的国家名称：

search_for$t_f <- mapply(function(x){
    any(country_names$names %in% strsplit(x, split=" ")[[1]])
  }, search_for$country)
  
search_for

输出结果：

country         t_f  
  <chr>           <lgl>
1 US - Texas      TRUE 
2 CA              FALSE
3 UK - London-123 TRUE 
4 US -NY          TRUE 
5 MX              FALSE
6 Japan           FALSE

尝试添加clean_name列时，使用了以下代码：

search_for$clean_name <- ifelse(mapply(function(x){
    any(country_names$names %in% strsplit(x, split=" ")[[1]])
  }, search_for$country)==TRUE,country_names$names,'Name not in search_for')
  
search_for

但输出的clean_name不符合预期：

country         t_f   clean_name            
  <chr>           <lgl> <chr>                 
1 US - Texas      TRUE  GB                     
2 CA              FALSE Name not in search_for
3 UK - London-123 TRUE  US                     
4 US -NY          TRUE  UK                     
5 MX              FALSE Name not in search_for
6 Japan           FALSE Name not in search_for

正确的clean_name应为US、UK、US，错误原因是代码按country_names的顺序填充值，而非按匹配条件匹配对应国家名称。需要修正代码，同时适配15000+行的大数据量，且国家缩写不一定是country字段的首个子串。

解决方案 #

方法1：优化mapply逻辑，返回匹配的国家名称 #

修改mapply的逻辑，不再仅返回布尔值，而是返回实际匹配到的国家名称（示例返回第一个匹配项，若需多匹配可拼接）：

library(tibble)

search_for <- tibble(country= c("US - Texas","CA","UK - London-123","US -NY",'MX','Japan' )) 
country_names <- tibble( names= c('GB','US','US','UK'))

# 提取唯一国家名称，避免重复匹配
unique_countries <- unique(country_names$names)

search_for <- search_for %>%
  mutate(
    clean_name = mapply(function(x) {
      # 拆分字符串为子串，匹配唯一国家列表
      matches <- unique_countries[unique_countries %in% strsplit(x, split = " ")[[1]]]
      if(length(matches) > 0) {
        matches[1] # 返回第一个匹配项，多匹配可改为paste(matches, collapse = ", ")
      } else {
        "Name not in search_for"
      }
    }, country),
    t_f = clean_name != "Name not in search_for"
  )

search_for

输出结果：

# A tibble: 6 × 3
  country         clean_name            t_f  
  <chr>           <chr>                 <lgl>
1 US - Texas      US                    TRUE 
2 CA              Name not in search_for FALSE
3 UK - London-123 UK                    TRUE 
4 US -NY          US                    TRUE 
5 MX              Name not in search_for FALSE
6 Japan           Name not in search_for FALSE

方法2：用stringr正则匹配（适配大数据量） #

针对15000+行的数据集，正则匹配效率更高，先构建精准匹配模式：

library(tibble)
library(stringr)

search_for <- tibble(country= c("US - Texas","CA","UK - London-123","US -NY",'MX','Japan' )) 
country_names <- tibble( names= c('GB','US','US','UK'))

unique_countries <- unique(country_names$names)
# 构建正则模式，匹配完整单词（避免部分匹配，比如US出现在其他词中）
match_pattern <- str_c("\\b", unique_countries, "\\b", collapse = "|")

search_for <- search_for %>%
  mutate(
    clean_name = str_extract(country, match_pattern),
    clean_name = ifelse(is.na(clean_name), "Name not in search_for", clean_name),
    t_f = clean_name != "Name not in search_for"
  )

search_for

输出结果与方法1一致，且正则匹配在大数据场景下性能更优。

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