Fill in the blank value

Question

I have a dataframe

   A      B
0  1  100.0
1  1    NaN
2  1  200.0
3  2  100.0
4  2    NaN

Group according to column A, then find the average of each group B column, and finally complete the null value of column B. The expected output is as follows.

   A    B
0  1  100
1  1  150
2  1  200
3  2  100
4  2  100

Answer 1

You want to use group by on column A and then only apply the mean value of A where column B == NaN

df['B'] = df.groupby('A')[df['B' == NaN].mean(df['A']) 

Something like this should work!

Answer 2

You can do:

df.loc[df.B.isnull(), 'B'] = df.groupby('A').B.transform('mean')

It checks if B is null, if yes, group it by A and take the mean and replace the value.