Polars for Finance: Join DataFrames in Polars — Python Tutorial

prices.join(sectors, on="Ticker", how="left") — the SQL semantics every finance analyst already knows, in Polars’s expression API. One line takes 28 thousand price rows and tags every one with its sector, no merge keys to manage by hand, no index alignment ceremony.

Finance data lives in pieces. Prices in one table, metadata in another, fundamentals in a third. Production work is the join — the line that stitches the row you have to the lookup table you need. Polars’s .join is the SQL JOIN you’ve used a thousand times, with the Polars expression API on either side of it.

The four join types you’ll actually use

Polars supports all the SQL flavors. The ones you’ll use ninety-five percent of the time:

The other two — anti (left rows without a right match) and cross (Cartesian product) — are useful but rarer.

Setup

Same venv, same dataset. data/sector_map.csv is the lookup table — fourteen rows, two columns, Ticker and Sector.

source .venv/bin/activate
nvim joins.py

Your join script

import polars as pl

prices = pl.read_parquet("data/prices.parquet")
sectors = pl.read_csv("data/sector_map.csv")

joined = prices.join(sectors, on="Ticker", how="left")

print("=== Joined (prices + sector) ===")
print(joined.head())
print(joined.shape)

Save (:wq), run:

python joins.py

Output:

=== Joined (prices + sector) ===
shape: (5, 9)
┌─────────────────────┬────────┬───────┬───────┬───────┬───────┬───────────┬───────────┬────────────┐
│ Date                ┆ Ticker ┆ Open  ┆ High  ┆ Low   ┆ Close ┆ Adj Close ┆ Volume    ┆ Sector     │
│ ---                 ┆ ---    ┆ ---   ┆ ---   ┆ ---   ┆ ---   ┆ ---       ┆ ---       ┆ ---        │
│ datetime[ms]        ┆ str    ┆ f64   ┆ f64   ┆ f64   ┆ f64   ┆ f64       ┆ i64       ┆ str        │
╞═════════════════════╪════════╪═══════╪═══════╪═══════╪═══════╪═══════════╪═══════════╪════════════╡
│ 2018-01-02 00:00:00 ┆ AAPL   ┆ 42.54 ┆ 43.08 ┆ 42.31 ┆ 43.06 ┆ 40.30     ┆ 102223600 ┆ Technology │
…
└─────────────────────┴────────┴───────┴───────┴───────┴───────┴───────────┴───────────┴────────────┘
(28140, 9)

Same 28,140 rows, now 9 columns — the original eight plus Sector. Every row stamped with its ticker’s sector via the left join.

That’s the entire mechanic. One method, three arguments — on, how, and either of the source frames. The Ticker key matches the column names on both sides; Polars handles the alignment automatically.

Stack with groupby for sector-level views

Once the sector column is in place, every Episode 4 aggregation can group on it:

sector_stats = joined.with_columns(
  daily_ret=pl.col("Close").pct_change().over("Ticker"),
).group_by("Sector").agg([
  pl.col("Ticker").n_unique().alias("tickers"),
  pl.col("Close").mean().alias("avg_close"),
  pl.col("daily_ret").std().alias("ret_std"),
]).sort("avg_close", descending=True)

print(sector_stats)

Output:

shape: (7, 4)
┌────────────────────────┬─────────┬────────────┬──────────┐
│ Sector                 ┆ tickers ┆ avg_close  ┆ ret_std  │
├────────────────────────┼─────────┼────────────┼──────────┤
│ Index ETF              ┆ 1       ┆ 413.49     ┆ 0.0123   │
│ Consumer Discretionary ┆ 2       ┆ 161.47     ┆ 0.0322   │
│ Financials             ┆ 1       ┆ 155.30     ┆ 0.0183   │
│ Health Care            ┆ 1       ┆ 154.67     ┆ 0.0124   │
│ Technology             ┆ 3       ┆ 150.91     ┆ 0.0241   │
│ Communication Services ┆ 1       ┆ 115.07     ┆ 0.0195   │
│ Sector ETF             ┆ 5       ┆ 68.40      ┆ 0.0158   │
└────────────────────────┴─────────┴────────────┴──────────┘

Seven sectors, three statistics each. Technology (AAPL + MSFT + NVDA) shows the highest volatility of the single-name sectors at 2.4 percent daily; Consumer Discretionary (AMZN + TSLA) is even higher at 3.2 percent driven by Tesla’s noise. The Index ETF row is the SPY benchmark with the lowest volatility — that’s the market floor.

Join then group is the canonical analyst pipeline shape. Adding more lookup tables is more joins.

Different key names on each side

When the join columns don’t share a name — left has Ticker, right has Symbol:

joined = prices.join(sectors, left_on="Ticker", right_on="Symbol", how="left")

Use left_on and right_on instead of on. The result frame keeps the left column name.

Multi-column joins

For composite keys — e.g. joining on (Ticker, Date) to merge a corporate-action table:

joined = prices.join(actions, on=["Ticker", "Date"], how="left")

Pass a list to on. All keys must match for a row to join.

Inner join — drop unmatched rows

If your sector lookup is missing a ticker, a left join leaves nulls in the Sector column for those rows. An inner join drops them entirely:

joined_inner = prices.join(sectors, on="Ticker", how="inner")

Useful when downstream code can’t handle nulls. Trade-off: silent row loss. Check the shape before and after if you care.

Anti join — rows without a match

The “find the gaps” query. Show me every price row whose ticker is NOT in the sector map:

orphans = prices.join(sectors, on="Ticker", how="anti")
print(orphans["Ticker"].unique())

If orphans is empty, your lookup table is complete. If not, you have prices for tickers you can’t sector-classify — a data-quality issue worth fixing.

Semi join — filter by membership

The “keep what matches” query, without bringing in the right-side columns:

covered = prices.join(sectors, on="Ticker", how="semi")

Same row count as inner would produce, but column count stays at the original 8. Useful when you want to filter prices to “tickers we cover” without polluting the frame with sector columns yet.

Pandas → Polars cheatsheet for joins

pd.merge becomes pl.DataFrame.join. The argument shapes are almost identical; the semantics are identical.

Column-name collisions

When both frames have a non-key column with the same name, Polars appends _right to the right-side column by default:

left  = pl.DataFrame({"T": ["A"], "Close": [100.0]})
right = pl.DataFrame({"T": ["A"], "Close": [99.5]})
left.join(right, on="T")
# columns: T, Close, Close_right

Override with suffix=:

left.join(right, on="T", suffix="_prev")
# columns: T, Close, Close_prev

Performance — hash join is the default

Polars picks a hash join automatically for equality-on-key joins. For sorted frames, the join_asof method gives you the time-series merge (“merge_asof” in pandas) — match each left row to the nearest-prior right row, useful for tick-data joins.

joined = prices.join_asof(actions, on="Date", by="Ticker", strategy="backward")

Used heavily in tick-data pipelines. Same shape as a regular join; the engine picks the right algorithm.

Common stumbles

Result has too many rows. Right table has duplicate keys, causing one-to-many expansion. Run right.group_by("key").len() to find duplicates before joining.

Result has fewer rows than expected. You used inner and some left rows had no right match. Switch to left to see the nulls, or use anti to find which keys are missing.

SchemaError: type mismatch on join key. Left has Int64 keys, right has String. Cast one side first: df.with_columns(pl.col("T").cast(pl.Utf8)).

Polars complains about ordering after join. Joins don’t preserve original row order. Sort after if you need it: joined.sort(["Ticker", "Date"]).

Pandas’s validate= is missing. Polars doesn’t have a pre-join cardinality validator. Check yourself: assert left["T"].is_unique() and right["T"].is_unique() before a one-to-one join.

Recap

df.join(other, on="key", how="left") is the canonical shape. how= chooses semantics: left for tagging, inner for intersection, outer for union, anti for gaps, semi for filtering. Pass a list to on= for multi-key joins. Use left_on and right_on when key names differ. Combine join with with_columns and group_by to get the full analyst pipeline: load pieces, stitch them, derive columns, summarize. The substitution for every pandas merge is direct — same arguments, same shape, faster execution.

Subscribe to the playlist for the next episode in the series.