polars.LazyFrame.join_where#

LazyFrame.join_where( other: LazyFrame, *predicates: Expr | Iterable[Expr], how: JoinWhereStrategy = 'inner', suffix: str = '_right', ) → LazyFrame[source]#

Perform a join based on one or multiple (in)equality predicates.

engine:In MemoryPartially StreamingPartially Distributed

Note

The row order of the input DataFrames is not preserved.

Warning

This functionality is experimental. It may be changed at any point without it being considered a breaking change.

Parameters:

other: DataFrame to join with.
*predicates: (In)Equality condition to join the two tables on. When a column name occurs in both tables, the proper suffix must be applied in the predicate.
how{‘inner’, ‘left’, ‘right’}: Join strategy.
suffix: Suffix to append to columns with a duplicate name.

See also

join
join_asof

Examples

Join two lazyframes together based on two predicates which get AND-ed together.

>>> east = pl.LazyFrame(
...     {
...         "id": [100, 101, 102],
...         "dur": [120, 140, 160],
...         "rev": [12, 14, 16],
...         "cores": [2, 8, 4],
...     }
... )
>>> west = pl.LazyFrame(
...     {
...         "t_id": [404, 498, 676, 742],
...         "time": [90, 130, 150, 170],
...         "cost": [9, 13, 15, 16],
...         "cores": [4, 2, 1, 4],
...     }
... )
>>> east.join_where(
...     west,
...     pl.col("dur") < pl.col("time"),
...     pl.col("rev") < pl.col("cost"),
... ).collect()
shape: (5, 8)
┌─────┬─────┬─────┬───────┬──────┬──────┬──────┬─────────────┐
│ id  ┆ dur ┆ rev ┆ cores ┆ t_id ┆ time ┆ cost ┆ cores_right │
│ --- ┆ --- ┆ --- ┆ ---   ┆ ---  ┆ ---  ┆ ---  ┆ ---         │
│ i64 ┆ i64 ┆ i64 ┆ i64   ┆ i64  ┆ i64  ┆ i64  ┆ i64         │
╞═════╪═════╪═════╪═══════╪══════╪══════╪══════╪═════════════╡
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 498  ┆ 130  ┆ 13   ┆ 2           │
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 676  ┆ 150  ┆ 15   ┆ 1           │
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
│ 101 ┆ 140 ┆ 14  ┆ 8     ┆ 676  ┆ 150  ┆ 15   ┆ 1           │
│ 101 ┆ 140 ┆ 14  ┆ 8     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
└─────┴─────┴─────┴───────┴──────┴──────┴──────┴─────────────┘

To OR them together, use a single expression and the | operator.

>>> east.join_where(
...     west,
...     (pl.col("dur") < pl.col("time")) | (pl.col("rev") < pl.col("cost")),
... ).collect()
shape: (6, 8)
┌─────┬─────┬─────┬───────┬──────┬──────┬──────┬─────────────┐
│ id  ┆ dur ┆ rev ┆ cores ┆ t_id ┆ time ┆ cost ┆ cores_right │
│ --- ┆ --- ┆ --- ┆ ---   ┆ ---  ┆ ---  ┆ ---  ┆ ---         │
│ i64 ┆ i64 ┆ i64 ┆ i64   ┆ i64  ┆ i64  ┆ i64  ┆ i64         │
╞═════╪═════╪═════╪═══════╪══════╪══════╪══════╪═════════════╡
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 498  ┆ 130  ┆ 13   ┆ 2           │
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 676  ┆ 150  ┆ 15   ┆ 1           │
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
│ 101 ┆ 140 ┆ 14  ┆ 8     ┆ 676  ┆ 150  ┆ 15   ┆ 1           │
│ 101 ┆ 140 ┆ 14  ┆ 8     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
│ 102 ┆ 160 ┆ 16  ┆ 4     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
└─────┴─────┴─────┴───────┴──────┴──────┴──────┴─────────────┘

Pass how="left" to additionally keep left rows that match nothing, with the right columns set to null.

>>> east.join_where(
...     west,
...     pl.col("dur") < pl.col("time"),
...     pl.col("rev") < pl.col("cost"),
...     how="left",
... ).collect()
shape: (6, 8)
┌─────┬─────┬─────┬───────┬──────┬──────┬──────┬─────────────┐
│ id  ┆ dur ┆ rev ┆ cores ┆ t_id ┆ time ┆ cost ┆ cores_right │
│ --- ┆ --- ┆ --- ┆ ---   ┆ ---  ┆ ---  ┆ ---  ┆ ---         │
│ i64 ┆ i64 ┆ i64 ┆ i64   ┆ i64  ┆ i64  ┆ i64  ┆ i64         │
╞═════╪═════╪═════╪═══════╪══════╪══════╪══════╪═════════════╡
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 498  ┆ 130  ┆ 13   ┆ 2           │
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 676  ┆ 150  ┆ 15   ┆ 1           │
│ 100 ┆ 120 ┆ 12  ┆ 2     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
│ 101 ┆ 140 ┆ 14  ┆ 8     ┆ 676  ┆ 150  ┆ 15   ┆ 1           │
│ 101 ┆ 140 ┆ 14  ┆ 8     ┆ 742  ┆ 170  ┆ 16   ┆ 4           │
│ 102 ┆ 160 ┆ 16  ┆ 4     ┆ null ┆ null ┆ null ┆ null        │
└─────┴─────┴─────┴───────┴──────┴──────┴──────┴─────────────┘