← Back

How to Open and Analyze Hospital Standard Charges Files

2024-09-17 // Tom Ward, Software and Analytics Consultant

Open Standard Charges File

standard charges UCLA pancreas transplant

Working with Hospital Standard Charge Files

Hospital standard charge files are publicly available data files published by hospitals of prices for all items and services they provide, including prices charged to patients paying out of pocket and prices charged to insurance companies. These files are notoriously difficult to work with due to their size and format but Row Zero workbooks are an easy way to view and work with hospital standard charge files. If you are already familiar with hospital standard charge files, feel free to skip ahead to the How to Work with Hospital Standard Charge Files in Row Zero section. You can also read our post about how to interpret hospital standard charge files.

Table of Contents

If you are interested in taking a look at a couple of examples of hospital standard charge files, I have loaded charges as of 8/27/2024 from UCLA’s Ronald Reagan UCLA Medical Center, NYU Langone Health’s Brooklyn Hospital, the Mayo Clinic in Rochester, MN, and Cleveland Clinic Main Campus into this Row Zero template.

What are Hospital Standard Charge Files?

If you’re in the United States, the last time you went to the doctor or hospital, you probably didn’t find out how much the services you received actually cost until a month or 2 (or more) after your visit. And assuming you had health insurance, you were probably still confused by what the hospital charged your insurance company, what you were asked to pay, and how that compares to what other people pay who had the exact same services, but different insurance or no insurance at all.

Until very recently, finding out how much hospitals charge for the services they provide was very difficult and gathering this kind of data in any systematic way across many hospitals was practically impossible. However, a series of recent regulations by Centers for Medicare & Medicaid Services (CMS) has made this at least somewhat possible. Hospitals are now required to make public a “machine readable file” of prices they charge for all items and services, along with the prices they’ve negotiated for these items and services with various insurance companies.

The Challenges of Working With Hospital Standard Charge Files

Even though hospitals now make these files public, there are still hurdles in actually making sense of the data contained in the files:

  1. The files are often technically challenging to work with due to their size and format
  2. You need some knowledge of healthcare billing and charging to interpret the data

In this post, I’ll show you how using a Row Zero workbook simplifies the technical challenges to working with hospital standard charge files. If you already have a hospital charge file that you want to load into Row Zero, skip ahead to the How to work with Hospital Standard Charge files in Row Zero section.

In a subsequent post, I’ll give you a brief overview of healthcare billing and charging so you can make sense of the data contained in these files.

Why Row Zero Makes Working with Hospital Standard Charge Files Easy

A lot of hospital standard charge files are very large in size. NYU Langone Health’s files, for instance, are about 1.6 GB each at the time of this writing. Row Zero has no problem handling large files this size and larger. While trying to download these files onto a computer and open them using Excel or another locally installed tool might be difficult or impossible, there are no issues loading a file of this size or larger into a Row Zero workbook.

Additionally, many hospital standard charge files contain millions of rows of data. In the CSV “tall” template format, there is a row for every combination of item/service and every payer with a negotiated rate for that item or service. That can quickly add up to millions of rows of data. UCLA Health’s file for Ronald Reagan UCLA Medical Center has over 3.2 million rows as of this writing. Excel has a limit of 1,048,576 rows per worksheet. Row Zero allows you to load up to billions of rows of data and maintains good performance with very high row counts.

A Brief History of Price Transparency Regulation

The CMS Price Transparency Rule made hospital charges and pricing data widely available to the public, or at least that was the intent. Since January 2, 2021, hospitals have been required to post a “machine-readable file” of standard charges for all items and services they provide (as well as a consumer-friendly file of 300 “shoppable services” but we’ll focus on the machine-readable files here). This means that, in theory, anyone (or at least machines…) should be able to access a lot of interesting data on hospital charges and pricing like:

  • How much various hospitals charge for procedures when you pay cash and receive a discounted “self-pay” price vs. when your health insurer pays for the procedures
  • The rates that hospitals have negotiated with different insurance companies (payers) and how those rates differ from one another
  • How much hospitals charge patients paying out of pocket for medications and supplies and what the negotiated payer rates are for those same medications and supplies

After the Price Transparency Rule went into effect, hospitals' compliance with the regulation was hit or miss. Because CMS did not release much in the way of standards or format requirements for the files, hospitals published data in whatever way they felt complied with the law, which often meant that data was illegible and very difficult to collect across hospital systems. However, as of July 1, 2024, hospitals must adopt one of three released CMS templates:

  • CSV “Tall” Template
  • CSV “Wide” Template
  • JSON Schema

These template formats at least provide some uniformity across hospital systems and give users a starting point to understand the data published by hospitals.

CMS-Released CSV Template Formats

CSV “tall” template

In the CSV “tall” template, a row in the file corresponds to a hospital charge for an item or service + payer negotiated rate (or self pay price) for that service. So for example, if a hospital has negotiated a rate for an appendectomy with 15 different payers, you’ll see 16 rows for that appendectomy charge (15 negotiated payer rates + 1 self-pay rate). Hospital standard charge files using this template tend to have a lot of rows, hence the “tall” name. Tall standard charges file

CSV “wide” template

In the CSV “wide” template, a row corresponds to a hospital charge for an item or service. Payer and payer negotiated rate (and some related data) show up as columns in this template. So in our previous appendectomy example, you’d see a single row for the appendectomy charge and 16 sets of columns corresponding to payer information for that appendectomy charge. Hospital standard charge files using this template tend to have a lot of columns, hence the “wide” name. Wide standard charges file

How to work with Hospital Standard Charge files in Row Zero

Loading “Tall CSV” or “Wide CSV” format files into Row Zero

  1. Navigate to a hospital’s standard charge files page. You can generally find it by searching their public website for “hospital charges”, “price transparency”, or similar search terms. You can also try "hospital name standard charges." Here is an example page for NYU Langone Health.
  2. Find a hyperlinked file and confirm that it has a .csv file extension.
  3. Copy the URL of the file
  4. Open a Row Zero workbook and click on Data -> Import from URL Standard charges import from URL
  5. Enter the URL of the file Enter file url
  6. Click on Advanced options and change the separator to “Comma” standard charges preview advanced
  7. Click on “Import” to load the file into the workbook
  8. When the file loads, the first few rows will be meta-data about the file - you can note these values and then delete them.
  9. Determine which template the file is using (either the CSV “Tall” Template or the CSV “Wide” Template). This will allow you to use the CMS Github page to understand the schema of the file. In my next post, I’ll further help you make sense of the contents of the file.

Sample Hospital Standard Charges Files

Conclusion

Hospital standard charge files provide a valuable resource for understanding healthcare costs, but their size and complexity can make them challenging to work with. For more information on standard charges files, check out how to interpret hospital standard charge files. Row Zero simplifies this process, offering a robust platform that handles large datasets and supports detailed analysis across multiple templates. With its ability to manage millions of rows efficiently, Row Zero makes it easier to compare hospital pricing data and gain insights into healthcare costs. Whether you're a data analyst or just someone looking to understand healthcare pricing, Row Zero is a powerful tool for your needs.

FAQs