🔧 Fix Census & demographics Joins & relationships Files & formats Census & Demographics · step 16 of 20 Updated

Fixing Census Joins That Fail Because Leading Zeros Were Dropped

Problem statement

The merge that should attach a census table to its tracts either refuses to run:

ValueError: You are trying to merge on str and int64 columns for key 'GEOID'. If you wish to proceed you should use pd.concat

or, once someone converts the numbers to strings to make that go away, runs and quietly matches far too little. Joining the 2020 tract gazetteer, read with pandas defaults, to the 2023 cartographic tract boundaries:

astype(str)    matched 69,424 of 85,186 boundaries

California, Arizona, Colorado, Alabama, Connecticut, Arkansas and Alaska are blank on the map. Every one of their tract GEOIDs starts with 0, and the zero was lost when the column was read as an integer: 06001400100 became 6001400100, ten characters that match nothing.

The fix takes one line. The part worth understanding is where the zeros go missing, because the same data can be read correctly by one tool and damaged by the next.

Quick answer

Read identifier columns as text, and if they are already damaged, restore the known width:

import pandas as pd

# prevention: text at the moment of reading
table = pd.read_csv("2020_Gaz_tracts_national.zip", sep="\t", dtype={"GEOID": "string"})

# repair: ids already stored as numbers (Int64 first, so a blank cell cannot add ".0")
damaged = pd.read_csv("2020_Gaz_tracts_national.zip", sep="\t")
damaged["GEOID"] = damaged["GEOID"].astype("Int64").astype("string").str.zfill(11)

Both routes matched 84,181 of the 85,186 boundaries. The remaining 1,005 are not a zero problem: 879 are Connecticut tracts whose codes changed after 2020 and 126 are island areas absent from the gazetteer. Check the width of every identifier before joining, and compare the matched count with what the join should produce.

Triage table of four symptoms of lost leading zeros in census identifiers and the fix for each.
The loud error is the helpful one; the silent version starts when it is converted away.

Step-by-step solution

1. Confirm the diagnosis by length

A damaged column has identifiers one character short of their level's width — 11 for tracts, 5 for counties, 12 for block groups, 15 for blocks:

lengths = damaged["GEOID"].astype(str).str.len()
print(lengths.value_counts().to_dict())

In the 2020 gazetteer, 15,661 of 85,395 tract GEOIDs were one character short. All of them belonged to states with FIPS codes below 10. If the short ids come from other states, the problem is something else.

2. Do not silence the merge error with astype(str)

pandas refuses to merge text with integers, and that refusal is correct. Converting the integers to strings makes the merge legal and leaves the missing zero missing: 69,424 matches instead of 84,181, and no error at all. Treat the ValueError as a report that one side was read wrongly, and fix the reading.

3. Re-read the source as text

If the original file is available, reading it again is the cleanest repair, because nothing has to be reconstructed:

safe = pd.read_csv(path, sep="\t", dtype={"GEOID": "string"})

Name every identifier column: GEOID, STATEFP, COUNTYFP, TRACTCE, ZCTA5. Reading the whole file with dtype=str also works, but then every numeric column needs converting afterwards.

4. Repair with the known width when you cannot re-read

zfill pads a string with zeros on the left to a given width. It is safe only when every identifier in the column belongs to the same level:

geoid = damaged["GEOID"].astype("Int64").astype("string").str.zfill(11)
assert geoid.str.len().eq(11).all()

The Int64 step matters when the column contains a blank. An integer column with a missing value becomes a float, its text form gains .0, and '6001400100.0' is already 12 characters, so zfill(11) leaves it unchanged.

5. Watch the CSV round trip

Correct text identifiers do not stay correct once written to a CSV and read back with defaults:

frame = pd.DataFrame({"GEOID": ["06077005127", "06077003406"], "pop": [1, 2]})
frame.to_csv("tracts.csv", index=False)
print(pd.read_csv("tracts.csv")["GEOID"].tolist())
[6077005127, 6077003406]

CSV has no types, so the reader guesses again. Every reader in the pipeline needs the dtype, not just the first one. Formats that store types — Parquet, GeoPackage, a shapefile's .dbf — keep text as text.

6. Do not rely on DuckDB guessing the same way twice

DuckDB's CSV sniffer keeps values with leading zeros as VARCHAR. It types a file whose identifiers happen to have no leading zero as BIGINT:

file of California tracts   GEOID VARCHAR   '06001400100'
file of Delaware tracts     GEOID BIGINT    10001040100

A pipeline developed on Delaware therefore produces integer identifiers that later fail to join to anything read as text. Declare the type:

select * from read_csv('tracts.csv', types = {'GEOID': 'VARCHAR'});

7. Treat a spreadsheet as a destructive step

Opening a CSV in a spreadsheet and saving it again usually converts identifiers to numbers before anyone sees the file. Import identifier columns as text in the spreadsheet, or repair with step 4 afterwards. dtype=str in pandas cannot recover a zero that is no longer in the file.

8. Assert before every join

A two-line check catches all of the above:

assert table["GEOID"].str.len().eq(11).all(), "tract GEOIDs are not 11 characters"
assert boundaries.merge(table, on="GEOID").shape[0] > 0.99 * len(boundaries)

Set the second threshold from a known-good run of the same geography.

Bar chart of boundaries matched when joining tract GEOIDs converted with astype(str), repaired with zfill, and read as text.
Repairing and re-reading give the same answer; converting to strings without padding loses 14,757 boundaries.

Code examples

Example 1 — describe the damage in a column 

import pandas as pd


def damaged_ids(values, width):
    """How many ids in a column are not the expected width, and what shape they are."""
    s = pd.Series(values)
    kind = str(s.dtype)
    text = s.astype("string")
    lengths = text.str.len()
    report = {
        "dtype": kind,
        "rows": len(s),
        "wrong_width": int((lengths != width).sum()),
        "one_short": int((lengths == width - 1).sum()),
        "float_suffix": int(text.str.endswith(".0").sum()),
        "missing": int(s.isna().sum()),
    }
    print(report)
    return report

On the gazetteer read with defaults, and read as text:

{'dtype': 'int64', 'rows': 85395, 'wrong_width': 15661, 'one_short': 15661, 'float_suffix': 0, 'missing': 0}
{'dtype': 'string', 'rows': 85395, 'wrong_width': 0, 'one_short': 0, 'float_suffix': 0, 'missing': 0}

one_short equal to wrong_width is the leading-zero signature. A float_suffix count points at a column that went through a float, and ids of other lengths point at mixed levels or corrupted values that padding will not fix.

Example 2 — a reader that keeps identifiers as text 

def read_census_csv(path, id_columns, **kwargs):
    """Read a CSV with every identifier column as text, whatever the rest infers to."""
    dtype = {col: "string" for col in id_columns}
    frame = pd.read_csv(path, dtype=dtype, keep_default_na=False, na_values=[""], **kwargs)
    return frame
gaz = read_census_csv("2020_Gaz_tracts_national.zip", ["GEOID"], sep="\t")

keep_default_na=False stops pandas from turning strings such as NA into missing values, which matters for columns of codes; only genuinely empty cells become missing. The value columns still infer to numbers.

Example 3 — the three joins, measured 

import geopandas as gpd

boundaries = gpd.read_file("cb_2023_us_tract_500k.zip", columns=["GEOID"], ignore_geometry=True)
naive = pd.read_csv("2020_Gaz_tracts_national.zip", sep="\t")
safe = read_census_csv("2020_Gaz_tracts_national.zip", ["GEOID"], sep="\t")

candidates = {
    "astype(str)": naive["GEOID"].astype(str),
    "zfill(11)": naive["GEOID"].astype(str).str.zfill(11),
    "read as text": safe["GEOID"],
}
for label, ids in candidates.items():
    m = boundaries.merge(pd.DataFrame({"GEOID": ids}), on="GEOID", how="left", indicator=True)
    print(f"{label:14} matched {int((m._merge == 'both').sum()):,} of {len(boundaries):,} boundaries")
astype(str)    matched 69,424 of 85,186 boundaries
zfill(11)      matched 84,181 of 85,186 boundaries
read as text   matched 84,181 of 85,186 boundaries

Keep this comparison in a test for any pipeline that reads census identifiers from text files. It fails the moment a reader somewhere loses its dtype.

Explanation

Why a GEOID is not a number

A GEOID concatenates fixed-width codes: two digits of state, three of county, six of tract. The leading zero of 06 is part of California's code, not formatting. Numbers have no leading zeros, so storing a GEOID as a number destroys information that no later step can infer — except by knowing the width, which is why zfill works only per level.

Why pandas and DuckDB disagree

pandas asks whether every value in a column parses as an integer, and a column of digits does, zero or not. DuckDB's sniffer treats a leading zero as evidence that the value is text, because a number would not be written that way. Both are reasonable heuristics, and they give different answers on the same data. A file of Delaware tracts contains no leading zeros at all, so DuckDB has no evidence and picks BIGINT.

Relying on inference means the type of an identifier depends on which rows happened to be in the file. Declared types do not.

Why GeoPandas files are usually safe

Shapefiles, GeoPackages and GeoParquet store field types, so a GEOID written as text is read as text: the 2023 cartographic boundary file arrived as a string column. Even reading a CSV through GeoPandas and GDAL kept the identifiers as text in testing, because GDAL's CSV driver does not guess numeric types unless asked to. The damage almost always enters through pd.read_csv, a spreadsheet, or a hand-typed table.

Why the remaining unmatched rows are a different problem

After the repair, 1,005 boundaries still had no gazetteer row. 879 are in Connecticut, whose tract GEOIDs changed when planning regions replaced its counties after the 2020 gazetteer was published, and 126 are in Guam, the Northern Mariana Islands, American Samoa and the US Virgin Islands. Those are vintage and coverage mismatches, with their own fixes; padding cannot and should not make them match.

Table of how pandas, DuckDB, GeoPandas and a CSV round trip type a census GEOID column.
Only the readers told the type, or reading a typed format, are safe every time.

Edge cases or notes

  • Pad per level, never across levels. A column mixing counties and tracts needs the level from another column before zfill.
  • Blank cells turn integers into floats. Convert through Int64 before padding, or the .0 suffix defeats it.
  • ZCTAs lose zeros too. ZIP Code Tabulation Areas in New England and New Jersey start with 0.
  • Excel's "General" format strips zeros on open. Import the column as text, or repair after.
  • The Census API returns geography columns as text. Concatenate state, county and tract without converting them.
  • Parquet keeps the type you wrote. Write the repaired column as text once, and later readers inherit it.
  • FIPS codes as integers are sometimes intended. A few datasets store state and county numerically on purpose; pad to 2 and 3 digits before building a GEOID.
  • Codes with letter prefixes are immune. ONS codes such as E01000001 never lose characters.

FAQ

Why does pandas drop the leading zero from my GEOID?

Because read_csv sees a column of digits and stores it as int64, and integers have no leading zeros. Pass dtype={"GEOID": "string"} to keep it as text.

How do I add the leading zeros back?

Convert to text and pad to the known width: 11 for tracts, 5 for counties. If the column is a float because of blanks, convert it to Int64 first, or the .0 suffix stops the padding.

Should I convert both sides to strings to make the merge work?

No. That removes the error without restoring the zeros; on US tracts it matched 69,424 of 85,186 boundaries instead of 84,181.

Why does my DuckDB query give numeric GEOIDs for some files?

DuckDB infers the type from the file's contents, and a file whose identifiers have no leading zeros is typed as BIGINT. Declare the column with types={'GEOID': 'VARCHAR'} in read_csv.

Can I tell which rows lost a zero?

Yes: they are exactly one character short of the level's width. In the 2020 tract gazetteer that was 15,661 rows, all in states with FIPS codes below 10.

Why do some tracts still not match after the fix?

Other problems remain after the zeros are restored. In this join, 879 Connecticut tracts had changed codes and 126 boundaries were in island areas the gazetteer does not cover.