如何用Pandas基于限定表格为Excel数值表格实现单元格色码标注

需求 #

需要生成符合以下规则的Excel文件：数值表格中每个单元格，根据其所属类型（aa/bb）和对应Measure的上下限，判断是否在范围内并进行色码标注。现有两个核心表格：

• 限定表格：存储aa、bb两类下，Measure（A/B/C）的上下限值
• 数值表格：列名包含_aa/_bb类型标识，数值列数量不固定

解决方案 #

使用pandas处理数据，结合openpyxl引擎实现Excel单元格的条件格式设置，步骤如下：

1. 解析列的类型与对应Measure #

从数值表格的列名中提取类型（aa/bb），同时匹配限定表格中的Measure索引。

2. 定义条件格式规则 #

针对每个数值列，根据其类型和Measure，获取对应的上下限，设置单元格着色规则（例如：在范围内标绿色，超出标红色）。

3. 写入Excel并应用格式 #

用pd.ExcelWriter打开文件，先写入数据，再遍历每个数值列，应用条件格式。

完整代码示例 #

import pandas as pd
from openpyxl.styles import PatternFill
from openpyxl.utils.dataframe import dataframe_to_rows

# ---------------------- 初始化数据（用户提供的示例数据） ----------------------
df_limits_1 = pd.DataFrame({"Measure": ["A", "B", "C"],
                          "lower limit": [0.1, 1, 10],
                          "upper limit": [1.2, 3.4, 100]})
df_limits_1 = df_limits_1.set_index("Measure")

df_limits_2 = pd.DataFrame({"Measure": ["A", "B", "C"],
                          "lower limit": [0.3, 2, 15],
                          "upper limit": [1.1, 5, 28]})
df_limits_2 = df_limits_2.set_index("Measure")

df_limits_1.columns = pd.MultiIndex.from_product([['aa'], df_limits_1.columns])
df_limits_2.columns = pd.MultiIndex.from_product([['bb'], df_limits_2.columns])
df_limits = pd.concat([df_limits_1, df_limits_2], axis=1)

df_values = pd.DataFrame({"Measure": ["A", "B", "C"],
                           "value1_aa": [1, 5, 34],
                           "value1_bb": [0.2, 3, 21],
                           "value2_aa": [0.3, 2, 23],
                           "value2_bb": [1, 0.9, 12]})
df_values = df_values.set_index("Measure")

# ---------------------- 实现色码标注逻辑 ----------------------
# 定义填充样式：范围内绿色，超出红色
fill_in_range = PatternFill(start_color="90EE90", end_color="90EE90", fill_type="solid")
fill_out_range = PatternFill(start_color="FFCCCB", end_color="FFCCCB", fill_type="solid")

# 创建Excel写入对象，使用openpyxl引擎
with pd.ExcelWriter('colored_values.xlsx', engine='openpyxl') as writer:
    # 写入数值表格到Excel
    df_values.to_excel(writer, sheet_name='Values', index=True)
    # 获取工作表对象
    ws = writer.sheets['Values']
    
    # 遍历数值表格的每一列（跳过索引列）
    for col_idx, col_name in enumerate(df_values.columns, start=2):  # 列从B开始（索引2）
        # 解析列名中的类型（aa/bb）
        _, category = col_name.split('_')
        # 遍历每一行（跳过表头行）
        for row_idx, measure in enumerate(df_values.index, start=2):  # 行从第二行开始（索引2）
            # 获取当前单元格的数值
            cell_value = df_values.loc[measure, col_name]
            # 获取对应类型和Measure的上下限
            lower = df_limits.loc[measure, (category, 'lower limit')]
            upper = df_limits.loc[measure, (category, 'upper limit')]
            
            # 判断是否在范围内，设置填充样式
            cell = ws.cell(row=row_idx, column=col_idx)
            if lower <= cell_value <= upper:
                cell.fill = fill_in_range
            else:
                cell.fill = fill_out_range

说明 #

• 代码中定义了两种填充样式，可根据需求修改颜色代码
• 列索引从2开始是因为Excel中A列是索引列，数值列从B列开始
• 行索引从2开始是因为第一行是表头

内容的提问来源于stack exchange，提问作者s.cerioli