Cleaning Data: Missing Values and Messy Text
Tidying the room before counting what is in it
In this lesson
Every dataset you have seen so far was tidy, because it was written for a lesson. Real data is not. It has blank cells, "London" and "LONDON" counted as different cities, names with stray spaces, and numbers stored as text.
Analysts routinely say cleaning is most of the job. This lesson is the part of the unit you will use every single time.
Explain it like I’m 5
Cleaning data is tidying a messy room before trying to count what is inside it. Count first and you will count the same sock twice.
Look at the damage first
Before fixing anything, find out what is wrong. Three lines do it: print the table, count the missing values per column with isna().sum(), and check the types with dtypes.
That third one matters more than it looks. If a column you expect to be numeric shows up as object, pandas is treating it as text, and every calculation on it will either fail or quietly do the wrong thing.
import io
import pandas as pd
CSV = """name,city,age
Ada Lovelace ,london,36
Sam Rivers,,29
Rae Kim,LONDON,
Kit Brown,Bristol,41
"""
contacts = pd.read_csv(io.StringIO(CSV))
print(contacts)
print()
print(contacts.isna().sum())
print()
print(contacts.dtypes)
name city age 0 Ada Lovelace london 36.0 1 Sam Rivers NaN 29.0 2 Rae Kim LONDON NaN 3 Kit Brown Bristol 41.0 name 0 city 1 age 1 dtype: int64 name object city object age float64 dtype: object
Dropping versus filling
Two ways to deal with a gap, and choosing badly is how analyses go quietly wrong.
Dropping (dropna()) removes rows with missing values. It is honest and simple, but it can be brutal: one blank cell throws away the whole row, including the columns that were fine.
Filling (fillna()) substitutes a value: a median for numbers, or something like "Unknown" for text. It keeps the row, but you have now invented data, and you must be able to say why.
The rule of thumb: drop when a row is missing something essential and there are plenty of rows left; fill when the missing part is incidental, or when losing the row would bias what remains.
import pandas as pd
readings = pd.DataFrame({
"sensor": ["a", "b", "c", "d"],
"temp": [20.5, None, 22.1, None],
})
print("rows to start:", len(readings))
print("after dropna:", len(readings.dropna()))
print()
print(readings.fillna({"temp": readings["temp"].mean()}))
rows to start: 4 after dropna: 2 sensor temp 0 a 20.5 1 b 21.3 2 c 22.1 3 d 21.3
Messy text, and keeping the original safe
Text problems are subtler than blanks because nothing looks wrong. "london", "LONDON" and " London" are three different values to a computer, so a grouped count of cities would report them separately and you might never notice.
The .str accessor applies string methods down a whole column: .str.strip() for stray spaces, .str.title() or .str.lower() for consistent capitalization.
And one habit that will save you: clean a copy. Keep the raw data untouched so you can always check what the file actually said.
import io
import pandas as pd
CSV = """name,city,age
Ada Lovelace ,london,36
Sam Rivers,,29
Rae Kim,LONDON,
Kit Brown,Bristol,41
"""
raw = pd.read_csv(io.StringIO(CSV))
clean = raw.copy() # keep the original untouched
clean["name"] = clean["name"].str.strip()
clean["city"] = clean["city"].str.strip().str.title()
clean["city"] = clean["city"].fillna("Unknown")
clean["age"] = clean["age"].fillna(clean["age"].median()).astype(int)
print(clean)
print()
print("missing values left:", int(clean.isna().sum().sum()))
print("original still messy:", repr(raw.loc[0, "name"]))
name city age 0 Ada Lovelace London 36 1 Sam Rivers Unknown 29 2 Rae Kim London 36 3 Kit Brown Bristol 41 missing values left: 0 original still messy: ' Ada Lovelace '
Clean the contacts table in three steps: trim the stray spaces from every name, fill the missing city with "Unknown" and make the capitalization consistent with .str.title(), and fill the missing age with the median before converting the column to whole numbers.
import io
import pandas as pd
CSV = """name,city,age
Ada Lovelace ,london,36
Sam Rivers,,29
Rae Kim,LONDON,
Kit Brown,Bristol,41
"""
contacts = pd.read_csv(io.StringIO(CSV))
# TODO: trim whitespace from the names
# TODO: fill missing cities with "Unknown", then strip and title-case them
# TODO: fill missing ages with the median, then convert the column to int
print(contacts)
print("missing left:", int(contacts.isna().sum().sum()))Assign each cleaned column back onto itself. Fill the city before title-casing, so "Unknown" goes through the same treatment: contacts["city"].fillna("Unknown").str.strip().str.title(). For age, chain .fillna(...) then .astype(int).
import io
import pandas as pd
CSV = """name,city,age
Ada Lovelace ,london,36
Sam Rivers,,29
Rae Kim,LONDON,
Kit Brown,Bristol,41
"""
contacts = pd.read_csv(io.StringIO(CSV))
contacts["name"] = contacts["name"].str.strip()
contacts["city"] = contacts["city"].fillna("Unknown").str.strip().str.title()
contacts["age"] = contacts["age"].fillna(contacts["age"].median()).astype(int)
print(contacts)
print("missing left:", int(contacts.isna().sum().sum()))Common mistake: Dropping rows without checking how many are left
dropna() is one call and produces no warning.
Print len(df) before and after. Losing half the dataset should be a decision, not a surprise.
Common mistake: Cleaning in place and losing the original
Working on one variable feels tidier than keeping two.
Use clean = raw.copy(). When a number looks wrong you will want to see what the file really said.
Common mistake: Doing math on numbers stored as text
They look like numbers when printed.
Check df.dtypes. An object column is text. Convert with .astype(int) or pd.to_numeric() first.
Common mistake: Grouping on text that was never normalized
london and London look the same to a reader.
Strip and case-normalize text columns before grouping, or your counts split across spellings.
What does a missing value mean in pandas?
NaN marks an absent value. It is not zero, and treating it as zero changes your averages.
When might filling be better than dropping?
Dropping discards the whole row. If the other columns matter, filling keeps them, but the filled value is an estimate you must disclose.
Why convert a column's data type?
An object column is text. Summing it either fails or concatenates strings.
Mini exercise (medium)
A small product table with the usual problems: padded names, inconsistent category capitalization, and one missing price. Trim the product names, lower-case the categories so they group properly, and fill the missing price with the median. Then count how many products are in each category and print the total price rounded to two decimals.
Now you. Edit the starter code below, then Run it, everything happens in the browser.
import io
import pandas as pd
CSV = """product,category,price
Widget ,TOOLS,9.99
Gadget,tools,
Doohickey,Toys ,4.50
Thing,toys,12.00
"""
items = pd.read_csv(io.StringIO(CSV))
# TODO: strip spaces from product names
# TODO: strip and lower-case the categories so they group together
# TODO: fill the missing price with the median price
print(items)
print()
print(items["category"].value_counts())
print("total price:", round(items["price"].sum(), 2))Same three moves as the lesson: .str.strip() on the names, .str.strip().str.lower() on the categories, and .fillna(items["price"].median()) on the price. Then items["category"].value_counts() and round(items["price"].sum(), 2).
import io
import pandas as pd
CSV = """product,category,price
Widget ,TOOLS,9.99
Gadget,tools,
Doohickey,Toys ,4.50
Thing,toys,12.00
"""
items = pd.read_csv(io.StringIO(CSV))
items["product"] = items["product"].str.strip()
items["category"] = items["category"].str.strip().str.lower()
items["price"] = items["price"].fillna(items["price"].median())
print(items)
print()
print(items["category"].value_counts())
print("total price:", round(items["price"].sum(), 2))