14个Python处理Excel的常用操作，我先试过了，非常好用

田明海

自从学了Python后就逼迫用Python来处理Excel，所有操作用Python实现。目的是巩固Python，与增强数据处理能力。
这也是我写这篇文章的初衷。废话不说了，直接进入正题。
数据是网上找到的销售数据，长这样：

一、关联公式:Vlookup

vlookup是excel几乎最常用的公式，一般用于两个表的关联查询等。所以我先把这张表分为两个表。

1. df1=sale[['订单明细号','单据日期','地区名称', '业务员名称','客户分类', '存货编码', '客户名称', '业务员编码', '存货名称', '订单号',
2.        '客户编码', '部门名称', '部门编码']]
3. df2=sale[['订单明细号','存货分类', '税费', '不含税金额', '订单金额', '利润', '单价','数量']]

复制代码

 
需求：想知道df1的每一个订单对应的利润是多少。
利润一列存在于df2的表格中，所以想知道df1的每一个订单对应的利润是多少。用excel的话首先确认订单明细号是唯一值，然后在df1新增一列写：=vlookup(a2,df2!a:h,6,0) ，然后往下拉就ok了。（剩下13个我就不写excel啦）
那用python是如何实现的呢？

1. #查看订单明细号是否重复，结果是没。
2. df1["订单明细号"].duplicated().value_counts()
3. df2["订单明细号"].duplicated().value_counts()
5. df_c=pd.merge(df1,df2,on="订单明细号",how="left")
6. # 新的一年，必须卷死同学、同事，狠狠的提升一波自己的Python实力，这里我给大家准备了2023最新的Python学习资料、电子书、基础视频教程、实战案例，直接在这个抠裙 708525271 自取即可

复制代码

 
二、数据透视表

需求：想知道每个地区的业务员分别赚取的利润总和与利润平均数。

1. pd.pivot_table(sale,index="地区名称",columns="业务员名称",values="利润",aggfunc=[np.sum,np.mean])

复制代码

 
三、对比两列差异

因为这表每列数据维度都不一样，比较起来没啥意义，所以我先做了个订单明细号的差异再进行比较。
需求：比较订单明细号与订单明细号2的差异并显示出来。

1. sale["订单明细号2"]=sale["订单明细号"]
3. #在订单明细号2里前10个都+1.
4. sale["订单明细号2"][1:10]=sale["订单明细号2"][1:10]+1
6. #差异输出
7. result=sale.loc[sale["订单明细号"].isin(sale["订单明细号2"])==False]

复制代码

 
四、去除重复值

需求：去除业务员编码的重复值

1. sale.drop_duplicates("业务员编码",inplace=True)

复制代码

 
五、缺失值处理

先查看销售数据哪几列有缺失值。

1. #列的行数小于index的行数的说明有缺失值，这里客户名称329<335,说明有缺失值
2. sale.info()

复制代码

 
七、 模糊筛选数据

需求:筛选存货名称含有"三星"或则含有"索尼"的信息。

1. #用0填充缺失值
2. sale["客户名称"]=sale["客户名称"].fillna(0)
3. #删除有客户编码缺失值的行
4. sale.dropna(subset=["客户编码"])

复制代码

 
八、分类汇总

需求:北京区域各业务员的利润总额。

1. sale.loc[(sale["地区名称"]=="北京")&(sale["业务员名称"]=="张爱")&(sale["订单金额"]>5000)]

复制代码

 
九、条件计算

需求：存货名称含“三星字眼”并且税费高于1000的订单有几个？这些订单的利润总和和平均利润是多少？（或者最小值，最大值，四分位数，标注差）

1. sale.loc[sale["存货名称"].str.contains("三星|索尼")]

复制代码

 

十、删除数据间的空格

需求：删除存货名称两边的空格。

1. sale.groupby(["地区名称","业务员名称"])["利润"].sum()

复制代码

 
十一、数据分列

需求：将日期与时间分列。

1. sale.loc[sale["存货名称"].str.contains("三星")&(sale["税费"]>=1000)][["订单明细号","利润"]].describe()

复制代码

 
十二、异常值替换

首先用describe()函数简单查看一下数据有无异常值。

1. sale["存货名称"].map(lambda s :s.strip(""))

复制代码

 

需求：用0代替异常值。

1. sale=pd.merge(sale,pd.DataFrame(sale["单据日期"].str.split(" ",expand=True)),how="inner",left_index=True,right_index=True)

复制代码

 
十三、分组

需求：根据利润数据分布把地区分组为：“较差”,“中等”,“较好”,“非常好”
首先，当然是查看利润的数据分布呀，这里我们采用四分位数去判断。

1. #可看到销项税有负数，一般不会有这种情况，视它为异常值。
2. sale.describe()

复制代码

 

根据四分位数把地区总利润为[-9,7091]区间的分组为“较差”，(7091,10952]区间的分组为"中等" (10952,17656]分组为较好，(17656,37556]分组为非常好。

1. sale["订单金额"]=sale["订单金额"].replace(min(sale["订单金额"]),0)

复制代码

 
十四、根据业务逻辑定义标签

需求：销售利润率（即利润/订单金额）大于30%的商品信息并标记它为优质商品，小于5%为一般商品。
[code]sale.loc[(sale["利润"]/sale["订单金额"])>0.3,"label"]="优质商品"sale.loc[(sale["利润"]/sale["订单金额"])