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?
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()
What I have tried:
Got it to work! This is the final code! Does exactly what I wanted. I hope this will help others who run into the same problem I was having.
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 the code to do...
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...