Mathew K AnalyticsLesson 19 · Pandas Full Course
Clean Text in Pandas with .str: split, contains, extract and Regex | Pandas Course #19
In this lesson you'll be able to: Tidy case and stray spaces with the .str accessor Search text with contains, startswith and endswith Split text into…
- CoursePandas Full Course
- Lesson19 of 65
- FormatJupyter notebook · 42 code cells
- Data3 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- mpg.csv preview20.7 KB
- titanic.csv preview58.9 KB
- netflix_titles.csv preview2.9 MB
📓 Full notebook
Download .ipynbPandas 19: String Operations and Text Cleaning in Pandas#
In this lesson you'll be able to:
- Tidy case and stray spaces with the
.straccessor - Search text with
contains,startswithandendswith - Split text into pieces, and one row per piece with
explode - Pull numbers and words out of text with
extractand patterns Data: Titanic passenger names and the Netflix catalogue
Warm-up: the cars from last lesson#
We converted their units. Their names are text, waiting for today.
import pandas as pd
cars = pd.read_csv("data/pandas/mpg.csv")
cars["l_per_100km"] = (235.215 / cars["mpg"]).round(1)
cars[["name", "mpg", "l_per_100km"]].head(3)
print(cars["name"].dtype)
print(cars["name"].str.len().max(), "characters in the longest")
Clean up case and stray spaces#
Text methods live behind .str, and work on every row at once.
titanic = pd.read_csv("data/pandas/titanic.csv")
names = titanic["Name"]
names.head(3)
print(names.str.upper().head(2).tolist())
print(names.str.lower().head(2).tolist())
print(names.str.len().describe().round(1)[["min", "mean", "max"]])
print(cars["name"].str.title().head(3).tolist())
netflix = pd.read_csv("data/pandas/netflix_titles.csv")
print(netflix.shape)
netflix[["title", "type", "duration", "date_added"]].head(3)
titles = netflix["title"]
tidy = titles.str.strip().str.replace(r"\s+", " ", regex=True)
changed = tidy != titles
print(changed.sum(), "titles changed")
print(titles[changed].tolist())
print(tidy[changed].tolist())
added = netflix["date_added"]
padded = (added.str.strip() != added) & added.notna()
print(padded.sum(), "dates have stray spaces")
print(added[padded].head(2).tolist())
Common mistake: converting text to dates before stripping the spaces.
pd.to_datetime(added, format="%B %d, %Y")
netflix["added"] = pd.to_datetime(added.str.strip(),
format="%B %d, %Y")
netflix["added"].dt.year.astype("Int64").value_counts().head(3)
Search text with contains and startswith#
Each search returns True or False per row, ready for filtering.
xmas = netflix["title"].str.contains("christmas", case=False)
print(xmas.sum(), "titles mention Christmas")
netflix.loc[xmas, "title"].head(3).tolist()
Quick check: how many titles come from India? Does
==orcontainsgive more?
country = netflix["country"]
print((country == "India").sum(), "with ==")
print(country.str.contains("India").sum(), "with contains")
country[country.str.contains("India") & (country != "India")].head(3)
Common mistake: searching for a symbol that has a special meaning.
netflix["title"].str.contains("(")
bracket = netflix["title"].str.contains("(", regex=False)
print(bracket.sum(), "titles contain a bracket")
netflix.loc[bracket, "title"].head(3).tolist()
rating = netflix["rating"]
print(rating.str.startswith("TV-").mean().round(3))
print(netflix["title"].str.endswith("2").sum(), "titles end in 2")
war = netflix["title"].str.contains("war|battle", case=False)
print(war.sum(), "matches")
netflix.loc[war, "title"].head(4).tolist()
pattern = r"\b(?:war|battle)\b"
war = netflix["title"].str.contains(pattern, case=False)
print(war.sum(), "whole-word matches")
netflix.loc[war, "title"].head(4).tolist()
Your turn: how many titles mention love, ignoring capitals?
love = netflix["title"].str.contains("love", case=False)
print(love.sum(), "titles")
netflix.loc[love, "type"].value_counts()
Split text into pieces#
split breaks text at a separator. expand and explode reshape the pieces.
parts = names.str.split(", ")
print(parts.head(2).tolist())
print(parts.str[0].head(3).tolist())
titanic[["surname", "rest"]] = names.str.split(", ", n=1,
expand=True)
titanic[["surname", "rest"]].head(3)
titanic["surname"].value_counts().head(3)
first = titanic["rest"].str.split(". ", n=1, regex=False).str[1]
titanic["short_name"] = first + " " + titanic["surname"]
titanic["short_name"].head(3).tolist()
Your turn: take the car make from each car
name. Which are the top 5?
make = cars["name"].str.split().str[0]
make.value_counts().head(5)
print(make[make.str.startswith(("chev", "vw", "volks"))]
.value_counts())
genres = netflix["listed_in"].str.split(", ").explode()
print(len(netflix), "titles ->", len(genres), "genre rows")
genres.value_counts().head(5)
Extract patterns with regular expressions#
A pattern like (\d+) means "capture one or more digits".
titanic["title"] = names.str.extract(r",\s*([^.]+)\.")[0]
titanic["title"].value_counts().head(6)
Common mistake: chopping off text and hoping the rest is a number.
netflix["duration"].str.replace(" min", "").astype(int)
dur = netflix["duration"]
netflix["length"] = dur.str.extract(r"(\d+)")[0].astype(int)
netflix["unit"] = dur.str.extract(r"\d+ (\w+)")[0]
netflix[["duration", "length", "unit"]].head(3)
pd.crosstab(netflix["type"], netflix["unit"])
pattern = r"(?P<length>\d+) (?P<unit>\w+)"
dur.str.extract(pattern).head(3)
Your turn: take the deck letter from
Cabin. Which deck had the best survival?
deck = titanic["Cabin"].str[0]
titanic.groupby(deck)["Survived"].agg(["mean", "size"]).round(2)
Quick check: does a passenger's title tell us anything about survival?
common = ["Mr", "Mrs", "Miss", "Master"]
group = titanic["title"].where(titanic["title"].isin(common), "Rare")
titanic.groupby(group)["Survived"].agg(["mean", "size"]).round(2)
Replace and count patterns#
replace and count work with plain text or with patterns.
in_brackets = names.str.contains("(", regex=False)
print(in_brackets.sum(), "names include a bracketed name")
short = names.str.replace(r"\s*\(.*?\)", "", regex=True)
print(short[in_brackets].head(2).tolist())
fixes = {"Mlle": "Miss", "Ms": "Miss", "Mme": "Mrs"}
titanic["title"] = titanic["title"].replace(fixes)
titanic["title"].value_counts().head(4)
cast_size = netflix["cast"].str.count(",") + 1
print(cast_size.median(), "actors listed in the median title")
netflix.loc[cast_size.idxmax(), ["title", "type"]].tolist()
n_countries = (netflix["country"].str.count(",") + 1).astype("Int64")
print(n_countries.value_counts().sort_index().head(4))
print(n_countries.isna().sum(), "titles with no country")
Your turn: how many words are in the longest Netflix title?
words = netflix["title"].str.split().str.len()
print(words.max(), "words")
print(netflix.loc[words.idxmax(), "title"])
Mini-project: which film genres run longest?#
Combine extract, split and explode on the Netflix films.
films = netflix[netflix["type"] == "Movie"]
print(len(films), "films")
films["length"].describe().round(1)[["min", "50%", "max"]]
by_genre = (films.assign(genre=films["listed_in"].str.split(", "))
.explode("genre"))
stats = by_genre.groupby("genre")["length"].agg(["median", "size"])
stats = stats[stats["size"] >= 100].sort_values("median")
print(stats.head(3))
print(stats.tail(3))
decade = films["release_year"] // 10 * 10
films.groupby(decade)["length"].median().loc[1990:]
import matplotlib.pyplot as plt
ax = stats["median"].plot.barh(figsize=(8, 6))
ax.set_title("Median Netflix film length by genre (100+ films)")
ax.set_xlabel("Median length (minutes)")
ax.set_ylabel("Genre")
plt.show()
import os
os.makedirs("output/pandas", exist_ok=True)
stats.to_csv("output/pandas/l19_genre_lengths.csv")
check = pd.read_csv("output/pandas/l19_genre_lengths.csv")
print(check.shape)
print(check.iloc[[0, -1]].to_string(index=False))
Finding: stand-up specials run about 65 minutes; dramas and classics 109 to 112. Newer films run shorter too: 108 minutes in the 2000s, 94 in 2020.
Recap#
- Text methods live behind
.str:lower,strip,len - Strip spaces before converting text to dates or numbers
contains,startswith,endswithgive True/False masks- Use
regex=Falsewhen searching for symbols like( split+expandmakes columns;split+explodemakes rowsextractwith a pattern pulls out numbers and words
Challenges#
- Which Netflix directors have the most titles? Watch for lists.
- Find the most common first word in Netflix film titles.
- Count TV shows by number of seasons, using
lengthandunit.
What's next#
Lesson 20: Handling Outliers and Data Validation. Spotting extreme values, and checking data follows the rules.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
Lesson 11
Clean Stock Price Data in Python: Apple vs Tesla with Pandas | Data Analytics #11
26 minLesson 4
Data Mining with Python: pandas & scikit-learn Crash Course
58 minLesson 6
Python Regex & f-strings for Beginners: Clean Messy Text (Beginner Python Course, Part 6)
18 minLesson 5