用 python 从读取 Excel 文件到生成可视化报告

据估计至少有 11 亿人在使用 Excel^1。事后看来，可以理解的是，它提供了一个易于使用的界面，让用户可以直观地执行几乎任何数据操作。

然而，尽管功能多样，Excel 也有一些缺点--在撰写本文时，它（只能）处理 1,048,576 行 x 16,384 列^2。这看起来似乎很多，但对于大数据世界来说却微不足道。此外，如果你想提供针对特定团队或个人的指标报告，你最终需要管理许多不同的 Excel 文件。

Python 提供了一种管理 Excel 任务的强大方法。利用 Python 处理大量数据和与操作系统交互的能力，用户可以同时生成多份报告。

在本篇文章中，我将介绍如何使用 Python 完成以下任务。

• 读写 Excel 文件
• 根据条件删除行
• 格式化列（数据类型）
• 创建新列
• 有条件地突出显示行
• 应用计算
• 创建图表
• 根据条件生成多个报告

情景：医疗分析师的手动噩梦

想象一下：你是一名医疗分析师，厌倦了重复性的工作。你定期从系统中提取病人预约数据，并将其存入 Excel 文件。你的任务是对数据进行清理和格式化，并提取属于特定保险提供商的患者，以便与公司共享。

原始数据中的每个条目都指一名患者，包含患者 ID、年龄、性别、医生、诊费和保险提供商。有五种可能的保险提供商值：Aetna、Cigna、United Health、Blue Cross 或 None（无保险患者）。

定制的报告，无混乱

每个保险提供商都会收到一份为其量身定制的精美报告，其中只包含其患者的信息。报告内容包括

• 计算税费的Consultation fees
• 格式化并突出显示的列，便于快速了解情况
• 按diagnosis类型分列的条形图可视化fees

不再需要杂乱无章的电子表格--只需如下所示的整洁、专业的报告即可共享。

但是，你不想每周手动为每个服务提供商创建一个单独的概述，你更想投入到更有趣的项目中去。因此，你可以绘制一张图表，计划如何摆脱这项乏味的工作。

解决方案

目标很简单：你可以随时向 Python 提供原始的数据集。它将自动进行处理，按保险提供商进行拆分，并生成格式化的 Excel 可视化报告。

任何数据从业者都知道，解决方案从来都不是单一的。Python 提供了多种处理 Excel 文件的库，每种库都有自己的优势。下面是几个库的简要介绍。

• Pandas 读取大型 Excel 文件的首选，因为它支持分块。这允许你以更小、更易于管理的部分加载和处理数据。有效处理的数据量超过了 Excel 通常所能处理的数据量。不过，它不提供格式化或样式化 Excel 文件的功能。
• XlsxWriter从零开始创建 Excel 报告的理想工具。它提供了大量格式化选项。遗憾的是，它是一个只能写入的库，你不能用它来读取 Excel 文件。
• OpenPyXL - 一个可以读取、写入和修改 Excel 文件的多功能库。它支持复杂的格式化和图表创建。

显然，OpenPyXL最适合我们的项目。

OpenPyXL

安装依赖项

首先，我们使用以下命令安装 OpenPyXL 库。

pip install openpyxl

导入库

然后，我们导入将要使用的库。OS - 用于与我们的操作系统交互，并在我们的资源库中查找文件。

import os 
import openpyxl 

读取 Excel 文件

我们可以使用以下代码行读取 Excel 工作簿并在 Excel 文件中选择所需的工作表。

#Read to workbook
workbook = openpyxl.load_workbook(
r"data/input/Healthcare_Appointments_Dataset.xlsx"
)
# 完整数据获取：在公众号：数据STUDIO 
# 找到原文：《不要在 Excel 中浪费时间了： 让 Python 完成工作》
# 一键三连后留言即可免费获取
#Access required sheet
sheet = workbook['Healthcare_Appointments_Dataset']

工作表对象代表 Excel 工作簿中的单个工作表，允许我们与数据和单元格交互。

显示列标题

要快速查看数据集中的列标题，我们可以按如下方法遍历第一行。

# print the values of the first row
for cell in sheet[1]:
    print(cell.value)
Appointment Date
Patient ID
Age
Gender
Diagnosis
Doctor
Visit Type
Appointment Duration
Consultation Fee
Insurance Provider
Is Follow-up Needed?

删除缺少患者 ID 的行

在我们的 Excel 文件中，有些行不包含患者 ID。创建一个函数来删除这些条目。

• 我们创建一个以 OpenPyXL 工作表对象为参数的函数。
• sheet.iter_rows()会生成工作表中所有行的可迭代行。
• 然后检查第二个值（患者 ID 为空）row[1].value == None。
• 然后，delete_rows()方法会删除row[0].row返回的索引号上的行。

# delete row with missing value in the column 'PatientId'
def delete_missing_patient_id_rows(sheet):
    for row in sheet.iter_rows():
        if row[1].value == None:
            sheet.delete_rows(row[0].row)

列类型格式化

在第二个函数中，我们要对数值 Calculation Fee进行格式化，并加上欧元符号。下面的函数可以实现这一功能：

• 我们再次使用带有 iter_rows() 方法的 for 循环。
• 我们检查第 9 列（row[8]）中的值是否不是None，以避免出错。
• number_format 设置为 ‘EUR#,##0.00’，将数值显示为欧元金额（例如，1,234.56 欧元）。

def apply_currency_format(sheet):
    for row in sheet.iter_rows():
        if row[8].value is not None:
            # Set the number format to Euro currency
            row[8].number_format = 'EUR#,##0.00'

添加新列：是否有保险

在这个函数中，我们将插入一个新列，以显示病人是否有保险。具体操作如下。

• 我们使用 insert_cols(11) 在位置 11 或列 “K”）创建新列。
• 新列的列头使用 sheet['K1'] = column_name 设置。
• 我们循环查看每一行（从第二行开始），并检查第 10 列（row[9]，保存保险信息）中的值是否为'None'。
• 如果是No，我们将新列的值设置为 No；否则，将其设置为 Yes。

# Function adds new column to 
def add_column(sheet, column_name):
    sheet.insert_cols(11)
    sheet['K1'] = column_name
    for row in sheet.iter_rows(min_row=2):
        if row[9].value == 'None':
            row[10].value = 'No'
        else:
            row[10].value = 'Yes'

条件格式化：突出显示复诊患者

为了便于发现复诊患者，我们将用绿色高亮显示这些行。下面的函数可以做到这一点。

• 我们从第二行开始循环查看各行（跳过标题）。
• 如果第 12 列（row[11]）中的值为True，则表示已安排随访。
• 我们使用 PatternFill 对该行的每个单元格应用绿色高亮（#FF77DD77）。

# Follow up rows green
def highlight_follow_ups(sheet):
    for row in sheet.iter_rows(min_row=2):
        if row[11].value == True:
            for cell in row:
                cell.fill = openpyxl.styles.PatternFill(
                    start_color='FF77DD77', 
                    end_color='FF77DD77', 
                    fill_type='solid')

应用税率计算

此函数更新 Calculation Fee 列，对每个值应用 15%的税率。

• 我们跳过表头，遍历工作表的每一行。
• 我们检查第9 列（“行[8]”，即 Calculation Fee）中的值是否为 None，以避免出错。
• 我们将费用乘以 1.15，实际上是加上 15%的税。

def apply_tax(sheet):
    for row in sheet.iter_rows(min_row=2):
        if row[8].value != None:
            row[8].value = float(row[8].value) * 1.15

为每个保险提供商保存单独的工作簿

以下函数为数据集中的每家保险公司创建一个新的 Excel 工作簿。它按保险公司筛选数据，并将相应的行保存到单独的文件中。

• 我们从 J 列中提取一组唯一的保险公司。
• 为每家公司创建一个新的工作簿，并复制相关行。
• 首先复制标题行，然后复制过滤后的行。
• 每个文件都以公司名称保存在 data/transformed/ 目录中。

# 为每家保险公司保存不同的工作簿 
def save_workbook_per_insurance(input_path):

    #在输入路径中查找以 .xlsx 结尾的文件
    xlsx_file = [file for file in os.listdir(input_path) if file.endswith('.xlsx')]
    workbook = openpyxl.load_workbook(input_path + "/"+ xlsx_file[0])
    sheet = workbook.active

    insurance_companies = sheet['J']
    insurance_companies = set([cell.value for cell in insurance_companies])
    for company in insurance_companies:
        new_workbook = openpyxl.Workbook()
        new_sheet = new_workbook.active
        new_sheet.title = company
        # 在新工作表中添加标题行，并标注公司名称
        
        new_sheet.append([cell.value for cell in sheet[1]]) # Copy the header
        for row in sheet.iter_rows(min_row=2):
            if row[9].value == company:
                new_sheet.append([cell.value for cell in row])
        new_workbook.save(f'data/transformed/{company}.xlsx')

创建汇总表和条形图

此功能在 Excel 文件的第二个选项卡中生成汇总表，显示按诊断分组的总诊费。这些汇总数据将作为下一步创建条形图的基础。

• 我们创建一个diagnosis_fees字典来存储每个诊断的总费用。
• 函数遍历每一行，提取诊断和诊费。
• 如果诊断diagnosis已存在于字典中，我们会添加费用；否则，我们会创建一个新条目。
• 最后，创建名为 Summary 的新工作表，并添加结果。

def add_summary(workbook, sheet):
    diagnosis_fees = {}

    # 遍历数据行，按诊断累计费用
    for row in sheet.iter_rows(min_row=2, values_only=True):
        diagnosis = str(row[4])
        consultation_fee = float(row[8])

        # 更新每次诊断的总费用
        if diagnosis in diagnosis_fees:
            diagnosis_fees[diagnosis] += consultation_fee
        else:
            diagnosis_fees[diagnosis] = consultation_fee

    # 创建一个新的摘要表
    summary_sheet = workbook.create_sheet('Summary')
    summary_sheet.append(['Diagnosis', 'Total Fee'])

    # 在摘要表中加上诊断和总费用
    for diagnosis, fee in diagnosis_fees.items():
        summary_sheet.append([diagnosis, fee])

此函数使用 OpenPyXL 的 BarChart 类和我们之前在函数中定义的摘要数据在 Excel 文件中创建一个条形图。

• 标签：取自 Summary 表的第一列（诊断名称）。
• 数据：取自第二栏（总诊费）。
• 图表使用这些标签和数据显示每个诊断的总费用。
• 它被添加到第一张工作表的M1位置。

def add_chart(workbook):
    summary_sheet = workbook['Summary']
    first_sheet = workbook[workbook.sheetnames[0]]

    # 创建条形图对象
    chart = openpyxl.chart.BarChart()
    chart.title = 'Diagnosis Fees'
    chart.x_axis.title = 'Diagnosis'
    chart.y_axis.title = 'Total Fee'

    # 为图表定义数据
    labels = openpyxl.chart.Reference(summary_sheet, min_col=1, min_row=2, max_row=summary_sheet.max_row)
    data = openpyxl.chart.Reference(summary_sheet, min_col=2, min_row=1, max_row=summary_sheet.max_row)

    # 向图表添加数据和标签
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(labels)

    # 将图表添加到第一张工作表
    first_sheet.add_chart(chart, 'M1')

将所有功能整合在一起

该函数将所有内容整合为一个精简流程，应用我们之前定义的所有函数。

• input_path：包含 Excel 文件的文件夹路径。
• output_path：保存已处理文件的路径。
• new_column_name：要添加的新列的名称。
• sheet_name（可选）：要处理的特定工作表名称；如果省略，默认为活动工作表。

def run_all(input_path, output_path, new_column_name, sheet_name=None):

    # 为各保险公司保存不同的工作簿
    save_workbook_per_insurance(input_path)

    for file in os.listdir(input_path):
        if file.endswith('.xlsx'):
            file_path = os.path.join(input_path, file)
            workbook = openpyxl.load_workbook(file_path)
            sheet = workbook[sheet_name] if sheet_name else workbook.active

            # 应用所有处理函数
            delete_missing_patient_id_rows(sheet)
            apply_currency_format(sheet)
            add_column(sheet, new_column_name)
            highlight_follow_ups(sheet)
            apply_tax(sheet)
            add_summary(workbook, sheet)
            add_chart(workbook)

            # 保存修改好了的workbook
            output_file_path = os.path.join(output_path, file)
            workbook.save(output_file_path)
            print(f"Processed and saved: {output_file_path}"