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

Forint

Answer 1

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

Enyo

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. – Forint

There is a typo on line 7: 'sheetName_3', dataframe3 -- key and value should be separated by colon : not comma ,. – Eyecatching

FYI, user need to install xlsxwriter separately. It does not installed directly while installing pandas. – Recitativo

The above code is outdate, with python3, you can use the below code without having to install xlswriter: writer = pd.ExcelWriter('MyFile.xlsx') iteritems has been depreciated, so use or sheet, frame in frames.items(): writer.save() also depreciated, so: writer.close() – Ointment

Answer 2

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

Roundabout

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. – Sphygmo

Answer 3

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

Karalynn