Dataset: Hospital-price-transparency

About this Dataset

Hospital-price-transparency

Source

Hospital Price transparency

The Centers for Medicare and Medicaid Services recently required hospitals under 45 CFR §180.50 to publish a list of prices on their websites. They specifically instruct hospitals to make these lists...

As a comprehensive machine-readable file with all items and services.
In a display of shoppable services in a consumer-friendly format.

There is a lot of variation in adherence to these policies. Without strong guidance on formatting from CMS, it is no wonder hospitals are all over the map on formatting. Many hospitals have complied with the new rules but in ways that are not consumer friendly. 500 Megabytes of JSON data is not a strong start!

This repository cuts out pricing noise purposefully introduced by these hospital systems. You can easily search for a given CPT or HCPCS code and compare those prices across hospitals.

Supplied Data

If you don't have the proclivity to transform these data yourself with docker, there are CSV extracts available in ./volumes/data/extracts. They are broken down into four distinct groups.

gross: this is often the top line item that the hospital never actually charges
cash: this is the self-pay discounted price you would pay without insurance
max: this is the maximum negotiated rate by an insurance company in the hospital network.
min: this is the minimum negotiated rate by an insurance company in the hospital network

Ontology

We rely on the excellent work of the Athena vocabulary to define the ontology of healthcare procedures. This maps CPT and HCPCS codes into a common data model.

Coverage

Only North Carolina is covered right now because I happen to live there. Submit a PR if you have found data for other hospital systems.

Usage

Quickstart with docker-compose

docker-compose up

Run the flyway migrations

docker-compose run flyway

Run the ETL

docker-compose run etl

Interactive PSQL client

docker exec -it postgres psql -d postgres -U builder

What this is not

I sacrificed some scalabilty for the name of speed. There are some excellent examples how you could scrape your way through this to complete automation. I introduced a s manual step of downloading a file and naming it by the hospital ID. All other transformations are codified and reproducible in the container.

Contact

Submit an issue if you find anything inconsistent. Like all data products, we make assumptions and provide no warrantee.

Tables

Concept

@kaggle.natesutton_hospitalpricetransparency.concept

488.59 KB
10793 rows
4 columns


CREATE TABLE concept (
  "concept_id" BIGINT,
  "concept_code" VARCHAR,
  "vocabulary_id" VARCHAR,
  "concept_name" VARCHAR
);

Hospital

@kaggle.natesutton_hospitalpricetransparency.hospital

14.78 KB
115 rows
8 columns


CREATE TABLE hospital (
  "hospital_id" BIGINT,
  "hospital_name" VARCHAR,
  "hospital_npi" BIGINT,
  "city" VARCHAR,
  "state" VARCHAR,
  "critical_access_ind" BIGINT,
  "affiliation" VARCHAR,
  "disclosure" VARCHAR
);

Price

@kaggle.natesutton_hospitalpricetransparency.price

6.23 MB
1507795 rows
4 columns


CREATE TABLE price (
  "hospital_id" BIGINT,
  "concept_id" BIGINT,
  "price" VARCHAR,
  "amount" DOUBLE
);