In pandas, what does `DataFrame.explode` do to the index, to empty lists and to the dtype when a list-valued sensors column is exploded?
answer
- one row per list element
- the other columns are copied
- row labels repeat
- empty list, NaN row
basics
~20 sDataFrame.explode gives each list element its own row, copying the other columns and repeating the original index label. An empty list becomes one row with NaN, and the exploded column keeps object dtype for numbers.
solid answer
~40 s`df.explode("sensors")` turns each list-like in `sensors` into one row per element, copying the station's other columns onto every new row. The original index label is **repeated**, so `[0, 1, 2]` can become `[0, 0, 1, 2]`, and a later `df.loc[0, ...] = ...` writes to every row labelled 0; pass `ignore_index=True` or `reset_index(drop=True)` when you want unique labels. An empty list does not disappear: it becomes one row with NaN in that column, and a scalar or NaN passes through unchanged. Exploding lists of integers leaves an `object` column, so cast it afterwards, to `Int64` if empty lists produced NaN. Several columns can be exploded together only when their lists have the same length on every row.
code
python · 14 linesimport pandas as pd
st = pd.DataFrame({
"station": ["ST01", "ST02", "ST03"],
"sensor_ids": [[11, 12], [], [13]],
})
e = st.explode("sensor_ids")
print(e.index.tolist()) # [0, 0, 1, 2]
print(e["sensor_ids"].dtype) # object
fixed = st.explode("sensor_ids", ignore_index=True).astype({"sensor_ids": "Int64"})
print(fixed.index.tolist()) # [0, 1, 2, 3]
print(fixed["sensor_ids"].tolist()) # [11, 12, <NA>, 13]go deeper
Recall that explode makes one row per list element and copies the row's other columns.
Explain the repeated index labels, the NaN row for an empty list and the object dtype, and how ignore_index and astype fix them.
Catch the bugs exploding causes downstream: loc writes hitting several rows, joins multiplying on duplicate labels, cross products.
Decide whether nested lists are stored at all, or un-nested at ingestion so downstream code never meets them.
## What `explode` does A station registry stores the sensors fitted at each station as a Python list in one column. That is convenient to load but impossible to filter or count by sensor. `DataFrame.explode(column)` **un-nests** the column: each element of each list-like gets its own row, and every other column of that row is copied alongside it. `Series.explode()` does the same for a single Series. ```python import pandas as pd st = pd.DataFrame({ "station": ["ST01", "ST02", "ST03"], "sensors": [["temp", "wind"], [], ["temp"]], "installed": [2019, 2021, 2024], }) e = st.explode("sensors") # station sensors installed # 0 ST01 temp 2019 # 0 ST01 wind 2019 # 1 ST02 NaN 2021 # 2 ST03 temp 2024 ``` ## The index repeats The result keeps the **original row labels**, so `ST01`'s two rows are both labelled `0`. That is useful, because you can aggregate back with `e.groupby(level=0)`, but it is also the source of the most common bug: - `e.loc[0, "installed"] = 2020` updates **both** `ST01` rows, because label `0` now selects two rows. - A later `join` or `concat` that aligns on the index sees duplicate labels and can multiply rows. - `e.loc[0]` returns a DataFrame of two rows, not a single row as code written for the unexploded frame expects. Two ways to get unique labels: 1. `st.explode("sensors", ignore_index=True)` relabels the result `0..n-1`. 2. `e.reset_index(drop=True)` does the same after the fact; `reset_index()` without `drop` keeps the old label as a column, which preserves the link back to the source row. ## Empty lists, scalars and NaN | Value in the column | Result | |---|---| | list, tuple, set, Series or ndarray with n elements | n rows, one element each | | empty list-like | one row, `NaN` in the column | | scalar (a plain string or number) | one row, value unchanged | | `NaN` | one row, `NaN` | So the row count is the total number of elements plus one per empty list, scalar or missing value. A station with no sensors **stays** in the result, which is usually what a report wants; drop it with `dropna(subset=["sensors"])` if not. Exploding a **set** gives its elements in a non-deterministic order. ## The dtype of the exploded column `explode` does not re-infer a numeric dtype. Lists of integers come out as an `object` column even though every element is an `int`, which means slow arithmetic and surprising results from `select_dtypes`. Cast explicitly: - `.astype("int64")` works only when no empty list or NaN produced a missing value. - `.astype("Int64")`, the nullable integer dtype, accepts the NaN rows and shows them as `<NA>`. - `.infer_objects()` picks a NumPy dtype, which is `float64` once a NaN is present. Check `dtypes` after exploding text too, rather than assuming which string dtype you got. ## Exploding several columns `df.explode(["sensors", "serials"])` explodes two columns **in lockstep**, pairing the first sensor with the first serial number. It requires, row by row, the same number of elements in every listed column; otherwise it raises `ValueError: columns must have matching element counts`. It also raises `ValueError` when the frame's column names are not unique. Exploding the columns one after the other instead gives a cross product, which is almost never what you want. ## From a delimited string Exports often store `"temp,wind"` as one string rather than a list. Split it into lists first (the string tools are covered with pandas text columns) and then explode: ```python raw = pd.DataFrame({"station": ["ST01", "ST02"], "tags": ["temp,wind", "temp"]}) raw.assign(tags=raw["tags"].str.split(",")).explode("tags", ignore_index=True) ``` ## `explode` is not `melt` Both make a frame longer, and interviewers sometimes ask which one a task needs. | | `explode` | `melt` | |---|---|---| | Input shape | one column holding list-likes | several columns holding one value each | | What becomes rows | the elements inside each cell | the columns themselves | | Index | original labels repeated | reset unless `ignore_index=False` | | New columns | none | a variable column and a value column | A survey with one column per question needs `melt`; a registry with a list of sensors in one cell needs `explode`. ## Checklist - Decide whether duplicated index labels are wanted (to group back) or not (`ignore_index=True`). - Decide whether empty lists should stay as NaN rows. - Cast the exploded column to its real dtype. - Explode paired columns in one call, never one after the other.
- How do you aggregate an exploded frame back to one row per original station?Group on the repeated index: `e.groupby(level=0).agg(...)` or, for the list itself, `e.groupby(level=0)["sensors"].agg(list)`. This works only if you did not pass `ignore_index=True`, which discards the link to the source row.
- What happens if you explode `sensors` and then `serials` in two separate calls?The second call explodes every row the first one produced, so a station with two sensors and two serials yields four rows, every sensor paired with every serial. Passing both columns in one `explode(["sensors", "serials"])` call pairs them element by element instead.
- explode gives every new row its own fresh index label by default
- explode drops rows whose list is empty
- Exploding a list of integers gives an int64 column
- Exploding two columns one after another pairs their elements
- explode raises on a scalar value in the list column