使用Pandas清理航空事故国家统计数据并合并相似国家行的方法

第一步：加载数据并过滤非国家条目 #

先加载你提供的字典为DataFrame，再直接过滤掉名称包含Ocean、Airport这类非国家关键词的行：

import pandas as pd

# 加载数据
data = {'country': {0: 'Afghanistan', 1: 'Airport', 2: 'Algeria', 3: 'Angola', 4: 'Antigua and Barbuda', 5: 'Argentina', 6: 'Armenia', 7: 'Atlantic Ocean', 8: 'Australia', 9: 'Austria', 10: 'Bahamas', 11: 'Bahrain', 12: 'Bangladesh', 13: 'Belgium', 14: 'Botswana', 15: 'Brazil', 16: 'Cameroon', 17: 'Canada', 18: 'Chile', 19: 'China', 20: 'Colombia', 21: 'Comoros', 22: 'Congo', 23: 'Congo Democratic Republic', 24: 'Costa Rica', 25: 'Croatia', 26: 'Cuba', 27: 'Czech Republic', 28: 'Dominican Republic', 29: 'Ecuador', 30: 'Egypt', 31: 'Ethiopia', 32: 'Finland', 33: 'France', 34: 'Gabon', 35: 'Germany', 36: 'Ghana', 37: 'Greece', 38: 'Greenland', 39: 'Guam', 40: 'Guatemala', 41: 'Guinea', 42: 'Guyana', 43: 'Honduras', 44: 'Hong Kong', 45: 'India', 46: 'Indian Ocean', 47: 'Indonesia', 48: 'Iran', 49: 'Iraq', 50: 'Ireland', 51: 'Israel', 52: 'Italy', 53: 'Ivory Coast', 54: 'Jamaica', 55: 'Japan', 56: 'Kazakhstan', 57: 'Kenya', 58: 'Kuwait', 59: 'Kyrgyzstan', 60: 'Laos', 61: 'Lebanon', 62: 'Libya', 63: 'Lybia', 64: 'Macedonia', 65: 'Malaysia', 66: 'Mali', 67: 'Malta', 68: 'Mexico', 69: 'Morocco', 70: 'Mozambique', 71: 'Myanmar', 72: 'Namibia', 73: 'Nepal', 74: 'Netherlands', 75: 'Nicaragua', 76: 'Niger', 77: 'Nigeria', 78: 'Norway', 79: 'Oman', 80: 'Pacific Ocean', 81: 'Pakistan', 82: 'Panama', 83: 'Papua New Guinea', 84: 'Peru', 85: 'Philippines', 86: 'Poland', 87: 'Portugal', 88: 'Puerto Rico', 89: 'Qatar', 90: 'Romania', 91: 'Russia', 92: 'Rwanda', 93: 'San Vicente', 94: 'Saudi Arabia', 95: 'Senegal', 96: 'Serbia and Montenegro', 97: 'Sierra Leone', 98: 'Slovakia', 99: 'Solomon Islands', 100: 'Somalia', 101: 'South Africa', 102: 'South Korea', 103: 'Spain', 104: 'Sri Lanka', 105: 'Sudan', 106: 'Sweden', 107: 'Switzerland', 108: 'Taiwan', 109: 'Tanzania', 110: 'Tanzanie', 111: 'Thailand', 112: 'Tunisia', 113: 'Turkey', 114: 'Uganda', 115: 'Ukraine', 116: 'United Arab Emirates', 117: 'United Kingdom', 118: 'United Knigdom', 119: 'USA', 120: 'Venezuela', 121: 'Yemen'}, 'accidents': {0: 3, 1: 2, 2: 2, 3: 5, 4: 1, 5: 7, 6: 1, 7: 1, 8: 1, 9: 1, 10: 2, 11: 1, 12: 1, 13: 5, 14: 3, 15: 18, 16: 4, 17: 12, 18: 3, 19: 13, 20: 7, 21: 3, 22: 2, 23: 3, 24: 1, 25: 1, 26: 1, 27: 1, 28: 1, 29: 4, 30: 5, 31: 1, 32: 1, 33: 15, 34: 3, 35: 4, 36: 1, 37: 2, 38: 1, 39: 1, 40: 2, 41: 1, 42: 1, 43: 1, 44: 2, 45: 19, 46: 1, 47: 18, 48: 5, 49: 5, 50: 2, 51: 1, 52: 8, 53: 1, 54: 1, 55: 8, 56: 3, 57: 2, 58: 1, 59: 3, 60: 1, 61: 1, 62: 10, 63: 2, 64: 1, 65: 6, 66: 1, 67: 1, 68: 5, 69: 2, 70: 3, 71: 2, 72: 1, 73: 4, 74: 3, 75: 1, 76: 1, 77: 8, 78: 1, 79: 1, 80: 1, 81: 6, 82: 2, 83: 2, 84: 7, 85: 4, 86: 3, 87: 1, 88: 1, 89: 1, 90: 2, 91: 6, 92: 1, 93: 1, 94: 4, 95: 1, 96: 1, 97: 1, 98: 1, 99: 1, 100: 3, 101: 1, 102: 7, 103: 8, 104: 4, 105: 3, 106: 1, 107: 1, 108: 13, 109: 1, 110: 1, 111: 9, 112: 1, 113: 9, 114: 1, 115: 1, 116: 7, 117: 7, 118: 1, 119: 66, 120: 3, 121: 3}}
df = pd.DataFrame(data)

# 过滤非国家条目
exclude_keywords = ['Ocean', 'Airport']
df = df[~df['country'].str.contains('|'.join(exclude_keywords))]

第二步：合并名称变体/拼写错误的国家条目 #

你这个数据量很小，最稳妥、最简单的方式是手动做名称映射表，避免自动模糊匹配出错：

# 手动定义名称映射：键是原名称，值是标准名称
country_mapping = {
    'United Knigdom': 'United Kingdom',
    'Tanzanie': 'Tanzania',
    'Lybia': 'Libya',
    'Congo Democratic Republic': 'Congo'
    # 有其他变体可以继续加在这里
}

# 替换国家名称为标准名
df['country'] = df['country'].replace(country_mapping)

# 按国家分组求和事故数
df_clean = df.groupby('country', as_index=False)['accidents'].sum()

如果后续数据量变大，不想手动维护映射，可以用第三方库fuzzywuzzy做模糊匹配自动合并：

1. 先安装依赖：pip install fuzzywuzzy python-Levenshtein
2. 匹配代码：

from fuzzywuzzy import process

# 先准备标准国家名列表
standard_countries = ['Afghanistan', 'Algeria', 'Angola', 'United Kingdom', 'Tanzania', 'Libya', 'Congo'] # 补全所有需要的标准名即可

# 给每个原名称匹配最接近的标准名，匹配度低于80的保留原名（阈值可以自己调）
df['country'] = df['country'].apply(lambda x: process.extractOne(x, standard_countries, score_cutoff=80)[0] if process.extractOne(x, standard_countries, score_cutoff=80) else x)

# 分组求和
df_clean = df.groupby('country', as_index=False)['accidents'].sum()

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