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

2
votes

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 tabsanother 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.

4
pythonexcelpandasxlsxwriter
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
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
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

4 Answers

3
votes

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()
1
votes

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:

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

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

0
votes

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)

0
votes

@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.