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

Question

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?

Check http://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data out, might solve your problem — Yarnspinner, Jun 18 '16 at 02:36

Stefan · Accepted Answer · 2016-06-22T13:06:35.487

1

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

edited Jun 22 '16 at 13:06

answered Jun 18 '16 at 10:35

Stefan

41,759
13
76
81

if I need to simultaneously write to multiple files? I have names if df and I try to `df.groupby('mail').apply(lambda g: g.to_excel(str(g.name) + '.xlsx', 'Full about smartphones'))` – NineWasps Jun 21 '16 at 16:11
When I use that, data, that are existing in this files, are deleted. Even I use another `sheetname` – NineWasps Jun 21 '16 at 22:12
See updated with instructions for `.groupby()` use case. – Stefan Jun 22 '16 at 12:46
How I can specify a lot of file it `path`? – NineWasps Jun 22 '16 at 12:48
If you plan to write the same DataFrame to multiple existing files, you need to repeat the steps inside the function for all files. You could either extend the function so it takes multiple paths and loops over the paths inside the function, or call groupby repeatedly and provide a different path there. Looks like this is a bit different from the question you originally asked, though. – Stefan Jun 22 '16 at 12:51
`df.groupby('col_name').apply(lambda x: add_xlsx_sheet(x, x.name, path='{}.xlsx'.format(x.name)))`It doesn't retirn error, but it doesn't add any to files. – NineWasps Jun 22 '16 at 12:57
`'col_name'` refers to a `column` in your data that you want to group on. Also, the files with the group names need to exist already, but this would give an error, so probably there was just nothing to `group` on. – Stefan Jun 22 '16 at 12:59
Sorry to my folly, I understand all. Thank you for your answer and explanation – NineWasps Jun 22 '16 at 13:11

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

1 Answers1