1. 問題描述在日常工作中,我們可能會遇到一些 Excel 文件,其中會有多個 sheet,每個 sheet 中的數據結構都相同,在分析的時候需要合併後再處理。如果文件數據量較小倒還好,萬一遇上幾百幾千萬行的多 sheet 的 Excel 文件,光是打開就要卡死老半天,更不要提處理數據了。本文就如何提高大數據量 Excel 多sheet 文件時的讀取效率,運用 Python 工具進行解決。以如下數據進行舉例說明,同一個 Excel 文件中有 3 個 sheet,其中數據結構都相同:2. 解決方法2.1 對 Excel 文件進行多 sheet 合併讀取方法一:合併同一工作簿中的所有工作表
import pandas as pd
import time
time_start = time.time()
result = pd.DataFrame()
dfs = pd.read_excel('./多sheet.xlsx', sheet_name=None).values()
result = pd.concat(dfs)
print(f'合併後的數據如下:\n{result}')
result.to_excel('./合併後結果.xlsx', index=False, freeze_panes=(1,0))
time_end = time.time()
print('共耗時 {}分 {}秒'.format(int(round((time_end - time_start) / 60,0)),round((time_end - time_start) % 60,2)))
方法二:循環讀取同一工作簿中的所有工作表time_start = time.time()
dfs = pd.read_excel('./多sheet.xlsx', sheet_name=None)
keys = list(dfs.keys())
# print(keys)
result = pd.DataFrame()
num = 1
for i in keys:
df = dfs[i]
print(f'第{num}個 sheet 的數據如下:\n{df}')
result = pd.concat([result,df])
result.to_excel('./循環合併後結果.xlsx', index=False, freeze_panes=(1,0))
num += 1
print(f'循環合併後的數據如下:\n{result}')
time_end = time.time()
print('共耗時 {}分 {}秒'.format(int(round((time_end - time_start) / 60,0)),round((time_end - time_start) % 60,2)))
2.2 大規模 Excel 文件數據讀取:使用 usecols 參數指定列方法一:使用列表 - 欄位順序數字(從 0 開始)df1 = pd.read_excel('./多sheet.xlsx', sheet_name='Sheet1',usecols=[0,1,2])
print(f'使用列表 - 欄位順序數字後的數據如下:\n{df1}')
df1.to_excel('./usecols參數示例1.xlsx', index=False, freeze_panes=(1,0))
方法二:使用列表 - 欄位名稱文本df2 = pd.read_excel('./多sheet.xlsx', sheet_name='Sheet2',usecols=['來源','名稱'])
print(f'使用列表 - 欄位名稱文本後的數據如下:\n{df2}')
df2.to_excel('./usecols參數示例2.xlsx', index=False, freeze_panes=(1,0))
方法三:使用 Excel 欄位英文字母(可用冒號 ':' 進行連續範圍切片選擇,僅支持 pandas 1.1.0 以上版本)df3 = pd.read_excel('./多sheet.xlsx', sheet_name='Sheet3',usecols='A,C:D')
print(f'使用 Excel 欄位英文字母後的數據如下:\n{df3}')
df3.to_excel('./usecols參數示例3.xlsx', index=False, freeze_panes=(1,0))
方法四:使用自定義函數def filter_yield(col_name):
if '名' in col_name:
return col_name
df4 = pd.read_excel('./多sheet.xlsx', sheet_name='Sheet3',usecols=filter_yield)
print(f'使用自定義函數後後的數據如下:\n{df4}')
df4.to_excel('./usecols參數示例4.xlsx', index=False, freeze_panes=(1,0))
2.3 大規模 Excel 文件數據讀取:使用 dtype 參數指定數據類型# 使用字典(dict)的形式
print(f'數據類型修改前:\n{result.info()}')
result = pd.read_excel('./多sheet.xlsx', sheet_name='Sheet1',dtype={'數量':'str'})
print('=======================================================================================')
print(f'數據類型修改後:\n{result.info()}')
print('=======================================================================================')
2.4 大規模 Excel 文件數據讀取:使用 skiprows、skipfooter、nrows 參數指定部分行df = pd.read_excel('./多sheet.xlsx', sheet_name='Sheet1',skiprows=range(1, 3),nrows=2)
print(f'指定跳過行數後的數據如下:\n{df}')
df.to_excel('./skiprows+nrows參數示例.xlsx', index=False, freeze_panes=(1,0))
2.5 大規模 Excel 文件數據讀取:使用 dask 庫,不支持 Excel 文件格式由於 「dask」 庫「不支持 Excel 文件格式」,所以我把 Excel 格式先「轉換成 CSV 文件格式」。result = pd.DataFrame()
dfs = pd.read_excel('./【數據集】近一年訂購大分類.xlsx', sheet_name=None)
keys = list(dfs.keys())
# print(keys)
result = pd.DataFrame()
for i in keys:
df = dfs[i]
result = pd.concat([result,df])
result.to_csv('./近一年訂購大分類.csv', index=False,encoding='utf-8')
print('Excel 轉換 CSV 完成!!')
然後再使用 dask 庫對大規模數據的 CSV 文件進行讀取。import dask.dataframe as ddfrom tqdm import tqdmtime_start = time.time()
df = dd.read_csv('./近一年訂購大分類.csv')
print(f'合併後的數據如下:\n{df}')
print(df.groupby(df.顧客編號).mean().compute())
time_end = time.time()
print('使用 dask 庫讀取數據共耗時 {}分 {}秒'.format(int(round((time_end - time_start) / 60,0)),int(round((time_end - time_start) % 60,0))))
2.6 大規模 Excel 文件數據讀取:使用 chunksize 參數:指定分塊讀取的數據量df = pd.read_csv('./近一年訂購大分類.csv',chunksize=1000)
result = (pd.concat([chunk.groupby(['顧客編號'], as_index=False).agg({'累計訂購數量': 'sum','累計訂購金額': 'sum'}) for chunk in tqdm(df)]).groupby(['顧客編號']).agg({'累計訂購數量': 'sum','累計訂購金額': 'sum'}))
print(f'指定跳過行數後的數據如下:\n{result}')
result.to_csv('./chunksize參數示例.csv', index=False)
3. 小技巧4. 資料下載我已將以上配套「玩具數據集」和代碼文件打包上傳至我的 Github 和 Gitee,感興趣的讀者可以下載學習和練手。「Github 項目地址」:https://github.com/don2vito/wechat_project.git「Gitee 項目地址」:https://gitee.com/don2vito/wechat_official_account.git歡迎關注