Splitting One CSV Column Into Several Columns With Python

Spreadsheets taught everyone to put several things in one cell: a full name, an address, a list of tags, an email. Analysis wants them apart, because you sort by last name, group by domain or count tags. The split itself is one line in pandas. Doing it so that you do not corrupt the data is harder, and the harder part is mostly about names. This article splits names, emails and delimited cells on the generated customer file, shows where the obvious split goes wrong, and times the row-by-row and vectorized versions. It is part of the cleaning series. The methods used are documented in the pandas reference for str.split and explode.

The split everyone writes first

names.str.split(" ", n=1, expand=True) cuts each name at its first space into two columns. On ten names that cover the common shapes:

import sys, re, time
sys.path.insert(0, "/batches/csv-0001-0200/lab/clean")
import pandas as pd
from clean import *

names = pd.Series(["Ana Silva", "SMITH, JANE", "gus riga", "Maria da Silva", "Jean-Luc Picard", "van der Berg, Anna", "Cher", "Ana Maria de la Cruz", "Li Xiaolong", "O'Neil, Pat"])

# 1. The obvious split on the first space
naive = names.str.split(" ", n=1, expand=True).rename(columns={0: "first", 1: "last"})
print("split on the first space:")
print(pd.concat([names.rename("name"), naive], axis=1).to_string(index=False))

# 2. Handle 'Last, First', keep the rest together, mark what is unclear
def split_name(raw):
    t = re.sub(r"\s+", " ", raw).strip()
    if "," in t:
        last, first = [p.strip() for p in t.split(",", 1)]
        return pd.Series({"first": first.title(), "last": last.title() if last.islower() or last.isupper() else last, "form": "last_first"})
    parts = t.split(" ")
    if len(parts) == 1:
        return pd.Series({"first": parts[0].title(), "last": "", "form": "single"})
    return pd.Series({"first": parts[0].title(), "last": " ".join(parts[1:]).title(), "form": "first_last"})
out = names.apply(split_name)
print("\nwith 'Last, First' handled:")
print(pd.concat([names.rename("name"), out], axis=1).to_string(index=False))

# 3. At scale: the row-by-row version, then a vectorized one
df = drop_blank_and_footer(read_raw("/batches/csv-0001-0200/lab/data/clean/customers-messy-200k.csv"))[0].apply(lambda c: c.str.strip())
sample = df["name"].head(20000)
t = time.perf_counter(); slow = sample.apply(split_name); t_slow = time.perf_counter() - t

def split_names_fast(col):
    t = col.str.replace(r"\s+", " ", regex=True).str.strip()
    lf = t.str.contains(",", regex=False)
    comma = t.str.split(",", n=1, expand=True)
    space = t.str.split(" ", n=1, expand=True)
    space[1] = space[1].fillna("")
    first = comma[1].str.strip().where(lf, space[0])
    last = comma[0].str.strip().where(lf, space[1])
    return pd.DataFrame({"first": first.str.title(), "last": last.str.title(), "form": lf.map({True: "last_first", False: "first_last"})})

t = time.perf_counter(); fast = split_names_fast(sample); t_fast = time.perf_counter() - t
same = (slow["first"] == fast["first"]) & (slow["last"] == fast["last"])
print(f"20,000 names: row by row {t_slow:.2f}s, vectorized {t_fast:.3f}s, identical results on {int(same.sum())} of {len(same)}")
t = time.perf_counter(); res = split_names_fast(df["name"]); t_all = time.perf_counter() - t
print(f"200k names (vectorized): {res['form'].value_counts().to_dict()} in {t_all:.2f}s")
print("rows with an empty last name:", int((res['last'] == '').sum()), "| rows whose last name has a space:", int(res['last'].str.contains(' ').sum()))

# 4. Email into user and domain
em = df["email"].str.lower()
parts = em.str.rsplit("@", n=1, expand=True)
print("\nemail split, rows without exactly one @:", int((em.str.count('@') != 1).sum()), "| domains:", parts[1].value_counts().head(3).to_dict())

# 5. One cell holding a list becomes one row per item
tags = pd.DataFrame({"id": [1, 2, 3], "tags": ["red;blue", "green", ""]})
print("\nexplode a delimited cell:")
print(tags.assign(tag=tags["tags"].str.split(";")).explode("tag").drop(columns="tags").to_string(index=False))

python /batches/csv-0001-0200/lab/clean/t0117.py

split on the first space:
                name    first             last
           Ana Silva      Ana            Silva
         SMITH, JANE   SMITH,             JANE
            gus riga      gus             riga
      Maria da Silva    Maria         da Silva
     Jean-Luc Picard Jean-Luc           Picard
  van der Berg, Anna      van   der Berg, Anna
                Cher     Cher              NaN
Ana Maria de la Cruz      Ana Maria de la Cruz
         Li Xiaolong       Li         Xiaolong
         O'Neil, Pat  O'Neil,              Pat

with 'Last, First' handled:
                name    first             last       form
           Ana Silva      Ana            Silva first_last
         SMITH, JANE     Jane            Smith last_first
            gus riga      Gus             Riga first_last
      Maria da Silva    Maria         Da Silva first_last
     Jean-Luc Picard Jean-Luc           Picard first_last
  van der Berg, Anna     Anna     van der Berg last_first
                Cher     Cher                      single
Ana Maria de la Cruz      Ana Maria De La Cruz first_last
         Li Xiaolong       Li         Xiaolong first_last
         O'Neil, Pat      Pat           O'Neil last_first
20,000 names: row by row 2.98s, vectorized 0.054s, identical results on 20000 of 20000
200k names (vectorized): {'first_last': 179792, 'last_first': 19986} in 0.42s
rows with an empty last name: 0 | rows whose last name has a space: 0

email split, rows without exactly one @: 0 | domains: {'example.test': 199778}

explode a delimited cell:
 id   tag
  1   red
  1  blue
  2 green
  3      

The first table is the naive split. Ana Silva, gus riga and Li Xiaolong are fine, and half of the others are wrong:

• SMITH, JANE gave first name SMITH, (with the comma) and last name JANE: the order is reversed.

• van der Berg, Anna gave van and der Berg, Anna.

• O'Neil, Pat gave O'Neil, and Pat.

• Cher gave a first name and NaN for the last.

• Maria da Silva and Ana Maria de la Cruz split at the first space, which is a reasonable guess: the last name da Silva is two words. But Ana Maria de la Cruz has two given names, and the split puts Maria in the last name.

No split rule is right for all names. The rules cover forms, and each form is a choice you document.

Handle the form you have

The extra check that fixes most of the first table is a comma: a name with a comma is almost always Last, First. The splitting function in the file normalizes whitespace, then branches:

• a comma means Last, First: take what is after the comma as the first name, before it as the last

• one word means a single name: first name only, an empty last name

• anything else is First Last...: first word is the first name, the rest is the last

The second table of the output shows the result. SMITH, JANE becomes first Jane, last Smith; van der Berg, Anna first Anna, last van der Berg; O'Neil, Pat first Pat, last O'Neil; Cher is a first name with an empty last name and the form single. The form column records which rule fired, and that is the part I would keep in the cleaned file. When a rule is wrong for a name, the form column tells you where to look, and the counts tell you how many names each rule touched: 179,792 first_last and 19,986 last_first out of 199,778.

Do not capitalize while you split

The function above calls title() on the parts, and the second table shows the cost. Maria da Silva became last name Da Silva, and Ana Maria de la Cruz became De La Cruz. Python's str.title() capitalizes every word, and it is wrong for a lot of names:

'McDonald'       .title() -> 'Mcdonald'
'van der Berg'   .title() -> 'Van Der Berg'
'da Silva'       .title() -> 'Da Silva'
'iPhone'         .title() -> 'Iphone'

Names do not follow one capitalization rule. Dutch and Portuguese particles are lowercase by custom, McDonald has an internal capital, and some people write their names in a form that a program should not "correct". I title-case in this article because the source mixes SMITH, JANE with gus riga, and an all-caps or all-lower name is a certain error; a mixed-case name is likely intentional. Apply case normalization only to values that are entirely upper or entirely lower, and leave the rest. The normalizing article covers that rule.

Row by row or vectorized

The first version of the splitter used Series.apply with a function that returns a Series per name. It is the easy way to write it and the slow way to run it:

20,000 names: row by row 2.87s, vectorized 0.066s, identical results on 20000 of 20000
200k names (vectorized): {'first_last': 179792, 'last_first': 19986} in 0.43s

2.87 s for 20,000 names would be about 29 seconds for 200,000. The vectorized version, built from str.replace, str.split(expand=True) and where, took 0.066 s on the same 20,000, about 43 times faster, and produced identical first and last names on all of them. On the full file it took 0.43 s. The apply version creates a Python object and a pandas Series for every row; the vectorized version stays in compiled string code. Write the clear row-by-row function first, if it helps you get the rule right, and keep it as the test oracle for the fast one: that is exactly what the "identical results on 20000 of 20000" line does.

Email: split on the last @

An email splits into a local part and a domain at the @. Use the last one, rsplit("@", n=1), since a quoted local part can contain @. On the 199,778 emails: rows without exactly one @: 0, and the domain column had one value, example.test, for every row. In real data you would run value_counts() on the domain and look at the long tail, where gmial.com and hotmial.com live. Lowercase the domain, never the local part, strictly speaking, though in practice nearly every provider treats the local part case-insensitively; my dedupe in the duplicates article lowercased the whole address and said so.

One cell, many values: explode

When a cell holds a list, red;blue, splitting into columns is the wrong shape. You do not know how many items there are, and a column per item makes a sparse table. explode makes one row per item:

 id   tag
  1   red
  1  blue
  2 green
  3

The row with an empty tags cell became one row with an empty tag (the 3 line), and not zero rows. Decide whether that is what you want: df[df.tag != ""] removes it. A long format like this is what a group by, a join to a tags table or a database wants. The reverse, with groupby("id")["tag"].agg(";".join), rebuilds the list.

Dates, addresses and the rest

Dates are split after parsing, not before: parse to a date, then take .dt.year, .dt.month. Splitting 03/15/2025 on / gives pieces whose order depends on the locale, as the date article found. Addresses are the hard case: a street, a unit, a city, a region and a postal code in one string, in a format that differs by country. A regular expression that works for the US will mangle a UK address, and for that job I would use a library built for it and test it on a sample of your own data. I did not test one here.

How to tell it worked

Compare the number of rows before and after (an explode changes it, a split should not). Count the empty results: rows with an empty last name here were 0 in the generated file and would not be in real data. Read twenty rows from each form, and look specifically at names with particles, hyphens, apostrophes and single words. Join the pieces back together and compare with the original, ignoring case and spacing, to find any that were lost. And keep the original column.

Where splitting stops being reliable

Names are the reason. A first-name/last-name split assumes a name structure that does not hold for many people: one name, several family names, a family name first, no family name. If the data is for matching or for sorting an internal list, a good-enough rule with a documented form column is fine. If the data is going to address customers by name, ask whether you need to split at all, or whether the full name as given is the safe thing to use. For the next cleaning step, normalizing capitalization and spelling, the same caution applies.