Pandas, how to create multiple sheet?

Question

I'd like to make several sheets with pandas.

This is my test code

import pandas as pd

number = [1, 2, 3, 4, 5]
name = ['john', 'james', 'ken', 'jiny']
df = pd.DataFrame(number, columns=['number'])
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='test1_sheet')
writer.save()

output

I currently has created with a single test sheet name ("test1_sheet"). Additionally, I would like to add "test2_sheet" with "name" data list to the same Excel file (test.xlsx). For example,

df = pd.DataFrame(name, columns=['name'])
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='test2_sheet')

I don't know how to do that.

Make 2 data frames; save them with the same writer with different sheet names. What's wrong with your approach? — ifly6
– ifly6, Commented Jul 20, 2021 at 14:36
The Pandas docs provide quite detailed instructions on how to do that, note the with pd.ExcelWriter('output.xlsx') as writer: part in particular. — fsimonjetz
– fsimonjetz, Commented Jul 20, 2021 at 14:38
Don't declare the Writer 2nd time. One time is enough otherwise it will not work as you expect — IoaTzimas
– IoaTzimas, Commented Jul 20, 2021 at 14:39

gernworm · Accepted Answer · 2021-07-20 14:48:23Z

1

With your data, it would look like this.

import pandas as pd

number = [1, 2, 3, 4, 5]
name = ['john', 'james', 'ken', 'jiny']
df1 = pd.DataFrame(number, columns=['number'])
df2 = pd.DataFrame(name, columns=['name'])

# now write to excel
with pd.ExcelWriter('text.xlsx', engine='xlsxwriter') as writer:
    df1.to_excel(writer, sheet_name='test1_sheet')
    df2.to_excel(writer, sheet_name='test2_sheet')

answered Jul 20, 2021 at 14:48

gernworm

3405 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

Pandas, how to create multiple sheet?

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