I'd like to use the bucket expression with groupby, to downsample on monthly basis, as the downsampling function will be deprecated. Is there a easy way to do this, datetime.timedelta only works on days and lower.
2 Answers
With the landing groupby_dynamic we can now downsample and use the whole expression API for our aggregations. Meaning we can resample by either.
- upsampling
- downsampling
- first upsample and then downsample
Let's go through an example:
df = pl.DataFrame(
{"time": pl.date_range(low=datetime(2021, 12, 16), high=datetime(2021, 12, 16, 3), interval="30m"),
"groups": ["a", "a", "a", "b", "b", "a", "a"],
"values": [1., 2., 3., 4., 5., 6., 7.]
})
print(df)
shape: (7, 3)
┌─────────────────────┬────────┬────────┐
│ time ┆ groups ┆ values │
│ --- ┆ --- ┆ --- │
│ datetime ┆ str ┆ f64 │
╞═════════════════════╪════════╪════════╡
│ 2021-12-16 00:00:00 ┆ a ┆ 1 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:30:00 ┆ a ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 01:00:00 ┆ a ┆ 3 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 01:30:00 ┆ b ┆ 4 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:00:00 ┆ b ┆ 5 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:30:00 ┆ a ┆ 6 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 03:00:00 ┆ a ┆ 7 │
└─────────────────────┴────────┴────────┘
Upsampling
Upsampling can be done by defining an interval. This will yield a DataFrame with nulls, which can then be filled with a fill strategy or interpolation.
df.upsample("time", "15m").fill_null("forward")
shape: (13, 3)
┌─────────────────────┬────────┬────────┐
│ time ┆ groups ┆ values │
│ --- ┆ --- ┆ --- │
│ datetime ┆ str ┆ f64 │
╞═════════════════════╪════════╪════════╡
│ 2021-12-16 00:00:00 ┆ a ┆ 1 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:15:00 ┆ a ┆ 1 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:30:00 ┆ a ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:45:00 ┆ a ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ ... ┆ ... ┆ ... │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:00:00 ┆ b ┆ 5 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:15:00 ┆ b ┆ 5 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:30:00 ┆ a ┆ 6 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:45:00 ┆ a ┆ 6 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 03:00:00 ┆ a ┆ 7 │
└─────────────────────┴────────┴────────┘
(df.upsample("time", "15m")
.interpolate()
.fill_null("forward") # string columns cannot be interpolated
)
shape: (13, 3)
┌─────────────────────┬────────┬────────┐
│ time ┆ groups ┆ values │
│ --- ┆ --- ┆ --- │
│ datetime ┆ str ┆ f64 │
╞═════════════════════╪════════╪════════╡
│ 2021-12-16 00:00:00 ┆ a ┆ 1 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:15:00 ┆ a ┆ 1.5 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:30:00 ┆ a ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 00:45:00 ┆ a ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ ... ┆ ... ┆ ... │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:00:00 ┆ b ┆ 5 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:15:00 ┆ b ┆ 3.5 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:30:00 ┆ a ┆ 6 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 02:45:00 ┆ a ┆ 4 │
├╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2021-12-16 03:00:00 ┆ a ┆ 7 │
└─────────────────────┴────────┴────────┘
Downsampling
This is the powerful one, because we can also combine this with normal groupby keys. Having a virtual moving window over the time series (grouped by one or multiple keys) that can be aggregated with the expression API.
(df.groupby_dynamic(
time_column="time",
every="1h",
closed="both",
by="groups",
include_boundaries=True
)
.agg([
pl.col('time').count(),
pl.col("time").max(),
pl.sum("values"),
]))
shape: (4, 7)
┌────────┬────────────┬────────────┬────────────┬────────────┬─────────────────────┬────────────┐
│ groups ┆ _lower_bou ┆ _upper_bou ┆ time ┆ time_count ┆ time_max ┆ values_sum │
│ --- ┆ ndary ┆ ndary ┆ --- ┆ --- ┆ --- ┆ --- │
│ str ┆ --- ┆ --- ┆ datetime ┆ u32 ┆ datetime ┆ f64 │
│ ┆ datetime ┆ datetime ┆ ┆ ┆ ┆ │
╞════════╪════════════╪════════════╪════════════╪════════════╪═════════════════════╪════════════╡
│ a ┆ 2021-12-16 ┆ 2021-12-16 ┆ 2021-12-16 ┆ 3 ┆ 2021-12-16 01:00:00 ┆ 6 │
│ ┆ 00:00:00 ┆ 01:00:00 ┆ 00:00:00 ┆ ┆ ┆ │
├╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
│ a ┆ 2021-12-16 ┆ 2021-12-16 ┆ 2021-12-16 ┆ 1 ┆ 2021-12-16 00:00:00 ┆ 1 │
│ ┆ 01:00:00 ┆ 02:00:00 ┆ 00:00:00 ┆ ┆ ┆ │
├╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
│ a ┆ 2021-12-16 ┆ 2021-12-16 ┆ 2021-12-16 ┆ 2 ┆ 2021-12-16 03:00:00 ┆ 13 │
│ ┆ 02:00:00 ┆ 03:00:00 ┆ 00:00:00 ┆ ┆ ┆ │
├╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌╌╌╌╌┤
│ b ┆ 2021-12-16 ┆ 2021-12-16 ┆ 2021-12-16 ┆ 2 ┆ 2021-12-16 02:00:00 ┆ 9 │
│ ┆ 01:00:00 ┆ 02:00:00 ┆ 01:00:00 ┆ ┆ ┆ │
└────────┴────────────┴────────────┴────────────┴────────────┴─────────────────────┴────────────┘
I found a solutuion for my problem using the round expression and a groupby operation on the date column after it.
Here is some Code exmaple:
df = pl.DataFrame(
{
"A": [
"2020-01-01",
"2020-01-02",
"2020-02-03",
"2020-02-04",
"2020-03-05",
"2020-03-06",
"2020-06-06",
],
"B": [1.0, 8.0, 6.0, 2.0, 16.0, 10.0,2],
"C": [3.0, 6.0, 9.0, 2.0, 13.0, 8.0,2],
"D": [12.0, 5.0, 9.0, 2.0, 11.0, 2.0,2],
}
)
q = (
df.lazy().with_column(pl.col('A').str.strptime(pl.Date, "%Y-%m-%d").dt.round(rule='month',n=1))
.groupby('A').agg(
[pl.col("B").max(),
pl.col("C").min(),
pl.col("D").last()]
)
.sort('A')
)
df = q.collect()
print(df)
prints
┌────────────┬───────┬───────┬────────┐
│ A ┆ B_max ┆ C_min ┆ D_last │
│ --- ┆ --- ┆ --- ┆ --- │
│ date ┆ f64 ┆ f64 ┆ f64 │
╞════════════╪═══════╪═══════╪════════╡
│ 2020-01-01 ┆ 8 ┆ 3 ┆ 5 │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-02-01 ┆ 6 ┆ 2 ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-03-01 ┆ 16 ┆ 8 ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-06-01 ┆ 2 ┆ 2 ┆ 2 │
└────────────┴───────┴───────┴────────┘
Some explanation, first I cast the String column to pl.Date type, then I use .dt to create the Namespace of the Date Types. After that I use the round function of the DateTime Namespace to round all Dates on monthly basis to the first day in the month. As result every date in the same month got the same date, so I can group them with groupby and use some aggregation functions on the groups. The downside of this method(and downsampling too) is that u missing the months where no dates exist. For this u can us this code, it's a little bit messy and I had to use pandas but I think it works.
from dateutil.relativedelta import relativedelta
date_min = df['A'].dt.min()
date_max = df['A'].dt.max()+relativedelta(months=+1)
t_index=pd.date_range(date_min, date_max, freq='M',closed='right').values
t_index = [datetime.datetime.fromisoformat(str(np.datetime_as_string(x, unit='D'))) for x in t_index]
df_ref = pl.DataFrame(t_index,columns='A')
q=(
df_ref.lazy().with_column(pl.col('A').cast(pl.Date).dt.round(rule='month',n=1))
.join(df.lazy(),on='A',how='left')
)
df = q.collect()
print(df)
results in
┌────────────┬───────┬───────┬────────┐
│ A ┆ B_max ┆ C_min ┆ D_last │
│ --- ┆ --- ┆ --- ┆ --- │
│ date ┆ f64 ┆ f64 ┆ f64 │
╞════════════╪═══════╪═══════╪════════╡
│ 2020-01-01 ┆ 8 ┆ 3 ┆ 5 │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-02-01 ┆ 6 ┆ 2 ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-03-01 ┆ 16 ┆ 8 ┆ 2 │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-04-01 ┆ null ┆ null ┆ null │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-05-01 ┆ null ┆ null ┆ null │
├╌╌╌╌╌╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌┼╌╌╌╌╌╌╌╌┤
│ 2020-06-01 ┆ 2 ┆ 2 ┆ 2 │
└────────────┴───────┴───────┴────────┘
Related