ldevyataykina ldevyataykina - 5 months ago 23
Python Question

Write data from a df to an excel workbook that already has other worksheets

I want to write data to an Excel WorkBook from a dataframe. The Workbook has other worksheet with existing data. I want to write the data from the dataframe to a new Worksheet

When I use

df.to_excel('name.xlsx', 'Sheet3')


data from other lists(sheets) are deleted. How can I write data to another sheet in an existing workbook and not lose existing data?

Answer

You can use the below add_xlsx_sheet function that includes a few settings available for to_excel, and checks (and removes) if the sheet with the desire names already exists in the given workbook:

from openpyxl import load_workbook
import pandas as pd

def add_xlsx_sheet(df, sheet_name='sheet', index=True, digits=2, path=None):
    """Save pd.DataFrame to new excel worksheet in existing .xlsx workbook

    :param df: pd.DataFrame
    :param sheet_name:
    :param index: bool
    :param digits: int number of digits for float formatting
    :param path: path to existing workbook of type .xlsx
    """
    book = load_workbook(path)
    writer = pd.ExcelWriter(path, engine='openpyxl')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df.to_excel(excel_writer=writer, sheet_name=sheet_name, startrow=1, startcol=1,
                float_format='%.{}f'.format(digits), index=index)
    writer.save()

For the .groupby() usage would be:

df.groupby('col_name').apply(lambda x: add_xlsx_sheet(x, x.name, path='{}.xlsx'.format(x.name)))