Statistics and Machine Learning Toolbox: Modeling guide

Complete tabular schema, feature preprocessing, hyperparameter controls, cross-validation partitioning, output layouts, and custom dataset authoring.

← Statistics and Machine Learning Toolbox overview · Getting started · Methods guide · Examples · Reference

Tabular modeling in Excel

The Statistics and Machine Learning Toolbox operates on clean tabular data layouts. Rather than requiring procedural Python scripts, the workbook defines models declaratively through structured tables. The underlying runtime ingests these tables, validates numerical consistency, trains the specified algorithm, and writes inferences directly back to dedicated worksheet ranges.

flowchart LR
    Start["Start\n(Controls & Catalog)"] --> Runtime["Marimo Engine\n(Preproc + Solver)"]
    Data["Data\n(Features & Target)"] --> Runtime
    Config["Model Config\n(Hyperparameters)"] --> Runtime
    Runtime --> Results["Results\n(Metrics & Weights)"]
    Runtime --> Predictions["Predictions\n(Row Inferences)"]
    Runtime --> Diagnostics["Diagnostics\n(7 Health Checks)"]

Worksheet schema and data structures

The workbook organizes all model configurations into three input tables:

1. Start sheet: Model controls (`Start!A5:B10`)

The control table governs global execution parameters:

Cell	Parameter	Type	Allowed Values	Default	Purpose
`Start!B6`	Active model	Dropdown (List)	`customer_churn_logistic`, `housing_regression_ridge`, `iris_clustering_kmeans`, `credit_default_tree`, `feature_pca`	`customer_churn_logistic`	Selects the active model dataset and algorithm family.
`Start!B7`	Test size	Decimal	\(0.00\) to \(0.49\)	`0.25`	Proportion of data reserved for out-of-sample holdout testing.
`Start!B8`	Random seed	Integer	\(\ge 0\)	`42`	Deterministic random state for splits, centroid seeding, and solvers.
`Start!B9`	CV folds	Integer	\(2\) to \(5\)	`3`	Number of cross-validation folds for out-of-fold scoring.
`Start!B10`	Scale features	Dropdown (List)	`Standard`, `MinMax`, `None`	`Standard`	Feature preprocessing normalization mode.

2. Data sheet: Dataset table (`Data!A5:G70`)

Observations are organized by Model tag. The active model filters rows matching Start!B6:

Column	Header	Data Type	Role	Description
A	`Model`	Text	Key	Model ID binding the row to the catalog.
B	`SampleID`	Integer / Text	Index	Unique sample or customer identifier.
C	`Feature1`	Numeric	Predictor	Continuous or ordinal feature measurement.
D	`Feature2`	Numeric	Predictor	Continuous or ordinal feature measurement.
E	`Feature3`	Numeric	Predictor	Continuous or ordinal feature measurement.
F	`Feature4`	Numeric	Predictor	Continuous or ordinal feature measurement.
G	`Target`	Numeric	Ground Truth	Classification label (\(0\) or \(1\)), regression target, or ground truth cluster.

[!NOTE] For unsupervised tasks (feature_pca), the Target column is ignored by the solver but preserved for validation indexing.

3. Model Config sheet: Hyperparameters (`Model Config!A5:D20`)

Algorithm hyperparameters are configured per model family:

Column	Header	Description
A	`Model`	Associated Model ID
B	`Hyperparameter`	Exact parameter name (`C`, `alpha`, `n_clusters`, `max_depth`, `n_components`, `max_iter`)
C	`Value`	User-editable numeric parameter value
D	`Description`	Mathematical role and tuning guidance

Feature preprocessing and scaling

Feature normalization is critical for gradient-based linear models, regularization penalties, distance calculations, and orthogonal decomposition. The toolbox provides three scaling modes via Start!B10:

Standard scaling (\(z\)-score)

Transforms each feature column \(X_{*, j}\) to have zero mean and unit variance:

\[z_{i, j} = \frac{x_{i, j} - \mu_j}{\sigma_j}\]

where \(\mu_j = \frac{1}{n} \sum_{i=1}^n x_{i, j}\) and \(\sigma_j = \sqrt{\frac{1}{n} \sum_{i=1}^n (x_{i, j} - \mu_j)^2}\).

When to use: Default recommendation. Essential for Logistic Regression, Ridge, K-Means, and PCA to ensure features with large raw scales do not dominate the objective.

Min-Max scaling

Scales each feature column into the unit interval \([0, 1]\):

\[z_{i, j} = \frac{x_{i, j} - \min(X_{*, j})}{\max(X_{*, j}) - \min(X_{*, j})}\]

When to use: When features have bounded physical domains or when preserving exact zero entries in sparse matrices is required.

None (Raw pass-through)

Passes raw feature values directly to the estimator without transformation:

\[z_{i, j} = x_{i, j}\]

When to use: Decision tree classifiers (which are invariant to monotonic transformations) or when features have already been standardized.
Risk: Linear and distance models will experience numerical conditioning degradation (high \(\kappa\)) and distorted regularizer weights.

Train / test partitioning mechanics

The toolbox implements dynamic holdout partitioning governed by Start!B7 (Test size):

flowchart TD
    N["Total Samples (n)"] --> Split{"Test size > 0?"}
    Split -->|"Yes (e.g. 0.25)"| Part["train_test_split(seed=42)"]
    Split -->|"No (0.00)"| AllTrain["All samples assigned to Train"]
    Part --> Strat{"Classification Task?"}
    Strat -->|"Yes"| StratSplit["Stratified Holdout\n(preserves class proportions)"]
    Strat -->|"No"| RandSplit["Uniform Random Holdout\n(regression & clustering)"]
    StratSplit --> Tag["Row tags assigned in Predictions!Split\n('Train' vs 'Test')"]
    RandSplit --> Tag
    AllTrain --> Tag

Stratification: For classification tasks (customer_churn_logistic, credit_default_tree), train_test_split enforces stratify=y. This ensures the minority class is represented proportionally in both training and test partitions.
Reproducibility: Partitioning is locked to Start!B8 (Random seed), ensuring identical splits across recalculations.
Auditability: The split assignment is published on every row in Predictions!Split (Train or Test), allowing immediate identification of holdout errors.

Cross-validation mechanics

To assess true generalization performance beyond the training sample, the toolbox automatically computes out-of-fold cross-validation:

Fold count: Governed by Start!B9 (CV folds), clamped to \([2, 5]\).
Classification CV: Uses StratifiedKFold. Each fold contains approximately equal class balances. If a dataset has very small minority counts (e.g., in credit_default_tree), the CV fold count is clamped to min(cv_k, min_class_count) to prevent single-class training folds.
Regression & Clustering CV: Uses standard KFold.
CV Metric: Evaluates the primary task metric on out-of-fold validation sets and publishes the arithmetic mean to Results!B15.
Generalization Gap: The diagnostics engine monitors the absolute difference: \[\Delta_{\text{gen}} = |M_{\text{train}} - M_{\text{CV}}|\] A gap \(\le 0.15\) passes; \(\le 0.25\) triggers a warning; \(> 0.25\) triggers a failure, indicating severe overfitting.

Hyperparameter configuration guide

The notebook ingests hyperparameters from Model Config!A5:D20 and applies strict boundary validation:

Model ID	Parameter	Default	Min Bound	Max Bound	Boundary Violation Action	Tuning Recommendation
`customer_churn_logistic`	`C`	`1.0`	\(10^{-4}\)	\(10^4\)	Clamped; Diagnostic Check 2 flags `FAIL`	Decrease \(C\) if overfitting (\(\Delta_{\text{gen}} > 0.15\)); increase if underfitting.
`customer_churn_logistic`	`max_iter`	`200`	\(10\)	\(5000\)	Clamped; Diagnostic Check 2 flags `FAIL`	Increase to 500+ if solver reports convergence warnings.
`housing_regression_ridge`	`alpha`	`1.0`	\(10^{-5}\)	\(10^5\)	Clamped; Diagnostic Check 2 flags `FAIL`	Increase \(\alpha\) when multicollinearity (\(\kappa > 30\)) is high.
`housing_regression_ridge`	`max_iter`	`1000`	\(10\)	\(5000\)	Clamped; Diagnostic Check 2 flags `FAIL`	1000 is sufficient for analytical SVD solves.
`iris_clustering_kmeans`	`n_clusters`	`3`	\(2\)	\(\min(n-1, 10)\)	Clamped; Diagnostic Check 2 flags `FAIL`	Choose \(k\) by inspecting the Silhouette score peak.
`iris_clustering_kmeans`	`max_iter`	`300`	\(10\)	\(5000\)	Clamped; Diagnostic Check 2 flags `FAIL`	Lloyd’s algorithm typically converges in \(< 50\) iterations.
`credit_default_tree`	`max_depth`	`3`	\(1\)	\(20\)	Clamped; Diagnostic Check 2 flags `FAIL`	Keep \(\le 3\) on small datasets (\(n < 50\)) to avoid branch memorization.
`feature_pca`	`n_components`	`2`	\(1\)	\(\min(n, p)\)	Clamped; Diagnostic Check 2 flags `FAIL`	Select \(k\) such that cumulative explained variance \(\ge 0.80\).

Published output schemas

The Marimo reactive pipeline writes to three publication sheets:

1. Results sheet

Model summary (`Results!A5:B16`)

A5:B5 — Headers: Metric, Value
B6 — Active model label
B7 — Task type (Classification, Regression, Clustering, Decomposition)
B8 — Solver status (OPTIMAL)
B9 — Scikit-learn estimator class
B10 — Total sample count (\(n\))
B11 — Feature count (\(p\))
B12 — Primary performance metric (Accuracy / \(R^2\) / Silhouette / Explained Variance)
B13 — Secondary performance metric (F1 score / RMSE / Inertia / PC1 Variance Ratio)
B14 — Tertiary performance metric (ROC-AUC / MAE / Calinski-Harabasz / PC2 Variance Ratio)
B15 — Out-of-fold Cross-Validation Mean
B16 — Feature scaling method used

Feature coefficients / importances (`Results!D5:I16`)

Features are sorted in strictly descending order by AbsImportance, with deterministic alphabetical tie-breaking: - Feature (Col D): Feature column name (Feature1 to Feature4). - Weight (Col E): Signed regression weight, PCA loading, or cluster variance share. - OddsRatio (Col F): \(\exp(w_j)\) for logistic regression; \(0.0\) for other estimators. - AbsImportance (Col G): Absolute magnitude \(|w_j|\) used for ranking. - Rank (Col H): Integer rank (\(1, 2, 3, 4\)). - Significance (Col I): Semantic category (High, Moderate, ClusterFeature, GiniImportance, PC1_Loading).

2. Predictions sheet (`Predictions!A5:F55`)

SampleID (Col A): Identifier from input data.
Actual (Col B): Ground truth value from Data!Target.
Predicted (Col C): Predicted class, estimated price, cluster index, or PC1 coordinate.
Probability / Residual / Dist (Col D):
- Classification: Predicted class probability \(P(y=1)\).
- Regression: Prediction residual \(y_i - \hat{y}_i\).
- Clustering: Euclidean distance to assigned cluster centroid.
- PCA: PC2 coordinate or reconstruction residual.
Split (Col E): Holdout partition tag (Train or Test).
Correct (Col F): Hit/miss verification (YES, NO, Residual, ClusterAssigned, PCA).

3. Diagnostics sheet (`Diagnostics!A5:C25`)

Outputs 7 standardized rows: - Check (Col A): Check name. - Severity (Col B): PASS, WARN, or FAIL. - Detail (Col C): Specific numerical evidence and threshold comparisons.

Authoring custom datasets

To run machine learning on your own business datasets:

Step 1: Format your data table

Ensure your data has numeric features and an appropriate target column: 1. Open the Data sheet. 2. Under the Model column, assign a unique name (or replace rows for an existing model ID, e.g., customer_churn_logistic). 3. Fill Feature1 through Feature4 with numeric values. Ensure no text or blank cells exist. 4. Fill Target with \(0/1\) for classification or continuous numbers for regression.

Step 2: Configure hyperparameters on Model Config

If using a custom model ID, add corresponding hyperparameter entries to Model Config: - Specify rows matching your custom Model ID and parameters (C, alpha, etc.).

Step 3: Register in Start catalog

Add a row to the Model catalog on Start!D5:H10: - Model: Exact Model ID matching your Data rows. - Label: Friendly display title. - Task: Classification, Regression, Clustering, or Decomposition. - Algorithm: Estimator class name. - Description: Brief explanation of the use case.

Step 4: Run and diagnose

Select your new model on Start!B6. Review the Results, Predictions, and Diagnostics sheets. If any diagnostic check flags WARN or FAIL, follow the remediation guidance in the Reference guide.