如何在R语言中将大都市区名称转换为对应国家？

我有一个大型位置数据集，部分记录是大都市区名称，其余是国家名称。使用separate函数拆分location字段为locale、state、country列后，仍存在两类记录的country列为空：

• 仅含国家名的记录（如"United States"、"Israel"）
• 大都市区名称记录（如"Greater Stockholm Metropolitan Area"、"Greater Chicago Area"）

需要从这些记录中提取国家名并填入country列。

我的拆分代码：

df <- df %>% separate(location, c("locale", "state", "country"), sep = ",", extra = "merge", fill = "right")

拆分后的数据集结构：

structure(list(location = c("Höllviksnäs, Skåne County, Sweden", "Helsingborg, Skåne County, Sweden", "London, England, United Kingdom", "Scottsdale, Arizona, United States", "Seattle, Washington, United States", "Charleston, South Carolina, United States", "United States", "New York, New York, United States", "Basel, Basel, Switzerland", "Kuala Lumpur, Federal Territory of Kuala Lumpur, Malaysia", "Atlanta, Georgia, United States", "Edmonton, Alberta, Canada", "Antony, Île-de-France, France", "Israel", "Essex, Ontario, Canada", "Burlingame, California, United States", "Chattanooga, Tennessee, United States", "Marlborough, England, United Kingdom", "Greater Stockholm Metropolitan Area", "Sacramento, California, United States", "Laguna Beach, California, United States", "Greater Chicago Area", "United States", "Greater Minneapolis-St. Paul Area", "El Paso, Texas, United States"), locale = c("Höllviksnäs", "Helsingborg", "London", "Scottsdale", "Seattle", "Charleston", "United States", "New York", "Basel", "Kuala Lumpur", "Atlanta", "Edmonton", "Antony", "Israel", "Essex", "Burlingame", "Chattanooga", "Marlborough", "Greater Stockholm Metropolitan Area", "Sacramento", "Laguna Beach", "Greater Chicago Area", "United States", "Greater Minneapolis-St. Paul Area", "El Paso"), state = c(" Skåne County", " Skåne County", " England", " Arizona", " Washington", " South Carolina", NA, " New York", " Basel", " Federal Territory of Kuala Lumpur", " Georgia", " Alberta", " Île-de-France", NA, " Ontario", " California", " Tennessee", " England", NA, " California", " California", NA, NA, NA, " Texas"), country = c(" Sweden", " Sweden", " United Kingdom", " United States", " United States", " United States", NA, " United States", " Switzerland", " Malaysia", " United States", " Canada", " France", NA, " Canada", " United States", " United States", " United Kingdom", NA, " United States", " United States", NA, NA, NA, " United States")), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -25L))

解决方案 #

分两步处理两类空值记录：

1. 补全纯国家名记录的country列 #

先定义数据集里的国家列表，匹配纯国家名的记录，直接将国家名填入country列：

library(tidyverse)

# 定义数据集包含的国家列表（可根据实际情况扩展）
country_list <- c("United States", "Israel", "Sweden", "United Kingdom", "Canada", "France", "Switzerland", "Malaysia")

# 处理纯国家名的空值
df <- df %>%
  mutate(country = ifelse(is.na(country) & location %in% country_list, location, country))

2. 补全大都市区记录的country列 #

创建大都市区与对应国家的映射表，通过匹配映射表补全country列：

# 创建大都市区-国家映射表（新增大都市区时直接扩展此表即可）
metro_country_map <- tibble(
  locale = c(
    "Greater Stockholm Metropolitan Area",
    "Greater Chicago Area",
    "Greater Minneapolis-St. Paul Area"
  ),
  country = c("Sweden", "United States", "United States")
)

# 匹配映射表补全country列，并统一格式
df <- df %>%
  left_join(metro_country_map, by = "locale") %>%
  # 优先保留原有的country值，空值用映射表的内容填充
  mutate(country = ifelse(is.na(country.x), country.y, country.x)) %>%
  # 删除临时列
  select(-country.x, -country.y) %>%
  # 去除country列的首尾空格（拆分时产生的多余空格）
  mutate(country = str_trim(country))

验证结果 #

查看原本country为空的记录，确认补全效果：

df %>% 
  filter(location %in% c("United States", "Israel", "Greater Stockholm Metropolitan Area", "Greater Chicago Area", "Greater Minneapolis-St. Paul Area")) %>%
  select(location, country)

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