?簡(jiǎn)介

用pandas很容易讀取Excel文件并將數(shù)據(jù)轉(zhuǎn)換為DataFrame。然而現(xiàn)實(shí)世界中的Excel文件往往構(gòu)造不佳，在那些數(shù)據(jù)散落在工作表中的情況下，你可能需要定制讀取數(shù)據(jù)的方式。本文將討論如何使用pandas和openpyxl來(lái)讀取這些類型的Excel文件，并干凈地將數(shù)據(jù)轉(zhuǎn)換為適合進(jìn)一步分析的DataFrame。

問(wèn)題

pandas 的 read_excel函數(shù)在讀取Excel工作表方面做得很好。然而，在數(shù)據(jù)不是從A1單元格開始的連續(xù)表格的情況下，結(jié)果可能不是你所期望的那樣。

比如當(dāng)你嘗試使用 read_excel(src_file)讀取下面這個(gè)電子表格樣本。

你會(huì)得到一些下面這樣的東西。

這些結(jié)果包括很多 Unnamed的列、行內(nèi)的標(biāo)題標(biāo)簽以及一些我們不需要的額外列。

Pandas解決方案

對(duì)于這個(gè)數(shù)據(jù)集，最簡(jiǎn)單的解決方案是使用 read_excel()?的 header?和 usecols?參數(shù)。尤其是 usecols參數(shù)，對(duì)于控制你想包括的列非常有用。

如果你想繼續(xù)學(xué)習(xí)這些例子，文件在github上。

https://github.com/chris1610/pbpython/blob/master/data/shipping_tables.xlsx

下面是一個(gè)替代方法，只讀取我們需要的數(shù)據(jù)。

import pandas as pd

from pathlib importPath

src_file = Path.cwd() /  'shipping_tables.xlsx'



df = pd.read_excel(src_file, header=1, usecols='B:F')

產(chǎn)生的DataFrame只包含我們需要的數(shù)據(jù)。在這個(gè)例子中，我們特意排除了備注欄和日期欄。

usecols?可以接受Excel范圍，如 B:F?，并只讀入這些列。header?參數(shù)期望一個(gè)定義標(biāo)題列的單一整數(shù)。這個(gè)值是以0為索引的，所以我們傳入 1，盡管這是Excel的第2行。

在某些情況下，我們可能希望將列定義為一個(gè)數(shù)字列表。在這個(gè)例子中，我們可以定義為整數(shù)的列表。

df = pd.read_excel(src_file, header=1, usecols=[1,2,3,4,5])

如果你對(duì)一個(gè)大的數(shù)據(jù)集有某種想要遵循的數(shù)字模式（即每3列或只有偶數(shù)列），這種方法可能會(huì)很有用。

pandas的 usecols也可以接受一個(gè)列名的列表。這段代碼將創(chuàng)建一個(gè)等效的DataFrame。

# Define a more complex function:

def column_check(x):

if'unnamed'in x.lower():

returnFalse

if'priority'in x.lower():

returnFalse

if'order'in x.lower():

returnTrue

returnTrue



df = pd.read_excel(src_file, header=1, usecols=column_check)

需要記住的關(guān)鍵概念是，該函數(shù)將按名稱解析每一列，必須為每一列返回 True?或 False?。那些被評(píng)估為 True的列將被包括在內(nèi)。

另一種使用可調(diào)用函數(shù)的方法是包含一個(gè) lambda表達(dá)式。這里有一個(gè)例子，我們想只包括一個(gè)定義好的列的列表。我們通過(guò)將名稱轉(zhuǎn)換為小寫字母來(lái)進(jìn)行規(guī)范化，以便于比較。

cols_to_use = ['item_type', 'order id', 'order date', 'state', 'priority']

df = pd.read_excel(src_file,

                   header=1,

                   usecols=lambda x: x.lower() in cols_to_use)

可調(diào)用函數(shù)給了我們很大的靈活性來(lái)處理現(xiàn)實(shí)世界中混亂的Excel文件。

區(qū)間和表格

在某些情況下，數(shù)據(jù)在Excel中可以更加模糊不清。在這個(gè)例子中，我們有一個(gè)叫做 ship_cost的表，我們想讀取它。如果你必須處理這樣的文件，用我們到目前為止討論過(guò)的pandas選項(xiàng)來(lái)讀入可能是個(gè)挑戰(zhàn)。

在這種情況下，我們可以直接使用openpyxl來(lái)解析文件并將數(shù)據(jù)轉(zhuǎn)換成pandas DataFrame。事實(shí)上，數(shù)據(jù)是在一個(gè)Excel表格中，可以使這個(gè)過(guò)程更容易一些。

下面是如何使用openpyxl來(lái)讀取Excel文件。

from openpyxl import load_workbook

import pandas as pd

from pathlib importPath

src_file = src_file = Path.cwd() / 'shipping_tables.xlsx'



wb = load_workbook(filename = src_file)

這將加載整個(gè)工作簿。如果我們想看到所有的工作表。

wb.sheetnames

['sales', 'shipping_rates']

要訪問(wèn)具體的工作表。

sheet = wb['shipping_rates']

要查看所有命名的表的列表。

sheet.tables.keys()

dict_keys(['ship_cost'])

這個(gè)鍵對(duì)應(yīng)于我們?cè)贓xcel中分配給表的名稱。現(xiàn)在我們?cè)L問(wèn)該表，以獲得相當(dāng)于Excel的范圍。

lookup_table = sheet.tables['ship_cost']

lookup_table.ref

'C8:E16'

這就成功了。我們現(xiàn)在知道了我們要加載的數(shù)據(jù)范圍。最后一步是將這個(gè)范圍轉(zhuǎn)換為pandas DataFrame。下面是一個(gè)簡(jiǎn)短的代碼片段，用來(lái)循環(huán)瀏覽每一行并轉(zhuǎn)換為一個(gè)DataFrame。

# Access the data in the table range

data = sheet[lookup_table.ref]

rows_list = []



# Loop through each row and get the values in the cells

for row in data:

# Get a list of all columns in each row

    cols = []

for col in row:

        cols.append(col.value)

    rows_list.append(cols)



# Create a pandas dataframe from the rows_list.

# The first row is the column names

df = pd.DataFrame(data=rows_list[1:], index=None, columns=rows_list[0])

下面是產(chǎn)生的數(shù)據(jù)框架。

現(xiàn)在我們有了干凈的表格，可以用于進(jìn)一步的計(jì)算。

總結(jié)

在一個(gè)理想的條件下，我們使用的數(shù)據(jù)應(yīng)該擁有一個(gè)簡(jiǎn)單一致的格式。在本文的例子中，我們可以很容易地刪除行和列，使之更符合格式要求。然而，有些時(shí)候，這樣做是不可行的，也是不可取的。好消息是，pandas和openpyxl為我們提供了讀取Excel數(shù)據(jù)所需的所有工具。?