Python处理Excel，学会这十四个方法，工作量减少大半！

r囧r小猫 2022-08-28 05:55 54阅读 0赞

现在Python横行的年代，财务、人事、行政等等岗位多少得学点Python，省事又不费脑！  
所有操作都用Python自动实现，加班？不存在的！

![在这里插入图片描述][47b0b8ccf8c84e2e90aa2e632bafc01f.png_pic_center]excel和python其实都是工具，不要也不用拿去做对比，研究哪个好用，excel作为最为全球广泛的数据处理工具，垄断多年，肯定在数据处理方面有自己的优点，Python只是令 一些庞大的，费时间的操作加速处理，方便工作嘛。

当然也有很多excel的操作比用Python自动处理更加简单方便。

比如：对各列求和并在最下一行显示出来，excel就是对一列总一个sum()函数，然后往左一拉就解决，而python则要定义一个函数，python要判断格式，若非数值型数据会直接。我就不一一举例了！

好了，我们开始正题。

在网上找的销售数据，差不多长这样。  
[销售数据][Link 1]  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_20_color_FFFFFF_t_70_g_se_x_16]**1、数据透视表**

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

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

兄弟们学习python，有时候不知道怎么学，从哪里开始学。掌握了基本的一些语法或者做了两个案例后，不知道下一步怎么走，不知道如何去学习更加高深的知识。  
那么对于这些大兄弟们，我准备了大量的免费视频教程，PDF电子书籍，以及视频源的源代码！  
还会有大佬解答！  
都在这个裙里了 [点我快速进入][Link 2]  
欢迎加入，一起讨论 一起学习！

**2、去除重复值**

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

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

**3、分类汇总**

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

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

**4、关联公式:Vlookup**

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

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

需求

想知道df1的每一个订单对应的利润是多少

利润一列存在于df2的表格中，所以想知道df1的每一个订单对应的利润是多少。

用excel的话首先确认订单明细号是唯一值，然后在df1新增一列写：=vlookup(a2,df2!a:h,6,0) ，然后往下拉就ok了。

那用python是如何实现的呢？

#查看订单明细号是否重复，结果是没。
    df1["订单明细号"].duplicated().value_counts()
    df2["订单明细号"].duplicated().value_counts()
    
    df_c=pd.merge(df1,df2,on="订单明细号",how="left")

**5、条件计算**

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

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

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_14_color_FFFFFF_t_70_g_se_x_16]**6、分组**

需求

根据利润数据分布把地区分组为：“较差”,“中等”,“较好”,“非常好”

首先，当然是查看利润的数据分布呀，这里我们采用四分位数去判断。

sale.groupby("地区名称")["利润"].sum().describe()

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_12_color_FFFFFF_t_70_g_se_x_16]根据四分位数把地区总利润为\[-9,7091\]区间的分组为“较差”，(7091,10952\]区间的分组为"中等"

(10952,17656\]分组为较好，(17656,37556\]分组为非常好。

#先建立一个Dataframe
    sale_area=pd.DataFrame(sale.groupby("地区名称")["利润"].sum()).reset_index()
    
    #设置bins,和分组名称
    bins=[-10,7091,10952,17656,37556]
    groups=["较差","中等","较好","非常好"]
    
    #使用cut分组
    #sale_area["分组"]=pd.cut(sale_area["利润"],bins,labels=groups)

**7、对比两列差异**

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

需求：比较订单明细号与订单明细号2的差异并显示出来。

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

**8、异常值替换**

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

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

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_20_color_FFFFFF_t_70_g_se_x_16 1]需求

用0代替异常值。

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

**9、缺失值处理**

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

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

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_19_color_FFFFFF_t_70_g_se_x_16]  
需求

用0填充缺失值或则删除有客户编码缺失值的行

实际上缺失值处理的办法是很复杂的，这里只介绍简单的处理方法，若是数值变量，最常用平均数或中位数或众数处理，比较复杂的可以用随机森林模型根据其他维度去预测结果填充。

若是分类变量，根据业务逻辑去填充准确性比较高。

比如这里的需求填充客户名称缺失值：就可以根据存货分类出现频率最大的存货所对应的客户名称去填充。

这里我们用简单的处理办法：用0填充缺失值或则删除有客户编码缺失值的行。

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

**10、数据分列**  
![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_20_color_FFFFFF_t_70_g_se_x_16 2]

需求

将日期与时间分列

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

**11、 模糊筛选数据**

需求

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

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

**12、删除数据间的空格**

需求

删除存货名称两边的空格

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

**13、根据业务逻辑定义标签**

需求  
销售利润率（即利润/订单金额）大于30%的商品信息并标记它为优质商品，小于5%为一般商品。

sale.loc[(sale["利润"]/sale["订单金额"])>0.3,"label"]="优质商品"
    sale.loc[(sale["利润"]/sale["订单金额"])<0.05,"label"]="一般商品"

**14、多条件筛选**

需求

想知道业务员张爱，在北京区域卖的商品订单金额大于6000的信息。

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

这里只是列举了一些比较常用的，但是excel常用的操作还有很多，如果还想实现哪些操作，大家可以在评论区一起交流。大家如果对这这些操作有更好的写法，也可以在评论区一起交流！感谢！  
![在这里插入图片描述][d7d8dea4d17f4657ae4ff73999dc8c5e.png]  
.

[47b0b8ccf8c84e2e90aa2e632bafc01f.png_pic_center]: /images/20220828/640ef93e5d124aa5af137d391e3054d4.png
[Link 1]: https://jq.qq.com/?_wv=1027&k=ABoZS3lA
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/45948a9e40034cf9895a66295a556a89.png
[Link 2]: https://note.youdao.com/s/H7iz0hfA
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_14_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/d0ffddc8613c41f2a44e6a54e38903d0.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_12_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/e5b69e4955304368b34816c64532167f.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA5Zeo5a2m57yW56iL,size_12,color_FFFFFF,t_70,g_se,x_16
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/5732a21f303545a5819fd718344af282.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/1501082cc1c64055af9db202ee4cd554.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zeo5a2m57yW56iL_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/04c43f01763d43c7aa51e7f1520922b0.png
[d7d8dea4d17f4657ae4ff73999dc8c5e.png]: /images/20220828/5823c77bc42440ddb2c091f96d5d88f0.png