Outlier Identification

Outlier Identification

An outlier is an observation that is unusually far away from the general pattern of the other observations in a dataset.
Outliers are important in data analysis because they can reveal errors, unusual events, fraud, process problems, measurement problems,
or genuinely important cases that deserve further investigation.

An outlier should not automatically be considered a bad record. A value can be unusual and still be completely correct.
The responsibility of a data analyst is to detect the unusual observation, investigate why it is unusual, determine whether it is valid,
and then choose an appropriate treatment based on evidence and business context.

Learning Objectives

By the end of this lesson, you should be able to:

• Explain what an outlier is.
• Distinguish an outlier from an error and a legitimate extreme observation.
• Explain common causes of outliers.
• Identify outliers using charts and descriptive statistics.
• Calculate the Interquartile Range (IQR).
• Use the IQR method to identify potential outliers.
• Understand the z-score method.
• Detect potential outliers using Microsoft Excel.
• Use Python and pandas to investigate unusual values.
• Explain why outliers should not be deleted blindly.
• Choose an appropriate strategy for handling outliers.
• Document decisions made during the cleaning process.

1. What Is an Outlier?

An outlier is a value or observation that is substantially different from the majority of observations in a dataset.
For example, imagine that the ages of ten students are:

18, 19, 20, 18, 21, 19, 20, 18, 22, 75

The value 75 is much higher than the other ages. It may be an incorrect entry, or it may belong to a different population.
It therefore deserves investigation.

Another example is a sales dataset in which most transactions are between ₦5,000 and ₦80,000 while one transaction is ₦15,000,000.
The ₦15,000,000 transaction is unusual, but it does not automatically mean that it is wrong. It may represent a legitimate large corporate order.

2. Outlier, Error, or Valid Extreme Observation?

These concepts must be separated carefully.

Outlier

A statistical observation that is unusually far from the rest of the data.

Error

A value that is incorrect because of a data-entry problem, measurement problem, system problem, or another source of error.

Valid Extreme Observation

A value that is unusually large or small but is still accurate and meaningful.

For example, if a company’s normal order values range from ₦10,000 to ₦100,000 and a verified corporate customer places a ₦2,000,000 order,
that order may be an outlier statistically but a valid business event.

3. Why Do Outliers Occur?

Outliers can occur for many different reasons.

3.1 Data Entry Errors

A person may enter 5000 instead of 500, or enter an age of 180 instead of 18.

3.2 Measurement Errors

Sensors, measuring devices, laboratory equipment, or manual measurements may produce incorrect values.

3.3 Unusual but Legitimate Events

A major customer may make a very large purchase, or a student may receive an unusually high score in a special assessment.

3.4 Fraud or Suspicious Activity

Unusual transactions can sometimes indicate fraud or unauthorized activity.

3.5 Population Mixing

A dataset may combine groups that should have been analyzed separately.
For example, combining student ages with staff ages can create apparently unusual values.

3.6 Process Changes

A business may introduce a new pricing model, product, service, or marketing campaign that changes the normal distribution of its data.

3.7 Skewed Distributions

Some variables naturally have long tails. Income, transaction values, property prices, and response times are common examples.
A large value in a right-skewed distribution may be unusual without being erroneous.

4. Types of Outliers

4.1 Univariate Outliers

A univariate outlier is unusual when one variable is considered by itself.
For example, an age of 150 years would be suspicious when analyzing a single age column.

4.2 Bivariate Outliers

A bivariate outlier becomes unusual when two variables are considered together.
For example, a student’s study hours and examination score may normally have a particular relationship.
A student whose values are far away from that relationship may require investigation.

4.3 Multivariate Outliers

A multivariate outlier is unusual because of its combination of several variables.
An observation may look normal when each variable is considered independently but become unusual when several variables are analyzed together.

5. Visual Methods for Detecting Outliers

Charts are among the fastest ways to identify unusual observations.

5.1 Box Plot

A box plot displays the distribution of numerical data using the median, quartiles, and potential outliers.
Values that appear beyond the whiskers are commonly treated as potential outliers under the IQR rule.

5.2 Histogram

A histogram shows how frequently values occur within ranges.
An isolated bar far away from the main distribution may indicate an unusual observation.

5.3 Scatter Plot

A scatter plot is useful when comparing two numerical variables.
An observation far away from the main cluster can indicate a potential bivariate outlier.

5.4 Line Chart

For time-series data, a sudden spike or drop may indicate an unusual observation.
The analyst should investigate whether the change represents a genuine event or a data problem.

6. Descriptive Statistics and Outliers

Descriptive statistics help an analyst understand the central tendency and spread of a dataset.

• Mean: the arithmetic average.
• Median: the middle value after sorting the data.
• Minimum: the smallest value.
• Maximum: the largest value.
• Quartiles: values that divide ordered data into four sections.
• Standard deviation: a measure of how spread out observations are around the mean.
• IQR: the distance between the third and first quartiles.

Outliers can strongly affect the mean and standard deviation.
The median and IQR are generally more resistant to extreme observations.

7. The Interquartile Range (IQR) Method

The IQR method is one of the most widely used approaches for identifying potential outliers.

Step 1: Find Q1

Q1, or the first quartile, represents the 25th percentile of the data.

Step 2: Find Q3

Q3, or the third quartile, represents the 75th percentile.

Step 3: Calculate the IQR

IQR = Q3 - Q1

Step 4: Calculate the Lower Bound

Lower Bound = Q1 - (1.5 × IQR)

Step 5: Calculate the Upper Bound

Upper Bound = Q3 + (1.5 × IQR)

Values below the lower bound or above the upper bound are commonly flagged as potential outliers.

8. Example of the IQR Method

Consider the following values:

10, 12, 13, 14, 15, 16, 17, 18, 20, 50

The value 50 appears much larger than the other observations.
An IQR calculation can be used to determine whether it falls beyond the upper boundary.

Assume for illustration that:

Q1 = 13
Q3 = 18

IQR = 18 - 13
IQR = 5

Lower Bound = 13 - (1.5 × 5)
Lower Bound = 5.5

Upper Bound = 18 + (1.5 × 5)
Upper Bound = 25.5

Any value below 5.5 or above 25.5 would be flagged as a potential outlier under this calculation.
Therefore, 50 would be flagged.

9. Z-Score Method

Another common technique is the z-score.
A z-score indicates how many standard deviations an observation is away from the mean.

z = (x - mean) / standard deviation

A common practical rule is to investigate observations where:

|z| > 3

This means the observation is more than approximately three standard deviations from the mean.
However, the threshold is not a universal law. The correct threshold depends on the dataset, distribution, sample size, and business context.

10. Why Mean and Standard Deviation Must Be Used Carefully

The mean and standard deviation themselves can be influenced by extreme observations.
Therefore, when a dataset is strongly skewed, an analyst should not rely on a z-score alone.

Robust measures such as the median and IQR can provide a better picture of the typical observation when extreme values are present.

11. Detecting Outliers in Microsoft Excel

Excel provides several tools that can help identify unusual values.

Useful Functions

• AVERAGE() — calculates the mean.
• MEDIAN() — calculates the median.
• QUARTILE.INC() — calculates quartiles.
• STDEV.S() — calculates sample standard deviation.
• MIN() — returns the minimum value.
• MAX() — returns the maximum value.

Example Excel Formulas

If your numerical data is stored in cells B2:B101:

=AVERAGE(B2:B101)

=MEDIAN(B2:B101)

=QUARTILE.INC(B2:B101,1)

=QUARTILE.INC(B2:B101,3)

=STDEV.S(B2:B101)

=MIN(B2:B101)

=MAX(B2:B101)

Calculating the IQR in Excel

Q1:
=QUARTILE.INC(B2:B101,1)

Q3:
=QUARTILE.INC(B2:B101,3)

IQR:
=Q3-Q1

If Q1 is stored in cell E2 and Q3 is stored in E3:

Lower Bound:
=E2-(1.5*(E3-E2))

Upper Bound:
=E3+(1.5*(E3-E2))

12. Flagging Outliers in Excel

After calculating the lower and upper boundaries, you can create a new column called Outlier Status.

=IF(OR(B2<$E$4,B2>$E$5),"Potential Outlier","Within Range")

This does not delete the observation. It simply flags it for investigation.

13. Conditional Formatting

Excel conditional formatting can be used to visually highlight unusually high or low values.
This makes large datasets easier to investigate.

Remember that highlighting a value does not prove that it is incorrect.
It simply helps the analyst locate observations that deserve attention.

14. Box-and-Whisker Chart in Excel

Modern versions of Excel provide a Box and Whisker chart.
This chart can quickly show the median, quartiles, spread, and potential outliers of a numerical dataset.

A good workflow is to combine the chart with the underlying records so that suspicious values can be traced back to their source.

15. Detecting Outliers with Python and pandas

Python is useful when datasets become too large or complex for manual inspection.
The pandas library provides several functions for exploring numerical distributions.

Basic Descriptive Statistics

import pandas as pd

df = pd.read_csv("sales.csv")

print(df["amount"].describe())

The describe() method provides useful statistics such as count, mean, standard deviation, minimum, quartiles, and maximum.

16. Calculating IQR with pandas

q1 = df["amount"].quantile(0.25)
q3 = df["amount"].quantile(0.75)

iqr = q3 - q1

lower_bound = q1 - (1.5 * iqr)
upper_bound = q3 + (1.5 * iqr)

print("Q1:", q1)
print("Q3:", q3)
print("IQR:", iqr)
print("Lower Bound:", lower_bound)
print("Upper Bound:", upper_bound)

17. Filtering Potential Outliers in pandas

outliers = df[
    (df["amount"] < lower_bound) |
    (df["amount"] > upper_bound)
]

print(outliers)

This creates a separate view of the observations that fall outside the calculated boundaries.

18. Creating an Outlier Flag in pandas

df["outlier"] = (
    (df["amount"] < lower_bound) |
    (df["amount"] > upper_bound)
)

print(df[["amount", "outlier"]])

A flag is often preferable to immediately deleting records because it preserves the original information while allowing further investigation.

19. Z-Scores in Python

You can calculate z-scores using NumPy without requiring an additional statistical package.

import numpy as np

mean = df["amount"].mean()
std = df["amount"].std()

df["z_score"] = (df["amount"] - mean) / std

potential_outliers = df[
    df["z_score"].abs() > 3
]

print(potential_outliers)

Again, a threshold such as 3 should be treated as a practical investigation rule rather than an automatic deletion rule.

20. SQL and Outlier Investigation

SQL can also be used to investigate unusually large or small values.
The exact percentile functions available depend on the database system.

A simple first step is to inspect minimum, maximum, average, and counts:

SELECT
    MIN(amount) AS minimum_amount,
    MAX(amount) AS maximum_amount,
    AVG(amount) AS average_amount,
    COUNT(*) AS total_records
FROM sales;

After identifying suspicious ranges, the analyst can inspect the individual records responsible for those values.
For percentile-based methods, always check the documentation for the specific database system being used.

21. Outlier Investigation Workflow

A professional data analyst should follow a structured process.

1. Detect: identify unusual observations.
2. Investigate: inspect the underlying records.
3. Verify: compare the value with the original source.
4. Classify: determine whether it is an error, anomaly, or valid observation.
5. Decide: choose the appropriate treatment.
6. Document: record what was changed and why.

22. Do Not Delete Outliers Blindly

This is one of the most important rules in data cleaning.

An outlier is not automatically an error.

Deleting every unusual observation can destroy important information.
For example, fraud detection depends on identifying unusual transactions.
A hospital may need to investigate an unusually high measurement.
A business may want to understand unusually large purchases.

Therefore, the correct question is not:
“Is this value unusual?”

The better question is:
“Why is this value unusual, and is it valid?”

23. Ways to Handle Outliers

23.1 Keep the Outlier

If the observation is accurate and meaningful, keep it.

23.2 Correct the Value

If an original source confirms a data-entry or measurement error, correct the record.

23.3 Remove the Observation

Removal may be appropriate when the record is proven to be invalid, duplicated, corrupted, or outside the intended population.

23.4 Cap or Winsorize

In some analytical situations, extreme values may be capped at predefined boundaries.
This should only be done when it is statistically and operationally justified.

23.5 Transform the Data

Logarithmic or other transformations can reduce the influence of highly skewed values in certain analytical models.

23.6 Segment the Data

Sometimes the correct solution is to analyze different groups separately.
For example, standard retail customers and corporate customers may need separate analysis.

23.7 Flag the Outlier

Instead of changing the original value, create a flag that identifies it for special attention.

24. Effect of Outliers on Analysis

Mean

Extreme values can pull the mean toward themselves.

Median

The median is generally less affected by extreme values.

Standard Deviation

Outliers can increase the standard deviation and make a dataset appear more variable than it normally is.

Regression

Influential observations can significantly affect regression coefficients and predicted relationships.

Charts

A single extreme observation can compress the visual scale of a chart and make normal observations difficult to compare.

25. Outliers and Skewed Data

A dataset with a long right tail may naturally contain many values larger than the mean.
For example, income data is often right-skewed.

In such situations, the analyst should understand the distribution before labeling high values as errors.
A high income is not necessarily an incorrect income.

26. Robust Statistics

Robust statistics are less sensitive to extreme observations.
Common robust measures include:

• Median
• Interquartile Range
• Percentiles

When a dataset contains substantial skewness or extreme observations, these measures can provide a more stable description of the typical data point.

27. Example: Student Scores

Suppose the scores of students are:

42, 55, 61, 64, 68, 70, 72, 75, 81, 95

A score of 95 is high but may be completely valid.
The analyst should not remove it simply because it is above the average.

Now imagine that one record contains a score of 950.
This is much more likely to be a data-entry error and should be investigated against the original examination record.

28. Example: Sales Transactions

Imagine a business has transaction values mostly between ₦2,000 and ₦200,000.
A transaction worth ₦5,000,000 should be investigated.

Possibilities include:

• A legitimate corporate purchase.
• A decimal or digit-entry mistake.
• A duplicate transaction.
• A fraudulent transaction.
• A different type of transaction mixed into the dataset.

The correct treatment depends on the evidence.

29. Example: Customer Age

Consider a customer dataset containing:

21, 24, 31, 27, 35, 42, 29, 33, 18, 250

The value 250 is clearly suspicious for a human age dataset.
It should be checked against the source record.
It could have resulted from a typing error, a malformed import, or an incorrect unit.

30. Example: Sensor Data

A temperature sensor may normally report values between 20°C and 35°C.
A sudden reading of 150°C may represent equipment failure, a measurement error, or a genuine event.

The analyst should examine nearby timestamps, sensor status, other sensors, and the physical context before changing the value.

31. Outlier vs Anomaly

An outlier is primarily a statistical concept describing an observation that differs substantially from the general distribution.

An anomaly is an observation or pattern that appears abnormal in a particular operational or business context.

The two concepts overlap, but they are not always identical.
An observation can be statistically unusual but business-valid, while an operational anomaly may not be an extreme numerical value.

32. Outliers in Small and Large Datasets

In small datasets, one extreme observation can have a large influence on summary statistics.
The analyst should therefore inspect individual records carefully.

In large datasets, automated methods become more useful, but automation should still be followed by investigation and business validation.

33. A Professional Outlier Checklist

• Check the variable’s meaning.
• Check the expected range.
• Inspect the distribution.
• Calculate descriptive statistics.
• Use visualizations.
• Apply an appropriate statistical method.
• Trace suspicious records back to the source.
• Determine whether the observation is valid.
• Choose a treatment method.
• Document the decision.

34. Practical Exercise

Create a small Excel dataset containing 20 sales transactions.
Most transactions should fall between ₦5,000 and ₦100,000.
Include several deliberately unusual values.

Perform the following tasks:

1. Calculate the mean.
2. Calculate the median.
3. Calculate Q1 and Q3.
4. Calculate the IQR.
5. Calculate the lower boundary.
6. Calculate the upper boundary.
7. Flag potential outliers.
8. Create a box plot.
9. Investigate each flagged transaction.
10. Classify each unusual record as valid, erroneous, or requiring further investigation.
11. Document your final decision.

35. Practical Python Exercise

Use pandas to load a sales dataset and create an automated IQR-based outlier flag.

import pandas as pd

df = pd.read_csv("sales.csv")

q1 = df["amount"].quantile(0.25)
q3 = df["amount"].quantile(0.75)

iqr = q3 - q1

lower = q1 - (1.5 * iqr)
upper = q3 + (1.5 * iqr)

df["outlier"] = (
    (df["amount"] < lower) |
    (df["amount"] > upper)
)

print(df[df["outlier"]])

After identifying the records, do not automatically remove them.
Investigate their meaning first.

36. Knowledge Check

1. What is an outlier?
2. Why is an outlier not automatically an error?
3. Name three common causes of outliers.
4. What is the difference between an outlier and a valid extreme observation?
5. What does IQR stand for?
6. How is IQR calculated?
7. What formula is used for the lower IQR boundary?
8. What formula is used for the upper IQR boundary?
9. What is a z-score?
10. What is a commonly used practical z-score threshold for investigation?
11. Which Excel function calculates the median?
12. Which Excel function calculates quartiles?
13. How can pandas help identify outliers?
14. Why should analysts avoid blindly deleting outliers?
15. Name four possible ways to handle an outlier.

37. Key Terms

• Outlier: an observation that differs substantially from the general pattern.
• Anomaly: an unusual observation or pattern in a particular context.
• Quartile: a value dividing ordered data into sections.
• IQR: the difference between Q3 and Q1.
• Q1: the first quartile or 25th percentile.
• Q3: the third quartile or 75th percentile.
• Z-score: a measure of how many standard deviations a value is from the mean.
• Skewness: asymmetry in a distribution.
• Winsorization: a technique that limits extreme values to selected boundaries.
• Robust statistics: statistical measures that are less sensitive to extreme observations.

38. Key Takeaways

• Outliers are observations that differ substantially from the general pattern.
• An outlier is not automatically an error.
• Outliers may result from data-entry errors, measurement problems, unusual events, fraud, population mixing, or process changes.
• Box plots, histograms, scatter plots, and line charts can help reveal unusual observations.
• The IQR method uses Q1, Q3, and the 1.5 × IQR rule.
• Z-scores can also be used to investigate extreme observations.
• Excel provides functions and charts that can assist with outlier detection.
• Python and pandas can automate outlier investigation for larger datasets.
• Outliers should be investigated before they are changed or removed.
• Valid extreme observations may contain valuable business information.
• Documentation is essential when making data-cleaning decisions.

39. Lesson Summary

Outlier identification is an important part of data cleaning and preparation.
The goal is not simply to find large or small numbers, but to understand why observations are unusual and determine how they should be treated.

An effective analyst combines statistical methods, visualization, source verification, and business knowledge.
The IQR method and z-score method provide useful starting points, while Excel and Python make the process more efficient.

Most importantly, never delete an observation simply because it looks unusual.
Investigate it, verify it, classify it, make an informed decision, and document the decision.

Next Lesson

In the next lesson, we will study Data Transformation and learn how raw data can be converted into useful formats for analysis, reporting, visualization, and statistical modeling.