使用 Python 和 Excel 进行交互式数据分析

Question

简介

我已经写了 好几 次 关于 pandas 作为一个数据操纵/扯皮工具，以及它是如何能够有效地从 Excel 读取或写入数据，是多么的好用。但是，在有些你需要为数据分析提供一个交互式环境的情况下，试图在纯 Python 中，以一种用户友好的方式将它们拖到一起，将是困难的。这篇文章将会讨论如何使用 xlwings 来将 Excel, Python 和 pandas 绑定在一起，以构建一个数据分析工具，这个工具从一个外部的数据库中提取信息，操纵它，并且以一种熟悉的电子表格格式将其呈现给用户。

一个快速的 Excel 自动化简介

Excel 使用 VBA 支持一些自动化选项。用户定义函数(UDF，User Defined Functions) 是相当简单的，它们接收输入，然后返回一个简单的值。更强大的选择是一个宏（或过程），它们可以自动完成几乎任何 Excel 可以做到的事。

尽管 UDF 和宏是强大的，但是它们仍然是用 VBA 写的，而有时候，将 Python 的力量带给我们基于 Excel 的解决方法会非常有用。这就是 xlwings 的用武之地。最简单地说，xlwings 允许我们以两种主要方式粘合 python 和 Excel：

• 在 Python 中控制 Excel
• 在 Excel 中调用自定义的 Python 代码

本文将重点构建一个调用你自定义的 Python 代码的 Excel 工作表。

问题是……

在这个例子中，我们将开发一个简单的建模应用，它将允许某人输入一个账号以及日期范围，然后返回一些已经通过 pandas 转化的总结的销售信息。解决方法是简单的，但它显示了这种结合的威力，以及执行一些更复杂的数据分析有多么简单。

这里是一个我们正在试图做的事情的图表：

下面的例子可以很容易地扩展到查询多个数据库，或者与任何 Python 可以读取的文件(CSV, Excel, json 等等) 进行交互。

安装环境

为了达到这篇文章的目的，我将假设你在一个基于 Windows 的系统上运行该应用。我强烈建议你使用 anaconda (或者 miniconda ) 作为你选择的发行版本。

我们需要做的第一件事是安装 xlwings（假设 python+pandas 已经安装好了）：

conda install xlwings

版本警告
xlwings 在不断的更新中。这个代码时基于 0.7.1 版本的。

有一个漂亮的名为 quickstart 的 xlwings 帮助函数，它会为你创建一个 Excel 文件样例和 Python 文件桩。

c:\>xlwings quickstart pbp_proj

如果你看一看新创建的 pbp_proj 目录，那么你将看到两个文件：

pbp_proj.py
pbp_proj.xlsm

其中，Python 文件是空的，而 Excel 看上去也是空的，但是，幕后已经完成了一些工作，以使得 excel 到 Python 接口更加容易。

要看看 Excel 文件中有啥，请在 Excel 中打开你新创建的文件，然后选择 Developer -> Visual Basic，然后你应该可以看到一些像这样的东东：

你会发现有两个模块 - xlwings 和 Module1 。xlwings 模块包含所有的 VBA 代码，从而使得你自定义的代码可用。在大多数情况下，你应该不要管它。然而，如果你的配置有问题（例如无法找到 Python)，那么你可以在这里更新配置信息。

Module1 将有一些默认代码，它们看起来是这样的：

一会，我们将修改它来调用我们自定义的代码。首先，我想要创建 Excel 输入文件。

对于这个应用，我们将允许用户输入一个账号，开始日期和结束日期，然后将基于这些输入操纵销售日期。

下面是简单的电子表格：

我只是做了一些小的格式修改，这些格子中并没有公式。请务必将修改保存到 Excel 文件中。

下一步，我将创建一个简短的 Python 函数，它说明了如何从 Excel 中读取数据，然后将数据写回到 Excel 中。我将保存它到一个名为 pbp_proj.py 的空文件中。

import pandas as pd
from xlwings import Workbook, Range


def summarize_sales():
    """
    Retrieve the account number and date ranges from the Excel sheet
    """
    # Make a connection to the calling Excel file
    wb = Workbook.caller()

    # Retrieve the account number and dates
    account = Range('B2').value
    start_date = Range('D2').value
    end_date = Range('F2').value

    # Output the data just to make sure it all works
    Range('A5').value = account
    Range('A6').value = start_date
    Range('A7').value = end_date

该程序是简单的，但现在还没啥用。我想，为了确保所有的“管道”各就各位，开发一个框架程序更容易些。要记住的关键一点是，这个文件名为 pbp_proj.py ，而函数名为 summarize_sales 。

要将这一切写在一起，我们需要定义一个 Excel 过程来运行我们的代码：

代码时相当简洁的，它只是导入该模块，然后执行函数：

Sub RetrieveSales()
    RunPython ("import pbp_proj;pbp_proj.summarize_sales()")
End Sub

最后一块是添加一个按钮到我们的表单中，然后将其分配给程序/宏 RetrieveSales 。

一旦你有了这个，你应该可以按下按钮，然后看到像这样的东东：

基本过程已经有了，我们可以从 Excel 读取到 Python 程序中，然后使用读取的数据来输出数据到 Excel。现在，让我们使其更有用些。

从数据库中读取数据

在这个例子中，我将使用 sqlalchemy 来查询一个小的 sqlite db，然后直接将读取查询到一个 pandas dataframe 中。这种方法的好处是，如果你决定要查询另一个数据库，那么你可以只是修改 slqlalchemy 引擎，然后保持你的代码的剩余部分不 变。作为参考，xlwings 网站给出了另一个 例子 ，作为进一步的参考，这个例子应该很有帮助。

在处理代码之前，确保安装了 sqlalchemy：

conda install sqlalchemy

下面是如何通过使用到数据库的完整路径来连接到该 sqlite 引擎：

from sqlalchemy import create_engine

# Connect to sqlite db
db_file = os.path.join(os.path.dirname(wb.fullname), 'pbp_proj.db')
engine = create_engine(r"sqlite:///{}".format(db_file))

现在，我们有引擎了，我们可以构建并执行查询，然后将结果读取到一个 dataframe 中：

# Create SQL query
sql = 'SELECT * from sales WHERE account="{}" AND date BETWEEN "{}" AND "{}"'.format(account, start_date, end_date)

# Read query directly into a dataframe
sales_data = pd.read_sql(sql, engine)

一旦我们将数据保存在 sales_data dataframe 中，我们可以做任何我们想做的事情。为了简便起见，我会做一个简单的 groupby ，然后是总支出的 sum ：

# Analyze the data however we want
summary = sales_data.groupby(["sku"])["quantity", "ext-price"].sum()
total_sales = sales_data["ext-price"].sum()

幸运的是，xlwings“理解”pandas dataframe，因此将值返回到 Excel 工作表中很简单：

Range('A5').value = summary
Range('E5').value = "Total Sales"
Range('F5').value = total_sales

这样就完成数据 Excel -> Python -> Excel 的往返。

完整的程序

这里是包含在 pbp_proj.py 中的全功能代码：

import pandas as pd
from sqlalchemy import create_engine
from xlwings import Workbook, Range
import os


def summarize_sales():
    """
    Retrieve the account number and date ranges from the Excel sheet
    Read in the data from the sqlite database, then manipulate and return it to excel
    """
    # Make a connection to the calling Excel file
    wb = Workbook.caller()

    # Connect to sqlite db
    db_file = os.path.join(os.path.dirname(wb.fullname), 'pbp_proj.db')
    engine = create_engine(r"sqlite:///{}".format(db_file))

    # Retrieve the account number from the excel sheet as an int
    account = Range('B2').options(numbers=int).value

    # Get our dates - in real life would need to do some error checking to ensure
    # the correct format
    start_date = Range('D2').value
    end_date = Range('F2').value

    # Clear existing data
    Range('A5:F100').clear_contents()

    # Create SQL query
    sql = 'SELECT * from sales WHERE account="{}" AND date BETWEEN "{}" AND "{}"'.format(account, start_date, end_date)

    # Read query directly into a dataframe
    sales_data = pd.read_sql(sql, engine)

    # Analyze the data however we want
    summary = sales_data.groupby(["sku"])["quantity", "ext-price"].sum()

    total_sales = sales_data["ext-price"].sum()

    # Output the results
    if summary.empty:
        Range('A5').value = "No Data for account {}".format(account)
    else:
        Range('A5').options(index=True).value = summary
        Range('E5').value = "Total Sales"
        Range('F5').value = total_sales

这里是结果样例：

All of the data, including the sqlite db is in my github 所有的数据，包括 sqlite db，都在我的 github repo 中

总结

xlwings 提供了从 Python 与 Excel 无缝交互的一个有用的功能。通过使用此代码，你可以为你自己或那些从多个源获取数据，并且在非常熟 悉的 Excel 环境中分析数据的非技术用户，轻松构建交互式工具。一旦建立起了结构，那么将所有复杂的逻辑和数据分析放到 Python 文件中，并利用所有 在 Python 生态系统中提供的工具，这将是非常有用的。我希望，一旦你开始玩这个，那么你会发现有很多机会来使用这个方法将 Python 解决方法带给你 的一些陷入使用 Excel 作为他们唯一的数据分析工具的非技术用户。

原文： Interactive Data Analysis with Python and Excel