Identify Outliers in BigQuery

Identify Outliers

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:

Data

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.

Screenshot 2021-05-27 at 2.53.24 pm

“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,
   abs(difference/percentile_cont(difference, 0.5) over()) mad,
   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
order by mad desc

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

MAD Method Outliers


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)

Standard Deviation Method


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)

Z Score Method


1 Like