Guide

Advanced Data Transformation And CleaningDeep dive

Drop Rows with Missing Required Fields in pandas

dropna() with no arguments removes almost everything. Name the required columns, normalise blank-looking placeholders first, and keep the rejected rows with a reason and a source row number.

Dropping rows is the easiest data-quality decision to get wrong, because dropna() with no arguments does something almost nobody wants: it removes any row with a blank in any column, which on a real export means most of them. The useful version names the columns that genuinely must be present, counts what it removed, and keeps the removed rows where somebody can look at them. This guide is part of Handling Missing Data in Excel Reports.

Prerequisites

Bash

pip install pandas openpyxl

Python

import numpy as np
import pandas as pd

orders = pd.DataFrame({
    "Order_ID": [1001, 1002, None, 1004, 1005],
    "Region":   ["North", "  ", "West", "South", "North"],
    "Revenue":  [12400.0, 9800.5, 15320.25, np.nan, 7010.0],
    "Notes":    ["rush", None, None, None, "backorder"],
})

Notes is empty for three rows and that is fine — it is an optional column. Order_ID, Region and Revenue are not.

Why the default is wrong

Python

print(len(orders.dropna()))          # 2 of 5 — the Notes column decided

dropna() defaults to how="any" across every column, so an optional note being blank removes the row. On a forty-column export with a handful of optional fields, that routinely discards the majority of the data and looks, from the outside, like the source file was nearly empty.

Name the columns that matter

Python

REQUIRED = ["Order_ID", "Region", "Revenue"]

clean = orders.dropna(subset=REQUIRED)
print(f"{len(clean)} of {len(orders)} rows kept")

Declaring the required columns as a constant is worth more than the line it saves. It documents the contract, it can be asserted against the incoming file before anything else happens, and it is the same list the validation in Validate Excel Columns Before Import with Pandas checks for existence.

Blank is not always NaN

The Region value on row two is two spaces. It is not NaN, so dropna keeps it — and every subsequent group-by treats " " as its own region. Excel exports produce this constantly, along with "N/A", "-", "null" and empty strings.

Python

PLACEHOLDERS = {"", " ", "-", "--", "n/a", "N/A", "na", "null", "NULL", "#N/A"}

def normalise_blanks(frame: pd.DataFrame) -> pd.DataFrame:
    frame = frame.copy()
    for column in frame.select_dtypes(include=["object", "string"]).columns:
        stripped = frame[column].astype("string").str.strip()
        frame[column] = stripped.mask(stripped.str.casefold().isin(
            {p.casefold() for p in PLACEHOLDERS}
        ))
    return frame

orders = normalise_blanks(orders)
print(orders["Region"].isna().sum())          # now 1, as expected

Running that before any missing-data logic is what makes the rest of it behave predictably. Without it, dropna and isna() both report numbers that disagree with what a person sees on the sheet, which is the most confusing kind of disagreement to debug.

Keep what you dropped

The single most important habit in this whole area: never discard rows without keeping them somewhere.

Python

required_missing = orders[REQUIRED].isna().any(axis=1)
clean = orders[~required_missing].copy()
rejects = orders[required_missing].copy()

rejects["Reason"] = (
    orders.loc[required_missing, REQUIRED]
          .isna()
          .apply(lambda row: "missing " + ", ".join(row.index[row]), axis=1)
)

print(f"kept {len(clean)}, rejected {len(rejects)}")
print(rejects[["Order_ID", "Region", "Revenue", "Reason"]])

The Reason column is what makes the rejects actionable — "missing Revenue" and "missing Order_ID, Region" are different problems with different owners. Writing that frame to a second sheet of the output workbook costs one line and turns an invisible exclusion into something a recipient can fix.

Python

with pd.ExcelWriter("orders-clean.xlsx", engine="xlsxwriter") as writer:
    clean.to_excel(writer, sheet_name="Data", index=False)
    if not rejects.empty:
        rejects.to_excel(writer, sheet_name="Excluded rows", index=False)

Thresholds, and dropping by count

Sometimes the rule is not "these columns" but "enough columns". thresh keeps rows with at least a given number of non-null values.

Python

print(len(orders.dropna(thresh=3)))                       # at least 3 populated columns
print(len(orders.dropna(subset=REQUIRED, thresh=2)))      # at least 2 of the required three

The second form is the more useful one and the less known: combined with subset, thresh counts only within those columns. It suits the case where a record is usable with partial information — an order with a region and a value but no identifier might still count towards a regional total, and that is a business decision worth expressing explicitly rather than by omission.

Row numbers the source file recognises

A rejects sheet is only useful if the row numbers match the spreadsheet the user is looking at. pandas indexes from zero and the header takes row one, so the sheet row is the index plus two.

Python

rejects = rejects.reset_index().rename(columns={"index": "Source_row"})
rejects["Source_row"] = rejects["Source_row"] + 2

Doing that before writing the sheet is the difference between a rejects report somebody uses and one they give up on. The same adjustment appears wherever a validation result has to point back at a workbook, including Highlight Invalid Cells in Excel with Python.

Making the drop a checked step

A row count that changes between the read and the report is worth asserting on rather than discovering. Wrapping the whole operation in a small function that returns both frames and a summary makes it testable and gives the log something specific to say.

Python

from dataclasses import dataclass

@dataclass
class DropResult:
    kept: pd.DataFrame
    rejected: pd.DataFrame
    def summary(self) -> str:
        total = len(self.kept) + len(self.rejected)
        share = len(self.rejected) / total if total else 0.0
        return f"kept {len(self.kept):,} of {total:,} rows ({share:.1%} rejected)"

def drop_incomplete(frame: pd.DataFrame, required: list[str],
                    max_rejected_share: float = 0.05) -> DropResult:
    missing = frame[required].isna().any(axis=1)
    result = DropResult(kept=frame[~missing].copy(), rejected=frame[missing].copy())
    total = len(frame)
    if total and len(result.rejected) / total > max_rejected_share:
        raise ValueError(f"{result.summary()} — above the {max_rejected_share:.0%} threshold")
    return result

The threshold is the useful part. Losing one row in a thousand is normal; losing one in three means the source file changed shape, and continuing produces a report that is confidently wrong. Failing loudly at that point is the behaviour that catches an upstream schema change on the day it happens rather than at the end of the quarter, and it fits the pre-send checks in Validate an Excel Report Before Sending It.

Duplicates hide behind missing values

A related failure worth checking in the same pass: rows that are not missing anything but are duplicates of each other, often because a partially-blank row was re-entered rather than corrected.

Python

duplicates = clean[clean.duplicated(subset=["Order_ID"], keep=False)]
if not duplicates.empty:
    print(f"{len(duplicates)} row(s) share an Order_ID:")
    print(duplicates.sort_values("Order_ID")[["Order_ID", "Region", "Revenue"]])

keep=False marks every member of each duplicate group rather than just the repeats, which is what you want when reporting them — the person fixing the file needs to see both rows to decide which is correct. Handling them properly is covered in Find Duplicate Rows in Excel with Python, and running both checks together is worth the extra line because they usually have the same cause.

Common pitfalls

Symptom	Cause	Fix
Almost every row is dropped	`dropna()` with no subset	Pass `subset=REQUIRED`
A blank-looking value is kept	It is a space or `"N/A"`, not NaN	Normalise placeholders to NaN first
Totals do not match the source	Rows were dropped silently	Keep and report a rejects frame
`SettingWithCopyWarning` after filtering	The filtered frame is a view	Add `.copy()` after the boolean selection
Row numbers in the report do not match	Zero-based index reported	Add two for the header and one-based rows
Numeric column full of NaN after cleaning	Numbers stored as text, coerced to NaN	Check the dtype before assuming data loss

Deciding between dropping and filling

Dropping is not always the right answer, and the choice comes down to what the missing value means. A missing identifier makes the row unusable and it should be dropped. A missing optional note means nothing and should be left alone. A missing measurement is the interesting case: dropping it biases the average, filling it with zero biases it differently, and interpolating asserts something about the data that may not be true.

The rule that holds up is to drop only what makes a row unusable, and to make every other decision visible in the output — a count of imputed values, a flag column, a note in the report. The alternatives are covered in Fill Missing Values in Excel with pandas fillna and Interpolate Missing Numeric Values in Excel Data.

Performance and scale

dropna is a single pass and costs almost nothing. What does cost is the placeholder normalisation, because it touches every string cell — on a wide export with thirty text columns that is a real amount of work, and it is worth limiting to the columns that matter.

Python

# Normalise only the columns the pipeline depends on
for column in REQUIRED:
    if orders[column].dtype == "object":
        stripped = orders[column].astype("string").str.strip()
        orders[column] = stripped.mask(stripped == "")

The other scale consideration is where in the pipeline the drop happens. Doing it immediately after the read means every later step works on fewer rows, and it means the rejects are captured before any transformation has had a chance to obscure why a row was unusable.

Conclusion

Never call dropna() bare. Name the required columns in a constant, normalise blank-looking placeholders to NaN first so the check sees what a person sees, and split the frame into kept and rejected rather than filtering in place. Write the rejects to a second sheet with a reason and a source row number, and reserve dropping for rows that are genuinely unusable — everything else is a fill decision that should be visible in the output.

Frequently asked questions

What is the difference between dropna(how='any') and a subset? how='any' drops a row if any column is missing, which on a wide export removes almost everything because one optional note column is blank. A subset limits the check to the columns that genuinely must be present, which is nearly always what you want.

Do empty Excel cells always become NaN? No. A truly empty cell does, but a cell containing a space, an empty string, or a text placeholder like 'N/A' or '-' does not — it becomes a value. Normalising those to NaN before the check is what makes dropna behave the way people expect.

Should I drop the rows or report them? Both. Drop them from the working frame so the calculations are correct, and keep them in a rejects frame so somebody can see what was excluded. A report that quietly loses rows is the failure mode this whole practice exists to prevent.

How do I drop a column that is mostly empty? dropna(axis=1, thresh=n) keeps only columns with at least n non-null values. It is useful for exploring an unfamiliar export and dangerous in a pipeline, where a column becoming empty is a signal rather than a nuisance.

Up one level: Handling Missing Data in Excel Reports — the wider set of strategies for gaps.
Find and Report Missing Values in an Excel File — measuring the gaps before deciding what to do.
Fill Missing Values in Excel with pandas fillna — the alternative to dropping, and when it is honest.
Validate Excel Columns Before Import with Pandas — asserting the required-column contract at the read.
Remove Blank Rows from Excel with Pandas — the simpler case of rows that are entirely empty.