Session 05: Insurance Data Sources & Cleaning

1. Insurance Data Landscape

Insurance is an information business. An insurer's ability to accurately price risk, efficiently process claims, detect fraud, and retain customers depends entirely on the quality and breadth of its data. Understanding the types of data available — and their limitations — is the foundation of insurance analytics.

1.1 The Five Categories of Insurance Data

1.2 The Data Challenge in Insurance

Insurance data is notoriously messy for several structural reasons. First, data comes from multiple sources that were never designed to talk to each other — an insurer might have separate systems for policy administration, claims, billing, CRM, and document management, each with its own data formats, identifiers, and quality standards. Second, insurance data is long-tailed — a life insurance policy can span 30+ years, and the data quality from 1995 is unlikely to match the data quality from 2025. Third, claims data suffers from "reporting delay" — a claim may occur in one accounting period but not be reported until a later period (IBNR), and the final settlement may take years (long-tail liability claims).

These structural challenges are why data cleaning is not an optional preparatory step in insurance analytics — it is often 60–80% of the work. An analyst who cannot clean data effectively cannot produce reliable insurance insights, regardless of how sophisticated their modeling skills are.

3. Setting Up Python for Data Work

Before we can clean insurance data, we need to set up our Python environment. This session assumes you are using Jupyter Notebook (or JupyterLab) with the Pandas and NumPy libraries installed.

3.1 Required Imports

Every data analysis session in this course starts with these imports:

# Core data manipulation libraries
import pandas as pd
import numpy as np

# Optional: display all columns and rows in Jupyter
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 200)

# For reproducibility
np.random.seed(42)

print("Pandas version:", pd.__version__)
print("NumPy version:", np.__version__)

3.2 Loading the Data

The six CSV files should be placed in a `data/` folder relative to your notebook. Load them as follows:

# Specify the data path
DATA_PATH = 'data/'

# Load each table
customers   = pd.read_csv(DATA_PATH + 'customers.csv')
policies    = pd.read_csv(DATA_PATH + 'policies.csv')
claims      = pd.read_csv(DATA_PATH + 'claims.csv')
fraud_indicators = pd.read_csv(DATA_PATH + 'fraud_indicators.csv')
cyber       = pd.read_csv(DATA_PATH + 'cyber_incidents.csv')
weather     = pd.read_csv(DATA_PATH + 'weather_data.csv')

# Confirm all tables loaded
print("Tables loaded successfully:")
print(f"  Customers:       {customers.shape[0]:,} rows")
print(f"  Policies:        {policies.shape[0]:,} rows")
print(f"  Claims:          {claims.shape[0]:,} rows")
print(f"  Fraud Indicators:{fraud_indicators.shape[0]:,} rows")
print(f"  Cyber Incidents: {cyber.shape[0]:,} rows")
print(f"  Weather:         {weather.shape[0]:,} rows")

3.3 Initial Exploration

Before any cleaning, explore each table to understand its structure:

# Examine the customers table
print("=== CUSTOMERS TABLE ===")
print(f"Shape: {customers.shape}")
print(f"\nColumn names and types:\n{customers.dtypes}")
print(f"\nFirst 5 rows:\n{customers.head()}")
print(f"\nSummary statistics (numeric):\n{customers.describe()}")
print(f"\nSummary statistics (categorical):\n{customers.describe(include='object')}")

# Check for missing values in each column
print("=== MISSING VALUES ===")
print(customers.isnull().sum())
print(f"\nPercentage missing:\n{customers.isnull().mean() * 100}")

# Unique values in categorical columns
print(f"\nUnique values in 'occupation': {customers['occupation'].nunique()}")
print(f"Unique values in 'location': {customers['location'].nunique()}")
print(f"Value counts for 'gender':\n{customers['gender'].value_counts()}")

4. Handling Missing Values

Missing data is the most common data quality problem in insurance datasets. The question is never "will we have missing data?" but "how should we handle it?" The right strategy depends on the type of missingness, the column's role in analysis, and the proportion of missing values.

4.1 Types of Missingness

Statisticians distinguish three types of missing data, and the appropriate handling strategy depends on which type you have:

• MCAR — Missing Completely at Random: The missingness has no relationship to any data — it is purely random. A data entry error that randomly skips a field. This is rare in practice. When it occurs, you can safely delete missing rows or impute without introducing bias.
• MAR — Missing at Random: The missingness is related to observed data but not to the missing value itself. For example, older policyholders may be less likely to report their income. If age is observed, we can model income missingness using age. This is the most common type in insurance data.
• MNAR — Missing Not at Random: The missingness is related to the missing value itself. For example, policyholders with very low credit scores may refuse to share their credit score. If the missingness is caused by the low score itself, techniques like deletion or imputation will introduce bias. This requires more advanced handling (Heckman correction, sensitivity analysis).

4.2 Practical Strategies in Pandas

Strategy 1 — Deletion (Drop Missing Rows): Use only when a small proportion of rows is affected and missingness is MCAR.

# Drop rows where ANY value is missing (use with extreme caution)
df_clean = customers.dropna()

# Drop rows where a SPECIFIC column is missing (safer)
df_clean = customers.dropna(subset=['customer_id', 'age'])

# Drop columns with more than 50% missing values
threshold = len(customers) * 0.5
customers = customers.dropna(axis=1, thresh=threshold)

Strategy 2 — Mean/Median Imputation: Replace missing numeric values with the column mean or median. Median is preferred when the distribution is skewed (as claim amounts and incomes typically are).

# Mean imputation for normally distributed data
customers['age'].fillna(customers['age'].mean(), inplace=True)

# Median imputation for skewed data (preferred for income, claim amounts)
customers['income'].fillna(customers['income'].median(), inplace=True)

# Grouped median: impute based on similar customers
customers['income'] = customers.groupby('occupation')['income']\
    .transform(lambda x: x.fillna(x.median()))

Strategy 3 — Mode Imputation: Replace missing categorical values with the most frequent category. Use for categorical variables where the missingness is small (<5%).

# Mode imputation for categorical columns
customers['gender'].fillna(customers['gender'].mode()[0], inplace=True)
customers['kyc_status'].fillna(customers['kyc_status'].mode()[0], inplace=True)

Strategy 4 — Forward Fill: Use for time-series data where a missing value likely equals the previous value (e.g., monthly claims where one month is missing).

# Forward fill for time-series data
monthly_claims['claim_count'].fillna(method='ffill', inplace=True)

# Backward fill as alternative
monthly_claims['claim_count'].fillna(method='bfill', inplace=True)

5. Data Type Conversion & Standardization

Insurance data regularly arrives with incorrect data types. Dates arrive as strings, numeric columns contain stray text characters, and categorical variables have inconsistent capitalization or naming conventions. Fixing these issues is essential before any analysis.

5.1 Date Conversion

Dates are the most commonly mis-typed column in insurance data. Always convert them explicitly:

# Convert date columns from string to datetime
policies['start_date'] = pd.to_datetime(policies['start_date'])
policies['end_date']   = pd.to_datetime(policies['end_date'])
claims['claim_date']   = pd.to_datetime(claims['claim_date'])
# NOTE: The dataset stores settlement delay as the numeric column 'days_to_settle',
# not as a 'settlement_date' column — so there is no settlement date to convert.

# Verify the conversion
print(policies[['start_date', 'end_date']].dtypes)
print(f"Date range: {policies['start_date'].min()} to {policies['start_date'].max()}")

# Create derived date features
claims['claim_year']    = claims['claim_date'].dt.year
claims['claim_month']   = claims['claim_date'].dt.month
claims['claim_quarter'] = claims['claim_date'].dt.quarter
claims['day_of_week']   = claims['claim_date'].dt.dayofweek  # 0=Monday, 6=Sunday

5.2 Numeric Cleaning

Numeric columns often contain non-numeric characters — currency symbols, commas, spaces, or the word "NA":

# Check if premium column is actually numeric
print(f"Premium dtype: {policies['premium'].dtype}")
print(f"Unique non-numeric values:\n{policies[pd.to_numeric(policies['premium'], errors='coerce').isna()]['premium'].unique()}")

# Clean: remove currency symbols, commas, and convert to float
# NOTE: Only needed if the column was imported as TEXT (object dtype).
# In our generated dataset premium is already float, so we guard the
# .str accessor with a dtype check to avoid an AttributeError.
if policies['premium'].dtype == 'object':
    policies['premium'] = policies['premium']\
        .str.replace('₹', '')\
        .str.replace(',', '')\
        .str.strip()\
        .astype(float)

# For claim amounts — check for outliers first using .describe()
print(claims['claim_amount'].describe())

# Cap extreme outliers at the 99th percentile (optional, based on business context)
cap = claims['claim_amount'].quantile(0.99)
claims['claim_amount_capped'] = claims['claim_amount'].clip(upper=cap)

5.3 Categorical Standardization

Inconsistent categorical values are a persistent problem — "Motor", "motor", "MOTOR", and "Motor Insurance" should all map to the same category:

# Check unique values before cleaning
print("Before cleaning:", policies['policy_type'].unique())

# Standardize to title case
policies['policy_type'] = policies['policy_type'].str.strip().str.title()

# Map variations to standard categories
type_mapping = {
    'Motor Own Damage': 'Motor',
    'Motor Od': 'Motor',
    'Motor': 'Motor',
    'Health': 'Health',
    'Health Insurance': 'Health',
    'Property': 'Property',
    'Property Insurance': 'Property',
    'Fire': 'Property',
    'Crop': 'Crop',
    'Crop Insurance': 'Crop',
    'Travel': 'Travel'
}
policies['policy_type'] = policies['policy_type'].map(type_mapping)

print("After cleaning:", policies['policy_type'].unique())
print(f"Value counts:\n{policies['policy_type'].value_counts()}")

6. Duplicate Detection & Removal

Duplicates in insurance data can arise from system errors (the same claim entered twice), data integration (overlapping tables from merged systems), or data entry mistakes. Removing them requires careful judgment — what looks like a duplicate may actually be legitimate distinct records.

6.1 Identifying Duplicates

# Check for exact duplicates across all columns
exact_dups = claims.duplicated().sum()
print(f"Exact duplicate rows: {exact_dups}")

# Check for duplicates based on key columns (more common in insurance)
# A claim_id should be unique — any duplicate is a data error
dup_claim_ids = claims['claim_id'].duplicated().sum()
print(f"Duplicate claim IDs: {dup_claim_ids}")

# Show the actual duplicates (if any)
if dup_claim_ids > 0:
    dupe_mask = claims['claim_id'].duplicated(keep=False)
    print(claims[dupe_mask].sort_values('claim_id').head(10))

6.2 Smart Deduplication

Not all duplicates are identical — a customer may have two policies with the same policy type (legitimate — they bought a second vehicle) or the same claim entered twice (a duplicate that must be removed). The deduplication logic must reflect the business context.

# Remove exact duplicates (rare)
claims_deduped = claims.drop_duplicates()
print(f"Rows after exact dedup: {len(claims_deduped)}")

# For claim_id duplicates: keep the first occurrence, drop the rest
claims_deduped = claims.drop_duplicates(subset=['claim_id'], keep='first')

# For policy_id duplicates: check if same customer has multiple policies of same type
# These may be legitimate (two motor policies for two different cars)
# Flag them for review rather than automatically removing
policy_dup_check = policies.duplicated(subset=['customer_id', 'policy_type', 'start_date'], keep=False)
policy_suspicious = policies[policy_dup_check]
print(f"Policies with identical customer+type+start_date (possible duplicates): {len(policy_suspicious)}")

# For these suspicious cases, keep the one with the higher 'premium' (more complete data?)
policies = policies.sort_values('premium', ascending=False)\
                   .drop_duplicates(subset=['customer_id', 'policy_type', 'start_date'], keep='first')

7. Merging Insurance Tables

The cleaned tables must be joined to create the analysis-ready dataset. In insurance analytics, most analysis requires data from multiple tables — calculating loss ratios by customer segment (needs Customer + Policy + Claims) or building an underwriting model (needs features from all three).

7.1 The Merge Strategy

We join the tables using Pandas' `merge()` function, which works like SQL JOINs. The standard workflow is:

# Step 1: Merge Customer → Policy (left join: keep all policies, add customer info)
policy_customer = policies.merge(
    customers,
    on='customer_id',
    how='left'  # Keep all policies even if customer data is missing
)

# Step 2: Merge Policy+Customer → Claims
full_data = claims.merge(
    policy_customer,
    on=['policy_id', 'customer_id'],
    how='left'  # Keep all claims
)

# Verify the merged result
print(f"Merged dataset shape: {full_data.shape}")
print(f"Columns: {full_data.columns.tolist()}")
print(f"Missing values in merged dataset:\n{full_data.isnull().sum()}")

# Optional: merge fraud indicators
full_data = full_data.merge(
    fraud_indicators,
    on='claim_id',
    how='left'
)

print(f"Final dataset shape: {full_data.shape}")

7.2 Verifying the Merge

Always verify that the merge worked as expected:

# Check: did every claim get matched to a policy?
claims_unmatched = full_data[full_data['premium'].isna()].shape[0]
print(f"Claims without matching policy: {claims_unmatched}")

# Check: did every policy get matched to a customer?
policies_unmatched = policy_customer[policy_customer['age'].isna()]['policy_id'].nunique()
print(f"Policies without matching customer: {policies_unmatched}")

# Row count sanity check
print(f"Original claims: {len(claims)}")
print(f"Merged dataset:  {len(full_data)}")
if len(full_data) > len(claims):
    print("CAUTION: Merge created extra rows — check for duplicate keys!")
elif len(full_data) < len(claims):
    print("CAUTION: Merge dropped rows — check that all claim IDs exist in policies table!")

8. Data Quality Report

The final step is to generate a data quality report — a structured summary of data quality issues in the cleaned dataset. This becomes documentation for anyone using the data for analysis or modeling.

8.1 Building the Report

def generate_data_quality_report(df, dataset_name):
    """
    Generate a comprehensive data quality report for a DataFrame.

    Parameters:
    -----------
    df : pd.DataFrame — The dataset to report on
    dataset_name : str — Name of the dataset for the report header

    Returns:
    --------
    pd.DataFrame — A quality report with one row per column
    """
    report = pd.DataFrame({
        'Column': df.columns,
        'Data Type': df.dtypes.values,
        'Non-Null Count': df.notna().sum().values,
        'Null Count': df.isna().sum().values,
        'Null %': (df.isna().mean() * 100).values,
        'Unique Values': [df[col].nunique() for col in df.columns],
        'Has Duplicates': [df[col].duplicated().any() for col in df.columns],
    })

    # Detect numeric columns for outlier check
    numeric_cols = df.select_dtypes(include=[np.number]).columns
    report['Has Outliers (IQR)'] = '—'

    for col in df.columns:
        if col in numeric_cols:
            Q1 = df[col].quantile(0.25)
            Q3 = df[col].quantile(0.75)
            IQR = Q3 - Q1
            outlier_count = ((df[col] < (Q1 - 1.5 * IQR)) | (df[col] > (Q3 + 1.5 * IQR))).sum()
            report.loc[report['Column'] == col, 'Has Outliers (IQR)'] = \
                'Yes (' + str(outlier_count) + ')' if outlier_count > 0 else 'No'

    # Sort by null percentage descending
    report = report.sort_values('Null %', ascending=False).reset_index(drop=True)

    print(f"\n{'='*60}")
    print(f"DATA QUALITY REPORT: {dataset_name}")
    print(f"{'='*60}")
    print(f"Shape: {df.shape[0]:,} rows × {df.shape[1]:,} columns")
    print(f"Memory usage: {df.memory_usage(deep=True).sum() / 1024**2:.1f} MB")
    print(f"Columns with nulls: {(df.isna().sum() > 0).sum()} of {df.shape[1]}")
    print(f"Duplicate rows: {df.duplicated().sum()}")
    print(f"{'='*60}\n")

    return report


# Generate reports for each table
for name, df in [
    ('Customers', customers),
    ('Policies', policies),
    ('Claims', claims),
    ('Merged Dataset', full_data)
]:
    report = generate_data_quality_report(df, name)
    try:
        display(report)   # Jupyter displays a formatted table
    except NameError:
        print(report)     # Fallback for running as a plain .py script
    print("\n")

8.2 Saving the Cleaned Data

Once the data is cleaned and merged, save it for use in subsequent sessions:

# Export the cleaned, merged dataset
full_data.to_csv('data/insurance_cleaned.csv', index=False)
print(f"Cleaned dataset saved: data/insurance_cleaned.csv ({len(full_data):,} rows)")

# Also save individual cleaned tables
customers.to_csv('data/customers_cleaned.csv', index=False)
policies.to_csv('data/policies_cleaned.csv', index=False)
claims.to_csv('data/claims_cleaned.csv', index=False)

# Save the quality report for reference
quality_report = generate_data_quality_report(full_data, 'Merged Dataset')
quality_report.to_csv('data/data_quality_report.csv', index=False)
print("Data quality report saved: data/data_quality_report.csv")