Feature Type

  • [x] Adding new functionality to pandas

  • [ ] Changing existing functionality in pandas

  • [ ] Removing existing functionality in pandas

Problem Description

I work with scripts, which write a lot of dataframes to different sheets in excel files. For my use case, the excel files I want to write to may or may not exist yet. Since dataframe.to_excel("path/file.xlsx") always creates a new excel file I am using the ExcelWriter with mode="a" and if_sheet_exists="replace" for updating the data on the specific sheet. However, when the excel file doesn't exist, I need to use mode="w" and also use None for if_sheet_exists.

I wish it would be easier to append data to excel files, regardless of they exist or not.

What I want is a behaviour that is actually similar to the if_sheet_exists parameter. There, I don't need to know if the sheet already exist or not, I simply specify what I want to happen if it exists.

Feature Description

Add a new Option to the mode parameter of the "ExcelWriter".

mode : {{'w', 'a', 'append_or_create'}}, default 'w'
        File mode to use (write, append, append to the file and create the file if it doesn't exist yet). Append does not work with fsspec URLs.

The value for this option can be any other value that fits.

The mode "append_or_create" would check if the excel file exists, if it does it appends the sheet to it, using the value of if_sheet_exists. If the excel file doesn't exist it is created.

Alternative Solutions

import os
from typing import Literal

import pandas as pd


def write_excel_append_or_create(
    dataframe: pd.DataFrame,
    path: str,
    sheet_name: str = "Sheet1",
    if_sheet_exists: Literal["error", "new", "replace", "overlay"] = "replace",
    **kwargs,
) -> None:
    excel_file_exists = os.path.exists(path)
    mode = "a" if excel_file_exists else "w"
    replace = if_sheet_exists if mode == "a" else None
    with pd.ExcelWriter(path=path, mode=mode, engine="openpyxl", if_sheet_exists=replace) as writer:
        dataframe.to_excel(excel_writer=writer, sheet_name=sheet_name, **kwargs)

Additional Context

No response

Comment From: rmhowe425

I think this is a valid and useful use case.

I think it might be easier just to use os.path.exists() to determine if the file exists, and then just update engine_kwargs accordingly.

Comment From: DevastatingRPG

take

Comment From: DevastatingRPG

I agree this is an unconventional case where append mode doesn't create a file automatically. Whereas in both standard Python File Handling and Pandas to_csv methods Append mode automatically creates the file if it doesn't exist.

I shall add the functionality to 'a' mode itself by checking for existence and switching the modes internally like @rmhowe425 mentioned, and raise a PR soon after running tests.

Comment From: rmhowe425

@DevastatingRPG I recommend holding off until repo maintainers remove the Needs Triage label.