如何基于多列匹配条件筛选Pandas DataFrame？

数据结构（简化版） #

import pandas as pd

# 冠军数据
Winner = [[1938,"Italy"],[1950,"Uruguay"],[2014,"Germany"]]
df = pd.DataFrame(Winner, columns=['Year', 'Winner'])

# 比赛数据
MatchB = [[1938,"Germany",1.0],[1938,"Germany",2.0],[1938,"Brazil",1.0],[1950,"Italy",2.0],[1950,"Spain",2.0],[1950,"Spain",1.0],[1950,"Spain",1.0],[1950,"Brazil",1.0],
[2014,"Italy",2.0],[2014,"Spain",3.0],[2014,"Germany",1.0]]
df2B = pd.DataFrame(MatchB, columns=['Year', 'Away Team Name','Away Team Goals'])

解决方案 #

方法1：使用merge内连接

通过列名对齐后执行内连接，仅保留Year和球队名称同时匹配的行：

# 重命名df的Winner列，与df2B的Away Team Name统一
df_matching = df.rename(columns={'Winner': 'Away Team Name'})
# 内连接筛选匹配行
filtered_result = pd.merge(df2B, df_matching, on=['Year', 'Away Team Name'], how='inner')
print(filtered_result)

方法2：元组集合匹配

将目标匹配对转为集合，逐行校验是否符合条件：

# 生成(年份, 冠军球队)的元组集合
target_pairs = set(zip(df['Year'], df['Winner']))
# 筛选符合条件的行
filtered_result = df2B[df2B.apply(lambda r: (r['Year'], r['Away Team Name']) in target_pairs, axis=1)]
print(filtered_result)

方法3：query简洁语法

利用query结合集合实现快速筛选：

target_pairs = set(zip(df['Year'], df['Winner']))
filtered_result = df2B.query('(Year, `Away Team Name`) in @target_pairs')
print(filtered_result)

三种方法最终都会得到相同的筛选结果：仅保留2014年德国作为客队的那一行数据。

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