Click here to Skip to main content
15,212,533 members
Rate this:
Please Sign up or sign in to vote.
See more:
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()


What I have tried:

############ Update ##############

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...
Posted
Updated 2-Jun-20 4:31am
v6
Comments
Richard MacCutchan 12-Sep-19 11:18am
   
You need a method in the Excel library that will find the first blank column for you. Although looking at your examples above, I get the impression you are trying to add rows not columns.
quinnvdk 12-Sep-19 11:21am
   
Is there python syntax that allows for me to find the next blank column? And no, Im looking for columns. I just edited the question to reflect adding columns, my apologies.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You can get the last row used (or last column used) with max_row (or max_col) e.g. (NB untested)
wb = openpyxl.load_workbook('mypath.xlsx')
ws = wb.active

mr = ws.max_row
mc = ws.max_column
MaxU has provided a helper function to do this at Append existing excel sheet with new dataframe using python pandas[^]
   
Comments
quinnvdk 12-Sep-19 12:38pm
   
@CHill60 Would it be possible to show how I would implement this into my code? I was trying to use the example from MaxU and no data was written to the excel file.
CHill60 13-Sep-19 4:30am
   
I'm confused - you've posted the code to implement it in your latest version of the post. Here you are saying no data is written to the file but in your amended post you're saying the data is duplicated. If you are duplicating Columns then try using header=none (as suggested by MaxU)
quinnvdk 13-Sep-19 9:28am
   
Sorry for the confusion. I posted the update after I replied to your comment. The code now writes to the excel file. But for some strange reason, it writes the output twice (Example.... First time: same output in column A and B. Second time: same output in column C and D).
Maciej Los 13-Sep-19 3:19am
   
5ed!
CHill60 13-Sep-19 4:31am
   
Thank you! I've just started using Python myself so I was really pleased when I knew the solution to this one (still googled it to check my answer though :-) )
Maciej Los 13-Sep-19 4:44am
   
:thumbsup:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100