export_csv.py

"""
Export MySQL healthcare data to CSV files for PowerBI / Excel
"""

import pandas as pd
import mysql.connector
import os

os.makedirs("data", exist_ok=True)

conn = mysql.connector.connect(
    host="localhost", user="root", password="", database="healthcare_analytics"
)

# 1. National rates by condition
pd.read_sql("""
    SELECT
        measure_name,
        ROUND(AVG(score), 2)  AS avg_rate,
        ROUND(MIN(score), 2)  AS best_rate,
        ROUND(MAX(score), 2)  AS worst_rate,
        COUNT(*)              AS hospital_count
    FROM hospital_readmissions
    WHERE score IS NOT NULL
    GROUP BY measure_name
    ORDER BY avg_rate DESC
""", conn).to_csv("data/conditions_overview.csv", index=False)
print("conditions_overview.csv done")

# 2. Worst states — heart failure
pd.read_sql("""
    SELECT
        state,
        ROUND(AVG(score), 2)          AS avg_readmission_rate,
        COUNT(DISTINCT hospital_name) AS hospitals
    FROM hospital_readmissions
    WHERE measure_name = 'Heart failure (HF) 30-Day Readmission Rate'
      AND score IS NOT NULL
    GROUP BY state
    ORDER BY avg_readmission_rate DESC
    LIMIT 20
""", conn).to_csv("data/worst_states.csv", index=False)
print("worst_states.csv done")

# 3. Best vs worst hospitals — heart failure
pd.read_sql("""
    SELECT hospital_name, city, state, score,
           'Best' AS category
    FROM hospital_readmissions
    WHERE measure_name = 'Heart failure (HF) 30-Day Readmission Rate'
      AND score IS NOT NULL AND denominator >= 100
    ORDER BY score ASC LIMIT 10
""", conn).to_csv("data/best_hospitals.csv", index=False)

pd.read_sql("""
    SELECT hospital_name, city, state, score,
           'Worst' AS category
    FROM hospital_readmissions
    WHERE measure_name = 'Heart failure (HF) 30-Day Readmission Rate'
      AND score IS NOT NULL AND denominator >= 100
    ORDER BY score DESC LIMIT 10
""", conn).to_csv("data/worst_hospitals.csv", index=False)
print("best_hospitals.csv and worst_hospitals.csv done")

# 4. Hospital size vs performance
pd.read_sql("""
    SELECT
        CASE
            WHEN denominator < 100  THEN 'Small (<100)'
            WHEN denominator < 300  THEN 'Medium (100-299)'
            WHEN denominator < 600  THEN 'Large (300-599)'
            ELSE                         'Very Large (600+)'
        END                           AS hospital_size,
        ROUND(AVG(score), 2)          AS avg_rate,
        ROUND(MIN(score), 2)          AS best_rate,
        ROUND(MAX(score), 2)          AS worst_rate,
        COUNT(DISTINCT hospital_name) AS hospital_count
    FROM hospital_readmissions
    WHERE measure_name = 'Heart failure (HF) 30-Day Readmission Rate'
      AND score IS NOT NULL AND denominator IS NOT NULL
    GROUP BY hospital_size
    ORDER BY hospital_size
""", conn).to_csv("data/size_performance.csv", index=False)
print("size_performance.csv done")

# 5. Chronic underperformers
pd.read_sql("""
    SELECT
        hospital_name,
        city,
        state,
        COUNT(*)              AS measures_worse,
        ROUND(AVG(score), 2)  AS avg_score
    FROM hospital_readmissions
    WHERE compared_to_national = 'Worse than the National Rate'
      AND score IS NOT NULL
    GROUP BY hospital_name, city, state
    HAVING COUNT(*) >= 4
    ORDER BY measures_worse DESC, avg_score DESC
    LIMIT 15
""", conn).to_csv("data/chronic_underperformers.csv", index=False)
print("chronic_underperformers.csv done")

# 6. Full dataset sample for PowerBI (filtered, manageable size)
pd.read_sql("""
    SELECT
        hospital_name,
        city,
        state,
        measure_name,
        score,
        compared_to_national,
        denominator
    FROM hospital_readmissions
    WHERE score IS NOT NULL
      AND measure_name IN (
          'Heart failure (HF) 30-Day Readmission Rate',
          'Pneumonia (PN) 30-Day Readmission Rate',
          'Acute Myocardial Infarction (AMI) 30-Day Readmission Rate',
          'Heart failure (HF) 30-Day Mortality Rate',
          'Rate of readmission after discharge from hospital (hospital-wide)'
      )
""", conn).to_csv("data/main_dataset.csv", index=False)
print("main_dataset.csv done")

conn.close()
print("\nAll CSVs exported to data/ folder.")