TQ Scorecards

Methodology

---

Payer Scorecards

• hive.export.payer_mrf_scores

  Note: A newer version of this table is currently in development. By 4/11/24, we will be ready to switch to tq_production.mrf_scores.payer_mrf_scores and/or payer_mrf_network_scores.

  Further documentation to come.

Provider Scorecards

• hive.cld_utils.ref_hospital_scorecard_composite_scores

Moved table from Redshift to Trino:

python code

# %%
from IPython import get_ipython

if get_ipython() is not None:
    get_ipython().run_line_magic("load_ext", "autoreload")
    get_ipython().run_line_magic("autoreload", "2")
import os

import pandas as pd
import tqdm
from fuzzywuzzy import fuzz, process
from tqds.aws import utils as tu
from tqds.sql import db, sql

pd.options.display.max_columns = None
pd.options.display.max_rows = 500
trino_conn = db.TrinoClient().conn
redshift_conn = db.RedshiftClient().conn
query = sql.Query(TEMPLATE_DIR="../sql")

# %%
df = pd.read_sql(
    f"""
    SELECT *
    FROM datahouse.data_science.hospital_scorecard_composite_scores
""",
    con=redshift_conn,
)

# %%
df.to_csv("hospital_scorecard_composite_scores.csv", index=False)

query.execute_query(
    f"""
    DROP TABLE IF EXISTS hive.cld_utils.ref_hospital_scorecard_composite_scores_tmp
""",
    con=trino_conn,
)

query.execute_query(
    f"""
    DROP TABLE IF EXISTS hive.cld_utils.ref_hospital_scorecard_composite_scores
""",
    con=trino_conn,
)

tu.push_to_aws_s3(
    local_loc="hospital_scorecard_composite_scores.csv",
    dest_loc=f"s3://turquoise-health-payer-export-main/cld_utils/hospital_scorecard_composite_scores/hospital_scorecard_composite_scores.csv",
    capture_output=True,
)

tu.s3_to_trino(
    headers=df.columns,
    tablename=f"hive.cld_utils.ref_hospital_scorecard_composite_scores_tmp",
    s3_loc=f"s3://turquoise-health-payer-export-main/cld_utils/hospital_scorecard_composite_scores/",
    query=query,
    trino_conn=trino_conn,
)

query.execute_query(
    f"""
    CREATE TABLE hive.cld_utils.ref_hospital_scorecard_composite_scores AS
    SELECT 
        provider_id,
        CAST(total_primary_category_scores_unweighted_by_payer AS DECIMAL(18,10)) 
            AS total_primary_category_scores_unweighted_by_payer,
        CAST(composite_score AS DECIMAL(18,10)) AS composite_score,
        CAST(cash_price_score AS DECIMAL(18,10)) AS cash_price_score,
        CAST(list_price_score AS DECIMAL(10,2)) AS list_price_score,
        CAST(v2_score AS INT) AS v2_score,
        CAST(turquoise_star_status AS BOOLEAN) AS turquoise_star_status
    FROM hive.cld_utils.ref_hospital_scorecard_composite_scores_tmp
""",
    con=trino_conn,
)

query.execute_query(
    f"""
    DROP TABLE hive.cld_utils.ref_hospital_scorecard_composite_scores_tmp
""",
    con=trino_conn,
)

os.remove("hospital_scorecard_composite_scores.csv")

# %%