DEV Community

dustin liang
dustin liang

Posted on

pandas explode() on a List Column: Turning Stock-to-Concept Tags into a Lookup Table, a Co-occurrence Matrix and Back Again

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
    ],
})
Enter fullscreen mode Exit fullscreen mode

What explode() does by default

long = df.explode("concepts")
print(long)
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

Five behaviours are visible here, and each one matters:

  1. The index repeats. Every element keeps its parent's index label, so label 0 now appears twice. Anything that assumes a unique index, such as .loc[0] returning a single row or an index-aligned assignment, will behave differently.
  2. 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.
  3. Empty lists become NaN. [] produces one row with a missing value, not zero rows. The entity is kept.
  4. None becomes NaN. Same result as the empty list.
  5. Duplicates survive. AI compute appears 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)
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

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 Compute and another ai compute, lower-case first, then drop duplicates, otherwise both survive.
  • dropna removed 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 original df, not from long.

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())
Enter fullscreen mode Exit fullscreen mode
0    energy storage
0      grid upgrade
1        AI compute
dtype: str
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
{'ai compute': ['A02', 'A03'], 'chip packaging': ['A03'],
 'energy storage': ['A01', 'A04'], 'grid upgrade': ['A04'],
 'humanoid robot': ['A02'], 'solid-state battery': ['A01']}
Enter fullscreen mode Exit fullscreen mode

How many stocks each concept covers:

print(long["concepts"].value_counts())
Enter fullscreen mode Exit fullscreen mode
concepts
energy storage         2
ai compute             2
solid-state battery    1
humanoid robot         1
chip packaging         1
grid upgrade           1
Name: count, dtype: int64
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

Multiplying the transpose by the matrix itself counts, for every pair of concepts, how many stocks carry both:

co = wide.T @ wide
print(co)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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=";"))
Enter fullscreen mode Exit fullscreen mode
   AI compute  energy storage  grid upgrade
0           0               1             1
1           1               0             0
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
  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
Enter fullscreen mode Exit fullscreen mode

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"})
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode
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
Enter fullscreen mode Exit fullscreen mode

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 还原为列表以及性能对比。示例数据为占位样本,仅作方法演示。

Top comments (0)