Why Do Some of My Flows Vanish When I Join Them to a Map?

On this page

Problem statement

You join an origin–destination table to block, tract or county geometry, and part of the data does not match. Sometimes it is a whole state: every flow from Connecticut, Alabama or California disappears. There is no error. The join simply finds nothing, and totals come out short. The cause is almost always a code that lost its leading zero. FIPS and census block codes are strings of digits, and states numbered below 10 start with a zero. Read them as numbers and 09001… becomes 9001…, which matches nothing.

This page reproduces the problem with the LODES commuting file for jobs in Rhode Island held by out-of-state residents, where Connecticut's code is 09. It measures what is lost and fixes it.

Quick answer

Tell pandas the code columns are strings when reading, and check the length of every code before joining:

od = pd.read_csv("data/ri_od_aux_JT00_2021.csv.gz", dtype={"w_geocode": str, "h_geocode": str})
print(od.h_geocode.str.len().value_counts().to_dict(), "characters per home block code")
print(f"home blocks matched to the CT crosswalk: {od.h_geocode.isin(CT.index).sum():,} pairs, "
      f"{od.loc[od.h_geocode.isin(CT.index), 'S000'].sum():,} jobs")

{15: 65920} characters per home block code
home blocks matched to the CT crosswalk: 10,800 pairs, 10,959 jobs

Bar chart on a log scale of Rhode Island jobs held by residents of other states: Massachusetts about 47,000, Connecticut about 11,000 in red, then New York, New Jersey, New Hampshire and others, with California also in red; red marks state codes starting with zero. — Out-of-state workers in Rhode Island by home state. The red states have codes starting with 0; read as numbers, all their flows drop out of a join, and Connecticut is the second-largest source.

Reproduce it, measure the loss, repair damaged codes

1. Reproduce it

Read the same file with pandas' defaults. The geocodes look like large integers, so pandas stores them as int64:

bad = pd.read_csv("data/ri_od_aux_JT00_2021.csv.gz")
print(bad.dtypes[["w_geocode", "h_geocode"]].to_dict())
codes = bad.h_geocode.astype(str)
print(codes.str.len().value_counts().sort_index().to_dict(), "characters per home block code")
print(f"matched to the CT crosswalk: {codes.isin(CT.index).sum():,} pairs")

{'w_geocode': dtype('int64'), 'h_geocode': dtype('int64')}
{14: 11059, 15: 54861} characters per home block code
matched to the CT crosswalk: 0 pairs

The Connecticut codes now have 14 characters instead of 15, and none of them matches.

2. Measure what was lost

The loss is not random: it removes one state completely. Count the jobs by home state with the correct codes and see which part vanished:

by_state = od.groupby(od.h_geocode.str[:2]).S000.sum()
lost = by_state[by_state.index.str.startswith("0")]
print(f"jobs held by residents of states coded 01–09: {lost.sum():,} ({lost.sum() / by_state.sum():.1%} of aux jobs); "
      f"states: {', '.join(STATE_NAME.get(s, s) for s in lost.index)}")

jobs held by residents of states coded 01–09: 11,218 (16.7% of aux jobs); states: Alabama, Alaska, Arizona, Arkansas, California, Colorado, Connecticut

One read_csv argument decides whether a state is in the analysis.

3. Repair codes that were already damaged

If a file has already been read wrongly, pad the codes back to their full width. Block codes are 15 digits, tracts 11, counties 5, states 2:

fixed = bad.h_geocode.astype(str).str.zfill(15)
print(f"after zfill(15): {fixed.isin(CT.index).sum():,} pairs match the CT crosswalk; "
      f"identical to reading as strings: {(fixed == od.h_geocode).all()}")

after zfill(15): 10,800 pairs match the CT crosswalk; identical to reading as strings: True

Padding works only when you know the full width. A code that was stored as a float (9001.0) or rounded in a spreadsheet may be beyond repair; re-read the source.

A check to run before every join

The data used on this page

LODES version 8, 2021: the Rhode Island aux OD file (jobs in Rhode Island held by residents of other states) and the Connecticut block crosswalk.

from pathlib import Path

import pandas as pd
import requests

Path("data").mkdir(exist_ok=True)
L = "https://lehd.ces.census.gov/data/lodes/LODES8/"
for name, url in {"ri_od_aux_JT00_2021.csv.gz": L + "ri/od/ri_od_aux_JT00_2021.csv.gz",
                  "ct_xwalk.csv.gz": L + "ct/ct_xwalk.csv.gz"}.items():
    if not Path("data", name).exists():
        Path("data", name).write_bytes(requests.get(url, timeout=600).content)
CT = pd.read_csv("data/ct_xwalk.csv.gz", dtype=str, usecols=["tabblk2020", "ctyname"]).set_index("tabblk2020")
STATE_NAME = {"01": "Alabama", "02": "Alaska", "04": "Arizona", "05": "Arkansas", "06": "California", "08": "Colorado", "09": "Connecticut"}
print(f"{len(CT):,} Connecticut blocks; code width {CT.index.str.len().unique().tolist()}")

49,926 Connecticut blocks; code width [15]

Example 1: a check to run before every join

def check_codes(s, width, name):
    lengths = s.astype(str).str.len().value_counts().to_dict()
    assert set(lengths) == {width}, f"{name}: expected {width}-character codes, found lengths {lengths}"
    return f"{name}: all {len(s):,} codes are {width} characters"

print(check_codes(od.h_geocode, 15, "h_geocode (read as str)"))
try:
    check_codes(bad.h_geocode, 15, "h_geocode (read as int)")
except AssertionError as e:
    print("caught:", e)

h_geocode (read as str): all 65,920 codes are 15 characters
caught: h_geocode (read as int): expected 15-character codes, found lengths {15: 54861, 14: 11059}

A FIPS code is not a number

A FIPS code is an identifier that happens to be made of digits, not a number. Arithmetic on it is meaningless, and its leading zeros carry information: 09 is Connecticut, 9 is nothing. Every tool that guesses column types from content treats such a column as numeric unless told otherwise. That includes pandas, Excel and many database loaders. The damage is silent, because the conversion succeeds and the join simply finds no partners.

Check code widths before every join.

The loss is also systematic. Only codes starting with zero change, so whole states disappear while others are untouched: Alabama through Connecticut among the states, and many counties within them. A total that is a few percent short is easy to miss. A map with an empty state next to a full one is not, but by then the analysis has often moved on. Checking code widths before every join catches it at the start.

Excel, Parquet and ZIP codes

Leading-zero problems and fixes.

Excel strips leading zeros on opening a CSV; never round-trip code columns through a spreadsheet.
Parquet and GeoPackage keep types; once a column is stored as a string, it stays one.
Census flows workbook: state codes there are three characters (006); read them with dtype=str too.
ZIP codes have the same problem (02906 in Providence).
Mixed widths in one column (e.g. 2- and 5-digit codes) usually mean two geographic levels mixed together.

Internal links

How to map commuting flows from LODES — the file used here
How to map a workplace catchment — where the out-of-state homes matter

FAQ

Why does pandas remove the leading zeros from my FIPS codes?

It infers the column as integers. Pass dtype={"code": str} (or dtype=str for all columns) to read_csv.

How do I add leading zeros back?

Convert to string and pad to the known width with .str.zfill(n): 2 for states, 5 for counties, 11 for tracts, 15 for blocks.

Why do only some states go missing after a join?

Only states with codes 01 to 09 start with a zero, so only their codes change when read as numbers.

How can I catch this before it causes problems?

Check that every code has the expected number of characters before joining, and fail loudly if not.