NDC to ATC in Python:
keep the unmatched codes.

01 / GET THE FILES

A small example you can inspect.

Save these four files in the same folder. You need Python 3.10 or later; there are no packages to install, API keys, or network requests in the script.

Optional: plain-text instructions, expected output, expected summary, and source versions and checksums. This is a demonstration subset, not a test of coverage for your own file.

02 / RUN THE JOIN

Keep every original row.

Open a terminal in that folder and run this command. The two lines form one command; you can put it on one line. On Windows, your Python command may be python.

python3 join.py --input input.csv --crosswalk crosswalk.csv \
  --lookup-status lookup-status.csv --output result.csv

The script writes result.csv and result-summary.json. It reads every CSV field as text, checks reference keys, and performs a left join: each input line survives, even when there is no classification. Matching rows receive the full 11-field reference record, including the product name, RxCUI and ATC hierarchy, under an acd_ prefix.

Here are five of the output columns. L1 and L2 intentionally repeat the same package code. They are separate inventory lines, so both remain.

03 / READ THE OUTCOMES

“No match” needs an explanation.

matched

L1–L3 have a classification in this release. The two distinct package codes map to A10BX and A10AB. Three matched input lines does not mean three distinct drugs.

no_current_rxcui

L4 appears in the source FDA package universe, but our retained RxNorm Current Prescribable Content does not resolve it to a current RxCUI. This says nothing conclusive about historical mappings.

no_atc4

L5 resolves to an RxCUI, but our direct ATCPROD lookup supplies no ATC-4 class. A missing classification is not evidence that the product has no therapeutic use.

unsupported_input_format

L6 is an 11-digit billing-style value. The helper refuses to reinterpret it. Separate identifier preparation from classification so you can audit each step.

not_in_lookup_table

L7 is a synthetic placeholder absent from the provided lookup file. With the full file, this means absence from that release’s source universe. With this four-row sample, it means only absence from the sample.

missing_input

L8 has an empty NDC field. Keep it visible and fix the input if a code is available.

None of these unresolved outcomes establishes that a drug was withdrawn, is invalid, or cannot be classified through another source. The full lookup file can also record ambiguous RxCUIs, unsupported source formats, or missing/conflicting product names; inspect those reasons before deciding what to do.

04 / CHECK THE TOTALS

A successful join still needs a count.

Save check.py alongside the example and run python3 check.py. It checks the expected row count, order, total units and matched rows:

import csv

with open("input.csv", newline="", encoding="utf-8") as f:
    before = list(csv.DictReader(f))
with open("result.csv", newline="", encoding="utf-8") as f:
    after = list(csv.DictReader(f))

assert len(before) == len(after) == 8
assert [r["line_id"] for r in after] == [r["line_id"] for r in before]
assert sum(int(r["units"]) for r in after) == 45
assert sum(r["acd_match_status"] == "matched" for r in after) == 3
print("8 rows, 45 units, 3 matched input rows")

Every classified NDC in this particular release has one ATC-4 row. The helper nevertheless preserves all matches if a future reference contains more than one; it reports matched_multiple, sets acd_match_count, and expands the output. Never keep the first class just to force a one-row result.

05 / USE YOUR OWN DATA

Same join. The complete reference.

For your own file, replace the example references with ActuallyCleanData_NDC_to_ATC_Crosswalk.csv and ndc_lookup_status.csv from the same purchased ZIP. Keep your original columns; use --ndc-column your_column_name if your NDC field has another name. Columns beginning acd_ are reserved for the helper.

The script rejects duplicate relationship keys and disagreement between the classified-code lists. Those checks cannot establish that two files are the same edition: keep the package together and verify its CSV checksum against metadata.json.

This release classifies 162,167 of 256,748 source FDA package codes (63.2%). That is coverage of its source universe, not a prediction of your match rate. This small example was selected to illustrate different outcomes.