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?
answer
- a regex with capture groups
- first match per value
- named groups become labels
- expand picks Series or DataFrame
- rows that do not match
basics
~20 sSeries.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 linesimport 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
Recall that str.extract needs capture groups, returns one column per group and leaves missing values where nothing matched.
Explain the expand rules, how named and unnamed groups label columns, the first-match rule and when extractall's match level is needed.
Show how you would anchor and test a pattern on real catalog data, auditing all-missing rows instead of trusting a partial match.
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.