skip to content

Axis Labels and Alignment

Every Series and DataFrame carries an Index, and arithmetic between two objects aligns on it first. Interviewers ask why a sum came back full of NaN and how reindex fixes it.

on this pageshow

questions

5

In pandas, what do DataFrame.set_index and reset_index do to a product table's row labels, and what does drop change in each?

level: juniorimportance: must knowfreq 60%

answer

  1. moving values across the label border
  2. opposite drop defaults
  3. a fresh RangeIndex comes back
  4. a list of keys builds levels

basics

~20 s

set_index moves one or more columns into the row labels and, by default, removes them from the columns; reset_index moves the labels back into columns and installs a fresh RangeIndex, or discards them when drop=True.

solid answer

~40 s

`df.set_index("sku")` turns the `sku` column into the frame's Index, so lookups and arithmetic line up on SKU. `drop=True` is its default, so the column leaves the data, and a list of keys such as `["store", "month"]` builds a `MultiIndex`. `reset_index()` is the inverse: it inserts the labels back as the first column(s), named after the Index (or `index` when it is unnamed and no such column exists), and gives the frame a default `RangeIndex` 0..n-1. Its `drop` defaults to `False`; `reset_index(drop=True)` throws the old labels away instead, which is right after filtering or stacking pieces whose labels mean nothing. Both return a new object. One trap: after `set_index("sku", drop=False)` a plain `reset_index()` raises `ValueError: cannot insert sku, already exists`.

code

python · 15 lines
python
import pandas as pd

products = pd.DataFrame({"sku": ["A-100", "A-200", "B-300"], "stock": [5, 0, 12]})

by_sku = products.set_index("sku")            # drop=True: sku leaves the columns
print(by_sku.columns.tolist())                # ['stock']
print(by_sku.reset_index().columns.tolist())  # ['sku', 'stock']

kept = products.set_index("sku", drop=False)  # sku is a column and the Index
try:
    kept.reset_index()
except ValueError as err:
    print(err)                                # cannot insert sku, already exists

print(kept.reset_index(drop=True).columns.tolist())  # ['sku', 'stock']

go deeper

for a junior

Recall the opposite defaults: set_index drops the column, reset_index keeps the labels as columns. Know that a list of keys gives a MultiIndex and that both methods return a new frame.

for a middle

Explain how reset_index names what it inserts (the index name, then index or level_0, level_n for unnamed levels) and why a column kept with drop=False makes it raise.

for a senior

Show judgment about when labels carry meaning: drop them after a filter or a stack of extracts, never after a step whose Index holds group keys or dates you still need.

for a principal

Frame the convention: whether a team keeps identifiers as columns and sets the Index only where alignment is wanted, which avoids reset churn and duplicate-name errors in shared code.

## What the Index is in a pandas DataFrame Every pandas `DataFrame` and `Series` carries an **Index**: an immutable object holding one label per row. When you build a frame without naming an index, pandas gives it a **`RangeIndex`**, a memory-saving special case that describes the labels 0, 1, 2 ... n-1 by a start, stop and step instead of an array of every number. The labels are what pandas uses to line rows up in arithmetic, in `reindex` and in `align`, so deciding *which* values sit in the Index is a real modelling choice. Two methods move values across that border: **`set_index`** and **`reset_index`**. ## set_index: columns become row labels `df.set_index(keys)` accepts one column label, an array of the right length, or a list mixing both. - **`drop=True`** is the default: the chosen column leaves the data and lives only in the Index. - **`drop=False`** keeps the column as data *and* uses its values as the Index; convenient, but it sets up the trap described below. - **`append=False`** is the default: the old Index is thrown away. `append=True` keeps it and adds the new key as an extra level. - **A list of keys** such as `["store", "month"]` builds a **`MultiIndex`**, one level per key, in the order given. - The method returns a **new frame**. The original is unchanged unless you assign the result. By default pandas does not check that the new labels are unique when you call `set_index`; a SKU column with repeats produces an Index with repeats, and the first method that needs unique labels is the one that complains. ## reset_index: row labels become columns again `df.reset_index()` reverses the move: - The Index values are **inserted as the first column(s)** of the result, ahead of the existing data. - The new column takes the **Index's name**. An unnamed Index becomes a column called `index` (or `level_0` when a column called `index` already exists), and unnamed `MultiIndex` levels become `level_0`, `level_1` and so on. - The frame receives a fresh default **`RangeIndex`**. - **`level=`** resets only some levels of a `MultiIndex` and leaves the others as the Index. - **`names=`** renames the inserted column(s) on the way out. - On a **Series**, `reset_index()` returns a **DataFrame** (the labels plus the values), while `reset_index(drop=True)` returns a Series with a `RangeIndex`. ## What drop means on each side | Call | Default `drop` | With `drop=True` | With `drop=False` | |---|---|---|---| | `set_index("sku")` | `True` | `sku` leaves the columns | `sku` stays as a column too | | `reset_index()` | `False` | the old labels are discarded | the old labels become columns | The opposite defaults are the thing to remember: `set_index` drops the **column**, `reset_index` keeps the **labels**. Both describe the safe choice for their own direction: a key is rarely wanted twice, and labels are rarely meant to vanish. ## Traps worth naming in an interview 1. **Resetting after `drop=False`.** If `sku` is both a column and the Index name, `reset_index()` tries to insert a second `sku` column and raises `ValueError: cannot insert sku, already exists`. Use `reset_index(drop=True)`, or pass `names=` to insert the labels under another name. 2. **Not keeping the result.** Both methods return a new object, so `df.set_index("sku")` on a line of its own leaves `df` as it was. 3. **Resetting meaningful labels away.** `reset_index(drop=True)` after a filter or a stack of extracts is fine; on a result whose Index holds group keys or dates it throws that information away. 4. **Editing a label in place.** An Index does not support item assignment: `df.index[0] = "X"` raises `TypeError`. Relabel with `rename` or assign a whole new Index. ## A round trip on a product table ```python import pandas as pd products = pd.DataFrame({"sku": ["A-100", "A-200", "B-300"], "stock": [5, 0, 12]}) by_sku = products.set_index("sku") # sku is now the Index print(by_sku.index.name) # sku print(by_sku.columns.tolist()) # ['stock'] back = by_sku.reset_index() # sku is a column again print(back.columns.tolist()) # ['sku', 'stock'] print(back.index.tolist()) # [0, 1, 2] ``` In short, `set_index` decides what identifies a row for alignment, and `reset_index` hands that identity back to the data or discards it. Knowing both defaults, and that a kept column blocks the reset, covers most of what an interviewer asks at this level.

  • How do you build a MultiIndex with set_index, and how do you undo only one of its levels?
    Pass a list: `df.set_index(["store", "month"])` builds a two-level `MultiIndex` from the two columns. `reset_index(level="month")` moves only that level back into a column and leaves `store` as the Index; `reset_index()` with no level moves both and installs a `RangeIndex`.
  • Why does reset_index on a pandas Series return a DataFrame?
    With the default `drop=False` the labels must go somewhere, so they become a column beside the values and the result has two columns, which is a DataFrame; the values column takes the Series name, or the `name=` you pass. With `drop=True` the labels are discarded and you get a Series back with a `RangeIndex`.

  • set_index keeps the column by default, so the key appears twice
  • reset_index(drop=True) turns the index into a regular column
  • set_index changes the frame even when the result is not assigned
  • A single label can be fixed by assigning df.index[0] = 'X'
  • A list passed to set_index applies the keys one after another, keeping the last
open as a page

In pandas, why does adding two stores' monthly revenue Series with s1 + s2 return NaN for some months, and what does add(fill_value=0) change?

level: middleimportance: must knowfreq 70%

basics

~20 s

pandas pairs values by index label and returns the union of both indexes, so a month only one Series has gets NaN. add(fill_value=0) treats the missing side as 0, except where both sides are missing.

open as a page

In pandas, after pd.concat stacks two monthly product extracts, how do you detect duplicate index labels, and how do you stop them arising?

level: middleimportance: should knowfreq 40%

basics

~10 s

Check df.index.is_unique and list repeats with df.index.duplicated(keep=False). Prevent them with ignore_index=True on the concat, or guard a frame with set_flags(allows_duplicate_labels=False), which raises DuplicateLabelError when a repeat appears.

open as a page

In pandas, what does DataFrame.reindex do when a product table indexed by SKU is conformed to a full catalogue list of SKUs?

level: middleimportance: should knowfreq 50%

basics

~20 s

reindex returns a frame with exactly the listed labels, in list order: existing SKUs keep their rows, new SKUs get NaN (or fill_value), and SKUs absent from the list are dropped. It matches labels; it never renames them.

open as a page

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%

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.

open as a page