Append python dataframe not appending properly

Question

I have the following code:

import pandas as pd
df2 = pd.DataFrame(dataset)

df = pd.read_excel (r'C:/adhoc/test.xlsx')

df.append(df2,ignore_index=True)

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(r'C:/adhoc/test.xlsx', engine='xlsxwriter')

# Convert the dataframe to an XlsxWriter Excel object.
df.to_excel(writer, sheet_name='Sheet1')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

I am trying to take df2 (the current dataset) and append it into the already exiting data (df) and write that all back into the file. Essentially take the new data and append it to the bottom of the sheet. When I run this code all it does it add an extra index column beside the already exiting index column

perhaps using a with statement to make sure the writer is closing. Example from the docs: with ExcelWriter('path_to_file.xlsx', mode='a') as writer: df.to_excel(writer, sheet_name='Sheet3') — Docuemada
– Docuemada, Commented Mar 15, 2021 at 19:27

Patrick Bormann · Accepted Answer · 2021-03-15 19:24:39Z

1

If you are feeling fine with a third merged data frame this produced a working solution: df1 = pd.concat([df, df2], ignore_index=True) instead of append. The solution lies in creating a new variable it doesnt matter if its append or concat, but with concat it seems a little bit clearer I'll guess.

answered Mar 15, 2021 at 19:24

Patrick Bormann

7497 silver badges18 bronze badges

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

Comments

Michael Myrick · Accepted Answer · 2022-10-18 14:52:00Z

I'm having a similar problem as the original poster and I've arrived at I must not be appending my data properly or I'm not saving my file...problem is the solutions on the web are not seeming to work and/or Imma nub!

My program is supposed to append the last row of what I'm pulling into an excel sheet. Building a log.

only problem = it just saves over the data there or just creates a new excel sheet which it puts the data into...so that all i have the 1 row of data with the proper column names.

from sqlalchemy import false import yfinance as yf import pandas as pd from openpyxl import load_workbook import schedule import time from playsound import playsound

def func():

schedule.every(1).minutes.do(func)

while(True): schedule.run_pending() time.sleep(60)

spy = yf.Ticker('spy')

spy_history = spy.history(period="max")
spy_history = spy.history(start="2022-10-17", end="2022-10-19",interval="1m", actions = False)
spy_history = spy.history(start="2022-10-17", end="2022-10-19",interval="1m",prepost = True, actions = False)

pd.options.display.width = 0
pd.set_option("display.max_rows",50)

df = spy_history
most_recent = df.tail(1)
most_recent.to_excel('spyder.xlsx', index = False, columns=['Open', 'High', 'Low', 'Close', 'Volume'])

print (spy_history)
print (df.iloc[-1,-1])
mike = df.iloc[-1,-1]

if mike >=0:
    playsound('imp.wav')

Dharman · Accepted Answer · 2021-03-15 19:26:19Z

0

The method "append" doesn't change the dataframe, instead it returns another dataframe with the data of both dataframes. Therefore, you have to save the new dataframe in a variable. The code should resemble the following:

    df2 = df.append(df2, ignore_index=True)
    df2.to_excel('mi_file.xlsx', sheet_name = 'Sheet1')

edited Mar 15, 2021 at 19:26

Dharman♦

33.9k27 gold badges103 silver badges156 bronze badges

answered Mar 15, 2021 at 19:20

AarónRF

312 bronze badges

1 Comment

pandasnew123455 Over a year ago

Thank you that works. The only thing that is left is that it's still appending an extra column 'Unamed: 'x' number' every time its run. I thought the ignore index ignores that part.

Collectives™ on Stack Overflow

Append python dataframe not appending properly

3 Answers 3

Comments

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Related