How to create sheets in an Excel file with pandas and python?

Question

Let's say that I have the following Excel file: enter image description here

I would like to use Python to create an Excel file for each sheet, with all the columns as well. So in Sheet1 to have all the lines where "Sheet1" is mentioned in column A, and so on.

I am able to create the sheets, but not to put the data into it, could someone please help? Below is my code until now:

sheets = ['Sheet1', 'Sheet10', 'Sheet2', 'Sheet3']
finalfile = 'test to check.xlsx'
writer = pd.ExcelWriter(finalfile)
for sheet in sheets:
    df.to_excel(writer,sheet)
writer.save()

Not able to understand your question. Can you please explain clearly. — Student of the Digital World
– Student of the Digital World, Commented Sep 11, 2019 at 11:50
I have an Excel file with 3 columns: Sheet, Name, Version. I want to create one sheet for each element on the list sheets, and the content of this sheet to be from the Excel file with the 3 columns. Basically, when in column A 'Sheet' I have 'Sheet1', I want all those lines to be in the sheet 'Sheet1'. Is it more clear? — owce
– owce, Commented Sep 11, 2019 at 11:58

vvk24 · Accepted Answer · 2019-09-11 12:07:31Z

1

I suppose the below correction in your code should work fine for your expected output.

for sheet in sheets:
    df.loc[(df['Sheet'] == sheet)].to_excel(writer, sheet)

Let me know if this works :)

edited Sep 11, 2019 at 12:07

answered Sep 11, 2019 at 11:53

vvk24

4901 gold badge6 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

How to create sheets in an Excel file with pandas and python?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related