2.2 Importing, Joining & Reshaping Data Sets

What Task A-2 Actually Asks

The Content Outline's second Dealing with Data task is to "import, manipulate, and evaluate data sets using generally available programming languages and software packages (e.g., .csv file)." The supporting readings are Grolemund and Wickham's R for Data Science and McKinney's Python for Data Analysis, and the project accepts code in R, Python or SAS. CAS is explicit that no language is preferred, so exam items describe operations, not vendor syntax — but the project requires you to actually execute them.

The practical shape of the work is almost always the same: read a policy file, read a claim file, aggregate claims, join them onto the policy spine, and end with one row per unit of analysis carrying exposure, target, and predictors.

Reading a Flat File Without Corrupting It

A .csv carries no type information, so the reader guesses. The guesses are predictable, and so are the failures.

The defence is the same everywhere: declare types at read time rather than repairing them afterwards.

After any import, evaluate before you proceed: row count against the source system, column count against the dictionary, min and max of every date, and a frequency count of every categorical level. A file that arrived with 18 months of data when you asked for 36 is cheaper to catch now than after you have fitted three models.

The Policy-to-Claim Join

Insurance data arrives on two different grains. Policy data is one row per policy term (or per vehicle, per location, per coverage). Claim data is one row per claim, and most policies have none. Getting from two files to one modelling table is where the majority of preparation errors happen.

The safe sequence has three steps:

1. Build the policy spine. One row per unit of analysis, with exposure. This is the denominator and it must never lose rows.
2. Aggregate claims to that grain first. Sum incurred loss and count claims by the join key before joining. This collapses the many side to one row per key.
3. Left join the aggregate onto the spine, then fill nulls with zero. Policies with no claims must survive with a claim count of 0 and an incurred loss of 0.

  policy_terms (1 row per term)          claims (1 row per claim)
  ----------------------------          -------------------------
  policy_id, term_eff, exposure          policy_id, term_eff, incurred
          |                                        |
          |                          aggregate: SUM(incurred), COUNT(*)
          |                                  GROUP BY policy_id, term_eff
          |                                        |
          +----------------  LEFT JOIN  ----------+
                                 |
                     coalesce(claim_cnt, 0), coalesce(incurred, 0)
                                 |
                        modelling table: 1 row per term

Join Defects That Destroy Exposure

Inner join instead of left join. The single most damaging error in insurance data preparation. An inner join keeps only policies that had a claim, so the zero-claim exposure disappears. Frequency, which should be perhaps 0.05, becomes something close to 1.0, and every relativity in the model is wrong. Check: does the post-join row count equal the spine row count?

Many-to-many join. If the key is not unique on the claim side and you join before aggregating, a policy with three claims becomes three rows — and its exposure is now counted three times. Check: does total exposure after the join equal total exposure before it?

Key type mismatch. Character "0012345" will not match integer 12345. The join silently produces zero matches on the affected subset rather than throwing an error.

Date-boundary mismatch. Joining claims to terms on policy number alone assigns a 2025 claim to a 2023 term. The key must include the term identifier, or the join must be a range join on accident date between term effective and expiry dates.

Missing-key rows. NULL never equals NULL. Rows with a missing join key drop out of an inner join and match nothing in a left join, so count them explicitly.

[!WARNING] Reconcile three totals after every join: row count, exposure, and incurred loss. All three should be explainable. If exposure rose, you have duplication. If it fell, you have dropped rows. If loss fell, claims failed to match. This three-number check takes one minute and catches most preparation failures before they reach a model.

Wide, Long, and the Analysis Grain

Insurance data is frequently wide at the policy level (one column per coverage: bi_premium, pd_premium, comp_premium) and long at the claim level (one row per claim, with a coverage code).

Which shape you need depends on the target. A pure premium model by coverage needs the long form, one row per policy-coverage. A single all-perils model needs the wide form aggregated to the policy. Pivoting between them — pivot_longer/pivot_wider in R, melt/pivot in pandas, PROC TRANSPOSE in SAS — is routine, but each pivot changes what a row means, and therefore what exposure and weights must be.

State the grain of your final table in one sentence before modelling. If you cannot, the table is not ready.