CODEWITHSUNDEEP
pythonpandasoutliersquantile
Malek Belgacem
Malek Belgacem

Reputation: 15

Find and replace outliers with nan in Python

I started to use python and i am trying to find outliers per year using the quantile my data is organized as follows: columns of years, and for each year i have months and their corresponding salinity and temperature

year=[1997:2021]
month=[1,2...]
SAL=[33,32,50,......,35,...]

Following is my code:

#1st quartile
Q1 = DF['SAL'].quantile(0.25)
#3rd quartile
Q3 = DF['SAL'].quantile(0.75)
#calculate IQR
IQR = Q3 - Q1
print(IQR)
df_out = DF['SAL'][((DF['SAL'] < (Q1 - 1.5 * IQR)) |(DF['SAL'] > (Q3 + 1.5 * IQR)))]

I want to identify the month and year of the outlier and replace it with nan.

Upvotes: 1

Views: 2758

Answers (2)

fsimonjetz
fsimonjetz

Reputation: 5802

To get the outliers per year, you need to compute the quartiles for each year via groupby. Other than that, there's not much to change in your code, but I recently learned about between which seems useful here:

import numpy as np

clean_data = list()

for year, group in DF.groupby('year'):
    
    Q1 = group['SAL'].quantile(0.25)
    Q3 = group['SAL'].quantile(0.75)
    IQR = Q3 - Q1
    
    # set all values to np.nan that are not (~) in between the two values
    group.loc[~group['SAL'].between(Q1 - 1.5 * IQR, 
                                Q3 + 1.5 * IQR, 
                                inclusive=False),
              'SAL'] = np.nan
    
    clean_data.append(group)

clean_df = pd.concat(clean_data)

Upvotes: 1

mozway
mozway

Reputation: 260790

You can use the following function. It uses the definition of an outlier that is below Q1-1.5IQR or above Q3+1.5IQR, such as classically done for boxplots.

import pandas as pd
import numpy as np

df = pd.DataFrame({'year':  np.repeat(range(1997,2022), 12),
                   'month': np.tile(range(12), 25)+1,
                   'SAL':   np.random.randint(20,40, size=12*25)+np.random.choice([0,-20, 20], size=12*25, p=[0.9,0.05,0.05]),
                  })

def outliers(s, replace=np.nan):
    Q1, Q3 = np.percentile(s, [25 ,75])
    IQR = Q3-Q1
    return s.where((s > (Q1 - 1.5 * IQR)) & (s < (Q3 + 1.5 * IQR)), replace)

# add new column with excluded outliers
df['SAL_excl'] = df.groupby('year')['SAL'].apply(outliers)

Checking that it works:

with outliers:

import seaborn as sns
sns.boxplot(data=df, x='year', y='SAL')

boxplot with outliers

without outliers:

sns.boxplot(data=df, x='year', y='SAL_excl')

boxplot without outliers

NB. it is possible that new outliers appear as data has now new Q1/Q3/IQR due to the filtering.

How to retrieve rows with outliers:

df[df['SAL_excl'].isna()]

output:

     year  month  SAL  SAL_excl
28   1999      5   53       NaN
33   1999     10    7       NaN
94   2004     11   52       NaN
100  2005      5   38       NaN
163  2010      8    6       NaN
182  2012      3   25       NaN
188  2012      9   22       NaN
278  2020      3   53       NaN
294  2021      7    9       NaN

Upvotes: 0

Related Questions