Concatenate the column values with above rows when other columns are empty

Question

I have a data frame like this,

df
col1      col2     col3
 1         ab       4
           hn       
           pr       
 2         ff       3
 3         ty       3
           rt       
 4         ym       6

Now I want to create one data frame from above, if both col1 and col3 values are empty('') just append(concatenate) it with above rows where both col3 and col1 values are present.

So the final data frame will look like,

df
col1      col2     col3
 1       abhnpr     4
 2         ff       3
 3       tyrt       3
 4        ym       6

I could do this using a for loop and comparing one with another row, but the execution time will be more, so looking for short cuts (pythonic way) to do the same task most efficiently.

jezrael jezrael · Accepted Answer · 2020-02-13T08:11:25

Replace empty values to mising values and then forward filling them, then use aggregate join by GroupBy.agg and last reorder columns by DataFrame.reindex:

c = ['col1','col3']
df[c] = df[c].replace('', np.nan).ffill()
df = df.groupby(c)['col2'].agg(''.join).reset_index().reindex(df.columns, axis=1)
print (df)
  col1    col2 col3
0    1  abhnpr    4
1    2      ff    3
2    3    tyrt    3
3    4      ym    6

Concatenate the column values with above rows when other columns are empty

1 Answers