Computing Denial-Rate KPIs with pandas

Task: you have a de-identified table of adjudicated claims — one or more rows per claim carrying the 835 claim status and the CAS adjustment amounts — and you need to produce trustworthy denial-rate, dollars-at-risk, and first-pass-resolution numbers grouped by payer and reason code, without double-counting multi-line claims or letting patient-responsibility dollars leak into the denial figure. This guide gives the exact pandas needed. It implements the KPI definitions specified in Denial Analytics & Reporting and targets the Python automation engineers and healthcare IT teams who build revenue cycle reporting.

Prerequisites

Spec Reference: KPI Formulas and Source Fields

Every metric is defined against a named 835 element. The denominator column is the one that most often goes wrong: denial rate is per billed claim, not per service line and not per CAS row.

KPI Formula Source field(s)
Denial rate denied_claims / adjudicated_claims CLP02 = 4, counted over distinct CLP01
Denial $ at risk Σ CAS03 where CAS01 in {CO, PI} CAS01 group, CAS03 amount
First-pass resolution rate first_pass_paid / total_claims CLP02 = 1 on first remit, no prior denial

Relevant CLP02 claim status codes: 1 processed as primary (paid), 2 processed as secondary, 4 denied, 22 reversal of a previous payment. Relevant CAS01 group codes: CO contractual obligation, PR patient responsibility, OA other adjustment, PI payer-initiated reduction. Only CO and PI are provider-side withholds that belong in dollars at risk.

Step-by-Step Implementation

Step 1 — Load and validate the de-identified frame

Start from a tidy frame where each row is one CAS adjustment line, already carrying a de-identified claim_token and a normalized payer_key. Validate the schema up front so a missing column fails loudly at load rather than silently producing a wrong denominator downstream.

from __future__ import annotations

import logging

import pandas as pd

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(name)s | %(message)s",
)
logger = logging.getLogger("denial.kpis")

REQUIRED_COLUMNS: frozenset[str] = frozenset({
    "claim_token",    # salted hash of CLP01 — NOT the raw control number
    "payer_key",      # normalized payer cohort
    "clp02_status",   # 835 claim status code (int)
    "clp03_charge",   # billed charge for the claim
    "cas01_group",    # CO / PR / OA / PI
    "cas02_reason",   # CARC reason code, e.g. "197"
    "cas03_amount",   # adjustment dollars (>= 0)
    "is_first_remit", # True on the first 835 for this claim
})


def load_denials(frame: pd.DataFrame) -> pd.DataFrame:
    missing = REQUIRED_COLUMNS - set(frame.columns)
    if missing:
        raise ValueError(f"de-identified frame missing columns: {sorted(missing)}")
    df = frame.copy()
    # Category dtype speeds group-bys and cuts memory on high-cardinality keys.
    for col in ("payer_key", "cas01_group", "cas02_reason"):
        df[col] = df[col].astype("category")
    logger.info("loaded denial rows=%d | payers=%d",
                len(df), df["payer_key"].nunique())
    return df

Step 2 — Collapse multi-CAS rows to one status per claim

A single claim can carry several CAS segments — a contractual write-down and a denial reason, for instance. Before counting denials, reduce the frame to one status row per claim_token so the claim is counted exactly once. Take the max status only after mapping “denied” explicitly, so status-code ordering never accidentally decides the answer.

DENIED_STATUS: int = 4  # CLP02 = 4 -> denied


def claim_level_status(df: pd.DataFrame) -> pd.DataFrame:
    """One row per claim: is it denied, and was it a first-pass clean pay?"""
    grouped = df.groupby(["payer_key", "claim_token"], observed=True)
    claims = grouped.agg(
        any_denied=("clp02_status", lambda s: bool((s == DENIED_STATUS).any())),
        paid_clean=("clp02_status", lambda s: bool((s == 1).all())),
        first_remit=("is_first_remit", "max"),
        billed=("clp03_charge", "max"),  # charge is constant per claim
    ).reset_index()
    return claims

Step 3 — Compute denial rate with the correct denominator

Now the denominator is unambiguous: distinct claims per payer. Denial rate is denied claims over adjudicated claims — never over CAS rows.

def denial_rate_by_payer(claims: pd.DataFrame) -> pd.DataFrame:
    agg = claims.groupby("payer_key", observed=True).agg(
        adjudicated_claims=("claim_token", "nunique"),
        denied_claims=("any_denied", "sum"),
    )
    agg["denial_rate"] = (
        agg["denied_claims"] / agg["adjudicated_claims"]
    ).round(4)
    return agg

Step 4 — Sum dollars at risk over CO/PI only

Dollars at risk sums adjustment amounts across every CAS line, but only for the provider-side group codes. Filtering CAS01 to {CO, PI} before the sum is what keeps patient-responsibility (PR) balances out of the number.

DENIAL_GROUPS: frozenset[str] = frozenset({"CO", "PI"})


def dollars_at_risk_by_payer(df: pd.DataFrame) -> pd.Series:
    at_risk = df[df["cas01_group"].isin(DENIAL_GROUPS)]
    return (
        at_risk.groupby("payer_key", observed=True)["cas03_amount"]
        .sum()
        .rename("denial_dollars_at_risk")
    )

Step 5 — Assemble the tidy report

Join the pieces into one aggregate frame, add first-pass resolution, and sort by dollars at risk so the worst payer surfaces first. Log only the aggregate shape — never a claim_token or amount tied to a single claim.

def build_kpi_report(frame: pd.DataFrame) -> pd.DataFrame:
    df = load_denials(frame)
    claims = claim_level_status(df)

    rate = denial_rate_by_payer(claims)
    at_risk = dollars_at_risk_by_payer(df)
    first_pass = claims.groupby("payer_key", observed=True).agg(
        first_pass_rate=("paid_clean",
                         lambda s: round(float(s.mean()), 4)),
    )

    report = (
        rate.join(at_risk).join(first_pass)
        .fillna({"denial_dollars_at_risk": 0.0})
        .sort_values("denial_dollars_at_risk", ascending=False)
        .reset_index()
    )
    logger.info("kpi report | payers=%d | overall_denial_rate=%.4f",
                len(report),
                report["denied_claims"].sum() / report["adjudicated_claims"].sum())
    return report

Verification

Run the pipeline on a small deterministic sample and assert the aggregate numbers. The sample below has two payers: PAYER_A with two claims (one denied), PAYER_B with one denied claim carrying two CAS lines — the multi-CAS claim must still count as exactly one denial.

sample = pd.DataFrame([
    # PAYER_A, claim a1 — paid clean, first remit
    {"claim_token": "a1", "payer_key": "PAYER_A", "clp02_status": 1,
     "clp03_charge": 200.0, "cas01_group": "CO", "cas02_reason": "45",
     "cas03_amount": 40.0, "is_first_remit": True},
    # PAYER_A, claim a2 — denied
    {"claim_token": "a2", "payer_key": "PAYER_A", "clp02_status": 4,
     "clp03_charge": 300.0, "cas01_group": "CO", "cas02_reason": "197",
     "cas03_amount": 300.0, "is_first_remit": True},
    # PAYER_B, claim b1 — denied, TWO CAS lines (CO + PR)
    {"claim_token": "b1", "payer_key": "PAYER_B", "clp02_status": 4,
     "clp03_charge": 500.0, "cas01_group": "CO", "cas02_reason": "16",
     "cas03_amount": 450.0, "is_first_remit": True},
    {"claim_token": "b1", "payer_key": "PAYER_B", "clp02_status": 4,
     "clp03_charge": 500.0, "cas01_group": "PR", "cas02_reason": "1",
     "cas03_amount": 50.0, "is_first_remit": True},
])

report = build_kpi_report(sample)
row = report.set_index("payer_key")

# PAYER_A: 1 of 2 claims denied -> 0.5
assert row.loc["PAYER_A", "denial_rate"] == 0.5
# PAYER_B: 1 of 1 claim denied -> 1.0, counted ONCE despite two CAS lines
assert row.loc["PAYER_B", "denial_rate"] == 1.0
assert row.loc["PAYER_B", "adjudicated_claims"] == 1
# Dollars at risk: PAYER_B sums only the CO line (450), NOT the PR 50
assert row.loc["PAYER_B", "denial_dollars_at_risk"] == 450.0
# PAYER_A dollars at risk = 40 (CO on a1) + 300 (CO on a2) = 340
assert row.loc["PAYER_A", "denial_dollars_at_risk"] == 340.0

Expected log output (aggregate only — no claim identifiers):

2026-07-16 10:02:11 | INFO | denial.kpis | loaded denial rows=4 | payers=2
2026-07-16 10:02:11 | INFO | denial.kpis | kpi report | payers=2 | overall_denial_rate=0.6667

The overall denial rate is 2/3 = 0.6667 — two denied claims (a2, b1) over three adjudicated claims — confirming the multi-CAS claim b1 was not double-counted.

Common Gotchas

  • PR in the numerator. Patient-responsibility (CAS01 = PR) amounts are co-pays and deductibles, not denials. Filtering dollars at risk to {CO, PI} — and never treating a PR-only CAS line as a denial — is the difference between a real denied-dollars figure and one inflated by every patient balance.
  • Double-counting multi-CAS lines. A claim with three CAS segments is still one claim. Always reduce to claim grain (groupby claim_token) before counting denials; summing is_denied over the raw row frame over-counts every multi-line denial.
  • Wrong denominator. Denial rate’s denominator is distinct billed claims (nunique on claim_token), not service lines and not CAS rows. Mixing grains between numerator and denominator produces a number that is not a rate at all.
  • Reversals leaking in. A CLP02 = 22 reversal is not a fresh denial. If reversals appear in your feed, exclude or net them explicitly, or a re-adjudicated claim inflates both the denominator and the denial count.

Up: Denial Analytics & Reporting