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:

    A   B
0   1   10
1   2   11
2   3   12
3   4   13
4   5   14
5   6   15
6   7   16
7   8   17
8   9   18

Answer 1

You need to call writer.save() 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.save()