Dataframe append dataframe to excel with sheet names?

Question

I desire to append multiple dataframes to a blank dataframe, and then export them to excel file with sheet name. I am stuck with it now. I simplify my code and look like this


import pandas as pd
import numpy as np

def calculate_file():
    sheet_df = pd.DataFrame()
    for i in range(40):
#This is a toy dataframe
        random_data = np.random.randint(10,25,size=(5,3))
        df = pd.DataFrame(random_data, columns=['Column_1','Column_2','Column_3'])
    sheet_df = sheet_df.append(df)
    return(sheet_df)
calculate_file()

My desire output is dataframe with 40 sheet names exporting to an Excel file, and the name is Sheet1 to Sheet 40

Stef · Accepted Answer · 2022-06-27 14:11:20Z

1

You need to save the sheet name somewhere, for instance in the index:

def calculate_file():
    sheet_df = pd.DataFrame()
    for i in range(1, 41):
        random_data = np.random.randint(10, 25, size=(5,3))
        df = pd.DataFrame(random_data,
                          columns=['Column_1', 'Column_2', 'Column_3'],
                          index=[f'Sheet {i}'] * len(random_data))
        sheet_df = pd.concat([sheet_df, df])
    return(sheet_df)

sheet_df = calculate_file()

Then you can use an ExcelWriter:

with pd.ExcelWriter('output.xlsx') as writer:
    for sheet in sheet_df.index.unique():
        sheet_df.loc[sheet].to_excel(writer, sheet, index=False)

edited Jun 27, 2022 at 14:11

answered Jun 27, 2022 at 14:06

Stef

30.9k3 gold badges34 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Megan Over a year ago

I appreciate it sooooo much. Thanks for helping me, or I need to export the data one by one :)

Collectives™ on Stack Overflow

Dataframe append dataframe to excel with sheet names?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related