How to add data frames to multiple sheets in excel using python-CodePudding

I'm trying to get data for each language in langs_dict (see below). I want to add the data to a list, add that list to a dataframe, and then write that dataframe to an excel sheet. I want all the languages to be part of the same excel file, but for each to have their own sheet.

Below is my code:

langs_dict=['ar','zh','cs','id','ja','km','ms','pt','ru','es','th','fil']
dct = {}
i = 0

while i < 13:
   for l in langs_dict:
      dct['language_%s' % l]  = []
      dct['translation_%s' % l]  = []
 
   #put lists in dataframe
   df1 = pd.DataFrame({'Language' : dct['language_%s' % l] } )
   df2 = pd.DataFrame({'Translation': dct['translation_%s' % l]})
 
   #concat lists     
   df = pd.concat([df1, df2])
    
   #initialize excel writer    
   writer =  pd.ExcelWriter('searches_languages.xlsx',engine='xlsxwriter')
   
   #write df to sheet
   df.to_excel(writer, sheet_name = l,index=False)

   #save
   writer.save()
   
   i =1

As you can probably tell, the df.to_excel(writer, sheet_name = l,index=False) overwrites the previous sheet instead of creating a new sheet. Does anyone know how to create a new sheet for each new df?

CodePudding user response：

Define the write outside the loop:

#initialize excel writer    
writer =  pd.ExcelWriter('searches_languages.xlsx',engine='xlsxwriter')
while i < 13:
   for l in langs_dict:
      dct['language_%s' % l]  = []
      dct['translation_%s' % l]  = []
 
   #put lists in dataframe
   df1 = pd.DataFrame({'Language' : dct['language_%s' % l] } )
   df2 = pd.DataFrame({'Translation': dct['translation_%s' % l]})
 
   #concat lists     
   df = pd.concat([df1, df2])
   
   #write df to sheet
   df.to_excel(writer, sheet_name = str(i),index=False)

   #save
   writer.save()
   
   i =1