如何将预测Diagnostic_Score映射至含运算符的百分位对照表？

解决方案 #

你的思路是可行的，但可以通过解析规则的方式实现更高效的自动匹配，无需手动构建大量条件。

实现步骤 #

1. 解析映射表规则：拆分norms中Diagnostic_Score的运算符和数值，统一格式便于后续比较。
2. 精确匹配优先：先匹配完全相等的分数记录。
3. 范围匹配补全：对未匹配的记录，根据解析出的运算符和数值自动判断所属范围。

完整代码 #

import pandas as pd
import numpy as np

# 示例数据
d = {"percentile": {0: 1, 1: 1, 2: 1, 3: 1, 4: 1, 5: 1, 6: 1, 7: 1, 8: 1, 9: 2, 10: 2, 11: 2, 12: 2, 13: 2, 14: 2, 15: 2, 16: 2, 17: 2}, "Subject": {0: "Math", 1: "Math", 2: "Math", 3: "Math", 4: "Math", 5: "Math", 6: "Math", 7: "Math", 8: "Math", 9: "Math", 10: "Math", 11: "Math", 12: "Math", 13: "Math", 14: "Math", 15: "Math", 16: "Math", 17: "Math"}, "Term": {0: "Fall", 1: "Fall", 2: "Fall", 3: "Fall", 4: "Fall", 5: "Fall", 6: "Fall", 7: "Fall", 8: "Fall", 9: "Fall", 10: "Fall", 11: "Fall", 12: "Fall", 13: "Fall", 14: "Fall", 15: "Fall", 16: "Fall", 17: "Fall"}, "Grade_Level": {0: 0, 1: 1, 2: 2, 3: 3, 4: 4, 5: 5, 6: 6, 7: 7, 8: 8, 9: 0, 10: 1, 11: 2, 12: 3, 13: 4, 14: 5, 15: 6, 16: 7, 17: 8}, "Diagnostic_Score": {0: "<=296", 1: "<=322", 2: "<=352", 3: "<=372", 4: "<=390", 5: "<=405", 6: "<=412", 7: "<=423", 8: "<=434", 9: "297", 10: "323", 11: "353", 12: "373", 13: "391", 14: "406", 15: "413", 16: "424", 17: "435"}}

norms = pd.DataFrame(d)

df = pd.DataFrame({"Term": ["Fall", "Fall", "Fall", "Fall"],
               "Subject": ["Math", "Math", "Math", "Math"],
               "Grade_Level": [0, 3, 5, 7],
               "Predicted Score": [290, 300, 406, 424]})

# 1. 解析norms的规则字段
norms['operator'] = norms['Diagnostic_Score'].str.extract(r'([<>]=?)', expand=False).fillna('==')
norms['score'] = norms['Diagnostic_Score'].str.extract(r'(\d+)', expand=False).astype(int)

# 2. 精确匹配完全相等的记录
merged_exact = pd.merge(
    df,
    norms[norms['operator'] == '=='],
    left_on=['Term', 'Subject', 'Grade_Level', 'Predicted Score'],
    right_on=['Term', 'Subject', 'Grade_Level', 'score'],
    how='left'
)

# 3. 处理未匹配的记录，应用范围条件
unmatched = merged_exact[merged_exact['percentile'].isna()].drop(columns=['percentile', 'Diagnostic_Score', 'operator', 'score'])

def match_range(row):
    # 筛选当前记录对应的分组规则
    group_rules = norms[
        (norms['Term'] == row['Term']) &
        (norms['Subject'] == row['Subject']) &
        (norms['Grade_Level'] == row['Grade_Level'])
    ]
    # 遍历规则，找到符合条件的百分位
    for _, rule in group_rules.iterrows():
        if rule['operator'] == '<=' and row['Predicted Score'] <= rule['score']:
            return rule['percentile']
        elif rule['operator'] == '>=' and row['Predicted Score'] >= rule['score']:
            return rule['percentile']
    return np.nan

unmatched['percentile'] = unmatched.apply(match_range, axis=1)

# 合并结果并整理格式
final_result = pd.concat([merged_exact.dropna(subset=['percentile']), unmatched]).sort_index()
final_result = final_result[['Term', 'Subject', 'Grade_Level', 'Predicted Score', 'percentile']].reset_index(drop=True)

print(final_result)

输出结果 #

Term Subject  Grade_Level  Predicted Score  percentile
0  Fall    Math            0              290           1
1  Fall    Math            3              300           1
2  Fall    Math            5              406           2
3  Fall    Math            7              424           2

方案优势 #

• 无需手动编写所有匹配条件，规则变化时只需保证解析逻辑正确即可。
• 兼容精确匹配和范围匹配两种场景，覆盖所有记录。

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