Cleaning and organizing data: outliers and missing data

Exam ATPA – Advanced Topics in Predictive Analytics · SOA (Society of Actuaries) · 7 min read · Updated: 8 Nov 2026

Welcome to Data Cleaning!

Hello there! Welcome to one of the most important chapters in your Exam ATPA journey. You might have heard the phrase "garbage in, garbage out." In predictive analytics, your model is only as good as the data you feed it. Real-world data is often messy—it has holes (missing values) and weird surprises (outliers). In this chapter, we are going to learn how to spot these issues and, more importantly, how to fix them so your models can shine!

Part 1: Dealing with Outliers

Imagine you are looking at the heights of a group of adults. Most people are between 5 and 6 feet tall. Suddenly, you see a record for someone who is 12 feet tall! That is an outlier—a data point that is significantly different from the rest of the observations.

What exactly is an Outlier?

An outlier is an observation that lies an abnormal distance from other values in a random sample from a population. Outliers can be caused by:
1. Data entry errors: Someone typed "120" instead of "12".
2. Measurement errors: A sensor malfunctioned.
3. Natural variation: Sometimes, extreme values are real (like a billionaire's income in a study of average salaries).

Univariate vs. Multivariate Outliers

In predictive analytics and actuarial work, outliers come in two main flavors:
• Univariate outliers: An observation with an extreme or unusual value in a single variable (e.g., an annual salary of \$10,000,000 when the typical range is \$40,000 to \$150,000).
• Multivariate outliers: An observation that looks completely normal when examining each variable independently, but represents an unusual, implausible, or extreme combination across two or more variables simultaneously. For example, a person with an age of $12$ and $10$ years of driving experience, or a home with $1$ bedroom and $8$ bathrooms. Each value on its own is plausible in the dataset, but the combination is highly anomalous.

How to Spot Outliers

Don't worry if you can't find them just by staring at a spreadsheet! We have tools for both types:
Univariate Detection:
• Boxplots: Points plotted as individual dots outside the "whiskers" are flagged as potential outliers.
• The IQR Rule: Mathematically, a point is often called an outlier if it falls below $Q_1 - 1.5 \times IQR$ or above $Q_3 + 1.5 \times IQR$.
• Z-Scores: If an observation has a $Z$-score satisfying $|Z| > 3$, it lies more than 3 standard deviations from the mean and is flagged as a potential outlier.

Multivariate Detection:
• Scatterplots and Scatterplot Matrices: Bivariate plots help visualize pairs of variables to reveal points deviating from the overall correlation pattern.
• Mahalanobis Distance: A statistical metric that measures the distance between an observation and the multivariate mean (centroid), accounting for the correlation and covariance among all variables. A point with a large Mahalanobis distance is a multivariate outlier even if none of its individual coordinate values are univariate outliers.

What do we do with them?

Once you find an outlier, you have three main choices:
1. Keep it: if you believe the data is accurate and represents a real (though rare) phenomenon.
2. Remove it: if you are certain it is an error or it will disproportionately bias your model.
3. Transform/Cap it: This is often called Winsorization. You replace the extreme value with a less extreme one (like the 99th percentile value).

Quick Review: Outliers can skew your mean, inflate your variance, and distort model coefficients. Always investigate them before hitting "delete"!

Key Takeaway:

Outliers aren't always "bad" data; they are just "different" data. Your job is to check for both univariate and multivariate anomalies and decide if they are helpful signals or distracting noise.

Part 2: Missing Data – The "Holes" in Your Dataset

Missing data occurs when no value is stored for a variable in an observation. This is a common headache in actuarial work, perhaps because a policyholder didn't answer a survey question or a system failed to record a transaction.

Why is Missing Data a Problem?

Most predictive models (like standard GLMs) cannot handle missing values. If a row has a missing value, the software might just throw the whole row away, wasting perfectly good information in other columns!

The Three Types of Missingness

To fix the problem, we first need to understand why the data is missing.
1. Missing Completely at Random (MCAR): There is no pattern. It is like someone accidentally spilled coffee on a few random pages of a ledger.
2. Missing at Random (MAR): The missingness is related to other observed data. For example, men might be less likely to answer a survey question about their weight than women.
3. Missing Not at Random (MNAR): The missingness is related to the missing value itself. For example, people with very high incomes might refuse to report their income on a form.

Did you know? MNAR is the hardest to deal with because the reason for the missing data is "hidden" within the missing data itself!

Strategies for Handling Missing Data

1. Deletion of the Record
• Listwise Deletion: Delete the entire row if any value is missing. This is simple but can lead to a huge loss of data.
• When to use: Only when the amount of missing data is very small and the missingness mechanism is MCAR.

2. Imputation
Imputation means filling in the holes with estimated values.
• Mean/Median/Mode Imputation: Fill the hole with the central tendency of that column. Warning: This reduces the variance of your data and can lead to overconfident models!
• Predictive Imputation: Use a mini-model (like a regression or $k$-nearest neighbors) to predict what the missing value should be based on other variables.

3. Adding a Missing-Value Flag
Instead of simply imputing, you create an explicit binary missing-value flag (e.g., $Is\_Missing = 1$ if missing, and $0$ otherwise). For the original variable, you replace the missing entries with a constant (such as the median or $0$). This allows the model to capture whether the fact that data is missing carries predictive information on its own!

Key Takeaway:

Never just ignore missing data. Choosing to "do nothing" is actually choosing to let your software delete those rows, which might bias your results.

Part 3: Common Pitfalls and Tips

Common Mistakes to Avoid

• Don't blindly delete: If 40% of your data is missing in one column, deleting those rows will destroy your dataset. Consider dropping the column instead of the rows.
• Watch out for "Hidden" Missing Values: Sometimes missing data isn't a blank cell. It might be coded as "999", "Unknown", or "0". Always check your summary statistics to see if a value looks suspicious.
• Imputing the target: Generally, you should not impute the target variable (the response you are trying to predict). Just remove those records.

A Simple Analogy

Think of cleaning data like preparing a meal. The outliers are like a single pepper that is 100x hotter than the others—if you leave it in, it might ruin the whole dish for everyone. The missing data are like missing ingredients in a recipe—you can either skip that dish (deletion), substitute something similar (imputation), or recognize that the missing ingredient is a sign the chef forgot to shop (missing-value flag).

Summary Checklist for the Exam

• Identify Outliers: Check for both univariate outliers (boxplots, IQR, $Z$-scores) and multivariate outliers (scatterplot matrices, Mahalanobis distance).
• Diagnose Missingness: Determine whether the missing mechanism is MCAR, MAR, or MNAR.
• Select Action: Select the appropriate action from record deletion, imputation, or adding a missing-value flag based on the mechanism and business context.
• Document: In the ATPA assessment report, always justify and document why you chose a specific cleaning action.

Keep going! You're doing great. Mastering data cleaning is the foundation of becoming a top-tier predictive analyst. Once your data is clean, the predictive modeling part becomes much easier!

References

ATPA Assessment Learning Objectives — Society of Actuaries

Quick check

Can you answer these now?

Open each question to check the key ideas from this chapter.

What is an outlier and what are the three primary causes of outliers in a dataset?

An outlier is an observation lying an abnormal distance from other values in a sample, caused by data entry errors, measurement errors, or natural variation.

What is the difference between a univariate outlier and a multivariate outlier?

A univariate outlier is extreme in a single variable alone, whereas a multivariate outlier is anomalous only when considering the joint combination across two or more variables simultaneously.

Which common methods are used to detect univariate outliers?

Univariate outliers are typically spotted using boxplots (points outside the whiskers), the IQR rule (values beyond $Q_1 - 1.5 \times IQR$ or $Q_3 + 1.5 \times IQR$), and $Z$-scores (where $|Z| > 3$).

Which tools are used to identify multivariate outliers?

Multivariate outliers are detected graphically using scatterplots or scatterplot matrices, and statistically using the Mahalanobis distance metric.

What three main strategies can be applied to handle outliers in predictive modeling?

Outliers can be kept (if valid rare phenomena), removed (if errors or severely biasing), or transformed/capped through Winsorization.

What are the three mechanisms of missing data: MCAR, MAR, and MNAR?

MCAR means missingness is purely random; MAR means missingness relates to other observed variables; MNAR means missingness directly depends on the unobserved value itself.

What is listwise deletion and under what conditions is it appropriate to use?

Listwise deletion drops any row containing one or more missing values; it is only appropriate when the missing data mechanism is MCAR and the proportion of missing records is very small.

How do mean/median imputation and predictive imputation handle missing values, and what is the primary drawback of central tendency imputation?

Mean/median imputation replaces missing values with the variable's central tendency (which artificially deflates variance), whereas predictive imputation estimates missing entries using models based on other features.

How does adding a missing-value indicator flag benefit predictive models?

It flags missing records with a binary indicator ($1$ if missing, $0$ otherwise) and replaces missing entries with a constant, allowing the model to learn whether the missingness itself possesses predictive power.

Why should practitioners avoid imputing missing values in the target (response) variable?

Imputing the target variable introduces fabricated response signals into the model; rows with missing targets should be dropped rather than imputed.

What are "hidden" missing values and how can an analyst detect them?

Hidden missing values are missing entries encoded as arbitrary placeholder values (e.g., "999", "Unknown", or "0"), detected by inspecting summary statistics, minimum/maximum values, and frequency tables.

Ready to test yourself?

Turn these notes into exam-style practice. Get unlimited AI questions on this topic with instant marking and explanations.

Practise This Topic

More Exam ATPA – Advanced Topics in Predictive Analytics chapters

* The content provided by thinka is generated by AI and may not always be accurate or up-to-date. Please use it as a supplementary resource and verify with official materials.

Put These Notes into Practice

Reading the notes is a great start. Now practise with unlimited AI-generated questions and get instant feedback. 100,000+ students are already improving their grades.

Start Practising Now View Pricing

Done reading? Test yourself with AI practice questions

Practise This Topic Now