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

By

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:

  • \d means "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 "quot;. Take it off and what's left is the value.
  • .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.

Key terms

Related