Polar calculates percentiles

Question content

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 "", line 6, in 
  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.

Correct Answer

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!