OP Rate Distributions

Validated

	canonical_rate_percent_of_medicare
count	10000
mean	2.86434
std	3.06582
min	0.05
1%	0.19
2%	0.28
3%	0.37
4%	0.44
5%	0.5
6%	0.56
7%	0.61
8%	0.65
9%	0.7
10%	0.74
50%	2.06
max	36.99

df = pd.read_sql(f"""
SELECT
    canonical_rate_percent_of_medicare
FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_abridged
WHERE bill_type = 'Outpatient'
AND canonical_rate_score = 5
AND provider_type = 'Hospital'
ORDER BY RANDOM()
LIMIT 10000
""", con=trino_conn)
print(
    df['canonical_rate_percent_of_medicare']
    .describe(
        percentiles=[0.01, 0.02, 0.03, 0.04, 0.05, 0.06, 0.07, 0.08, 0.09, 0.1]
    )
    .to_markdown()
)

All Non-Outliers

	canonical_rate_percent_of_medicare
count	10000
mean	2.6747
std	3.07642
min	0.04
1%	0.2
2%	0.27
3%	0.33
4%	0.3796
5%	0.42
6%	0.47
7%	0.5
8%	0.54
9%	0.58
10%	0.62
50%	1.77
max	29.46

df = pd.read_sql(f"""
SELECT
    canonical_rate_percent_of_medicare
FROM tq_dev.internal_dev_csong_cld_v2_2_2.prod_combined_abridged
WHERE bill_type = 'Outpatient'
AND canonical_rate_score > 1
AND provider_type = 'Hospital'
ORDER BY RANDOM()
LIMIT 10000
""", con=trino_conn)

print(
    df['canonical_rate_percent_of_medicare']
    .describe(
        percentiles=[0.01, 0.02, 0.03, 0.04, 0.05, 0.06, 0.07, 0.08, 0.09, 0.1]
    )
    .to_markdown()
)