🔧 Fix Census & demographics Joins & relationships Data sources & downloads Census & Demographics · step 20 of 20 Updated

Fixing Census Data That Does Not Match the Boundary File Year

Problem statement

The join between a census table and its boundaries runs, the identifiers are read as text, and still a large part of the map is empty. Joining the 2023 ACS 5-year tract table to tract lists from three different years:

boundaries  tracts  table rows unmatched     %  boundary rows unmatched                               top states
      2019   74001                 23644  27.7                    12264  {'48': 3101, '06': 2248, '12': 1822, '13': 1536}
      2020   85395                   884   1.0                      898                               {'09': 884}
      2023   85396                     0   0.0                       15                                        {}

With 2019 boundaries, more than a quarter of the table fails to join, spread across Texas, California, Florida, Georgia and every other fast-growing state. With 2020 boundaries, only Connecticut fails, completely. With 2023 boundaries, every table row finds a polygon.

The data and the boundaries describe different vintages of the census geography. The two failure patterns have different causes — a decennial redraw and a recoding of one state's counties — and the fix is to identify which one you have and use boundaries from the same year as the table.

Quick answer

Use the boundary file whose year matches the table's release, and check the match before mapping:

import pandas as pd

acs = pd.read_csv("acsdt5y2023-b01003.dat", sep="|", dtype=str, usecols=["GEO_ID"])
table_ids = set(acs[acs.GEO_ID.str.startswith("1400000US")].GEO_ID.str[9:])

for year in ("2019", "2020", "2023"):
    ids = set(pd.read_csv(f"{year}_Gaz_tracts_national.zip", sep="\t", dtype={"GEOID": str},
                          usecols=["GEOID"]).GEOID)
    print(year, len(table_ids - ids), "table rows unmatched")

For a 2023 ACS release, download 2023 boundaries — cb_2023_us_tract_500k.zip, or the 2023 TIGER/Line files. If the table and boundaries cannot be from the same year, because you are comparing releases, crosswalk one onto the other rather than forcing a join.

Bar chart of 2023 ACS tract rows unmatched against 2019, 2020 and 2023 tract lists.
The right year matched every table row; one year out left Connecticut blank, four years out left a quarter of the country.

Step-by-step solution

1. Name the vintage of each input

Every census product is tabulated on the geography of a specific year. The boundary file's year is in its name: cb_2023_…, tl_2020_…. The table's is the release year: the 2023 ACS 5-year estimates cover 2019–2023 and are published on 2023 geography.

Write both down. Most vintage mismatches come from reusing a boundary file downloaded for an earlier project.

2. Measure the mismatch against several years

Run the check in the quick answer, or Example 1, against the boundary vintages you have. The pattern of misses identifies the cause:

  • Thousands of misses scattered across most states — the boundaries predate a decennial redraw. The 2019 file lists 74,001 tracts drawn for the 2010 census; the 2023 table uses the 85,000-odd tracts drawn for 2020.
  • Every tract in one state misses — that state's codes changed. Connecticut is the recent case.
  • A handful of misses in particular counties — rows missing from one file, not a vintage problem.

3. Download the matching year

The Census Bureau's boundary URLs contain the year, so the matching file is one substitution away:

https://www2.census.gov/geo/tiger/GENZ2023/shp/cb_2023_us_tract_500k.zip
https://www2.census.gov/geo/tiger/TIGER2023/TRACT/tl_2023_09_tract.zip

With 2023 boundaries the table matched completely. The 15 boundary rows without a table row are Suffolk and Ulster County tracts that the 2023 table does not publish — a gap in the table, not a vintage error.

4. Recognise the Connecticut case

In 2022, Connecticut's nine planning regions replaced its eight counties as county-equivalents in census geography. The county part of every tract GEOID changed, from codes 001–015 to 110–190, and the tract part mostly stayed the same.

Comparing the 2020 and 2022 TIGER/Line tract files for Connecticut: 883 and 884 tracts, 0 GEOIDs in common. By geometry, 878 of the 884 2022 tracts had a 2020 tract covering at least 99% of their area, and for all 884 the best-matching 2020 tract had the same six-digit tract code.

So the geography barely moved, and a 2020-vintage file will never join to a 2022-or-later table for Connecticut on GEOID.

5. Bridge Connecticut by geometry, not by tract code alone

When you must attach data on old county codes to new boundaries — a 2020 decennial table to current Connecticut tracts, say — match by overlap and keep the tract code as a check:

  • The tract code alone is almost enough, but not quite unique: codes 990000 and 990100 are used in more than one old county, both water tracts.
  • Overlap identifies every pair, and the six tracts under 99% overlap show where boundaries really moved.

Example 3 does this in one overlay.

6. Do not force a join across genuinely different geographies

Comparing a 2019 release with a 2023 release is a comparison across a decennial redraw. Renaming, padding or matching on tract code cannot make those tracts the same places. Move one year onto the other's tracts with a block-based crosswalk, then compare.

7. Record the expected unmatched counts

For 2023 ACS tracts against 2023 boundaries, the right answer is 0 table rows unmatched and 15 boundary rows without data. Put those numbers in a test. Any deviation means an input changed.

Decision diagram diagnosing a census join by the pattern of unmatched rows: scattered across states, one whole state, or a few counties.
The shape of the misses tells you which kind of mismatch you have before you look at a single row.

Code examples

Example 1 — check a table against several boundary vintages 

import pandas as pd


def acs_tract_ids(path):
    t = pd.read_csv(path, sep="|", dtype=str, usecols=["GEO_ID"])
    return set(t[t.GEO_ID.str.startswith("1400000US")].GEO_ID.str[9:])


def gazetteer_ids(path):
    g = pd.read_csv(path, sep="\t", dtype={"GEOID": str}, usecols=["GEOID"])
    return set(g.GEOID)


def vintage_check(table_ids, boundary_ids):
    """Match a table's GEOIDs against several boundary vintages and report where the misses are."""
    rows = []
    for label, ids in boundary_ids.items():
        missing = sorted(table_ids - ids)
        extra = len(ids - table_ids)
        by_state = pd.Series([g[:2] for g in missing]).value_counts().head(4).to_dict() if missing else {}
        rows.append((label, len(ids), len(missing), round(100 * len(missing) / len(table_ids), 1), extra, by_state))
    report = pd.DataFrame(rows, columns=["boundaries", "tracts", "table rows unmatched", "%",
                                         "boundary rows unmatched", "top states"])
    print(report.to_string(index=False))
    return report
acs = acs_tract_ids("acsdt5y2023-b01003.dat")
vintage_check(acs, {y: gazetteer_ids(f"{y}_Gaz_tracts_national.zip") for y in ("2019", "2020", "2023")})

The gazetteer files are small, tab-separated lists of every geography with its identifier and area. They are the fastest way to test a vintage without downloading polygons.

Example 2 — assert the join you expect 

def assert_vintage(table_ids, boundary_ids, max_table_misses=0, max_boundary_misses=15):
    """Fail loudly if a table and boundary file are from different vintages."""
    table_misses = len(table_ids - boundary_ids)
    boundary_misses = len(boundary_ids - table_ids)
    if table_misses > max_table_misses or boundary_misses > max_boundary_misses:
        states = sorted({g[:2] for g in table_ids - boundary_ids})
        raise ValueError(f"{table_misses} table rows and {boundary_misses} boundary rows unmatched; "
                         f"states with table misses: {states[:10]}")
    return table_misses, boundary_misses

The defaults are the known-good counts for 2023 ACS tracts against 2023 boundaries. With the 2020 gazetteer, the check fails with 884 table misses in state 09, which names the Connecticut problem directly.

Example 3 — match new Connecticut tracts to old ones by overlap 

import geopandas as gpd


def best_overlap_match(new, old, id_col="GEOID", code_col="TRACTCE", crs=5070):
    """For each new polygon, the old polygon covering most of it, with the overlap share."""
    a = new[[id_col, code_col, "geometry"]].to_crs(crs)
    b = old[[id_col, code_col, "geometry"]].to_crs(crs)
    pairs = gpd.overlay(a, b, how="intersection", keep_geom_type=True)
    pairs["share"] = pairs.area / pairs[f"{id_col}_1"].map(a.set_index(id_col).area)
    best = pairs.sort_values("share").groupby(f"{id_col}_1").tail(1)
    print(f"{len(best):,} new polygons; best match covers >= 99% for {int((best.share >= 0.99).sum()):,}; "
          f"same code for {int((best[f'{code_col}_1'] == best[f'{code_col}_2']).sum()):,}")
    return best.rename(columns={f"{id_col}_1": "new_id", f"{id_col}_2": "old_id"})
t20 = gpd.read_file("tl_2020_09_tract.zip", columns=["GEOID", "TRACTCE"])
t22 = gpd.read_file("tl_2022_09_tract.zip", columns=["GEOID", "TRACTCE"])
lookup = best_overlap_match(t22, t20)
884 new polygons; best match covers >= 99% for 878; same code for 884

The result is a lookup from each new GEOID to the old one. Use it for data that is attached to the old codes; for anything where the six under 99% matter, use a block-based crosswalk.

Explanation

Why tract lists change between years

Tracts are redrawn after every decennial census to keep their populations within the design range, so the tract list from before 2020 describes a different set of areas: 74,001 tracts in the 2019 gazetteer against 85,395 in 2020. Growth is uneven, so the redrawing concentrates in growing states, which is why Texas, California, Florida and Georgia led the unmatched rows against 2019 boundaries.

Between decennial censuses, the tract list is stable. The 2020 and 2023 gazetteers differ only in Connecticut, which has one more tract under its new codes.

Why Connecticut fails completely

A GEOID contains the county code. Connecticut abolished county government in 1960, and its counties remained census county-equivalents until the state's planning regions replaced them for 2022 data products. Changing the county code changed every tract, block group and block GEOID in the state at once, while the polygons stayed where they were. That produces the distinctive signature: one state entirely unmatched, and geometry that still lines up.

Why the boundary side has unmatched rows even in the right year

Boundary files and tables are made separately. The 2023 boundaries include 15 tracts in Suffolk and Ulster counties, New York, for which the 2023 table publishes no row, so a correct join still leaves them without data. The number is small, stable and explained, which is exactly what makes it a good test value.

Why forcing a match is worse than failing

Matching old and new tracts by tract code, or by padding and trimming identifiers until they join, produces a map with no gaps and wrong values: a 2010-era tract's number attached to a 2020 tract that covers different ground. The unmatched rows were the warning; removing them removes the only evidence of the problem.

Two panels comparing Connecticut tract GEOIDs before and after 2022: county codes 001 to 015 against 110 to 190, with no GEOIDs shared and almost identical geometry.
The codes changed for every tract in the state; the geometry changed for a handful.

Edge cases or notes

  • Decennial tables use their own year's geography. 2020 census tables join to 2020 boundaries, with Connecticut's old counties.
  • The cartographic and TIGER/Line files of one year share GEOIDs. Choosing between them is about detail, not vintage.
  • Block groups and blocks follow their tracts. A tract vintage mismatch is also a block group mismatch.
  • Places, ZCTAs and districts change too. Congressional districts in particular change after redistricting.
  • Gazetteer files are the cheapest check. Tens of megabytes of text against hundreds of megabytes of polygons.
  • The two Connecticut water-tract codes repeat across old counties. Match them by overlap, not by code.
  • Other countries have the same problem. England and Wales Output Areas were revised between the 2011 and 2021 censuses, and lookups map one onto the other.

FAQ

Why do so many tracts fail to join even though the GEOIDs look right?

The boundary file is probably from a different census vintage. The 2023 ACS table had 23,644 tract rows unmatched against 2019 boundaries, because tracts were redrawn after the 2020 census.

Which boundary file goes with the 2023 ACS 5-year estimates?

The 2023 boundaries, such as cb_2023_us_tract_500k. Against the 2023 tract list, every table row matched.

Why is Connecticut missing from my census map?

Its planning regions replaced its counties as county-equivalents in 2022, which changed every tract GEOID. A 2020 boundary file shares no tract GEOIDs with 2022-and-later data.

Can I match Connecticut's old and new tracts?

Yes, by overlap. For all 884 current tracts, the best-overlapping 2020 tract had the same tract code, and for 878 it covered at least 99% of the area.

How can I tell a vintage problem from a leading-zero problem?

Look at which rows fail. Lost zeros take out every state with a FIPS code below 10; a Connecticut recoding takes out one state; a decennial redraw scatters failures across many states.

Should I just use the older boundaries for both years?

Only if both tables are tabulated on them. Joining a newer table to older tracts leaves real rows unmatched, and forcing matches attaches values to the wrong places.