Tagging data often arrives as one row per entity with a list of tags in a single cell. A stock has several concept or theme tags, an article has several topics, a product sits in several categories. That shape is easy to store and hard to analyse. You cannot groupby a list, value_counts on a list column counts whole lists, and a join on a tag is impossible until each tag has its own row.
DataFrame.explode() is the pandas tool for this. It takes a column of lists and produces one row per element, repeating the other columns. This tutorial walks through explode on a small table of stocks and concept tags, covering the parts that cause bugs in real data: mixed cell types, empty lists and None, duplicate tags, the index that explode leaves behind, exploding two columns together, and the reverse operation. It ends with a tag lookup, tag counts and a co-occurrence matrix, which together answer "which stocks carry this concept?" and "which concepts tend to appear together?"
Everything runs on pandas 3.1. The tickers and company names are placeholders.
The starting table, with realistic mess
Real tag data is rarely clean. This table contains the four shapes you will meet in practice: a normal list, a list with a duplicate, a delimited string that never got parsed, an empty list and a missing value.
import numpy as np import pandas as pd df = pd.DataFrame({ "ticker": ["A01", "A02", "A03", "A04", "A05", "A06"], "name": ["Alpha Cells", "Beta Robotics", "Gamma Chips", "Delta Grid", "Epsilon Cloud", "Zeta Motors"], "concepts": [ ["solid-state battery", "energy storage"], ["humanoid robot", "AI compute"], ["AI compute", "chip packaging", "AI compute"], # duplicate tag "energy storage; grid upgrade", # unparsed string [], # no tags None, # missing ], }) What explode() does by default
long = df.explode("concepts") print(long) ticker name concepts 0 A01 Alpha Cells solid-state battery 0 A01 Alpha Cells energy storage 1 A02 Beta Robotics humanoid robot 1 A02 Beta Robotics AI compute 2 A03 Gamma Chips AI compute 2 A03 Gamma Chips chip packaging 2 A03 Gamma Chips AI compute 3 A04 Delta Grid energy storage; grid upgrade 4 A05 Epsilon Cloud NaN 5 A06 Zeta Motors NaN Five behaviours are visible here, and each one matters:
- The index repeats. Every element keeps its parent's index label, so label
0now appears twice. Anything that assumes a unique index, such as.loc[0]returning a single row or an index-aligned assignment, will behave differently. - Strings are not split. A string is a scalar to explode, so
"energy storage; grid upgrade"passes through as one tag. Explode only expands list-likes: lists, tuples, sets, Series and NumPy arrays. - Empty lists become NaN.
[]produces one row with a missing value, not zero rows. The entity is kept. - None becomes NaN. Same result as the empty list.
- Duplicates survive.
AI computeappears twice for A03.
Behaviours 3 and 4 are actually helpful: explode never silently drops an entity. Whether you keep those rows is a decision you make next, explicitly.
Normalise the cells before exploding
The reliable pattern is to coerce every cell to a clean Python list first, then explode. A small function handles every shape above:
def to_list(v): if v is None or (isinstance(v, float) and np.isnan(v)): return [] if isinstance(v, str): return [p.strip() for p in v.split(";") if p.strip()] return list(v) df["concepts"] = df["concepts"].map(to_list) Then explode with ignore_index=True to get a fresh, unique index, drop the empty-tag rows, remove duplicate pairs, and normalise case and whitespace:
long = (df.explode("concepts", ignore_index=True) .dropna(subset=["concepts"]) .drop_duplicates(["ticker", "concepts"])) long["concepts"] = long["concepts"].str.strip().str.lower() print(long) ticker name concepts 0 A01 Alpha Cells solid-state battery 1 A01 Alpha Cells energy storage 2 A02 Beta Robotics humanoid robot 3 A02 Beta Robotics ai compute 4 A03 Gamma Chips ai compute 5 A03 Gamma Chips chip packaging 7 A04 Delta Grid energy storage 8 A04 Delta Grid grid upgrade Note the gap at index 6: that was A03's duplicate AI compute, removed by drop_duplicates. If you need a contiguous index after filtering, add .reset_index(drop=True).
Two ordering decisions in this pipeline deserve a comment:
- Normalise case before deduplicating, if variants exist. Here the duplicate was an exact match, so the order did not matter. If one source writes
AI Computeand anotherai compute, lower-case first, then drop duplicates, otherwise both survive. -
dropnaremoved A05 and A06. That is correct for a lookup table, but if you later compute "share of stocks with at least one tag", count from the originaldf, not fromlong.
For a single delimited string column you can skip the function entirely and chain str.split with explode:
s = pd.Series(["energy storage; grid upgrade", "AI compute"]) print(s.str.split(";").explode().str.strip()) 0 energy storage 0 grid upgrade 1 AI compute dtype: str Tag lookup and tag counts
With one row per (ticker, concept) pair, the common questions become one-liners.
Which stocks carry each concept:
lookup = long.groupby("concepts")["ticker"].agg(list).to_dict() print(lookup) {'ai compute': ['A02', 'A03'], 'chip packaging': ['A03'], 'energy storage': ['A01', 'A04'], 'grid upgrade': ['A04'], 'humanoid robot': ['A02'], 'solid-state battery': ['A01']} How many stocks each concept covers:
print(long["concepts"].value_counts()) concepts energy storage 2 ai compute 2 solid-state battery 1 humanoid robot 1 chip packaging 1 grid upgrade 1 Name: count, dtype: int64 Because duplicates were dropped per ticker, these counts are numbers of distinct stocks, not numbers of tag mentions. Without the drop_duplicates step, ai compute would show 3, overstating its coverage by one stock. With six rows the error is obvious. With 5,000 stocks and tags merged from three vendors, it is not.
The long table is also the right shape for joins. If you have a second table of daily metrics per ticker, long.merge(metrics, on="ticker") attaches them to every concept the ticker belongs to, and a groupby("concepts") then aggregates the metric per concept.
A one-hot matrix and a co-occurrence matrix
pd.crosstab turns the long table into a ticker-by-concept indicator matrix:
wide = pd.crosstab(long["ticker"], long["concepts"]) print(wide) concepts ai compute chip packaging energy storage grid upgrade humanoid robot solid-state battery ticker A01 0 0 1 0 0 1 A02 1 0 0 0 1 0 A03 1 1 0 0 0 0 A04 0 0 1 1 0 0 Multiplying the transpose by the matrix itself counts, for every pair of concepts, how many stocks carry both:
co = wide.T @ wide print(co) concepts ai compute chip packaging energy storage grid upgrade humanoid robot solid-state battery concepts ai compute 2 1 0 0 1 0 chip packaging 1 1 0 0 0 0 energy storage 0 0 2 1 0 1 grid upgrade 0 0 1 1 0 0 humanoid robot 1 0 0 0 1 0 solid-state battery 0 0 1 0 0 1 The diagonal repeats the per-concept counts. Off-diagonal cells show overlap: energy storage shares one stock each with grid upgrade and solid-state battery, while ai compute overlaps with both chip packaging and humanoid robot. On a real universe, this matrix is the input for normalised overlap measures (divide by the smaller diagonal value, or compute Jaccard similarity) and for clustering concepts into broader groups.
If you only have the raw delimited strings and need indicators quickly, str.get_dummies does the split and one-hot in one call:
print(pd.Series(["energy storage;grid upgrade", "AI compute"]).str.get_dummies(sep=";")) AI compute energy storage grid upgrade 0 0 1 1 1 1 0 0 It splits on the exact separator, so strip spaces around delimiters first or you will get columns like " grid upgrade".
Exploding two columns together
Sometimes each tag has a paired value, for example a relevance weight. Since pandas 1.3, explode accepts a list of columns and expands them in lockstep:
d2 = pd.DataFrame({ "ticker": ["A01", "A02"], "concepts": [["solid-state battery", "energy storage"], ["humanoid robot", "AI compute"]], "weight": [[0.7, 0.3], [0.6, 0.4]], }) e = d2.explode(["concepts", "weight"]) print(e) print(e.dtypes) ticker concepts weight 0 A01 solid-state battery 0.7 0 A01 energy storage 0.3 1 A02 humanoid robot 0.6 1 A02 AI compute 0.4 ticker str concepts str weight object dtype: object Two catches. The lists in each row must have the same length, or pandas raises ValueError: columns must have matching element counts. And the exploded numeric column comes out as object dtype, because it was a column of Python lists. Convert it before doing arithmetic:
e = e.astype({"weight": "float64"}) Exploding the two columns separately and joining them afterwards is a common mistake. It produces a Cartesian product within each row (four rows per ticker here instead of two) and silently pairs every concept with every weight.
Going back: from long to lists
The inverse of explode is a groupby with list aggregation:
back = long.groupby("ticker")["concepts"].agg(sorted) print(back) ticker A01 [energy storage, solid-state battery] A02 [ai compute, humanoid robot] A03 [ai compute, chip packaging] A04 [energy storage, grid upgrade] Name: concepts, dtype: object Sorting inside the aggregation makes the output deterministic, which helps when you diff tag snapshots from two dates to see which concepts were added or removed. A05 and A06 are absent because they had no tags. If you need every ticker, back.reindex(df["ticker"], fill_value=[]) restores them with empty lists.
Performance
explode is vectorised and fast. On a synthetic frame with 200,000 rows and three tags each, it produced 600,000 rows in about 0.05 seconds on the test machine. A pure-Python list comprehension over zip(ticker, concepts) building the same rows took about 0.17 seconds. The common df.apply(lambda row: pd.Series(row["concepts"]), axis=1).stack() pattern from older answers is far slower than both and worth replacing wherever you find it.
FAQ
Why does explode keep NaN rows instead of dropping them?
So that no entity disappears by accident. An empty list or missing value becomes one row with NaN. Use dropna(subset=[col]) when you want them gone.
How do I explode a column that contains JSON strings like '["a", "b"]'?
Parse first: df[col] = df[col].map(json.loads), then explode. Explode treats any string as a single scalar.
Does explode work on a Series?
Yes. Series.explode() behaves the same way and also accepts ignore_index=True.
Conclusion
pandas explode on a list column is the step that turns tag data into something you can count, join and compare. Normalise every cell to a real list first, explode with ignore_index=True, drop empty rows and duplicate pairs on purpose, and convert dtypes after exploding paired columns. From the long table, a lookup, distinct counts, a one-hot matrix and a co-occurrence matrix each take a single line, and a list-aggregating groupby takes you back.
To see the same idea applied to maintained A-share concept tags, Lava's concept stocks lookup lists the stocks attached to each theme.
中文简介:本文讲 pandas explode() 如何处理“列表列”:以股票-概念标签表为例,说明 explode 的默认行为(索引重复、字符串不拆分、空列表与 None 变 NaN、重复标签保留),给出先规整为列表再展开、去空去重的稳妥写法;接着用展开后的长表做概念反查、按概念计股票数、crosstab 独热矩阵与共现矩阵;还覆盖多列同步展开、groupby 还原为列表以及性能对比。示例数据为占位样本,仅作方法演示。
