Explode a Column into Rows
One cell holding red, green, blue is three facts pretending to be one. Nothing can group, count or join on it while it stays that way. This splits the list and gives each value its own row, copying the rest of the row alongside it — explode in pandas, UNNEST in SQL, and the exact inverse of what group and aggregate does when you join values back together.
How to use
- Name the column that holds the list, by header name or 1-indexed position.
- Set the separator. A literal string by default —
,,;,|, or\nand\tfor escaped whitespace. Tick regex for something like\s*[;,]\s*that accepts either. - Optionally add an index column — name it and each output row gets the 1-based position the value had in its original list. That's how you preserve order that would otherwise be lost.
- Check the row counts in the status line: rows in, rows out, and the longest list found.
What happens to the other columns
They're copied verbatim onto every output row. A row with an order_id and three tags becomes three rows with the same order_id. That's the point — the result is a proper long-format table you can group and join on — but it means the file gets longer and any per-row aggregate you compute on it will now double-count. If you sum a total column after exploding, you will sum it once per tag. Explode last, or sum before.
The exploded column keeps its original name and position. Nothing is added except the optional index column, which goes on the end.
Empty lists and empty values
Two separate cases, two separate checkboxes. Drop empty values handles red,,blue and trailing separators like red,green, — without it you get a row with a blank cell for each. Keep rows with an empty list decides what happens to a row whose cell was empty to begin with, or became empty after dropping: on, it survives as a single row with a blank value; off, it disappears entirely. Keeping is the default because losing rows silently is the worse failure — a row with no tags is still an order.
Trim each value is on by default because red, green split on , leaves a leading space on green, and a space-prefixed value will not group or join with its unspaced twin. If the spaces are meaningful, turn it off.
FAQ
How do I put it back together afterwards?
Group and aggregate with join distinct values on the exploded column, grouping by whatever identifies the original row. That round-trips cleanly, though the separator becomes a semicolon and the original order is only preserved if you kept an index column and sorted by it first.
What's the difference from splitting a column?
Split a column works across — it turns one column into several, and the row count stays the same. This works down — one row becomes many, and the column count stays the same. Use split when the parts are different things (first name, last name); use explode when they're the same kind of thing repeated.
Can I explode two columns at once?
Not in one pass, and running it twice gives you the cross product — three tags and two regions become six rows. That's occasionally what you want and usually not. If the two lists are positionally paired, explode the first with an index column, explode the second the same way, then filter to the rows where the two indexes agree.
My list is a JSON array in the cell. Does that work?
Splitting on , would leave you with brackets and quotes. Use a regex separator of "\s*,\s*" if the values are quoted strings, then strip the outer [" and "] with find and replace. For anything more nested, convert the whole file with TSV to JSON and handle it there.
Privacy
100% client-side. No upload. See the privacy policy.