Flag Rows with Too Many Missing Values

Column-level checks — "the Email column is 12% empty" — tell you about a field. They do not tell you which records are unusable. A customer row missing an email is fine; a row missing the name, the account number and the country is a half-entered record that will end up in totals, mailing lists and deduplication as noise. Finding such rows is a row-wise question: how many of this record's fields are empty, and are the empty ones the fields that matter? This page answers it with pandas on a typical form-filled sheet: it turns placeholders such as "n/a", "-" and "TBC" into real missing values, counts missing fields per row, weights required fields more heavily than optional ones, applies a threshold, summarises completeness by the person who entered each record, and writes a workbook that highlights incomplete rows and lists them for follow-up. It belongs to Handling Missing Data in Excel Reports.

Prerequisites

pip install pandas openpyxl

The sample is a 40-row account list entered by three people, with the usual gaps: empty cells, and cells holding "n/a", "-", "TBC" or a single space instead of a value. Three records were clearly abandoned half-way.

Step 1 — Make every kind of "missing" look the same

import pandas as pd

raw = pd.read_excel("accounts.xlsx", dtype=str, keep_default_na=False)
PLACEHOLDERS = {"", "n/a", "na", "-", "--", "tbc", "tbd", "none", "unknown", "?"}

def to_missing(s):
    cleaned = s.str.strip()
    return cleaned.mask(cleaned.str.lower().isin(PLACEHOLDERS))

df = raw.apply(to_missing)
print("empty cells as read:", int((raw == "").sum().sum()), "| missing after cleaning:", int(df.isna().sum().sum()))
print(df.isna().sum().to_string())

Reading with dtype=str and keep_default_na=False gives the cells exactly as typed, so the code — not pandas' defaults — decides what counts as missing. Every value is trimmed, so a lone space counts as empty, and the placeholders people type when they have nothing to enter become real missing values. Counting before and after shows how much of the missingness was disguised as text: only 15 cells were literally empty, but 53 are missing once placeholders are recognised, so a column-level check on the raw data would have reported most of these fields as far more complete than they are. Keep the placeholder list short and explicit, and review it against the actual values in your data — a column of product codes may legitimately contain "NA" for North America, in which case that column needs its own rule.

Step 2 — Score each row

Not every field matters equally. The account number and company name are essential; industry and employee count are nice to have. Give each column a weight and score each row by the weighted share of its fields that are missing:

WEIGHTS = {"Account": 3, "Company": 3, "Contact": 2, "Email": 2, "Phone": 1, "Country": 2, "Industry": 1, "Employees": 1}
cols = list(WEIGHTS)
missing = df[cols].isna()
df["missing_fields"] = missing.sum(axis=1)
df["missing_score"] = (missing * pd.Series(WEIGHTS)).sum(axis=1) / sum(WEIGHTS.values())
df["missing_list"] = missing.apply(lambda r: ", ".join(c for c in cols if r[c]), axis=1)
REQUIRED = ["Account", "Company"]
df["required_missing"] = df[REQUIRED].isna().any(axis=1)

THRESHOLD = 0.25
df["flag"] = df["required_missing"] | (df["missing_score"] >= THRESHOLD)
print(df.loc[df["flag"], ["Account", "missing_fields", "missing_score", "missing_list"]].round(2).to_string(index=False))

isna().sum(axis=1) counts missing fields per row; multiplying the missing mask by the weights and dividing by their total gives a score from 0 (complete) to 1 (empty). A row is flagged when a required field is missing, whatever its score, or when a quarter or more of the weighted information is absent. The missing_list column spells out which fields are empty, which is what the person following up needs. Five of the forty rows are flagged. The three abandoned records — AC-1007, AC-1023 and AC-1031 — score 0.6 or more and are missing their company name, a required field. Two others cross the threshold without a required gap: AC-1011 is missing its contact, phone and country, and AC-1015 its email, industry and employee count.

Step 3 — Who needs to follow up

Missing data usually has a source. Summarise completeness by the person or system that entered the records:

by_person = df.groupby("Entered by").agg(records=("Account", "count"), flagged=("flag", "sum"),
                                         avg_score=("missing_score", "mean"))
by_person["flagged_share"] = by_person["flagged"] / by_person["records"]
print(by_person.round(2).to_string())
field_gaps = df[cols].isna().mean().sort_values(ascending=False)
print(field_gaps.round(2).to_string())

The per-person summary shows whether gaps are spread evenly or concentrated — here all three abandoned records were entered by ben, which suggests an interrupted session rather than a training issue, while amira's thirteen records are all complete enough. The per-field view puts industry and employee count at the top, each empty in 30% of records, with phone numbers close behind. The per-field view shows which fields are most often left empty across everyone, which points at the form rather than the people: a field that is empty for nearly a third of records is either unnecessary or badly explained. Missingness in general — which values are missing and in what patterns — is covered in Find and Report Missing Values in an Excel File.

Step 4 — Highlight flagged rows and write a follow-up list

from openpyxl import load_workbook
from openpyxl.styles import PatternFill

out = df.drop(columns=["required_missing"]).copy()
with pd.ExcelWriter("accounts_checked.xlsx", engine="openpyxl") as xw:
    out.to_excel(xw, sheet_name="Accounts", index=False)
    out[out["flag"]].sort_values("missing_score", ascending=False)[
        ["Account", "Company", "Entered by", "missing_score", "missing_list"]].to_excel(xw, sheet_name="Follow up", index=False)
    by_person.to_excel(xw, sheet_name="By person")

wb = load_workbook("accounts_checked.xlsx")
ws = wb["Accounts"]
flag_col = [c.value for c in ws[1]].index("flag") + 1
amber, grey = PatternFill("solid", fgColor="FFEB9C"), PatternFill("solid", fgColor="F2F2F2")
for row in ws.iter_rows(min_row=2):
    if row[flag_col - 1].value:
        for cell in row:
            cell.fill = amber
    for cell in row[:len(cols)]:
        if cell.value is None:
            cell.fill = grey if not row[flag_col - 1].value else PatternFill("solid", fgColor="F4B183")
wb.save("accounts_checked.xlsx")
print(f"{int(out['flag'].sum())} of {len(out)} rows flagged; follow-up list written")

Flagged rows are shaded amber across the whole record, and within them the empty cells are a darker orange, so the gaps are obvious at a glance; empty cells in unflagged rows get a light grey, visible but not alarming. The Follow up sheet lists flagged records worst first with exactly which fields are missing and who entered them, ready to send round. Placeholders were turned into true blanks along the way, so the cleaned sheet is also better input for whatever comes next. Run the same check every time the sheet is updated and compare the flagged list with the previous run: records that leave the list were fixed, and new entries show whether the problem is being solved or just moved.

Choosing the weights and threshold

The weights and the threshold encode a judgement about what makes a record usable, so set them with the people who use the data rather than by guesswork. A sales team may consider a record without a phone number useless; a finance team may not care about phone numbers at all but insist on a VAT number. Start with two tiers — required fields that flag a row on their own, and everything else with weight 1 — and only introduce finer weights if the flagged list turns out too long or too short. Then look at the score distribution before fixing the threshold: if most incomplete rows score around 0.1 and the abandoned ones above 0.5, any threshold in between works; if scores are spread evenly, the threshold is a policy decision and worth writing down next to the list it produces.

Common pitfalls

• Counting only empty cells. "n/a", "-" and single spaces are missing too.
• Treating all fields equally. A missing account number matters more than a missing industry.
• A threshold without required fields. A row missing only its ID may still score low.
• Flagging without saying why. List the missing fields for each flagged row.
• Blaming people for bad forms. Check which fields are empty for everyone.

Conclusion

To find incomplete records, read the sheet as text, turn placeholders and blanks into missing values, and score each row by the weighted share of its fields that are missing, with required fields able to flag a row on their own. Summarise completeness by who entered the records and by field, then write the data with flagged rows highlighted and a follow-up list that names the missing fields for each record.

Frequently asked questions

How do I count missing values per row in pandas? Use df.isna().sum(axis=1), after converting placeholders such as "n/a" and blank strings to missing values.

How do I treat "n/a" and "-" as missing when reading Excel? Read with dtype=str and keep_default_na=False, trim the values, and mask those that match a set of placeholder strings.

How can some columns count more than others? Multiply the missing mask by a Series of column weights, sum across the row and divide by the total weight to get a weighted missing share.

How do I highlight incomplete rows in the Excel output? Write the data with pandas, then use openpyxl to fill every cell in rows whose flag column is true.

Related

• Up one level: Handling Missing Data in Excel Reports — gaps in Excel data.
• Visualise Missing Data Patterns in an Excel File — seeing patterns of gaps.
• Drop Rows with Missing Required Fields in pandas — removing instead of flagging.
• Highlight Blank Cells with openpyxl — live highlighting of blanks.