[FIXED] Concating DataFrames in a Loop just overwriting instead of appending Dataframes

Issue

Trying to concat selected dataframes in a loop as always appending/concatenating/overwriting the last series into the the df_final

How can I fix this?

columns = ['Col1','Col2', 'Col3']
step_a = 1
step_b = 7
df_c = pd.DataFrame(columns=columns)
while step_b <= 601:
    service = df.columns[step_a]
    last_slash_index = service.rfind('/')
    last_dot_index = service.rfind('.')
    service = service[last_slash_index + 1:last_dot_index]

    df_selected = df.iloc[:,[0] + list(range(step_a, step_b))]
    df_b = df_selected.values
    df_b = pd.DataFrame(df_b, columns=None)
    df_b.columns = columns
    df_b['Service']= service 
    df_final = pd.concat([df_c, df_b],axis=0)
    step_a += 6
    step_b += 6

df_final.to_csv('table1-final.csv')

Solution

While your question is unclear and would benefit from a reproducible example, I believe you issue is due to using a different name for your final dataframe and the intermediate in the loop.

Here you just overwrite the previous step

df_final = pd.concat([df_c, df_b],axis=0)

Just use df_final in your code wherever you used df_c.

Now be aware that, unless you really need the intermediates, using concat in a loop is inefficient. Better collect your dataframes in a list and only run concat once in the end:

columns = ['Col1','Col2', 'Col3']
step_a = 1
step_b = 7

lst = []                                                     # HERE

while step_b <= 601:
    service = df.columns[step_a]
    last_slash_index = service.rfind('/')
    last_dot_index = service.rfind('.')
    service = service[last_slash_index + 1:last_dot_index]

    df_selected = df.iloc[:,[0] + list(range(step_a, step_b))]
    df_b = df_selected.values
    df_b = pd.DataFrame(df_b, columns=None)
    df_b.columns = columns
    df_b['Service']= service 
    lst.append(df_b)                                         # HERE
    step_a += 6
    step_b += 6

df_final = pd.concat(lst, axis=0)

Answered By - mozway