String Cleaning

Split Text Column

Break a combined "City, ST" column into separate city and state columns. By hand, loop over the strings, split each on ', ', and collect the parts as a tuple. With pandas, Series.str.split(', ', expand=True) returns a DataFrame of the pieces directly.

By hand

With pandas

df['loc'].str.split(', ', expand=True) returns a DataFrame where column 0 holds the city and column 1 holds the state. Renaming the columns to ['city', 'state'] makes the result self-describing. The snapshot shows both columns and the zipped RESULT.

naive.py 
locs = ['Austin, TX', 'Denver, CO', 'Miami, FL', 'Boston, MA']
result = []
for loc in locs:
    parts = loc.split(', ')
    result.append((parts[0], parts[1]))
print('RESULT:', result)
library.py 
import pandas as pd
from dalib.display import set_display
set_display()

locs = ['Austin, TX', 'Denver, CO', 'Miami, FL', 'Boston, MA']
df = pd.DataFrame({'loc': locs})
split = df['loc'].str.split(', ', expand=True)
split.columns = ['city', 'state']
result = list(zip(split['city'].tolist(), split['state'].tolist()))
print('columns:', split.columns.tolist())
print('city:', split['city'].tolist())
print('state:', split['state'].tolist())
print('RESULT:', result)

columns: ['city', 'state']
city: ['Austin', 'Denver', 'Miami', 'Boston']
state: ['TX', 'CO', 'FL', 'MA']
RESULT: [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL'), ('Boston', 'MA')]

Implementation notes

  • expand=True is what returns a DataFrame instead of a Series of lists. Without it, each cell would be a Python list — useful when the number of pieces varies per row.
  • Pass n=1 to limit splitting to the first delimiter and keep the remainder in the second column: str.split(', ', n=1, expand=True).
  • Cross-reference: parse-csv-rows (python-data-basics) for the same split-and-unpack pattern applied to raw CSV lines.