Unable to export dataframe into separate excel files using pandas

Question

I am trying to export a dataframe that I create in python to excel by group. I would like to create a new separate excel file for each group, not sheets within he same excel file.

This is the code I have right now and it creates sheets within the same excel file for each group. How can I alter this to create separate excel files. Do I need to write a for loop? Many of the solutions I am finding on Stack Overflow seem to be pointing in that direction.

data = df.groupby('ID')
writer = pd.ExcelWriter('Data_New.xlsx', engine='xlsxwriter')
for row,group in data:
       group.to_excel(writer, index = None, sheet_name=row)
writer.save()

@BigBen, i edited the question to make that more clear, thank you. I am not exactly sure what you mean by separate file names. — Trevor M
– Trevor M, Commented Sep 9, 2022 at 20:25
Right now you have one filename: Data_New.xlsx. You need multiple filenames if you want multiple files. — BigBen
– BigBen, Commented Sep 9, 2022 at 20:27

user1889297 · Accepted Answer · 2022-09-09 20:53:28Z

2

import pandas as pd
import numpy as np

#Example for DataFrame
df = pd.DataFrame(np.random.randint(0,4,size=(50, 4)), columns=['ID', 'A', 'B', 'C'])
# loop without groupby
for ID in df['ID'].unique():
    df[df['ID']==ID].to_excel(f'C:/temp/ID_{ID}.xlsx', index=False)

answered Sep 9, 2022 at 20:53

user1889297

5045 silver badges15 bronze badges

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

Comments

lummers · Accepted Answer · 2022-09-09 20:46:05Z

2

This will produce one separate Excel workbook:

df = pd.DataFrame([
    ['A',1],
    ['A',2],
    ['B',3],
    ['B',4],
    ['B',5]], columns=['id','val'])

data = df.groupby('id')

for group, frame in data:
    frame.to_excel(f"{group}.xlsx", index=False)

Documentation: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_excel.html

You don't need to use the ExcelWriter for your use-case:

To write a single object to an Excel .xlsx file, it is only necessary to specify a target file name. To write to multiple sheets it is necessary to create an ExcelWriter object with a target file name, and specify a sheet in the file to write to.

answered Sep 9, 2022 at 20:46

lummers

7893 gold badges9 silver badges20 bronze badges

Collectives™ on Stack Overflow

Unable to export dataframe into separate excel files using pandas

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related