Detect and exclude outliers in Pandas data frame

Question

I have a pandas data frame with few columns   Now I know that certain rows are outliers based on a certain column value   For instance     column  Vol  has all values around 12xx and one value is 4000  outlier     Now I would like to exclude those rows that have Vol column like this   So  essentially I need to put a filter on the data frame such that we select all rows where the values of a certain column are within  say  3 standard deviations from mean   What is an elegant way to achieve this

User · Answer

a full example with data and 2 groups follows:

Imports:

from StringIO import StringIO
import pandas as pd
#pandas config
pd.set_option('display.max_rows', 20)

Data example with 2 groups: G1:Group 1. G2: Group 2:

TESTDATA = StringIO("""G1;G2;Value
1;A;1.6
1;A;5.1
1;A;7.1
1;A;8.1

1;B;21.1
1;B;22.1
1;B;24.1
1;B;30.6

2;A;40.6
2;A;51.1
2;A;52.1
2;A;60.6

2;B;80.1
2;B;70.6
2;B;90.6
2;B;85.1
""")

Read text data to pandas dataframe:

df = pd.read_csv(TESTDATA, sep=";")

Define the outliers using standard deviations

stds = 1.0
outliers = df[['G1', 'G2', 'Value']].groupby(['G1','G2']).transform(
           lambda group: (group - group.mean()).abs().div(group.std())) > stds

Define filtered data values and the outliers:

dfv = df[outliers.Value == False]
dfo = df[outliers.Value == True]

Print the result:

print '\n'*5, 'All values with decimal 1 are non-outliers. In the other hand, all values with 6 in the decimal are.'
print '\nDef DATA:\n%s\n\nFiltred Values with %s stds:\n%s\n\nOutliers:\n%s' %(df, stds, dfv, dfo)

User · Answer

Another option is to transform your data so that the effect of outliers is mitigated  You can do this by winsorizing your data    import pandas as pd from scipy stats import mstats  matplotlib inline  test data   pd Series range 30   test data plot         Truncate values to the 5th and 95th percentiles transformed test data   pd Series mstats winsorize test data  limits  0 05  0 05     transformed test data plot

User · Answer

If you like method chaining  you can get your boolean condition for all numeric columns like this   df sub df mean    div df std    abs   lt 3    Each value of each column will be converted to True False based on whether its less than three standard deviations away from the mean or not

User · Answer

Use boolean indexing as you would do in numpy array  df   pd DataFrame   Data  np random normal size 200      example dataset of normally distributed data    df np abs df Data-df Data mean     lt    3 df Data std       keep only the ones that are within  3 to -3 standard deviations in the column  Data    df   np abs df Data-df Data mean     gt   3 df Data std        or if you prefer the other way around   For a series it is similar   S   pd Series np random normal size 200   S    S-S mean    abs    gt  3 S std

User · Answer

Since I haven t seen an answer that deal with numerical and non-numerical attributes  here is a complement answer   You might want to drop the outliers only on numerical attributes  categorical variables can hardly be outliers    Function definition  I have extended  tanemaki s suggestion to handle data when non-numeric attributes are also present   from scipy import stats  def drop numerical outliers df  z thresh 3         Constrains will contain  True  or  False  depending on if it is a value below the threshold      constrains   df select dtypes include  np number              apply lambda x  np abs stats zscore x    lt  z thresh  reduce False             all axis 1        Drop  inplace  values set to be rejected     df drop df index  constrains   inplace True    Usage  drop numerical outliers df    Example  Imagine a dataset df with some values about houses  alley  land contour  sale price      E g  Data Documentation   First  you want to visualise the data on a scatter graph  with z-score Thresh 3      Plot data before dropping those greater than z-score 3     The scatterAreaVsPrice function s definition has been removed for readability s sake  scatterAreaVsPrice df        Drop the outliers on every attributes drop numerical outliers train df     Plot the result  All outliers were dropped  Note that the red points are not   the same outliers from the first plot  but the new computed outliers based on the new data-frame  scatterAreaVsPrice train df

User · Answer

Deleting and dropping outliers I believe is wrong statistically   It makes the data different from original data  Also makes data unequally shaped and hence best way is to reduce or avoid the effect of outliers by log transform the data  This worked for me   np log data iloc

User · Answer

For each series in the dataframe  you could use between and quantile to remove outliers   x   pd Series np random normal size 200     with outliers x   x x between x quantile  25   x quantile  75      without outliers

User · Answer

You can use boolean mask   import pandas as pd  def remove outliers df  q 0 05       upper   df quantile 1-q      lower   df quantile q      mask    df  lt  upper   amp   df  gt  lower      return mask  t   pd DataFrame   train    1 1 2 3 4 5 6 7 8 9 9                      y    1 0 0 1 1 0 0 1 1 1 0     mask   remove outliers t  train    0 1   print t mask     output      train  y 2      2  0 3      3  1 4      4  1 5      5  0 6      6  0 7      7  1 8      8  1

User · Answer

Since I am in a very early stage of my data science journey  I am treating outliers with the code below    Outlier Treatment  def outlier detect df       for i in df describe   columns          Q1 df describe   at  25   i          Q3 df describe   at  75   i          IQR Q3 - Q1         LTV Q1 - 1 5   IQR         UTV Q3   1 5   IQR         x np array df i           p            for j in x              if j  lt  LTV or j gt UTV                  p append df i  median                else                  p append j          df i  p     return df

User · Answer

I prefer to clip rather than drop   the following will clip inplace at the 2nd and 98th pecentiles   df list   list df  minPercentile   0 02 maxPercentile   0 98  for   in range numCols       df df list       df df list     clip  df df list     quantile minPercentile    df df list     quantile maxPercentile

User · Answer

If you have multiple columns in your dataframe and would like to remove all rows that have outliers in at least one column  the following expression would do that in one shot  df   pd DataFrame np random randn 100  3    from scipy import stats df  np abs stats zscore df    lt  3  all axis 1    description   For each column  first it computes the Z-score of each value in the column  relative to the column mean and standard deviation  Then is takes the absolute of Z-score because the direction does not matter  only if it is below the threshold  all axis 1  ensures that for each row  all column satisfy the constraint  Finally  result of this condition is used to index the dataframe   Filter other columns based on a single column  Specify a column for the zscore  df 0  for example  and remove  all axis 1    df  np abs stats zscore df 0     lt  3

User · Answer

------------------------------------------------------------------------------   accept a dataframe  remove outliers  return cleaned data in a new dataframe   see http   www itl nist gov div898 handbook prc section1 prc16 htm  ------------------------------------------------------------------------------ def remove outlier df in  col name       q1   df in col name  quantile 0 25      q3   df in col name  quantile 0 75      iqr   q3-q1  Interquartile range     fence low    q1-1 5 iqr     fence high   q3 1 5 iqr     df out   df in loc  df in col name   gt  fence low   amp   df in col name   lt  fence high       return df out

User · Answer

Get the 98th and 2nd percentile as the limits of our outliers  upper limit   np percentile X train logerror values  98   lower limit   np percentile X train logerror values  2    Filter the outliers from the dataframe data    target     loc X train    target     gt upper limit    upper limit data    target     loc X train    target     lt lower limit    lower limit

User · Answer

This answer is similar to that provided by  tanemaki  but uses a lambda expression instead of scipy stats   df   pd DataFrame np random randn 100  3   columns list  ABC     df df apply lambda x  np abs x - x mean      x std    lt  3  all axis 1     To filter the DataFrame where only ONE column  e g   B   is within three standard deviations   df   df B - df B mean      df B std    abs    lt  3    See here for how to apply this z-score on a rolling basis  Rolling Z-score applied to pandas dataframe

User · Answer

My function for dropping outliers  def drop outliers df  field name       distance   1 5    np percentile df field name   75  - np percentile df field name   25       df drop df df field name   gt  distance   np percentile df field name   75   index  inplace True      df drop df df field name   lt  np percentile df field name   25  - distance  index  inplace True

User · Answer

scipy stats has methods trim1   and trimboth   to cut the outliers out in a single row  according to the ranking and an introduced percentage of removed values

User · Answer

For each of your dataframe column  you could get quantile with   q   df  col   quantile 0 99    and then filter with   df df  col    lt  q    If one need to remove lower and upper outliers  combine condition with an AND statement   q low   df  col   quantile 0 01  q hi    df  col   quantile 0 99   df filtered   df  df  col    lt  q hi   amp   df  col    gt  q low

[python] Detect and exclude outliers in Pandas data frame

Examples related to python

Examples related to pandas

Examples related to filtering

Examples related to dataframe

Examples related to outliers