6

I have next pandas DataFrame:

a b c 
1 1 5.0
1 1 None
1 1 4.0
1 2 1.0
1 2 1.0
1 2 4.0 
2 1 3.0
2 1 2.0
2 1 None
2 2 3.0
2 2 4.0

I want to replace None, but not by the column mean. I want to select all rows, where the values in a and b are similar and if c has a None-values in selected rows, replace them only with the c-mean of selected rows. Something like (this code doesn't work):

df[df[('a'==1) & ('b'==1)]]['c'].fillna(df[df[('a'==1) & ('b'==1)]]['c'].mean())

which should get me the output:

a b c 
1 1 5.0
1 1 4.5
1 1 4.0
1 2 1.0
1 2 1.0
1 2 4.0 
2 1 3.0
2 1 2.0
2 1 None
2 2 3.0
2 2 4.0
user495490
  • 77
  • 2
  • 7

1 Answers1

14

You need filter values of c by conditions and assign back column c:

mask = (df['a']==1) & (df['b']==1)
mean = df.loc[mask, 'c'].mean()
df.loc[mask, 'c'] = df.loc[mask, 'c'].fillna(mean)

Or use mask for replace by conditions:

df['c'] = df['c'].mask(mask, df['c'].fillna(mean))
#similar
#df['c'] = np.where(mask, df['c'].fillna(mean), df['c'])

print (df)
    a  b    c
0   1  1  5.0
1   1  1  4.5
2   1  1  4.0
3   1  2  1.0
4   1  2  1.0
5   1  2  4.0
6   2  1  3.0
7   2  1  2.0
8   2  1  NaN
9   2  2  3.0
10  2  2  4.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • What if the input df has a NaN where the 'a' and 'b' values match other than 1, say for example 'a' =2 and 'b' =2 – prabhakar Apr 22 '18 at 07:26
  • @prabhakar - There is no `NaN`, so no replace. But maybe missing, what you think. Can you explain more? – jezrael Apr 22 '18 at 07:28
  • @jezrael, you are right. Not all combination of values from a and b should have NaN values. Only values in random rows... – user495490 Apr 22 '18 at 07:31
  • 1
    My doubt is out of the question. Just a bit curious to know if there is such 'NaN' in the input, with 'a' and 'b' equal to 2. Sorry to bother you. – prabhakar Apr 22 '18 at 07:31