Version: Canary - 2.4 🚧

v2.4.0

Prod Features

Taxonomy Grouping, Classification, and Specialization

taxonomy_grouping	taxonomy_classification	taxonomy_specialization	_col3
Allopathic & Osteopathic Physicians	Psychiatry & Neurology	Vascular Neurology	18189
Allopathic & Osteopathic Physicians	Psychiatry & Neurology	Neurology with Special Qualifications in Child Neurology	16743
Respiratory, Developmental, Rehabilitative and Restorative Service Providers	Physical Therapist	Orthopedic	42061
Allopathic & Osteopathic Physicians	Ophthalmology	Retina Specialist	129303
Podiatric Medicine & Surgery Service Providers	Podiatrist	Primary Podiatric Medicine	5508
Allopathic & Osteopathic Physicians	Surgery		1540920
Group	Multi-Specialty		34507
Allopathic & Osteopathic Physicians	Internal Medicine	Endocrinology, Diabetes & Metabolism	113155
Allopathic & Osteopathic Physicians	Pathology	Pediatric Pathology	9460
Allopathic & Osteopathic Physicians	Nuclear Medicine		36661
Allopathic & Osteopathic Physicians	Hospitalist		2860408
Dental Providers	Dentist	Oral and Maxillofacial Surgery	2989
Eye and Vision Services Providers	Optometrist		1735086
Physician Assistants & Advanced Practice Nursing Providers	Nurse Anesthetist, Certified Registered		20597497
Allopathic & Osteopathic Physicians	Internal Medicine	Gastroenterology	1924568
Allopathic & Osteopathic Physicians	Neuromusculoskeletal Medicine & OMM		13741
Allopathic & Osteopathic Physicians	Orthopaedic Surgery	Orthopaedic Trauma	8723
Allopathic & Osteopathic Physicians	Orthopaedic Surgery	Orthopaedic Surgery of the Spine	70569
Allopathic & Osteopathic Physicians	Otolaryngology		663347
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Gerontology	9434
Allopathic & Osteopathic Physicians	Colon & Rectal Surgery		46074
Allopathic & Osteopathic Physicians	Internal Medicine	Hematology & Oncology	8257370
Allopathic & Osteopathic Physicians	Pediatrics	Pediatric Gastroenterology	31963
Allopathic & Osteopathic Physicians	Radiology	Vascular & Interventional Radiology	234906
Allopathic & Osteopathic Physicians	Psychiatry & Neurology	Psychiatry	1516886
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Family	21662765
Behavioral Health & Social Service Providers	Social Worker		26083
Allopathic & Osteopathic Physicians	Physical Medicine & Rehabilitation		545341
Dietary & Nutritional Service Providers	Dietitian, Registered		26306
Allopathic & Osteopathic Physicians	Emergency Medicine		15330733
Allopathic & Osteopathic Physicians	Pediatrics	Pediatric Critical Care Medicine	81114
Allopathic & Osteopathic Physicians	Psychiatry & Neurology	Neurology	13355331
Allopathic & Osteopathic Physicians	Surgery	Plastic and Reconstructive Surgery	73668
Allopathic & Osteopathic Physicians	Allergy & Immunology		162934
Group	Single Specialty		76986485
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Women's Health	56502
Allopathic & Osteopathic Physicians	Internal Medicine	Pulmonary Disease	1103533
Allopathic & Osteopathic Physicians	Neurological Surgery		546486
			56191
Allopathic & Osteopathic Physicians	Surgery	Surgery of the Hand	20393
Physician Assistants & Advanced Practice Nursing Providers	Physician Assistant	Medical	223722
Allopathic & Osteopathic Physicians	Internal Medicine	Medical Oncology	1444335
Allopathic & Osteopathic Physicians	Surgery	Trauma Surgery	14089
Allopathic & Osteopathic Physicians	Radiology	Pediatric Radiology	50887
Allopathic & Osteopathic Physicians	Internal Medicine	Rheumatology	4644455
Allopathic & Osteopathic Physicians	Internal Medicine	Cardiovascular Disease	4326537
Behavioral Health & Social Service Providers	Psychologist		73273
Allopathic & Osteopathic Physicians	Internal Medicine	Geriatric Medicine	79091
Allopathic & Osteopathic Physicians	Internal Medicine	Interventional Cardiology	806555
Allopathic & Osteopathic Physicians	Pediatrics		1723944
Allopathic & Osteopathic Physicians	Internal Medicine	Critical Care Medicine	444213
Allopathic & Osteopathic Physicians	Surgery	Vascular Surgery	325676
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Primary Care	11998
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Psychiatric/Mental Health	610459
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Pediatrics	191546
Behavioral Health & Social Service Providers	Social Worker	Clinical	980863
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Acute Care	692028
Allopathic & Osteopathic Physicians	Internal Medicine	Clinical Cardiac Electrophysiology	50695
Allopathic & Osteopathic Physicians	Pediatrics	Pediatric Cardiology	93933
Allopathic & Osteopathic Physicians	Family Medicine		14995612
Allopathic & Osteopathic Physicians	Emergency Medicine	Emergency Medical Services	26940
Behavioral Health & Social Service Providers	Counselor	Professional	323085
Allopathic & Osteopathic Physicians	Radiology	Diagnostic Radiology	28473919
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner		1539475
Allopathic & Osteopathic Physicians	Emergency Medicine	Pediatric Emergency Medicine	11097
Physician Assistants & Advanced Practice Nursing Providers	Physician Assistant	Surgical	358604
Allopathic & Osteopathic Physicians	Orthopaedic Surgery	Hand Surgery	24969
Allopathic & Osteopathic Physicians	Surgery	Surgical Oncology	671716
Ambulatory Health Care Facilities	Clinic/Center	Oncology	542568
Ambulatory Health Care Facilities	Clinic/Center	Oncology, Radiation	385357
Allopathic & Osteopathic Physicians	Anesthesiology	Pediatric Anesthesiology	23229
Allopathic & Osteopathic Physicians	Family Medicine	Sports Medicine	42702
Other Service Providers	Specialist		15217
Behavioral Health & Social Service Providers	Marriage & Family Therapist		17102
Allopathic & Osteopathic Physicians	Dermatology		1538733
Speech, Language and Hearing Service Providers	Audiologist		661710
Allopathic & Osteopathic Physicians	Radiology	Radiation Oncology	3394746
Podiatric Medicine & Surgery Service Providers	Podiatrist	Foot & Ankle Surgery	170569
Allopathic & Osteopathic Physicians	Internal Medicine		5492477
Allopathic & Osteopathic Physicians	Pain Medicine	Interventional Pain Medicine	60787
Physician Assistants & Advanced Practice Nursing Providers	Advanced Practice Midwife		135746
Dental Providers	Dentist		19934
Allopathic & Osteopathic Physicians	Surgery	Pediatric Surgery	69399
Podiatric Medicine & Surgery Service Providers	Podiatrist		67514
Respiratory, Developmental, Rehabilitative and Restorative Service Providers	Physical Therapist		5884860
Allopathic & Osteopathic Physicians	Pediatrics	Pediatric Emergency Medicine	29245
Allopathic & Osteopathic Physicians	Anesthesiology	Pain Medicine	59849
Physician Assistants & Advanced Practice Nursing Providers	Nurse Practitioner	Adult Health	69741
Allopathic & Osteopathic Physicians	Pediatrics	Pediatric Hematology-Oncology	405516
Allopathic & Osteopathic Physicians	Obstetrics & Gynecology	Gynecologic Oncology	753915
Allopathic & Osteopathic Physicians	Thoracic Surgery (Cardiothoracic Vascular Surgery)		254807
Respiratory, Developmental, Rehabilitative and Restorative Service Providers	Occupational Therapist		132032
Student, Health Care	Student in an Organized Health Care Education/Training Program		12089
Allopathic & Osteopathic Physicians	Internal Medicine	Infectious Disease	283808
Allopathic & Osteopathic Physicians	Anesthesiology		6226988
Allopathic & Osteopathic Physicians	Pediatrics	Neonatal-Perinatal Medicine	132334
Physician Assistants & Advanced Practice Nursing Providers	Physician Assistant		12013024
Behavioral Health & Social Service Providers	Counselor		8672
Allopathic & Osteopathic Physicians	Urology		1730144
Allopathic & Osteopathic Physicians	Dermatology	MOHS-Micrographic Surgery	6103
Allopathic & Osteopathic Physicians	Obstetrics & Gynecology	Maternal & Fetal Medicine	122939
Allopathic & Osteopathic Physicians	Pathology	Anatomic Pathology	154850
Allopathic & Osteopathic Physicians	Pathology	Dermatopathology	9580
Allopathic & Osteopathic Physicians	Ophthalmology		1582947
Allopathic & Osteopathic Physicians	Pain Medicine	Pain Medicine	19899
Allopathic & Osteopathic Physicians	Psychiatry & Neurology	Child & Adolescent Psychiatry	17446
Ambulatory Health Care Facilities	Clinic/Center	Infusion Therapy	505633
Allopathic & Osteopathic Physicians	Internal Medicine	Hospice and Palliative Medicine	40305
Allopathic & Osteopathic Physicians	Orthopaedic Surgery		2365563
Allopathic & Osteopathic Physicians	Internal Medicine	Hematology	304996
Chiropractic Providers	Chiropractor		81469
Allopathic & Osteopathic Physicians	Internal Medicine	Nephrology	1818642
Speech, Language and Hearing Service Providers	Speech-Language Pathologist		121592
Physician Assistants & Advanced Practice Nursing Providers	Anesthesiologist Assistant		357790
Allopathic & Osteopathic Physicians	General Practice		8824
Allopathic & Osteopathic Physicians	Orthopaedic Surgery	Sports Medicine	75817
Chiropractic Providers	Chiropractor	Rehabilitation	8899
Respiratory, Developmental, Rehabilitative and Restorative Service Providers	Occupational Therapist	Pediatrics	869
Allopathic & Osteopathic Physicians	Obstetrics & Gynecology		3966715
Behavioral Health & Social Service Providers	Counselor	Mental Health	215438
Behavioral Health & Social Service Providers	Psychologist	Clinical	19337
Allopathic & Osteopathic Physicians	Pathology	Anatomic Pathology & Clinical Pathology	1584819

Code

# [CLD-v2.4.0] Taxonomy Grouping, Classification, and Specialization
df = pd.read_sql(f"""
SELECT taxonomy_grouping, taxonomy_classification, taxonomy_specialization, count(*)
FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
WHERE canonical_rate IS NOT NULL
AND provider_type = 'Physician Group'
GROUP BY taxonomy_grouping, taxonomy_classification, taxonomy_specialization
LIMIT 1000
""", con=trino_conn)
print(df.to_markdown(index=False))

implant carveouts logic

payer_id	provider_id	device_dependent	device_price_inclusive	device_percentage	device_gross_charge	device_dollar	device_qualifier	device_source	canonical_rate	bundled_rate
522	2014	True	False	100	72942.1	72942.1	278	DevPR	83596.8	156539
522	6433	True	False	60	72942.1	43765.2	278	DevPR	84799.2	128564
522	6527	True	False	68	62411	42439.5	278	DevPR	75748.2	118188
76	1815	True	False	56.1	62411	35012.6	278	DevPR	159612	194624
522	6433	True	False	60	36066.4	21639.8	278	DevPR	25348.5	46988.3
403	6433	True	False	54.1	72942.1	39461.7	278	DevPR	84799.2	124261
7	6527	True	False	75	62411	46808.3	278	DevPR	30297	77105.3
76	6329	True	False	87	36066.4	31377.8	278	DevPR	36421.3	67799.1
7	6527	True	False	75	36066.4	27049.8	278	DevPR	30297	57346.8
403	6433	True	False	54.1	21502.8	11633	278	DevPR	11311.9	22944.9

Code

# check other guardant lab codes
df = pd.read_sql(f"""
SELECT 
    payer_id,
    provider_id,
    device_dependent,
    device_price_inclusive,
    device_percentage,
    device_gross_charge,
    device_dollar,
    device_qualifier,
    device_source,
    canonical_rate,
    bundled_rate
FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
WHERE 
    bundled_rate IS NOT NULL
    AND device_price_inclusive = False
    AND device_percentage IS NOT NULL
    AND device_gross_charge IS NOT NULL
LIMIT 10
""", con=trino_conn)
print(df.to_markdown(index=False))

SELECT COUNT(*)
FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
WHERE 
    bundled_rate IS NOT NULL
    AND device_price_inclusive = False
    AND device_percentage IS NOT NULL
    AND device_gross_charge IS NOT NULL
LIMIT 10

add best payer and hospital contract method

best_payer_contract_methodology	best_hospital_contract_methodology
Percent of Total Billed Charges	Case Rate
Fee Schedule	Case Rate
Fee Schedule	Case Rate
Fee Schedule	Case Rate
Fee Schedule	Case Rate
Fee Schedule	Case Rate
Fee Schedule	Case Rate
Fee Schedule	Case Rate
Percent of Total Billed Charges	Unknown
Fee Schedule	Case Rate

Code

df = pd.read_sql(f"""
SELECT best_payer_contract_methodology, best_hospital_contract_methodology
FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
WHERE canonical_rate IS NOT NULL
LIMIT 10
""", con=trino_conn)
print(df.to_markdown(index=False))

indicator device dependent procs

Code

# [CLD-v2.4.0] indicator device dependent procs
df = pd.read_sql(f"""
SELECT DISTINCT billing_code
FROM tq_dev.internal_dev_csong_cld_v2_4_0.prod_combined_abridged
WHERE canonical_rate IS NOT NULL
AND device_dependent = True
""", con=trino_conn)
df

Prod Features​

Taxonomy Grouping, Classification, and Specialization​

implant carveouts logic​

add best payer and hospital contract method​

indicator device dependent procs​

On this page:

Prod Features

Taxonomy Grouping, Classification, and Specialization

implant carveouts logic

add best payer and hospital contract method

indicator device dependent procs