当前位置：网站首页 > 技术资源 > 正文

excel数据处理需求，透视表无法解决?用Python吧

off999 2024-09-16 00:40 57 浏览 0 评论

请关注本号，后续会有更多相关教程。转发本文并私信我"python"，即可获得按水平领域分类好的Python资料

系列文章

前言

本系列前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列。

数据如下：