Join Orange Book products, patents and exclusivities in Python

01 / THE EXAMPLE

Build a drug-product research table

Suppose you have a research portfolio identified by FDA application and product numbers. The Orange Book reference adds the product name, strength, approval date and therapeutic-equivalence codes, along with the patent and exclusivity records listed for that product.

The optional input.csv contains three fictional project labels paired with real FDA product keys. The same records appear in the portfolio list in the Python code below. The project labels simply illustrate how to keep your own records attached to the added reference information.

Samples from the purchased reference

These three CSVs are unchanged selections from the $29 download, with the same columns and all patent and exclusivity relationships for the selected products. Save them together in your working folder. orange_book_products.csv is the main product table; the other files hold its related records.

The complete product covers 48,865 Orange Book product keys, with 22,438 patent listings and 2,347 distinct exclusivity records. The counts describe this source snapshot, including discontinued-category products, rather than currently available medicines.

02 / PYTHON

Code example

This code adds product details to each project and counts its related records. It works in a notebook, IPython or a script using Python’s standard library.

Paths are relative to your working folder; use from pathlib import Path; print(Path.cwd()) to check it, or supply full paths.

"""Join a fictional research portfolio to real Orange Book reference records."""
import csv
from collections import Counter

def read_csv(filename):
    with open(filename, newline='', encoding='utf-8') as stream:
        return list(csv.DictReader(stream))

# Project labels are fictional; these are real FDA application/product keys.
portfolio = [
    {'project': 'Research A', 'product_key': 'N202155-001'},
    {'project': 'Research B', 'product_key': 'N204629-001'},
    {'project': 'Research C', 'product_key': 'N205613-001'},
]
products = read_csv('orange_book_products.csv')
patents = read_csv('patents.csv')
exclusivities = read_csv('exclusivities.csv')
index = {row['product_key']: row for row in products}
assert len(index) == len(products), 'Product keys must be unique'

# Count related records separately so patents × exclusivities cannot multiply.
patent_counts = Counter(row['product_key'] for row in patents)
exclusivity_counts = Counter(row['product_key'] for row in exclusivities)
results = []
for item in portfolio:
    product = index.get(item['product_key'])
    if product is None:
        raise ValueError(f"Product not in supplied reference: {item['product_key']}")
    approval = product['approval_date'] or product['source_approval_date'] or 'Not supplied'
    result = {**item, 'trade_name': product['trade_name'], 'strength': product['strength'],
              'approval': approval, 'te_codes': product['te_codes'],
              'patent_listings': patent_counts[item['product_key']],
              'exclusivity_records': exclusivity_counts[item['product_key']]}
    assert result['patent_listings'] == int(product['patent_listing_count'])
    assert result['exclusivity_records'] == int(product['exclusivity_record_count'])
    results.append(result)
    print(f"{result['project']}: {result['trade_name']} {result['strength']} ({result['product_key']})")
    print(f"  Approval: {approval}; TE code(s): {result['te_codes'] or 'Not supplied'}")
    print(f"  Listed patent records: {result['patent_listings']}; exclusivity records: {result['exclusivity_records']}")

03 / RESULTS

Analysis of the output

Research A: ELIQUIS 2.5MG (N202155-001)
  Approval: 2012-12-28; TE code(s): AB
  Listed patent records: 13; exclusivity records: 2
Research B: JARDIANCE 10MG (N204629-001)
  Approval: 2014-08-01; TE code(s): AB
  Listed patent records: 35; exclusivity records: 3
Research C: UCERIS 2MG/ACTUATION (N205613-001)
  Approval: 2014-10-07; TE code(s): AB
  Listed patent records: 0; exclusivity records: 0

The portfolio now identifies the selected ELIQUIS, JARDIANCE and UCERIS products by their names and strengths. The application/product keys keep different strengths or dosage forms distinct. Each original project remains one result in the results list.

Count related records before combining tables

The ELIQUIS product has 13 patent listing records and two exclusivity records in this snapshot. A direct join of both detail tables would create 26 combinations for that one product. The example counts each table separately, then adds the counts to the product row. The assertions check those counts against the summaries supplied in the main table.

A patent listing can be repeated for different use codes or pediatric-extension entries. patent_listing_count therefore differs from a count of distinct patent-number strings. Open patents.csv to inspect the listed expiration dates, flags and use definitions; exclusivities.csv provides the matching code definitions and dates. Exact duplicates in the original exclusivity file are accounted for using source_occurrences and source_rows.

Interpret the source fields

The shared AB code in this example does not mean these different medicines are interchangeable. A therapeutic-equivalence assessment also depends on ingredients, strength, dosage form, route and FDA’s rules. The product retains codes without inventing equivalent-product pairs.

Zero related records for UCERIS means none are listed for this key in the captured files. It does not establish that all patent or regulatory protections are absent. Listed expiration dates are source facts, not a prediction of generic launch or legal clearance.

04 / YOUR DATA

Using the full dataset

Unzip your purchase and replace the three sample files with orange_book_products.csv, patents.csv and exclusivities.csv from the same release. Their filenames and columns are identical, so the example runs unchanged.

Replace the portfolio list with your records. If your CSV already has project and product_key columns, use:

portfolio = read_csv("my_portfolio.csv")

The key N202155-001 combines application type N, six-digit application number 202155 and three-digit product number 001. Preserve these identifiers as text. This is an application/product reference, not an NDC crosswalk; names alone are not a reliable join key. The example stops on an absent key so you can investigate rather than silently discard a project.

The download also includes a product-to-TE-code table, the used code definitions, a source-row accounting file, dictionaries, a quality report, and source/processing records. A missing or partial approval date is explicit: “before January 1, 1982” remains a bound, never an invented exact day.

05 / PROVENANCE

Sources and versions

FDA bulk files and code-definition pages were captured October 16, 2026. The ZIP’s member timestamps are October 9, 2026; that file metadata does not establish a coverage cutoff. FDA says the files update monthly, but the retained files do not name a precise monthly edition. The lookup definitions were captured separately. ACD packaged this release on 2026-10-16.

Sources: Orange Book files and field definitions, patent-use definitions, exclusivity definitions, and the FDA preface and scope.

This analytical snapshot does not provide approval-action history, guarantee market availability, or support medical care decisions. Initial release and preparation details.

Example checked October 16, 2026 against both these samples and the complete purchased files. Source and processing records, sample checksums · Expected result CSV. Independent preparation; no agency endorsement.