1.3.3 Window functions
A window function computes an expression within groups and maps the result back onto the rows, so the frame keeps its height. The Pokémon below are the first rows of upstream’s table.
> (define-enum types Grass Water Fire Normal Ground Electric Psychic Fighting Bug Steel Flying Dragon Dark Ghost Poison Rock Ice Fairy)
> (define pokemon (dataframe (list (series '("Bulbasaur" "Ivysaur" "Venusaur" "Charmander" "Charmeleon" "Charizard" "Mega Charizard X" "Squirtle" "Wartortle" "Blastoise") #:name "Name") (series '(Grass Grass Grass Fire Fire Fire Fire Water Water Water) #:name "Type 1" #:dtype types) (series (list 'Poison 'Poison 'Poison polars-null polars-null 'Flying 'Dragon polars-null polars-null polars-null) #:name "Type 2" #:dtype types) (series '(49 62 82 52 64 84 130 48 63 83) #:name "Attack") (series '(45 60 80 65 80 100 100 43 58 78) #:name "Speed")))) > pokemon
shape: (10, 5)
┌──────────────────┬────────┬────────┬────────┬───────┐
│ Name ┆ Type 1 ┆ Type 2 ┆ Attack ┆ Speed │
│ --- ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ enum ┆ enum ┆ i64 ┆ i64 │
╞══════════════════╪════════╪════════╪════════╪═══════╡
│ Bulbasaur ┆ Grass ┆ Poison ┆ 49 ┆ 45 │
│ Ivysaur ┆ Grass ┆ Poison ┆ 62 ┆ 60 │
│ Venusaur ┆ Grass ┆ Poison ┆ 82 ┆ 80 │
│ Charmander ┆ Fire ┆ null ┆ 52 ┆ 65 │
│ Charmeleon ┆ Fire ┆ null ┆ 64 ┆ 80 │
│ Charizard ┆ Fire ┆ Flying ┆ 84 ┆ 100 │
│ Mega Charizard X ┆ Fire ┆ Dragon ┆ 130 ┆ 100 │
│ Squirtle ┆ Water ┆ null ┆ 48 ┆ 43 │
│ Wartortle ┆ Water ┆ null ┆ 63 ┆ 58 │
│ Blastoise ┆ Water ┆ null ┆ 83 ┆ 78 │
└──────────────────┴────────┴────────┴────────┴───────┘
1.3.3.1 Operations per group
> (select pokemon "Name" "Type 1" (~> (col "Speed") (rank #:method 'dense #:descending #t) (over "Type 1") (alias "Speed rank")))
shape: (10, 3)
┌──────────────────┬────────┬────────────┐
│ Name ┆ Type 1 ┆ Speed rank │
│ --- ┆ --- ┆ --- │
│ str ┆ enum ┆ u32 │
╞══════════════════╪════════╪════════════╡
│ Bulbasaur ┆ Grass ┆ 3 │
│ Ivysaur ┆ Grass ┆ 2 │
│ Venusaur ┆ Grass ┆ 1 │
│ Charmander ┆ Fire ┆ 3 │
│ Charmeleon ┆ Fire ┆ 2 │
│ Charizard ┆ Fire ┆ 1 │
│ Mega Charizard X ┆ Fire ┆ 1 │
│ Squirtle ┆ Water ┆ 3 │
│ Wartortle ┆ Water ┆ 2 │
│ Blastoise ┆ Water ┆ 1 │
└──────────────────┴────────┴────────────┘
> (select pokemon "Name" "Type 1" "Type 2" (~> (col "Speed") (rank #:method 'dense #:descending #t) (over "Type 1" "Type 2") (alias "Speed rank")))
shape: (10, 4)
┌──────────────────┬────────┬────────┬────────────┐
│ Name ┆ Type 1 ┆ Type 2 ┆ Speed rank │
│ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ enum ┆ enum ┆ u32 │
╞══════════════════╪════════╪════════╪════════════╡
│ Bulbasaur ┆ Grass ┆ Poison ┆ 3 │
│ Ivysaur ┆ Grass ┆ Poison ┆ 2 │
│ Venusaur ┆ Grass ┆ Poison ┆ 1 │
│ Charmander ┆ Fire ┆ null ┆ 2 │
│ Charmeleon ┆ Fire ┆ null ┆ 1 │
│ Charizard ┆ Fire ┆ Flying ┆ 1 │
│ Mega Charizard X ┆ Fire ┆ Dragon ┆ 1 │
│ Squirtle ┆ Water ┆ null ┆ 3 │
│ Wartortle ┆ Water ┆ null ┆ 2 │
│ Blastoise ┆ Water ┆ null ┆ 1 │
└──────────────────┴────────┴────────┴────────────┘
1.3.3.2 Mapping results to dataframe rows
API gap: over has no mapping_strategy; it always maps as group_to_rows.
> (define athletes (dataframe (list (series '("A" "B" "C" "D" "E" "F") #:name "athlete") (series '("PT" "NL" "NL" "PT" "PT" "NL") #:name "country") (series '(6 1 5 4 2 3) #:name "rank")))) > athletes
shape: (6, 3)
┌─────────┬─────────┬──────┐
│ athlete ┆ country ┆ rank │
│ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 │
╞═════════╪═════════╪══════╡
│ A ┆ PT ┆ 6 │
│ B ┆ NL ┆ 1 │
│ C ┆ NL ┆ 5 │
│ D ┆ PT ┆ 4 │
│ E ┆ PT ┆ 2 │
│ F ┆ NL ┆ 3 │
└─────────┴─────────┴──────┘
1.3.3.2.1 group_to_rows
Each group’s result goes back to the rows the group came from. A regexp col stands in for pl.col("athlete", "rank").
> (select athletes (~> (col #rx"^(athlete|rank)$") (sort-by #:by (col "rank")) (over (col "country"))) (col "country"))
shape: (6, 3)
┌─────────┬──────┬─────────┐
│ athlete ┆ rank ┆ country │
│ --- ┆ --- ┆ --- │
│ str ┆ i64 ┆ str │
╞═════════╪══════╪═════════╡
│ E ┆ 2 ┆ PT │
│ B ┆ 1 ┆ NL │
│ F ┆ 3 ┆ NL │
│ D ┆ 4 ┆ PT │
│ A ┆ 6 ┆ PT │
│ C ┆ 5 ┆ NL │
└─────────┴──────┴─────────┘
1.3.3.2.2 explode
API gap: no explode strategy. Sorting the frame by the key and then the value gives the same rows, with the groups in key order rather than in order of first appearance.
> (sort athletes '("country" "rank"))
shape: (6, 3)
┌─────────┬─────────┬──────┐
│ athlete ┆ country ┆ rank │
│ --- ┆ --- ┆ --- │
│ str ┆ str ┆ i64 │
╞═════════╪═════════╪══════╡
│ B ┆ NL ┆ 1 │
│ F ┆ NL ┆ 3 │
│ C ┆ NL ┆ 5 │
│ E ┆ PT ┆ 2 │
│ D ┆ PT ┆ 4 │
│ A ┆ PT ┆ 6 │
└─────────┴─────────┴──────┘
1.3.3.2.3 join
API gap: no join strategy. Collect each group’s values with agg and join them back.
> (join (drop athletes "rank") (~> athletes (group-by "country") (agg (sort-by (col "rank") #:by "rank"))) #:on '("country") #:how 'left)
shape: (6, 3)
┌─────────┬─────────┬───────────┐
│ athlete ┆ country ┆ rank │
│ --- ┆ --- ┆ --- │
│ str ┆ str ┆ list[i64] │
╞═════════╪═════════╪═══════════╡
│ A ┆ PT ┆ [2, 4, 6] │
│ B ┆ NL ┆ [1, 3, 5] │
│ C ┆ NL ┆ [1, 3, 5] │
│ D ┆ PT ┆ [2, 4, 6] │
│ E ┆ PT ┆ [2, 4, 6] │
│ F ┆ NL ┆ [1, 3, 5] │
└─────────┴─────────┴───────────┘
1.3.3.3 Windowed aggregation expressions
An aggregation over a window is repeated on every row of its group.
> (select pokemon "Name" "Type 1" "Speed" (~> (col "Speed") mean (over (col "Type 1")) (alias "Mean speed in group")))
shape: (10, 4)
┌──────────────────┬────────┬───────┬─────────────────────┐
│ Name ┆ Type 1 ┆ Speed ┆ Mean speed in group │
│ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ enum ┆ i64 ┆ f64 │
╞══════════════════╪════════╪═══════╪═════════════════════╡
│ Bulbasaur ┆ Grass ┆ 45 ┆ 61.666667 │
│ Ivysaur ┆ Grass ┆ 60 ┆ 61.666667 │
│ Venusaur ┆ Grass ┆ 80 ┆ 61.666667 │
│ Charmander ┆ Fire ┆ 65 ┆ 86.25 │
│ Charmeleon ┆ Fire ┆ 80 ┆ 86.25 │
│ Charizard ┆ Fire ┆ 100 ┆ 86.25 │
│ Mega Charizard X ┆ Fire ┆ 100 ┆ 86.25 │
│ Squirtle ┆ Water ┆ 43 ┆ 59.666667 │
│ Wartortle ┆ Water ┆ 58 ┆ 59.666667 │
│ Blastoise ┆ Water ┆ 78 ┆ 59.666667 │
└──────────────────┴────────┴───────┴─────────────────────┘
1.3.3.4 More examples
API gap: without explode, the three fastest of each type are picked as rows instead: rank within the window, then filter. The strongest and the alphabetical first three follow the same pattern over "Attack" and "Name".
> (~> pokemon (with-columns (~> (col "Speed") (rank #:method 'ordinal #:descending #t) (over "Type 1") (alias "fastest/group"))) (filter (<= (col "fastest/group") 3)) (select "Type 1" "Name" "fastest/group") (sort '("Type 1" "fastest/group")))
shape: (9, 3)
┌────────┬──────────────────┬───────────────┐
│ Type 1 ┆ Name ┆ fastest/group │
│ --- ┆ --- ┆ --- │
│ enum ┆ str ┆ u32 │
╞════════╪══════════════════╪═══════════════╡
│ Grass ┆ Venusaur ┆ 1 │
│ Grass ┆ Ivysaur ┆ 2 │
│ Grass ┆ Bulbasaur ┆ 3 │
│ Water ┆ Blastoise ┆ 1 │
│ Water ┆ Wartortle ┆ 2 │
│ Water ┆ Squirtle ┆ 3 │
│ Fire ┆ Charizard ┆ 1 │
│ Fire ┆ Mega Charizard X ┆ 2 │
│ Fire ┆ Charmeleon ┆ 3 │
└────────┴──────────────────┴───────────────┘