skip to content

When dividing one store's monthly revenue DataFrame by another's in pandas, why call DataFrame.align with join='inner' first, and what does it return?

level: seniorimportance: should knowfreq 35%

answer

  1. operators always take the union
  2. a pair of conformed objects
  3. both axes unless told otherwise
  4. inner keeps the left order

basics

~20 s

The / operator aligns on the union of rows and columns, filling unmatched cells with NaN. a.align(b, join="inner") returns both frames cut to their shared labels on both axes, so the ratio covers only comparable cells.

solid answer

~40 s

Arithmetic operators in pandas always align with an outer join: `north / south` is labelled by the union of both indexes and both column sets, and every cell missing on one side is `NaN`, so the result is wider and longer than either input. `north.align(south, join="inner")` makes the choice explicit and returns a tuple `(left, right)`, both conformed to the intersection: on both axes by default (`axis=None`), or on one with `axis=0` or `axis=1`. `inner` keeps the left object's label order; `outer`, the default, sorts the union; `left` and `right` keep one side's labels. Because no label is added, an int column stays int. `fill_value` sets what unmatched cells get under an outer, left or right join. In pandas 3.0 `align` no longer accepts `method`, `limit`, `fill_axis` or `broadcast_axis`; fill afterwards with `ffill()`.

code

python · 17 lines
python
import pandas as pd

north = pd.DataFrame(
    {"revenue": [120, 135, 150], "orders": [30, 31, 33]},
    index=["2026-01", "2026-02", "2026-03"],
)
south = pd.DataFrame(
    {"revenue": [90, 110, 95], "returns": [2, 3, 1]},
    index=["2026-02", "2026-03", "2026-04"],
)

raw = north / south
print(raw.shape)                         # (4, 3): union of rows and of columns

n, s = north.align(south, join="inner")
print(n.index.tolist(), n.columns.tolist())   # ['2026-02', '2026-03'] ['revenue']
print((n / s)["revenue"].round(2).tolist())   # [1.5, 1.36]

go deeper

for a junior

Recall that frame arithmetic aligns rows and columns by label and fills unmatched cells with NaN.

for a middle

Explain the four join options of align, its tuple result, and the axis argument, including axis=None aligning both rows and columns.

for a senior

Show the production habit: align explicitly before comparing frames, record which labels the join dropped, and use axis=0 on flex methods when a Series is labelled by rows.

for a principal

Weigh where a team's comparisons choose their join: implicit outer joins inside operators against an explicit align step that reviewers can see and question.

## What the operators do on their own When two pandas **DataFrames** meet in an operator such as `/`, pandas aligns them on **both axes**: the row Index and the columns. The alignment is always an **outer join**, so the result carries the **union** of the row labels and the union of the column labels, and every cell that exists on only one side becomes `NaN`. Dividing a north-store frame with `revenue` and `orders` columns by a south-store frame with `revenue` and `returns` columns gives a frame with three columns, two of them entirely `NaN`, and a row for every month either store reported. The operator gives you no way to ask for the intersection instead. ## What align returns `DataFrame.align` and `Series.align` take that alignment step out of the operator and let you choose the join: - **The result is a tuple** `(left, right)`: the calling object and `other`, each conformed to the same labels. Unpack it: `n, s = north.align(south, join="inner")`. - **`join`** is `"outer"` by default; the other choices are `"inner"`, `"left"` and `"right"`. - **`axis`** is `None` by default, meaning both rows and columns; `axis=0` aligns only the rows and `axis=1` only the columns. - **`level`** aligns on one level of a `MultiIndex`. - **`fill_value`** sets what the cells introduced by an outer, left or right join contain; the default is `NaN`. | `join` | Labels kept | Order | |---|---|---| | `"outer"` | union of both | sorted when the labels can be compared | | `"inner"` | intersection | the left object's order | | `"left"` | the left object's labels | the left object's order | | `"right"` | the other object's labels | the other object's order | With `join="inner"` no label is added, so no `NaN` is introduced and integer columns keep their dtype. ## A frame against a Series: the axis trap Alignment also decides what happens between a DataFrame and a Series. By default pandas matches the **Series index against the frame's columns** and broadcasts row-wise. `north - north["revenue"]` therefore compares month labels with the column names `revenue` and `orders`, finds no match, and returns a wide frame of `NaN`. Use the flex method and say which axis the Series is labelled along: `north.sub(north["revenue"], axis=0)` matches the Series index against the frame's row index. ## Using align in a comparison 1. Align explicitly: `n, s = north.align(south, join="inner")`. 2. Record what was dropped: `north.index.difference(n.index)` and `north.columns.difference(n.columns)`, and the same for `south`. A report that silently loses a month is worse than one that says so. 3. Compute on the aligned pair: `ratio = n / s`. The operator still aligns, but the labels already match, so nothing new appears. 4. When one side is the reference, such as a budget that must keep every planned month, prefer `join="left"` and let the other side show `NaN` for months it has not reported. ## Series.align and aligning a level `Series.align` works the same way on a pair of Series and returns two Series. When a DataFrame is aligned with a Series, `axis` must be `0` or `1`, saying whether the Series is labelled along the frame's rows or its columns; leaving it as `None` raises `ValueError: Must specify axis=0 or 1`. On data with a `MultiIndex`, `level=` aligns on one named level only, so a frame indexed by `(store, month)` can be lined up, with `axis=0`, against a Series indexed by `month` alone. Each case keeps the same contract: two objects come back, conformed to the labels the join chose, and the caller decides what to compute next. ## What changed in pandas 3.0 - **Removed keywords.** `method`, `limit`, `fill_axis` and `broadcast_axis` are gone from `DataFrame.align`. Code that passed `method="ffill"` now fails; call `align` and then `ffill()` on the returned object. - **The `copy` keyword is deprecated.** Under Copy-on-Write, which pandas 3.0 makes the only mode, `align` always returns new objects that copy lazily; passing `copy` has no effect. - **Unchanged.** The join choices, the outer default, the tuple result and the axis behaviour are the same as in 2.x.

  • Why does north - north["revenue"] return a wide frame of NaN in pandas?
    A DataFrame-Series operator matches the Series index against the frame's columns. The Series is labelled by month and the columns are `revenue` and `orders`, so nothing matches; the result's columns are the union of both label sets and every cell is `NaN`. Subtract row-wise with `north.sub(north["revenue"], axis=0)`.
  • When would you align with join='left' rather than join='inner'?
    When one frame is the reference: a budget frame that must keep every planned month, compared with actuals that lag. `budget.align(actuals, join="left")` keeps the budget's labels in their order and gives the actuals `NaN` for months not yet reported, so the gap shows instead of the month vanishing.

  • pandas divides two frames cell by cell by position when their shapes match
  • align changes both frames in place and returns None
  • join='inner' is align's default join
  • align only adjusts the row index and leaves the columns alone
  • df - series subtracts the Series from every column, row by row

Assumes pandas 3.0, which removed align's method, limit, fill_axis and broadcast_axis keywords and deprecated its copy keyword; pandas 2.x still accepted them.