For years, anyone building healthcare software, conducting pharmacy research, or analyzing medication data has faced an unfair choice. On one side: free government data that requires months of learning obscure formats, parsing XML from nested zip files, and becoming an expert in RxNorm’s abstract table structures. On the other: enterprise drug databases with six-figure price tags and vendor lock-in.

We built the CodeRx Drug Database because there should be a better choice.

The problem we’re solving

Five years ago, when we needed drug data for a healthcare software development project, we discovered what thousands of developers and analysts already knew: there’s no middle ground. You either spend months learning to work with raw data sources like RxNorm, FDA, and DailyMed, or you sign a contract that costs more than most early-stage startups can afford.

We chose to build our own data pipeline. We learned the hard way that “open” doesn’t mean “easy.” We figured out the best way to parse DailyMed’s 50,000+ XML files buried in nested zip files. We became fluent in RxNorm’s SABs and TTYs. We normalized NDC formats across five different data sources. We automated and enhanced weekly pricing updates from CMS NADAC data.

When our project was complete, we realized something: we had built exactly what the healthcare community needed. Not another GitHub repo that tackles one narrow problem. Not another stagnant open-source project. But a complete, supported, modern drug data product that anyone could use.

Why the CodeRx Drug Database is different

Better than raw open data

You don’t need to become a government data format expert. We’ve done that part.

Instead of spending weeks learning RxNorm’s abstract RXNCONSO, RXNREL, and RXNSAT tables, you get purpose-built data marts like drugs, ingredients , packages, and classes that actually make sense to pharmacists and developers.

No more parsing XML from DailyMed zip files. No more Excel formulas to normalize NDC formats every time you download fresh data from FDA and CMS. No more writing complex SQL joins just to answer basic questions about drug products.

Setup in minutes, not months. Browse our open documentation to explore the exact data structure, or subscribe to weekly updates for production use. Either way, you’re working with clean, integrated data from day one.

Documentation built for humans. Our web-hosted, searchable documentation includes specific use cases with SQL examples. No lengthy PDFs to search through. No generic technical documentation written for data scientists. Just clear explanations focused on pharmacy applications.

More affordable than proprietary databases

Costs 90% less than alternatives. That’s not a typo.

For early-stage startups, researchers, and data analysts, enterprise drug databases are simply out of reach. The CodeRx Drug Database costs at least 90% less while providing the core data you actually need.

Modern integration. No complex vendor contracting. No legacy data formats. Just straightforward CSV (or Parquet!) files you can load into any modern database or analytics tool.

Analytics-ready data. Proprietary databases provide terminology that requires transformation for common use cases. The CodeRx Drug Database gives you semantic drug concepts organized around real-world pharmacy questions - identifying drug classes, calculating days’ supply, grouping therapeutics - without writing complex transformations every time.

Community-driven innovation. When you have an idea for a new data mart or feature, we can build it together. No waiting for a vendor’s product team to add it to their multi-year roadmap.

What you get

The CodeRx Drug Database includes pre-built data marts that solve real pharmacy problems:

• Packages: NDC-to-drug mappings with brand vs generic indicator and pricing data

• Drugs: Unified view of brand and clinical products with dose forms, ingredients, and brand relationships

• Classes: Multiple classification systems to aggregate drugs by therapeutic class or indication

• Ingredients: Detailed ingredient strength information, including precise ingredient classifications

• Excipients: Inactive ingredient tracking with special flags for preservatives, dyes, and gluten

• Synonyms: Multi-source synonym aggregation for improved search and matching

• Plus analytics-ready data marts for common pharmacy use cases

All data is integrated from multiple authoritative sources: FDA - NDC Directory, NLM - RxNorm and DailyMed, CMS - NADAC, and more.

Who this is for

Early-stage health tech startups that need professional drug data without enterprise pricing

Pharmacy researchers and analysts who want to focus on insights, not data wrangling

Healthcare developers building medication-related features who need reliable, well-structured drug data

Anyone who’s ever thought “there has to be a better way than learning RxNorm from scratch”

The best of all worlds

You shouldn’t have to choose between spending months learning government data formats and spending six figures on enterprise software. You shouldn’t have to reinvent the wheel just to get basic drug information into your application.

The CodeRx Drug Database is the solution we wish existed five years ago. It’s affordable, modern, easy to use, and built by pharmacists who code and developers who care about healthcare.

Ready to stop wrestling with drug data and start building?

Get Started

Get started now

Want to see what we’ve built? All our documentation is available right now on our website:

Explore the Docs →

Need weekly updates? Let’s talk about a subscription that makes sense for your organization.

Subscription Options →

Have questions? Shoot us a message or jump into the CodeRx Slack and we will help answer them.

Contact Us →

The CodeRx Drug Database is maintained by CodeRx, LLC. Our open-source data pipeline powers everything we do, and our commitment to the healthcare community means we’re always looking for ways to make drug data more accessible. View our code on GitHub or learn more at coderx.io.

Subscribe now

This release is just a refresh of the existing data marts. However, based on my aforementioned foray into the land of vibe coding, I’ve started eating my own dogfood as the saying goes.

In plain English, what I’m trying to say is that for the past year or more, I’ve been putting easy(ish)-to-use drug data out there in the hopes that people would use it to build cool and useful things… but I’ve kind of intentionally avoided building those things myself so I wouldn’t get distracted from actually producing the data.

With the advent of tools like LLMs-in-an-IDE (I use Cursor), it’s pretty easy to spin up entire apps on a whim without getting bogged down in the minutia of details. This let me easily use my own data to build an app and I found a few things that could definitely be improved or changed about the format of the data. So - like a dog food company eating its own dog food before selling it to customers (dogfooding - see above), I’m experimenting with being a data company using my own data.

So while not much as changed with the actual data available in the Compendium the past quarter or two, a lot of thought has been put into how the Compendium is used and how it could be better. Expect a Compendium v0.0.2 in the new year and some ruminations on why I think a slightly different organization will make sense to a lot of people. Possibly even some API access… we shall see.

Sign up for free access to the current Compendium at the link below. If you already have access, the link did not change, but the files are updated.

Access Compendium data marts

If you have been using any CodeRx products over the past year (SageRx, ParseRx, Compendium, etc), I would love to hear from you. How are you using it? What have you modified for your own purposes? What are we missing? What could we do better? If you have 5 minutes to spare, please leave some feedback at the link below.

CodeRx feedback survey

Thanks so much for following along this year. Hope you have a happy holiday season and a great new year!

Subscribe now

The answer revealed a fundamental challenge with NADAC when used for this purpose. To directly compare prices against NADAC, you need to use the exact same total pack size units that NADAC uses for its calculation. Unfortunately, that pack size data is locked behind the paywall and license of a commercial drug information database.

The pack size problem

Every meaningful drug price comparison requires one critical piece of information: how many units are in each package. Normalizing costs to the lowest unit makes it easy to know the average cost of a drug regardless of whether it comes in a 100 count or 500 count or 1000 count bottle. When a pharmacy pays $100 for a bottle of 100 tablets, calculating the per-unit cost is straightforward. But what about an inhaler? A vial of injectable medication? A colonoscopy prep kit? A unit-dose package?

This is where things get complicated. The National Council for Prescription Drug Programs (NCPDP) has established standards for pharmaceutical billing units—”EA” for each, “ML” for milliliters, and “GM” for grams. These standards provide the framework, but implementing them correctly requires navigating a 30+ page specification document filled with exceptions, edge cases, and industry-specific nuances.

Most organizations don’t implement these standards themselves. Instead, they rely on commercial drug databases like FDB (First Databank) or Medispan, which have spent decades building comprehensive implementations of these standards. Pharmacies access these databases through their pharmacy management systems, but the pack size data typically isn’t available in a format that allows for easy price comparison analysis.

The NADAC use case

NADAC itself relies on this commercial database approach. When Myers and Stauffer (the consulting firm that collects NADAC data) receives pricing submissions from pharmacies, they use commercial databases to determine pack sizes and calculate per-unit costs. A pharmacy submits an NDC and the total price paid; the database lookup provides the pack size; division gives the per-unit cost.

This system works well for NADAC’s primary purpose: creating reliable, standardized pricing benchmarks. But it creates a practical challenge for the pharmacies that contribute the underlying data. While most pharmacies do use commercial drug databases as part of their pharmacy management systems, this data typically isn’t accessible in a format that allows easy price comparison analysis. The pack size information exists somewhere in their software, but pharmacists can’t readily extract it to perform their own NADAC comparisons.

The situation is frustrating: pharmacies provide the raw data that creates NADAC, but they still can’t easily use NADAC for competitive analysis. If I wanted to help the pharmacy manager, I would have to either pay for a license to a commercial drug database (which costs thousands of dollars per year) or somehow figure out a way to help them extract pack size data from their pharmacy management system and create a report that combines that data with the weekly updates from NADAC.

Building better infrastructure

Rather than working around these limitations, we should focus on building public infrastructure that serves the entire industry. Here are some potential approaches:

Enhanced transparency in existing systems: NADAC could include both “per unit” and “per package” pricing in their data exports. This simple addition would allow users to directly compare prices they are paying per package without changing NADAC’s core methodology. Unfortunately, this type of change would require governance and approval and could potentially take years.

Public pack size reference: An open database that correctly implements NCPDP standards, maintained collaboratively by industry participants and freely accessible to all market participants. This is an area where CodeRx and the surrounding community of pharmacists and pharmacy leaders could help. We’ve already started working toward this goal.

Industry collaboration: Working groups focused on standardizing pack size calculations and making them publicly available, similar to how other industries manage reference data. FDA is the first organization that comes to mind to bear responsibility for including total pack size in its NDC Directory. It has all of the pieces of the puzzle to calculate total pack size based on NCPDP standards and could save everyone else a lot of headache by just doing it centrally. Or - perhaps NCPDP could take ownership of publishing NDC to pack size mapping data instead of just publishing guidelines on the topic.

The path forward

The goal isn’t to replace existing commercial databases—they provide valuable services beyond basic pack size calculations. Instead, we should reduce the industry’s dependence on proprietary databases for fundamental product information that enables basic price comparisons.

This would benefit everyone: pharmacies could make more informed purchasing decisions, researchers could conduct better analyses, policy makers could better understand market dynamics, and smaller market participants could compete more effectively.

NADAC has succeeded in bringing transparency to pharmaceutical pricing. Now we need to build the public data infrastructure that allows everyone to take full advantage of that transparency. This is not a problem with NADAC—it’s an opportunity to build something better for the entire industry.

What is your experience with pharmaceutical pack size calculations and pricing analysis? Have you run into similar challenges with accessing basic product reference data? I’d love to hear from others navigating these issues.

In case you don’t know, the Compendium is what we call our curated collection of finished data marts generated using SageRx. Essentially, they’re a dozen or so CSV files in a Google Drive folder that we update for free every quarter (plus, we archive and share the historical versions).

Click the button below for free access to our data marts. Quarterly updates are provided for free - for more frequent (weekly) updates, please contact us.

Access data marts

What’s new this quarter?

Drug pricing

We’ve always included NADAC as a data source, but have never published the transformed data as a data mart. We have contributed several enhancements to the raw NADAC data published weekly on the CMS website.

Continue reading below to learn about some of those enhancements.

Check out the NADAC data marts

De-duplicating row count while preserving the most useful data points

Every week that a drug is included in the current NADAC release, another row is added to the data with a new “as of date”, but everything else usually remaining the same - even the price in many cases. For people that only care about price changes over time, this creates a massive amount of duplicate rows that need to be cleaned up before working with the data.

We create an easy-to-use, lightweight history of price changes for every NDC in NADAC. We also join in RxNorm RXCUIs and normalize related drug names of NDCs to make it possible to aggregate NADAC data by drug product or ingredient using an open, standardized drug terminology.

Preserving recent prices for drugs that are not in the current week’s release

While rows where the “as of date” from the most recent release are the most relevant for reimbursement activities, prices from a week or even a month ago might still be relevant for other uses.

We provide an easy-to-use view of the data that cleans this up and makes it easy to use only prices from the most recent release, from within the past 30 days, from within the past 90 days, etc.

Handling what NADAC curators call “overwrites”

Overwrites are when the “as of date” moves forward, but the “effective date” goes backward. In practice, these rows / prices are erased from ever being used for reimbursement, yet they remain in the data as artifacts you need to deal with.

We flag these rows and remove them to preserve a logical progression of prices over time.

Drug indications

RxClass is an incredible resource for drug classifications and hierarchies. However, it is only accessible via an API or an web-based graphical user interface (GUI). Or I guess RxNav-in-a-box, which is just a version of the web GUI run locally.

Some of the most useful relationships in RxClass are those that provide some insight into which drugs may treat (or prevent) which conditions. These come from a variety of sources and map to a variety of standard disease ontologies - effectively mapping standardized medication terminology to standardized disease terminology with relationships like may_treat, may_prevent, and ci_with (contraindicated with).

We make this useful drug indication mapping easy to use by providing it in a flat file format. We map from products to diseases via ingredient-level mappings provided by RxClass. We also make it easy to map from MeSH terminology for diseases to other standardized disease terminology, thereby extending the utility provided by RxClass.

Check out the drug interactions data mart

OTC label images

In previous releases, we have used our DailyMed transformation process to extract a mapping of prescription NDCs to label images, but this release also includes the equivalent for OTC NDCs to label images. There doesn’t seem to be as good of coverage for OTCs, and likely there are improvements that could be made to the code to handle OTC labels better - but this is just an effort to share what we have for now.

Check out the label image data mart

Access data marts

Why do we do this?

We do this for several reasons.

Building in public is the best way to build

We want you to be able to actually put eyes / hands on the data we are working hard to transform into something greater than the sum of its parts. And we don’t expect you to run SageRx yourselves to do so… anymore… We think the more people exploring the data, the better. If we spent months developing a data mart and nobody ever looked at it, does it even matter?

The dream of the 80s is alive in healthcare

Why do we release these data marts as CSVs?

Flat files are gonna be around as long as faxing and paging are critical to parts of healthcare. They’re not inherently bad - they’re just not very flashy. However, they’re extremely versatile. Flat files (comma-separated value files, or CSVs) have the dual benefit of being easy for non-technical people to explore, and also trivial for technical people to ingest into data lakes or other data tools.

There are certainly more… modern ways for us to share this data with people. We have considered a cloud database or shared data workspace or even offering an API. We still may do some of these things in the near future. But for the time being, flat file are an easy common denominator that works for us and for most people we’ve asked.

No money, no margin

We put a lot of effort into developing these data marts, and it takes some effort to put together these quarterly releases. We think there is legitimate value for people to use these data marts for free if they are developing something new and need data to get started. Or perhaps for people doing historical research that doesn’t require frequent updates.

In full transparency, this also serves as a smooth on-ramp for someone who values the data but needs more frequent (weekly) updates to inquire about our services. For clients, we deliver these updates to an AWS s3 bucket on a weekly basis, provide data quality checks and ongoing support, and work closely to prioritize developing additional data marts that align with our roadmap.

If any of that sounds interesting to you, please reach out and see how we can work together. Supporting CodeRx helps us develop more data marts that can help others develop innovative and useful things.

Contact us

We hope you find these free quarterly data marts useful, as well as the open-source development we have done to produce them. If you have any questions, are interested in more frequent (weekly) updates and support, or are looking for consulting help, please contact us.

Subscribe now

We’ve just shipped a small but mighty update to our website that makes it easier than ever to understand what’s inside our data marts.

You can now browse the first 10,000 rows of every data mart through a slick new interactive table powered by Observable. No signup, no credentials, no SQL required — just click and explore. Whether you're a developer sizing up our schema or a data analyst trying to answer “Is this what I think it is?”, this feature gives you a quick, intuitive feel for how our data is structured and what it contains.

🔗 You’ll find the interactive tables linked right from each data mart page.

This is part of our broader mission to make drug data more accessible, interoperable, and inspectable. We believe good data tools should feel like good software: fast, transparent, and easy to test-drive.

Coming soon: Q2 2025 data mart updates

We're putting the final touches on our Q2 2025 refresh across all data marts — new data, new insights, and a few structural improvements. More on that soon.

Stay tuned 👀

View data marts

Plus, thanks to some great work by Kwasi Agyeman, our ATC codes to products data mart has captured over 300 extra rows from the API.

We will continue to update these data marts for free on a quarterly basis. If you find them useful but need more frequent updates, please contact us and we will work with you to deliver what you need. If you want to leave us general feedback or request additional data marts, also feel free to let us know - we’d love to hear from you!

Product synonyms

If you’re training a ML model to recognize fuzzy matches for names of drug products, this data mart could be useful. It contains synonyms of drug products from many different sources and maps each synonym to a drug product ID (RxNorm product-level RXCUI).

Product synonyms mart

This data mart was originally created for a recent Awell Health hackathon with the goal of allowing patients to take a picture of their prescription and OTC bottles / packages and have AI identify the product either using the name or NDC and map it to a product-level RxNorm RXCUI. I was personally shocked and impressed at how well the demo worked. We’re now making this data available to everyone.

Here’s a link to the actual original data used for the hackathon in 2024.

GTINs

This data mart is just an estimate of what a calculated GTIN (GTIN-14, specifically) should be be for any given NDC. It has not been validated and could be wrong for many reasons. Use at your own discretion but please provide feedback for ways it could be improved.

NDCs to GTINs mart

GTINs are important for track and trace (DSCSA) as well as any sort of barcode-related medication workflows (dispensing cabinets, for instance). This data represents a “best-guess” approach (based on this guide) for what a calculated GTIN should be for a given NDC, but manufacturers have some laterality in terms of using different digits.

Talk to us

More documentation about most of these data marts can be found on our website. If you have ideas for other data marts or if you find this data useful and would like updated data delivered on a weekly basis, please contact us and we will work with you. Please let us know if you have any questions or feedback and thanks for taking the time to read this!

Subscribe now

What is a drug product?

The idea or concept of a drug product requires three things:

1. Ingredient - i.e. metformin

2. Strength - i.e. 500 mg

3. Dose form - i.e. tablet

These are the three essential pieces of information required to hold the idea in your head about a drug product that could be prescribed by a provider or taken by a patient. Lose any one of these data points and you can no longer identify a drug product. A patient can’t take “metformin 500 mg” or “500 mg tablet” - you need all three parts (“metformin 500 mg tablet”).

Single-ingredient products

The simplest example of a drug product would probably be - you guessed it - metformin 500 mg oral tablet. You can see we have an ingredient (metformin), a strength (500 mg), and a dose form (oral tablet). But as soon as you get out of the realm of simple examples, it can get complicated pretty quickly - as we will see next.

Multiple-ingredient products

For a slightly more complex example, it’s very common for a drug product to have multiple ingredients with different strengths. One example of this is sitagliptin 50 mg / metformin 500 mg oral tablet. This is a single drug product with multiple different ingredients - which themselves each have different strengths.

Branded products

Another wrinkle is that we need to also account for branded products which can have financial and regulatory / operational implications, but are otherwise the same “clinical drug” behind all that branding. Below is an example of Janumet, which is a branded equivalent to the now generically available sitagliptin / metformin drug product above.

Packs and kits

We haven’t even scratched the surface of the most complex drug products which are known as “packs” or “kits”. These are drug products packaged together with OTHER drug products. These are things like oral contraceptives (birth control) where the package you pick up at the pharmacy actually has two separate drug products combined together in a blister pack. Perhaps 21 tablets of one drug product and 7 tablets of another to make up a full 28 day supply.

Packs are different from multiple-ingredient drug products because within the outer saleable package, not all the pills have the same medication in them. Conversely, even though a bottle of a multiple-ingredient drug product has multiple ingredients, all of the pills in the bottle contain the same ingredients.

A kit can be even more complex with perhaps several vials of different drug products combined in one package along with alcohol wipes and sterile water and perhaps other things. We’re not going to get into those complex examples here, but dealing with kits is important for any complete understanding of drug products.

From products to packaging

This is all fine and good, but a patient can’t ingest the idea or concept of a drug product. They have to take an actual, physical medication. The missing pieces needed to go from “product” to “package” are drug manufacturer (who made this drug product) and drug packaging (how this drug product is available for pharmacies to purchase). This is where the National Drug Code (NDC) comes into play.

We aren’t going to go in depth into NDCs or drug packaging considerations here because it’s enough of a topic for an entirely separate article. However, in all the examples above, you will notice that each label has an NDC on the packaging. Below is an example from the first drug product where the NDC is 57664-397-51.

The NDC is a drug identifier on packaging for drug products. By itself, it is far too specific and not nearly standardized enough to be useful for most analytical purposes. However, mapping the NDC to a drug terminology unlocks a lot of useful things you could do using drug data. For instance, you could aggregate all drug claims by drug product or even drug class. The first step of doing this is mapping NDCs to a drug product concept like we reviewed above.

CodeRx drug product data

We’ve been developing CodeRx data marts to make it easier to work with drug products. The main data mart relevant to this particular article is aptly named “products”. We can easily find all generic single-ingredient metformin products with a dose form of tablet. Note that the identifier for our product in question (metformin 500 mg tablet) is 861007. We leverage open standard identifiers from public data sources such as RxNorm wherever possible.

Knowing the product identifier for this specific drug product allows us to use a second data mart to find all NDCs that map to that drug product. We found our example NDC (57664-397-51 - or 57664039751 in NDC11 format) in the list as shown below. The fda_description column hints at the fact that we not only look for NDCs in RxNorm - we also pull NDCs directly from the FDA.

In practice, we would more likely start with an NDC and map it to the associated drug product identifier, but given this article is all about drug products, it made more sense to do it this way.

We invite you to explore our drug product data further. See if you can do a similar exercise as above with the multiple-ingredient drug product or one of the pack examples from the beginning. All CodeRx data marts are available for free and updated quarterly. If you would like more frequent (weekly) updates, please contact us.

View all data marts

Drug products are only one foundational aspect of working with drug data. Subscribe and read along as we continue to write about our strange obsession with drug data and how we are making it easier to work with.

Subscribe now

To me, that sounded kind of like if you needed to know how much a bottle of Tylenol cost, and your only option was to pay a significant amount of money to a company for a license to a product that provides pricing information. Something just felt bizarre or wrong about the need for people to pay to know what drugs cost.

This may be more of an issue with drug pricing overall (which I will definitely not get into here), but it just left a weird taste in my mouth. In general, I think I took offense to the notion that there should be a price barrier at all for people to have easy access to important information.

More recently, I led a team to develop a piece of software that depended on drug data. When we looked around for options to source that drug data, none of them met all three of our criteria:

1. easy to use off the shelf,

2. well trusted and supported by a community, and

3. within our budget.

Before we could even begin focusing on the potentially innovative part of our project, we had to build a data pipeline from scratch just to create a local database that contained the data we needed.

Open source exploration

Of course we searched for open-source solutions to our problems as many people in our position would try. Most of the free options were open-source repositories that might tackle one specific problem in a very narrow or brittle way. Many had become stagnant — with years since the last commit to the repo. Few of them made it easy for developers to contribute to the project by extending the open-source framework with their own additional use case or feature. None of them seemed to have a community of people that agree it’s a good option as a general source of drug information.

Another thing I was personally stubborn about is that any solution had to be in a modern data language like Python or heavily SQL-centric. Selfishly, these languages are my comfort zone; but I also knew that they are languages that a lot of people know. We may have ruled some useful options out because of this self-imposed limitation.

Commercial options

Commercial options weren’t even a consideration because their price tags were in the tens of thousands of dollars per year. Thinking about this from the perspective of an early stage health tech startup or a pharmacy data analyst or researcher without considerable funding, it feels like a barrier to entry that does not need to be so high.

Pretty much all of what we needed for our project was free for the taking from government-provided data sources — if we could figure out how to work with them. I knew that FDA’s NDC Directory and NLM’s RxNorm were the two main sources, but didn’t have a ton of experience working with either of them. So it seemed like our options were paying a lot of money we didn’t have… or starting from scratch.

Starting from scratch

I assume drug data is not unique in the fact that it’s a source of data provided freely by the government that is well-intentioned but not easy to work with. We had to do a decent amount of background research just to figure out which data points come from which data sources. Once we had a grasp on that, we had to figure out how to work with the wide variety of file types and formats.

For us, the first milestone was learning how to re-organize RxNorm from its three oddly named tables into a shape that made common sense to our clinical minds that were used to working with normalized data in relational databases. It meant doing a lot of SQL query writing just to get started, as well as becoming an expert in SABs and TTYs and relationships and attributes. If you’ve worked with RxNorm before, you may have been through all this yourself.

I want to be clear that I’m not trying to say these tasks are impossible to do, or that we are the first to do them. It just seems just odd that nobody has already packaged this up in a way that most people agree is the right way to essentially reconstitute RxNorm into a more accessible format. We did this for ourselves for our specific software development project, and I guess this is where most work with RxNorm probably ends. In other words, I believe people re-invent the wheel over and over again just to get RxNorm up and working they way they want it to work for a very specific use case. And there’s no guarantee that my interpretation of RxNorm is the same as yours.

The best of all worlds

It seemed like there should be a solution that was cheaper than a commercial database, easier to get started with than using raw data, and had an open source community supporting it. After completing our project for the ONC contest (and winning first prize - no big deal), we set out to build such a thing. Three years later, I think we’re getting close. We’ve built a stable data pipeline to not only pull raw data from all these important sources, but also made it easy to transform the data into useful tables using modern data engineering practices. This is all available right now 100% open source on GitHub.

One thing we’ve struggled with is making our data stupidly easy for people to explore and use. So without overthinking it any longer, I just loaded all of our currently available data marts to a Google Drive folder. I will try to update this on a quarterly basis for free. If you would benefit from more frequent updates or have questions about this, please contact us.

Explore the data

Some useful data marts to call out:

• products: all RxNorm products with product-level details such as a brand vs generic flag, ingredients, dose forms, and normalized description

• all_ndc_descriptions: NDCs consolidated from five different open sources of drug data, mapped to RxNorm product codes and any available RxNorm or FDA descriptions of the NDC

• ndcs_to_label_images: a mapping of DailyMed label images at the NDC level —this is something you can’t get from any other open source

• ndc_associations: inner-outer NDC packaging associations parsed from the FDA NDC Directory

• pack_size: my first attempt at parsing total pack size information from the FDA NDC Directory, including innermost and outermost packaging units

View all data marts

More documentation about most of these data marts can be found on our website. If you have ideas for other data marts or if you find this data useful and would like updated data delivered on a weekly basis, please contact us and we will work with you. Please let us know if you have any questions and thanks for taking the time to read this!

Subscribe now

In this article, we explore the value of open drug data and how it helps answer critical questions with drug information.

What is this drug product?

One of the core strengths of open drug data is its ability to identify and classify drug products accurately. A prime example of this is RxNorm, an open standard maintained by the National Library of Medicine. RxNorm’s structured data includes:

• SCD (Semantic Clinical Drug): Clinical drug descriptions, such as "Atorvastatin 10 mg Oral Tablet."

• SBD (Semantic Branded Drug): Branded drug representations, like "Lipitor 10 mg Oral Tablet."

• GPCK / BPCK (Generic Pack / Branded Pack): Packaged drug groupings, such as a "14-count blister pack of Omeprazole."

These term types make it easy to understand what a drug is, its components, and how it might be grouped or packaged. The use of standardized terminology in open drug data ensures that users are not locked into proprietary formats, allowing for easier integration across systems.

How is it available?

Another question open drug data addresses is: how is this drug available to purchase? Is it available as an injectable product? Does it come as a 100-count bottle? A 1000-count bottle?

The NDC (National Drug Code) directory is a robust source of information for current drug products on the market. For those who need historical insights — such as discontinued drugs — RxNorm Historical via RxClass provides a more complete picture.

Additionally, details like pack size — whether a drug is sold in a 1000-count bottle versus a 100-count bottle — can have significant implications for purchasing and cost analysis. Open drug data offers many of these details, although it may require some effort to extract and utilize them fully.

What does it cost?

Pricing is a critical question in the world of drug data, and open resources provide important tools for understanding this aspect. While open drug data does not include AWP (Average Wholesale Price) — a commonly cited but highly criticized figure for its lack of transparency — it does offer more reliable alternatives, such as:

• NADAC (National Average Drug Acquisition Cost): Reflects the average price paid by retail pharmacies for prescription drugs.

• ASP (Average Sales Price): Used in Medicare Part B reimbursement, showing real-world prices rather than inflated estimates.

These pricing benchmarks are free from the "shell game" often associated with intentionally obfuscated pricing models, giving users a clearer and more trustworthy view of some aspects of drug costs.

Is it equivalent to anything else?

Determining whether a drug product is equivalent to another is essential in clinical and regulatory settings. Open drug data excels in this area with resources such as:

• Orange Book: Published by the FDA, this resource lists therapeutic equivalence evaluations, enabling users to identify generic alternatives for branded drugs.

• RxNorm product codes: These standardized identifiers make it easy to link drugs across systems, ensuring consistency and clarity.

• FDA pack size information: Details about packaging and dose form help ensure accurate comparisons, such as whether two products with the same active ingredient are sold in equivalent pack sizes.

These tools empower users to assess equivalencies effectively without relying on proprietary systems.

Closing the gaps

While open drug data provides a strong foundation, there are areas where it can fall short. This is where innovative solutions step in to bridge the gaps. By enhancing open data with features like:

• Improved accessibility: Making the data easier to access and use for all stakeholders.

• Interoperability: Ensuring seamless integration across systems and platforms.

• Innovation potential: Leveraging open data to power new tools and insights that proprietary systems can’t offer.

These enhancements unlock the full value of open drug data, empowering organizations to reduce dependency on costly, restrictive commercial databases while driving progress in the industry.

Conclusion

Open drug data has tremendous potential to transform how we access and use drug information. By building on its strengths and addressing its gaps, we can create a future where drug data is more accessible, interoperable, and innovative.

If you’re looking for ways to maximize the power of open drug data, let’s connect — I’d love to help you take the next step!

Contact us

Subscribe now

If you wanted to compare the price of pop between package sizes, the best way would be to convert each package to the lowest common unit - let’s say ounces. So the 12 pack has 144 total ounces, the 24 pack has 288 total ounces, the 20 oz bottle has 20 ounces (duh), and the 2 liter bottle has (consults Google…) 67.628 ounces. Our oral solid product example is pretty straightforward aside from converting the specific “tablet” units to a more generic “eaches” unit - the 100 tablet bottle is 100 “eaches”, the 500 tablet bottle is 500 “eaches”, etc. For our vaccine example, the total package size of a 10 pack of 5 mL multi-dose vials is 50 mL, and the total package size of a 10 pack of 0.5 mL pre-filled syringes is 5 mL.

I get it - so what’s the problem?

The problem is that this drug packaging information is not easily available in machine-readable format. There’s not a simple source of information that provides the total amount of a drug product contained within a drug package. The closest you can easily get is a semi-structured, free-text description of the packaging from FDA’s NDC Directory. Example below:

1 VIAL, MULTI-DOSE in 1 CARTON (70461-554-10) / 5 mL in 1 VIAL, MULTI-DOSE (70461-554-11)

Ideally, we would be able to break this up into discrete columns of data instead of one blob of text. We could have some sort of column for the outer package description (CARTON), a column for the inner package description (VIAL, MULTI-DOSE) and then maybe columns for the total quantity of inner packages (1), the total amount in each inner package (5), and the dose unit of the inner package (ML).

The problem that makes this a lot more complicated is that drug packaging can get pretty complex pretty quickly. Another (more complex) example below:

1 KIT in 1 CARTON (80425-0348-1) * .9 mL in 1 PACKET (67777-419-02) * 10 mL in 1 VIAL, PLASTIC (0409-4887-17) * 10 mL in 1 VIAL, SINGLE-DOSE (0409-4888-02) * 5 mL in 1 POUCH * 10 mL in 1 VIAL, SINGLE-DOSE (0409-1159-18) * 10 VIAL, SINGLE-DOSE in 1 CARTON (55150-162-05) / 5 mL in 1 VIAL, SINGLE-DOSE * 25 VIAL, SINGLE-DOSE in 1 CARTON (70121-1573-5) / 1 mL in 1 VIAL, SINGLE-DOSE

Additionally, total package size for billing purposes needs to align with the NCPCP Billing Unit Standard Implementation Guide - you can see examples in section 5 (page 11) of this old version of the guide. This is a bridge we will have to cross eventually, but it’s hard enough to just extract fundamental packaging information from drug products without even worrying about standards like this.

Why would someone need drug package size information?

Off the top of my head, I can think of at least 3 good examples where I’ve come across a need for discrete drug packaging information. These are all solved by commercial drug information databases, but not easy to solve with freely available drug data.

Drug pricing

NADAC provides a “per unit” (EA / GM / ML - same as the NCPCP Billing Unit Standard) price that most pharmacies pay for a given drug product. If you wanted to know how much an entire package costs, you need to know how many total EA / GM / ML are contained within the package - again following the NCPDP BUS implementation conventions.

Drug equivalency

If you’re a pharmacy browsing a catalog for vaccines to purchase, and only want to see vaccines that come in a multi-pack, whatever purchasing platform you’re using needs to have discrete data about their drug products that includes package quantity (i.e. 10 vials in a package vs 1 vial in a package). Entire proprietary coding systems (GPPC, for instance) exist just to compare equivalent drug products by package size or package type.

Basic drug information

If you’re building a system that displays package-level (or NDC-level) information about drugs, you will need to include pack size info in the description of the drug name — otherwise drug products with multiple pack sizes will be indistinguishable by name alone. Also, you will likely need a column that lists the total package size or some sort of sortable or filterable number.

Our goal is to solve this problem with our next data mart

I’ve seen this challenge as a major milestone of my work for as long as CodeRx has been around. I’ve always been aware of where to start, but knew that if I dipped my toe in, I would have to be prepared to dive in completely to really solve the problem. I think I’m ready to jump in head first now.

The other important thing this packaging data has the potential to unlock is a mapping of inner to outer NDCs. In the first example from the previous section, the outer package (the CARTON) has an NDC of 70461-554-10 while the inner package (the VIAL, MULTI-DOSE) has an NDC of 70461-554-11. Pharmacies purchase the OUTER NDC (the carton), but sometimes want to know details about the INNER NDC (the vial) - such as what the label looks like or how much drug it contains.

Our existing data marts are available for download as CSV files via AWS s3 buckets — this one would work the same way. Alternatively, you can use SageRx (our open-source drug data pipeline platform) to generate them for yourself.

I’m aiming to have something to show for this data mart in the next month or two.

• If you would like to be notified when this data mart becomes available, please join our waitlist and we’ll reach out as soon as it’s ready.

• If you want to help develop this, please join our Slack and introduce yourself.

• If you had other questions or feedback, please don’t hesitate to contact us.

Join data mart waitlist

I’ll be writing about our progress toward building this data mart, along with some actual sample data. Please subscribe if you haven’t already, and I would greatly appreciate if you would share this article with someone you think would find it useful. Thanks!

Subscribe now

1. what SageRx is,

2. why we built it,

3. and how it works.

If you’ve been following along, we just wanted to take a moment to try and articulate the vision of what we think open drug data has the potential to do when it’s made easily accessible to many people.

Long story short, our goal in the coming months is to release a stable, hosted version of the SageRx database, along with an open source visualization / BI toolkit that will make it easy to work with the data. More on the details of our roadmap in future posts - for now, simply enjoy our vision of what it could look like.

Overall product vision

SageRx came out of a need for lowering the barrier to entry for working with drug data. Our platform will provide easy access to clean and trustworthy drug data (expanding into additional data over time). 

Users will be able to:

• develop custom datasets

• visualize data

• share data via charts and dashboards

• integrate data into existing applications

SageRx plans to be a hub for all things drug data. By creating a place to easily find, use, and share drug data, it will spark new ideas and teamwork in the healthtech community.

Mission statement

Our mission is to make drug data more accessible for everyone.

Target audience

We want to use SageRx to help anyone who needs drug data, even if they're not tech experts. If you're someone who struggles to find the needed data or spends a lot of time manipulating drug data, our platform aims to make it easier for you to use this data in your work.

• Researchers

• Startups

• Data scientists

• Maybe you?

Value proposition

SageRx aims to give you all the data you need, neatly organized and ready to use. With our help, you can spend less time searching and processing data, and more time making a difference with the information you find. By using our data either via visualizations or download (eventually through an API), you can unlock value without worrying about the underlying infrastructure.

Flywheel

Imagine a big wheel that's hard to start spinning but gets faster and faster once it's moving. That's what we want for SageRx. As more people use our platform and find useful information, they'll share their creations with others. That means even more people will use SageRx, creating a cycle of learning and discovery that never stops.

How the flywheel works:

Key functionality

To realize our vision, SageRx will need to focus on a few essential features:

Components

• Hosted version of SageRx database in the cloud (likely BigQuery), managed by the SageRx team

• Authorization system, ensuring limited access and thus controlling costs

• Data changelog, for easier explanation to users about data changes over time

• Data documentation, allowing users to understand what is available and in which tables

• Data quality validation, ensuring high trust with our users

• Visualization platform (likely Superset) to allow users to query and utilize the data

• Data delivery mechanism for users who just want a finished product, such as a flat file version of a data mart (cloud storage with provisioned access)

Optional components:

• API, to call queries and extract data

Success metrics

Success for SageRx is defined by our utility, which we are defining as our users discovering a variety of drug data. This metric of finding new insights can be estimated by tracking how many different questions people ask (aka novel queries). The more unique questions people ask, the more we know they find value in using our platform.

We want to grow a community of active and enthusiastic members, who – thanks to their feedback and our continuous improvement – will help fuel SageRx's momentum and thus its impact.

SageRx Website

If you’d like to learn more or feel like contributing your time or expertise, please visit our website by clicking the button above. If you have comments or feedback about anything you read, please leave them in the Substack comments section or find us on our Slack.

Thanks for following along! 🌿

Subscribe now

• Working with Medication Data - Bite-size videos (<5 minutes each) from CodeRx founder Joey LeGrand for a primer on working with medication data, as well as an introduction to 🌿 SageRx.

• Intro to FHIR (60 minutes) - Overview of standards, regulation, and the nuts and bolts of healthcare's hottest standard, Fast Healthcare Interoperability Resources (FHIR).

  

News

• Updoc Announces Funding and Partnerships for their AI Assistant - Funding round included Eli Lily & Company and Mayo. New collaborations with UCSF and American Heart Association announced.

• Out-of-Pocket Health covers InPharmD - Review of go-to-market and a peak under the hood of the combo generative AI / clinical question platform.

• Turquoise Health Announces Funding - Pricing platform raises $30 million in investment led by Adams Street Partners and previous investors such as Andreessen Horowitz and BoxGroup.

Jobs / opportunities

• Pharmacist Informatics, Ohio Health - Data Analytics Focus

• Information Systems Product Manager, blue circle health - Remote

• VP Product, Oak Street Health - Chicago, IL

• Director, Clinical Product Strategy, Free Market Health

• Senior Software Engineer, Healthcare Interoperability - IntelliScript

• Data Scientist, Amgen - Remote

Thread highlights

• Drug Classifications with RXCUIs

• Standard Databases for Durable Medical Equipment

Member/Community spotlights

• SageRx gets into the holiday sprit! Twelve-ish days of SageRx-mas, a gentle introduction to the open-source medication information tool. The twelve-ish days are over, but you can learn more about SageRx here.

Get your login info here 🌿 https://forms.gle/tbEQwkhy5EH5Rcxq8

If people actually sign up for this and (even better!) actually use it over the next dozen or so days, we will likely host our own version of Apache Superset at some point early next year. The simple reason this only lasts twelve-ish days is that we have a free 14 day trial of premium features for Preset… and it started yesterday. This is just a free and easy way for us to gauge interest in something similar to this without going through the hassle of hosting Apache Superset ourselves first.

With Apache Superset, you can write queries using any of the tables in SageRx, create charts using that data, and build visualizations using those charts, tables, and also markdown. You can publish your work to instantly share it with other users using SageRx via the hosted instance of Apache Superset — or keep it in draft form. Easily collaborate with team members and data nerds from around the world 🤓 using open drug data from SageRx in the cloud.

Get your login info here 🌿 https://forms.gle/tbEQwkhy5EH5Rcxq8

Even if you don’t celebrate SageRx-mas, you are absolutely still invited to join. Also, we’d love any feedback about your experience - please shoot us an email at hello@coderx.io or jump into our Slack and start up a conversation with the community that supports SageRx.

Oh - and please be patient with us. One kind of weirdly positive but annoying outcome of this might be that we overload the meager server we’re currently using to host the SageRx database. If that happens, we’ll try to get everything up and running again as soon as possible.

Thanks for following along and for all of your support!

🌿

Subscribe now

Anyway, at some point in my formative years I discovered QBasic in the DOS prompt of my grandma’s old PC (C:\>qbasic). Then I rode my physical bike to a physical library, and rented a physical book that taught me all about QBasic and more importantly about basic programming principals in general. Gradually, I started spending less time digging around in dusty flea market bins and more time scribbling workflow diagrams and pseudo code on big yellow legal pads - inventing digital things instead of physical.

At some point in physics class in high school, I told some random guy that I was torn between going to college for pharmacy or computer science, and he said that you can teach yourself whatever you need to know about computers, but you can’t just manifest a pharmacy license. So, off to pharmacy school I went. Six very academically challenging years later, I emerged with a PharmD. One of the first things I did after graduating was get another physical book - this time about PHP - and started learning how to build a webapp.

For several years after graduating, I was working as a retail pharmacist and in my free time building a webapp that aggregated local news from blogs / news outlets and ranked it based on social media reactions. After a while, I paused and asked myself why I (a pharmacist) was using my technical skills to build a journalism application - something I didn’t really care that much about. I shuttered my website and threw myself into learning more about health data standards, FHIR, Python, and SQL. This time, there wasn’t a good physical book that covered all that ground.

I went straight from retail pharmacy to a role as a e-prescribing software product owner at a major academic medical center (as one does) and learned a little about software development processes. Then, I got lucky and was part of a big-bang Epic EHR implementation and was immersed in the world of HealthIT as an EHR analyst. I started a little Slack group initially as a way to chat with people about HealthIT topics relating to EHRs, but found that my interests were really less about “turning the knobs of Epic” than they were about “inventing healthcare stuff”.

Obviously, my sweet spot was somewhere in the middle of pharmacy and computer science, so I “rebranded” my little Slack to CodeRx and started connecting with pharmacists like me who were way smarter and better than me at coding and data. I somehow convinced a few of them to compete in an HHS/ONC-sponsored contest to make synthetic medication data more realistic and we won first prize in 2021. However, in doing so, we discovered what a pain it was to work with open drug data - different file types, formatting, update schedules, terminologies, etc.

The friction we felt working with open drug data, combined with the withdrawal symptoms I felt from not having a big project to work on, led a smaller group of us to start working on a project that could make it much easier to work with open drug data. If you’ve been reading what I’ve been writing, you might already know that this led to the development of SageRx. I went from knowing very little about drug data to having a pretty comprehensive library of open drug data sources to work with in a couple of years.

With SageRx, I feel like I’m starting to again gather my boxes full of odds and ends that can be used to build things greater than the sum of their parts. This time, I’m curating parts from government organizations instead of flea markets and garage sales. Some recall data from FDA here, patient prescription medication surveys from AHRQ MEPS there, all historical NDCs from RxNorm here, WHO ATC classification mappings to product RXCUIs there. Now that my box of parts is starting to get full, what should we build with it? What problems can we solve with it? What research can be done with it? We have some ideas, but would love to know what you think.

If you want to be part of the rest of the story, please consider jumping into the CodeRx Slack - it’s nearly 900 people strong now! If you have any ideas for things to build or problems to solve or research to be done, please contact us - we hope to hear from you. In the meantime, I’ll continue down this path and see where it leads me.

Subscribe now

Per a request from Benjamin Jolley, I started working on pulling data from the Medicare Part D Prescription Drug Plan Formulary, Pharmacy Network, and Pricing Information data source into SageRx.

I went after the quarterly release files because they were the only ones with pricing information.

Here is a list of barriers I had to overcome:

1. The zip files were encoded in a weird deflate64 format - likely because someone at CMS just puts the files in a folder on a Windows machine, right clicks, and selects "Send to zip file" when they are putting this data together for a release. I discovered that the normal Python zipfile package does not handle this format and had to add a separate zipfile-deflate64 package as a dependency to the project in order to work with this data… which is kind of a bummer because I’m trying to keep the dependencies to a minimum.

2. Once unzipped, the pipe-delimited text files contained within the zip files have a slightly different naming convention than then names of the zip files themselves AND they aren’t even consistently formatted within themselves. Examples below:

   1. Only one text file is capitalized (i.e. “Indication Based Coverage Formulary File…”) vs all the other files being lower case (i.e. “excluded drugs formulary file…”). A minor inconvenience to standardization.

   2. Some of the text files have one space before “ PPUF” while others have two spaces before “ PPUF”. This is extra annoying because it means you have to treat individual files differently vs just looping through them somehow. Or I guess I could do some RegEx and renaming of the files to clean all of this up.

3. All of the text files, once unzipped, were in UTF-8 encoding EXCEPT FOR ONE which was in ANSI formatting for some reason. And I had to figure out that Postgres does not support copying ANSI encoded data so I had to use a WIN1252 encoding format just for this file.

4. All of the files except one had “file” in their name (i.e. “pricing file” and “pharmacy networks file” vs “plan information”), which wouldn’t be a huge deal if it weren’t for my OCD.

5. The naming of the quarterly zip files has a date in the file name (which means we need to know the date to download new files), but the only pattern I can figure out is below… which is kind of a weird pattern to automate.

   1. Q1 seems to be the second Friday in March

   2. Q2 seems to be the first Friday in June

   3. Q3 seems to be the second Friday in September

   4. Q4 seems to be the first Friday in December

6. Instead of being consistent and containing the date like the exterior zip files (i.e. “20230602”, the INTERIOR zip files contain the year and quarter number (i.e. “2023Q2”). This isn’t a huge deal - it just means that I have to take an extra step to calculate the quarter number from the date to automate this part, which has to be custom code since I don’t think this is commonly available in Python packages that work with dates.

7. Due to their massive size, the pharmacy network data is split into six different zip files, but I’m assuming it should all be contained in a single table to make it easy to work with. So I decided to unzip all the files and combine them into a single table instead of creating six different tables.

Here’s the finished product. Unzipped and loaded into the database, it takes up about 40 GB of storage space… which is a lot compared to other open data sources. Definitely lots of optimizations to make, but at least it’s all loaded. Now to start transforming it…

If you want to learn more about SageRx, please read through some of my earlier posts or check out the GitHub repo. Until I submit a PR, you can review the branch I created for this work. Thanks for reading!

🌿

Subscribe now

RxNorm Reconfigured

This article is the follow-up to another one from nearly two years ago called Restructuring RxNorm for humans. To make RxNorm easier to use in a relational database alongside other data sources that use NDCs and RXCUIs to identify medications, we restructured / rearranged / reconfigured the tables in RxNorm to the structure depicted above. Forgive the sloppy lines connecting everything - working with what I’ve got.

All of these tables are available within SageRx and in this article I’m going to step through each section and explain the thought process behind organizing it this way. These building blocks are in the staging schema and then are utilized for more useful utility queries in the intermediate schema. Both are discussed below.

Staging schema

The staging schema contains data models that represent the most clinically useful elements of the data within RxNorm.

NDCs

This table contains one row per NDC (in NDC11 format) with links to clinical products (and brand products where the NDC has an associated brand product per RxNorm). You can use this table to quickly determine if an NDC has an associated brand product. All NDCs should have an associated clinical product.

Brand products

If an NDC has an associated brand product (BPCK / SBD), this table will link to the brand name as well as the associated clinical product.

If a brand product is a package that has multiple components (BPCK), this table will also link to the base brand product components (typically SBD) via the linking table.

Brands

This table contains all brand names for brand products. You can also get to the clinical ingredients from this table.

Clinical products

Every NDC should have an associated clinical product (GPCK / SCD), even though not every NDC has an associated brand product. Think of clinical products like the generic name of a brand name product (even if the brand name product does not currently have a generic alternative).

Like brand products, clinical products can be packaged with multiple clinical product components (GPCK). This table links to to the base clinical product components (typically SCD) via the linking table.

Here’s an example of how this linking works:

Ingredients

From this point onward, we forego brand products (since they are just branded clinical products) and focus on how clinical products link to other things like ingredients and dose forms.

Clinical products (SCD) can have single ingredients (IN) or multiple ingredients (MIN). If a clinical product contains a multiple ingredient (MIN), you can link to the base individual ingredient components (IN) using the linking table.

Ingredient strengths

Ingredient strengths are represented by the SCDC term type (TTY) in RxNorm.

Because you need to know which product and which ingredient in that product to determine strength, the ingredient strength table in SageRx links to both the clinical product and ingredient tables.

In this table, you can determine the numeric ingredient strength and map the units from RxNorm to your system to calculate total dosage for products.

Dose forms and dose form groups

Clinical product components (SCD) have generally one dose form (DF) in RxNorm. An oral tablet is an oral tablet, and an oral capsule is an oral capsule.

Individual dose forms (DF) can belong to multiple dose form groups (DFG). This can be helpful if you are looking for all oral products, for instance, and don't care about the difference between oral tablets and oral capsules.

Intermediate schema

The intermediate schema builds upon the staging schema and creates time-saving pathways between things that people working with drug data tend to want to know.

A few example tables are listed below (there are several others, too):

NDC to clinical product

This lets you go from NDC to all the information about the clinical product (SCD/GPCK) that NDC represents. This table does not differentiate between brands and generics.

NDC to product

This is similar to the table above, but if an NDC has an associated brand product, it will return the brand product information (SBD/BPCK) as opposed to the clinical product information (SCD/GPCK).

This is helpful if you are doing cost analysis and need to know why one NDC is more expensive than another even though they represent the same clinical product (hint: one NDC might be a brand product).

In addition to returning the product information (SBD/BPCK RXCUI if brand name), it also includes the clinical product information (SCD/GPCK RXCUI).

Clinical product to ingredient strength

This is a useful table for calculating total daily dosage for a given product or for giving users a pick list of strength options for a given clinical product.

Clinical product to dose form

If you are trying to determine what dose form a clinical product is, this table can quickly give you an answer.

Conclusion

If you’ve used RxNorm data in the past, we would love to hear your thoughts on our approach to restructuring it. If you feel so inclined, please install SageRx and write some queries. Let us know your thoughts and feedback in the comments. Stay tuned for more SageRx info!

🌿

Subscribe now

Fair warning: this article is somewhat technical, so if you aren’t really interested in knowing how software works “under the hood”, you might want to skip this one. Or don’t! You might learn something and get hooked on software development!

Overall architecture

Docker is used to containerize the tools below. Spinning up SageRx is as simple as a few initial setup steps and then a couple of docker-compose up statements.

See detailed installation instructions in the GitHub repo here: https://github.com/coderxio/sagerx

Flow of data

1. Airflow
   ⬇️
2. PostgreSQL / pgAdmin
   ⬇️
3. dbt

This simplistic diagram is not entirely correct because dbt also updates PostgreSQL with tables created from the transformations… but hopefully you get the general idea.

I will try to explain some highlights specific to SageRx from each of these steps in the sections that follow.

1. Airflow

Airflow is the data pipeline tool we chose for this project because it is open source, supported by Apache, and seems to be widely used by a variety of industries. All of the business logic relating to where the raw open drug data lives (NLM / CMS / FDA / etc), what format it is stored in, how often it is updated, how to unpack and manipulate it, etc is stored within Airflow “DAGs” (directed acyclic graphs) - which are basically just Python scripts.

Airflow is used for the Extraction and Loading (EL) of the overall Extract-Load-Transform (ELT) data engineering process.

Timing of data source updates

The update timing of data sources is maintained here. Some data sources are updated weekly, others monthly, others yearly, etc. With the flip of a switch, you can automate recurring updates of any data source.

Authentication

Select data sources (RxNorm) require a free license key even though the parts of the data used from the source are open. Airflow handles the authentication steps required for these scenarios.

Changing file names

Some raw data file names change with every release - possibly including a date at the very least. Some files aren't regularly updated on the same day of the month and require an initial API call to determine the most recent file before downloading.

Back-loading of data

Some data sources have data going back several years. In situations where loading all of this data is beneficial (NADAC, for instance), Airflow can automate the backfill process of historic data.

Unzipping of files

Some data sources use zipped files one or more layers deep. Airflow, combined with custom functions, handles unzipping these files and only loading the necessary ones into the database.

Loading into the database

SageRx utilizes the Postgres COPY function to load data directly from a file into a database. We think this is the most efficient way to load data into a database. This can handle the vast majority of file types (CSV, TSV, RRF, etc).

In scenarios where the COPY function can't handle a file type (Excel files, for instance), we use Python / pandas to transform the data into a format that can be loaded into the database.

2. PostgreSQL / pgAdmin

Currently, all data ends up in a PostgreSQL database. We chose PostgreSQL because it is open source, supported by a large community, and has excellent documentation.

We suspect that there will be a need for connectors to other types of databases (cloud, data lake, etc), but initially we wanted to keep it simple.

Schemas

The three main schemas in the PostgreSQL database are:

1. datasource

2. staging

3. intermediate

All of the raw data from Airflow ends up in the datasource schema in as close to its raw format as possible. There may be slight differences solely due to column type, but the intent is to leave it in raw format.

From there, it is transformed by dbt (as described in step 3 below) and the resulting data ends up in the staging and intermediate schemas. More information about this in the dbt section below.

Eventually, we may have some data mart data in the mart schema and possibly a separate flatfile schema for data meant to be loaded into flat files for easy download.

Custom functions

NDC formatter

Due to the need for common transformations like taking a hyphenated NDC and converting it to NDC11 format, we created custom Postgres functions to handle this.

We made this decision prior to implementing dbt, so a future optimization may be converting these functions from Postgres to dbt. However, they come in handy when writing queries directly in pgAdmin.

select ndc_to_11('1234-5678-90')

/* returns 01234567890 */

Connecting to the database

Using pgAdmin

It is not necessary to use pgAdmin to connect to the database, but it makes it easy to run some quick queries and has (in our opinion) a great user interface.

By default pgAdmin is hosted on port 8002.

Connecting directly

Since we use Postgres as the database, connecting to a hosted version of SageRx is just like connecting to any other Postgres database.

By default, the connection information is configured as listed below.

server: <<server_address>>:5432
username: sagerx
password: sagerx

3. dbt

Data build tool (commonly known as dbt) is where the Transformation (T) of the Extract-Load-Transform (ELT) data engineering process happens.

The steps of the dbt process line up nicely with schemas in Postgres. The two main schemas populated by dbt are staging and intermediate.

Staging

The staging step is where normalization of data generally happens (i.e. converting to NDC11, formatting date columns as DATE data types, etc).

It is also where basic data modeling begins happening. If the datasource schema contains raw energy and quarks, the staging schema condenses and refines this material into individual atoms we will use to build more intricate and useful structures. Some examples:

• FDA NDC Directory - Class tables in this schema have one row per drug class per product. In the raw data, this information is crammed into a single cell, delimited by semicolons.

• NLM RxNorm - Major transformation of RxNorm data happens at this step. Raw RxNorm tables are converted into data models representing NDCs, clinical products, brand products, ingredients, dose forms, etc.

• CMS NADAC - De-duplication of data happens at this step. Additionally, differences in percentage and dollars is calculated between each price change reported in the NADAC data.

Intermediate

The intermediate schema uses the building blocks of the staging schema to create more complex, and sometimes more useful, data models. Continuing the raw energy → atoms analogy, the intermediate schema is where we use the atoms from the staging schema to create molecules.

Some examples of intermediate tables:

• NLM RxNorm - The intermediate schema of RxNorm data contains extremely useful tables that let you go from NDC to clinical product information (ingredients, dose forms, etc) with a single select statement.

• CMS NADAC - This table contains data from the NADAC staging table, but filtered to only current prices based on the current flag defined in the staging table.

Documentation

Documentation is a first class citizen in dbt (and SageRx), and helps support the understanding of the transformations that happen so that (in addition to the underlying queries being open source) end users can feel confident when using the resulting data.

Documentation lives in YAML files inside of the folders of the models/ directory, but dbt also generates a user-friendly website (dbt docs generate) that can be hosted (dbt docs serve - pending Docker integration) to make documentation easily accessible to consumers of the data.

Seeds

Seeds are a dbt feature, and are meant to be small CSV files that can be loaded as tables in a different seeds schema.

SageRx doesn't currently utilize seeds for much, but one potential use could be a repository of "fixes" or "flags" for incorrect or out of date open data (i.e. take the raw source data and “patch” it with corrections or modifications stored in seeds).

Macros

Macros are a dbt feature that utilizes Jinja 2 templating to allow you to create SQL "functions" or easily re-usable SQL code that can be updated in one place and instantly update all the models where it is used.

Conclusion

If you’re still reading this, it probably means you have experience or are interested in the technical building blocks used to build a data pipeline. Hopefully I did a good enough job explaining the tools we chose to make ours (there are many others out there). I’m definitely open to feedback from anyone experienced in this area if it could make SageRx more useful or easier to maintain.

I touched a little on how our data pipeline addressed things specific to open drug data sources, but I plan to dig into the drug data-specific examples in a future post - specifically about RxNorm. If you’re not subscribed already, please subscribe now to follow along and get updates in your inbox.

Thanks for reading!

🌿

Subscribe now

SageRx Website

We spent the last couple of years learning as much as we could about these data sources and using them in our work wherever possible. In the process of doing so, we experienced many of the pain points associated with using any variety of data sources - but specifically with open drug data. These are things like different file formats, different update intervals, and non-standardized formatting of common fields like NDCs - just to name a few.

This led to us falling backwards down a rabbit hole of discovering and learning as much as we could about data engineering. Early on, we realized that applying some basic data engineering principles to these open drug data sources could address many of these pain points. After a while, we started taking it a step further and transforming the raw data with our clinical and domain knowledge in pharmacy (did I mention we're pharmacists who code?). Now, it feels like it could either be the start of something big or at least something really useful for a few groups of people.

We built a platform of one-click data pipelines that that can automatically extract up-to-the-day current open drug data and not only load it all into a common database so it’s easier to work with, but also transform it into something greater than the sum of its parts.

Oh - and we're open sourcing it all. https://github.com/coderxio/sagerx

We've already built pipelines for the things that we use frequently (RxNorm, FDA NDC Directory, NADAC, etc), but there's so much more potential. Our biggest challenge with this project has been focus. There so many more data sources that could be loaded and transformed, but if we spend a lot of time taking the effort to build one of these pipelines and nobody knows about it or uses it, then what's the point?

We want to explain what we've been working on and how it works. We want to open the doors and let people poke around. And we want people to try it out. And if somebody really likes it, we want to focus our efforts on building something that makes them like it even more.

This is different from the commercial drug database you might be familiar with.

1. For one, not only is the code and SQL to do the data transformations completely open source, the documentation is also open and written by pharmacist / developer hybrids who know how to translate pharmacy domain knowledge into developer-friendly concepts.

2. Second, it is fairly lightweight, easy to spin up (using Docker), and pretty much runs itself. Even not-super-technical people can add their own custom data transformations just by writing some SQL. And - if you think your work could benefit others - you could even contribute a pull request to the overall open-source SageRx project.

3. Lastly, at its core, SageRx is based on open common standards that promote interoperability - instead of licensed, proprietary coding systems that make it difficult to share data between organizations.

To be clear, we’re not a huge organization of people scrubbing the source data and phoning manufacturers to fill in gaps… but it’s not our intention to be that. We want to build something sustainable with very little overhead that might make drug data more accessible and understandable for people that need to work with it. Our hope is that SageRx can benefit (at the very least):

• Startup founders

• Researchers

• Data analysts

• Maybe you?

If any of this interests you, please star the repo, join our Slack, and/or shoot us an email. Oh - and please be patient as we try to get our documentation in order over the next few weeks. If you have questions or need help getting started in the meantime, the #proj-sagerx channel of our Slack is an excellent resource for support.

Stay tuned for more fun-filled SageRx content in the near future. Expect to see some documentation on SageRx as well as tutorials and example queries. Thanks for reading and let us know if you have any questions or feedback in the comments!

SageRx Website

🌿

Subscribe now

• CaryRx - Example of documenting accessible APIs for prescription fulfillment.

• The Deadlock Empire - Multi-threaded programming game, shared by CodeRx member Benjamin Jolley.

• ChatGPT Engineer - Specify what you want it to build, the AI asks for clarification, and then builds it.

News

• Published! - The work Team CodeRx did on the Medication Diversification Tool (MDT) as part of the Synthetic Health Data Challenge in 2021 was published in JAMIA Open with the title: “A novel method to create realistic synthetic medication data”. Congratulations to CodeRx Members: Authors - Robert Hodges, Kristen Tokunaga, Joseph LeGrand; Team member also included - Kent Bridgeman, Yevgeny (Eugene) Bulochnik, Dalton Fabian

• AI Partnerships - GalenAI, an AI search engine for clinicians founded by CodeRx member Jonathan Adly, announced a new partnership with cloud pharmacy management software DRX, founded by CodeRx user Dan Safee

  

• Applied AI Challenge: Large Language Models (LLMs) - Launch of challenge aims for Improving Federal Government Services through the Use of LLMs

• Amazon's PillPack suffers data breach

• ONC HITAC Creates Pharmacy Interoperability Task Force - Task force to identifying recommendations to support interoperability between pharmacy constituents

• Ultra low-cost smartphone attachment measures blood pressure at home

Jobs / opportunities

• Walmart Health - Clinical Informatics Specialist - Bentonville, AR

• RxAnte - Director, Web Services - Remote

Thread highlights

• Using openFDA APIs for reference - Discussion, documentation, and an easy to read narrative article (Identifying FDA approved indications for drugs (using NLP)) from user Manas Kaushik.

• Pet projects: Check printing from Vue App (Via CodeRx member Dan Safee)

Member/Community spotlights

• LinkedIn reached 1,000+ followers! Check out and follow if you haven’t yet!

  

• ShellGPT - A command-line productivity tool powered by OpenAI's GPT models. “We can leverage AI capabilities to generate shell commands, code snippets, comments, and documentation, among other things.”

• ViteJS - New approach for front-end design, shared as a part of a list/article on best tools for React.

• Pandas AI - Python library that integrates generative artificial intelligence capabilities into Pandas, making dataframes conversational.

• Coverage Search (iOS app link) - Quickly understand how every health plan and PBM covers all FDA approved medications in your geography.

• 27 AI sites - Shared from LinkedIn (Ruben Hassid), via Joey LeGrand.

  

News

• The next big step forwards for analytics engineering - news from “CodeRx”(/Kent Bridgemen) fan platform of dbt, saying “many software engineering features finally making it to analytics through DBT.”

• Super Integration Fighter III - From Health API Guy Brendan Keeler, a great review of direct-to-database strategies.

• VA EHR leader calls for ‘accelerated delivery’ in fixes to pharmacy disruptions

Jobs / opportunities

• Clinical Content Consultant - Pharmacist, Medi-Span

Thread highlights

• Informatics in the Community Pharmacy setting? - Great review of comparing settings for informatics, great quote (“I've seen roles for BI Analyst with CVS on LinkedIn that have higher salaries compared to regular pharmacists.")

Member spotlights

• Congrats to our first-ever student capstone team! The team of Paula Sharoubeem, David Poling, Vignesh Senguttuvan from the University of Pittsburgh and mentor Joey LeGrand (with help from Kent Bridgeman, Eugene Bulochnik). See their work/outcome presentation here, slide from their presentation below: