基于字符串模式合并Pandas DataFrame报错排查求助

需求背景 #

需要从df['Company']列中提取城市、国家名称和Alpha2代码，实现两个DataFrame的匹配合并。小样本测试时方法可行，但加载完整世界城市数据集后触发报错。

目标DataFrame（df） #

# Target-df  
df = pd.DataFrame({'Company':['MAC CHEM PRODUCTS (INDIA) PVT. LTD. Mumbai IN',
                              'SIEGFRIED LTD. Zofingen CH',
                              'SHANDONG JINYANG PHARMACEUTICAL CO., LTD. Zibo City CN',
                              'CHIFENG ARKER PHARMACEUTICAL TECHNOLOGY CO., LTD. Zibo CZ', 
                               ], 
                   'Certificate+Number':['R1-CEP 2012-025 - Rev 02',
                                         'R2-CEP 1996-036 - Rev 02',
                                         'R0-CEP 2008-165 - Rev 00',
                                         'R1-CEP 2002-193 - Rev 00',
                                          ],
                   'Substance':['Suxamethonium Chloride',
                                'Amitriptyline hydrochloride',
                                'Oxytetracycline hydrochloride',
                                'Ephedrine hydrochloride', 
                                 ], 
                       }
                       )

世界城市数据集（最小示例） #

world_cities_min = pd.DataFrame({'Geoname ID':[1275339,
                                 '2657915',
                                 '1785286',
                                 '3061344', 
                                 ], 
                                  'City':['Mumbai',
                                          'Zofingen',
                                          'Zibo',
                                          'Zibo',
                                           ],
                                  'ASCII Name':['Mumbai',
                                                'Zofingen',
                                                'Zibo',
                                                'City', 
                                                 ], 
                                  'Country':['India',
                                             'Switzerland',
                                             'China',
                                             'Czech Republic', 
                                            ],
                                  'Alpha2':['IN',
                                            'CH',
                                            'CN',
                                            'CZ', 
                                            ], 
                               })

小样本测试代码（正常运行） #

pat = '|'.join(r"\b{}\b".format(x) for x in world_cities_min['ASCII Name'])

# 在目标DataFrame中创建城市名称列
df['ASCII Name']= df['Company'].str.extract('('+ pat + ')', expand=False)

完整数据集加载与报错 #

加载完整世界城市数据集的代码：

url = 'https://public.opendatasoft.com/api/explore/v2.1/catalog/datasets/geonames-all-cities-with-a-population-1000/exports/csv?lang=en&timezone=Europe%2FBerlin&use_labels=true&delimiter=%3B'

column_names = ['Geoname ID',
                'Name', 
                'ASCII Name',   
                'Alternate Names',
                'Feature Class',
                'Feature Code',
                'Country Code',
                'Country name EN',  
                'Country Code 2'    ,
                'Admin1 Code'   ,
                'Admin2 Code'   ,
                'Admin3 Code',  
                'Admin4 Code',  
                'Population',
                'Elevation',    
                'DIgital Elevation Model',  
                'Timezone', 
                'Modification date',    
                'LABEL EN', 
                'Coordinates'
                 ]
    
world_cities  = pd.read_csv(url,
                        header=1,
                        sep=';',
                          names=column_names,
                          usecols = [
                                    'Name', 
                                    'ASCII Name',   
                                    'Country Code'  ,
                                    'Country name EN',  
                                    'Coordinates'],
                            converters={
                                        },
                          )

执行相同提取操作时报错：

pat = '|'.join(r"\b{}\b".format(x) for x in world_cities['ASCII Name'])

# 在目标DataFrame中创建城市名称列
df['ASCII Name']= df['Company'].str.extract('('+ pat + ')', expand=False)

报错信息：

ValueError: Cannot set a DataFrame with multiple columns to the single column ASCII Name

问题原因 #

完整世界城市数据集中，ASCII Name列存在包含括号、特殊字符或正则表达式元字符的城市名（如带括号的名称），导致生成的正则表达式模式被解析为多个独立捕获组，str.extract返回多列DataFrame，无法直接赋值给单个列df['ASCII Name']。小样本无此类特殊名称，因此测试正常。

解决方案 #

方案1：转义正则元字符，确保单一捕获组 #

对城市名中的正则特殊字符进行转义，构建单一捕获组的正则模式：

import re

# 转义城市名中的正则元字符，去重并过滤空值
escaped_cities = [re.escape(city) for city in world_cities['ASCII Name'].dropna().unique()]
# 构建包含所有城市名的单一捕获组正则
pat = r"\b(" + "|".join(escaped_cities) + r")\b"

# 提取匹配结果（expand=False返回Series，可直接赋值给单列）
df['ASCII Name'] = df['Company'].str.extract(pat, expand=False)

方案2：使用findall获取匹配后取第一个结果 #

如果存在多个匹配项，取第一个有效结果：

import re

escaped_cities = [re.escape(city) for city in world_cities['ASCII Name'].dropna().unique()]
pat = r"\b(" + "|".join(escaped_cities) + r")\b"

# 提取所有匹配项，取第一个（无匹配则为NaN）
df['ASCII Name'] = df['Company'].str.findall(pat).str[0]

方案3：结合国家代码双重验证，避免歧义 #

针对城市名重复的情况（如示例中的Zibo对应中国和捷克），同时匹配城市名和国家代码，提高准确性：

import re

# 构建城市+国家代码的组合模式，避免歧义
world_cities['city_country'] = world_cities.apply(
    lambda x: f"{re.escape(x['ASCII Name'])}\\s+{re.escape(x['Country Code'])}", 
    axis=1
)
pat = r"\b(" + "|".join(world_cities['city_country'].dropna().unique()) + r")\b"

# 提取组合字符串并拆分
df['city_country'] = df['Company'].str.extract(pat, expand=False)
df[['ASCII Name', 'Alpha2']] = df['city_country'].str.split(r'\s+', n=1, expand=True)

# 合并国家名称
df = df.merge(
    world_cities[['ASCII Name', 'Country Code', 'Country name EN']].drop_duplicates(), 
    left_on=['ASCII Name', 'Alpha2'], 
    right_on=['ASCII Name', 'Country Code'], 
    how='left'
)

# 清理临时列
df.drop('city_country', axis=1, inplace=True)

最终效果 #

运行上述方案后，目标DataFrame会新增ASCII Name（城市名）、Alpha2（国家代码）、Country name EN（国家名称）列，实现从Company列中提取所需信息的目标。

内容的提问来源于stack exchange，提问作者Paul G.