CODEWITHSUNDEEP
pythonexcelpandasdataframe
Samuel
Samuel

Reputation: 57

Python/Pandas: writing multiple Dataframes to Excel sheets using a "for-loop"

I cant imagine this question wasnt asked before but im not able to find the answere here: I got a Excel-File as Dataframe and used Dataframe.groupby on it. Now I want to save every single group into ONE new Excel file using a DIFFERENT Sheet for every Group. All I was able to do is creating a lot new files with one group in every file. My new "solution" does nothing.

df = pd.read_excel(file)
neurons = df.groupby("Tags")

#writing Keys into a list
tags = neurons.groups.keys()
tags = list(tags)


for keyInTags in tags:
     cells = group.get_group(keyInTags)
     cells.to_excel("final.xlsx", sheet_name=keyInTags)

I get no errors but also not new file or writing to an existing file.

Upvotes: 3

Views: 12108

Answers (2)

Clade
Clade

Reputation: 986

Here is a cleaner solution for anyone still looking:

import pandas as pd

df = pd.read_excel("input.xlsx")

with pd.ExcelWriter("output.xlsx") as writer:
    for name, group in df.groupby("column_name"):
        group.to_excel(writer, index=False, sheet_name=name[:31])

Upvotes: 1

Neo
Neo

Reputation: 627

Actually, I believe this is a better solution. Replace your for loop with this code:

writer = pd.ExcelWriter('excel_file_name.xlsx')

for keyInTags in tags:
     cells = group.get_group(keyInTags)
     cells.to_excel(writer, sheet_name=keyInTags)

writer.save()
writer.close()

Upvotes: 2

Related Questions