Writing multiple pandas dataframes to multiple excel worksheets

Question

I'd like for the code to run 12345 thru the loop, input it in a worksheet, then start on 54321 and do the same thing except input the dataframe into a new worksheet but in the same workbook. Below is my code.

workbook = xlsxwriter.Workbook('Renewals.xlsx')

groups = ['12345', '54321']

for x in groups:

    (Do a bunch of data manipulation and get pandas df called renewals)

    writer = pd.ExcelWriter('Renewals.xlsx', engine='xlsxwriter')
    worksheet = workbook.add_worksheet(str(x))
    renewals.to_excel(writer, sheet_name=str(x))

When this runs, I am left with a workbook with only 1 worksheet (54321).

Sam · Accepted Answer · 2021-02-26T19:51:50.773

31

try something like this:

import pandas as pd
#initialze the excel writer
writer = pd.ExcelWriter('MyFile.xlsx', engine='xlsxwriter')

#store your dataframes in a  dict, where the key is the sheet name you want
frames = {'sheetName_1': dataframe1, 'sheetName_2': dataframe2,
        'sheetName_3': dataframe3}

#now loop thru and put each on a specific sheet
for sheet, frame in  frames.iteritems(): # .use .items for python 3.X
    frame.to_excel(writer, sheet_name = sheet)

#critical last step
writer.save()

edited Feb 26 '21 at 19:51

answered Feb 29 '16 at 19:53

Sam

4,000
20
27

I guess a problem I'm having is that I only have 1 dataframe at a time. Basically my code starts with the 1st group of 12345. It manipulates the data and I'm left with an ending dataframe. After that is finished it starts over with the next group. So in the end, I will be left with the same dataframe with just new data. If that makes sense. – Milhouse Feb 29 '16 at 20:00
There is a typo on line 7: `'sheetName_3', dataframe3` -- key and value should be separated by colon `:` not comma `,`. – BoB Feb 25 '21 at 11:11
2

FYI, user need to install `xlsxwriter` separately. It does not installed directly while installing pandas. – EMT Jan 25 '22 at 10:34

Rocky Mohan · Answer 2 · 2020-10-22T09:54:02.250

1

import pandas as pd
writer = pd.ExcelWriter('Renewals.xlsx', engine='xlsxwriter')

renewals.to_excel(writer, sheet_name=groups[0])
renewals.to_excel(writer, sheet_name=groups[1])
writer.save()

edited Oct 22 '20 at 09:54

answered Oct 20 '20 at 15:08

Rocky Mohan

41
1
6

3

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – ZF007 Oct 22 '20 at 10:35

score 1 · Answer 3 · answered May 25 '21 at 05:15

Building on the accepted answer, you can find situations where the sheet name will cause the save to fail if it has invalid characters or is too long. This could happen if you are using grouped values for the sheet name as an example. A helper function could address this and save you some pain.

def clean_sheet_name(sheet):
"""Clean sheet name so that it is a valid Excel sheet name.

Removes characters in []:*?/\ and limits to 30 characters.

Args:
    sheet (str): Name to use for sheet.
    
Returns:
    cleaned_sheet (str): Cleaned sheet name.
"""
if sheet in (None, ''):
    return sheet
clean_sheet = sheet.translate({ord(i): None for i in '[]:*?/\\'})
if len(clean_sheet) > 30: # Set value you feel is appropriate
    clean_sheet = clean_sheet[:30]
return clean_sheet

Then add a call to the helper function before writing to Excel.

for sheet, frame in groups.items():
    # Clean sheet name for length and invalid characters
    sheet = clean_sheet_name(sheet)
    frame.to_excel(writer, sheet_name = sheet, index=False)
writer.save()

Writing multiple pandas dataframes to multiple excel worksheets

3 Answers3

Linked