實(shí)戰(zhàn)，Python處理Excel比Vba快100倍，媳婦連連夸贊今晚不用再跪搓衣板

背景

最近經(jīng)歷了一次把vb腳本改造成python腳本，并獲得性能提升數(shù)倍的過(guò)程，當(dāng)然，這個(gè)過(guò)程也不是一帆風(fēng)順，中間也經(jīng)歷了一些波折，但是，也收獲了一波新的認(rèn)知。正好最近有時(shí)間，姑且寫(xiě)下來(lái)記錄一下。

什么是VB

話說(shuō)現(xiàn)在的年輕人，聽(tīng)說(shuō)過(guò)這個(gè)編程語(yǔ)言的應(yīng)該不多了。VB是一種由微軟公司開(kāi)發(fā)的包含協(xié)助開(kāi)發(fā)環(huán)境的事件驅(qū)動(dòng)編程語(yǔ)言。從任何標(biāo)準(zhǔn)來(lái)說(shuō)，VB都是世界上使用人數(shù)最多的語(yǔ)言，它源自于BASIC編程語(yǔ)言，也屬于高級(jí)語(yǔ)言的一種了。只是現(xiàn)在各大應(yīng)用場(chǎng)景以及被Java、Go、Python等編程語(yǔ)言瓜分一空，VB基本很少人知道了。

什么是VBA

而VBA和VB又有點(diǎn)差別，Visual Basic for Applications(VBA)是Visual Basic的一種宏語(yǔ)言，是微軟開(kāi)發(fā)出來(lái)在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化(OLE)任務(wù)的編程語(yǔ)言。主要能用來(lái)擴(kuò)展Windows的應(yīng)用程式功能，特別是Microsoft Office軟件，比如excel、powerpoint、word等。

故事的開(kāi)端

而本次故事的場(chǎng)景，就是在excel中編寫(xiě)vba宏腳本，而這個(gè)場(chǎng)景的需求，則來(lái)源于筆者的媳婦。筆者的媳婦平時(shí)的工作大部分時(shí)間都是跟excel打交道，也就是很多人口中的“表姐”，因此excel的各種高級(jí)操作比如vlookup、數(shù)據(jù)透視等，也算是應(yīng)用的爐火純青了。

可偏偏事不如人愿，企業(yè)中的業(yè)務(wù)總是會(huì)越來(lái)越復(fù)雜，老板的要求也會(huì)越來(lái)越高，漸漸地，有一些需求我媳婦用她爐火純青的技巧也搞不動(dòng)了。于是她把希望寄托在了我這個(gè)廉價(jià)勞動(dòng)力身上，畢竟傳說(shuō)中的搞IT的，可是什么都能干的。

于是大概從幾年前，我開(kāi)始陸陸續(xù)續(xù)用vba寫(xiě)宏，幫助媳婦處理類似復(fù)雜的數(shù)據(jù)計(jì)算問(wèn)題，說(shuō)到這里，我翻了翻我的朋友圈，竟然有據(jù)可查：2017年就開(kāi)始了！有圖為證：

也就是從那個(gè)時(shí)候開(kāi)始，媳婦搞不定的復(fù)雜數(shù)據(jù)處理問(wèn)題，就扔給我用vba來(lái)搞。要知道，對(duì)于一個(gè)寫(xiě)慣Java語(yǔ)言的人來(lái)說(shuō)，對(duì)vba這種語(yǔ)言真的是一百種不習(xí)慣，尤其是那個(gè)土得掉渣的開(kāi)發(fā)環(huán)境，話不多說(shuō)，上圖：

有沒(méi)有一種年代復(fù)古風(fēng)的感覺(jué)！這還是最新版本的，老版本的連調(diào)試功能都沒(méi)有，任何問(wèn)題都得默念加各種打日志排查，更不用說(shuō)高級(jí)點(diǎn)IDE都具備的自動(dòng)補(bǔ)全、提示、重構(gòu)等功能了，所以，用這個(gè)玩意寫(xiě)代碼的效率那真是一言難盡。

就這樣被媳婦的需求折磨了幾年，好在這幾年的需求也沒(méi)復(fù)雜到哪里去，一路也就忍過(guò)來(lái)了?？勺罱淮蜗眿D扔過(guò)來(lái)的需求，可著實(shí)把我可累了一把。

詳細(xì)的需求就不說(shuō)了，大概就是對(duì)一個(gè)excel的兩個(gè)sheet進(jìn)行計(jì)算，其中一個(gè)sheet將近1萬(wàn)行，兩外一個(gè)sheet數(shù)據(jù)量倒不多300多行，但是格式比較復(fù)雜，各種合并和拆分單元格（見(jiàn)下圖），而要計(jì)算的需求復(fù)雜度相比之前也上升了一個(gè)臺(tái)階。

拿到需求后，我還是按照慣例用vba來(lái)寫(xiě)，大概耗費(fèi)了一個(gè)周末的時(shí)間搞定了，雖然交了差。但是面對(duì)未來(lái)可能越來(lái)越復(fù)雜的需求，我的心里打了鼓，vba的開(kāi)發(fā)效率和復(fù)雜數(shù)據(jù)處理需求的矛盾越來(lái)越突出，而且這次寫(xiě)的腳本，性能上也問(wèn)題很大，整個(gè)處理過(guò)程耗時(shí)10分鐘之巨，如下圖所示：

作為一個(gè)自認(rèn)優(yōu)秀且有良心的搞IT的，怎么能夠忍受這種開(kāi)發(fā)效率和運(yùn)行效率，二話不說(shuō)，我要優(yōu)化它！

怎么優(yōu)化呢？話說(shuō)在大數(shù)據(jù)處理領(lǐng)域，Python可算是TIOBE排行榜上，數(shù)一數(shù)二的利器了，尤其是在AI大熱的背景下，Python在TIOBE排行榜上的地位是逐漸躥升，除了大數(shù)據(jù)領(lǐng)域，Python在web開(kāi)發(fā)、Excel辦公、科學(xué)計(jì)算和數(shù)據(jù)可視化等方面也表現(xiàn)優(yōu)秀。好了，就用Python搞！

Python優(yōu)化過(guò)程

大概的優(yōu)化思路是這樣的：用Python的xlwings庫(kù)來(lái)處理excel數(shù)據(jù)的讀寫(xiě)，但數(shù)據(jù)的計(jì)算就不用它直接搞了，效率會(huì)比較低，而是用Pandas庫(kù)在內(nèi)存中進(jìn)行數(shù)據(jù)的復(fù)雜計(jì)算，然后將計(jì)算后的結(jié)果寫(xiě)回excel

思路其實(shí)很簡(jiǎn)單，但實(shí)操的過(guò)程卻不是完全一帆風(fēng)順，接下來(lái)就是整個(gè)優(yōu)化的過(guò)程

第一版優(yōu)化

因?yàn)橛肞andas把數(shù)據(jù)讀到內(nèi)存后，是一個(gè)DataFrame，我們可以很容易的拿到這個(gè)DataFrame的行數(shù)和列數(shù)，類似一個(gè)數(shù)組一樣可以方便的遍歷，因此第一版的實(shí)現(xiàn)，使用的是標(biāo)準(zhǔn)的遍歷的方法來(lái)實(shí)現(xiàn)，核心代碼如下：

讀取excel

import pandas as pd
import xlwings as xw

#要處理的文件路徑
fpath = "datas/joyce/DS_format_bak.xlsm"

#把CP和DS兩個(gè)sheet的數(shù)據(jù)分別讀入pandas的dataframe
cp_df = pd.read_excel(fpath,sheet_name="CP",header=[0])
ds_df = pd.read_excel(fpath,sheet_name="DS",header=[0,1])
......

標(biāo)準(zhǔn)遍歷方法

for j in range(len(cp_df)):
    
    cp_measure = cp_df.loc[j,'Measure']
    cp_item_group = cp_df.loc[j,'Item Group']
    
    if cp_measure == "Total Publish Demand":
        
        for i in range(len(ds_df)):
            #如果cp和ds的item_group值相同
            if cp_item_group == ds_df.loc[i,('Total','Capabity')]:
            
......
                

寫(xiě)入excel

#保存結(jié)果到excel       
app = xw.App(visible=False,add_book=False)

ds_format_workbook = app.books.open(fpath)
ds_format_workbook.sheets["DS"].range("A3").expand().options(index=False).value = ds_df 

ds_format_workbook.save()
ds_format_workbook.close()
app.quit()
......

說(shuō)到這里插一句，大家還記得我前面提到的那個(gè)各種拆分和合并單元格的復(fù)雜格式嗎，這種格式在Pandas里又叫多層索引（MultiIndex）,這種結(jié)構(gòu)下數(shù)據(jù)的查詢和操作，比普通的表格要復(fù)雜，大概處理代碼類似下面：

#用元組的方式來(lái)定位某一列
ds_total_capabity1 = ds_df.loc[k,('Total','Capabity.1')]
#
#獲取多層索引某一層數(shù)據(jù)的方法
ds_month = ds_df.columns.get_level_values(0)[k]
ds_datatime = ds_df.columns.get_level_values(1)[k]
......

因?yàn)檫@個(gè)話題跟本文章無(wú)關(guān)，這里就不展開(kāi)了，有興趣大家自己去學(xué)習(xí)了解。

這一版寫(xiě)完后，信心滿滿地執(zhí)行腳本，但是立馬被現(xiàn)實(shí)澆了一盆冷水，執(zhí)行時(shí)間竟然要555秒，也就是9分多鐘，并沒(méi)有比vba快多少，如下圖：

為什么會(huì)這樣！Python不是號(hào)稱數(shù)據(jù)處理利器嗎。我們仔細(xì)看一下打印的日志輸出，可以看到主要的瓶頸在循環(huán)計(jì)算這塊，耗時(shí)469+42 = 517秒，基本所有時(shí)間都用在這里。當(dāng)然，從日志也可以看到，讀寫(xiě)excel的性能也一般，但并不是性能瓶頸。對(duì)于性能優(yōu)化的一般準(zhǔn)則是：數(shù)據(jù)驅(qū)動(dòng)+二八原則，也即通過(guò)數(shù)據(jù)分析發(fā)現(xiàn)瓶頸，即占用80%耗時(shí)的地方，然后有針對(duì)性地優(yōu)化該瓶頸。

內(nèi)存中的循環(huán)計(jì)算為什么這么慢呢？遇事不決問(wèn)度娘，通過(guò)一番搜索，終于讓我找到一個(gè)官方解釋，原來(lái)DataFrame(數(shù)據(jù)幀)是具有行和列的Pandas對(duì)象(objects)，如果使用循環(huán)，則將遍歷整個(gè)對(duì)象，Python無(wú)法利用任何內(nèi)置函數(shù)，而且速度非常慢，建議用Pandas內(nèi)置函數(shù)：iterrows()，iterrows()為每行返回一個(gè)Series，因此將DataFrame迭代為一對(duì)索引，將感興趣的列作為Series進(jìn)行迭代，這使其比標(biāo)準(zhǔn)循環(huán)更快。

既然官方這么說(shuō)，那我們還懷疑什么，那就試試唄。

第二版優(yōu)化

有了解決方案，那就好辦了，無(wú)非就是把代碼里所有用到標(biāo)準(zhǔn)循環(huán)的地方，改成用iterrows()，改動(dòng)的地方代碼如下：

#根據(jù)CP和DS表的Item_group值做lookup，計(jì)算DS表的Delta值
for index_i,cp_row in cp_df.iterrows():
    
    #獲取CP表的Item_group和siteid值
    cp_item_group = cp_row['Item Group']
    siteid = cp_row['SITEID']
    key = cp_item_group + "-" + siteid  
        
    for index_j,ds_row in ds_df.iterrows():
        
        #獲取DS表的Item_group值
        ds_item_group = ds_row[('Total','Capabity')]
        
        if ds_item_group != "" and cp_item_group == ds_item_group :
           
            iner_iter_df = ds_df.loc[index_j:index_j+5]
        ......

改完后執(zhí)行，果然，效率提升了一些，見(jiàn)下圖：

整體耗時(shí)337秒，也就是5分多鐘，比前一版提升40%，看起來(lái)還不錯(cuò)。但是，作為一名優(yōu)秀的IT人，不能滿足于既有的成績(jī)，要不斷追求極致。于是，就有了第三版優(yōu)化。

第三版優(yōu)化

其實(shí)第三版優(yōu)化的思路，還是追求更快地遍歷效率，Pandas除了iterrows()之外，據(jù)說(shuō)還有一個(gè)更快的apply()方法，能夠?qū)ataFrame的每一行逐行應(yīng)用自定義函數(shù)，且遍歷性能更好。于是，第三版的核心代碼如下：

def Cal_Delta_Loi_Iter_In_Cp(data):
    global cal_delta_loi_cp_row
    cal_delta_loi_cp_row = data
    #獲取CP表的Item_group和siteid值
    global cp_item_group
    cp_item_group = cal_delta_loi_cp_row['Item Group']
    siteid = cal_delta_loi_cp_row['SITEID']
    global key 
    key = cp_item_group + "-" + siteid
    ds_df.apply(Cal_Delta_Loi_Iter_In_Ds,axis=1)
    
#開(kāi)始計(jì)算Delta和LOI值
cp_df.apply(Cal_Delta_Loi_Iter_In_Cp,axis=1)
......

按apply()改完代碼再次執(zhí)行，這次執(zhí)行效率果然又上了一個(gè)臺(tái)階，如下圖：

整體耗時(shí)147秒，也即2分多鐘，相比上一版再次提升56%，Very Done!

小小總結(jié)一下

優(yōu)化到這里，我們可以看到，使用Python的Pandas類庫(kù)，并且使用較高性能的內(nèi)置函數(shù)，能夠很大程度提升數(shù)據(jù)處理的性能。但是，我們從前面打印出的日志也能看到，Python提供的xlwings庫(kù)，在讀寫(xiě)excel方面的性能缺很難說(shuō)優(yōu)秀，相比vba來(lái)說(shuō)更是差了一大截。

VBA雖然數(shù)據(jù)結(jié)構(gòu)少，數(shù)據(jù)計(jì)算速度慢，但訪問(wèn)自己Excel的Sheet，Range，Cell等對(duì)象卻速度飛快，這就是一體化產(chǎn)品的優(yōu)勢(shì)。VBA讀取Excel的Range，Cell等操作是通過(guò)底層的API直接讀取數(shù)據(jù)的，而不是通過(guò)微軟統(tǒng)一的外部開(kāi)發(fā)接口。所以Python的各種開(kāi)源和商用的Excel處理類庫(kù)如果和VBA來(lái)比較讀寫(xiě)Excel格子里面的數(shù)據(jù)，都是處于劣勢(shì)的（至少是不占優(yōu)勢(shì)的）。

因此，Python處理Excel的時(shí)候，就要把Excel一次性地讀取數(shù)據(jù)到Python的數(shù)據(jù)結(jié)構(gòu)中，而不是大量調(diào)用Excel里的對(duì)象，不要說(shuō)頻繁地寫(xiě)入Excel，就是頻繁地讀取Excel里面的某些單元格也是效率較低的。

寫(xiě)在最后

再次聲明，程序性能的優(yōu)化是沒(méi)有止境的，永遠(yuǎn)追求極致也是每一個(gè)優(yōu)秀IT人的共同追求。上面的代碼，其實(shí)還是繼續(xù)有優(yōu)化空間的，比如其中一個(gè)思路，就是使用并發(fā)編程的方式來(lái)優(yōu)化，Python里提供了多線程、多進(jìn)程和協(xié)程等多種并發(fā)編程方式，我們是不是可以用來(lái)繼續(xù)優(yōu)化呢？筆者先賣個(gè)關(guān)子，可以關(guān)注后續(xù)我的優(yōu)化系統(tǒng)二。

附：完整代碼地址：

https://github.com/xiaoyuge/kingfish-python/tree/master/excel