.combine_first for merging multiple rows

Question

I have a pandas dataframe (df) where there are duplicating rows for some of the rows. Some columns in these repeating rows have NaN values while similar columns in the duplicated rows have values. I would like to merge the duplicating rows such that the missing values are replaced with the values from the duplicating rows and then dropping the duplicated rows. For examples the following are duplicated rows:

     id   col1   col2   col3
0    01   abc           123
9    01           xy   

The result should be like:

     id   col1   col2   col3
0    01   abc     xy     123

I tried .combine_first by using df.iloc[0:1,].combine_first(df.iloc[9:10,]) but no success. Can anybody help me with this? Thanks!

Answer 1

I think you need groupby with forward and back filling NaNs and then drop_duplicates:

print (df)
   id col1 col2   col3
0   1  abc  NaN  123.0
9   1  NaN   xy    NaN
0   2  abc  NaN   17.0
9   2  NaN   xr    NaN
9   2  NaN   xu    NaN


df = df.groupby('id').apply(lambda x: x.ffill().bfill()).drop_duplicates()
print (df)
   id col1 col2   col3
0   1  abc   xy  123.0
0   2  abc   xr   17.0
9   2  abc   xu   17.0