如何为Pandas DataFrame的Util%列设置条件格式并导出至Excel

需求说明 #

需要对Pandas DataFrame中的Util %列执行条件格式设置，导出到Excel后能看到高亮单元格，颜色规则如下：

• 绿色（#BAFFC9）：0.00% - 49.99%
• 黄色：50.00% - 79.99%
• 红色：≥80.00% 或 值为inf%

数据示例（df1） #

Customer    Code Util %
BlackRock   BFK 
BlackRock   BFK 
BlackRock   BFK 
BlackRock   BFK 0.12%
BlackRock   BFK 0.00%
        
Ohio        OPS 
Ohio        OPS 18.75%
        
Fund        CEN 
Fund        CEN 
Fund        CEN 
Fund        CEN 
Fund        CEN 2.47%
Fund        CEN 6.21%
        
Rock        ROS 
Rock        ROS 72.81%
        
Square      SCM 
Square      SCM 25.19%
        
Dwight      DWIG    
Dwight      DWIG 0.00%
Dwight      DWIG 17.61%
        
Millennium  MIC 
Millennium  MIC inf%
Millennium  MIC inf%

尝试的代码 #

def highlight_rows(col):
    value=col.loc['Util %']
    if value >=0.00 and value <=0.4999:
        color = '#BAFFC9' #Green
    elif value >= 0.50 and value <=0.79999:
        color = 'Yellow' # Green
    elif value >=0.80:
        color = 'Red' # Green
    elif value == inf%:
        color = 'Red' # Green
    else:
        pass
    return ['background-color: {}'.format(color) for r in col]

df.style.apply(highlight_rows)
df.to_excel("report.xlsx")

问题分析与解决方案 #

原代码存在核心问题：Util %列是带%的字符串/空值无法直接做数值比较、高亮函数逻辑错误、导出方式未保留样式。以下是修正后的完整实现：

步骤1：数据清洗

先将Util %列转换为可计算的数值类型：

import pandas as pd
import numpy as np

# 处理Util %列：清空空值、去掉%符号、转浮点数，inf%转为inf
df['Util %'] = df['Util %'].replace('', np.nan)
df['Util %'] = df['Util %'].str.replace('%', '', regex=False).astype(float) / 100
df['Util %'] = df['Util %'].replace(np.nan, np.nan)  # 保留空值
df.loc[df['Util %'] == 'inf', 'Util %'] = np.inf

步骤2：编写正确的高亮函数

针对Util %列的每个单元格设置背景色：

def highlight_util_cell(val):
    if pd.isna(val):
        return ''  # 空值不设置颜色
    if 0 <= val <= 0.4999:
        return 'background-color: #BAFFC9'
    elif 0.5 <= val <= 0.7999:
        return 'background-color: yellow'
    elif val >= 0.8 or np.isinf(val):
        return 'background-color: red'
    else:
        return ''

步骤3：应用样式并导出Excel

# 仅对Util %列应用样式
styled_df = df.style.applymap(highlight_util_cell, subset=['Util %'])

# 导出带样式的Excel
with pd.ExcelWriter('report.xlsx') as writer:
    styled_df.to_excel(writer, index=False)

关键说明 #

• 使用applymap对单个单元格应用样式，subset参数限定只处理目标列，避免不必要的计算
• 数据清洗是核心前提，必须将百分比字符串转为浮点数才能进行数值判断
• 必须通过ExcelWriter导出样式化后的DataFrame，直接使用df.to_excel会丢失样式

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