“Statistics are like bikinis. What they reveal is suggestive, but what they conceal is vital.”
— Aaron Levenstein
Summary
Covers the four structural pillars of DataFrame manipulation — grouping, windowing, table combination, and reshape — using EuroStoxx price, dimension, and score data to show how Pandas and Polars summarize, enrich, and reorient tables without losing track of row counts, join cardinality, or output shape.
Part 1: Group By
Aggregate by one or more keys with groupby() / group_by(), named aggregation, multi-function aggregation, and same-length group statistics via transform() or Polars .over(...)
Apply group-by-plus-sort patterns and sector-level analysis to turn raw OHLCV and scores tables into grouped summaries
Part 2: Window Functions
Build rolling windows, cumulative metrics, rank-within-group logic, and lead/lag features without collapsing the original row set
Emphasize ordering requirements so rolling and window calculations operate on meaningful sequences rather than shuffled rows
Part 3: Combining DataFrames
Compare inner, left, anti, semi, and cross joins plus vertical, horizontal, and diagonal concat patterns
Show how dimension enrichment, membership filtering, and schema-aware table stacking behave differently depending on key uniqueness and shape
Part 4: Reshaping
Convert between long and wide layouts with melt() / unpivot(), pivot(), explode, implode, transpose, and one-hot encoding
Highlight where duplicates, list cardinality, or dynamic category sets make reshape operations fail or explode in width/row count
Operations and safety
Warnings: many-to-many joins silently multiply rows, cross joins scale quadratically, Polars group_by() is unordered until sorted, as_index=False is version-sensitive in Pandas, rolling windows need sorted data, and pivot() fails on duplicate key pairs
Recommendations: 7 practices covering join-cardinality validation, explicit post-group sorting in Polars, named aggregation, .over() for Polars transforms, limiting cross joins, unpivot() preference, and post-join/post-reshape shape assertions
Troubleshooting: 8 failure modes covering join row explosions, missing groups from null keys, duplicate-entry pivot errors, null-producing concat schema mismatches, anti-join empties, explode dtype issues, and one-hot width explosions
Glossary
groupby() / group_by()
Grouping operations that partition a DataFrame by one or more key columns before applying aggregations or grouped transforms.
They matter because the note’s aggregation patterns all start by defining which rows belong together logically.
Group object is not the result
In Pandas, groupby() returns a GroupBy object rather than a summary table. You still need .agg(), .transform(), or another grouped operation to materialize results.
Aggregation / agg()
A reduction step that turns many rows per group into one or a few summary values such as mean, sum, count, min, max, first, or last.
It matters because grouping only becomes analytically useful once those groups are reduced into interpretable statistics.
Count semantics differ
Counting non-null values is not always the same as counting all rows. Verify whether you need count, size, or a length-style aggregate.
Named aggregation
An aggregation style that assigns explicit output names while computing grouped metrics, instead of accepting auto-generated or MultiIndex column labels.
It matters because clear output schemas make grouped results easier to chain into joins, sorts, and downstream analysis.
Prefer explicit output names
Auto-generated aggregate names often become ambiguous once multiple source columns and functions are involved. Naming the outputs up front avoids cleanup later.
Transform
A grouped computation that returns the same number of rows as the input by broadcasting the group-level result back onto each original row.
It matters because the note uses it to enrich rows with group statistics without collapsing the table.
Same length is the key difference
If the result has fewer rows than the input, you aggregated; if it has the same length, you transformed. Mixing those expectations causes alignment bugs.
Window function
A per-row computation that depends on neighboring rows, group membership, or ordered context while preserving one output value per input row.
It matters because ranking, rolling metrics, lag features, and cumulative statistics all rely on window semantics.
Order defines meaning
A window over unsorted data still computes something, but not necessarily something meaningful. Always verify the sort order before interpreting the result.
Rolling window
A fixed-size moving frame that slides across ordered rows and recomputes an aggregate at each position.
It matters because moving averages and related smoothing metrics are a core part of the note’s time-series examples.
Early rows are incomplete
The first rows of a rolling calculation usually lack a full window and therefore produce null or NaN outputs unless a minimum-period rule says otherwise.
Join
A table-combination operation that matches rows across DataFrames by one or more shared keys.
It matters because the note uses joins to enrich fact-like market data with dimension attributes and to filter by membership.
Cardinality mistakes multiply data
A join can silently create more rows than either input if keys are duplicated on both sides. Validate uniqueness assumptions before trusting row counts.
Inner join
A join that keeps only rows whose key exists in both input tables.
It matters when unmatched records are intentionally excluded and only confirmed matches should survive.
Dropped rows are easy to miss
An inner join removes non-matching rows silently. Compare input and output row counts if record loss matters.
Left join
A join that preserves every row from the left table and attaches matching columns from the right table where available.
It matters because it is the default enrichment pattern for fact-plus-dimension pipelines in this note.
Left join can still expand rows
Preserving all left rows does not guarantee preserving left row count. Duplicate keys on the right side still fan out the result.
Anti join
A join-style filter that returns only rows from the left table whose keys do not appear in the right table.
It matters for finding orphan records, excluded keys, and failed reference matches.
Useful for diagnostics
Anti joins are often the fastest way to answer “what did not match?” after a join or membership check.
Semi join
A join-style filter that keeps left-table rows whose keys exist in the right table, without bringing right-side columns into the result.
It matters because it expresses table-driven membership filtering more clearly than ad hoc boolean lists.
Membership without enrichment
Use a semi join when the right table is only a key filter and not a source of attributes you need to keep.
Cross join
A Cartesian product that pairs every row from one table with every row from the other.
It matters because it can generate scenario grids and exhaustive combinations, but it is also the easiest reshape/join pattern to blow up in size.
Growth is quadratic
Even medium-size inputs can create unmanageable outputs. Estimate row count before running a cross join, not after.
Concat
An operation that stacks DataFrames vertically or horizontally to combine partitions, results, or aligned column sets.
It matters because the note compares multiple concat modes and how schema mismatches surface in each library.
Schema alignment is part of the operation
Concat is not just appending memory blocks. Column names, column order, and dtypes determine whether the result is clean, sparse, or erroneous.
Pivot
A reshape that turns unique values from one column into new output columns, converting long-form data into wide-form layout.
It matters because pivoting is a common reporting pattern for ticker-by-period summary tables.
Duplicate coordinate pairs break pivots
If the same row-key and column-key combination appears more than once, you need an aggregation-aware pivot strategy instead of a strict pivot.
melt() / unpivot()
Long-format reshapes that turn multiple measured columns into key-value rows instead of keeping them as separate wide columns.
They matter because many analytics and visualization tools prefer long-form data over wide spreadsheets.
Same idea, different API names
Pandas and Polars expose the same conceptual reshape under different names. The operation is equivalent even though the function labels differ.
Explode
A reshape that turns each element of a list-like cell into its own row while duplicating the other row values as needed.
It matters because nested list columns must often be flattened before relational analysis or export.
Row count can surge
Exploding a list column multiplies rows by list length. On heavily nested data, that expansion can be large enough to affect memory planning.
Implode
The inverse-style operation of explode in Polars, collecting multiple row values into a single list-valued column per group.
It matters because the note uses it to package grouped records into compact list outputs before further processing.
Useful for grouped collection
Implode is a natural fit when you need “all values per key” as a list instead of one row per value.
Transpose
A reshape that swaps rows and columns so that previous row labels or identifiers become column headings in the output.
It matters for compact display-oriented summaries where metrics should appear as rows and entities as columns.
Best on small, regular tables
Transpose is most useful when the data is small and homogeneous. Large mixed-type tables usually become harder to interpret after transposition.
One-hot encoding
A categorical expansion that turns one category column into many binary indicator columns, one per distinct category.
It matters because machine-learning and numeric-only modeling pipelines often require categorical variables in this expanded form.
as_index=False vs as_index=True — fundamentally different output
Pandas groupby() defaults to as_index=True, which puts group keys into the index.
This breaks chaining with .merge() and makes the output unusable with many Pandas
operations. Always use as_index=False for pipeline code to get a flat DataFrame.
Polars group_by() always returns a flat DataFrame — no index concept exists.
Keep grouped keys as regular columns
Use df.groupby("col", as_index=False).agg(...) to get a flat DataFrame with group
keys as regular columns. This makes the result chainable with .merge(), .sort_values(),
and any downstream operation. In Polars, group_by() is always flat — no change needed.
Pandas named aggregation
Pandas named aggregation — .agg(new_name=("column", "func")) — is the cleanest
syntax for groupby. It names the output columns explicitly and avoids the confusing
MultiIndex column headers that .agg({"col": ["mean", "sum"]}) produces.
Per-Symbol Aggregation
Pandas | Group by symbol to compute mean close, total volume, and trading days
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Aggregates all 66,355 rows by symbol, producing one row per constituent with mean closing price, total share volume, and trading day count, then sorts by avg_close descending to surface the 10 highest-priced stocks.
Polars | Group by symbol to compute mean close, total volume, and trading days
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas aggregation using chained Polars expressions in a single .agg() call, rounding avg_close to 2 decimal places and returning the same 10-row flat DataFrame sorted by avg_close descending.
Pass a list of column names to groupby() / group_by() to create composite group keys. A common pattern is extracting a date part (year, month, quarter) as a new column and grouping on both symbol and that period — giving per-symbol per-period aggregates without a MultiIndex. Polars expresses the extraction inline with .dt.year() in a .with_columns() step; Pandas extracts to a new column first via pd.to_datetime(...).dt.year.
Composite Group Keys with Date Extraction
Pandas | Group by symbol and year to compute annual average and maximum close price
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Extracts year from the date column into a new ohlcv_pd["year"] column, then groups on ["symbol", "year"] to compute annual average and max close for ASML.AS, showing the last 5 years’ performance sorted ascending.
# Pandas: group by symbol + yearohlcv_pd["year"] = pd.to_datetime(ohlcv_pd["date"]).dt.yeardisplay( ohlcv_pd.groupby(["symbol", "year"], as_index=False) .agg(avg_close=("close", "mean"), max_close=("close", "max")) .query("symbol == 'ASML.AS'") .tail(5))
Rendered tabular output preserved below.
symbol
year
avg_close
max_close
49
ASML.AS
2022
531.578794
701.7
50
ASML.AS
2023
611.328627
694.7
51
ASML.AS
2024
799.262109
1002.2
52
ASML.AS
2025
724.614118
963.4
53
ASML.AS
2026
1170.418000
1288.4
Polars | Group by symbol and year to compute annual average and maximum close, extracting year inline
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Uses .with_columns(pl.col("date").dt.year()) to extract year without mutating the DataFrame, then groups on ["symbol", "year"] and filters to ASML.AS to show 5 years sorted descending — confirming identical annual statistics as the Pandas result.
Both libraries support computing multiple aggregation functions in a single groupby pass, avoiding the cost of repeated scans. Pandas uses named aggregation syntax — .agg(output_col=("input_col", "func")) — which produces a flat DataFrame with explicitly named columns. Polars uses a list of expressions in .agg(), each chained with .alias(). Named aggregation in Pandas is preferred over the dict-of-lists form .agg({"col": ["mean", "sum"]}), which produces confusing MultiIndex column headers.
Named Aggregation with Multiple Functions
Pandas | Compute mean, std, min, max close and date range per symbol using named aggregation
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Runs six aggregation functions in a single groupby() pass using named aggregation syntax, producing a flat 50-row frame with explicitly named columns — sorted by mean_close descending to rank the 10 highest-priced constituents.
Polars | Compute mean, std, min, max close and date range per symbol using expression list
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Passes six chained expressions to .agg() — each using .alias() to name output columns — returning the same 10-row summary as the Pandas named aggregation with no MultiIndex, sorted by mean_close descending.
groupby().transform() broadcasts a group-level statistic back to every row in the original DataFrame without collapsing it. Use this when you need both the raw value and its group context on the same row — e.g., “what is ASML’s close price vs its all-time group mean?“. The output is always the same length as the input. Pandas uses .transform("mean") for this; Polars uses .mean().over("group_col") (covered in detail in Part 2).
Same-Length Group Broadcast
Pandas | Broadcast symbol mean close back to each row alongside deviation percentage
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Filters to ASML.AS, adds group_avg via .groupby("symbol")["close"].transform("mean"), then computes vs_avg as the percentage deviation of each day’s close from the all-time mean — returning the last 10 rows at full DataFrame length without collapsing rows.
# Pandas: group mean alongside each rowasml_pd = ohlcv_pd[ohlcv_pd["symbol"] == "ASML.AS"].copy()asml_pd["group_avg"] = asml_pd.groupby("symbol")["close"].transform("mean")asml_pd["vs_avg"] = ((asml_pd["close"] - asml_pd["group_avg"]) / asml_pd["group_avg"] * 100).round(2)display(asml_pd[["symbol", "date", "close", "group_avg", "vs_avg"]].tail(10))
Rendered tabular output preserved below.
symbol
date
close
group_avg
vs_avg
11955
ASML.AS
2026-02-27
1233.4
671.348911
83.72
11956
ASML.AS
2026-03-02
1210.4
671.348911
80.29
11957
ASML.AS
2026-03-03
1161.8
671.348911
73.05
11958
ASML.AS
2026-03-04
1199.8
671.348911
78.71
11959
ASML.AS
2026-03-05
1186.0
671.348911
76.66
11960
ASML.AS
2026-03-06
1147.0
671.348911
70.85
11961
ASML.AS
2026-03-09
1147.6
671.348911
70.94
11962
ASML.AS
2026-03-10
1200.0
671.348911
78.74
11963
ASML.AS
2026-03-11
1198.8
671.348911
78.57
11964
ASML.AS
2026-03-12
1190.8
671.348911
77.37
Polars equivalent: .over() window expression
Polars replaces .transform() with .mean().over("symbol") inside .with_columns().
The result is identical — one group mean value broadcast across all rows — but Polars
computes it as a window expression without mutating the DataFrame. See the full
.over() examples in Part 2 — Window Functions below.
Sector Analysis with Scores
Group the scores dimension table by sector to compute portfolio-level metrics: count of constituents, average composite and momentum scores, and total index weight per sector. This is a typical index analytics query — the result is a small 10-row frame (one row per sector) regardless of how many stocks are in the dataset.
Portfolio-Level Sector Metrics
Pandas | Aggregate scores by sector to compute constituent count, average scores, and total weight
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Groups the 466-row scores table by sector, computing stock count, mean composite and momentum scores, and summed index weight per sector, returning a 10-row frame sorted by avg_composite descending.
Polars | Aggregate scores by sector to compute constituent count, average scores, and total weight
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas sector aggregation using .count(), .mean().round(4), and .sum().round(4) expressions in a single .agg() call, producing the same 10-row sector summary sorted by avg_composite descending.
To retrieve the top-N rows per group (e.g., top 3 stocks per sector by rank), pre-sort the DataFrame and then call .group_by().head(n). This is more efficient than filtering with rank().over() and avoids a second sort pass. Polars .group_by() is unordered by default, so the pre-sort is essential — it determines which rows each group “sees first”.
Pandas equivalent: sort + groupby + head
Pandas has no direct .group_by().head() method. The equivalent pattern is:
This works but returns rows in the original sorted order across all groups, not grouped.
Chain .sort_values(["sector", "composite_rank"]) after to match the Polars output layout.
Top-N Rows Per Group
Polars | Retrieve top 3 stocks per sector by composite rank using group_by().head()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Pre-sorts the scores table by composite_rank ascending, then calls .group_by("sector").head(3) to capture the 3 highest-ranked stocks per sector — producing a 30-row result across all 10 sectors, re-sorted by sector and rank.
# Top 3 stocks per sector by composite score (Polars)display( scores_pl .sort("composite_rank") .group_by("sector") .head(3) .select("sector", "symbol", "composite_score", "composite_rank") .sort("sector", "composite_rank"))
Rendered tabular output preserved below.
sector
symbol
composite_score
composite_rank
str
str
f64
i64
Basic Materials
4063.T
0.344008
7
Basic Materials
4063.T
0.24484
11
Basic Materials
4063.T
0.245728
12
Communication Services
DTE.DE
0.515005
2
Communication Services
DTE.DE
0.521442
2
Communication Services
DTE.DE
0.487049
3
Consumer Cyclical
VOW.DE
0.57561
2
Consumer Cyclical
VOW.DE
0.463323
3
Consumer Cyclical
7203.T
0.429936
3
Consumer Defensive
ABI.BR
0.42094
4
Consumer Defensive
ABI.BR
0.38521
5
Consumer Defensive
ABI.BR
0.406873
5
Energy
DVN
0.665507
1
Energy
VLO
0.49009
2
Energy
WDS.AX
0.490654
2
Financial Services
BNP.PA
0.679599
1
Financial Services
BNP.PA
0.663971
1
Financial Services
BNP.PA
0.683947
1
Healthcare
2269.HK
0.357499
5
Healthcare
4568.T
0.355843
6
Healthcare
4568.T
0.377455
6
Industrials
8001.T
0.478444
1
Industrials
8031.T
0.48932
2
Industrials
8001.T
0.46616
3
Technology
6981.T
0.494602
1
Technology
6981.T
0.546581
1
Technology
MU
0.925838
1
Utilities
ENEL.MI
0.039337
25
Utilities
ENEL.MI
0.018668
26
Utilities
ENEL.MI
0.002435
27
Grouping Crosswalk
Operation
Pandas
Polars
Group by
.groupby(col)
.group_by(col)
Aggregate
.agg(name=(col, func))
.agg(pl.col(col).func().alias(name))
Transform
.groupby().transform()
.over() window expression
Multiple aggs
dict of (col, func)
List of expressions
Reset index
as_index=False
Not needed (no index)
Filter groups
.filter(func)
.filter() after group_by
Window Functions
Window Transform
Window functions compute per-row values that depend on a partition (group) of the data — without collapsing rows. They are the DataFrame equivalent of SQL OVER(PARTITION BY col ORDER BY ...). Common uses: broadcasting a group mean or sum back to each row, computing within-group ranks, and rolling statistics scoped to a symbol’s own history.
Window functions like PARTITION BY and ROWS BETWEEN appear across SQL and DataFrame APIs. The SQL Server gold layer in gold-transforms applies the same ranking and running-total logic, and bq-advanced covers BigQuery window functions for identical analytical needs.
Broadcasting Group Statistics
Pandas | Window transform (groupby().transform())
.groupby().transform(func) applies func to each group and broadcasts the result back to the full-length DataFrame. It is the Pandas idiom for “add a group-level column without collapsing rows”. The function receives each group’s Series and must return a same-length Series. Common functions: "mean", "sum", "rank", or a custom lambda.
Filters to ASML.AS, broadcasts the all-time mean close back to each row as avg_close using .transform("mean"), then adds a rank column via .rank(ascending=False) — showing the last 10 rows with both raw close and its ordinal position in the full 1,331-row ASML history.
.expr.over("group_col") in Polars computes the expression within each partition defined by group_col and broadcasts the result back to each row — the exact equivalent of Pandas .transform(). Unlike .transform(), .over() can be chained with any expression (.mean(), .rank(), .cum_sum()) inside a single .with_columns() call without multiple passes. Multiple .over() expressions in one .with_columns() are computed in parallel.
Replicates the Pandas transform by computing .mean().over("symbol") and .rank(descending=True).over("symbol") in a single .with_columns() call — showing that multiple .over() expressions are evaluated in parallel, producing identical last-10-row values as the Pandas result.
A rolling window aggregation computes a statistic over the last N consecutive rows per row — the classic moving average. Pandas uses .rolling(n).mean() / .rolling(n).max() on a Series. Polars uses .rolling_mean(window_size=n) / .rolling_max(window_size=n) as expressions in .with_columns(). Both produce null / NaN for the first n-1 rows where the window is incomplete.
Positional rolling-window arguments no longer survive Polars 1.x
In Polars 0.x, rolling_mean(7) accepted the window size as a positional argument.
Polars 1.x requires the keyword argument:rolling_mean(window_size=7).
The positional form raises a DeprecationWarning in 0.x and a TypeError in 1.x.
Always use window_size= explicitly to future-proof your code.
Always pass the rolling window size by keyword
Write rolling_mean(window_size=7), rolling_max(window_size=30), and the
equivalent keyword form for every rolling expression. That keeps the code
explicit and avoids version-sensitive breakage.
Moving Average and Rolling Maximum
Pandas | Compute 7-day SMA and 30-day rolling max for ASML.AS using rolling()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Filters to ASML.AS sorted by date, then uses .assign() with .rolling(7).mean() and .rolling(30).max() to append sma_7 and rolling_max — displaying the last 10 rows where the 30-day high is uniformly 1,288.4.
Polars | Compute 7-day SMA and 30-day rolling max for ASML.AS using rolling_mean() and rolling_max()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas rolling windows using rolling_mean(window_size=7) and rolling_max(window_size=30) as expressions in a single .with_columns() call, producing the same last-10-row result with identical SMA and rolling high values.
Cumulative (running) aggregations compute a running total, maximum, or count from the first row to the current row. Use cum_sum on volume to track total shares traded to date; use cum_max on price to track the running all-time high. Both operations produce the same length as the input. Ensure the DataFrame is sorted by date before applying cumulative operations — both Pandas and Polars process rows in storage order.
Running Totals and All-Time Highs
Pandas | Compute cumulative volume and running close high for ASML.AS using cumsum() and cummax()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Filters to ASML.AS sorted by date, appending cum_vol (running total shares traded) and run_high (all-time closing high) via .assign() — showing the last 10 rows where cumulative volume approaches 945 million shares.
Polars | Compute cumulative volume and running close high for ASML.AS using cum_sum() and cum_max()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas cumulative operations using pl.col("volume").cum_sum() and pl.col("close").cum_max() in a single .with_columns(), confirming identical running totals and all-time highs in the last 10 rows.
Within-group ranking assigns each row a rank number based on its value within its group, without collapsing the DataFrame. Use this to find the top-N stocks per sector, or to flag outliers within a group. Polars uses .rank(descending=True).over("sector") inside .with_columns(). Pandas uses .groupby()["col"].rank(ascending=False), which also broadcasts the rank back to every row.
Within-Group Percentile Ranking
Pandas | Rank each stock within its sector by composite score using groupby().rank()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Assigns each stock a within-sector rank based on composite_score descending, broadcasting the rank back to every row using .groupby("sector")["composite_score"].rank(ascending=False) — showing the top 15 rows sorted by sector and rank.
Polars | Rank each stock within its sector by composite score using rank().over()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Computes the within-sector rank in a single .with_columns() call using .rank(descending=True).over("sector") — returning the same 15-row result sorted by sector and rank, with all sectors’ rankings computed in parallel.
Shift (lag/lead) accesses the value from a previous (shift(1)) or future (shift(-1)) row. Use lag to compute daily returns (close / prev_close - 1) or to detect price-direction changes. The first row of a lag series and the last row of a lead series are NaN (Pandas) or null (Polars). Both use .shift(n) with the same sign convention: positive = look back, negative = look forward.
Shift for Lag and Lead Values
Pandas | Compute previous-day and next-day close for ASML.AS using shift()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Filters to ASML.AS sorted by date, then uses .assign() to add prev_close (shift(1)) and next_close (shift(-1)) — showing the last 10 rows where the final row’s next_close is NaN because no future row exists.
Polars | Compute previous-day and next-day close for ASML.AS using shift()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas shift using pl.col("close").shift(1) and pl.col("close").shift(-1) in a single .with_columns() call — producing the same last-10-row result with null (instead of NaN) for the final lead value.
Combining DataFrames covers joins (key-based row matching), concatenation (stacking frames), and set-based filters (anti/semi). Both Pandas and Polars use SQL-style join semantics: inner, left, right, full outer, anti, semi, cross. The key API difference is that Pandas uses .merge() / pd.merge() while Polars uses .join().
Additional Dataset Loading
Python | Load USA OHLCV, signals, trading calendar, and index performance datasets
Load the additional parquet inputs once so the join and concat examples can reuse stable usa_*, signals_*, cal_*, and perf_* frames without redefining paths in every subsection.
Populate the secondary pandas and polars DataFrames used in the combination examples, including the USA OHLCV set, daily signals, trading calendar, and performance tables.
If both DataFrames have duplicate keys and you don’t set validate=, merge() produces
a Cartesian product for those keys — your 66K row DataFrame can become millions of rows
with no error or warning. Always add validate='many_to_one' or validate='one_to_one'
to catch unexpected duplicates.
# Safe merge — raises MergeError if key relationship is violatedresult = ohlcv_pd.merge(dim_pd, on="symbol", validate="many_to_one")
Use validate= on every merge to detect unexpected duplicates
Pass validate='many_to_one' or validate='one_to_one' to pd.merge() / .merge().
A MergeError is raised immediately if the key cardinality violates the constraint,
preventing silent row explosions. In Polars, use .join_where() or assert
result.shape[0] == left.shape[0] after the join when the relationship should be many-to-one.
Join safety is only one half of the problem. The other is default join behavior and
post-join ergonomics, which still deserve an explicit check even when cardinality is valid.
Default join behavior still hides dropped rows and suffix surprises
Pandas merge() defaults to how='inner' — rows without matches are silently dropped
Polars join() defaults to how='inner' too, but uses different suffix behavior:
Pandas appends _x/_y, Polars appends _right
Declare the join shape and audit the result immediately
Always pass how='inner', how='left', etc. explicitly — never rely on defaults.
After any join, assert len(result) == expected to catch silent row drops or explosions.
In Polars, use suffix="_right" awareness or rename() the conflicting column before
joining to avoid ambiguous column names.
Key-Based Row Matching
Pandas | Inner join OHLCV to dimension table on symbol, adding short_name and sector columns
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Merges the 66,355-row OHLCV frame with the 169-row dimension table on symbol using how="inner", enriching every price row with short_name and sector — confirming all 66,355 rows are retained because every OHLCV symbol has a dimension entry.
Polars | Inner join OHLCV to dimension table on symbol, adding short_name and sector columns
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas inner join using .join(dim_pl.select(...), on="symbol", how="inner"), confirming 66,355 rows retained — Polars uses _right instead of _x/_y for duplicate column name collisions.
Duplicate keys on the right side multiply left-join rows
Left join with duplicate keys silently multiplies rows
This left join produces more rows than the left DataFrame because scores_pd has
multiple rows per symbol (one per date). The output has len(ohlcv) × scores_per_symbol
rows — a classic accidental many-to-many. Always check len(result) after a join.
Collapse the right side to one row per key before joining
Before a left join, deduplicate the right DataFrame to one row per key:
scores_pd.drop_duplicates("symbol"). Or keep only the latest score with
.sort_values("date").groupby("symbol").last().reset_index(). Then assert
len(result) == len(ohlcv_pd) to confirm no row multiplication occurred.
Preserving All Left-Side Rows
Pandas | Left join OHLCV to scores on symbol, attaching composite score to each price row
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Joins the 66,355-row OHLCV frame to the scores table on symbol using how="left", demonstrating the many-to-many row explosion where ABI.BR appears three times (one per scoring date) — showing the first 5 rows to expose the duplication.
Polars | Left join OHLCV to scores on symbol, attaching composite score to each price row
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas left join using .join(scores_pl.select(...), on="symbol", how="left"), producing the same many-to-many expansion — confirming identical composite score values as the Pandas result in the first 5 rows.
An anti join returns rows from the left DataFrame that have no match in the right DataFrame. Use it to find coverage gaps: symbols in OHLCV that are not yet in the scores table, or securities missing from a dimension file. It is more readable than a .merge() followed by df[df["col"].isna()].
No native Pandas anti join
Pandas has no how='anti' parameter. The equivalent is a left join with an indicator column:
result = ohlcv_pd.merge(scores_pd[["symbol"]].drop_duplicates(), on="symbol", how="left", indicator=True)anti = result[result["_merge"] == "left_only"].drop(columns="_merge")
Or use ~df["symbol"].isin(other["symbol"]) for simple key-exclusion filters.
Identifying Unmatched Rows
Polars | Find symbols in OHLCV that have no match in the scores table using anti join
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Extracts unique symbols from both OHLCV and scores, then applies how="anti" to return only OHLCV symbols absent from scores — confirming 0 unmatched symbols, meaning full OHLCV coverage in the scores table.
# Polars: symbols in OHLCV not in scoresresult = ohlcv_pl.select("symbol").unique().join(scores_pl.select("symbol").unique(), on="symbol", how="anti")print(f"Symbols without scores: {result.height}")display(result)
Symbols without scores: 0
symbol
str
Semi Join
A semi join returns rows from the left DataFrame that have at least one match in the right DataFrame — but it does not add any columns from the right side. Use it to filter a large fact table (OHLCV) down to only the symbols that exist in a dimension or scoring table, without risk of row duplication.
No native Pandas semi join
Pandas has no how='semi' parameter. The equivalent filter is:
This is efficient for simple key membership tests but does not generalize to multi-column join keys.
Filtering by Key Membership
Polars | Filter OHLCV to keep only symbols present in the scores table using semi join
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Joins the 66,355-row OHLCV to the unique scores symbols using how="semi", returning all 66,355 rows — confirming every OHLCV symbol has a corresponding scores entry without adding any columns from the right side.
result = ohlcv_pl.join(scores_pl.select("symbol").unique(), on="symbol", how="semi")print(f"OHLCV rows with scores: {result.height} (of {ohlcv_pl.height})")
OHLCV rows with scores: 66355 (of 66355)
Cross Join
A cross join produces the Cartesian product of two DataFrames: every row in the left is paired with every row in the right. Result row count = len(left) × len(right). Use this to generate all (symbol, date) combinations for a universe/calendar scaffold, then left-join actual prices onto it to expose gaps.
Cross joins scale quadratically
Joining two tables of 50 and 1331 rows produces 66,550 rows. Joining OHLCV (66K rows)
with itself produces 4.4 billion rows. Always apply .select() to the smallest possible
subset before a cross join.
Bound the Cartesian product before you execute it
Filter one side first, keep only the columns you actually need, and compute the
expected left_rows * right_rows output size before running how="cross".
If the multiplication is not acceptable on paper, it is not safe in code.
Cartesian Product Generation
Polars | Generate all combinations of two symbols and two dates using cross join
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Joins a 2-row symbols DataFrame to a 2-row dates DataFrame using how="cross", producing all 4 (symbol, date) combinations — demonstrating the Cartesian product that scales to millions of rows on larger inputs.
Vertical concatenation stacks DataFrames on top of each other (adds rows). Both DataFrames must have compatible schemas — same column names and compatible types. Use this to combine data from multiple time periods, markets, or API pages into a single frame. Polars uses pl.concat([df1, df2]); Pandas uses pd.concat([df1, df2], ignore_index=True). Always pass ignore_index=True in Pandas to reset the row index after concat — without it, duplicate index values are preserved, which breaks many downstream operations.
Stacking Frames Row-Wise
Pandas | Stack Euro Stoxx 50 and US 50 OHLCV head rows vertically using pd.concat()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Concatenates the first 3 rows of ohlcv_pd (ABI.BR) and usa_pd (AAPL) using pd.concat([...], ignore_index=True), producing a 6-row frame with a clean integer index reset — one frame stacked on top of the other.
Polars | Stack Euro Stoxx 50 and US 50 OHLCV head rows vertically using pl.concat()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas vertical concat using pl.concat([ohlcv_pl.head(3), usa_pl.head(3)]), returning the same 6-row result — no ignore_index parameter needed since Polars has no row index concept.
Horizontal concatenation adds columns side by side. Both DataFrames must have the same number of rows and no overlapping column names. Use this to attach a computed Series or a separate feature frame to an existing DataFrame. Polars uses pl.concat([left, right], how="horizontal").
In Pandas, horizontal concat is pd.concat([df1, df2], axis=1). Unlike Polars, Pandas
aligns on the index, so mismatched indexes produce NaN fill rather than an error.
Use .reset_index(drop=True) on both frames before concat to avoid unintended alignment.
Attaching Columns Side by Side
Polars | Horizontally concatenate a symbol-price frame with a sector frame using pl.concat()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Combines a 2-row symbol/price frame with a 2-row sector frame using how="horizontal", producing a 2-row, 3-column result — both frames must have matching row counts for horizontal concat to succeed.
Diagonal concat stacks DataFrames vertically even when their schemas differ. Columns present in one frame but absent in another are filled with null. Use this when combining data from heterogeneous sources — e.g., merging two API responses with slightly different field sets — without needing to align schemas manually first.
No Pandas equivalent
Pandas pd.concat() raises a column mismatch error when schemas differ unless
join='outer' is specified, which fills missing columns with NaN — functionally
similar but uses NaN (float) rather than native null, causing type coercion.
Schema-Tolerant Vertical Stack
Polars | Diagonally concatenate two frames with different schemas, filling missing columns with null
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Stacks a {symbol, close} frame and a {symbol, volume} frame using how="diagonal", producing a 2-row, 3-column result where close is null for row B and volume is null for row A.
Reshaping transforms the structure of a DataFrame without changing the underlying data. The two fundamental operations are wide to long (melt/unpivot — spread column names into rows) and long to wide (pivot — collapse row values into columns). Additional operations include explode (list column to rows), implode (rows to list), transpose, and one-hot encoding.
Wide to Long: melt / unpivot
Melt (Pandas) / unpivot (Polars) converts a wide DataFrame — where multiple columns represent the same measurement at different points — into a long format where column names become values in a variable column and their values go into a value column. This is required before plotting multi-series charts, applying long-format aggregations, or loading into a normalized database table.
Spreading Column Names into Rows
Pandas | Melt ASML.AS OHLC columns into long format with price_type and price columns
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Takes the last 3 rows of ASML.AS with open, high, low, close columns and melts them into a 12-row long frame where price_type holds the column name and price holds the value — keeping date as the identity variable.
Polars | Unpivot ASML.AS OHLC columns into long format with price_type and price columns
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Replicates the Pandas melt using .unpivot(index="date", variable_name="price_type", value_name="price"), producing the same 12-row result with Float64 typed price values and a Polars-native API name.
Pivot converts a long-format DataFrame into wide format by spreading the unique values of an on column into new columns, filling each cell with a corresponding values column. Use pivot to create a symbol-by-year close-price matrix from daily time series data, or to produce a sector-by-metric scorecard. Polars .pivot(on=, index=, values=) performs this eagerly; Pandas uses pivot_table() which also supports aggregation functions for duplicate index combinations.
Spreading Row Values into Columns
Polars | Pivot annual average close prices into a symbol × year matrix using pivot()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Groups ASML.AS, MC.PA, and SAP.DE by symbol and year to compute annual average close, then pivots year values into columns — producing a 3-row, 7-column matrix of average prices from 2021 to 2026.
Explode expands a list-typed column so that each element in the list becomes its own row, repeating the non-list columns. Use this after a group_by().agg(pl.col("x").implode()) to restore a collected list back to rows, or when ingesting JSON/Parquet data where one field contains a list of tags or events. Both Pandas and Polars support .explode("col").
Expanding List Columns to Rows
Polars | Explode a tags list column so each tag becomes its own row using explode()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Takes a 2-row DataFrame where each symbol has a list of 2 tags, then explodes the tags column into 4 rows — repeating the symbol value for each tag element.
Implode (Polars only) is the inverse of explode: it collects all values in a group into a single list[T] column. Use it to create a “bag of symbols per sector” column, or to bundle related values before serializing to JSON. Pandas has no direct equivalent — the closest is .groupby("sector")["symbol"].apply(list).
Collecting Rows into Lists
Polars | Group symbols by sector into a list column per sector using group_by() and implode()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Groups the scores table by sector and collects all symbol values into a list[str] column per sector using .implode(), showing the first 5 sectors sorted alphabetically — each as a bag of symbol strings.
Transpose swaps rows and columns — the row index becomes column names and vice versa. Use it to convert a small “symbol × metric” frame into a “metric × symbol” view, e.g., for display in a dashboard table. Polars .transpose(include_header=True, column_names=col) names the output columns from a string column in the input. Pandas uses .T (the transposed property). Both require a homogeneous schema (all numeric, or all string) for the transposed columns.
Swapping Rows and Columns
Polars | Transpose a 2-symbol × 2-metric frame into a 2-metric × 2-symbol view using transpose()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Selects the most recent composite and momentum scores for ASML.AS and MC.PA, then transposes the 2-row, 3-column frame so metric names become rows and symbol values become columns — using column_names="symbol" to name the output columns.
# Need unique rows per symbol for transpose (scores has multiple dates)small = ( scores_pl .sort("score_date", descending=True) .unique(subset=["symbol"], keep="first") .filter(pl.col("symbol").is_in(["ASML.AS", "MC.PA"])) .select("symbol", "composite_score", "momentum_score"))display(small)display(small.transpose(include_header=True, column_names="symbol"))
Rendered tabular output preserved below.
symbol
composite_score
momentum_score
str
f64
f64
ASML.AS
0.176104
1.470134
MC.PA
-0.203769
-0.70792
column
ASML.AS
MC.PA
str
f64
f64
composite_score
0.176104
-0.203769
momentum_score
1.470134
-0.70792
One-Hot Encoding
One-hot encoding converts a categorical column into N binary columns (one per unique value), where each cell is 1 if the row belongs to that category and 0 otherwise. Required for ML feature engineering — most scikit-learn estimators require numeric input. Polars uses .to_dummies() on the DataFrame; Pandas uses pd.get_dummies(df, columns=["sector"]).
Encoding Categoricals as Binary Columns
Polars | One-hot encode a sector column into binary dummy columns using to_dummies()
This example shows the stated DataFrame operation in runnable form so you can inspect the transformation before reading the preserved output.
Converts a 4-row sector column with values Tech, Luxury, and Energy into 3 binary columns (sector_Energy, sector_Luxury, sector_Tech) using to_dummies() — each column uses u8 dtype for memory efficiency.
If duplicated keys appear on both sides, merge() or join() can produce more rows than either input. Treat validate= or explicit key-uniqueness checks as a precondition whenever row count matters.
Estimate the output size of a duplicated-key join before running it.
A how="cross" operation grows as left_rows * right_rows, so even moderate inputs become expensive quickly. Filter one side first or replace the join with a smaller driver table.
Compute the expected row count before issuing a cross join.
Polars group_by() does not guarantee insertion order, so a grouped result should be followed by .sort(...) whenever you present or compare the output. The grouped values are correct, but the row order is not part of the contract.
Compare an unsorted label sequence with the explicit sorted(...) version you want to present.
as_index=False should not be your only portability plan
as_index=False is convenient, but version-sensitive pipelines are safer if they can also tolerate an explicit .reset_index() step. The real contract is a flat output schema, not a single spelling.
Show the two flat-schema strategies that keep grouped keys accessible as columns.
strategies = ["groupby(..., as_index=False).agg(...)", "groupby(...).agg(...).reset_index()"]for item in strategies: print(item)
rolling() and rolling_mean() operate in storage order, so unsorted timestamps make the statistic meaningless even when the syntax succeeds. Always sort by the logical key, usually date, before applying the window.
Compare the storage order with the correctly sorted order for a time-based window.
A strict pivot() requires each (index, column) pair to be unique. If duplicates exist, aggregate first with pivot_table() or a grouped pre-step so the reshape has one value per cell.
Check whether the (index, column) coordinate set is unique before pivoting.
get_dummies() and to_dummies() create one output column per distinct category, so a high-cardinality feature can explode the width of the table. Bucket rare values before encoding if the category space is unbounded.
Treat the number of dummy columns as a direct function of category count.
Use validate="one_to_one" or validate="many_to_one" when the join semantics are known up front. Failing fast is cheaper than auditing a silently multiplied result later.
Keep the accepted join shapes explicit instead of relying on defaults.
for rule in ["one_to_one", "many_to_one"]: print(rule)
one_to_onemany_to_one
Check row counts after every structural operation
An immediate result.shape[0] comparison catches bad joins, bad explodes, and bad pivots before the mistake leaks into later calculations. Structural assertions are low-cost and high-signal.
Compare the expected and actual row count after a shape-changing step.
When a cross join is the right tool, reduce one side first so the Cartesian product is intentional and bounded. A small driver table makes the cost legible.
Cross a filtered 50-row driver with 20 scenarios instead of the full fact table.
Treat .sort(...) as part of the display contract for grouped Polars output. That keeps comparisons and downstream joins stable even though the aggregate values were already correct.
Append the presentation sort immediately after the grouped aggregate.
Named outputs like avg_close or total_volume are easier to merge and reason about than MultiIndex column headers. The alias is part of the schema design, not just a cosmetic cleanup.
Name grouped outputs at aggregation time instead of renaming later.
aliases = ["avg_close", "total_volume", "trading_days"]for alias in aliases: print(alias)
avg_closetotal_volumetrading_days
Prefer .over() for Polars same-length metrics
When the goal is a same-length grouped result, .over(...) keeps the logic in one expression tree and reads more clearly than detouring through a separate grouped object. Use it for ranks, grouped means, and grouped normalizations.
Express a same-length grouped metric with a single .over(...) expression.
For new Polars code, unpivot() names the operation directly and reads cleanly beside pivot(). Use it when wide measurement columns need to become (variable, value) rows.
Normalize multiple measurement columns into a variable and value pair.
If a left-side preservation join returns extra rows, duplicated keys on the right side are the first place to look. Check n_unique() or deduplicate before repeating the join.
Compare total right-side keys with unique right-side keys to spot fanout risk.
Missing groups often come from null keys rather than a broken aggregation. Count nulls in the grouping column first, then decide whether to fill them or analyze them separately.
Measure how many grouping keys are null before aggregating.
keys = ["Tech", None, "Energy", None]print(sum(key is None for key in keys))
2
Anti joins return no rows
An empty how="anti" join means every left key matched, or that the key comparison is not testing what you think it is because of dtype or whitespace issues. Confirm both the values and the normalized representation.
Trim and compare candidate keys before assuming the anti join is wrong.
A duplicate-entry ValueError means at least one (index, column) coordinate maps to more than one value. Aggregate first so each cell has one scalar.
Use coordinate cardinality to confirm whether a strict pivot can succeed.
If the window is larger than the available rows, or the data is not sorted, the early or entire result can be null-heavy. Verify both the window size and the ordering assumption.
Compare the available row count with the requested rolling window size.
Unexpected nulls after concat() usually mean the input schemas were not aligned. Compare column sets and dtypes before stacking, or use Polars how="diagonal" deliberately.
Check whether two frames expose the same column names before concatenating them vertically.
explode() fails because the column is not list-typed
explode() expects list-like values; a scalar column will fail or behave unexpectedly. Inspect the schema first and cast or restructure the data before exploding.
Test whether the target values behave like lists before calling explode().
value = "tech"print(isinstance(value, list))
False
One-hot encoding produces too many columns
If get_dummies() or to_dummies() generates an unmanageable width, the source category has too many unique values. Bucket rare values or encode only the top categories you intend to model.
Treat unique-category count as the upper bound on dummy-column count.