Working With Text — Python Primer

Text columns are where categories multiply, joins fail, and models learn that UK and United Kingdom are unrelated countries.

Most of the work is normalisation, and most of that is three method calls.

It is easy to dismiss this as tidying up. It is not. Ever wonder how Siri works out that “text my wife I’m running late” is one instruction rather than four words and a complaint? A great deal of what makes that possible is unglamorous normalisation happening before anything clever gets a look in. Garbage in, garbage out is the oldest idiom in computing, and it has aged annoyingly well.

The .str Accessor

Every string method you know works on a whole column through .str:

s = pd.Series(['  New York ', 'TOKYO', 'paris  '])

s.str.strip()                  # remove surrounding whitespace
s.str.lower()                  # lowercase
s.str.len()                    # length of each
s.str.contains('del')          # boolean mask
s.str.replace('a', 'A')
s.str.startswith('D')
s.str.split(',')               # gives a column of lists

These run in optimised code rather than a Python loop, so prefer them over .apply(lambda x: ...) whenever one exists.

Chain them, which is how nearly all cleaning is written:

df['city'] = df.city.str.strip().str.lower()

Missing values propagate rather than raising, which is usually what you want:

pd.Series(['Tokyo', None]).str.upper()
# 0    TOKYO
# 1     None

Finding the Problem

Before normalising, look at what is actually there:

df.city.value_counts(dropna=False)

A typical result:

new york           412
New York           203
NEW YORK            47
 New York           12
New York City        8
new york             3

Six categories, one city. And the last one is not a duplicate of the first, it has a trailing space you cannot see.

Make invisible characters visible:

df.city.head().apply(repr)
# "'New York '"  ← the trailing space is now obvious
# "'Tokyo'"

repr is the tool for this. A plain print hides exactly the characters that cause the problem.

The Normalisation Ladder

Apply these in order, checking the unique count as you go.

s = df.city

print(s.nunique())                          # 47
s = s.str.strip();          print(s.nunique())   # 41  — whitespace
s = s.str.lower();          print(s.nunique())   # 29  — case
s = s.str.replace(r'\s+', ' ', regex=True)
                            print(s.nunique())   # 27  — internal spacing

Three lines took forty-seven categories to twenty-seven, and none of them required any thought about the data.

For accented text, normalise the Unicode form as well. The same visible character can be stored two different ways, and they compare as unequal:

import unicodedata

def fold(s):
    """Strip accents so São Paulo and Sao Paulo compare equal."""
    return (s.str.normalize('NFKD')
             .str.encode('ascii', errors='ignore')
             .str.decode('utf-8'))

df['city_key'] = fold(df.city.str.strip().str.lower())

Keep this as a separate _key column rather than overwriting the original. You want to join and group on the key while still displaying the real name.

Mapping the Remainder

Automatic normalisation gets you most of the way. The rest needs a decision, and the decision should be written down as data:

CITY_FIXES = {
    'new york city': 'new york',
    'nyc': 'new york',
    'peking': 'beijing',
    'saigon': 'ho chi minh city',
    'constantinople': 'istanbul',
}

df['city_key'] = df.city_key.replace(CITY_FIXES)

A dictionary is far better than a chain of if statements. It can be reviewed by a domain expert, tested, and loaded from a file when it grows.

Check what remains unmapped against a known list:

known = {'new york', 'tokyo', 'paris', 'london'}
unknown = set(df.city_key) - known
print(sorted(unknown)[:20])

Run this every time the data refreshes. New spellings appear constantly.

Regular Expressions, Sparingly

A regular expression is a pattern for matching text. They are powerful and they become unreadable quickly, so use them where they earn their place and not elsewhere.

The pieces you will actually use:

Pattern	Matches
`\d`	Any digit
`\w`	Letter, digit or underscore
`\s`	Whitespace
`.`	Any character
`+`	One or more of the preceding
`*`	Zero or more
`?`	Optional
`^` `$`	Start, end of string
`[abc]`	Any one of a, b, c
`(...)`	A capture group

Always write patterns as raw strings with r'...', so that backslashes mean what they say.

Extracting

s = pd.Series(['Order #1234 (2026)', 'Order #5678 (2025)'])

s.str.extract(r'#(\d+)')                    # one column: 1234, 5678
s.str.extract(r'#(\d+) \((\d{4})\)')        # two columns
s.str.extract(r'#(?P<order>\d+) \((?P<year>\d{4})\)')   # named columns

Named groups are worth the extra characters. df.order is readable where df[0] is not.

extract takes the first match. extractall takes every match, giving you one row per occurrence.

Cleaning

# Digits only, for a phone column
df['phone'] = df.phone.str.replace(r'\D', '', regex=True)

# Currency to a number
(df.amount.str.replace(r'[£$,]', '', regex=True)
          .astype(float))

# Collapse repeated whitespace
df['notes'] = df.notes.str.replace(r'\s+', ' ', regex=True).str.strip()

Testing

valid_email = df.email.str.match(r'^[^@\s]+@[^@\s]+\.[a-z]{2,}$', case=False)
df.loc[~valid_email.fillna(False), 'email'].head(20)

That email pattern is deliberately loose. A fully correct one is famously enormous and still gets edge cases wrong. Validate loosely, then send a confirmation message, which is the only real test.

Keeping them readable

When a pattern gets long, use verbose mode and comment it:

import re

pattern = re.compile(r"""
    ^(?P<code>[A-Z]{3})     # three-letter product code
    -
    (?P<year>\d{4})         # four-digit year
    -
    (?P<seq>\d+)$           # sequence number
""", re.VERBOSE)

df[['code', 'year', 'seq']] = df.sku.str.extract(pattern)

If a pattern is longer than about forty characters and has no comments, you have written something you will not understand in March.

Splitting and Joining

# Split into columns
df[['first', 'last']] = df.full_name.str.split(' ', n=1, expand=True)

# Split into rows — one row per tag
df.assign(tag=df.tags.str.split(',')).explode('tag')

# Combine columns
df['full'] = df.first + ' ' + df.last

n=1 limits the number of splits, which matters for names where the surname contains a space. expand=True gives columns instead of a column of lists.

Concatenating columns propagates missing values: if first is NaN, full is NaN. To treat missing as empty:

df['full'] = df.first.fillna('') + ' ' + df.last.fillna('')

Fuzzy Matching

When keys nearly match and no amount of normalisation closes the gap.

from difflib import get_close_matches

known = ['new york', 'tokyo', 'paris', 'london']

def best_match(value, options, cutoff=0.8):
    matches = get_close_matches(value, options, n=1, cutoff=cutoff)
    return matches[0] if matches else None

best_match('tokio', known)      # 'tokyo'  — one letter out
best_match('nairobi', known)    # None     — genuinely absent

Two rules if you use this. Never apply it silently. Produce a mapping table, have a human review it, then apply the reviewed table. And keep the original value, because you will be asked which records were altered.

review = (pd.DataFrame({'raw': sorted(unknown)})
            .assign(suggested=lambda d: d.raw.apply(best_match, options=known)))
review.to_csv('name_review.csv', index=False)     # send it to someone

The Text Type

Use the dedicated string dtype rather than object:

df['city'] = df.city.astype('string')

You get proper missing-value handling with pd.NA, errors when you accidentally mix types, and clearer intent. The object dtype means “arbitrary Python objects” and is nearly always a sign that something was not parsed properly.

What Carries Forward

.str.strip().str.lower() fixes more category duplication than anything else. Apply it before every text join and every groupby.

Use repr to see what is really in the column. Trailing spaces are invisible until you look properly.

Keep a separate _key column for joining and grouping, and preserve the original for display.

Put manual fixes in a dictionary, not in code branches. It can be reviewed and tested.

Never apply fuzzy matching without human review.

Next: dates and times, which have their own separate set of ways to be wrong.