Iterate multiple Dataframe and write into excel spreadsheets within an excel xlsxwriter python

Question

I am working on writing multiple dataframes in excel spreadsheets within an excel file. The dataframes generates using for loop , so in every iteration I get next available dataframe but I can not able to write every dataframe in spreadsheets. I could only write the first dataframe in first spreadsheet. Below is the code snippet:

 writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
    workbook = writer.book
    web_ClassID=df_stag["Web-Class ID"].unique()
          for data_id, df in df_stag.groupby('Web-Class ID'): 
             for workbook_Id in web_ClassID:
                 if workbook_Id == data_id:
                    for index, col in enumerate(df):
                    df.to_excel(writer,sheet_name=workbook_Id,index=False )
                    writer.save()
    workbook.close()

Note: The webclass_id here is type of object Df_stag: is a dataframe which is grouped by Webclass_id df is the dataframe which I want to write into excel spreadsheets Also, I am creating spreadsheets whenever a new Webclass_id is found , so the tab gets created with corresponding Webclass_id when found.One file having few tabs another file having few tabs

Output: I want a single excel file having all the tabs from all the files and appended data from those file which have same tab in both the files. ie. 11111,22222,33333 and data from 11111 should be from both the files.

We really need sample data to reproduce your issue. What is data_id and why reassign it in another for loop? And why increment loop iterators? Plus you have indentation issues and finally, your post seems cut off. Please edit so we can help. — Parfait
– Parfait, Commented Mar 17, 2017 at 22:21
Sorry for my indentation issues!! the data_id is one of the column in data frame and it contains numeric values which can have multiple occurrences. — shweta kapgate
– shweta kapgate, Commented Mar 21, 2017 at 14:50
What I want to achieve is group the whole dataframe by Web-class Id and wherever I find a match of data_id and workbook_id , and since it has multiple occurrences of same number , the whole dataframe should be written in specific spreadsheet within an excel file. — shweta kapgate
– shweta kapgate, Commented Mar 21, 2017 at 14:52

Parfait · Accepted Answer · 2017-03-22 00:36:32Z

Simply move writer.save() outside of for loop:

writer = pd.ExcelWriter('output.xlsx', engine='xlsxwriter')
workbook = writer.book
web_ClassID=df_stag["Web-Class ID"].unique()

for data_id, df in df_stag.groupby('Web-Class ID'):
    for workbook_Id in web_ClassID:
        if workbook_Id == data_id:
            for index, col in enumerate(df):
                df.to_excel(writer,sheet_name=workbook_Id,index=False )
writer.save()
workbook.close()

And for shorter code, simply filter iteratively with no need for workbook object:

writer = pd.ExcelWriter('output.xlsx')
for d in df_stag["Web-Class ID"].unique():    
    df_stag[df_stag["Web-Class ID"]==d].to_excel(writer, sheet_name=d, index=False)        

writer.save()

jmcnamara · Accepted Answer · 2017-03-18 10:57:48Z

1

It is definitely possible to write multiple dataframes into separate worksheets or to different positions within the same worksheet using XlsxWriter.

See the Handling multiple Pandas Dataframes section of the XlsxWriter docs or the examples here and here.

If it isn't working in your case then I'd guess:

workbook_Id isn't unique and you are overwriting the same worksheet.
workbook_Id == data_id is only true once.
You have some old version of either Pandas or XlsxWriter.

I would start with 1. and 2. and put in some debug statements.

answered Mar 18, 2017 at 10:57

jmcnamara

42k7 gold badges98 silver badges114 bronze badges

3 Comments

shweta kapgate Over a year ago

Thankyou for your information but I have already tried those logic.

shweta kapgate Over a year ago

What issue I am facing is , with in for loop I have instance of dataframe which i want to write in spreadsheet and the name of that spreadsheet should be data_id.

shweta kapgate Over a year ago

I could able to copy the headers of dataframe and generate the spreadsheets with tab names as data_id but I am failing in copying the dataframe inside those tabs.

shweta kapgate · Accepted Answer · 2017-03-22 15:10:04Z

0

I finally found answer to my question!!

I simply have to assign the object to sheet_name attribute and it will automatically copy every instance of dataframe into the excel sheet having sheet name as workbook_Id. ie. df.to_excel(writer,sheet_name=workbook_Id)

edited Mar 22, 2017 at 15:10

answered Mar 21, 2017 at 18:14

shweta kapgate

371 silver badge8 bronze badges

Comments

shweta kapgate · Accepted Answer · 2017-03-22 15:19:53Z

@Parfait: earlier my code was creating new excel sheets but it wasnot copying the instance of dataframe inside excel sheets. I only had sheet names as web-class id.

Even if I enumerated the df and tried to write df inside excel sheets. It was only writing content of first dataframe

for index, col in enumerate(df):
                df.to_excel(writer,sheet_name=workbook_Id,index=False )
writer.save()

Assigning workbook_id to sheet_name will not create a new sheet and assign the content inside the sheet_name.

df.to_excel(writer,sheet_name=workbook_Id)

Also, I found no need of explicit writing of writer.save . Even if we write workbook.close() after iteration to_excel() it writes and saves the data.

Collectives™ on Stack Overflow

Iterate multiple Dataframe and write into excel spreadsheets within an excel xlsxwriter python

4 Answers 4

Comments

3 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

Comments

Related