Molecule to Mergers
Production-deployed data platform that reconstructs the acquisition history of the pharmaceutical industry. Multi-source ETL with two-stage exact-then-fuzzy entity resolution across 31,533 companies, confidence-scored clinical trial matching, and six purpose-built dashboard pages — including a four-layout M&A family tree visualizer with proportional radial sweeps and an ordinal event timeline. Deployed behind nginx + pm2 with Cloudflare Worker path routing on a live domain.
Problem
Pharma industry structure is a moving target. A drug developed by a small biotech in 1995 might be owned, three acquisitions later, by a company whose name has changed twice. Answering 'who actually controls this drug pipeline today, and how did it get there?' requires reconciling messy, inconsistently-named entities across company registries, M&A event records, and clinical trial sponsor fields — none of which agree on how to spell a company's name.
Solution
Built the reconciliation layer: a two-stage exact-then-fuzzy entity match (rapidfuzz token_sort_ratio, 87% cutoff) against a canonical name/alias map, plus confidence-scored sponsor matching for 35,277 ClinicalTrials.gov records. Exposed the resulting graph through six Streamlit dashboard pages, headlined by a Company Lineage visualizer offering four interchangeable tree layouts whose spacing and angular sweep are computed from the actual data — never guessed.
Architecture
Four CSVs joined into one coherent model: 31,533 companies from the CDEK knowledgebase, 3,761 date-validated acquisition/dissolution events resolved to company IDs, 35,277 clinical trial records, and 1,272 curated enrichment entries.
Two-stage matching — exact lookup against a canonical name/alias map, then rapidfuzz token_sort_ratio at an 87% cutoff. Clinical trial sponsors are fuzzy-matched with confidence scores; only matches ≥60 (~22k records) are used in-app.
Detected a proprietary drug-code taxonomy cross-contaminated into derived clinical trial columns (659 of 870 codes verbatim). Surgically removed every trace from loader and UI while preserving the genuinely independent fields from the same source.
Directed, cycle-tolerant acquisition graph built from relational event data with BFS traversal under depth and node budgets — powering full bidirectional M&A family trees for any searched company.
Four layout modes (proportional radial dendrogram, ordinal-axis timeline, strict hierarchy, force-directed) on NetworkX + PyVis. Node spacing derived from the longest company name in the current tree — guaranteed non-overlap.
pm2-managed Streamlit behind nginx with Let's Encrypt TLS and WebSocket upgrade headers, routed under an existing domain via a Cloudflare Worker scoped to /pharma-insights/* — with the pre-existing root redirect rule left byte-for-byte intact.
Highlights
- Entity resolution across three disagreeing sources — 31,533 companies canonicalized with exact-then-fuzzy matching.
- Radial tree layout with angular width proportional to each subtree's leaf count, not fixed slots.
- Ordinal timeline axis: only years with real events get width; multi-year gaps get an explicit skip cue.
- IP audit that surgically removed a proprietary taxonomy (659 contaminated codes) instead of dropping the dataset.
- Traced 85% 'unknown' company statuses to their origin and recovered 167 dissolutions from our own event data.
- Discovered Cloudflare evaluates redirect rules before Worker routes — solved coexistence with one additive exclusion clause.