Skip to content
Bamboo Weekly

pandas explode

Turn a column of lists into one row per element.

What it does

What do you do when a single cell contains several values? Real data sets do this constantly: one police department using two camera vendors, one Nobel Prize shared by three laureates, one paper tagged with six keywords. The value you want to count is not the cell — it is each item inside the cell.

explode is the method for that. Give it a series whose values are lists, and it hands back a longer series with one row per list element. Give a data frame the name of a column holding lists, and it duplicates each row once per element, carrying the other columns along for the ride. A two-element list becomes two rows; a ten-element list becomes ten. Nothing is summarized and nothing is thrown away — the frame simply gets taller and narrower in spirit, which is usually the shape that value_counts and groupby want.

The thing to hold onto is that explode does not create the lists. It only unpacks them. Most of the time the lists arrive from an API that returned nested JSON, or from str.split applied to a column of comma-separated text, which is the pairing I reach for most.

Official documentation: DataFrame.explode and Series.explode

The arguments that earn their keep

There are only two, and one of them does not exist on a series:

s.explode()                                # a series of lists -> a longer series
df.explode('Vendor')                       # unpack one column, duplicating rows
df.explode(['Technology', 'Vendor'])       # two columns at once (Pandas 1.3+)
df.explode('Vendor', ignore_index=True)    # renumber 0..n-1 instead of repeating

column is the first positional argument on a data frame, and it is required — Pandas will not guess which of your columns holds the lists. Pass a list of names and Pandas unpacks them in parallel, pairing the first element of one against the first element of the other, which only works if the lists in a given row are the same length.

ignore_index=True is the one to remember. Without it, every row that came out of a list keeps the index label of the row it came from, so the index repeats. That is sometimes exactly what you want and sometimes the start of a bad afternoon; more on that below.

A worked example, on real data

The Electronic Frontier Foundation's Atlas of Surveillance records which surveillance technologies American law-enforcement agencies have bought, and from whom. Bamboo Weekly #182 built a puzzle around it. Each row is one agency-technology pair, and the whole thing is one CSV file:

import pandas as pd

url = 'https://www.atlasofsurveillance.org/download.csv'

df = pd.read_csv(url, usecols=['Agency', 'State', 'Technology', 'Vendor'])

df['Vendor'].value_counts().head(5)

Vendor
Flock Safety     2746
Axon              899
SoundThinking     797
DJI               660
Idemia            257
Name: count, dtype: int64

Reasonable-looking numbers, and quietly wrong. Some deployments involve more than one company, and the file records that by putting both names in the cell:

df.loc[pd.col('Vendor').str.contains(',', na=False), ['Agency', 'Vendor']].head(3)

                                                Agency                        Vendor
155                       Cleveland Division of Police           Flock Safety, Selex
261                         Chandler Police Department      PIPS, Vigilant Solutions
711  Santa Fe Regional Emergency Communications Center  Motorola Solutions, BriefCam

value_counts treated "Flock Safety, Selex" as its own vendor, so Cleveland's Flock installation was never counted as a Flock installation. The fix is two methods. First str.split, which breaks each string on the comma and gives back a series of lists:

df['Vendor'].dropna().str.split(', ').head(3)

0    [VML Insurance Programs]
1               [ShotSpotter]
2               [ShotSpotter]
Name: Vendor, dtype: object

Notice that even the single-vendor rows are now one-element lists. That is fine; explode turns a one-element list into one row, which is what we want. Here is Cleveland, row 155, after exploding:

df['Vendor'].dropna().str.split(', ').explode().loc[155]

155    Flock Safety
155           Selex
Name: Vendor, dtype: str

One row became two, both still labeled 155. Now the count is honest:

(
    df['Vendor']
    .dropna()
    .str.split(', ')
    .explode()
    .value_counts()
    .head(5)
)

Vendor
Flock Safety     2785
Axon              911
SoundThinking     797
DJI               747
Idemia            258
Name: count, dtype: int64

7,974 rows became 8,167. Flock Safety gained 39 deployments and the drone maker DJI gained 87, because drones are frequently bought alongside a second vendor's software. SoundThinking, which never shares a cell, did not move at all.

That was a series. On a data frame you name the column, and every other column comes along:

(
    df
    .loc[pd.col('Vendor').str.contains(',', na=False)]
    .assign(Vendor=pd.col('Vendor').str.split(', '))
    .explode('Vendor', ignore_index=True)
    [['Agency', 'Vendor']]
    .head(4)
)

                         Agency              Vendor
0  Cleveland Division of Police        Flock Safety
1  Cleveland Division of Police               Selex
2    Chandler Police Department                PIPS
3    Chandler Police Department  Vigilant Solutions

assign builds the lists, explode unpacks them, and ignore_index=True gives me a clean 0-to-n index rather than 155, 155, 261, 261.

Since Pandas 1.3 you can explode several columns together. The lists have to line up element for element, which in practice means they came from the same place — an API that returned parallel arrays, or an aggregation you are now undoing. Collect each agency's technologies and vendors into two lists, and they are aligned by construction:

(
    df
    .dropna(subset='Vendor')
    .groupby('Agency')[['Technology', 'Vendor']]
    .agg(list)
    .loc[['Chicago Police Department']]
    .explode(['Technology', 'Vendor'])
)

                                                    Technology              Vendor
Agency
Chicago Police Department                     Face Recognition      DataWorks Plus
Chicago Police Department                      Camera Registry  Motorola Solutions
Chicago Police Department                  Cell-site Simulator        Harris Corp.
Chicago Police Department                    Gunshot Detection         ShotSpotter
Chicago Police Department      Automated License Plate Readers  Vigilant Solutions
Chicago Police Department  Third-party Investigative Platforms       SoundThinking
Chicago Police Department                      Video Analytics            BriefCam

Each technology stays married to its own vendor. Explode the two columns in separate calls instead and you get the cross product — 49 rows of nonsense instead of 7.

Five mistakes people make

Forgetting that the index repeats. This is the big one. explode gives every new row the label of the row it came from, so your index is no longer unique. That breaks join, it makes .loc[155] return a data frame where you expected a series, and it quietly duplicates rows in a later merge. Pass ignore_index=True, or call reset_index() afterward, unless you have a reason to keep the old labels.

Assuming the repeated index is always a bug. Sometimes it is the whole point. If your index is a meaningful key — a legislator's ID, a prize ID — then the repetition is exactly the many-to-one relationship you want, and reset_index() turns it into a foreign-key column for free. Decide which situation you are in before reaching for ignore_index.

Expecting empty lists to disappear. They do not. An empty list becomes a single row holding NaN, so a three-row series can come back with four rows rather than two:

pd.Series([['Flock Safety', 'Selex'], [], ['Axon']]).explode()

0    Flock Safety
0           Selex
1             NaN
2            Axon
dtype: str

dropna() after explode is a routine part of the chain for that reason. Missing values behave the same way: a NaN in, a NaN out.

Exploding a column of strings. A Python string is iterable, so people expect explode to break it into characters, or to split it on commas. It does neither. Non-list values pass through untouched, which means df['Vendor'].explode() returns the column exactly as it was, with no error and no warning. If you want "Flock Safety, Selex" to become two rows, you need str.split first. The silence is what makes this one expensive.

Exploding two columns whose lists differ in length. Ask Pandas to unpack Vendor and Technology in parallel when one holds two names and the other holds one, and it stops you:

ValueError: columns must have matching element counts

That is a good error. If you genuinely want every technology paired with every vendor, do the two explodes in separate calls and accept the cross product on purpose.

Where it shows up in Bamboo Weekly

Bamboo Weekly #65: Microplastics is the cleanest example of the str.split pairing. NOAA's marine microplastics data set separates keywords with either a slash or a semicolon, so the answer is .str.split('[/;]', regex=True).explode().str.strip().value_counts() — a regular expression, an explode, and a count.

Bamboo Weekly #73: Avocado hand counts the words in emergency-room narratives about avocado injuries. Splitting on whitespace gives a series of word lists, explode flattens it into a series of words, and everything after that is ordinary filtering.

Bamboo Weekly #76: Aging legislators uses the data frame form. Each member of Congress arrives with a terms column holding a list of every term they served, so .set_index('bioguide').explode('terms') gives one row per term with the legislator's ID repeated down the index — which is precisely what the rest of the analysis needs.

Bamboo Weekly #36: Nobel Prize is the issue to read if you want to see the repeated index used deliberately. Each prize holds a list of laureates, so explode produces one row per laureate with the prize ID repeated; reset_index() then turns that index into a prize_id column, and the result is a join table between prizes and people.

Practice it

Work through a .explode() exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/explode/

Go deeper

explode almost never travels alone. str.split is what usually creates the lists, value_counts and groupby are usually what you called explode in order to reach, and reset_index is how you deal with the index afterward.

More Pandas videos on Python and Pandas with Reuven Lerner.

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 20 Bamboo Weekly exercises that use explode on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.