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 · Accepted Answer · 2021-09-28 02:46:20Z

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, 2021 at 2:46

answered Sep 28, 2021 at 1:54

U13-Forward

71.8k15 gold badges100 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Aavesh Over a year ago

stackoverflow.com/a/54237619/13960989 Check this out, to better understand how to write to multiple sheets inside a workbook.

U13-Forward Over a year ago

@Aavesh Misread OP's question, edited my answer.

shorttriptomars Over a year ago

@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

U13-Forward Over a year ago

@shorttriptomars Edited my answer

U13-Forward Over a year ago

@shorttriptomars Hope it works

|

jmcnamara · Accepted Answer · 2021-09-28 10:00:46Z

1

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.

answered Sep 28, 2021 at 10:00

jmcnamara

42.1k7 gold badges98 silver badges114 bronze badges

1 Comment

shorttriptomars Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related