如何扩展Python脚本实现多组Service与CI匹配校验及高亮

需求背景 #

拥有一组预定义的Service与CI对应关系：

需要从呼叫日志提取的Excel数据中，高亮所有与上述预定义关系不匹配的行，但现有脚本仅能处理单个Service的判断，其余合法组合会被错误标红。

现有问题 #

现有代码只针对单个Service生成掩码，手动添加多个掩码会导致逻辑繁琐且可维护性差，无法覆盖所有合法组合。

优化方案 #

将预定义的合法Service-CI组合整理成可查询的集合，通过判断每行的(Service, CI)是否属于合法集合，生成统一的掩码，再应用样式标红不匹配的行。

完整代码 #

from pathlib import Path
import pandas as pd
import numpy as np

# 读取呼叫日志数据
extract = Path.cwd() / "extract.xlsx"  # 修复原代码的路径拼接语法错误
df_extract = pd.read_excel(extract)

# 定义预定义的合法Service-CI组合
valid_pairs = [
    ("Non-Financial Risk Management South Africa", "Aravo"),
    ("Non-Financial Risk Management South Africa", "Business Resilience"),
    ("Non-Financial Risk Management South Africa", "Change Risk Management"),
    ("First Line Control Attestation South Africa", "Control First"),
    ("Group Audit Assurance South Africa", "DigiAud"),
    ("Group Governance Advisory and Support South Africa", "Diligent Boardbooks")
]

# 生成掩码：True表示行在合法组合中，False表示不匹配
is_valid = df_extract.apply(lambda row: (row['Service'], row['CI']) in valid_pairs, axis=1)

# 应用样式：不匹配的行标红
(df_extract.style.apply(lambda x: np.where(is_valid, '', 'background-color: red'), axis=0)
   .to_excel('/output.xlsx', index=False))

关键说明 #

1. 路径修复：原代码中Path.cwd() "/extract.xlsx"存在语法错误，改为Path.cwd() / "extract.xlsx"符合Pathlib的正确用法。
2. 合法集合定义：用元组列表存储所有合法的Service-CI组合，后续新增组合只需在列表中添加即可，无需修改判断逻辑。
3. 掩码生成：通过apply遍历每行，判断(Service, CI)是否在合法集合中，生成统一的布尔掩码。
4. 样式应用：利用np.where对不匹配（is_valid为False）的行设置红色背景。

替代实现（大数量组合更高效） #

如果预定义组合较多，用DataFrame合并的方式判断性能更优：

from pathlib import Path
import pandas as pd
import numpy as np

extract = Path.cwd() / "extract.xlsx"
df_extract = pd.read_excel(extract)

# 将预定义组合转为DataFrame
valid_df = pd.DataFrame([
    {"Service": "Non-Financial Risk Management South Africa", "CI": "Aravo"},
    {"Service": "Non-Financial Risk Management South Africa", "CI": "Business Resilience"},
    {"Service": "Non-Financial Risk Management South Africa", "CI": "Change Risk Management"},
    {"Service": "First Line Control Attestation South Africa", "CI": "Control First"},
    {"Service": "Group Audit Assurance South Africa", "CI": "DigiAud"},
    {"Service": "Group Governance Advisory and Support South Africa", "CI": "Diligent Boardbooks"}
])

# 用merge标记合法行
df_extract['is_valid'] = df_extract.merge(valid_df, on=['Service', 'CI'], how='left').notna().any(axis=1)

# 应用样式
(df_extract.style.apply(lambda x: np.where(df_extract['is_valid'], '', 'background-color: red'), axis=0)
   .to_excel('/output.xlsx', index=False))

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