from enum import Enum
from typing import Final
from common.utils import get_param
from data_science.cld.core_licensable_data_sub_dag import views
from data_science.cld.core_licensable_data_sub_dag.utils.mappings import (
NON_TRANSFORMED_COLUMN_MAPS,
ORIGINAL_BILLING_CODE_MAPS,
ORIGINAL_BILLING_CODE_TYPE_MAPS,
)
from data_science.cld.core_licensable_data_sub_dag.utils.utils import build_sorted_cols
def get_cld_sub_dag_params(dag_run=None, params=None):
sql_loc = get_param('sql_loc', dag_run=dag_run, params=params)
version = get_param('version', dag_run=dag_run, params=params)
sub_version = get_param('sub_version', dag_run=dag_run, params=params)
schema_name = get_param('schema_name', dag_run=dag_run, params=params)
return (sql_loc, version, sub_version, schema_name)
class Tables(Enum):
"""
see documentation here: https://cld.turquoise.health/components/reference/
"""
UNIFIED_PROVIDER_TABLE: Final = "tq_production.reference_legacy.unified_provider_table"
PROVIDER_DEMOGRAPHICS_TABLE: Final = "tq_production.reference_legacy.provider_demographics"
TAXONOMY_PROCEDURE_PLAUSIBILITY: Final = "tq_production.reference_internal.taxonomy_procedure_plausibility"
RATE_OUTLIER_TABLE: Final = "tq_intermediate.cld_utils.outlier_bounds_negotiated_rate_2025_09_26"
CASH_OUTLIER_TABLE: Final = "tq_intermediate.cld_utils.outlier_bounds_cash_price_2025_08_14"
ASC_MEDICARE_REFERENCE_TABLE: Final = "tq_dev.internal_dev_csong_sandbox.asc_reference_pricing"
IPPS_MEDICARE_REFERENCE_TABLE: Final = "tq_dev.internal_dev_csong_sandbox.ipps_reference_pricing"
APRDRG_XWALKED_IPPS_REFERENCE_TABLE: Final = "tq_intermediate.cld_utils.ref_cms_aprdrg_xwalked_ipps_pricing_2025_09_19"
TMP_OPPS_MEDICARE_REFERENCE_TABLE: Final = "tq_production.reference_internal.opps_reference_pricing"
OPPS_MEDICARE_REFERENCE_TABLE: Final = "tq_dev.internal_dev_csong_sandbox.opps_reference_pricing"
OPPS_ADDENDUM_B: Final = "tq_production.reference_internal.opps_addendum_b"
MPFS_MEDICARE_REFERENCE_TABLE: Final = "tq_production.reference_internal.physician_reference_pricing"
MPFS_RVU: Final = "tq_production.reference_legacy.ref_cms_pfs_rvu"
ANESTHESIA_REFERENCE_PRICING: Final = "tq_production.reference_internal.anesthesia_reference_pricing"
ANESTHESIA_BASE_UNITS: Final = "tq_intermediate.cld_utils.ref_anesthesia_base_units"
CLINICAL_LAB_REFERENCE_PRICING: Final = "tq_production.reference_internal.clinical_laboratory_reference_pricing"
ASP_PRICING_TABLE: Final = "tq_production.reference_internal.asp_reference_pricing"
MSDRG_WEIGHTS_TABLE: Final = "tq_intermediate.cld_utils.msdrg_weights_by_year"
REF_CMS_MSDRG_WEIGHTS: Final = "tq_production.reference_internal.ipps_msdrg"
REF_CMS_ZIP_CARRIER_LOCALITY: Final = "tq_production.reference_legacy.ref_cms_zip_carrier_locality"
GROSS_CHARGES_NPI: Final = "tq_intermediate.cld_utils.claims_benchmarks_gross_charges_npi_2025_09"
GROSS_CHARGES_CBSA: Final = "tq_intermediate.cld_utils.claims_benchmarks_gross_charges_cbsa_2025_09"
GROSS_CHARGES_STATE: Final = "tq_intermediate.cld_utils.claims_benchmarks_gross_charges_state_2025_09"
UTILIZATION_NPI: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_utilization_npi"
UTILIZATION_STATE_PAYERCHANNEL: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_utilization_state_payerchannel"
UTILIZATION_NATIONAL: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_utilization_national"
UTILIZATION_NATIONAL_PAYERCHANNEL: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_utilization_national_payerchannel"
UTILIZATION_NATIONAL_PAYER: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_utilization_national_payer"
ALLOWABLES_NPI_PAYER: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_allowable_npi_payer"
ALLOWABLES_NATIONAL_PAYERCHANNEL: Final = "hive.claims_benchmarks.claims_benchmarks_allowable_national_payerchannel"
ALLOWABLES_ZIP3_PAYER: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_allowable_zip3_payer"
ALLOWABLES_CBSA_PAYER: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_allowable_cbsa_payer"
ALLOWABLES_STATE_PAYER: Final = "tq_intermediate.claims_benchmarks.claims_benchmarks_allowable_state_payer"
CLAIMS_MEDICAL_HEADERS: Final = "tq_intermediate.external_komodo.medical_headers"
CLAIMS_MEDICAL_SERVICE_LINES: Final = "tq_intermediate.external_komodo.medical_service_lines"
POLICY_REPORTER_COUNTY: Final = "tq_production.reference_legacy.policy_reporter_county"
POLICY_REPORTER_CBSA_XWALK: Final = "tq_intermediate.cld_utils.ref_policy_reporter_cbsa_xwalk"
CSTM_ALL_XWALK_TABLE: Final = "tq_intermediate.cld_utils.cstm_all"
APR_DRG_XWALK_TABLE: Final = "tq_production.reference_legacy.drg_crosswalk"
HCPCS_EAPG_CROSSWALK_TABLE: Final = "tq_intermediate.cld_utils.hcpcs_eapg_crosswalk"
OPG_SCHEDULES_UNITED: Final = "tq_intermediate.cld_utils.contract_surg_grouper_united_v0"
OPG_SCHEDULES_AETNA: Final = "tq_intermediate.cld_utils.contract_surg_grouper_aetna_v0"
OPG_SCHEDULES_CIGNA: Final = "tq_intermediate.cld_utils.contract_surg_grouper_cigna_v0"
OPG_SCHEDULES_BCBS_CA: Final = "tq_intermediate.cld_utils.contract_surg_grouper_bcbs_ca_v0"
OPG_SCHEDULES_CAREFIRST: Final = "tq_intermediate.cld_utils.contract_surg_grouper_carefirst_v0"
OPG_SCHEDULES_ANTHEM_CA: Final = "tq_intermediate.cld_utils.contract_surg_grouper_anthem_ca_v0"
OPG_SCHEDULES_FLORIDA_BLUE: Final = "tq_intermediate.cld_utils.contract_surg_grouper_florida_blue_v0"
LEAPFROG_HOSPITAL_RATINGS: Final = "tq_intermediate.cld_utils.quality_leapfrog_hospital_ratings_v0"
CAREJOURNEY_HOSPITAL_SCORES: Final = "tq_production.reference_internal.carejourney_hospital_scores"
NASHP_HOSPITAL_COST_REPORT: Final = "tq_intermediate.cld_utils.nashp_hospital_cost_report_v1"
DSH_SUPPLEMENT_TABLE: Final = "tq_intermediate.cld_utils.ipps_medicare_dsh_supplemental_file_2025"
RC_MSDRG_AFFILIATIONS: Final = "tq_intermediate.cld_utils.rc_msdrg_affiliations"
RC_GROUP_FAMILY: Final = "tq_intermediate.cld_utils.rc_group_family"
COST_REPORT_CHARGE_RELATIVITIES: Final = "tq_intermediate.cld_utils.cost_report_charge_relativities_2025_04_03"
RATE_TYPE_CATEGORIES: Final = "tq_intermediate.cld_utils.rate_type_categories"
CLD_PHYSICIAN_GROUP_IDS: Final = "tq_intermediate.cld_utils.cld_pg_ids_2025_09_13"
CLD_NON_AFFILIATED_PHYSICIAN_GROUP_IDS: Final = "tq_intermediate.cld_utils.cld_pg_ids_nonaffiliated_standalone_2025_09_13"
CLD_PAC_TIN_EDGE_STATS: Final = "tq_dev.internal_dev_csong_sandbox.prod_pac_tin_edges"
CLD_PHYSICIAN_GROUP_HCO_CONNECTIONS: Final = "tq_dev.internal_dev_csong_sandbox.prod_pac_connections"
CLD_PHYSICIAN_GROUP_CODESET: Final = "tq_intermediate.cld_utils.pg_codeset_2025_08_24"
OUTPATIENT_CODESET: Final = "tq_intermediate.cld_utils.outpatient_codeset_2025_08_15"
DPR_IMPUTED_UTILIZATIONS: Final = "tq_intermediate.cld_utils.dpr_imputed_utilization_20250321"
CMV_INFO: Final = "tq_production.cmv.cmv_info"
CMV_FF: Final = "tq_production.cmv.cmv_ff"
REF_ASP_HCPCS_NDC_CROSSWALK: Final = "tq_intermediate.cld_utils.ref_asp_hcpcs_ndc_crosswalk"
DRUG_THERAPEUTIC_AREAS: Final = "tq_intermediate.cld_utils.drug_therapeutic_areas"
REF_CMS_CMI: Final = "tq_intermediate.cld_utils.ref_cms_cmi"
CLINICAL_CATEGORIES_REF: Final = "tq_production.reference_legacy.clinical_categories_ref"
CLINICAL_CATEGORIES_INDEX: Final = "tq_production.reference_legacy.clinical_categories_index"
AMSURG_ASC_PROVIDER_TYPES: Final = "tq_intermediate.cld_utils.amsurg_asc_provider_types"
DEFINITIVE_HOSPITAL_CLAIMS_BASED_PAYOR_MIX: Final = "tq_raw.definitive.hospital_claims_based_payor_mix"
DEFINITIVE_HOSPITAL_OVERVIEW: Final = "tq_raw.definitive.hospital_overview"
DEFINITIVE_HOSPITAL_XWALK: Final = "tq_intermediate.cld_utils.price_transparency_tq_definitive_xwalk"
SPINES_GEO_CBSA: Final = "tq_production.spines.spines_geo_cbsa"
SPINES_GEO_STATE: Final = "tq_production.spines.spines_geo_state"
SPINES_GEO_RELATIONSHIPS: Final = "tq_production.spines.spines_geo_relationships"
SPINES_GEO_FLATTENED: Final = "tq_production.spines.spines_geo_denormalized"
SPINES_PROVIDER_HEALTH_SYSTEMS: Final = "tq_production.spines.spines_provider_healthsystems"
SPINES_PROVIDER_INTERMEDIATE_NUCC_LATEST: Final = "tq_dev.spines.spines_provider_intermediate_nucc_latest"
SPINES_PROVIDER_INTERMEDIATE_DEFINITIVE_ASC_DATA: Final = "tq_dev.spines.spines_provider_intermediate_definitive_asc_data"
SPINES_PROVIDER_ASC: Final = "tq_production.spines.spines_provider_ascs"
SPINES_PROVIDER_ASC_ADDITIONAL_NPIS: Final = "tq_production.spines.spines_provider_ascs_additional_npis"
SPINES_PROVIDER_IMAGING_CENTERS: Final = "tq_production.spines.spines_provider_imaging_centers"
SPINES_PROVIDER_INTERMEDIATE_DEFINITIVE_IMAGING_CENTER_DATA: Final = "tq_dev.spines.spines_provider_intermediate_definitive_imaging_center_data"
SPINES_PROVIDER_HOSPITAL: Final = "tq_production.spines.spines_provider_hospitals"
SPINES_PROVIDER_HOSPITAL_ADDITIONAL_NPIS: Final = "tq_production.spines.spines_provider_hospitals_additional_npis"
SPINES_SERVICES: Final = "tq_production.spines.spines_services"
SPINES_SERVICES_RELATIONSHIPS: Final = "tq_dev.spines.spines_services_relationships"
SPINES_SERVICES_CLINICAL_CATEGORIES: Final = "tq_dev.spines.spines_services_clinical_categories"
SPINES_PAYER: Final = "tq_production.spines.spines_payer"
PAYER_MRF_SCORES: Final = "tq_production.mrf_scores.payer_mrf_scores"
PAYER_MRF_STATE_SCORES: Final = "tq_production.mrf_scores.payer_mrf_state_scores"
PAYER_MRF_NETWORK_SCORES: Final = "tq_production.mrf_scores.payer_mrf_network_scores"
PAYER_MRF_NETWORK_STATE_SCORES: Final = "tq_production.mrf_scores.payer_mrf_network_state_scores"
PAYER_MRF_EXPECTED_GEOGRAPHIES: Final = "tq_production.mrf_scores.payer_mrf_expected_geographies"
HOSPITAL_SCORECARD_COMPOSITE_SCORES: Final = "tq_production.mrf_scores.hospital_scorecard_composite_scores"
SSP_SUB_PACKAGES: Final = "tq_dev.standard_service_packages.ssp_sub_packages"
SSP_PACKAGE_CONTENTS: Final = "tq_dev.standard_service_packages.sub_package_contents"
MSDRG_WEIGHTS_YEAR_RANGE_START = 2015
MSDRG_WEIGHTS_YEAR_RANGE_END = 2025
SCORES = [
7,
6,
5,
4,
1,
0
]
MIN_NON_OUTLIER_RAW_SCORE = 0
IMPUTATION_SCORES = [
None,
6,
3,
2,
1,
0,
]
MAX_BEST_SCORE = max(SCORES)
VALIDATION_MAPPING = {
7: 'payer + hospital mrf validated',
6: 'raw: not an outlier',
5: 'hospital-mrf gross charge w/ MRF PoC or state-level medicare benchmarks',
4: 'not-validated; not an outlier',
1: 'outlier',
3: 'hospital-mrf gross charge w/ MRF PoC or state-level medicare benchmarks',
2: 'not-validated; not an outlier',
1: 'outlier',
}
CONFIDENCE_SCORES = {
7:5,
6:4,
5:3,
4:2,
3: 3,
2: 2,
1: 1
}
GROSS_CHARGE_ASP_LOWER_BOUND = 2.00
GROSS_CHARGE_ASP_UPPER_BOUND = 22.00
PAYER_SPINE_COLS = [
'payer_name'
]
NETWORK_SPINE_COLS = [
'network_name',
'network_type',
'network_class',
'payer_data_network'
]
PROV_SPINE_COLS = [
'provider_name', 'health_system_name', 'health_system_id', 'provider_type',
'total_beds', 'npi', 'ein',
'medicare_provider_id',
'hq_latitude', 'hq_longitude',
'city', 'zip_code', 'state', 'cbsa', 'cbsa_name', 'county',
'taxonomy_grouping'
]
CODE_SPINE_COLS = [
'billing_code_full',
'service_description',
'service_line',
'therapeutic_area',
'drug_name_array',
'is_drug_code',
'is_surg_code',
]
GROSS_CHARGE_COLS = [
'mrf_gross_charge_provider',
'komodo_gross_charge_provider',
'mrf_gross_charge_state_median',
'komodo_gross_charge_cbsa_median',
'komodo_gross_charge_state_median',
'mrf_gross_charge_cbsa_median',
]
CASH_COLS = [
'discounted_cash_rate',
]
ALLOWED_AMOUNT_STATS = [
'count_encounters_primary_line_item',
'count_encounters_primary_claim'
]
HOSPITAL_RAW_COLS = [
'hospital_case_rate_dollar',
'hospital_percent_of_total_billed_charges_dollar',
'hospital_fee_schedule_dollar',
'hospital_other_dollar',
'hospital_null_methodology_dollar',
'hospital_case_rate_untransformed_dollar',
'hospital_percent_of_total_billed_charges_untransformed_dollar',
'hospital_fee_schedule_untransformed_dollar',
'hospital_other_untransformed_dollar',
'hospital_null_methodology_untransformed_dollar',
'hospital_case_rate_allowed_amount',
'hospital_percent_of_total_billed_charges_allowed_amount',
'hospital_fee_schedule_allowed_amount',
'hospital_per_diem_allowed_amount',
'hospital_other_allowed_amount',
'hospital_null_methodology_allowed_amount',
'hospital_case_rate_untransformed_allowed_amount',
'hospital_percent_of_total_billed_charges_untransformed_allowed_amount',
'hospital_fee_schedule_untransformed_allowed_amount',
'hospital_per_diem_untransformed_allowed_amount',
'hospital_other_untransformed_allowed_amount',
'hospital_null_methodology_untransformed_allowed_amount',
]
PAYER_RAW_COLS = [
'payer_negotiated_rate',
'payer_derived_rate',
'payer_fee_schedule_rate',
'payer_negotiated_untransformed_rate',
'payer_derived_untransformed_rate',
'payer_fee_schedule_untransformed_rate',
]
ORIGINAL_NONTRANSFORMED_COLS = [
'payer_percentage_rate',
'hospital_case_rate_percentage',
'hospital_percent_of_total_billed_charges_percentage',
'hospital_fee_schedule_percentage',
'hospital_other_percentage',
'hospital_null_methodology_percentage',
'hospital_per_diem_percentage',
'payer_per_diem_rate',
'hospital_per_diem_dollar',
]
HOSPITAL_TRANSFORMATION_COLS = [
'hosp_per_diem_mult_glos',
'hospital_case_rate_gc_hosp_perc_to_dol',
'hospital_case_rate_gc_hosp_cbsa_perc_to_dol',
'hospital_case_rate_gc_hosp_state_perc_to_dol',
'hospital_case_rate_gc_komodo_perc_to_dol',
'hospital_case_rate_gc_komodo_cbsa_perc_to_dol',
'hospital_case_rate_gc_komodo_state_perc_to_dol',
'hospital_perc_of_total_billed_charges_gc_hosp_perc_to_dol',
'hospital_perc_of_total_billed_charges_gc_hosp_cbsa_perc_to_dol',
'hospital_perc_of_total_billed_charges_gc_hosp_state_perc_to_dol',
'hospital_perc_of_total_billed_charges_gc_komodo_perc_to_dol',
'hospital_perc_of_total_billed_charges_gc_komodo_cbsa_perc_to_dol',
'hospital_perc_of_total_billed_charges_gc_komodo_state_perc_to_dol',
'hospital_fee_schedule_gc_hosp_perc_to_dol',
'hospital_fee_schedule_gc_hosp_cbsa_perc_to_dol',
'hospital_fee_schedule_gc_hosp_state_perc_to_dol',
'hospital_fee_schedule_gc_komodo_perc_to_dol',
'hospital_fee_schedule_gc_komodo_cbsa_perc_to_dol',
'hospital_fee_schedule_gc_komodo_state_perc_to_dol',
'hospital_other_gc_hosp_perc_to_dol',
'hospital_other_gc_hosp_cbsa_perc_to_dol',
'hospital_other_gc_hosp_state_perc_to_dol',
'hospital_other_gc_komodo_perc_to_dol',
'hospital_other_gc_komodo_cbsa_perc_to_dol',
'hospital_other_gc_komodo_state_perc_to_dol',
'hospital_null_methodology_gc_hosp_perc_to_dol',
'hospital_null_methodology_gc_hosp_cbsa_perc_to_dol',
'hospital_null_methodology_gc_hosp_state_perc_to_dol',
'hospital_null_methodology_gc_komodo_perc_to_dol',
'hospital_null_methodology_gc_komodo_cbsa_perc_to_dol',
'hospital_null_methodology_gc_komodo_state_perc_to_dol',
'hosp_gc_hosp_per_diem_perc_to_dol_mult_glos',
'hosp_gc_hosp_cbsa_per_diem_perc_to_dol_mult_glos',
'hosp_gc_hosp_state_per_diem_perc_to_dol_mult_glos',
'hosp_gc_komodo_per_diem_perc_to_dol_mult_glos',
'hosp_gc_komodo_cbsa_per_diem_perc_to_dol_mult_glos',
'hosp_gc_komodo_state_per_diem_perc_to_dol_mult_glos',
'drug_dosage_std_case_rate_dollar',
'drug_dosage_std_percent_of_total_billed_charges_dollar',
'drug_dosage_std_fee_schedule_dollar',
'drug_dosage_std_other_dollar',
'drug_dosage_std_null_methodology_dollar',
'drug_dosage_std_case_rate_perc_to_dol',
'drug_dosage_std_percent_of_total_billed_charges_perc_to_dol',
'drug_dosage_std_fee_schedule_perc_to_dol',
'drug_dosage_std_other_perc_to_dol',
'drug_dosage_std_null_methodology_perc_to_dol',
]
PAYER_TRANSFORMATION_COLS = [
'payer_gc_hosp_perc_to_dol',
'payer_gc_hosp_cbsa_perc_to_dol',
'payer_gc_hosp_state_perc_to_dol',
'payer_gc_komodo_perc_to_dol',
'payer_gc_komodo_cbsa_perc_to_dol',
'payer_gc_komodo_state_perc_to_dol',
'payer_negotiated_rate_anesthesia_cf',
'payer_derived_rate_anesthesia_cf',
'payer_fee_schedule_rate_anesthesia_cf',
'payer_per_diem_mult_glos',
]
DERIVED_IMPUTATION_COLS = [
'msdrg_mrf_base_rate_mult_cms_weight',
'msdrg_base_rate_mult_cms_weight',
'rc_family_gc_hosp_perc_to_dol',
'rc_family_gc_komodo_perc_to_dol',
'rc_family_gc_komodo_cbsa_perc_to_dol',
'rc_family_gc_hosp_cbsa_perc_to_dol',
'rc_family_gc_hosp_state_perc_to_dol',
'rc_family_gc_komodo_state_perc_to_dol',
'rc_family_per_diem_mult_glos',
'msdrg_gc_hosp_base_perc_to_dol',
'msdrg_gc_komodo_base_perc_to_dol',
'msdrg_gc_hosp_cbsa_base_perc_to_dol',
'msdrg_gc_komodo_cbsa_base_perc_to_dol',
'msdrg_gc_hosp_state_base_perc_to_dol',
'msdrg_gc_komodo_state_base_perc_to_dol',
'rc_global_gc_hosp_base_perc_to_dol',
'rc_global_gc_hosp_cbsa_base_perc_to_dol',
'rc_global_gc_hosp_state_base_perc_to_dol',
'rc_global_gc_komodo_base_perc_to_dol',
'rc_global_gc_komodo_cbsa_base_perc_to_dol',
'rc_global_gc_komodo_state_base_perc_to_dol',
'rc_global_per_diem_mult_glos',
'opg_base_rate',
'op_gc_komodo_base_perc_to_dol',
'op_gc_komodo_cbsa_base_perc_to_dol',
'op_gc_komodo_state_base_perc_to_dol',
'op_gc_hosp_base_perc_to_dol',
'op_gc_hosp_cbsa_base_perc_to_dol',
'op_gc_hosp_state_base_perc_to_dol',
]
CSTM_IMPUTATION_COLS = [
'cstm_negotiated_rate',
'cstm_gc_hosp_perc_to_dol',
'cstm_gc_hosp_cbsa_perc_to_dol',
'cstm_gc_hosp_state_perc_to_dol',
'cstm_gc_komodo_perc_to_dol',
'cstm_gc_komodo_cbsa_perc_to_dol',
'cstm_gc_komodo_state_perc_to_dol',
'cstm_per_diem_mult_glos',
]
POSTED_IMPUTATION_COLS = [
'msdrg_mrf_base_rate_mult_cms_weight',
]
IMPUTATION_COLS = (
DERIVED_IMPUTATION_COLS +
CSTM_IMPUTATION_COLS + [
]
)
IMPUTATION_COLS_NO_POSTED = [
col for col in IMPUTATION_COLS if col not in POSTED_IMPUTATION_COLS
]
INPATIENT_COLS_RAW_TRANSFORM, INPATIENT_COLS_FULL = build_sorted_cols(
posted_imputation_cols=POSTED_IMPUTATION_COLS,
hospital_raw_cols=HOSPITAL_RAW_COLS,
payer_raw_cols=PAYER_RAW_COLS,
hospital_transformation_cols=HOSPITAL_TRANSFORMATION_COLS,
payer_transformation_cols=PAYER_TRANSFORMATION_COLS,
imputation_cols=IMPUTATION_COLS_NO_POSTED,
is_inpatient=True
)
NON_INPATIENT_COLS_RAW_TRANSFORM, NON_INPATIENT_COLS_FULL = build_sorted_cols(
posted_imputation_cols=POSTED_IMPUTATION_COLS,
hospital_raw_cols=HOSPITAL_RAW_COLS,
payer_raw_cols=PAYER_RAW_COLS,
hospital_transformation_cols=HOSPITAL_TRANSFORMATION_COLS,
payer_transformation_cols=PAYER_TRANSFORMATION_COLS,
imputation_cols=IMPUTATION_COLS_NO_POSTED,
is_inpatient=False
)
DERIVED_IMPUTATION_STATS_COLS = [
'msdrg_cms_weight',
'msdrg_cms_fy',
'msdrg_base_rate',
'msdrg_n_freq',
'msdrg_n_total',
'msdrg_mrf_cms_weight',
'msdrg_mrf_cms_fy',
'msdrg_mrf_base_rate',
'msdrg_mrf_n_freq',
'msdrg_mrf_n_total',
'msdrg_percentage_candidate_base_rate',
'msdrg_percentage_n_freq',
'msdrg_percentage_n_total',
'rc_family_percentage_rate',
'rc_family_perc',
'rc_family_per_diem_rate',
'rc_family_per_diem',
'rc_global_inpatient_base_rate',
'rc_global_inpatient_billing_code_array',
'rc_global_inpatient_per_diem_base_rate',
'opg',
'opg_candidate_base_rate',
'opg_n_freq',
'opg_n_total',
'opg_n_total_possible',
'op_percentage_candidate_base_rate',
'op_percentage_n_freq',
'op_percentage_n_total',
]
CSTM_IMPUTATION_STATS_COLS = [
'cstm_percentage_rate',
'cstm_per_diem_rate',
'cstm_original_billing_code',
'cstm_additional_information',
]
IMPUTATION_STATS_COLS = (
DERIVED_IMPUTATION_STATS_COLS +
CSTM_IMPUTATION_STATS_COLS + [
]
)
UTILIZATION_COLS = [
'npi_commercial_code_encounters',
'state_commercial_code_taxonomy_encounters',
'national_commercial_code_taxonomy_encounters',
'clear_market_utilization_commercial',
'clear_market_utilization_medicare',
'clear_market_utilization_medicaid'
]
GROSS_CHARGE_STATS = [
'mrf_gross_charge_provider_original_billing_code_type',
'mrf_gross_charge_provider_original_billing_code',
'gross_charge_cbsa_provider_adjustment',
'gross_charge_state_provider_adjustment',
'komodo_gross_charge_provider_count_encounters',
'komodo_gross_charge_cbsa_median_count_encounters',
'komodo_gross_charge_state_median_count_encounters',
'mrf_gross_charge_provider_dosage_standardization_params',
'komodo_gross_charge_provider_dosage_standardization_params',
'komodo_gross_charge_cbsa_median_dosage_standardization_params',
'komodo_gross_charge_state_median_dosage_standardization_params'
]
TRANSFORMATION_COLS = [
'medicare_glos',
'drug_dosage_std_case_rate_parsed_quantity',
'drug_dosage_std_percent_of_total_billed_charges_parsed_quantity',
'drug_dosage_std_fee_schedule_parsed_quantity',
'drug_dosage_std_other_parsed_quantity',
'drug_dosage_std_null_methodology_parsed_quantity',
'drug_dosage_std_case_rate_asp_quantity',
'drug_dosage_std_percent_of_total_billed_charges_asp_quantity',
'drug_dosage_std_fee_schedule_asp_quantity',
'drug_dosage_std_other_asp_quantity',
'drug_dosage_std_null_methodology_asp_quantity',
'drug_dosage_std_case_rate_parsed_unit_label',
'drug_dosage_std_percent_of_total_billed_charges_parsed_unit_label',
'drug_dosage_std_fee_schedule_parsed_unit_label',
'drug_dosage_std_other_parsed_unit_label',
'drug_dosage_std_null_methodology_parsed_unit_label',
'drug_dosage_std_case_rate_asp_unit_label',
'drug_dosage_std_percent_of_total_billed_charges_asp_unit_label',
'drug_dosage_std_fee_schedule_asp_unit_label',
'drug_dosage_std_other_asp_unit_label',
'drug_dosage_std_null_methodology_asp_unit_label',
'payer_negotiated_anesthesia_conversion_method',
'payer_derived_anesthesia_conversion_method',
'payer_fee_schedule_anesthesia_conversion_method',
'anesthesia_base_units',
]
BENCHMARK_COLS = [
'asp_payment_limit',
'medicare_rate',
'medicare_pricing_type',
'medicare_reference_source',
'state_avg_medicare_rate',
'national_avg_medicare_rate',
'cbsa_avg_medicare_rate',
'asc_avg_medicare_rate',
'lab_avg_medicare_rate',
'state_30_primary_claim',
'state_50_primary_claim',
'state_70_primary_claim',
'state_30_primary_line_item',
'state_50_primary_line_item',
'state_70_primary_line_item',
'state_30_secondary_line_item',
'state_50_secondary_line_item',
'state_70_secondary_line_item',
'state_count_encounters',
'cbsa_30_primary_claim',
'cbsa_50_primary_claim',
'cbsa_70_primary_claim',
'cbsa_30_primary_line_item',
'cbsa_50_primary_line_item',
'cbsa_70_primary_line_item',
'cbsa_30_secondary_line_item',
'cbsa_50_secondary_line_item',
'cbsa_70_secondary_line_item',
'cbsa_count_encounters',
'provider_id_30_primary_claim',
'provider_id_50_primary_claim',
'provider_id_70_primary_claim',
'provider_id_30_primary_line_item',
'provider_id_50_primary_line_item',
'provider_id_70_primary_line_item',
'provider_id_30_secondary_line_item',
'provider_id_50_secondary_line_item',
'provider_id_70_secondary_line_item',
'provider_id_count_encounters'
]
OUTLIER_BOUNDS = [
'lower_bound', 'upper_bound',
'lower_bound_type', 'upper_bound_type',
'median', 'stddev', 'use_validated_distribution_for_accuracy'
]
PAYER_WHISPERS = [
'payer_overall_score', 'payer_mrf_ip_hospital_score', 'payer_mrf_op_hospital_score',
'payer_mrf_hospital_score', 'payer_mrf_asc_score', 'payer_mrf_imaging_center_score',
'payer_overall_score_state', 'payer_mrf_ip_hospital_score_state', 'payer_mrf_op_hospital_score_state',
'payer_mrf_hospital_score_state', 'payer_mrf_asc_score_state', 'payer_mrf_imaging_center_score_state',
'cbsa_payer_covered_lives', 'cbsa_payer_covered_lives_market_share', 'cbsa_payer_covered_lives_rank',
'state_payer_covered_lives', 'state_payer_covered_lives_market_share', 'state_payer_covered_lives_rank',
'national_payer_covered_lives', 'national_payer_covered_lives_market_share', 'national_payer_covered_lives_rank'
]
NETWORK_WHISPERS = [
'payer_network_overall_score', 'payer_network_mrf_ip_hospital_score', 'payer_network_mrf_op_hospital_score',
'payer_network_mrf_hospital_score', 'payer_network_mrf_asc_score', 'payer_network_mrf_imaging_center_score',
'payer_network_mrf_hospital_score_state', 'payer_network_overall_score_state', 'payer_network_mrf_ip_hospital_score_state', 'payer_network_mrf_op_hospital_score_state',
'payer_network_mrf_asc_score_state', 'payer_network_mrf_imaging_center_score_state',
]
PROVIDER_WHISPERS = [
'hospital_scorecard_composite_score', 'hospital_scorecard_cash_price_score',
'hospital_scorecard_list_price_score', 'hospital_scorecard_v2_score',
'provider_hospital_ownership', 'provider_market_concentration_index',
'provider_geographic_classification',
'provider_is_340b_hospital',
'provider_340b_hospital_type',
'number_staffed_beds', 'number_licensed_beds',
'provider_net_patient_revenue', 'provider_pct_of_cbsa_net_patient_revenue',
'provider_pct_of_state_net_patient_revenue',
'provider_pct_of_national_net_patient_revenue',
'provider_net_patient_revenue_cbsa_rank',
'provider_net_patient_revenue_state_rank',
'provider_net_patient_revenue_national_rank',
'provider_cms_overall_rating',
'provider_leapfrog_score', 'provider_leapfrog_grade',
'nashp_net_profit_margin', 'nashp_commercial_breakeven',
'nashp_charity_mix', 'nashp_medicaid_mix',
'nashp_medicare_mix', 'nashp_commercial_payer_mix',
'nashp_total_drug_costs', 'nashp_total_drug_charges',
'nashp_drug_charge_ratio', 'nashp_inpatient_drug_charges',
'nashp_outpatient_drug_charges',
'cmi',
'provider_amc',
'cj_hospital_provider_count_physician',
'cj_hospital_provider_count_non_physician',
'cj_hospital_provider_count_unknown_type',
'cj_hospital_provider_count_all',
'cj_hospital_avg_quality_score_physician',
'cj_hospital_avg_quality_score_non_physician',
'cj_hospital_avg_cost_score_physician',
'cj_hospital_cost_score_non_physician',
'cj_hospital_avg_quality_score_all',
'cj_hospital_avg_cost_score_all',
]
CODE_WHISPERS = [
'code_national_avg_canonical_rate',
'code_national_avg_number_of_claims',
'code_national_estimated_revenue',
'rank_code_canonical_rate',
'rank_code_revenue',
'rank_code_claims'
]
PROVIDER_CODE_WHISPERS = [
'provider_code_total_estimated_revenue',
'provider_code_total_claims',
'provider_code_avg_canonical_rate',
'rank_code_within_provider_over_revenue',
'rank_code_within_provider_over_claims',
'rank_code_within_provider_over_canonical_rate'
]
PROVIDER_PAYER_WHISPERS = [
'within_provider_payer_pct_of_total_claims',
'within_provider_payer_pct_of_total_charges',
'within_provider_payer_payer_rank'
]
PAYER_CODE_WHISPERS = [
'payer_code_avg_canonical_rate',
'payer_code_avg_number_of_claims',
'payer_code_estimated_revenue',
'rank_payer_code_canonical_rate',
'rank_payer_code_revenue',
'rank_payer_code_claims'
]
PROVIDER_PAYER_NETWORK_WHISPERS = [
'provider_network_msdrg_base_rate',
]