Is it possible to reference another dataframe when using Polars expressions without using a lambda?
07:00 04 Aug 2022

Is there a way to reference another Polars Dataframe in Polars expressions without using lambdas?

Just to use a simple example - suppose I have two dataframes:

import polars as pl

df_1 = pl.DataFrame(
    {
        "time": pl.date_range(
            pl.date(2021, 1, 1),
            pl.date(2022, 1, 1),
            interval="1d",
            eager=True
        ),
        "x": pl.int_range(0, 366, eager=True),
    }
)

df_2 = pl.DataFrame(
    {
        "time": pl.date_range(
            pl.date(2021, 1, 1),
            pl.date(2021, 2, 1),
            interval="1mo",
            eager=True
        ),
        "y": [50, 100],
    }
)

For each y value in df_2, I would like to find the maximum date in df_1, conditional on the x value being lower than the y.

I am able to perform this using map_elements/lambda (see below), but just wondering whether there is a more idiomatic way of performing this operation?

df_2.group_by("y").agg(
    pl.col("y").map_elements(lambda s: df_1.filter(pl.col("x") < s).select(pl.col("time")).max().item()).alias('latest')
)
shape: (2, 2)
┌─────┬────────────┐
│ y   ┆ latest     │
│ --- ┆ ---        │
│ i64 ┆ date       │
╞═════╪════════════╡
│ 50  ┆ 2021-02-19 │
│ 100 ┆ 2021-04-10 │
└─────┴────────────┘

Edit:

Is it possible to pre-filter df_1 prior to using join_asof. So switching the question to look for the min instead of the max, on an individual case this is what I would do:

(
    df_2
    .filter(pl.col('y') == 50)
    .join_asof(
                df_1
                .sort("x")
                .filter(pl.col('time') > date(2021,11,1))
                .select(
                    pl.col("time").cum_min().alias("time_min"),
                    pl.col("x").alias("original_x"),
                    (pl.col("x") + 1).alias("x"),
                ),
            left_on="y",
            right_on="x",
            strategy="forward",
    )
)

Is there a way to generalise this merge without using a loop / map_elements function?

python dataframe python-polars asof-join