CDA數據分析師 出品

作者：曹鑫

編輯：JYD

我真遇到了上百萬行的 Excel

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

業務發展越來越大，數據的規模會越來越大，在初期的時候，還覺得Excel 夠用了，但是當 Excel 規模的數據量不斷增加，我們開始發現打開 Excel 越來越慢，操作一下 Excel 要等很久。

直接雙擊打開？

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

Access

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

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

PowerBI

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

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

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

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

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

那用 Python 試試？

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

1.導入 pandas 包， import pandas as pd ，是最常用的數據處理包。

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

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

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

更高效的數據格式

CSV 格式

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

Pickle 格式

當然 Python 里面還引入了其他的格式，你可能平時接觸的不多，但是效果絕對讓你驚喜。比如將數據存儲為 pkl 的格式，"pickling" 是將 Python 對象及其所擁有的層次結構轉化為一個字節流的過程。

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

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

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