用Python處理Excel的14個常用操作

大家好，我是菜鳥哥！

數據是網路上找到的銷售數據，長這樣：

#一、關聯公式:Vlookup

vlookup是excel幾乎最常用的公式，一般用於兩個表的關聯查詢等。所以我先把這張表分成兩個表。

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

需求：想知道df1的每一個訂單對應的利潤是多少。

利潤一列存在於df2的表格中，所以想知道df1的每一個訂單對應的利潤是多少。用excel的話先確認訂單明細號是唯一值，然後在df1新增一列寫：=vlookup(a2,df2!a:h,6,0) ，然後往下拉就ok了。 （剩下13個我就不寫excel啦）

那用python是如何實現的呢？

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

二、資料透視表

需求：想知道每個地區的業務員分別賺取的利潤總和與利潤平均數。

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

三、對比兩列差異

因為這表每列資料維度都不一樣，比較起來沒啥意義，所以我先做了個訂單明細號的差異再進行比較。

需求：比較訂單明細號與訂單明細號2的差異並顯示出來。

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

四、移除重複值

需求：移除業務員編碼的重複值

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

五、缺失值處理

先查看銷售資料哪幾列有缺失值。

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

需求：以0填滿缺失值或則刪除有客戶編碼缺失值的行。實際上缺失值處理的辦法是很複雜的，這裡只介紹簡單的處理方法，若是數值變量，最常用平均數或中位數或眾數處理，比較複雜的可以用隨機森林模型根據其他維度去預測結果填充。若是分類變量，根據業務邏輯去填充準確度比較高。例如這裡的需求填入顧客名稱缺失值：就可以依照存貨分類出現頻率最大的存貨所對應的顧客名稱去填入。

這裡我們用簡單的處理方法：用0填滿缺失值或則刪除有客戶編碼缺失值的行。

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

六、多條件篩選

需求：想知道業務員張愛，在北京區域賣的商品訂單金額大於6000的資訊。

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

七、 模糊篩選資料

需求:篩選存貨名稱含有"三星"或則含有"Sony"的資訊。

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

八、分類總和

需求：北京區域各業務員的利潤總額。

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

九、條件計算

需求：存貨名稱包含「三星字眼」且稅金高於1000的訂單有幾個？這些訂單的利潤總和和平均利潤是多少？ （或最小值，最大值，四分位數，標註差）

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

#十、刪除資料間的空格

#需求：刪除存貨名稱兩邊的空格。

sale["存貨名稱"].map(lambda s :s.strip(""))

十一、資料分列

#需求：將日期與時間分列。

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

十二、異常值替換

先用describe()函數簡單檢視資料有無異常值。

#可看到銷項稅有負數，一般不會有這種情況，視它為異常值。

sale.describe()

需求：用0取代異常值。

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

十三、分組

需求：根據利潤資料分佈把地區分組為："較差","中","較好","非常好"

首先，當然是查看利潤的資料分佈呀，這裡我們採用四分位數去判斷。

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

根據四分位數把地區總利潤為[-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)

十四、根據業務邏輯定義標籤

#需求：銷售利潤率（即利潤/訂單金額）大於30%的商品資訊並標記它為優質商品，小於5%為一般商品。

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

其實excel常用的操作還有很多，我就列舉了14個自己比較常用的，若還想實現哪些操作可以評論一起交流討論，另外我自身也知道我寫python不夠精簡，慣性使用loc。（其實query會比較精簡）。若大家對這幾個操作有更好的寫法請務必評論告知我，感謝！

最後想說說，我覺得最好不要拿excel和python做對比，去研究哪個好用，其實都是工具，excel作為最廣泛的數據處理工具，壟斷這麼多年必定在數據處理方便也是相當優秀的，有些操作確實python會比較簡單，但也有不少excel操作起來比python簡單的。

例如一個很簡單的操作：對各列求和並在最下一行顯示出來，excel就是對一列總一個sum()函數，然後往左一拉就解決，而python則要定義一個函數（因為python要判斷格式，若非數值型資料直接報錯。）

以上是用Python處理Excel的14個常用操作的詳細內容。更多資訊請關注PHP中文網其他相關文章！