Pandas中比较两个不同DataFrame并依据匹配结果新增列的实现方法

现有数据 #

1. country_df（年度主办国&获胜国对照表） #

定义代码：

country = {'Year':[2020,2021],'Host':['Mexico','Panama'],'Winners':['Canada','Japan']}
country_df = pd.DataFrame(country,columns=['Year','Host','Winners'])

数据预览：

Year    Host Winners
0  2020  Mexico  Canada
1  2021  Panama   Japan

2. all_country_df（待标记全量国家数据） #

定义代码：

all_country = {'Country': ['USA','Mexico','USA','Panama','Japan'],'Year':[2021,2020,2020,2021,2021]}
all_country_df=pd.DataFrame(all_country,columns=['Country','Year'])

数据预览：

Country  Year
0     USA  2021
1  Mexico  2020
2     USA  2020
3  Panama  2021
4   Japan  2021

需求说明 #

基于两个DataFrame的年份字段匹配，给all_country_df新增两列：

• Winner列：如果当前行国家是对应年份的获胜国，填Winner，否则填None
• Host列：如果当前行国家是对应年份的主办国，填Host，否则填None

实现代码 #

import pandas as pd

# 构造映射字典，便于按年份快速查询对应的主办国、获胜国
year_to_host = country_df.set_index('Year')['Host'].to_dict()
year_to_winner = country_df.set_index('Year')['Winners'].to_dict()

# 逐行匹配赋值
all_country_df['Winner'] = all_country_df.apply(
    lambda row: 'Winner' if row['Country'] == year_to_winner.get(row['Year']) else None,
    axis=1
)
all_country_df['Host'] = all_country_df.apply(
    lambda row: 'Host' if row['Country'] == year_to_host.get(row['Year']) else None,
    axis=1
)

最终结果 #

Country  Year  Winner  Host
0     USA  2021    None  None
1  Mexico  2020    None  Host
2     USA  2020    None  None
3  Panama  2021    None  Host
4   Japan  2021  Winner  None

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