Table

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import matplotlib.ticker as mtick
import numpy as np

# 1. Load the Data
# Make sure to update the filename to match your local file
file_name = "KI_Copy of Reach Rates by Outreach Program_2025 (1).xlsx - Blinded Preventitive Programs -.csv"
df = pd.read_csv(file_name)

# 2. Define Categorization Logic
def categorize_program(name):
    name = str(name).lower()
    if 'depression' in name:
        return 'Depression Screening'
    elif 'diabetes' in name:
        return 'Diabetes Care'
    elif 'cancer' in name or 'colon' in name:
        return 'Cancer Screening'
    elif 'well child' in name:
        return 'Well Child Visit'
    elif 'flu' in name:
        return 'Flu Vaccine'
    elif 'hypertension' in name:
        return 'Hypertension'
    elif 'medication adherence' in name:
        return 'Medication Adherence'
    elif 'annual' in name or 'wellness' in name:
        return 'Annual Wellness'
    elif 'primary care' in name:
        return 'Primary Care'
    elif 'care gaps' in name:
        return 'General Care Gaps'
    else:
        return 'Other'

# 3. Process the Data
df['Program Category'] = df['Outreach Program Full Name'].apply(categorize_program)

# Calculate absolute numbers based on rates
df['Completed Count'] = (df['Patients Outreached To'] * df['% Completed']).round().astype(int)
df['Medically Reached Count'] = (df['Patients Outreached To'] * df['% Medically Reached']).round().astype(int)

# Group by category and sum up the counts
grouped_counts = df.groupby('Program Category').agg({
    'Patients Outreached To': 'sum',
    'Completed Count': 'sum',
    'Medically Reached Count': 'sum'
}).reset_index()

# Calculate weighted rates for plotting/labels
grouped_counts['Weighted % Completed'] = grouped_counts['Completed Count'] / grouped_counts['Patients Outreached To']
grouped_counts['Weighted % Medically Reached'] = grouped_counts['Medically Reached Count'] / grouped_counts['Patients Outreached To']

# --- Graph 1: Scale of Impact (Volume Bar Chart) ---

# Sort by Volume for the bar chart
plot_data_bar = grouped_counts.sort_values('Completed Count', ascending=True)

plt.figure(figsize=(14, 8))
# Use a "Success Green" color
bars = plt.barh(plot_data_bar['Program Category'], plot_data_bar['Completed Count'], color='#00A36C')

# Add text labels to the bars
for bar, rate in zip(bars, plot_data_bar['Weighted % Completed']):
    width = bar.get_width()
    label_y = bar.get_y() + bar.get_height() / 2
    
    # Label format: "17,106 Gaps Closed (22.3%)"
    label_text = f"{int(width):,} Gaps Closed ({rate:.1%})"
    
    plt.text(width + 500, label_y, label_text, 
             va='center', fontweight='bold', fontsize=12, color='black')

plt.xlabel('Number of Patients (Care Gaps Closed)', fontsize=12)
plt.title('Real World Impact: Total Care Gaps Closed by Program Category\n(Focus on Volume of Success)', 
          fontsize=16, fontweight='bold', loc='left')
plt.xlim(0, max(plot_data_bar['Completed Count']) * 1.3) # Add space for labels

# Styling
sns.despine(left=True, bottom=True)
plt.grid(axis='x', linestyle='--', alpha=0.5)
plt.tight_layout()

# Save or Show
plt.savefig('cipher_impact_volume.png', dpi=300)
plt.show()


# --- Graph 2: Program Landscape (Bubble Chart) ---

plt.figure(figsize=(12, 8))

# Sort by volume so smaller bubbles are plotted on top if they overlap
bubble_data = grouped_counts.sort_values('Patients Outreached To', ascending=False)

# Define bubble sizes (scaled down for readability)
sizes = bubble_data['Patients Outreached To'] / 20 

# Create Scatter Plot
plt.scatter(
    x=bubble_data['Weighted % Completed'],
    y=bubble_data['Weighted % Medically Reached'],
    s=sizes,
    alpha=0.6,
    c='#0072B2',
    edgecolors='white',
    linewidth=2
)

# Add labels to bubbles
for i, row in bubble_data.iterrows():
    plt.annotate(
        row['Program Category'],
        (row['Weighted % Completed'], row['Weighted % Medically Reached']),
        xytext=(0, 0), textcoords='offset points',
        ha='center', va='center', fontsize=9, fontweight='bold'
    )

plt.title('Program Landscape: Efficiency (Completion) vs. Engagement (Reach)\n(Bubble Size = Total Patient Volume)', 
          fontsize=16, fontweight='bold')
plt.xlabel('Completion Rate (%)', fontsize=12)
plt.ylabel('Medically Reached Rate (%)', fontsize=12)

# Format axes as percentages
plt.gca().xaxis.set_major_formatter(mtick.PercentFormatter(1.0))
plt.gca().yaxis.set_major_formatter(mtick.PercentFormatter(1.0))

# Add diagonal reference line
plt.plot([0, 1], [0, 1], 'k--', alpha=0.2, label='Reference: 100% Conversion')

sns.despine()
plt.tight_layout()

# Save or Show
plt.savefig('cipher_efficiency_landscape.png', dpi=300)
plt.show()