Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Split Delimited Cell Values into Rows with pandas

Turn cells like "Red; Blue; Green" or "INV-1, INV-2" into one row per value with str.split and explode: clean up spaces and empty items, split two parallel columns together (and handle the "matching element counts" error when they disagree), keep a link to the original row, count and pivot the results, and join them back into one cell when needed.
Data cleaningpandas

Spreadsheets kept by hand pack several values into one cell all the time: a product row with Tags = outdoor; garden; sale, an order with Invoices = INV-1001, INV-1002, a contact with three email addresses separated by commas. That is convenient to type and awkward to analyse — you cannot filter for "garden", count how often each tag occurs, or join the invoice numbers to an invoice table until each value has a row of its own. pandas does this in two steps: split the text into a list, then explode the list into rows. This page covers that, the clean-up that real cells need, splitting two related columns in step, and the reverse operation. It belongs to Cleaning Excel Data with pandas.

Prerequisites

Bash
pip install pandas openpyxl

catalogue.xlsx has five products. Tags is a semicolon-separated list with the usual inconsistencies: missing spaces, extra spaces, an empty item between two semicolons, an empty cell, and the same tag typed twice in different case. Aliases and Prices are comma-separated lists that belong together — the first price goes with the first alias — except for the ladder, where one price is missing.

Step 1 — Split, then explode

Python
import pandas as pd

cat = pd.read_excel("catalogue.xlsx", keep_default_na=False)
tags = cat.assign(Tag=cat["Tags"].str.split(";")).explode("Tag")
print(tags[["SKU", "Tag"]].to_string())

str.split(";") turns each cell into a Python list, and explode("Tag") repeats the row once per list element, copying every other column. The index is repeated too — all of P-100's rows keep index 0 — which is exactly what makes it possible to trace each new row back to its source row later. The raw result still has problems: " outdoor" with a leading space, "power tools " with a trailing one, an empty string from the double semicolon, and an empty string for the gloves.

One product row becomes one row per tag On the left the product P-100 with the cell "garden; outdoor;sale". On the right three rows for P-100 with the tags garden, outdoor and sale, after splitting on semicolons, exploding and trimming. A B 1 SKU Tags 2 P-100 garden; outdoor;sale 3 P-101 garden A B 1 SKU Tag 2 P-100 garden 3 P-100 outdoor 4 P-100 sale 5 P-101 garden before after str.split + explode

Step 2 — Clean the pieces

Trim, normalise case, drop empty items and remove duplicates within each product:

Python
tags = (cat.assign(Tag=cat["Tags"].str.split(";"))
           .explode("Tag")
           .assign(Tag=lambda d: d["Tag"].str.strip().str.lower())
           .query("Tag != ''")
           .drop_duplicates(["SKU", "Tag"]))
print(tags[["SKU", "Product", "Tag"]].to_string())

Stripping after the explode handles every spacing variant at once. Filtering out empty strings removes both the item between the two semicolons and the product with no tags at all — decide whether that product should vanish or keep a row with a placeholder such as "(none)"; for a tag count, vanishing is right. drop_duplicates on SKU and tag removes the ladder's second "outdoor", which only differed by case and spaces. For splitting on several possible separators, use a regular expression: str.split(r"\s*[;,|]\s*", regex=True) splits on semicolons, commas or pipes and eats the surrounding spaces in one go.

Step 3 — Count, filter and pivot the values

With one value per row, the questions that were impossible become one-liners:

Python
print(tags["Tag"].value_counts())
print("garden products:", tags.loc[tags["Tag"] == "garden", "Product"].tolist())
try:
    pd.crosstab(tags["Product"], tags["Tag"])
except ValueError as exc:
    print("ValueError:", exc)
tags = tags.reset_index(drop=True)
matrix = pd.crosstab(tags["Product"], tags["Tag"])
print(matrix)

value_counts ranks the tags and filtering finds every product with a tag. The first crosstab attempt fails with ValueError: cannot reindex on an axis with duplicate labels: after explode, several rows share each original index label, and functions that align Series by index cannot tell them apart. reset_index(drop=True) gives every row its own label, and the second attempt turns the long list into a product × tag matrix of 0s and 1s, ready to write to Excel with conditional formatting. The same shape feeds a co-occurrence analysis — which tags appear together — as in Build a Product Co-occurrence Matrix from Order Lines.

Step 4 — Explode two parallel columns together

Aliases and Prices are paired lists: the second alias goes with the second price. Exploding them separately would pair every alias with every price. pandas can explode several columns in one call, keeping the pairs — if every row's lists have the same length:

Python
pairs = cat.assign(Alias=cat["Aliases"].str.split(","), Price=cat["Prices"].str.split(","))
try:
    pairs.explode(["Alias", "Price"])
except ValueError as exc:
    print("ValueError:", exc)

The ladder has two aliases and one price, so the call fails with ValueError: columns must have matching element counts. That error is a feature: it found a data problem. Report the offending rows and decide how to handle them before exploding:

Python
lengths = pairs["Alias"].str.len() != pairs["Price"].str.len()
print("mismatched:", pairs.loc[lengths, ["SKU", "Aliases", "Prices"]].to_dict("records"))

def pad_to_match(row):
    n = max(len(row["Alias"]), len(row["Price"]))
    return pd.Series({"Alias": row["Alias"] + [None] * (n - len(row["Alias"])),
                      "Price": row["Price"] + [None] * (n - len(row["Price"]))})

pairs[["Alias", "Price"]] = pairs.apply(pad_to_match, axis=1)
long = pairs.explode(["Alias", "Price"])[["SKU", "Alias", "Price"]]
long["Price"] = pd.to_numeric(long["Price"])
print(long.to_string())

Padding the shorter list with None keeps every alias and marks the missing price as empty, which is honest; repeating the last price would invent data. After the explode, prices are converted from text to numbers. The result has one row per alias with its own price: the drill's three names keep their three prices in order, and the step ladder appears with an empty price that a reviewer can fill in, rather than silently inheriting the ladder's 79.00. Writing this long table back to Excel gives the catalogue owner a list they can sort and correct line by line — far easier than hunting for a missing comma inside a cell.

Exploding paired columns separately versus together Exploding Aliases and then Prices separately pairs every alias with every price, giving nine rows for the drill instead of three. Exploding both columns in one call keeps the pairs, and raises an error when a row's lists differ in length. explode("Alias").explode("Price") every alias × every price drill: 3 × 3 = 9 rows wrong prices silently explode(["Alias", "Price"]) pairs kept in order drill: 3 rows length mismatch raises ValueError

When the exploded table goes back to someone who will fix the source sheet, include the original row number:

Python
tags_with_row = (cat.reset_index(names="SourceRow")
                    .assign(SourceRow=lambda d: d["SourceRow"] + 2,       # Excel row: header is row 1
                            Tag=lambda d: d["Tags"].str.split(";"))
                    .explode("Tag"))
tags_with_row["Tag"] = tags_with_row["Tag"].str.strip()
print(tags_with_row.loc[tags_with_row["Tag"] == "", ["SourceRow", "SKU", "Tags"]].to_string(index=False))

The report lists the Excel rows that contain empty items — the double semicolon on row 4 and the empty cell on row 5 — so the owner of the sheet can fix them at the source. Adding 2 converts the zero-based DataFrame position to a worksheet row number, because row 1 is the header.

Step 6 — Join values back into one cell

The reverse — one cell per product, values joined with a separator — is a group-by with str.join, the pandas version of Excel's TEXTJOIN:

Python
clean_tags = tags.groupby("SKU")["Tag"].agg(lambda s: "; ".join(sorted(s))).rename("Tags (clean)")
out = cat[["SKU", "Product"]].merge(clean_tags, on="SKU", how="left").fillna({"Tags (clean)": ""})
out.to_excel("catalogue_clean.xlsx", index=False)
print(out.to_string(index=False))

Round-tripping through the long format is also the cleanest way to normalise a delimited column: split, clean, de-duplicate and sort the values, then join them back with one consistent separator. See TEXTJOIN Equivalent in pandas for more on joining.

Normalising a delimited column by round-tripping through rows The messy Tags cell is split and exploded into rows, each value is trimmed, lower-cased and de-duplicated, then the values are joined back into one cell per product with a consistent separator. Messy cell "outdoor; Outdoor ;safety" Split + explode one row per value Strip, lower, dedupe clean values Join back "outdoor; safety"

Performance notes

str.split and explode are vectorised and handle hundreds of thousands of rows in well under a second; the cost grows with the number of values produced, not the number of source rows. The row-wise apply used to pad mismatched lists in Step 4 is the slow part, so run it only on the rows that need it — select them with the length comparison first and assign the padded lists back to those rows. For very large files where most cells hold a single value, splitting only the cells that contain a separator (str.contains(";")) avoids creating millions of one-element lists.

Common pitfalls

  • Forgetting to strip. " outdoor" and "outdoor" count as different values; strip after exploding.
  • Empty strings versus NaN. Splitting "" gives [""] and an empty-string row; splitting NaN gives NaN. Read with keep_default_na=False or fill first, then drop empty items.
  • Exploding paired columns separately. It creates a cross product; explode them together.
  • Duplicate index after explode. Call reset_index(drop=True) before index-based operations that expect unique labels.
  • Separators inside values. A product called Nuts, bolts & washers in a comma-separated list cannot be split reliably; agree a separator that never appears in the values.

Conclusion

Split delimited cells with str.split, give each value its own row with explode, then strip, normalise case, drop empty items and de-duplicate. Explode paired columns together so their values stay matched, treat the "matching element counts" error as a data-quality report, keep the source row number for corrections, and join the cleaned values back with groupby and "; ".join when one cell per record is needed again.

Frequently asked questions

How do I split a cell with several values into multiple rows in pandas? Split the text into lists with df"Tags".str.split(";"), then call df.explode("Tags"). Each list element gets its own row and the other columns are repeated.

How do I remove spaces and empty values after exploding? Apply str.strip() to the exploded column, then filter out empty strings with dfdf"Tag" != "". Lower-case the values and drop duplicates if the same value can appear twice in one cell.

Why does explode say "columns must have matching element counts"? You exploded several columns at once and at least one row has lists of different lengths. Find those rows by comparing str.len() of the columns, fix or pad them, then explode again.

How do I combine exploded rows back into one cell? Group by the record key and join the values: df.groupby("SKU")"Tag".agg("; ".join). Sort the values first if you want a consistent order.

More on data cleaning

More on data cleaning →