欢迎关注 &＃xff0c;专注Python、数据分析、数据挖掘、好玩工具&＃xff01;
自从学了 Python 后就不逼迫自己学习 Excel&＃xff0c;所有操作都可以用 Python 实现。利用 Python&＃xff0c;它不仅可以让办公自动化&＃xff0c;而且可以大大增强了数据处理能力。
今天我将给大家分享如何用 Python 实现 excel 的14个常用操作。
废话不说了&＃xff0c;直接进入正题。
本文所需数据文末可以下载&＃xff1a;
数据是网上找到的销售数据&＃xff0c;长这样&＃xff1a;
一、关联公式:Vlookup
vlookup是excel几乎最常用的公式&＃xff0c;一般用于两个表的关联查询等。所以我先把这张表分为两个表。
df1&＃61;sale[[&＃39;订单明细号&＃39;,&＃39;单据日期&＃39;,&＃39;地区名称&＃39;, &＃39;业务员名称&＃39;,&＃39;客户分类&＃39;, &＃39;存货编码&＃39;, &＃39;客户名称&＃39;, &＃39;业务员编码&＃39;, &＃39;存货名称&＃39;, &＃39;订单号&＃39;,&＃39;客户编码&＃39;, &＃39;部门名称&＃39;, &＃39;部门编码&＃39;]]
df2&＃61;sale[[&＃39;订单明细号&＃39;,&＃39;存货分类&＃39;, &＃39;税费&＃39;, &＃39;不含税金额&＃39;, &＃39;订单金额&＃39;, &＃39;利润&＃39;, &＃39;单价&＃39;,&＃39;数量&＃39;]]
需求&＃xff1a;想知道df1的每一个订单对应的利润是多少。
利润一列存在于df2的表格中&＃xff0c;所以想知道df1的每一个订单对应的利润是多少。用excel的话首先确认订单明细号是唯一值&＃xff0c;然后在df1新增一列写&＃xff1a;&＃61;vlookup(a2,df2!a:h,6,0) &＃xff0c;然后往下拉就ok了。&＃xff08;剩下13个我就不写excel啦&＃xff09;
那用python是如何实现的呢&＃xff1f;
df1["订单明细号"].duplicated().value_counts()
df2["订单明细号"].duplicated().value_counts()df_c&＃61;pd.merge(df1,df2,on&＃61;"订单明细号",how&＃61;"left")
二、数据透视表
需求&＃xff1a;想知道每个地区的业务员分别赚取的利润总和与利润平均数。
pd.pivot_table(sale,index&＃61;"地区名称",columns&＃61;"业务员名称",values&＃61;"利润",aggfunc&＃61;[np.sum,np.mean])
三、对比两列差异
因为这表每列数据维度都不一样&＃xff0c;比较起来没啥意义&＃xff0c;所以我先做了个订单明细号的差异再进行比较。
需求&＃xff1a;比较订单明细号与订单明细号2的差异并显示出来。
sale["订单明细号2"]&＃61;sale["订单明细号"]
sale["订单明细号2"][1:10]&＃61;sale["订单明细号2"][1:10]&＃43;1
result&＃61;sale.loc[sale["订单明细号"].isin(sale["订单明细号2"])&＃61;&＃61;False]
四、去除重复值
需求&＃xff1a;去除业务员编码的重复值
sale.drop_duplicates("业务员编码",inplace&＃61;True)
五、缺失值处理
先查看销售数据哪几列有缺失值。
sale.info()
需求&＃xff1a;用0填充缺失值或则删除有客户编码缺失值的行。
实际上缺失值处理的办法是很复杂的&＃xff0c;这里介绍简单的处理方法&＃xff1a;
- 若是数值变量&＃xff0c;最常用平均数或中位数或众数处理&＃xff0c;比较复杂的可以用随机森林模型根据其他维度去预测结果填充。
- 若是分类变量&＃xff0c;根据业务逻辑去填充准确性比较高&＃xff0c;比如这里的需求填充客户名称缺失值&＃xff1a;就可以根据存货分类出现频率最大的存货所对应的客户名称去填充。
这里我们用简单的处理办法&＃xff1a;用0填充缺失值或则删除有客户编码缺失值的行。
sale["客户名称"]&＃61;sale["客户名称"].fillna(0)
sale.dropna(subset&＃61;["客户编码"])
六、多条件筛选
需求&＃xff1a;想知道业务员张爱&＃xff0c;在北京区域卖的商品订单金额大于6000的信息。
sale.loc[(sale["地区名称"]&＃61;&＃61;"北京")&(sale["业务员名称"]&＃61;&＃61;"张爱")&(sale["订单金额"]>5000)]
七、 模糊筛选数据
需求:筛选存货名称含有"三星"或则含有"索尼"的信息。
sale.loc[sale["存货名称"].str.contains("三星|索尼")]
八、分类汇总
需求:北京区域各业务员的利润总额。
sale.groupby(["地区名称","业务员名称"])["利润"].sum()
九、条件计算
需求&＃xff1a;存货名称含“三星字眼”并且税费高于1000的订单有几个&＃xff1f;这些订单的利润总和和平均利润是多少&＃xff1f;&＃xff08;或者最小值&＃xff0c;最大值&＃xff0c;四分位数&＃xff0c;标注差&＃xff09;
sale.loc[sale["存货名称"].str.contains("三星")&(sale["税费"]>&＃61;1000)][["订单明细号","利润"]].describe()
十、删除数据间的空格
需求&＃xff1a;删除存货名称两边的空格。
sale["存货名称"].map(lambda s :s.strip(""))
十一、数据分列
需求&＃xff1a;将日期与时间分列。
sale&＃61;pd.merge(sale,pd.DataFrame(sale["单据日期"].str.split(" ",expand&＃61;True)),how&＃61;"inner",left_index&＃61;True,right_index&＃61;True)
十二、异常值替换
首先用describe()函数简单查看一下数据有无异常值。
sale.describe()
需求&＃xff1a;用0代替异常值。
sale["订单金额"]&＃61;sale["订单金额"].replace(min(sale["订单金额"]),0)
十三、分组
需求&＃xff1a;根据利润数据分布把地区分组为&＃xff1a;“较差”,“中等”,“较好”,“非常好”
首先&＃xff0c;当然是查看利润的数据分布呀&＃xff0c;这里我们采用四分位数去判断。
sale.groupby("地区名称")["利润"].sum().describe()
根据四分位数把地区总利润为[-9,7091]区间的分组为“较差”&＃xff0c;(7091,10952]区间的分组为"中等"
(10952,17656]分组为较好&＃xff0c;(17656,37556]分组为非常好。
sale_area&＃61;pd.DataFrame(sale.groupby("地区名称")["利润"].sum()).reset_index()
bins&＃61;[-10,7091,10952,17656,37556]
groups&＃61;["较差","中等","较好","非常好"]
十四、根据业务逻辑定义标签
需求&＃xff1a;销售利润率&＃xff08;即利润/订单金额&＃xff09;大于30%的商品信息并标记它为优质商品&＃xff0c;小于5%为一般商品。
sale.loc[(sale["利润"]/sale["订单金额"])>0.3,"label"]&＃61;"优质商品"
sale.loc[(sale["利润"]/sale["订单金额"])<0.05,"label"]&＃61;"一般商品"
结论
我们知道 excel 是非常强大的&＃xff0c;但是面对大量的复杂重复操作&＃xff0c;Excel很难轻松应对。此时我们可以利用 Python 的强大数据处理和自动化方法帮助我们提升效率。
数据获取方式
我已把数据进行打包共享了&＃xff0c;获取方法如下&＃xff1a;
- 方法1、微信搜索公众号&＃xff1a;Python学习与数据挖掘&＃xff0c;后台回复&＃xff1a;销售数据
- 方法2、扫描二维码或者发送图片到微信识别&＃xff0c;后台回复&＃xff1a;销售数据
京公网安备 11010802041100号