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

For each location string, call .split(', ') to get a two-element list, then append (parts[0], parts[1]) to result. The trace shows parts refreshing each iteration and result growing with city/state tuples.

naive.py
Replay: real traced execution (multi-file project)
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)

  1. locs ← ['Austin, TX', 'Denver, CO', 'Miami, FL', 'Boston, MA']

    1locs = ['Austin, TX', 'Denver, CO', 'Miami, FL', 'Boston, MA']2result = []
    values this step['Austin, TX', 'Denver, CO', 'Miami, FL', 'Boston, MA']locs

  2. result ← []

    1locs = ['Austin, TX', 'Denver, CO', 'Miami, FL', 'Boston, MA']2result = []3for loc in locs:
    values this step[]result

  3. loc ← 'Austin, TX'

    2result = []3for loc in locs:4    parts = loc.split(', ')
    values this step'Austin, TX'loc

  4. parts ← ['Austin', 'TX']

    3for loc in locs:4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))
    values this step['Austin', 'TX']parts

  5. result ← [('Austin', 'TX')]

    4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))6print('RESULT:', result)
    values this step[] → [('Austin', 'TX')]result

  6. loc ← 'Denver, CO'

    2result = []3for loc in locs:4    parts = loc.split(', ')
    values this step'Austin, TX' → 'Denver, CO'loc

  7. parts ← ['Denver', 'CO']

    3for loc in locs:4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))
    values this step['Austin', 'TX'] → ['Denver', 'CO']parts

  8. result ← [('Austin', 'TX'), ('Denver', 'CO')]

    4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))6print('RESULT:', result)
    values this step[('Austin', 'TX')] → [('Austin', 'TX'), ('Denver', 'CO')]result

  9. loc ← 'Miami, FL'

    2result = []3for loc in locs:4    parts = loc.split(', ')
    values this step'Denver, CO' → 'Miami, FL'loc

  10. parts ← ['Miami', 'FL']

    3for loc in locs:4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))
    values this step['Denver', 'CO'] → ['Miami', 'FL']parts

  11. result ← [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL')]

    4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))6print('RESULT:', result)
    values this step[('Austin', 'TX'), ('Denver', 'CO')] → [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL')]result

  12. loc ← 'Boston, MA'

    2result = []3for loc in locs:4    parts = loc.split(', ')
    values this step'Miami, FL' → 'Boston, MA'loc

  13. parts ← ['Boston', 'MA']

    3for loc in locs:4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))
    values this step['Miami', 'FL'] → ['Boston', 'MA']parts

  14. result ← [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL'), ('Boston', 'MA')]

    4    parts = loc.split(', ')5    result.append((parts[0], parts[1]))6print('RESULT:', result)
    values this step[('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL')] → [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL'), ('Boston', 'MA')]result

  15. for loc in locs:

    2result = []3for loc in locs:4    parts = loc.split(', ')

  16. stdout ← RESULT: [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL'), ('Boston', 'MA')]

    5    result.append((parts[0], parts[1]))6print('RESULT:', result)
    values this stepRESULT: [('Austin', 'TX'), ('Denver', 'CO'), ('Miami', 'FL'), ('Boston', 'MA')]stdout

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.

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.