Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

Python Polars Join, Concat, and Unique Operations

Practical guide to using Polars join, concat, and unique to combine and deduplicate DataFrames, with examples for common production edge cases.

~6 min readPython
#polars#dataframe#data-engineering#python
Illustration of three data streams merging into a single output, representing Polars join, concat, and unique operations.

Working with DataFrames in Polars often means combining data. Three operations cover most of this work: join(), concat(), and unique(). Joins combine columns from two frames based on key columns, concatenation stacks frames vertically or horizontally, and unique operations remove duplicate rows or identify distinct values. Each behaves differently from its pandas counterpart, so it helps to know what those differences are.

Why Combining DataFrames in Polars Differs from pandas

If you are coming from pandas, the first thing to notice is that Polars is explicit about what each operation does. There is no single merge method that tries to infer intent. Instead, join() handles key-based column combination, concat() handles stacking, and unique() handles deduplication. This separation makes code easier to reason about and lets the query engine optimize each operation independently.

Polars also does not use index-based alignment. In pandas, concat(axis=1) aligns on index values by default, and merge uses index alignment only when you explicitly request left_index=True or right_index=True. Polars treats DataFrames as collections of columns with positional rows, so combining operations must be explicit about how rows relate to each other.

Joining DataFrames with join()

The join() method combines two DataFrames based on key columns. The basic syntax mirrors SQL:

python
import polars as pl customers = pl.DataFrame({ "customer_id": [1, 2, 3, 4], "name": ["Alice", "Bob", "Carol", "Dave"], }) orders = pl.DataFrame({ "customer_id": [2, 3, 3, 5], "order_id": [101, 102, 103, 104], "amount": [250.0, 175.5, 89.0, 420.0], }) result = customers.join(orders, on="customer_id", how="inner") print(result)

The result contains only rows where customer_id exists in both frames. Customer 1 and 4 are dropped because they have no orders, and customer 5 is dropped because it has no customer record. Note that customer 3 appears twice because they have two orders — this is standard SQL join behavior.

The how parameter controls the join type:

Join typeBehaviorUse case
innerOnly rows with matching keys in both framesFiltering to records that exist in both sources
leftAll rows from left, nulls for unmatched rightEnriching a primary dataset with optional data
outerAll rows from both, nulls where no matchComparing two datasets to find gaps
crossCartesian product of all rowsGenerating combinations, rarely needed
semiRows from left where a match exists, no right columnsFiltering without adding columns
antiRows from left where no match existsFinding records missing from the other frame

When the key column names differ between the two frames, use left_on and right_on:

python
orders_renamed = orders.rename({"customer_id": "cust_id"}) result = customers.join(orders_renamed, left_on="customer_id", right_on="cust_id", how="left")

Polars will not guess which columns to join on. If you omit the key specification entirely, it raises an error rather than falling back to a default.

Concatenating DataFrames Vertically with concat()

Vertical concatenation stacks DataFrames on top of each other, adding rows. This is the standard way to combine frames that share the same schema, such as batches of log data or partitioned query results.

python
df1 = pl.DataFrame({"id": [1, 2], "value": ["a", "b"]}) df2 = pl.DataFrame({"id": [3, 4], "value": ["c", "d"]}) combined = pl.concat([df1, df2]) print(combined)

The result has four rows and the same two columns. By default, concat() requires all frames to have identical schemas. If the schemas differ, you must choose a mode:

  • how="vertical_relaxed" — allows vertical concatenation when the same columns have different data types
  • how="diagonal" — aligns columns by name across frames and fills gaps with null for columns that are missing from a frame
python
df3 = pl.DataFrame({"id": [5], "value": ["e"], "extra": [True]}) combined = pl.concat([df1, df3], how="diagonal")

This produces a frame with columns id, value, and extra. Rows from df1 have null in the extra column. The diagonal mode is useful when you are combining data from sources that have evolved over time and gained new columns.

Concatenating Horizontally with concat(how="horizontal")

Horizontal concatenation adds columns rather than rows. It is useful when you have two frames with the same number of rows but different features — for example, when feature engineering produces separate frames that need to be combined into one training dataset.

python
features_a = pl.DataFrame({"id": [1, 2, 3], "feature_a": [0.1, 0.2, 0.3]}) features_b = pl.DataFrame({"feature_b": [True, False, True]}) combined = pl.concat([features_a, features_b], how="horizontal")

Horizontal concatenation does not align by key. It places columns side by side, so the row counts must match exactly. If they do not, Polars raises an error rather than silently producing misaligned data.

This is a deliberate departure from pandas, where pd.concat(axis=1) aligns on index values. In Polars, you are responsible for ensuring the row order matches. If the frames were produced independently, sort both by the same key before concatenating.

Working with Unique Values

The unique() method removes duplicate rows. By default, it considers all columns when determining uniqueness:

python
df = pl.DataFrame({ "user_id": [1, 1, 2, 3, 3], "action": ["login", "login", "logout", "login", "purchase"], }) unique_rows = df.unique()

The result keeps one row per unique combination of user_id and action. To deduplicate based on a subset of columns, pass subset:

python
unique_per_user = df.unique(subset=["user_id"])

This keeps one row per user_id, dropping the other rows. The keep parameter controls which row is retained: "any" (the default) keeps an arbitrary row, while "first" and "last" keep the first or last occurrence in the frame's row order.

To get unique values from a single column, call unique() on the Series:

python
unique_users = df["user_id"].unique()

When you need the frequency of each value, use value_counts():

python
counts = df["user_id"].value_counts()

This returns a two-column DataFrame with the value and its count, sorted by count in descending order.

Performance and Memory Considerations

Polars uses hash joins for equi-joins, which scale well with large datasets. The join key type matters: integer keys hash faster than string keys, so joining on integer columns is generally cheaper. If you are joining on strings and the cardinality is high, consider encoding the strings as integers first.

Vertical concatenation is essentially a memory copy operation. If you are concatenating many small frames, consider whether you can avoid materializing them in the first place. For example, pl.scan_parquet("data/*.parquet") reads multiple files lazily, and calling collect() at the end produces a single frame without intermediate concatenation steps.

unique() must compare every row with the set of keys it has already seen, so it uses memory as the distinct-key set grows. If you only need a yes-or-no answer, use is_duplicated() followed by any() instead of materializing a deduplicated frame.

For large pipelines, use lazy frames so Polars can push down predicates and projections through joins:

python
other = pl.scan_parquet("other.parquet") lazy_result = ( pl.scan_parquet("data/*.parquet") .join(other, on="key", how="left") .unique(subset=["key"]) .collect() )

This lets the query planner eliminate columns and rows before the join runs, reducing memory pressure and the work each operation performs.

Common Pitfalls and Edge Cases

Duplicate keys in the right frame. If the right DataFrame has duplicate values in the join key, the result contains multiple rows for each match. This is correct SQL behavior, but it can surprise developers who expect pandas-style index alignment. Check for duplicates in the right frame before joining if you expect a one-to-one relationship.

Null values in join keys. Polars treats null as a valid join key value. Two rows with null in the join key will match each other in an inner join. If null keys should not match, filter them out first:

python
df_clean = df.filter(pl.col("key").is_not_null())

Schema mismatch in concat(). Calling pl.concat() without specifying how raises an error when schemas differ. Use how="diagonal" to align columns by name, or how="vertical_relaxed" when only data types differ.

Unique with nulls. unique() treats rows with null values as duplicates of each other. Two rows where every column is null collapse to one row. If nulls should be preserved as distinct, fill them with a sentinel value before deduplication.

Column name collisions in horizontal concat. If both frames have a column with the same name, horizontal concat produces duplicate column names. Accessing such columns by name becomes ambiguous. Rename columns before concatenating when this matters:

python
features_b_renamed = features_b.rename({"feature_b": "flag"}) combined = pl.concat([features_a, features_b_renamed], how="horizontal")

These edge cases are where most bugs appear in production pipelines. Being explicit about join keys, concat modes, and deduplication behavior is the difference between a pipeline that silently corrupts data and one that fails loudly when assumptions are violated.

Try matching browser tools

All tools

Small utilities that fit Python work — they run entirely in this browser tab, so nothing you paste leaves your device.

All Python guides

More in Python

All Python guides

The guides filed next to this one in Python, in library order.