Study guide
2.2 Importing, Joining & Reshaping Data Sets
CAS Property & Casualty Predictive Analytics PCPA
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: PCPA Exam & Project Orientation
1.1 PCPA Exam Format, Scoring & Registration 1.2 The PCPA Project: Windows, Deliverables & Grading
2 Chapter 2: Dealing with Data — Sourcing, Importing & Assessing
2.1 Requirements Gathering & Assessing Data Relevance 2.2 Importing, Joining & Reshaping Data Sets 2.3 Exploratory Data Analysis & Evaluating a Data Set 2.4 Data Quality Under ASOP 23 & Modeling Under ASOP 56
3 Chapter 3: Dealing with Data — Conditioning P&C Data for a GLM
3.1 Exposure Bases, Weights & Data Grain 3.2 Missing Data Mechanisms & Imputation Strategies 3.3 Outlier Diagnostics, Trimming & Winsorization 3.4 Variable Transformation & Scaling
4 Chapter 4: Model Diagnostics & Selection — Building a GLM
4.1 Exponential Dispersion Family & Link Functions 4.2 Poisson Regression & Exposure Offsets 4.3 Overdispersion, Quasi-Poisson & Negative Binomial 4.4 Gamma & Inverse Gaussian Models for Claim Severity
5 Chapter 5: Model Diagnostics & Selection — Model Form & Predictors
5.1 Tweedie Compound Poisson-Gamma Modeling 5.2 Binomial & Logistic GLMs for Binary Outcomes 5.3 Categorical Predictors, Base Levels & High-Cardinality Grouping 5.4 Interaction Terms & Non-Linear Effects 5.5 Interpreting Coefficients & Multiplicative Relativities
6 Chapter 6: Model Diagnostics & Selection — Evaluating and Improving the Fit
6.1 Deviance, Likelihood Ratio Tests & Residual Diagnostics 6.2 Stepwise Selection & Information Criteria Penalties 6.3 Multicollinearity, Correlated Predictors & Aliasing 6.4 Under-fitting, Over-fitting & Cross-Validation
7 Chapter 7: Model Diagnostics & Selection — Validation & Model Choice
7.1 Actual vs. Predicted, Calibration & Loss Ratio Charts 7.2 Simple Quantile Plots & Measuring Lift 7.3 Double Lift Charts & Competing Model Evaluation 7.4 Gini Index, Lorenz Curves & ROC Curves
8 Chapter 8: Model Interpretation & Presentation — Tables, Charts & Exhibits
8.1 Designing Tables & Statistical Exhibits 8.2 Chart Selection & Graphical Perception 8.3 How Charts Mislead 8.4 One-Way, Two-Way & Partial Dependence Exhibits
9 Chapter 9: Model Interpretation & Presentation — Communicating the Analysis
9.1 Communicating Methodology & Modeling Decisions 9.2 Explaining Why a Variable Was or Was Not Included 9.3 Communicating to Non-Technical Audiences 9.4 The PCPA Technical Report: Structure & Rubric
2.2 Importing, Joining & Reshaping Data Sets
Key Takeaways Explain This Topic AI
Task A-2 requires importing, manipulating and evaluating data sets in generally available languages, and the PCPA Project accepts code in R, Python or SAS only.
A left join from the policy spine to claims preserves zero-claim exposures, while an inner join silently deletes them and inflates every frequency estimate.
A many-to-many join between policy terms and claims multiplies exposure; always confirm the key is unique on at least one side before joining.
Insurance modelling data is wide at the policy level and long at the claim level, so aggregating claims to the policy grain before joining is the safer direction.
Reading a .csv without controlling column types turns leading-zero ZIP codes into integers and blank strings into NA, corrupting keys before any modelling begins.
Last updated: September 2026
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.
- Field Naive default What breaks
- ZIP code 02134 Integer 2134 Leading zero lost; join to territory table fails for all New England
- Policy number 0012345 Integer Same key-corruption problem
- Effective date 03/04/2025 String or wrong locale March 4 vs April 3 ambiguity silently flips
- Empty string "" NA / NaN "Not applicable" becomes indistinguishable from "not collected"
- $1,234.50 String Currency symbols and separators block numeric conversion
The defence is the same everywhere: declare types at read time rather than repairing them afterwards.
Language Type-controlled read
R readr::read_csv("policy.csv", col_types = cols(zip = col_character(), eff_date = col_date("%m/%d/%Y"), exposure = col_double()))
Python pd.read_csv("policy.csv", dtype={"zip": "string"}, parse_dates=["eff_date"], keep_default_na=False)
SAS PROC IMPORT with a GUESSINGROWS= override, or an explicit DATA step INFILE / INPUT with informats such as $5. and MMDDYY10.
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:
Build the policy spine. One row per unit of analysis, with exposure. This is the denominator and it must never lose rows.
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.
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
Operation R (dplyr) Python (pandas) SAS
Aggregate group_by(policy_id, term_eff) %>% summarise(...) groupby([...]).agg(...) PROC SQL with GROUP BY , or PROC MEANS ... NWAY
Left join left_join(spine, agg, by = c(...)) spine.merge(agg, how="left", on=[...]) PROC SQL LEFT JOIN , or a MERGE ... IN= DATA step
- Fill nulls replace_na(list(claim_cnt = 0)) fillna({"claim_cnt": 0}) IF MISSING(claim_cnt) THEN claim_cnt = 0;
- 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.