大数据分析Python库xlwings提升Excel工作效率教程

　　Excel在当今的企业中非常非常普遍。在AAA教育，我们通常建议出于很多原因使用代码，并且我们的许多数据科学课程旨在教授数据分析和数据科学的有效编码。但是，无论您偏爱使用大数据分析Python的程度如何，最终，有时都需要使用Excel来展示您的发现或共享数据。
 

　　但这并不意味着仍然无法享受大数据分析Python的某些效率!实际上，使用名为的库xlwings，您可以使用大数据分析Python加快在Excel中的工作。
 

　　在本xlwings教程中，我们将逐步介绍如何在Excel中使用大数据分析Python来执行和使用一些常见操作，例如根据特定条件删除行，使用Excel函数和公式，自动填充，创建工作表，图表等。在这篇文章中，您应该熟悉大数据分析Python的基本概念(对象，方法，属性，函数)和大数据分析Python的语法，并且具有Excel和VBA的中级知识。
 

　　我们将使用一个数据集，其中包含有关称为EuroMillions的欧洲彩票开奖的信息。这组数据是从下载该链接，它包含了所有的欧洲百万彩票绘制一张，并包括，9月20日期。该链接上的可用数据应使用最新信息进行更新，直到您阅读此帖子时为止，但是如果不可用，请使用CSV文件，其中包含截至9月20日该链接的数据。
 

　　在撰写本文时，抽奖包括来自50个号码池(编号1到50)中的五个号码和lucky stars来自12个号码池的两个号码。为了赢得大奖，参与者必须正确选择所有抽奖号码和幸运星。有史以来最大的大奖是1.9亿欧元。(不过请注意，我们的数据集表示的是英镑而不是欧元的赢利)。
 

　　在本教程中，我们将使用大数据分析Python和xlwings与Excel清理数据集，然后生成一些图表以可视化哪些数字最常赢得欧洲百万奖金。

　　第一列是开奖号码，各列N1-L2是开奖号码和幸运星(按绘制顺序)，该Jackpot列是欧元的累积奖金，该Wins列告诉我们有多少投注下了大奖。
 

　　遇见 xlwings
 

　　xlwings是一个大数据分析Python库，可在Excel实例中使用大数据分析Python的某些数据分析功能，包括对numpy数组以及pandasSeries和DataFrames的支持。与其他任何大数据分析Python库一样，它可以使用pip或通用方法安装conda，但是如果需要其他详细信息，可以在xlwings此处访问文档。
 

　　请注意，您需要在用于执行本xlwings教程的计算机上安装Microsoft Excel版本。
 

　　xlwings 对象
 

　　在xlwings有四个主要对象类型其是，在降低分层顺序：App(代表一个Excel实例)， Book，Sheet和Range。除了这些，我们还将处理Chart和Shape对象。您可以在官方文档中找到有关这些对象和其他对象的有用信息，但是我们将一次查看每个对象。
 

　　让我们开始创建一个Book实例并命名它wb(工作簿)。

　　当您运行该代码时，它应该看起来像这样。

　　请注意，当代码单元在Jupyter Notebook中运行时，Excel将自动启动。
 

　　通过实例化一个Book对象，将App自动创建属于我们的书本对象的对象。这是我们可以检查所有打开的Excel实例的方法。
 

　　注意：我们不会在本教程的每个步骤中都包含gif图像，因为我们不希望该页面为互联网连接速度慢或连接受限的人带来麻烦。但是，随后的代码运行步骤应类似于上面的代码：在Juypter中运行单元格时，Excel电子表格会根据我们运行的任何代码进行更新。

　　该对象xw.apps是可迭代的。要检查此迭代器中哪些工作簿属于唯一实例，我们可以books像这样调用其上的方法。

　　不出所料，唯一的实例是工作簿wb。我们在下面检查这个事实。

　　同样，我们可以检查哪些表属于该工作簿：

　　我们还可以通过工作表名称来引用工作表：

　　我们可以将数据从某些大数据分析Python对象(例如列表和元组)移到Excel中。让我们将数据框中的数据移动到表EuroMillions中。为此，我们将利用range创建一个范围对象，该对象将来自DataFrame的数据存储在Excel中的一系列单元格中，在这种情况下，从单元格A1开始：

　　外观如下：

　　如我们所见，的索引列df也已移至Excel。让我们清除此工作表的内容，然后复制不带索引的数据。

　　能够告诉我们表格的结束位置将很有用。更具体地说，我们需要最后一行包含数据的行。为此，我们可以使用对象的end方法和row属性Range。
 

　　的row方法，这并不奇怪，返回row所述的Range对象。
 

　　该方法end将方向("up"(或1)，"right"(或2)，"left"(或(或))作为参数3，并返回另一个范围对象，它模仿Excel中非常常见的动作。"down"4CTRL+Shift+Arrow

　　它签出!
 

　　API属性
 

　　并非所有Excel功能都可以作为本机xlwings功能使用。有时，我们必须找到解决方法来完成我们想要的事情。幸运的是xlwings，这对我们来说非常容易。在官方文档的“ 缺少功能”部分中：
 

　　解决方法：本质上，xlwings只是Windows 上pywin32和Mac 上appscript的智能包装。您可以通过调用api属性来访问基础对象。底层对象将使用pywin32(非常像VBA)和appscript(不像VBA)的语法为您提供几乎可以使用VBA进行的所有操作。但是除了难看之外，请记住，它使您的代码平台特定于(!)。Excel Visual Basic for Applications是各种现有Excel对象的丰富解释源。
 

　　排序是缺少的一种功能xlwings。您可能已经注意到，记录是从最新到最旧的抽签排列的。在接下来的几个步骤中，我们将颠倒顺序。
 

　　对象ws.range("A2:N{row}".format(row=last_row))是Range对象。将api属性附加到其上将产生一个VBA Range对象，该对象又可以访问其VBA功能。
 

　　我们将使用此VBA对象的Sort属性。在其最简单的应用程序中，Sort采用两个参数：要对表进行排序的列(作为VBA Range对象)和排序类型(无论是按升序还是降序排序)。第二个参数的参数文档可在此处查看。我们将按升序排序。
 

　　将所有这些放在一起看起来像这样：

　　运行后，它在屏幕上的显示方式如下(请注意，第一列已更改，现在它以升序而不是降序排序。

　　分析我们的数据
 

　　我们在尝试分析此数据集时会遇到的一个问题是，日期分散在三个不同的列中。我们需要将其压缩到单个列中。为此，我们将使用大数据分析Python在Excel中适当地连接列。我们首先在相邻的空白列中插入标题。

　　接下来，我们可以插入要用作字符串的Excel公式。注意：应使用哪种参数分隔符的具体信息取决于计算机的本地区域设置。在我看来，参数分隔符是一个逗号，这是我在本教程中使用的，但是在您看来，它可能是一个分号。

　　在第一个单元格中插入了公式之后，常规Excel工作流中的第二个特性是自动填充表格末尾的其余单元格。自动填充是VBA Range对象的一种方法。它以目标单元格为VBA Range对象和填充类型为参数。我们对枚举为的默认值感兴趣0。

　　这大致是此步骤后屏幕的外观。请注意最右边的新“日期”列。

　　我们还可以使用想要的填充类型的命名形式。为此，我们需要从模块中检索它，该模块xlwings.constants包含大多数VBA属性的枚举参数的命名版本。回想一下，您始终可以通过打印来检查可用属性dir(xlwings.constants)。
 

　　(如果您不熟悉它，它dir是大数据分析Python的本机功能，它可以接受多种参数(模块，类和常规对象(如列表和字符串)。例如，如果打印dir(some_list)出来的话)将为您提供所有方法和可以与列表一起使用的属性。)
 

　　我们上面的操作也可以通过下面的代码片段实现。

　　由于我们将经常使用它，因此我们将创建一个应用默认填充的函数，如下所示：
 

　　工作表
 

　　代表工作表中单元格的字符串
 

　　最后一行要填充。
 

　　为此，我们将介绍一种Range称为的新方法get_address。它接受四个布尔参数，并返回一个字符串，该字符串标识具有不同详细程度的范围。这是该方法的一个令人鼓舞的示例。

　　现在我们定义我们的功能。

　　为了避免Excel进行不必要的计算，我们将刚插入列中的公式替换为O硬编码值。我们这样做在此之前，让我们花点时间去思考什么样的大数据分析Python对象的是Range.value，当Range是一个数组。

　　清单!让我们看看它的前十个元素。

　　如果我们将此列表插入任意范围，它将水平放置这些值，这不是我们想要的。为了垂直放置它们，我们需要使用带有选项作为参数options的Range对象方法transpose=True，如下所示：

　　多年来，EuroMillions格式经历了一些轻微的修改，最后一次更改发生在2016年9月24日。
 

　　从2016年9月24日起，幸运星的数量从11个池更改为12个池。为了进行有意义的分析，我们将仅考虑上次修改后发生的绘制。下一个代码段查找修改之前发生的最后一个游戏并将其命名to_delete。
 

　　这是我们现在的位置：

　　准备好数据之后，我们现在将格式化该表。首先，将第一行的字体设置为bold。

　　我们可以通过Jackpot以百万列格式设置列的大小。请注意，以下字符串格式取决于您计算机的本地区域设置。如果格式看起来很奇怪，请尝试将逗号替换为点。有关Excel自定义格式的更多信息。

　　作为后续步骤的辅助步骤，我们将找到与具有数据的最后一列相对应的字母。

　　现在让我们在标题单元格的底部添加边框。与我们一直在做的类似，我们将使用该api属性。另外，我们将需要对象的Border属性Range，边界方向枚举和border的样式。我们将-4119仅在标题单元格的底部(方向9)设置双边框(线条样式)。

　　现在让我们通过行和列自动拟合。

　　糟糕!这看起来有些压缩，让我们将所有列的宽度设置为J似乎最大的列的宽度。这是ColumnWidth我们在下面使用的文档。

　　那看起来应该更好。我们已经完成了这张工作表!
 

　　让我们创建add一个新的空白表，Frequencies并将其分配给大数据分析Python名称frequencies。

　　我们将用表中刚刚组织的数据集中每个数字和每个幸运星的绝对频率填充此表EuroMillions。

　　在下面，我们将为单元格中的频率插入一个标题，B1并在单元格中B2输入一个公式，该公式将计算in值A2在该范围内出现的次数C2:G201。换句话说，我们将计算1列中发生了多少次N1-N5。之后，我们将自动填充列中的其余单元格，B以对其各自的行执行相同的操作。
 

　　我们对幸运星也这样做：

　　这是我们的新工作表应如何处理的：

　　我们正在接近我们的目标。让我们创建一个名为的工作表Graphs。

　　现在，我们将创建一个Chart对象。这只会产生一个空白的白框，但是请放心!我们将立即使用该框来绘制数据图表。
 

　　我们可以name将图表制作成与工作表类似的图表。该方法set_source_data允许我们通过传入范围对象来定义图表的数据源。

　　Excel将尝试猜测x轴应该是什么，但是我们可以Frequencies使用VBA Chart方法强制将其作为在其上创建的数字FullSeriesCollection。我们可以使用索引1的对象来编辑图表nr_freq.api：

　　Excel非常擅长猜测用户想要哪种图表，但是如果万一它猜测不正确，我们将强制它成为柱形图。此处列出了各种类型的图表。las，唯一将这些文件与chart_type属性的可能值联系起来的文档是源代码本身。

　　现在，我们将定义图表的高度和宽度。度量单位将是点。

　　这是我们现在应该看到的：

　　该SetElement方法与参数一起2在图表上方设置标题。在这里查看其他参数。

　　我们添加了最后的修饰。我们使用HasLegend属性删除图例。

　　xlCategory作为参数1传入Axes方法的类别，以及将TickLabelSpacing属性设置为1，确保可以显示轴的每个元素。

　　要完成这一格式图，我们通过设置属性删除大纲Visible中的Line对象0。

　　这是我们将看到的：

　　下面我们对幸运星做了几乎相同的事情。

　　最后，我们创建一个显示累积奖金演变的时间序列图。

　　然后，我们通过将TickLabels属性NumberFormat设置为所需的外观来固定垂直轴的“标签”格式。

　　我们完成了!现在，我们保存文件并退出我们的Excel实例。