Outlier detection based on the moving mean in Python

user_13100360 0
Viewed 2802

I am trying to translate an algorithm from MATLAB to Python. The algorithm works with large datasets, and need an outlier detection and elimination technique to be applied.

In the MATLAB code, the outlier deletion technique I use is movmedian:

   Outlier_T=isoutlier(Data_raw.Temperatura,'movmedian',3);
   Data_raw(find(Outlier_T),:)=[]

Which detects outliers with a rolling median, by finding desproportionate values in the centre of a three value moving window. So If I have a column "Temperatura" with a 40 on row 3, it is detected and the entire row is deleted.

         Temperatura     Date       
    1        24.72        2.3        
    2        25.76        4.6        
    3        40           7.0        
    4        25.31        9.3        
    5        26.21       15.6
    6        26.59       17.9        
   ...        ...         ...

To my understanding, this is achieved with pandas.DataFrame.rolling. I have seen several posts examplify its use, but I am not managing to make it work with my code:

Attempt A:

Dataframe.rolling(df["t_new"]))

Attempt B:

df-df.rolling(3).median().abs()>200

#based on @Ami Tavory's answer

Am I missing something obvious here? What is the right way of doing this? Thank you for your time.

rolling-computation outliers pandas python
3 Answers

Code below drops the rows based on threshold. This threshold could be adjusted as needed. Not sure if it replicates Matlab code though.

# Import Libraries
import pandas as pd
import numpy as np

# Create DataFrame
df = pd.DataFrame({
    'Temperatura': [24.72, 25.76, 40, 25.31, 26.21, 26.59],
    'Date':[2.3,4.6,7.0,9.3,15.6,17.9]
})

# Set threshold for difference with rolling median
upper_threshold = 1
lower_threshold = -1

# Calculate rolling median
df['rolling_temp'] = df['Temperatura'].rolling(window=3).median()

# Calculate difference
df['diff'] = df['Temperatura'] - df['rolling_temp']

# Flag rows to be dropped as `1`
df['drop_flag'] = np.where((df['diff']>upper_threshold)|(df['diff']<lower_threshold),1,0)

# Drop flagged rows
df = df[df['drop_flag']!=1]
df = df.drop(['rolling_temp', 'rolling_temp', 'diff', 'drop_flag'],axis=1)

Output

print(df)

   Temperatura  Date
0        24.72   2.3
1        25.76   4.6
3        25.31   9.3
4        26.21  15.6
5        26.59  17.9
user_60052060

Late to the party, based on Nilesh Ingle's answer. Modified to be more general, verbose (graphs!), and a percentage threshold instead of the data's real values.

# Calculate rolling median
df["Temp_Rolling"] = df["Temp"].rolling(window=3).median()

from sklearn.preprocessing import MinMaxScaler

scaler = MinMaxScaler()
df["Temp_Scaled"] = scaler.fit_transform(df["Temp"].values.reshape(-1, 1))

df["Temp_Rolling"] = scaler.fit_transform(df["Temp_Rolling"].values.reshape(-1, 1))

# Calculate difference
df["Temp_Diff"] = df["Temp_Scaled"] - df["Temp_Rolling"]

import numpy as np
import matplotlib.pyplot as plt

# Set threshold for difference with rolling median
upper_threshold = 0.4
lower_threshold = -0.4

# Flag rows to be keepped True
df["Temp_Keep_Flag"] = np.where( (df["Temp_Diff"] > upper_threshold) | (df["Temp_Diff"] < lower_threshold), False, True)

# Keep flagged rows
print('dropped rows')
print(df[~df["Temp_Keep_Flag"]].index)
print('Your new graph')
df_result = df[df["Temp_Keep_Flag"].values]
df_result["Temp"].plot()

Once you're satisfied with the data cleaning

# Satisfied, replace data
df = df[df["Temp_Keep_Flag"].values]
df.drop(columns=["Temp_Rolling", "Temp_Diff", "Temp_Keep_Flag"], inplace=True)
df.plot()
user_45201360

Nilesh answer works perfectly, to iterate on his code you could also do :

upper_threshold = 1
lower_threshold = -1

# Calculate rolling median
df['rolling_temp'] = df['Temp'].rolling(window=3).median()
# all in one line 
df = df.drop(df[(df['Temp']-df['rolling_temp']>upper_threshold)|(df['Temp']- df['rolling_temp']<lower_threshold)].index) 
# if you want to drop the column as well
del df["rolling_temp"]
user_41310180
Related