Cleaning openFDA data: how we linked FAERS, drug labels and approvals to trials and PubMed

ClinLit AI now answers questions from FDA’s own drug data: approvals and supplements, Orange Book patents, the NDC directory, recalls, shortages, about 91,000 prescription labels, and 7.8 million serious adverse event reports from the last ten years. openFDA publishes all of it as free bulk downloads. It is not ready to use as it comes. Each dataset names drugs its own way, a large share of labels carry no drug identifiers at all, and the adverse event reports repeat the same case under different numbers.

This post describes what we did to the data before ClinLit AI could use it: the problems we ran into, how we fixed each one, and how we checked the result. If you work with openFDA yourself, most of these problems will be yours too.

Key points

• One key for every drug. Everything is linked through FDA’s ingredient identifier (the UNII), rolled up from salts to the active moiety, so a drug’s trials, articles, approvals, labels and adverse event reports line up under any of its names, including development codes like NN9535 or MK-3475.
• Salts were the hardest part. FDA’s substance database links a salt both to its drug and to its counter-ion. A first rule sent 360 substances to the counter-ion: quetiapine fumarate’s reports were counted under fumaric acid.
• 61% of the labels in scope have no openFDA identifiers. We identify their drug from the NDC codes on the carton or from the product listing. Without them, the newest label for many drugs, including Wegovy’s, was missing.
• FAERS shrinks from 20.7 million reports to 7.8 million once limited to serious reports from the last ten years, and about 11% of those are grouped as duplicates of another report of the same case.
• The checks matched. Our report count equals openFDA’s own count for the same filter, to the report, and known safety signals (fluoroquinolones and tendon rupture, isotretinoin and depression) stand out while negative controls don’t.

What openFDA gives you

openFDA (open.fda.gov) publishes each dataset as zipped JSON files. We use the human drug datasets, plus two reference files that hold the names:

Dataset	Records (October 2026)
FAERS adverse event reports	20,692,690, in 1,767 files (about 105 GB zipped)
Drug labels (SPL)	262,901, of which 35,365 are prescription labels openFDA has identified
Drugs@FDA	29,416 applications, 188,890 submissions
Orange Book	48,865 products, 17,748 patent rows, 2,215 exclusivities
NDC directory	138,032 products
Recalls and shortages	18,043 recalls, 1,611 shortage entries
UNII list and substance database (GSRS)	176,800 UNIIs; 167,385 substances with 997,624 names

Most records carry an openfda block that openFDA adds: the UNIIs of the ingredients, brand and generic names, and the application number. That block is what makes the datasets joinable. It is also the first thing that turns out to be incomplete.

Problem 1: every dataset names drugs differently

A trial registers “NN9535”, a PubMed article is indexed under “semaglutide”, a FAERS report names “OZEMPIC 1 MG PEN”, and a label is for “Wegovy”. To answer “what do we know about semaglutide” across all of them, every one of those names has to resolve to the same drug.

The key. FDA’s UNII identifies each substance, and PubMed already stores it: for 14,299 MeSH substances, the registry number is the UNII. FDA’s substance database (GSRS) supplies each UNII’s names, including brand and development-code names (semaglutide: NN-9535, NNC 0113-0217, OZEMPIC, RYBELSUS, WEGOVY). We built one name table from GSRS, the UNII list, NDC brand names and PubMed’s MeSH synonyms, 708,975 names in all, and match names after reducing them to lower-case letters and digits, so “MK-3475”, “mk 3475” and “MK3475” are the same name.

What went wrong with names, and the fixes:

• Stripping doses broke code names. To match “semaglutide 2.4 mg SC injection” we strip dose and form words, but removing every number turned BMS-754807 into “bms”, and “MEK 162” into “mek”, which is methyl ethyl ketone. Now only standalone numbers are stripped, and at least five characters must remain.
• Generic names in the NDC directory are noisy. One insulin glargine listing’s generic name is just “Insulin”, which made every insulin resolve to glargine. We don’t use NDC generic names; NDC brand names come only from approved (NDA or BLA) single-ingredient products.
• Some names belong to several substances. “Ibuprofen” names the racemate and both enantiomers. A name shared by several drugs now goes to the one whose official name it is, or to none: we’d rather leave a name unlinked than guess.

Results. 75.7% of trials with a drug or biological intervention are linked to at least one drug (82% of industry Phase 3 trials, 66% of industry Phase 1), and 94% of Drugs@FDA applications. Most of the gap is early-phase compounds known only by a code that neither GSRS nor MeSH lists yet. There is no fuzzy matching anywhere in the links: a misspelt name stays unlinked rather than being matched to the wrong drug.

Problem 2: salts and counter-ions

Most small-molecule drugs are sold as salts: quetiapine as quetiapine fumarate, imatinib as imatinib mesylate, metformin as metformin hydrochloride. The salt and the free drug have different UNIIs but act as the same drug, so both must count as one. FDA calls the part that does the work the active moiety.

GSRS records which substances are salts of which. The trouble is that it links a salt to both of its parts: to the drug and to the counter-ion (the fumarate, the mesylate). Counter-ions are “parents” of dozens of salts: maleic acid of 124, tartaric acid of 122, citric acid of 117, fumaric acid of 79.

Each simple rule we tried failed on a spot check:

First attempt	What went wrong	Fix
Use GSRS’s “active moiety” field	Prodrugs went to their active metabolites: cyclophosphamide to 4-hydroxycyclophosphamide, capecitabine to 5’-deoxy-5-fluorocytidine	Use the salt links instead
Take any salt parent	Atorvastatin went to its strontium salt: GSRS records the link the wrong way round	A parent must be the parent of more substances than its child; a substance that is its own active moiety has no parent
Take the parent of the fewest salts	360 substances went to their counter-ion. Quetiapine fumarate went to fumaric acid, escitalopram oxalate to oxalic acid	Prefer the parent GSRS also names as active moiety
…	Quetiapine fumarate’s only salt link is to fumaric acid	Use the active moiety directly when its name is part of the salt’s name, which a prodrug’s metabolite isn’t

The counter-ion bug was found while identifying labels (below), after FAERS had already been built. Its effect on adverse event data was real: quetiapine’s reports had been counted under fumaric acid. After the fix and a rebuild, quetiapine has 64,276 reports as a suspect drug and fumaric acid 15, and quetiapine’s known signal for neuroleptic malignant syndrome appears where it should (1,218 reports). The only marketed substances that still roll up to a counter-ion are salts where the counter-ion is the drug, such as potassium citrate.

Problem 3: most labels have no identifiers

Of openFDA’s 262,901 labels, 179,998 have no openfda block: no UNII, no application number, no product type. Most are OTC products or not drugs. But our first build, which kept only identified prescription labels, missed Novo Nordisk’s current Wegovy label (revised June 2026). openFDA hadn’t identified it yet, so the only Wegovy label we had was a 2024 copy from a repackager. Unidentified labels are often the most recently revised: 6,648 are dated 2025 or later.

So we took in every label whose sections are those of prescribing information: indications plus contraindications or adverse reactions, and none of an OTC Drug Facts panel’s sections. That added 55,555 labels to the 35,365 identified ones, and each needed its drug worked out:

1. From the carton (5,874 labels): an NDC code printed on the package label or in How Supplied, looked up in the NDC directory, which also gives the application number and the labeler.
2. From the product listing (45,875 labels): a label’s product listing opens with the brand name, then the generic name, then every ingredient in capitals: “WEGOVY semaglutide SEMAGLUTIDE SEMAGLUTIDE SODIUM PHOSPHATE…”. We match the opening names against the name table.

The listing route needed the most care, because the inactive ingredients follow the name without a break:

• Tramadol labels were identified as lactose. The name “tramadol hydrochloride” was ambiguous, so the matcher went on into the ingredient list. The ingredient list in capitals is now never searched.
• An albuterol inhalation label became water and sodium chloride. A further name now counts only after a connector such as “and” or “/”, and a comma counts only outside capitals, where it separates the inactive ingredients.
• “Levothyroxine Sodium” added sodium. Salt and counter-ion words never count on their own, and only names of approved active ingredients count at all.

A second problem surfaced when we tested the search tools on the live system: the names shown for these labels. They had been copied from the listing, so 32,345 labels had the whole ingredient list as their generic name (“FEXOFENADINE HYDROCHLORIDE FEXOFENADINE SILICON DIOXIDE CROSCARMELLOSE SODIUM…”), and 13,781 a brand like “Octreotide Acetate Octreotide Acetate”. The names now come from the matched substances (“FEXOFENADINE”), and the brand from the words before the generic name. No label changed drug in the process; 51 long generic names remain, for combination products.

3,806 labels stay unidentified, mostly because their name is ambiguous. Their text can still be searched; they just can’t be found by drug. When openFDA identifies a label later, its identifiers replace ours at the next weekly refresh.

Generics share text. Only 42% of prescription label section texts are unique: generics and repackagers copy the reference label’s wording. Each distinct text is stored and embedded once, and search results show one label per text, preferring the original manufacturer’s newest version.

Problem 4: FAERS is big, filtered and full of repeats

Scope. FAERS in openFDA covers more than 20 years. We keep the last ten years by receipt date (July 2016 to June 2026) and serious reports only: death, a life-threatening event, hospitalisation, disability, a birth defect, or another medically important event. That leaves 7,781,638 of the window’s 13.9 million reports (56%). It also means every comparison in ClinLit AI is against other serious reports, and mild reactions such as nausea are under-represented. ClinLit AI states this scope whenever it uses the data.

Drugs on reports. A report lists each drug as the reporter wrote it. Only 69% of the window’s 38.5 million drug entries carry openFDA’s UNIIs; almost all the rest have an “active substance” name, sometimes several joined with a backslash. Counting by openFDA’s identifiers alone misses most reports of adalimumab and apixaban, whose products often lack them. We map every distinct product through the same name table as everything else: openFDA’s UNIIs first, then the active substance names, then the product name. That resolves 96% of drug entries, and 99% of those for the suspect drugs.

Duplicates. There are two kinds. Follow-ups to a report reuse its number with a higher version, and openFDA already keeps only the latest. Harder is the same case reported by several senders under different numbers: a physician reports to FDA and to the manufacturer, which forwards it; two manufacturers of co-suspect drugs each report; a patient and their doctor both report. Reports with the same sex, age, country, event date, suspect drugs and reactions are grouped as one case, and only the latest report of each group counts in the statistics. About 0.9 million reports (11%) were grouped this way. The grouping is deliberately strict: duplicates that differ in any detail stay separate, and reports without an age or an event date aren’t grouped, so that sparse reports aren’t merged by accident.

Statistics. For each drug and reaction with at least three reports, we precompute the measures pharmacovigilance uses to spot disproportionate reporting: the proportional reporting ratio (PRR) and reporting odds ratio (ROR) with their 95% intervals, chi-squared, and the information component’s lower bound (IC025), with the usual signal criteria (PRR at least 2, chi-squared at least 4, at least three reports). They compare a drug’s share of reports of a reaction with all other drugs’. They are leads for review, not risks: FAERS reports are voluntary and unchecked, most adverse events are never reported, and what is reported depends on publicity, lawsuits, how new a drug is and who reports. ClinLit AI says so in every answer that uses them.

How we checked the result

• Counts against openFDA’s own API. Our load kept exactly 7,781,638 reports, the API’s count for the same window and filter. Per drug, our counts are within 0.1% of the API’s count by active substance for adalimumab (281,155 vs 281,136), apixaban and semaglutide, and within 1% of its count by UNII for pembrolizumab, tirzepatide, dupilumab, nivolumab and rivaroxaban.
• Known signals stand out. Levofloxacin and ciprofloxacin with tendon rupture (PRR 44 and 36), isotretinoin with depression (14), atorvastatin with rhabdomyolysis (23), pembrolizumab and nivolumab with colitis (7 and 9), quetiapine with neuroleptic malignant syndrome (54).
• Negative controls don’t. Metformin and amoxicillin with tendon rupture: PRR 0.4.
• Novel approvals, roughly. Drugs@FDA has no “novel approval” flag. Approved original BLAs plus original NDAs for new molecular entities give 63, 55, 47, 67 and 78 for 2020 to 2024, against CDER’s published 53, 50, 37, 55 and 50. The difference is mostly biosimilars, which Drugs@FDA doesn’t mark, so ClinLit AI treats this count as approximate.
• Spot checks of the links. Ozempic, Wegovy and NN9535 resolve to semaglutide; MK-3475 and Keytruda to pembrolizumab; Gleevec and imatinib mesylate to imatinib.

Making labels searchable

Labels are searched two ways. Full-text search covers every section of all 91,000 labels, for exact terms, genes and quoted phrases. Semantic search finds passages by meaning, so “liver injury” also finds “hepatotoxicity”. It covers the key sections (boxed warning, indications, contraindications, warnings, adverse reactions, interactions, use in specific populations, mechanism) of the 52,381 current labels, cut into 450,007 passages.

Two lessons from testing it on the live system:

• The query needs an instruction. The embedding model we use is trained to see a search query with a short task description in front of it. Without one, a query’s own words outweighed its meaning: “acute pancreatitis warning” ranked metformin’s boxed warning (about lactic acidosis) first, because of the word “warning”. With it, the GLP-1 drugs’ and DPP-4 inhibitors’ pancreatitis warnings come first. The same change made adverse event term search worse (“trouble sleeping” lost Insomnia), so it is used where it helps and nowhere else.
• Ranking every match can be slow. A common term matches 20,000 or more label texts, and ranking them all took up to half a minute before the data was in memory. Keyword search now ranks every match when it can do so in a few seconds, and otherwise ranks a first set and says it did.

What’s still imperfect

• Compounds known only by a code that GSRS and MeSH don’t list yet don’t link to anything.
• A few labels are identified as an inactive ingredient, such as silicon dioxide, which passes the “approved active ingredient” test because some approved product uses it as one.
• FAERS duplicate grouping misses duplicates that differ in a detail. The reportduplicate field, which carries other senders’ case numbers on 62% of reports, isn’t used for grouping yet.
• There is no MedDRA hierarchy (it is licensed), so ClinLit AI finds related reaction terms by meaning and lists which terms it counted.

All of it is refreshed every week, and the adverse event reports every quarter, when FDA publishes them. For an example of the data in use, see our report on GLP-1 drug side effects in FAERS.