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

Question

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.

Hello! Maybe it would help to look here: stackoverflow.com/questions/42207319/…. I believe you need to open a file before the for loop and write on each sheet at every iteration. — Neo
– Neo, Commented Apr 2, 2019 at 13:44
But if I go for print(cells) I get exactly what I want: all the neurons grouped by their tags. — Samuel
– Samuel, Commented Apr 2, 2019 at 14:02

Neo · Accepted Answer · 2019-04-02 14:05:18Z

2

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

answered Apr 2, 2019 at 14:05

Neo

6273 silver badges7 bronze badges

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

2 Comments

Samuel Over a year ago

Yes, thats excalty doing what i wanted :) Thanks a lot. Im new to coding at all and "better solutions" always help me to learn writing proper code.

Neo Over a year ago

You are welcome. I am new too, I just try to contribute to the SO community when I can, because it has helped me so much. Cheers!

Clade · Accepted Answer · 2019-08-27 21:07:50Z

1

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

answered Aug 27, 2019 at 21:07

Clade

9861 gold badge7 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related