get-commuting-data.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "id": "760e9ebe",
   "metadata": {},
   "source": [
    "Quality concerns:\n",
    "Arkansas data from 2019 is corrupted.\n",
    "Mississippi data is from 2018.\n",
    "Alaska data is from 2016.\n",
    "No Puerto Rico datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8712551",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyquery import PyQuery as pq\n",
    "import requests\n",
    "import simplejson as json\n",
    "from tqdm import tqdm\n",
    "\n",
    "import csv\n",
    "from decimal import *\n",
    "from glob import glob\n",
    "import gzip\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4b2ba6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "downloaddir = \"downloads/\"\n",
    "rawdir = \"raw/\"\n",
    "parseddir = \"parsed/\"\n",
    "for mydir in [downloaddir, rawdir, parseddir]:\n",
    "    os.makedirs(mydir, exist_ok=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c67b9fe0",
   "metadata": {},
   "outputs": [],
   "source": [
    "starturl = \"https://lehd.ces.census.gov/data/lodes/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10710bd4",
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(starturl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da69be87",
   "metadata": {},
   "outputs": [],
   "source": [
    "lodesversion = pq(pq(r.content)(\"a\")[-1]).attr(\"href\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "582ec842",
   "metadata": {},
   "outputs": [],
   "source": [
    "baseurl = starturl + lodesversion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "131e82d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "r = requests.get(baseurl)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fcd8f02b",
   "metadata": {},
   "outputs": [],
   "source": [
    "states = {}\n",
    "for row in pq(r.content)(\"tr\"):\n",
    "    if pq(pq(row)(\"img\")).attr(\"src\") == \"/icons/folder.gif\":\n",
    "        state = pq(pq(row)(\"a\")[0]).attr(\"href\").replace(\"/\", \"\")\n",
    "        if state != \"us\":\n",
    "            states[state] = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef9b9f40",
   "metadata": {},
   "outputs": [],
   "source": [
    "badvalue = \"None found\"\n",
    "fileyears = {}\n",
    "for state in list(states.keys()):\n",
    "    print(f\"Searching {state}\")\n",
    "    stateurl = baseurl + state + \"/od/\"\n",
    "    r = requests.get(stateurl)\n",
    "    thingywanted = badvalue\n",
    "    for row in pq(r.content)(\"tr\")[2:]:\n",
    "        links = pq(row)(\"a\")\n",
    "        if links:\n",
    "            href = pq(pq(row)(\"a\")[0]).attr(\"href\")\n",
    "            if \"main_JT00\" in href:\n",
    "                thingywanted = href\n",
    "                # print(thingywanted)\n",
    "    if thingywanted == badvalue:\n",
    "        fileyear = badvalue\n",
    "    else:\n",
    "        fileyear = thingywanted.split(\"_\")[-1][:4]\n",
    "    if fileyear == \"2019\" and state == \"ar\":    # Patch for bad Arkansas 2019 data\n",
    "        fileyear = \"2018\"\n",
    "        thingywanted = thingywanted.replace(\"2019\", \"2018\")\n",
    "    if fileyear not in fileyears:\n",
    "        fileyears[fileyear] = []\n",
    "    fileyears[fileyear].append(state)\n",
    "    if thingywanted != badvalue:\n",
    "        targetfilename = downloaddir + state + fileyear + \".csv.gz\"\n",
    "        if not os.path.exists(targetfilename):\n",
    "            r = requests.get(stateurl + thingywanted)\n",
    "            with open(targetfilename, \"wb\") as outfile:\n",
    "                outfile.write(r.content)\n",
    "        \n",
    "        # Now get the aux files\n",
    "        thingywanted = thingywanted.replace(\"_main_\", \"_aux_\")\n",
    "        targetfilename = downloaddir + state + fileyear + \"_aux.csv.gz\"\n",
    "        if not os.path.exists(targetfilename):\n",
    "            r = requests.get(stateurl + thingywanted)\n",
    "            with open(targetfilename, \"wb\") as outfile:\n",
    "                outfile.write(r.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15a99002",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(json.dumps(fileyears, indent=4*' '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "59721185",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataindex = {}\n",
    "downloadedfilesraw = sorted(list(glob(downloaddir + \"*.gz\")))\n",
    "\n",
    "# Filter out aux files, which should be paired with the mains\n",
    "downloadedfiles = []\n",
    "for downloadedfileraw in downloadedfilesraw:\n",
    "    if \"_aux\" not in downloadedfileraw:\n",
    "        downloadedfiles.append(downloadedfileraw)\n",
    "for downloadedfile in downloadedfiles:\n",
    "    base = downloadedfile.replace(\"\\\\\", \"/\").split(\"/\")[-1]    #base filename is everything in the ultimate directory\n",
    "    state = base[:2]\n",
    "    dataindex[state] = downloadedfile   # Update with the latest year of data     "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bae7c514",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Use main and aux GZIPs to build a single CSV, badly.\n",
    "for state in tqdm(dataindex):\n",
    "    sourcefilename = dataindex[state]\n",
    "    targetfilename = rawdir + state + \".csv\"\n",
    "    with open(targetfilename, \"wb\") as outfile:\n",
    "        with gzip.open(sourcefilename, 'rb') as infile:\n",
    "            outfile.write(infile.read())\n",
    "        with gzip.open(sourcefilename.replace(\".csv\", \"_aux.csv\"), 'rb') as infile:\n",
    "            outfile.write(infile.read())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13ddcddc",
   "metadata": {},
   "outputs": [],
   "source": [
    "countyholder = {}\n",
    "csvfiles = sorted(list(glob(rawdir + \"*.csv\")))\n",
    "for csvfile in tqdm(csvfiles):\n",
    "    stateholder = {}\n",
    "    base = csvfile.replace(\"\\\\\", \"/\").split(\"/\")[-1][:2]\n",
    "    targetfilename = parseddir + base + \".json\"\n",
    "    with open(csvfile, \"r\") as infile:\n",
    "        reader = csv.DictReader(infile)\n",
    "        for row in reader:\n",
    "            if row['w_geocode'] != 'w_geocode':    # Skip extra header row from aux files\n",
    "                work = row['w_geocode'][:5]\n",
    "                home = row['h_geocode'][:5]\n",
    "                pop = int(row['S000'])\n",
    "                if work not in stateholder:\n",
    "                    stateholder[work] = {}\n",
    "                if home not in stateholder[work]:\n",
    "                    stateholder[work][home] = 0\n",
    "                stateholder[work][home] += pop\n",
    "\n",
    "    getcontext().prec = 6    # Decimal degrees of precision\n",
    "    betterstate = {}\n",
    "    betterstate['fileyear'] = \"\"\n",
    "    fileyear = \"error\"\n",
    "    for myyear in fileyears:\n",
    "        if base in fileyears[myyear]:\n",
    "            fileyear = myyear\n",
    "    betterstate['fileyear'] = fileyear\n",
    "    betterstate['commute'] = {}\n",
    "    betterstate['metadata'] = {}\n",
    "    for work in sorted(list(stateholder.keys())):\n",
    "        betterstate['commute'][work] = {}\n",
    "        betterstate['metadata'][work] = {}\n",
    "        betterstate['metadata'][work]['workers'] = 0\n",
    "        betterstate['metadata'][work]['counties'] = len(stateholder[work])\n",
    "        localwork = stateholder[work]\n",
    "        localwork = dict(sorted(stateholder[work].items(), key=lambda x: x[1], reverse=True))\n",
    "        for home in localwork:\n",
    "            betterstate['commute'][work][home] = {}\n",
    "            betterstate['commute'][work][home]['count'] = localwork[home]\n",
    "#            betterstate['commute'][work][home]['workshare'] = 0\n",
    "#            betterstate['commute'][work][home]['homeshare'] = 0            \n",
    "            betterstate['metadata'][work]['workers'] += localwork[home]\n",
    "#    for work in betterstate['commute']:\n",
    "#        workvalue = Decimal(betterstate['metadata'][work]['workers'])\n",
    "#        for home in betterstate['commute'][work]:\n",
    "#            betterstate['commute'][work][home]['workshare'] = Decimal(betterstate['commute'][work][home]['count']) / workvalue\n",
    "            # betterstate['commute'][work][home]['homeshare'] = Decimal(betterstate['commute'][work][home]['count']) / Decimal(betterstate['metadata'][home]['workers'])\n",
    "    for work in betterstate['commute']:\n",
    "        countyholder[work] = betterstate['commute'][work]\n",
    "    with open(targetfilename, \"w\") as outfile:\n",
    "        outfile.write(json.dumps(betterstate, indent=4*' '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "505d9dba",
   "metadata": {},
   "outputs": [],
   "source": [
    "betterstate['metadata']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ec7e08ca",
   "metadata": {},
   "outputs": [],
   "source": [
    "sortedcounties = {}\n",
    "for work in sorted(list(countyholder.keys())):\n",
    "    sortedcounties[work] = countyholder[work]\n",
    "with open(\"us-county-commuters.json\", \"w\") as outfile:\n",
    "    outfile.write(json.dumps(sortedcounties, indent=4*' '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e7caf6dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "sortedcounties['12099']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0abe61b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, because someone started relying on this and we can't change the existing data format ...\n",
    "# Let's start pulling data we already dumped out, bring it back in, and try processing in a more useful way.\n",
    "# We need to find out how many people are commuting into, and from, each county. First, into:\n",
    "\n",
    "workerstats = {}\n",
    "for filename in tqdm(glob(parseddir + \"*.json\")):\n",
    "    with open(filename) as infile:\n",
    "        raw = json.load(infile)\n",
    "        for fips in raw['metadata']:\n",
    "            if fips in workerstats:\n",
    "                print(f\"Duplicate {fips}\")\n",
    "            else:\n",
    "                workerstats[fips] = {}\n",
    "                workerstats[fips][\"incounty\"] = raw['metadata'][fips][\"workers\"]\n",
    "                workerstats[fips][\"fromcounty\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97863837",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now build data on how many people from a county are workers, working anywhere from this county.\n",
    "\n",
    "for workfips in sortedcounties:\n",
    "    if workfips not in workerstats:\n",
    "        print(f\"Somehow missing {workfips}\")\n",
    "    else:\n",
    "        for homefips in sortedcounties[workfips]:\n",
    "            workerstats[homefips][\"fromcounty\"] += sortedcounties[workfips][homefips][\"count\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "100c6ae3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Now, let's find out what counties are really important to a particular county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f6901c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "commutecut = 0.10   # If more than 10 percent of a county's workers come from here ...\n",
    "homecut = 0.20      # If more than 20 percent of a county's workers go to there"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6de7e341",
   "metadata": {},
   "outputs": [],
   "source": [
    "cutdict = {}\n",
    "for workfips in sortedcounties:\n",
    "    commutebreak = int(float(commutecut) * float(workerstats[workfips]['incounty']))\n",
    "    cutdict[workfips] = []\n",
    "    for homefips in sortedcounties[workfips]:\n",
    "        homebreak = int(float(homecut) * float(workerstats[homefips]['fromcounty']))\n",
    "        localcount = sortedcounties[workfips][homefips][\"count\"]\n",
    "        if localcount >= commutebreak or localcount >= homebreak:\n",
    "            cutdict[workfips].append(homefips)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf782535",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"county-highlights.json\", \"w\") as outfile:\n",
    "    outfile.write(json.dumps(cutdict, indent=4*' '))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f50fe246",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}