前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Excel数据处理你是选择Vba还是Python?当然是选pandas!

Excel数据处理你是选择Vba还是Python?当然是选pandas!

作者头像
咋咋
发布2021-09-01 15:28:02
3.4K0
发布2021-09-01 15:28:02
举报
文章被收录于专栏:数据大宇宙

前言

本号之前已经分享过关于如何使用 Python 中的数据处理分析包 pandas 处理 Excel 的数据,本文继续分享一个小案例,此案例源于上周末帮朋友做的一个需求,并且是以 vba 编写解决,后来我用 Python 再解决一次,通过本文作简单分享。

数据与需求

此案例的数据如下:

  • - 每个开单人员的销售记录
  • - 描述为: 销售员"张三"(开单部门),把xxx货品(货品编码、货品名字)售出了5件(数量),此笔订单总价为2000元(价税合计)
  • - 上述的括号部分就是表中的列标题
  • - 数据行中,有许多无效的行,只要 开单部门 列有名字,就是有效的行

此案例的数据对所有敏感数据进行随机生成替换

需求结果如下图:

  • - 按 销售员、货品编码,汇总 货品数量和价税合计
  • - 每个销售员单独生成一个表输出
  • - 最后再输出一个所有货品的汇总表

vba 的方案简短分析

本文的核心不是 vba ,因此这里只做简单的讲解,如果你是 vba 用户,可以获取源码查看。

vba 的解决方案尽可能采用了面向对象的方式进行,下图为主要结构:

  • - m_main 模块包含整个主流程逻辑,入口方法 main
  • - 类模块 D_ArrayVar 与 D_GVar ,是为了做到 数据源的列位置变化,也不需要修改程序
  • - D_Person 与 D_Sku ,分别表示销售员与货品,里面有关键的累计逻辑
  • - 模块 Msys_Function 与类模块 C_GetFile ,是很久以前写的帮助类

如果你是 vba 的高级用户,可能会觉得直接使用 字典+数组 的方式即可完成,但注意,直接 字典+数组 方式会导致代码难以维护

Python 的方案

上面说的 vba 方案,我大概花费了接近1小时的时间(vba 中编写类模块太繁琐了),期间有一个需求变动,得益于面向对象的优点,在几分钟内完成应对,并且无需要大范围做测试。

但是,这样的需求如果在 Python 中,我们的处理效率可以提高多少呢?我使用 Python 的 pandas 包处理,在5分钟内搞定,并且代码有非常好的阅读性与扩展性。

这次我们直接使用 pandas 读写 excel 数据,而无需使用 xlwings 库

首先定义需要的列与每列的统计方式:

  • - 其中核心是 g_agg_funcs 字典,他定义了每个输出列的统计方法。 凡是文本类型的内容,统一用 first ,就是去组内的第一笔

接着定义加载 excel 数据到 DataFrame:

  • - 由于数据源的标题在第3行,因此在调用 read_excel 时,参数 header 设置为 None,表示不需要用 excel 中的数据行作为 DataFrame 的标题
  • - header=df.iloc[header_idx,:] ,把指定行的内容读取出来
  • - df.columns=header ,赋值作为 df 的标题
  • - df.dropna(subset=[g_pName]) ,把名字列中是空的行去掉

然后即可生成结果,如下:

  • - df.groupby(cols).agg(g_agg_funcs) ,按销售员与货品分组并统计结果,pandas 中就是这么简单

但是,我们需要每个销售员单独一个 sheet 输出结果。如下图:

  • - with pd.ExcelWriter('result.xlsx') as exl: ,由于本案例需要对一个 excel 文件进行批量输出,因此不能直接使用 DataFrame.to_excel 。 这里先创建一个 ExcelWriter对象
  • - res.index.get_level_values(0) ,从分组结果中获得销售人员列,但这里的输出是带重复值的,因此我们需要使用 set 去重复
  • - res.loc[idx,:] ,通过一个销售人员,即可获得这个销售员的货品汇总结果(是一个 DataFrame),这时就可以调用 to_excel 输出结果
  • - to_excel 中的参数 startrow ,表示结果输出在第2行

到这里,你可能会问,还有一个按照货品的汇总结果啊,这是非常简单,因为汇总方式是一样的,只是汇总字段有变化而已。如下:

  • - 这里特意重复写一次 ExcelWriter ,我们这次是往已经存在的 excel 文件追加数据,因此其参数 mode='a' ,是 append 的意思。 而要使用追加模式,需要使用 openpyxl 引擎,因此需要设置 engine='openpyxl'

新增需求

在完成代码的情况下,如果需要在汇总结果中新增一列对单价列求平均,在 Python 的方案中,只需要在定义 g_agg_funcs 中添加单价列的统计方式,如下:

如果是在 vba 方案中,目前的修改还是比较容易的(在 sku 类模块的 add 方法中添加逻辑),但是与 Python 的方案比较就显得低效得多。

总结

pandas 使用总结如下:

  • - 理解好 pandas 中的索引(特别是多层索引)可以大大提升你的数据处理能力
  • - pandas 中如果需要多次输出同一个 excel 文件,可以使用 ExcelWriter,注意追加模式需要设置参数 engine='openpyxl'

vba 使用总结如下:

  • - 如非一次性代码,请面向领域设计代码(如本文例子),而非面向数据设计代码

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

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

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档