Explode
Python · Reshaping & Workflow
Theory
Sometimes one cell holds a list - several skills per employee, several tags per product, items in an order. df.explode('skills') gives each list element its own row and repeats the other columns, so Asha with ['SQL', 'Python'] becomes two rows. The original index label is repeated for each new row (use ignore_index=True to renumber); an empty list becomes one row with NaN, and a plain value stays as one row.
Data often arrives as a delimited string instead of a list, such as 'SQL,Python'. Split it first: df.assign(skills=df['skills'].str.split(',')).explode('skills'), then strip spaces. You can explode several list columns of matching lengths together with explode(['a', 'b']). Once exploded, ordinary tools work: value_counts() for the most common skill, groupby for counts per person, or merge to a lookup table. To go back, df.groupby('name')['skills'].agg(list) or agg(', '.join).
Why we use it
List-valued cells cannot be filtered, counted or joined directly. explode normalises them into one value per row, the shape every pandas operation and every database table expects.
When to use it
Use explode whenever a column holds lists or delimited strings you need to analyse per item. Watch the row count: one row with 50 tags becomes 50 rows. In SQL this is LATERAL FLATTEN (Snowflake), UNNEST or CROSS APPLY STRING_SPLIT.
How it works
- Column holding lists or 'a,b,c' strings
- Is it a delimited string? Yes: Split it: .str.split(','). No: Already a list.
- explode('col'): one row per list element
- Strip spaces and drop NaN from empty lists
- One item per row, ready for value_counts / groupby
Syntax
df.explode('col', ignore_index=False)
df.assign(col=df['col'].str.split(',')).explode('col')
df.groupby('key')['col'].agg(list)Example
skills = df.assign(skill=df['skills'].str.split(',')).explode('skill')
print(skills['skill'].value_counts())Key points
- Each list element gets its own row; other columns are repeated
- Index labels repeat - pass ignore_index=True to renumber
- Empty lists become NaN rows; split delimited strings with .str.split first
- Reverse with groupby(key)[col].agg(list) or agg(', '.join)
- SQL equivalent: LATERAL FLATTEN / UNNEST(skills)