Is there a way to create lags based on the sampling interval?

[kaijennissen]

When working with time-series it's common to use lagged features, where the lags are wrt. the sampling interval. If the sampling interval is f.e. daily, then a lag of 1 returns the value of the previous day. I was able to come up with a solution which works by first adding the sampling-interval and then joining the newly created table. Is there a way to get the same result without intermediate table?

import datetime as dt

import ibis
from ibis import _

ibis.options.interactive = True
t = ibis.memtable(
    {
        "store_id": [1] * 5 + [2] * 5,
        "date": [dt.datetime(2022, 1, x) for x in [2, 3, 4, 5, 6]]
        + [dt.datetime(2022, 1, x) for x in [2, 5, 6, 7, 9]],
        "value": [1, 2, 3, 4, 5] + [1, 4, 5, 6, 8],
    },
    name="t",
)

t_lag = t.mutate(lag_date=_.date + ibis.interval(days=1)).rename(value_lag_1="value")
t.join(
    t_lag,
    [t.store_id == t_lag.store_id, t.date == t_lag.lag_date],
    how="left",
).drop(_.lag_date, _.store_id_right, _.date_right).order_by(t.store_id, t.date)

┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━┓
┃ store_id ┃ date                ┃ value ┃ value_lag_1 ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━┩
│ int64    │ timestamp           │ int64 │ int64       │
├──────────┼─────────────────────┼───────┼─────────────┤
│        1 │ 2022-01-02 00:00:00 │     1 │        NULL │
│        1 │ 2022-01-03 00:00:00 │     2 │           1 │
│        1 │ 2022-01-04 00:00:00 │     3 │           2 │
│        1 │ 2022-01-05 00:00:00 │     4 │           3 │
│        1 │ 2022-01-06 00:00:00 │     5 │           4 │
│        2 │ 2022-01-02 00:00:00 │     1 │        NULL │
│        2 │ 2022-01-05 00:00:00 │     4 │        NULL │
│        2 │ 2022-01-06 00:00:00 │     5 │           4 │
│        2 │ 2022-01-07 00:00:00 │     6 │           5 │
│        2 │ 2022-01-09 00:00:00 │     8 │        NULL │
└──────────┴─────────────────────┴───────┴─────────────┘

[cpcloud]

You should be able do this with a bit of window function-fu:

In [35]: from ibis.interactive import *

In [36]: import datetime as dt

In [37]: t = ibis.memtable(
    ...:     {
    ...:         "store_id": [1] * 5 + [2] * 5,
    ...:         "date": [dt.datetime(2022, 1, x) for x in [2, 3, 4, 5, 6]]
    ...:         + [dt.datetime(2022, 1, x) for x in [2, 5, 6, 7, 9]],
    ...:         "value": [1, 2, 3, 4, 5] + [1, 4, 5, 6, 8],
    ...:     },
    ...:     name="t",
    ...: )

In [38]: t.mutate(value_lag_1=(_.date.delta(_.date.lag().over(group_by="store_id"), 'days') == 1).cast('int').nullif(0) * _.value.lag().over(group_by="store_id")).order_by("store_id", "date")
Out[38]:
┏━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━┳━━━━━━━━━━━━━┓
┃ store_id ┃ date                ┃ value ┃ value_lag_1 ┃
┡━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━╇━━━━━━━━━━━━━┩
│ int64    │ timestamp           │ int64 │ int64       │
├──────────┼─────────────────────┼───────┼─────────────┤
│        1 │ 2022-01-02 00:00:00 │     1 │        NULL │
│        1 │ 2022-01-03 00:00:00 │     2 │           1 │
│        1 │ 2022-01-04 00:00:00 │     3 │           2 │
│        1 │ 2022-01-05 00:00:00 │     4 │           3 │
│        1 │ 2022-01-06 00:00:00 │     5 │           4 │
│        2 │ 2022-01-02 00:00:00 │     1 │        NULL │
│        2 │ 2022-01-05 00:00:00 │     4 │        NULL │
│        2 │ 2022-01-06 00:00:00 │     5 │           4 │
│        2 │ 2022-01-07 00:00:00 │     6 │           5 │
│        2 │ 2022-01-09 00:00:00 │     8 │        NULL │
└──────────┴─────────────────────┴───────┴─────────────┘

In [39]: ibis.to_sql(t.mutate(value_lag_1=(_.date.delta(_.date.lag().over(group_by="store_id"), 'days') == 1).cast('int').nullif(0) * _.value.lag().over(group_by="store_id")).order_by("store_id", "d
    ...: ate"))
Out[39]:
SELECT
  *
FROM (
  SELECT
    "t0"."store_id",
    "t0"."date",
    "t0"."value",
    NULLIF(
      CAST(DATE_DIFF(
        'DAYS',
        LAG("t0"."date") OVER (PARTITION BY "t0"."store_id" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),
        "t0"."date"
      ) = 1 AS BIGINT),
      0
    ) * LAG("t0"."value") OVER (PARTITION BY "t0"."store_id" ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "value_lag_1"
  FROM "t" AS "t0"
) AS "t1"
ORDER BY
  "t1"."store_id" ASC,
  "t1"."date" ASC