Read Yes/No and TRUE/FALSE Columns as Booleans with pandas

Flags are everywhere in business spreadsheets — active customers, approved invoices, paid orders — and people record them however they like. One sheet uses Excel's own TRUE/FALSE, the next types "Yes" and "No", another uses Y/N codes, 1/0, or an "x" in the column when the answer is yes and nothing when it is no. pandas.read_excel turns only the first of those into booleans by itself. Its true_values and false_values options handle more, but with rules strict enough that they often do nothing and say nothing.

This page, part of Reading Excel Files with pandas, shows what each style of flag becomes, when the built-in options work, and how to convert every flag column into a boolean you can filter on and trust.

Prerequisites

pip install pandas openpyxl

The examples read flags.xlsx, which has one column for each style shown above.

Step 1 — See what read_excel does by default

Read the sheet with no options and look at the types:

import pandas as pd

raw = pd.read_excel("flags.xlsx")
print(raw.dtypes.to_dict())
print(raw)

Only active arrives as bool, because Excel stored it as real boolean cells and openpyxl hands pandas Python True and False. flag is int64, which is right for a number and wrong for a flag. Every text style — Yes/No, Y/N, the mixed-case opt_in, the tick marks — is a string column. Filtering on those with df[df["approved"]] raises an error or, worse, treats every non-empty string as true.

Step 2 — Use true_values and false_values for clean columns

read_excel accepts lists of values to treat as True and False. For a column spelled consistently and without blanks, this is all you need:

typed = pd.read_excel("flags.xlsx", true_values=["Yes", "Y", "yes"], false_values=["No", "N"])
print(typed.dtypes.to_dict())
print(typed[["approved", "paid", "opt_in"]])

approved becomes a proper bool column. The other two show the rules:

• All or nothing. A column is converted only when every non-empty value is in one of the two lists. opt_in holds "YES" and "No " with a trailing space; neither matches exactly, so the column is left as text — with no warning.
• Blanks break the type. paid has an empty cell. Its values are converted, but a NumPy bool column cannot hold a missing value, so pandas falls back to an object column of True, False and NaN, which behaves badly in filters.
• Exact matching. The comparison is case-sensitive and whitespace-sensitive; "yes" and "Yes" must both be listed.

The options apply to every column in the sheet, too, so a "Y" in a free-text notes column would be matched as well — another reason to prefer converting named columns explicitly.

Step 3 — Convert flag columns with a strict mapping

For sheets that people type into, normalise the text and map it yourself. Two decisions make the conversion trustworthy: use pandas' nullable "boolean" dtype, which holds True, False and <NA>, and raise an error on any value you did not expect, so a new spelling is reported instead of silently becoming missing:

TRUE_WORDS = {"yes", "y", "true", "t", "1", "x", "✓"}
FALSE_WORDS = {"no", "n", "false", "f", "0"}

def to_flag(series, blank_means=pd.NA):
    def convert(value):
        if pd.isna(value) or str(value).strip() == "":
            return blank_means
        if isinstance(value, bool):
            return value
        word = str(value).strip().lower()
        if isinstance(value, (int, float)) and value in (0, 1):
            word = str(int(value))
        if word in TRUE_WORDS:
            return True
        if word in FALSE_WORDS:
            return False
        raise ValueError(f"{series.name}: unexpected flag value {value!r}")
    return series.map(convert).astype("boolean")

df = pd.read_excel("flags.xlsx")
for col in ["active", "approved", "paid", "flag", "opt_in"]:
    df[col] = to_flag(df[col])
print(df.dtypes.to_dict())
print(df[["approved", "paid", "opt_in", "flag"]])

Every flag column is now boolean. paid keeps its blank as <NA>, which is honest: nobody recorded whether that order was paid. Filtering with df[df["paid"]] would fail on the missing value, so fill it according to the business rule before filtering — df["paid"].fillna(False) when a blank means "not yet", for example.

Step 4 — Treat a tick or a blank as yes or no

The done column uses the paper-form convention: an "x" when true, nothing when false. There a blank is not unknown — it is an answer. Pass the meaning of a blank explicitly:

df["done"] = to_flag(df["done"], blank_means=False)
print(df[["id", "done"]])
print(df["done"].dtype, df["done"].sum())

Making the rule an argument rather than a default keeps the two conventions apart in your code. A blank in "paid" and a blank in "done" mean different things, and the column-by-column calls say so.

Step 5 — Fail loudly on a value nobody expected

The ValueError in to_flag is the most useful part of it. A sheet that gains a third answer — "Pending", "N/A", "?" — should stop the job rather than become <NA> or False without anyone knowing:

survey = pd.DataFrame({"consent": ["Yes", "No", "Pending"]})
try:
    to_flag(survey["consent"])
except ValueError as exc:
    print(exc)
# consent: unexpected flag value 'Pending'

When a third state is legitimate, the column is not a boolean. Keep it as a category and decide explicitly how each state is reported. Validate Excel Columns Before Import with pandas shows how to collect errors like this across a whole sheet and report them together.

Step 6 — Write booleans back, and re-read them carefully

Writing a bool or boolean column produces real Excel TRUE/FALSE cells, and missing values become empty cells, so the file itself is exactly what a reader expects. Reading it back is where the asymmetry shows:

df[["id", "approved", "paid", "done"]].to_excel("flags_clean.xlsx", index=False)
back = pd.read_excel("flags_clean.xlsx")
print(back.dtypes.to_dict())
print(back[["approved", "paid"]])

approved and done return as bool. paid, which has an empty cell, returns as float64 holding 1.0, 0.0 and NaN: pandas cannot put a missing value in a NumPy bool column, so it falls back to floats. Run such columns through the same conversion on the way in — to_flag accepts 1.0 and 0.0 — and the round trip is complete:

back["paid"] = to_flag(back["paid"])
print(back["paid"].tolist(), back["paid"].dtype)
# [True, False, <NA>] boolean

This is the general rule for flag columns: convert them explicitly every time a DataFrame is built from a file, rather than trusting whatever type inference produced. A job that reads the same workbook every day can list its flag columns once and apply to_flag to each straight after read_excel.

If the report's readers expect "Yes" and "No" rather than TRUE and FALSE, map the column to text as the last step before writing — df["paid"].map({True: "Yes", False: "No"}) — and keep the boolean in your code. Converting to text early brings back every problem this page solves.

Common pitfalls

• Filtering on a text column. df[df["approved"] == True] on a column of "Yes"/"No" strings returns nothing; df[df["approved"].astype(bool)] returns every row, because non-empty strings are truthy.
• Using astype(bool) to convert. It turns "No", "False" and "0" into True. Map the words instead.
• Letting blanks become False by default. Decide per column whether a blank means "no" or "unknown".
• Summing a float flag column. A flag column that came back as 1.0/0.0/NaN still sums correctly, which hides the problem until someone filters on it or compares it with True. Check dtypes after every read.
• Listing values once. true_values is exact; if you keep it, include every case variant — or normalise first and skip it.

Performance notes

Series.map with a Python function processes each value in turn, which is fast enough for hundreds of thousands of rows. For millions, normalise with vectorised string methods — s.str.strip().str.lower() — then map with a dictionary, which pandas executes much faster than a function call per value, and check for unmapped values with isna() afterwards.

Conclusion

read_excel returns real booleans only for native TRUE/FALSE cells. true_values and false_values help with consistently spelled, blank-free text columns and silently skip everything else. For flags people type, normalise case and whitespace, map to pandas' nullable "boolean" dtype, state per column what a blank means, and raise on unexpected values so new spellings are noticed on the day they appear.

Frequently asked questions

Why did true_values not convert my column? pandas converts a column only when every non-empty value is in true_values or false_values, compared exactly. One blank, one "YES" when you listed "Yes", or a trailing space leaves the whole column as text.

What is the difference between bool and boolean in pandas? bool is NumPy's type and cannot hold a missing value. boolean (with a lower-case b, written "boolean") is pandas' nullable type: True, False or . Flag columns with blanks need the nullable one.

How are Excel TRUE and FALSE cells read? As real booleans. A column of native TRUE/FALSE cells arrives with dtype bool without any options, because openpyxl returns Python True and False for them.

Should 1 and 0 become booleans? Only if the column really is a flag. pandas leaves numeric 1/0 columns as integers, which is correct for counts. Convert a flag column explicitly with astype("boolean") after checking it holds only 0 and 1.

Related

• Up one level: Reading Excel Files with pandas — every option that shapes what read_excel returns.
• Use Converters When Reading Excel with pandas — applying a function to a column while reading.
• Excel IF Formulas as pandas Conditional Columns — building flags from other columns.
• Replace Placeholder Text with NaN in pandas — when "N/A" and "-" mean missing.