All lessons

Module 3: Cleaning data · Lesson 3.4 · about 20 min

Cleaning text with .str

Tidy messy text columns with the .str methods: strip spaces, fix capitals, replace characters, split one column into several, extract patterns with regular expressions, and standardise spelling variants.

Key points, meanings and formulas on one page: choose "Save as PDF" in the print box.

Module 3 · about 20 minutes · uses Lesson 3.3. You’ll be able to clean and split text columns so that the same thing is always written the same way.

The .str methods clean a whole text column at once: trim spaces, fix capitals, replace, split and map variants to one standard spelling.

In everyday terms: sorting post where people wrote “Harbourview”, ” harbourview” and “HARBOUR VIEW”. A careful clerk tidies each into one form before counting letters per street.

1. Spaces and capitals

Pythonorders_messy.csv

Every .str method works on the whole column: strip, lower, upper, title, replace, startswith, contains, len, split, extract. Missing values stay missing.

2. Replace, split and extract

Python

Regular expressions (regex=True, or always in extract and contains) describe patterns: \d a digit, \D a non-digit, + one or more, ^ start, $ end, brackets capture a part. You only need a handful; look up the rest when you need them.

3. Standardise variants with a mapping

When cleaning leaves known variants (abbreviations, old names), map them to one form:

Python

map(fix) gives NaN for anything not in the dictionary, and fillna(s) keeps the original for those.

str.strip()
Remove spaces at both ends.
str.lower() / str.title()
Make capitals consistent.
str.replace(a, b)
Swap text.
str.split(sep, expand=True)
Split into columns.
map({...}) / replace({...})
Turn known variants into one standard value.
Physical meaning
To a computer "S01 " and "S01" are different stores. Every unseen variant splits one group into two and makes its totals too small.

“value_counts() shows 7 stores, so the names are clean.” Look at the list: “Harbourview” and “harbourview ” count as two. Clean, then count again.

Watch it happen, one step at a time:

Raw.
store
harbourview
Harbourview
HARBOURVIEW
Pine Lake
Strip. .str.strip() removes the outer spaces.
Title case. .str.title() → Harbourview, Harbourview, Harbourview, Pine Lake.
Count. value_counts(): Harbourview 3, Pine Lake 1. Before cleaning it said 4 different stores.

4. Your turn

Your turnorders_messy.csv
Your turn
How it works

Principle: computers compare text character by character, so 'Maple Ridge', ' Maple Ridge' and 'maple ridge' are three different values. Every count, group and join treats them as separate. Clean text before grouping or joining, then check with value_counts() that the variants are gone.

Order of steps: strip → case → replace characters → map known variants → check.

5. Practice

  1. Find all product names containing “oil”, ignoring case.
  2. Keep only the first word of each name.
  3. Remove everything except digits from '$1,299.00' style text, then divide by 100.
Answers
  1. df[df['product'].str.contains('oil', case=False)].
  2. df['name'].str.split().str[0].
  3. s.str.replace(r'\D', '', regex=True).astype(int) / 100.

New words: .str accessor, strip, title case, split, extract, regular expression, mapping.

  • Use .str methods on whole text columns.
  • strip, then lower or title, before counting or joining.
  • Map known variants to one standard spelling.
  • Re-check with value_counts() after cleaning.

Check yourself

1'Maple Ridge', ' maple ridge' and 'MAPLE RIDGE' should be one store. Which fixes it?
2How do you split 'Toronto, ON' into city and province columns?
3What does str.extract(r"(\d+)") do?

Sources

Your ticks stay in this browser. Save them as a file on the course home.

Updated 2026-11-22. All datasets are made up. Python runs in your browser (the first run downloads it once, about 20 MB); nothing you type is sent anywhere.