Scripts/create_notebooks.py

import os
import nbformat as nbf

def generate_notebooks():
    notebooks_dir = "Notebooks"
    os.makedirs(notebooks_dir, exist_ok=True)
    
    # -------------------------------------------------------------
    # 1. CREATE DATA CLEANING & EDA NOTEBOOK
    # -------------------------------------------------------------
    nb1 = nbf.v4.new_notebook()
    
    cells1 = [
        nbf.v4.new_markdown_cell(
            "# Phase 1: Data Cleaning & Exploratory Data Analysis (EDA)\n"
            "This notebook details the exploratory analysis, profiling, and visualization "
            "of the DataCo Supply Chain Dataset, as well as executing SQL analysis queries."
        ),
        nbf.v4.new_code_cell(
            "import os\n"
            "import pandas as pd\n"
            "import numpy as np\n"
            "import matplotlib.pyplot as plt\n"
            "import seaborn as sns\n"
            "from sqlalchemy import create_engine, text\n\n"
            "sns.set_theme(style='whitegrid')\n"
            "plt.rcParams['figure.figsize'] = (10, 6)"
        ),
        nbf.v4.new_markdown_cell(
            "## 1. Load and Inspect Cleaned Data"
        ),
        nbf.v4.new_code_cell(
            "cleaned_csv_path = os.path.join('..', 'Data', 'cleaned_data.csv')\n"
            "if not os.path.exists(cleaned_csv_path):\n"
            "    cleaned_csv_path = os.path.join('Data', 'cleaned_data.csv')\n\n"
            "df = pd.read_csv(cleaned_csv_path)\n"
            "print(f'Dataset shape: {df.shape}')\n"
            "df.head()"
        ),
        nbf.v4.new_markdown_cell(
            "## 2. Target Variable Analysis\n"
            "Analyze the target variable `late_delivery_risk` which represents whether the order was delayed (1) or not (0)."
        ),
        nbf.v4.new_code_cell(
            "target_counts = df['late_delivery_risk'].value_counts()\n"
            "target_pct = df['late_delivery_risk'].value_counts(normalize=True) * 100\n"
            "print('Target Distribution:')\n"
            "for val, count in target_counts.items():\n"
            "    print(f'  Class {val}: {count} orders ({target_pct[val]:.2f}%)')\n\n"
            "plt.figure(figsize=(6, 4))\n"
            "sns.countplot(x='late_delivery_risk', data=df, palette='viridis')\n"
            "plt.title('Distribution of Late Delivery Risk')\n"
            "plt.xlabel('Late Delivery Risk (0 = On Time/Early, 1 = Delayed)')\n"
            "plt.ylabel('Count')\n"
            "plt.show()"
        ),
        nbf.v4.new_markdown_cell(
            "## 3. Shipping Mode vs Delay Risk\n"
            "Let's see if different shipping modes are more prone to delay."
        ),
        nbf.v4.new_code_cell(
            "plt.figure(figsize=(10, 5))\n"
            "sns.countplot(x='shipping_mode', hue='late_delivery_risk', data=df, palette='muted')\n"
            "plt.title('Late Delivery Risk by Shipping Mode')\n"
            "plt.xlabel('Shipping Mode')\n"
            "plt.ylabel('Count')\n"
            "plt.xticks(rotation=15)\n"
            "plt.legend(title='Delay Risk', labels=['On Time/Early', 'Delayed'])\n"
            "plt.show()\n\n"
            "# Get exact percentages\n"
            "shipping_delay_pct = df.groupby('shipping_mode')['late_delivery_risk'].mean() * 100\n"
            "print('Late Delivery Rate by Shipping Mode:')\n"
            "print(shipping_delay_pct.sort_values(ascending=False))"
        ),
        nbf.v4.new_markdown_cell(
            "## 4. Market and Region vs Delay Risk"
        ),
        nbf.v4.new_code_cell(
            "plt.figure(figsize=(12, 6))\n"
            "region_delay_pct = df.groupby('order_region')['late_delivery_risk'].mean().sort_values(ascending=False) * 100\n"
            "sns.barplot(x=region_delay_pct.values, y=region_delay_pct.index, palette='crest')\n"
            "plt.title('Late Delivery Rate (%) by Order Region')\n"
            "plt.xlabel('Late Delivery Rate (%)')\n"
            "plt.ylabel('Order Region')\n"
            "plt.show()"
        ),
        nbf.v4.new_markdown_cell(
            "## 5. Simple SQL Queries\n"
            "Here are some basic SQL queries to inspect the tables, count records, and run simple aggregates."
        ),
        nbf.v4.new_code_cell(
            "# Establish connection to MySQL database\n"
            "engine = create_engine('mysql+mysqlconnector://root:admin123@localhost:3306/supply_chain_db')\n\n"
            "def run_query(sql_query):\n"
            "    with engine.connect() as conn:\n"
            "        return pd.read_sql(text(sql_query), conn)\n\n"
            "# 1. Select first 5 products\n"
            "run_query('SELECT product_card_id, product_name, product_price FROM products LIMIT 5;')"
        ),
        nbf.v4.new_code_cell(
            "# 2. Count total customers and orders\n"
            "run_query('SELECT (SELECT COUNT(*) FROM customers) AS total_customers, (SELECT COUNT(*) FROM orders) AS total_orders;')"
        ),
        nbf.v4.new_code_cell(
            "# 3. Show order count grouped by customer segment\n"
            "run_query('SELECT customer_segment, COUNT(*) AS count FROM customers GROUP BY customer_segment;')"
        ),
        nbf.v4.new_markdown_cell(
            "## 6. Advanced SQL Analysis Queries\n"
            "We can run more complex analytical queries to answer deeper supply chain questions."
        ),
        nbf.v4.new_code_cell(
            "# Query 1: Late delivery rate and shipping days comparison\n"
            "q1 = '''\n"
            "SELECT \n"
            "    shipping_mode,\n"
            "    COUNT(*) AS total_orders,\n"
            "    SUM(CASE WHEN late_delivery_risk = 1 THEN 1 ELSE 0 END) AS delayed_orders,\n"
            "    ROUND(SUM(CASE WHEN late_delivery_risk = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS late_delivery_rate_percent,\n"
            "    ROUND(AVG(days_for_shipping_real), 2) AS avg_actual_shipping_days,\n"
            "    ROUND(AVG(days_for_shipping_scheduled), 2) AS avg_scheduled_shipping_days\n"
            "FROM orders\n"
            "GROUP BY shipping_mode\n"
            "ORDER BY late_delivery_rate_percent DESC;\n"
            "'''\n"
            "run_query(q1)"
        ),
        nbf.v4.new_code_cell(
            "# Query 2: Top 5 categories with highest late delivery rate\n"
            "q2 = '''\n"
            "SELECT \n"
            "    cat.category_name,\n"
            "    COUNT(DISTINCT o.order_id) AS total_orders,\n"
            "    SUM(CASE WHEN o.late_delivery_risk = 1 THEN 1 ELSE 0 END) AS delayed_orders,\n"
            "    ROUND(SUM(CASE WHEN o.late_delivery_risk = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT o.order_id), 2) AS late_delivery_rate_percent\n"
            "FROM orders o\n"
            "JOIN order_items oi ON o.order_id = oi.order_id\n"
            "JOIN products p ON oi.order_item_cardprod_id = p.product_card_id\n"
            "JOIN categories cat ON p.product_category_id = cat.category_id\n"
            "GROUP BY cat.category_name\n"
            "HAVING total_orders > 50\n"
            "ORDER BY late_delivery_rate_percent DESC\n"
            "LIMIT 5;\n"
            "'''\n"
            "run_query(q2)"
        )
    ]
    
    nb1['cells'] = cells1
    
    # Save notebook 1
    notebook1_path = os.path.join(notebooks_dir, "1_Data_Cleaning_EDA.ipynb")
    with open(notebook1_path, "w", encoding="utf-8") as f:
        nbf.write(nb1, f)
    print(f"Generated {notebook1_path}")

    # -------------------------------------------------------------
    # 2. CREATE MACHINE LEARNING NOTEBOOK
    # -------------------------------------------------------------
    nb2 = nbf.v4.new_notebook()
    
    cells2 = [
        nbf.v4.new_markdown_cell(
            "# Phase 2: Supply Chain Delay Prediction (Machine Learning)\n"
            "This notebook details the training, comparison, and evaluation of "
            "Random Forest and XGBoost classifiers to predict whether shipments will be delayed."
        ),
        nbf.v4.new_code_cell(
            "import os\n"
            "import pickle\n"
            "import pandas as pd\n"
            "import numpy as np\n"
            "import matplotlib.pyplot as plt\n"
            "import seaborn as sns\n"
            "from sklearn.model_selection import train_test_split\n"
            "from sklearn.preprocessing import LabelEncoder, StandardScaler\n"
            "from sklearn.ensemble import RandomForestClassifier\n"
            "from sklearn.metrics import classification_report, accuracy_score, roc_auc_score, confusion_matrix, roc_curve\n"
            "import xgboost as xgb\n\n"
            "sns.set_theme(style='whitegrid')"
        ),
        nbf.v4.new_markdown_cell(
            "## 1. Load Data and Engineer Features"
        ),
        nbf.v4.new_code_cell(
            "cleaned_csv_path = os.path.join('..', 'Data', 'cleaned_data.csv')\n"
            "if not os.path.exists(cleaned_csv_path):\n"
            "    cleaned_csv_path = os.path.join('Data', 'cleaned_data.csv')\n\n"
            "df = pd.read_csv(cleaned_csv_path)\n\n"
            "# 1. Drop Leakage columns\n"
            "leakage_cols = ['days_for_shipping_real', 'delivery_status', 'shipping_date', 'order_status']\n"
            "id_text_cols = ['customer_fname', 'customer_lname', 'customer_street', 'customer_zipcode', \n"
            "                'order_zipcode', 'product_name', 'category_name', 'department_name']\n"
            "df_features = df.drop(columns=leakage_cols + id_text_cols)\n\n"
            "# 2. Date features\n"
            "df_features['order_date'] = pd.to_datetime(df_features['order_date'])\n"
            "df_features['order_year'] = df_features['order_date'].dt.year\n"
            "df_features['order_month'] = df_features['order_date'].dt.month\n"
            "df_features['order_day'] = df_features['order_date'].dt.day\n"
            "df_features['order_hour'] = df_features['order_date'].dt.hour\n"
            "df_features['order_dayofweek'] = df_features['order_date'].dt.dayofweek\n"
            "df_features['is_weekend'] = df_features['order_dayofweek'].isin([5, 6]).astype(int)\n\n"
            "            # Advanced Cross-border & distance proxy features\n"
            "df_features['is_domestic'] = (df_features['customer_country'] == df_features['order_country']).astype(int)\n"
            "df_features['is_same_state'] = (df_features['customer_state'] == df_features['order_state']).astype(int)\n"
            "df_features['is_same_city'] = (df_features['customer_city'] == df_features['order_city']).astype(int)\n"
            "df_features['discount_amount'] = df_features['sales'] * df_features['order_item_discount_rate']\n"
            "df_features['price_per_item'] = df_features['sales'] / (df_features['order_item_quantity'] + 1e-5)\n\n"
            "df_features = df_features.drop(columns=['order_date'])\n\n"
            "print(f'Feature dimensions: {df_features.shape}')"
        ),
        nbf.v4.new_markdown_cell(
            "## 2. Encode Categorical Variables"
        ),
        nbf.v4.new_code_cell(
            "categorical_cols = ['type', 'customer_segment', 'customer_city', 'customer_state', 'customer_country',\n"
            "                    'market', 'order_city', 'order_state', 'order_country', 'order_region', 'shipping_mode']\n\n"
            "label_encoders = {}\n"
            "for col in categorical_cols:\n"
            "    if col in df_features.columns:\n"
            "        df_features[col] = df_features[col].fillna('Unknown').astype(str)\n"
            "        le = LabelEncoder()\n"
            "        df_features[col] = le.fit_transform(df_features[col])\n"
            "        label_encoders[col] = le\n"
            "print('Categorical variables encoded.')"
        ),
        nbf.v4.new_markdown_cell(
            "## 3. Train-Test Split and Scaling"
        ),
        nbf.v4.new_code_cell(
            "X = df_features.drop(columns=['late_delivery_risk'])\n"
            "y = df_features['late_delivery_risk']\n\n"
            "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42, stratify=y)\n\n"
            "# Scaling\n"
            "numerical_cols = ['benefit_per_order', 'sales_per_customer', 'latitude', 'longitude',\n"
            "                  'order_item_discount', 'order_item_discount_rate', 'order_item_product_price',\n"
            "                  'order_item_profit_ratio', 'order_item_quantity', 'sales', 'order_item_total',\n"
            "                  'order_profit_per_order', 'product_price', 'days_for_shipping_scheduled',\n"
            "                  'discount_amount', 'price_per_item']\n"
            "numerical_cols = [col for col in numerical_cols if col in X.columns]\n\n"
            "scaler = StandardScaler()\n"
            "X_train[numerical_cols] = scaler.fit_transform(X_train[numerical_cols])\n"
            "X_test[numerical_cols] = scaler.transform(X_test[numerical_cols])\n\n"
            "print(f'Train size: {X_train.shape[0]}, Test size: {X_test.shape[0]}')"
        ),
        nbf.v4.new_markdown_cell(
            "## 4. Train Models\n"
            "We train Random Forest and XGBoost classifiers."
        ),
        nbf.v4.new_code_cell(
            "# 1. Random Forest Classifier\n"
            "rf_model = RandomForestClassifier(n_estimators=100, max_depth=12, random_state=42, n_jobs=-1)\n"
            "rf_model.fit(X_train, y_train)\n"
            "rf_preds = rf_model.predict(X_test)\n"
            "rf_probs = rf_model.predict_proba(X_test)[:, 1]\n\n"
            "print('Random Forest Accuracy:', accuracy_score(y_test, rf_preds))\n"
            "print('Random Forest ROC-AUC:', roc_auc_score(y_test, rf_probs))"
        ),
        nbf.v4.new_code_cell(
            "# 2. XGBoost Classifier (Tuned)\n"
            "xgb_model = xgb.XGBClassifier(\n"
            "    n_estimators=300,\n"
            "    max_depth=8,\n"
            "    learning_rate=0.05,\n"
            "    subsample=0.8,\n"
            "    colsample_bytree=0.8,\n"
            "    random_state=42,\n"
            "    eval_metric='logloss',\n"
            "    n_jobs=-1\n"
            ")\n"
            "xgb_model.fit(X_train, y_train)\n"
            "xgb_preds = xgb_model.predict(X_test)\n"
            "xgb_probs = xgb_model.predict_proba(X_test)[:, 1]\n\n"
            "print('XGBoost Accuracy:', accuracy_score(y_test, xgb_preds))\n"
            "print('XGBoost ROC-AUC:', roc_auc_score(y_test, xgb_probs))"
        ),
        nbf.v4.new_markdown_cell(
            "## 5. Detailed Evaluation (XGBoost)\n"
            "Let's print the classification report, plot the confusion matrix, and plot the ROC curve."
        ),
        nbf.v4.new_code_cell(
            "print('XGBoost Classification Report:')\n"
            "print(classification_report(y_test, xgb_preds))\n\n"
            "cm = confusion_matrix(y_test, xgb_preds)\n"
            "plt.figure(figsize=(6, 5))\n"
            "sns.heatmap(cm, annot=True, fmt='d', cmap='Blues', xticklabels=['On Time', 'Delayed'], yticklabels=['On Time', 'Delayed'])\n"
            "plt.title('Confusion Matrix (XGBoost)')\n"
            "plt.ylabel('Actual')\n"
            "plt.xlabel('Predicted')\n"
            "plt.show()"
        ),
        nbf.v4.new_code_cell(
            "# Plot ROC Curves\n"
            "rf_fpr, rf_tpr, _ = roc_curve(y_test, rf_probs)\n"
            "xgb_fpr, xgb_tpr, _ = roc_curve(y_test, xgb_probs)\n\n"
            "plt.figure(figsize=(8, 6))\n"
            "plt.plot(rf_fpr, rf_tpr, label=f'Random Forest (AUC = {roc_auc_score(y_test, rf_probs):.3f})')\n"
            "plt.plot(xgb_fpr, xgb_tpr, label=f'XGBoost (AUC = {roc_auc_score(y_test, xgb_probs):.3f})')\n"
            "plt.plot([0, 1], [0, 1], 'k--', label='Random Guess')\n"
            "plt.title('ROC Curves Comparison')\n"
            "plt.xlabel('False Positive Rate')\n"
            "plt.ylabel('True Positive Rate')\n"
            "plt.legend(loc='lower right')\n"
            "plt.show()"
        ),
        nbf.v4.new_markdown_cell(
            "## 6. Feature Importance"
        ),
        nbf.v4.new_code_cell(
            "importances = xgb_model.feature_importances_\n"
            "feat_imp = pd.Series(importances, index=X.columns).sort_values(ascending=False).head(10)\n\n"
            "plt.figure(figsize=(10, 6))\n"
            "sns.barplot(x=feat_imp.values, y=feat_imp.index, palette='viridis')\n"
            "plt.title('Top 10 Feature Importances (XGBoost)')\n"
            "plt.xlabel('Importance')\n"
            "plt.ylabel('Feature')\n"
            "plt.show()"
        )
    ]
    
    nb2['cells'] = cells2
    
    # Save notebook 2
    notebook2_path = os.path.join(notebooks_dir, "2_Delay_Prediction_ML.ipynb")
    with open(notebook2_path, "w", encoding="utf-8") as f:
        nbf.write(nb2, f)
    print(f"Generated {notebook2_path}")
    print("All notebooks created successfully!")

if __name__ == "__main__":
    generate_notebooks()