Writing

Joining and Reshaping Data with Polars: joins, concat, pivot, unpivot, and unnest

Real-world analytics rarely lives in a single table. You must routinely combine disparate tables using relational joins, align asynchronous time series feeds, or reshape wide metrics into tidy normalized formats.

This guide explores Polars' suite of joining, concatenation, and restructuring operators.

Download the example data: Pokemon.csv.

1. Relational Joins (join)

Polars supports all standard relational join strategies: inner, left, full (outer), cross, semi, and anti.

import polars as pl

pokemon = pl.read_csv("Pokemon.csv")

moves = pl.DataFrame({
    "Name": ["Pikachu", "Charizard", "Mewtwo"],
    "Signature_Move": ["Thunder", "Flamethrower", "Psychic"],
    "Base_Power": [110, 90, 90]
})

# Left join keeps all primary Pokemon records
merged = pokemon.join(moves, on="Name", how="left")

Filtering Joins: Semi and Anti Joins

  • Semi Join (how="semi"): Filters the left table to rows that match keys in the right table without appending right-hand columns.
  • Anti Join (how="anti"): Filters the left table to rows that have no match in the right table (ideal for spotting missing keys or orphans).
# Returns only Pokemon that have an entry in the moves table
with_moves = pokemon.join(moves, on="Name", how="semi")

# Returns Pokemon without move entries
without_moves = pokemon.join(moves, on="Name", how="anti")

2. As-Of Joins (join_asof) for Time Series

Financial tick data, system metrics, and IoT logs rarely share exact timestamps. join_asof matches keys based on the closest prior timestamp (or next timestamp) within an optional tolerance window:

trades = pl.DataFrame({
    "time": ["2026-10-25 09:30:00", "2026-10-25 09:30:05"],
    "ticker": ["AAPL", "AAPL"],
    "size": [100, 200]
}).with_columns(pl.col("time").str.to_datetime())

quotes = pl.DataFrame({
    "time": ["2026-10-25 09:29:59", "2026-10-25 09:30:04"],
    "ticker": ["AAPL", "AAPL"],
    "bid": [150.10, 150.15]
}).with_columns(pl.col("time").str.to_datetime())

# Match each trade to the latest quote preceding it
aligned = trades.join_asof(
    quotes,
    on="time",
    by="ticker",
    strategy="backward"
)

3. Concatenating DataFrames (pl.concat)

  • Vertical (how="vertical"): Stacks matching schemas row-wise.
  • Horizontal (how="horizontal"): Glues columns side-by-side (index position matters).
  • Diagonal (how="diagonal"): Stacks dataframes with differing schemas, filling missing columns with nulls.
batch1 = pokemon.filter(pl.col("Generation") == 1)
batch2 = pokemon.filter(pl.col("Generation") == 2)

stacked = pl.concat([batch1, batch2], how="vertical")

4. Reshaping Tables: Pivot & Unpivot

Long to Wide (pivot)

Aggregate categorical metric values across columns:

[!NOTE] Polars API Update In Polars 1.0+, aggregate_function parameter is named aggregate_function (or agg depending on sub-release), taking strings like "mean", "sum", or "first".

# Average HP by Generation across Type 1 columns
wide_hp = pokemon.pivot(
    values="HP",
    index="Generation",
    on="Type 1",
    aggregate_function="mean"
)

Wide to Long (unpivot)

Convert multiple metric columns into key-value pairs (the equivalent of Pandas melt):

long_stats = pokemon.unpivot(
    index=["Name", "Type 1"],
    on=["HP", "Attack", "Defense", "Speed"],
    variable_name="stat_type",
    value_name="stat_value"
)

5. Working with Nested & Structured Data

Exploding Lists (explode)

When an entry contains an embedded list, explode() duplicates rows for each list element:

# Convert list of types into individual rows
df_exploded = df_with_types.explode("Types")

Flattening Structs (unnest)

When columns contain nested JSON-like key-value structures, unnest() unpacks the sub-fields into top-level columns:

# Unpacks struct fields (Attack, Defense, HP) into standalone columns
flattened = structured_df.unnest("stats_group")

Next in the series: Optimizing Polars Performance and Using SQL.