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?
answer
- operators always take the union
- a pair of conformed objects
- both axes unless told otherwise
- inner keeps the left order
basics
~20 sThe / 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 sArithmetic 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 linesimport 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
Recall that frame arithmetic aligns rows and columns by label and fills unmatched cells with NaN.
Explain the four join options of align, its tuple result, and the axis argument, including axis=None aligning both rows and columns.
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.
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.