Splitting and Extracting Text
str.split with expand=True turns one text column into several, with n=1 to split at the first space only, and str.extract with the short regular expression (\d+), read 'one or more digits, kept', pulls a number out of text typed in many different ways, where a plain replace would need a call for every wording.
Journeyman · Python · Updated
After this lesson you can
- Split a text column into several columns with str.split and expand=True, using n=1 to split at the first space only
- Pull a number out of free text with str.extract and the pattern (\d+), and choose between str.extract and str.replace
Key takeaways
- .str.split() cuts each value at the spaces, and expand=True puts the pieces in columns of their own.
- n=1 splits at the first space only, so a two-word last name such as Torres Vega stays together.
- A regular expression is a pattern that describes text. (\d+) is read "one or more digits, kept".
- .str.extract() keeps the part of each value the pattern's parentheses mark, and a value with no match becomes NaN.
- Use .str.replace() when one fixed piece of text is in the way, and .str.extract() when the part you want sits among words that vary.
A full name that a mailing tool wants as first and last name, an address that should be street and city, a comment box where people typed "about 30 minutes" when you wanted a number: often the value you need is a piece of a longer piece of text. This lesson cuts text into columns and pulls a number out of whatever words people wrapped around it.
The example is the Hillcrest 5K Fun Run's registrations, registrations.csv: 450 runners who typed their own sign-up form. The runners and the race are our own made-up data.
Splitting a name into first and last
The company that times the race wants first and last names in separate columns for its results board. Start from tidy names (Tidying Text with .str), then .str.split(), which cuts each value at its spaces and gives back a list of the pieces:
import pandas as pd
reg = pd.read_csv("registrations.csv")
reg["runner_name"] = reg["runner_name"].str.strip().str.title()
reg["runner_name"].str.split().head(3)
0 [Mateo, Walsh]
1 [Isaac, Silva]
2 [Nora, Foster]
Name: runner_name, dtype: object
A list in each row isn't a column yet. expand=True puts each piece in a column of its own, numbered from 0. The shape says how many columns that made:
parts = reg["runner_name"].str.split(expand=True)
parts.shape
(450, 3)
Three columns, not two. Some name had three words. Look at the rows where column 2 isn't missing:
parts[parts[2].notna()]
0 1 2
294 Leo Ruiz Castillo
296 Hugo Torres Vega
297 Caleb Sanchez Mora
332 Chloe Herrera Blanco
Four runners have a two-part last name, as many Spanish names do. Splitting at every space cuts Torres Vega in half. n=1 splits at the first space only: everything before it is the first name, everything after it the last name. Assign the two columns at once with a list of names inside the square brackets:
reg[["first_name", "last_name"]] = reg["runner_name"].str.split(n=1, expand=True)
reg[["runner_name", "first_name", "last_name"]].loc[[294, 296, 320]]
runner_name first_name last_name
294 Leo Ruiz Castillo Leo Ruiz Castillo
296 Hugo Torres Vega Hugo Torres Vega
320 Maria Lopez Maria Lopez
.loc[[294, 296, 320]] picks those rows by their index labels (Picking Rows and Columns: loc and iloc teaches it). No rule fits every name: a runner with two first names, such as Mary Beth, would get the wrong split. Checking the rows that look unusual, as the three-column surprise made you do, is part of the job.
Free text with a number inside
The race wants pacers, runners who hold a steady speed for a goal time, so it asked each runner for a goal. The form's box took any text:
reg["goal_time"].value_counts().head(6)
goal_time
30 29
30 minutes 28
not sure 27
about 30 26
30 min 22
under 30 21
Name: count, dtype: int64
The number is there, wrapped in different words. Turning Text into Numbers: to_numeric took a fixed " lbs" off with .str.replace, but here that would take four calls, one each for " minutes", " min", "about " and "under ", and the next runner who types approx 30 would slip through. What you want to say is "the digits, whatever is around them".
A pattern: (\d+)
A regular expression is a pattern that describes text, instead of spelling out the text itself. Patterns can grow long and hard to read; this course needs only one short one, read piece by piece:
\dmeans "a digit", any of 0 to 9.+means "one or more of the thing before me".- The parentheses
( )mean "keep this part".
So (\d+) is read "one or more digits, kept". .str.extract() looks for the pattern in each value and keeps what the parentheses matched. The r before the quotes makes a raw string, so Python leaves the backslash in \d alone, and expand=False gives back one column instead of a table:
goals = reg["goal_time"].str.extract(r"(\d+)", expand=False)
pd.DataFrame({"typed": reg["goal_time"], "digits": goals}).head(6)
typed digits
0 35 35
1 NaN NaN
2 under 30 30
3 about 25 25
4 not sure NaN
5 40 40
Where the pattern finds no digits, as in not sure, the result is NaN. The digits are still text, so convert them, then count:
reg["goal_minutes"] = pd.to_numeric(goals)
reg["goal_minutes"].value_counts().head(3)
goal_minutes
30.0 126
35.0 72
25.0 57
Name: count, dtype: int64
Thirty minutes is the goal 126 runners share, so that pacer goes first. As always, list what became missing before you trust the numbers. dropna=False makes value_counts count the blanks too:
missing = reg["goal_minutes"].isna()
reg["goal_time"][missing].value_counts(dropna=False)
goal_time
NaN 53
not sure 27
Name: count, dtype: int64
Fifty-three blanks and 27 runners who weren't sure: none of them had a number to lose. One honest note: under 30 became 30. The pattern keeps the digits, not their meaning, and for planning pacers that's close enough.
replace or extract?
.str.replace: one fixed piece of text is in the way, such as" lbs"or". Take it off and what's left is the value.quot; .str.extract: the part you want sits among words that vary. Describe the part, and keep it.
Check yourself
2 questions to try on the lesson page.
So what
.str.split(n=1, expand=True) turns one text column into the columns a tool needs, and .str.extract(r"(\d+)") pulls the number out of whatever words people typed around it, so free text becomes data you can count.