Why pandas Turns ZIP Codes Into Numbers and How to Stop It

A ZIP code is a label that happens to be made of digits. pandas does not know that. Given a column where every value is digits, it decides the column is numbers, and a number does not have leading zeros. 02134 in Massachusetts becomes 2134, 00501 in New York becomes 501, and nothing in the load says so. This article shows the mechanism, measures the damage on a 2,000,000 row file, gives the fixes that work, and says why the tempting repair afterwards is not one. It is the same trap that the DuckDB type article looks at from the other side, and it is the reason for the rule I give in the Python CSV guide: identifiers are text.

What happens

A four row file with ZIP codes, two of which start with 0:

id,zip,state
1,02134,MA
2,90210,CA
3,00501,NY
4,10001,NY

Read with defaults, then written back:

import io
import pandas as pd

df = pd.read_csv("zips.csv")
print(df["zip"].tolist(), df["zip"].dtype)
print(df.to_csv(index=False).splitlines())
blank = pd.read_csv("zips-blank.csv")
print(blank["zip"].tolist(), blank["zip"].dtype)
fixed = pd.read_csv("zips.csv", dtype={"zip": "str"})
print(fixed["zip"].tolist(), fixed["zip"].dtype)
fixed_blank = pd.read_csv("zips-blank.csv", dtype={"zip": "str"})
print(fixed_blank["zip"].tolist())
conv = pd.read_csv("zips.csv", converters={"zip": str})
print(conv["zip"].tolist(), conv["zip"].dtype)

python /batches/csv-0001-0200/lab/py/pd_zip.py

[2134, 90210, 501, 10001] int64
['id,zip,state', '1,2134,MA', '2,90210,CA', '3,501,NY', '4,10001,NY']
[2134.0, nan, 501.0] float64
['02134', '90210', '00501', '10001'] str
['02134', nan, '00501']
['02134', '90210', '00501', '10001'] str

The first line shows the column as [2134, 90210, 501, 10001] with dtype int64. The leading zeros are gone at the moment of parsing. The second line is the file you get if you write the frame out again: 1,2134,MA, 3,501,NY. The damage has now left your program, and anyone who receives the file sees a four digit ZIP code. A step in a pipeline that only reads and writes a CSV has changed the data.

The blank cell makes it worse

The third line reads a file where one ZIP code is missing. The column becomes [2134.0, nan, 501.0] with dtype float64. An integer column cannot hold a missing value, so pandas promotes it to float, and the codes now print with a decimal point. If this is written back out, 2134.0 is what lands in the file.

Why it infers numbers

A CSV file has no types, so every reader must guess (why). pandas's rule is to try to convert a column to numbers, and keep the conversion if every non-missing value converts. For 02134, int("02134") works in Python and yields 2134, and so does the parser's. From the parser's point of view, a column where every value converts is a numeric column. That is a reasonable default for a column of quantities and the wrong one for an identifier. It is also a deliberate trade-off, since a reader that left every digit string as text would hand you a column of strings for every numeric field in the file.

DuckDB's sniffer makes a different choice for this one case and keeps a value with a leading zero as VARCHAR, as the DuckDB type article found with 001 and 02134. pandas 3.1 does not. So the same file loaded in two tools produces different data, and it is not a bug in either.

Fix: declare the column as text

State the type when you read, so that no inference happens. The fourth line of the output above is the dtype form: ['02134', '90210', '00501', '10001'], dtype str. The fifth line shows the same with a blank cell in the file, where the blank stays NaN and the other values keep their zeros. The sixth line is the converters={"zip": str} form, which also works but calls a Python function on every value, so it is slower on big files. Use dtype; the syntax is in the pandas IO guide. When there are many identifier columns, give dtype=str for the whole file and convert the numeric columns afterwards; with thirty columns, that is shorter than listing three.

Measuring the damage on a large file

On the 2,000,000 row orders file, with ZIP codes from 00500 upwards, I read the column both ways:

import pandas as pd

path = "/batches/csv-0001-0200/work/orders-2m.csv"
text = pd.read_csv(path, usecols=["zip"], dtype={"zip": "str"})["zip"]
num = pd.read_csv(path, usecols=["zip"])["zip"]
print("zip dtype default:", num.dtype, "| dtype=str:", text.dtype)
print("values starting with 0 in the file :", int(text.str.startswith("0").sum()))
print("5-digit strings                    :", int((text.str.len() == 5).sum()))
print("numbers below 10000 after default  :", int((num < 10000).sum()))
print("str lengths after default read     :", num.astype(str).str.len().value_counts().sort_index().to_dict())

python /batches/csv-0001-0200/lab/py/pd_zip_big.py

zip dtype default: int64 | dtype=str: str
values starting with 0 in the file : 190451
5-digit strings                    : 2000000
numbers below 10000 after default  : 190451
str lengths after default read     : {3: 9941, 4: 180510, 5: 1809549}

Read as text, 190,451 values start with 0, and all 2,000,000 are five characters long. Read with defaults, the dtype is int64, exactly 190,451 values are below 10,000, and the string lengths of the numbers are 3 (9,941 codes), 4 (180,510 codes) and 5 (1,809,549 codes). That is 9.5% of the rows with a wrong ZIP code, no error and no warning. A sample of the first fifty rows would very likely show nothing wrong, which is how it reaches production.

Why repairing afterwards is a trap

The obvious fix is df["zip"].astype(str).str.zfill(5), and on this data it works:

import io
import pandas as pd

text = "zip\n02134\n90210-1234\nK1A 0B1\n2134\n"
raw = pd.read_csv(io.StringIO(text), dtype=str)["zip"]
print("as read    :", raw.tolist())
print("zfill(5)   :", raw.str.zfill(5).tolist())
lossy = pd.read_csv(io.StringIO("zip\n02134\n90210\n00501\n"))["zip"]
print("int, zfill :", lossy.astype(str).str.zfill(5).tolist())

python /batches/csv-0001-0200/lab/py/pd_zip_repair.py

as read    : ['02134', '90210-1234', 'K1A 0B1', '2134']
zfill(5)   : ['02134', '90210-1234', 'K1A 0B1', '02134']
int, zfill : ['02134', '90210', '00501']

The last line shows it: from integers, zfill(5) gave ['02134', '90210', '00501']. The reason I do not trust it is in the other lines. In a text column holding 02134, 90210-1234, K1A 0B1 and 2134, zfill(5) returns ['02134', '90210-1234', 'K1A 0B1', '02134']. The ZIP+4 and the Canadian postal code are untouched, which is fine. But the genuine 2134, which might be a typo, a different country's code, or a four digit value that should be rejected, became a valid looking 02134. The repair makes bad data look good. It assumes that every code is a five digit US ZIP, and if that is true then it is a safe patch for a file you cannot re-read. If it is not true, you have lost information that cannot be recovered. Where the original file exists, re-read it as text.

Postal codes elsewhere have the same problem in other shapes: UK codes contain letters and are always text, Swedish codes contain a space in the usual form, and some countries use digit-only codes with leading zeros. The rule that fits all of them is that a postal code is a string.

Other columns with the same problem

The same applies when you load the file into a database instead: the type chapter in the SQLite guide shows the matching decision there, where a typed column fixes comparisons and costs the leading zero.

Anything that is an identifier made of digits is at risk: account and order numbers, phone numbers and country calling codes, SKUs, product barcodes, employee IDs and bank sort codes. A 20 digit number read as float64 loses precision, as the DuckDB article showed with 12345678901234567890. Read each of them as text. If you need arithmetic on one of them, convert on purpose, and a column you convert on purpose is a column you decided was a number.

How to tell it worked

Check df["zip"].dtype and the string lengths: df["zip"].str.len().value_counts() should show only the lengths you expect. Compare df["zip"].str.startswith("0").sum() with grep -c ',0' file.csv or the count from another tool. Write the frame back out and diff the ZIP column against the original. And add an assertion to the pipeline, assert df["zip"].str.len().between(5, 10).all(), so that the day a change upstream breaks it, you hear about it.

When zfill is not a repair

Use zfill when you have no choice, when the data is already numeric and known to be five digit US ZIP codes, and when you tell the reader of the output that the codes were reconstructed. Do not use it on a column that can contain other countries, ZIP+4 values, blanks you care about, or any value that might legitimately be short. And do not use it to hide the fact that the load is wrong: fix the dtype, and the repair becomes unnecessary.