python3 pandas 实现excel数据分析的 数据分类汇总 按条件求和 以及按条件计数 和excel 赛选,excel指定改列名

Python 自动化办公 专栏收录该内容
14 篇文章 5 订阅

本人是在一家零售行业工作,所出的教程,都是我日常工作中遇到的 复杂而大量重复的工作 我用python代替去完成它,都是原创内容,非粘贴复制,如果我的文章能够帮助到大家,希望帮忙点个关注。当然,如果有很多错别字,也请见谅。

问题来了:

领导总是叫我每天导出来分析,但是我们公司分析数据总是要到 大区和小区,因为我们大区和小区都有负责人,KPI考核要对应负责人,通过数据去体现问题。这一次领导叫我把线上的门店自提订单数据导出来做数据分析。

题外话:因为公司对接到第三方ERP系统都是以门店为主去对接的,因为我们钱给的少,所以第三方数据不支持定制化数据分析。只能靠我们手工用excel 去做表。但是如果只是做一次,还勉强用excel 能做好。如果是天天都需要数据跟进,那天天都需要做数据赛选和匹配。虽然excel 可以做到输入好公式,把数据源复制粘贴进去,可以自动计算。但是excel 公式多,会非常的吃电脑性能,而且出现无响应等。所以这个时候我们需要利用代码去完成这个,我们叫的高大上的名字 办公自动化

先给大家看下从ERP导出来的数据源是什么样子:

订单状态订单金额(实付)下单时间提货门店销售门店(code)客服备注商品金额总计
订单支付已过期0.102019-09-17 10:45:56多多一上总部A9999 0.10
订单支付已过期0.102019-09-17 17:33:07江南摩尔店A9999 0.10
订单支付已过期0.102019-09-17 17:33:59多多一上总部A9999 0.10
订单支付已过期0.102019-09-17 17:35:04江南摩尔店A9999 0.10
订单支付已过期0.102019-09-18 08:24:09江南摩尔店A9999 0.10
订单支付已过期0.102019-09-18 08:28:07江南摩尔店A9999 0.10
订单支付已过期0.102019-09-18 08:29:25嘉兴平湖店A9999 0.10
订单支付已过期0.102019-09-18 08:53:05江南摩尔店A9999 0.10
订单支付已过期0.102019-09-18 09:51:24多多一上总部A9999 0.10
订单支付已过期0.102019-09-18 09:56:04多多一上总部A9999 0.10
订单支付已过期0.102019-09-18 10:17:22多多一上总部A9999 0.10
订单取消0.012019-09-18 10:55:52江南摩尔店A9999 0.01
待备货29.002019-09-23 19:36:06兰溪星辰店A0012 29.00
待备货29.002019-09-23 19:38:32巫山祥云店B0203 29.00
订单支付已过期19.002019-09-23 19:38:51全椒新华路A0466 19.00
订单取消29.002019-09-23 19:49:06宣恩解放街A0465 29.00
订单支付已过期29.002019-09-23 19:49:12临沂兰山店A0265 29.00
待备货29.002019-09-23 19:49:34枣庄薛城财富步行街A0295 29.00
待备货67.002019-09-23 19:50:12三水湾店A0105 67.00
待备货67.002019-09-23 19:50:12三水湾店A0105 67.00
待备货67.002019-09-23 19:50:12三水湾店A0105 67.00

在数据合并前需要准备一个带有大区,小区 ,门店 的表去根据 “提货门店” 和 下方 的 “门店” 合并 如下表:我看到数据后先做的是 python 的 merge 数据合并,这里合并就是excel 函数的vlookup 是一样的基本功能,如果要区别的,我觉得merge 还是要强大。

大区小区门店
南方大区温州大区温州楠江店
南方大区温州大区温州新桥店
南方大区温州大区温州状元店
南方大区温州大区温州浦西店
南方大区温州大区温州北白象新店
南方大区温州大区路桥金清店
南方大区温州大区温岭松门店
南方大区温州大区温州蟠凤商业街
南方大区温州大区区域合计
北方大区江苏一区淮安幸福店
北方大区江苏一区漕运广场店
北方大区江苏一区东长街店
北方大区江苏一区淮安盱眙店
北方大区江苏一区阜宁阜师路
北方大区江苏一区淮安汇通店
北方大区江苏一区东大街店
北方大区江苏一区少年宫店

代码如下:

#encoding:utf-8
import pandas as pd #导入pandas包
rd_excel1 = pd.read_excel(r"D:\111\Book1.xlsx") #读取具有大区,小区,门店的表
rd_excel2 = pd.read_excel(r"D:\111\12049_2019092810070530368495.xls") #读取数据源表
merge_excel = pd.merge(rd_excel1,rd_excel2,left_on='门店',right_on='提货门店') # 进行指定相同值的合并
merge_excel.to_excel(r"D:\111\merge.xlsx") #保存为merge.xlsx 

合并后效果如下:

事情还没完,这个只是在数据源中匹配合并好了提货门店所属的大区和小区

下面我们据需要根据上面的数据源 进行 对数据的分类汇总 和 计数 以及修改列名。

第一步,修改列名:

为了生成的数据更好理解 所以需要修改列名 。把数据源列名的 订单状态 修改为 订单数量购买数量 修改为 购买件数

代码如下:

rd_merge = pd.read_excel(r"D:\111\merge.xlsx") #读取刚刚合并好的excel
changer =rd_merge.rename(columns={'订单状态':'订单数量','购买数量':'购买件数'}) #修改列名

第二步,赛选出不要的数值,这里是在excel 中 订单状态 那列除了“已自提”,“待备货”,“待自提” 且 商品总金额  大于0。

这里主要去除掉不要的数据,方便在生成的时候,不占用太多资源。

代码如下:

df = pd.DataFrame(changer) #讲上面改变列名的表格转成 DF
title = ['已自提','待备货','待自提'] #定义一个列表,就是需要选中的列的值
sel_excel = df[(df['订单数量'].isin(title)) & (df["商品金额总计"]>1)] #进行赛选

第三步,按条件进行分类汇总(按条件进行求和),进行按条件进行计数。

这里主要根据自己工作业务逻辑去做求和 和 计数 等计算方式

代码如下:

province = sel_excel.groupby(['大区']).agg({'订单数量':'count','商品金额总计':'sum','购买件数':'sum'})
to_city = sel_excel.groupby(['小区']).agg({'订单数量':'count','商品金额总计':'sum','购买件数':'sum'})
shop = sel_excel.groupby(['门店']).agg({'订单数量':'count','商品金额总计':'sum','购买件数':'sum'})

上述代码解释,以一行代码为例解释,

province = sel_excel.groupby(['大区']).agg({'订单数量':'count','商品金额总计':'sum','购买件数':'sum'})

按照上面“进行赛选” 后得出的数据进行 分组统计,也就是 groupby函数,然后集合agg函数,网上说agg 功能主要是提供基于列的聚合操作。在 groupby()中的值 就是要计算的条件。而后面的出 在订单数量【前名字为订单状态】为计数,商品金额总计为求和,购买件数 为 求和 都是计算方式。

第四步,新建表格,将上面 大区,小区,门店,写入到excel 不同sheet中。

代码如下:

writer = pd.ExcelWriter(r"D:\111\m2.xlsx") #新建 m2.xlsx 表
sheet1 = pd.DataFrame(province) #进行转成DF格式
sheet2 = pd.DataFrame(to_city) #进行转成DF格式
sheet3 = pd.DataFrame(shop) #进行转成DF格式

sheet1.to_excel(writer,sheet_name='大区') #写入到m2.xlsx 的名叫 大区的sheet中
sheet2.to_excel(writer,sheet_name='小区') #写入到m2.xlsx 的名叫 小区的sheet中
sheet3.to_excel(writer,sheet_name='门店') #写入到m2.xlsx 的名叫 门店的sheet中
writer.save() #保存
writer.close() #关闭excel

最后代码执行后效果如下:

下方是sheet的截图

下方是门店sheet中的数据:

 

门店订单数量商品金额总计购买件数
万州五桥上海大道3873
万州新城一店4964
万州新城二店21162
三台老西街2582
三水湾店52495
上虞青春店41924
东大街店3773
东长街店1043111
中江上南街店2382
临沂义堂店31453

下方是小区sheet中的数据:

小区订单数量商品金额总计购买件数
安徽一区1850818
安徽二区1966919
安徽四区2886628
山东一区54212556
山东二区40187046
江苏一区55191856
江苏三区29121532
浙北大区2496925
浙西一区61826
浙西二区1044810
温州大区1793917

下方是大区sheet中的数据:

大区订单数量商品金额总计购买件数
北方大区2439171255
南方大区57253858
成都大区39915550411
湖南大区1123848116
贵州大区1047487115
重庆大区1244419126

分享就到这里了,可能文字描述逻辑有些不对,还请原谅。

别忘记,如果对你带来启发和灵感,给我点个关注呗。

 

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页

打赏作者

技术宅的宋哈哈

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值