How to append a dataframe without overwriting existing dataframe

Question

Each time the function is called, 2 rows are written in the Excel file, but at the end of the application there is only the last 2 row in the file. Every time i call the Function the two new row overwritte the last two rows. I think it's because of this line:

    df_write = pd.concat([df_write, df1], ignore_index=True)
    df_write = pd.concat([df_write, df2], ignore_index=True)

    df_write.to_excel(df_write_path)

from main.py

import pandas as pd        
# Open file 
    df_read_path = 'Data/Bauteilliste.xlsx'
    df_read = pd.read_excel(df_read_path)

    # Set display definitions on Jupyter
    pd.set_option('display.max_columns', 75)

    # Create file to write sorted row's from df_read
    df_write = pd.DataFrame([],
                       [],
                       columns=['Bez', 'KZ', 'A', 'B', 'D','D1','D2','D3', 'L', 'Stk', 'IsoOf', 'LtgTyp'])
    df_write_path = 'Data/Bauteilliste_sortiert.xlsx'

row_counter = 0

for row in df_read['KZ']:
    if df_read.at[row_counter, 'KZ'] == 'R-R':
        sort_component(df_read, df_write, df_read_path, df_write_path, row_counter, 'L')
        row_counter += 1
    else:
        row_counter += 1

from function

    # append new row to df_write with filtered R-R Element
    df_write = df_write.append({
        'Bez': 'Wickelfalzrohr nicht isoliert', 
        'KZ': 'R-R',
        'D': df_read.at[row_counter, 'D'],
        'L': counter_without_isolation
    }, ignore_index=True, sort=False)

    df_write = df_write.append({
        'Bez': 'Wickelfalzrohr', 
        'KZ': 'R-R',
        'D': df_read.at[row_counter, 'D'],
        'L': counter_with_isolation
    }, ignore_index=True, sort=False)

        df_write = pd.concat([df_write, df1], ignore_index=True)
        df_write = pd.concat([df_write, df2], ignore_index=True)

    df_write.to_excel(df_write_path)

Output:

before row
160.0
before row
355.0
before row
200.0
before row
100.0
before row
125.0
before row
100.0
before row
200.0
before row
250.0
before row
160.0
before row
250.0
before row
125.0
before row
125.0
before row
160.0
before row
125.0

df_read.head()

df_write excel file with 2 rows, it schould be 10+

main.py

def sort_component.py

Does anyone know how I can solve the problem? Do I have to save the df_write file in main.py so that the Excel file is not saved until the end of the for-slice?

Please [provide a reproducible copy of the DataFrames with `df.head(10).to_clipboard(sep=',')`](https://stackoverflow.com/questions/52413246/how-to-provide-a-copy-of-your-dataframe-with-to-clipboard) and what the expected output should look like, because it's not clear. — Trenton McKinney, May 13 '20 at 20:11
In general, if `df` is your existing df and `df_app` is the dataframe you want to append at the row level, you should use - `df = pd.concat([df, df_app], ignore_index=True)` — tidakdiinginkan, May 14 '20 at 09:08
@tidakdiinginkan Thanks for your answer, but my application is still not working. I think the problem is because I save the Excel file in the function and not in the main.py, I set the value df_write every time to a new dictionarie {} — Tiago, May 14 '20 at 16:49

score 0 · Accepted Answer · answered May 16 '20 at 11:07

After a long search I found a solution that worked.

Function:

    df1 = pd.DataFrame([['Wickelfalzrohr isoliert', 'R-R', df_read.at[row_counter, 'D'], counter_with_isolation ]],
               columns=['Bez', 'KZ', 'D','L'])

    df2 = pd.DataFrame([['Wickelfalzrohr nicht isoliert', 'R-R', df_read.at[row_counter, 'D'], counter_without_isolation ]],
               columns=['Bez', 'KZ', 'D','L'])

    df3 = pd.concat([df1, df2])

    return df3

Main.py

# Open file 
df_read_path = 'Data/Bauteilliste.xlsx'
df_read = pd.read_excel(df_read_path)

# Set display definitions on Jupyter
pd.set_option('display.max_columns', 75)

#Path to save the new Excel file
df_write_path = 'Data/Bauteilliste_sortiert.xlsx'
row_counter = 0
df4 = []
for row in df_read['KZ']:
    if df_read.at[row_counter, 'KZ'] == 'R-R':
        row_append = sort_component(df_read, df_read_path, df_write_path, row_counter, 'L')
        df4.append(row_append)
        row_counter += 1
    else:
        row_counter += 1

df_write = pd.concat(df4, ignore_index=True)

df_write.to_excel('Data/Bauteilliste_concat.xlsx')

How to append a dataframe without overwriting existing dataframe

1 Answers1