基于双列（含key2反转匹配）比较DataFrame并输出匹配及分组结果

实现代码 #

import pandas as pd

# 构造示例数据
df1 = pd.DataFrame({
    'name': ['a1', 'a2', 'a3', 'a3'],
    'key1': [1, 2, 3, 4],
    'key2': ['K0', 'K1', 'K0', 'K1'],
    'A': ['A0', 'A1', 'A2', 'A3'],
    'B': ['B0', 'B1', 'B2', 'B3']
})

df2 = pd.DataFrame({
    'key1': [1, 2, 3, 4],
    'key2': ['K0', 'K0', '0K', '1K']
})

# 生成反转后的key2字段
df2['rev_key2'] = df2['key2'].str[::-1]

# ---------------------- 生成df3 ----------------------
# 小数据量直接用集合匹配即可
match_pairs = set()
for _, row in df2.iterrows():
    match_pairs.add((row['key1'], row['key2']))
    match_pairs.add((row['key1'], row['rev_key2']))
df3 = df1[df1.apply(lambda x: (x['key1'], x['key2']) in match_pairs, axis=1)].reset_index(drop=True)

# 大数据量推荐用向量化merge操作，性能更高
# df2_1 = df2[['key1', 'key2']]
# df2_2 = df2[['key1', 'rev_key2']].rename(columns={'rev_key2':'key2'})
# df2_all = pd.concat([df2_1, df2_2], ignore_index=True)
# df3 = df1.merge(df2_all, on=['key1', 'key2'], how='inner').drop_duplicates().reset_index(drop=True)

# ---------------------- 生成df4 ----------------------
# 按name分组，所有字段转字符串后用逗号拼接
df4 = df3.astype(str).groupby('name', as_index=False).agg(','.join)

# 输出结果
print("df3:\n", df3)
print("\ndf4:\n", df4)

逻辑说明 #

1. 匹配规则实现：把df2每行的(key1,原key2)、(key1,反转后key2)两种组合全部存入匹配集合，直接判断df1的行键是否在集合内即可完成筛选，逻辑清晰易维护。
2. 聚合实现：先统一把所有列转为字符串类型，再按name分组调用字符串拼接方法，一步得到要求的聚合结果。

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