```html <!doctype html> Metformin IQVIA–SDUD–NADAC Integration: Methods & Data Documentation

Metformin IQVIA–SDUD–NADAC Integration: Methods & Data Documentation

Last updated: 2025-09-14

Purpose

We built a monthly NDC11 × date panel by merging IQVIA volumes, Medicaid SDUD units/prescriptions/spend, and NADAC prices. Pre-Jul 2019 IQVIA rows lacked NDCs, so we backfilled them using a product signature from 2019+, optionally splitting across multiple NDCs by 2019+ shares.

Inputs (relative to project `Data/` root)

IQVIA (2019+ with NDCs): 06 - Metformin Data/IQVIA/Metformin Jul 2019 - Jun 2025 NDC Level.xlsx (sheets: TRx, Extended Units)
IQVIA (2015–Mar 2025, no NDCs): 06 - Metformin Data/IQVIA/Metformin Jan 2015 - Mar 2025 No NDC.xlsx (drop “Grand Total”, use months ≤ 2019-06)
SDUD parquet: 04 - Medicaid - SDUD/sdud_monthly_national.parquet
NADAC weekly CSVs: 11 - Medicaid - NADAC/ with NADAC_*.csv

Key normalization

NDC normalization

Prefer an 11-digit run; else parse 10-digit hyphenated forms (4–4–2, 5–3–2, 5–4–1) and pad to 5–4–2.
Never zfill blanks to 00000000000; remove invalid placeholders.

Product signature (for pre-2019)

Canonicalized concat of Combined Molecule + Manufacturer + Prod Form2 + Product Sum + Strength (separates GLUCOPHAGE vs GLUCOPHAGE XR).

IQVIA processing

2019+ with NDC: tidy long: ndc11, signature, date, metric∈{TRx, Extended Units}, volume + meta.
Learn signature → NDC shares: weight = TRx + Extended Units; convert to within-signature shares; optionally drop tiny shares via --share-min; save signature_to_ndc_mapping.csv.
2015–Jun 2019 (no NDC): tidy by signature, date, metric, volume (drop “Grand Total”).
Backfill: proportional (split by shares, add alloc_share) or modal (pick top NDC).
Concat pre-2019 + 2019+; pivot to wide by (ndc11, date): iqvia_trx, iqvia_extended_units. Meta kept via per-NDC mode.

Audit: pre_allocation_audit.csv compares pre-2019 source vs allocated totals by (signature, date, metric); delta ≈ 0.

SDUD processing

Aggregate to ndc11 × month (national): units, prescriptions, and spend; derive:

sdud_price_total_per_unit = total_amount_reimbursed / sdud_units_reimbursed
sdud_price_medicaid_per_unit = medicaid_amount_reimbursed / sdud_units_reimbursed

NADAC processing

Normalize all weekly files and average to month-end: nadac_price (mean), n_obs, pricing_unit_mode. Optional filter --nadac-units (e.g., EA).

Merge logic

Start with IQVIA wide.
Left-join SDUD by (ndc11, date).
Left-join NADAC by (ndc11, date).
Attach per-NDC meta (mode), drop invalid NDCs, format date.

Outputs

Main panel: iqvia_with_sdud_nadac.cleaned.csv with keys, meta, IQVIA, SDUD, and NADAC fields.
Signature → NDC shares: signature_to_ndc_mapping.csv
Pre-allocation audit: pre_allocation_audit.csv

Diagnostics

Coverage (SDUD/NADAC match rates)
Mean/median of IQVIA Extended Units overall vs blanks (both SDUD & NADAC missing)
Correlation: NADAC vs SDUD implied prices

Reproducing

python -u src/iqvia_with_sdud_nadac.py \
  --data-dir "<path to Data root>" \
  --iqvia-xlsx "06 - Metformin Data/IQVIA/Metformin Jul 2019 - Jun 2025 NDC Level.xlsx" \
  --iqvia-pre2019-xlsx "06 - Metformin Data/IQVIA/Metformin Jan 2015 - Mar 2025 No NDC.xlsx" \
  --out-csv "06 - Metformin Data/IQVIA/iqvia_with_sdud_nadac.cleaned.csv" \
  --mapping-csv "06 - Metformin Data/IQVIA/signature_to_ndc_mapping.csv" \
  --nadac-units EA \
  --pre-mapping-strategy proportional \
  --share-min 0.01

Notes & limitations

Multiple valid NDCs per signature (e.g., pack sizes) handled by proportional split; use modal if you need one code.
Optional date-aware restriction (exclude NDCs first seen after a month) can be added.
IQVIA units ≠ SDUD units; SDUD prices are computed from SDUD spend/units.

Data dictionary (selected)

ndc11: 11-digit National Drug Code (5–4–2)
date: month-end (YYYY-MM-DD)
iqvia_trx / iqvia_extended_units: IQVIA volumes
sdud_units_reimbursed, sdud_num_prescriptions: SDUD totals
total_amount_reimbursed, medicaid_amount_reimbursed: SDUD spend
sdud_price_total_per_unit, sdud_price_medicaid_per_unit: SDUD implied prices
nadac_price: average NADAC per unit; with pricing_unit_mode, n_obs
Meta: Combined Molecule, Manufacturer, Prod Form2, Product Sum, Strength