Python Topic #6 — Learning by Doing

# File: ml_fatal_crash_model_v1.py
# Purpose: Baseline model using an internal split from dbo.Crashes.

import pandas as pd
from sqlalchemy import create_engine

from sklearn.model_selection import train_test_split
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder
from sklearn.pipeline import Pipeline
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report, confusion_matrix, roc_auc_score

server = "JCDCOMPUTER"
database = "CaliforniaCrashes"
driver = "ODBC Driver 17 for SQL Server"

connection_string = (
    f"mssql+pyodbc://@{server}/{database}"
    f"?driver={driver.replace(' ', '+')}"
    f"&trusted_connection=yes"
)

engine = create_engine(connection_string)

query = """
SELECT
    Source_Year,
    LightingDescription,
    Collision_Type_Description,
    PedestrianActionDesc,
    Weather_1,
    City_Name,
    Crash_Date_Time,
    Primary_Collision_Factor_Violation,
    CASE
        WHEN TRY_CAST(NumberKilled AS INT) > 0 THEN 1
        ELSE 0
    END AS Fatal_Flag
FROM dbo.Crashes
WHERE Crash_Date_Time IS NOT NULL;
"""

print("Reading data from SQL Server...")
df = pd.read_sql(query, engine)
print("Rows loaded:", len(df))
print(df["Fatal_Flag"].value_counts())

df["Crash_Date_Time"] = pd.to_datetime(df["Crash_Date_Time"], errors="coerce")
df = df.dropna(subset=["Crash_Date_Time"])
df["Crash_Hour"] = df["Crash_Date_Time"].dt.hour
df["Crash_Time_2H_Bucket"] = (df["Crash_Hour"] // 2) * 2

def violation_group(v):
    v = str(v).upper()
    if "22350" in v: return "Unsafe Speed"
    if "23152" in v: return "DUI / Alcohol"
    if "22107" in v: return "Unsafe Turn / Lane Change"
    if "21658" in v: return "Lane Violation"
    if "21802" in v: return "Stop Sign / Right of Way"
    if "21804" in v: return "Failure to Yield"
    return "Other / Unclassified"

df["Violation_Group"] = df["Primary_Collision_Factor_Violation"].apply(violation_group)

features = [
    "Source_Year",
    "LightingDescription",
    "Collision_Type_Description",
    "PedestrianActionDesc",
    "Weather_1",
    "City_Name",
    "Crash_Time_2H_Bucket",
    "Violation_Group"
]

target = "Fatal_Flag"
df_model = df[features + [target]].fillna("UNKNOWN")
X = df_model[features]
y = df_model[target]

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.25, random_state=42, stratify=y
)

preprocessor = ColumnTransformer(
    transformers=[("cat", OneHotEncoder(handle_unknown="ignore"), features)]
)

model = LogisticRegression(max_iter=1000, class_weight="balanced", n_jobs=-1)

pipeline = Pipeline(steps=[("preprocessor", preprocessor), ("model", model)])

print("Training model...")
pipeline.fit(X_train, y_train)

print("Predicting...")
y_pred = pipeline.predict(X_test)
y_prob = pipeline.predict_proba(X_test)[:, 1]

print("CONFUSION MATRIX")
print(confusion_matrix(y_test, y_pred))
print("CLASSIFICATION REPORT")
print(classification_report(y_test, y_pred, digits=4))
print("ROC AUC")
print(roc_auc_score(y_test, y_prob))

# File: ml_fatal_crash_model_v2_train_2019_2025_test_2026.py
# Purpose: Train on dbo.Crashes (2019-2025) and validate on dbo.Crashes_2026.

import pandas as pd
from sqlalchemy import create_engine
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder
from sklearn.pipeline import Pipeline
from sklearn.linear_model import LogisticRegression
from sklearn.metrics import classification_report, confusion_matrix, roc_auc_score

server = "JCDCOMPUTER"
database = "CaliforniaCrashes"
driver = "ODBC Driver 17 for SQL Server"

connection_string = (
    f"mssql+pyodbc://@{server}/{database}"
    f"?driver={driver.replace(' ', '+')}"
    f"&trusted_connection=yes"
)
engine = create_engine(connection_string)

def violation_group(v):
    v = str(v).upper()
    if "22350" in v: return "Unsafe Speed"
    if "23152" in v: return "DUI / Alcohol"
    if "22107" in v: return "Unsafe Turn / Lane Change"
    if "21658" in v: return "Lane Violation"
    if "21802" in v: return "Stop Sign / Right of Way"
    if "21804" in v: return "Failure to Yield"
    return "Other / Unclassified"

def prepare_df(df):
    df["Crash_Date_Time"] = pd.to_datetime(
        df["Crash_Date_Time"],
        format="%m/%d/%Y %I:%M:%S %p",
        errors="coerce"
    )
    df = df.dropna(subset=["Crash_Date_Time"])
    df["Crash_Hour"] = df["Crash_Date_Time"].dt.hour
    df["Crash_Time_2H_Bucket"] = (df["Crash_Hour"] // 2) * 2
    df["Violation_Group"] = df["Primary_Collision_Factor_Violation"].apply(violation_group)
    return df

features = [
    "LightingDescription",
    "Collision_Type_Description",
    "PedestrianActionDesc",
    "Weather_1",
    "City_Name",
    "Crash_Time_2H_Bucket",
    "Violation_Group"
]
target = "Fatal_Flag"

query_train = """
SELECT
    LightingDescription,
    Collision_Type_Description,
    PedestrianActionDesc,
    Weather_1,
    City_Name,
    Crash_Date_Time,
    Primary_Collision_Factor_Violation,
    CASE WHEN TRY_CAST(NumberKilled AS INT) > 0 THEN 1 ELSE 0 END AS Fatal_Flag
FROM dbo.Crashes
WHERE Crash_Date_Time IS NOT NULL;
"""

query_test = """
SELECT
    LightingDescription,
    Collision_Type_Description,
    PedestrianActionDesc,
    Weather_1,
    City_Name,
    Crash_Date_Time,
    Primary_Collision_Factor_Violation,
    CASE WHEN TRY_CAST(NumberKilled AS INT) > 0 THEN 1 ELSE 0 END AS Fatal_Flag
FROM dbo.Crashes_2026
WHERE Crash_Date_Time IS NOT NULL;
"""

print("Reading TRAIN data 2019-2025...")
train_df = pd.read_sql(query_train, engine)
print("Reading TEST data 2026...")
test_df = pd.read_sql(query_test, engine)

train_df = prepare_df(train_df)
test_df = prepare_df(test_df)

train_model = train_df[features + [target]].fillna("UNKNOWN")
test_model = test_df[features + [target]].fillna("UNKNOWN")

X_train = train_model[features]
y_train = train_model[target]
X_test = test_model[features]
y_test = test_model[target]

preprocessor = ColumnTransformer(
    transformers=[("cat", OneHotEncoder(handle_unknown="ignore"), features)]
)

model = LogisticRegression(
    max_iter=1000,
    class_weight="balanced",
    n_jobs=-1
)

pipeline = Pipeline(steps=[("preprocessor", preprocessor), ("model", model)])

print("Training on 2019-2025...")
pipeline.fit(X_train, y_train)

print("Predicting 2026...")
y_pred = pipeline.predict(X_test)
y_prob = pipeline.predict_proba(X_test)[:, 1]

print("CONFUSION MATRIX")
print(confusion_matrix(y_test, y_pred))
print("CLASSIFICATION REPORT")
print(classification_report(y_test, y_pred, digits=4))
print("ROC AUC")
print(roc_auc_score(y_test, y_prob))

# File: ml_fatal_crash_model_v3_random_forest_importance.py
# Purpose: Train Random Forest and export feature importance for explainability.

import pandas as pd
from sqlalchemy import create_engine
from sklearn.compose import ColumnTransformer
from sklearn.preprocessing import OneHotEncoder
from sklearn.pipeline import Pipeline
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import classification_report, confusion_matrix, roc_auc_score

server = "JCDCOMPUTER"
database = "CaliforniaCrashes"
driver = "ODBC Driver 17 for SQL Server"

connection_string = (
    f"mssql+pyodbc://@{server}/{database}"
    f"?driver={driver.replace(' ', '+')}"
    f"&trusted_connection=yes"
)
engine = create_engine(connection_string)

def violation_group(v):
    v = str(v).upper()
    if "22350" in v: return "Unsafe Speed"
    if "23152" in v: return "DUI / Alcohol"
    if "22107" in v: return "Unsafe Turn / Lane Change"
    if "21658" in v: return "Lane Violation"
    if "21802" in v: return "Stop Sign / Right of Way"
    if "21804" in v: return "Failure to Yield"
    return "Other / Unclassified"

def prepare_df(df):
    df["Crash_Date_Time"] = pd.to_datetime(
        df["Crash_Date_Time"],
        format="%m/%d/%Y %I:%M:%S %p",
        errors="coerce"
    )
    df = df.dropna(subset=["Crash_Date_Time"])
    df["Crash_Hour"] = df["Crash_Date_Time"].dt.hour
    df["Crash_Time_2H_Bucket"] = (df["Crash_Hour"] // 2) * 2
    df["Violation_Group"] = df["Primary_Collision_Factor_Violation"].apply(violation_group)
    return df

features = [
    "LightingDescription",
    "Collision_Type_Description",
    "PedestrianActionDesc",
    "Weather_1",
    "City_Name",
    "Crash_Time_2H_Bucket",
    "Violation_Group"
]
target = "Fatal_Flag"

base_query = """
SELECT
    LightingDescription,
    Collision_Type_Description,
    PedestrianActionDesc,
    Weather_1,
    City_Name,
    Crash_Date_Time,
    Primary_Collision_Factor_Violation,
    CASE WHEN TRY_CAST(NumberKilled AS INT) > 0 THEN 1 ELSE 0 END AS Fatal_Flag
FROM {table}
WHERE Crash_Date_Time IS NOT NULL;
"""

train_df = pd.read_sql(base_query.format(table="dbo.Crashes"), engine)
test_df = pd.read_sql(base_query.format(table="dbo.Crashes_2026"), engine)

train_df = prepare_df(train_df)
test_df = prepare_df(test_df)

train_model = train_df[features + [target]].fillna("UNKNOWN")
test_model = test_df[features + [target]].fillna("UNKNOWN")

X_train = train_model[features]
y_train = train_model[target]
X_test = test_model[features]
y_test = test_model[target]

preprocessor = ColumnTransformer(
    transformers=[("cat", OneHotEncoder(handle_unknown="ignore"), features)]
)

rf = RandomForestClassifier(
    n_estimators=120,
    max_depth=18,
    min_samples_leaf=50,
    class_weight="balanced",
    random_state=42,
    n_jobs=-1
)

pipeline = Pipeline(steps=[("preprocessor", preprocessor), ("model", rf)])

print("Training Random Forest on 2019-2025...")
pipeline.fit(X_train, y_train)

print("Predicting 2026...")
y_pred = pipeline.predict(X_test)
y_prob = pipeline.predict_proba(X_test)[:, 1]

print("CONFUSION MATRIX")
print(confusion_matrix(y_test, y_pred))
print("CLASSIFICATION REPORT")
print(classification_report(y_test, y_pred, digits=4))
print("ROC AUC")
print(roc_auc_score(y_test, y_prob))

print("Extracting feature importance...")
ohe = pipeline.named_steps["preprocessor"].named_transformers_["cat"]
feature_names = ohe.get_feature_names_out(features)
importances = pipeline.named_steps["model"].feature_importances_

importance_df = pd.DataFrame({"Feature": feature_names, "Importance": importances}) \
    .sort_values("Importance", ascending=False)

print("TOP 40 FEATURE IMPORTANCE")
print(importance_df.head(40).to_string(index=False))
importance_df.to_excel("rf_feature_importance_v3.xlsx", index=False)
print("DONE - Feature importance exported to rf_feature_importance_v3.xlsx")