Python中各類Excel表格批量合并問題的實現(xiàn)思路與案例
在日常工作中,可能會遇到各類表格合并的需求。這類需求只要搞懂核心原理都很簡單,本質(zhì)都是萬變不離其宗,相信大部分讀者都能解決大部分需求。
基本思路:
- 遍歷需要被合并的文件
- 讀取數(shù)據(jù),并合并數(shù)據(jù)(使用pandas最簡單便捷)
- 保存數(shù)據(jù)
對樣式無要求,使用Pandas對象直接寫出
對樣式有要求,使用openpyxl加載模板
要求樣式與原始表格完全一致,使用VBA復(fù)制粘貼(本文未實現(xiàn))
首先我們看下遍歷文件比較簡單的方法:
遍歷文件示例
遍歷當(dāng)前目錄下以xlsx為后綴的Excel,排除以~或r開頭的文件:
from glob import glob glob("[!~r]*.xlsx")
['合并結(jié)果.xlsx', '多sheet表格合并.xlsx', '帶表頭樣式合并.xlsx']
同時還想包含xls格式的文件:
glob("[!~r]*.xls*")
['test.xls', '合并結(jié)果.xlsx', '多sheet表格合并.xlsx', '帶表頭樣式合并.xlsx']
遞歸遍歷當(dāng)前文件夾,包含子文件夾:
glob("**/[!~r]*.xls*", recursive=True)
['test.xls', '合并結(jié)果.xlsx', '多sheet表格合并.xlsx', '帶表頭樣式合并.xlsx', 'Excel多sheet合并\\excel3.xlsx', 'Excel多sheet合并\\excel4.xlsx', 'Excel多sheet合并\\新建文件夾\\excel3.xlsx', 'Excel多sheet合并\\新建文件夾\\excel4.xlsx', 'Excel多sheet合并\\新建文件夾\\新建文件夾\\excel3.xlsx', 'Excel多sheet合并\\新建文件夾\\新建文件夾\\excel4.xlsx', '帶樣式合并\\HB區(qū).xlsx', '帶樣式合并\\HN區(qū).xlsx', '帶樣式合并\\XN區(qū).xlsx', '帶樣式合并\\匯總表.xlsx']
遞歸遍歷指定文件夾(例如搜索本機所有登錄過的微信接收到的Excel文件):
import os path = os.path.expanduser("~/Documents/WeChat Files") glob(f"{path}/**/[!~r]*.xls*", recursive=True)
掌握了遍歷文件的基本用法,我們就可以正式開始進行文件合并了:
無樣式單文件合并示例
案例1:有一堆gzip壓縮的csv文件,需要合并成新的csv文件
解壓后的文本格式:
合并一堆gzip壓縮的csv文件最終合并成一個gzip壓縮的csv文件:
from glob import glob import pandas as pd dfs = [pd.read_csv(file, skiprows=1, sep="|", compression="gzip") for file in glob("gzip/*.csv.gz")] df = pd.concat(dfs, ignore_index=True) df.to_csv("合并后的csv壓縮文件.csv.gz", index=False, compression="gzip")
最終合并結(jié)果:
案例2:一堆csv文件,只取其中三列,表名不固定,但相對順序一致
from glob import glob import pandas as pd import numpy as np columns = ['Date_ID', 'erbs', 'EUtranCell'] dfs = [pd.read_csv(file, usecols=[0, 2, 3]).values for file in glob("csv/*.csv")] df = pd.DataFrame(np.vstack(dfs), columns=columns) df.to_csv("合并后的csv文件.csv", index=False)
案例3:一堆csv文件,列非常多,僅一列列名存在變動
import pandas as pd import glob dfs = [ pd.read_csv(file).rename( columns=lambda x:"EUtranCell" if x.startswith("EUtranCell") else x) for file in glob.glob("csv/*.csv") ] df = pd.concat(dfs, ignore_index=True) df.to_csv("合并后的csv文件2.csv", index=False)
其他方法(一般不會這么寫):
在預(yù)先能夠定義好列名時,推薦以下兩種寫法:
案例4:寄存器數(shù)據(jù)處理并合并
需求說明:
- 需要讀取0xa17~0xa20這10個寄存器的數(shù)據(jù),前4個寄存器數(shù)據(jù)保存到R,GR,RB,B這四列中
- 0xa1b~0xa20后6個寄存器,兩兩合并到RG_L_H,BG_L_H,GG_L_H這三列中
- 標(biāo)識每行數(shù)據(jù)所讀取的文件名
如下所示:
特殊情況:
為了方便獲取文件名,我們使用pathlib來進行g(shù)lob遍歷:
import pandas as pd from pathlib import Path result = [] for file in Path("csv/PT0004B_LOG").glob("*.csv"): df = pd.read_csv(file, header=None, usecols=[1, 2], index_col=0) t = df[2].str[2:] r = [str(file.name[:-4])] r.extend(t.loc["0xa17":"0xa1a"].values) r.extend(t.loc["0xa1c":"0xa20":2].values + t.loc["0xa1b":"0xa20":2].str.zfill(2).values) result.append(r) df = pd.DataFrame( result, columns=["file", "R", "GR", "GB", "B", "RG_L_H", "BG_L_H", "GG_L_H"]) df.to_excel("combine.xlsx", index=False)
df.head()
無樣式同名多sheet表格合并
如果只遞歸合并一個文件夾下的所有Excel的默認sheet,會非常簡單,僅需4行代碼搞定:
path = "Excel多sheet合并" dfs = [pd.read_excel(file) for file in glob.glob(f"{path}/**/[!~]*.xls*", recursive=True)] df = pd.concat(dfs, ignore_index=True) df.to_excel("合并結(jié)果.xlsx", index=False)
下面要求對一個文件夾下所有Excel表格,要求所有的sheet分別合并。
數(shù)據(jù)示例如下:
假設(shè)被合并的文件夾名稱是Excel多sheet合并
,合并代碼如下:
import pandas as pd from glob import glob path = "Excel多sheet合并" data = {} for file in glob(f"{path}/**/[!~]*.xls*", recursive=True): for name, df in pd.read_excel(file, sheet_name=None).items(): data.setdefault(name, []).append(df) with pd.ExcelWriter("多sheet表格合并.xlsx") as write: for name, dfs in data.items(): pd.concat(dfs).to_excel(write, name, index=False)
合并結(jié)果:
保留表頭樣式同名多sheet表格合并
如果要求完全帶有原有樣式合并會比較麻煩,本文就不作演示了,存在具體真實需求時再考慮單獨開文。
需求說明:
有很多區(qū)域表:
現(xiàn)在需要將每個區(qū)域的表格黃色的sheet合并到一張匯總表中。
為了保持表頭樣式的一致性,我們可以事先建立好模板,或者直接任選一個被合并的文件作為模板。
首先我們讀取數(shù)據(jù):
import pandas as pd from glob import glob path = "帶樣式合并" # 定義被讀取的sheet名和跳過的行數(shù) sheet_start_num = {'03': 3, '06': 2, '07': 3, '08': 3} data = {} for file in glob(f"{path}/**/[!~r匯]*.xls*", recursive=True): for sheet_name, skiprows in sheet_start_num.items(): excel = pd.ExcelFile(file) df = excel.parse(sheet_name=sheet_name, skiprows=skiprows, header=None) data.setdefault(sheet_name, []).append(df.values)
然后通過openpyxl加載模板,將數(shù)據(jù)寫入各個子表中:
from openpyxl import load_workbook workbook = load_workbook(filename="帶樣式合并/匯總表.xlsx") for sheet_name, sheet_data in data.items(): sheet = workbook[sheet_name] sheet.delete_rows(sheet_start_num[sheet_name] + 1, sheet.max_row) for row in np.vstack(sheet_data).tolist(): sheet.append(row) workbook.save(filename="帶表頭樣式合并.xlsx")
最終就實現(xiàn)了帶表頭樣式多sheet合并。
圖形化界面選擇指定的目錄
如果我們希望將其做成圖形化界面,可以使用tk的如下組件選擇被合并的目錄,或保存的位置:
from tkinter import filedialog filedialog.askdirectory(initialdir=".")
filedialog.asksaveasfilename(title="保存", initialdir=".", defaultextension="xlsx", filetypes=[("Excel 工作簿", "*.xlsx"), ("Excel 97-2003 工作簿", "*.xls")])
我們以多Excel多Sheet合并為例,可以編寫如下代碼的py腳本:
from tkinter import filedialog import pandas as pd from glob import glob path = filedialog.askdirectory(initialdir=".") data = {} for file in glob(f"{path}/**/[!~]*.xls*", recursive=True): for name, df in pd.read_excel(file, sheet_name=None).items(): data.setdefault(name, []).append(df) save_name = filedialog.asksaveasfilename(title="保存", initialdir=".", defaultextension="xlsx", filetypes=[("Excel 工作簿", "*.xlsx"), ("Excel 97-2003 工作簿", "*.xls")]) with pd.ExcelWriter(save_name) as write: for name, dfs in data.items(): pd.concat(dfs).to_excel(write, name, index=False)
也可以考慮使用Gooey工具轉(zhuǎn)換為圖形化界面:
from glob import glob import pandas as pd from gooey import Gooey, GooeyParser def combine_excel(path, save_name): data = {} for file in glob(f"{path}/**/[!~]*.xls*", recursive=True): for name, df in pd.read_excel(file, sheet_name=None).items(): data.setdefault(name, []).append(df) with pd.ExcelWriter(save_name) as write: for name, dfs in data.items(): pd.concat(dfs).to_excel(write, name, index=False) @Gooey def main(): parser = GooeyParser(description="多Excel多Sheet合并程序 - @小小明") parser.add_argument('path', help="被合并的Excel文件目錄", widget="DirChooser") parser.add_argument('save_name', help="合并后保存的文件(以Excel文件形式保存)", widget="FileSaver") args = parser.parse_args() print("輸入路徑:", args.path) print("保存位置:", args.save_name) combine_excel(args.path, args.save_name) print("合并完成!") if __name__ == '__main__': main()
還可以通過Gooey展示合并進度:
from glob import glob import pandas as pd from gooey import Gooey, GooeyParser def combine_excel(path, save_name): data = {} files = glob(f"{path}/**/[!~]*.xls*", recursive=True) for i, file in enumerate(files, 1): for name, df in pd.read_excel(file, sheet_name=None).items(): data.setdefault(name, []).append(df) yield f"合并進度:{i}/{len(files)}" with pd.ExcelWriter(save_name) as write: items = data.items() for i, (name, dfs) in enumerate(items, 1): pd.concat(dfs).to_excel(write, name, index=False) yield f"保存進度:{i}/{len(items)}" @Gooey(progress_regex=r"^..進度:(?P<current>\d+)/(?P<total>\d+)$", progress_expr="current / total * 100", timing_options={ 'show_time_remaining': False, 'hide_time_remaining_on_complete': True, }) def main(): parser = GooeyParser(description="多Excel多Sheet合并程序 - @小小明") parser.add_argument('path', help="被合并的Excel文件目錄", widget="DirChooser") parser.add_argument('save_name', help="合并后保存的文件(以Excel文件形式保存)", widget="FileSaver") args = parser.parse_args() print("輸入路徑:", args.path) print("保存位置:", args.save_name) for msg in combine_excel(args.path, args.save_name): print(msg) print("合并完成!") if __name__ == '__main__': main()
到此這篇關(guān)于Python中各類Excel表格批量合并問題的實現(xiàn)思路與案例的文章就介紹到這了,更多相關(guān)Python Excel表格批量合并內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
windows下python 3.9 Numpy scipy和matlabplot的安裝教程詳解
這篇文章主要介紹了windows下python 3.9 Numpy scipy和matlabplot的安裝教程詳解,本文給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2020-11-11python統(tǒng)計文本文件內(nèi)單詞數(shù)量的方法
這篇文章主要介紹了python統(tǒng)計文本文件內(nèi)單詞數(shù)量的方法,涉及Python針對文本文件及字符串的相關(guān)操作技巧,需要的朋友可以參考下2015-05-05