如何匹配两个Pandas DataFrame所有行并返回匹配行信息？

我有两个行数超过200的DataFrame，想要在df1的每一行中，找到df2中存在匹配值的行，并返回匹配行的行号与对应值。我尝试了以下代码，但它只能处理df1的第一行，无法覆盖所有行。希望修改代码适配所有行，或者提供其他可行代码。

vals_to_find = set(df1.iloc[0])
mask = df2.loc[:, "Num1":].apply(lambda x: 
len(vals_to_find.intersection(x)) > 2, axis=1)
print(df2[mask])  

示例数据 #

df1 = pd.DataFrame([[5,10,21],[22,15,7],[6,23,10],[4,34,57]],columns = ['Num1','Num2','Num3'])


df2    = pd.DataFrame([[100,1,2,4,5,6,8],
                   [87,1,6,10,22,23,34],
                   [99,1,12,13,34,45,46],
                   [64,1,10,14,29,32,33],
                   [55,1,22,13,23,33,35],
                   [66,1,6,7,8,9,10],
                   [77,1,2,3,5,6,8],
                   [811,1,2,5,6,8,10], 
                   [118,1,7,8,22,44,56],
                   [117,1,66,44,47,87,91],
                   [299,2,4,7,20,21,22],
                   [187,3,6,10,12,23,39],
                   [199,4,12,24,34,56,57],
                   [264,3,7,8,9,10,33],
                   [50,6,8,10,23,33,35],
                   [212,4,6,12,18,19,20],
                   [45,3,7,23,35,56,88],
                   [801,1,2,4,6,28,39], 
                   [258,2,3,4,9,10,41],
                   [220,5,6,10,27,57,81]],
                   columns = ['Row', 'Num1','Num2','Num3','Num4','Num5','Num6'])

预期匹配结果 #

Row  Num1  Num2  Num3  Num4  Num5  Num6
1    87     1     6    10    22    23    34   Match 6,23,10
11  187     3     6    10    12    23    39   Match 6,23,10
14   50     6     8    10    23    33    35   Match 6,23,10
12  199     4    12    24    34    56    57   Match 4,34,57

基础实现（清晰直观） #

遍历df1的每一行，对每行数据生成匹配条件，筛选df2中符合条件的行并记录匹配数值：

import pandas as pd

# 示例数据（若已定义可省略）
df1 = pd.DataFrame([[5,10,21],[22,15,7],[6,23,10],[4,34,57]],columns = ['Num1','Num2','Num3'])
df2 = pd.DataFrame([[100,1,2,4,5,6,8],
                   [87,1,6,10,22,23,34],
                   [99,1,12,13,34,45,46],
                   [64,1,10,14,29,32,33],
                   [55,1,22,13,23,33,35],
                   [66,1,6,7,8,9,10],
                   [77,1,2,3,5,6,8],
                   [811,1,2,5,6,8,10], 
                   [118,1,7,8,22,44,56],
                   [117,1,66,44,47,87,91],
                   [299,2,4,7,20,21,22],
                   [187,3,6,10,12,23,39],
                   [199,4,12,24,34,56,57],
                   [264,3,7,8,9,10,33],
                   [50,6,8,10,23,33,35],
                   [212,4,6,12,18,19,20],
                   [45,3,7,23,35,56,88],
                   [801,1,2,4,6,28,39], 
                   [258,2,3,4,9,10,41],
                   [220,5,6,10,27,57,81]],
                   columns = ['Row', 'Num1','Num2','Num3','Num4','Num5','Num6'])

result_list = []

# 遍历df1每行，匹配df2
for _, df1_row in df1.iterrows():
    target_set = set(df1_row.values)
    for df2_idx, df2_row in df2.iterrows():
        df2_vals = set(df2_row.loc['Num1':].values)
        common_vals = target_set.intersection(df2_vals)
        # 匹配条件：交集长度>2（即df1的3个值全匹配）
        if len(common_vals) > 2:
            temp_row = df2_row.copy()
            temp_row['Match'] = ', '.join(map(str, common_vals))
            result_list.append(temp_row)

# 转换为结果DataFrame
result_df = pd.DataFrame(result_list)
print(result_df)

优化版本（适配大数据量） #

针对行数超过200的场景，减少循环次数，用向量化操作提升效率：

import pandas as pd

# 预处理df1所有行的集合
df1_sets = [set(row) for _, row in df1.iterrows()]

# 定义函数：检查df2单行与所有df1行的匹配情况
def check_matches(row):
    row_vals = set(row.loc['Num1':].values)
    matches = []
    for s in df1_sets:
        common = s.intersection(row_vals)
        if len(common) > 2:
            matches.append(', '.join(map(str, common)))
    return '; '.join(matches) if matches else None

# 应用函数并筛选有匹配的行
df2['Match'] = df2.apply(check_matches, axis=1)
result_df = df2[df2['Match'].notna()]
print(result_df)

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