evaluate.py

import joblib
import pandas as pd
import matplotlib.pyplot as plt
from sklearn.metrics import mean_absolute_error, r2_score
from sklearn.model_selection import train_test_split
import os
import platform
import subprocess
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as ExcelImage
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

# Paths
DATA_PATH = "dynamic_speed_limit_dataset_free.csv"
MODEL_PATH = "dynamic_speed_limit_model.pkl"
OUTPUT_EXCEL = "evaluation_results.xlsx"
CHART_IMAGE = "speed_comparison_chart.png"

# Load dataset
df = pd.read_csv(DATA_PATH)

# Features & target
X = df.drop("speed_limit", axis=1)
y = df["speed_limit"]

# Train-test split
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# Load model
model = joblib.load(MODEL_PATH)

# Predict
y_pred = model.predict(X_test)

# Evaluate
mae = mean_absolute_error(y_test, y_pred)
r2 = r2_score(y_test, y_pred)

print("📊 Model Evaluation Results")
print(f"Mean Absolute Error (MAE): {mae:.2f} km/h")
print(f"R² Score: {r2:.2f} (1.0 = perfect fit)\n")

# Create DataFrames
comparison_df = pd.DataFrame({
    "Actual Speed": y_test.values,
    "Predicted Speed": y_pred
})
metrics_df = pd.DataFrame({
    "Metric": ["Mean Absolute Error (MAE)", "R² Score"],
    "Value": [mae, r2]
})

# Save both metrics & predictions to Excel
with pd.ExcelWriter(OUTPUT_EXCEL, engine="openpyxl") as writer:
    metrics_df.to_excel(writer, sheet_name="Evaluation", index=False)
    comparison_df.to_excel(writer, sheet_name="Predictions", index=False)

# --- Styling function ---
def style_sheet(ws):
    bold_font = Font(bold=True, color="FFFFFF")
    fill = PatternFill("solid", fgColor="4F81BD")
    thin_border = Border(
        left=Side(style="thin"), right=Side(style="thin"),
        top=Side(style="thin"), bottom=Side(style="thin")
    )

    for col in ws.iter_cols(min_row=1, max_row=1):
        for cell in col:
            cell.font = bold_font
            cell.fill = fill
            cell.alignment = Alignment(horizontal="center")
    
    for row in ws.iter_rows():
        for cell in row:
            cell.border = thin_border
            cell.alignment = Alignment(horizontal="center")

    # Auto-adjust column width
    for col in ws.columns:
        max_length = 0
        col_letter = col[0].column_letter
        for cell in col:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
        ws.column_dimensions[col_letter].width = max_length + 2

# --- Plot Chart ---
sample_df = comparison_df.head(10)
plt.figure(figsize=(10, 6))
bar_width = 0.35
indices = range(len(sample_df))

plt.bar(indices, sample_df["Actual Speed"], bar_width, label="Actual Speed", color="skyblue")
plt.bar([i + bar_width for i in indices], sample_df["Predicted Speed"], bar_width, label="Predicted Speed", color="orange")

plt.xlabel("Sample Index")
plt.ylabel("Speed (km/h)")
plt.title("Actual vs Predicted Speed (First 10 Samples)")
plt.xticks([i + bar_width/2 for i in indices], range(1, 11))
plt.legend()
plt.tight_layout()
plt.savefig(CHART_IMAGE)
plt.show()

# --- Embed chart into Excel ---
wb = load_workbook(OUTPUT_EXCEL)

for sheet in ["Evaluation", "Predictions"]:
    if sheet in wb.sheetnames:
        style_sheet(wb[sheet])

# Create chart sheet
ws_chart = wb.create_sheet("Chart")
img = ExcelImage(CHART_IMAGE)
ws_chart.add_image(img, "A1")

wb.save(OUTPUT_EXCEL)

print(f"✅ Styled evaluation results with chart saved to: {OUTPUT_EXCEL}")

# Auto-open file
if platform.system() == "Windows":
    os.startfile(OUTPUT_EXCEL)
elif platform.system() == "Darwin":
    subprocess.call(["open", OUTPUT_EXCEL])
else:
    subprocess.call(["xdg-open", OUTPUT_EXCEL])