Writing Data to Existing Excel using pandas in python

Question

Currently, I am exporting two data frames to a single excel file. Each data frame is placed on separate sheets. I am currently running a simulation and need 2,000 columns of each data frame (2,000 on each sheet). Every time I run my code, it creates a new excel file (what I originally intended). But to save time, I was wondering if it would be possible to write onto an existing excel file by adding new columns of data without writing over the existing data?

######### This is my original code ################
import pandas as pd
from pandas import ExcelWriter
from pandas import ExcelFile
from pandas import DataFrame
from openpyxl import Workbook

df1 = pd.DataFrame(returns1)
df2 = pd.DataFrame(returns2)


x1 = 'mypath/ '
x2 = datetime.datetime.now().strftime('%B %d, %Y %H-%M-%S')
x3 = '.xlsx'
destination = x1 + x2 + x3
writer = pd.ExcelWriter(destination, engine='xlsxwriter')

df1.to_excel(writer, sheet_name= 'Returns 1', index=False)
df2.to_excel(writer, sheet_name= 'Returns 2', index=False)
writer.save()

### Update

Code works! Thank you for everyone that helped, especially @zhqiat. Below is the final code. Does exactly what I wanted. I hope this will help others that run into the same issue I did.

df1 = pd.DataFrame(returns1)
df2 = pd.DataFrame(returns2)

book = load_workbook('mypath.xlsx')
writer = pd.ExcelWriter('mypath.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

df1.to_excel(writer, sheet_name='Returns1', startrow=0, startcol=writer.sheets['Returns1'].max_column, index=False)
df2.to_excel(writer, sheet_name='Returns2', startrow=0, startcol=writer.sheets['Returns2'].max_column, index=False)

This is what I wanted.
 A
100
120
119
225

Second Time
 A     B
100    98
120   100
119   105
125   111

Third Time
 A     B     C
100    98    106 
120   100    99
119   105    101
125   111    89

and so on...

Please also forgive me as I am fairly new to python. You wrote a good question, well-formatted and precise manner. Be with StackOverflow code of conduct and no need of apologies. — Kiran Maniya
– Kiran Maniya, Commented Sep 11, 2019 at 19:00
Why don't you just read the excel file and store it as a dataframe and append the series and write to the same excel sheet? — aunsid
– aunsid, Commented Sep 12, 2019 at 20:23
@aunsid could you provide an example on how I could do that? — quinnvdk
– quinnvdk, Commented Sep 12, 2019 at 20:33
Just use the openpyxl function for converting a dataframe to rows. — Charlie Clark
– Charlie Clark, Commented Sep 13, 2019 at 10:27
You're looping over the sheets in the workbook without it being necessary and without a dynamic loop. Inside your for sheetname in writer.sheets: loop, you are writing the output of df1 and df2 to the sheets. But for each sheet in the workbook, you execute the same commands (without any change) and this is how your output ends up getting duplicated. — zglin
– zglin, Commented Sep 15, 2019 at 19:33

zglin · Accepted Answer · 2019-09-12 20:43:24Z

1

Unfortunately appending to excel isn't a fully fledged feature in pandas.

In your case for a hacked together solution, you can use an excel writer object to stitch the sheets together.

It sounds like you want to add columns for all new data (not row) so you would likely need to determine the width of your spreadsheet with

maxcol = writer.sheets['SheetName'].max_column

** Edit, my bad, it is max_column not max_col **

Refer to a prior question from 2017 for the full code on how to add rows.

edited Sep 12, 2019 at 20:43

answered Sep 12, 2019 at 20:37

zglin

2,9192 gold badges18 silver badges26 bronze badges

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

1 Comment

quinnvdk Over a year ago

Wow!Thank you, that actually worked. However, I ran into another issue. I have explained that issue in the original post under UPDATE. Do you know why it might be duplicating the output?

aunsid · Accepted Answer · 2019-09-12 20:38:59Z

0

read the excel file and store it as a dataframe and append the series and write to the same excel sheet

# read wherever you have stored the file

prev_df = pd.read_excel('path to file.xlsx')

# convert new series to df
new_df =  pd.DataFrame(var1)

# join
df_to_write = prev_df.join(new_df)

# write to excel

answered Sep 12, 2019 at 20:38

aunsid

3972 silver badges10 bronze badges

Collectives™ on Stack Overflow

Writing Data to Existing Excel using pandas in python

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related