PYTHON / PANDAS
Handling missing data
Detect, count, drop, and fill missing values in pandas, and predict how NaN changes aggregations and column dtypes.
What you will learn
- Detect gaps with isna()/notna(); NaN never compares equal, so == np.nan always fails
- Profile a frame with df.isna().sum() before deciding to drop or fill anything
- Drop selectively using dropna(subset=[...]) or thresh=, not a bare dropna()
- Fill per column with fillna({...}), or ffill/bfill when rows have a real order
Understanding Handling missing data
pandas marks a value as missing rather than storing a real one. In float columns the marker is the IEEE-754 value NaN, in datetime columns it is NaT, in object columns it can be either None or NaN, and in the nullable dtypes (Int64, boolean, string) it is pd.NA. What all of these share is that they refuse to compare equal to anything, including themselves, so a test like df['temp'] == np.nan is False on every single row. That is why detection always goes through isna() and notna(), which inspect the marker directly instead of comparing values.
Once you know where the gaps are, there are only three honest responses: drop those rows, fill them with something you can defend, or leave them and let the aggregations skip them. dropna() with no arguments removes every row that has a gap in any column, which on a wide table can delete most of your data because a row only needs one bad cell to qualify. dropna(subset=['temp']) restricts the test to the columns your calculation actually needs, and thresh=n keeps rows that have at least n non-missing values, which is the tool for partly-empty records.
Filling is a modelling decision, not a cleanup chore. fillna(0) on a temperature column asserts those days were zero degrees and drags the mean down, while filling with the column mean leaves the mean untouched but shrinks the spread. Reductions skip missing values by default, so mean() divides by count() rather than len(), whereas element-wise arithmetic propagates NaN through the whole expression; that asymmetry is why dropping and filling can silently produce different numbers from the same raw data. Always re-check isna().sum() on the result you assigned back, because dropna and fillna return new objects and leave the original frame alone.
import pandas as pd
import numpy as np
df = pd.DataFrame({
"city": ["Oslo", "Oslo", "Bergen", "Bergen", "Tromso"],
"temp": [3.0, np.nan, 5.5, np.nan, np.nan],
"rain": [12.0, 4.0, np.nan, 8.0, 3.0],
})
print(df.isna().sum())
print("mean of temp:", df["temp"].mean())
print(df.dropna(subset=["temp"]))
print(df.fillna({"temp": df["temp"].mean(), "rain": 0}))Missing is a separate state rather than a value, so you detect it with isna and then decide per column whether to drop, fill, or let aggregations skip it.
Worked examples
Why == np.nan cannot find missing values
Shows that NaN is not equal to itself and that reductions ignore it while len does not.
import pandas as pd
import numpy as np
s = pd.Series([1.0, np.nan, 3.0])
print(np.nan == np.nan)
print((s == np.nan).tolist())
print(s.isna().tolist())
print(s.sum(), s.sum(skipna=False))
print(s.count(), len(s))Example explained
Line 1np.nan == np.nan is False by IEEE-754 rule, so any equality test against NaN can never match.
Line 2(s == np.nan) therefore returns all False, which is why filtering with that mask silently returns nothing.
Line 3s.isna() checks the missing marker instead of comparing values, so it correctly flags position 1.
Line 4s.sum() skips the gap and returns 4.0, but skipna=False propagates it; count() is 2 while len(s) is 3.
Carrying values forward in ordered data
Uses ffill and bfill on a daily series, including the limit argument and the leading gap that ffill cannot touch.
import pandas as pd
import numpy as np
readings = pd.Series(
[np.nan, 10.0, np.nan, np.nan, 14.0],
index=pd.date_range("2024-03-01", periods=5, freq="D"),
)
print(readings.ffill())
print(readings.ffill(limit=1))
print(readings.bfill().tolist())Example explained
Line 1ffill() copies the last valid observation downwards, so 2024-03-03 and 2024-03-04 both become 10.0.
Line 2The first row stays NaN because there is no earlier value to carry forward, no matter how you call ffill.
Line 3limit=1 fills at most one consecutive gap, which stops a single stale reading from covering a long outage.
Line 4bfill() pulls the next valid value backwards instead, which is what fixes the leading gap.
Turning sentinel codes into real missing values
Converts -999 and placeholder strings to NaN, then drops only the rows that are almost entirely empty.
import pandas as pd
import numpy as np
raw = pd.DataFrame({
"id": ["s1", "s2", "s3", "s4"],
"value": [21.0, -999.0, 19.5, -999.0],
"note": ["ok", "N/A", "", "ok"],
})
clean = raw.replace({-999.0: np.nan, "N/A": np.nan, "": np.nan})
print(clean)
print("total missing:", int(clean.isna().sum().sum()))
print(clean.dropna(thresh=2))Example explained
Line 1raw['value'].mean() before the replace would be -486.75, because -999 is an ordinary number to pandas.
Line 2replace with a single dict and no value argument maps old values to new ones across every column.
Line 3isna().sum().sum() adds the per-column counts into one number, useful as a quick data-quality check.
Line 4thresh=2 keeps rows with at least two non-missing cells, so only s2 (id alone) is removed.
Missing values and integer columns
Demonstrates the dtype change caused by introducing NaN and how the nullable Int64 dtype avoids it.
import pandas as pd
import numpy as np
counts = pd.Series([4, 7, 2])
print(counts.dtype)
counts.iloc[1] = np.nan
print(counts.dtype, counts.tolist())
nullable = pd.Series([4, 7, 2], dtype="Int64")
nullable.iloc[1] = pd.NA
print(nullable.dtype)
print(nullable.sum(), nullable.isna().sum())Example explained
Line 1A plain int64 column has no bit pattern reserved for missing, so writing NaN upcasts the column to float64.
Line 2After the upcast the surviving values print as 4.0 and 2.0, which quietly changes how they format and compare.
Line 3The nullable Int64 dtype stores a separate mask, so pd.NA fits without abandoning integer arithmetic.
Line 4nullable.sum() skips the missing entry and returns the integer 6, while isna().sum() still reports one gap.
Important notes
0, an empty string, and codes like -999 or "unknown" are ordinary values to pandas and will enter your sums and means until you convert them to NaN yourself.
fillna and dropna accept inplace=True, but modern pandas discourages it and it fails silently through chained indexing; prefer df = df.fillna(...).
Common mistakes
Filtering with df[df['temp'] == np.nan] or df[df['temp'] != np.nan]: the first returns an empty frame and the second returns every row, because NaN comparisons are always False.
Calling df.dropna() or df.fillna(0) without assigning the result, then wondering why the gaps are still there; both return a new object and leave df unchanged.
Using a bare df.dropna() on a wide table: one missing cell anywhere in a row deletes the whole record, so you can lose 80% of the data while only 5% of cells were missing.
Try it yourself
Change, predict, then run
Build a DataFrame of six students with a 'quiz' column containing two NaN values and a 'name' column containing one None, then print the missing count per column and compare the mean quiz score from dropna(subset=['quiz']) against fillna(0) and fillna(median).
Open the Python workspaceCheck your understanding
A column temp holds 3.0, NaN, 5.5, NaN, NaN. Which statement about temp.mean() and temp.fillna(0).mean() is correct?
- temp.mean() is 4.25 because the mean divides by the 2 non-missing values, while temp.fillna(0).mean() is 1.7
- Both are 1.7, because pandas treats NaN as 0 in arithmetic
- temp.mean() is nan, because a single NaN propagates through any mean calculation
- Both are 4.25, because fillna(0) cannot change an average that already ignores missing values
Show answer
mean() uses skipna=True by default, so it sums 3.0 + 5.5 and divides by count() = 2, giving 4.25. Filling first turns the three gaps into real zeros, so the denominator becomes 5 and the result drops to 8.5 / 5 = 1.7. Option 3 is tempting because NaN really does propagate in element-wise arithmetic (3.0 + np.nan is nan), but reductions such as mean and sum skip it unless you pass skipna=False.