Molecule to Mergers: Turning 31,000 Pharma Companies into an Explorable Acquisition Graph

Most of my projects start with a pipeline. This one started with a question that turned out to be unanswerable with any existing tool: a drug developed by a small biotech in 1995 might be owned, three acquisitions later, by a company whose name has changed twice. Who actually controls that pipeline today — and how did it get there?

Molecule to Mergers is my answer: 31,533 pharmaceutical companies, 3,761 acquisition and dissolution events, and 35,000+ clinical trials reconciled into one explorable graph, live at avegganorkar.com/pharma-insights. Here is what makes it different from every dashboard I have built before.

The hard part is that nobody spells a company the same way

Company registries, M&A event records, and ClinicalTrials.gov sponsor fields all disagree on what to call the same entity — "Pfizer" vs "Pfizer Inc." vs "Pfizer, Inc." is the easy case. The reconciliation layer uses a two-stage match: exact lookup against a canonical name and alias map first, then rapidfuzz token_sort_ratio with an 87% cutoff for everything that survives. Clinical trials get confidence-scored fuzzy matching on the sponsor field, and only matches scoring 60 or above make it into the app — about 22,000 of the 35,277 records. The result is a graph where an acquisition chain actually chains, instead of dead-ending on a spelling variant.

An IP audit most projects skip

Halfway through, I found that one upstream source's proprietary drug-code taxonomy had cross-contaminated the clinical trial dataset — 659 of its 870 internal codes appeared verbatim in columns that looked independently parsed. The tempting move is to ignore it; the lazy one is to drop the dataset entirely. I did neither: every trace of the taxonomy was surgically removed from the loader and the UI, while the genuinely independent fields from the same source — names, countries, websites, trial dates — were kept. The same discipline applied to data quality: 85% of companies carried an "unknown" status, and instead of scraping around a robots.txt that explicitly disallows crawlers, I traced the field to its origin, documented that the source exposes a proper dated status we never captured, and found 167 companies our own acquisition data already proves are dissolved. The fix for those was free.

Four layouts, zero guessed constants

The Company Lineage page is the piece I iterated on longest. Search any company and you get its full bidirectional M&A family tree in four interchangeable layouts — and each one earns its place:

• The radial tree gives every branch angular width proportional to its subtree's leaf count. A company with 40 acquisitions gets 40 times the room of one with a single deal, not a fixed slot.
• The timeline layout puts real acquisition chronology on an ordinal axis: only years with an event get a slot, so a tree spanning 1880 to 2000 with all its activity in 1995–2000 does not waste 80% of its width on empty decades. A multi-year gap still gets an explicit "something was skipped" cue.
• Strict hierarchy and force-directed cover the classic hop-distance views.

The detail I am proudest of is invisible: node spacing is computed from the longest company name actually present in the current tree — roughly 7px per character plus padding, shared by all four layouts through one helper. Label overlap is guaranteed impossible, not heuristically unlikely. Every edge tooltip states direction explicitly ("Pfizer acquired Pharmacia (2003)"), and the hop-distance slider distinguishes "this branch really ends here" from "there is more, raise the slider" instead of truncating silently.

Production, not localhost

The dashboard runs on a Linux instance already hosting several live services, managed by pm2 with a version-controlled ecosystem file, behind nginx with Let's Encrypt TLS and WebSocket upgrade headers for Streamlit's reactivity. The interesting problem was routing: the root domain has a standing Cloudflare redirect rule pointing elsewhere, and redirect rules evaluate before Worker routes — not after, as commonly assumed. The fix was a dedicated origin subdomain, a Worker that intercepts only the /pharma-insights/* path, and a single additive exclusion clause on the existing rule. The original redirect is byte-for-byte unchanged for every other path.

Why this one matters to me

Any of the pieces — fuzzy entity resolution, a BFS graph builder, a PyVis tree, an nginx deploy — is a known quantity. What made this project unique is that every design decision was derived from the actual data rather than guessed: spacing from real name lengths, angular sweep from real branching factors, an ordinal axis from real event sparsity, and a licensing posture from an actual audit instead of optimism. That is the standard I want all my data products held to.