Quality

SchemaMethodology

---

CMS

• hospital_overall_rating from glue.hospital_data.price_transparency_provider

Leapfrog

Imported in lab to hive.labps.quality_leapfrog_hospital_ratings_v0. See code below:

See Python Code

import pandas as pd
from tqds.aws import utils as tu
from labps import utils as lu
from labps.settings import Settings

settings = Settings()

leapfrog = pd.read_excel(
    f"{settings.DATA_LOC}/leapfrog/HospitalSafetyGrade_DataLicensee_Spring2024_05282024.xlsx",
    sheet_name="Spring2024",
    dtype={
        "NPI": str,
        "Hospital Name": str,
        "Hospital Score": float,
        "Hospital Grade": str,
    },
)[
    [
        "NPI",
        "Hospital Name",
        "Hospital Score",
        "Hospital Grade",
    ]
]

leapfrog.columns = [
    "npi",
    "hospital_name",
    "hospital_score",
    "hospital_grade",
]

leapfrog.to_orc(f"{settings.DATA_LOC}/leapfrog/leapfrog_scores.orc", index=False)

tu.push_to_aws_s3(
    local_loc=f"{settings.DATA_LOC}/leapfrog/leapfrog_scores.orc", 
    dest_loc="s3://turquoise-health-payer-export-main/labps/leapfrog/leapfrog.orc", 
    capture_output=True
)

headers = [
    (col, "VARCHAR") 
    if col not in ["hospital_score"] 
    else (col, "DOUBLE")
    for col in leapfrog.columns
]

lu.build_orc_table_trino(
    headers=headers,
    tablename=f"hive.labps.quality_leapfrog_hospital_ratings_{version}",
    s3_loc="s3://turquoise-health-payer-export-main/labps/leapfrog/",
    trino_conn=trino_conn,
    query=query,
)