Analysing Insurance Claim Patterns

import pandas as pd
import numpy as np

df = pd.DataFrame({
    "policy_id":      ["P001","P002","P003","P004","P005","P006","P007","P008",
                       "P009","P010","P011","P012","P013","P014","P015","P016",
                       "P017","P018","P019","P020"],
    "policy_type":    ["Motor","Home","Health","Travel","Motor","Home","Health","Travel",
                       "Motor","Home","Health","Travel","Motor","Home","Health","Travel",
                       "Motor","Home","Health","Travel"],
    "region":         ["North","South","East","West","South","North","West","East",
                       "East","West","North","South","West","East","South","North",
                       "North","South","East","West"],
    "age":            [27,54,68,35,22,48,71,29,33,61,44,25,55,38,65,42,31,57,72,46],
    "annual_premium": [840,620,1240,280,920,580,1380,260,760,640,1180,300,880,600,1320,540,800,660,1260,320],
    "claim_amount":   [2400,0,4800,0,1800,850,5200,0,0,1100,3600,420,2200,0,4400,0,1600,920,5800,0],
    "claim_count":    [2,0,3,0,1,1,4,0,0,1,2,1,2,0,3,0,1,1,4,0],
    "claimed":        [1,0,1,0,1,1,1,0,0,1,1,1,1,0,1,0,1,1,1,0]
})

# Portfolio headline metrics
total_premiums = df["annual_premium"].sum()
total_claims   = df["claim_amount"].sum()
portfolio_lr   = total_claims / total_premiums * 100
claim_rate     = df["claimed"].mean()

print(f"Policies:          {len(df)}")
print(f"Total premiums:    ${total_premiums:,}")
print(f"Total claims paid: ${total_claims:,}")
print(f"Portfolio loss ratio: {portfolio_lr:.1f}%")
print(f"Overall claim rate:   {claim_rate:.1%}")

# Age banding
df["age_band"] = pd.cut(
    df["age"],
    bins   = [0, 30, 45, 60, float("inf")],
    labels = ["Under 30","30–45","46–60","Over 60"]
)

# Policy type overview
type_overview = df.groupby("policy_type").agg(
    policies       = ("policy_id",      "count"),
    claim_rate     = ("claimed",         "mean"),
    mean_premium   = ("annual_premium",  "mean"),
    mean_claim_amt = ("claim_amount",    "mean"),
    total_premiums = ("annual_premium",  "sum"),
    total_claims   = ("claim_amount",    "sum")
).round(1).reset_index()

# Loss ratio: total claims / total premiums per product
type_overview["loss_ratio"] = (
    type_overview["total_claims"] / type_overview["total_premiums"] * 100
).round(1)
type_overview["claim_rate_pct"] = (type_overview["claim_rate"] * 100).round(0)
type_overview = type_overview.sort_values("loss_ratio", ascending=False)

print("\nPolicy type overview — ranked by loss ratio:")
print(type_overview[[
    "policy_type","policies","claim_rate_pct",
    "mean_premium","mean_claim_amt","loss_ratio"
]].to_string(index=False))

# Claim severity: mean amount ONLY among policies that actually claimed
claimants = df[df["claimed"] == 1]
severity_by_type = claimants.groupby("policy_type")["claim_amount"].mean().round(0)
print("Mean claim severity (claimants only) by policy type:")
print(severity_by_type.sort_values(ascending=False).to_string())

# Frequency: claim rate per type (already computed — using claimed binary)
freq_by_type = df.groupby("policy_type")["claimed"].mean().round(3)
print("\nClaim frequency (rate) by policy type:")
print((freq_by_type * 100).round(1).sort_values(ascending=False).to_string())

# Regional loss ratios
region_stats = df.groupby("region").agg(
    policies       = ("policy_id",     "count"),
    claim_rate     = ("claimed",        "mean"),
    total_premiums = ("annual_premium", "sum"),
    total_claims   = ("claim_amount",   "sum"),
    mean_claim_amt = ("claim_amount",   "mean")
).round(1).reset_index()
region_stats["loss_ratio"]      = (region_stats["total_claims"] / region_stats["total_premiums"] * 100).round(1)
region_stats["claim_rate_pct"]  = (region_stats["claim_rate"] * 100).round(0)
region_stats = region_stats.sort_values("loss_ratio", ascending=False)

print("\nRegional loss ratios — ranked:")
print(region_stats[[
    "region","policies","claim_rate_pct","total_premiums","total_claims","loss_ratio"
]].to_string(index=False))

# Correlation: premium level vs claim amount
premium_claim_corr = df["annual_premium"].corr(df["claim_amount"]).round(3)
age_claim_corr     = df["age"].corr(df["claim_amount"]).round(3)
print(f"\nCorrelations with claim_amount:")
print(f"  annual_premium: r = {premium_claim_corr:+.3f}")
print(f"  age:            r = {age_claim_corr:+.3f}")

# Age band performance
age_stats = df.groupby("age_band", observed=True).agg(
    policies       = ("policy_id",      "count"),
    claim_rate     = ("claimed",         "mean"),
    mean_premium   = ("annual_premium",  "mean"),
    total_premiums = ("annual_premium",  "sum"),
    total_claims   = ("claim_amount",    "sum"),
    mean_claim_amt = ("claim_amount",    "mean"),
    mean_age       = ("age",             "mean")
).round(1).reset_index()

age_stats["loss_ratio"]     = (age_stats["total_claims"] / age_stats["total_premiums"] * 100).round(1)
age_stats["claim_rate_pct"] = (age_stats["claim_rate"] * 100).round(0)

print("Age band performance:")
print(age_stats[[
    "age_band","policies","claim_rate_pct","mean_premium",
    "mean_claim_amt","loss_ratio"
]].to_string(index=False))

# Severity for claimants only by age band
age_severity = df[df["claimed"]==1].groupby("age_band", observed=True).agg(
    claimants      = ("policy_id",    "count"),
    mean_severity  = ("claim_amount", "mean"),
    median_severity= ("claim_amount", "median")
).round(0).reset_index()
print("\nClaim severity (claimants only) by age band:")
print(age_severity.to_string(index=False))

# Pricing gap: mean_claim_amt vs mean_premium per age band
age_stats["pricing_gap"] = (age_stats["mean_claim_amt"] - age_stats["mean_premium"]).round(0)
print("\nPricing gap (mean claim - mean premium) by age band:")
print(age_stats[["age_band","mean_premium","mean_claim_amt","pricing_gap"]].to_string(index=False))

LOSS_RATIO_THRESHOLD = 90  # % — segments above this need pricing action

# Loss ratio by policy_type x region
segment_lr = df.groupby(["policy_type","region"]).agg(
    policies       = ("policy_id",     "count"),
    total_premiums = ("annual_premium","sum"),
    total_claims   = ("claim_amount",  "sum"),
    claim_rate     = ("claimed",       "mean")
).reset_index()
segment_lr["loss_ratio"]    = (segment_lr["total_claims"] / segment_lr["total_premiums"] * 100).round(1)
segment_lr["capital_at_risk"] = (segment_lr["total_claims"] - segment_lr["total_premiums"]).clip(lower=0)

# Filter to high-loss segments
high_loss = segment_lr[segment_lr["loss_ratio"] >= LOSS_RATIO_THRESHOLD].copy()
high_loss = high_loss.sort_values("loss_ratio", ascending=False)

print(f"High-loss segments (loss ratio >= {LOSS_RATIO_THRESHOLD}%):")
print(high_loss[[
    "policy_type","region","policies","total_premiums",
    "total_claims","loss_ratio","capital_at_risk"
]].to_string(index=False))

total_capital_risk = high_loss["capital_at_risk"].sum()
print(f"\nTotal capital at risk in high-loss segments: ${total_capital_risk:,}")
print(f"High-loss segments account for {len(high_loss)} of {len(segment_lr)} product-region cells")

# What premium increase would bring each to 85% target loss ratio?
high_loss["required_premium"] = (high_loss["total_claims"] / 0.85).round(0)
high_loss["premium_increase"] = (high_loss["required_premium"] - high_loss["total_premiums"]).round(0)
high_loss["increase_pct"]     = (high_loss["premium_increase"] / high_loss["total_premiums"] * 100).round(1)
print("\nRequired premium increase to reach 85% loss ratio target:")
print(high_loss[["policy_type","region","loss_ratio","premium_increase","increase_pct"]].to_string(index=False))

# Custom aggregation: loss ratio = sum(claims) / sum(premiums)
# We need a lambda that receives a Series — use two separate pivots then divide
pivot_claims   = pd.pivot_table(df, index="region", columns="policy_type",
                                values="claim_amount", aggfunc="sum", fill_value=0)
pivot_premiums = pd.pivot_table(df, index="region", columns="policy_type",
                                values="annual_premium", aggfunc="sum", fill_value=0)

# Loss ratio pivot: element-wise division of two pivot tables
pivot_lr = (pivot_claims / pivot_premiums * 100).round(1)
pivot_lr = pivot_lr.replace([float("inf"), float("-inf")], float("nan"))

print("Loss ratio (%) by region x policy type:")
print(pivot_lr.to_string())

# Region row totals
pivot_lr["All Products"] = (
    pivot_claims.sum(axis=1) / pivot_premiums.sum(axis=1) * 100
).round(1)

print("\nWith all-products total column:")
print(pivot_lr.to_string())

# Policy type column means (unweighted — for directional comparison only)
col_means = pivot_lr.drop(columns="All Products").mean().round(1)
print("\nMean loss ratio by policy type (across regions):")
print(col_means.sort_values(ascending=False).to_string())