Python逐行提取指定列最高/次高值匹配另一列生成标记字段求解

核心逻辑说明 #

• 按行独立处理每条数据，先把时间段和对应的呼叫成功率绑定为对应关系
• Match1严格遵循规则：先提取所有最高分对应的时间段，按是否有重复最高分走分支判断
• Match2专门修正了第二高分的定义：优先排除所有最高分的分数，从剩余分数中取最高值作为第二高分，不存在剩余分数时直接返回NO，完全匹配样例逻辑

完整实现代码 #

import pandas as pd

# 构造样例数据（替换成你自己的数据集即可）
data = [
    [1, 11, 8, 10, 11, 14, 0.62, 0.59, 0.59, 0.54],
    [2, 13, 15, 16, 18, 13, 0.57, 0.57, 0.57, 0.57],
    [3, 16, 9, 14, 16, 12, 0.67, 0.54, 0.48, 0.34],
    [4, 8, 11, 8, 12, 17, 0.58, 0.55, 0.43, 0.25]
]
cols = ['ID', 'Hour', 'Cus1', 'Cus2', 'Cus3', 'Cus4', 'Cus1_Score', 'Cus2_Score', 'Cus3_Score', 'Cus4_Score']
df = pd.DataFrame(data, columns=cols)

# 每行计算Match1、Match2的函数
def cal_match(row):
    # 绑定分数和对应时间段的映射
    score_cus_map = [
        (row['Cus1_Score'], row['Cus1']),
        (row['Cus2_Score'], row['Cus2']),
        (row['Cus3_Score'], row['Cus3']),
        (row['Cus4_Score'], row['Cus4'])
    ]
    # 计算Match1
    max_score = max(s for s, c in score_cus_map)
    max_cus_list = [c for s, c in score_cus_map if s == max_score]
    if len(max_cus_list) == 1:
        match1 = 'YES' if row['Cus1'] == row['Hour'] else 'NO'
    else:
        match1 = 'YES' if row['Hour'] in max_cus_list else 'NO'
    
    # 计算Match2
    lower_scores = [s for s, c in score_cus_map if s < max_score]
    if not lower_scores:
        match2 = 'NO'
    else:
        sec_score = max(lower_scores)
        sec_cus_list = [c for s, c in score_cus_map if s == sec_score]
        if len(sec_cus_list) == 1:
            match2 = 'YES' if row['Cus2'] == row['Hour'] else 'NO'
        else:
            match2 = 'YES' if row['Hour'] in sec_cus_list else 'NO'
    return pd.Series([match1, match2])

# 应用函数生成结果列
df[['Match1', 'Match2']] = df.apply(cal_match, axis=1)

# 输出结果
print(df)

运行结果 #

运行后得到的Match1、Match2和你提供的样例完全一致：

ID  Hour  Cus1  Cus2  Cus3  Cus4  ...  Cus4_Score  Match1  Match2
0   1    11     8    10    11    14  ...        0.54      NO     YES
1   2    13    15    16    18    13  ...        0.57     YES      NO
2   3    16     9    14    16    12  ...        0.34      NO      NO
3   4     8    11     8    12    17  ...        0.25      NO     YES

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