Python数据分析

- PolarsBook中文版: https://www.pythondataanalysis.com/docs/polars_book_cn/
- Polars快速入门: https://www.pythondataanalysis.com/docs/polars_book_cn/quickstart/
- Polars表达式: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/
- Polars表达式: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/expressions/
- Polars上下文: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/contexts/
- Polars分组: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/groupby/
- Polars折叠: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/folds/
- Polars自定义函数: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/custom_functions/
- Polars实例: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/introduction_polars/
- Polars表达式方法: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/api/
- Polars视频介绍: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/video_intro/
- Polars与Numpy交互: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/numpy/
- Polars窗口函数: https://www.pythondataanalysis.com/docs/polars_book_cn/dsl/window_functions/
- Polars索引: https://www.pythondataanalysis.com/docs/polars_book_cn/indexing/
- Polars数据类型: https://www.pythondataanalysis.com/docs/polars_book_cn/datatypes/
- 来自Pandas: https://www.pythondataanalysis.com/docs/polars_book_cn/coming_from_pandas/
- 来自ApacheSpark: https://www.pythondataanalysis.com/docs/polars_book_cn/coming_from_spark/
- Polars性能: https://www.pythondataanalysis.com/docs/polars_book_cn/performance/
- 字符串: https://www.pythondataanalysis.com/docs/polars_book_cn/performance/strings/
- Polars优化: https://www.pythondataanalysis.com/docs/polars_book_cn/optimizations/
- Polars惰性方法: https://www.pythondataanalysis.com/docs/polars_book_cn/optimizations/lazy/
- 谓词下推: https://www.pythondataanalysis.com/docs/polars_book_cn/optimizations/lazy/predicate-pushdown/
- 投影下推: https://www.pythondataanalysis.com/docs/polars_book_cn/optimizations/lazy/projection-pushdown/
- 其它优化: https://www.pythondataanalysis.com/docs/polars_book_cn/optimizations/lazy/other-optimizations/
- Polars参考指南: https://www.pythondataanalysis.com/docs/polars_book_cn/references/
- Polars时间序列: https://www.pythondataanalysis.com/docs/polars_book_cn/timeseries/
- Polars时间序列实例: https://www.pythondataanalysis.com/docs/polars_book_cn/timeseries/time-series/
- Polars使用范围: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/
- IO: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/
- Polars操作CSV文件: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/csv/
- Polars操作Parquet文件: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/parquet/
- Polars处理多个文件: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/multiple_files/
- Polars读取数据库: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/read_db/
- Polars与AWS交互: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/aws/
- Polars与Google BigQuery交互: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/google-big-query/
- Polars与Postgres交互: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/io/postgres/
- 互通性: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/interop/
- Arrow: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/interop/arrow/
- Numpy: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/interop/numpy/
- 数据: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/data/
- 字符串: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/data/strings/
- 时间戳: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/data/timestamps/
- 数据帧: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/
- 选中行或列: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/row_col_selection/
- 常用操作: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/common-manipulations/
- 聚合: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/aggregate/
- 分组: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/groupby/
- 过滤: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/filter/
- 连接: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/join/
- 重塑: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/melt/
- 条件应用: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/conditionally-apply/
- 排序: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/sorting/
- 透视: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/df/pivot/
- 应用: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/apply/
- Polars自定义函数: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/apply/udfs/
- Polars窗口函数: https://www.pythondataanalysis.com/docs/polars_book_cn/howcani/apply/window-functions/
- Python数据分析 第二版: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/
- 第 1 章 准备工作: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-01/
- 第 2 章 Python 语法基础: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-02/
- 第 3 章 Python 的数据结构、函数和文件: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-03/
- 第 4 章 NumPy 基础：数组和向量计算: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-04/
- 第 5 章 Pandas 入门: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-05/
- 第 6 章 数据加载、存储与文件格式: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-06/
- 第 7 章 数据清洗和准备: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-07/
- 第 10 章 数据聚合与分组运算: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-10/
- 第 11 章 时间序列: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-11/
- 第 12 章 pandas 高级应用: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-12/
- 第 13 章 Python 建模库介绍: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-13/
-  第 14 章 数据分析案例: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-14/
-  附录 A NumPy 高级应用: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Appendix-A/
-  附录 B 更多关于 IPython 的内容: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Appendix-B/
- 第 8 章 数据规整：聚合、合并和重塑: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-08/
- 第 9 章 绘图和可视化: https://www.pythondataanalysis.com/docs/Python_Data_Analysis_2nd_Editon/Chapter-09/
- Polars用户指南: https://www.pythondataanalysis.com/docs/Polars_user_guide/
- Polars入门: https://www.pythondataanalysis.com/docs/Polars_user_guide/polars_getting_started/
- 安装Polars: https://www.pythondataanalysis.com/docs/Polars_user_guide/polars_installation/
- Polars核心概念: https://www.pythondataanalysis.com/docs/Polars_user_guide/concepts/
- Polars数据类型和结构: https://www.pythondataanalysis.com/docs/Polars_user_guide/concepts/data-types-and-structures/
- Polars表达式和上下文: https://www.pythondataanalysis.com/docs/Polars_user_guide/concepts/expressions-and-contexts/
- Polars延迟API: https://www.pythondataanalysis.com/docs/Polars_user_guide/concepts/lazy-api/
- Streaming: https://www.pythondataanalysis.com/docs/Polars_user_guide/concepts/_streaming/
- Polars表达式: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/
- Polars基本操作: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/basic-operations/
- Aggregation: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/aggregation/
- Casting: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/casting/
- Categorical Data and Enums: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/categorical-data-and-enums/
- Expression Expansion: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/expression-expansion/
- Folds: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/folds/
- Lists and Arrays: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/lists-and-arrays/
- Missing Data: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/missing-data/
- Numpy Functions: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/numpy-functions/
- Strings: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/strings/
- Structs: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/structs/
- User Defined Python Functions: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/user-defined-python-functions/
- Window Functions: https://www.pythondataanalysis.com/docs/Polars_user_guide/expressions/window-functions/
- Reference: https://www.pythondataanalysis.com/docs/Polars_user_guide/api/reference/
- Index: https://www.pythondataanalysis.com/docs/Polars_user_guide/development/contributing/
- Versioning: https://www.pythondataanalysis.com/docs/Polars_user_guide/development/versioning/
- Index: https://www.pythondataanalysis.com/docs/Polars_user_guide/polars-cloud/
- Ecosystem: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/ecosystem/
- Gpu Support: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/gpu-support/
- Index: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/io/
- Index: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/lazy/
- Pandas: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/migration/pandas/
- Spark: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/migration/spark/
- Arrow: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/misc/arrow/
- Comparison: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/misc/comparison/
- Multiprocessing: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/misc/multiprocessing/
- Polars Llms: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/misc/polars_llms/
- Styling: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/misc/styling/
- Visualization: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/misc/visualization/
- Index: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/plugins/
- Create: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/sql/create/
- Cte: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/sql/cte/
- Intro: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/sql/intro/
- Select: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/sql/select/
- Show: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/sql/show/
- Index: https://www.pythondataanalysis.com/docs/Polars_user_guide/user-guide/transformations/

# Window functions

Window functions are expressions with superpowers. They allow you to perform aggregations on groups
within the context `select`. Let's get a feel for what that means.

First, we load a Pokémon dataset:

{{code_block('user-guide/expressions/window','pokemon',['read_csv'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:pokemon"
```

## Operations per group

Window functions are ideal when we want to perform an operation within a group. For instance,
suppose we want to rank our Pokémon by the column “Speed”. However, instead of a global ranking, we
want to rank the speed within each group defined by the column “Type 1”. We write the expression to
rank the data by the column “Speed” and then we add the function `over` to specify that this should
happen over the unique values of the column “Type 1”:

{{code_block('user-guide/expressions/window','rank',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:rank"
```

To help visualise this operation, you may imagine that Polars selects the subsets of the data that
share the same value for the column “Type 1” and then computes the ranking expression only for those
values. Then, the results for that specific group are projected back to the original rows and Polars
does this for all of the existing groups. The diagram below highlights the ranking computation for
the Pokémon with “Type 1” equal to “Grass”.

<div class="excalidraw">
--8<-- "docs/source/user-guide/expressions/speed_rank_by_type.svg"
</div>

Note how the row for the Pokémon “Golbat” has a “Speed” value of `90`, which is greater than the
value `80` of the Pokémon “Venusaur”, and yet the latter was ranked 1 because “Golbat” and “Venusar”
do not share the same value for the column “Type 1”.

The function `over` accepts an arbitrary number of expressions to specify the groups over which to
perform the computations. We can repeat the ranking above, but over the combination of the columns
“Type 1” and “Type 2” for a more fine-grained ranking:

{{code_block('user-guide/expressions/window','rank-multiple',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:rank-multiple"
```

In general, the results you get with the function `over` can also be achieved with
[an aggregation](aggregation.md) followed by a call to the function `explode`, although the rows
would be in a different order:

{{code_block('user-guide/expressions/window','rank-explode',['explode'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:rank-explode"
```

This shows that, usually, `group_by` and `over` produce results of different shapes:

- `group_by` usually produces a resulting dataframe with as many rows as groups used for
  aggregating; and
- `over` usually produces a dataframe with the same number of rows as the original.

The function `over` does not always produce results with the same number of rows as the original
dataframe, and that is what we explore next.

## Mapping results to dataframe rows

The function `over` accepts a parameter `mapping_strategy` that determines how the results of the
expression over the group are mapped back to the rows of the dataframe.

### `group_to_rows`

The default behaviour is `"group_to_rows"`: the result of the expression over the group should be
the same length as the group and the results are mapped back to the rows of that group.

If the order of the rows is not relevant, the option `"explode"` is more performant. Instead of
mapping the resulting values to the original rows, Polars creates a new dataframe where values from
the same group are next to each other. To help understand the distinction, consider the following
dataframe:

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:athletes"
```

We can sort the athletes by rank within their own countries. If we do so, the Dutch athletes were in
the second, third, and sixth, rows, and they will remain there. What will change is the order of the
names of the athletes, which goes from “B”, “C”, and “F”, to “B”, “F”, and “C”:

{{code_block('user-guide/expressions/window','athletes-sort-over-country',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:athletes-sort-over-country"
```

The diagram below represents this transformation:

<div class="excalidraw">
--8<-- "docs/source/user-guide/expressions/athletes_over_country.svg"
</div>

### `explode`

If we set the parameter `mapping_strategy` to `"explode"`, then athletes of the same country are
grouped together, but the final order of the rows – with respect to the countries – will not be the
same, as the diagram shows:

<div class="excalidraw">
--8<-- "docs/source/user-guide/expressions/athletes_over_country_explode.svg"
</div>

Because Polars does not need to keep track of the positions of the rows of each group, using
`"explode"` is typically faster than `"group_to_rows"`. However, using `"explode"` also requires
more care because it implies reordering the other columns that we wish to keep. The code that
produces this result follows

{{code_block('user-guide/expressions/window','athletes-explode',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:athletes-explode"
```

### `join`

Another possible value for the parameter `mapping_strategy` is `"join"`, which aggregates the
resulting values in a list and repeats the list over all rows of the same group:

{{code_block('user-guide/expressions/window','athletes-join',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:athletes-join"
```

## Windowed aggregation expressions

In case the expression applied to the values of a group produces a scalar value, the scalar is
broadcast across the rows of the group:

{{code_block('user-guide/expressions/window','pokemon-mean',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:pokemon-mean"
```

## More examples

For more exercises, below are some window functions for us to compute:

- sort all Pokémon by type;
- select the first `3` Pokémon per type as `"Type 1"`;
- sort the Pokémon within a type by speed in descending order and select the first `3` as
  `"fastest/group"`;
- sort the Pokémon within a type by attack in descending order and select the first `3` as
  `"strongest/group"`; and
- sort the Pokémon within a type by name and select the first `3` as `"sorted_by_alphabet"`.

{{code_block('user-guide/expressions/window','examples',['over'])}}

```python exec="on" result="text" session="user-guide/window"
--8<-- "python/user-guide/expressions/window.py:examples"
```