How do I write to individual excel sheets for each dataframe generated from for loop?

user_9106985 0
Viewed 110

I have input data in the form of a dictionary consisting of 3 dataframes of numbers. I wish to iterate through each dataframe with some operations and then finally write results for each dataframe to excel.

The following code works fine except that it only writes the resulting dataframe for the last key in the dictionary.

How do I get results for all 3 dataframes written to individual sheets?

Input_Data={'k1':test1,'k2':test24,'k3':test3}

for v in Input_Data.values():
   
    df1 = v[126:236] 
    df=df1.sort_index(ascending=False)
    Indexer=df.columns.tolist()
    df = [(pd.concat([df[Indexer[0]],df[Indexer[num]]],axis=1)) for num in [1,2,3,4,5,6]]
    df = [(df[num].astype(str).agg(','.join, axis=1)) for num in [0,1,2,3,4,5]]
    df=pd.DataFrame(df)
  dff=df.loc[0].append(df.loc[1].append(df.loc[2].append(df.loc[3].append(df.loc[4].append(df.loc[5])))))


    dff.to_excel('test.xlsx',index=False, header=False)
pandas python
3 Answers

Try to change the file name at each iteration:

Input_Data={'k1':test1,'k2':test24,'k3':test3}
file_number = 1

for v in Input_Data.values():
   
    df1 = v[126:236] 
    df=df1.sort_index(ascending=False)
    Indexer=df.columns.tolist()
    df = [(pd.concat([df[Indexer[0]],df[Indexer[num]]],axis=1)) for num in [1,2,3,4,5,6]]
    df = [(df[num].astype(str).agg(','.join, axis=1)) for num in [0,1,2,3,4,5]]
    df=pd.DataFrame(df)
  dff=df.loc[0].append(df.loc[1].append(df.loc[2].append(df.loc[3].append(df.loc[4].append(df.loc[5])))))
    
    file_name='test'
    file_number=str(file_number)
    

    dff.to_excel( str(file_name+file_number)+".xlsx",index=False, header=False)
    
    file_number=int(file_number)
    file_number = file_number+1
user_124308460 
# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

# Write each dataframe to a different worksheet.
for sheet_name, df in zip(sheet_names, dfs):
    df.to_excel(writer, sheet_name=sheet_name)

# Close the Pandas Excel writer and output the Excel file.
writer.save()
user_25296190

Your first issue is that with each iteration of the loop you are opening a new file.

As per pandas documentation:

"Multiple sheets may be written to by specifying unique sheet_name. With all data written to the file it is necessary to save the changes. Note that creating an ExcelWriter object with a file name that already exists will result in the contents of the existing file being erased."

Second, you are not providing a variable sheet name, so each time the data is being re-written as the same sheet.

An example solution, with ExcelWriter

#df1, df2, df3 - dataframes
input_data={
'sheet_name1' : df1,
'sheet_name2' : df2,
'sheet_name3' : df3
}

# Initiate ExcelWriter - use xlsx engine
writer = pd.ExcelWriter('multiple_sheets.xlsx', engine='xlsxwriter')

# Iterate over input_data dictionary 
for sheet_name, df in input_data.items():

    """
    Perform operations here
    """

    # Write each dataframe to a different worksheet.
    df.to_excel(writer, sheet_name=sheet_name)

    
# Finally, save ExcelWriter to file
writer.save()

Note 1. You only initiate and save the ExcelWriter object once, the iterations only add sheets to that object

Note 2. Compared to your code, the variable "sheet_name" is provided to the "to_excel()" function

user_123344150
Related