Home > Article > Backend Development > Polar calculates percentiles
I have a polar dataframe with one column containing dates and other columns containing prices, and I want to calculate 252 x 3 observations Percentile for each column in the window.
To do this, I'm doing this:
prices = prices.sort(by=["date"]) rank_cols = list(set(prices.columns).difference("date")) percentiles = ( prices.sort(by=["date"]) .set_sorted("date") .group_by_dynamic( index_column=["date"], every="1i", start_by="window", period="756i" ) .agg( [ (pl.col(col).rank() * 100.0 / pl.col(col).count()).alias( f"{col}_percentile" ) for col in rank_cols ] ) )
But the exception thrown is:
traceback (most recent call last): file "<string>", line 6, in <module> file "/usr/local/lib/python3.10/site-packages/polars/dataframe/group_by.py", line 1047, in agg self.df.lazy() file "/usr/local/lib/python3.10/site-packages/polars/lazyframe/frame.py", line 1706, in collect return wrap_df(ldf.collect()) polars.exceptions.invalidoperationerror: argument in operation 'group_by_dynamic' is not explicitly sorted - if your data is already sorted, set the sorted flag with: '.set_sorted()'. - if your data is not sorted, sort the 'expr/series/column' first.
In the code, I have done as suggested but the exception still exists.
edit:
Made some changes as suggested by @hericks.
import polars as pl import pandas as pd from datetime import datetime, timedelta # generate 10 dates starting from today start_date = datetime.now().date() date_list = [start_date + timedelta(days=i) for i in range(10)] # generate random prices for each date and column data = { 'date': date_list, 'asset_1': [float(f"{i+1}.{i+2}") for i in range(10)], 'asset_2': [float(f"{i+2}.{i+3}") for i in range(10)], 'asset_3': [float(f"{i+3}.{i+4}") for i in range(10)], } prices = pl.dataframe(data) prices = prices.cast({"date": pl.date}) rank_cols = list(set(prices.columns).difference("date")) percentiles = ( prices.sort(by=["date"]) .set_sorted("date") .group_by_dynamic( index_column="date", every="1i", start_by="window", period="4i" ) .agg( [ (pl.col(col).rank() * 100.0 / pl.col(col).count()).alias( f"{col}_percentile" ) for col in rank_cols ] ) )
now I understand
pyo3_runtime.panicexception: attempt to divide by zero
Edit 2:
The problem is the use of dates, I changed the dates with integers and then the problem was solved. (Also added to get the first register first)
import polars as pl int_list = [i+1 for i in range(6)] # Generate random prices for each date and column data = { 'int_index': int_list, 'asset_1': [1.1, 3.4, 2.6, 4.8, 7.4, 3.2], 'asset_2': [4, 7, 8, 3, 4, 5], 'asset_3': [1, 3, 10, 20, 2, 4], } # Convert the Pandas DataFrame to a Polars DataFrame prices = pl.DataFrame(data) rank_cols = list(set(prices.columns).difference("int_index")) percentiles = ( prices.sort(by="int_index") .set_sorted("int_index") .group_by_dynamic( index_column="int_index", every="1i", start_by="window", period="4i" ) .agg( [ (pl.col(col).rank().first() * 100.0 / pl.col(col).count()).alias( f"{col}_percentile" ) for col in rank_cols ] ) )
Edit 3:
The idea given is that index i takes the values at index i, i 1, i 2, i 3 and calculates the percentile rank of register i relative to these four values.
For example, for the first index (1) in asset_1, the example (and the next three registers) is:
1.1, 3.4, 2.6, 4.8, so the percentile of the first register is 25
For asset_1, the second index (2) example (and the next three registers) is:
3.4, 2.6, 4.8, and 7.4, so the percentile is 50.
I'm still kind of guessing what the answer you're expecting is, but you can probably start with this answer
So, considering your example data:
import polars as pl # generate random prices for each date and column prices = pl.dataframe({ 'int_index': range(6), 'asset_1': [1.1, 3.4, 2.6, 4.8, 7.4, 3.2], 'asset_2': [4, 7, 8, 3, 4, 5], 'asset_3': [1, 3, 10, 20, 2, 4], }) ┌───────────┬─────────┬─────────┬─────────┐ │ int_index ┆ asset_1 ┆ asset_2 ┆ asset_3 │ │ --- ┆ --- ┆ --- ┆ --- │ │ i64 ┆ f64 ┆ i64 ┆ i64 │ ╞═══════════╪═════════╪═════════╪═════════╡ │ 0 ┆ 1.1 ┆ 4 ┆ 1 │ │ 1 ┆ 3.4 ┆ 7 ┆ 3 │ │ 2 ┆ 2.6 ┆ 8 ┆ 10 │ │ 3 ┆ 4.8 ┆ 3 ┆ 20 │ │ 4 ┆ 7.4 ┆ 4 ┆ 2 │ │ 5 ┆ 3.2 ┆ 5 ┆ 4 │ └───────────┴─────────┴─────────┴─────────┘
Create the window using rolling()
and then (same as you did in your question) - rank().first()
divide by count()
, name.suffix()
Assign a new name to the column:
cols = pl.all().exclude('int_index') percentiles = ( prices.sort(by="int_index") .rolling(index_column="int_index", period="4i", offset="0i", closed="left") .agg((cols.rank().first() * 100 / cols.count()).name.suffix('_percentile')) ) ┌───────────┬────────────────────┬────────────────────┬────────────────────┐ │ int_index ┆ asset_1_percentile ┆ asset_2_percentile ┆ asset_3_percentile │ │ --- ┆ --- ┆ --- ┆ --- │ │ i64 ┆ f64 ┆ f64 ┆ f64 │ ╞═══════════╪════════════════════╪════════════════════╪════════════════════╡ │ 0 ┆ 25.0 ┆ 50.0 ┆ 25.0 │ │ 1 ┆ 50.0 ┆ 75.0 ┆ 50.0 │ │ 2 ┆ 25.0 ┆ 100.0 ┆ 75.0 │ │ 3 ┆ 66.666667 ┆ 33.333333 ┆ 100.0 │ │ 4 ┆ 100.0 ┆ 50.0 ┆ 50.0 │ │ 5 ┆ 100.0 ┆ 100.0 ┆ 100.0 │ └───────────┴────────────────────┴────────────────────┴────────────────────┘
You can also inspect the contents within each window:
( prices.sort(by="int_index") .rolling(index_column="int_index", period="4i", offset="0i", closed="left") .agg(cols) ) ┌───────────┬───────────────────┬─────────────┬───────────────┐ │ int_index ┆ asset_1 ┆ asset_2 ┆ asset_3 │ │ --- ┆ --- ┆ --- ┆ --- │ │ i64 ┆ list[f64] ┆ list[i64] ┆ list[i64] │ ╞═══════════╪═══════════════════╪═════════════╪═══════════════╡ │ 0 ┆ [1.1, 3.4, … 4.8] ┆ [4, 7, … 3] ┆ [1, 3, … 20] │ │ 1 ┆ [3.4, 2.6, … 7.4] ┆ [7, 8, … 4] ┆ [3, 10, … 2] │ │ 2 ┆ [2.6, 4.8, … 3.2] ┆ [8, 3, … 5] ┆ [10, 20, … 4] │ │ 3 ┆ [4.8, 7.4, 3.2] ┆ [3, 4, 5] ┆ [20, 2, 4] │ │ 4 ┆ [7.4, 3.2] ┆ [4, 5] ┆ [2, 4] │ │ 5 ┆ [3.2] ┆ [5] ┆ [4] │ └───────────┴───────────────────┴─────────────┴───────────────┘
The above is the detailed content of Polar calculates percentiles. For more information, please follow other related articles on the PHP Chinese website!