Example datasets: iris and penguins

These examples load two small, well-known datasets over HTTPS so you do not need a duckboost checkout. For a support-ticket NLP example that pairs duckboost with community quackformers, see Local ticket NLP.

Dataset	Rows	Columns	Source
Iris	150	4 measurements (cm) and species	R’s datasets::iris via Rdatasets (Fisher, 1936; Anderson, 1935)
Palmer penguins	344	species, island, 4 measurements, sex, year	palmerpenguins (Horst, Hill, and Gorman, 2020), CC0

Iris is read from Rdatasets and renamed to snake_case (sepal_length rather than R’s Sepal.Length) to match the penguins style. The values are R’s, which differ from the UCI copy in rows 35 and 38. Penguins comes from the package CSV, with missing values written as NA (nullstr = 'NA').

Released DuckDB autoloads httpfs for HTTPS read_csv. Each block on this page starts a new in-memory database. Blocks that continue an earlier step silently recreate the tables that step made.

Each dataset gets a multiclass species model, a binary model, and a regression model.

Iris

Load and look

LOAD duckboost;
CREATE TABLE iris AS
SELECT
  "Sepal.Length" AS sepal_length,
  "Sepal.Width" AS sepal_width,
  "Petal.Length" AS petal_length,
  "Petal.Width" AS petal_width,
  Species AS species
FROM read_csv('https://raw.githubusercontent.com/vincentarelbundock/Rdatasets/master/csv/datasets/iris.csv');
SELECT
  species,
  count(*) AS n,
  round(avg(sepal_length), 3) AS sepal_length,
  round(avg(sepal_width), 3) AS sepal_width,
  round(avg(petal_length), 3) AS petal_length,
  round(avg(petal_width), 3) AS petal_width
FROM iris
GROUP BY species
ORDER BY species;

┌────────────┬────┬──────────────┬─────────────┬──────────────┬─────────────┐
│  species   │ n  │ sepal_length │ sepal_width │ petal_length │ petal_width │
├────────────┼────┼──────────────┼─────────────┼──────────────┼─────────────┤
│ setosa     │ 50 │ 5.006        │ 3.428       │ 1.462        │ 0.246       │
│ versicolor │ 50 │ 5.936        │ 2.77        │ 4.26         │ 1.326       │
│ virginica  │ 50 │ 6.588        │ 2.974       │ 5.552        │ 2.026       │
└────────────┴────┴──────────────┴─────────────┴──────────────┴─────────────┘

Split into train and test

This page splits by hand, so you can see exactly what happens. duckboost_initial_split does the same job in one line, with a random-looking but reproducible assignment; see Splitting data and preprocessing. This split is stratified by species and deterministic: within each species, rows are ranked by their measurements and every fifth row is held out. That gives 40 training and 10 test rows per species, and the same split on every run.

LOAD duckboost;
CREATE TABLE iris AS
SELECT
  "Sepal.Length" AS sepal_length,
  "Sepal.Width" AS sepal_width,
  "Petal.Length" AS petal_length,
  "Petal.Width" AS petal_width,
  Species AS species
FROM read_csv('https://raw.githubusercontent.com/vincentarelbundock/Rdatasets/master/csv/datasets/iris.csv');
CREATE TABLE iris_split AS
SELECT
  *,
  CASE
    WHEN row_number() OVER (
      PARTITION BY species
      ORDER BY sepal_length, sepal_width, petal_length, petal_width
    ) % 5 = 0 THEN 'test'
    ELSE 'train'
  END AS split
FROM iris;

SELECT split, species, count(*) AS n
FROM iris_split
GROUP BY ALL
ORDER BY ALL;

┌───────┬────────────┬────┐
│ split │  species   │ n  │
├───────┼────────────┼────┤
│ test  │ setosa     │ 10 │
│ test  │ versicolor │ 10 │
│ test  │ virginica  │ 10 │
│ train │ setosa     │ 40 │
│ train │ versicolor │ 40 │
│ train │ virginica  │ 40 │
└───────┴────────────┴────┘

Multiclass: which species?

A multiclass label must be an integer class index starting at 0, so map the species names with CASE. The model has one tree per class per round, duckboost_predict returns the class index, and duckboost_predict_proba returns one probability per class.

LOAD duckboost;
CREATE TABLE iris AS
SELECT
  "Sepal.Length" AS sepal_length,
  "Sepal.Width" AS sepal_width,
  "Petal.Length" AS petal_length,
  "Petal.Width" AS petal_width,
  Species AS species
FROM read_csv('https://raw.githubusercontent.com/vincentarelbundock/Rdatasets/master/csv/datasets/iris.csv');
CREATE TABLE iris_split AS
SELECT
  *,
  CASE
    WHEN row_number() OVER (
      PARTITION BY species
      ORDER BY sepal_length, sepal_width, petal_length, petal_width
    ) % 5 = 0 THEN 'test'
    ELSE 'train'
  END AS split
FROM iris;

CREATE TABLE iris_multi AS
SELECT
  species,
  CASE species WHEN 'setosa' THEN 0 WHEN 'versicolor' THEN 1 ELSE 2 END::DOUBLE AS label,
  [sepal_length, sepal_width, petal_length, petal_width] AS features,
  split
FROM iris_split;

CREATE TABLE models AS
SELECT duckboost_train(
  label, features,
  MAP {'task': 'multiclass', 'n_estimators': '30', 'max_depth': '2', 'learning_rate': '0.3'}
) AS model
FROM iris_multi
WHERE split = 'train';

SELECT
  round(duckboost_evaluate_agg(model, label, features, MAP {'metric': 'accuracy'}), 3) AS test_accuracy,
  round(duckboost_evaluate_agg(model, label, features, MAP {'metric': 'logloss'}), 3) AS test_logloss
FROM models, iris_multi
WHERE split = 'test';

┌───────────────┬──────────────┐
│ test_accuracy │ test_logloss │
├───────────────┼──────────────┤
│ 0.933         │ 0.505        │
└───────────────┴──────────────┘

A confusion matrix shows which species get mixed up on the test rows. Setosa is always right; the errors, if any, are between versicolor and virginica.

SELECT
  species AS actual,
  count(*) FILTER (WHERE duckboost_predict(model, features) = 0) AS pred_setosa,
  count(*) FILTER (WHERE duckboost_predict(model, features) = 1) AS pred_versicolor,
  count(*) FILTER (WHERE duckboost_predict(model, features) = 2) AS pred_virginica
FROM models, iris_multi
WHERE split = 'test'
GROUP BY species
ORDER BY species;

┌────────────┬─────────────┬─────────────────┬────────────────┐
│   actual   │ pred_setosa │ pred_versicolor │ pred_virginica │
├────────────┼─────────────┼─────────────────┼────────────────┤
│ setosa     │ 10          │ 0               │ 0              │
│ versicolor │ 0           │ 9               │ 1              │
│ virginica  │ 0           │ 1               │ 9              │
└────────────┴─────────────┴─────────────────┴────────────────┘

SELECT
  species,
  (['setosa', 'versicolor', 'virginica'])[duckboost_predict(model, features)::INTEGER + 1] AS predicted,
  list_transform(duckboost_predict_proba(model, features), lambda p: round(p, 3)) AS proba
FROM models, iris_multi
WHERE split = 'test'
ORDER BY list_max(duckboost_predict_proba(model, features))
LIMIT 5;

┌────────────┬────────────┬─────────────────────┐
│  species   │ predicted  │        proba        │
├────────────┼────────────┼─────────────────────┤
│ virginica  │ virginica  │ [0.0, 0.27, 0.73]   │
│ virginica  │ versicolor │ [0.0, 0.774, 0.226] │
│ versicolor │ versicolor │ [0.0, 0.983, 0.017] │
│ virginica  │ virginica  │ [0.0, 0.0, 1.0]     │
│ virginica  │ virginica  │ [0.0, 0.0, 1.0]     │
└────────────┴────────────┴─────────────────────┘

These are the five test flowers the model is least sure about.

Binary: virginica or versicolor?

Setosa is separable from the other two species by petal size alone, so this task drops it and asks the harder question: is a flower virginica rather than versicolor? The binary task needs a 0/1 label.

CREATE TABLE iris_binary AS
SELECT
  (species = 'virginica')::DOUBLE AS is_virginica,
  [sepal_length, sepal_width, petal_length, petal_width] AS features,
  split
FROM iris_split
WHERE species <> 'setosa';

CREATE TABLE models AS
SELECT duckboost_train(
  is_virginica, features,
  MAP {
    'task': 'binary',
    'n_estimators': '30',
    'max_depth': '2',
    'learning_rate': '0.3',
    'feature_names': 'sepal_length,sepal_width,petal_length,petal_width'
  }
) AS model
FROM iris_binary
WHERE split = 'train';

SELECT
  round(duckboost_evaluate_agg(model, is_virginica, features, MAP {'metric': 'accuracy'}), 3) AS test_accuracy,
  round(duckboost_evaluate_agg(model, is_virginica, features, MAP {'metric': 'logloss'}), 3) AS test_logloss
FROM models, iris_binary
WHERE split = 'test';

┌───────────────┬──────────────┐
│ test_accuracy │ test_logloss │
├───────────────┼──────────────┤
│ 0.9           │ 0.508        │
└───────────────┴──────────────┘

duckboost_predict returns the probability of the positive class for a binary model:

SELECT
  species,
  features,
  round(duckboost_predict(model, features), 3) AS p_virginica
FROM models, iris_binary
WHERE split = 'test'
ORDER BY p_virginica
LIMIT 6;

┌────────────┬──────────────────────┬─────────────┐
│  species   │       features       │ p_virginica │
├────────────┼──────────────────────┼─────────────┤
│ versicolor │ [7.0, 3.2, 4.7, 1.4] │ 0.0         │
│ versicolor │ [6.0, 3.4, 4.5, 1.6] │ 0.0         │
│ versicolor │ [5.6, 3.0, 4.1, 1.3] │ 0.001       │
│ versicolor │ [5.2, 2.7, 3.9, 1.4] │ 0.001       │
│ versicolor │ [5.7, 2.9, 4.2, 1.3] │ 0.001       │
│ versicolor │ [6.4, 2.9, 4.3, 1.3] │ 0.001       │
└────────────┴──────────────────────┴─────────────┘

Regression with one-hot species

To predict petal_width from the other measurements and the species, turn species into numeric columns first. duckboost only accepts DOUBLE features, so one-hot encoding is a comparison cast to DOUBLE per level. Trees do not need a reference level dropped, so every level gets a column.

The baseline column is the RMSE of always predicting the training mean.

CREATE TABLE iris_reg AS
SELECT
  petal_width,
  [
    sepal_length,
    sepal_width,
    petal_length,
    (species = 'setosa')::DOUBLE,
    (species = 'versicolor')::DOUBLE,
    (species = 'virginica')::DOUBLE
  ] AS features,
  split
FROM iris_split;

CREATE TABLE models AS
SELECT duckboost_train(
  petal_width, features,
  MAP {
    'n_estimators': '50',
    'max_depth': '3',
    'learning_rate': '0.1',
    'feature_names': 'sepal_length,sepal_width,petal_length,species_setosa,species_versicolor,species_virginica'
  }
) AS model
FROM iris_reg
WHERE split = 'train';

SELECT
  round(duckboost_evaluate_agg(model, petal_width, features, MAP {'metric': 'rmse'}), 3) AS test_rmse,
  round(sqrt(avg(power(
    petal_width - (SELECT avg(petal_width) FROM iris_reg WHERE split = 'train'), 2
  ))), 3) AS baseline_rmse
FROM models, iris_reg
WHERE split = 'test';

┌───────────┬───────────────┐
│ test_rmse │ baseline_rmse │
├───────────┼───────────────┤
│ 0.187     │ 0.732         │
└───────────┴───────────────┘

Palmer penguins

Load and handle missing values

The file writes missing values as NA, so pass nullstr = 'NA'. Two penguins have no measurements, and eleven (including those two) have no recorded sex.

LOAD duckboost;
CREATE TABLE penguins AS FROM read_csv('https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv', nullstr = 'NA');
SELECT
  count(*) AS n,
  count(*) - count(bill_length_mm) AS missing_measurements,
  count(*) - count(sex) AS missing_sex,
  count(*) FILTER (WHERE sex IS NOT NULL AND bill_length_mm IS NOT NULL) AS complete_rows
FROM penguins;

┌─────┬──────────────────────┬─────────────┬───────────────┐
│  n  │ missing_measurements │ missing_sex │ complete_rows │
├─────┼──────────────────────┼─────────────┼───────────────┤
│ 344 │ 2                    │ 11          │ 333           │
└─────┴──────────────────────┴─────────────┴───────────────┘

LOAD duckboost;
CREATE TABLE penguins AS FROM read_csv('https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv', nullstr = 'NA');
SELECT species, island, count(*) AS n, round(avg(body_mass_g)) AS avg_body_mass_g
FROM penguins
GROUP BY ALL
ORDER BY ALL;

┌───────────┬───────────┬─────┬─────────────────┐
│  species  │  island   │  n  │ avg_body_mass_g │
├───────────┼───────────┼─────┼─────────────────┤
│ Adelie    │ Biscoe    │ 44  │ 3710.0          │
│ Adelie    │ Dream     │ 56  │ 3688.0          │
│ Adelie    │ Torgersen │ 52  │ 3706.0          │
│ Chinstrap │ Dream     │ 68  │ 3733.0          │
│ Gentoo    │ Biscoe    │ 124 │ 5076.0          │
└───────────┴───────────┴─────┴─────────────────┘

Preprocess and split three ways

This step keeps the 333 complete rows, one-hot encodes species, island, and sex, and splits train / validation / test (about 60 / 20 / 20). The split is stratified by species and sex. The validation rows are for choosing hyperparameters, so the test rows only score the final model. It is written out by hand. Splitting data and preprocessing makes the same kind of split with duckboost_initial_validation_split, explains when you need a validation set at all, and has a complete example that imputes missing values instead of dropping those rows.

LOAD duckboost;
CREATE TABLE penguins AS FROM read_csv('https://raw.githubusercontent.com/allisonhorst/palmerpenguins/master/inst/extdata/penguins.csv', nullstr = 'NA');
CREATE TABLE penguins_prep AS
SELECT
  species,
  body_mass_g::DOUBLE AS body_mass_g,
  (sex = 'male')::DOUBLE AS is_male,
  bill_length_mm,
  bill_depth_mm,
  flipper_length_mm::DOUBLE AS flipper_length_mm,
  (species = 'Adelie')::DOUBLE AS species_adelie,
  (species = 'Chinstrap')::DOUBLE AS species_chinstrap,
  (species = 'Gentoo')::DOUBLE AS species_gentoo,
  (island = 'Biscoe')::DOUBLE AS island_biscoe,
  (island = 'Dream')::DOUBLE AS island_dream,
  (island = 'Torgersen')::DOUBLE AS island_torgersen,
  CASE row_number() OVER (
      PARTITION BY species, sex
      ORDER BY bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g, island, year
    ) % 5
    WHEN 0 THEN 'test'
    WHEN 1 THEN 'validation'
    ELSE 'train'
  END AS split
FROM penguins
WHERE sex IS NOT NULL AND bill_length_mm IS NOT NULL;

SELECT split, count(*) AS n
FROM penguins_prep
GROUP BY split
ORDER BY split;

┌────────────┬─────┐
│   split    │  n  │
├────────────┼─────┤
│ test       │ 63  │
│ train      │ 201 │
│ validation │ 69  │
└────────────┴─────┘

Multiclass: species from measurements

The classic penguins question: which species is a bird, given only its bill, flipper, and body mass measurements? The model is trained on train and validation together, since nothing is tuned here.

CREATE TABLE species AS
SELECT
  species,
  CASE species WHEN 'Adelie' THEN 0 WHEN 'Chinstrap' THEN 1 ELSE 2 END::DOUBLE AS label,
  [bill_length_mm, bill_depth_mm, flipper_length_mm, body_mass_g] AS features,
  split
FROM penguins_prep;

CREATE TABLE models AS
SELECT duckboost_train(
  label, features,
  MAP {
    'task': 'multiclass',
    'n_estimators': '30',
    'max_depth': '3',
    'learning_rate': '0.3',
    'feature_names': 'bill_length_mm,bill_depth_mm,flipper_length_mm,body_mass_g'
  }
) AS model
FROM species
WHERE split <> 'test';

SELECT
  species AS actual,
  count(*) AS n,
  count(*) FILTER (WHERE duckboost_predict(model, features) = 0) AS pred_adelie,
  count(*) FILTER (WHERE duckboost_predict(model, features) = 1) AS pred_chinstrap,
  count(*) FILTER (WHERE duckboost_predict(model, features) = 2) AS pred_gentoo
FROM models, species
WHERE split = 'test'
GROUP BY species
ORDER BY species;

┌───────────┬────┬─────────────┬────────────────┬─────────────┐
│  actual   │ n  │ pred_adelie │ pred_chinstrap │ pred_gentoo │
├───────────┼────┼─────────────┼────────────────┼─────────────┤
│ Adelie    │ 28 │ 28          │ 0              │ 0           │
│ Chinstrap │ 12 │ 1           │ 11             │ 0           │
│ Gentoo    │ 23 │ 0           │ 0              │ 23          │
└───────────┴────┴─────────────┴────────────────┴─────────────┘

Regression: body mass

Predict body_mass_g from the bill and flipper measurements, sex, species, and island. The model is trained on train and validation together, since nothing is tuned here.

CREATE TABLE mass AS
SELECT
  body_mass_g,
  [
    bill_length_mm, bill_depth_mm, flipper_length_mm, is_male,
    species_adelie, species_chinstrap, species_gentoo,
    island_biscoe, island_dream, island_torgersen
  ] AS features,
  split
FROM penguins_prep;

CREATE TABLE models AS
SELECT duckboost_train(
  body_mass_g, features,
  MAP {'n_estimators': '100', 'max_depth': '3', 'learning_rate': '0.1'}
) AS model
FROM mass
WHERE split <> 'test';

SELECT
  round(duckboost_evaluate_agg(model, body_mass_g, features, MAP {'metric': 'rmse'}), 1) AS test_rmse_g,
  round(duckboost_evaluate_agg(model, body_mass_g, features, MAP {'metric': 'mae'}), 1) AS test_mae_g,
  round(sqrt(avg(power(
    body_mass_g - (SELECT avg(body_mass_g) FROM mass WHERE split <> 'test'), 2
  ))), 1) AS baseline_rmse_g
FROM models, mass
WHERE split = 'test';

┌─────────────┬────────────┬─────────────────┐
│ test_rmse_g │ test_mae_g │ baseline_rmse_g │
├─────────────┼────────────┼─────────────────┤
│ 314.0       │ 240.7      │ 812.6           │
└─────────────┴────────────┴─────────────────┘

Binary with a grid search: sex

Predict whether a penguin is male from its measurements and species. duckboost has no built-in tuner, but duckboost_train is an aggregate, so a grid search is a cross join with a table of hyperparameter values and a GROUP BY. Options that should not vary, such as n_estimators here, stay fixed in the MAP.

Each grid row trains one model on the training rows and is scored on the validation rows:

CREATE TABLE grid AS
SELECT depth::VARCHAR AS max_depth, lr::VARCHAR AS learning_rate
FROM (VALUES (1), (2), (3)) d(depth), (VALUES (0.1), (0.3)) l(lr);

CREATE TABLE models AS
SELECT
  max_depth,
  learning_rate,
  duckboost_train(
    is_male, features,
    MAP {
      'task': 'binary',
      'n_estimators': '50',
      'max_depth': max_depth,
      'learning_rate': learning_rate
    }
  ) AS model
FROM sex, grid
WHERE split = 'train'
GROUP BY max_depth, learning_rate;

CREATE TABLE tuning AS
SELECT
  max_depth,
  learning_rate,
  round(duckboost_evaluate_agg(model, is_male, features, MAP {'metric': 'logloss'}), 4) AS validation_logloss,
  round(duckboost_evaluate_agg(model, is_male, features, MAP {'metric': 'accuracy'}), 3) AS validation_accuracy
FROM models, sex
WHERE split = 'validation'
GROUP BY max_depth, learning_rate, model;

FROM tuning ORDER BY validation_logloss;

┌───────────┬───────────────┬────────────────────┬─────────────────────┐
│ max_depth │ learning_rate │ validation_logloss │ validation_accuracy │
├───────────┼───────────────┼────────────────────┼─────────────────────┤
│ 3         │ 0.1           │ 0.2476             │ 0.913               │
│ 2         │ 0.1           │ 0.2639             │ 0.899               │
│ 1         │ 0.3           │ 0.2655             │ 0.884               │
│ 2         │ 0.3           │ 0.277              │ 0.913               │
│ 3         │ 0.3           │ 0.2985             │ 0.928               │
│ 1         │ 0.1           │ 0.3578             │ 0.913               │
└───────────┴───────────────┴────────────────────┴─────────────────────┘

Refit the combination with the lowest validation log loss on train and validation together, then score the untouched test rows once:

CREATE TABLE best AS
FROM tuning ORDER BY validation_logloss LIMIT 1;

CREATE TABLE final AS
SELECT
  best.max_depth,
  best.learning_rate,
  duckboost_train(
    is_male, features,
    MAP {
      'task': 'binary',
      'n_estimators': '50',
      'max_depth': best.max_depth,
      'learning_rate': best.learning_rate
    }
  ) AS model
FROM sex, best
WHERE split <> 'test'
GROUP BY best.max_depth, best.learning_rate;

SELECT
  max_depth,
  learning_rate,
  round(duckboost_evaluate_agg(model, is_male, features, MAP {'metric': 'accuracy'}), 3) AS test_accuracy,
  round(duckboost_evaluate_agg(model, is_male, features, MAP {'metric': 'logloss'}), 4) AS test_logloss
FROM final, sex
WHERE split = 'test'
GROUP BY max_depth, learning_rate, model;

┌───────────┬───────────────┬───────────────┬──────────────┐
│ max_depth │ learning_rate │ test_accuracy │ test_logloss │
├───────────┼───────────────┼───────────────┼──────────────┤
│ 3         │ 0.1           │ 0.937         │ 0.2374       │
└───────────┴───────────────┴───────────────┴──────────────┘

Citations

• Anderson, E. (1935). The irises of the Gaspé Peninsula. Bulletin of the American Iris Society, 59, 2–5.
• Fisher, R. A. (1936). The use of multiple measurements in taxonomic problems. Annals of Eugenics, 7(2), 179–188.
• Horst, A. M., Hill, A. P., and Gorman, K. B. (2020). palmerpenguins: Palmer Archipelago (Antarctica) penguin data. R package version 0.1.0. https://allisonhorst.github.io/palmerpenguins/. doi:10.5281/zenodo.3960218.
• Gorman, K. B., Williams, T. D., and Fraser, W. R. (2014). Ecological sexual dimorphism and environmental variability within a community of Antarctic penguins (genus Pygoscelis). PLoS ONE, 9(3), e90081. doi:10.1371/journal.pone.0090081.