用Python處理Excel的14個(gè)常用操作

大家好，我是菜鳥哥！
自從學(xué)了Python后就逼迫用Python來處理Excel，所有操作用Python實(shí)現(xiàn)。目的是鞏固Python，與增強(qiáng)數(shù)據(jù)處理能力。這也是我寫這篇文章的初衷。廢話不說了，直接進(jìn)入正題。
數(shù)據(jù)是網(wǎng)上找到的銷售數(shù)據(jù)，長(zhǎng)這樣：

vlookup是excel幾乎最常用的公式，一般用于兩個(gè)表的關(guān)聯(lián)查詢等。所以我先把這張表分為兩個(gè)表。

df1=sale[['訂單明細(xì)號(hào)','單據(jù)日期','地區(qū)名稱', '業(yè)務(wù)員名稱','客戶分類', '存貨編碼', '客戶名稱', '業(yè)務(wù)員編碼', '存貨名稱', '訂單號(hào)',
       '客戶編碼', '部門名稱', '部門編碼']]
df2=sale[['訂單明細(xì)號(hào)','存貨分類', '稅費(fèi)', '不含稅金額', '訂單金額', '利潤(rùn)', '單價(jià)','數(shù)量']]

需求：想知道df1的每一個(gè)訂單對(duì)應(yīng)的利潤(rùn)是多少。

利潤(rùn)一列存在于df2的表格中，所以想知道df1的每一個(gè)訂單對(duì)應(yīng)的利潤(rùn)是多少。用excel的話首先確認(rèn)訂單明細(xì)號(hào)是唯一值，然后在df1新增一列寫：=vlookup(a2,df2!a:h,6,0) ，然后往下拉就ok了。（剩下13個(gè)我就不寫excel啦）

那用python是如何實(shí)現(xiàn)的呢？

#查看訂單明細(xì)號(hào)是否重復(fù)，結(jié)果是沒。
df1["訂單明細(xì)號(hào)"].duplicated().value_counts()
df2["訂單明細(xì)號(hào)"].duplicated().value_counts()

df_c=pd.merge(df1,df2,on="訂單明細(xì)號(hào)",how="left")

需求：想知道每個(gè)地區(qū)的業(yè)務(wù)員分別賺取的利潤(rùn)總和與利潤(rùn)平均數(shù)。

pd.pivot_table(sale,index="地區(qū)名稱",columns="業(yè)務(wù)員名稱",values="利潤(rùn)",aggfunc=[np.sum,np.mean])

因?yàn)檫@表每列數(shù)據(jù)維度都不一樣，比較起來沒啥意義，所以我先做了個(gè)訂單明細(xì)號(hào)的差異再進(jìn)行比較。

需求：比較訂單明細(xì)號(hào)與訂單明細(xì)號(hào)2的差異并顯示出來。

sale["訂單明細(xì)號(hào)2"]=sale["訂單明細(xì)號(hào)"]

#在訂單明細(xì)號(hào)2里前10個(gè)都+1.
sale["訂單明細(xì)號(hào)2"][1:10]=sale["訂單明細(xì)號(hào)2"][1:10]+1

#差異輸出
result=sale.loc[sale["訂單明細(xì)號(hào)"].isin(sale["訂單明細(xì)號(hào)2"])==False]

需求：去除業(yè)務(wù)員編碼的重復(fù)值

sale.drop_duplicates("業(yè)務(wù)員編碼",inplace=True)

先查看銷售數(shù)據(jù)哪幾列有缺失值。

#列的行數(shù)小于index的行數(shù)的說明有缺失值，這里客戶名稱329<335,說明有缺失值
sale.info()

需求：用0填充缺失值或則刪除有客戶編碼缺失值的行。實(shí)際上缺失值處理的辦法是很復(fù)雜的，這里只介紹簡(jiǎn)單的處理方法，若是數(shù)值變量，最常用平均數(shù)或中位數(shù)或眾數(shù)處理，比較復(fù)雜的可以用隨機(jī)森林模型根據(jù)其他維度去預(yù)測(cè)結(jié)果填充。若是分類變量，根據(jù)業(yè)務(wù)邏輯去填充準(zhǔn)確性比較高。比如這里的需求填充客戶名稱缺失值：就可以根據(jù)存貨分類出現(xiàn)頻率最大的存貨所對(duì)應(yīng)的客戶名稱去填充。

這里我們用簡(jiǎn)單的處理辦法：用0填充缺失值或則刪除有客戶編碼缺失值的行。

#用0填充缺失值
sale["客戶名稱"]=sale["客戶名稱"].fillna(0)
#刪除有客戶編碼缺失值的行
sale.dropna(subset=["客戶編碼"])

需求：想知道業(yè)務(wù)員張愛，在北京區(qū)域賣的商品訂單金額大于6000的信息。

sale.loc[(sale["地區(qū)名稱"]=="北京")&(sale["業(yè)務(wù)員名稱"]=="張愛")&(sale["訂單金額"]>5000)]

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

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

需求:北京區(qū)域各業(yè)務(wù)員的利潤(rùn)總額。

sale.groupby(["地區(qū)名稱","業(yè)務(wù)員名稱"])["利潤(rùn)"].sum()

需求：存貨名稱含“三星字眼”并且稅費(fèi)高于1000的訂單有幾個(gè)？這些訂單的利潤(rùn)總和和平均利潤(rùn)是多少？（或者最小值，最大值，四分位數(shù)，標(biāo)注差）

sale.loc[sale["存貨名稱"].str.contains("三星")&(sale["稅費(fèi)"]>=1000)][["訂單明細(xì)號(hào)","利潤(rùn)"]].describe()

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

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

需求：將日期與時(shí)間分列。

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

首先用describe()函數(shù)簡(jiǎn)單查看一下數(shù)據(jù)有無(wú)異常值。

#可看到銷項(xiàng)稅有負(fù)數(shù)，一般不會(huì)有這種情況，視它為異常值。
sale.describe()

需求：用0代替異常值。

sale["訂單金額"]=sale["訂單金額"].replace(min(sale["訂單金額"]),0)

需求：根據(jù)利潤(rùn)數(shù)據(jù)分布把地區(qū)分組為："較差","中等","較好","非常好"

首先，當(dāng)然是查看利潤(rùn)的數(shù)據(jù)分布呀，這里我們采用四分位數(shù)去判斷。

sale.groupby("地區(qū)名稱")["利潤(rùn)"].sum().describe()

根據(jù)四分位數(shù)把地區(qū)總利潤(rùn)為[-9,7091]區(qū)間的分組為“較差”，(7091,10952]區(qū)間的分組為"中等" (10952,17656]分組為較好，(17656,37556]分組為非常好。

#先建立一個(gè)Dataframe
sale_area=pd.DataFrame(sale.groupby("地區(qū)名稱")["利潤(rùn)"].sum()).reset_index()

#設(shè)置bins,和分組名稱
bins=[-10,7091,10952,17656,37556]
groups=["較差","中等","較好","非常好"]

#使用cut分組
#sale_area["分組"]=pd.cut(sale_area["利潤(rùn)"],bins,labels=groups)

需求：銷售利潤(rùn)率（即利潤(rùn)/訂單金額）大于30%的商品信息并標(biāo)記它為優(yōu)質(zhì)商品，小于5%為一般商品。

sale.loc[(sale["利潤(rùn)"]/sale["訂單金額"])>0.3,"label"]="優(yōu)質(zhì)商品"
sale.loc[(sale["利潤(rùn)"]/sale["訂單金額"])<0.05,"label"]="一般商品"

其實(shí)excel常用的操作還有很多，我就列舉了14個(gè)自己比較常用的，若還想實(shí)現(xiàn)哪些操作可以評(píng)論一起交流討論，另外我自身也知道我寫python不夠精簡(jiǎn)，慣性使用loc。（其實(shí)query會(huì)比較精簡(jiǎn)）。若大家對(duì)這幾個(gè)操作有更好的寫法請(qǐng)務(wù)必評(píng)論告知我，感謝！

最后想說說，我覺得最好不要拿excel和python做對(duì)比，去研究哪個(gè)好用，其實(shí)都是工具，excel作為最為廣泛的數(shù)據(jù)處理工具，壟斷這么多年必定在數(shù)據(jù)處理方便也是相當(dāng)優(yōu)秀的，有些操作確實(shí)python會(huì)比較簡(jiǎn)單，但也有不少excel操作起來比python簡(jiǎn)單的。

比如一個(gè)很簡(jiǎn)單的操作：對(duì)各列求和并在最下一行顯示出來，excel就是對(duì)一列總一個(gè)sum()函數(shù)，然后往左一拉就解決，而python則要定義一個(gè)函數(shù)（因?yàn)閜ython要判斷格式，若非數(shù)值型數(shù)據(jù)直接報(bào)錯(cuò)。）

總結(jié)一下就是：無(wú)論用哪個(gè)工具，能解決問題就是好數(shù)據(jù)分析師！

轉(zhuǎn)自：菜J學(xué)Python

推薦閱讀:
入門: 最全的零基礎(chǔ)學(xué)Python的問題  | 零基礎(chǔ)學(xué)了8個(gè)月的Python  | 實(shí)戰(zhàn)項(xiàng)目 |學(xué)Python就是這條捷徑

干貨:爬取豆瓣短評(píng)，電影《后來的我們》 | 38年NBA最佳球員分析 |   從萬(wàn)眾期待到口碑撲街！唐探3令人失望  | 笑看新倚天屠龍記 | 燈謎答題王 ｜用Python做個(gè)海量小姐姐素描圖 ｜碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

趣味:彈球游戲  | 九宮格  | 漂亮的花 | 兩百行Python《天天酷跑》游戲!

AI: 會(huì)做詩(shī)的機(jī)器人 | 給圖片上色 | 預(yù)測(cè)收入 | 碟中諜這么火，我用機(jī)器學(xué)習(xí)做個(gè)迷你推薦系統(tǒng)電影

小工具: Pdf轉(zhuǎn)Word，輕松搞定表格和水??！ | 一鍵把html網(wǎng)頁(yè)保存為pdf！|  再見PDF提取收費(fèi)！ | 用90行代碼打造最強(qiáng)PDF轉(zhuǎn)換器，word、PPT、excel、markdown、html一鍵轉(zhuǎn)換 | 制作一款釘釘?shù)蛢r(jià)機(jī)票提示器！ ｜60行代碼做了一個(gè)語(yǔ)音壁紙切換器天天看小姐姐！｜


年度爆款文案
1).臥槽！Pdf轉(zhuǎn)Word用Python輕松搞定！
2).學(xué)Python真香！我用100行代碼做了個(gè)網(wǎng)站，幫人PS旅行圖片，賺個(gè)雞腿吃
3).首播過億，火爆全網(wǎng)，我分析了《乘風(fēng)破浪的姐姐》，發(fā)現(xiàn)了這些秘密 
4).80行代碼！用Python做一個(gè)哆來A夢(mèng)分身 
5).你必須掌握的20個(gè)python代碼，短小精悍，用處無(wú)窮 
6).30個(gè)Python奇淫技巧集 
7).我總結(jié)的80頁(yè)《菜鳥學(xué)Python精選干貨.pdf》,都是干貨 
8).再見Python！我要學(xué)Go了！2500字深度分析！
9).發(fā)現(xiàn)一個(gè)舔狗福利！這個(gè)Python爬蟲神器太爽了，自動(dòng)下載妹子圖片

點(diǎn)閱讀原文，看B站我的視頻！