使用Python對Excel多sheet合併與大規模數據集讀取,看這一篇就夠了

2021-02-14 數海隨記
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 tqdm

time_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歡迎關注

相關焦點

  • Python數據分析:pandas讀取和寫入數據
    我的公眾號是關於自己在數據分析/挖掘學習過程中的一些技術和總結分享,文章會持續更新......繼續深入學習pandas相關操作,數據讀取寫入、分組、合併,轉換等等。前面一篇文章裡已經寫了關於描述性統計以及常用的基本操作。接下來的一段時間裡,我將陸續地去掌握並輸出。這篇文章是關於數據讀取與寫入的知識點。
  • 如何從excel中讀取多個工作表
    近期有學員學習《python文本分析:從入門到到精通》第四部分時,後臺問如果要處理的excel數據是含有多個sheet表,我們該怎麼處理。
  • 基於python的大數據分析-pandas數據讀取(代碼實戰)
    長按上方二維碼加入千人QQ群基於python的大數據分析實戰學習筆記-Anaconda基於python的大數據分析實戰學習筆記-pandas(數據分析包)基於python的大數據分析實戰學習筆記-pandas之DataFrame我們常見的數據存儲格式無非就是csv、excel、txt以及資料庫等形式
  • 手把手教你4種方法用Python批量實現多Excel多Sheet合併
    前兩天給大家分享了Python自動化文章:手把手教你利用Python輕鬆拆分Excel為多個CSV文件,而後在Python進階交流群裡邊有讀者遇到一個問題,他有很多個Excel表格,他需要對這些Excel文件進行合併。
  • Python操作Excel,將匯總數據分到不同sheet
    方法一:讀取原Excel,根據country列將不同的內容放到不同的sheet,並根據國家名稱命名,將結果放到新的輸出文件中。import pandas as pdinput_file = "F://python入門//數據2//appname_test.xlsx"output_file = "F://python入門//數據2//output.xlsx"data_frame = pd.read_excel(input_file,sheet_name='sum1'
  • Python讀寫Excel表格,就是這麼簡單粗暴又好用
    最近在做一些數據處理和計算的工作,因為數據是以.csv格式保存的,因此剛開始直接用Excel來處理。但是做著做著發現重複的勞動其實並沒有多大的意義,於是就想著寫個小工具幫著處理。以前正好在一本書上看到過使用Python來處理Excel表格,可惜沒有仔細看。於是我到處查找資料,基本解決了日常所需,終於算是完成了任務,因此撰寫此文就算是總結吧,主要記錄使用過程的常見問題及解決。
  • 如何使用 Pandas 讀寫Excel
    Pandas是一個基於Numpy專業的數據結構化分析工具,可用於數據挖掘和數據分析,同時也提供數據清洗功能。其中的數據框格式將非常有利於我們對Excel表格的處理,相關介紹可以點擊上一篇內容。今天的推送主要內容圍繞讀取表格及一些參數使用,同時也徵集一下大家的使用經驗,歡迎留言。
  • 使用Pandas讀取複雜的Excel數據
    關於Excel數據處理,很多同學可能使用過Pyhton的pandas模塊,用它可以輕鬆地讀取和轉換Excel數據。但是實際中Excel表格結構可能比較雜亂,數據會分散不同的工作表中,而且在表格中分布很亂,這種情況下啊直接使用pandas就會非常吃力。本文蟲蟲給大家介紹使用pandas和openpyxl讀取這樣的數據的方法。
  • Pandas進階Excel(一)——讀取
    今天開始介紹python可以操作Excel的另一個強大的庫——pandas庫。個人認為,pandas庫對於操作Excel有著極好的支撐。在數據導入、數據清洗、數據計算、數據導出都有著完整性的支撐,是一個提供高性能易用數據類型和分析工具,並且用一段時間你就會發現如果拿pandas只操作表格數據,是真的大材小用。 它不僅可以處理數據,更可以可視化數據。譬如可以做出這樣的圖表。
  • Python讀寫Excel表格,就是這麼簡單粗暴又好用(文末送書)
    來源 | 戀習Python(ID:sldata2017)最近在做一些數據處理和計算的工作,因為數據是以.csv格式保存的,因此剛開始直接用Excel來處理。但是做著做著發現重複的勞動其實並沒有多大的意義,於是就想著寫個小工具幫著處理。以前正好在一本書上看到過使用Python來處理Excel表格,可惜沒有仔細看。
  • Python讀取CSV和Excel
    CSV逗號分隔值(Comma-Separated Values,CSV,有時也稱為字符分隔值,因為分隔字符也可以不是逗號),其文件以純文本形式存儲表格數據(數字和文本)。純文本意味著該文件是一個字符序列,不含必須像二進位數字那樣被解讀的數據。
  • 如何用python實現excel中的vlookup功能?
    因為刀哥是python初學者,對於需要用到的知識點,如果以前學過的要再複習一下,年紀大了嘛記性比較差,沒學過的新知識點要先學習一下,邊學邊用,所以做的速度比較慢,但是好在,哪怕慢,只要每天進步一點點,都是好的。今天這篇分享,就是刀哥在做的過程中,遇到的其中一個知識點,即用python來實現excel中的vlookup函數功能。
  • 14-用Python 讀寫 Excel 文件
    可以完成xlsx文件的自動化構造,包括:合併單元格,製作excel圖表等功能:1.優點一、功能比較強相對而言,這是除Excel自身之外功能最強的工具了。二、支持大文件寫入如果數據量非常大,可以啟用constant memory模式,這是一種順序寫入模式,得到一行數據就立刻寫入一行,而不會把所有的數據都保持在內存中。2.缺點一、不支持讀取和修改作者並沒有打算做一個XlsxReader來提供讀取操作。不能讀取,也就無從修改了。
  • 利用pandas讀取格式不規範的Excel文件
    ,在那些數據分散在不同Sheet的情況下,就需要自定義讀取數據的方式,這篇文章將討論如何用pandas和openpyxl讀取這類格式的Excel文件,將裡面的數據轉換為DataFrame以便進一步的分析工作。
  • 一文掌握Excel、PowerBI、SQL、Python【數據合併】大法
    如果看文章不舒服的話,可以直接滑到文末看視頻講解,希望你能喜歡~       在工作中,會經常遇到將多張表合併為一張表的需求;面試的時候,數據合併方法、左連接、右連接、內連接的區別也經常會被面試官問到。
  • Python自動化測試—使用Pandas來高效處理測試數據
    >df = pd.read_excel('lemon_cases.xlsx', sheet_name='multiply')print(df)# 1.讀取一列數據# df["title"] 返回一個Series對象,記錄title這列的數據print(df["title"])# Series對象能轉化為任何序列類型和dict
  • 如何讀取文本數據
    如何將數據讀取到程序中?做數據處理的同學們,肯定會遇到這個問題。因為這通常是我們進行數據處理的第一步。
  • 乾貨 | 用 pandas 讀取 csv 和 Excel 數據
    接下來我們將使用 Python 中的 pandas 數據分析包來進行數據的讀取和查看。讀取的數據需要能還原原始數據中的信息,比如 Last Update 應該是時間格式的數據,而在 IPO Year 中存在 NAN這類缺失的數值,這些目前都沒有反應出來。所以下面需要設置參數,改進csv文件的讀取方式。
  • Matlab—Excel數據的讀取
  • PDF轉EXCEL,python的這個技能知道嗎?
    當在pdf上看到自己想用的表格,卻無法將其複製下來的時候,只能默默地打開excel對照著pdf表格的形式敲打出來,既費時又費力!這裡介紹如何用python程序將pdf上的表格自動轉化為excel表!1.使用的庫簡單介紹一下要使用的庫:pdfplumber 和xlwt1.pdfplumberpdfplumber使用來解析pdf的文字與表格。