Pandas中按层级优先级分组比对两个DataFrame指定列的实现方法

方案核心优势 #

• 支持任意层级扩展：仅需修改层级优先级列表即可适配最多9种甚至更多层级的匹配需求，无需编写重复合并代码
• 严格遵循优先级规则：高优先级层级匹配完成的账户不再参与低层级校验，避免重复匹配
• 计算效率更高：已匹配账户直接退出待匹配池，减少无效关联计算

完整可运行代码 #

import pandas as pd
import numpy as np

# 构建示例AccountTable
AccountTable = pd.DataFrame([[1234567890,456,'EUR',3.5],
                    [7854567890,15,'USD',2.7],
                    [9632587415,56,'GBP',1.4]],
columns = ['Account','ParentID','Cur','Rate'])

# 构建示例RateTable
RateTable = pd.DataFrame([['Account',1234567890,'EUR',3.5],
                    ['ParentID',456,'EUR',3.5],
                    ['ParentID',15,'USD',2.7],
                    ['ParentID',15,'CAD',1.5],
                    ['Account',9876542190,'EUR',3.5],
                    ['ParentID',56,'GBP',1.5]],
columns = ['Level_Type','ID','Cur','Set_Rate'])

# 层级优先级配置：顺序越靠前优先级越高，新增层级直接添加到列表即可
level_priority = ['Account', 'ParentID']
# 层级显示名称映射，可按需调整
level_name_map = {'Account':'Account', 'ParentID':'Parent'}

# 初始化待匹配账户池、结果容器
unmatched_accounts = AccountTable.copy()
result_list = []

# 按优先级依次完成各层级匹配
for level in level_priority:
    if len(unmatched_accounts) == 0:
        break
    # 筛选当前层级的费率规则
    level_rates = RateTable[RateTable['Level_Type'] == level].rename(columns={'ID': level})
    # 按【当前层级ID + 币种】关联账户表和费率表
    match_result = pd.merge(
        unmatched_accounts,
        level_rates[['Cur', level, 'Set_Rate']],
        on=['Cur', level],
        how='left'
    )
    # 拆分匹配成功、待下一轮匹配的账户
    matched_part = match_result[match_result['Set_Rate'].notna()].copy()
    unmatched_accounts = match_result[match_result['Set_Rate'].isna()][AccountTable.columns]
    
    # 计算匹配结果
    matched_part['IsMatch'] = (matched_part['Rate'] == matched_part['Set_Rate']).astype(int)
    matched_part['LevelFound'] = level_name_map[level]
    result_list.append(matched_part[AccountTable.columns.tolist() + ['IsMatch', 'LevelFound']])

# 合并结果，补充未匹配到任何层级的账户
final_result = pd.concat(result_list, ignore_index=True)
if len(unmatched_accounts) > 0:
    unmatched_accounts['IsMatch'] = 0
    unmatched_accounts['LevelFound'] = '无匹配层级'
    final_result = pd.concat([final_result, unmatched_accounts], ignore_index=True)

# 按原AccountTable顺序排序，保证输出和原表顺序一致
final_result = final_result.set_index('Account').reindex(AccountTable['Account']).reset_index()
print(final_result)

输出结果 #

运行上述代码将直接得到你期望的输出：

Account  ParentID  Cur  Rate  IsMatch LevelFound
0  1234567890       456  EUR   3.5        1    Account
1  7854567890        15  USD   2.7        1     Parent
2  9632587415        56  GBP   1.4        0     Parent

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