homelab/stack
1
0
Fork 0
Code Issues 218 Pull Requests 2 Actions 4 Packages Projects 1 Releases Wiki Activity

skin-subs: ingest historical CMS ASP files (all quarters since inception) #233

New Issue
Closed
opened 2026-03-25 05:33:55 +00:00 by kert · 3 comments
kert commented 2026-03-25 05:33:55 +00:00
Owner
Copy Link

Objective

Build a complete longitudinal ASP pricing dataset for all skin substitute products.

Tasks

  • Identify all CMS ASP quarterly data files from inception (~2005) through current
  • Download from CMS.gov (ASP Drug Pricing Files)
  • Parse each quarterly file: HCPCS, short description, ASP per unit, volume-weighted ASP, payment limit
  • Filter to skin substitute code universe (from previous issue)
  • Build time series: skin_subs.asp_quarterly (code, quarter, asp_per_unit, payment_limit, manufacturer)
  • Track product entries/exits (new products entering market, products discontinued)
  • Tag each source file in Zotero with source:cms-asp, module:skin-subs, year:YYYY
  • Validate: cross-check totals against OIG report figures ($256M 2019 → $10B+ 2024)
## Objective Build a complete longitudinal ASP pricing dataset for all skin substitute products. ## Tasks - [ ] Identify all CMS ASP quarterly data files from inception (~2005) through current - [ ] Download from CMS.gov (ASP Drug Pricing Files) - [ ] Parse each quarterly file: HCPCS, short description, ASP per unit, volume-weighted ASP, payment limit - [ ] Filter to skin substitute code universe (from previous issue) - [ ] Build time series: `skin_subs.asp_quarterly` (code, quarter, asp_per_unit, payment_limit, manufacturer) - [ ] Track product entries/exits (new products entering market, products discontinued) - [ ] Tag each source file in Zotero with `source:cms-asp`, `module:skin-subs`, `year:YYYY` - [ ] Validate: cross-check totals against OIG report figures ($256M 2019 → $10B+ 2024)
kert added this to the P29: Skin Substitutes — predatory pricing, distribution, and utilization analysis milestone 2026-03-25 05:33:55 +00:00
kert commented 2026-03-25 13:41:23 +00:00
Author
Owner
Copy Link

Resetting approach. Previous agent downloaded 86 ZIP files but the ingestion was not rigorous enough:

  1. The skin sub filtering included non-skin-sub Q4 codes (Q4054 darbepoetin, Q4055 epoetin — these are ESRD drugs that happen to share the Q4 prefix)
  2. No provenance tracking — each ASP observation needs to trace back to a specific CMS quarterly file with a verifiable URL
  3. Source URLs need to be captured as Zotero items for citation
  4. The ingestion script needs to document every column mapping decision

Revised plan:

  1. Save canonical source URLs (86 quarters, provided by user from CMS.gov)
  2. Download each ZIP using the canonical URL (not guessed URLs)
  3. Parse with explicit column mapping documentation
  4. Filter strictly to codes in skin_subs.hcpcs_universe (not regex on Q4)
  5. Every row in the output gets a source_url and source_quarter column
  6. Add Zotero items for each quarterly file
Resetting approach. Previous agent downloaded 86 ZIP files but the ingestion was not rigorous enough: 1. The skin sub filtering included non-skin-sub Q4 codes (Q4054 darbepoetin, Q4055 epoetin — these are ESRD drugs that happen to share the Q4 prefix) 2. No provenance tracking — each ASP observation needs to trace back to a specific CMS quarterly file with a verifiable URL 3. Source URLs need to be captured as Zotero items for citation 4. The ingestion script needs to document every column mapping decision **Revised plan:** 1. Save canonical source URLs (86 quarters, provided by user from CMS.gov) 2. Download each ZIP using the canonical URL (not guessed URLs) 3. Parse with explicit column mapping documentation 4. Filter strictly to codes in `skin_subs.hcpcs_universe` (not regex on Q4) 5. Every row in the output gets a `source_url` and `source_quarter` column 6. Add Zotero items for each quarterly file
kert commented 2026-03-25 13:44:04 +00:00
Author
Owner
Copy Link

Progress update — ingestion script rewritten with full academic rigor.

Completed:

  • 86 quarterly ASP ZIP files downloaded (2005-Q1 through 2026-Q2)
  • 86 canonical source URLs captured in data/cms/asp_source_urls.csv
  • Ingestion script rewritten (dev/scripts/ingest_asp.py) with:
    • Strict filtering to skin_substitute_hcpcs.csv code universe (NO regex fallback)
    • 28 non-skin-sub Q4 codes explicitly excluded (Q4054 darbepoetin, Q4055 epoetin, Q4074-Q4099 ESRD drugs, etc.)
    • source_url and source_file columns on every row (100% provenance)
    • Documented column mapping rationale across 20 years of CMS format drift
    • DuckDB path corrected to data/aco.duckdb

Results:

  • 1,974 skin substitute ASP observations (was 2,216 with incorrect filter)
  • 68 quarters (2009-Q1 through 2025-Q4 — skin subs first appear in ASP files ~2009)
  • 169 unique HCPCS codes observed
  • Q4101 (Apligraf) ASP trajectory: $29.61/cm² (2009-Q1) → trackable through present
  • 100% of rows have source URL provenance

Note: Skin subs do not appear in pre-2009 ASP files. The Q4100+ series was created 2001 but ASP reporting for these products began later.

Remaining:

  • Tag 86 source URLs as Zotero items (deferred to #244)
  • Validate against OIG spending figures ($256M 2019, $10B+ 2024)
  • Add 2026 data once reclassification pricing stabilizes
Progress update — ingestion script rewritten with full academic rigor. **Completed:** - [x] 86 quarterly ASP ZIP files downloaded (2005-Q1 through 2026-Q2) - [x] 86 canonical source URLs captured in `data/cms/asp_source_urls.csv` - [x] Ingestion script rewritten (`dev/scripts/ingest_asp.py`) with: - Strict filtering to `skin_substitute_hcpcs.csv` code universe (NO regex fallback) - 28 non-skin-sub Q4 codes explicitly excluded (Q4054 darbepoetin, Q4055 epoetin, Q4074-Q4099 ESRD drugs, etc.) - `source_url` and `source_file` columns on every row (100% provenance) - Documented column mapping rationale across 20 years of CMS format drift - DuckDB path corrected to `data/aco.duckdb` **Results:** - 1,974 skin substitute ASP observations (was 2,216 with incorrect filter) - 68 quarters (2009-Q1 through 2025-Q4 — skin subs first appear in ASP files ~2009) - 169 unique HCPCS codes observed - Q4101 (Apligraf) ASP trajectory: $29.61/cm² (2009-Q1) → trackable through present - 100% of rows have source URL provenance **Note:** Skin subs do not appear in pre-2009 ASP files. The Q4100+ series was created 2001 but ASP reporting for these products began later. **Remaining:** - [ ] Tag 86 source URLs as Zotero items (deferred to #244) - [ ] Validate against OIG spending figures ($256M 2019, $10B+ 2024) - [ ] Add 2026 data once reclassification pricing stabilizes
kert commented 2026-03-25 13:58:48 +00:00
Author
Owner
Copy Link

Status: Data loaded with full provenance, Zotero tagging and validation outstanding.

Checklist:

  • Identified all CMS ASP quarterly data files from inception (2005-Q1) through 2026-Q2
  • Downloaded 86 quarterly ZIP files from CMS.gov
  • 86 canonical source URLs captured in data/cms/asp_source_urls.csv
  • Parsed each quarterly file with documented column mapping across 20 years of format drift
  • Filtered STRICTLY to skin substitute code universe (NO regex fallback on Q4 prefix)
  • 28 non-skin-sub Q4 codes excluded: Q4054 (darbepoetin), Q4055 (epoetin), Q4074-Q4099 (ESRD drugs), Q4172, Q4210
  • Built time series: skin_subs.asp_quarterly — 1,974 rows, 68 quarters (2009-Q1 to 2025-Q4), 169 unique codes
  • Every row has source_url (canonical CMS.gov URL) and source_file (specific file within ZIP) — 100% provenance
  • Product entry tracking: skin subs first appear in ASP files ~2009; code count grows from ~10 to 169 by 2025
  • Tag each source file in Zotero (86 items, deferred to #244)
  • Validate totals against OIG report figures ($256M 2019, $10B+ 2024)

Methodological notes:

  • ASP per unit derived as payment_limit / 1.06 (CMS payment limit = ASP + 6%). For 2026+ skin subs under reclassification (flat $127.14/cm²), this derivation no longer applies.
  • Skin subs do not appear in pre-2009 ASP files despite Q4100+ codes existing since 2001. ASP reporting for these products began later.
  • CMS has changed column names across 20+ years: "HCPCS Code Dosage" (2005-2006), "Dosage" (2007-2019), "Dosage or Unit" (2026+). All mapped to dosage_or_unit.
  • Early files (2005-Q1 through 2006-Q1) are XLS-only; 2006-Q2 onward have CSV section 508 versions. xlrd dependency added for XLS parsing.

Commits: e169842, de20b57
Files: dev/scripts/ingest_asp.py, data/cms/asp_source_urls.csv
DuckDB: skin_subs.asp_quarterly (1,974 rows)

**Status: Data loaded with full provenance, Zotero tagging and validation outstanding.** Checklist: - [x] Identified all CMS ASP quarterly data files from inception (2005-Q1) through 2026-Q2 - [x] Downloaded 86 quarterly ZIP files from CMS.gov - [x] 86 canonical source URLs captured in `data/cms/asp_source_urls.csv` - [x] Parsed each quarterly file with documented column mapping across 20 years of format drift - [x] Filtered STRICTLY to skin substitute code universe (NO regex fallback on Q4 prefix) - [x] 28 non-skin-sub Q4 codes excluded: Q4054 (darbepoetin), Q4055 (epoetin), Q4074-Q4099 (ESRD drugs), Q4172, Q4210 - [x] Built time series: `skin_subs.asp_quarterly` — 1,974 rows, 68 quarters (2009-Q1 to 2025-Q4), 169 unique codes - [x] Every row has `source_url` (canonical CMS.gov URL) and `source_file` (specific file within ZIP) — 100% provenance - [x] Product entry tracking: skin subs first appear in ASP files ~2009; code count grows from ~10 to 169 by 2025 - [ ] Tag each source file in Zotero (86 items, deferred to #244) - [ ] Validate totals against OIG report figures ($256M 2019, $10B+ 2024) **Methodological notes:** - ASP per unit derived as `payment_limit / 1.06` (CMS payment limit = ASP + 6%). For 2026+ skin subs under reclassification (flat $127.14/cm²), this derivation no longer applies. - Skin subs do not appear in pre-2009 ASP files despite Q4100+ codes existing since 2001. ASP reporting for these products began later. - CMS has changed column names across 20+ years: "HCPCS Code Dosage" (2005-2006), "Dosage" (2007-2019), "Dosage or Unit" (2026+). All mapped to `dosage_or_unit`. - Early files (2005-Q1 through 2006-Q1) are XLS-only; 2006-Q2 onward have CSV section 508 versions. `xlrd` dependency added for XLS parsing. Commits: `e169842`, `de20b57` Files: `dev/scripts/ingest_asp.py`, `data/cms/asp_source_urls.csv` DuckDB: `skin_subs.asp_quarterly` (1,974 rows)
kert closed this issue 2026-03-25 18:56:21 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
api
REST API (FastAPI)
 auth
Authentication and authorization
 bib
Bibliography, Zotero, citations
 ci
CI/CD pipelines and Woodpecker plugins
 cli
CLI commands and UX
 cloud
Cloud provider abstraction (AWS, GCP, Azure, self-hosted)
 config
Configuration and wiring
 data
Data loading, CCLF, BCDA, seeds
 databricks
Databricks jobs and deployment
 docs
Documentation and generated references
 image:api
image:docs
image:mc
image:notebooks
image:zotero
infra
lakehouse
Nessie, Polaris, Trino, Iceberg
 llm
llm module — local RAG over comments + Zotero corpus
 notebooks
marimo notebook errors (auto-filed by nb-watcher/nb-integration)
 packaging
Python packaging, optional deps, build system
 pfs
pipeline
Pipeline execution and validation
 pipeline:ci
pipeline:deploy
pipeline:harden
pipeline:infra-ci
pipeline:rebuild-all
pipeline:release
qpp
quality
Testing, coverage, error handling
 step:build
step:deploy
step:hadolint
step:lint
step:publish
step:scan
step:test
step:validate
styles
Charts, figures, visual theming
 tree
Project directory structure and file organization
No Label
Milestone
No items
No Milestone P29: Skin Substitutes — predatory pricing, distribution, and utilization analysis
Projects
Clear projects
No project
Assignees
Clear assignees
kert renovate-bot
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: homelab/stack#233
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?