File size: 17,007 Bytes
6c2997c 7c354e2 6c2997c 7c354e2 6c2997c 7c354e2 6c2997c 8956b1e 6c2997c 8956b1e 6c2997c 8956b1e 6c2997c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 | 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()
|