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?
answer
- moving values across the label border
- opposite drop defaults
- a fresh RangeIndex comes back
- a list of keys builds levels
basics
~20 sset_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 linesimport 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
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.
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.
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.
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