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

自從學瞭Python後就逼迫用Python來處理Excel,所有操作用Python實現。目的是鞏固Python,與增強數據處理能力。

這也是我寫這篇文章的初衷。廢話不說瞭,直接進入正題。

數據是網上找到的銷售數據,長這樣:

一、關聯公式: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)]

七、 模糊篩選數據

需求:篩選存貨名稱含有"三星"或則含有"索尼"的信息。

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要判斷格式,若非數值型數據直接報錯。)

總結一下就是:無論用哪個工具,能解決問題就是好數據分析師!

以上就是14個Python處理Excel的常用操作分享的詳細內容,更多關於Python處理Excel的資料請關註WalkonNet其它相關文章!

推薦閱讀: