CODEWITHSUNDEEP
pythonpandas
Jogi
Jogi

Reputation: 314

Writing to multiple sheets in same excel file

I want to write different sheets that are stored in the same excel file. Even though I skimmed through various posts, I don't get the desired result.

Basically, I create various summary statistics in a loop and want to store them. My code looks as follows:

import pandas as pd 
import numpy as np


df = pd.DataFrame(np.random.randint(0,100,size=(100, 4)), columns=list('ABCD'))

writer = pd.ExcelWriter(r'Output.xlsx', engine='xlsxwriter')


for i in list(df): 
  summary=df.loc[:,i].describe()
  summary.to_excel (writer, sheet_name=i,startrow=1)
  summary.to_excel (writer, sheet_name=i,startrow=12)
  writer.save()

However, after running the code, only sheet A is filled.

Any clue what I missed?

Upvotes: 1

Views: 76

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

The program in calling save() and thus closing the workbook within the loop. Move that outside the loop and the program will work as expected:

for i in list(df): 
  summary=df.loc[:,i].describe()
  summary.to_excel (writer, sheet_name=i,startrow=1)
  summary.to_excel (writer, sheet_name=i,startrow=12)

writer.save()

Output:

enter image description here

Upvotes: 1

Related Questions