Browse cheat sheets · Python

Technology: PythonCategory: Data CleaningDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Count missing values by column
  5. 2. Calculate missing-value percentages
  6. 3. Find rows with a missing value in one column
  7. 4. Find rows with any missing value
  8. 5. Find rows where all selected fields are missing
  9. 6. Drop rows missing a required business key
  10. 7. Require at least N non-null values
  11. 8. Drop columns that are entirely missing
  12. 9. Fill a categorical missing value explicitly
  13. 10. Fill numeric values only when zero is semantically correct
  14. 11. Fill from another column
  15. 12. Forward-fill values
  16. 13. Backward-fill values
  17. 14. Interpolate numeric values
  18. 15. Preserve nullable integer semantics
  19. 16. Use nullable string dtype
  20. 17. Convert invalid numeric values to missing values for inspection
  21. 18. Convert invalid dates to NaT and inspect failures
  22. 19. Detect empty strings separately from missing values
  23. 20. Normalize known null-like strings carefully
  24. 21. Replace known sentinel values with missing values
  25. 22. Fill by group
  26. 23. Compare before and after missing-value handling
  27. 24. Assert required fields after cleaning
  28. What the result means
  29. What to look for
  30. Null percentage suddenly increased
  31. Required identifier is missing
  32. Missing values appear only after a merge
  33. Numeric nulls appear after conversion
  34. Date nulls appear after parsing
  35. A category has both null and "Unknown"
  36. Production notes
  37. Missingness is business logic
  38. Filling values can destroy information
  39. Null behavior depends on dtype
  40. dropna() can remove large amounts of data
  41. Forward fill requires ordering
  42. Imputation changes data
  43. Large DataFrames
  44. Common mistakes
  45. 1. Filling every null with zero
  46. 2. Calling dropna() with no subset
  47. 3. Using == np.nan
  48. 4. Treating empty strings as automatically missing
  49. 5. Forward-filling unsorted data
  50. 6. Filling after a bad join
  51. 7. Overwriting failed conversions immediately
  52. 8. Dropping a high-null column without checking rare value importance
  53. Next step
  54. 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.

Next step

Use the missing-value pattern to decide what to do next.

  • Required keys are missing → trace upstream source quality before downstream processing.
  • Nulls appeared after a join → inspect unmatched keys and join cardinality.
  • Nulls appeared after parsing → inspect invalid source values and type conversion rules.
  • Missing value has a valid default → fill explicitly and document the rule.
  • Missing value means unknown → consider preserving null or using a clearly labeled category.
  • Rows are unusable without the field → drop only the affected subset and measure the impact.
  • Time-series gaps are expected → consider interpolation or forward fill only with correct ordering and semantics.
  • Missingness is operationally important → add validation metrics and trend the null rate over time.

← All cheat sheets