through a list have the dataframe print to excel a different document. If not into the same document different sheet

Question

How to get separate excel file of the data frame from a list?

I don't want to make 4 separate functions for each costcenter.

import pandas as pd

    df = pd.read_excel(r'c:\temp\code.xlsx') 
    costcenter = ['1130', '1236', '3427', '3148' ]
    for each_costcenter in costcenter:
        y = df[df['COST CENTER']== each_costcenter] 
        y.to_excel(r'c:\temp\code\finalput.xlsx', sheet_name=each_costcenter)

I thought I can get finalput with 4 sheets of the data but I end up with one sheet with the last from the list.

I wouldn't mind getting 4 separate files with cost center names.

You're overwriting the Excel file on each invocation. To make different sheets in the same file, check https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.ExcelWriter.html — ifly6, Aug 09 '19 at 17:50
Possible duplicate of [Using Loop to Create Excel Sheets with Dataframe Pandas](https://stackoverflow.com/questions/41215508/using-loop-to-create-excel-sheets-with-dataframe-pandas) — ifly6, Aug 09 '19 at 17:51

score 1 · Accepted Answer · answered Aug 09 '19 at 18:03

1

You need to create the Excel writer object first:

writer = pd.ExcelWriter(r'c:\temp\code\finalput.xlsx')

Then in your loop:

y.to_excel(excel_writer=writer, sheet_name=each_costcenter)

Then after the loop save the file:

writer.save()

answered Aug 09 '19 at 18:03

Toby Petty

4,431
1
17
29

OMG it works!!! i tried this before but i couldnt figure it out...i forgot to put writer.save() outside of the loop – Leightonscode Aug 09 '19 at 18:15
Great! Please accept the answer if it fixed the problem. – Toby Petty Aug 11 '19 at 00:19

through a list have the dataframe print to excel a different document. If not into the same document different sheet

1 Answers1