How to append a pandas dataframe to an excel sheet

Question

Hi i have seen solutions to more complicated problems but am trying to do the following:

Append a dataframe to an excel table. I have an excel file which contains data like:

# Create Initial Excel 
data = [['tom', 10,1,'a'], ['matt', 15,5,'b'],['nick', 18,2,'b'],['luke', 12,6,'b'],['geoff', 20,10,'a']]

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Attempts','Score','Category']) 
df
    Name    Attempts    Score   Category
0   tom     10             1    a
1   matt    15             5    b
2   nick    18             2    b
3   luke    12             6    b
4   geoff   20             10   a

df.to_excel('Excel.xlsx',index=False)

Each week i get new data in the form of:

  #New Dataframe
    data2 = [['mick', 10,1,'a'], ['matt', 15,5,'b'],['jim', 18,2,'b'],['mark', 12,6,'b'],['geoff', 20,10,'a']]
    df2 = pd.DataFrame(data2, columns = ['Name', 'Attempts','Score','Category']) 
    df2
        Name    Attempts    Score   Category
    0   mick    10             1    a
    1   matt    15             5    b
    2   jim     18             2    b
    3   mark    12             6    b
    4   geoff   20            10    a

I have tried the following to append the new data underneath the spreadsheet data:

#Append DF2

with pd.ExcelWriter('Excel.xlsx',
                    mode='a') as writer:
    df2.to_excel(writer,'Sheet1',index=False,header=False)

But it has appended to a new sheet?

I am just hoping to add to my excel so that it appears:

Name    Attempts    Score   Category
0   tom     10             1    a
1   matt    15             5    b
2   nick    18             2    b
3   luke    12             6    b
4   geoff   20             10   a
0   tom     10             1    a
1   matt    15             5    b
2   nick    18             2    b
3   luke    12             6    b
4   geoff   20             10   a

I posted a answer to this in a similar question stackoverflow.com/a/75222516/21073184 — Rodney Souza
– Rodney Souza, Commented Jan 24, 2023 at 15:56

SOK · Accepted Answer · 2020-06-23 11:59:37Z

6

I found my answer here append dataframe to excel with pandas and the specifics for my question

from openpyxl import load_workbook

path = "Excel.xlsx"
book = load_workbook(path)
writer = pandas.ExcelWriter("Excel.xlsx", engine='openpyxl')
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}


df2.to_excel(writer, startrow=writer.sheets['Sheet1'].max_row, index = False,header= False)

writer.save()

answered Jun 23, 2020 at 11:59

SOK

1,8122 gold badges19 silver badges47 bronze badges

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

Comments

PM 77-1 · Accepted Answer · 2022-03-23 22:28:53Z

2

#For Python 3.9,

df = pd.DataFrame(data={'a':[433], 'b':['corn'], 'c':[0.544]})
df.to_excel("test1.xlsx")

import openpyxl 

df1 = pd.DataFrame(data={'a':[4], 'b':['corn'], 'c':[0.5]})     
wb = openpyxl.load_workbook("test1.xlsx") 
  
sheet = wb.active 

for index, row in df1.iterrows():

    sheet.append(row.values.tolist())
  
wb.save('test1.xlsx')

edited Mar 23, 2022 at 22:28

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

answered Mar 23, 2022 at 19:13

Parijat Mishra

212 bronze badges

Comments

Javier Palomino · Accepted Answer · 2020-06-23 04:17:25Z

0

Here an approach you can try, something similar was mentioned in this answer. What I suggest is that first concatenate your 2 data-frames then write to the Excel file, instead of trying to merge the Excel files.

import pandas as pd

# Create Initial Excel 
data = [['tom', 10,1,'a'], ['matt', 15,5,'b'],['nick', 18,2,'b'],['luke', 12,6,'b'],['geoff', 20,10,'a']]

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Attempts','Score','Category']) 

#New Dataframe
data2 = [['mick', 10,1,'a'], ['matt', 15,5,'b'],['jim', 18,2,'b'],['mark', 12,6,'b'],['geoff', 20,10,'a']]
df2 = pd.DataFrame(data2, columns = ['Name', 'Attempts','Score','Category']) 

# Prepare a list of the dataframes, needed for the concat method
all_df = [df, df2]

out_df = pd.concat(all_df).reset_index(drop=True)

# Write concatenated dataframes to Excel
out_df.to_excel("Combined.xlsx", index=False)

answered Jun 23, 2020 at 4:17

Javier Palomino

113 bronze badges

3 Comments

SOK Over a year ago

Thanks @Javier Palomino. I probably didnt make myself clear but my excel file sits permanently in the directory and i get the new data weekly so df and df2 wont be made at the same time. Unless I write code to reimport from the spreadsheet each week to combine which could be an option

Javier Palomino Over a year ago

Thank you for your feedback @SOK, maybe this approach could help you, in the answer posted here stackoverflow.com/a/47738103/13280881

SOK Over a year ago

Ah greta - perfect that answers my questions. I must have missed that trying to find an answer. Aprrectiate the heads up

Collectives™ on Stack Overflow

How to append a pandas dataframe to an excel sheet

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related