حلل هذا الكود واقترح تحسينات:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
from matplotlib.backends.backend_pdf import PdfPages
from statsmodels.tsa.seasonal import seasonal_decompose
from statsmodels.tsa.statespace.sarimax import SARIMAX
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
import warnings
from tabulate import tabulate
import calendar

warnings.filterwarnings('ignore')

def load_and_preprocess_data(file_path):
    try:
        df = pd.read_excel(file_path)
        df['Service date'] = pd.to_datetime(df['Service date'], errors='coerce')
        df.dropna(subset=['Service date'], inplace=True)

        df['Half_Year'] = df['Service date'].apply(lambda x: f'H{1 if x.month <= 6 else 2} {x.year}')
        df['Quarter'] = df['Service date'].dt.to_period('Q')
        df['Month'] = df['Service date'].dt.to_period('M')
        df['Age_Group'] = pd.cut(df['pt age'], bins=[0, 18, 35, 50, np.inf], labels=['<18', '18-35', '36-50', '50+'])

        return df
    except Exception as e:
        print(f"Error loading data: {e}")
        return None

def create_quarterly_service_plot(df, pdf):
    quarterly_service_counts = df.pivot_table(
        index='Quarter',
        columns='Provided service',
        values='pt Name',
        aggfunc='count'
    ).fillna(0)

    plt.figure(figsize=(12, 7))
    quarterly_service_counts.plot(kind='bar', stacked=True)
    plt.title('Quarterly Case Count by Service Type', fontsize=14)
    plt.xlabel('Quarter', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.legend(title='Services', bbox_to_anchor=(1.05, 1), loc='upper left')
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def create_monthly_trend_plot(df, pdf):
    monthly_service_counts = df.pivot_table(
        index='Month',
        columns='Provided service',
        values='pt Name',
        aggfunc='count'
    ).fillna(0).reset_index()
    monthly_service_counts['Month'] = monthly_service_counts['Month'].astype(str)

    plt.figure(figsize=(15, 8))
    for service in df['Provided service'].unique():
        plt.plot(
            monthly_service_counts['Month'],
            monthly_service_counts[service],
            marker='o',
            label=service
        )
    plt.title('Monthly Trends in Case Count by Service', fontsize=14)
    plt.xlabel('Month', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.xticks(rotation=45)
    plt.legend()
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def create_age_group_plot(df, pdf):
    age_service_counts = df.pivot_table(
        index='Age_Group',
        columns='Provided service',
        values='pt Name',
        aggfunc='count'
    ).fillna(0)

    plt.figure(figsize=(12, 7))
    age_service_counts.plot(kind='bar')
    plt.title('Age Group Distribution by Service', fontsize=14)
    plt.xlabel('Age Group', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.legend(title='Services', bbox_to_anchor=(1.05, 1), loc='upper left')
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def create_gender_plot(df, pdf):
    gender_service_counts = df.pivot_table(
        index='pt gender',
        columns='Provided service',
        values='pt Name',
        aggfunc='count'
    ).fillna(0)

    plt.figure(figsize=(12, 7))
    gender_service_counts.plot(kind='bar')
    plt.title('Gender Distribution by Service', fontsize=14)
    plt.xlabel('Gender', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.legend(title='Services', bbox_to_anchor=(1.05, 1), loc='upper left')
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def create_peak_times_plot(df, pdf):
    df['DayOfWeek'] = df['Service date'].dt.dayofweek
    df['Hour'] = df['Service date'].dt.hour

    plt.figure(figsize=(15, 10))
    
    plt.subplot(2, 1, 1)
    sns.countplot(data=df, x='DayOfWeek', order=range(7))
    plt.title('Case Distribution by Day of Week', fontsize=14)
    plt.xlabel('Day of Week', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.xticks(range(7), calendar.day_abbr, rotation=0)

    plt.subplot(2, 1, 2)
    sns.countplot(data=df, x='Hour', order=range(24))
    plt.title('Case Distribution by Hour of Day', fontsize=14)
    plt.xlabel('Hour of Day', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)

    plt.tight_layout()
    pdf.savefig()
    plt.close()

def create_service_count_plot(df, pdf):
    service_counts = df['Provided service'].value_counts().reset_index()
    service_counts.columns = ['Provided service', 'Counts']

    plt.figure(figsize=(12, 7))
    sns.barplot(data=service_counts, x='Provided service', y='Counts', palette='muted')
    plt.title('Case Count by Service', fontsize=14)
    plt.xlabel('Service', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.xticks(rotation=45)
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def perform_time_series_analysis(df, pdf):
    df['Date'] = df['Service date'].dt.to_period('D').astype(str)
    daily_cases = df.groupby('Date')['pt Name'].count().reset_index()
    daily_cases['Date'] = pd.to_datetime(daily_cases['Date'])
    daily_cases.set_index('Date', inplace=True)
    daily_cases.columns = ['Total_Cases']

    # Seasonal Decomposition
    decomposition = seasonal_decompose(daily_cases['Total_Cases'], model='additive', period=30)

    plt.figure(figsize=(15, 12))
    plt.subplot(411)
    plt.plot(daily_cases.index, daily_cases['Total_Cases'], label='Observed')
    plt.legend(loc='upper left')
    plt.subplot(412)
    plt.plot(daily_cases.index, decomposition.trend, label='Trend')
    plt.legend(loc='upper left')
    plt.subplot(413)
    plt.plot(daily_cases.index, decomposition.seasonal, label='Seasonal')
    plt.legend(loc='upper left')
    plt.subplot(414)
    plt.plot(daily_cases.index, decomposition.resid, label='Residual')
    plt.legend(loc='upper left')
    plt.tight_layout()
    pdf.savefig()
    plt.close()

    # SARIMA Forecast
    model = SARIMAX(daily_cases['Total_Cases'], order=(1, 1, 1), seasonal_order=(1, 1, 1, 12))
    results = model.fit()

    forecast = results.get_forecast(steps=30)
    forecast_index = pd.date_range(start=daily_cases.index[-1], periods=31, freq='D')[1:]

    plt.figure(figsize=(15, 7))
    plt.plot(daily_cases.index, daily_cases['Total_Cases'], label='Historical Data')
    plt.plot(forecast_index, forecast.predicted_mean, label='Forecast', linestyle='--')
    plt.fill_between(forecast_index,
                     forecast.conf_int()['lower Total_Cases'],
                     forecast.conf_int()['upper Total_Cases'],
                     color='gray', alpha=0.2, label='95% Confidence Interval')
    plt.title('Future Demand Forecast (SARIMA)', fontsize=14)
    plt.xlabel('Date', fontsize=12)
    plt.ylabel('Number of Cases', fontsize=12)
    plt.legend()
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def perform_clustering_analysis(df, pdf):
    # Select features for clustering
    features = ['pt age', 'Service date']
    X = df[features].copy()
    X['Service date'] = X['Service date'].astype(int) // 10**9  # Convert to Unix timestamp

    # Standardize features
    scaler = StandardScaler()
    X_scaled = scaler.fit_transform(X)

    # Perform K-means clustering
    kmeans = KMeans(n_clusters=3, random_state=42)
    df['Cluster'] = kmeans.fit_predict(X_scaled)

    # Visualize clusters
    plt.figure(figsize=(12, 8))
    scatter = plt.scatter(df['Service date'], df['pt age'], c=df['Cluster'], cmap='viridis')
    plt.title('Patient Clusters based on Age and Service Date', fontsize=14)
    plt.xlabel('Service Date', fontsize=12)
    plt.ylabel('Patient Age', fontsize=12)
    plt.colorbar(scatter, label='Cluster')
    plt.tight_layout()
    pdf.savefig()
    plt.close()

def create_comparison_tables(df, pdf):
    # Half-yearly comparison
    half_yearly = df.groupby('Half_Year')['pt Name'].count().reset_index()
    half_yearly['Change'] = half_yearly['pt Name'].pct_change()
    half_yearly['Arrow'] = half_yearly['Change'].apply(lambda x: '↑' if x > 0 else '↓' if x < 0 else '-')
    half_yearly['Change'] = half_yearly['Change'].apply(lambda x: f"{x:.2%}" if pd.notnull(x) else "-")
    half_yearly.columns = ['Period', 'Total Cases', 'Change', 'Trend']

    # Quarterly comparison
    quarterly = df.groupby('Quarter')['pt Name'].count().reset_index()
    quarterly['Change'] = quarterly['pt Name'].pct_change()
    quarterly['Arrow'] = quarterly['Change'].apply(lambda x: '↑' if x > 0 else '↓' if x < 0 else '-')
    quarterly['Change'] = quarterly['Change'].apply(lambda x: f"{x:.2%}" if pd.notnull(x) else "-")
    quarterly.columns = ['Period', 'Total Cases', 'Change', 'Trend']

    # Service type comparison
    service_comp = df.groupby('Provided service')['pt Name'].count().reset_index()
    service_comp = service_comp.sort_values('pt Name', ascending=False)
    service_comp['Percentage'] = service_comp['pt Name'] / service_comp['pt Name'].sum()
    service_comp.columns = ['Service', 'Total Cases', 'Percentage']
    service_comp['Percentage'] = service_comp['Percentage'].apply(lambda x: f"{x:.2%}")

    # Add tables to PDF
    pdf.savefig(plt.figure(figsize=(12, 4)))
    plt.axis('off')
    plt.table(cellText=half_yearly.values, colLabels=half_yearly.columns, cellLoc='center', loc='center')
    plt.title('Half-Yearly Comparison', fontsize=16)
    plt.tight_layout()

    pdf.savefig(plt.figure(figsize=(12, 6)))
    plt.axis('off')
    plt.table(cellText=quarterly.values, colLabels=quarterly.columns, cellLoc='center', loc='center')
    plt.title('Quarterly Comparison', fontsize=16)
    plt.tight_layout()

    pdf.savefig(plt.figure(figsize=(12, 6)))
    plt.axis('off')
    plt.table(cellText=service_comp.values, colLabels=service_comp.columns, cellLoc='center', loc='center')
    plt.title('Service Type Comparison', fontsize=16)
    plt.tight_layout()

def main():
    file_path = 'corrected_dates_file.xlsx'
    df = load_and_preprocess_data(file_path)
    if df is None:
        print(f"Failed to load data from {file_path}. Please check the file path and format.")
        return

    output_file = 'detailed_analysis_report.pdf'
    try:
        with PdfPages(output_file) as pdf:
            create_quarterly_service_plot(df, pdf)
            create_monthly_trend_plot(df, pdf)
            create_age_group_plot(df, pdf)
            create_gender_plot(df, pdf)
            create_peak_times_plot(df, pdf)
            create_service_count_plot(df, pdf)
            perform_time_series_analysis(df, pdf)
            perform_clustering_analysis(df, pdf)
            create_comparison_tables(df, pdf)
        print(f"Detailed analysis report has been generated and saved as '{output_file}'")
    except Exception as e:
        print(f"An error occurred while generating the report: {e}")

if __name__ == "__main__":
    main()