如何检测Pandas DataFrame列是否包含列表中国家并返回匹配值

需求说明 #

• 检查DataFrame的remarks列文本中是否包含指定国家列表里的任意国家
• 若存在匹配，新增country列存储匹配到的国家名称

实现步骤与代码 #

1. 预处理国家列表 #

为避免短名称优先匹配导致的错误（比如误将"Dominican Republic"拆分为短词匹配），先将国家列表按名称长度倒序排序，确保长名称优先被匹配。

2. 构建正则匹配规则 #

将国家名称转换为正则表达式模式，使用单词边界\b确保匹配完整的国家名称，同时对特殊字符做转义处理，避免正则语法冲突。

3. 提取匹配结果并新增列 #

使用Pandas的str.extract方法从remarks列中提取匹配到的国家，生成新的country列。

完整代码

import pandas as pd
import re

# 定义国家列表
countries = ["Afghanistan", "Albania",  "Algeria",  "Andorra",  "Angola",   "Antigua and Barbuda",  "Argentina",    "Armenia",  "Austria",  "Azerbaijan",   "Bahrain",  "Bangladesh",   "Barbados", "Belarus",  "Belgium",  "Belize",   "Benin",    "Bhutan",   "Bolivia",  "Bosnia and Herzegovina",   "Botswana", "Brazil",   "Brunei",   "Bulgaria", "Burkina Faso", "Burundi",  "Cabo Verde",   "Cambodia", "Cameroon", "Canada",   "Central African Republic", "Chad", "Channel Islands",  "Chile",    "China",    "Colombia", "Comoros",  "Congo",    "Costa Rica",   "Côte d'Ivoire",    "Croatia",  "Cuba", "Cyprus",   "Czech Republic",   "Denmark",  "Djibouti", "Dominica", "Dominican Republic",   "DR Congo", "Ecuador",  "Egypt",    "El Salvador",  "Equatorial Guinea",    "Eritrea",  "Estonia",  "Eswatini", "Ethiopia", "Faeroe Islands",   "Finland",  "France",   "French Guiana",    "Gabon",    "Gambia",   "Georgia",  "Germany",  "Ghana",    "Gibraltar",    "Greece",   "Grenada",  "Guatemala",    "Guinea",   "Guinea-Bissau",    "Guyana",   "Haiti",    "Holy See", "Honduras", "Hong Kong",    "Hungary",  "Iceland",  "India",    "Indonesia",    "Iran", "Iraq", "Ireland",  "Isle of Man",  "Israel",   "Italy",    "Jamaica",  "Japan",    "Jordan",   "Kazakhstan",   "Kenya",    "Kuwait",   "Kyrgyzstan",   "Laos", "Latvia",   "Lebanon",  "Lesotho",  "Liberia",  "Libya",    "Liechtenstein",    "Lithuania",    "Luxembourg",   "Macao",    "Madagascar",   "Malawi",   "Malaysia", "Maldives", "Mali", "Malta",    "Mauritania",   "Mauritius",    "Mayotte",  "Mexico",   "Moldova",  "Monaco",   "Mongolia", "Montenegro",   "Morocco",  "Mozambique",   "Myanmar",  "Namibia",  "Nepal",    "Netherlands",  "Nicaragua",    "Niger",    "Nigeria",  "North Korea",  "North Macedonia",  "Norway",   "Oman", "Pakistan", "Panama",   "Paraguay", "Peru", "Philippines",  "Poland",   "Portugal", "Qatar",    "Réunion",  "Romania",  "Russia",   "Rwanda",   "Saint Helena", "Saint Kitts and Nevis",    "Saint Lucia",  "Saint Vincent and the Grenadines", "San Marino",   "Sao Tome & Principe",  "Saudi Arabia", "Senegal",  "Serbia",   "Seychelles",   "Sierra Leone", "Singapore",    "Slovakia", "Slovenia", "Somalia",  "South Africa", "South Korea",  "South Sudan",  "Spain",    "Sri Lanka",    "State of Palestine",   "Sudan",    "Suriname", "Sweden",   "Switzerland",  "Syria",    "Taiwan",   "Tajikistan",   "Tanzania", "Thailand", "The Bahamas",  "Timor-Leste",  "Togo", "Trinidad and Tobago",  "Tunisia",  "Turkey",   "Turkmenistan", "Uganda",   "Ukraine",  "United Arab Emirates", "United Kingdom",   "United States",    "Uruguay",  "Uzbekistan",   "Venezuela",    "Vietnam",  "Western Sahara",   "Yemen",    "Zambia",   "Zimbabwe"]

# 按国家名称长度倒序排序，优先匹配长名称
sorted_countries = sorted(countries, key=lambda x: len(x), reverse=True)

# 构建正则模式：转义特殊字符，添加单词边界
pattern = r'\b(' + '|'.join(re.escape(country) for country in sorted_countries) + r')\b'

# 创建DataFrame
ds1 = {'remarks':["DOB 21 Mar 1974; POB Baghdad, Iraq.","DOB 26 Mar 1969; POB Tunis, Tunisia; Italian Fiscal Code TLLLHR69C26Z352G.","DOB 10 Jun 1970; POB Tunis, Tunisia; nationality Tunisia; Passport L550681 issued 23 Sep 1997 expires 22 Sep 2002; Italian Fiscal Code WDDHBB70H10Z352O."] , "Latitude" : [-23.69057,-23.41165,-23.51482]}
df1 = pd.DataFrame(data=ds1)

# 提取匹配的国家，新增country列
df1['country'] = df1['remarks'].str.extract(pattern, expand=False)

# 查看结果
print(df1)

结果说明 #

运行代码后，df1会新增country列，存储每行remarks中匹配到的第一个国家名称：

• 第一行匹配到Iraq
• 第二行匹配到Tunisia
• 第三行匹配到Tunisia

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