compute-predictions-to-excel-with-automl
Read a file "/home/piotr/Documents/charges/insurance_historical.xlsx"
Thinking
Determining file selection
I need to figure out the file selection process first. The user has provided an explicit path, and I’ll focus on the code to load and inspect the file. It's important to determine if the file is an Excel document and then convert it into a DataFrame (df). This step is crucial before moving forward with any further analysis or operations on the data.
</>Load insurance data
import pandas as pd
df = pd.read_excel("/home/piotr/Documents/charges/insurance_historical.xlsx")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()Shape: (1238, 7) Columns (7): ['age', 'sex', 'bmi', 'children', 'smoker', 'region', 'charges']
| age | sex | bmi | children | smoker | region | charges | |
|---|---|---|---|---|---|---|---|
| 0 | 19 | female | 27.900 | 0 | yes | southwest | 16884.92400 |
| 1 | 18 | male | 33.770 | 1 | no | southeast | 1725.55230 |
| 2 | 28 | male | 33.000 | 3 | no | southeast | 4449.46200 |
| 3 | 33 | male | 22.705 | 0 | no | northwest | 21984.47061 |
| 4 | 32 | male | 28.880 | 0 | no | northwest | 3866.85520 |
The workbook has been read into df with 1,238 records and 7 fields. It contains demographic and policy attributes (age, sex, bmi, children, smoker, region) alongside the insurance cost outcome, charges.
Train an AutoML model to predict charges
charges is a continuous target, so this is a regression task. The available predictors contain no identifier-like fields to exclude.
</>Fit AutoML regression
from supervised.automl import AutoML
X = df.drop(columns="charges")
y = df["charges"]
automl = AutoML(
mode="Explain",
ml_task="regression",
results_path="insurance_charges_automl",
random_state=42,
)
automl.fit(X, y)
report = automl.report_structured()
print(report)
automl.report()AutoML directory: insurance_charges_automl The task is regression with evaluation metric rmse AutoML will use algorithms: ['Baseline', 'Linear', 'Decision Tree', 'Random Forest', 'Xgboost', 'Neural Network'] AutoML is generating and selecting models automatically. Review outputs before important use. AutoML will ensemble available models AutoML steps: ['simple_algorithms', 'default_algorithms', 'ensemble'] * Step simple_algorithms will try to check up to 3 models 1_Baseline rmse 11837.203081 trained in 0.44 seconds
/home/piotr/.config/mljar-studio/jlab_server/lib/python3.12/site-packages/sklearn/tree/_classes.py:1351: FutureWarning: Value `"friedman_mse"` for `criterion` is deprecated and will be removed in 1.11. It maps to `"squared_error"` as both were always equivalent. Use `criterion="squared_error"` to remove this warning.
2_DecisionTree rmse 4649.463036 trained in 3.85 seconds 3_Linear rmse 6077.089454 trained in 3.79 seconds * Step default_algorithms will try to check up to 3 models 4_Default_Xgboost rmse 4917.773106 trained in 45.5 seconds 5_Default_NeuralNetwork rmse 4950.48459 trained in 2.6 seconds 6_Default_RandomForest rmse 4545.946127 trained in 5.45 seconds * Step ensemble will try to check up to 1 model Ensemble rmse 4512.614975 trained in 0.19 seconds AutoML fit time: 68.13 seconds AutoML best model: Ensemble # MLJAR AutoML Report > Models in this report were generated and selected automatically by MLJAR AutoML. Review model behavior, data suitability, and decision impact before important use. ## Leaderboard | name | model_type | metric_type | metric_value | train_time | |:------------------------|:---------------|:--------------|---------------:|-------------:| | 1_Baseline | Baseline | rmse | 11837.2 | 0.74 | | 2_DecisionTree | Decision Tree | rmse | 4649.46 | 4.29 | | 3_Linear | Linear | rmse | 6077.09 | 4.39 | | 4_Default_Xgboost | Xgboost | rmse | 4917.77 | 45.97 | | 5_Default_NeuralNetwork | Neural Network | rmse | 4950.48 | 2.96 | | 6_Default_RandomForest | Random Forest | rmse | 4545.95 | 6.11 | | Ensemble | Ensemble | rmse | 4512.61 | 0.19 | ## Global Feature Importance (Averaged Across Models) Method: `mean_rank_across_models` Models used: `5`, Features: `6` ### Most Influential Features (Top 3 by mean rank) | Feature | Mean Rank | |:----------|------------:| | smoker | 1 | | bmi | 2.2 | | age | 2.8 | ### Least Influential Features (Bottom 3 by mean rank) | Feature | Mean Rank | |:----------|------------:| | children | 4 | | region | 5 | | sex | 6 |
AutoML Leaderboard
Models in this report were generated and selected automatically by MLJAR AutoML. Review model behavior, data suitability, and decision impact before important use.
| Best model | name | model_type | metric_type | metric_value | train_time |
|---|---|---|---|---|---|
| 1_Baseline | Baseline | rmse | 11837.2 | 0.74 | |
| 2_DecisionTree | Decision Tree | rmse | 4649.46 | 4.29 | |
| 3_Linear | Linear | rmse | 6077.09 | 4.39 | |
| 4_Default_Xgboost | Xgboost | rmse | 4917.77 | 45.97 | |
| 5_Default_NeuralNetwork | Neural Network | rmse | 4950.48 | 2.96 | |
| 6_Default_RandomForest | Random Forest | rmse | 4545.95 | 6.11 | |
| the best | Ensemble | Ensemble | rmse | 4512.61 | 0.19 |
AutoML Performance
AutoML Performance Boxplot
Features Importance (Original Scale)
Scaled Features Importance (MinMax per Model)
Spearman Correlation of Models
Summary of 5_Default_NeuralNetwork
Neural Network
- n_jobs: -1
- dense_1_size: 32
- dense_2_size: 16
- learning_rate: 0.05
- explain_level: 2
Validation
- validation_type: split
- train_ratio: 0.75
- shuffle: True
Optimized metric
rmse
Training time
2.6 seconds
Metric details:
| Metric | Score |
|---|---|
| MAE | 2805.09 |
| MSE | 2.45073e+07 |
| RMSE | 4950.48 |
| R2 | 0.824703 |
| MAPE | 0.317221 |
Learning curves
Permutation-based Importance
True vs Predicted
Predicted vs Residuals
Summary of 2_DecisionTree
Decision Tree
- n_jobs: -1
- criterion: friedman_mse
- max_depth: 4
- explain_level: 2
Validation
- validation_type: split
- train_ratio: 0.75
- shuffle: True
Optimized metric
rmse
Training time
3.8 seconds
Metric details:
| Metric | Score |
|---|---|
| MAE | 2750.64 |
| MSE | 2.16175e+07 |
| RMSE | 4649.46 |
| R2 | 0.845374 |
| MAPE | 0.348553 |
Learning curves
Permutation-based Importance
True vs Predicted
Predicted vs Residuals
SHAP Importance
SHAP Dependence plots
Dependence (Fold 1)
SHAP Decision plots
Top-10 Worst decisions (Fold 1)
Top-10 Best decisions (Fold 1)
Summary of 1_Baseline
Baseline Regressor (Baseline)
- n_jobs: -1
- explain_level: 2
Validation
- validation_type: split
- train_ratio: 0.75
- shuffle: True
Optimized metric
rmse
Training time
0.4 seconds
Metric details:
| Metric | Score |
|---|---|
| MAE | 8903.64 |
| MSE | 1.40119e+08 |
| RMSE | 11837.2 |
| R2 | -0.00225088 |
| MAPE | 1.60448 |
Learning curves
True vs Predicted
Predicted vs Residuals
Summary of Ensemble
Ensemble structure
| Model | Weight |
|---|---|
| 2_DecisionTree | 1 |
| 5_Default_NeuralNetwork | 1 |
| 6_Default_RandomForest | 3 |
Metric details:
| Metric | Score |
|---|---|
| MAE | 2493.07 |
| MSE | 2.03637e+07 |
| RMSE | 4512.61 |
| R2 | 0.854342 |
| MAPE | 0.334347 |
Learning curves
True vs Predicted
Predicted vs Residuals
Summary of 6_Default_RandomForest
Random Forest
- n_jobs: -1
- criterion: squared_error
- max_features: 0.9
- min_samples_split: 30
- max_depth: 4
- eval_metric_name: rmse
- explain_level: 2
Validation
- validation_type: split
- train_ratio: 0.75
- shuffle: True
Optimized metric
rmse
Training time
5.4 seconds
Metric details:
| Metric | Score |
|---|---|
| MAE | 2628.83 |
| MSE | 2.06656e+07 |
| RMSE | 4545.95 |
| R2 | 0.852182 |
| MAPE | 0.355469 |
Learning curves
Permutation-based Importance
True vs Predicted
Predicted vs Residuals
SHAP Importance
SHAP Dependence plots
Dependence (Fold 1)
SHAP Decision plots
Top-10 Worst decisions (Fold 1)
Top-10 Best decisions (Fold 1)
Summary of 3_Linear
Linear Regression (Linear)
- n_jobs: -1
- explain_level: 2
Validation
- validation_type: split
- train_ratio: 0.75
- shuffle: True
Optimized metric
rmse
Training time
3.8 seconds
Metric details:
| Metric | Score |
|---|---|
| MAE | 4099.63 |
| MSE | 3.6931e+07 |
| RMSE | 6077.09 |
| R2 | 0.735839 |
| MAPE | 0.451905 |
Learning curves
Coefficients
| feature | Learner_1 |
|---|---|
| smoker | 0.797924 |
| age | 0.292291 |
| bmi | 0.179434 |
| children | 0.0538462 |
| intercept | -4.27937e-18 |
| sex | -0.019646 |
| region | -0.0423077 |
Permutation-based Importance
True vs Predicted
Predicted vs Residuals
SHAP Importance
SHAP Dependence plots
Dependence (Fold 1)
SHAP Decision plots
Top-10 Worst decisions (Fold 1)
Top-10 Best decisions (Fold 1)
Summary of 4_Default_Xgboost
Extreme Gradient Boosting (Xgboost)
- n_jobs: -1
- objective: reg:squarederror
- eta: 0.075
- max_depth: 6
- min_child_weight: 1
- subsample: 1.0
- colsample_bytree: 1.0
- eval_metric: rmse
- explain_level: 2
Validation
- validation_type: split
- train_ratio: 0.75
- shuffle: True
Optimized metric
rmse
Training time
45.5 seconds
Metric details:
| Metric | Score |
|---|---|
| MAE | 2830.25 |
| MSE | 2.41845e+07 |
| RMSE | 4917.77 |
| R2 | 0.827012 |
| MAPE | 0.391656 |
Learning curves
Permutation-based Importance
True vs Predicted
Predicted vs Residuals
The AutoML regression model was trained on 1,238 insurance records using all six available predictors. MLJAR selected an ensemble as the best model.
- Best validation RMSE: 4,512.61
- Best single model: Random Forest, RMSE 4,545.95
- Baseline RMSE: 11,837.20
The ensemble reduces prediction error by about 62% relative to the baseline. Its typical error is approximately 4,513 USD per predicted charge, measured by RMSE. The ensemble is therefore the recommended trained model for predicting charges.
explain the model performance in simple terms
The model predicts insurance charges much better than a simple average-based guess.
- Best model: An ensemble, which combines multiple models.
- Prediction error (RMSE): about 4,513 USD. In simple terms, a typical prediction can be off by roughly 4,500 USD, with larger mistakes weighted more heavily.
- Baseline error: about 11,837 USD. This baseline would largely predict a general average charge for everyone.
- Improvement: the trained ensemble reduced error by about 62% compared with the baseline.
Among individual models, the Random Forest was strongest, with an error of about 4,546 USD. Combining models improved this slightly to 4,513 USD.
This is a useful model for estimating charges, but it should not be treated as exact: actual charges can still differ substantially from predictions, especially for unusual customer situations.
Read a file "/home/piotr/Documents/charges/insurance_new_customers.xlsx"
Thinking
Inspecting file selection
I'm thinking we need to read and inspect only the first load of data. The file selection has to be explicit, which makes sense. It sounds like I need to ensure that the code is written in one block and that the columns are shaped according to the exact header requirements. This seems important for the accuracy of whatever I'm working on, so I'll make sure everything aligns properly!
Load and inspect the new-customer file before generating charge predictions.
</>Load new customer data
new_customers = pd.read_excel("/home/piotr/Documents/charges/insurance_new_customers.xlsx")
print(f"Shape: {new_customers.shape}")
columns = new_customers.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
new_customers.head()Shape: (100, 6) Columns (6): ['age', 'sex', 'bmi', 'children', 'smoker', 'region']
| age | sex | bmi | children | smoker | region | |
|---|---|---|---|---|---|---|
| 0 | 45 | female | 25.175 | 2 | no | northeast |
| 1 | 36 | female | 30.020 | 0 | no | northwest |
| 2 | 64 | female | 26.885 | 0 | yes | northwest |
| 3 | 46 | male | 25.745 | 3 | no | northwest |
| 4 | 19 | male | 31.920 | 0 | yes | northwest |
The file contains 100 new customer records and 6 predictor fields: age, sex, BMI, number of children, smoking status, and region. Its schema matches the historical model’s input features, with no charges column—as expected for records that need predictions.
Use the trained AutoML model to predict charges for the new customers
The new-customer data matches the model’s six training features. Create predictions and display the first rows with predicted charges.
</>Predict charges for customers
predicted_customers = new_customers.copy()
predicted_customers["predicted_charges"] = automl.predict(new_customers)
predicted_customers.head()| age | sex | bmi | children | smoker | region | predicted_charges | |
|---|---|---|---|---|---|---|---|
| 0 | 45 | female | 25.175 | 2 | no | northeast | 10079.179694 |
| 1 | 36 | female | 30.020 | 0 | no | northwest | 5952.460288 |
| 2 | 64 | female | 26.885 | 0 | yes | northwest | 27089.380112 |
| 3 | 46 | male | 25.745 | 3 | no | northwest | 9773.703266 |
| 4 | 19 | male | 31.920 | 0 | yes | northwest | 34537.330327 |
Predicted annual charges have been generated for all 100 new customers and added as predicted_charges. The sample spans approximately 5,952 USD to 34,537 USD, reflecting the model’s expected variation by customer characteristics such as age and smoking status.
Save the dataframe to insurance_new_customers_predictions.xlsx
</>Save predictions to Excel
output_path = "/home/piotr/Documents/charges/insurance_new_customers_predictions.xlsx"
predicted_customers.to_excel(output_path, index=False)
print(f"Saved predictions to: {output_path}")Saved predictions to: /home/piotr/Documents/charges/insurance_new_customers_predictions.xlsx
The exported workbook contains the 100 new-customer records with all six original input fields plus the predicted_charges column. It was saved at:
/home/piotr/Documents/charges/insurance_new_customers_predictions.xlsx
Read a file "/home/piotr/sandbox/datasets-for-start/insurance/insurance.csv"
Thinking
Loading dataset
I need to read a new prompt from a file. It seems the dataset isn’t currently loaded in my context, and the existing DataFrame is different from what I need. I should load the dataset using pandas and inspect it with the read_csv function. That way, I can ensure I've got everything correctly set up for the task at hand. It’s all about making sure I'm working with the right data!
</>Load insurance data
import pandas as pd
df = pd.read_csv("/home/piotr/sandbox/datasets-for-start/insurance/insurance.csv")
print(f"Shape: {df.shape}")
columns = df.columns.tolist()
print(f"Columns ({len(columns)}):", columns if len(columns) <= 100 else columns[:100] + ["..."])
df.head()Shape: (1338, 7) Columns (7): ['age', 'sex', 'bmi', 'children', 'smoker', 'region', 'charges']
| age | sex | bmi | children | smoker | region | charges | |
|---|---|---|---|---|---|---|---|
| 0 | 19 | female | 27.900 | 0 | yes | southwest | 16884.92400 |
| 1 | 18 | male | 33.770 | 1 | no | southeast | 1725.55230 |
| 2 | 28 | male | 33.000 | 3 | no | southeast | 4449.46200 |
| 3 | 33 | male | 22.705 | 0 | no | northwest | 21984.47061 |
| 4 | 32 | male | 28.880 | 0 | no | northwest | 3866.85520 |
The CSV contains 1,338 insurance records and 7 fields: customer demographics and insurance attributes (age, sex, bmi, children, smoker, region) plus the numeric target charges. The sample confirms mixed categorical and numeric data, with charges recorded as decimal values.
the last 100 rows from the latest file, have real charges for new customers data, compare predictions, make visualization
Thinking
Evaluating predictions and comparisons
I need to use the latest data, specifically the last 100 records, and compare them to earlier predictions that might match historical data. My first step should be to validate and build a comparison between the last 100 actual records and predicted customers. Once I've ensured there's a feature match, I can create a scatter chart to visualize the actual versus predicted values. I’ll run the necessary code without making assumptions and confirm the feature alignment for accuracy.Focusing on a single chart
I realize I don’t need an output table; instead, I’m focusing on creating just one chart. This simplification will help in visualizing the data more effectively without unnecessary clutter. By concentrating on a single chart, I can make sure the insights are clear and easy to understand for the user. So, I’ll proceed with that approach to streamline the presentation and enhance the clarity of the information I'm sharing.
This compares the last 100 records’ actual charges with the existing predictions and plots predicted versus actual charges.
</>Plot predicted vs actual charges
import matplotlib.pyplot as plt
import seaborn as sns
from sklearn.metrics import mean_absolute_error, r2_score
actual_new_customers = df.tail(100).reset_index(drop=True)
comparison = actual_new_customers[["charges"]].copy()
comparison["predicted_charges"] = automl.predict(
actual_new_customers.drop(columns="charges")
)
mae = mean_absolute_error(comparison["charges"], comparison["predicted_charges"])
r2 = r2_score(comparison["charges"], comparison["predicted_charges"])
plt.figure(figsize=(8, 6))
sns.scatterplot(
data=comparison,
x="charges",
y="predicted_charges",
s=65,
alpha=0.75,
)
limits = [
min(comparison["charges"].min(), comparison["predicted_charges"].min()),
max(comparison["charges"].max(), comparison["predicted_charges"].max()),
]
plt.plot(limits, limits, linestyle="--", color="black", label="Perfect prediction")
plt.xlim(limits)
plt.ylim(limits)
plt.title(f"Predicted vs Actual Insurance Charges\nMAE: {mae:,.0f} | R²: {r2:.3f}")
plt.xlabel("Actual charges")
plt.ylabel("Predicted charges")
plt.legend()
plt.tight_layout()
plt.show()
The scatter plot compares actual and predicted charges for the 100 latest customer records, with the dashed diagonal representing perfect predictions. Numeric accuracy results are needed to quantify how closely the points follow that line.
</>Show error metrics
print(f"Mean absolute error: {mae:,.2f} USD")
print(f"R²: {r2:.3f}")
comparison["absolute_error"] = (
comparison["charges"] - comparison["predicted_charges"]
).abs()
comparison[["charges", "predicted_charges", "absolute_error"]].head(10)Mean absolute error: 2,245.14 USD R²: 0.907
| charges | predicted_charges | absolute_error | |
|---|---|---|---|
| 0 | 6985.50695 | 8498.409915 | 1512.902965 |
| 1 | 3238.43570 | 4983.864203 | 1745.428503 |
| 2 | 47269.85400 | 46279.155209 | 990.698791 |
| 3 | 49577.66240 | 46113.910277 | 3463.752123 |
| 4 | 4296.27120 | 7580.719298 | 3284.448098 |
| 5 | 3171.61490 | 5106.751598 | 1935.136698 |
| 6 | 1135.94070 | 3347.659030 | 2211.718330 |
| 7 | 5615.36900 | 7104.366661 | 1488.997661 |
| 8 | 9101.79800 | 9924.701770 | 822.903770 |
| 9 | 6059.17300 | 7207.865234 | 1148.692234 |
For the last 100 records with known charges, predicted charges align closely with actual charges overall: $R^2 = 0.907$, meaning the model explains about 90.7% of charge variation in this set.
The mean absolute error is 2,245.14 USD, so a prediction differs from the real charge by about 2,245 USD on average. The visualization’s points cluster around the perfect-prediction diagonal, though individual customers can have larger errors—for example, some observed examples differ by roughly 3,300 USD.