Making Categories Match: replace and map
value_counts shows every spelling a column holds, replace with a dictionary fixes the few misspellings you name and leaves every other value alone, map turns every spelling into one standard value and leaves missing whatever it doesn't know, which you count before moving on, and the category type stores a column with a fixed list of values.
Apprentice · Python · Updated
After this lesson you can
- Fix a few misspellings in a column with replace and a dictionary, after listing every spelling with value_counts
- Turn every spelling into one standard value with map, check for the values it left missing, and name the category type
Key takeaways
- value_counts() lists every spelling a column holds and how often, so look at it before you fix anything.
- replace() with a dictionary changes only the values the dictionary names, and leaves every other value as it was.
- map() with a dictionary turns every value into the one the dictionary gives it, and a value the dictionary doesn't name becomes NaN.
- After map(), count the values that came out missing and list the spellings behind them, so no value disappears without your noticing.
- The category type stores a column whose values come from a fixed list, such as shirt sizes, and can keep that list in a set order.
A survey's "Which state do you live in?" box, a store's product types, a club's membership levels: whenever people type a choice instead of picking it from a list, one choice arrives in many spellings, and every count splits into pieces. Before you can count, group or chart by a column like that, every spelling has to become one standard value. This lesson does it twice, once for a handful of typos and once for a column where every value needs a standard name.
The example is the Hillcrest 5K Fun Run's registrations, registrations.csv: 450 runners who typed their own sign-up form, with a tshirt_size and a hometown for each. The runners and the race are our own made-up data.
How many shirts of each size?
The race orders a t-shirt for every runner, so the order needs a count of each size. value_counts() (Counting What's There: value_counts and unique) counts each value:
import pandas as pd
reg = pd.read_csv("registrations.csv")
reg["tshirt_size"].value_counts()
tshirt_size
M 73
L 67
XL 46
large 38
S 37
medium 36
Med 33
Large 24
Lg 18
Medium 15
X-Large 11
xl 11
small 11
s 8
Small 8
l 6
XL 4
m 4
Name: count, dtype: int64
Four sizes, eighteen spellings. Notice XL twice: the second one has a space after it. That makes value_counts your first tool here: it shows every spelling, so you fix what's really there instead of what you guess is there. The tools from Tidying Text with .str take care of the spaces and the capitals:
sizes = reg["tshirt_size"].str.strip().str.lower()
sizes.value_counts()
tshirt_size
m 77
l 73
large 62
xl 61
medium 51
s 45
med 33
small 19
lg 18
x-large 11
Name: count, dtype: int64
Ten spellings left. These differ in words, not in spaces or capitals, so they need a list that says which size each spelling means.
map: every spelling to one standard value
A dictionary pairs each key with a value, like a phrase book: look up "medium", read "M". map() looks up every value of the column in the dictionary and puts in what it finds:
standard = {"s": "S", "small": "S", "m": "M", "med": "M", "medium": "M",
"l": "L", "large": "L", "xl": "XL", "x-large": "XL"}
shirts = sizes.map(standard)
print("missing after map:", shirts.isna().sum())
missing after map: 18
Here is what makes map careful: a value the dictionary doesn't name has nothing to look up, so it becomes NaN. Eighteen sizes became missing. List the spellings behind them, the same way you'd list the values pd.to_numeric couldn't read:
sizes[shirts.isna()].value_counts()
tshirt_size
lg 18
Name: count, dtype: int64
The dictionary left out lg. Add it and map again:
standard["lg"] = "L"
shirts = sizes.map(standard)
print("missing after map:", shirts.isna().sum())
missing after map: 0
Every runner has one of four sizes. Counting the missing values after map is the habit that matters: a spelling nobody planned for shows up as a number you can't miss, instead of a shirt that quietly never gets ordered.
replace: fix a few, keep the rest
The towns have a different problem. After strip and title, most towns are spelled right, and only a few have typos:
towns = reg["hometown"].str.strip().str.title()
towns.value_counts()
hometown
Hillcrest 189
Cedar Grove 70
Maple Falls 60
Riverton 54
Hilcrest 25
Oak Hollow 22
Lakeview 8
Pine Ridge 7
Cedar Grv 6
Maplefalls 5
Mapel Falls 4
Name: count, dtype: int64
replace() with a dictionary changes only the values the dictionary names and leaves every other value as it was. So the dictionary needs only the four mistakes:
fixes = {"Hilcrest": "Hillcrest", "Maplefalls": "Maple Falls",
"Mapel Falls": "Maple Falls", "Cedar Grv": "Cedar Grove"}
towns.replace(fixes).value_counts()
hometown
Hillcrest 214
Cedar Grove 76
Maple Falls 69
Riverton 54
Oak Hollow 22
Lakeview 8
Pine Ridge 7
Name: count, dtype: int64
map with the same dictionary would be a mistake: it would turn every town the dictionary doesn't name, Hillcrest included, into NaN:
print("missing if we used map:", towns.map(fixes).isna().sum())
missing if we used map: 410
So the choice is about the dictionary you have. With a few known typos and many good values, use replace: everything else stays. With a complete list of the allowed values, use map: anything it doesn't know becomes missing, so you find out.
The category type
A column whose values come from a short, fixed list, such as four shirt sizes, can be stored as the category type. pandas keeps the list once and each row points into it, and you can give the list an order of your own. pd.Categorical makes the column, and value_counts(sort=False) then counts in the list's order instead of from biggest to smallest, which is the order the shirt supplier wants:
order = ["S", "M", "L", "XL"]
reg["tshirt_size"] = pd.Categorical(shirts, categories=order)
reg["tshirt_size"].value_counts(sort=False)
tshirt_size
S 64
M 161
L 153
XL 72
Name: count, dtype: int64
That's the shirt order: 64 small, 161 medium, 153 large and 72 extra large, 450 in all.
Check yourself
2 questions to try on the lesson page.
So what
value_counts shows every spelling, replace fixes the few you name and keeps the rest, and map turns every spelling into one standard value, with a count of what it left missing so no runner's shirt is lost along the way.