code 2.py

import pandas as pd
import plotly.express as px

# --- Part 1: Load the Excel File Correctly ---
try:
    file_path = "/Users/nirajmutha/Downloads/NHS data by area.xlsx"

    # Use header=1 to tell pandas the headers are on the second row (index 1)
    df = pd.read_excel(file_path, header=1)

    print("✅ Excel file loaded successfully using the correct header row.")

except FileNotFoundError:
    print(f"❌ Error: The file was not found at {file_path}")
    exit()

# --- Part 2: Filter for Region Data and Clean ---

# Filter the DataFrame to only include rows where the 'Type' is 'Region'
df_regions = df[df['Type'] == 'Region'].copy()

# Rename the 'DNA' column to avoid issues
df_regions.rename(columns={'DNA': 'Did Not Attend', 'Total': 'Total Appointments'}, inplace=True)

# Clean the numeric columns
for col in ['Total Appointments', 'Did Not Attend']:
    df_regions[col] = pd.to_numeric(df_regions[col], errors='coerce')

df_regions.dropna(subset=['Total Appointments', 'Did Not Attend'], inplace=True)

# --- Part 3: Calculate and Visualize the DNA Rate ---

# Calculate the DNA rate for each Region
df_regions['DNA_Rate'] = df_regions['Did Not Attend'] / df_regions['Total Appointments']
df_sorted = df_regions.sort_values(by='DNA_Rate', ascending=False)

print("\n✅ DNA Rate calculated for each NHS Region:")
print(df_sorted[['Name', 'DNA_Rate']])

# Create the visualization
chart_title = "Comparison of 'Did Not Attend' (DNA) Rates by NHS Region"
fig = px.bar(
    df_sorted,
    x='Name',
    y='DNA_Rate',
    title=chart_title,
    labels={'DNA_Rate': "'Did Not Attend' Rate", 'Name': 'NHS Region'},
    text='DNA_Rate',
    color='DNA_Rate',
    color_continuous_scale='Reds'
)

# Format the graph
fig.update_layout(
    title_font_size=22,
    plot_bgcolor='white',
    yaxis_tickformat='.1%',
    coloraxis_showscale=False
)
fig.update_traces(
    texttemplate='%{text:.1%}',
    textposition='outside'
)

# --- Part 4: Save the Plot ---
try:
    fig.write_image("dna_by_region.png", width=1000, height=600, scale=2)
    print("\n✅ Chart saved to 'dna_by_region.png'")
except ValueError as e:
    print(f"\n❌ Error saving image: {e}. Please ensure 'kaleido' is installed.")