Write List of DataFrames to Excel using to_excel()

Question

I have different dataframes and want to write them to excel using List Comprehension or a convenient function insted of writing each df individually.

For the time being my approach is to use list comprehension with:

AList=[a,b,c,d]

writer=pd.ExcelWriter(r"P:\Data\Test.xlsx")
[A.to_excel(writer,sheet_name="{0}".format(A)) for A in AList]

writer.save()

But this is not working.

Appreciate each answer.

Answer 1

I think you need to enumerate the list of select sheetnames. Also, it is necessary to assign your output to some variable like _:

#sample data
df = pd.DataFrame({'B':[4,5,4,5,5,4],
                   'C':[7,8,9,4,2,3],
                   'D':[1,3,5,7,1,0],
                   'E':[5,3,6,9,2,4]})

#list of dfs
AList=[df,df*10,df*100,df*1000]
#list of stheetnames
names = ['sheet1','sheet2','sheet3','sheet4']

writer=pd.ExcelWriter(r"P:\Data\Test.xlsx")
_ = [A.to_excel(writer,sheet_name="{0}".format(names[i])) for i, A in enumerate(AList)]
writer.save()

---

Note that the non list comprehension solution below is more suitable here:

writer=pd.ExcelWriter(r"P:\Data\Test.xlsx")
for i, A in enumerate(AList):
    A.to_excel(writer,sheet_name="{0}".format(names[i]))

writer.save()