首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >"Python替代Excel Vba"系列(二):pandas分组统计与操作Excel

"Python替代Excel Vba"系列(二):pandas分组统计与操作Excel

作者头像
咋咋
发布于 2021-09-01 07:18:40
发布于 2021-09-01 07:18:40
1.9K00
代码可运行
举报
文章被收录于专栏:数据大宇宙数据大宇宙
运行总次数:0
代码可运行

系列列表

"替代Excel Vba"系列(一):用Python的pandas快速汇总

前言

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

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

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

本文要点:

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

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

案例

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

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

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

导入包

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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 不仅代码简洁易懂,并且整个过程都可以重复执行。

源码地址

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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-06-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据大宇宙 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
智能分析:ChatGPT+Excel+Python超强组合玩转数据分析
首先是顶流Python高举卷王之王的大旗向传统王者VBA抢班夺权,pandas, xlwings、OpenPyXL和Matplotlib等第三方包已经具备VBA和Power Query的几乎所有功能。
博文视点Broadview
2023/12/21
1.3K0
智能分析:ChatGPT+Excel+Python超强组合玩转数据分析
"替代Excel Vba"系列(一):用Python的pandas快速汇总
以前学习 Python 的 pandas 包时,经常到一些 excel 的论坛寻找实战机会。接下来我会陆续把相关案例分享出来,还会把其中的技术要点做详细的讲解。
咋咋
2023/11/06
6810
"替代Excel Vba"系列(一):用Python的pandas快速汇总
Python替代Excel Vba"系列(四):课程表分析与动态可视化图表
前一节我们已经成功把一份教师课程表整理成规范的形式,本节我们就看一下怎么利用这份数据得到一些信息。并且尽可能让每个部分都有可视化输出。
咋咋
2021/09/01
1.9K0
Python替代Excel Vba"系列(四):课程表分析与动态可视化图表
Pandas按班拆分Excel文件+按班排名和按级排名
1。用pandas.groupby+apply+to_excel进行按‘班别’列对一个Excel文件拆分成一个班一个文件的操作。简单又强大
哆哆Excel
2022/10/25
1.5K0
Pandas按班拆分Excel文件+按班排名和按级排名
"Python替代Excel Vba"系列(终):vba中调用Python
有小伙伴向我反映到,本系列前面的章节主要还是在讲 pandas ,几乎与 xlwings 没有啥关系。
咋咋
2021/09/01
6.2K0
"Python替代Excel Vba"系列(终):vba中调用Python
Excel数据处理你是选择Vba还是Python?当然是选pandas!
本号之前已经分享过关于如何使用 Python 中的数据处理分析包 pandas 处理 Excel 的数据,本文继续分享一个小案例,此案例源于上周末帮朋友做的一个需求,并且是以 vba 编写解决,后来我用 Python 再解决一次,通过本文作简单分享。
咋咋
2021/09/01
3.8K0
Excel数据处理你是选择Vba还是Python?当然是选pandas!
pandas_VS_Excel提取各班前2名后2名的数据
pandas_VS_Excel提取各班前2名后2名的数据 【要求】 提取各班前2名的数据 提取各班后2名的数据 【代码】 # -*- coding: utf-8 -*- ''' 提取出了分组中的前2名:例如:提取出各班的总分的前2名 提取出分组的中的后2名:例如:提取出各班的总分的后2名 ''' import pandas as pd df=pd.read_excel('数据源(5个班各6人).xlsx') #这里先插入一个列'班名次'方便自己提取出数据后进行观察 df['班名次']=df['总分'].
哆哆Excel
2022/10/25
4240
pandas_VS_Excel提取各班前2名后2名的数据
Pandas三百题
pd.set_option('display.max_columns',None)
SingYi
2022/07/13
5.3K0
Pandas三百题
数据分析之Pandas分组操作总结
Pandas做分析数据,可以分为索引、分组、变形及合并四种操作。之前介绍过索引操作,现在接着对Pandas中的分组操作进行介绍:主要包含SAC含义、groupby函数、聚合、过滤和变换、apply函数。文章的最后,根据今天的知识介绍,给出了6个问题与2个练习,供大家学习实践。
Datawhale
2020/06/23
8.4K0
高手系列!数据科学家私藏pandas高阶用法大全 ⛵
如果你是数据科学家、数据分析师、机器学习工程师,或者任何 python 数据从业者,你一定会高频使用 pandas 这个工具库——它操作简单功能强大,可以很方便完成数据处理、数据分析、数据变换等过程,优雅且便捷。
ShowMeAI
2022/12/04
6.5K0
高手系列!数据科学家私藏pandas高阶用法大全 ⛵
Python中Pandas库的相关操作
Pandas是Python中常用的数据处理和分析库,它提供了高效、灵活且易于使用的数据结构和数据分析工具。
周小末天天开心
2023/10/16
1K0
vba新姿势,如何让vba的数据处理"超越"Python
上一节我们讨论了 Python 在数据处理上的优势,前后台大概收到的有用评论如下:
咋咋
2021/09/01
3.4K0
vba新姿势,如何让vba的数据处理"超越"Python
"Python替代Excel Vba"系列(三):pandas处理不规范数据
本系列前2篇已经稍微展示了 python 在数据处理方面的强大能力,这主要得益于 pandas 包的各种灵活处理方式。
咋咋
2021/09/01
5.6K0
"Python替代Excel Vba"系列(三):pandas处理不规范数据
用 Pandas 进行数据处理系列 二
获取行操作df.loc[3:6]获取列操作df['rowname']取两列df[['a_name','bname']] ,里面需要是一个 list 不然会报错增加一列df['new']=list([...])对某一列除以他的最大值df['a']/df['a'].max()排序某一列df.sorted_values('a',inplace=True,ascending=True) , inplace 表示排序的时候是否生成一个新的 dataFrame , ascending=True 表示升序,默认为升序,如果存在缺失的补值( Nan ),排序的时候会将其排在末尾
zucchiniy
2019/10/30
8.6K0
懂Excel也能轻松入门Python数据分析包pandas(二):高级筛选(下)
经常听别人说 Python 在数据领域有多厉害,结果学了很长时间,连数据处理都麻烦得要死。后来才发现,原来不是 Python 数据处理厉害,而是他有数据分析神器—— pandas 。
Excel催化剂
2021/08/20
5390
懂Excel也能轻松入门Python数据分析包pandas(二):高级筛选(下)
用Python自动生成Excel数据报表!
原始数据如下,主要有水果蔬菜名称、销售日期、销售数量、平均价格、平均成本、总收入、总成本、总利润等。
小F
2021/04/09
2.2K0
用Python自动生成Excel数据报表!
14个pandas神操作,手把手教你写代码
导读:Pandas是Python数据分析的利器,也是各种数据建模的标准工具。本文带大家入门Pandas,将介绍Python语言、Python数据生态和Pandas的一些基本功能。
Python进阶者
2021/07/27
3.9K0
14个pandas神操作,手把手教你写代码
【数据处理包Pandas】分组及相关操作
数据集team.xlsx下载地址: 链接:https://pan.quark.cn/s/9e3b2a933510 提取码:7i2y
Francek Chen
2025/01/22
4780
【数据处理包Pandas】分组及相关操作
50个超强的Pandas操作 !!
首先给出一个示例数据,是一些用户的账号信息,基于这些数据,这里给出最常用,最重要的50个案例。
JOYCE_Leo16
2024/03/22
1.4K0
《Python for Excel》读书笔记连载12:使用pandas进行数据分析之理解数据
说明:有点忙,这本书最近更新慢了一些,抱歉!这部分仍免费呈现给有兴趣的朋友。附已发表内容链接:
fanjy
2021/12/20
4.8K0
《Python for Excel》读书笔记连载12:使用pandas进行数据分析之理解数据
推荐阅读
相关推荐
智能分析:ChatGPT+Excel+Python超强组合玩转数据分析
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验