清理并匹配两个表中的数据条目

问：

数据结构： 表A和表B包含许多条目。 两个表都有相同的列：国家/地区、省/自治区/直辖市表 A 中的条目表示为 （xa，ya，za），表 B 中的条目表示为 （xb，yb，zb）。 表特点： 表 A 是一个 excel 文件： 条目可能是不干净的，但它们是英文的。 城市被英语化，例如，“米兰”而不是“米兰”。 表B： 条目可能是不干净的，并且是非英文名称。

制备： 标准化表 A 和 B 的格式，以便以后进行匹配。 有两个城市列表可用： city1000.txt 是一个文本文件：包含城市的原始名称。 alternatenames.txt 是一个 txt 文件：包含城市的潜在英文翻译。 从这两个列表中创建一个“英语化列表”。目标是使用城市名称的英文版本（如果可用）。此列表应与 city1000.txt 一样详尽。

过程： 使用英语化的城市列表清理表 B 中的数据。 确保全球城市列表标准化，以实现一致匹配。 对于表 B 中与全球列表中的任何城市都不匹配的城市： 找到最接近的匹配项并创建表 F。此表将 B 中的原始条目与其最接近的匹配项配对。 突出显示 F 中未找到匹配项的城市。 比较 B 和 A 的清理条目： 匹配的条目进入表 C。 不匹配的条目进入表 D。

我试过了这个，但它没有给我想要的结果，你能帮我吗？

从 Excel 文件加载数据

import pandas as pd
 
# Load data from Excel
table_A = pd.read_excel('path_to_table_A.xlsx', engine='openpyxl')
table_B = pd.read_excel('path_to_table_B.xlsx', engine='openpyxl')

准备英语化名称数据集

# Load Geonames data
column_names_alternate = ["alternateNameId", "geonameid", "isolanguage", "alternate_name", "isPreferredName", "isShortName", "isColloquial", "isHistoric", "from", "to"]
alternate_names_df = pd.read_csv('path_to_alternateNames.txt', sep='\t', header=None, names=column_names_alternate, low_memory=False, dtype=str)
 
column_names_cities = ["geonameid", "name", "asciiname", "alternatenames", "latitude", "longitude", "feature class", "feature code", "country code", "cc2", "admin1 code", "admin2 code", "admin3 code", "admin4 code", "population", "elevation", "dem", "timezone", "modification date"]
cities_df = pd.read_csv('path_to_cities1000.txt', sep='\t', header=None, names=column_names_cities, low_memory=False, dtype=str)
 
# Filter for English names
english_names_df = alternate_names_df[alternate_names_df['isolanguage'] == 'en']
 
# Merge with main Geonames dataset to get anglicized names
merged_df = pd.merge(english_names_df, cities_df, on='geonameid', how='inner')
final_df = merged_df[['alternate_name', 'name', 'country code']]
final_df.columns = ['Anglicized Name', 'Original Name', 'Country Code']

数据清洗

def clean_data(df):
    return df.applymap(lambda x: str(x).lower().strip())
 
table_B_clean = clean_data(table_B)

表B中的城市名称英语化

使用表 B 中的城市名称进行英语化：final_df

def anglicize_city(city):
    anglicized_name = final_df[final_df['Original Name'].str.lower() == city]['Anglicized Name'].values
    return anglicized_name[0] if anglicized_name.size > 0 else city
 
table_B_clean['City'] = table_B_clean['City'].apply(anglicize_city)

生成表 F

在以下位置查找每个城市最接近的匹配城市：table_B_clean

import difflib
 
def find_closest_city(city, choices):
    closest_match = difflib.get_close_matches(city, choices, n=1)
    return closest_match[0] if closest_match else city
 
table_B_clean['closest_city'] = table_B_clean['City'].apply(lambda x: find_closest_city(x, final_df['Anglicized Name'].tolist()))
table_F = table_B.copy()
table_F['closest_city'] = table_B_clean['closest_city']
table_F['match_found'] = table_F['City'] == table_F['closest_city']

生成表 C 和 D

将表 B 和表 A 中清理的条目进行比较：

merged_table = pd.merge(table_A, table_B_clean, on=['Country', 'State', 'City'], how='outer', indicator=True)
table_C = merged_table[merged_table['_merge'] == 'both'].drop(columns=['_merge'])
table_D = merged_table[merged_table['_merge'] == 'right_only'].drop(columns=['_merge'])

将结果保存到 Excel

with pd.ExcelWriter('table_C.xlsx', engine='openpyxl') as writer:
    table_C.to_excel(writer, index=False, sheet_name='Table C')
 
with pd.ExcelWriter('table_D.xlsx', engine='openpyxl') as writer:
    table_D.to_excel(writer, index=False, sheet_name='Table D')
 
with pd.ExcelWriter('table_F.xlsx', engine='openpyxl') as writer:
    table_F.to_excel(writer, index=False, sheet_name='Table F')
 
with pd.ExcelWriter('anglicized_cities.xlsx', engine='openpyxl') as writer:
    final_df.to_excel(writer, index=False, sheet_name