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()