[FIXED] Appending new rows to a pandas dataframe is giving unexpected result

Issue

sorry if this a stupid question but I cant find a solution.

I am running a model calculation and putting the key output variables into a list to append to a master record of results. A new row should be added every time the model is run. The problem is after a row is added a new index column is created, shifting the older data progressively to the right. I've tried everything to suppress this but obviously missing something - it should be so simple!

model_output is the new list of variables to append as df.new_row

df_old is the archive file residing on a cloud server to be appended and resaved.

here is the resulting file showing the extra columns that appear each time a new line is added

here is what I want

here is the code - I have tried set_index, reset_index etc

How do suppress the new index column each time? Many thanks, dave

# append results to model output

    model_output = [[date_today, bh_level_today, bh_change_today, PminusET_today, model, movement, proj_date_end,proj_depth_end]]
                    
    df_newrow = pd.DataFrame(model_output, columns=['date', 'height', 'rate', 'P_ET', 'model', 'statement', 'model_date','model_height'])
                    
  
# read model_output archive file and append new line
    df_old = pd.read_csv("ftp://............../wwwhome/FEM_model_output/model_output.csv")
    df_old.set_index('date')

    df_new = pd.concat([df_old, df_newrow], ignore_index=True, axis=0)
    df_new.set_index('date')
    df_new.reset_index(inplace=True, drop=True)

 # resave file
    buffer = io.StringIO()
    df_new.to_csv(buffer)
    text = buffer.getvalue()
    bio = io.BytesIO(str.encode(text))

    ftp.storbinary('STOR %s' % os.path.basename("model_output.csv"), bio)

    print('data uploaded')

    ftp.quit()

Solution

By default, pd.DataFrame.to_csv saves the index as a column in the csv file. Try:

df_new.to_csv(buffer, index=False)

or, when reading:

df_old = pd.read_csv("ftp://............../wwwhome/FEM_model_output/model_output.csv", index_col=0)

The former is probably better since you set your own indices based on date, etc. (although it appears you override that with reset_index. Perhaps that was added to resolve your problem). But the alternative might work for others who end up at this question.

Answered By - Ryan Dempsey