前言
以前學(xué)習(xí) Python 的 pandas 包時(shí)，經(jīng)常到一些 excel 的論壇尋找實(shí)戰(zhàn)機(jī)會。接下來我會陸續(xù)把相關(guān)案例分享出來，還會把其中的技術(shù)要點(diǎn)做詳細(xì)的講解。
本文要點(diǎn)：
使用 xlwings ，如同 vba 一樣操作 excel
使用 pandas 快速做透視表
案例
今天的例子非常簡單，從一個(gè)表中讀取學(xué)生的數(shù)據(jù)，然后按班級匯總各個(gè)科目的成績。下圖，左為原始數(shù)據(jù)，右為示意結(jié)果：
導(dǎo)入包
本文所需的包，安裝命令如下:
pip install xlwings
pip install pandas
腳本中導(dǎo)入
讀取表格的數(shù)據(jù)
使用 xw.books[] 可以快速訪問當(dāng)前打開的工作簿。可以使用索引也可以使用名字。
同樣，通過 book.sheets[] 快速訪問工作表，可以使用索引也可以使用名字。
接下來讀取表格數(shù)據(jù)
通過 sheet.range(地址) 即可訪問單元格區(qū)域。
接著使用 current_region 快速得到整個(gè)表格數(shù)據(jù)。這里可以使用其他方式定位數(shù)據(jù)的大小。
options(pd.DataFrame) 是一個(gè)很關(guān)鍵的操作，我們希望把數(shù)據(jù)放入 pandas 的 DataFrame ，以便快速處理數(shù)據(jù)。然后通過 value 獲得。
我們來看看數(shù)據(jù)
現(xiàn)在姓名列變成了 index 。不過需求是不需要理會姓名，因此我們不處理。
[班級]列變成小數(shù)。其實(shí)是小數(shù)也不會影響結(jié)果。
數(shù)據(jù)透視
接下來就非常簡單，直接使用pandas做出透視表。
使用 pd.pivot_table ，即可快速生成透視表。
其中參數(shù) index ，則是結(jié)果左邊的行分類字段——[班級]。
參數(shù) margins ，表示生成一個(gè)匯總行。
參數(shù) margins_name ，則是匯總行的名字。
注意，其實(shí)還有一個(gè)參數(shù) aggfunc ，用于指定匯總方式，默認(rèn)是 mean。
如果你對excel的透視表比較熟悉就會馬上學(xué)會這些。
index 相當(dāng)于 excel 透視表的行區(qū)域。
values 相當(dāng)于 excel 透視表的值區(qū)域。
columns 相當(dāng)于 excel 透視表的字段區(qū)域。
放入 index 與 columns 的字段，一般是分類的字段，比如：班級，性別。
放入 values 的字段，一般是連續(xù)值，比如：分?jǐn)?shù)，銷售額。如果是類別的值，一般會用于統(tǒng)計(jì)個(gè)數(shù)。
上述3個(gè)參數(shù)都可以傳入列表，以表示處理多個(gè)字段。
但是，看一下結(jié)果，卻發(fā)現(xiàn)了一些問題：
列的順序與原數(shù)據(jù)不一樣了。
結(jié)果需要把匯總列放到最右邊。
下面是針對上述問題的解決方法
cols=df.columns[1:].tolist() 首先需要讀取原數(shù)據(jù)的字段(第一個(gè)字段是班級，因此通過切片 1: 從第2個(gè)字段開始)。
cols.append(cols.pop(0)) 把[匯總]移到列表的最后。
pv_df=pv_df[cols] 把透視表的字段調(diào)整為我們需要的順序。
pv_df.reset_index(inplace=True) 是為了把[班級]從 index 移動回來作為 column。
看看結(jié)果，非常完美
輸出結(jié)果
把 DataFrame 寫回 excel 是非常容易。比如： wrk.range('O11').value=pv_df
但是這會把其中的index也輸出到excel上。因此，我們可以分開兩步輸出。如下：
第一行代碼，首先輸出字段行。
第二行代碼，輸出值。
完整代碼
以下是完整的代碼：
與 Vba 的對比
本文的案例是從某個(gè)知名excel論壇中挑選的，我從中挑選了最簡短的vba解決方案。如下：
可以看到使用 vba 進(jìn)行統(tǒng)計(jì)，代碼很長，并且都不是給人看的。
如果原數(shù)據(jù)的字段順序有變化，這代碼立刻無效。并且代碼仍然可以跑出結(jié)果，只是錯誤結(jié)果而已。
如果需求有變化，比如：求出每個(gè)班級的 top 3的學(xué)生。很快就放棄了吧。
總結(jié)
如果需要從 excel 讀取數(shù)據(jù)進(jìn)行匯總處理，可以選用 xlwings + pandas(如果數(shù)據(jù)非常規(guī)范并且無需處理格式等，可以直接使用 pandas)。
pandas 中的 pivot_table 快速得到各種方式的分組匯總。
