NDC conflicts between branded drugs and clinical drugs

[hoganwr]

Despite cleaning up duplicate NDCs and implementing a fix in the scripts to prevent the error from recurring, there are a couple dozen NDCs that exist in both template files: ndc_branded_drug.tsv and ndc_clinical_drug.tsv.

My theory about how these errors occur is that in earlier versions of RxNorm, the NDC is associated with an SCD (clinical drug), and then in later versions of RxNorm, gets "reassociated" with an SBD (branded drug).

The current script adds any NDC to ndc_branded_drug that does not already exist there, then adds NDCs to ndc_clinical_drug as long as the NDC does not exist in ndc_branded_drug.

As with all such issues, there are two stages of resolution: (1) fix the existing problem and (2) implement a fix that prevents if from recurring.

In terms of preventive fix, one thought is to first add all NDCs that do not exist in either table, then find all "reassignments" of NDCs to different RxCUIs. One tricky aspect is the situation where we'd have to "move" an NDC from ndc_clinical_drug to ndc_branded_drug (which would just be implemented by deletion and insertion, but still...).

[hoganwr]

Here's an example:

../templates/ndc_branded_drug.tsv:DRON:00843509 00132000508 DRON:00076018 ../templates/ndc_clinical_drug.tsv:DRON:01066147 00132000508 DRON:00042305

The same NDC, '00132000508' is in both template files with a different DrOn IRI. They are associated with different IRI targets as well.

If you look at branded_drug.tsv, you see that the two targets are related by subClassOf, which is what we would expect:

DRON:00076018 simethicone 180 MG Oral Capsule [Phazyme] DRON:00042305 352091

The branded_drug (DRON:00076018) is a subClassOf the clinical_drug (DRON:00042305). So it would appear that the NDC was first associated with the clinical drug, then later associated with the branded drug.

Because the NDC did not exist yet in the ndc_branded_drug.tsv template, it got added (duplicated) and associated with the branded drug. But the process did not recognize the duplication, so the NDC remained in the ndc_clinical_drug.tsv template, with its association to the clinical drug.

[hoganwr]

There is a somewhat larger "philosophical" issue at play here: do we want to track all the history of NDCs and the SCDs and SBDs with which they have been associated over time?

The key challenge with a "yes" answer is the considerable amount of effort it would entail.

A more immediate, less-intensive approach: update NDC associations in DrOn as they change in RxNorm. DrOn will therefore mostly be consistent with the most recent association an NDC has/had.

But I'd like to add one caveat: once an NDC is associated with an SBD, I don't think it makes sense to revert it to an SCD: it should instead stay with the SBD (every NDC has a brand implicit in it, after all, and technically, they should all be associated with SBDs). We would therefore only update SCD -> SBD changes.

What this means:

1. For the current problems, we obsolete the DrOn IRIs in the SCD table, with replacement of IRIs in the SBD table (modulo identical NDCs)
2. We update the monthly update script.

• First, add all NDCs not in DrOn at all.
• Then look for NDCs associated with SBDs, where the NDCs already exist in ndc_clinical_drug. Delete those NDCs from ndc_clinical_drug, and add them to ndc_branded_drug with the new RxCui association.
• The reason we can't add the pre-existing NDCs to ndc_branded_drug directly in the first step is that that process mints new IRIs. We want to keep the IRIs the classes have in ndc_branded_drug.

[hoganwr]

query to generate new entries in templates/obsolete.tsv

SELECT b.curie, 'obsolete ' || n.ndc, 'owl:Class', n.curie FROM ndc_clinical_drug n, ndc_branded_drug b WHERE n.ndc = b.ndc;

Query to update replace DrOn IRIs in ndc_branded_drug with IRIs from ndc_clinical_drug where NDCs are equal:

UPDATE ndc_branded_drug SET curie = ndc_clinical_drug.curie FROM ndc_clinical_drug WHERE ndc_branded_drug.ndc = ndc_clinical_drug.ndc;

Query to delete NDCs from ndc_clinical_drug that are already in ndc_branded_drug:
delete from ndc_clinical_drug where ndc in (select ndc from ndc_branded_drug);

How it went interactively in sqlite3:
sqlite> select count(*) from ndc_clinical_drug; count(*) 582643 sqlite> delete from ndc_clinical_drug where ndc in (select ndc from ndc_branded_drug); sqlite> select count(*) from ndc_clinical_drug; count(*) 582603

[hoganwr]

These issues have all been fixed. Closing.