如何在R语言中从城市提取国家名称并保存为列？

我的数据集包含以下几种格式的位置信息：

• city, province/state, country
• city
• province/state
• country

我已经完成了部分数据清洗，代码如下：

## 第一步：移除无意义文本
df <- df%>% 
  add_column(location_clean = trimws(str_replace_all(.$location, "Greater|Metropolitan|Metro|Area", "")), 
             .after = "location")

## 拆分位置字段为locale、area1、area2，后续用locale提取国家名
df <- df%>% 
  separate(location_clean, 
           c("locale", "area1","area2"), sep=",", 
           remove = FALSE, extra="merge", fill="right") %>%
  mutate(locale = trimws(locale), area1 = trimws(area1), area2 = trimws(area2))

# 当前未完成清洗的数据集结构
df <- structure(list(location = c("United States", "Israel", "Greater Stockholm Metropolitan Area", 
"Greater Chicago Area", "United States", "Greater Minneapolis-St. Paul Area", 
"Greater Tampa Bay Area", "Greater Sacramento", "United States", 
"Atlanta Metropolitan Area", "Kansas City Metropolitan Area", 
"Kansas City Metropolitan Area", "Charlotte Metro", "San Diego Metropolitan Area", 
"Greater Hamburg Area", "United Arab Emirates", "Greater Stockholm Metropolitan Area", 
"Raleigh-Durham-Chapel Hill Area", "Slovakia", "Latvia", "Greater Sydney Area", 
"Sweden", "Romania", "Canada", "United States", "Metro Jacksonville", 
"Metro Jacksonville", "Greater Tampa Bay Area", "Singapore", 
"Greater Bolzano Metropolitan Area", "Greater Scranton Area"), 
    locale = c("United States", "Israel", "Stockholm", "Chicago", 
    "United States", "Minneapolis-St. Paul", "Tampa Bay", "Sacramento", 
    "United States", "Atlanta", "Kansas City", "Kansas City", 
    "Charlotte", "San Diego", "Hamburg", "United Arab Emirates", 
    "Stockholm", "Raleigh-Durham-Chapel Hill", "Slovakia", "Latvia", 
    "Sydney", "Sweden", "Romania", "Canada", "United States", 
    "Jacksonville", "Jacksonville", "Tampa Bay", "Singapore", 
    "Bolzano", "Scranton"), area1 = c(NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_), area2 = c(NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    ), country = c(NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_, 
    NA_character_, NA_character_, NA_character_, NA_character_
    )), row.names = c(NA, -31L), class = c("tbl_df", "tbl", "data.frame"
))

现在数据集包含原始location列和拆分后的locale列，但我无法从这两列中准确提取国家名称。我尝试用maps包的world.cities函数，但结果不一致，代码如下：

get_country_name <- function(city){
  
  w <- world.cities %>% 
    filter(name == city) %>% 
    arrange(desc(pop)) %>% head(1) %>% pull(country.etc)
  
  return(w)
}

df %>% 
  mutate(country = ifelse(is.na(country), unlist(sapply(locale, get_country_name)), country))

另外，存在同名城市分属不同国家的问题，目前我通过人口排序选择大城市对应的国家，但希望有更优方案。

期望输出的数据集示例（已填充正确国家名）：

df <- structure(list(location = c("United States", "Israel", "Greater Stockholm Metropolitan Area", 
"Greater Chicago Area", "United States", "Greater Minneapolis-St. Paul Area", 
"Greater Tampa Bay Area", "Greater Sacramento", "United States", 
"Atlanta Metropolitan Area", "Kansas City Metropolitan Area", 
"Kansas City Metropolitan Area", "Charlotte Metro", "San Diego Metropolitan Area", 
"Greater Hamburg Area", "United Arab Emirates"), locale = c("United States", 
"Israel", "Stockholm", "Chicago", "United States", "Minneapolis-St. Paul", 
"Tampa Bay", "Sacramento", "United States", "Atlanta", "Kansas City", 
"Kansas City", "Charlotte", "San Diego", "Hamburg", "United Arab Emirates"
), area1 = c(NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_), area2 = c(NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_, NA_character_, 
NA_character_, NA_character_, NA_character_, NA_character_), 
    country = c("United States", "Israel", "Sweden", 
    "United States", "United States", "United States", "United States", 
    "United States", "United States", "United States", "United States", 
    "United States", "United States", "United States", "Germany", 
    "United Arab Emirates")), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame"))

方案1：优先匹配国家名，再处理城市 #

先建立常用国家名称列表，优先判断locale是否本身是国家名，避免把国家名当成城市匹配，再处理城市条目：

library(dplyr)
library(maps)
library(stringr)

# 加载世界城市数据
data(world.cities)

# 自定义国家名称列表（可根据数据集扩展）
country_list <- c("United States", "Israel", "Sweden", "Germany", "United Arab Emirates", 
                  "Slovakia", "Latvia", "Romania", "Canada", "Singapore", "Italy")

# 优化的国家名称获取函数
get_country <- function(locale_str) {
  # 先判断是否为国家名
  if(locale_str %in% country_list) {
    return(locale_str)
  }
  
  # 匹配城市，忽略大小写，优先选人口最多的城市
  city_match <- world.cities %>%
    filter(str_detect(name, regex(paste0("^", locale_str, "$"), ignore_case = TRUE))) %>%
    arrange(desc(pop)) %>%
    slice(1) %>%
    pull(country.etc)
  
  # 无匹配返回NA，后续可手动补全
  return(ifelse(length(city_match) == 0, NA, city_match))
}

# 填充country列
df_cleaned <- df %>%
  mutate(country = ifelse(is.na(country), sapply(locale, get_country), country))

# 查看结果
df_cleaned %>% select(location, locale, country)

方案2：结合countrycode包增强匹配 #

如果需要更精准的国家映射，可使用countrycode包处理国家别名/代码，再补充城市匹配：

library(countrycode)

# 先处理国家名/别名的情况
df_cleaned <- df %>%
  mutate(country = case_when(
    locale %in% country_list ~ locale,
    # 尝试匹配国家别名/代码
    !is.na(countrycode(locale, origin = "country.name", destination = "country.name")) ~ countrycode(locale, origin = "country.name", destination = "country.name"),
    TRUE ~ NA_character_
  ))

# 处理剩余未匹配的城市条目
city_entries <- df_cleaned %>% filter(is.na(country))
city_matches <- city_entries %>%
  rowwise() %>%
  mutate(country = get_country(locale)) %>%
  ungroup()

# 合并结果
df_cleaned <- df_cleaned %>%
  filter(!is.na(country)) %>%
  bind_rows(city_matches)

同名城市问题优化方案 #

针对同名城市的匹配误差，可采用以下方法：

• 结合地区信息：如果原始location或拆分后的area1/area2包含州/省信息，可加入匹配条件缩小范围
• 自定义映射表：针对数据集中出现的高频同名城市，手动建立城市-国家映射表，强制匹配正确结果
• 地理编码API：调用第三方地理编码API（如高德、谷歌地图），通过经纬度反向解析国家，准确率最高，但需注意API配额限制

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