用pandas处理Excel

Excel是我们最熟悉的数据分析软件，它足够强大，在VBA的配合下几乎能做任何事情。但很多时候，我们又觉得Excel不够简洁，一个简单的功能实现都需要嵌套函数或使用宏。于是，数据科学家开始使用R、Stata或Python读取Excel文件，在纯代码环境下进行数据处理——pandas就是最好用的工具之一。

1 导入与导出

假设我们有一份Excel表格（ ）存放于D盘的根目录下，使用pandas的顶级方法 即可读取该表格：

当我们修改过数据框后，可以通过 的顶级方法将其保存到文件中，当然，我们也不一定非要保存为Excel的文件格式。

2 数据观察

读取后的data变量是数据框类型，默认表格的第一行是列标签，pandas会自动为数据框添加行标签。我们可以通过 和 方法观察数据框对象的前几行和后几行，以此确定数据内容，特别是了解行标签和列标签。

通过观察，我们基本可以确定，data.xlsx这张表格中有5列，分别是序号、姓名、班级、课程和成绩。进一步地，我们可以使用 方法了解一定口径下的数据统计特征。例如，我们想知道1班数学成绩的分布情况：

返回值包括样本量、均值、标准差、极小值、极大值和四分位数。

3 表格的合并

在Excel中，我们使用MATCH和VLOOKUP函数来合并表格，但这种合并方式的效率相对较低；微软已经在Power系列中引入了更为高效和灵活的数据合并方式，与pandas的合并方式异曲同工。

用append进行0轴合并

我们使用 函数进行0轴合并，假设我们又读取了一个被称为dataplus的表格，其中包含三名重修学生的成绩，但重修的同学没有序号和班级，仅包含name、course和score三列：

不难看出，dataplus中的数据按照data的列标签进行了对齐，缺失数据用 代替。如果我们希望用缺失值来筛选，可以使用 方法并结合条件切片。例如，我们希望找到班级为空值的学生的数据：

用merge进行1轴的合并

pandas下的merge方法可以快速实现1轴合并，其效率超过Excel和R。假设我们已经知道1号至25号学生为男性，26号至50号学生为女性，并希望将这一信息添加到data表格中，就可以使用merge函数。

merge函数强大的原因来自how参数，默认是inner，可选outer、left和right，这让我们在拼接表格后保留其一的行标签或取其交集或并集；key参数是用来匹配行的标准，能够用列表表示多个标准，这是VLOOKUP做不到的（大约从我高中时期微软就说要出VLOOKUPS，至今也没有）。

concat函数

当然，无论是纵向合并（0轴合并）还是横向合并（1轴合并），我们都可以使用concat方法，只需要注意其中的axis参数是0还是1。如

参数中的axis最为关键，表明合并是在哪个轴上实现的；join_axes参数表示我们合并后的数据框保留哪些列；ignore_index参数表明是否忽略合并前的行标签（一般都不用保留）。

对Excel合并功能的补充

Excel 2016开始，Power Query被集成在【数据】菜单下，使用者可以更方便地追加（0轴）和合并（1轴）数据，但在Excel内使用Query并不是最有效率的，安装并独立使用Power BI可以更加高效地连接数据。

4 函数

使用pandas处理数据，函数就不再是一个问题。我们不用去学习函数和数组公式，pandas的灵活性很高，使用起来也很方便。在此仅举一例，假设我们希望求取1班同学的数学考试平均分，可以这样做：

对满足多个特定条件的数据进行统计，始终是Excel函数的难点，而在pandas中就很简洁清晰。如果这个例子还不能体现pandas的优势，大家可以尝试以下要求：将1号至50号同学文理分科，语文成绩高于数学成绩的学文科，否则学理科。

需要注意的是，Excel提供了大量行业函数，比如财务、统计、工程等等，这就要结合相应的Python库来实现，以减少自己造轮子的繁琐。

5 数据透视

对很多学生而言，在Excel中使用数据透视表已经是很高级的操作了，这项功能确实很厉害，微软足够重视并将pivot table注册为商标，Power Povit更是一款独立的透视表工具（也可以在Excel和Power BI中使用）。pandas也有数据透视，大家可以从它的同名函数 入手来了解它。

从一个例子开始。假设我们希望统计每个同学三门课的平均分，可以用以下方法：

其中，data参数是我们需要建立数据透视的原表格；index是汇总后的行标签（对应Excel透视表的“行”）；默认采用展示均值的方法进行数据汇总。结果如下：

如果不使用数据透视，我们就只能循环得出每个人的三门均分。比如用下述代码可以做一个验证，姓名为1的同学，三门均分是不是78.67呢？

显然，这样的数据透视还过于简单了，我们至少需要五个方面的改进：

只展示特定列：values

我们使用values参数来控制进入汇总的列数据（对应Excel透视表的“值”），比如本例中，我们只需要统计每位同学的三门均分，因此将score列放进来就足够了。

多层统计：index

假设我们希望统计每个班的每门课的均分，这就是一个多层级的汇总，可以通过调整index参数（老版本中叫rows）的方式实现：

注意index参数列表中的元素是有顺序的，即班级优先，在每个班级下再统计科目，最终的结果如下：

改变汇总方式：aggfunc

此前的例子都是计算的平均值，但汇总时我们常需要一些其他的方式，常见的如总和、方差、计数等等。pivot_table函数为我们提供了一个奇怪的aggfunc参数，它同样是一个列表，你可以将所有你需要的统计方式一次性放进去。假设我们希望统计每个班级每门课成绩的均值和标准差，可以通过添加aggfunc参数来实现：

我们可以清晰地看出，1班的语文成绩不仅均分最高，标准差也最小，考得很不错。

增加汇总维度：columns

还记得吗，我们有一个添加了性别列的newdata数据表格，这回我们用到它了，如果找不到了，你可以在这里复习一下merge函数：

然后，假设我们希望统计每个班级的每门课程的均分和参考人数，并按照性别分别汇总，就需要通过columns参数将新维度（gender）增加在列标签上，即

注意这里还有一个fill_value参数，它是用来将空值填充为某个特定值的，比如这里的0。从下面的结果中，我们能够看出1班全是男生，3班全是女生，而2班女生虽然少，但成绩比男生好很多。没想到我随机生成的数据也这么伤人...

各行各列汇总：margins

如果你需要各行或各列的汇总值，就在参数中设定margins=True，不必赘述。

6 缺失值

一说到数据分析，就不得不提缺失值的处理。pandas提供了很多简便地缺失值填充方法。

dropna()和notnull()

如果有缺失值，我们最常用的方法是把这一行数据删掉，这就用到 方法：

函数默认丢弃有空值的行，我们可以通过传入how参数（=‘all’）来删除全空值的行，或传入axis参数（=1）来删除有空值的列。

有时候我们也用notnull()方法，但毕竟啰嗦了一点：

fillna()

如果你舍不得把有缺失值的行或列扔掉， 函数就派上了用场。比较常见的用法包括

将空值替换为特定值

其中的inplace参数我们并不陌生，该参数取值为True时表示原地操作，即修改对象data，而不是生成一个新的对象。用于填充的值可以是数值，也可以是字符串。

对不同列填充不同的值

这里对列标签为a的列填充4，而对列标签为b的列填充1。

按特定方法填充：method参数

method参数能实现的填充方式有两种：用前一个非缺失值去填充或用后一个非缺失值去填充，对应的参数取值分别为pad/ffill和backfill/bfill。写作：

需要注意的是，method参数往往和limit参数结合使用，即限制先前或向后填充的缺失值数量，如果limit=1，那就只能用非缺失值补上紧随其后的一个缺失值，再多就只能保留空值了。

按特定方法填充：表达式

我们还可以用表达式来明确希望填充的值，比如常用均值来填充缺失值，这跟“将空值替换为特定值”并没有什么不同：

replace()

并不是所有时候缺失值都以空值的形式存在，更多的情况下，为了避免混淆并占位，我们会用一个明显的异常值去表示缺失值，比如用0表示未婚，1表示已婚，999表示空值。这时 就无用武之地，需要用到 函数。假设我们希望将data数据框中的空值替换为0，而将999替换为9：

结束语

pandas的功能还有很多，它2000多页的手册并不全是致谢；它在数据分析上的优势不仅来自于自身的强大功能，还来自于与海量Python数据分析库的无缝对接，比如与glob库结合对文件夹中所有Excel文件进行批处理。当然，写在最后，我还是要说，pandas真的很强大，Excel真的很强大，Power BI真的很强大。但它们都只是工具，数据分析除了技法外，还有人心。