CODEWITHSUNDEEP
pythonexcelpython-3.xpandasdataframe
guagay_wk
guagay_wk

Reputation: 28060

Append pandas dataframe to excelsheet, not overwrite it

I want to append the contents of a panda dataframe df to an excelsheet.

This is what I did;

df.to_excel(excel_writer="target.xlsx", sheet_name="sheet_1")

The problem with this code is that it overwrites target.xlsx. I lost all my old data in target.xlsx as a result. What I want the code to do is to append, not overwrite the excel sheet.

I am using python 3.7.

Upvotes: 6

Views: 5931

Answers (6)

kaushik karan
kaushik karan

Reputation: 361

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

Source: Pandas Dataframe to Excel

Upvotes: 5

vatsal gosar
vatsal gosar

Reputation: 187

You can simply read the data stored in target.xlsx and store it in a data frame. Now, using pandas concat() method, you can merge the two data frames (new data frame and old data frame) and finally store them in the same excel file.

Code Snippet:

import pandas as pd
old_df = pd.read_excel('target.xlsx')  
frames = [old_df, new_df]
result = pd.concat(frames)
result.to_excel("target.xlsx", sheet_name="sheet_1", index=False)

I hope it helps.

Upvotes: 2

Sreekar Atla - EA
Sreekar Atla - EA

Reputation: 16

Try this:

from openpyxl import load_workbook
writer = pandas.ExcelWriter('target.xlsx', engine='openpyxl') 
writer.book = load_workbook('target.xlsx')
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
df.to_excel(excel_writer=writer, sheet_name="sheet_1")
writer.save()

Upvotes: 0

Daniel Scott
Daniel Scott

Reputation: 985

If you want to append a new sheet, here's your answer.

import pandas as pd

# Open the existing excel file with all sheets
df_dict = pd.read_excel('target.xlsx',sheet_name=None)

with pd.ExcelWriter(fp) as writer:

     # Save the original sheets
     for sheet in df_dict:
          df[sheet].to_excel(writer, sheet_name=sheet)

     # If the new sheet name doesn't exist, append it
     if 'sheet_1' not in df_dict:
          df.to_excel(writer, sheet_name='sheet_1')

Upvotes: 4

Subir Verma
Subir Verma

Reputation: 423

Load 'target.xlsx' in target_df. Then 

new_df = target_df.append(df)

It should work fine I guess. Then save it back as 'target.xlsx'.

Upvotes: 3

Fateh A.
Fateh A.

Reputation: 372

I think the easiest way to do this is:

import pandas as pd
import numpy as np
import xlsxwriter

workbook = xlsxwriter.Workbook('arrays.xlsx')
worksheet = workbook.add_worksheet() # a created excel sheet

array = pd.read_csv('array.csv')
array = array.tolist()

row = 0

for col, data in enumerate(array):
    worksheet.write_column(row, col, data)

workbook.close()

Here is the xlsxwriter documentation.

Upvotes: 4

Related Questions