Cleaning and organizing data: outliers and missing data

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).

How to Spot Outliers

Don't worry if you can't find them just by staring at a spreadsheet! We have tools for this:
• Boxplots: These are your best friends. Any points plotted as individual dots outside the "whiskers" are usually considered outliers.
• Scatterplots: Great for seeing if a point doesn't fit the relationship between two variables.
• The IQR Rule: Mathematically, a point is often called an outlier if it is more than \(1.5 \times IQR\) above the third quartile or below the first quartile.
• Z-Scores: If a data point has a Z-score greater than 3 (or less than -3), it is very far from the mean and likely an outlier.

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 and inflate your variance. Always investigate them before hitting "delete"!

Key Takeaway:

Outliers aren't always "bad" data; they are just "different" data. Your job is to 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’s 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 (The "Easy" Way)
• 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 it is MCAR.

2. Imputation (The "Filling In" Way)
Imputation means filling in the holes with estimated values.
• Mean/Median/Mode Imputation: Fill the hole with the average 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) to predict what the missing value should be based on other variables.

3. The "Missingness" Indicator (The "Actuarial" Way)
Instead of guessing, you can create a new binary/indicator variable (e.g., \(Is\_Missing = 1\)). For the original variable, you fill the missing spots with a constant (like 0). This allows the model to learn if the fact that data is missing is actually a predictor itself!

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 thing you are trying to predict). Just remove those rows.

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 realize that the missing ingredient is a sign the chef forgot to shop (indicator variable).

Summary Checklist for the Exam

• Identify: Use plots (Boxplots/Scatterplots) and stats (IQR/Z-score) for outliers.
• Diagnose: Is the missing data MCAR, MAR, or MNAR?
• Treat: Choose between deletion, imputation, or indicator variables based on the situation.
• Document: In the ATPA exam, always explain why you chose a specific cleaning method. The "why" is just as important as the "how"!

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 "fun" modeling part becomes much easier!