技術(shù)背景

數(shù)據(jù)處理是一個(gè)當(dāng)下非常熱門的研究方向，通過(guò)對(duì)于大型實(shí)際場(chǎng)景中的數(shù)據(jù)進(jìn)行建模，可以用于預(yù)測(cè)下一階段可能出現(xiàn)的情況。比如我們有過(guò)去的2002年-2018年的黃金價(jià)格的數(shù)據(jù)：

該數(shù)據(jù)來(lái)源于Gitee上的一個(gè)開(kāi)源項(xiàng)目。其中包含有：時(shí)間、開(kāi)盤價(jià)、收盤價(jià)、最高價(jià)、最低價(jià)、交易數(shù)以及成交額這么幾個(gè)參數(shù)。假如我們使用一個(gè)機(jī)器學(xué)習(xí)的模型去分析這個(gè)數(shù)據(jù)，也許我們可以預(yù)測(cè)在這個(gè)數(shù)據(jù)中并不存在的金價(jià)數(shù)據(jù)。如果預(yù)測(cè)的契合度較好，那么對(duì)于一些人的投資策略來(lái)說(shuō)有重大意義。但是這種實(shí)際場(chǎng)景下的數(shù)據(jù)，往往數(shù)據(jù)量是非常大的。雖然這里我們使用到的數(shù)據(jù)只有300多KB，但是我們更多的時(shí)候不得不考慮10個(gè)GB甚至是1個(gè)TB以上的數(shù)據(jù)的處理。如果處理都無(wú)法處理，那我們?nèi)绾螌?duì)這些數(shù)據(jù)進(jìn)行建模呢？

python對(duì)Excel表格的處理

首先我們看一個(gè)最簡(jiǎn)單的情況，我們先不考慮性能的問(wèn)題，那么我們可以使用xlrd這個(gè)工具來(lái)在python中打開(kāi)和加載一個(gè)Excel表格：

# table.py

def read_table_by_xlrd():
    import xlrd
    workbook = xlrd.open_workbook(r'data.xls')
    sheet_name = workbook.sheet_names()
    print ('All sheets in the file data.xls are: {}'.format(sheet_name))
    sheet = workbook.sheet_by_index(0)
    print ('The cell value of row index 0 and col index 1 is: {}'.format(sheet.cell_value(0, 1)))
    print ('The elements of row index 0 are: {}'.format(sheet.row_values(0)))
    print ('The length of col index 1 are: {}'.format(len(sheet.col_values(1))))

if __name__ == '__main__':
    read_table_by_xlrd()

上述代碼的輸出如下：

[dechin@dechin-manjaro gold]$ python3 table.py 
All sheets in the file data.xls are: ['Sheet1', 'Sheet2', 'Sheet3']
The cell value of row index 0 and col index 1 is: 開(kāi)
The elements of row index 0 are: ['時(shí)間', '開(kāi)', '高', '低', '收', '量', '額']
The length of col index 1 are: 3923

我們這里成功的將一個(gè)xls格式的表格加載到了python的內(nèi)存中，我們可以對(duì)這些數(shù)據(jù)進(jìn)行分析。如果需要對(duì)這些數(shù)據(jù)修改，可以使用openpyxl這個(gè)倉(cāng)庫(kù)，但是這里我們不做過(guò)多的贅述。

在python中還有另外一個(gè)非常常用且非常強(qiáng)大的庫(kù)可以用來(lái)處理表格數(shù)據(jù)，那就是pandas，這里我們利用ipython這個(gè)工具簡(jiǎn)單展示一下使用pandas處理表格數(shù)據(jù)的方法：

[dechin@dechin-manjaro gold]$ ipython
Python 3.8.5 (default, Sep  4 2020, 07:30:14) 
Type 'copyright', 'credits' or 'license' for more information
IPython 7.19.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import pandas as pd

In [2]: !ls -l
總用量 368
-rw-r--r-- 1 dechin dechin 372736  3月 27 21:31 data.xls
-rw-r--r-- 1 dechin dechin    563  3月 27 21:42 table.py

In [3]: data = pd.read_excel('data.xls', 'Sheet1') # 讀取excel格式的文件

In [4]: data.to_csv('data.csv', encoding='utf-8') # 轉(zhuǎn)成csv格式的文件

In [7]: !ls -l
總用量 588
-rw-r--r-- 1 dechin dechin 221872  3月 27 21:52 data.csv
-rw-r--r-- 1 dechin dechin 372736  3月 27 21:31 data.xls
-rw-r--r-- 1 dechin dechin    563  3月 27 21:42 table.py

In [8]: !head -n 10 data.csv # 讀取csv文件的頭10行
,時(shí)間,開(kāi),高,低,收,量,額
0,2002-10-30,83.98,92.38,82.0,83.52,352,29373370
1,2002-10-31,83.9,83.92,83.9,83.91,66,5537480
2,2002-11-01,84.5,84.65,84.0,84.51,77,6502510
3,2002-11-04,84.9,85.06,84.9,84.99,95,8076330
4,2002-11-05,85.1,85.2,85.1,85.13,61,5193650
5,2002-11-06,84.9,84.9,84.9,84.9,1,84900
6,2002-11-07,85.0,85.15,85.0,85.14,26,2212310
7,2002-11-08,85.25,85.28,85.1,85.16,35,2981780
8,2002-11-11,85.18,85.19,85.18,85.19,65,5537050

在ipython中我們不僅可以執(zhí)行python指令，還可以在前面加一個(gè)!就能夠執(zhí)行一些系統(tǒng)命令，非常的方便。csv格式的文件，其實(shí)就是用逗號(hào)跟換行符來(lái)替代常用的\t字符串進(jìn)行數(shù)據(jù)的分隔。

但是，不論是使用xlrd還是pandas，我們都會(huì)面臨一個(gè)同樣的問(wèn)題：需要把所有的數(shù)據(jù)加載到內(nèi)存中進(jìn)行處理。我們一般的個(gè)人電腦只有8GB-16GB的內(nèi)存，就算是比較大的64GB的內(nèi)存，我們也只能夠在內(nèi)存中對(duì)64GB以下內(nèi)存大小的文件進(jìn)行處理，這對(duì)于大數(shù)據(jù)場(chǎng)景來(lái)說(shuō)遠(yuǎn)遠(yuǎn)不夠。所以，下一章節(jié)中介紹的vaex就是一個(gè)很好的解決方案。另外，關(guān)于Linux下查看本地內(nèi)存以及使用情況的方法如下：

[dechin@dechin-manjaro gold]$ vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b 交換 空閑 緩沖 緩存   si   so    bi    bo   in   cs us sy id wa st
 0  0      0 35812168 328340 2904872    0    0    20    27  362  365  8  4 88  0  0
[dechin@dechin-manjaro gold]$ vmstat 2 3
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b 交換 空閑 緩沖 緩存   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 35810916 328356 2905844    0    0    20    27  362  365  8  4 88  0  0
 0  0      0 35811916 328364 2904952    0    0     0     6  613  688  1  1 99  0  0
 0  0      0 35812168 328364 2904856    0    0     0     0  672  642  0  1 99  0  0

我們可以看到空閑內(nèi)存大約有36GB的內(nèi)存，這里我們本機(jī)一共有40GB的內(nèi)存，算是比較大的了。

vaex的安裝與使用

vaex提供了一種內(nèi)存映射的數(shù)據(jù)處理方案，我們不需要將整個(gè)的數(shù)據(jù)文件加載到內(nèi)存中進(jìn)行處理，我們可以直接對(duì)硬盤存儲(chǔ)進(jìn)行操作。換句話說(shuō)，我們所能夠處理的文件大小不再受到內(nèi)存大小的限制，只要在磁盤存儲(chǔ)空間允許的范圍內(nèi)，我們都可以對(duì)這么大小的文件進(jìn)行處理。
一般現(xiàn)在個(gè)人PC的磁盤最小也有128GB，遠(yuǎn)遠(yuǎn)大于內(nèi)存可以承受的范圍。當(dāng)然，由于分區(qū)的不同，不一定能夠保障所有的內(nèi)存資源都能夠被使用到，這里附上查看當(dāng)前目錄分區(qū)的可用磁盤空間大小查詢的方法：

[dechin@dechin-manjaro gold]$ df -hl .
文件系統(tǒng)        容量  已用  可用 已用% 掛載點(diǎn)
/dev/nvme0n1p9  144G   57G   80G   42% /

這里可以看到我們還有80GB的可用磁盤空間，也就是說(shuō)，如果我們?cè)诋?dāng)前目錄放一個(gè)80GB大小的表格文件，那么用pandas和xlrd都是沒(méi)辦法處理的，因?yàn)檫@已經(jīng)遠(yuǎn)遠(yuǎn)超出了內(nèi)存可支持的空間。但是用vaex，我們依然可以對(duì)這個(gè)文件進(jìn)行處理。

在vaex的官方文檔鏈接中也介紹有vaex的原理和優(yōu)勢(shì)：

vaex的安裝

與大多數(shù)的python第三方包類似的，我們可以使用pip來(lái)進(jìn)行下載和管理。當(dāng)然由于下載的文件會(huì)比較多，中間的過(guò)程也會(huì)較為緩慢，我們只需安靜等待即可：

[dechin@dechin-manjaro gold]$ python3 -m pip install vaex
Collecting vaex
  Downloading vaex-4.1.0-py3-none-any.whl (4.5 kB)
Collecting vaex-ml0.12,>=0.11.0
  Downloading vaex_ml-0.11.1-py3-none-any.whl (95 kB)
     |████████████████████████████████| 95 kB 81 kB/s 
Collecting vaex-core5,>=4.1.0
  Downloading vaex_core-4.1.0-cp38-cp38-manylinux2010_x86_64.whl (2.5 MB)
     |████████████████████████████████| 2.5 MB 61 kB/s 
Collecting vaex-viz0.6,>=0.5.0
  Downloading vaex_viz-0.5.0-py3-none-any.whl (19 kB)
Collecting vaex-astro0.9,>=0.8.0
  Downloading vaex_astro-0.8.0-py3-none-any.whl (20 kB)
Collecting vaex-hdf50.8,>=0.7.0
  Downloading vaex_hdf5-0.7.0-py3-none-any.whl (15 kB)
Collecting vaex-server0.5,>=0.4.0
  Downloading vaex_server-0.4.0-py3-none-any.whl (13 kB)
Collecting vaex-jupyter0.7,>=0.6.0
  Downloading vaex_jupyter-0.6.0-py3-none-any.whl (42 kB)
     |████████████████████████████████| 42 kB 82 kB/s 
Requirement already satisfied: traitlets in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-ml0.12,>=0.11.0->vaex) (5.0.5)
Requirement already satisfied: numba in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-ml0.12,>=0.11.0->vaex) (0.51.2)
Requirement already satisfied: jinja2 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-ml0.12,>=0.11.0->vaex) (2.11.2)
Requirement already satisfied: psutil>=1.2.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (5.7.2)
Requirement already satisfied: six in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (1.15.0)
Requirement already satisfied: cloudpickle in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (1.6.0)
Requirement already satisfied: numpy>=1.16 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (1.20.1)
Requirement already satisfied: dask[array] in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (2.30.0)
Collecting pyarrow>=3.0
  Downloading pyarrow-3.0.0-cp38-cp38-manylinux2014_x86_64.whl (20.7 MB)
     |████████████████████████████████| 20.7 MB 86 kB/s 
Requirement already satisfied: pandas in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (1.1.3)
WARNING: Retrying (Retry(total=4, connect=None, read=None, redirect=None, status=None)) after connection broken by 'ReadTimeoutError("HTTPSConnectionPool(host='pypi.org', port=443): Read timed out. (read timeout=15)")': /simple/tabulate/                                       
Collecting tabulate>=0.8.3
  Downloading tabulate-0.8.9-py3-none-any.whl (25 kB)
Requirement already satisfied: pyyaml in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (5.3.1)
Collecting frozendict
  Downloading frozendict-1.2.tar.gz (2.6 kB)
Collecting aplus
  Downloading aplus-0.11.0.tar.gz (3.7 kB)
Requirement already satisfied: requests in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (2.24.0)
Requirement already satisfied: nest-asyncio>=1.3.3 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (1.4.2)
Collecting progressbar2
  Downloading progressbar2-3.53.1-py2.py3-none-any.whl (25 kB)
Requirement already satisfied: future>=0.15.2 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-core5,>=4.1.0->vaex) (0.18.2)
Requirement already satisfied: matplotlib>=1.3.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-viz0.6,>=0.5.0->vaex) (3.3.4)
Requirement already satisfied: pillow in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-viz0.6,>=0.5.0->vaex) (8.0.1)
Requirement already satisfied: astropy in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-astro0.9,>=0.8.0->vaex) (4.0.2)
Requirement already satisfied: h5py>=2.9 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-hdf50.8,>=0.7.0->vaex) (2.10.0)
Collecting cachetools
  Downloading cachetools-4.2.1-py3-none-any.whl (12 kB)
Requirement already satisfied: tornado>4.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from vaex-server0.5,>=0.4.0->vaex) (6.0.4)
Collecting xarray
  Downloading xarray-0.17.0-py3-none-any.whl (759 kB)
     |████████████████████████████████| 759 kB 28 kB/s 
Collecting ipympl
  Downloading ipympl-0.7.0-py2.py3-none-any.whl (106 kB)
     |████████████████████████████████| 106 kB 39 kB/s 
Collecting ipyleaflet
  Downloading ipyleaflet-0.13.6-py2.py3-none-any.whl (3.3 MB)
     |████████████████████████████████| 3.3 MB 75 kB/s 
Collecting ipyvuetify2,>=1.2.2
  Downloading ipyvuetify-1.6.2-py2.py3-none-any.whl (11.7 MB)
     |████████████████████████████████| 11.7 MB 173 kB/s 
Collecting ipyvolume>=0.4
  Downloading ipyvolume-0.5.2-py2.py3-none-any.whl (2.9 MB)
     |████████████████████████████████| 2.9 MB 66 kB/s 
Collecting bqplot>=0.10.1
  Downloading bqplot-0.12.23-py2.py3-none-any.whl (1.2 MB)
     |████████████████████████████████| 1.2 MB 175 kB/s 
Requirement already satisfied: ipython-genutils in /home/dechin/anaconda3/lib/python3.8/site-packages (from traitlets->vaex-ml0.12,>=0.11.0->vaex) (0.2.0)
Requirement already satisfied: setuptools in /home/dechin/anaconda3/lib/python3.8/site-packages (from numba->vaex-ml0.12,>=0.11.0->vaex) (50.3.1.post20201107)
Requirement already satisfied: llvmlite0.35,>=0.34.0.dev0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from numba->vaex-ml0.12,>=0.11.0->vaex) (0.34.0)
Requirement already satisfied: MarkupSafe>=0.23 in /home/dechin/anaconda3/lib/python3.8/site-packages (from jinja2->vaex-ml0.12,>=0.11.0->vaex) (1.1.1)
Requirement already satisfied: toolz>=0.8.2; extra == "array" in /home/dechin/anaconda3/lib/python3.8/site-packages (from dask[array]->vaex-core5,>=4.1.0->vaex) (0.11.1)
Requirement already satisfied: pytz>=2017.2 in /home/dechin/anaconda3/lib/python3.8/site-packages (from pandas->vaex-core5,>=4.1.0->vaex) (2020.1)
Requirement already satisfied: python-dateutil>=2.7.3 in /home/dechin/anaconda3/lib/python3.8/site-packages (from pandas->vaex-core5,>=4.1.0->vaex) (2.8.1)
Requirement already satisfied: certifi>=2017.4.17 in /home/dechin/anaconda3/lib/python3.8/site-packages (from requests->vaex-core5,>=4.1.0->vaex) (2020.6.20)
Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,1.26,>=1.21.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from requests->vaex-core5,>=4.1.0->vaex) (1.25.11)
Requirement already satisfied: idna3,>=2.5 in /home/dechin/anaconda3/lib/python3.8/site-packages (from requests->vaex-core5,>=4.1.0->vaex) (2.10)
Requirement already satisfied: chardet4,>=3.0.2 in /home/dechin/anaconda3/lib/python3.8/site-packages (from requests->vaex-core5,>=4.1.0->vaex) (3.0.4)
Collecting python-utils>=2.3.0
  Downloading python_utils-2.5.6-py2.py3-none-any.whl (12 kB)
Requirement already satisfied: cycler>=0.10 in /home/dechin/anaconda3/lib/python3.8/site-packages (from matplotlib>=1.3.1->vaex-viz0.6,>=0.5.0->vaex) (0.10.0)
Requirement already satisfied: kiwisolver>=1.0.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from matplotlib>=1.3.1->vaex-viz0.6,>=0.5.0->vaex) (1.3.0)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in /home/dechin/anaconda3/lib/python3.8/site-packages (from matplotlib>=1.3.1->vaex-viz0.6,>=0.5.0->vaex) (2.4.7)
Collecting ipywidgets>=7.6.0
  Downloading ipywidgets-7.6.3-py2.py3-none-any.whl (121 kB)
     |████████████████████████████████| 121 kB 175 kB/s 
Requirement already satisfied: ipykernel>=4.7 in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (5.3.4)
Collecting branca0.5,>=0.3.1
  Downloading branca-0.4.2-py3-none-any.whl (24 kB)
Collecting shapely
  Downloading Shapely-1.7.1-cp38-cp38-manylinux1_x86_64.whl (1.0 MB)
     |████████████████████████████████| 1.0 MB 98 kB/s 
Collecting traittypes3,>=0.2.1
  Downloading traittypes-0.2.1-py2.py3-none-any.whl (8.6 kB)
Collecting ipyvue2,>=1.5
  Downloading ipyvue-1.5.0-py2.py3-none-any.whl (2.7 MB)
     |████████████████████████████████| 2.7 MB 80 kB/s 
Collecting ipywebrtc
  Downloading ipywebrtc-0.5.0-py2.py3-none-any.whl (1.1 MB)
     |████████████████████████████████| 1.1 MB 99 kB/s 
Collecting pythreejs>=1.0.0
  Downloading pythreejs-2.3.0-py2.py3-none-any.whl (3.4 MB)
     |████████████████████████████████| 3.4 MB 30 kB/s 
Requirement already satisfied: widgetsnbextension~=3.5.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (3.5.1)
Requirement already satisfied: nbformat>=4.2.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (5.0.8)
Requirement already satisfied: ipython>=4.0.0; python_version >= "3.3" in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (7.19.0)
Collecting jupyterlab-widgets>=1.0.0; python_version >= "3.6"
  Downloading jupyterlab_widgets-1.0.0-py3-none-any.whl (243 kB)
     |████████████████████████████████| 243 kB 115 kB/s 
Requirement already satisfied: jupyter-client in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipykernel>=4.7->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (6.1.7)
Collecting ipydatawidgets>=1.1.1
  Downloading ipydatawidgets-4.2.0-py2.py3-none-any.whl (275 kB)
     |████████████████████████████████| 275 kB 73 kB/s 
Requirement already satisfied: notebook>=4.4.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (6.1.4)
Requirement already satisfied: jsonschema!=2.5.0,>=2.4 in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbformat>=4.2.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (3.2.0)
Requirement already satisfied: jupyter-core in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbformat>=4.2.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (4.6.3)
Requirement already satisfied: backcall in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.2.0)
Requirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,3.1.0,>=2.0.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (3.0.8)
Requirement already satisfied: pickleshare in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.7.5)
Requirement already satisfied: pexpect>4.3; sys_platform != "win32" in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (4.8.0)
Requirement already satisfied: pygments in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (2.7.2)
Requirement already satisfied: jedi>=0.10 in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.17.1)
Requirement already satisfied: decorator in /home/dechin/anaconda3/lib/python3.8/site-packages (from ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (4.4.2)
Requirement already satisfied: pyzmq>=13 in /home/dechin/anaconda3/lib/python3.8/site-packages (from jupyter-client->ipykernel>=4.7->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (19.0.2)
Requirement already satisfied: terminado>=0.8.3 in /home/dechin/anaconda3/lib/python3.8/site-packages (from notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.9.1)
Requirement already satisfied: argon2-cffi in /home/dechin/anaconda3/lib/python3.8/site-packages (from notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (20.1.0)
Requirement already satisfied: Send2Trash in /home/dechin/anaconda3/lib/python3.8/site-packages (from notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (1.5.0)
Requirement already satisfied: nbconvert in /home/dechin/anaconda3/lib/python3.8/site-packages (from notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (6.0.7)
Requirement already satisfied: prometheus-client in /home/dechin/anaconda3/lib/python3.8/site-packages (from notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.8.0)
Requirement already satisfied: pyrsistent>=0.14.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from jsonschema!=2.5.0,>=2.4->nbformat>=4.2.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.17.3)
Requirement already satisfied: attrs>=17.4.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from jsonschema!=2.5.0,>=2.4->nbformat>=4.2.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (20.3.0)
Requirement already satisfied: wcwidth in /home/dechin/anaconda3/lib/python3.8/site-packages (from prompt-toolkit!=3.0.0,!=3.0.1,3.1.0,>=2.0.0->ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.2.5)
Requirement already satisfied: ptyprocess>=0.5 in /home/dechin/anaconda3/lib/python3.8/site-packages (from pexpect>4.3; sys_platform != "win32"->ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.6.0)
Requirement already satisfied: parso0.8.0,>=0.7.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from jedi>=0.10->ipython>=4.0.0; python_version >= "3.3"->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.7.0)
Requirement already satisfied: cffi>=1.0.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from argon2-cffi->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (1.14.3)
Requirement already satisfied: mistune2,>=0.8.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.8.4)
Requirement already satisfied: testpath in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.4.4)
Requirement already satisfied: pandocfilters>=1.4.1 in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (1.4.3)
Requirement already satisfied: jupyterlab-pygments in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.1.2)
Requirement already satisfied: bleach in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (3.2.1)
Requirement already satisfied: entrypoints>=0.2.2 in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.3)
Requirement already satisfied: defusedxml in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.6.0)
Requirement already satisfied: nbclient0.6.0,>=0.5.0 in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.5.1)
Requirement already satisfied: pycparser in /home/dechin/anaconda3/lib/python3.8/site-packages (from cffi>=1.0.0->argon2-cffi->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (2.20)
Requirement already satisfied: webencodings in /home/dechin/anaconda3/lib/python3.8/site-packages (from bleach->nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (0.5.1)
Requirement already satisfied: packaging in /home/dechin/anaconda3/lib/python3.8/site-packages (from bleach->nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (20.4)
Requirement already satisfied: async-generator in /home/dechin/anaconda3/lib/python3.8/site-packages (from nbclient0.6.0,>=0.5.0->nbconvert->notebook>=4.4.1->widgetsnbextension~=3.5.0->ipywidgets>=7.6.0->ipympl->vaex-jupyter0.7,>=0.6.0->vaex) (1.10)
Building wheels for collected packages: frozendict, aplus
  Building wheel for frozendict (setup.py) ... done
  Created wheel for frozendict: filename=frozendict-1.2-py3-none-any.whl size=3148 sha256=1ae5d8fe0d670f73bf3ee88453978246919197a616f0e08e601c84cc244cb238
  Stored in directory: /home/dechin/.cache/pip/wheels/9b/9b/56/5713233cf7226423ab6c58c08081551a301b5863e343ba053c
  Building wheel for aplus (setup.py) ... done
  Created wheel for aplus: filename=aplus-0.11.0-py3-none-any.whl size=4412 sha256=9762d51c5ece813b0c5a27ff6ebc1a86e709d55edb7003dcc11272c954dd39c7
  Stored in directory: /home/dechin/.cache/pip/wheels/de/93/23/3db69e1003030a764c9827dc02137119ec5e6e439afd64eebb
Successfully built frozendict aplus
Installing collected packages: pyarrow, tabulate, frozendict, aplus, python-utils, progressbar2, vaex-core, vaex-ml, vaex-viz, vaex-astro, vaex-hdf5, cachetools, vaex-server, xarray, jupyterlab-widgets, ipywidgets, ipympl, branca, shapely, traittypes, ipyleaflet, ipyvue, ipyvuetify, ipywebrtc, ipydatawidgets, pythreejs, ipyvolume, bqplot, vaex-jupyter, vaex
  Attempting uninstall: ipywidgets
    Found existing installation: ipywidgets 7.5.1
    Uninstalling ipywidgets-7.5.1:
      Successfully uninstalled ipywidgets-7.5.1
Successfully installed aplus-0.11.0 bqplot-0.12.23 branca-0.4.2 cachetools-4.2.1 frozendict-1.2 ipydatawidgets-4.2.0 ipyleaflet-0.13.6 ipympl-0.7.0 ipyvolume-0.5.2 ipyvue-1.5.0 ipyvuetify-1.6.2 ipywebrtc-0.5.0 ipywidgets-7.6.3 jupyterlab-widgets-1.0.0 progressbar2-3.53.1 pyarrow-3.0.0 python-utils-2.5.6 pythreejs-2.3.0 shapely-1.7.1 tabulate-0.8.9 traittypes-0.2.1 vaex-4.1.0 vaex-astro-0.8.0 vaex-core-4.1.0 vaex-hdf5-0.7.0 vaex-jupyter-0.6.0 vaex-ml-0.11.1 vaex-server-0.4.0 vaex-viz-0.5.0 xarray-0.17.0

在出現(xiàn)Successfully installed的字樣之后，就代表我們已經(jīng)安裝成功，可以開(kāi)始使用了。

性能對(duì)比

由于使用其他的工具我們也可以正常的打開(kāi)和讀取表格文件，為了體現(xiàn)出使用vaex的優(yōu)勢(shì)，這里我們直接用ipython來(lái)對(duì)比一下兩者的打開(kāi)時(shí)間：

[dechin@dechin-manjaro gold]$ ipython
Python 3.8.5 (default, Sep  4 2020, 07:30:14) 
Type 'copyright', 'credits' or 'license' for more information
IPython 7.19.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import vaex

In [2]: import xlrd

In [3]: %timeit xlrd.open_workbook(r'data.xls')
46.4 ms ± 76.2 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

In [4]: %timeit vaex.open('data.csv')
4.95 ms ± 48.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [7]: %timeit vaex.open('data.hdf5')
1.34 ms ± 1.84 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

我們從結(jié)果中發(fā)現(xiàn)，打開(kāi)同樣的一份文件，使用xlrd需要將近50ms的時(shí)間，而vaex最低只需要1ms的時(shí)間，如此巨大的性能優(yōu)勢(shì)使得我們不得不對(duì)vaex給予更多的關(guān)注。關(guān)于跟其他庫(kù)的對(duì)比，在這個(gè)鏈接中已經(jīng)有人做過(guò)了，即使是對(duì)比pandas，vaex在讀取速度上也有1000多倍的加速，而計(jì)算速度的加速效果在數(shù)倍，總體來(lái)說(shuō)表現(xiàn)非常的優(yōu)秀。

數(shù)據(jù)格式轉(zhuǎn)換

在上一章節(jié)的測(cè)試中，我們用到了1個(gè)沒(méi)有提到過(guò)的文件：data.hdf5，這個(gè)文件其實(shí)是從data.csv轉(zhuǎn)換而來(lái)的。這一章節(jié)我們主要就介紹如何將數(shù)據(jù)格式進(jìn)行轉(zhuǎn)換，以適配vaex可以打開(kāi)和識(shí)別的格式。第一個(gè)方案是使用pandas將csv格式的文件直接轉(zhuǎn)換為hdf5格式，操作類似于在python對(duì)表格數(shù)據(jù)處理的章節(jié)中將xls格式的文件轉(zhuǎn)換成csv格式：

[dechin@dechin-manjaro gold]$ ipython
Python 3.8.5 (default, Sep  4 2020, 07:30:14) 
Type 'copyright', 'credits' or 'license' for more information
IPython 7.19.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import pandas as pd

In [4]: data = pd.read_csv('data.csv')

In [10]: data.to_hdf('data.hdf5','data',mode='w',format='table')

In [11]: !ls -l
總用量 932
-rw-r--r-- 1 dechin dechin 221872  3月 27 21:52 data.csv
-rw-r--r-- 1 dechin dechin 348524  3月 27 22:17 data.hdf5
-rw-r--r-- 1 dechin dechin 372736  3月 27 21:31 data.xls
-rw-r--r-- 1 dechin dechin    563  3月 27 21:42 table.py

操作完成之后在當(dāng)前目錄下生成了一個(gè)hdf5文件。但是這種操作方式有個(gè)弊端，就是生成的hdf5文件跟vaex不是直接適配的關(guān)系，如果直接用df = vaex.open('data.hdf5')的方法進(jìn)行讀取的話，輸出內(nèi)容如下所示：

In [3]: df
Out[3]: 
#      table
0      '(0, [83.98, 92.38, 82.  , 83.52], [       0,   ...
1      '(1, [83.9 , 83.92, 83.9 , 83.91], [      1,    ...
2      '(2, [84.5 , 84.65, 84.  , 84.51], [      2,    ...
3      '(3, [84.9 , 85.06, 84.9 , 84.99], [      3,    ...
4      '(4, [85.1 , 85.2 , 85.1 , 85.13], [      4,    ...
...    ...
3,917  '(3917, [274.65, 275.35, 274.6 , 274.61], [     ...
3,918  '(3918, [274.4, 275.2, 274.1, 275. ], [      391...
3,919  '(3919, [275.  , 275.01, 274.  , 274.19], [     ...
3,920  '(3920, [275.2, 275.2, 272.6, 272.9], [      392...
3,921  '(3921, [272.96, 273.73, 272.5 , 272.93], [     ...

在這個(gè)數(shù)據(jù)中，丟失了最關(guān)鍵的索引信息，雖然數(shù)據(jù)都被正確的保留了下來(lái)，但是在讀取上有非常大的不便。因此我們更加推薦第二種數(shù)據(jù)轉(zhuǎn)換的方法，直接用vaex進(jìn)行數(shù)據(jù)格式的轉(zhuǎn)換：

[dechin@dechin-manjaro gold]$ ipython
Python 3.8.5 (default, Sep  4 2020, 07:30:14) 
Type 'copyright', 'credits' or 'license' for more information
IPython 7.19.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import vaex

In [2]: df = vaex.from_csv('data.csv')

In [3]: df.export_hdf5('vaex_data.hdf5')

In [4]: !ls -l
總用量 1220
-rw-r--r-- 1 dechin dechin 221856  3月 27 22:34 data.csv
-rw-r--r-- 1 dechin dechin 348436  3月 27 22:34 data.hdf5
-rw-r--r-- 1 dechin dechin 372736  3月 27 21:31 data.xls
-rw-r--r-- 1 dechin dechin    563  3月 27 21:42 table.py
-rw-r--r-- 1 dechin dechin 293512  3月 27 22:52 vaex_data.hdf5

執(zhí)行完畢后在當(dāng)前目錄下生成了一個(gè)vaex_data.hdf5文件，讓我們?cè)僭囋囎x取這個(gè)新的hdf5文件：

[dechin@dechin-manjaro gold]$ ipython
Python 3.8.5 (default, Sep  4 2020, 07:30:14) 
Type 'copyright', 'credits' or 'license' for more information
IPython 7.19.0 -- An enhanced Interactive Python. Type '?' for help.

In [1]: import vaex

In [2]: df = vaex.open('vaex_data.hdf5')

In [3]: df
Out[3]: 
#      i     t             s       h       l      e       n      a
0      0     '2002-10-30'  83.98   92.38   82.0   83.52   352    29373370
1      1     '2002-10-31'  83.9    83.92   83.9   83.91   66     5537480
2      2     '2002-11-01'  84.5    84.65   84.0   84.51   77     6502510
3      3     '2002-11-04'  84.9    85.06   84.9   84.99   95     8076330
4      4     '2002-11-05'  85.1    85.2    85.1   85.13   61     5193650
...    ...   ...           ...     ...     ...    ...     ...    ...
3,917  3917  '2018-11-23'  274.65  275.35  274.6  274.61  13478  3708580608
3,918  3918  '2018-11-26'  274.4   275.2   274.1  275.0   13738  3773763584
3,919  3919  '2018-11-27'  275.0   275.01  274.0  274.19  13984  3836845568
3,920  3920  '2018-11-28'  275.2   275.2   272.6  272.9   15592  4258130688
3,921  3921  '2018-11-28'  272.96  273.73  272.5  272.93  592    161576336

In [4]: df.s
Out[4]: 
Expression = s
Length: 3,922 dtype: float64 (column)
-------------------------------------
   0   83.98
   1    83.9
   2    84.5
   3    84.9
   4    85.1
    ...     
3917  274.65
3918   274.4
3919     275
3920   275.2
3921  272.96

In [11]: df.plot(df.i, df.s, show=True) # 作圖
/home/dechin/anaconda3/lib/python3.8/site-packages/vaex/viz/mpl.py:311: UserWarning: `plot` is deprecated and it will be removed in version 5.x. Please `df.viz.heatmap` instead.
  warnings.warn('`plot` is deprecated and it will be removed in version 5.x. Please `df.viz.heatmap` instead.')

這里我們也需要提一下，在新的hdf5文件中，索引從高、低等中文變成了h、l等英文，這是為了方便數(shù)據(jù)的操作，我們?cè)赾sv文件中將索引手動(dòng)的修改成了英文，再轉(zhuǎn)換成hdf5的格式。最后我們使用vaex自帶的畫圖功能，繪制了這十幾年期間黃金的價(jià)格變動(dòng)：

由于vaex自帶的繪圖方法比較少，總結(jié)如下：

最常用的還是熱度圖，因此這里繪制出來(lái)的黃金價(jià)格圖的效果也是熱度圖的效果，但是基本上功能是比較完備的，而且性能異常的強(qiáng)大。

總結(jié)概要

在這篇文章中我們介紹了三種不同的python庫(kù)對(duì)表格數(shù)據(jù)進(jìn)行處理，分別是xlrd、pandas和vaex，其中特別著重的強(qiáng)調(diào)了一下vaex的優(yōu)越性能以及在大數(shù)據(jù)中的應(yīng)用價(jià)值。配合一些簡(jiǎn)單的示例，我們可以初步的了解到這些庫(kù)各自的特點(diǎn)，在實(shí)際場(chǎng)景中可以斟酌使用。

以上就是利用python做表格數(shù)據(jù)處理的詳細(xì)內(nèi)容，更多關(guān)于python 表格數(shù)據(jù)處理的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

python 刪除excel表格重復(fù)行,數(shù)據(jù)預(yù)處理操作
Python3讀取和寫入excel表格數(shù)據(jù)的示例代碼
基于Python快速處理PDF表格數(shù)據(jù)
Python基于pandas爬取網(wǎng)頁(yè)表格數(shù)據(jù)
基于python實(shí)現(xiàn)把json數(shù)據(jù)轉(zhuǎn)換成Excel表格
使用 Python 讀取電子表格中的數(shù)據(jù)實(shí)例詳解
python讀取word 中指定位置的表格及表格數(shù)據(jù)
python 中Arduino串口傳輸數(shù)據(jù)到電腦并保存至excel表格
Python 用三行代碼提取PDF表格數(shù)據(jù)
Python獲取數(shù)據(jù)庫(kù)數(shù)據(jù)并保存在excel表格中的方法
python 獲取頁(yè)面表格數(shù)據(jù)存放到csv中的方法
python3 讀取Excel表格中的數(shù)據(jù)

標(biāo)簽：山西濟(jì)南長(zhǎng)沙喀什安康崇左海南山西

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《利用python做表格數(shù)據(jù)處理》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。