SyntaxPilot
Courses/Data Analytics with Python/Lesson 21

Data Analytics with Python · Lesson 21 of 90 · 10 min

Cleaning 3: text data and regular expressions

.str accessorstriploweruppertitlereplacesplitexpand=Truestr.extractstr.findallregular expression (regex)character classquantifiercapture group

These are the notes for Data Analytics with Python, episode 21. Numbers are tidy; people's typing is not. Here we clean text typed by real people: the .str accessor (strip, lower, upper, title, replace, split with expand=True), standardising categories, and regular expressions (regex) from zero: character classes, quantifiers, capture groups, anchors, with str.extract, str.findall, str.contains and str.fullmatch. We finish with fuzzy matching for typos (difflib, and the rapidfuzz library). Every term is defined the first time it appears and collected in the Glossary. Every program below was run for real (Python 3.13, pandas 3.0.5); outputs are unedited.

The data: a hand-filled sign-up sheet

Ramesh started a loyalty card. Customers wrote their name, city, a note (often with a phone number) and a card code. The sheet uses | as the separator (sep="|"), because the notes contain commas:

import sys; sys.tracebacklimit = 0
import pandas as pd, io
raw = """name|city|note|code
 ravi KUMAR |Pune|call me on 98765 43210|CH-020
Priya sharma|pune |ph: 9876543210, 2 chai 1 samosa|SM-015
ANIL  mehta|PUNE|+91-98220-11223 after 6pm|CH-020 x2
sneha Patil|Poona|no phone|cf40
"""
cust = pd.read_csv(io.StringIO(raw), sep="|")
print(cust[["name", "city"]])
print(cust["city"].value_counts())
           name   city
0   ravi KUMAR    Pune
1  Priya sharma  pune 
2   ANIL  mehta   PUNE
3   sneha Patil  Poona
city
Pune     1
pune     1
PUNE     1
Poona    1
Name: count, dtype: int64

One city, counted as four. To a computer, Pune, pune (with a space), PUNE and Poona are different values. Some differences are invisible. str.len() counts the characters in each value:

print(cust["city"].tolist())
print(cust["city"].str.len().tolist())
print((cust["city"] == "pune").sum(), "rows equal to 'pune'")
['Pune', 'pune ', 'PUNE', 'Poona']
[4, 5, 4, 5]
0 rows equal to 'pune'

The lowercase pune has 5 characters: a hidden trailing space. So not even that row equals "pune". Invisible spaces are one of the most common reasons a filter or a merge finds nothing.

The .str accessor

A Python string has a .lower() method, so the obvious move is:

print(cust["city"].lower())
AttributeError: 'Series' object has no attribute 'lower'

lower() belongs to one string; a Series is a whole column. An accessor is a doorway on a Series to a group of methods. Series.str gives string methods that run on every value of the column and skip missing values:

city = cust["city"].str.strip().str.lower()
print(city.tolist())
print(cust["name"].str.strip().str.title().tolist())
['pune', 'pune', 'pune', 'poona']
['Ravi Kumar', 'Priya Sharma', 'Anil  Mehta', 'Sneha Patil']
Method Does " ravi KUMAR " →
str.strip() remove spaces (and tabs, newlines) at both ends "ravi KUMAR"
str.lower() all lowercase " ravi kumar "
str.upper() all capitals " RAVI KUMAR "
str.title() capitalise each word " Ravi Kumar "
str.replace(a, b) replace text a with b
str.len() number of characters 12

Each method returns a new Series, so they chain: .str.strip().str.title(). Notice "Anil Mehta" still has two spaces in the middle: strip only touches the ends.

Standardising categories

After tidying, which spellings are still wrong? Compare with the list of allowed values using isin (episode 15); ~ means not:

city = cust["city"].str.strip().str.title()
allowed = ["Pune", "Mumbai", "Nashik"]
print(city[~city.isin(allowed)].tolist())
city = city.replace({"Poona": "Pune"})
print(city.isin(allowed).all())
['Poona']
True

Only Poona is left over, and after mapping every value is allowed (all() is True only if every value is True). No string method knows that Poona is the old name of Pune. That's a business rule, so we write it as a mapping (a dictionary) and use replace (episode 17). Making every spelling of a category use one standard form is called standardising categories:

city = cust["city"].str.strip().str.title()
cust["city"] = city.replace({"Poona": "Pune"})
print(cust["city"].value_counts())
city
Pune    4
Name: count, dtype: int64

Rule of thumb: fix case and spaces with .str, fix known variants (old names, abbreviations like Blr → Bengaluru) with a mapping, and keep the mapping in your data dictionary.

Splitting text into columns

str.split(sep) cuts text at a separator into a list; expand=True puts the pieces in separate columns instead:

name = cust["name"].str.strip().str.title()
print(name.str.split(" ", expand=True))
cust[["first", "last"]] = name.str.split(" ", expand=True)
       0       1      2
0   Ravi   Kumar    NaN
1  Priya  Sharma    NaN
2   Anil          Mehta
3  Sneha   Patil    NaN
ValueError: Columns must be same length as key

Anil's double space produced an empty piece, so there are three columns, and three columns can't go into two names. We need to say "one or more spaces", and a plain string can't say "however many". We need a pattern.

Regular expressions from zero

A traffic officer doesn't memorise every number plate; he knows the shape: two letters, two digits, two letters, four digits (MH 12 AB 1234). A regular expression (regex) is a short description of the shape of some text. pandas (through Python's re module) can then find, extract or replace every piece of text with that shape.

Cheat sheet

Regex Meaning Matches
\d any digit 0–9 7
\D any character that is not a digit a, , -
\s any whitespace (space, tab, newline)
\w a "word" character: letter, digit or _ a, 5, _
. any character except newline x
[6-9] character class: one character from the set 6, 7, 8, 9
[\s-] a space or a dash
+ quantifier: one or more of the previous item \d+ → 2026
* zero or more
? zero or one (optional) colou?r
{9} exactly 9 \d{9}
{2,4} between 2 and 4
( ) capture group: the part you want back (\d{10})
^ anchor: start of the text ^CH
$ anchor: end of the text \d$
\b anchor: a word boundary (edge between a word character and a non-word character) \b2
a|b alternation: a or b \+91|-
\+, \. escaping: a real + or .

Raw strings. Write patterns as r"..." (a raw string): Python then leaves backslashes alone, so r"\d" reaches the regex engine as \d. In pandas string methods, pass regex=True to str.replace when the first argument is a pattern (str.extract, str.findall, str.contains and str.fullmatch treat it as a pattern already).

Fixing the names: \s+

name = cust["name"].str.strip().str.title()
name = name.str.replace(r"\s+", " ", regex=True)
cust[["first", "last"]] = name.str.split(" ", expand=True)
print(cust[["first", "last"]])
   first    last
0   Ravi   Kumar
1  Priya  Sharma
2   Anil   Mehta
3  Sneha   Patil

\s+ means "one or more whitespace characters", so any run of spaces becomes one space. (Plain str.split() with no separator also splits on runs of whitespace.)

Extracting phone numbers

The obvious idea: delete everything that is not a digit (\D):

print(cust["note"].str.replace(r"\D", "", regex=True).tolist())
['9876543210', '987654321021', '9198220112236', '']

Ravi's number survives, but Priya's gets 2 and 1 from her order glued on, and Anil's gets 91 in front and 6 (from "6pm") at the end. Deleting isn't enough: we must describe the shape. Remove the country code first:

print(cust["note"].str.replace("+91", "", regex=True))
re.PatternError: nothing to repeat at position 0

In regex, + means "one or more of the previous thing", and at position 0 there is no previous thing. Escape it as \+ (or pass regex=False to treat the text literally). Then remove spaces and dashes with a character class, and use str.extract, which returns what the capture group matched (the first match in each value):

note = cust["note"].str.replace(r"\+91", "", regex=True)
note = note.str.replace(r"[\s-]", "", regex=True)
print(note.tolist())
cust["phone"] = note.str.extract(r"([6-9]\d{9})")
print(cust[["name", "phone"]])
['callmeon9876543210', 'ph:9876543210,2chai1samosa', '9822011223after6pm', 'nophone']
           name       phone
0   ravi KUMAR   9876543210
1  Priya sharma  9876543210
2   ANIL  mehta  9822011223
3   sneha Patil         NaN

[6-9]\d{9} reads: one digit from 6 to 9, then exactly nine digits, i.e. a 10-digit Indian mobile number. Digits that don't have that shape (2, 1, 6) are ignored, and no match gives NaN. str.extract returns a DataFrame with one column per group; with one group, assigning it to one column works as above.

Every match: str.findall, and word boundaries

str.extract gives the first match; str.findall gives every match, as a list:

print(cust["note"].str.findall(r"\d+ [a-z]+").tolist())
print(cust["note"].str.findall(r"\b\d [a-z]+").tolist())
[[], ['2 chai', '1 samosa'], ['11223 after'], []]
[[], ['2 chai', '1 samosa'], [], []]

The first pattern ("digits, a space, letters") also grabbed 11223 after from Anil's phone number. Regex does exactly what you wrote, not what you meant. \b\d means "a single digit that starts a word", which excludes the tail of a longer number.

Validation with anchors, and two capture groups

Card codes should be exactly two capital letters, a dash and three digits. str.contains(pat) is True if the pattern appears anywhere; anchors ^ and $ force the whole value to match:

pat = r"[A-Z]{2}-\d{3}"
print(cust["code"].str.contains(pat).tolist())
print(cust["code"].str.contains("^" + pat + "$").tolist())
parts = cust["code"].str.extract(r"^([A-Z]{2})-(\d{3})$")
parts.columns = ["item", "price"]
print(parts)
[True, True, True, False]
[True, True, False, False]
  item price
0   CH   020
1   SM   015
2  NaN   NaN
3  NaN   NaN

Without anchors, CH-020 x2 passes because the pattern is inside it. With anchors it fails, and so does cf40. str.fullmatch(pat) is the same as wrapping the pattern in ^…$. Two capture groups give two columns. The extracted parts are still text ("020"); convert with pd.to_numeric if needed.

Fuzzy matching typos

masla chai, Samosaa, cofee: no regex can guess those. Fuzzy matching scores how similar two strings are and picks the closest candidate above a cutoff (a minimum similarity score, 0 to 1). Python's built-in difflib module has get_close_matches:

import pandas as pd, difflib
menu = ["Masala Chai", "Samosa", "Coffee", "Ginger Chai"]
def closest(text):
    m = difflib.get_close_matches(text.title(), menu, n=1, cutoff=0.8)
    return m[0] if m else None
typed = pd.Series(["masla chai", "Samosaa", "cofee", "Vada Pav"])
print(pd.DataFrame({"typed": typed, "match": typed.map(closest)}))
        typed        match
0  masla chai  Masala Chai
1     Samosaa       Samosa
2       cofee       Coffee
3    Vada Pav          NaN

n=1 asks for the single best match; Vada Pav is not on the menu and nothing scores above 0.8, so it stays unmatched instead of being forced onto the wrong item. For large tables, the third-party rapidfuzz library (pip install rapidfuzz) does the same kind of matching much faster, with several scoring methods. A higher cutoff means fewer but safer matches. Always review fuzzy matches by eye before trusting them, and add confirmed ones to your mapping.

Mini project: clean_customers()

def clean_customers(df):
    df = df.copy()
    name = df["name"].str.strip().str.replace(r"\s+", " ", regex=True)
    df[["first", "last"]] = name.str.title().str.split(" ", expand=True)
    df["city"] = df["city"].str.strip().str.title().replace({"Poona": "Pune"})
    note = df["note"].str.replace(r"\+91|[\s-]", "", regex=True)
    df["phone"] = note.str.extract(r"([6-9]\d{9})")
    df["code_ok"] = df["code"].str.fullmatch(r"[A-Z]{2}-\d{3}")
    return df[["first", "last", "city", "phone", "code_ok"]]
print(clean_customers(cust))
   first    last  city       phone  code_ok
0   Ravi   Kumar  Pune  9876543210     True
1  Priya  Sharma  Pune  9876543210     True
2   Anil   Mehta  Pune  9822011223    False
3  Sneha   Patil  Pune         NaN    False

\+91|[\s-] uses alternation: remove "+91" or any space or dash, in one pass. The two False codes go to Ramesh's to_check list (episode 18) rather than being guessed.

Common mistakes

Code / habit Real error / symptom Why Fix
cust["city"].lower() AttributeError: 'Series' object has no attribute 'lower' lower is for one string cust["city"].str.lower()
str.split(" ", expand=True) into 2 columns ValueError: Columns must be same length as key a double space made 3 pieces str.replace(r"\s+", " ", regex=True) first
str.replace("+91", "", regex=True) re.PatternError: nothing to repeat at position 0 + is a quantifier r"\+91" or regex=False
str.replace(r"\D", "") to get phones no error; extra digits glued on deleting isn't describing the shape str.extract(r"([6-9]\d{9})")
str.contains(r"[A-Z]{2}-\d{3}") to validate CH-020 x2 passes no anchors str.fullmatch(...) or ^…$
"\d" without r SyntaxWarning: invalid escape sequence '\d' Python reads backslashes first always r"..."
Trusting every fuzzy match wrong item merged similar ≠ same cutoff + review by eye

Practice

All practice questions use this sheet:

import pandas as pd, io
raw = """student|phone|pin|email
 asha RAO |Mob: 98450 12345|Pune 411001|asha.rao@school.in
RAVI  k|+91 9900112233|411 038|ravi@@mail
meena|landline 020-2567 8910|PIN-411045|meena_s@school.in
"""
s = pd.read_csv(io.StringIO(raw), sep="|")

1. Clean student: trim, single spaces, title case.

Solution
print(s["student"].str.strip().str.replace(r"\s+", " ", regex=True).str.title().tolist())
['Asha Rao', 'Ravi K', 'Meena']

2. Extract 10-digit mobile numbers (landlines should give NaN).

Solution
ph = s["phone"].str.replace(r"\+91|[\s-]", "", regex=True)
print(ph.str.extract(r"([6-9]\d{9})")[0].tolist())
['9845012345', '9900112233', nan]

[0] picks the extracted column (named 0) from the DataFrame extract returns.

3. Extract the 6-digit PIN code (some have a space inside).

Solution
pin = s["pin"].str.replace(" ", "")
print(pin.str.extract(r"(\d{6})")[0].tolist())
['411001', '411038', '411045']

4. Validate emails with a simple pattern: word characters or dots, @, a word, a dot, lowercase letters.

Solution
print(s["email"].str.fullmatch(r"[\w.]+@[\w]+\.[a-z]+").tolist())
[True, False, True]

Real email validation is much more complex; a simple pattern catches obvious junk like ravi@@mail.

5. What does str.findall(r"\d+") return for the phone column, and why is it not enough to get phone numbers?

Solution
print(s["phone"].str.findall(r"\d+").tolist())
[['98450', '12345'], ['91', '9900112233'], ['020', '2567', '8910']]

Numbers are split wherever a space or dash appears, and the country code is a separate match. Remove separators first, then extract the full shape.

Quick quiz

s = pd.Series(["a1b22"])
print(s.str.findall(r"\d+")[0])

A. ['1', '2', '2'] B. ['1', '22'] C. ['122'] D. ['a', 'b']

Answer

B. ['1', '22']. \d+ means one or more digits in a row, so the two 2s form one match. \d alone would give ['1', '2', '2'].

Interview questions

  1. What is the .str accessor? A namespace of vectorised string methods on a Series (strip, lower, replace, split, extract, contains...) that work on every value and skip missing values.
  2. How do you standardise a messy category column? Normalise case and whitespace with .str, map known variants with a dictionary (replace/map), fuzzy-match remaining typos with review, then validate with isin against the allowed list.
  3. str.extract vs str.findall? extract returns the capture groups of the first match as columns; findall returns a list of all matches per value.
  4. What do ^, $ and \b do? Anchors: start of string, end of string, word boundary. They match positions, not characters.
  5. Why use raw strings for regex in Python? So backslashes reach the regex engine unchanged (r"\d"), instead of being interpreted as Python escape sequences.
  6. How would you extract a 10-digit Indian mobile number from free text? Remove separators and the +91 prefix, then str.extract(r"([6-9]\d{9})").
  7. contains vs fullmatch for validation? contains is True if the pattern appears anywhere; fullmatch requires the whole string to match.
  8. What is a capture group? A part of the pattern in parentheses whose matched text is returned (by extract) or reused.
  9. What's fuzzy matching and when is it risky? Matching strings by similarity score (difflib, rapidfuzz); risky when different items have similar names, so use a cutoff, a known list of valid values, and manual review.
  10. Why does str.replace("+91", "", regex=True) fail? + is a regex quantifier with nothing to repeat; escape it (\+) or use regex=False.

Glossary

Term Meaning Example
Accessor A doorway on a Series to a group of methods .str, .cat, .dt
.str accessor String methods that run on every value of a column col.str.lower()
str.strip() Remove whitespace at both ends " pune " → "pune"
str.lower() / str.upper() All lowercase / all capitals "PUNE" → "pune"
str.title() Capitalise each word "ravi kumar" → "Ravi Kumar"
str.len() Number of characters in each value "pune " → 5
isin(list) True where the value is in the list allowed cities
str.replace(a, b) Replace text or a pattern (regex=True) r"\s+" → " "
Standardising categories Making every spelling of a category one standard form Poona → Pune
Mapping A dictionary from variant to standard value {"Poona": "Pune"}
str.split(sep) Cut text into pieces at a separator "Ravi Kumar" → ["Ravi", "Kumar"]
expand=True Put split pieces in separate columns first, last
Regular expression (regex) A short description of the shape of text [6-9]\d{9}
Raw string r"...": Python leaves backslashes alone r"\d"
\d, \D A digit / anything but a digit 7 / -
\s, \w Whitespace / a word character / a
Character class [...]: one character from a set [6-9]
Quantifier How many times: +, *, ?, {n}, {m,n} \d{9}
Capture group ( ): the part of the match returned (\d{10})
Anchor Matches a position: ^ start, $ end, \b word boundary ^CH
Word boundary The edge between a word character and a non-word character \b2
Alternation a|b: a or b \+91|-
Escaping \ before a special character to mean it literally \+
str.extract(pat) Capture groups of the first match, as columns phone column
str.findall(pat) List of every match per value ['2 chai', '1 samosa']
str.contains(pat) True if the pattern appears anywhere CH-020 x2 → True
str.fullmatch(pat) True if the whole value matches CH-020 x2 → False
re.PatternError Python's error for an invalid regex "+91"
Fuzzy matching Matching by similarity score, not equality cofee → Coffee
Cutoff Minimum similarity score to accept a match cutoff=0.8
difflib Python's built-in module for comparing sequences get_close_matches
rapidfuzz A fast third-party fuzzy-matching library pip install rapidfuzz

Summary

  • Use .str for column-wide text methods: strip, lower, upper, title, replace, split(expand=True).
  • Standardise categories: case and spaces with .str, known variants with a mapping.
  • Regex describes the shape of text: \d \s \w, character classes [ ], quantifiers + * ? {n}, capture groups ( ), anchors ^ $ \b, alternation |; escape special characters and write patterns as raw strings.
  • str.extract returns the group of the first match, str.findall every match, str.fullmatch (or ^…$) validates whole values.
  • Fuzzy matching (difflib, rapidfuzz) fixes typos against a known list: use a cutoff and review.
  • Next episode: cleaning 4, dates and times.