New join type: LEFT ANY JOIN, RIGHT ANY JOIN and INNER ANY JOIN about polars HOT 2 OPEN

A join_asof should accomplish this for you as a LEFT ANY JOIN. You may have to play with some of the parameters depending on your use case.

import polars as pl

df1 = pl.DataFrame({
    "key": [1, 2, 3],
    "a": [1, 2, 3],
}).set_sorted("key")

df2 = pl.DataFrame({
    "key": [1, 2, 2, 3, 3],
    "a": [1, 2, 3, 4, 5],
}).set_sorted("key")

print(df1.join_asof(df2, on="key"))

shape: (3, 3)
┌─────┬─────┬─────────┐
│ key ┆ a   ┆ a_right │
│ --- ┆ --- ┆ ---     │
│ i64 ┆ i64 ┆ i64     │
╞═════╪═════╪═════════╡
│ 1   ┆ 1   ┆ 1       │
│ 2   ┆ 2   ┆ 3       │
│ 3   ┆ 3   ┆ 5       │
└─────┴─────┴─────────┘

For a RIGHT ANY JOIN you can swap the dataframes, as polars does not support a right-join right now, although there is an open issue for this feature.

In the general case, you can always call df1.join(df2.unique(subet=join_key), ...) to accomplish your task.

from polars.

I don't think asof_join is a valid workaround for my question, namely because it has tolerance, and requires sorted dataframes.

For example, the following would return a non-empty dataframe, while in proposal:

• ANY INNER should return empty dataframe
• ANY LEFT should return empty a_right.

import polars as pl

df1 = pl.DataFrame({
    "key": [1, 10, 20],
    "a": [1, 2, 3],
}).set_sorted("key")

df2 = pl.DataFrame({
    "key": [2, 8, 9, 13, 18],
    "a": [1, 2, 3, 4, 5],
}).set_sorted("key")

print(df1.join_asof(df2, on="key"))

>>> print(df1.join_asof(df2, on="key"))
shape: (3, 3)
┌─────┬─────┬─────────┐
│ key ┆ a   ┆ a_right │
│ --- ┆ --- ┆ ---     │
│ i64 ┆ i64 ┆ i64     │
╞═════╪═════╪═════════╡
│ 1   ┆ 1   ┆ null    │
│ 10  ┆ 2   ┆ 3       │
│ 20  ┆ 3   ┆ 5       │
└─────┴─────┴─────────┘

from polars.