group by in python with columns having N/A values

Question

I have a dataframe frame:

                    x                  y                z    city
        0       -0.459476              NaN              NaN   hyd
        0             NaN        20.439870              NaN   hyd
        0             NaN              NaN         1.142743   hyd
        0             N/A              NaN              NaN   pune
        0             NaN         9.827238              NaN   pune
        0             NaN              NaN       -99.950162   pune

I want the above data frame to group by city and produce the below result:

                x                      y              z    city
            -0.459476              20.439870      1.142743 hyd
               N/A                 9.827238     -99.950162 pune

I am using the below code:

newdf = frame[frame.columns[:3]]
newdf1 = list(newdf.columns.values)
df1 = frame.groupby(city)[newdf1].sum().reset_index()

The result does not give me the column x as it as N/A.

How do I resolve this?

Answer 1

groupby with ffill bfill then drop_duplicates

df.groupby('city').ffill().bfill().drop_duplicates(keep='first')

          x          y          z  city
0 -0.459476  20.439870   1.142743   hyd
3       N/A   9.827238 -99.950162  pune