關于Excel資料處理，很多同學可能使用過Pyhton的pandas子產品，用它可以輕松地讀取和轉換Excel資料。但是實際中Excel表格結構可能比較雜亂，資料會分散不同的工作表中，而且在表格中分布很亂，這種情況下啊直接使用pandas就會非常吃力。本文蟲蟲給大家介紹使用pandas和openpyxl讀取這樣的資料的方法。
問題緣起
pandas read_excel函數在讀取Excel工作表方面做得很好。但是，如果資料不是從頭開始，不是從單元格A1開始的連續表格，則結果會不是很好。比如下面一個銷售表，使用read_excel讀取：
讀取的結果如下所示：
結果中标題表頭變成了Unnamed，而且還會額外增加很多職位NaN列，字段為空的列的值也會被轉換為NaN，這顯然不是我們所期望的。
header和usecols參數
對這樣的非标準格式的表格，我們可以使用read_excel()的header和usecols參數來控制選擇的需要讀取的列。
import pandas as pdfrom pathlib import Pathsrc_file = 'sales.xlsx'
df = pd.read_excel(src_file, header=1, usecols='B:F')
結果的DataFrame包含了我們期望的資料。
代碼中使用header和usecols參數設定了用于顯示标題的列和需要讀取的字段：
header參數為一個整數，從0開始索引，其為選擇的行，比如1表示Excel中的第2行。
usecols參數設定選擇的Excel列範圍範圍(A-…)，例如，B:F表示讀取B到F列。
在某些情況下，可能希望将列定義為數字清單。比如，可以定義整數列數：
df = pd.read_excel(src_file, header=1, usecols=[1,2,3,4,5])
這對對大型資料集(例如，每3列或僅偶數列)要遵循一定的數字模式，則這個參數方法會很有用。
usecols還可以設定從列名清單讀取。比如上面的例子也可以這樣寫：
df = pd.read_excel(src_file,header=1,usecols=['item_type', 'order id', 'order date', 'state', 'priority'])
列順序支援自由選擇，這種命名列清單的方式實際中很有用。
usecols支援一個回調函數column_check，可通過該函數對資料進行處理。
下面是一個簡單的示例：
def column_check(x): if 'unnamed' in x.lower(): return False if 'priority' in x.lower(): return False if 'order' in x.lower(): return True return True
df = pd.read_excel(src_file, header=1, usecols=column_check)
column_check按名稱解析每列，每列通過定義True或False，來選擇是否讀取。
usecols也可以使用lambda表達式。下面的示例中定義的需要顯示的字段清單。為了進行比較，通過将名稱轉換為小寫來規範化。
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)
回調函數為我們提供了許多靈活性，可以處理Excel檔案的實際混亂情況。
關于read_exce函數更多參數可以檢視官方文檔，下面是一個總結表格：
結合openpyxl
在某些情況下，資料甚至可能在Excel中變得更加複雜。在下面示例中，我們有一個ship_cost要讀取的表。如果必須使用這樣的檔案，那麼隻用pandas函數和選項也很難做到。在這種情況下，可以直接使用openpyxl解析檔案并将資料轉換為pandas DataFrame。比如要讀取下面示例的資料：
from openpyxl import load_workbookimport pandas as pdfrom pathlib import Pathsrc_file = ' sales1.xlsx'
加載整個工作簿：
cc = load_workbook(filename = src_file)
檢視所有工作表：
cc.sheetnames
['sales', 'shipping_rates']
要通路特定的工作表：
sheet = cc['shipping_rates']
要檢視所有命名表的清單：
sheet.tables.keys()
dict_keys(['ship_cost'])
該鍵對應于Excel中配置設定給表的名稱。這樣就可以設定要讀取的Excel範圍：
lookup_table = sheet.tables['ship_cost']
lookup_table.ref
'C8:E16'
這樣就獲得了要加載的資料範圍。最後将其轉換為pandas DataFrame即可。周遊每一行并轉換為DataFrame：
data = sheet[lookup_table.ref]rows_list = []for row in data:cols = []for col in row:cols.append(col.value)rows_list.append(cols)df = pd.DataFrame(data=rows_list[1:], index=None, columns=rows_list[0])
結果資料框：
總結
在理想情況下，使用的資料将采用簡單一緻的格式。在本文中，我們介紹了在Pandas下通過參數輕松删除行和列以使其格式更加合理。尤其是結合openpyxl的情況下可以讓我們讀取Excel資料更加靈活，可以處理比較複雜的表格資料。