History of us_county_flows
The changes to the source code of this entry in the repository, newest first, each with the differences it made to the entry's files. They are also listed on GitLab.
[entries] New entry: us_county_flows 4b190dc
diff --git a/entries/us_county_flows/__init__.py b/entries/us_county_flows/__init__.py
new file mode 100644
index 0000000..a131cde
--- /dev/null
+++ b/entries/us_county_flows/__init__.py
@@ -0,0 +1,208 @@
+#! /usr/bin/env python
+# -*- coding: utf-8 -*-
+#
+# Copyright (C) 2020-2026 Tiago de Paula Peixoto <tiago@skewed.de>
+#
+# This program is free software: you can redistribute it and/or modify it under
+# the terms of the GNU Affero General Public License as published by the Free
+# Software Foundation, either version 3 of the License, or (at your option) any
+# later version.
+#
+# This program is distributed in the hope that it will be useful, but WITHOUT
+# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+# FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
+# details.
+#
+# You should have received a copy of the GNU Affero General Public License along
+# with this program. If not, see <http://www.gnu.org/licenses/>.
+
+from .. import *
+import openpyxl
+import pandas
+
+title = "US county-to-county migration and commuting flows (2006-2023)"
+description = r"""Networks of the flows of people between the counties of the United States: yearly migration flows from the tax returns of the Internal Revenue Service (IRS), and commuting flows from the American Community Survey (ACS) of the Census Bureau. Nodes are counties (and county equivalents, e.g. parishes, boroughs and independent cities), and directed edges are weighted by the number of people (or households) moving from one county to another.
+
+1. Migration (`migration_2011_2012`, ..., `migration_2022_2023`): the county-to-county migration data of the Statistics of Income (SOI) division of the IRS, one network per pair of consecutive years. Migration is inferred by matching the individual income tax returns filed in two consecutive years, comparing the addresses of the filers: a directed edge $(i, j)$ means that taxpayers who filed in county $i$ in the first year filed in county $j$ in the second year. The edges are weighted by the number of returns (approximately, households), the number of individuals (counted as exemptions in the earlier years) and the adjusted gross income of the returns. To protect privacy, the IRS lists only flows of at least 20 returns (10 in 2011-2012 and 2012-2013; in a few years, some flows of 18 or 19 returns are listed); the smaller flows are only given in aggregates (by state and region of origin), which are not included here. The nodes are the counties of the 50 states and the District of Columbia in the data of that year; the flows from and to foreign countries are not included as edges. Years before 2011 (published in other formats) are not included. The file of 2013-2014 contains about a thousand repeated rows, which are included only once.
+2. Commuting (`commuting_2006_2010`, `commuting_2009_2013`, `commuting_2011_2015`, `commuting_2016_2020`): the residence county to workplace county commuting flows (Table 1) of the ACS 5-year estimates for these periods. A directed edge $(i, j)$ means that workers 16 years and over living in county $i$ worked in county $j$, weighted by the estimated number of workers (with the margin of error of the estimate). Self-loops are the workers who live and work in the same county. The nodes are the counties of the United States and Puerto Rico in the data; commuting to a workplace abroad is not included. The tables of commuting flows by travel mode (2009-2013) and between minor civil divisions are not included.
+
+The networks have the following properties:
+
+- `name`: the name of the county.
+- `state`: the state (two-letter postal code for the migration networks, full name for the commuting networks).
+- `fips`: the five-digit FIPS code of the county (state and county code).
+- Migration networks: `nonmigrant_returns`, `nonmigrant_individuals`, `nonmigrant_agi`: the number of returns, individuals and the adjusted gross income (in thousands of US dollars) of the taxpayers who filed in the same county in both years; `inflow_returns`, `inflow_individuals`, `inflow_agi` and `outflow_returns`, `outflow_individuals`, `outflow_agi`: the totals of migration into and out of the county (including from and to other countries, and the flows below the threshold); -1 if suppressed by the IRS, or if missing.
+- Migration networks, edges: `returns`, `individuals`, `agi` (the adjusted gross income of the migrating returns, in thousands of US dollars).
+- Commuting networks, edges: `weight`, the estimated number of workers; `moe`, the margin of error of the estimate (90% confidence)."""
+tags = ['Social', 'Travel', 'Economic', 'Weighted', 'Metadata']
+url = ['https://www.irs.gov/statistics/soi-tax-stats-migration-data',
+ 'https://www.census.gov/topics/employment/commuting/guidance/flows.html']
+citation = [('Internal Revenue Service, Statistics of Income, "SOI Tax Stats - Migration Data." (2011-2023)',
+ 'https://www.irs.gov/statistics/soi-tax-stats-migration-data'),
+ ('U.S. Census Bureau, "County-to-County Commuting Flows." American Community Survey 5-year estimates (2006-2020)',
+ 'https://www.census.gov/topics/employment/commuting/guidance/flows.html')]
+icon_hash = None
+upstream_license = ("Public domain (works of the US federal government)", None)
+upstream_prefix = None
+
+IRS = 'https://www.irs.gov/pub/irs-soi'
+MIGRATION_YEARS = [(y, y + 1) for y in range(2011, 2023)]
+CENSUS = 'https://www2.census.gov/programs-surveys'
+COMMUTING = {"2006_2010": f"{CENSUS}/demo/tables/metro-micro/2010/commuting-employment-2010/table1.xlsx",
+ "2009_2013": f"{CENSUS}/commuting/tables/time-series/commuting-flows/table1.xlsx",
+ "2011_2015": f"{CENSUS}/demo/tables/metro-micro/2015/commuting-flows-2015/table1.xlsx",
+ "2016_2020": f"{CENSUS}/demo/tables/metro-micro/2020/commuting-flows-2020/table1.xlsx"}
+
+def yy(y1, y2):
+ return f"{y1 % 100:02d}{y2 % 100:02d}"
+
+urls = {}
+files = []
+for y1, y2 in MIGRATION_YEARS:
+ fin, fout = f"countyinflow{yy(y1, y2)}.csv", f"countyoutflow{yy(y1, y2)}.csv"
+ urls[fin] = f"{IRS}/{fin}"
+ urls[fout] = f"{IRS}/{fout}"
+ files.append(((fin, fout), f"migration_{y1}_{y2}", None))
+for period, u in COMMUTING.items():
+ fname = f"commuting_{period}_table1.xlsx"
+ urls[fname] = u
+ files.append((fname, f"commuting_{period}", None))
+
+def fetch_upstream(force=False):
+ return fetch_archived_files(__name__.split(".")[-1], urls, force=force, tries=3)
+
+def read_irs(name, fname):
+ with open_upstream_file(name, fname, "rb") as f:
+ d = pandas.read_csv(f, encoding="latin-1", dtype=str)
+ # the codes are not zero-padded in some years
+ for c in d.columns:
+ if c.endswith("statefips"):
+ d[c] = d[c].str.strip().str.zfill(2)
+ elif c.endswith("countyfips"):
+ d[c] = d[c].str.strip().str.zfill(3)
+ # the file of 2013-2014 repeats about a thousand rows
+ return d.drop_duplicates()
+
+def parse_migration(name, fin, fout):
+ din = read_irs(name, fin)
+ dout = read_irs(name, fout)
+ # rows of state and national totals (county code 000) are left out
+ din = din[(din.y2_countyfips != "000") & (din.y2_statefips != "00")].copy()
+ dout = dout[(dout.y1_countyfips != "000") & (dout.y1_statefips != "00")].copy()
+ din["ostate"] = din.y1_statefips.astype(int)
+ dout["dstate"] = dout.y2_statefips.astype(int)
+ # county-to-county flows (state codes above 56 are aggregates or foreign)
+ cc = din[din.ostate <= 56]
+ nonmig = cc[(cc.y1_statefips == cc.y2_statefips) &
+ (cc.y1_countyfips == cc.y2_countyfips)]
+ cc = cc.drop(nonmig.index)
+ nonmig = nonmig.set_index(nonmig.y2_statefips + nonmig.y2_countyfips)
+ # names of the counties, from the flows and the totals of each file
+ names, states = {}, {}
+ for d, s, c, st, cn in [(din, "y1_statefips", "y1_countyfips", "y1_state", "y1_countyname"),
+ (dout, "y2_statefips", "y2_countyfips", "y2_state", "y2_countyname")]:
+ x = d[d[s].astype(int) <= 56]
+ for f, stv, n in zip(x[s] + x[c], x[st], x[cn]):
+ if "Non-migrants" in n:
+ n = n.replace(" Non-migrants", "")
+ names.setdefault(f, n)
+ states.setdefault(f, stv)
+ tin = din[din.ostate == 96]
+ tin = tin.set_index(tin.y2_statefips + tin.y2_countyfips)
+ assert not tin.index.duplicated().any()
+ tout = dout[dout.dstate == 96]
+ tout = tout.set_index(tout.y1_statefips + tout.y1_countyfips)
+ assert not tout.index.duplicated().any()
+ assert not nonmig.index.duplicated().any()
+ fips = sorted(set(din.y2_statefips + din.y2_countyfips) |
+ set(dout.y1_statefips + dout.y1_countyfips) |
+ set(cc.y1_statefips + cc.y1_countyfips))
+ fips = [f for f in fips if int(f[:2]) <= 56]
+ idx = {f: i for i, f in enumerate(fips)}
+ g = Graph(directed=True)
+ g.add_vertex(len(fips))
+ g.vp.name = g.new_vp("string", vals=[names.get(f, "") for f in fips])
+ g.vp.state = g.new_vp("string", vals=[states.get(f, "") for f in fips])
+ g.vp.fips = g.new_vp("string", vals=fips)
+ def vals(t, col):
+ return [int(t[col].get(f, -1)) for f in fips]
+ for prefix, t in [("nonmigrant", nonmig), ("inflow", tin), ("outflow", tout)]:
+ for p, col in [("returns", "n1"), ("individuals", "n2"), ("agi", "agi")]:
+ g.vp[f"{prefix}_{p}"] = g.new_vp("int64_t", vals=vals(t, col))
+ g.ep.returns = g.new_ep("int64_t")
+ g.ep.individuals = g.new_ep("int64_t")
+ g.ep.agi = g.new_ep("int64_t")
+ g.add_edge_list(numpy.column_stack(((cc.y1_statefips + cc.y1_countyfips).map(idx).to_numpy(),
+ (cc.y2_statefips + cc.y2_countyfips).map(idx).to_numpy(),
+ cc.n1.astype("int64").to_numpy(),
+ cc.n2.astype("int64").to_numpy(),
+ cc.agi.astype("int64").to_numpy())),
+ eprops=[g.ep.returns, g.ep.individuals, g.ep.agi])
+ return g
+
+def parse_commuting(name, fname):
+ with open_upstream_file(name, fname, "rb", seekable=True) as f:
+ wb = openpyxl.load_workbook(f, read_only=True, data_only=True)
+ ws = wb[wb.sheetnames[0]]
+ rows = iter(ws.iter_rows(values_only=True))
+ prev = ()
+ for r in rows:
+ if r[0] == "State FIPS Code":
+ # the headers of the flow columns are in the previous row in
+ # 2006-2010
+ header = [str(x).strip() if x is not None else
+ (str(p).strip() if p is not None else "")
+ for x, p in zip(r, prev)]
+ break
+ prev = r
+ st = [i for i, x in enumerate(header) if x == "State FIPS Code"]
+ ct = [i for i, x in enumerate(header) if x.startswith("County FIPS Code")]
+ if len(st) == 1:
+ # 2006-2010: the workplace state column has another header
+ st.append(2)
+ sn = [i for i, x in enumerate(header) if x in ("State Name", "State",
+ "State/U.S. Island Area/Foreign Country")]
+ cn = [i for i, x in enumerate(header) if x in ("County Name", "County")]
+ w = [i for i, x in enumerate(header) if x in ("Workers in Commuting Flow", "Number")][0]
+ moe = [i for i, x in enumerate(header) if x in ("Margin of Error", "MOE")][0]
+ flows = []
+ names = {}
+ for r in rows:
+ if r[st[0]] is None or not str(r[st[0]]).strip().isdigit() or r[w] is None:
+ continue
+ o = f"{int(r[st[0]]):02d}{int(r[ct[0]]):03d}"
+ names.setdefault(o, (r[cn[0]], r[sn[0]]))
+ if r[st[1]] is None or int(r[st[1]]) > 72:
+ continue # workplace abroad
+ ws_ = int(r[st[1]])
+ d = f"{ws_:02d}{int(r[ct[1]]):03d}"
+ names.setdefault(d, (r[cn[1]], r[sn[1]]))
+ flows.append((o, d, int(r[w]), int(r[moe]) if r[moe] is not None else -1))
+ wb.close()
+ fips = sorted(names)
+ idx = {f: i for i, f in enumerate(fips)}
+ g = Graph(directed=True)
+ g.add_vertex(len(fips))
+ g.vp.name = g.new_vp("string", vals=[str(names[f][0]).strip() for f in fips])
+ g.vp.state = g.new_vp("string", vals=[str(names[f][1]).strip() for f in fips])
+ g.vp.fips = g.new_vp("string", vals=fips)
+ g.ep.weight = g.new_ep("int64_t")
+ g.ep.moe = g.new_ep("int64_t")
+ g.add_edge_list([(idx[o], idx[d], x, m) for o, d, x, m in flows],
+ eprops=[g.ep.weight, g.ep.moe])
+ return g
+
+@cache_network()
+@coerce_props(vforce={"name": "string", "fips": "string"})
+@annotate()
+def parse(alts=None):
+ global files
+ name = __name__.split(".")[-1]
+ for fnames, alt, fmt in files:
+ if alts is not None and alt not in alts:
+ continue
+ if alt.startswith("migration"):
+ g = parse_migration(name, *fnames)
+ else:
+ g = parse_commuting(name, fnames)
+ yield alt, g