A cross-reference of ZCTAs by state, and how it was made

01_readme.md

A question came up on the US Census slack, leading to the recognition that the US Census Bureau API doesn't support queries for data for "all ZCTAs in a state". Nothing about the Census Bureau's definition of ZCTA requires that they be contained within a single state, which is probably why the API rejects the query with a message, error: unknown/unsupported geography heirarchy.

I've been looking for a general method to answer these kinds of questions for a long time. This Gist demonstrates a workable approach. It's based on data published by the Census LEHD LODES program, which provides, for every Census block in the US, a crosswalk indicating which geographies that block is in. (The set of geographies is limited but still very useful. See the technical doc PDF for more details.)

For any two geography types, one can simply select those two columns from the crosswalk and eliminate duplicate rows. This results in a crosswalk that can be used to answer the question "which {instances of geography type A} include(s) some or all of {instance of geography type b}". Or, put in plain language, "which state or states is a given ZIP code in?" The only shortcoming of the method is that it gives no information about how much of any given geography is contained in the other.

This gist includes the crosswalk created to answer the original question, since it seems like something many people might need. It also includes the code used to make that, and two variants for other geography types (but not the data files produced by those.)

You should be able to run these python programs readily in Google Colab, Azure Notebooks, or other public Jupyter Notebook server. In fact, here's an example, in Google Colab of a tract-to-place xref for the state of Massachusetts.

02_make_xref.py

import pandas as pd
# This is SLOW! (Took just over 4 minutes when I timed it.) 
# The file is 237 MB gzipped and 5GB unzipped.
# You may want to download it once and read it from your local file system.
# You can store it compressed, pandas has no problem reading compressed files.
# If you only need data for a specific state, consider changing both occurrences of 
# `us` to the appropriate state code
# (Start at https://lehd.ces.census.gov/data/lodes/LODES7/ if you want to check it out more)
df = pd.read_csv('https://lehd.ces.census.gov/data/lodes/LODES7/us/us_xwalk.csv.gz',
                 usecols=[1,2,12],
                 dtype={
                   'st': 'object', 
                   'stusps': 'object', 
                   'zcta': 'object'
                 })

df = df.drop_duplicates().sort_values(by=['st','zcta'])

from collections import Counter
zip_count = Counter(df['zcta'])

df['num_states'] = df['zcta'].apply(lambda x: zip_count[x])

df.to_csv('state_zcta_xref.csv',index=False)

03_make_county_zcta_xref.py

import pandas as pd
# This works for just one state at a time. You could change 'mo' to 'us' twice in the URL,
# but note that the full US file is large and the job would take several minutes to run.
# (Start at https://lehd.ces.census.gov/data/lodes/LODES7/ if you want to check it out more)
df = pd.read_csv('https://lehd.ces.census.gov/data/lodes/LODES7/mo/mo_xwalk.csv.gz',
                 usecols=[2,4,5,12],
                 dtype={
                   'cty': 'object', 
                   'ctyname': 'object',
                   'stusps': 'object', 
                   'zcta': 'object'
                 })

df = df.drop_duplicates().sort_values(by=['cty','zcta'])

from collections import Counter
zip_count = Counter(df['zcta'])

df['num_counties'] = df['zcta'].apply(lambda x: zip_count[x])

df.to_csv('county_zcta_xref.csv',index=False)

04_make_tract_countysubdivision_xref.py

# This variant was based on someone asking on the Census Bureau slack
# how to get data by San Diego County subdivision which was not 
# tabulated by county subdivision.
#
# In San Diego County, the only tract which crosses multiple county subdivisions 
# is 06073990100 which, by looking at Census Reporter, can be visually verified
# to be entirely water along the Pacific Ocean coast.
#
# When running this across the entire state, I found nearly 200 tracts that crossed
# county subdivision lines. They weren't all water-only, so rather than try to 
# explain/document, I simplified the task to focus on SD county.
# 
import pandas as pd
from collections import Counter
df = pd.read_csv('https://lehd.ces.census.gov/data/lodes/LODES7/ca/ca_xwalk.csv.gz',
                 usecols=[6, 16],
                 dtype={
                   'trct': 'object', 
                   'ctycsub': 'object', 
                 })

# California is 06 San Diego County is 073 so tracts there begin with 06073
sdtracts= df[df['trct'].str.startswith('06073')].drop_duplicates()
tract_count = Counter(sdtracts['trct'])
sdt_with_counts = sdtracts.set_index('trct').join(pd.DataFrame.from_dict(tract_count,orient='index',columns=['num_cousubs']))

for tract, count in tract_count.most_common():
    if count > 1:
        print(f"Tract {tract} is in {count} subdivisions, so check https://censusreporter.org/profiles/14000US{tract}")

sdt_with_counts.to_csv('/tmp/sd_tract_cousub_xwalk.csv')

98_xwalk_headers.csv

99_state_zcta_xref.csv