CODEWITHSUNDEEP
pythonpandasnumpy
rahul knair
rahul knair

Reputation: 113

pandas write to excel overwriting the existing excel rows

I have to write the data to an excel and append a row to the same after each iteration:

        writer = pandas.ExcelWriter('test.xlsx', engine='openpyxl')
        reader = pandas.read_excel('test.xlsx')
        frame = i.to_frame().T
        l = len(reader)
        if l==0:
            frame.to_excel(writer, index=False, header=True, sheet_name=country)
            writer.save()
        else:
            writer.book = load_workbook('test.xlsx')
            writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
            reader = pandas.read_excel(r'test.xlsx')
            frame.to_excel(writer, index=False, header=False, startrow=len(reader) + 1)
            writer.save()

The first iteration just works fine but from the next iteration, it overwrites the previous iteration rows. i get the data as series and that i convert into dataframe(frame) before writing to excel

Upvotes: 0

Views: 2685

Answers (1)

caseygrun
caseygrun

Reputation: 2170

You can use the ExcelWriter class in append mode (a). From the docs: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

with pd.ExcelWriter('output.xlsx',
                    mode='a') as writer:  
     df.to_excel(writer, sheet_name='Sheet_name_3')

You may need to rearrange your code if you want to guarantee that the file (if it exists) is overwritten on the first iteration, but is appended to on subsequent iterations:

for (i, df) in enumerate(my_data):
    
    if i  == 0:
        mode = 'w'
    else:
        mode = 'a'

    with pd.ExcelWriter('output.xlsx',
                        mode=mode) as writer:  
        df.to_excel(writer, sheet_name='Sheet_name_3')

I am a bit confused what you are trying to achieve with the existing sheets, but I think you will need to specify explicitly to which sheet(s) you want the DataFrame to be written. You shouldn't need to re-write the other sheets.

Upvotes: 1

Related Questions