Level 8 · Units sold per product tag

Lists inside cells

Sometimes one cell holds several values: a product’s tags, a job’s required skills, the answers to a “pick all that apply” question. Stored in a DataFrame, that’s a column whose cells are lists. You can’t group or count by it directly, because groupby needs one value per row.

explode fixes that. It gives every list item its own row and repeats the other columns:

df = pd.DataFrame({"product_id": [101, 113], "tags": [["statement", "bestseller"], ["bestseller"]]})
df.explode("tags")
#    product_id        tags
# 0         101   statement
# 0         101  bestseller
# 1         113  bestseller

The index labels repeat (0, 0, 1), which tells you which rows came from the same original row. Add .reset_index(drop=True) if you want a clean 0, 1, 2. An empty list becomes one row with NaN, and a missing cell stays missing.

When the lists arrive as text

Files can’t store Python lists. A CSV or a database export writes them as text, so after loading, the cell is the string "['statement', 'bestseller']", square brackets and quotes included. explode treats a string as a single value and leaves it alone.

ast.literal_eval (from Python’s built-in ast module) reads such text back into the real object. It only accepts literals: lists, dicts, tuples, numbers, strings, True, False and None. Unlike eval, it never runs code, so it’s safe on data from a file.

import ast
ast.literal_eval("['gift-idea', 'small-space']")   # ['gift-idea', 'small-space'], a list

Apply it to the column, guarding against missing cells, which are not strings:

col.apply(lambda x: ast.literal_eval(x) if isinstance(x, str) else [])

A worked example: counting tags

After converting and exploding the eight-product export, there are 12 rows: one per product and tag, plus a NaN row for the product with an empty list and one for the product with a missing cell. Counting tags is then an ordinary value_counts:

exploded["tags"].value_counts()
# gift-idea      4
# bestseller     3
# statement      2
# small-space    1

Joining the exploded rows to order_items on product_id brings in every order line of each product, once per tag. A product with two tags has its units counted under both, so the tag totals add up to more than the products sold. That’s correct for “units sold under each tag”, but say so when you report it.

Common mistakes

• Exploding a text column. The string isn’t a list, so explode changes nothing.
• Calling ast.literal_eval on a missing cell, which raises an error. Check isinstance(x, str).
• Using eval instead of ast.literal_eval on file data. eval runs whatever code the text holds.
• Adding up per-tag totals as if they were a grand total: multi-tag products are counted more than once.