CDA數(shù)據(jù)分析師 出品

作者：曹鑫

編輯：JYD

我真遇到了上百萬行的 Excel

年底到了，我想把公司歷年的銷售明細(xì)和指標(biāo)等業(yè)務(wù)數(shù)據(jù)放在一起透視做分析，覺得這樣很方便，但是無奈一張表就50多萬行，好幾年的數(shù)據(jù)加在一起有兩三百萬行，受 excel行數(shù)限制，我只能將數(shù)據(jù)按年分開，一年一張表，每張表里的表頭項目都是一樣的。

業(yè)務(wù)發(fā)展越來越大，數(shù)據(jù)的規(guī)模會越來越大，在初期的時候，還覺得Excel 夠用了，但是當(dāng) Excel 規(guī)模的數(shù)據(jù)量不斷增加，我們開始發(fā)現(xiàn)打開 Excel 越來越慢，操作一下 Excel 要等很久。

直接雙擊打開？

最簡單的方法，當(dāng)然是雙擊打開，當(dāng)你雙擊下去，看著鼠標(biāo)變成旋轉(zhuǎn)的模式，你就陷入了無盡的等待，聽著電腦的機聲音越來越大，最后還沒打開，電腦和我就都崩潰了。這完全沒法開展下一步的數(shù)據(jù)分析?作了，怎么辦？

Access

首先想到的是個比較冷門，但又沒那么冷門，好像學(xué)過，但又好像沒用過，好像很難，但其實也沒那么難的軟件：Access。

Access 導(dǎo)入 Excel 數(shù)據(jù)的操作很直觀，打開 Access，點擊「外部數(shù)據(jù)」-「新數(shù)據(jù)源」-「從文件」-「Excel」，按照指引一步步操作下去即可，而且 Access 也支持新表追加到舊表的后面，可以把幾十萬的表一張張拼接到一起。但估計你現(xiàn)在電腦里還有沒有Access還不一定。

PowerBI

同樣是微軟出品的軟件，現(xiàn)在更流行，你還可以選擇 PowerBI 的一系列組合軟件。

從Excel2010開始，微軟推出了一個叫Power Query的插件，可以彌補Excel的不足，處理數(shù)據(jù)的能力邊界大大提升，Excel2013也同樣可以使用，現(xiàn)在還在用Excel2010和 2013的同學(xué)可以從微軟官網(wǎng)下載powerquery插件使用。

而到了Excel2016，微軟直接把PQ的功能嵌入進來，放在數(shù)據(jù)選項卡下。

首先我們使用Excel2016打開一個空白的Excel工作簿文件，依次點擊“數(shù)據(jù)/從文件/從工作簿”，在導(dǎo)航器界面，左側(cè)列出了所有工作表，我們這個不是一個個去勾選加載，如果表很多，那么勾起來太麻煩，直接選任一個表，點擊“轉(zhuǎn)換數(shù)據(jù)”按鈕，進入Power Query管理界面即可。

都說到這份兒上了，Python 黨得出來說兩句了：上百萬行的數(shù)據(jù)還放在excel里面？！別說處理了，你連打開有時候可能都是問題。這種情況下最根本的辦法了就是存入數(shù)據(jù)庫然后再處理，即使再不濟也可以放入access?？赡苡腥藭f可以是使用 power query或者power pivot來處理，但是，實際情況是這么大的數(shù)據(jù)量，PowerBI也很吃力。

那用 Python 試試？

Python 讀取百萬行的 Excel 大概要花費5分鐘（以我以前的電腦配置 16GB 內(nèi)存），如果你的配置更好，當(dāng)然會更快，代碼也很簡單，如下圖：

1.導(dǎo)入 pandas 包， import pandas as pd ，是最常用的數(shù)據(jù)處理包。

2.使入 pd.read_excel() 讀取 test4.xlsx 文件，讀取 Excel 有直接寫好的方法。

3.使入 df.head() 查看一下前五行。

最終花了 5 分鐘，才把這份 50 萬行 50 列的數(shù)據(jù)打開了。雖然比起雙擊打開是要快一點的（至少打開了），但是還不滿足，有沒有更快的方式？這時候，就要開始跳出Excel，開始思考其他一些更高效的數(shù)據(jù)格式。

更高效的數(shù)據(jù)格式

CSV 格式

CSV文件，是一種以純文本形式存儲表格數(shù)據(jù)的簡單文件格式。在CSV中，每列數(shù)據(jù)由特殊分隔符分割（如逗號，分號或制表符），用 Python 來讀取都非常方便，只要格式規(guī)整，用 Pandas 里面的 read_csv 可以快速讀取以上格式文件，在我的電腦上，同樣是 50 萬行 50 列的數(shù)據(jù)，原來打開要花 5 分鐘，現(xiàn)在只花了 5 秒鐘，速度提升了60倍：

Pickle 格式

當(dāng)然 Python 里面還引入了其他的格式，你可能平時接觸的不多，但是效果絕對讓你驚喜。比如將數(shù)據(jù)存儲為 pkl 的格式，"pickling" 是將 Python 對象及其所擁有的層次結(jié)構(gòu)轉(zhuǎn)化為一個字節(jié)流的過程。

我們來看看讀取的速度，打開速度一下子提升到500毫秒。

從5分鐘，到5秒鐘，到500毫秒，沒有最快只有更快。

隨著業(yè)務(wù)擴展，數(shù)據(jù)量一定會越來越大。你也會面臨著數(shù)據(jù)量越來越大，處理的效率越來越慢的問題。我們思考問題的路徑就可以從軟件 Access、PowerBI，到編程語言 Python，再到文件格式 Excel、CSV、Pickle，一路解決下去。