Pharmaceutical Supply & Shortage Intelligence
A validated openFDA snapshot of shortage status, category, dosage-form, reason, age, and update patterns.
Independent public-data reconstruction using no client or employer data.
01
Executive Summary
The snapshot contains 1,637 shortage listings, not 1,637 unique drugs. 1,177 listings (71.9%) are marked Current, while the remainder are Resolved or To Be Discontinued.
Supply signals are concentrated by dosage form and spread across overlapping therapeutic categories. Injection is the largest dosage-form label at 1,006 listings. Anesthesia is the most frequent category relationship, but 407 listings carry multiple categories.
Update activity is recent even though many Current listings are old. 77.9% of all listings were updated in the 90 days before the source as-of date, while the median Current listing age is 1,704 days.
The data is useful for monitoring with visible limitations. All 17 pages reconcile, but reason and availability fields are incomplete, one exact duplicate row is retained, and three source date-order issues are disclosed.
02
Business Question
What does the public FDA Drug Shortages data reveal about currently reported shortage listings, affected therapeutic categories and dosage forms, duration and update patterns, stated shortage reasons, and the data-quality challenges involved in monitoring pharmaceutical supply signals?
The decision-support goal is to define a repeatable monitoring view that separates status, scope, age, update activity, classification coverage, and source-data quality without converting descriptive listings into medical or causal claims.
03
Context and Provenance
This case study is not the original professional deliverable. It uses public FDA data to demonstrate comparable data-engineering, quality-validation and analytical methods in a different context.
This analysis is for portfolio and informational purposes only. It is not medical advice and should not be used to make decisions about patient care or treatment.
- Project type
- PUBLIC DATA CASE STUDY
- Provenance
- ORIGINAL INDEPENDENT ANALYSIS
- Data exposure
- PUBLIC
- Relationship to experience
- DEMO-SAFE METHODOLOGY RECONSTRUCTION
My Role
Independent analyst, data engineer, modeler, validator, and case-study author.
04
Data Sources
The controlling source is the official openFDA Drug Shortages endpoint. The website uses a generated snapshot and never depends on a live FDA request at runtime.
- openFDA Drug Shortages — Official FDA public API endpoint for Drug Shortages listing data.
- openFDA Drug Shortages documentation — Official field, source, update-frequency, and responsible-use documentation.
Data Scope and Grain
One API result is treated as a shortage listing snapshot for a company/product presentation and package NDC when present. It is not labeled as a unique drug or update event.
Source update: August 15, 2026 · Retrieval: August 17, 2026 · Update frequency: daily per official documentation.
- Source rows
- 1,637 shortage listings
- Package NDCs
- 1,590 unique native values
- Company labels
- 133 exact labels
- Category bridge
- 2,541 listing-category relationships
05
Metric Definitions
Every headline measure records its numerator, denominator, grain, date logic, and exclusions. No listing count is described as a count of unique drugs.
| Measure | Value | Numerator / denominator | Grain and date logic | Exclusions |
|---|---|---|---|---|
| Shortage listings | 1,637 | All API results in the complete snapshot Not applicable | Shortage listing snapshot Source snapshot updated 2026-08-15 | None |
| Current shortage listings | 1,177 | Listings where status = Current 1637 total listings | Shortage listing snapshot Status as of source update 2026-08-15 | Resolved and To Be Discontinued statuses |
| Unique package NDCs | 1,590 | Distinct non-empty native package_ndc values 1637 total listings | Package NDC Snapshot field; no duration logic | Missing package NDC |
| Distinct company labels | 133 | Distinct trimmed company_name labels 1637 total listings | Exact company label Snapshot field | No corporate-parent normalization |
| Median current listing age | 1,704 | Median of non-negative ages for status = Current 1177 Current listings with valid initial dates | Current shortage listing Source as-of date minus validated initial_posting_date | Non-Current statuses, missing/invalid/future initial dates |
| Listings updated in last 90 days | 77.89 | 1275 listings with update_date 0-90 days before as-of 1637 total listings | Shortage listing snapshot Validated update_date relative to source as-of date | Missing, invalid, or future update dates |
| Missing package NDC rate | 0 | 0 listings without package_ndc 1637 total listings | Shortage listing snapshot Not applicable | None |
| Multi-category listing rate | 24.86 | 407 listings with more than one category 1637 total listings | Shortage listing snapshot Not applicable | None |
06
Data-Quality Problems
The source is analytically usable with disclosed issues. Pagination, row preservation, key uniqueness, status validation, date parsing, age boundaries, and Python-to-SQL reconciliation passed. The chart keeps the material source observations visible.
Data-quality summary
Rates across 1,637 shortage listings
Text alternative: Shortage reason is missing for 74.0 percent of listings, availability for 28.1 percent, optional openFDA harmonization for 11.1 percent, multiple therapeutic categories occur on 24.9 percent, repeated package NDC rows are 2.9 percent, and date-order issues are 0.2 percent.
Pages reconciled
API metadata, retrieved, curated, and status populations each equal 1,637.
Exact duplicate
Retained with a unique occurrence ID and a shared record hash.
Date-order issues
Discontinued dates precede initial posting dates on three To Be Discontinued rows; excluded from duration findings.
Rows lost or multiplied
No NDC enrichment join was performed; the source population remains intact.
07
Technical Architecture
openFDA Drug Shortages API → paginated raw cache → Python transformation → listing and category-bridge CSVs → SQLite staging and marts → automated quality checks → executed notebook → static JSON snapshot → accessible Next.js case study
Inspect extraction, transformation, and validation methods
Extraction and Pagination
- Request pages with a 100-row limit and deterministic skip offsets.
- Retry bounded rate-limit and transient server responses.
- Require every non-final page to contain 100 rows.
- Reconcile 1,637 retrieved rows to API metadata before transformation.
Transformation Method
- Preserve every source result at listing grain.
- Normalize whitespace only; do not infer corporate parents or unique drugs.
- Explode the therapeutic-category array into a bridge without changing listing totals.
- Parse calendar dates and calculate age only for Current listings from initial posting date.
Validation Method
- Execute SQLite staging, mart, and quality-check SQL.
- Recompute five headline metrics independently in Python and SQL.
- Test leap-day, same-day, invalid-format, and future-date boundaries.
- Block Completed status on population, key, status, parsing, age, bridge, or reconciliation failure.
08
Analysis
Status and age answer different questions. Status describes the source's current classification. Age measures how long Current listings have remained represented since their validated initial posting date.
Status distribution
Listings by source status · August 15, 2026
Text alternative: Current is the largest status with 1,177 listings, followed by 450 To Be Discontinued and 10 Resolved listings.
Initial shortage postings over time
Listings in the current snapshot · 2012-2026
Text alternative: Initial posting years range from 2012 through 2026. The largest count represented in the current snapshot is 363 listings initially posted in 2023.
Therapeutic categories overlap by design. This view counts listing-category relationships; one listing can contribute to several categories, so shares do not form a mutually exclusive whole.
Therapeutic-category comparison
Top eight category relationships · multi-valued field
Text alternative: Anesthesia leads with 372 listings, followed by Pediatric with 294 and Psychiatry with 275. Gastroenterology and Neurology each have 177.
Injection dominates the dosage-form labels in the snapshot. This is a listing distribution and does not measure treatment volume, clinical severity, or patient exposure.
Dosage-form comparison
Top eight exact labels · 1,637 listings
Text alternative: Injection is the largest dosage form with 1,006 listings, followed by Tablet with 333 and Capsule with 140.
Reason reporting is incomplete. Unknown remains the leading bar so the chart does not imply that populated reason categories cover the whole population.
Shortage-reason distribution
Reported and unknown values · all statuses
Text alternative: Reason is unknown or not reported for 1,211 listings. Among reported labels, Other has 141, demand increase has 99, manufacturing discontinuation has 72, and active-ingredient shortage has 64.
09
Key Findings
- 01
Current status accounts for 1,177 of 1,637 shortage listings (71.9%).
- 02
Injection is the most common dosage-form label, representing 1,006 listings (61.5%).
- 03
Anesthesia is the most frequent therapeutic-category relationship at 372 listings; 407 records carry multiple categories, so category shares are not mutually exclusive.
- 04
The median age of Current listings is 1,704 days as of August 15, 2026; the longest Current listing in this snapshot is 5,340 days old.
- 05
1,275 listings (77.9%) were updated within 90 days of the source as-of date, including 1,090 within 30 days.
- 06
A shortage reason is reported for 426 of 1,177 Current listings (36.2%); unreported reasons remain explicit rather than being excluded.
10
Operational Recommendations
- Snapshot the endpoint on a schedule so true update-event history can be reconstructed instead of inferred from one listing state.
- Monitor status, update recency, and listing age together; no single field is a sufficient operational signal.
- Keep unknown and multi-valued classifications visible in Power BI-oriented marts and quality views.
- Treat shortage reason and availability as incomplete descriptive signals, not causal proof.
These recommendations support monitoring design and data governance. They are not medical, treatment, supply-allocation, or patient-care recommendations.
11
Limitations
- The endpoint is a listing snapshot, not a longitudinal update-event history.
- Counts represent shortage listings, not unique drugs or patients.
- Therapeutic category is multi-valued; category relationship totals can exceed listing totals.
- Availability and shortage reason are not populated for every status and include source-entered text variants.
- openFDA harmonization is based on exact matches and is not present for every listing.
- The analysis is descriptive and does not establish medical impact, patient risk, causation, or wrongdoing.
What I Would Improve
- Persist dated snapshots to create a true listing-state history and measure transitions.
- Add reviewed entity-resolution rules for company-parent analysis without overwriting source labels.
- Evaluate exact NDC-directory matches only when the added attributes justify quantified join coverage and multiplicity controls.
- Build a Power BI semantic model with listing, category bridge, date, company-label, status, and quality-check dimensions.
12
Reproducible Evidence
Python retrieval and transformation, SQLite SQL models, 14 executable SQL checks, boundary tests, an executed 16-cell notebook with six chart outputs, and the static website snapshot are packaged together.
Data Attribution
Data source: U.S. Food and Drug Administration, openFDA Drug Shortages API. Source updated August 15, 2026; retrieved August 17, 2026. openFDA states that its results should be assumed unvalidated and should not be relied on for medical-care decisions.
No FDA seal, logo, or endorsement is used or implied.