Use Nullable Data Types When Reading Excel with pandas

A quantity column in Excel holds whole numbers. A "Shipped" column holds TRUE and FALSE. Read the sheet with pd.read_excel and both can arrive as floats — 5.0, 1.0, 0.0 — because one blank cell somewhere in the column forced pandas to use NaN, and the classic NumPy integer and boolean types have no way to store a missing value. The numbers still add up, but IDs print as 1.200340e+05, flags become arithmetic, and a report written back from the DataFrame shows 5.0 where the source said 5. pandas has had a fix for years: nullable data types, which keep the column's real type and mark gaps as <NA>. This page shows how to ask read_excel for them, per file or per column, and what changes downstream. It belongs to Reading Excel Files with Pandas.

Prerequisites

pip install "pandas>=2.0" openpyxl pyarrow

The dtype_backend argument arrived in pandas 2.0; everything below was run on pandas 3.0. pyarrow is only needed for the Arrow-backed variant. The sample file orders.xlsx has four orders. Order 1002 has no quantity, discount or customer, and order 1003 has no shipped flag and no region — the kind of gaps any hand-maintained sheet has.

Step 1 — See what the default read does

import pandas as pd

df = pd.read_excel("orders.xlsx")
print(df.dtypes)
print(df)

OrderID          int64
Qty            float64
Discount       float64
Shipped        float64
Region             str
Customer ID    float64

Only OrderID, the one numeric column without a gap, stays an integer. Qty and Customer ID became float64 because NumPy's int64 cannot hold NaN. Shipped is the surprising one: a column of TRUE, FALSE and one blank comes back as 1.0, 0.0 and NaN, because pandas falls back to the nearest NumPy type that can hold a missing value, and for booleans that is a float. A filter such as df[df["Shipped"]] now raises an error instead of selecting shipped orders.

Step 2 — Ask for nullable types for the whole file

nullable = pd.read_excel("orders.xlsx", dtype_backend="numpy_nullable")
print(nullable.dtypes)
print(nullable)

OrderID          Int64
Qty              Int64
Discount       Float64
Shipped        boolean
Region          string
Customer ID      Int64

Every column now has the type its values actually have, and each gap shows as <NA>. Note the capital letters: Int64, Float64, boolean and string are pandas' extension types, which store a separate mask of missing positions next to the values, so a missing entry no longer needs a float to represent it. Customer IDs print as 120034 again, quantities as 5, and nullable[nullable["Shipped"].fillna(False)] selects the shipped orders.

The other option is Arrow-backed columns:

arrow = pd.read_excel("orders.xlsx", dtype_backend="pyarrow")
print(arrow.dtypes)

This gives int64[pyarrow], double[pyarrow], bool[pyarrow] and string[pyarrow]. The values and the <NA> markers are the same; the storage is Apache Arrow memory, which uses less RAM for text and hands columns to Polars, DuckDB or Parquet without copying. For a report pipeline that stays inside pandas, numpy_nullable is the conservative choice; pick pyarrow when the next step is Parquet or another Arrow tool, as in Convert Excel Files to Parquet with Python.

Step 3 — Set types for specific columns only

You do not have to switch the whole file. The dtype argument accepts the extension type names per column, which is handy when only a couple of columns matter or when the rest of the code expects classic NumPy types:

mixed = pd.read_excel("orders.xlsx", dtype={"Qty": "Int64", "Shipped": "boolean", "Customer ID": "Int64"})
print(mixed.dtypes)

Qty, Shipped and Customer ID come back nullable; Discount stays float64 and Region keeps the default text type. If a column you mark Int64 contains a real decimal such as 0.05, the read fails with TypeError: cannot safely cast non-equivalent object to int64 instead of silently rounding — a free check that an integer column really holds integers. The message names the sheet but not the cell, so find the offending values with df[col] % 1 != 0 on a default read. For identifiers that must keep leading zeros, read them as text instead, as described in Keep Leading Zeros When Reading Excel with pandas.

Step 4 — Why convert_dtypes after the read is too late

DataFrame.convert_dtypes() converts an existing frame to nullable types, and it is tempting to read normally and convert afterwards. For integers that works. For booleans it does not:

late = pd.read_excel("orders.xlsx").convert_dtypes()
print(late["Shipped"].dtype, late["Shipped"].tolist())

The output is Int64 with [1, 0, <NA>, 1]. By the time convert_dtypes runs, the default read has already turned TRUE and FALSE into 1.0 and 0.0, and nothing in the frame remembers they were booleans. Choose the type at read time — through dtype_backend or dtype — so that the reader sees the original cell values.

Step 5 — Work with NA values downstream

Nullable columns behave like their NumPy cousins with one difference: a comparison involving a missing value returns <NA> instead of False. Aggregations are unaffected, but filters and if tests need a decision about what a missing value means:

df = nullable
print(df["Qty"].sum(), df["Qty"].mean())
mask = df["Qty"] > 4
print(mask.tolist())
print("big:", df[mask]["OrderID"].tolist(), "not big:", df[~mask]["OrderID"].tolist())

sum and mean skip missing values exactly as before (14 and 4.67). The mask is [True, <NA>, True, False], and when you index with it pandas treats <NA> as "do not select". That looks harmless until you take the opposite filter: ~mask is [False, <NA>, False, True], so order 1002 appears in neither list. With the default float columns the same comparison gives False for NaN, and the inverted mask would have picked 1002 up. Any report that splits rows into "above" and "below" a threshold therefore needs an explicit choice:

split = mask.fillna(False)
print("big:", df[split]["OrderID"].tolist(), "rest:", df[~split]["OrderID"].tolist())
try:
    if df.loc[1, "Qty"] > 4:
        pass
except TypeError as exc:
    print("TypeError:", exc)

With fillna(False) the missing quantity lands in "rest". Plain Python if statements and numpy.where refuse to guess and raise TypeError: boolean value of NA is ambiguous, which is the behaviour you want — it points at the exact place a missing value needs handling. String methods on a string column return nullable results too, isna() works on every column regardless of backend, and integer arithmetic stays integer: df["Qty"] * 2 is Int64, not float.

Step 6 — Write the frame back to Excel

nullable.to_excel("orders_out.xlsx", index=False)

from openpyxl import load_workbook
for row in load_workbook("orders_out.xlsx").active.iter_rows(min_row=2, values_only=True):
    print(row)

Missing values are written as empty cells, integers as integers and booleans as real TRUE/FALSE cells, so the output looks like the input. The round trip is lossless for these types, which matters when another script reads the output: it sees integers and booleans, not floats it has to convert back. If identifiers already lost digits in the file (Excel only stores 15 significant digits), no dtype can restore them; see Fix Long ID Numbers Losing Digits in Excel.

Common pitfalls

• Boolean columns read as floats. A single blank turns a TRUE/FALSE column into float64; ask for boolean at read time.
• Converting too late. convert_dtypes() after a default read turns those floats into Int64, not boolean.
• Filtering with an NA mask. df[mask] and df[~mask] both drop rows where the comparison is <NA>; fill the mask first so every row lands on one side.
• Libraries that expect NumPy types. Some older plotting and statistics code chokes on extension arrays. Convert at the boundary with df["Qty"].astype("float64") or to_numpy(dtype=float, na_value=np.nan).
• Placeholder text. Cells holding "n/a" or "-" make a numeric column text under any backend; clean them first, as in Replace Placeholder Text with NaN in pandas.

Performance notes

The nullable NumPy types cost one extra byte per value for the mask, which is negligible next to the cost of parsing the XML in the first place. Arrow-backed frames are usually smaller than default ones for text-heavy sheets, because Arrow stores strings contiguously rather than as one Python object each. Reading speed is dominated by the engine, not the backend: combining engine="calamine" with either backend is the fastest option for large files, as measured in Speed Up pandas Excel Reads with the Calamine Engine.

Conclusion

Blank cells force the classic NumPy types into floats, which is why integer columns show .0, IDs switch to scientific notation and TRUE/FALSE flags become numbers. Pass dtype_backend="numpy_nullable" (or "pyarrow") to read_excel to keep Int64, boolean and string columns with <NA> for the gaps, or set the extension types per column with dtype. Do it at read time rather than with convert_dtypes afterwards, decide how NA should behave in filters, and the frame writes back to Excel with empty cells where the source had them.

Frequently asked questions

Why does pandas read my integer Excel column as float? Because the column contains at least one blank cell. NumPy's int64 has no missing-value marker, so pandas switches to float64 and uses NaN. Read with dtype_backend="numpy_nullable" or dtype={"col": "Int64"} to keep integers.

What is the difference between numpy_nullable and pyarrow in read_excel? Both keep integer, boolean and text columns with for missing values. numpy_nullable uses pandas' masked NumPy arrays (Int64, boolean, string); pyarrow stores columns in Apache Arrow memory, which is more compact for text and shares data with Parquet, Polars and DuckDB without copying.

Why did my TRUE/FALSE column turn into 1.0 and 0.0? The column has blanks, so the default reader stored it as float64. Ask for the boolean type when reading — dtype={"Shipped": "boolean"} or dtype_backend="numpy_nullable" — because converting afterwards gives Int64, not boolean.

How do I filter on a nullable column that contains NA? Comparisons with a missing value return NA. Indexing treats NA as not selected — in both the mask and its inverse, so those rows vanish from both halves of a split. Decide what NA means and fill the mask first, for example split = (df"Qty" > 4).fillna(False), then use dfsplit and df~split.

Related

• Up one level: Reading Excel Files with Pandas — every read_excel option that shapes the DataFrame.
• Check Excel Data Types with pandas — confirm each column has the type you expect.
• Read Boolean Columns from Excel with pandas — when flags arrive as text such as "Yes" and "No".
• Use Converters When Reading Excel with pandas — per-column functions for values that need parsing.