Maximizing productivity with selectors

blog
new feature
productivity
duckdb
Author

Phillip Cloud

Published

February 27, 2023

Before Ibis 5.0 it’s been challenging to concisely express whole-table operations with ibis. Happily this is no longer the case in ibis 5.0.

Let’s jump right in!

We’ll look at selectors examples using the palmerpenguins data set with the DuckDB backend.

Setup

from ibis.interactive import *

t = ex.penguins.fetch()
t
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ int64             │ int64       │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ Adelie  │ Torgersen │           39.1 │          18.7 │               181 │        3750 │ male   │  2007 │
│ Adelie  │ Torgersen │           39.5 │          17.4 │               186 │        3800 │ female │  2007 │
│ Adelie  │ Torgersen │           40.3 │          18.0 │               195 │        3250 │ female │  2007 │
│ Adelie  │ Torgersen │            nan │           nan │              NULL │        NULL │ NULL   │  2007 │
│ Adelie  │ Torgersen │           36.7 │          19.3 │               193 │        3450 │ female │  2007 │
│ Adelie  │ Torgersen │           39.3 │          20.6 │               190 │        3650 │ male   │  2007 │
│ Adelie  │ Torgersen │           38.9 │          17.8 │               181 │        3625 │ female │  2007 │
│ Adelie  │ Torgersen │           39.2 │          19.6 │               195 │        4675 │ male   │  2007 │
│ Adelie  │ Torgersen │           34.1 │          18.1 │               193 │        3475 │ NULL   │  2007 │
│ Adelie  │ Torgersen │           42.0 │          20.2 │               190 │        4250 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘

Examples

Normalization

Let’s say you want to compute the z-score of every numeric column and replace the existing data with that normalized value. Here’s how you’d do that with selectors:

t.mutate(s.across(s.numeric(), (_ - _.mean()) / _.std()))
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year      ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ float64           │ float64     │ string │ float64   │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────────┤
│ Adelie  │ Torgersen │      -0.883205 │      0.784300 │         -1.416272 │   -0.563317 │ male   │ -1.257484 │
│ Adelie  │ Torgersen │      -0.809939 │      0.126003 │         -1.060696 │   -0.500969 │ female │ -1.257484 │
│ Adelie  │ Torgersen │      -0.663408 │      0.429833 │         -0.420660 │   -1.186793 │ female │ -1.257484 │
│ Adelie  │ Torgersen │            nan │           nan │               nan │         nan │ NULL   │ -1.257484 │
│ Adelie  │ Torgersen │      -1.322799 │      1.088129 │         -0.562890 │   -0.937403 │ female │ -1.257484 │
│ Adelie  │ Torgersen │      -0.846572 │      1.746426 │         -0.776236 │   -0.688012 │ male   │ -1.257484 │
│ Adelie  │ Torgersen │      -0.919837 │      0.328556 │         -1.416272 │   -0.719186 │ female │ -1.257484 │
│ Adelie  │ Torgersen │      -0.864888 │      1.240044 │         -0.420660 │    0.590115 │ male   │ -1.257484 │
│ Adelie  │ Torgersen │      -1.799025 │      0.480471 │         -0.562890 │   -0.906229 │ NULL   │ -1.257484 │
│ Adelie  │ Torgersen │      -0.352029 │      1.543873 │         -0.776236 │    0.060160 │ NULL   │ -1.257484 │
│ …       │ …         │              … │             … │                 … │           … │ …      │         … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────────┘

What’s Up With the year Column?

Whoops, looks like we included year in our normalization because it’s an int64 column (and therefore numeric) but normalizing the year doesn’t make sense.

We can exclude year from the normalization using another selector:

t.mutate(s.across(s.numeric() & ~s.c("year"), (_ - _.mean()) / _.std()))
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ float64           │ float64     │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ Adelie  │ Torgersen │      -0.883205 │      0.784300 │         -1.416272 │   -0.563317 │ male   │  2007 │
│ Adelie  │ Torgersen │      -0.809939 │      0.126003 │         -1.060696 │   -0.500969 │ female │  2007 │
│ Adelie  │ Torgersen │      -0.663408 │      0.429833 │         -0.420660 │   -1.186793 │ female │  2007 │
│ Adelie  │ Torgersen │            nan │           nan │               nan │         nan │ NULL   │  2007 │
│ Adelie  │ Torgersen │      -1.322799 │      1.088129 │         -0.562890 │   -0.937403 │ female │  2007 │
│ Adelie  │ Torgersen │      -0.846572 │      1.746426 │         -0.776236 │   -0.688012 │ male   │  2007 │
│ Adelie  │ Torgersen │      -0.919837 │      0.328556 │         -1.416272 │   -0.719186 │ female │  2007 │
│ Adelie  │ Torgersen │      -0.864888 │      1.240044 │         -0.420660 │    0.590115 │ male   │  2007 │
│ Adelie  │ Torgersen │      -1.799025 │      0.480471 │         -0.562890 │   -0.906229 │ NULL   │  2007 │
│ Adelie  │ Torgersen │      -0.352029 │      1.543873 │         -0.776236 │    0.060160 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘

c is short for “column” and the ~ means “negate”. Combining those we get “not the year column”!

Pretty neat right?

Composable Group By

The power of this approach comes in when you want the grouped version. Perhaps we think some of these columns vary by species.

With selectors, all you need to do is slap a .group_by("species") onto t:

t.group_by("species").mutate(
    s.across(s.numeric() & ~s.c("year"), (_ - _.mean()) / _.std())
)
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ float64           │ float64     │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ Adelie  │ Torgersen │       0.791697 │     -1.270997 │          0.160007 │   -0.001444 │ female │  2008 │
│ Adelie  │ Biscoe    │       1.467524 │     -0.038103 │          0.924596 │    0.816322 │ male   │  2009 │
│ Adelie  │ Dream     │       0.378692 │      0.619441 │         -0.910418 │    2.070231 │ male   │  2007 │
│ Adelie  │ Dream     │      -0.860324 │     -0.284681 │         -1.216254 │   -1.200835 │ female │  2007 │
│ Adelie  │ Torgersen │      -0.785232 │      0.783827 │          0.465843 │   -0.546622 │ female │  2007 │
│ Adelie  │ Torgersen │       0.190962 │      1.852335 │          0.007089 │   -0.110480 │ male   │  2007 │
│ Adelie  │ Torgersen │       0.040778 │     -0.449067 │         -1.369172 │   -0.164997 │ female │  2007 │
│ Adelie  │ Torgersen │       0.153416 │      1.030405 │          0.771678 │    2.124749 │ male   │  2007 │
│ Adelie  │ Torgersen │      -1.761426 │     -0.202489 │          0.465843 │   -0.492104 │ NULL   │  2007 │
│ Adelie  │ Torgersen │       1.204702 │      1.523563 │          0.007089 │    1.197947 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘

Since ibis translates this into a run-of-the-mill selection as if you had called select or mutate without selectors, nothing special is needed for a backend to work with these new constructs.

Let’s look at some more examples.

Min-max Normalization

Grouped min/max normalization? Easy:

t.group_by("species").mutate(
    s.across(s.numeric() & ~s.c("year"), (_ - _.min()) / (_.max() - _.min()))
)
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ float64           │ float64     │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ Adelie  │ Torgersen │       0.633094 │      0.216667 │          0.500000 │    0.441558 │ female │  2008 │
│ Adelie  │ Biscoe    │       0.762590 │      0.466667 │          0.631579 │    0.636364 │ male   │  2009 │
│ Adelie  │ Dream     │       0.553957 │      0.600000 │          0.315789 │    0.935065 │ male   │  2007 │
│ Adelie  │ Dream     │       0.316547 │      0.416667 │          0.263158 │    0.155844 │ female │  2007 │
│ Adelie  │ Torgersen │       0.330935 │      0.633333 │          0.552632 │    0.311688 │ female │  2007 │
│ Adelie  │ Torgersen │       0.517986 │      0.850000 │          0.473684 │    0.415584 │ male   │  2007 │
│ Adelie  │ Torgersen │       0.489209 │      0.383333 │          0.236842 │    0.402597 │ female │  2007 │
│ Adelie  │ Torgersen │       0.510791 │      0.683333 │          0.605263 │    0.948052 │ male   │  2007 │
│ Adelie  │ Torgersen │       0.143885 │      0.433333 │          0.552632 │    0.324675 │ NULL   │  2007 │
│ Adelie  │ Torgersen │       0.712230 │      0.783333 │          0.473684 │    0.727273 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘

Casting and Munging

How about casting every column whose name ends with any of the strings "mm" or "g" to a float32? No problem!

t.mutate(s.across(s.endswith(("mm", "g")), _.cast("float32")))
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float32        │ float32       │ float32           │ float32     │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ Adelie  │ Torgersen │      39.099998 │     18.700001 │             181.0 │      3750.0 │ male   │  2007 │
│ Adelie  │ Torgersen │      39.500000 │     17.400000 │             186.0 │      3800.0 │ female │  2007 │
│ Adelie  │ Torgersen │      40.299999 │     18.000000 │             195.0 │      3250.0 │ female │  2007 │
│ Adelie  │ Torgersen │            nan │           nan │               nan │         nan │ NULL   │  2007 │
│ Adelie  │ Torgersen │      36.700001 │     19.299999 │             193.0 │      3450.0 │ female │  2007 │
│ Adelie  │ Torgersen │      39.299999 │     20.600000 │             190.0 │      3650.0 │ male   │  2007 │
│ Adelie  │ Torgersen │      38.900002 │     17.799999 │             181.0 │      3625.0 │ female │  2007 │
│ Adelie  │ Torgersen │      39.200001 │     19.600000 │             195.0 │      4675.0 │ male   │  2007 │
│ Adelie  │ Torgersen │      34.099998 │     18.100000 │             193.0 │      3475.0 │ NULL   │  2007 │
│ Adelie  │ Torgersen │      42.000000 │     20.200001 │             190.0 │      4250.0 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘

We can make all string columns have the same case too!

t.mutate(s.across(s.of_type("string"), _.lower()))
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ year  ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ int64             │ int64       │ string │ int64 │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────┤
│ adelie  │ torgersen │           39.1 │          18.7 │               181 │        3750 │ male   │  2007 │
│ adelie  │ torgersen │           39.5 │          17.4 │               186 │        3800 │ female │  2007 │
│ adelie  │ torgersen │           40.3 │          18.0 │               195 │        3250 │ female │  2007 │
│ adelie  │ torgersen │            nan │           nan │              NULL │        NULL │ NULL   │  2007 │
│ adelie  │ torgersen │           36.7 │          19.3 │               193 │        3450 │ female │  2007 │
│ adelie  │ torgersen │           39.3 │          20.6 │               190 │        3650 │ male   │  2007 │
│ adelie  │ torgersen │           38.9 │          17.8 │               181 │        3625 │ female │  2007 │
│ adelie  │ torgersen │           39.2 │          19.6 │               195 │        4675 │ male   │  2007 │
│ adelie  │ torgersen │           34.1 │          18.1 │               193 │        3475 │ NULL   │  2007 │
│ adelie  │ torgersen │           42.0 │          20.2 │               190 │        4250 │ NULL   │  2007 │
│ …       │ …         │              … │             … │                 … │           … │ …      │     … │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────┘

Multiple Computations per Column

What if I want to compute multiple things? Heck yeah!

t.group_by("sex").mutate(
    s.across(
        s.numeric() & ~s.c("year"),
        dict(centered=_ - _.mean(), zscore=(_ - _.mean()) / _.std()),
    )
).select("sex", s.endswith(("_centered", "_zscore")))
┏━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ sex    ┃ bill_length_mm_centered ┃ bill_depth_mm_centered ┃ flipper_length_mm_centered ┃ body_mass_g_centered ┃ bill_length_mm_zscore ┃ bill_depth_mm_zscore ┃ flipper_length_mm_zscore ┃ body_mass_g_zscore ┃
┡━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ string │ float64                 │ float64                │ float64                    │ float64              │ float64               │ float64              │ float64                  │ float64            │
├────────┼─────────────────────────┼────────────────────────┼────────────────────────────┼──────────────────────┼───────────────────────┼──────────────────────┼──────────────────────────┼────────────────────┤
│ female │                 4.10303 │              -1.925455 │                  11.636364 │           937.727273 │              0.836760 │            -1.072270 │                 0.930851 │           1.407635 │
│ female │                 1.20303 │              -2.425455 │                  10.636364 │           712.727273 │              0.245342 │            -1.350716 │                 0.850856 │           1.069885 │
│ female │                -8.09697 │               0.674545 │                 -12.363636 │          -462.272727 │             -1.651271 │             0.375649 │                -0.989030 │          -0.693924 │
│ female │                -5.89697 │               0.874545 │                 -10.363636 │          -562.272727 │             -1.202610 │             0.487027 │                -0.829039 │          -0.844035 │
│ female │                -0.99697 │               1.174545 │                 -15.363636 │          -662.272727 │             -0.203319 │             0.654095 │                -1.229015 │          -0.994147 │
│ female │                -5.49697 │               1.374545 │                 -12.363636 │          -162.272727 │             -1.121035 │             0.765473 │                -0.989030 │          -0.243590 │
│ female │                -3.39697 │               2.574545 │                  -2.363636 │          -412.272727 │             -0.692768 │             1.433743 │                -0.189079 │          -0.618868 │
│ female │                -7.69697 │               1.974545 │                 -13.363636 │          -537.272727 │             -1.569697 │             1.099608 │                -1.069025 │          -0.806507 │
│ female │                -4.29697 │               1.874545 │                 -23.363636 │          -462.272727 │             -0.876311 │             1.043919 │                -1.868975 │          -0.693924 │
│ female │                -6.19697 │               2.774545 │                  -8.363636 │           -62.272727 │             -1.263791 │             1.545122 │                -0.669049 │          -0.093478 │
│ …      │                       … │                      … │                          … │                    … │                     … │                    … │                        … │                  … │
└────────┴─────────────────────────┴────────────────────────┴────────────────────────────┴──────────────────────┴───────────────────────┴──────────────────────┴──────────────────────────┴────────────────────┘

Don’t like the naming convention?

Pass a function to make your own name!

t.select(s.startswith("bill")).mutate(
    s.across(
        s.all(),
        dict(x=_ - _.mean(), y=_.max()),
        names=lambda col, fn: f"{col}_{fn}_improved",
    )
)
┏━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ bill_length_mm ┃ bill_depth_mm ┃ bill_length_mm_x_improved ┃ bill_depth_mm_x_improved ┃ bill_length_mm_y_improved ┃ bill_depth_mm_y_improved ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ float64        │ float64       │ float64                   │ float64                  │ float64                   │ float64                  │
├────────────────┼───────────────┼───────────────────────────┼──────────────────────────┼───────────────────────────┼──────────────────────────┤
│           39.1 │          18.7 │                  -4.82193 │                  1.54883 │                      59.6 │                     21.5 │
│           39.5 │          17.4 │                  -4.42193 │                  0.24883 │                      59.6 │                     21.5 │
│           40.3 │          18.0 │                  -3.62193 │                  0.84883 │                      59.6 │                     21.5 │
│            nan │           nan │                       nan │                      nan │                      59.6 │                     21.5 │
│           36.7 │          19.3 │                  -7.22193 │                  2.14883 │                      59.6 │                     21.5 │
│           39.3 │          20.6 │                  -4.62193 │                  3.44883 │                      59.6 │                     21.5 │
│           38.9 │          17.8 │                  -5.02193 │                  0.64883 │                      59.6 │                     21.5 │
│           39.2 │          19.6 │                  -4.72193 │                  2.44883 │                      59.6 │                     21.5 │
│           34.1 │          18.1 │                  -9.82193 │                  0.94883 │                      59.6 │                     21.5 │
│           42.0 │          20.2 │                  -1.92193 │                  3.04883 │                      59.6 │                     21.5 │
│              … │             … │                         … │                        … │                         … │                        … │
└────────────────┴───────────────┴───────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┘

Don’t like lambda functions? We support a format string too!

t.select(s.startswith("bill")).mutate(
    s.across(
        s.all(),
        func=dict(x=_ - _.mean(), y=_.max()),
        names="{col}_{fn}_improved",
    )
).head(2)
┏━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ bill_length_mm ┃ bill_depth_mm ┃ bill_length_mm_x_improved ┃ bill_depth_mm_x_improved ┃ bill_length_mm_y_improved ┃ bill_depth_mm_y_improved ┃
┡━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ float64        │ float64       │ float64                   │ float64                  │ float64                   │ float64                  │
├────────────────┼───────────────┼───────────────────────────┼──────────────────────────┼───────────────────────────┼──────────────────────────┤
│           39.1 │          18.7 │                  -4.82193 │                  1.54883 │                      59.6 │                     21.5 │
│           39.5 │          17.4 │                  -4.42193 │                  0.24883 │                      59.6 │                     21.5 │
└────────────────┴───────────────┴───────────────────────────┴──────────────────────────┴───────────────────────────┴──────────────────────────┘

Working with other Ibis APIs

We’ve seen lots of mutate use, but selectors also work with .agg:

t.group_by("year").agg(s.across(s.numeric() & ~s.c("year"), _.mean())).order_by("year")
┏━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┓
┃ year  ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃
┡━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━┩
│ int64 │ float64        │ float64       │ float64           │ float64     │
├───────┼────────────────┼───────────────┼───────────────────┼─────────────┤
│  2007 │      43.740367 │     17.427523 │        196.880734 │ 4124.541284 │
│  2008 │      43.541228 │     16.914035 │        202.798246 │ 4266.666667 │
│  2009 │      44.452941 │     17.125210 │        202.806723 │ 4210.294118 │
└───────┴────────────────┴───────────────┴───────────────────┴─────────────┘

Naturally, selectors work in grouping keys too, for even more convenience:

t.group_by(~s.numeric() | s.c("year")).mutate(
    s.across(s.numeric() & ~s.c("year"), dict(centered=_ - _.mean(), std=_.std()))
).select("species", s.endswith(("_centered", "_std")))
┏━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━┓
┃ species ┃ bill_length_mm_centered ┃ bill_depth_mm_centered ┃ flipper_length_mm_centered ┃ body_mass_g_centered ┃ bill_length_mm_std ┃ bill_depth_mm_std ┃ flipper_length_mm_std ┃ body_mass_g_std ┃
┡━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━┩
│ string  │ float64                 │ float64                │ float64                    │ float64              │ float64            │ float64           │ float64               │ float64         │
├─────────┼─────────────────────────┼────────────────────────┼────────────────────────────┼──────────────────────┼────────────────────┼───────────────────┼───────────────────────┼─────────────────┤
│ Adelie  │                   -1.46 │               0.400000 │                  -1.600000 │          -170.000000 │           1.327780 │          0.681909 │              2.302173 │      189.076704 │
│ Adelie  │                   -0.96 │              -0.200000 │                   3.400000 │           180.000000 │           1.327780 │          0.681909 │              2.302173 │      189.076704 │
│ Adelie  │                   -0.36 │              -1.100000 │                  -1.600000 │            30.000000 │           1.327780 │          0.681909 │              2.302173 │      189.076704 │
│ Adelie  │                    1.44 │               0.300000 │                   1.400000 │          -220.000000 │           1.327780 │          0.681909 │              2.302173 │      189.076704 │
│ Adelie  │                    1.34 │               0.600000 │                  -1.600000 │           180.000000 │           1.327780 │          0.681909 │              2.302173 │      189.076704 │
│ Gentoo  │                    1.00 │               0.935294 │                  11.117647 │           147.058824 │           3.056755 │          0.670766 │              4.973459 │      349.763576 │
│ Gentoo  │                    1.00 │              -0.164706 │                  -0.882353 │           147.058824 │           3.056755 │          0.670766 │              4.973459 │      349.763576 │
│ Gentoo  │                   -1.40 │              -0.864706 │                  -3.882353 │          -152.941176 │           3.056755 │          0.670766 │              4.973459 │      349.763576 │
│ Gentoo  │                   -2.30 │              -0.064706 │                   0.117647 │          -352.941176 │           3.056755 │          0.670766 │              4.973459 │      349.763576 │
│ Gentoo  │                   -2.20 │               0.035294 │                  -3.882353 │          -402.941176 │           3.056755 │          0.670766 │              4.973459 │      349.763576 │
│ …       │                       … │                      … │                          … │                    … │                  … │                 … │                     … │               … │
└─────────┴─────────────────────────┴────────────────────────┴────────────────────────────┴──────────────────────┴────────────────────┴───────────────────┴───────────────────────┴─────────────────┘

Filtering Selectors

You can also express complex filters more concisely.

Let’s say we only want to keep rows where all the bill size z-score related columns’ absolute values are greater than 2.

t.drop("year").group_by("species").mutate(
    s.across(s.numeric(), dict(zscore=(_ - _.mean()) / _.std()))
).filter(s.if_all(s.startswith("bill") & s.endswith("_zscore"), _.abs() > 2))
┏━━━━━━━━━┳━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━┓
┃ species ┃ island    ┃ bill_length_mm ┃ bill_depth_mm ┃ flipper_length_mm ┃ body_mass_g ┃ sex    ┃ bill_length_mm_zscore ┃ bill_depth_mm_zscore ┃ flipper_length_mm_zscore ┃ body_mass_g_zscore ┃
┡━━━━━━━━━╇━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━┩
│ string  │ string    │ float64        │ float64       │ int64             │ int64       │ string │ float64               │ float64              │ float64                  │ float64            │
├─────────┼───────────┼────────────────┼───────────────┼───────────────────┼─────────────┼────────┼───────────────────────┼──────────────────────┼──────────────────────────┼────────────────────┤
│ Adelie  │ Torgersen │           46.0 │          21.5 │               194 │        4200 │ male   │              2.706539 │             2.592071 │                 0.618760 │           1.088911 │
│ Adelie  │ Dream     │           32.1 │          15.5 │               188 │        3050 │ female │             -2.512345 │            -2.339505 │                -0.298747 │          -1.418906 │
│ Gentoo  │ Biscoe    │           55.9 │          17.0 │               228 │        5600 │ male   │              2.724046 │             2.056508 │                 1.667394 │           1.039411 │
│ Gentoo  │ Biscoe    │           59.6 │          17.0 │               230 │        6050 │ male   │              3.924621 │             2.056508 │                 1.975799 │           1.932062 │
└─────────┴───────────┴────────────────┴───────────────┴───────────────────┴─────────────┴────────┴───────────────────────┴──────────────────────┴──────────────────────────┴────────────────────┘

Bonus: Generated SQL

The SQL for that last expression is pretty gnarly:

ibis.to_sql(
    t.drop("year")
    .group_by("species")
    .mutate(s.across(s.numeric(), dict(zscore=(_ - _.mean()) / _.std())))
    .filter(s.if_all(s.startswith("bill") & s.endswith("_zscore"), _.abs() > 2))
)
WITH t0 AS (
  SELECT
    t2.species AS species,
    t2.island AS island,
    t2.bill_length_mm AS bill_length_mm,
    t2.bill_depth_mm AS bill_depth_mm,
    t2.flipper_length_mm AS flipper_length_mm,
    t2.body_mass_g AS body_mass_g,
    t2.sex AS sex
  FROM main._ibis_examples_penguins_mqqdnfaydfbevoowdsf7djbsom AS t2
), t1 AS (
  SELECT
    t0.species AS species,
    t0.island AS island,
    t0.bill_length_mm AS bill_length_mm,
    t0.bill_depth_mm AS bill_depth_mm,
    t0.flipper_length_mm AS flipper_length_mm,
    t0.body_mass_g AS body_mass_g,
    t0.sex AS sex,
    (
      t0.bill_length_mm - AVG(t0.bill_length_mm) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) / STDDEV_SAMP(t0.bill_length_mm) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bill_length_mm_zscore,
    (
      t0.bill_depth_mm - AVG(t0.bill_depth_mm) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) / STDDEV_SAMP(t0.bill_depth_mm) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS bill_depth_mm_zscore,
    (
      t0.flipper_length_mm - AVG(t0.flipper_length_mm) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) / STDDEV_SAMP(t0.flipper_length_mm) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS flipper_length_mm_zscore,
    (
      t0.body_mass_g - AVG(t0.body_mass_g) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
    ) / STDDEV_SAMP(t0.body_mass_g) OVER (PARTITION BY t0.species ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS body_mass_g_zscore
  FROM t0
)
SELECT
  t1.species,
  t1.island,
  t1.bill_length_mm,
  t1.bill_depth_mm,
  t1.flipper_length_mm,
  t1.body_mass_g,
  t1.sex,
  t1.bill_length_mm_zscore,
  t1.bill_depth_mm_zscore,
  t1.flipper_length_mm_zscore,
  t1.body_mass_g_zscore
FROM t1
WHERE
  ABS(t1.bill_length_mm_zscore) > CAST(2 AS TINYINT)
  AND ABS(t1.bill_depth_mm_zscore) > CAST(2 AS TINYINT)

Good thing you didn’t have to write that by hand!

Summary

This blog post illustrates the ability to apply computations to many columns at once and the power of ibis as a composable, expressive library for analytics.

Back to top