EL: Add'l Notes Fields

Using hospital data additional_generic_notes, additional_payer_notes, and plan_name, we can sometimes parse out Medicare Advantage percentage of Medicare.

Only Baptist Health, Memorial Hermann, and Vanderbilt have these fields populated with relevant data.

Out of the 89 hospitals in these three health systems, 64 (72%) have a multiplier of 100% (i.e. MA pays 100% of Medicare). This is a non-random sample, but it does align with what we've heard from SMEs at TQ.

Baptist

provider_healthsystem_name	facility_name	payer_name	provider_id	payer_id	multiplier
Baptist Health	Baptist Health Corbin	Anthem	3240	42	1
Baptist Health	Baptist Health Corbin	Humana	3240	510	1
Baptist Health	Baptist Health Corbin	United Healthcare	3240	643	1
Baptist Health	Baptist Health Floyd	Aetna	2640	7	1
Baptist Health	Baptist Health Floyd	Anthem	2640	42	1
Baptist Health	Baptist Health Floyd	Humana	2640	510	1
Baptist Health	Baptist Health Floyd	United Healthcare	2640	643	0.95
Baptist Health	Baptist Health Hardin	Aetna	3245	7	1
Baptist Health	Baptist Health Hardin	Anthem	3245	42	1
Baptist Health	Baptist Health Hardin	Humana	3245	510	1
Baptist Health	Baptist Health Hardin	United Healthcare	3245	643	1
Baptist Health	Baptist Health La Grange	Aetna	3267	7	1
Baptist Health	Baptist Health La Grange	Anthem	3267	42	1
Baptist Health	Baptist Health La Grange	Humana	3267	510	1
Baptist Health	Baptist Health La Grange	United Healthcare	3267	643	1
Baptist Health	Baptist Health Lexington	Aetna	3271	7	1
Baptist Health	Baptist Health Lexington	Anthem	3271	42	1
Baptist Health	Baptist Health Lexington	Humana	3271	510	1.02
Baptist Health	Baptist Health Lexington	United Healthcare	3271	643	1
Baptist Health	Baptist Health Louisville	Aetna	3266	7	1
Baptist Health	Baptist Health Louisville	Anthem	3266	42	1
Baptist Health	Baptist Health Louisville	Humana	3266	510	1
Baptist Health	Baptist Health Louisville	United Healthcare	3266	643	1
Baptist Health	Baptist Health Paducah	Anthem	3310	42	1
Baptist Health	Baptist Health Paducah	Humana	3310	510	1.02
Baptist Health	Baptist Health Paducah	United Healthcare	3310	643	1
Baptist Health	Baptist Health Richmond	Aetna	3318	7	1
Baptist Health	Baptist Health Richmond	Anthem	3318	42	1
Baptist Health	Baptist Health Richmond	Humana	3318	510	1
Baptist Health	Baptist Health Richmond	United Healthcare	3318	643	1

Memorial Hermann

provider_healthsystem_name	facility_name	payer_name	provider_id	payer_id	multiplier
Memorial Hermann Health System	Memorial Hermann - Texas Medical Center	Aetna	5390	7	100
Memorial Hermann Health System	Memorial Hermann - Texas Medical Center	Humana	5390	510	100
Memorial Hermann Health System	Memorial Hermann - Texas Medical Center	Kaiser Permanente	5390	522	100
Memorial Hermann Health System	Memorial Hermann - Texas Medical Center	United Healthcare	5390	643	105
Memorial Hermann Health System	Memorial Hermann Cypress Hospital	Aetna	6857	7	100
Memorial Hermann Health System	Memorial Hermann Cypress Hospital	Humana	6857	510	100
Memorial Hermann Health System	Memorial Hermann Cypress Hospital	Kaiser Permanente	6857	522	100
Memorial Hermann Health System	Memorial Hermann Cypress Hospital	United Healthcare	6857	643	105
Memorial Hermann Health System	Memorial Hermann Greater Heights Hospital	Aetna	5395	7	100
Memorial Hermann Health System	Memorial Hermann Greater Heights Hospital	Humana	5395	510	100
Memorial Hermann Health System	Memorial Hermann Greater Heights Hospital	Kaiser Permanente	5395	522	100
Memorial Hermann Health System	Memorial Hermann Greater Heights Hospital	United Healthcare	5395	643	105
Memorial Hermann Health System	Memorial Hermann Katy Hospital	Aetna	5423	7	100
Memorial Hermann Health System	Memorial Hermann Katy Hospital	Humana	5423	510	100
Memorial Hermann Health System	Memorial Hermann Katy Hospital	Kaiser Permanente	5423	522	100
Memorial Hermann Health System	Memorial Hermann Katy Hospital	United Healthcare	5423	643	105
Memorial Hermann Health System	Memorial Hermann Memorial City Medical Center	Aetna	5397	7	100
Memorial Hermann Health System	Memorial Hermann Memorial City Medical Center	Humana	5397	510	100
Memorial Hermann Health System	Memorial Hermann Memorial City Medical Center	Kaiser Permanente	5397	522	100
Memorial Hermann Health System	Memorial Hermann Memorial City Medical Center	United Healthcare	5397	643	105
Memorial Hermann Health System	Memorial Hermann Northeast Hospital	Aetna	5414	7	100
Memorial Hermann Health System	Memorial Hermann Northeast Hospital	Humana	5414	510	100
Memorial Hermann Health System	Memorial Hermann Northeast Hospital	Kaiser Permanente	5414	522	100
Memorial Hermann Health System	Memorial Hermann Northeast Hospital	United Healthcare	5414	643	105
Memorial Hermann Health System	Memorial Hermann Orthopedic & Spine Hospital	Aetna	521	7	100
Memorial Hermann Health System	Memorial Hermann Orthopedic & Spine Hospital	Humana	521	510	100
Memorial Hermann Health System	Memorial Hermann Orthopedic & Spine Hospital	Kaiser Permanente	521	522	100
Memorial Hermann Health System	Memorial Hermann Orthopedic & Spine Hospital	United Healthcare	521	643	105
Memorial Hermann Health System	Memorial Hermann Pearland Hospital	Aetna	6856	7	100
Memorial Hermann Health System	Memorial Hermann Pearland Hospital	Humana	6856	510	100
Memorial Hermann Health System	Memorial Hermann Pearland Hospital	Kaiser Permanente	6856	522	100
Memorial Hermann Health System	Memorial Hermann Pearland Hospital	United Healthcare	6856	643	105
Memorial Hermann Health System	Memorial Hermann Rehabilitation Hospital - Katy	Aetna	5126	7	100
Memorial Hermann Health System	Memorial Hermann Rehabilitation Hospital - Katy	Humana	5126	510	100
Memorial Hermann Health System	Memorial Hermann Rehabilitation Hospital - Katy	Kaiser Permanente	5126	522	100
Memorial Hermann Health System	Memorial Hermann Rehabilitation Hospital - Katy	United Healthcare	5126	643	105
Memorial Hermann Health System	Memorial Hermann Southeast Hospital	Aetna	6855	7	100
Memorial Hermann Health System	Memorial Hermann Southeast Hospital	Humana	6855	510	100
Memorial Hermann Health System	Memorial Hermann Southeast Hospital	Kaiser Permanente	6855	522	100
Memorial Hermann Health System	Memorial Hermann Southeast Hospital	United Healthcare	6855	643	105
Memorial Hermann Health System	Memorial Hermann Southwest Hospital	Aetna	7279	7	100
Memorial Hermann Health System	Memorial Hermann Southwest Hospital	Humana	7279	510	100
Memorial Hermann Health System	Memorial Hermann Southwest Hospital	Kaiser Permanente	7279	522	100
Memorial Hermann Health System	Memorial Hermann Southwest Hospital	United Healthcare	7279	643	105
Memorial Hermann Health System	Memorial Hermann Sugar Land Hospital	Aetna	5550	7	100
Memorial Hermann Health System	Memorial Hermann Sugar Land Hospital	Humana	5550	510	100
Memorial Hermann Health System	Memorial Hermann Sugar Land Hospital	Kaiser Permanente	5550	522	100
Memorial Hermann Health System	Memorial Hermann Sugar Land Hospital	United Healthcare	5550	643	105

Vanderbilt

provider_healthsystem_name	facility_name	payer_name	provider_id	payer_id	multiplier
Vanderbilt Health	Monroe Carell Jr Childrens Hospital at Vanderbilt	Aetna	8073	7	112
Vanderbilt Health	Monroe Carell Jr Childrens Hospital at Vanderbilt	Humana	8073	510	105
Vanderbilt Health	Vanderbilt Bedford Hospital	Aetna	3461	7	112
Vanderbilt Health	Vanderbilt Bedford Hospital	Humana	3461	510	105
Vanderbilt Health	Vanderbilt Tullahoma-Harton Hospital	Aetna	3466	7	112
Vanderbilt Health	Vanderbilt Tullahoma-Harton Hospital	Humana	3466	510	105
Vanderbilt Health	Vanderbilt University Medical Center	Aetna	3448	7	112
Vanderbilt Health	Vanderbilt University Medical Center	Humana	3448	510	105
Vanderbilt Health	Vanderbilt Wilson County Hospital	Aetna	3340	7	112
Vanderbilt Health	Vanderbilt Wilson County Hospital	Humana	3340	510	105
Vanderbilt Health	Vanderbilt Wilson County Hospital	United Healthcare	3340	643	100

Code

Baptist

df = pd.read_sql(f"""
WITH 
df AS (
    SELECT 
        DISTINCT 
        hr.payer_id, 
        hr.provider_id, 
        hr.payer_name,
        hr.plan_name,
        h.provider_name_short AS facility_name,
        CASE
            WHEN phs.provider_name_short LIKE '%Baptist%' THEN 'Baptist Health'
            WHEN phs.provider_name_short LIKE '%Duke%' THEN 'Duke Health'
            WHEN phs.provider_name_short LIKE '%Ochsner%' THEN 'Ochsner Health'
            ELSE phs.provider_name_short
        END as provider_healthsystem_name,
        hr.additional_generic_notes, 
        hr.additional_payer_notes
    FROM glue.hospital_data.hospital_rates hr
    JOIN tq_production.spines.spines_provider_hospitals h
        ON hr.provider_id = h.provider_id
    JOIN tq_production.spines.spines_provider_healthsystems phs
        ON h.provider_healthsystem_id = phs.provider_id
    WHERE hr.payer_class_name = 'Medicare Advantage'
    AND hr.payer_id IN (643, 7, 510, 522, 42) -- UHC, Aetna, Humana, Kaiser, Anthem
    AND phs.provider_id IN (
        '100039','100815','100041','100224','100037','100038','100040',
        '100771','100052','100482','100681','100160','100876','100230',
        '100247','100312','100582','100419','100371','100269','101033','101235'
    )
    AND COALESCE(hr.billing_class, '') != 'Professional'
    AND (
        (
            hr.additional_generic_notes IS NOT NULL
            AND hr.additional_generic_notes NOT LIKE 'Contracting method is an algorithm described%'
        )
        OR (
            hr.additional_payer_notes IS NOT NULL
            AND hr.additional_payer_notes NOT IN (
                'contract indicates payment as: algorithm',
                'DRG Factor * DRG Weight'
            )
        )
    )
    AND phs.provider_name_short LIKE '%Baptist%'
)
SELECT *
FROM df
ORDER BY 6
""", con=trino_conn)

# %%
import re
df["additional_generic_notes"] = df["additional_generic_notes"].fillna('')
df["multiplier"] = df["additional_generic_notes"].str.extract(r'\*\s*([\d.]+)\s*\)').astype(float)

# %%
df_summary = (
    df
    .loc[df['multiplier'].notnull()]
    .groupby(['provider_healthsystem_name', 'facility_name', 'payer_name', 'provider_id', 'payer_id', ])
    .agg({
        'multiplier': 'median',
    })
    .reset_index()
)
df_summary.columns = ['provider_healthsystem_name', 'facility_name', 'payer_name', 'provider_id', 'payer_id', 'multiplier']
print(df_summary.to_markdown(index=False))

Memorial Hermann

df = pd.read_sql(f"""
WITH 
df AS (
    SELECT 
        DISTINCT 
        hr.payer_id, 
        hr.provider_id, 
        hr.payer_name,
        hr.plan_name,
        h.provider_name_short AS facility_name,
        CASE
            WHEN phs.provider_name_short LIKE '%Baptist%' THEN 'Baptist Health'
            WHEN phs.provider_name_short LIKE '%Duke%' THEN 'Duke Health'
            WHEN phs.provider_name_short LIKE '%Ochsner%' THEN 'Ochsner Health'
            ELSE phs.provider_name_short
        END as provider_healthsystem_name,
        hr.additional_generic_notes, 
        hr.additional_payer_notes
    FROM glue.hospital_data.hospital_rates hr
    JOIN tq_production.spines.spines_provider_hospitals h
        ON hr.provider_id = h.provider_id
    JOIN tq_production.spines.spines_provider_healthsystems phs
        ON h.provider_healthsystem_id = phs.provider_id
    WHERE hr.payer_class_name = 'Medicare Advantage'
    AND hr.payer_id IN (643, 7, 510, 522, 42) -- UHC, Aetna, Humana, Kaiser, Anthem
    AND phs.provider_id IN (
        '100039','100815','100041','100224','100037','100038','100040',
        '100771','100052','100482','100681','100160','100876','100230',
        '100247','100312','100582','100419','100371','100269','101033','101235'
    )
    AND COALESCE(hr.billing_class, '') != 'Professional'
    AND (
        (
            hr.additional_generic_notes IS NOT NULL
            AND hr.additional_generic_notes NOT LIKE 'Contracting method is an algorithm described%'
        )
        OR (
            hr.additional_payer_notes IS NOT NULL
            AND hr.additional_payer_notes NOT IN (
                'contract indicates payment as: algorithm',
                'DRG Factor * DRG Weight'
            )
        )
    )
    AND phs.provider_name_short LIKE '%Memorial Hermann Health%'
)
SELECT *
FROM df
ORDER BY 6
""", con=trino_conn)

# %%
df['multiplier'] = df["plan_name"].str.split("(").str[1].str.split('%').str[0].astype(float)


# %%
df_summary = (
    df
    .loc[df['multiplier'].notnull()]
    .groupby(['provider_healthsystem_name', 'facility_name', 'payer_name', 'provider_id', 'payer_id', ])
    .agg({
        'multiplier': 'median',
    })
    .reset_index()
)
df_summary.columns = ['provider_healthsystem_name', 'facility_name', 'payer_name', 'provider_id', 'payer_id', 'multiplier']
df_summary

print(df_summary.to_markdown(index=False))
# %%

Vanderbilt

df = pd.read_sql(f"""
WITH 
df AS (
    SELECT 
        DISTINCT 
        hr.payer_id, 
        hr.provider_id, 
        hr.payer_name,
        hr.plan_name,
        h.provider_name_short AS facility_name,
        CASE
            WHEN phs.provider_name_short LIKE '%Baptist%' THEN 'Baptist Health'
            WHEN phs.provider_name_short LIKE '%Duke%' THEN 'Duke Health'
            WHEN phs.provider_name_short LIKE '%Ochsner%' THEN 'Ochsner Health'
            ELSE phs.provider_name_short
        END as provider_healthsystem_name,
        hr.additional_generic_notes, 
        hr.additional_payer_notes
    FROM glue.hospital_data.hospital_rates hr
    JOIN tq_production.spines.spines_provider_hospitals h
        ON hr.provider_id = h.provider_id
    JOIN tq_production.spines.spines_provider_healthsystems phs
        ON h.provider_healthsystem_id = phs.provider_id
    WHERE hr.payer_class_name = 'Medicare Advantage'
    AND hr.payer_id IN (643, 7, 510, 522, 42) -- UHC, Aetna, Humana, Kaiser, Anthem
    AND phs.provider_id IN (
        '100039','100815','100041','100224','100037','100038','100040',
        '100771','100052','100482','100681','100160','100876','100230',
        '100247','100312','100582','100419','100371','100269','101033','101235'
    )
    AND COALESCE(hr.billing_class, '') != 'Professional'
    AND (
        (
            hr.additional_generic_notes IS NOT NULL
            AND hr.additional_generic_notes NOT LIKE 'Contracting method is an algorithm described%'
        )
        OR (
            hr.additional_payer_notes IS NOT NULL
            AND hr.additional_payer_notes NOT IN (
                'contract indicates payment as: algorithm',
                'DRG Factor * DRG Weight'
            )
        )
    )
    AND phs.provider_name_short LIKE '%Vanderbilt%'
)
SELECT *
FROM df
ORDER BY 6
""", con=trino_conn)

# %%
df['multiplier'] = df['additional_payer_notes'].str.split('@ ').str[1].str.split('%').str[0].astype(float)


# %%
df_summary = (
    df
    .loc[df['multiplier'].notnull()]
    .groupby(['provider_healthsystem_name', 'facility_name', 'payer_name', 'provider_id', 'payer_id', ])
    .agg({
        'multiplier': ['median'],
    })
    .reset_index()
)
df_summary.columns = ['provider_healthsystem_name', 'facility_name', 'payer_name', 'provider_id', 'payer_id', 'multiplier']
print(df_summary.to_markdown(index=False))

Baptist​

Memorial Hermann​

Vanderbilt​

Code​

On this page:

Baptist

Memorial Hermann

Vanderbilt

Code