Join Keys and Crosswalks

Where the data comes from, and what it is for

A FIPS code is the federal government’s name for a place: two digits of state, three of county, so Autauga County, Alabama is 01001. The codes began in the 1970s as a Federal Information Processing Standard, written so that federal computers naming the same county would name it the same way. The standards agency retired the formal standard in 2008,¹ and the codes carried on as ANSI codes under Census Bureau maintenance.² That is where you get them today: the Bureau’s annual Gazetteer files, one small table per year at https://www2.census.gov/geo/docs/maps-data/data/gazetteer/, are the working list of what officially counts as a county.

Who is in it, and how they got there. Every county and county-equivalent in the country — 3,144 of them across the 50 states and the District of Columbia, once territories are set aside. A place is in the list because a state created it. Louisiana’s parishes, Alaska’s boroughs, Virginia’s independent cities and the District itself are all “county-equivalents”: rows in the same list, holding codes of the same shape.

Who it was made for. Machines, and the agencies that run them. The list exists so that every federal file about places joins to every other federal file about places. That is why the same five characters appear in census tables, health statistics, farm reports and election compilations. It is infrastructure. Nobody reads a FIPS code; everything joins on one.

What it costs to get. Nothing. The Gazetteer is a small tab-separated file at a public address, no account, no key, replaced each year. The election files this chapter joins it against are also free, but their assembly was not: a compiler collected results from fifty-one separate jurisdictions on election night, twice, four years apart. You download in seconds what took someone weeks, and his conventions arrive with the file.

What has already been done to it. For the code list: counties are added, renamed and retired as states change them, and each yearly file silently reflects the law of its year. The copies this chapter uses keep only the codes and names, leaving out the Gazetteer’s land areas and map coordinates; they cover the fifty states and the District; and every code in them is treated as a piece of text rather than a number. That last decision is what the next section is about. Nothing is edited by hand.

How the codes are published, and the specimen files

The Bureau publishes the county list every year as Gazetteer files: one row per county-equivalent, tab-separated, the code in a column called GEOID — the Bureau’s general name for a geographic identifier, the same kind of code that, with more digits, names a census tract or block. This chapter reads the 2024 edition. Beside it sit two files that carry the same code from a different world: Tony McGovern’s county-level compilation of the 2020 and 2024 presidential results. He copied them, by program, from news organisations’ election-night pages, and the README states plainly that the numbers are not authoritative. The pairing is the point. An official list of what the code means, and a working file that uses it, is the situation every join in this section is in.

Three files, three row counts: the Bureau lists 3,144 county-equivalents, the 2024 returns hold 3,160 reporting units, and the 2020 returns hold 3,152. Three files all claiming to describe American counties, none agreeing how many there are. Keep that disagreement in view; the worked example below is an audit of it.

Cleaning: a code is not a number

Before any join, the key has to survive being read, and the most common data bug in American political data happens right here. A FIPS code is a label made of digits, like a phone number. Software that reads a column of digits assumes it is reading quantities.

328 of 3,160 rows are broken, and they are not a random 10%. They are exactly the states whose FIPS code begins with a zero:

Alabama through Connecticut, alphabetically, and nothing after. Read as a number, 01001 becomes 1001, and 1001 matches nothing in any file that kept the zero. Match these against any other government file and none of them will match — silently. The fix is one instruction: tell the software the column is text. The damage, when the instruction is skipped, is not an error message. It is a fifth of the country quietly leaving the analysis, alphabetically first.

The Bureau’s own Gazetteer does exactly the same thing when read with no instructions, so this is not a defect of anyone’s file. It is what happens whenever a code made of digits meets a program that assumes digits are amounts, and the only prevention is somebody saying so in advance.

A worked example: one join, every row accounted for

Now the join itself. Both election files came from the same repository, built by the same person, four years apart, keyed on the same column. Join them on county_fips and ask the bookkeeping questions a join deserves: how many rows went in, how many came out, and where the difference went.

The merge returned 3,104 tidy rows and no warnings. It also silently dropped two entire states, and the row counts are the only witness. Where did they go?

Connecticut abolished its counties as statistical units. The state asked the Census Bureau to replace them with its nine planning regions, and the Bureau agreed in a notice dated 6 June 2022: new names, new boundaries, new codes.³ The two files share 0 Connecticut codes. Alaska was renumbered by the compiler: its returns come by state house district, which has no federal code, so the compiler invented codes — one convention in 2020, a different one in 2024, announced nowhere. Every one of those units drops out of the join, and a before-and-after row count catches all of it in a minute.

Follow one row through. Hartford County, Connecticut is 09003 in the 2020 file, a row carrying 449,336 votes. The join takes that code and looks for it among the 2024 rows, where Connecticut is now planning regions: Hartford’s votes sit under 09110, the Capitol Planning Region, with towns from neighbouring counties folded in. No 2024 row is labeled 09003, so there is nothing to match, and Hartford is simply not in the result — no error, no blank row, one line fewer. The same happens to the other 7 Connecticut counties and to every Alaska district, and the row count is the only thing that noticed.

Figure 1 accounts for every reporting unit on both sides of the merge at once.

Hover a ribbon for the number of reporting units it carries.

Figure 1. What became of every reporting unit between the two files. A flow diagram fits this question because the question is bookkeeping: where did every unit go. The ribbons carry counts. 3,103 units keep a code that means the same place in both years, 48 exist in 2020 and are gone in 2024, and 56 appear in 2024 with no counterpart. The 4 thinnest ribbons are drawn at a minimum width so they stay visible; every other ribbon is to scale.

So the join “never failed” in a precise and narrow sense: every row it returned matched a real code on both sides, and every unit it dropped announced itself in the row count. That is what a maintained key buys. An office holds the definition still, publishes the changes, and the failures are loud ones.

Except one. The red ribbon is a single row whose code survives intact while the place underneath it changes. Both files carry a row labeled 11001, the federal code for the District of Columbia. In the 2020 file that string means the whole city. In the 2024 file it means Ward 1, roughly an eighth of it, because the compiler took that year’s returns from a source that reports by ward.

The join runs, the row count comes back full, and the resulting “swing” for DC is fiction. It is the only failure in this figure that a row count cannot see. A join key is only as good as the promise that the same string means the same thing on both sides, and that promise is not stored anywhere. No column records it, and no software can check it.

Keys that misbehave

FIPS is the well-behaved end of a spectrum. Most keys you will meet live further down it, and each of the failure modes below has a chapter of this book where it bites for real.

Names are not keys. When two files share no code, the temptation is to join on the name. Here is what happens when the county returns meet the census list on county_name alone:

A merge matches every row on the left against every row on the right that shares the key. With 30 Washington Counties on each side, one name produces 900 rows, of which 30 are right and 870 are garbage. Nothing in the output tells them apart. Names are also spelled, abbreviated, accented and re-punctuated differently across files, so a name join fails in both directions at once: it multiplies rows it should not match and misses rows it should. Matching on state and name does better, at 3,112 rows, but still drops 48. A key built by pasting columns together inherits every quirk of both.

Codes can be invented, and inventions collide. Join the 2024 returns to the census list on FIPS and 3,116 rows match. Check the names that came along and 4 of them disagree:

Two of the three are Alaska, where the compiler’s invented district codes collide with real borough codes — Anchorage, a city of nearly 300,000, is handed 6,965 votes belonging to a state legislative district. The third is the DC collision again. In the other direction, Hawaii’s tiny Kalawao County is real, coded, and absent from every returns file because its ballots are counted with Maui’s. A reasonable local decision sits behind each of these; what none of them is, is recorded.

Some identifiers were never identifiers at all. Precinct names drift between elections — polling places move, precincts split, and nothing distinguishes the two, which is why comparing precincts across two elections is its own chapter. A ZIP code is a routing instruction for mail trucks, not an area, and what happens when it is used as one has a chapter too. And people carry no key at all. Matching a person across two files means guessing from name and birthdate, and the false-matches chapter shows a voter purge built on the assumption that such guesses cannot collide. They can, and they do.

Crosswalks are the repair

When two files carry different keys for the same ground, the repair is a crosswalk: a table that translates one set of areas or identifiers into another. One column holds the key as the first file speaks it, another holds the key as the second file speaks it, and joining through the middle table carries you across. The Census Bureau publishes them for its own geography: relationship files translating 2010 census blocks into 2020 blocks, for instance (https://www.census.gov/geographies/reference-files/time-series/geo/relationship-files.html). Researchers build them for everything else, from precincts to districts, ZIP codes to census tracts, and counties across a boundary change.

Two things make a crosswalk more than a lookup table. First, the relationship is rarely one-to-one. When an old unit splits across several new ones, the crosswalk must say how much of it goes to each — a weight, usually a population share. From that moment your numbers are estimates, because the weight is an assumption about where people live inside the old unit. Second, a crosswalk is itself a file somebody made, with a date and a method, and it can be wrong. It repairs a join by adding a third source to the chain, and the chain is only as strong as its newest link.

And sometimes no crosswalk can exist at the level you want. Connecticut’s planning regions are not the old counties regrouped: a region can take towns from more than one county, so no table can send each county to one region. The honest translation has to descend to a smaller unit that nests inside both — the Bureau’s own crosswalk for the change goes down to towns, and a census block would serve as well — and rebuild each side from below. That is the general lesson. A crosswalk between two sets of areas is really a claim that some smaller unit tiles both, and when none does, the translation is a modelling exercise wearing a table’s clothing.

What this data cannot tell you

No file can tell you its own key has changed meaning. The promise that 11001 names the same ground in both files lives in a README, in somebody’s memory, or nowhere. Every check in this chapter is circumstantial evidence gathered from outside the key itself: row counts, dropped keys, names carried along and compared. There is no test in any language that detects a definition that moved.

A successful join is not evidence that the values are right. The FIPS join above matches perfectly on files whose vote counts still disagree with the states’ own certified returns in places. Keys and values fail independently, and auditing the join says nothing about the arithmetic it carries.

The best-behaved key does not generalise. The county join never failed because an office is paid to hold county definitions still and announce every change. Precincts, ZIP codes, place names and people have no such office. What FIPS proves is what maintenance buys — not that joins are safe.

And a crosswalk cannot recover information nobody recorded. Weights allocate an old unit’s people by assumption, not by observation. Any quantity pushed through a weighted crosswalk is an estimate, and the crosswalk’s assumptions do not travel with the tidy number that comes out.

What you should have learned

• A join key is a column two files share, and a join fails three ways: missing keys drop rows, duplicated keys multiply them, and a key that changed meaning produces wrong rows silently — the only failure no row count can catch.
• Reading a code as a number destroys it. 328 of 3,160 county codes lose their leading zero on a naive read, and they are precisely the alphabetically first states, so the damage is systematic and silent.
• Row-count bookkeeping is the join’s audit: 3,160 and 3,152 rows in, 3,104 out, and the difference — Alaska’s renumbering and Connecticut’s redrawn units — announces itself, while the one same-code collision does not.
• Names are not keys: one repeated county name turns into 900 matched rows, of which 870 join unrelated places, and nothing in the output marks them.
• A crosswalk is the repair — a table translating one set of areas into another — and it usually carries weights, which make everything downstream an estimate resting on one more file’s assumptions.

Where this data appears in this book

Every cluster of this section is a join, so this chapter’s mechanics run underneath all of them.

• Survey meets record. Validated turnout joins what people told a survey to the ballots the states counted, across sixty years.
• Race inference. Surnames joins a name file to a census table; bisg-check grades the guess against a state that records race; uncertainty shows a tight interval on a number that describes nobody; rpv runs ecological inference where the answer is known.
• Districts. Neutral maps, the same votes redrawn, a bias statistic, a dilution claim and majority-Black district counts all join returns, population and geography through the codes this chapter is about.
• Forecasting. Grading forecasts, a House model, Senate ratings and election-night baselines join races to predictions, where the key is a race identifier somebody had to construct.
• Proxies and indices. Two turnout denominators, the cost of voting index, a grocery-store proxy rebuilt and the same curve six times each join a measured file to an idea, which is the loosest key of all.

Sources

Data

• Tony McGovern, United States General Election Presidential Results by District, Ward, or County from 2008 to 2024 (https://github.com/tonmcg/US_County_Level_Election_Results_08-24, DOI 10.5281/zenodo.14223604). The 2024 figures were copied from a broadcaster’s results pages; the README states plainly that the results are not authoritative.
• U.S. Census Bureau, 2024 Gazetteer Files, Counties, trimmed to codes and names for the 50 states and DC. https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2024_Gazetteer/
• U.S. Census Bureau, American National Standards Institute (ANSI) and Federal Information Processing Series (FIPS) codes — the county codes’ current home and change notices, including Connecticut’s 2022 change. https://www.census.gov/library/reference/code-lists/ansi.html
• U.S. Census Bureau, “Change to County Equivalents in the State of Connecticut,” Federal Register, 6 June 2022. https://www.federalregister.gov/documents/2022/06/06/2022-12063/change-to-county-equivalents-in-the-state-of-connecticut; the Bureau’s summary and county-to-town crosswalk: https://www.census.gov/programs-surveys/geography/technical-documentation/county-changes/2020.html
• National Institute of Standards and Technology, withdrawal of FIPS 6-4 and nine other standards, Federal Register, 2 September 2008. https://www.govinfo.gov/content/pkg/FR-2008-09-02/html/E8-20138.htm
• State returns from Jason Timm, PresElectionResults (https://github.com/jaytimm/PresElectionResults).
• MIT Election Data and Science Lab, County Presidential Election Returns 2000–2024, Harvard Dataverse, doi:10.7910/DVN/VOQCHQ — the maintained alternative, cited here for comparison rather than used.

The data itself

Every figure above is drawn from these tables. They are plain CSV, they open in a spreadsheet, and they are yours to take further.

census_counties.csv, pres2020_counties.csv, pres2024_counties.csv, pres2024_states.csv

Your turn

None of these is answered above, and every one can be answered from the tables you just opened.

• pres2020_counties.csv and pres2024_counties.csv have different row counts, and census_counties.csv has another. Sort each by county_fips (or fips) and work out which units are in one file and not the others.
• Join the two election files on county_fips. Count the rows that match. Then find what the unmatched rows actually are before you call them errors.
• pres2024_states.csv has harris, trump and other as percentages. Add the county totals within a state and check them against the state row. Do they agree?
• A join that never fails is suspicious. Work out what would have to be true of these files for a silent mismatch to hide inside a successful join.

I want to rebuild three public datasets from their original sources,
without any starting files. Please do the following and show your
work.

THE FIRST SOURCE. Tony McGovern's county-level compilation of
presidential results, on GitHub as
tonmcg/US_County_Level_Election_Results_08-24. Download two files raw
from the master branch:
2024_US_County_Level_Presidential_Results.csv and
2020_US_County_Level_Presidential_Results.csv. Each row is one
county: Democratic votes, Republican votes, total votes. No account
or key is needed. The repository's own README says the numbers were
copied by a program from news organizations' results pages and "are not
authoritative" -- an unusually honest label, and part of why the file
is worth studying.

THE SECOND SOURCE. U.S. Census Bureau, 2024 national county
gazetteer:
https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2024_Gazetteer/2024_Gaz_counties_national.zip
A tab-separated file, one row per county or county-equivalent, with
land area. It includes the territories.

THE THIRD SOURCE. The Bureau's 2020 county code list:
https://www2.census.gov/geo/docs/reference/codes2020/national_county2020.txt
Read the state and county codes as text: they are zero-padded, and
"01" stops being Alabama the moment it becomes 1.

WHAT TO BUILD.
1. A trimmed county returns table for each election: state, county
   code, county name, Democratic, Republican, and total votes.
2. The Bureau's official list of counties in the fifty states and
   the District of Columbia, with land area attached.
3. The mismatch lists: units that appear in the returns compilation
   but not on the Bureau's list, and the reverse -- then explain a
   few. Connecticut and Alaska repay the effort.

CHECK YOUR WORK. The copies this book used were downloaded in August
2026. If your rebuild matches them, you should find:
- 3,160 county rows for 2024 and 3,152 for 2020
- 3,144 counties on the Bureau's list once territories are dropped
- the gazetteer arrives with 3,223 rows, the 2020 code file with
  3,236 -- both before any trimming
The gazetteer is replaced every year and the compilation can change
without notice or version number; a difference means the source
moved, not that you made an error.