如何对含Customer/Location的两个DataFrame外连接且避免全量行组合？

问题背景 #

现有两个DataFrame：Before和After，均包含Customer和Location列，每行代表客户的一条服务车道。需从客户视角分析前后车道变化，满足以下要求：

• 优先匹配两个表中[Customer, Location]完全相同的行
• 仅存在于单侧表的车道需可复现地任意配对
• 保留两个表的所有行，对前后Location不同或仅单侧存在的行标记变化
• 最终输出需包含Location Before、Location After及变化标记Flag

原始数据 #

import pandas as pd

# Before表数据
a = {'Customer':['A','B','C','C','D','D','E','E','F','F','F','G','G','G','H','H','H','I','I','J','J'],
     'Location':[1,1,1,2,3,4,6,7,1,2,3,1,2,3,1,2,3,7,8,1,2]}
Before = pd.DataFrame(a)

# After表数据
b = {'Customer':['A','B','C','C','D','D','E','E','F','F','G','G','H','H','I','I','I','J','J','J',],
     'Location':[1,2,1,2,3,5,8,9,1,3,1,4,5,6,7,8,9,3,4,5]}
After = pd.DataFrame(b)

# 期望输出示例
c = {'Customer':['A','B','C','C','D','D','E','E','F','F','F','G','G','G','H','H','H','I','I','I','J','J','J',],
     'Location A':[1,1,1,2,3,4,6,7,1,2,3,1,2,3,1,2,3,7,8,None,1,2,None],
     'Location B':[1,2,1,2,3,5,8,9,1,None,3,1,4,None,5,6,None,7,8,9,3,4,5]}
desired_output = pd.DataFrame(c)

解决方案代码 #

import pandas as pd

# 1. 为每个客户的行添加组内序号，保证非匹配行配对可复现
Before['match_idx'] = Before.groupby('Customer').cumcount()
After['match_idx'] = After.groupby('Customer').cumcount()

# 2. 优先匹配[Customer, Location]完全一致的行，区分匹配状态
matched = pd.merge(
    Before, After, 
    on=['Customer', 'Location'], 
    how='outer', 
    suffixes=('_before', '_after'), 
    indicator=True
)

# 3. 拆分已匹配、仅Before存在、仅After存在的行
matched_rows = matched[matched['_merge'] == 'both'].drop(columns=['match_idx_before', 'match_idx_after', '_merge'])
unmatched_before = matched[matched['_merge'] == 'left_only'].drop(columns=['Location_after', '_merge'])
unmatched_after = matched[matched['_merge'] == 'right_only'].drop(columns=['Location_before', '_merge'])

# 4. 对未匹配行按客户+组内序号配对
unmatched_paired = pd.merge(
    unmatched_before, unmatched_after, 
    on=['Customer', 'match_idx'], 
    how='outer', 
    suffixes=('_before', '_after')
)

# 5. 合并结果并整理列名、顺序
result = pd.concat([matched_rows, unmatched_paired], ignore_index=True)
result = result[['Customer', 'Location_before', 'Location_after']].rename(columns={
    'Location_before': 'Location Before',
    'Location_after': 'Location After'
})

# 6. 添加变化标记Flag
result['Flag'] = result.apply(
    lambda row: '无变化' if pd.notna(row['Location Before']) and pd.notna(row['Location After']) and row['Location Before'] == row['Location After'] 
                else '新增车道' if pd.isna(row['Location Before']) 
                else '删除车道' if pd.isna(row['Location After']) 
                else '车道变更',
    axis=1
)

# 按客户排序对齐期望输出顺序
result = result.sort_values(by=['Customer', 'Location Before', 'Location After'], na_position='last').reset_index(drop=True)

print(result)

代码说明 #

• 组内序号：通过cumcount()为每个客户的行分配连续序号，确保单侧存在的车道能按固定规则配对，保证结果可复现。
• 优先匹配：利用merge的indicator参数精准筛选完全匹配的行，同时分离单侧存在的行。
• 未匹配行处理：按客户+序号配对单侧行，避免跨客户乱配。
• 变化标记：根据前后Location的存在状态和是否相等，直观标记四种变化类型。

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