"""
Export Healthcare Patient Dataset to Excel
==========================================
This script exports the healthcare patient dataset to Excel format
with multiple sheets and formatting.

Author: RSK World
Website: https://rskworld.in
Email: help@rskworld.in
Phone: +91 93305 39277
"""

import pandas as pd
import numpy as np
from datetime import datetime

def export_to_excel(csv_file='healthcare_patients.csv', excel_file='healthcare_patients.xlsx'):
    """
    Export CSV dataset to Excel format with multiple sheets.
    
    Parameters:
    csv_file (str): Path to input CSV file
    excel_file (str): Path to output Excel file
    """
    try:
        # Read CSV file
        print(f"Reading CSV file: {csv_file}")
        df = pd.read_csv(csv_file)
        
        # Convert date columns
        df['admission_date'] = pd.to_datetime(df['admission_date'])
        df['discharge_date'] = pd.to_datetime(df['discharge_date'])
        
        # Create Excel writer object
        print(f"Creating Excel file: {excel_file}")
        with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
            # Main dataset sheet
            df.to_excel(writer, sheet_name='Patient Data', index=False)
            
            # Summary statistics sheet
            summary_data = {
                'Metric': [
                    'Total Patients',
                    'Mean Age',
                    'Median Age',
                    'Mean Length of Stay',
                    'Median Length of Stay',
                    'Total Charges',
                    'Mean Charges',
                    'Median Charges',
                    'Readmission Rate (%)'
                ],
                'Value': [
                    len(df),
                    f"{df['age'].mean():.2f} years",
                    f"{df['age'].median():.2f} years",
                    f"{df['length_of_stay'].mean():.2f} days",
                    f"{df['length_of_stay'].median():.2f} days",
                    f"${df['charges'].sum():,.2f}",
                    f"${df['charges'].mean():,.2f}",
                    f"${df['charges'].median():,.2f}",
                    f"{(df['readmission_30_days'] == 'Yes').sum() / len(df) * 100:.2f}%"
                ]
            }
            summary_df = pd.DataFrame(summary_data)
            summary_df.to_excel(writer, sheet_name='Summary Statistics', index=False)
            
            # Diagnosis summary sheet
            diagnosis_summary = df.groupby('diagnosis').agg({
                'patient_id': 'count',
                'age': 'mean',
                'length_of_stay': 'mean',
                'charges': ['mean', 'sum']
            }).round(2)
            diagnosis_summary.columns = ['Count', 'Avg Age', 'Avg Stay (days)', 'Avg Charges', 'Total Charges']
            diagnosis_summary = diagnosis_summary.sort_values('Count', ascending=False)
            diagnosis_summary.to_excel(writer, sheet_name='Diagnosis Summary')
            
            # Treatment summary sheet
            treatment_summary = df.groupby('treatment_type').agg({
                'patient_id': 'count',
                'length_of_stay': 'mean',
                'charges': ['mean', 'sum'],
                'outcome': lambda x: x.value_counts().to_dict()
            }).round(2)
            treatment_summary.columns = ['Count', 'Avg Stay (days)', 'Avg Charges', 'Total Charges', 'Outcomes']
            treatment_summary = treatment_summary.sort_values('Count', ascending=False)
            treatment_summary.to_excel(writer, sheet_name='Treatment Summary')
            
            # Demographic summary sheet
            demographic_data = {
                'Gender': df['gender'].value_counts().index.tolist(),
                'Count': df['gender'].value_counts().values.tolist(),
                'Percentage': (df['gender'].value_counts().values / len(df) * 100).round(2).tolist()
            }
            demographic_df = pd.DataFrame(demographic_data)
            demographic_df.to_excel(writer, sheet_name='Demographics', index=False)
            
            # Outcome analysis sheet
            outcome_analysis = df.groupby('outcome').agg({
                'patient_id': 'count',
                'length_of_stay': 'mean',
                'charges': 'mean',
                'readmission_30_days': lambda x: (x == 'Yes').sum()
            }).round(2)
            outcome_analysis.columns = ['Count', 'Avg Stay (days)', 'Avg Charges', 'Readmissions']
            outcome_analysis = outcome_analysis.sort_values('Count', ascending=False)
            outcome_analysis.to_excel(writer, sheet_name='Outcome Analysis')
        
        print(f"[OK] Excel file created successfully: {excel_file}")
        print(f"  - Total sheets: 6")
        print(f"  - Total patients: {len(df)}")
        return True
        
    except FileNotFoundError:
        print(f"[ERROR] File '{csv_file}' not found")
        return False
    except Exception as e:
        print(f"[ERROR] Error creating Excel file: {str(e)}")
        return False

def main():
    """
    Main function to export dataset to Excel.
    """
    print("\n" + "="*60)
    print("HEALTHCARE PATIENT DATASET - EXCEL EXPORT")
    print("="*60)
    print("Author: RSK World")
    print("Website: https://rskworld.in")
    print("Email: help@rskworld.in")
    print("Phone: +91 93305 39277")
    print("="*60 + "\n")
    
    success = export_to_excel()
    
    if success:
        print("\n" + "="*60)
        print("EXPORT COMPLETE!")
        print("="*60)
        print("\nThe Excel file contains the following sheets:")
        print("  1. Patient Data - Complete dataset")
        print("  2. Summary Statistics - Key metrics")
        print("  3. Diagnosis Summary - Analysis by diagnosis")
        print("  4. Treatment Summary - Analysis by treatment type")
        print("  5. Demographics - Patient demographics breakdown")
        print("  6. Outcome Analysis - Patient outcomes analysis")
        print("\n")

if __name__ == "__main__":
    main()