Welcome to Data Cleaning!
In the world of actuarial modeling, we rarely get "perfect" data. Real-world data is often messy, full of holes, or just plain weird. This chapter focuses on Repairing Corrupt or Missing Data. Think of this as being a "data detective." Your job is to find the clues, fix what’s broken, and make sure your model remains reliable. Don't worry if this seems a bit technical at first—by the end of these notes, you'll have a clear toolkit for handling any data disaster!
1. Identifying the Problem: What is "Bad" Data?
Before we can fix data, we need to know what’s wrong with it. Usually, data issues fall into two buckets:
1. Missing Data: Blank cells where a value should be (e.g., a policyholder’s age is missing).
2. Corrupt/Illogical Data: Data that is present but clearly wrong (e.g., a policyholder’s age is listed as -5 or 250).
How to spot them:
A great way to find these errors is to use Summary Statistics or Data Filters in Excel. If you look at the "Maximum" value for age and it says 999, you know you’ve found a placeholder that needs fixing!
Quick Review: Common Red Flags
• Negative values where only positives should exist (like premiums).
• Dates in the future (like a claim date in 2050).
• Categorical errors (e.g., "Male", "Female", and "Pizza" in the gender column).
• Extreme outliers (values so large or small they distort the average).
Summary Takeaway: Always "profile" your data first. You can't fix a leak you haven't found!
2. The Three Big Questions
When you find an error, you have to decide how to handle it. Ask yourself:
1. Is it significant? If 1 record out of 1,000,000 is missing a middle name, it probably doesn't matter. If 10% of your "Sum Assured" column is missing, that’s a big problem!
2. Can I find the true value? Can you check a different database or an original paper file?
3. Should I fix it or delete it? This is the "Repair vs. Remove" debate.
3. Methods of Repair
If you decide to repair the data, here are the standard actuarial techniques:
A. Deletion (The "Last Resort")
If a record is so corrupt that you can't trust any of it, you might delete the entire row. Example: A life insurance record is missing the age, the gender, and the sum assured. It’s useless! Warning: Be careful. If you delete too many records, you might introduce bias (e.g., if you only delete records for older people, your model will think everyone is young).
B. Mean/Median Imputation
This means filling the blank cell with the average value of the rest of the column. Analogy: If you're organizing a group dinner and one person forgets to tell you their budget, you assume they'll spend about the same as the "average" person in the group.
C. Logical Defaults
Sometimes you can guess the value based on other clues. Example: If "Smoker Status" is blank, but the policy is a "Non-Smoker Preferred" product, it is logical to set the status to "Non-Smoker."
D. Linear Interpolation
If you have a sequence of numbers with a gap, you can "bridge" the gap. If you know the value at Time 1 is 10 and at Time 3 is 20, you can assume Time 2 is 15. The formula for a simple linear interpolation between two points \( (x_0, y_0) \) and \( (x_1, y_1) \) to find \( y \) for a given \( x \) is: \( y = y_0 + (x - x_0) \frac{y_1 - y_0}{x_1 - x_0} \)
Did you know? Interpolation comes from the Latin inter (between) and polare (to smooth/polish). You are literally smoothing out the gap!
4. Documenting Your Changes (The Audit Trail)
In CP2, how you fix the data is often less important than how you explain it. You must keep a record of every change you make. This is called an Audit Trail.
The "Golden Rules" of Data Repair:
1. Never overwrite the original data: Always keep a "Raw Data" tab and a "Cleaned Data" tab.
2. Flag your repairs: Create a new column called "Repair_Flag". Put a "1" if you changed the data and a "0" if it's original. This helps you track the impact later.
3. Justify your choices: Don't just say "I fixed it." Say "I replaced the 5 missing ages with the median age of 42 to avoid reducing the sample size."
Summary Takeaway: A model is only as good as its documentation. If a colleague can't replicate your "fixes" exactly, you've failed the audit trail test.
5. Common Mistakes to Avoid
• Ignoring the "Zero" vs "Blank" distinction: A blank cell is "no data." A zero is a specific value. Don't treat them the same unless you have a good reason!
• Fixing things that aren't broken: Sometimes an "outlier" is actually a real, albeit unusual, piece of data. Don't delete a claim just because it's huge; that claim might be the most important part of your risk analysis!
• Not checking for consistency: If you fix an age to be 25, make sure the "Years of Driving Experience" isn't 30!
6. The "M.E.N.D." Mnemonic
When you see a data error, remember to M.E.N.D. it:
M - Measure: How much data is actually missing or wrong?
E - Examine: Is there a pattern? (e.g., are only the female ages missing?)
N - Note: Document exactly what you found before you touch it.
D - Decide: Choose a repair method (Delete, Impute, or Default) and explain why.
Final Key Takeaway
Repairing data isn't about making the data "perfect"; it's about making it fit for purpose. Every time you repair a piece of data, you are making an assumption. Your goal in CP2 is to make reasonable assumptions and communicate them clearly to the reader. You’ve got this!