用 Pandas 處理結(jié)構(gòu)不佳的 Excel 文件
?簡介
用pandas很容易讀取Excel文件并將數(shù)據(jù)轉(zhuǎn)換為DataFrame。然而現(xiàn)實(shí)世界中的Excel文件往往構(gòu)造不佳，在那些數(shù)據(jù)散落在工作表中的情況下，你可能需要定制讀取數(shù)據(jù)的方式。本文將討論如何使用pandas和openpyxl來讀取這些類型的Excel文件，并干凈地將數(shù)據(jù)轉(zhuǎn)換為適合進(jìn)一步分析的DataFrame。
問題
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ù)集，最簡單的解決方案是使用 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è)定義好的列的列表。我們通過將名稱轉(zhuǎn)換為小寫字母來進(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ù)給了我們很大的靈活性來處理現(xiàn)實(shí)世界中混亂的Excel文件。
區(qū)間和表格
在某些情況下，數(shù)據(jù)在Excel中可以更加模糊不清。在這個(gè)例子中，我們有一個(gè)叫做 ship_cost的表，我們想讀取它。如果你必須處理這樣的文件，用我們到目前為止討論過的pandas選項(xiàng)來讀入可能是個(gè)挑戰(zhàn)。
在這種情況下，我們可以直接使用openpyxl來解析文件并將數(shù)據(jù)轉(zhuǎn)換成pandas DataFrame。事實(shí)上，數(shù)據(jù)是在一個(gè)Excel表格中，可以使這個(gè)過程更容易一些。
下面是如何使用openpyxl來讀取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']
要訪問具體的工作表。
sheet = wb['shipping_rates']
要查看所有命名的表的列表。
sheet.tables.keys()
dict_keys(['ship_cost'])
這個(gè)鍵對(duì)應(yīng)于我們?cè)贓xcel中分配給表的名稱?，F(xiàn)在我們?cè)L問該表，以獲得相當(dāng)于Excel的范圍。
lookup_table = sheet.tables['ship_cost']
lookup_table.ref
'C8:E16'這就成功了。我們現(xiàn)在知道了我們要加載的數(shù)據(jù)范圍。最后一步是將這個(gè)范圍轉(zhuǎn)換為pandas DataFrame。下面是一個(gè)簡短的代碼片段，用來循環(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è)簡單一致的格式。在本文的例子中，我們可以很容易地刪除行和列，使之更符合格式要求。然而，有些時(shí)候，這樣做是不可行的，也是不可取的。好消息是，pandas和openpyxl為我們提供了讀取Excel數(shù)據(jù)所需的所有工具。?
