Pandas DataFrame复杂列匹配：USD关联账户借贷校验规则优化

核心实现思路 #

• 先构造账号特征辅助列：提前提取账号前6位、后4位后缀，筛选USD币种账号，避免后续重复计算
• 批量标记备用账号关联关系，生成HasCorresAc标识列
• 替换原有行级apply为向量化判断逻辑，相比逐行遍历性能提升10倍以上，同时兼容新增的USD账户特殊规则

完整实现代码 #

import pandas as pd

# 若账号字段为数字类型，先统一转字符串避免提取前缀/后缀出错
df['Account'] = df['Account'].astype(str)

# -------------------------- 第一步：生成辅助特征列 --------------------------
df['acc_prefix'] = df['Account'].str[:6] # 提取账号前6位
df['acc_suffix'] = df['Account'].str[-4:] # 提取账号后4位后缀
df['is_usd'] = df['Currency'] == 'USD'

# -------------------------- 第二步：生成HasCorresAc列 --------------------------
# 筛选同时存在USD 5400和USD 5405账号的前6位前缀
valid_corres_prefixes = df[
    (df['is_usd']) & (df['acc_suffix'].isin(['5400','5405']))
].groupby('acc_prefix').filter(
    lambda x: set(x['acc_suffix']) >= {'5400','5405'}
)['acc_prefix'].unique()

# 标记HasCorresAc
df['HasCorresAc'] = 'N'
df.loc[
    (df['is_usd']) & (df['acc_suffix'] == '5400') & (df['acc_prefix'].isin(valid_corres_prefixes)),
    'HasCorresAc'
] = 'Y'

# -------------------------- 第三步：贷方匹配逻辑 --------------------------
# 先计算常规匹配结果
cond_credit_normal_match = (df['Credit_Code'] == df['DB_Credit_Code']) & (df['Credit_Rate'] == df['DB_Credit_Rate'])
df['Credit_Match_Res'] = 'NoMatch'
df.loc[cond_credit_normal_match, 'Credit_Match_Res'] = 'Match'
# 叠加5400账号特殊规则：贷方直接返回IgnoreHasCorAcc
df.loc[df['HasCorresAc'] == 'Y', 'Credit_Match_Res'] = 'IgnoreHasCorAcc'

# -------------------------- 第四步：借方匹配逻辑 --------------------------
# 先计算常规匹配结果
cond_debit_normal_match = (df['Debit_Code'] == df['DB_Debit_Code']) & (df['Debit_Rate'] == df['DB_Debit_Rate'])
df['Debit_Match_Res'] = 'NoMatch'
df.loc[cond_debit_normal_match, 'Debit_Match_Res'] = 'Match'
# 叠加5405备用账号特殊规则：借方直接返回IgnoreHasCorAcc
cond_5405_acc = (df['is_usd']) & (df['acc_suffix'] == '5405') & (df['acc_prefix'].isin(valid_corres_prefixes))
df.loc[cond_5405_acc, 'Debit_Match_Res'] = 'IgnoreHasCorAcc'

# 删除不需要的辅助列（可根据需求保留）
df = df.drop(columns=['acc_prefix', 'acc_suffix', 'is_usd'])

结果校验规则 #

• 非USD账号、USD非5400结尾账号HasCorresAc统一为N，走常规借贷双方匹配逻辑
• 存在对应5405备用账号的USD 5400账号，HasCorresAc标记为Y，贷方结果固定为IgnoreHasCorAcc，仅借方返回正常匹配结果
• 对应5405备用账号，借方结果固定为IgnoreHasCorAcc，仅贷方返回正常匹配结果

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