python - Fill missing values with the most common value in the grouped form

Question

Could anybody help me with fill missing values with the most common value but grouped form? .Here I want to fill missing value of cylinders columns with the same model of cars.

I tried this :

sh_cars['cylinders']=sh_cars['cylinders'].fillna(sh_cars.groupby('model')['cylinders'].agg(pd.Series.mode))

and other ones but I got everytime error messages.

Thanks in advance.

question from:https://stackoverflow.com/questions/65950425/fill-missing-values-with-the-most-common-value-in-the-grouped-form

Answer 1

I think problem is there are only NaNs per some (or all) groups, so error is raised. Possible solution is use custom function with GroupBy.transform for return Series with same size like original DataFrame:

data = {'model':['a','a','a','a','b','b','a'], 
        'cylinders':[2,9,9,np.nan,np.nan,np.nan,np.nan]}

sh_cars = pd.DataFrame(data) 

f = lambda x: x.mode().iat[0] if x.notna().any() else np.nan
s = sh_cars.groupby('model')['cylinders'].transform(f)
sh_cars['new']=sh_cars['cylinders'].fillna(s)
print (sh_cars)
  model  cylinders  new
0     a        2.0  2.0
1     a        9.0  9.0
2     a        9.0  9.0
3     a        NaN  9.0
4     b        NaN  NaN
5     b        NaN  NaN
6     a        NaN  9.0

Replace original column:

f = lambda x: x.mode().iat[0] if x.notna().any() else np.nan
s = sh_cars.groupby('model')['cylinders'].transform(f)
sh_cars['cylinders']=sh_cars['cylinders'].fillna(s)
print (sh_cars)
  model  cylinders
0     a        2.0
1     a        9.0
2     a        9.0
3     a        9.0
4     b        NaN
5     b        NaN
6     a        9.0