Iterating over pandas dataframe and saving into separate sheets in xlxs file

Question

I'm iterating over a pandas dataframe during and carry out and operation to obtain the information (excel sheet number) for saving the appropriate excel sheet like this:

from opnepyxl.utils.dataframe import dataframe_to_rows

for i,data in df.iterows:
    sheet=data['SheetNo']

    #Create excel writer
    writer=pd.Excelwriter('output.xlxs')

    # write dataframe to excelsheet
    data.to_excel(writer, sheet)

    #save the excel file
    writer.save()

Dataframe:

ID  SheetNo setting
2304    2   IGV5
2305    3   IGV2
2306    1   IGV6
2307    2   IGV2
2308    1   IGV1

What I wanted was for data to go into each of the created 'SheetNo' of the excel file, instead the the previous sheet is being overwritten by the following one, and you can only see the last sheet number.

What can I do to make this code work? Any other approach apart from mine above will be welcome.

Hi @Rob - Are you trying to write each row into a separate sheet within output.xlsx? If yes, you have repeated SheetNo for 1 and 2. Should that not be unique? — Redox
– Redox, Commented Jun 23, 2022 at 2:35
Hi @Redox, my idea was to use "sheet=data['SheetNo']" to obtain unique sheet no for each row. — Rob John
– Rob John, Commented Jun 23, 2022 at 8:16
Correct, but data['SheetNo'] = 2 for the 1st and 4th rows. Are you ok that the 4th iteration overwrites the data written in the first iteration? Based on your logic, won't there just be 3 tab/worksheets 1,2,3? — Redox
– Redox, Commented Jun 23, 2022 at 8:39
What the code was meant to do is to add data to the appropriate sheet in the excel file, e.g., data in rows 1 and 4 should go into sheet no 2 (appended), and data in rows 3 and 5 should go into sheet no 1 (appended). I'm sure my approach may be wrong. Any other suggestion is welcome. — Rob John
– Rob John, Commented Jun 23, 2022 at 8:47

Redox · Accepted Answer · 2022-06-23 13:07:34Z

1

the python code is below. Note that:

Assumption is that there is already an output.xlsx file in same dir that this code runs
It will search for worksheet names with the SheetNo column. If not available in the file, it will create a new worksheet/tab with that name and add the header row 3.The program will then add each row (append) to the sheet
Once all data in DF is added, it will save file back to same name
You can run this as many times as you want, it will keep adding new sheets or appending to existing sheets.

import pandas as pd
from openpyxl import load_workbook

data = {'ID': [2304,2305,2306,2307,2308],
'SheetNo': [2,3,1,2,1],
'setting': ['IGV5', 'IGV2', 'IGV6', 'IGV2', 'IGV1']}

df = pd.DataFrame(data)
headers = ['ID','SheetNo','setting']

FilePath = 'output.xlsx' #ASSUMPTON - File already exists
wb = load_workbook(FilePath)

for sheet in df.SheetNo.unique(): 
    if str(sheet) in wb.sheetnames: #If sheet in excel file
        ws = wb[str(sheet)]
    else:
        ws = wb.create_sheet(title=str(sheet)) #Create New sheet is not present
        ws.append(headers) #New sheet = Need header, else not required
    i =0
    j = 0
    for row in ws.iter_rows(min_row=ws.max_row+1, max_row=ws.max_row+len(df[df.SheetNo == sheet]), min_col=1, max_col=3):
        for cell in row:
            cell.value = df[df.SheetNo == sheet].iloc[i,j]
            j = j + 1
        j=0
        i= i + 1
wb.save('output.xlsx')

Output excel after one run of the code

answered Jun 23, 2022 at 13:07

Redox

10.1k5 gold badges11 silver badges28 bronze badges

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

1 Comment

Rob John Over a year ago

Problem solved. Thanks for your help @Redox. I really apreciate it!

Collectives™ on Stack Overflow

Iterating over pandas dataframe and saving into separate sheets in xlxs file

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related