"Python替代Excel Vba"系列（二）：pandas分组统计与操作Excel

2021-09-01 15:18:40 浏览数 (1)

系列列表

"替代Excel Vba"系列（一）：用Python的pandas快速汇总

前言

在本系列的上一节已经介绍了如何读写 excel 数据，并快速进行汇总处理。但有些小伙伴看完之后有些疑惑：

  • 那只是简单读写数据而已，但有时候我需要设置 excel 的格式。
  • 我用透视表不用写代码，两三下也可以弄出结果来。

今天，我就沿用上一节的数据，把需求升级一下，以解决上述的疑点。

本文要点：

  • 使用 xlwings ，设置单元格格式
  • 使用 pandas 快速做高难度分组操作

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

案例

数据与上一节一样，就一个学生的数据表。

不过这次我们需要把每个班级成绩好的同学给揪出来好好表扬，因此条件如下：

  • 找出每个班级的top 3 学生，在原数据表中以绿色底色标记
  • 找出每个班级中低于班级平均分的学生，在原数据表中以红色底色标记
  • 上述条件均以[总分]列为判断依据

导入包

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

代码语言：javascript复制
pip install xlwings
pip install pandas

脚本中导入

本文只说重点细节，至于如何从 excel 中读取数据，上一节已经有详细介绍。

排名

首先需要解决的是怎么得到班级 top 3？

首要任务是得到排名，如下:

  • 这里需要在数据中新增一列[排名]
  • df.groupby('班级') 就是按 班级 分组的意思。
  • df.groupby('班级')['总分'] 表示分组后每个组我们只使用[总分]这个字段。
  • .rank(ascending=False,method='min') 是 pandas 中进行排名的处理。
    • 参数 ascending=False ，表明需要以 [总分] 倒序做排名。
    • 参数 method='min' ，表明如果有多个人有相同的总分，那么全部的人都用所有名次中最小的排名值。后面会看到数据。
  • 此时显示变量 rank 的数据，可以看到结果就是排名结果(1列数据)
  • 在 pandas 中往 DataFrame 中新增一列非常简单。 df['排名']=rank ，即可把排名结果放入表中新增的字段中。
  • df.sort_values(['班级','排名'],inplace=True) ,按先[班级]后[排名]进行排序，不是必须的，只是为了方便查看数据。
    • 参数 inplace=True ，表示直接在原有数据上操作，如果不设置这个参数，那么就需要写 df=df.sort_values(['班级','排名'])

来看看结果。

  • 注意看第3和4行数据，他们是并列第3名。并且后面的人是从第5名开始。

找出低水平学生

现在找出低于所在班级平均分的同学吧。 先按班级计算平均分，然后把平均分填到每一行上。

  • df.groupby('班级')['总分'] 就不用说了，与上面的排名是一样的意思。
  • .transform('mean') ，表示每组求平均。结果是每组都有一个分数。而 transform 方法的特点就是不会压缩原数据的行数，因此每组的数都是一样的平均分。
  • df['班级均分']=class_avg ，同样新增一列。

看看数据

对于这里的 transform 方法可能有些小伙伴会不太理解。后续我会另外发文，针对分组后的 apply、agg、transform 做详细的讲解，关注我噢。

万事俱备

看到这里，你可能会觉得很复杂，但注意，我们只是写了2句代码即可做到了比较复杂的分组汇总。

首先把 top 3的同学挑出来

  • df.query('排名<=3') ，过滤符合条件的记录。

接着把低于平均分的也挑出来

  • df.query('总分<班级均分') ，过滤符合条件的记录。

但是，需求是需要我们在原表格上标记颜色。怎么可以用目前的结果数据关联到原数据上。

我们注意看得到的结果中的 index。就是最左边的那一列数字

每个 DataFrame 都会有这样的 index，不管你怎么操作他，这个 index 都不会改变。因此我们可以利用 index 定位 excel 的单元格，然后通过 xlwings 标记底色就好了。

给表格加点颜色

首先定义一个设置颜色的方法

  • indices=(n for n in arg_df.index) ， 获得结果的索引值。
  • rng=top_range.offset(i).resize(1,cols_count) ，定位需要设置颜色的行。
  • rng.api.Interior.Color = color ，设置单元格底色。注意这里 .api 是因为 xlwings 是对 com 的封装，许多属性xlwings并没有做，因此许多对象都提供了 api 这个属性，以便你能用 com 对象那套方法去操作。

如果你对 excel 不熟悉，可能你会问，你怎么知道设置颜色是这些代码？ 其实我是通过录制宏来得到。如下：

完整代码

以下是完整的代码：

本节就不再上 vba 代码了(没人愿意写~~)。

彩蛋

目前为止，我们只是在用 Python 做数据处理，很多时候我们还会使用 excel 做分析。下一节就会对目前的数据做探索分析，敬请留意。

总结

通过本文应该可以解答之前的一些疑惑。像本次需求中的数据处理任务，即使你用透视表来解决也是不容易的，更不用说用 vba 了。 使用 python 不仅代码简洁易懂，并且整个过程都可以重复执行。

源码地址

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

css python 数据处理

0 人点赞