Calculate mean of df, BUT if =>1 of the values differs >20% from this mean, the mean is set to NaN

user_12152992 0
Viewed 78

I want to calculate the mean of columns a,b,c,d of the dataframe BUT if one of four values in each dataframe row differs more then 20% from this mean (of the four values), the mean has to be set to NaN.

Calculation of the mean of 4 columns is easy, but I'm stuck at defining the condition 'if mean*0.8 <= one of the values in the data row <= mean*1,2 then mean == NaN.

In the example, one or more of the values in ID:5 en ID:87 don't fit in the interval and therefore the mean is set to NaN. (NaN-values in the initial dataframe are ignored when calculating the mean and when applying the 20%-condition to the calculated mean)

So I'm trying to calculate the mean only for the data rows with no 'outliers'.

Initial df:

 ID   a    b    c   d
  2  31   32   31  31
  5  33   52  159   2
  7  51  NaN   52  51 
 87  30   52  421   2
 90  10   11   10  11
102  41   42  NaN  42

Desired df:

 ID   a    b    c   d    mean
  2  31   32   31  31   31.25
  5  33   52  159   2     NaN
  7  51  NaN   52  51   51.33
 87  30   52  421   2     NaN
 90  10   11   10  11   10.50
102  41   42  NaN  42   41.67

Code:

import pandas as pd

import numpy as np



df = pd.DataFrame({"ID": [2,5,7,87,90,102],
    
                    "a": [31,33,51,30,10,41],
     
                    "b": [32,52,np.nan,52,11,42],
      
                    "c": [31,159,52,421,10,np.nan],
  
                    "d": [31,2,51,2,11,42]})


print(df)



a = df.loc[:, ['a','b','c','d']]


df['mean'] = (a.iloc[:,0:]).mean(1)


print(df)


b = df.mean.values[:,None]*0.8 < a.values[:,:] < df.mean.values[:,None]*1.2
print(b)
...
pandas mean nan python
1 Answers

Try this:

# extract related information
s = df.iloc[:,1:]

# calculate mean
mean = s.mean(1)

# where condition is violated    
mask = s.lt(mean*.8, axis=0) | s.gt(mean*1.2, axis=0)

# mask where mask is True on any row
df['mean'] = mean.mask(mask.any(1))

Output:

    ID   a     b      c   d       mean
0    2  31  32.0   31.0  31  31.250000
1    5  33  52.0  159.0   2        NaN
2    7  51   NaN   52.0  51  51.333333
3   87  30  52.0  421.0   2        NaN
4   90  10  11.0   10.0  11  10.500000
5  102  41  42.0    NaN  42  41.666667
user_42384080
Related