Translate Pandas groupby plus resample to Polars in Python

Question

I have this code that generates a toy DataFrame (production df is much complex):

import polars as pl
import numpy as np
import pandas as pd

def create_timeseries_df(num_rows):
    date_rng = pd.date_range(start='1/1/2020', end='1/01/2021', freq='T')
    data = {
        'date': np.random.choice(date_rng, num_rows),
        'category': np.random.choice(['A', 'B', 'C', 'D'], num_rows),
        'subcategory': np.random.choice(['X', 'Y', 'Z'], num_rows),
        'value': np.random.rand(num_rows) * 100
    }
    df = pd.DataFrame(data)
    df = df.sort_values('date')
    df.set_index('date', inplace=True, drop=False)
    df.index = pd.to_datetime(df.index)

    return df

num_rows = 1000000  # for example
df = create_timeseries_df(num_rows)

Then perform this transformations with Pandas.

df_pd = df.copy()
df_pd = df_pd.groupby(['category', 'subcategory'])
df_pd = df_pd.resample('W-MON')
df_pd.agg({
    'value': ['sum', 'mean', 'max', 'min']
}).reset_index()

But, obviously it is quite slow with Pandas (at least in production). Thus, I'd like to use Polars to speed up time. This is what I have so far:

#Convert to Polars DataFrame
df_pl = pl.from_pandas(df)

#Groupby, resample and aggregate
df_pl = df_pl.group_by('category', 'subcategory')
df_pl = df_pl.group_by_dynamic('date', every='1w', closed='right')
df_pl.agg(
   pl.col('value').sum().alias('value_sum'),
   pl.col('value').mean().alias('value_mean'),
   pl.col('value').max().alias('value_max'),
   pl.col('value').min().alias('value_min')
)

But I get AttributeError: 'GroupBy' object has no attribute 'group_by_dynamic'. Any ideas on how to use groupby followed by resample in Polars?

Answer 1

You can pass additional columns to group by in a call to group_by_dynamic by passing a list with the named argument group_by=:

df_pl = df_pl.group_by_dynamic(
    "date", every="1w", closed="right", group_by=["category", "subcategory"]
)

With this, I get a dataframe that looks similar to the one your pandas code produces:

shape: (636, 7)
┌──────────┬─────────────┬─────────────────────┬──────────────┬───────────┬───────────┬──────────┐
│ category ┆ subcategory ┆ date                ┆ sum          ┆ mean      ┆ max       ┆ min      │
│ ---      ┆ ---         ┆ ---                 ┆ ---          ┆ ---       ┆ ---       ┆ ---      │
│ str      ┆ str         ┆ datetime[ns]        ┆ f64          ┆ f64       ┆ f64       ┆ f64      │
╞══════════╪═════════════╪═════════════════════╪══════════════╪═══════════╪═══════════╪══════════╡
│ D        ┆ Z           ┆ 2019-12-30 00:00:00 ┆ 55741.652346 ┆ 50.399324 ┆ 99.946595 ┆ 0.008139 │
│ D        ┆ Z           ┆ 2020-01-06 00:00:00 ┆ 76161.42206  ┆ 50.139185 ┆ 99.96917  ┆ 0.138366 │
│ D        ┆ Z           ┆ 2020-01-13 00:00:00 ┆ 80222.894298 ┆ 49.581517 ┆ 99.937069 ┆ 0.117216 │
│ D        ┆ Z           ┆ 2020-01-20 00:00:00 ┆ 82042.968995 ┆ 50.456931 ┆ 99.981101 ┆ 0.009077 │
│ D        ┆ Z           ┆ 2020-01-27 00:00:00 ┆ 82408.144078 ┆ 49.494381 ┆ 99.954734 ┆ 0.023769 │
│ …        ┆ …           ┆ …                   ┆ …            ┆ …         ┆ …         ┆ …        │
│ B        ┆ Z           ┆ 2020-11-30 00:00:00 ┆ 79530.963748 ┆ 49.737939 ┆ 99.973554 ┆ 0.007446 │
│ B        ┆ Z           ┆ 2020-12-07 00:00:00 ┆ 80050.524653 ┆ 49.566888 ┆ 99.975546 ┆ 0.003066 │
│ B        ┆ Z           ┆ 2020-12-14 00:00:00 ┆ 77896.578291 ┆ 50.029915 ┆ 99.969098 ┆ 0.033222 │
│ B        ┆ Z           ┆ 2020-12-21 00:00:00 ┆ 76490.507942 ┆ 49.636929 ┆ 99.953563 ┆ 0.021683 │
│ B        ┆ Z           ┆ 2020-12-28 00:00:00 ┆ 46964.533378 ┆ 50.553857 ┆ 99.653981 ┆ 0.042546 │
└──────────┴─────────────┴─────────────────────┴──────────────┴───────────┴───────────┴──────────┘