pandas DataFrame多条件规则应用实现账户利率匹配校验

实现逻辑 #

完全对齐你给出的校验规则，分两个部分处理：

1. 先判断每行的校验分支：走非标准利率校验还是标准利率校验，对应生成利率匹配结果字段
2. 单独处理协商利率为Y的行，生成两个指标字段的匹配结果

如果数据量不大可以用apply逐行处理，逻辑易读易维护；如果是大型机下来的大批量数据，推荐用向量化操作，性能提升非常明显。

完整实现代码 #

小数据量版本（易读） #

import pandas as pd
import numpy as np

# 构造原始DataFrame
df = pd.DataFrame([[1234567890,3.5,'GG','N','N','Y',np.NaN,np.NaN,'N','N',3.5,'GG'],
                    [7854567890,np.NaN,'GG','N','N','N',np.NaN,'GG','N','N',3.5,'GG'],
                    [9876542190,3.5,'FF','N','N','Y',np.NaN,np.NaN,'N','Y',3.5,'FI'],
                    [9632587415,3.5,'GG','N','N','N',3,'GG','N','N',3.5,'GG']],
columns = ['Account','Account_Spread','Account_Swing','indict_1','indict_2','Negotiated_Rate',
           'Non_std_Spread','Non_std_Code','Non_std_indict_1','Non_std_indict_2','Std_Spread','Std_Swing'])

# 生成利率匹配结果字段
def get_match_status(row):
    # 触发非标准利率校验
    has_non_std = pd.notna(row['Non_std_Spread']) or pd.notna(row['Non_std_Code'])
    if has_non_std and row['Negotiated_Rate'] == 'N':
        # 仅非空的非标准字段需要校验
        spread_match = (row['Account_Spread'] == row['Non_std_Spread']) if pd.notna(row['Non_std_Spread']) else True
        code_match = (row['Account_Swing'] == row['Non_std_Code']) if pd.notna(row['Non_std_Code']) else True
        return 'MatchOnNSR' if (spread_match and code_match) else 'MismatchOnNSR'
    # 触发标准利率校验
    else:
        spread_match = row['Account_Spread'] == row['Std_Spread']
        swing_match = row['Account_Swing'] == row['Std_Swing']
        return 'MatchOnSR' if (spread_match and swing_match) else 'MismatchOnSR'

df['Is_Match'] = df.apply(get_match_status, axis=1)

# 生成协商利率指标匹配字段
df['Match_indict_1'] = df.apply(lambda x: x['indict_1'] == x['Non_std_indict_1'] if x['Negotiated_Rate'] == 'Y' else np.nan, axis=1)
df['Match_indict_2'] = df.apply(lambda x: x['indict_2'] == x['Non_std_indict_2'] if x['Negotiated_Rate'] == 'Y' else np.nan, axis=1)

print(df)

大数据量高性能版本（向量化实现） #

# 非标准校验触发掩码
non_std_mask = (df['Non_std_Spread'].notna() | df['Non_std_Code'].notna()) & (df['Negotiated_Rate'] == 'N')
# 非标准利率匹配结果
spread_match_ns = (df['Account_Spread'] == df['Non_std_Spread']) | df['Non_std_Spread'].isna()
code_match_ns = (df['Account_Swing'] == df['Non_std_Code']) | df['Non_std_Code'].isna()
ns_match = spread_match_ns & code_match_ns

# 标准利率匹配结果
spread_match_s = df['Account_Spread'] == df['Std_Spread']
code_match_s = df['Account_Swing'] == df['Std_Swing']
s_match = spread_match_s & code_match_s

# 赋值利率匹配字段
df['Is_Match'] = np.where(non_std_mask, 
                          np.where(ns_match, 'MatchOnNSR', 'MismatchOnNSR'),
                          np.where(s_match, 'MatchOnSR', 'MismatchOnSR'))

# 协商利率指标匹配
nego_mask = df['Negotiated_Rate'] == 'Y'
df['Match_indict_1'] = np.where(nego_mask, df['indict_1'] == df['Non_std_indict_1'], np.nan)
df['Match_indict_2'] = np.where(nego_mask, df['indict_2'] == df['Non_std_indict_2'], np.nan)

注：你给出的预期示例中第四行的MismatchOnSNR为笔误，代码中统一修正为命名规范的MismatchOnNSR，和非标准利率的标识前缀对齐。

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