skip to content

In pandas, how does Series.str.extract turn product SKU codes like 'TSH-L-RED' into separate columns, and what decides the result's shape?

level: middleimportance: must knowfreq 55%

answer

  1. a regex with capture groups
  2. first match per value
  3. named groups become labels
  4. expand picks Series or DataFrame
  5. rows that do not match

basics

~20 s

Series.str.extract applies a regex to each string and returns one column per capture group, labelled by named groups, from the first match only. Non-matching or missing rows get missing values; expand=False with one group returns a Series.

solid answer

~50 s

`s.str.extract(pat)` searches each value with the regular expression (so it can match anywhere unless you anchor it) and keeps only the **first** match. Each capture group becomes a column: `(?P<size>...)` labels it `size`, unnamed groups are numbered from 0. With the default `expand=True` the result is always a DataFrame indexed like the source, even for one group; `expand=False` with one group returns a Series, and with several groups still a DataFrame. A row that does not match, or is missing, is missing in every column, and an optional group that did not take part is missing in its column only. A pattern with no capture group raises `ValueError`. The columns stay text (the source's string dtype, or object), so extracted digits still need a numeric cast. For every match rather than the first, `str.extractall` returns one row per match with an extra `match` index level.

code

python · 10 lines
python
import pandas as pd

skus = pd.Series(["TSH-L-RED", "HOD-XL-BLU", "MUG-BLK", None])
pattern = r"^(?P<product>[A-Z]+)(?:-(?P<size>XS|S|M|L|XL))?-(?P<colour>[A-Z]{3})$"
parts = skus.str.extract(pattern)
print(parts)

bundles = pd.Series(["TSH-L-RED+CAP-BLK", "HOD-XL-BLU"], index=["b1", "b2"])
items = bundles.str.extractall(r"(?P<product>[A-Z]{3})-(?:[A-Z]+-)?(?P<colour>[A-Z]{3})")
print(items)

go deeper

for a junior

Recall that str.extract needs capture groups, returns one column per group and leaves missing values where nothing matched.

for a middle

Explain the expand rules, how named and unnamed groups label columns, the first-match rule and when extractall's match level is needed.

for a senior

Show how you would anchor and test a pattern on real catalog data, auditing all-missing rows instead of trusting a partial match.

for a principal

Discuss when a codebase should parse codes with regex in pandas versus fixing the upstream format so the parts arrive as separate fields.

## What extract does `Series.str.extract(pat, flags=0, expand=True)` is the pandas tool for pulling **structured parts out of text**. For each value it searches with the regular expression `pat` (like `re.search`, so the match may start anywhere) and returns the text captured by each **capture group**, a parenthesised part of the pattern. It keeps only the **first match** in each value. A product catalog whose SKU codes pack product, size and colour into one string (`"TSH-L-RED"`, `"HOD-XL-BLU"`, `"MUG-BLK"`) is the classic case. ## Shape rules The result's type depends on the caller, the number of groups and `expand`: | caller | groups | `expand=True` (default) | `expand=False` | |---|---|---|---| | Series | 1 | DataFrame with one column | Series | | Series | more than 1 | DataFrame | DataFrame | | Index | 1 | DataFrame with one column | Index | | Index | more than 1 | DataFrame | `ValueError` | A pattern with **no** capture group raises `ValueError` ("pattern contains no capture groups"). The result keeps the **source index**, so it lines up row for row and can be joined back to the frame. ## Column labels - A **named group** `(?P<colour>[A-Z]{3})` labels its column `colour`. - **Unnamed groups** are labelled by position: `0`, `1`, `2`. - When some groups are named and others are not, each unnamed one is labelled by its position. - A **non-capturing group** `(?:...)` groups without creating a column, which is how you express optional parts. ## Missing and non-matching rows - A value the pattern does not match becomes a row of **missing values** in every column, not the original string. - A missing input value gives a row of missing values too. - An **optional group** that did not take part in the match, such as a size that some SKUs lack, is missing in its own column only. - The extracted columns are **text**: the source column's string dtype, or object for an object column. A captured `"42"` is the string `"42"`. ## Writing the pattern for a catalog 1. Anchor the pattern with `^` and `$` when the whole code must conform; otherwise a partial match in the middle of a malformed SKU is accepted. 2. Name every group so the result needs no renaming. 3. Make optional parts optional with `(?:-(?P<size>...))?`, so codes without a size still match. 4. Check the rows that came back all-missing (`parts.isna().all(axis=1)`) to find SKUs the pattern rejects. ## Reading the catalog example row by row With the pattern `^(?P<product>[A-Z]+)(?:-(?P<size>XS|S|M|L|XL))?-(?P<colour>[A-Z]{3})$`: - `"TSH-L-RED"` fills all three columns: `TSH`, `L`, `RED`. - `"HOD-XL-BLU"` shows why alternatives need care: the engine tries the alternatives in order (`XS`, `S`, `M`, `L`, `XL`), and only `XL` matches the characters there, so the size is `XL`. - `"MUG-BLK"` has no size; the optional group is skipped, `size` is missing and `colour` is still `BLK`. - A missing SKU gives missing values in all three columns. ## Flags and joining the parts back - `flags` takes `re` module flags: `skus.str.extract(pattern, flags=re.IGNORECASE)` accepts `tsh-l-red` as well as `TSH-L-RED`. - Because the result keeps the source index, `catalog.join(parts)` or `pd.concat([catalog, parts], axis=1)` attaches the new columns to the right rows, even after filtering. - Assigning several columns at once also works: `catalog[["product", "size", "colour"]] = parts`, provided the number of names matches the number of groups. ## extractall when a value holds several matches `Series.str.extractall(pat, flags=0)` returns **every** match. The result is always a DataFrame with a **MultiIndex** on its rows: the original index plus a last level named `match` that numbers the matches within each value, starting at 0. Values with no match contribute no rows. When every value has exactly one match, `s.str.extractall(pat).xs(0, level="match")` equals `s.str.extract(pat)`. ## What extract does not do - It does not convert types; turning extracted digits into numbers belongs to the casting lesson. - It does not test for a match: `str.contains` does, and a capture group in a `contains` pattern only triggers a warning that points you to `extract`. - `str.findall` also returns every match, but as one list per row instead of a tidy frame.

  • Why does str.contains warn when its pattern has a capture group?
    `contains` only answers whether each value matches, so a capture group is wasted work and usually signals that you wanted the captured text. pandas emits a `UserWarning` saying the pattern has match groups and suggesting `str.extract`. Use a non-capturing group `(?:...)` in `contains`, or switch to `extract` if you need the parts.
  • How do you find the SKUs the pattern rejected?
    A non-matching value comes back as a row of missing values, so `skus[parts.isna().all(axis=1) & skus.notna()]` lists the codes that were present but did not fit. Reviewing those is how you decide whether to widen the pattern or fix the source data.
  • When is str.findall a better fit than extractall?
    `findall` returns one Python list of matches per row, keeping the original shape, which is handy for counting matches or feeding a per-row function. `extractall` returns a long DataFrame with one row per match, a `match` index level and one column per named group, which is what you want for further grouping or joining.

extract is a paper form with labelled boxes: each named group is a box, a code that fits the form fills its boxes, an optional box may stay blank, and a code that does not fit the form leaves every box blank rather than being copied in.

  • str.extract returns every match in each string, not only the first.
  • With one capture group str.extract always returns a Series.
  • A row that fails to match keeps its original string in the result.
  • str.extract without capture groups returns the whole match.
  • Extracted digits come back as int64 without any cast.