Browse cheat sheets · Python
Handle Missing Values in pandas
Inspect, filter, fill, drop, and validate missing values in pandas without silently changing the meaning or dtype of production data.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Count missing values by column
- 2. Calculate missing-value percentages
- 3. Find rows with a missing value in one column
- 4. Find rows with any missing value
- 5. Find rows where all selected fields are missing
- 6. Drop rows missing a required business key
- 7. Require at least N non-null values
- 8. Drop columns that are entirely missing
- 9. Fill a categorical missing value explicitly
- 10. Fill numeric values only when zero is semantically correct
- 11. Fill from another column
- 12. Forward-fill values
- 13. Backward-fill values
- 14. Interpolate numeric values
- 15. Preserve nullable integer semantics
- 16. Use nullable string dtype
- 17. Convert invalid numeric values to missing values for inspection
- 18. Convert invalid dates to NaT and inspect failures
- 19. Detect empty strings separately from missing values
- 20. Normalize known null-like strings carefully
- 21. Replace known sentinel values with missing values
- 22. Fill by group
- 23. Compare before and after missing-value handling
- 24. Assert required fields after cleaning
- What the result means
- What to look for
- Null percentage suddenly increased
- Required identifier is missing
- Missing values appear only after a merge
- Numeric nulls appear after conversion
- Date nulls appear after parsing
- A category has both null and "Unknown"
- Production notes
- Missingness is business logic
- Filling values can destroy information
- Null behavior depends on dtype
- dropna() can remove large amounts of data
- Forward fill requires ordering
- Imputation changes data
- Large DataFrames
- Common mistakes
- 1. Filling every null with zero
- 2. Calling dropna() with no subset
- 3. Using == np.nan
- 4. Treating empty strings as automatically missing
- 5. Forward-filling unsorted data
- 6. Filling after a bad join
- 7. Overwriting failed conversions immediately
- 8. Dropping a high-null column without checking rare value importance
- Next step
- Related deep dives
Problem
Real datasets often contain missing values:
- absent customer attributes
- unreported measurements
- failed parses
- incomplete joins
- optional fields
- empty source values
- delayed upstream data
The technical operation is easy.
The difficult part is deciding what a missing value means before changing it.
You need to know:
- where missing values exist
- whether they are expected
- whether they indicate a data-quality problem
- whether rows should be kept or removed
- whether filling values changes business meaning
- whether a conversion created new missing values
- whether missing values break joins, aggregations, or downstream types
Quick answer
Start by profiling missing values.
null_summary = (
pd.DataFrame(
{
"null_count": df.isna().sum(),
"null_pct": df.isna().mean().mul(100),
}
)
.sort_values("null_pct", ascending=False)
)
print(null_summary)
Filter missing rows:
missing_email = df.loc[df["email"].isna()]
Fill only when the replacement has clear business meaning:
df["country"] = df["country"].fillna("Unknown")
Drop rows only when the missing field makes the row unusable:
df = df.dropna(subset=["order_id"])
Do not use fillna(0) or dropna() globally without understanding the data semantics.
Code / commands
1. Count missing values by column
null_counts = (
df.isna()
.sum()
.sort_values(ascending=False)
)
print(null_counts)
This is the fastest first check.
2. Calculate missing-value percentages
null_summary = (
pd.DataFrame(
{
"null_count": df.isna().sum(),
"null_pct": df.isna().mean().mul(100),
}
)
.sort_values("null_pct", ascending=False)
)
print(null_summary)
Percentages are more useful than counts when columns or datasets differ greatly in size.
3. Find rows with a missing value in one column
missing_email = df.loc[
df["email"].isna()
]
Find rows where the value exists:
has_email = df.loc[
df["email"].notna()
]
Use .isna() and .notna() instead of comparing against None or np.nan.
4. Find rows with any missing value
rows_with_missing = df.loc[
df.isna().any(axis=1)
]
Count them:
print(df.isna().any(axis=1).sum())
This is useful for broad inspection, but do not treat all missing fields as equally important.
5. Find rows where all selected fields are missing
cols = ["email", "phone", "mobile"]
no_contact_details = df.loc[
df[cols].isna().all(axis=1)
]
This is often more meaningful than testing the entire row.
6. Drop rows missing a required business key
df_clean = df.dropna(
subset=["order_id"]
)
For several required fields:
df_clean = df.dropna(
subset=["order_id", "customer_id"]
)
Use this only when a missing key genuinely makes the record unusable.
7. Require at least N non-null values
df_clean = df.dropna(
thresh=3
)
This keeps rows with at least three non-null values across the DataFrame.
You can apply the rule to selected columns:
df_clean = df.dropna(
subset=["email", "phone", "mobile"],
thresh=1,
)
This keeps rows where at least one contact field is present.
8. Drop columns that are entirely missing
df_clean = df.dropna(
axis="columns",
how="all",
)
This is safe when a column contains no information at all.
Do not automatically drop a mostly-null column if rare values are business-critical.
9. Fill a categorical missing value explicitly
df["country"] = (
df["country"]
.fillna("Unknown")
)
Use a label such as "Unknown" only when that value clearly means:
the value is not known
Do not use a real category such as "US" as a default unless the business rule guarantees it.
10. Fill numeric values only when zero is semantically correct
df["discount_amount"] = (
df["discount_amount"]
.fillna(0)
)
This is correct only if missing means:
no discount was applied
It is wrong if missing means:
discount data was unavailable
Zero and unknown are different states.
11. Fill from another column
df["billing_country"] = (
df["billing_country"]
.fillna(df["shipping_country"])
)
Use this only when the fallback is valid according to the business rule.
12. Forward-fill values
df["status"] = df["status"].ffill()
This is useful when rows represent an ordered sequence and a value remains valid until replaced.
Example:
df = df.sort_values(
["device_id", "event_time"]
)
df["status"] = (
df.groupby("device_id")["status"]
.ffill()
)
Always define the correct sort order and grouping scope before forward-filling.
13. Backward-fill values
df["status"] = df["status"].bfill()
Use backward fill only when a future known value is a valid replacement for earlier missing rows.
This is less common in transactional production data and should be justified explicitly.
14. Interpolate numeric values
df["temperature"] = (
df["temperature"]
.interpolate()
)
For time-indexed data:
df = df.sort_values("event_time").set_index("event_time")
df["temperature"] = (
df["temperature"]
.interpolate(method="time")
)
Interpolation is appropriate for some continuous measurements.
It is usually inappropriate for:
- IDs
- categories
- financial transaction amounts
- discrete events
- business keys
15. Preserve nullable integer semantics
A regular NumPy integer column cannot represent NaN.
Use pandas’ nullable integer dtype when missing integers are valid.
df["customer_age"] = (
pd.to_numeric(
df["customer_age"],
errors="coerce",
)
.astype("Int64")
)
This preserves integer values while allowing pd.NA.
16. Use nullable string dtype
df["customer_name"] = (
df["customer_name"]
.astype("string")
)
This provides more consistent missing-value behavior than generic object columns.
Check:
print(df["customer_name"].dtype)
17. Convert invalid numeric values to missing values for inspection
parsed_amount = pd.to_numeric(
df["amount"],
errors="coerce",
)
Find rows that failed conversion:
failed = df.loc[
df["amount"].notna()
& parsed_amount.isna()
]
print(failed)
Do not overwrite the source column until you understand why parsing failed.
18. Convert invalid dates to NaT and inspect failures
parsed_date = pd.to_datetime(
df["event_time"],
errors="coerce",
)
Find failed parses:
failed_dates = df.loc[
df["event_time"].notna()
& parsed_date.isna()
]
print(failed_dates)
NaT is pandas’ missing datetime value.
19. Detect empty strings separately from missing values
An empty string is not necessarily treated as missing.
empty_strings = (
df.select_dtypes(include=["object", "string"])
.apply(lambda s: s.eq("").sum())
.sort_values(ascending=False)
)
print(empty_strings)
Also inspect whitespace-only strings:
whitespace_only = (
df.select_dtypes(include=["object", "string"])
.apply(
lambda s: (
s.astype("string")
.str.strip()
.eq("")
.sum()
)
)
)
print(whitespace_only)
Do not assume source systems encode absence consistently.
20. Normalize known null-like strings carefully
Suppose the source uses values such as:
NULL
N/A
None
unknown
Inspect first:
null_like = {
"",
"null",
"none",
"n/a",
"na",
}
for col in df.select_dtypes(
include=["object", "string"]
).columns:
normalized = (
df[col]
.astype("string")
.str.strip()
.str.lower()
)
matches = normalized.isin(null_like).sum()
if matches:
print(col, matches)
Only convert these values to missing after confirming they really represent absence in that field.
21. Replace known sentinel values with missing values
Example:
df["temperature"] = (
df["temperature"]
.replace(-9999, pd.NA)
)
This is appropriate only when -9999 is documented as a source-system sentinel.
Do not guess sentinel values from unusual-looking data.
22. Fill by group
Example: fill missing regional manager from known values within each region.
df["regional_manager"] = (
df.groupby("region")["regional_manager"]
.transform(
lambda s: s.ffill().bfill()
)
)
Use this only when values are stable within the group and row ordering is appropriate.
A group-level fill can silently spread bad data if the grouping key is wrong.
23. Compare before and after missing-value handling
before = df["country"].isna().sum()
df["country"] = (
df["country"]
.fillna("Unknown")
)
after = df["country"].isna().sum()
print("Before:", before)
print("After:", after)
Production transformations should make the expected data-quality change explicit.
24. Assert required fields after cleaning
assert df["order_id"].notna().all()
assert df["customer_id"].notna().all()
For several fields:
required = [
"order_id",
"customer_id",
"event_time",
]
assert df[required].notna().all().all()
Assertions are useful in controlled pipelines, but production systems may need structured validation and error handling instead of hard process termination.
What the result means
| Value / method | Meaning |
|---|---|
np.nan |
Common floating-point missing-value marker. |
pd.NA |
pandas nullable missing-value scalar used by nullable dtypes. |
NaT |
Missing datetime/timedelta value. |
isna() |
Detects recognized pandas missing values. |
notna() |
Detects non-missing values. |
dropna() |
Removes rows or columns according to missing-value rules. |
fillna() |
Replaces missing values with explicit values. |
ffill() |
Carries the last valid value forward. |
bfill() |
Carries the next valid value backward. |
interpolate() |
Estimates intermediate values according to a method. |
The important point is semantic:
missing != zero
missing != empty string
missing != false
missing != "Unknown"
unless your business rules explicitly make them equivalent.
What to look for
Null percentage suddenly increased
Possible causes:
- upstream schema change
- failed join
- parsing error
- missing source partition
- changed source semantics
Compare with previous runs before filling the values away.
Required identifier is missing
This is usually more serious than a missing optional description.
Prioritize missingness by business importance, not only by percentage.
Missing values appear only after a merge
Likely causes:
- unmatched keys
- key-format differences
- incomplete lookup table
- incorrect join columns
Investigate the merge before filling enrichment fields.
Numeric nulls appear after conversion
The source probably contained non-numeric values.
Inspect failed conversions rather than treating them as genuine missing values.
Date nulls appear after parsing
Inspect:
- unexpected format
- impossible dates
- timezone text
- mixed date conventions
A category has both null and "Unknown"
These are now two different states.
That may be correct, but it should be intentional.
Production notes
Missingness is business logic
There is no universal rule such as:
< 5% missing -> drop
> 5% missing -> fill
The correct action depends on:
- field semantics
- source reliability
- downstream use
- regulatory requirements
- reporting expectations
Filling values can destroy information
Once you replace nulls with 0, "Unknown", or another value, you may lose the distinction between:
- originally observed value
- missing source value
- imputed value
For important data, consider preserving an indicator:
df["country_was_missing"] = (
df["country"].isna()
)
df["country"] = (
df["country"]
.fillna("Unknown")
)
Null behavior depends on dtype
pandas may use different missing markers depending on dtype.
Prefer modern nullable dtypes when missing values are part of the model.
Examples:
Int64
boolean
string
dropna() can remove large amounts of data
Always measure the impact first.
before = len(df)
candidate = df.dropna(
subset=["customer_id"]
)
removed = before - len(candidate)
print("Rows removed:", removed)
Forward fill requires ordering
This is unsafe:
df["status"].ffill()
if the rows are not in the correct business sequence.
Sort first.
Imputation changes data
Mean, median, interpolation, forward-fill, and other imputation methods produce derived values.
If the distinction matters, track that the value was imputed.
Large DataFrames
Missing-value operations often scan entire columns.
On large data:
- inspect targeted columns first
- avoid repeated full-frame scans
- select only required columns
- push validation closer to the source when possible
Common mistakes
1. Filling every null with zero
Zero is a real value.
Missing means unknown/absent unless the business rule says otherwise.
2. Calling dropna() with no subset
This can delete rows because of irrelevant optional fields.
Target the fields that actually determine row validity.
3. Using == np.nan
This does not reliably detect missing values.
Use:
isna()
4. Treating empty strings as automatically missing
Some systems distinguish:
NULL
""
" "
Normalize only when the business semantics support it.
5. Forward-filling unsorted data
This can propagate values across the wrong sequence.
Sort and group correctly first.
6. Filling after a bad join
If nulls were created because keys failed to match, filling them can hide the join defect.
Fix the match problem first.
7. Overwriting failed conversions immediately
This destroys evidence.
Create a parsed Series, inspect failures, then assign intentionally.
8. Dropping a high-null column without checking rare value importance
A column may be 99% null and still contain critical exceptions.
Inspect actual use before removal.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.