 # Description

Outlier detection is a key step to any analysis. It allows you to quickly spot errors in data collection, or which points you may need to remove before you do any statistical modeling.
There are a number of ways to find outliers, but this notebook goes through the most common ways:

# The Data

For this example, we’ll be looking at some data that shows the total number of daily streams for the Top 200 Tracks on Spotify every day since Jan 1 2017:

# Method 1: MAD (Median Absolute Deviation)

MAD describes how far away a point is from the median. This method can be preferable to other methods, like the z-score method because you don’t have to assume the data is normally distributed. “A critical value for the Median Absolute Deviation of 5 has been suggested by workers in Nonparametric statistics, as a Median Absolute Deviation value of just under 1.5 is equivalent to one standard deviation, so MAD = 5 is approximately equal to 3 standard deviations.”

``````SELECT
diff_to_med.*,
percentile_cont(diff_to_med.difference, 0.5) over() median_of_diff,
case
when abs(difference/percentile_cont(difference, 0.5) over()) > <MAD_THRESHOLD> then 'outlier'
else 'not outlier'
end label
FROM (
select
<OTHER_COLUMNS>
<OUTLIER_COLUMN>,
med.median ,
abs(data.<OUTLIER_COLUMN> - med.median) difference
from
<TABLE> as data
cross join
(
select distinct
percentile_cont(<OUTLIER_COLUMN>,.5) over() median
from <TABLE> as data) as med
) as diff_to_med
``````

where:

• `<OTHER_COLUMNS>`: Columns selected other than the outlier column
• `<OUTLIER_COLUMN>`: Column name for outlier
• `<MAD_TRESHOLD>`: The outlier threshold. 5 = 3 standard deviations

# Method 2: Standard Deviations

The most common way outliers are detected is by finding points outside a certain number of sigmas (or standard deviations) from the mean.
If the data is normally distributed, then the following rules apply:

Image from anomoly.io
So, identifying points 2 or 3 sigmas away from the mean should lead us to our outliers.

``````SELECT
data.*,
case
when <OUTLIER_COLUMN> between mean - 3 * stdev and mean + 3 * stdev
then 'not outlier'
else 'outlier'
end label,
mean - <SIGMAS> * stdev lower_bound,
mean + <SIGMAS> * stdev upper_bound
FROM data
CROSS JOIN
(
SELECT
avg(<OUTLIER_COLUMN>) mean,
stddev_samp(data.streams<OUTLIER_COLUMN>
FROM <TABLE> AS data ) mean_sd
ORDER BY label DESC
``````

where:

• `<OTHER_COLUMNS>`: Columns selected other than the outlier column
• `<OUTLIER_COLUMN>`: Column name for outlier
• `<SIGMAS>`: Number of standard deviations to identify the outliers (suggested: 3)

# Method 3: Z-Score

Similar to Method 2, the z-score method tells us the probability of a point occurring in a normal distribution.

Image from simplypsychology.org
To use z-scores to identify outliers, we can set an alpha (significance level) that will determine how ‘likely’ each point will be in order for it to be classified as an outlier.
This method also assumes the data is normally distributed.

``````SELECT
find_z.*,
case
when abs(z_score) >=abs(<Z_THRESHOLD>) then 'outlier' else 'not outlier'
end label
FROM (
SELECT
<OTHER_COLUMNS>,
<OUTLIER_COLUMN>,
(<OUTLIER_COLUMN> - avg(<OUTLIER_COLUMN>) over ())
/ (stddev(<OUTLIER_COLUMN>) over ()) z_score
FROM <TABLE> AS data) find_z
ORDER BY abs(z_score) DESC
``````

where:

• `<OTHER_COLUMNS>`: Columns selected other than the outlier column
• `<OUTLIER_COLUMN>`: Column name for outlier
• `<Z_THRESHOLD>`: z-score threshold to identify outliers (suggested: 1.96)

1 Like