Python与Excel协同应用初学者指南

excelperfect

标签：Python与Excel协同

本文将探讨学习如何在Python中读取和导入Excel文件，将数据写入这些电子表格，并找到最好的软件包来做这些事。

为数据科学使用Python和Excel

Excel是Microsoft在1987年开发的电子表格应用程序，它得到了几乎所有操作系统（如Windows、Macintosh、Android等）的正式支持。它预装在Windows操作系统中，可以轻松地与其他操作系统平台集成。在处理结构化数据时，Microsoft Excel是最好且最易访问的工具。

它以表格的方式组织、分析和存储数据，可以执行计算，创建数据透视表、图表，等等。自发布以来，该软件广受欢迎，并广泛使用于世界各地的许多不同应用领域和各种场合。

自互联网诞生之日起，它就以指数级增长，数据量也以指数级增长。数据的增长促使人们了解如何分析数据。企业和政府正在收集大数据。因此，数据科学一词应运而生。

在处理数据时，需要在某个时候处理电子表格；然而，直接处理电子表格有时会让人恼火，尤其当你是一名开发人员的时候。为了解决这个问题，Python开发人员想出了读取、写入、分析各种文件格式的方法，包括电子表格。

这里将主要介绍如何使用Python编程语言并在不直接使用Microsoft Excel应用程序的情况下处理Excel。它将提供使用包的亲身体验，可以使用这些包在Python的帮助下加载、读取、写入和分析这些电子表格。你将处理pandas、openpyxl、xlrd、xlutils和pyexcel等软件包。

数据就是石油

当启动任何直接或间接处理数据的项目时，首先要做的就是搜索数据集。现在可以通过各种方式收集数据，可以使用网络抓取、客户端的私有数据集，也可以使用从GitHub、universities、kaggle、quandl等来源下载公共数据集。

数据可能位于Excel文件中，也可能使用.csv、.txt、.JSON等文件扩展名来保存。数据可以是定性的，也可以是定量的。根据计划解决的问题类型，数据类型可能会有所不同。因此，作为第一步，应该弄清楚使用的是定性数据还是定量数据。

数据可以是：

• 连续的
• 离散的
• 分类-二进制、无序、有序
• 图像的像素，等。

 

电子表格数据的最佳实践

在开始用Python加载、读取和分析Excel数据之前，最好查看示例数据，并了解以下几点是否与计划使用的文件一致：

• 电子表格的第一行通常是为标题保留的，标题描述了每列数据所代表的内容，除非电子表格中的数据是图像的像素。
• 避免在名称或值字段标题中使用空格或由多个单词组成的名称之间有间隙或空格。考虑使用Python的标准PET-8格式，例如：下划线、破折号、驼峰式大小写，文本每一部分的第一个字母大写，或者偏向使用短名字而不是长名字或句子。
• 尽量避免使用包含特殊字符的名称，例如？、$、%、^，等等，因为特殊字符不会告诉任何有关数据的信息。
• 数据在某些列中可能缺少值。确保使用NA或完整列的平均值或中位数来填充它们。

 

在使用Microsoft Excel时，会发现大量保存文件的选项。除了默认的扩展名.xls或.xlsx，可以转到“文件”选项卡，单击“另存为”，然后选择“保存类型”文件扩展名选项中列出的扩展名之一。为数据科学保存数据集最常用的扩展名是.csv和.txt（作为制表符分隔的文本文件），甚至是.xml。根据选择的保存选项，数据集的字段由制表符或逗号分隔，这将构成数据集的“字段分隔符”。

 

了解文件的扩展名很重要，因为加载Excel中存储的数据时，Python库需要明确知道它是逗号分隔的文件还是制表符分隔的文件。

 

以下是MS excel支持的所有文件扩展名：

图1

 

准备工作区（可选）

准备工作区是其中很好的一步，但这不是一个强制性步骤，可以跳过。然而，把这作为第一步，会让事情变得更简单，并确保有一个良好的开端。

 

验证代码库目录是否与Python的工作目录相同。

 

在终端中工作时，可以首先导航到文件所在的目录，然后启动Python。这也意味着必须确保文件位于想要工作的目录中。

 

但是有些人是初学者，已经开始了Python会话，而对正在使用的目录一无所知，可以考虑执行以下命令：

图2

 

另一种方法是跟踪数据集文件的存放位置。还可以在代码中给出该文件夹的绝对路径，而不是更改计划编写Python代码的目录。绝对路径将确保无论在哪里编写Python代码，它都能够获取数据。

 

你将看到，这些命令非常重要，不仅用于加载数据，还用于进一步分析。现在，已经完成了所有检查，保存了数据，并准备好了工作区。

 

在最终开始用Python读取数据之前，还有一件事要做：安装读取和写入Excel文件所需的软件包。

 

安装读取和写入Excel文件的软件包

确保系统上安装了pip和setuptools。不要使用Python2，因为它已经停止使用，确保已经安装了Python3.4以上版本，不过也不需要担心，因为这些通常已经准备好了。如果已经有了Python3，只需确保已经升级到了最新版本。

 

检查pip或pip3命令是否以符号方式链接到Python3，使用计划在本文中使用的当前版本的Python（>=3.4）。此外，通过在终端中键入Python来检查它显示的版本是>=2.7还是>=3.4，如果是2.7，则通过键入Python3来检查，如果这有效，则意味着系统上安装了两个不同的Python版本。

 

要执行此操作，在终端中运行以下命令：

对于Linux/OS X:

pip install –Upip setuptools or pip3 install –U pip3 setuptools

 

对于Windows：

python –m pipinstall –U pip setuptools or python3 –m pip install –U pip setuptools

 

如果尚未安装pip，运行python get-pip.py。如果需要更多帮助以使一切正常运行，也可以按照页面上的安装说明进行操作。

 

安装Anaconda

Anaconda Python发行版可能是你应该寻找的，因为它与开始数据科学之旅所需的几乎所有东西捆绑在一起。从Python、Pip、Pandas、Numpy、Matplotlib等开始，所有东西都将安装在它里面。这将为你提供一种简单快捷的方法来开始进行数据科学，因为不需要担心单独安装数据科学所需的软件包。然而，仍然有很多包可能不在Anaconda的涵盖范围内，可以通过Pip手动安装这些包，或者从源代码构建这些包。

 

Anaconda不仅对初学者有用，而且对经验丰富的开发人员也很有用。这是一种快速测试概念验证的方法，无需单独安装每个软件包，从而节省大量时间。

 

Anaconda包括100个最流行的Python、R和Scala数据科学软件包，以及几个开源开发环境，如JupyterLab/Notebook和Spyder IDE。

 

要了解如何安装Anaconda，可以查看官方文档。按照说明进行安装，就可以开始了。

 

恭喜你，你的环境已经设置好了！准备好开始加载文件并分析它们了。

 

将Excel文件作为Pandas数据框架加载

Pandas包是导入数据集并以表格行-列格式呈现数据集的最佳方法之一。Pandas库建立在数字Python（通常称为NumPy）之上，为Python编程语言提供易于使用的数据结构和数据分析工具。Pandas有内置的函数，可以用来分析和绘制数据，并使它的展现其意义。

 

由于该库提供的强大功能和灵活性，它已成为每一位数据科学家的首选。当然，这个库也有一些缺点，尤其是在处理大型数据集时，它在加载、读取和分析具有数百万条记录的大型数据集时可能会变慢。

 

如果已经通过Anaconda获得了Pandas，那么可以使用pd.Excelfile（）函数将Excel文件加载到数据框架（DataFrames）中，如下图所示。

 

只需创建一个虚拟example.xlsx文件，并在行和列中填写一些任意值，然后将其以.xlsx格式保存。

图3

 

如果没有安装Anaconda，可能会出现nomodule错误。只需在终端执行pip install pandas或者在jupyter notebook单元格中执行！pip install pandas在你的环境中安装Pandas软件包，然后执行上面代码块中包含的命令。

 

很简单，对吧？

 

要读取.csv文件，有一个类似的函数来在数据框架中装载数据：read_csv()。下面是一个如何使用此函数的示例：

图4

 

pd.read_csv()函数有一个sep参数，充当此函数将考虑的分隔符逗号或制表符，默认情况下设置为逗号，但如果需要，可以指定另一个分隔符。

 

如何将数据框架写入Excel文件

由于使用.csv或.xlsx文件格式在Pandas中装载和读取文件，类似地，可以将Pandas数据框架保存为使用.xlsx的Excel文件，或保存为.csv文件。假设在数据分析和机器学习预测之后，希望将更新的数据或结果写回到一个新文件，可以使用pandas的to_excel()函数实现。

 

但是，在使用此函数之前，如果要将数据写入.xlsx文件中的多个工作表，确保已安装XlsxWriter，如下所示：

图5

 

让我们分解上面的代码块，一步一步地理解它：

• 首先，使用ExcelWriter对象来输出数据框架，定义将在其中保存数据框架输出的writer。
  
  
• pd.ExcelWriter函数接受两个参数，文件名和是xlsxwriter的引擎。
   
• 接下来，将writer变量传递给to_excel()函数，并指定工作表名称。通过这种方式，可以将包含数据的工作表添加到现有工作簿中，该工作簿中可能有许多工作表：可以使用ExcelWriter将多个不同的数据框架保存到一个包含多个工作表的工作簿中。

 

一个更好、更简单的选项是将数据写入.csv扩展。正如在上面所看到的，可以使用read_csv读取.csv文件，还可以使用pandas的to_csv()方法将数据框架结果写回到逗号分隔的文件，如下所示：

图6

 

如果要以制表符分隔的方式保存输出，只需将\t传递给参数sep。注意，还可以使用其他各种函数和方法来写入文件，甚至可以将header和index参数传递给to_csv函数。

 

使用Conda环境

安装这些软件包的一般建议是在Python或Anaconda virtualenv中安装，而不使用系统软件包。在虚拟环境中安装软件包的好处是，它不会升级或降级基本系统软件包，并且可以为不同的项目使用不同的conda环境。

 

要开始使用virtualenv，首先需要安装它。安装虚拟环境非常简单，尤其是使用Anaconda。在你的基础上，anaconda只需使用一个名称和希望它使用的python版本创建虚拟环境。只要激活它，安装需要的任何软件包，然后转到你的项目文件夹。

 

提示：完成后别忘了关闭该环境。

图7

 

拥有虚拟环境使事情变得非常简单。想象一下，作为一名开发人员，将在多个不同的项目上工作，每个项目可能需要具有不同版本的不同软件包。当你的项目有冲突的需求时，虚拟环境就会派上用场。

 

否则，你会一直在安装一个软件包，然后为一个项目升级，为另一个项目降级。更好的办法是为每个项目提供不同的环境。

 

现在，终于可以开始安装和导入读取要加载到电子表格数据中的包了。

 

如何使用Openpyxl读取和写入Excel文件

如果想读写.xlsx、.xlsm、.xltx和xltm文件格式，建议使用Openpyxl软件包。

 

可以使用pip安装openpyxl，但要安装在excel conda环境中，如下面的代码单元所示。

图8

 

现在已经安装了openpyxl，可以开始加载数据了。

 

但在加载数据之前，需要创建它。

 

要创建数据，可以按照下面的工作簿进行操作，其中有三张工作表将加载到Python中：

图9

 

load_workbook()函数接受文件名作为参数，并返回一个workbook对象wb，它代表文件。可以通过运行type(wb)检查wb的类型。

图10

 

上面的代码块返回在Python中加载的工作簿的工作表名称。接下来，还可以使用此信息检索工作簿的单个工作表。

 

还可以使用wb.active检查当前处于活动状态的工作表。从下面的代码中可以看到，还可以从工作簿中加载另一张工作表：

图11

 

虽然一开始会认为这些Worksheet对象没有用处，但你可以用它们做很多事情。就像可以使用方括号[]从工作簿工作表中的特定单元格中检索值一样，在这些方括号中，可以传递想要从中检索值的确切单元格。

 

这种从单元格中提取值的方法在本质上与通过索引位置从NumPy数组和Pandas数据框架中选择和提取值非常相似。但是使用Openpyxl时，除了指定要从中提取值的索引外,还需要指定属性.value，如下所示：

图12

 

如你所见，除了value属性外，还有其他属性可用于检查单元格，如row、column和coordinate。

 

从sheet1中选择B3元素时，从上面的代码单元输出：

• row属性为3
• column属性为2
• 单元格的坐标为B3

 

这是关于单元格的信息，如果要检索单元格值呢？

 

可以使用sheet.cell()函数检索单元格值，只需传递row和column参数并添加属性.value，如下所示：

图13

 

要连续提取值，而不是手动选择行和列索引，可以在range()函数的帮助下使用for循环。

 

这将在提取单元格值方面提供很大的灵活性，而无需太多硬编码。让我们打印出第2列中包含值的行的值。如果那些特定的单元格是空的，那么只是获取None。

图14

 

openpyxl有一个utility类，它有两个方法get_column_letter和column_index_from_string。顾名思义，前者返回给定数字/整数的字母，后者返回字母作为字符串提供的数字。

 

可以在下面看到它的工作原理：

图15

 

已经为在特定列中具有值的行检索了值，但是如果要打印文件的行而不只是关注一列，需要做什么？

 

当然，可以使用另一个for循环。

 

例如，只关心在A1和C3之间的区域，其中第一个指定想关心的区域的左上角，第二个指定想关注的区域的右下角。

 

这个区域就是在下面第一行代码中看到的所谓的cellObj。然后，对于位于该区域的每个单元格，打印该单元格中包含的坐标和值。每行结束后，将打印一条消息，表明cellObj区域的行已打印。

 

注意，区域的选择与选择、获取和索引列表以及NumPy数组元素非常相似，其中还使用方括号和冒号：来指示要获取值的区域。此外，上面的循环还很好地使用了单元格属性。

 

要使上述解释和代码可视化，可能需要查看循环完成后返回的结果：

图16

 

最后，有一些属性可以用来检查导入的结果，即max_row和max_column。当然，这些属性是确保正确加载数据的一般方法，但尽管如此，它们可以而且将非常有用。

图17

 

至此，还看到了如何在Python中使用openpyxl读取数据并检索数据。很多人可能会觉得这是一种非常困难的处理这些文件的方法，当还没有研究如何操作数据时，这肯定会更加复杂。

 

但别担心，有一个更简单的方法。

 

数据框架来了。

 

可以使用Pandas包中的DataFrame()函数将工作表的值放入数据框架（DataFrame），然后使用所有数据框架函数分析和处理数据：

图18

 

如果要指定标题和索引，可以传递带有标题和索引列表为True的标题参数，然而，由于已转换为数据框架的工作表已经具有标题，因此不需要添加标题：

图19

 

甚至可以在dataframe_to_rows方法的帮助下，将值追加或写入Excel文件，如下图所示。可以将上面创建的数据框df连同索引和标题一起传递给Excel：

图20

 

openpyxl软件包提供了将数据写回Excel文件的高度灵活性，允许改变单元格样式等等，这使它成为在使用电子表格时需要知道的软件包之一。

 

注意：要了解更多关于openpyxl的信息，比如如何更改单元格样式，或者该软件包如何与NumPy和Pandas配合使用，查看以下内容。

Python学习笔记合集~2019.8>>>

 

另一个在处理电子表格时很有用的软件包是xlrd。

 

读取和格式化Excel文件：xlrd

如果想从具有.xls或.xlsx扩展名的文件中读取和操作数据，该软件包非常理想。

图21

 

xlrd提供了一些函数，可以使用这些函数仅检索或筛选特定的工作表，而不是整个工作簿。它提供了sheet_by_name()或sheet_by_index()等函数，用于检索要在分析中使用的工作表，并筛选其余的工作表。

图22

 

使用xlwt将数据写入Excel文件

与其他Excel Python软件包一样，可以使用xlwt创建包含数据的电子表格，甚至可以手动创建。除了XlsxWriter软件包之外，还可以使用xlwt软件包。xlwt非常适合将数据和格式信息写入具有旧扩展名的文件，如.xls。

 

乍一看，很难发现它比你之前学习的Excel软件包有多好，但更多的是因为与其他软件包相比，在使用这个软件包时感觉有多舒服。

 

通过一个示例来理解它，在这个示例中，将使用Python代码手动创建工作簿并向其写入数据：

图23

 

自动化数据写入过程

自动化Excel文件中的数据写入过程至关重要，尤其是当想将数据写入文件，但又不想花时间手动将数据输入文件时。在这种情况下，可以使用非常简单的技术（如for循环）自动化。

 

让我们了解如何实现这一目标：

图24

 

让我们一步一步地理解上面的代码：

1.首先使用xlwt.workbook()初始化工作簿；

2.然后向工作簿中添加一个名为Sheet1的工作表；

3.接着定义数据，即标题（cols）和行（txt）；

4.接下来，有一个for循环，它将迭代数据并将所有值填充到文件中：对于从0到4的每个元素，都要逐行填充值；指定一个row元素，该元素在每次循环增量时都会转到下一行; 另一个for循环，每行遍历工作表中的所有列；为该行中的每一列填写一个值。

5.用值填充每行的所有列后，将转到下一行，直到剩下零行。

 

使用pyexcel读取.xls或.xlsx文件

pyexcel是一个Python包装器，它提供了一个用于在.csv、.ods、.xls、.xlsx和.xlsm文件中读取、操作和写入数据的API接口。使用pyexcel，Excel文件中的数据可以用最少的代码转换为数组或字典格式。

 

下面是一个示例，说明如何使用pyexcel包中的函数get_array()将Excel数据转换为数组格式：

图25

 

让我们了解一下如何将Excel数据转换为有序的列表字典。要实现这一点，可以使用get_dict()函数，它也包含在pyexcel包中：

图26

 

也可以得到二维数组的字典。简单地说，可以在get_book_dict()函数的帮助下提取单个字典中的所有工作簿。

图27

 

记住，上面的两个输出my_dict和book_dict可以使用pd.DataFrame()转换为数据框架，这将更容易处理数据。

 

用pyexcel写入文件

就像使用这个软件包可以轻松地将数据加载到数组中一样，也可以轻松地将数组导出回电子表格。可以使用save_as()函数来获得这个值，并将数组和目标文件名传递给dest_file_name参数，如下所示：

图28

 

注意，如果要指定分隔符，可以添加dest_delimiter参数，并在两者之间传递要用作分隔符的符号，如\t、，、””。

 

然而，如果有字典，则需要使用save_book_as()函数，将二维字典传递给bookdict，并指定文件名：

图29

 

注意，上述代码中不会保留字典中数据的顺序。

 

读取和写入.csv文件

Python有大量的包，可以用一组不同的库实现类似的任务。因此，如果仍在寻找允许加载、读取和写入数据的包。除了Excel包和Pandas，读取和写入.csv文件可以考虑使用CSV包，如下代码所示：

图30

 

数据的最终检查

当数据可用时，通常建议检查数据是否已正确加载。如果已将数据放入数据框架中，则可以通过运行head()和tail()函数轻松快速地检查数据是否已按预期加载。head()将输出数据框架的前几行，tail()将输出数据框架的最后几行。

图31

 

还可以检查数据框架data的形状、尺寸和数据类型：

图32

 

结论

本文教你如何用Python读取Excel文件。

 

但导入数据只是数据科学工作流程的开始。一旦你的环境中有了电子表格中的数据，就可以专注于重要的事情：分析数据。

 

然而，如果想继续研究这个主题，考虑PyXll，它允许在Python中编写函数并在Excel中调用它们。

 

注：本文整理自datacamp.com，供有兴趣的朋友参考。

有兴趣的朋友可以到知识星球完美Excel社群下载《Python与Excel协同应用初学者指南》中文电子版。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。

每日精进：2022.2.21 21:30-22:10 研学《普林斯顿微积分读本（修订版）》9.5 取对数求导法；9.6 指数增长和指数衰变；9.7 双曲函数