Write multiple pandas dataframes to excel

Question

I am attempting to write multiple pandas dataframes which I extracted from a larger dataset into multiple worksheets of an excel workbook. The issue is that it only writes the first dataframe i.e. index[0], so the resulting workbook has only one worksheet, see sheet1 below. What am I missing? This is a recreation of my problem.

Code:

import pandas as pd
from pandas import ExcelWriter

df_list = []
a = pd.DataFrame({'A':[1,2,3,4,5,6,7,8,9], 'B':[10,11,12,13,14,15,16,17,18]})
b = pd.DataFrame({'C':[11,22,33,44,55,66,77,88,99], 'D':[105,117,128,139,140,153,166,176,188]})

df_list.append(a)
df_list.append(b)

writer = ExcelWriter('test_output.xlsx')
for n, df in enumerate(df_list):
    df.to_excel(writer, 'sheet%s' % str(n + 1))
    writer.save()

Sheet1:

jpp · Accepted Answer · 2023-07-30 13:49:26Z

10

You need to call writer.save() or writer.close() in later API after the for construct.

Once this method is called, the writer object is effectively closed and you will not be able to use it to write more data.

writer = ExcelWriter('test_output.xlsx')
for n, df in enumerate(df_list):
    df.to_excel(writer, 'sheet%s' % str(n + 1))
writer.close()

edited Jul 30, 2023 at 13:49

answered May 13, 2018 at 10:15

jpp

166k37 gold badges301 silver badges362 bronze badges

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

3 Comments

Nobi Over a year ago

Thanks for shedding light on this.

blue_chip Over a year ago

Thanks! I didn't know that saving the writer would close it.

Joe Over a year ago

They must have changed the API, see here

Collectives™ on Stack Overflow

Write multiple pandas dataframes to excel

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related