Joining Federal Datasets on FIPS Codes: Common Pitfalls

One of the core promises of FIPS codes is that they make it easy to join datasets from different federal agencies — match on the 5-digit county code and you're done. In practice, there are several common failure modes that cause joins to silently drop rows or produce incorrect matches. Understanding them can save hours of debugging.

Leading zero loss. The most common pitfall: a state or county FIPS code stored as an integer loses its leading zero. FIPS code "06037" (Los Angeles) becomes 6037, which doesn't match the string "06037" in the other dataset. Always store FIPS codes as strings and always zero-pad when constructing them. In SQL: LPAD(fips_code::text, 5, '0'). In Python: str(fips).zfill(5). Our Los Angeles County page shows the correct zero-padded format.

Vintage mismatch. County boundaries and names occasionally change. Shannon County, SD was renumbered 46102 (from 46113) in 2015. Bedford City, VA was merged into Bedford County. Connecticut reorganized its county-level geography in 2022. If you're joining data across years that span a boundary change, some FIPS codes in one dataset won't exist in the other. The Census Bureau publishes vintage-specific reference files for this reason.

Geographic level mismatch. Some datasets report at the county level, some at the MSA level, some at the state level. MSA FIPS codes (5 digits, e.g., 42660 for Seattle) look similar to county FIPS codes (also 5 digits) but are entirely different identifiers from a different namespace. Never join an MSA code against a county code column. Always verify which geographic level a dataset uses before attempting a join.

Separate vs. combined columns. Many older federal datasets store state and county FIPS in separate columns (state_fips and county_fips) rather than a single 5-digit field. When joining to a dataset that uses the combined 5-digit code, concatenate with zero-padding: LPAD(state_fips, 2, '0') || LPAD(county_fips, 3, '0'). Our FIPS code explainer covers the structure in detail, and the search tool is useful for verifying individual codes during development.