如何在pandas中将数据框追加到同一Excel工作表中
如何在pandas中将数据框追加到同一Excel工作表中
我目前有这段代码。它完美地运行。
它循环遍历文件夹中的Excel文件，
删除前两行，然后将它们保存为单独的Excel文件，
并且还将循环中的文件保存为附加文件。
目前，每次运行代码时，附加文件覆盖现有文件。
我需要将新数据附加到已存在的Excel表格 ('master_data.xlsx') 的底部。
dfList = []
path = 'C:\\Test\\TestRawFile'
newpath = 'C:\\Path\\To\\New\\Folder'
for fn in os.listdir(path):
# 绝对文件路径
file = os.path.join(path, fn)
if os.path.isfile(file):
# 导入Excel文件并称之为xlsx_file
xlsx_file = pd.ExcelFile(file)
# 查看Excel文件的工作表名称
xlsx_file.sheet_names
# 将xlsx文件数据工作表加载为数据帧
df = xlsx_file.parse('Sheet1',header= None)
df_NoHeader = df[2:]
data = df_NoHeader
# 保存单个数据帧
data.to_excel(os.path.join(newpath, fn))
dfList.append(data)
appended_data = pd.concat(dfList)
appended_data.to_excel(os.path.join(newpath, 'master_data.xlsx'))
我以为这将是一个简单的任务，但我猜错了。
我认为我需要将master_data.xlsx文件作为数据帧导入，然后将索引与新附加数据匹配，并将其保存。或者也许有更简单的方法。任何帮助将不胜感激。
Pandas是一个流行的数据处理库，可以用于处理和分析数据。在使用Pandas时，有时需要将多个数据框(DataFrames)附加到同一个Excel工作表中。在Pandas 1.4.0之前的版本中，这个需求并不容易实现。然而，从Pandas 1.4.0开始，官方支持将数据框附加到现有的Excel工作表中。
问题的出现原因是，在Pandas 1.4.0之前的版本中，未提供直接将数据框附加到现有工作表的方法。使用Pandas 1.2.0及更早版本的用户可能会遇到“BadZipFile”异常，导致文件损坏。此外，即使在Pandas 1.3.0及更高版本中，使用默认参数将数据框附加到现有工作表时，新的工作表会被创建，而不是将数据附加到现有工作表中。
为了解决这个问题，可以使用一个帮助函数来附加数据框到现有的Excel文件中。以下是一种方法，可以将数据框附加到现有的工作表中，而不会覆盖原有的内容。
from pathlib import Path
from copy import copy
from typing import Union, Optional
import numpy as np
import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
def copy_excel_cell_range(
src_ws: openpyxl.worksheet.worksheet.Worksheet,
min_row: int = None,
max_row: int = None,
min_col: int = None,
max_col: int = None,
tgt_ws: openpyxl.worksheet.worksheet.Worksheet = None,
tgt_min_row: int = 1,
tgt_min_col: int = 1,
with_style: bool = True
) -> openpyxl.worksheet.worksheet.Worksheet:
# 复制源工作表[src_ws]中从[min_row]行和[min_col]列开始到[max_row]行和[max_col]列的所有单元格
# 到目标工作表[tgt_ws]中，从[tgt_min_row]行和[tgt_min_col]列开始。
if tgt_ws is None:
tgt_ws = src_ws
for row in src_ws.iter_rows(min_row=min_row, max_row=max_row,
min_col=min_col, max_col=max_col):
for cell in row:
tgt_cell = tgt_ws.cell(
row=cell.row + tgt_min_row - 1,
column=cell.col_idx + tgt_min_col - 1,
value=cell.value
)
if with_style and cell.has_style:
tgt_cell.font = copy(cell.font)
tgt_cell.border = copy(cell.border)
tgt_cell.fill = copy(cell.fill)
tgt_cell.number_format = copy(cell.number_format)
tgt_cell.protection = copy(cell.protection)
tgt_cell.alignment = copy(cell.alignment)
return tgt_ws
def append_df_to_excel(
filename: Union[str, Path],
df: pd.DataFrame,
sheet_name: str = 'Sheet1',
startrow: Optional[int] = None,
max_col_width: int = 30,
autofilter: bool = False,
fmt_int: str = "#,##0",
fmt_float: str = "#,##0.00",
fmt_date: str = "yyyy-mm-dd",
fmt_datetime: str = "yyyy-mm-dd hh:mm",
truncate_sheet: bool = False,
storage_options: Optional[dict] = None,
**to_excel_kwargs
) -> None:
# 将DataFrame [df]附加到现有的Excel文件[filename]中的[sheet_name]工作表中。
# 如果[filename]不存在，则将创建一个新文件。
def set_column_format(ws, column_letter, fmt):
for cell in ws[column_letter]:
cell.number_format = fmt
filename = Path(filename)
file_exists = filename.is_file()
first_col = int(to_excel_kwargs.get("index", True)) + 1
if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')
if file_exists:
wb = load_workbook(filename)
sheet_names = wb.sheetnames
sheet_exists = sheet_name in sheet_names
sheets = {ws.title: ws for ws in wb.worksheets}
with pd.ExcelWriter(
filename.with_suffix(".xlsx"),
engine="openpyxl",
mode="a" if file_exists else "w",
if_sheet_exists="new" if file_exists else None,
date_format=fmt_date,
datetime_format=fmt_datetime,
storage_options=storage_options
) as writer:
if file_exists:
writer.book = wb
if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row
if truncate_sheet and sheet_name in writer.book.sheetnames:
idx = writer.book.sheetnames.index(sheet_name)
writer.book.remove(writer.book.worksheets[idx])
writer.book.create_sheet(sheet_name, idx)
writer.sheets = sheets
else:
startrow = 0
df.to_excel(writer, sheet_name=sheet_name, **to_excel_kwargs)
worksheet = writer.sheets[sheet_name]
if autofilter:
worksheet.auto_filter.ref = worksheet.dimensions
for xl_col_no, dtyp in enumerate(df.dtypes, first_col):
col_no = xl_col_no - first_col
width = max(df.iloc[:, col_no].astype(str).str.len().max(),
len(df.columns[col_no]) + 6)
width = min(max_col_width, width)
column_letter = get_column_letter(xl_col_no)
worksheet.column_dimensions[column_letter].width = width
if np.issubdtype(dtyp, np.integer):
set_column_format(worksheet, column_letter, fmt_int)
if np.issubdtype(dtyp, np.floating):
set_column_format(worksheet, column_letter, fmt_float)
if file_exists and sheet_exists:
wb = load_workbook(filename)
new_sheet_name = set(wb.sheetnames) - set(sheet_names)
if new_sheet_name:
new_sheet_name = list(new_sheet_name)[0]
copy_excel_cell_range(
src_ws=wb[new_sheet_name],
tgt_ws=wb[sheet_name],
tgt_min_row=startrow + 1,
with_style=True
)
del wb[new_sheet_name]
wb.save(filename)
wb.close()
使用这个函数，可以将多个数据框附加到同一个Excel工作表中，并保留原有的内容。以下是函数的用法示例：
filename = r'C:\OCC.xlsx' append_df_to_excel(filename, df) append_df_to_excel(filename, df, header=None, index=False) append_df_to_excel(filename, df, sheet_name='Sheet2', index=False) append_df_to_excel(filename, df, sheet_name='Sheet2', index=False, startrow=25)
这个函数在处理大型文件时可能会很慢。如果需要提高性能，可以尝试使用其他方法，例如通过多线程或将数据分片处理。
总结起来，通过使用这个函数，可以方便地将多个数据框附加到同一个Excel工作表中，而不会覆盖原有的内容。这对于需要将多个数据框整理到同一个工作表中进行比较和分析的任务非常有用。