Python和Excel的完美結(jié)合:常用操作匯總(案例詳析)
在以前，商業(yè)分析對(duì)應(yīng)的英文單詞是Business Analysis，大家用的分析工具是Excel，后來(lái)數(shù)據(jù)量大了，Excel應(yīng)付不過(guò)來(lái)了（Excel最大支持行數(shù)為1048576行），人們開始轉(zhuǎn)向python和R這樣的分析工具了，這時(shí)候商業(yè)分析對(duì)應(yīng)的單詞是Business Analytics。
其實(shí)python和Excel的使用準(zhǔn)則一樣，都是[We don't repeat ourselves]，都是盡可能用更方便的操作替代機(jī)械操作和純體力勞動(dòng)。
用python做數(shù)據(jù)分析，離不開著名的pandas包，經(jīng)過(guò)了很多版本的迭代優(yōu)化，pandas現(xiàn)在的生態(tài)圈已經(jīng)相當(dāng)完整了，官網(wǎng)還給出了它和其他分析工具的對(duì)比：
本文用的主要也是pandas，繪圖用的庫(kù)是plotly，實(shí)現(xiàn)的Excel的常用功能有：
Python和Excel的交互
vlookup函數(shù)
數(shù)據(jù)透視表
繪圖
以后如果發(fā)掘了更多Excel的功能，會(huì)回來(lái)繼續(xù)更新和補(bǔ)充。開始之前，首先按照慣例加載pandas包：
import numpy as npimport pandas as pdpd.set_option('max_columns', 10)pd.set_option('max_rows', 20)pd.set_option('display.float_format', lambda x: '%.2f' % x) # 禁用科學(xué)計(jì)數(shù)法
pandas里最常用的和Excel I/O有關(guān)的四個(gè)函數(shù)是read_csv/ read_excel/ to_csv/ to_excel，它們都有特定的參數(shù)設(shè)置，可以定制想要的讀取和導(dǎo)出效果。
比如說(shuō)想要讀取這樣一張表的左上部分：
可以用pd.read_excel("test.xlsx", header=1, nrows=17, usecols=3)，返回結(jié)果：
df:姓名 性別 部門0 A0001 張偉 男 工程1 A0002 王秀英 女 人事2 A0003 王芳 女 行政3 A0004 鄭勇 男 市場(chǎng)4 A0005 張麗 女 研發(fā)5 A0006 王艷 女 后勤6 A0007 李勇 男 市場(chǎng)7 A0008 李娟 女 工程8 A0009 張靜 女 人事9 A0010 王磊 男 行政10 A0011 李娜 女 市場(chǎng)11 A0012 劉詩(shī)雯 女 研發(fā)12 A0013 王剛 男 后勤13 A0014 葉倩 女 后勤14 A0015 金雯雯 女 市場(chǎng)15 A0016 王超杰 男 工程16 A0017 李軍 男 人事
輸出函數(shù)也同理，使用多少列，要不要index，標(biāo)題怎么放，都可以控制。
vlookup號(hào)稱是Excel里的神器之一，用途很廣泛，下面的例子來(lái)自豆瓣，VLOOKUP函數(shù)最常用的10種用法，你會(huì)幾種?
案例一
問題：A3:B7單元格區(qū)域?yàn)樽帜傅燃?jí)查詢表，表示60分以下為E級(jí)、60~69分為D級(jí)、70~79分為C級(jí)、80~89分為B級(jí)、90分以上為A級(jí)。D:G列為初二年級(jí)1班語(yǔ)文測(cè)驗(yàn)成績(jī)表，如何根據(jù)語(yǔ)文成績(jī)返回其字母等級(jí)？
方法：在H3:H13單元格區(qū)域中輸入=VLOOKUP（G3, $A$3:$B$7, 2）
python實(shí)現(xiàn)：
df = pd.read_excel("test.xlsx", sheet_name=0)def grade_to_point(x):if x >= 90:return 'A'elif x >= 80:return 'B'elif x >= 70:return 'C'elif x >= 60:return 'D'else:return 'E'= df['語(yǔ)文'].apply(grade_to_point)df:姓名 性別 語(yǔ)文 等級(jí)0 101 王小麗 女 69 D1 102 王寶勤 男 85 B2 103 楊玉萍 女 49 E3 104 田東會(huì) 女 90 A4 105 陳雪蛟 女 73 C5 106 楊建豐 男 42 E6 107 黎梅佳 女 79 C7 108 張興 男 91 A8 109 馬進(jìn)春 女 48 E9 110 魏改娟 女 100 A10 111 王冰研 女 64 D
案例二
問題：在Sheet1里面如何查找折舊明細(xì)表中對(duì)應(yīng)編號(hào)下的月折舊額？（跨表查詢）
方法：在Sheet1里面的C2:C4單元格輸入 =VLOOKUP(A2, 折舊明細(xì)表!A$2:$G$12, 7, 0)
python實(shí)現(xiàn)：使用merge將兩個(gè)表按照編號(hào)連接起來(lái)就行
df1 = pd.read_excel("test.xlsx", sheet_name='折舊明細(xì)表')df2 = pd.read_excel("test.xlsx", sheet_name=1) #題目里的sheet1df2.merge(df1[['編號(hào)', '月折舊額']], how='left', on='編號(hào)')Out[]:編號(hào) 資產(chǎn)名稱 月折舊額0 YT001 電動(dòng)門 13991 YT005 桑塔納轎車 11472 YT008 打印機(jī) 51
案例三
問題：類似于案例二，但此時(shí)需要使用近似查找
方法：在B2:B7區(qū)域中輸入公式=VLOOKUP(A2&"*", 折舊明細(xì)表!$B$2:$G$12, 6, 0)
python實(shí)現(xiàn)：這個(gè)比起上一個(gè)要麻煩一些，需要用到一些pandas的使用技巧
df1 = pd.read_excel("test.xlsx", sheet_name='折舊明細(xì)表')df3 = pd.read_excel("test.xlsx", sheet_name=3) #含有資產(chǎn)名稱簡(jiǎn)寫的表df3['月折舊額'] = 0for i in range(len(df3['資產(chǎn)名稱'])):df3['月折舊額'][i] = df1[df1['資產(chǎn)名稱'].map(lambda x:df3['資產(chǎn)名稱'][i] in x)]['月折舊額']df3Out[]:資產(chǎn)名稱 月折舊額0 電動(dòng) 13991 貨車 24382 惠普 1323 交聯(lián) 101334 桑塔納 11475 春蘭 230
案例四
問題：在Excel中錄入數(shù)據(jù)信息時(shí)，為了提高工作效率，用戶希望通過(guò)輸入數(shù)據(jù)的關(guān)鍵字后，自動(dòng)顯示該記錄的其余信息，例如，輸入員工工號(hào)自動(dòng)顯示該員工的姓名，輸入物料號(hào)就能自動(dòng)顯示該物料的品名、單價(jià)等。
如圖所示為某單位所有員工基本信息的數(shù)據(jù)源表，在“2010年3月員工請(qǐng)假統(tǒng)計(jì)表”工作表中，當(dāng)在A列輸入員工工號(hào)時(shí)，如何實(shí)現(xiàn)對(duì)應(yīng)員工的姓名、身份證號(hào)、部門、職務(wù)、入職日期等信息的自動(dòng)錄入？
方法：使用VLOOKUP+MATCH函數(shù)，在“2010年3月員工請(qǐng)假統(tǒng)計(jì)表”工作表中選擇B3:F8單元格區(qū)域，輸入下列公式=IF($A3="","",VLOOKUP($A3,員工基本信息!$A:$H,MATCH(B$2,員工基本信息!$2:$2,0),0))，按下【Ctrl+Enter】組合鍵結(jié)束。
python實(shí)現(xiàn)：上面的Excel的方法用得很靈活，但是pandas的想法和操作更簡(jiǎn)單方便些
df4 = pd.read_excel("test.xlsx", sheet_name='員工基本信息表')df5 = pd.read_excel("test.xlsx", sheet_name='請(qǐng)假統(tǒng)計(jì)表')df5.merge(df4[['工號(hào)', '姓名', '部門', '職務(wù)', '入職日期']], on='工號(hào)')Out[]:工號(hào) 姓名 部門 職務(wù) 入職日期0 A0004 龔夢(mèng)娟 后勤 主管 2006-11-201 A0003 趙敏 行政 文員 2007-02-162 A0005 黃凌 研發(fā) 工程師 2009-01-143 A0007 王維 人事 經(jīng)理 2006-07-244 A0016 張君寶 市場(chǎng) 工程師 2007-08-145 A0017 秦羽 人事 副經(jīng)理 2008-03-06
案例五
問題：用VLOOKUP函數(shù)實(shí)現(xiàn)批量查找，VLOOKUP函數(shù)一般情況下只能查找一個(gè)，那么多項(xiàng)應(yīng)該怎么查找呢？如下圖，如何把張一的消費(fèi)額全部列出？
方法：在C9：C11單元格里面輸入公式=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6)),B$9),$C$2:$C$6),2,)，按SHIFT+CTRL+ENTER鍵結(jié)束。
python實(shí)現(xiàn)：vlookup函數(shù)有兩個(gè)不足（或者算是特點(diǎn)吧），一個(gè)是被查找的值一定要在區(qū)域里的第一列，另一個(gè)是只能查找一個(gè)值，剩余的即便能匹配也不去查找了，這兩點(diǎn)都能通過(guò)靈活應(yīng)用if和indirect函數(shù)來(lái)解決，不過(guò)pandas能做得更直白一些。
df6 = pd.read_excel("test.xlsx", sheet_name='消費(fèi)額')df6[df6['姓名'] == '張一'][['姓名', '消費(fèi)額']]Out[]:姓名 消費(fèi)額0 張一 1002 張一 3004 張一 1000
數(shù)據(jù)透視表是Excel的另一個(gè)神器，本質(zhì)上是一系列的表格重組整合的過(guò)程。這里用的案例來(lái)自知乎，Excel數(shù)據(jù)透視表有什么用途：（https://www.zhihu.com/question/22484899/answer/39933218 ）
問題：需要匯總各個(gè)區(qū)域，每個(gè)月的銷售額與成本總計(jì)，并同時(shí)算出利潤(rùn)
通過(guò)Excel的數(shù)據(jù)透視表的操作最終實(shí)現(xiàn)了下面這樣的效果：
python實(shí)現(xiàn)：對(duì)于這樣的分組的任務(wù)，首先想到的就是pandas的groupby，代碼寫起來(lái)也簡(jiǎn)單，思路就是把剛才Excel的點(diǎn)鼠標(biāo)的操作反映到代碼命令上：
df = pd.read_excel('test.xlsx', sheet_name='銷售統(tǒng)計(jì)表')df['訂購(gòu)月份'] = df['訂購(gòu)日期'].apply(lambda x:x.month)df2 = df.groupby(['訂購(gòu)月份', '所屬區(qū)域'])[['銷售額', '成本']].agg('sum')df2['利潤(rùn)'] = df2['銷售額'] - df2['成本']df2Out[]:銷售額 成本 利潤(rùn)訂購(gòu)月份 所屬區(qū)域1 南京 134313.61 94967.84 39345.77常熟 177531.47 163220.07 14311.40無(wú)錫 316418.09 231822.28 84595.81昆山 159183.35 145403.32 13780.03蘇州 287253.99 238812.03 48441.962 南京 187129.13 138530.42 48598.71常熟 154442.74 126834.37 27608.37無(wú)錫 464012.20 376134.98 87877.22昆山 102324.46 86244.52 16079.94蘇州 105940.34 91419.54 14520.80... ... ...11 南京 286329.88 221687.11 64642.77常熟 2118503.54 1840868.53 277635.01無(wú)錫 633915.41 536866.77 97048.64昆山 351023.24 342420.18 8603.06蘇州 1269351.39 1144809.83 124541.5612 南京 894522.06 808959.32 85562.74常熟 324454.49 262918.81 61535.68無(wú)錫 1040127.19 856816.72 183310.48昆山 1096212.75 951652.87 144559.87蘇州 347939.30 302154.25 45785.05[]
也可以使用pandas里的pivot_table函數(shù)來(lái)實(shí)現(xiàn)：
df3 = pd.pivot_table(df, values=['銷售額', '成本'], index=['訂購(gòu)月份', '所屬區(qū)域'] , aggfunc='sum')df3['利潤(rùn)'] = df3['銷售額'] - df3['成本']df3Out[]:成本 銷售額 利潤(rùn)訂購(gòu)月份 所屬區(qū)域1 南京 94967.84 134313.61 39345.77常熟 163220.07 177531.47 14311.40無(wú)錫 231822.28 316418.09 84595.81昆山 145403.32 159183.35 13780.03蘇州 238812.03 287253.99 48441.962 南京 138530.42 187129.13 48598.71常熟 126834.37 154442.74 27608.37無(wú)錫 376134.98 464012.20 87877.22昆山 86244.52 102324.46 16079.94蘇州 91419.54 105940.34 14520.80... ... ...11 南京 221687.11 286329.88 64642.77常熟 1840868.53 2118503.54 277635.01無(wú)錫 536866.77 633915.41 97048.64昆山 342420.18 351023.24 8603.06蘇州 1144809.83 1269351.39 124541.5612 南京 808959.32 894522.06 85562.74常熟 262918.81 324454.49 61535.68無(wú)錫 856816.72 1040127.19 183310.48昆山 951652.87 1096212.75 144559.87蘇州 302154.25 347939.30 45785.05[60 rows x 3 columns]
pandas的pivot_table的參數(shù)index/ columns/ values和Excel里的參數(shù)是對(duì)應(yīng)上的（當(dāng)然，我這話說(shuō)了等于沒說(shuō)，數(shù)據(jù)透視表里不就是行/列/值嗎還能有啥。）
但是我個(gè)人還是更喜歡用groupby，因?yàn)樗\(yùn)算速度非常快。我在打kaggle比賽的時(shí)候，有一張表是貸款人的行為信息，大概有2700萬(wàn)行，用groupby算了幾個(gè)聚合函數(shù)，幾秒鐘就完成了。
groupby的功能很全面，內(nèi)置了很多aggregate函數(shù)，能夠滿足大部分的基本需求，如果你需要一些其他的函數(shù)，可以搭配使用apply和lambda。
不過(guò)pandas的官方文檔說(shuō)了，groupby之后用apply速度非常慢，aggregate內(nèi)部做過(guò)優(yōu)化，所以很快，apply是沒有優(yōu)化的，所以建議有問題先想想別的方法，實(shí)在不行的時(shí)候再用apply。
我打比賽的時(shí)候，為了生成一個(gè)新變量，用了groupby的apply，寫了這么一句：ins['weight'] = ins[['SK_ID_PREV'， 'DAYS_ENTRY_PAYMENT']].groupby('SK_ID_PREV').apply(lambda x: 1-abs(x)/x.sum().abs()).iloc[:,1]，1000萬(wàn)行的數(shù)據(jù)，足足算了十多分鐘，等得我心力交瘁。
因?yàn)镋xcel畫出來(lái)的圖能夠交互，能夠在圖上進(jìn)行一些簡(jiǎn)單操作，所以這里用的python的可視化庫(kù)是plotly，案例就用我這個(gè)學(xué)期發(fā)展經(jīng)濟(jì)學(xué)課上的作業(yè)吧，當(dāng)時(shí)的圖都是用Excel畫的，現(xiàn)在用python再畫一遍。開始之前，首先加載plotly包。
import plotly.offline as offimport plotly.graph_objs as gooff.init_notebook_mode()
柱狀圖
當(dāng)時(shí)用Excel畫了很多的柱狀圖，其中的一幅圖是
下面用plotly來(lái)畫一下
df = pd.read_excel("plot.xlsx", sheet_name='高等教育入學(xué)率')trace1 = go.Bar(x=df['國(guó)家'],y=df[1995],name='1995',opacity=0.6,marker=dict(color='powderblue'))trace2 = go.Bar(x=df['國(guó)家'],y=df[2005],name='2005',opacity=0.6,marker=dict(color='aliceblue',))trace3 = go.Bar(x=df['國(guó)家'],y=df[2014],name='2014',opacity=0.6,marker=dict(color='royalblue'))layout = go.Layout(barmode='group')data = [trace1, trace2, trace3]fig = go.Figure(data, layout)off.plot(fig)
雷達(dá)圖
用Excel畫的：
用python畫的：
df = pd.read_excel('plot.xlsx', sheet_name='政治治理')theta = df.columns.tolist()theta.append(theta[0])names = df.index= df.iloc[:,0]df = np.array(df)trace1 = go.Scatterpolar(r=df[0],theta=theta,name=names[0])trace2 = go.Scatterpolar(r=df[1],theta=theta,name=names[1])trace3 = go.Scatterpolar(r=df[2],theta=theta,name=names[2])trace4 = go.Scatterpolar(r=df[3],theta=theta,name=names[3])data = [trace1, trace2, trace3, trace4]layout = go.Layout(polar=dict(radialaxis=dict(visible=True,range=[0,1])),showlegend=True)fig = go.Figure(data, layout)off.plot(fig)
畫起來(lái)比Excel要麻煩得多。
總體而言，如果畫簡(jiǎn)單基本的圖形，用Excel是最方便的，如果要畫高級(jí)一些的或者是需要更多定制化的圖形，使用python更合適。