Turning Text into Numbers: to_numeric

Text such as '59 lbs' and '$75' becomes numbers in two steps, .str.replace to take off the units and symbols, then pd.to_numeric with errors='coerce' to convert what's left, followed by a list of the values that became missing so none is lost silently.

Apprentice · Python · Updated

By

After this lesson you can

  • Take symbols and units such as '#39;, ',' and ' lbs' off a text column with .str.replace
  • Convert text to numbers with pd.to_numeric(errors='coerce'), and list the values that became missing

Key takeaways

  • .str.replace() changes text in every value of a column at once, such as taking ' lbs' off '59 lbs' or ' off '$75'.
  • pd.to_numeric() turns a text column into numbers. On its own it stops at the first value it can't read, and says which one.
  • With errors="coerce", every value pd.to_numeric can't read becomes NaN instead, so the conversion finishes.
  • Count the missing values before and after, and list the values that became missing, so you decide about each one instead of losing it silently.

Prices with dollar signs, weights with units, totals with commas: files are full of numbers dressed up as text, because they were typed for people to read. Before you can add them up, average them or chart them, the dressing has to come off. You'll do this on almost every file a person typed, from a class survey to a store's sales export.

The example is Happy Tails Animal Shelter's intake log, intake.csv: 800 rows, one for each time an animal arrived, typed in by volunteers. Its weight_lbs column holds text such as 59 lbs and 42, and its adoption_fee column text such as $150 and 150.00, so both are str columns, as Column Types: dtypes and astype shows.

Taking the units off: .str.replace

A column of text has a set of string tools under .str (read "dot s t r"), and each one works on every value in the column at once. .str.replace(old, new) swaps one piece of text for another in each value. Replacing " lbs" with nothing leaves the number:

import pandas as pd

intake = pd.read_csv("intake.csv", na_values=["unknown", "-"])
weights = intake["weight_lbs"].str.replace(" lbs", "")
weights.head(3)
0    5.5
1     59
2     42
Name: weight_lbs, dtype: str

The values look like numbers now, but the column is still str. Tidying Text with .str covers the other .str tools.

to_numeric, and errors="coerce"

pd.to_numeric() turns a column of text into numbers. On its own, it stops at the first value it can't read:

pd.to_numeric(weights)
ValueError: Unable to parse string 'about 15' at position 73

A volunteer guessed a weight and typed about 15. The error names the value and where it sits: position 73, counting from 0. Stopping is a safe default, but with 800 rows you want the conversion to finish and then look at every value it couldn't read. errors="coerce" does that: each value it can't read becomes NaN.

numbers = pd.to_numeric(weights, errors="coerce")
numbers.head(3)
0     5.5
1    59.0
2    42.0
Name: weight_lbs, dtype: float64

"Coerce" means "force": every value becomes a number, or missing.

Nothing lost silently

errors="coerce" is quiet, which is exactly what makes it risky. Count the missing values before and after:

print("missing before:", weights.isna().sum())
print("missing after:", numbers.isna().sum())
missing before: 34
missing after: 38

Four weights were typed in but turned into NaN. To see which, put a column of True and False inside square brackets: pandas keeps the values where it's True. Here that's "the original text, where the number came out missing", and value_counts() lists them (and leaves out the ones that were blank to begin with):

weights[numbers.isna()].value_counts()
weight_lbs
about 15    3
about 20    1
Name: count, dtype: int64

Filtering Rows with a Condition does much more with square brackets and True and False. Here, four guesses: the shelter decides a guess isn't a measurement, so they stay missing. Now you know, and the report can say so. Store the numbers back in the table:

intake["weight_lbs"] = numbers
print(intake["weight_lbs"].dtype)
print("total pounds:", intake["weight_lbs"].sum())
float64
total pounds: 21892.0

Fees: '$75' and '75.00'

The fees were typed two ways:

intake["adoption_fee"].value_counts().head(4)
adoption_fee
$150      207
150.00    151
$75       106
75.00      78
Name: count, dtype: int64

Taking off the $ makes them all plain numbers. A fee of a thousand dollars or more would also carry a comma ($1,200), so a second .str.replace takes commas out too. Then convert, and count:

fees = intake["adoption_fee"].str.replace("quot;, "").str.replace(",", "")
amounts = pd.to_numeric(fees, errors="coerce")
print("missing before:", fees.isna().sum())
print("missing after:", amounts.isna().sum())
missing before: 50
missing after: 50

The same 50 fees that were blank, and no new ones: every typed fee became a number.

intake["adoption_fee"] = amounts
intake["adoption_fee"].value_counts()
adoption_fee
150.0    358
75.0     184
45.0      87
40.0      71
95.0      50
Name: count, dtype: int64

$150 and 150.00 are one fee now, counted together.

Check yourself

2 questions to try on the lesson page.

So what

Taking off the units with .str.replace, converting with pd.to_numeric(errors="coerce") and then listing what became missing turns typed text into numbers you can add up, without losing a value you didn't know about.

Key terms

Related