Unit 5 • Lesson 2

Missing Markers on Load

Overview

Files write missing values in a dozen ways; na_values names yours.

Intermediate 20–25 min

← Previous Next: Selecting What You Need

What You Will Learn in This Lesson

By the end of this lesson, you will be able to:

List what pandas already treats as missing.
Name a file's own missing markers at load time.
Explain why an unnamed marker costs you the whole column.
Count what is missing before deciding what to do about it.

Why This Matters

Every file writes "we do not know" differently: blank, n/a, N/A, -, ., NULL, 999. pandas knows some of them. The ones it does not know do not become missing values — they stay as text, and one of them is enough to turn a numeric column into strings and every calculation on it into nonsense.

Step 1: What counts as missing

pandas already treats an empty field, NA, NaN, NULL and a few others as missing. Real files also use n/a, a single dash, a full stop, or a sentinel number like 999.

The ones it knows

An empty field becomes NaN on its own, and the column stays numeric.

import pandas as pd
from io import StringIO

text = "name,score\nAda,92\nGrace,\n"
df = pd.read_csv(StringIO(text))
print(df)
print(df.dtypes)

Naming your own

na_values adds to the list. Doing it at load time means the column is numeric from the start, rather than being repaired afterwards.

import pandas as pd
from io import StringIO

text = "name,score\nAda,92\nGrace,n/a\n"
df = pd.read_csv(StringIO(text), na_values=["n/a", "-", "."])
print(df)
print(df.dtypes)

Mini Practice #1: Name the markers

Try It Yourself

This file uses two different markers. Load it so that both become missing and the column stays numeric.

import pandas as pd
from io import StringIO

text = "name,score\nAda,92\nGrace,n/a\nAlan,-\n"

df = pd.read_csv(StringIO(text))
print(df.dtypes)

# Now load it again, naming both markers:

Press Run to see output

Step 2: A missing marker left unnamed poisons the column

One "n/a" in a numeric column makes the whole column text, and every calculation on it is then wrong in a way that does not raise.

The damage

Compare these two loads of the same file. The only difference is whether the marker was named.

import pandas as pd
from io import StringIO

text = "name,score\nAda,92\nGrace,n/a\nAlan,79\n"

bad = pd.read_csv(StringIO(text))
print(bad["score"].dtype, "|", bad["score"].sum())

good = pd.read_csv(StringIO(text), na_values=["n/a"])
print(good["score"].dtype, "|", good["score"].sum())

The first sum concatenated three strings. It did not raise, and on a real file it would have produced a number-shaped answer you might have believed.

Count before you decide

How much is missing is a fact about your data and usually belongs in the write-up. Find it before you fill or drop anything, because afterwards it is gone.

import pandas as pd
from io import StringIO

text = "name,score\nAda,92\nGrace,n/a\nAlan,79\n"
df = pd.read_csv(StringIO(text), na_values=["n/a"])

print(df.isna().sum())
print("rows with a score:", int(df["score"].notna().sum()))

The sentinel that looks like data

A missing marker of 999 or -1 is the dangerous kind: the column stays numeric, nothing looks wrong, and the mean is quietly ruined. Only knowing the file catches it.

import pandas as pd

with_sentinel = pd.Series([92, 88, 999])
print(with_sentinel.mean())

cleaned = pd.Series([92, 88, 999]).replace(999, None)
print(cleaned.mean())

Mini Practice #2: How much is missing

Try It Yourself

Print the count of missing values per column, and the total across the whole table.

import pandas as pd
from io import StringIO

text = "name,score,city\nAda,92,\nGrace,,Leeds\n"
df = pd.read_csv(StringIO(text))

print(df.isna().sum())
print(None)   # the total across every column

Press Run to see output

Step 3: When to Use It

Files with text markers

"n/a", "-", "missing": name them in na_values so the column stays numeric.

pd.read_csv(path, na_values=["n/a", "-"])

Numeric sentinels

Values like 999 or -1 that mean “not recorded” should be listed too, or they ruin the mean.

When Not to Use It

Do not clean markers after the load with string replacement when read_csv can do it. And do not assume blanks are the only marker — look at the distinct values.

End-of-Lesson Exercises

Exercise 1: Count the known scores

A file scores.csv uses n/a for a missing score. Write known_scores(path) that returns how many rows have a score.

Name "n/a" in na_values so it loads as missing, then count what is left with notna().sum() or by dropping the missing rows.

import pandas as pd

def known_scores(path):
    # How many rows actually have a score?
    return 0

print(known_scores("scores.csv"))

Press Run to try it, then Check my work to mark it.

Exercise 2: The mean without the sentinel

A file readings.csv uses 999 to mean "no reading". Write true_mean(path) that returns the mean of the real readings, rounded to two decimal places. The column is numeric either way, so nothing will warn you.

na_values=[999] at load time turns the sentinel into NaN, and mean() skips NaN. Filtering with != 999 works too; naming it at load is the habit that scales.

import pandas as pd

def true_mean(path):
    # The mean, with 999 treated as missing.
    return 0.0

print(true_mean("readings.csv"))

Press Run to try it, then Check my work to mark it.

← Prev Next