Skip to content

Instantly share code, notes, and snippets.

@paulsmith paulsmith/README.md
Last active Aug 29, 2015

Embed
What would you like to do?
Medicare provider payment data SQL

The Centers for Medicare and Medicaid Services (CMS) today released a 9m row dataset detailing payment data about doctors and other providers who get paid by Medicare. Read more about the release.

The data, along with a description PDF, is here, and it's roughly 400MB zipped, 1.3GB unzipped, tab-delimited.

I stashed the tab-delimited data file itself on S3, it's public, you can grab it.

It's not a huge file, so you could load it with the schema I made into a local pgsql instance on your laptop, but it's fun to play around with it on AWS Redshift. To load it in Redshift, start up a cluster, connect to it with psql, execute the schema SQL, and run the following commands:

dev=# copy medicare_physician from 's3://paulsmith/medicare/medicare.txt.gz' credentials 'aws_access_key_id=<YOUR ACCESS KEY>;aws_secret_access_key=<YOUR SECRET KEY>' delimiter '\t' gzip ignoreheader 1;
dev=# copy place_of_service from 's3://paulsmith/medicare/place_of_service.txt' credentials 'aws_access_key_id=<YOUR ACCESS KEY>;aws_secret_access_key=<YOUR SECRET KEY>' delimiter '\t' ignoreheader 1;
-- PostgreSQL/Redshift
BEGIN;
CREATE TABLE medicare_physician (
npi varchar,
nppes_provider_last_org_name varchar,
nppes_provider_first_name varchar,
nppes_provider_mi varchar,
nppes_credentials varchar,
nppes_provider_gender varchar,
nppes_entity_code varchar,
nppes_provider_street1 varchar,
nppes_provider_street2 varchar,
nppes_provider_city varchar,
nppes_provider_zip varchar,
nppes_provider_state varchar,
nppes_provider_country varchar,
provider_type varchar,
medicare_participation_indicator varchar,
place_of_Service varchar,
hcpcs_code varchar,
hcpcs_description varchar,
line_srvc_cnt real,
bene_unique_cnt real,
bene_day_srvc_cnt real,
average_Medicare_allowed_amt real,
stdev_Medicare_allowed_amt real,
average_submitted_chrg_amt real,
stdev_submitted_chrg_amt real,
average_Medicare_payment_amt real,
stdev_Medicare_payment_amt real
);
COMMENT ON TABLE medicare_physician IS 'Medicare Fee-For Service Provider Utilization & Payment Data';
COMMENT ON COLUMN medicare_physician.npi IS 'National Provider Identifier';
COMMENT ON COLUMN medicare_physician.nppes_provider_last_org_name IS 'Last Name/Organization Name';
COMMENT ON COLUMN medicare_physician.nppes_provider_first_name IS 'First Name';
COMMENT ON COLUMN medicare_physician.nppes_provider_mi IS 'Middle Initial';
COMMENT ON COLUMN medicare_physician.nppes_credentials IS 'Credentials';
COMMENT ON COLUMN medicare_physician.nppes_provider_gender IS 'Gender';
COMMENT ON COLUMN medicare_physician.nppes_entity_code IS 'Entity Code';
COMMENT ON COLUMN medicare_physician.nppes_provider_street1 IS 'Street Address 1';
COMMENT ON COLUMN medicare_physician.nppes_provider_street2 IS 'Street Address 2';
COMMENT ON COLUMN medicare_physician.nppes_provider_city IS 'City';
COMMENT ON COLUMN medicare_physician.nppes_provider_zip IS 'Zip Code';
COMMENT ON COLUMN medicare_physician.nppes_provider_state IS 'State Code';
COMMENT ON COLUMN medicare_physician.nppes_provider_country IS 'Country Code';
COMMENT ON COLUMN medicare_physician.provider_type IS 'Provider Type';
COMMENT ON COLUMN medicare_physician.medicare_participation_indicator IS 'Medicare Participation Indicator';
COMMENT ON COLUMN medicare_physician.place_of_Service IS 'Place of Service';
COMMENT ON COLUMN medicare_physician.hcpcs_code IS 'HCPCS Code';
COMMENT ON COLUMN medicare_physician.hcpcs_description IS 'HCPCS Description';
COMMENT ON COLUMN medicare_physician.line_srvc_cnt IS 'Number of Services';
COMMENT ON COLUMN medicare_physician.bene_unique_cnt IS 'Number of Medicare Beneficiaries';
COMMENT ON COLUMN medicare_physician.bene_day_srvc_cnt IS 'Number of Medicare Beneficiary/Day Services';
COMMENT ON COLUMN medicare_physician.average_Medicare_allowed_amt IS 'Average Medicare Allowed Amount';
COMMENT ON COLUMN medicare_physician.stdev_Medicare_allowed_amt IS 'Standard Deviation Medicare Allowed Amount';
COMMENT ON COLUMN medicare_physician.average_submitted_chrg_amt IS 'Average Submitted Charge';
COMMENT ON COLUMN medicare_physician.stdev_submitted_chrg_amt IS 'Standard Deviation Submitted Charge Amount';
COMMENT ON COLUMN medicare_physician.average_Medicare_payment_amt IS 'Average Medicare Payment Amount';
COMMENT ON COLUMN medicare_physician.stdev_Medicare_payment_amt IS 'Standard Deviation Medicare Payment Amount';
CREATE TABLE place_of_service (
code varchar primary key,
description varchar,
facility_based boolean default false
);
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.