如何使用pandas包对Excel设置条件格式？

最新推荐文章于 2025-02-08 09:39:23 发布

原创最新推荐文章于 2025-02-08 09:39:23 发布 · 625 阅读

CC 4.0 BY-SA版权

文章标签：

在数据处理和分析领域，Pandas 是一个不可或缺的工具。它不仅能够高效地处理大规模数据集，还提供了丰富的功能来简化数据操作。然而，很多时候我们不仅仅需要处理数据，还需要将处理后的结果以美观且直观的方式展示给用户。Excel 作为最常用的数据展示工具之一，其条件格式功能可以帮助我们高亮显示特定的数据，从而更好地传达信息。本文将详细介绍如何使用 Pandas 包对 Excel 设置条件格式，让你的数据展示更加专业和有说服力。

为什么选择 Pandas 和 Excel？

Pandas 的优势

强大的数据处理能力：Pandas 提供了 DataFrame 和 Series 数据结构，可以轻松处理各种复杂的数据操作。
丰富的功能：除了基本的数据读写，Pandas 还支持数据清洗、转换、聚合等多种高级操作。
与 Python 生态系统的集成：Pandas 可以无缝集成其他 Python 库，如 NumPy、Matplotlib 等，形成强大的数据科学工具链。

Excel 的优势

广泛使用：Excel 是企业中最常用的电子表格软件，几乎每个人都能熟练使用。
丰富的可视化功能：Excel 提供了多种图表和条件格式功能，可以直观地展示数据。
易于分享：Excel 文件可以轻松地在团队成员之间共享，便于协作。

安装必要的库

在开始之前，我们需要安装 Pandas 和 openpyxl 库。openpyxl 是一个用于读写 Excel 文件的 Python 库，我们将使用它来设置条件格式。

pip install pandas openpyxl

基本示例

读取和写入 Excel 文件

首先，我们来看一个简单的示例，如何使用 Pandas 读取和写入 Excel 文件。

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel('data.xlsx')

# 查看前几行数据
print(df.head())

# 写入 Excel 文件
df.to_excel('output.xlsx', index=False)

设置条件格式

接下来，我们使用 openpyxl 来设置条件格式。假设我们有一个包含销售数据的 DataFrame，我们希望将销售额超过 1000 的单元格高亮显示为红色。

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

# 读取 Excel 文件
df = pd.read_excel('sales_data.xlsx')

# 将 DataFrame 写入新的 Excel 文件
with pd.ExcelWriter('formatted_sales_data.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales', index=False)

# 加载工作簿和工作表
wb = load_workbook('formatted_sales_data.xlsx')
ws = wb['Sales']

# 设置条件格式
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        if cell.value > 1000:
            cell.fill = red_fill

# 保存文件
wb.save('formatted_sales_data.xlsx')

在这个示例中，我们首先读取了一个包含销售数据的 Excel 文件，并将其写入一个新的 Excel 文件。然后，我们加载了这个新的 Excel 文件，并使用 openpyxl 设置了条件格式，将销售额超过 1000 的单元格高亮显示为红色。

高级示例

多条件格式

假设我们不仅希望高亮显示销售额超过 1000 的单元格，还希望将销售额低于 500 的单元格高亮显示为黄色。

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

# 读取 Excel 文件
df = pd.read_excel('sales_data.xlsx')

# 将 DataFrame 写入新的 Excel 文件
with pd.ExcelWriter('formatted_sales_data.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales', index=False)

# 加载工作簿和工作表
wb = load_workbook('formatted_sales_data.xlsx')
ws = wb['Sales']

# 设置条件格式
red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
yellow_fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        if cell.value > 1000:
            cell.fill = red_fill
        elif cell.value < 500:
            cell.fill = yellow_fill

# 保存文件
wb.save('formatted_sales_data.xlsx')

动态条件格式

有时候，我们希望根据某些动态条件来设置格式。例如，我们希望将销售额高于平均值的单元格高亮显示。

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

# 读取 Excel 文件
df = pd.read_excel('sales_data.xlsx')

# 计算平均销售额
average_sales = df['Sales'].mean()

# 将 DataFrame 写入新的 Excel 文件
with pd.ExcelWriter('formatted_sales_data.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales', index=False)

# 加载工作簿和工作表
wb = load_workbook('formatted_sales_data.xlsx')
ws = wb['Sales']

# 设置条件格式
green_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')

for row in ws.iter_rows(min_row=2, min_col=2, max_col=2):
    for cell in row:
        if cell.value > average_sales:
            cell.fill = green_fill

# 保存文件
wb.save('formatted_sales_data.xlsx')

实战案例

案例背景

假设你是一家电商公司的数据分析师，负责分析各个产品的销售情况。你的任务是生成一份销售报告，并使用条件格式高亮显示表现优秀的商品。

数据准备

我们有一份包含产品名称、销售额和利润率的 Excel 文件 product_sales.xlsx。

代码实现

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

# 读取 Excel 文件
df = pd.read_excel('product_sales.xlsx')

# 计算平均销售额和利润率
average_sales = df['Sales'].mean()
average_profit_margin = df['Profit Margin'].mean()

# 将 DataFrame 写入新的 Excel 文件
with pd.ExcelWriter('formatted_product_sales.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sales Report', index=False)

# 加载工作簿和工作表
wb = load_workbook('formatted_product_sales.xlsx')
ws = wb['Sales Report']

# 设置条件格式
high_sales_fill = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
high_profit_margin_fill = PatternFill(start_color='0000FF', end_color='0000FF', fill_type='solid')

# 遍历所有行，设置条件格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=4):
    sales_cell = row[0]
    profit_margin_cell = row[1]
    
    if sales_cell.value > average_sales:
        sales_cell.fill = high_sales_fill
    
    if profit_margin_cell.value > average_profit_margin:
        profit_margin_cell.fill = high_profit_margin_fill

# 保存文件
wb.save('formatted_product_sales.xlsx')