Dataframe append dataframe to excel with sheet names?-CodePudding

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

CodePudding user response：

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