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

Question

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?

U13-Forward · Answer 1 · 2021-09-28T02:46:20.667

2

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

edited Sep 28 '21 at 02:46

answered Sep 28 '21 at 01:54

U13-Forward

69,221
14
89
114

https://stackoverflow.com/a/54237619/13960989 Check this out, to better understand how to write to multiple sheets inside a workbook. – Aavesh Sep 28 '21 at 01:55
1

@Aavesh Misread OP's question, edited my answer. – U13-Forward Sep 28 '21 at 01:56
@U12-Forward thank you for taking the time to answer my question. I tried your solution but I'm still getting the same problem. Each new `df` overwrites the previous `df` on the same sheet instead of writing to a new sheet. I think I need to create a loop, but I'm not sure how – shorttriptomars Sep 28 '21 at 02:17
@shorttriptomars Edited my answer – U13-Forward Sep 28 '21 at 02:46
@shorttriptomars Hope it works – U13-Forward Sep 28 '21 at 02:49
@U12-Forward thank you so much for your help. I actually realized that in addition to your solution of putting the writer outside the loop, I had to fix the indent, making sure the `df` and `df.to_excel` were in the for loop instead of outside it – shorttriptomars Sep 28 '21 at 04:13
@shorttriptomars Yeap! Happy to help! – U13-Forward Sep 28 '21 at 04:13

score 1 · Accepted Answer · answered Sep 28 '21 at 10:00

There are a number of issues in the code. The main one is that the Pandas ExcelWriter initialisation and saving should be outside the loop. Also, the inner for() loop just returns the last entry in the array every time. Fixing these issues would give you something like this:

import pandas as pd

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

# initialize excel writer
writer = pd.ExcelWriter('searches_languages.xlsx', engine='xlsxwriter')

for i in range(len(langs_dict)):
    lang = langs_dict[i]

    # TODO. Fix the following to add actual data.
    dct['language_%s' % lang] = []
    dct['translation_%s' % lang] = []

    # put lists in dataframe
    df1 = pd.DataFrame({'Language': dct['language_%s' % lang]})
    df2 = pd.DataFrame({'Translation': dct['translation_%s' % lang]})

    # concat lists
    df = pd.concat([df1, df2])

    # write df to sheet
    df.to_excel(writer, sheet_name=lang, index=False)

writer.save()

Output:

Note, you will have to fix the df1 and df1 data based on your real data. In my example they are blank.

Thank you so much for taking the time to answer my question. You're right, I figured out the mistakes you pointed out (see my comment under the solution above). But I still really appreciate you taking the time to provide a detailed solution — shorttriptomars, Sep 29 '21 at 00:28

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

2 Answers2