Python商务办公——python＋pandas高效实现Excel文件合并与分析

写在前面

新的一周又开始了，各位准备好迎接接下来的机械化流程了吗？上期文章中，小编和大家讲述了怎么使用R语言去绘制热地图，其中重点使用了maptools包和ggplot2包，大致了解了使用R语言绘制中国人口分布热地图的大致流程。当然那篇文章只是对于ggplot2包的初次展示，后续的文章中，小编会进一步讲解这一笔者至今所接触到的最神级的绘图包。本期文章中，小编自然要转到Python方面的主题，本期将主要讲解怎么使用Python和pandas库去实现多个Excel表格文件的合并与添加，以及怎么使用Python去实现Excel表格文件的有效选取。这篇文章将是笔者先前文章的继续，当然后续笔者会继续推出此类文章。现在就正式进入正题！

第一讲：Excel文件的合并

相信常常使用Excel文件的朋友都有过这样的经历，同类型的多份Excel工作簿或者多个Excel工作表需要合并在一起，遇到这种问题，我们一般都会习惯采用三步：其一，新建一个工作表，然后不断地复制目标工作表中的各种数据，然后粘贴到工作表中；其二，使用vlookup函数将目标数据表中的列合并到新的工作表中；其三，对新建成的数据表进行数据清洗与清洁（说到底就是将调整列的数据格式、调整数据表中的NAN值）。是不是熟悉的味道呀，如果你经常遇到这种问题，然后你每次都要重复上面的操作步骤，那我想你最终会衍生出两种心态：第一，你完全习惯了这种操作方式，并且已经不愿意去改变了；第二，你想采用更加高效的方式，去改变这种机械化的操作了。那么，小编在这里就要告诉你，本文正是要解决你这一困惑的。

当然这种高效的方式不止一种，如果你对Excel中VBA比较熟悉的话，那么你完全可以使用VBA函数实现这一过程，不过我再想，即使你会VBA，估计你也不愿意使用这种方法去实现多个Excel文件的合并，因为使用这种VBA函数的话，真的很痛苦，小编正是被这种VBA方法折磨的一群人。所以这里小编要向各位朋友介绍的是如何使用Python以及pandas库去实现这种Excel文件的合并、清洗与分析。在进行这些之前，请确保你的pandas库的版本要高于0.15，可以使用以下命令进行检查。

如果你的版本没有达到要求，你只需要重新安装一下pandas库或者升级一下pandas库即可，笔者这里告诉你如何去进行python中库的升级。

你在后面的操作中，所需要的就仅仅是一个Python环境外加一个达到版本要求（一般来说，如果你是最近才进行anaconda的话，你的版本绝对是达到要求的）的pandas库。为了让大家更加熟悉这种操作流程，笔者这里提供一些Excel表格供大家实践之用，大家请自行下载：

一月销售数据

二月销售数据

三月销售数据

客户等级数据

下载好了之后请将其存放在同一个文件夹中。这里声明一下，这些数据都是一些过期数据，也是小编以前用来实验的一些数据，这些数据纯属实践学习之中，任何基于此种数据使用不当造成的损失可不要找小编呀。下面开始进行实践！

这些命令都是前期准备工作（这里需要说明的一点这些操作都是在juyter notebook中进行的，所以请确保你是在在其中实践），然后在命令红中输入一下命令，检查文件夹中的文件。

因为你已经定位到了你下载文件的文件夹，所以里面只有四个Excel文件，正如上面所显示的那样。然而很多时候你的文件夹中可能存在多份文件，你只想找出文件名开头是以sales字符串开始的excel文件，那么此时你就需要采用以下命令：

虽然使用命令可以查看文件夹中文件，但是这个命令存在一个劣势，即它只是显示了文件，而没有把文件合理地组织在一起，从而使得对于文件的循环操作存在一定的困难，正是因为这个缺陷，所以python中存在一个更加高效的文件操作库，这个歌库是python自带的，所以不需要额外安装，下面尝试一下这个库。

现在我相信你应该明白了这个库的优势了吧，它的返回结果是一个列表，这对于循环操作文件提供了极大地便利。既然我们已经准备好了文件以及相关的操作库，那么下面就开始合并文件吧。不过在这之前，我们先简单地了解一些三个sales文件（这里声明一下，这并不是必须的，笔者只是想通过这样做让你们确认最后合并的成功），你完全可以直接打开Excel文件进行查看，当然笔者这里采用pandas进行。

前三行代码读入了三个月的销售数据，最后一行代码统计出这三份Excel表格共有384行记录，请记住这个数字，在等下我们合并文件之后用来确定是否进行了正确合并。下面我们正式开始合并三份销售数据表。

代码中的第一行，小编先建立了一个空的数据框sales_all以便存放三份文件中的数据（我希望各位也能学会这种习惯，最好不要直接修改原文件）；后面三行编写了一个循环体去依次将三份文件中数据存入sales_all中。注意到小编这里使用的这个合并命令，当然你也可以使用，下面我们来统计一下合并后的文件的记录数：

看来我们正确地合并了文件，输出的元组中的第一个数字代表行数，第二个数字代表列数，所以我们合并后的数据集共有384行七列（注意在Excel文件中是385行，因为Excel文件将标题行也作为一行，但是python默认是不将行统计进行数的），这里可以看一下合并后的数据的前几行。

excel合并后的数据表

虽然不是必须的，但是对于最后一列我们最好还是将其转换为日期格式，这在python中很容易实现，直接输入一下命令即可：

输出效果表和上面一样，这里不再展示，只是将原来格式改变成了日期类型。

上面主要讲述了怎样将多个Excel文件合并成一个Excel文件，但是在现实中，我们可能更多的时候需要去根据原数据表中的某列值去相应地扩充原数据表的特征列，这在Excel中主要通过vlookup函数进行实现，下面我们将刚刚建成的sales_all数据表中增加一列等级列，以展示每一个客户的等级。等级数据已经在上文的下载出给出了，这里我们将其读入进来，并稍稍地看一些相关的数据特征。

可以看出客户的等级主要分成了金银铜三等，既然拥有了这一等级数据，下面就是要将其与sales_all数据进行连接，从而将sales_all数据表中的客户进行等级划分。

扩充列后的数据表

这样你便将等级数据表和原数据表合并在了一起，恭喜你完成了一个阶段性的成果。至此，小编完成了对Excel文件的行合并以及列扩充的相关介绍。在完成数据合并之后，你接着需要进行的是对于数据的清洗，从而获得整洁的数据以便进行分析。

第二讲：pandas完成数据清洗

上面虽然完成了对于数据表的合并连接，但是现实中因为数据的质量问题，往往会存在着数据记录重复、数据缺失以及异常值的情况。因为异常值的定义各有不同，通常的标准是三西格玛标准，所以这里不对其进行介绍，有需要的朋友可以留言给我。这里主要讲述重复值的剔除以及缺失数据的处理。

所谓重复值就是这那些行记录完全一致的数据，对这些数据，从数据角度看来，它们是无效数据，因此是必须要从数据集中进行剔除的，通过运行一下命令便可以将这些数据进行剔除。

这样便剔除了数据表中的重复值，因为本数据集并不存在重复值，所以这里这条命令实际上并没有剔除任何值，命令中的参数是指对原数据进行修改，而不是生成重新生成一个数据表副本。当然你也可以根据数据表中的某几列重复来剔除数据，这只需要你在上面的函数中增加一个参数即可，见下面：

这条命令会会剔除数据表中account number,name以及sku完全相同的记录，但是这里千万不要运行这条命令，小编这里只是举一个例子，如果你真的运行了这条命令，那么你就失去了很多有效的数据，所以可千万不要删除了！！！

完成了重复值处理，下面接着进行缺失值的处理吧，我们现在命令行中运行以下命令：

可以看出在客户的等级一列中存在着一些缺失值nan，这些值的存在在很多时候会严重地影响分析，所以任何数据分析之前都需要对这些缺失值进行妥善处理，因为这里的缺失值主要存在于等级列，而我们知道一般对于客户的分类中，如果你没有等级也就是最低等级，所以这里可以将这些缺失的等级全部定位青铜级，使用以下命令即可完成。

现在你的等级一列不再存在缺失值了，但是另外一个问题又再一次引起了主要，即等级列的数据类型竟然是，这与我们的认知存在一定偏差，对于这种具有明确等级的数据，一般我们都是将其定义为类别格式，所以下面运行以下命令将其改正过来。

第一条命令将等级列设置为了类别格式，而第二条命令则将这种类别予以了等级排序，其中，这与我们的观念相符。接着运行一下以下命令去查看一下数据表中的各列是否都具有的合适的数据格式。

OK!截止到这里已经完成了对于数据表的初步清洗，下面开始对这些相对整洁的数据进行数据分析，从而去发现一些有趣的现象。

第三讲：pandas与数据分析

数据分析，这个是一个比较综合的分析体系，如果指望这篇文章讲述清楚，那是完全不可能的，这里只是稍稍地讲解几点，后续文章中会进一步进行完善。上文对列完成了分类，那么如果你想知道等级下的人员分布情况，只需一条命令便可进行解决：

现在如果你想统计每种等级客户的成交量、单位价格以及总价的均值情况，那么你也仅仅只需要一条命令即可：

当然你不会满足与只了解均值，你可能还想了解总额以及标准差等，那么不好意思，我还是一条语句：

汇总数据

是不是很方便，在Excel中可能需要你做蛮久的工作，在python中，最多也就是这么一条语句就可以了，对于数据分析的更多内容，这里就不再进行讲述了，详情见后续文章。

后记

本期文章写到这里就告一段落了，通过本期文章，了解到了python+pandas如何去快速有效地对Excel文件进行合并、清洗与分析，当然这里的介绍只是冰山一角，更多内容留待后续。下一期文章中，笔者将对matplotib库绘图进行初步介绍，让大家再一次震撼于python的能力，敬请期待！最后，再一次感谢你们的支持，你们的支持是小编不断前行的动力。

PS：数据文件的下载请到小编的个人博客，微信在这方面真的很不方便呀