Welcome to Data Preparation!

Hello there! Welcome to one of the most crucial parts of the CP2 curriculum. Before an actuary can build a fancy model or predict the future, they have to deal with the "raw stuff": Data. Think of this stage like preparing ingredients for a gourmet meal. If you try to cook with unwashed vegetables or spoiled milk, it doesn't matter how good the recipe is—the result will be a disaster! In the actuarial world, we call this "Garbage In, Garbage Out" (GIGO).

In this chapter, we will learn how to check, clean, and whip our data into shape so it’s ready for analysis. Don't worry if you find data messy or overwhelming at first; we're going to break it down into simple, logical steps.

1. Data Checking: The "Look Before You Leap" Phase

Before you start changing anything, you need to understand what you have. Data checking is about finding errors, inconsistencies, or weird "outliers" (values that look out of place).

Common Visual and Logic Checks

  • Reasonableness: Does the data make sense? For example, if you see a policyholder whose age is \( 150 \), or a premium that is negative, something is probably wrong!
  • Completeness: Are there any "holes" in the data? Look for blank cells or "N/A" values where there should be numbers.
  • Consistency: If you have two tables, do they agree? For example, does the total number of claims in the "Claims" table match the total shown in the "Summary" table?

Quick Tip: Use the Filter tool in Excel to quickly look at the minimum and maximum values in a column. It’s the fastest way to spot an age of \( 500 \) or a date in the year \( 1900 \)!

Key Takeaway

Always perform high-level checks (like summing a column or counting rows) before and after you process data to ensure you haven't lost any information.

2. Data Cleaning: Scrubbing the Grime Away

Once you've found the errors, you need to fix them. However, in CP2, there is a golden rule: Never delete or change the original raw data source! Always work on a copy.

Steps for Cleaning

1. Handling Duplicates: Sometimes the same piece of data is entered twice. Use Excel’s "Remove Duplicates" feature, but be careful—ensure that the rows really are identical before hitting delete!
2. Fixing Formats: Sometimes numbers are stored as text (meaning you can’t add them up), or dates are in a weird format. Use VALUE() or DATEVALUE() functions to standardize them.
3. Dealing with Outliers: If a value is clearly wrong (like that \( 150 \)-year-old person), you might need to exclude it or replace it with a "cap." Important: Always document why you did this in your Audit Trail!

Analogy: Cleaning data is like sorting through a bag of apples. You throw away the rotten ones, polish the dirty ones, and make sure they are all in the same basket before you start making your pie.

3. Data Restructuring: Getting the Layout Right

Sometimes the data is "correct" but it’s in the wrong shape for your model. Restructuring is about moving things around so your formulas work efficiently.

Tools for Restructuring

  • Transposing: Switching rows to columns or vice-versa. (In Excel: Paste Special > Transpose).
  • Mapping and Merging: Using VLOOKUP, HLOOKUP, or INDEX/MATCH to bring data from two different tables together. For example, matching a "Policy ID" in a sales table to a "Policy ID" in a claims table.
  • Sorting and Filtering: Organizing data chronologically or by category to make it easier to read.

Did you know? Using INDEX/MATCH is often considered "best practice" over VLOOKUP because it is more flexible and doesn't break if you add new columns to your data table!

Key Takeaway

A well-structured table usually has one row per observation and one column per variable. This makes it much easier to use Pivot Tables later!

4. Data Transformation: Creating New Information

Transformation is where you use the existing data to calculate something new that your model needs. This is the "value-add" step.

Examples of Transformations

  • Grouping/Binning: Instead of looking at every individual age, you might group them into bands (e.g., \( 20-29 \), \( 30-39 \)).
  • Derived Fields: Calculating a "Claim Ratio" by using the formula: \( \text{Ratio} = \frac{\text{Total Claims}}{\text{Total Premiums}} \).
  • Scaling: If your numbers are huge, you might divide everything by \( 1,000 \) to make the outputs more readable.

Common Mistake to Avoid: Forgetting to update your formulas when you transform data. If you divide your inputs by \( 1,000 \), make sure your output labels reflect that they are now in "thousands"!

5. Using the Right Tools (The Excel Toolkit)

Since CP2 is an Excel-based exam, you should be comfortable with these specific tools for data preparation:

1. Pivot Tables: Excellent for summarizing large datasets and spotting gaps or errors quickly.
2. Data Validation: Use this to prevent bad data from being entered in the first place (e.g., setting a cell to only accept dates).
3. If/Then Logic: Using IF(), AND(), and OR() functions to flag data that meets certain criteria (e.g., =IF(Age > 100, "CHECK ME", "OK")).
4. Text Functions: LEFT(), RIGHT(), MID(), and CONCATENATE() for splitting or joining text strings.

Quick Review Box

Check: Look for errors/outliers.
Clean: Fix formats and handle duplicates.
Restructure: Move data into a usable layout (e.g., VLOOKUP).
Transform: Calculate new fields or group data.
Document: Record every change you made for your audit trail!

Summary and Final Encouragement

Data preparation might feel like a chore, but it is actually where many CP2 marks are won or lost. If your data is clean and well-structured, the rest of your modelling will be much smoother and your formulas will be simpler.

Don't worry if this seems like a lot of steps! With practice, you'll start to spot "bad data" almost instantly. Just remember to be methodical, keep your original data safe, and always explain your steps in your documentation. You've got this!