Excel 表格处理技巧（一）

不再私藏，满满的干货，希望通过分享能解决伙伴们的 Excel 处理问题

一、关联:Vlookup (merage)
vlookup 是 excel 几乎最常用的公式，一般用于两个表的关联查询等。
如若需求如下：

工号，部门两列列存在于df2的表格中，所以想知道df1的每一工号对应的部门是多少。
用excel的话首先确认工号是唯一值，然后在df1新增一列写：=vlookup(a2,df2!a:h,6,0) 

然而用用 python 是如何实现的呢？
python 中我们实现的方法，代码如下：

df=pd.merge(df1,df2,on="订单明细号",how="left")

在这里引申一点

df1["订单号"].duplicated().value_counts()
此处 duplicated() 起到到去重的作用

二、数据透视表（好处谁用谁知道！！！）

需求：想知道每个营业点的收入总和

pd.pivot_table(f,index=[‘商户名’],values=[‘交易金额’,‘商户手续费’,’入账金额 ’],aggfunc=[np.sum])
可参考这个贴：
http://support.i-search.com.cn/article/1557823880420

引申：

1、index：
   索引列；
   
2、values:
    显示的列
    
3、aggfunc=[np.sum]
    对values数据求和，aggfunc={"Quantity":len,"Price":np.sum}，可为字典形式，对对应的列应用不同的方法；
    sum：求和
    mean：求平均
    len：求长度
    
4、margins=True
    在行位进行合计
    
5、fill_value=0
    将非数值NaN以‘0’填充；
    
6、columns=["Product"]
    索引列出现在表头
            
7、数据显示不全，解决方案：
pd.set_option('display.max_rows', None)         # 行数不做限制
# pd.set_option('display.max_colwidth', 15)   针对列宽度

三、对比两列差异
代码如下：

result=sale.loc[sale["订单号"].isin(sale["订单号2"])==False]

会将两列有差异的数据显示出来。
引申：

我们在处理表格比对的时候，是否可以先用merage连接两表，在进行两列比对。
（好处，若数据量较大的时候，节省了数据循环比对）

四、去除重复值（相信很容易理解）

代码如下：

df.drop_duplicates("商户号",inplace=True)

五、缺失值处理
#用 0 填充缺失值
代码如下：

  df["交易金额"]=df["交易金额"].fillna(0)

引申：
#删除有客户编码缺失值的行
代码如下：

df.dropna(subset=["客户编码"])

六、多条件筛选
需求：想知道商户编号为 xxxxxx，在上海 01 区域卖的商品交易金额大于 5000 的信息。

代码如下：

df.loc[(df["商户名"]=="上海01")&(df["商户编号"]=="xxxxxx")&(df["交易金额"]>5000)]

七、 模糊筛选数据

需求: 筛选商户名含有 "上海" 或则含有 "安徽" 的信息。
代码如下：

df.loc[sale["商户名"].str.contains("上海|安徽")]

八、分类汇总（groupby）

需求: 上海区域营业部的交易总额。

代码如下：

df.groupby(["地区名称","营业部名称"])["交易额"].sum()

十、删除数据间的空格（数据处理的时候可能会遇到该列数据中含有空格及制表符等问题）

需求：删除商户编号两边的空格。
代码如下：

sale["商户编号"].map(lambda s :s.strip(""))

十一、数据分列（相信处理 Excel 的时候遇到该单元格中同时含有日期与时间的问题）
需求：将日期与时间分列。
代码如下：

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

！！！补充技巧：

有的时候我们可以先写一份模板文件，每次用的时候，可以复制到指定路径，并改名为指定的文件名。（好处，格式化，表头更为美观，列的宽度可以设定，文本的格式可以设定）当然可以包括，我模板中对某些列设定vlookup函数。当然我们也可以通过设计器中组件单元格写入，写入vlookup函数。

这 11 点，关于处理 Excel 的方法，你 get 到了吗？
欢迎补充！！！