Medicare provider payment data SQL

README.md

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;

ddl.sql

-- 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;