6

I want to remove outliers based on percentile 99 values by group wise. 

 import pandas as pd
 df = pd.DataFrame({'Group': ['A','A','A','B','B','B','B'], 'count': [1.1,11.2,1.1,3.3,3.40,3.3,100.0]})

in output i want to remove 11.2 from group A and 100 from group b. so in final dataset there will only be 5 observations.

wantdf = pd.DataFrame({'Group': ['A','A','B','B','B'], 'count': [1.1,1.1,3.3,3.40,3.3]})

I have tried this one but I'm not getting the desired results

df[df.groupby("Group")['count'].transform(lambda x : (x<x.quantile(0.99))&(x>(x.quantile(0.01)))).eq(1)]
max
  • 4,141
  • 5
  • 26
  • 55
Kumar AK
  • 987
  • 3
  • 10
  • 23

2 Answers2

16

Here is my solution:

def is_outlier(s):
    lower_limit = s.mean() - (s.std() * 3)
    upper_limit = s.mean() + (s.std() * 3)
    return ~s.between(lower_limit, upper_limit)

df = df[~df.groupby('Group')['count'].apply(is_outlier)]

You can write your own is_outlier function

ryoung29
  • 169
  • 1
  • 3
  • 4
    This is much more concise and readable than the marked answer. – mc51 Aug 11 '20 at 13:23
  • If you want percentile instead of 3 SD, use `s.quantile(.01)` for lower limit and `s.quantile(.99)` for upper limit – ryoung29 Feb 10 '21 at 17:22
  • btw, this doesnt remove remove 11.2 from group A and 100 from group b with +/3 SD or +/- 2SD. Only with 1 SD they get removed. Is it because of very low no of values ? 11.2 and 100 seemed to be outliers just by looking at them – Gupta Dec 20 '21 at 09:24
  • I think this might be happening due to very small sample size and the assumption of normality is not right. Refer : https://stats.stackexchange.com/questions/78609/outlier-detection-in-very-small-sets – Gupta Dec 20 '21 at 09:34
4

I don't think you want to use quantile, as you'll exclude your lower values:

import pandas as pd
df = pd.DataFrame({'Group': ['A','A','A','B','B','B','B'], 'count': [1.1,11.2,1.1,3.3,3.40,3.3,100.0]})
print(pd.DataFrame(df.groupby('Group').quantile(.01)['count']))

output:

       count
Group       
A        1.1
B        3.3

Those aren't outliers, right? So you wouldn't want to exclude them.

You could try setting left and right limits by using standard deviations from the median maybe? This is a bit verbose, but it gives you the right answer:

left = pd.DataFrame(df.groupby('Group').median() - pd.DataFrame(df.groupby('Group').std()))
right = pd.DataFrame(df.groupby('Group').median() + pd.DataFrame(df.groupby('Group').std()))

left.columns = ['left']
right.columns = ['right']

df = df.merge(left, left_on='Group', right_index=True)
df = df.merge(right, left_on='Group', right_index=True)

df = df[(df['count'] > df['left']) & (df['count'] < df['right'])]
df = df.drop(['left', 'right'], axis=1)
print(df)

output:

  Group  count
0     A    1.1
2     A    1.1
3     B    3.3
4     B    3.4
5     B    3.3
Troy D
  • 2,093
  • 1
  • 14
  • 28