Replace Placeholder Text with NaN in pandas

People who maintain spreadsheets rarely leave a cell empty on purpose. They type "n/a", a dash, "TBC", "unknown", -999 in a numeric column, or 31/12/9999 as an end date that has not happened yet. To pandas these are ordinary values: a score column containing "-" is read as text, so it cannot be summed, and -999 drags every average down. This guide, part of Handling Missing Data in Excel Reports, turns placeholders into real missing values at read time and afterwards, and shows how to avoid the opposite mistake of destroying legitimate values.

Prerequisites

pip install pandas openpyxl

import datetime as dt

import numpy as np
import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["country", "score", "closed", "comment"])
ws.append(["NA", -999, dt.datetime(9999, 12, 31), "None"])
ws.append(["DE", 12, dt.datetime(2026, 3, 1), "n/a"])
ws.append(["N/A", "-", None, "#N/A"])
ws.append(["FR", "TBC", dt.datetime(2026, 4, 1), " "])
wb.save("survey.xlsx")

Step 1 — See what pandas does by default

df = pd.read_excel("survey.xlsx")
print(df)
print(df["score"].dtype)          # object

The default read already converts some placeholders — and gets two cells wrong. It recognises "N/A", "n/a", "#N/A" and the empty cell, because they are on pandas' built-in list of missing-value strings. It also converts "NA" (the country code for Namibia) and "None" (a real answer in the comment column) to NaN, because those are on the list too. Meanwhile "-", "TBC" and -999 survive, so the score column is text and cannot be summed. The single space in the last comment survives as well.

Step 2 — Take control with per-column na_values

read_excel accepts na_values as a dictionary keyed by column name. Combined with keep_default_na=False, only the values you list count as missing, so you decide column by column:

NA_RULES = {
    "country": ["", "N/A"],
    "score": ["", "-", "TBC", -999],
    "closed": [""],
    "comment": ["", "n/a", "#N/A"],
}
df = pd.read_excel("survey.xlsx", keep_default_na=False, na_values=NA_RULES)
print(df["country"].tolist())     # ['NA', 'DE', nan, 'FR']
print(df["score"].tolist())       # [nan, 12.0, nan, nan]
print(df["comment"].tolist())     # ['None', nan, nan, ' ']

Namibia and "None" survive, and the score column is now numeric. Note the empty string in every list: once keep_default_na=False is set, blank cells are no longer treated as missing unless you say so. Leave "" out of the closed list and the empty cell arrives as an empty string, turning the whole date column into text — a quiet failure that only shows up later when a date calculation breaks.

Step 3 — Handle whitespace-only cells

na_values compares exact strings, so " ", " " and a non-breaking space would each need their own entry. Convert every whitespace-only text cell after reading instead:

text_cols = df.select_dtypes(include=["object", "string"]).columns
df[text_cols] = df[text_cols].replace(r"^\s*$", np.nan, regex=True)
print(df["comment"].tolist())     # ['None', nan, nan, nan]

The anchored pattern only matches cells that contain nothing but whitespace, so "None" and other real text are untouched. Wider whitespace problems are covered in Strip Whitespace and Normalise Text Columns with pandas.

Step 4 — Mask sentinel numbers and dates

Some placeholders are values of the right type: -999 or 9999 in a numeric column, 31/12/9999 or 01/01/1900 in a date column. When they arrive through a path where na_values does not apply — a frame from another function, or a sentinel that depends on the column — mask them explicitly:

scores = pd.Series([-999, 12, 9999, 40])
print(scores.mask(scores.isin([-999, 9999])).tolist())   # [nan, 12.0, nan, 40.0]

closed = pd.read_excel("survey.xlsx", keep_default_na=False, na_values=NA_RULES)["closed"]
far_future = closed.dt.year >= 9999
print(closed.mask(far_future).tolist())

Before masking a date sentinel, decide what it means. 31/12/9999 usually means "still open", which is a different fact from "unknown". If reports need to tell the two apart, keep a boolean is_open column before replacing the date with NaT.

Step 5 — Build the rules from the data, not from memory

Placeholder habits vary by team and by column. Find the candidates by listing the non-numeric values in columns that should be numeric, and the most frequent short strings in text columns:

raw = pd.read_excel("survey.xlsx", keep_default_na=False, dtype=str)
for col in ["score"]:
    as_num = pd.to_numeric(raw[col], errors="coerce")
    print(col, raw.loc[as_num.isna(), col].value_counts().to_dict())
# score {'-': 1, 'TBC': 1}

Reading with dtype=str and keep_default_na=False shows every value exactly as typed, so nothing is converted before you have looked at it. Review the list with whoever owns the sheet; "TBC" might need to become a separate status rather than a blank. Run the same check on every sheet you receive, because new placeholders appear whenever a new person starts filling in the workbook. A full missing-value report is covered in Find and Report Missing Values in an Excel File.

Step 6 — Keep the meaning in a status column

Not every placeholder means "unknown". In the score column, "TBC" says a score is expected, "-" says the question was skipped, and -999 is what the survey tool writes when the respondent never reached the question. Once all three become NaN, a report can no longer say how many scores are still to come. Record the reason in its own column before blanking the value:

raw_scores = pd.read_excel("survey.xlsx", keep_default_na=False, dtype={"score": str})["score"]
STATUS = {"TBC": "pending", "-": "skipped", "-999": "not reached"}
status = raw_scores.map(STATUS).fillna("answered")
score = pd.to_numeric(raw_scores.where(~raw_scores.isin(STATUS.keys())), errors="coerce")
result = pd.DataFrame({"score": score, "score_status": status})
print(result.values.tolist())
# [[nan, 'not reached'], [12.0, 'answered'], [nan, 'skipped'], [nan, 'pending']]
print(result["score_status"].value_counts().to_dict())

The numeric column is clean for sums and averages, and the status column answers the questions the placeholders were really trying to express: three missing scores, but only one of them is still expected. Keep the STATUS dictionary next to your NA_RULES so the two stay consistent; any value that is in one but not the other is a rule someone forgot to write.

Step 7 — Excel error cells

Cells that show #DIV/0!, #VALUE! or #REF! in Excel are read as NaN by both the openpyxl and calamine engines — indistinguishable from blanks. If an error means the source workbook is broken and the report should not run, check for errors before reading with pandas:

wb = Workbook()
ws = wb.active
ws.append(["value"])
for v in [10, "#DIV/0!", "#REF!", 7]:
    ws.append([v])
wb.save("errors.xlsx")

from openpyxl import load_workbook

errors = [c.coordinate for row in load_workbook("errors.xlsx").active.iter_rows()
          for c in row if c.data_type == "e"]
print(errors)                                         # ['A3', 'A4']
print(pd.read_excel("errors.xlsx")["value"].tolist())  # [10.0, nan, nan, 7.0]

Failing on errors rather than blanking them keeps a broken lookup in the source from turning into a quietly smaller total. Broken references themselves are covered in Find External Links and Broken References in a Workbook with Python.

Common pitfalls

• Global na_values lists. A flat list applies to every column, so a rule meant for scores ("-") also blanks a comment that is just a dash. Use a dictionary.
• Forgetting "" with keep_default_na=False. Blank cells become empty strings and date or number columns turn into text.
• Placeholders with meaning. "TBC", "exempt" and "not applicable" can be different facts. Blanking them loses information; map them to a status column first.
• Rules that drift between scripts. Two jobs reading the same sheet with different placeholder lists will disagree on every count. Keep NA_RULES in one shared module and import it.
• Numbers typed as placeholders. A 0 can be a real zero or someone's way of saying "no data". Only the sheet owner knows; do not guess, ask.
• Replacing after arithmetic. A -999 that entered a sum before being masked has already changed the result. Clean at read time.

Conclusion

Default missing-value handling in pandas is both too eager ("NA", "None") and too timid ("-", "TBC", -999). Discover the placeholders each column actually uses, read with keep_default_na=False and a per-column na_values dictionary that always includes "", blank whitespace-only cells with an anchored pattern, and mask sentinel numbers and dates after deciding what they mean. Check for Excel error cells separately when they should stop the run.

Frequently asked questions

Which strings does pandas treat as missing by default? The empty string plus "#N/A", "#N/A N/A", "#NA", "-1.#IND", "-1.#QNAN", "-NaN", "-nan", "1.#IND", "1.#QNAN", "", "N/A", "NA", "NULL", "NaN", "None", "n/a", "nan" and "null". Anything else, such as "-" or "TBC", stays as text.

Why did the country code NA disappear? The string "NA" is on pandas' default list of missing-value strings, so Namibia's code is read as NaN. Turn the defaults off for that column by passing keep_default_na=False with an explicit na_values dictionary.

Why are my empty cells empty strings after using keep_default_na=False? With keep_default_na=False, only the values you list are treated as missing, and that includes the empty string. Add "" to the list for every column where blank cells should be NaN.

How are Excel error values like #DIV/0! read? As NaN, with both the openpyxl and calamine engines. If you need to know which cells held errors rather than blanks, inspect the workbook with openpyxl, where error cells have data_type "e".

Related

• Up one level: Handling Missing Data in Excel Reports — everything about gaps in Excel data.
• Fill Missing Values in Excel with pandas fillna — what to do once gaps are real NaN.
• Drop Rows with Missing Required Fields in pandas — enforcing required values.
• Replace Values in an Excel Column with pandas — recoding values that are not placeholders.