Ce site est optimisé pour être consulté depuis un navigateur moderne dans lequel JavaScript est activé.

Append existing excel sheet avec new dataframe using python pandas

ayi

I currently have this code. It works perfectly.
It loops through excel files in a folder, 
removes the first 2 rows, then saves them as individual excel files, 
and it also saves the files in the loop as an appended file.
Currently the appended file overwrites the existing file each time I run the code.
I need to append the new data to the bottom of the already existing excel sheet ('master_data.xlsx)
<pre><code class="lang-auto">dfList = []
path = 'C:\Test\TestRawFile'
newpath = 'C:\Path\To\New\Folder'

for fn in os.listdir(path):

Absolute file path

file = os.path.join(path, fn)
if os.path.isfile(file):

Import the excel file and call it xlsx_file

xlsx_file = pd.ExcelFile(file)

View the excel files sheet names

xlsx_file.sheet_names

Load the xlsx files Data sheet as a dataframe

df = xlsx_file.parse('Sheet1',header= None)
df_NoHeader = df[2:]
data = df_NoHeader

Save individual dataframe

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'))

</code></pre>
I thought this would be a simple task, but I guess not. 
I think I need to bring in the master_data.xlsx file as a dataframe, then match the index up with the new appended data, and save it back out. Or maybe there is an easier way. Any Help is appreciated.
<hr>
Source : <a href="https://pandas.pydata.org/docs/dev/reference/api/pandas.ExcelWriter.html" rel="noopener nofollow ugc">Stack Overflow</a><img src="https://forum-microsoft.fr/images/emoji/twitter/slight_smile.png?v=15" title=":slight_smile:" class="emoji only-emoji" alt=":slight_smile:" loading="lazy" width="20" height="20">

ayi_2

<h2><a name="p-34972-update-2022-01-08-starting-from-version-140-pandas-supports-appending-to-existing-excel-sheet-preserving-the-old-contents-out-of-the-box-1" class="anchor" href="#p-34972-update-2022-01-08-starting-from-version-140-pandas-supports-appending-to-existing-excel-sheet-preserving-the-old-contents-out-of-the-box-1" aria-label="Heading link"></a>UPDATE [2022-01-08]: starting from version 1.4.0 Pandas supports appending to existing Excel sheet, preserving the old contents, “out of the box”!</h2>
Good job Pandas Team!
Excerpt from the <a href="https://pandas.pydata.org/docs/dev/reference/api/pandas.ExcelWriter.html">ExcelWriter documentation</a>:
<blockquote></blockquote>
<pre><code class="lang-auto">if_sheet_exists : {'error', 'new', 'replace', 'overlay'}, default 'error'
How to behave when trying to write to a sheet that already
exists (append mode only).
...
* overlay: Write contents to the existing sheet without removing the old contents.

.. versionadded:: 1.3.0
.. versionchanged:: 1.4.0
   Added ``overlay`` option

</code></pre>
For Pandas versions < 1.4.0 please find below a helper function for appending a Pandas DataFrame to an existing Excel file.
If an Excel file doesn’t exist then it will be created.
<h2><a name="p-34972-update-2021-09-12-fixed-for-pandas-130-2" class="anchor" href="#p-34972-update-2021-09-12-fixed-for-pandas-130-2" aria-label="Heading link"></a>UPDATE [2021-09-12]: fixed for Pandas 1.3.0+</h2>
The following functions have been tested with:
<ul>
<li>
Pandas 1.3.2
</li>
<li>
OpenPyxl 3.0.7
</li>
</ul>
`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: 
“”" 
copies all cells from the source worksheet [src_ws] starting from [min_row] row 
and [min_col] column up to [max_row] row and [max_col] column 
to target worksheet [tgt_ws] starting from [tgt_min_row] row 
and [tgt_min_col] column.
<pre><code>@param src_ws: source worksheet
@param min_row: smallest row index in the source worksheet (1-based index)
@param max_row: largest row index in the source worksheet (1-based index)
@param min_col: smallest column index in the source worksheet (1-based index)
@param max_col: largest column index in the source worksheet (1-based index)
@param tgt_ws: target worksheet.
If None, then the copy will be done to the same (source) worksheet.
@param tgt_min_row: target row index (1-based index)
@param tgt_min_col: target column index (1-based index)
@param with_style: whether to copy cell style. Default: True

@return: target worksheet object
"""
if tgt_ws is None:
tgt_ws = src_ws

https://stackoverflow.com/a/34838233/5741205

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.style = copy(cell.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
</code></pre>
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: 
“”" 
Append a DataFrame [df] to existing Excel file [filename] 
into [sheet_name] Sheet. 
If [filename] doesn’t exist, then this function will create it.
<pre><code>@param filename: File path or existing ExcelWriter
(Example: '/path/to/file.xlsx')
@param df: DataFrame to save to workbook
@param sheet_name: Name of sheet which will contain DataFrame.
(default: 'Sheet1')
@param startrow: upper left cell row to dump data frame.
Per default (startrow=None) calculate the last row
in the existing DF and write to the next row...
@param max_col_width: maximum column width in Excel. Default: 40
@param autofilter: boolean - whether add Excel autofilter or not. Default: False
@param fmt_int: Excel format for integer numbers
@param fmt_float: Excel format for float numbers
@param fmt_date: Excel format for dates
@param fmt_datetime: Excel format for datetime's
@param truncate_sheet: truncate (remove and recreate) [sheet_name]
before writing DataFrame to Excel file
@param storage_options: dict, optional
Extra options that make sense for a particular storage connection, e.g. host, port,
username, password, etc., if using a URL that will be parsed by fsspec, e.g.,
starting “s3://”, “gcs://”.
@param to_excel_kwargs: arguments which will be passed to DataFrame.to_excel()
[can be a dictionary]
@return: None

Usage examples:

append_df_to_excel('/tmp/test.xlsx', df, autofilter=True,
freeze_panes=(1,0))

append_df_to_excel('/tmp/test.xlsx', df, header=None, index=False)

append_df_to_excel('/tmp/test.xlsx', df, sheet_name='Sheet2',
index=False)

append_df_to_excel('/tmp/test.xlsx', df, sheet_name='Sheet2',
index=False, startrow=25)

append_df_to_excel('/tmp/test.xlsx', df, index=False,
fmt_datetime="dd.mm.yyyy hh:mm")

(c) MaxU
"""
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()

process parameters

calculate first column number

if the DF will be written using `index=True`, then `first_col = 2`, else `first_col = 1`

first_col = int(to_excel_kwargs.get("index", True)) + 1

ignore [engine] parameter if it was passed

if 'engine' in to_excel_kwargs:
to_excel_kwargs.pop('engine')

save content of existing sheets

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:

try to open an existing workbook

writer.book = wb

get the last row in the existing Excel sheet

if it was not specified explicitly

if startrow is None and sheet_name in writer.book.sheetnames:
startrow = writer.book[sheet_name].max_row

truncate sheet

if truncate_sheet and sheet_name in writer.book.sheetnames:

index of [sheet_name] sheet

idx = writer.book.sheetnames.index(sheet_name)

remove [sheet_name]

writer.book.remove(writer.book.worksheets[idx])

create an empty sheet [sheet_name] using old index

writer.book.create_sheet(sheet_name, idx)

copy existing sheets

writer.sheets = sheets
else:

file doesn't exist, we are creating a new one

startrow = 0

# write out the DataFrame to an ExcelWriter
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:

move (append) rows from new worksheet to the `sheet_name` worksheet

wb = load_workbook(filename)

retrieve generated worksheet name

new_sheet_name = set(wb.sheetnames) - set(sheet_names)
if new_sheet_name:
new_sheet_name = list(new_sheet_name)[0]

copy rows written by `df.to_excel(...)` to

copy_excel_cell_range(
src_ws=wb[new_sheet_name],
tgt_ws=wb[sheet_name],
tgt_min_row=startrow + 1,
with_style=True
)

remove new (generated by Pandas) worksheet

del wb[new_sheet_name]
wb.save(filename)
wb.close()
</code></pre>
<pre><code class="lang-auto">

Old version (tested with Pandas 1.2.3 and Openpyxl 3.0.5):

`import os
from openpyxl import load_workbook

def append_df_to_excel(filename, df, sheet_name='Sheet1', startrow=None,
truncate_sheet=False,
**to_excel_kwargs):
"""
Append a DataFrame [df] to existing Excel file [filename]
into [sheet_name] Sheet.
If [filename] doesn't exist, then this function will create it.

@param filename: File path or existing ExcelWriter
                 (Example: '/path/to/file.xlsx')
@param df: DataFrame to save to workbook
@param sheet_name: Name of sheet which will contain DataFrame.
                   (default: 'Sheet1')
@param startrow: upper left cell row to dump data frame.
                 Per default (startrow=None) calculate the last row
                 in the existing DF and write to the next row...
@param truncate_sheet: truncate (remove and recreate) [sheet_name]
                       before writing DataFrame to Excel file
@param to_excel_kwargs: arguments which will be passed to `DataFrame.to_excel()`
                        [can be a dictionary]
@return: None

Usage examples:

>>> append_df_to_excel('d:/temp/test.xlsx', df)

>>> append_df_to_excel('d:/temp/test.xlsx', df, header=None, index=False)

>>> append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2',
                       index=False)

>>> append_df_to_excel('d:/temp/test.xlsx', df, sheet_name='Sheet2', 
                       index=False, startrow=25)

(c) [MaxU](https://stackoverflow.com/users/5741205/maxu?tab=profile)
"""
# Excel file doesn't exist - saving and exiting
if not os.path.isfile(filename):
    df.to_excel(
        filename,
        sheet_name=sheet_name, 
        startrow=startrow if startrow is not None else 0, 
        **to_excel_kwargs)
    return

# ignore [engine] parameter if it was passed
if 'engine' in to_excel_kwargs:
    to_excel_kwargs.pop('engine')

writer = pd.ExcelWriter(filename, engine='openpyxl', mode='a')

# try to open an existing workbook
writer.book = load_workbook(filename)

# get the last row in the existing Excel sheet
# if it was not specified explicitly
if startrow is None and sheet_name in writer.book.sheetnames:
    startrow = writer.book[sheet_name].max_row

# truncate sheet
if truncate_sheet and sheet_name in writer.book.sheetnames:
    # index of [sheet_name] sheet
    idx = writer.book.sheetnames.index(sheet_name)
    # remove [sheet_name]
    writer.book.remove(writer.book.worksheets[idx])
    # create an empty sheet [sheet_name] using old index
    writer.book.create_sheet(sheet_name, idx)

# copy existing sheets
writer.sheets = {ws.title:ws for ws in writer.book.worksheets}

if startrow is None:
    startrow = 0

# write out the new sheet
df.to_excel(writer, sheet_name, startrow=startrow, **to_excel_kwargs)

# save the workbook
writer.save()

</code></pre>
Usage examples:
<pre><code class="lang-auto">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)

</code></pre>
c:/temp/test.xlsx:
PS you may also want to specify <code>header=None</code> if you don’t want to duplicate column names…
UPDATE: you may also want to check <a href="https://stackoverflow.com/a/47740262/5741205">this old solution</a>