学Python还不会处理Excel数据?带你用pandas玩转各种数据处理

x
用微信扫描二维码
分享至好友和朋友圈

  

  

  前言

  以前学习 Python 的 pandas 包时,经常到一些 excel 的论坛寻找实战机会。接下来我会陆续把相关案例分享出来,还会把其中的技术要点做详细的讲解。

  本文要点:

  使用 xlwings ,如同 vba 一样操作 excel

  使用 pandas 快速做透视表

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

  案例

  今天的例子非常简单,从一个表中读取学生的数据,然后按班级汇总各个科目的成绩。下图,左为原始数据,右为示意结果:

  

  导入包

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

  1. pip install xlwings

  2. pip install pandas

  脚本中导入

  

  读取表格的数据

  

  使用 xw.books[] 可以快速访问当前打开的工作簿。可以使用索引也可以使用名字。

  同样,通过 book.sheets[] 快速访问工作表,可以使用索引也可以使用名字。

  接下来读取表格数据

  

  通过 sheet.range(地址) 即可访问单元格区域。

  接着使用 current_region 快速得到整个表格数据。这里可以使用其他方式定位数据的大小。

  options(pd.DataFrame) 是一个很关键的操作,我们希望把数据放入 pandas 的 DataFrame ,以便快速处理数据。然后通过 value 获得。

  我们来看看数据

  

  现在姓名列变成了 index 。不过需求是不需要理会姓名,因此我们不处理。

  [班级]列变成小数。其实是小数也不会影响结果。

  数据透视

  接下来就非常简单,直接使用 pandas 做出透视表。

  

  使用 pd.pivot_table ,即可快速生成透视表。

  其中参数 index ,则是结果左边的行分类字段——[班级]。

  参数 margins ,表示生成一个汇总行。

  参数 margins_name ,则是汇总行的名字。

  注意,其实还有一个参数 aggfunc ,用于指定汇总方式,默认是 mean。

  如果你对 excel 的透视表比较熟悉就会马上学会这些。

  

  index 相当于 excel 透视表的行区域。

  values 相当于 excel 透视表的值区域。

  columns 相当于 excel 透视表的字段区域。

  放入 index 与 columns 的字段,一般是分类的字段,比如:班级,性别。

  放入 values 的字段,一般是连续值,比如:分数,销售额。如果是类别的值,一般会用于统计个数。

  上述3个参数都可以传入列表,以表示处理多个字段。

  但是,看一下结果,却发现了一些问题:

  

  列的顺序与原数据不一样了。

  结果需要把汇总列放到最右边。

  下面是针对上述问题的解决方法

  

  cols=df.columns[1:].tolist() 首先需要读取原数据的字段(第一个字段是班级,因此通过切片 1: 从第2个字段开始)。

  cols.append(cols.pop(0)) 把[汇总]移到列表的最后。

  pv_df=pv_df[cols] 把透视表的字段调整为我们需要的顺序。

  pv_df.reset_index(inplace=True) 是为了把[班级]从 index 移动回来作为 column。

  看看结果,非常完美

  

  输出结果

  把 DataFrame 写回 excel 是非常容易。比如: wrk.range('O11').value=pv_df

  但是这会把其中的 index 也输出到 excel上。因此,我们可以分开两步输出。如下:

  

  第一行代码,首先输出字段行。

  第二行代码,输出值。

  完整代码

  以下是完整的代码:

  

  与 Vba 的对比

  本文的案例是从某个知名 excel 论坛中挑选的,我从中挑选了最简短的 vba 解决方案。如下:

  

  可以看到使用 vba 进行统计,代码很长。

  如果原数据的字段顺序有变化,这代码立刻无效。并且代码仍然可以跑出结果,只是错误结果而已。

  如果需求有变化,比如:求出每个班级的 top 3的学生。很快就放弃了吧。

  总结

  如果需要从 excel 读取数据进行汇总处理,可以选用 xlwings + pandas(如果数据非常规范并且无需处理格式等,可以直接使用 pandas)。

  pandas 中的 pivot_table 快速得到各种方式的分组汇总。

  源码地址

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

特别声明:本文为网易自媒体平台“网易号”作者上传并发布,仅代表该作者观点。网易仅提供信息发布平台。

跟贴 跟贴 2 参与 2
© 1997-2019 网易公司版权所有 About NetEase | 公司简介 | 联系方法 | 招聘信息 | 客户服务 | 隐私政策 | 广告服务 | 网站地图 | 意见反馈 | 不良信息举报

python数据领地

专注于有趣的Python

头像

python数据领地

专注于有趣的Python

5

篇文章

85

人关注

列表加载中...
请登录后再关注
x

用户登录

网易通行证/邮箱用户可以直接登录:
忘记密码