Tidying Text with .str

Text that people type differs in spaces and capital letters, so the same name fails to match itself; the .str tools strip, lower and title tidy a whole column at once, and len, contains and startswith test every value so you can find the ones that look wrong.

Apprentice · Python · Updated

By

After this lesson you can

  • Tidy a text column with .str.strip, .str.lower and .str.title, and explain why ' maria LOPEZ ' doesn't match 'Maria Lopez'
  • Test every value of a text column with .str.len, .str.contains and .str.startswith

Key takeaways

  • Two pieces of text are equal only when every character matches, spaces and capital letters included, so ' maria LOPEZ ' and 'Maria Lopez' are different values.
  • .str, read "dot s t r", gives a text column the string tools, and each tool works on every value in the column at once.
  • .str.strip() takes the spaces off both ends, .str.lower() makes every letter small, and .str.title() gives each word a capital first letter.
  • .str.len(), .str.contains() and .str.startswith() answer for every value, so you can count or find the values that look wrong.
  • .str.contains() cares about capital letters, so make the column lowercase first when the capitals vary.

Sign-up forms, class rosters, customer lists: whenever people type text themselves, the same name arrives a dozen ways, with a stray space here and capital letters there. Before you can look a person up, count people by town or match one list against another, the text has to agree. You'll tidy text like this in almost every file people filled in by hand.

The example is the Hillcrest 5K Fun Run's registrations, registrations.csv: one row for each of the 450 runners who signed up on the race's web form, typed by the runners themselves. Its columns are bib (the runner's race number), runner_name, email, tshirt_size, hometown, birth_date, registered_at and goal_time. The runners and the race are our own made-up data.

A runner who's there but can't be found

Maria Lopez emails the race to check her registration went through. Look her up by name:

import pandas as pd

reg = pd.read_csv("registrations.csv")
reg[reg["runner_name"] == "Maria Lopez"]
Empty DataFrame
Columns: [bib, runner_name, email, tshirt_size, hometown, birth_date, registered_at, goal_time]
Index: []

No rows. Putting a condition inside square brackets keeps the rows where it's true (Filtering Rows with a Condition teaches it in full), and here it's true nowhere. Her email finds her, and tolist() shows her name in quotes, so every character is visible:

maria = reg[reg["email"] == "maria.lopez@example.com"]
maria["runner_name"].tolist()
['  maria LOPEZ ']

She typed two spaces before her name, one after it, her first name in small letters and her last name in capitals. To Python, two pieces of text are equal only when every character matches, and a space is a character, as is the difference between m and M. So ' maria LOPEZ ' and 'Maria Lopez' are different values, and the lookup misses her.

.str: the string tools for a whole column

A text column has a set of string tools under .str (read "dot s t r"), the same tools a single Python string has, and each one works on every value in the column at once. Three of them tidy names:

  • .str.strip() takes off the spaces (and tabs) at both ends, the whitespace you can't see.
  • .str.lower() makes every letter small.
  • .str.title() gives each word a capital first letter and makes the rest small.

Each returns a new column, so they can follow one another in a chain. Here are the first five names as typed, then tidied:

names = reg["runner_name"]
names.head(5).tolist()
['mateo walsh', 'isaac SILVA', 'Nora Foster', 'Daniel Price', '  Andre Ali ']
names.str.strip().str.title().head(5).tolist()
['Mateo Walsh', 'Isaac Silva', 'Nora Foster', 'Daniel Price', 'Andre Ali']

Use .str.title() for names people will read, and .str.lower() when you only compare values, such as emails, where capitals don't matter. title capitalizes after every space, hyphen and apostrophe, so garcia-lee becomes Garcia-Lee and o'neil becomes O'Neil, but a name such as McKenzie would come out Mckenzie. Check a few names by eye after any automatic tidy.

Measuring text: .str.len

How many names had stray spaces? .str.len() gives the number of characters in each value. A name longer before strip() than after it had spaces at an end:

spaces = names.str.len() - names.str.strip().str.len()
print("names with extra spaces:", (spaces > 0).sum())
names with extra spaces: 97

Ninety-seven of 450 names, more than one in five. Store the tidy names, and the lookup works:

reg["runner_name"] = names.str.strip().str.title()
reg[reg["runner_name"] == "Maria Lopez"][["bib", "runner_name", "hometown"]]
     bib  runner_name   hometown
320  421  Maria Lopez  Hillcrest

Testing text: .str.contains and .str.startswith

.str.contains("@") answers True or False for each value: does it hold that text anywhere? An email without an @ can't reach anyone:

has_at = reg["email"].str.contains("@")
has_at.value_counts()
email
True     446
False      4
Name: count, dtype: int64
reg[has_at == False][["bib", "email"]]
     bib                      email
271  372    priya.smith.example.com
354  455  sofia.bennett.example.com
386  487     sofia.diaz.example.com
388  489   andre.larsen.example.com

Four runners typed a dot where the @ goes. Now the race knows exactly whom to call.

.str.startswith("Cedar") answers whether each value begins with that text. Tidy the towns first, then look at every town that starts with "Cedar":

towns = reg["hometown"].str.strip().str.title()
towns[towns.str.startswith("Cedar")].value_counts()
hometown
Cedar Grove    70
Cedar Grv       6
Name: count, dtype: int64

strip and title fixed the spaces and the capitals, but six runners abbreviated the town. That's a spelling difference, not a tidying one, and Making Categories Match: replace and map fixes it.

One trap: contains and startswith care about capital letters, as == does. Lowercase the column first when capitals vary:

print("contains 'hill':", reg["hometown"].str.contains("hill").sum())
print("lowercase first:", reg["hometown"].str.lower().str.contains("hill").sum())
contains 'hill': 27
lowercase first: 189

Check yourself

2 questions to try on the lesson page.

So what

Text typed by people only matches once it's tidy: .str.strip(), .str.lower() and .str.title() fix a whole column in one line, and .str.len(), .str.contains() and .str.startswith() find the values that still look wrong.

Key terms

Related