Files
stack/dev/scripts/ingest_asp.py
kert 7c8c0e72bd feat(drugs): all-code quarterly ASP payment, not-payable and NOC rows with provenance; skin subs read the same parser
Also: the CPT descriptor text is blanked in skin_substitute_hcpcs_reference.csv; level()/ndc11() reject fullwidth digits and trailing newlines, expand() caps ranges, appl_id() rejects unknown kinds.
2026-11-13 04:34:01 -05:00

220 lines
8.3 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
"""Ingest CMS Average Sales Price (ASP) Drug Pricing Files.
Reads all quarterly ASP ZIP files from data/cms/asp/ through the drug tier's
parser (``drugs.asp.parse_quarter_zip``: column-name drift, section-508 CSV
before its XLS twin, one file per quarter), filters **strictly** to
skin substitute HCPCS codes defined in data/cms/skin_substitute_hcpcs.csv,
and produces:
* data/cms/asp/skin_subs_asp_quarterly.csv (flat file with provenance)
* skin_subs.asp_quarterly (DuckDB table)
Every output row carries ``source_url`` and ``source_file`` columns for
full provenance — each observation traces back to a specific CMS
quarterly ZIP and the file within it that was parsed.
Source URLs are read from data/cms/asp_source_urls.csv (canonical URLs
provided from CMS.gov, one per quarter 2005-Q1 through 2026-Q2).
Column mapping rationale (the map itself now lives in ``drugs.asp.COL_MAP``)
---------------------------------------------------------------------------
CMS has changed column names across 20+ years of ASP files:
2005–2006: "HCPCS Code", "Short Description", "HCPCS Code Dosage",
"Estimated ASP", "Payment Limit"
2007–2011: Same but sometimes "Dosage" instead of "HCPCS Code Dosage"
2012–2019: "HCPCS Code", "Short Description", "Dosage",
"Payment Limit"
2020–2025: "HCPCS Code", "Short Description", "HCPCS Code Dosage",
"Payment Limit"
2026+: "HCPCS Code", "Short Description", "Dosage or Unit",
"Payment Limit" (now "Payment Limit" for skin subs is
flat $127.14/cm² under reclassification)
All variants are mapped to: hcpcs_code, short_description,
dosage_or_unit, payment_limit.
The ``asp_per_unit`` column is derived: payment_limit / 1.06, since
CMS payment limit = ASP + 6% for drugs/biologicals. For 2026+ skin
subs under reclassification, this derivation no longer applies (flat
rate), but we retain it for consistency and flag it in notes.
Filtering
---------
We filter STRICTLY to codes present in skin_substitute_hcpcs.csv
(the code universe built from CMS rulemaking in issue #232). This
is critical because the Q4xxx prefix includes non-skin-sub codes
(Q4054 darbepoetin alfa, Q4055 epoetin alfa, Q4074-Q4082 various
ESRD drugs) that must be excluded.
Usage:
uv run python dev/scripts/ingest_asp.py
"""
from __future__ import annotations
import csv
from pathlib import Path
import pandas as pd
from drugs.asp import newest_per_quarter, parse_quarter_zip, pick_pricing_file # noqa: F401
# ---------------------------------------------------------------------------
# Paths
# ---------------------------------------------------------------------------
ROOT = Path(__file__).resolve().parents[2]
ASP_DIR = ROOT / "data" / "cms" / "asp"
HCPCS_REF = ROOT / "data" / "cms" / "skin_substitute_hcpcs.csv"
HCPCS_REFERENCE = ROOT / "data" / "cms" / "skin_substitute_hcpcs_reference.csv"
URL_REF = ROOT / "data" / "cms" / "asp_source_urls.csv"
DUCKDB_PATH = ROOT / "data" / "aco.duckdb"
OUTPUT_CSV = ASP_DIR / "skin_subs_asp_quarterly.csv"
OUT_COLS = [
"quarter",
"hcpcs_code",
"short_description",
"asp_per_unit",
"payment_limit",
"dosage_or_unit",
"source_url",
"source_file",
]
def _load_source_urls() -> dict[str, str]:
"""Load canonical source URLs: {quarter: url}."""
urls = {}
if URL_REF.exists():
with URL_REF.open() as f:
for row in csv.DictReader(f):
urls[row["quarter"]] = row["url"]
return urls
def require_rows(df: pd.DataFrame) -> None:
"""Refuse to replace ``skin_subs.asp_quarterly`` with nothing: a parser
fault must not publish an empty table over the replica."""
if df.empty:
raise RuntimeError(
"no skin-substitute rows parsed; leaving skin_subs.asp_quarterly as it is"
)
def build_skin_df() -> pd.DataFrame:
"""Every payment-limit row of a skin-substitute code (strict filter: only
codes in skin_substitute_hcpcs.csv), one file per quarter, with
``asp_per_unit = payment_limit / 1.06`` (CMS payment limit = ASP + 6% for
drugs/biologicals; no longer meaningful for the flat 2026+ rate, kept for
consistency)."""
skin_codes = set(
pd.read_csv(HCPCS_REF, dtype=str)["hcpcs_code"].str.strip().str.upper()
)
print(f"Loaded {len(skin_codes)} skin-substitute HCPCS codes from reference")
source_urls = _load_source_urls()
print(f"Loaded {len(source_urls)} source URLs")
zips = newest_per_quarter(ASP_DIR.glob("asp_*.zip"))
print(f"Found {len(zips)} quarterly ZIP files\n")
records: list[dict[str, object]] = []
seen_q4: set[str] = set()
total = 0
for quarter, zpath in sorted(zips.items()):
try:
rows, _ = parse_quarter_zip(
zpath, quarter, source_url=source_urls.get(quarter, "")
)
except Exception as e: # a bad zip must not hide the other quarters
print(f" ERR {quarter}: {e}")
continue
payable = [r for r in rows if r.list_kind == "payment"]
if not payable:
print(f" SKIP {quarter}: no pricing rows found in ZIP")
continue
total += len(payable)
print(f" OK {quarter}: {len(payable):>5} rows from {payable[0].source_file}")
for r in payable:
if r.hcpcs in skin_codes:
records.append(
{
"quarter": quarter,
"hcpcs_code": r.hcpcs,
"short_description": r.short_desc,
"payment_limit": r.payment_limit,
"dosage_or_unit": r.dosage,
"source_url": r.source_url,
"source_file": r.source_file,
}
)
elif r.hcpcs.startswith("Q4") and len(r.hcpcs) in (4, 5):
seen_q4.add(r.hcpcs)
print(f"\nTotal rows across all quarters: {total}")
print(f"Skin substitute rows (strict filter): {len(records)}")
if seen_q4:
print(f" Excluded Q4 codes NOT in skin sub universe: {sorted(seen_q4)}")
skin_df = pd.DataFrame.from_records(records)
if skin_df.empty:
return pd.DataFrame(columns=OUT_COLS)
skin_df["payment_limit"] = pd.to_numeric(skin_df["payment_limit"], errors="coerce")
# ASP per unit = payment_limit / 1.06
skin_df["asp_per_unit"] = (skin_df["payment_limit"] / 1.06).round(3)
return skin_df[OUT_COLS].sort_values(["quarter", "hcpcs_code"])
# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------
def main() -> None:
skin_df = build_skin_df()
require_rows(skin_df)
# Write CSV
skin_df.to_csv(OUTPUT_CSV, index=False)
print(f"\nWrote {len(skin_df)} rows to {OUTPUT_CSV}")
quarters = sorted(skin_df["quarter"].unique())
codes = sorted(skin_df["hcpcs_code"].unique())
print(f" Quarters: {len(quarters)} ({quarters[0]} to {quarters[-1]})")
print(f" Unique HCPCS codes: {len(codes)}")
# Load into DuckDB
print(f"\nLoading into DuckDB at {DUCKDB_PATH} ...")
# Lock preflight + retry instead of a raw IOException when a notebook
# kernel holds the single-writer file (#508).
from conf.connect import duckdb_batch, publish_replica
with duckdb_batch("aco") as con:
con.execute("CREATE SCHEMA IF NOT EXISTS skin_subs")
con.execute("DROP TABLE IF EXISTS skin_subs.asp_quarterly")
con.execute(
"""
CREATE TABLE skin_subs.asp_quarterly AS
SELECT * FROM read_csv_auto(?, header=true)
""",
[str(OUTPUT_CSV)],
)
count = con.execute("SELECT count(*) FROM skin_subs.asp_quarterly").fetchone()[
0
]
print(f" Loaded {count} rows into skin_subs.asp_quarterly")
if HCPCS_REFERENCE.exists():
# the scrubbed reference (CPT descriptors blanked; codes and
# HCPCS Level II text kept)
con.execute("DROP TABLE IF EXISTS skin_subs.hcpcs_reference")
con.execute(
"CREATE TABLE skin_subs.hcpcs_reference AS "
"SELECT * FROM read_csv_auto(?, header=true)",
[str(HCPCS_REFERENCE)],
)
# Refresh the notebook read replica (#510).
print(f"replica → {publish_replica('aco')}")
if __name__ == "__main__":
main()