"Python替代Excel Vba"系列（三）：pandas处理不规范数据

前言

本系列前2篇已经稍微展示了 python 在数据处理方面的强大能力，这主要得益于 pandas 包的各种灵活处理方式。

但是身经百战的你肯定会觉得，前2篇例子中的数据太规范了，如果把数据导入到数据库还是可以方便解决问题的。

因此，本文将使用稍微复杂的数据做演示，充分说明 pandas 是如何灵活处理各种数据。

本文要点：

• 使用 pandas 处理不规范数据。
• pandas 中的索引。

注意：虽然本文是"Python替代Excel Vba"系列，但希望各位读者明白，工具都是各有所长，选择适合的工具，才是最好的。

案例

这次的数据是一个教师课程表。如下图：

• 其中表格中的第3行是班级。诸如"一1"，表示是一年级1班，最多8个年级。
• 表格中的1至3列，分别表示"星期"、"上下午"、"第几节课"。
• 前2列有大量的合并单元格，并且数据量不一致。比如星期一有9行，但星期二却只有7行。
• 表格的主要内容是，每天每个班级的每堂课是什么课以及是那位教师负责。诸如"语文xxx"，表示这是语文课，由xxx老师负责。这里的名字按照原有数据做了脱敏。

这是典型的报表输出格式，其中有合并单元格，内容把科目和人名回到一起去。由于案例原有的需求比较繁琐，本文核心是处理数据，因此简化了需求。

不管我们的分析目的是什么，第一步就是要把这份数据整理好，才能应对各种分析。我们开始吧。

导入包

本文所需的包，安装命令如下:

pip install xlwings
pip install pandas
pip install numpy

建议你安装 anaconda ，那么最难安装的 pandas 和 numpy 都不会是问题。

脚本中导入

设定目标数据格式

我们需要得到最小维度的数据格式，即每天每个班的每节课是哪位老师负责的哪个科目。如下：

为了管理方便，下面会把每个环节的处理放入一个独立的方法中

加载数据

代码如下：

• 由于这次的标题是从第3行开始，因此 wrk.range('a3').current_region 会导致内容包含了前2行。
• .options(np.array)，因此我们把整块数据加载到 numpy 的数组中。numpy 数组可以很方便做各种切片。
• header=arr[2] ， 取出第3行作为标题。注意索引是从0开始算。
• values=arr[3:]，从第4行往后一大片作为值。
• pd.DataFrame(values,columns=header) ， 生成一个 DataFrame 。
• .replace(['/','nan'],np.nan)，把读取进来的有些无效值替换为 nan，这是为了后续操作方便。

我们来看看数据：

• 注意看左上角有3个 nan ，是因为表格的标题行前3列是空的。
• 由于前2列有合并单元格，出现了很多 nan。
• 此外注意看第3列，把课时序号显示成小数。

处理标题

pandas 的 DataFrame 最大的好处是，我们可以使用列名字操作数据，这样子就无需担心列的位置变化。因此需要把标题处理好。 代码如下：

• cols=df.columns.tolist()，把 df 的字段拿出来。这是一个list
• cols[:3]=['day','apm','num'] ，把列表的前3项的 nan ，替换成我们需要的字段名字。
• df.columns=cols ， 表示更新 df 的字段
• df['num'].astype('float').astype('int') ，顺手把 num 字段的小数变整数。这里不能直接转整数，因为 python 怕有精度丢失，直接转换 int 会报错。因此先转 float，再转 int。

再次看看 数据，一切正常：

填充缺失

下一步就是把前2列的 nan 给填充正确。

• df[cols]=df[cols].fillna(method='ffill') , fillna 方法即可填充 nan 。此外 pandas 中有各种内置的填充方式。 ffill 表示用上一个有效值填充。
• 合并单元格很多时候就是第一个有值，其他为空，ffill 填充方式刚好适合这样的情况。

现在数据美如画了。

重塑

要理解 pandas 中的重塑，先要了解 DataFrame 的构成。 如下是一个 DataFrame 的组成部分：

• 红框中的是 DataFrame 的值部分(values)
• 上方深蓝色框中是 DataFrame 的列索引(columns)，注意，为什么方框不是一行？是因为 DataFrame 允许多层次索引。类似于平时的复合表头。
• 左方深蓝色框中是 DataFrame 的行索引(index)。本质上是与列索引一致，只是 index 用于定位行，columns 用于定位列。

不要被"多层次索引"这种词汇吓到，其实是我们经常遇到的东西。 下面来看看一个多层次索引的例子：

• 上图的上方有3个层次的列索引，依次从上到下。
• 上图的左方有2个层次的行索引，依次从左到右。
• 我们平时操作 DataFrame 就是通过这两个玩意去定位里面的数据。

如果你熟悉 excel 中的透视表，那么完全可以把行列索引当作是透视表中的行列区域。

理解了索引，那么就要说一下如何变换行列索引。 pandas 中通过 stack 方法，可以把需要的列索引转成行索引。 用上面的数据作为例子，我们需要左边的行索引显示每天上下午的气温和降雨量。如下图：

• 不妨在 excel 的透视表上操作一下，把一个放入列区域的字段移到行区域上，就是上图的结果。

回到我们的例子。我们需要把前3列放入行索引，然后把整个列索引移到行索引上。 代码如下:

• .set_index(['day','apm','num']) , 把这3列放入行索引区域。
• .stack() ，stack 方法默认把最后的列索引区域的最后一个列索引，移到行索引区域的最后。由于目前的 df 只有一个列索引，因此直接调用 stack 即可。
• 此时，由于把唯一的列索引移走了，df 已经没有任何列索引！
• .reset_index(-1) ， 把最后的行索引移走，并成为单独的一列。
• 到此，df 又重新有了一层列索引。

看看现在的数据，如下：

剩下的工作则非常简单，主要是把班级和内容分成2列。

数据如下：

最后

本文通过实例展示了如何在 Python 中使用 xlwings + pandas 灵活处理各种的不规范格式表格数据。这种方式尤其适合报表形式的数据。

[源码地址](https://github.com/CrystalWindSnake/Creative/tree/master/python/excel_pandas/3)

请关注本号，后续会有更多相关教程。