Question: I have two dataset coming from two very different data sources. Dataframe 1 ┌────────────┬─────────────────┬─────────────────┬─────────────────┬────────────────┬──────────────┐ │ date ┆ label ┆ org_slug ┆ org_id ┆ org_name ┆ issues_count │ │ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │ │ date ┆ str ┆ str ┆ str ┆ str ┆ i64 │ ╞════════════╪═════════════════╪═════════════════╪═════════════════╪════════════════╪══════════════╡ │ 2023-08-29 ┆ label1 ┆ org-slug-name-1 ┆ org-id-1 ┆ org-name ┆ 1 │ │ └────────────┴─────────────────┴─────────────────┴─────────────────┴────────────────┴──────────────┘  Dataframe 2 ┌────────────┬─────────────┬───────────────────┬───────────────────┬───────────┬─────────┐ │ date ┆ org_name ┆ org_id ┆ org_slug ┆ info_1 ┆ info_2 │ │ --- ┆ --- ┆ --- ┆ --- ┆ --- ┆ --- │ │ date ┆ str ┆ str ┆ str ┆ i64 ┆ i64 │ ╞════════════╪═════════════╪═══════════════════╪═══════════════════╪═══════════╪═════════╡ │ 2023-08-29 ┆ org-name ┆ org-id-1 ┆ org-slug-name-1 ┆ 10 ┆ 12 └────────────┴─────────────┴───────────────────┴───────────────────┴───────────┴─────────┘  I'm trying to join these two dataframe based on date. Then I would like to still joining via org_id or org_slug or org_name. These generally match, but there are times where org_slug or org_id can be null and I want to rely on the next condition. is it possible to achieve it in polars?

Answer: Assuming your data per date is small, you might get away with an inner join to a filter. However, this can quickly explode the result set and may run out of memory. print( df1.join(df2, on=["date"], how="inner") .filter( pl.any_horizontal( pl.col("org_id") == pl.col("org_id_right"), pl.col("org_slug") == pl.col("org_slug_right"), pl.col("org_name") == pl.col("org_name_right"), ) ) .drop("org_name_right", "org_id_right", "org_slug_right") )  If your data is of any significant size, it might be better suited to do multiple left joins, then include a when condition to match to the first matched join. The .with_columns(pl.lit(True).alias("joined")) will be null if the join fails, or true if is matches. # columns we want to get from the join (e.g. info_1, info_2) df2_columns = [c for c in df2.columns if c in set(df2.columns) - set(df1.columns)] # expression to select the results of the first join, # alias the output to the first join's name when_expr = [ ( # First join criteria pl.when(pl.col("joined").is_not_null()) .then(pl.col(c)) # Second join criteria .when(pl.col("joined_joinslug").is_not_null()) .then(pl.col(f"{c}_joinslug").alias(c)) # Third join criteria .when(pl.col("joined_joinname").is_not_null()) .then(pl.col(f"{c}_joinname").alias(c)) ) for c in df2_columns ] # Multiple left joins on the different join keys. print( df1.join( df2.with_columns(pl.lit(True).alias("joined")), on=["date", "org_id"], how="left", suffix="_joinid", ) .join( df2.with_columns(pl.lit(True).alias("joined")), on=["date", "org_slug"], how="left", suffix="_joinslug", ) .join( df2.with_columns(pl.lit(True).alias("joined")), on=["date", "org_name"], how="left", suffix="_joinname", ) .select(pl.col(df1.columns), *when_expr) )  Another alternative is to loop through each key set and collect the inner join matches and try the remaining anti join results, after going through each join, concatenate the results. df2_columns = [c for c in df2.columns if c in set(df2.columns) - set(df1.columns)] out_dfs = [] for joinkey in ["org_id", "org_slug", "org_name"]: out_df = df1.join( df2, on=["date", joinkey], how="inner", ).select(pl.col(df1.columns), pl.col(df2_columns)) out_dfs.append(out_df) df1 = df1.join( df2, on=["date", joinkey], how="anti", ) out_df = pl.concat(out_dfs)