CODEWITHSUNDEEP
pythonpandasnan
saul
saul

Reputation: 299

fill NaN values with mean based on another column specific value

I want to fill the NaN values on my dataframe on column c with the mean for only rows who has as category B, and ignore the others.

print (df)
    Category   b    c
0   A          1  5.0
1   C          1  NaN
2   A          1  4.0
3   B          2  NaN
4   A          2  1.0
5   B          2  Nan
6   C          1  3.0
7   C          1  2.0
8   B          1  NaN

So what I'm doing for the moment is :

df.c = df.c.fillna(df.c.mean())

But it fill all the NaN values, while I want only to fill the 3rd, 5th and the 8th rows who had category value equal to B.

Upvotes: 1

Views: 261

Answers (2)

Andy L.
Andy L.

Reputation: 25269

Combine fillna with slicing assignment

df.loc[df.Category.eq('B'), 'c'] = (df.loc[df.Category.eq('B'), 'c'].
                                       fillna(df.c.mean()))

Out[736]:
  Category  b    c
0        A  1  5.0
1        C  1  NaN
2        A  1  4.0
3        B  2  3.0
4        A  2  1.0
5        B  2  3.0
6        C  1  3.0
7        C  1  2.0
8        B  1  3.0

Or a direct assignment with 2 masks

df.loc[df.Category.eq('B') & df.c.isna(), 'c'] = df.c.mean()

Out[745]:
  Category  b    c
0        A  1  5.0
1        C  1  NaN
2        A  1  4.0
3        B  2  3.0
4        A  2  1.0
5        B  2  3.0
6        C  1  3.0
7        C  1  2.0
8        B  1  3.0

Upvotes: 3

Amine Mchayaa
Amine Mchayaa

Reputation: 124

This would be the answer for your question: 

df.c = df.apply(
        lambda row: row['c'].fillna(df.c.mean()) if row['Category']=='B' else  row['c'] ,axis=1)

Upvotes: -1

Related Questions