04_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# California H-2A visas analysis\n",
    "\n",
    "By [Ben Welsh](mailto:ben.welsh@latimes.com)\n",
    "\n",
    "The Los Angeles Times conducted an analysis of temporary visas granted to foreign agricultural workers by the United States Department of Labor via its H-2A program.\n",
    "\n",
    "The results were reported in a May 25, 2017, Los Angeles Times story titled [\"Trump promised a ‘big beautiful door’ in his border wall. California farmers are ready and waiting\"](http://www.latimes.com/projects/la-fi-farm-labor-guestworkers/).\n",
    "\n",
    "Here are the key findings of the data analysis, which is documented below:\n",
    "\n",
    "* The total number of certified H2A visas is going up nationwide\n",
    "* California's total topped 11,000 last year, a fivefold increase from 2011\n",
    "* If this year’s hiring pace holds, that number will soar even higher\n",
    "* Counties on the Central Coast, from Ventura up to Santa Cruz, are driving the growth\n",
    "* Strawberries and lettuce crops have accounted for most of the new workers\n",
    "* The Santa Maria Valley, straddling San Louis Obispo and Santa Barbara counties, leapt from six sheepherders in 2012 to more than 2,000 guest farm workers last year\n",
    "* Strawberry workers account for most of the growth in that area"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How we did it"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Python analysis tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import geopandas as gp\n",
    "from datetime import date\n",
    "from shapely.geometry import Point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.options.display.max_columns = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "input_dir = os.path.join(os.getcwd(), 'input')\n",
    "output_dir = os.path.join(os.getcwd(), 'output')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare the data for analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Download the source data files from the U.S. Department of Labor's [Office of Foreign Labor Certification](https://www.foreignlaborcert.doleta.gov/performancedata.cfm)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "%run 01_download.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Prepare them for analysis by carefully combining annual lists, winnowing out duplicate entries and limiting the result to approved applications."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "%run 02_transform.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Map work site locations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%capture\n",
    "run 03_geocode.ipynb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: The total number of certified H2A visas is going up nationwide"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in the transformed file for analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\n",
    "    os.path.join(output_dir, \"transformed_master_cases.csv\"),\n",
    "    index_col=\"latimes_id\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Group by calendar year and sum the total number of certified workers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "annual_usa = df.groupby(\"certification_start_year\").agg(dict(workers_certified=\"sum\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7ff472aa0f50>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAA4MAAAHHCAYAAAAFwY2pAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3Xu4XVV97//31wSIFuUaU0rAYI2WcC2EgAIKRkO4aOCn\nKBRL7AGjRxDFC8TqU1oqbaz+RFHBQyXlUgQBUdISRCQoHGogCfeASIoooVxCEi4RUMDv+WONwCLs\n3Paa7JW1x/v1PPvZa33nmHOO/U1Wkk/mXGNFZiJJkiRJqsuruj0BSZIkSdLAMwxKkiRJUoUMg5Ik\nSZJUIcOgJEmSJFXIMChJkiRJFTIMSpIkSVKFDIOSJEmSVCHDoCRJkiRVyDAoSZIkSRUa2u0JNG3z\nzTfPUaNGdXsakiRJktQV8+bNezQzh69u3KALg6NGjWLu3LndnoYkSZIkdUVE/GZNxnmbqCRJkiRV\nyDAoSZIkSRUyDEqSJElShQbdewb78uyzz7Jw4UKeeeaZbk9Fr5Bhw4YxcuRI1ltvvW5PRZIkSeoJ\nVYTBhQsX8trXvpZRo0YREd2ejhqWmSxevJiFCxeyzTbbdHs6kiRJUk+o4jbRZ555hs0228wgOEhF\nBJtttplXfiVJkqS1UEUYBAyCg5y/vpIkSdLaqSYMSpIkSZJeVMV7Blc0aurljR7vvmkHNno8SZIk\nSXqleWWwh4waNYpHH32029NYra9//es89dRTLzw/4IADeOyxxwA47bTT2HbbbTniiCOYMWMG06ZN\nW6tj90oPJEmSpHVdlVcGe9Hzzz/f8f5DhgxpaDarPs/Xv/51PvShD/Ga17wGgJkzZ76w/fTTT+en\nP/0pI0eOBOC9733vKz4nSZIkSS/nlcEB8JWvfIXTTjsNgOOPP553vvOdAMyaNYsjjjiCCy64gB12\n2IHtt9+eE0888YX9NtxwQz7zmc+w00478Ytf/OKF+tNPP83+++/Pv/7rvwLw7//+74wbN46dd96Z\nj370oy8ExxX3nzp1KmPGjGHHHXfks5/97Ern+/DDD3PIIYew0047sdNOO/Ff//Vfa3yeU045hf/5\nn/9h3333Zd999wVevJr3sY99jHvvvZf999+fU089lbPPPptjjz0WgEWLFvG+972P3Xbbjd12243r\nr78egMWLFzNhwgS22247jj76aDKz818QSZIkSYbBgbD33ntz3XXXATB37lyWLVvGs88+y3XXXceb\n3/xmTjzxRGbNmsUtt9zCnDlz+NGPfgTA7373O3bffXduvfVW9tprLwCWLVvGe97zHg4//HA+8pGP\ncNddd/H973+f66+/nltuuYUhQ4Zw/vnnv2z/bbfdlh/+8IfMnz+f2267jS9+8Ysrne9xxx3HO97x\nDm699VZuuukmtttuuzU+z9/93d/xZ3/2Z1xzzTVcc801Lznud77znRe2HX/88S/Z9slPfpLjjz+e\nOXPm8IMf/ICjjz4agH/4h39gr732Yv78+RxyyCH89re/beBXRJIkSZK3iQ6AXXfdlXnz5vHEE0+w\nwQYbsMsuuzB37lyuu+463vOe97DPPvswfPhwAI444giuvfZaDj74YIYMGcL73ve+lxxr0qRJnHDC\nCRxxxBEAXH311cybN4/ddtsNaF01fP3rXw/wkv032mgjhg0bxlFHHcVBBx3EQQcdtNL5zpo1i3PP\nPfeFY2y00Uacd955a3Se/vrpT3/KnXfe+cLzJ554gmXLlnHttddy6aWXAnDggQeyySabdHQeSZIk\nSS2GwQGw3nrrsc0223D22Wfztre9jR133JFrrrmGBQsWMGrUKObNm9fnfsOGDXvZ+/z23HNPfvzj\nH/NXf/VXRASZyeTJk/nnf/7nVe4/dOhQbrzxRq6++mouueQSvvWtbzFr1qw1/hnW9Dz99cc//pHZ\ns2czbNiwjo4jSZIkac1UGQa78VEQe++9N1/96leZPn06O+ywA5/+9KfZddddGTduHMcddxyPPvoo\nm2yyCRdccAGf+MQnVnqck08+mZNPPpljjjmG008/nfHjxzNp0iSOP/54Xv/617NkyRKefPJJ3vCG\nN7xkv2XLlvHUU09xwAEHsOeee/LGN75xpecYP348Z5xxBp/61Kd4/vnnWbZs2RqfB+C1r30tTz75\nJJtvvvka92fChAl885vf5HOf+xwAt9xyCzvvvDNvf/vb+d73vscXv/hFrrjiCpYuXbrGx5QkSZK0\ncr5ncIDsvffePPjgg7z1rW9lxIgRDBs2jL333pstttiCadOmse+++7LTTjux6667MmnSpFUe6xvf\n+AZPP/00J5xwAmPGjOFLX/oSEyZMYMcdd+Td7343Dz744Mv2efLJJznooIPYcccd2Wuvvfja1762\nyuNfc8017LDDDuy6667ceeeda3wegClTpjBx4sQXFpBZE6eddhpz585lxx13ZMyYMXznO98B4KST\nTuLaa69lu+2249JLL2Xrrbde42NKkiRJWrkYbKszjh07NufOnfuS2l133cW2227bpRlpoPjrLEmS\netmoqZd3ewprpBt32WntRMS8zBy7unFeGZQkSZKkClX5nkG1nHLKKVx88cUvqR166KF84Qtf6NKM\nJEmSJA2UasJgZhIR3Z7GOuULX/jCoAl+g+12Z0mSJOmVVsVtosOGDWPx4sUGhkEqM1m8eLEfSyFJ\nkiSthSquDI4cOZKFCxeyaNGibk9Fr5Bhw4YxcuTIbk9DkiRJ6hlVhMHlH/ouSZIkSWqp4jZRSZIk\nSdJLGQYlSZIkqUKGQUmSJEmq0GrDYERMj4hHIuKOFeqfiIhfRsT8iPiXtvrnI2JBRNwdEfu11SeW\n2oKImNpW3yYibij170fE+qW+QXm+oGwf1cQPLEmSJElasyuDZwMT2wsRsS8wCdgpM7cDvlrqY4DD\ngO3KPqdHxJCIGAJ8G9gfGAMcXsYCfBk4NTPfBCwFjir1o4ClpX5qGSdJkiRJasBqw2BmXgssWaH8\nv4Fpmfn7MuaRUp8EXJiZv8/MXwMLgHHla0Fm3puZfwAuBCZF61Pg3wlcUvY/Bzi47VjnlMeXAOPD\nT42XJEmSpEb09z2Dbwb2Lrdv/jwidiv1LYH728YtLLWV1TcDHsvM51aov+RYZfvjZbwkSZIkqUP9\n/ZzBocCmwB7AbsBFEfHGxma1liJiCjAFYOutt+7WNCRJkiSpZ/T3yuBC4NJsuRH4I7A58ACwVdu4\nkaW2svpiYOOIGLpCnfZ9yvaNyviXycwzM3NsZo4dPnx4P38kSZIkSapHf8Pgj4B9ASLizcD6wKPA\nDOCwshLoNsBo4EZgDjC6rBy6Pq1FZmZkZgLXAO8vx50MXFYezyjPKdtnlfGSJEmSpA6t9jbRiLgA\n2AfYPCIWAicB04Hp5eMm/gBMLkFtfkRcBNwJPAcck5nPl+McC1wJDAGmZ+b8cooTgQsj4kvAzcBZ\npX4WcF5ELKC1gM1hDfy8kiRJkiTWIAxm5uEr2fShlYw/BTilj/pMYGYf9XtprTa6Yv0Z4NDVzU+S\nJEmStPb6e5uoJEmSJKmHGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJ\nkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIk\nqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZ\nBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQk\nSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQqsNgxExPSIeiYg7+tj2\nmYjIiNi8PI+IOC0iFkTEbRGxS9vYyRFxT/ma3FbfNSJuL/ucFhFR6ptGxFVl/FURsUkzP7IkSZIk\naU2uDJ4NTFyxGBFbAROA37aV9wdGl68pwBll7KbAScDuwDjgpLZwdwbwkbb9lp9rKnB1Zo4Gri7P\nJUmSJEkNWG0YzMxrgSV9bDoVOAHIttok4NxsmQ1sHBFbAPsBV2XmksxcClwFTCzbXpeZszMzgXOB\ng9uOdU55fE5bXZIkSZLUoaH92SkiJgEPZOat5a7O5bYE7m97vrDUVlVf2EcdYERmPlgePwSM6M9c\nJUmS9MoZNfXybk9hjdw37cBuT0Fa56x1GIyI1wB/S+sW0QGRmRkRubLtETGF1m2pbL311gM1LUmS\nJEnqWf1ZTfTPgW2AWyPiPmAkcFNE/CnwALBV29iRpbaq+sg+6gAPl9tIKd8fWdmEMvPMzBybmWOH\nDx/ejx9JkiRJkuqy1mEwM2/PzNdn5qjMHEXr1s5dMvMhYAZwZFlVdA/g8XKr55XAhIjYpCwcMwG4\nsmx7IiL2KKuIHglcVk41A1i+6ujktrokSZIkqUNr8tESFwC/AN4SEQsj4qhVDJ8J3AssAP4V+DhA\nZi4B/hGYU75OLjXKmO+Wff4buKLUpwHvjoh7gHeV55IkSZKkBqz2PYOZefhqto9qe5zAMSsZNx2Y\n3kd9LrB9H/XFwPjVzU+SJEmStPb6855BSZIkSVKPMwxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJ\nkiRJFTIMSpIkSVKFDIOSJEmSVCHDoCRJkiRVyDAoSZIkSRUyDEqSJElShQyDkiRJklQhw6AkSZIk\nVcgwKEmSJEkVMgxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJkiRJFTIMSpIkSVKFDIOSJEmSVCHD\noCRJkiRVyDAoSZIkSRUyDEqSJElShQyDkiRJklQhw6AkSZIkVcgwKEmSJEkVMgxKkiRJUoUMg5Ik\nSZJUIcOgJEmSJFXIMChJkiRJFTIMSpIkSVKFDIOSJEmSVCHDoCRJkiRVyDAoSZIkSRUyDEqSJElS\nhQyDkiRJklSh1YbBiJgeEY9ExB1tta9ExC8j4raI+GFEbNy27fMRsSAi7o6I/drqE0ttQURMbatv\nExE3lPr3I2L9Ut+gPF9Qto9q6oeWJEmSpNqtyZXBs4GJK9SuArbPzB2BXwGfB4iIMcBhwHZln9Mj\nYkhEDAG+DewPjAEOL2MBvgycmplvApYCR5X6UcDSUj+1jJMkSZIkNWC1YTAzrwWWrFD7SWY+V57O\nBkaWx5OACzPz95n5a2ABMK58LcjMezPzD8CFwKSICOCdwCVl/3OAg9uOdU55fAkwvoyXJEmSJHWo\nifcM/i/givJ4S+D+tm0LS21l9c2Ax9qC5fL6S45Vtj9exkuSJEmSOtRRGIyILwDPAec3M51+z2NK\nRMyNiLmLFi3q5lQkSZIkqScM7e+OEfFh4CBgfGZmKT8AbNU2bGSpsZL6YmDjiBharv61j19+rIUR\nMRTYqIx/mcw8EzgTYOzYsdnXGEmSJIBRUy/v9hTWyH3TDuz2FCQNcv26MhgRE4ETgPdm5lNtm2YA\nh5WVQLcBRgM3AnOA0WXl0PVpLTIzo4TIa4D3l/0nA5e1HWtyefx+YFZb6JQkSZIkdWC1VwYj4gJg\nH2DziFgInERr9dANgKvKmi6zM/NjmTk/Ii4C7qR1++gxmfl8Oc6xwJXAEGB6Zs4vpzgRuDAivgTc\nDJxV6mcB50XEAloL2BzWwM8rSZIkSWINwmBmHt5H+aw+asvHnwKc0kd9JjCzj/q9tFYbXbH+DHDo\n6uYnSZIkSVp7TawmKkmSJEnqMYZBSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIkqUKGQUmSJEmq\nkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQoZB\nSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmS\nJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQK\nGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqtNow\nGBHTI+KRiLijrbZpRFwVEfeU75uUekTEaRGxICJui4hd2vaZXMbfExGT2+q7RsTtZZ/TIiJWdQ5J\nkiRJUufW5Mrg2cDEFWpTgaszczRwdXkOsD8wunxNAc6AVrADTgJ2B8YBJ7WFuzOAj7TtN3E155Ak\nSZIkdWi1YTAzrwWWrFCeBJxTHp8DHNxWPzdbZgMbR8QWwH7AVZm5JDOXAlcBE8u212Xm7MxM4NwV\njtXXOSRJkiRJHRraz/1GZOaD5fFDwIjyeEvg/rZxC0ttVfWFfdRXdY6XiYgptK5EsvXWW6/tzyJJ\n0jpt1NTLuz2FNXLftAO7PQVJ0lroeAGZckUvG5hLv8+RmWdm5tjMHDt8+PBXciqSJEmSNCj0Nww+\nXG7xpHx/pNQfALZqGzey1FZVH9lHfVXnkCRJkiR1qL9hcAawfEXQycBlbfUjy6qiewCPl1s9rwQm\nRMQmZeGYCcCVZdsTEbFHWUX0yBWO1dc5JEmSJEkdWu17BiPiAmAfYPOIWEhrVdBpwEURcRTwG+AD\nZfhM4ABgAfAU8DcAmbkkIv4RmFPGnZyZyxel+TitFUtfDVxRvljFOSRJkiRJHVptGMzMw1eyaXwf\nYxM4ZiXHmQ5M76M+F9i+j/rivs4hSZIkSepcxwvISJIkSZJ6j2FQkiRJkipkGJQkSZKkChkGJUmS\nJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQK\nGQYlSZIkqUKGQUmSJEmq0NBuT6AXjJp6ebensEbum3Zgt6cgSZIkqUd4ZVCSJEmSKmQYlCRJkqQK\nGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqNLTb\nE1B9Rk29vNtTWK37ph3Y7SlIPa8XXuvg612SVC+vDEqSJElShQyDkiRJklQhw6AkSZIkVcgwKEmS\nJEkVMgxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJkiRJFTIMSpIkSVKFDIOSJEmSVKGOwmBEHB8R\n8yPijoi4ICKGRcQ2EXFDRCyIiO9HxPpl7Abl+YKyfVTbcT5f6ndHxH5t9YmltiAipnYyV0mSJEnS\ni/odBiNiS+A4YGxmbg8MAQ4DvgycmplvApYCR5VdjgKWlvqpZRwRMabstx0wETg9IoZExBDg28D+\nwBjg8DJWkiRJktShTm8THQq8OiKGAq8BHgTeCVxStp8DHFweTyrPKdvHR0SU+oWZ+fvM/DWwABhX\nvhZk5r2Z+QfgwjJWkiRJktShfofBzHwA+CrwW1oh8HFgHvBYZj5Xhi0EtiyPtwTuL/s+V8Zv1l5f\nYZ+V1SVJkiRJHRra3x0jYhNaV+q2AR4DLqZ1m+eAi4gpwBSArbfeuhtTkCRJkrQOGTX18m5PYY3c\nN+3Arp27k9tE3wX8OjMXZeazwKXAnsDG5bZRgJHAA+XxA8BWAGX7RsDi9voK+6ys/jKZeWZmjs3M\nscOHD+/gR5IkSZKkOnQSBn8L7BERrynv/RsP3AlcA7y/jJkMXFYezyjPKdtnZWaW+mFltdFtgNHA\njcAcYHRZnXR9WovMzOhgvpIkSZKkot+3iWbmDRFxCXAT8BxwM3AmcDlwYUR8qdTOKrucBZwXEQuA\nJbTCHZk5PyIuohUknwOOycznASLiWOBKWiuVTs/M+f2dryRJkiTpRf0OgwCZeRJw0grle2mtBLri\n2GeAQ1dynFOAU/qozwRmdjJHSZIkSdLLdfrREpIkSZKkHtTRlUFJGkxcdUySJNXEK4OSJEmSVCHD\noCRJkiRVyNtEpR7mbY2SJEnqL68MSpIkSVKFDIOSJEmSVCHDoCRJkiRVyDAoSZIkSRUyDEqSJElS\nhQyDkiRJklQhw6AkSZIkVcgwKEmSJEkVMgxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJkiRJFTIM\nSpIkSVKFDIOSJEmSVCHDoCRJkiRVyDAoSZIkSRUyDEqSJElShQyDkiRJklQhw6AkSZIkVcgwKEmS\nJEkVMgxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJkiRJFTIMSpIkSVKFDIOSJEmSVCHDoCRJkiRV\nyDAoSZIkSRUyDEqSJElShQyDkiRJklShjsJgRGwcEZdExC8j4q6IeGtEbBoRV0XEPeX7JmVsRMRp\nEbEgIm6LiF3ajjO5jL8nIia31XeNiNvLPqdFRHQyX0mSJElSS6dXBr8B/Dgz/wLYCbgLmApcnZmj\ngavLc4D9gdHlawpwBkBEbAqcBOwOjANOWh4gy5iPtO03scP5SpIkSZLoIAxGxEbA24GzADLzD5n5\nGDAJOKcMOwc4uDyeBJybLbOBjSNiC2A/4KrMXJKZS4GrgIll2+syc3ZmJnBu27EkSZIkSR3o5Mrg\nNsAi4N8i4uaI+G5E/AkwIjMfLGMeAkaUx1sC97ftv7DUVlVf2Ef9ZSJiSkTMjYi5ixYt6uBHkiRJ\nkqQ6dBIGhwK7AGdk5l8Cv+PFW0IBKFf0soNzrJHMPDMzx2bm2OHDh7/Sp5MkSZKkntdJGFwILMzM\nG8rzS2iFw4fLLZ6U74+U7Q8AW7XtP7LUVlUf2UddkiRJktShfofBzHwIuD8i3lJK44E7gRnA8hVB\nJwOXlcczgCPLqqJ7AI+X20mvBCZExCZl4ZgJwJVl2xMRsUdZRfTItmNJkiRJkjowtMP9PwGcHxHr\nA/cCf0MrYF4UEUcBvwE+UMbOBA4AFgBPlbFk5pKI+EdgThl3cmYuKY8/DpwNvBq4onxJkiRJkjrU\nURjMzFuAsX1sGt/H2ASOWclxpgPT+6jPBbbvZI6SJEmSpJfr9HMGJUmSJEk9yDAoSZIkSRUyDEqS\nJElShQyDkiRJklQhw6AkSZIkVcgwKEmSJEkVMgxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJkiRJ\nFTIMSpIkSVKFDIOSJEmSVCHDoCRJkiRVyDAoSZIkSRUyDEqSJElShQyDkiRJklQhw6AkSZIkVcgw\nKEmSJEkVMgxKkiRJUoUMg5IkSZJUIcOgJEmSJFXIMChJkiRJFTIMSpIkSVKFDIOSJEmSVCHDoCRJ\nkiRVyDAoSZIkSRUyDEqSJElShQyDkiRJklQhw6AkSZIkVcgwKEmSJEkVMgxKkiRJUoUMg5IkSZJU\nIcOgJEmSJFXIMChJkiRJFeo4DEbEkIi4OSL+szzfJiJuiIgFEfH9iFi/1DcozxeU7aPajvH5Ur87\nIvZrq08stQURMbXTuUqSJEmSWpq4MvhJ4K62518GTs3MNwFLgaNK/ShgaamfWsYREWOAw4DtgInA\n6SVgDgG+DewPjAEOL2MlSZIkSR3qKAxGxEjgQOC75XkA7wQuKUPOAQ4ujyeV55Tt48v4ScCFmfn7\nzPw1sAAYV74WZOa9mfkH4MIyVpIkSZLUoU6vDH4dOAH4Y3m+GfBYZj5Xni8EtiyPtwTuByjbHy/j\nX6ivsM/K6pIkSZKkDvU7DEbEQcAjmTmvwfn0dy5TImJuRMxdtGhRt6cjSZIkSeu8Tq4M7gm8NyLu\no3UL5zuBbwAbR8TQMmYk8EB5/ACwFUDZvhGwuL2+wj4rq79MZp6ZmWMzc+zw4cM7+JEkSZIkqQ79\nDoOZ+fnMHJmZo2gtADMrM48ArgHeX4ZNBi4rj2eU55TtszIzS/2wstroNsBo4EZgDjC6rE66fjnH\njP7OV5IkSZL0oqGrH7LWTgQujIgvATcDZ5X6WcB5EbEAWEIr3JGZ8yPiIuBO4DngmMx8HiAijgWu\nBIYA0zNz/iswX0mSJEmqTiNhMDN/BvysPL6X1kqgK455Bjh0JfufApzSR30mMLOJOUqSJEmSXtTE\n5wxKkiRJknqMYVCSJEmSKmQYlCRJkqQKGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkG\nJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJ\nkqQKGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIq\nZBiUJEmSpAoZBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIkqUKGQUmSJEmqkGFQ\nkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAr1OwxGxFYRcU1E3BkR\n8yPik6W+aURcFRH3lO+blHpExGkRsSAibouIXdqONbmMvyciJrfVd42I28s+p0VEdPLDSpIkSZJa\nOrky+BzwmcwcA+wBHBMRY4CpwNWZORq4ujwH2B8YXb6mAGdAKzwCJwG7A+OAk5YHyDLmI237Texg\nvpIkSZKkot9hMDMfzMybyuMngbuALYFJwDll2DnAweXxJODcbJkNbBwRWwD7AVdl5pLMXApcBUws\n216XmbMzM4Fz244lSZIkSepAI+8ZjIhRwF8CNwAjMvPBsukhYER5vCVwf9tuC0ttVfWFfdT7Ov+U\niJgbEXMXLVrU0c8iSZIkSTXoOAxGxIbAD4BPZeYT7dvKFb3s9Byrk5lnZubYzBw7fPjwV/p0kiRJ\nktTzOgqDEbEerSB4fmZeWsoPl1s8Kd8fKfUHgK3adh9Zaquqj+yjLkmSJEnqUCeriQZwFnBXZn6t\nbdMMYPmKoJOBy9rqR5ZVRfcAHi+3k14JTIiITcrCMROAK8u2JyJij3KuI9uOJUmSJEnqwNAO9t0T\n+Gvg9oi4pdT+FpgGXBQRRwG/AT5Qts0EDgAWAE8BfwOQmUsi4h+BOWXcyZm5pDz+OHA28GrgivIl\nSZIkSepQv8NgZv5fYGWf+ze+j/EJHLOSY00HpvdRnwts3985SpIkSZL61shqopIkSZKk3mIYlCRJ\nkqQKGQZEmBK6AAARlElEQVQlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJ\nkiRJqpBhUJIkSZIqZBiUJEmSpAoZBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIk\nqUKGQUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAoZ\nBiVJkiSpQoZBSZIkSaqQYVCSJEmSKmQYlCRJkqQKGQYlSZIkqUKGQUmSJEmqkGFQkiRJkipkGJQk\nSZKkChkGJUmSJKlChkFJkiRJqpBhUJIkSZIqZBiUJEmSpAqt82EwIiZGxN0RsSAipnZ7PpIkSZI0\nGKzTYTAihgDfBvYHxgCHR8SY7s5KkiRJknrfOh0GgXHAgsy8NzP/AFwITOrynCRJkiSp563rYXBL\n4P625wtLTZIkSZLUgcjMbs9hpSLi/cDEzDy6PP9rYPfMPHaFcVOAKeXpW4C7B3Si/bM58Gi3JzGI\n2M/m2Mtm2c9m2c/m2Mtm2c9m2c/m2Mtm9Uo/35CZw1c3aOhAzKQDDwBbtT0fWWovkZlnAmcO1KSa\nEBFzM3Nst+cxWNjP5tjLZtnPZtnP5tjLZtnPZtnP5tjLZg22fq7rt4nOAUZHxDYRsT5wGDCjy3OS\nJEmSpJ63Tl8ZzMznIuJY4EpgCDA9M+d3eVqSJEmS1PPW6TAIkJkzgZndnscroKdua+0B9rM59rJZ\n9rNZ9rM59rJZ9rNZ9rM59rJZg6qf6/QCMpIkSZKkV8a6/p5BSZIkSdIrwDAoSZIkSRUyDEqSJElS\nhdb5BWSklYmITQEyc0m35zIY2M/mRMQIYMvy9IHMfLib8+ll9rJ5vtabZT+b4Wu9WfazWYO5ny4g\nMwAiYiPg88DBwOuBBB4BLgOmZeZjXZxeT4mIrYF/AcYDjwEBvA6YBUzNzPu6N7veYz+bFRE7A98B\nNgIeKOWRtHr78cy8qVtz6zX2slm+1ptlP5vja71Z9rNZNfTTMDgAIuJKWn9BnJOZD5XanwKTgfGZ\nOaGb8+slEfEL4OvAJZn5fKkNAQ4FPpWZe3Rzfr3GfjYrIm4BPpqZN6xQ3wP4P5m5U3dm1nvsZbN8\nrTfLfjbH13qz7GezauinYXAARMTdmfmWtd2ml4uIezJz9NpuU9/sZ7NW088FmfmmgZ5Tr7KXzfK1\n3iz72Rxf682yn82qoZ++Z3Bg/CYiTqB1ZfBheOHe4w8D93dzYj1oXkScDpzDi73bitZV1pu7Nqve\nZT+bdUVEXA6cy0v7eSTw467NqjfZy2b5Wm+W/WyOr/Vm2c9mDfp+emVwAETEJsBUYBKt9wwCPAzM\nAL7sm87XXESsDxxFq5fL38i7EPgP4KzM/H235taL7GfzImJ/XtrPB4AZmTmze7PqTfayOb7Wm2U/\nm+VrvVn2s1mDvZ+GQUmSJEmqkJ8z2GURsUu35zBYRMRB3Z7DYGI/mxURU7o9h8HCXjbL13qz7Gdz\nfK03y342a7D00zDYff+72xMYRHbr9gQGGfvZrOj2BAYRe9ksX+vNsp/N8bXeLPvZrEHRT28TlSRJ\nkqQKuZroAImItwMPZ+bdEbEn8Fbgrsy8vMtT6zkRsSEwkdZqTs8DvwJ+kpl/7OrEelRE/AV9vzH6\nru7NavCJiL/JzH/r9jx6Sfm9uSVwQ2Yua6tPzMxBsYrbQIqIcUBm5pyIGEPrz9FfDpZFELotIs7N\nzCO7PY9eFxF7AeOAOzLzJ92eT6+JiN1p/fvyiYh4Na0FDHcB7gT+KTMf7+oEe0xEHAf8MDMH7er/\nXhkcABHxdVp/sA0FrgTGA1cA7wBuzszPdXF6PSUiPgB8FrgN2Bf4L1q3O+8AfCgzb+vi9HpORJwI\nHA5cSGslPICRwGHAhZk5rVtzG2wi4reZuXW359Eryl/AxwB3ATsDn8zMy8q2mzLT91uvhYg4Cdif\n1t9DVwG7A9cA7wauzMxTuji9nhMRM1Ys0fo7aRZAZr53wCfVoyLixswcVx5/hNbr/ofABOA//Hto\n7UTEfGCnzHwuIs4EngIuofVvz50y8//r6gR7TEQ8DvwO+G/gAuDizFzU3Vk1yzA4AMoLc3vg1bSu\numyZmU9FxHq0wuD2XZ1gD4mI24A9Sv82B87PzP0iYkfgO5n5ti5PsadExK+A7TLz2RXq6wPz/eDk\ntVN+f/a5CXhzZm4wkPPpZRFxO/DWzFwWEaNo/WPmvMz8RkTcnJl/2dUJ9pjSz52BDYCHgJFtVw5u\nyMwduzrBHhMRN9G60vJdIGm9xi+g9R9pZObPuze73tL+eo6IOcABmbkoIv4EmJ2ZO3R3hr0lIu7K\nzG3L45f8x1lE3JKZO3dvdr0nIm4GdgXeBXwQeC8wj9br/dLMfLKL02uEt4kOjMzMjIjltzEuT+B/\nxEV81lYAT5fHv6N8bmNm3hYRr+varHrXH4E/A36zQn2Lsk1rZwSwH7B0hXrQuoqtNfeq5beGZuZ9\nEbEPcElEvIFB8qb9AfZcZj4PPBUR/52ZTwBk5tNtfzdpzY0FPgl8AfhcZt4SEU8bAvvlVeXzmF9F\n6yLFIoDM/F1EPNfdqfWkO9relnBrRIzNzLkR8Wbg2dXtrJfJ8jaknwA/KRdy9qd1V9VXgeHdnFwT\nDIMD4/KIuA4YRut/ES+KiNm0bhO9tqsz6z0zgR9HxLW03u9yMUBEbIr/QOyPTwFXR8Q9wPL74bcG\n3gQc27VZ9a7/BDbMzFtW3BARPxv46fS0hyNi5+W9LFcIDwKm07otXGvnDxHxmsx8itb/cgMQERvh\nf/ystfKPw1Mj4uLy/WH8N1V/bUTrSksAGRFbZOaDZX0A/15fe0cD34iILwKPAr+IiPtp/R1/dFdn\n1pte8nuw3Ek1A5gREa/pzpSa5W2iAyQi3krrfxdmR8SfA4cAvwUuceGTtRMRBwBjgFsz86pSexWw\nXmb+vquT60Gld+N46QIyc8pVBKkrImIkratZD/Wxbc/MvL4L0+pZEbFBX38+ltvtt8jM27swrUEj\nIg4E9szMv+32XAaL8g/tEZn5627PpReVu6W2ofWfFAsz8+EuT6knRcSbM/NX3Z7HK8kwOIAiYgRt\n/+D2hdl/9vKVFxEbtq/gqM7Yz+bYy2bZz2bZz+bYy2bZz2YNln4aBgdAROwMfIfWrRAPlPJI4DHg\n45l5U7fm1mvs5cBx9ctm2c/m2Mtm2c9m2c/m2Mtm2c9mDZZ+en/7wDgb+Ghm3tBejIg9gH8DdurG\npHrU2djLxkTEp1e2CdhwIOcyGNjP5tjLZtnPZtnP5tjLZtnPZtXQT1eyHBh/smJ4AcjM2cCfdGE+\nvcxeNuufgE2A167wtSH++dAf9rM59rJZ9rNZ9rM59rJZ9rNZg76fXhkcGFdExOXAuby4YuNWwJHA\nj7s2q95kL5t1E/CjzJy34oaIcNWxtWc/m2Mvm2U/m2U/m2Mvm2U/mzXo++l7BgdIROwPTOKlKzbO\nyMyZ3ZtVb7KXzYmItwBLln+u0wrbRrgwz9qxn82xl82yn82yn82xl82yn82qoZ+GQUmSJEmq0KC4\n13VdFxEbRcS0iLgrIpZExOLyeFpEbNzt+fUSe9mstn7+0n52zn42x142y342y342x142y342q4Z+\nGgYHxkXAUmDfzNw0MzcD9qX1cQgXdXVmvcdeNmt5P/dZoZ9LsZ/9YT+bYy+bZT+bZT+bYy+bZT+b\nNej76W2iAyAi7s7Mt6ztNr2cvWyW/WyW/WyOvWyW/WyW/WyOvWyW/WxWDf30yuDA+E1EnBARI5YX\nImJERJzIiytias3Yy2bZz2bZz+bYy2bZz2bZz+bYy2bZz2YN+n4aBgfGB4HNgJ9HxNKIWAL8DNgU\n+EA3J9aD7GWz7Gez7Gdz7GWz7Gez7Gdz7GWz7GezBn0/vU10gETEXwAjgdmZuaytPjEz/Xy8tWAv\nm2U/m2U/m2Mvm2U/m2U/m2Mvm2U/mzXY++mVwQEQEccBlwHHAndExKS2zf/UnVn1JnvZLPvZLPvZ\nHHvZLPvZLPvZHHvZLPvZrBr6ObTbE6jER4BdM3NZRIwCLomIUZn5DSC6OrPeYy+bZT+bZT+bYy+b\nZT+bZT+bYy+bZT+bNej7aRgcGK9aflk5M++LiH1o/WZ6A4PkN9IAspfNsp/Nsp/NsZfNsp/Nsp/N\nsZfNsp/NGvT99DbRgfFwROy8/En5TXUQsDmwQ9dm1ZvsZbPsZ7PsZ3PsZbPsZ7PsZ3PsZbPsZ7MG\nfT9dQGYARMRI4LnMfKiPbXtm5vVdmFZPspfNsp/Nsp/NsZfNsp/Nsp/NsZfNsp/NqqGfhkFJkiRJ\nqpC3iUqSJElShQyDkiRJklQhw6AkSZIkVcgwKEnqCRGxc0Qc0Pb8vRExtTweHhE3RMTNEbF3RMyM\niI37cY59IuJtbc8/FhFHNvMTrPScG0fEx/u57982PR9JUj1cQEaStM6LiKHAh4CxmXlsH9sPA96V\nmUd3eJ6/B5Zl5lc7Oc5annMU8J+Zuf1a7BO0PuPqiczc8BWa2qrOPyQznx/o80qSmuWVQUnSgIqI\nIyPitoi4NSLOK1f1fhARc8rXnmXc35ft1wPnAScDH4yIWyLigxHx4Yj4VvkMqH8BJpVtr46I+yJi\n877OV2rvabuS+NOIGFFC2ceA48tx9i5z+GzZZ+eImF2O9cOI2KTUfxYRX46IGyPiVxGx9yp+9u3K\nuFvKcUYD04A/L7WvRMSGEXF1RNwUEbdHxKSy76iIuDsizgXuAM4CXl32O38l5zs5Ij7V9vyUiPhk\nefy50u/bIuIf2sb8KCLmRcT8iJjSVl8WEf9/RNwKvHUtfsklSesorwxKkgZMRGwH/BB4W2Y+GhGb\nAt8CTs/M/xsRWwNXZua25Srde4C9MvPpiPgwbVcG25/3se0+YCwwYsXzZeaSEuQey8yMiKOBbTPz\nMyteGWx/HhG3AZ/IzJ9HxMnA6zLzUxHxM2Be2f8A4NOZ+a6V/PzfBGZn5vkRsT4wpMzxhSuD5Sro\nazLziRJoZwOjgTcA95afZXYZu2xVVwZLwL00M3eJiFcB9wDjgF2B9wMfpXWFcQbwL5l5bVuPXg3M\nAd6RmYsjIoEPZuZFK/8VliT1kqHdnoAkqSrvBC7OzEcBSuh4FzCmdecjAK+LiOUBZ0ZmPt3k+Up9\nJPD9iNgCWB/49aoOEhEbARtn5s9L6Rzg4rYhl5bv84BRqzjUL4AvROuDjC/NzHvafu4XTgf8U0S8\nHfgjsCWtwAjwm+VBcE1k5n0RsTgi/rIc4+YS7CYAE4Cby9ANaQXOa4HjIuKQUt+q1BcDzwM/WNNz\nS5LWfYZBSVK3vQrYIzOfaS+WkPS7V+ic3wS+lpkzImIf4O87PN7vy/fnWcXfrZn5vYi4ATgQmBkR\nH6V1ta/dEcBwYNfMfLZc5RxWtvWnH98FPgz8KTC91AL458z8P+0DSy/eBbw1M58qVz2Xn/sZ3yco\nSYOL7xmUJA2kWcChEbEZQLlN9CfAJ5YPKO8B7MuTwGsbOB/ARsAD5fHk1Z0jMx8Hlra9H/CvgZ+v\nOG51IuKNwL2ZeRpwGbBjH+fcCHikBMF9ad0eujLPRsR6qzntD4GJwG7AlaV2JfC/ll+BjYgtI+L1\n5dxLSxD8C2CPtfsJJUm9xDAoSRowmTkfOAX4eVmI5GvAccDYspDJnbQWcenLNbRuJ70lIj7Ywfmg\ndSXw4oiYBzzatst/AIcsX0BmhcNNBr5S3ju4M60FbdbWB4A7IuIWYHvg3MxcDFwfEXdExFeA82n1\n43bgSOCXqzjemcBtK1tABiAz/0Crdxctv7KXmT8Bvgf8opznElqB9MfA0Ii4i9bCNmt8S6okqfe4\ngIwkSYNYWTjmJuDQzLyn2/ORJK07vDIoSdIgFRFjgAXA1QZBSdKKvDIoSVLDImI/4MsrlH+dmYf0\nNb6B820GXN3HpvHlNlRJkl7GMChJkiRJFfI2UUmSJEmqkGFQkiRJkipkGJQkSZKkChkGJUmSJKlC\nhkFJkiRJqtD/Aw67sLBJDxYLAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7ff473e32b50>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "annual_usa.plot.bar(figsize=(15, 7))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>workers_certified</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>certification_start_year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2008.0</th>\n",
       "      <td>91324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009.0</th>\n",
       "      <td>86604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010.0</th>\n",
       "      <td>75617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011.0</th>\n",
       "      <td>77221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>86725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>100360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>119240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>140701</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>166457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017.0</th>\n",
       "      <td>79893</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          workers_certified\n",
       "certification_start_year                   \n",
       "2008.0                                91324\n",
       "2009.0                                86604\n",
       "2010.0                                75617\n",
       "2011.0                                77221\n",
       "2012.0                                86725\n",
       "2013.0                               100360\n",
       "2014.0                               119240\n",
       "2015.0                               140701\n",
       "2016.0                               166457\n",
       "2017.0                                79893"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annual_usa"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: California's total topped 11,000 last year, a fivefold increase from 2011"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Group the applications by state and year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "state_totals = df.groupby([\n",
    "    'state',\n",
    "    'certification_start_year']\n",
    ").agg(dict(workers_certified=\"sum\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a crosstab for a graphic and comparison"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "state_crosstab = state_totals.unstack(1).fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output it for a graphic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "state_crosstab.to_csv(\"./output/state_crosstab.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter down to just California's totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_totals = state_totals[state_totals.index.get_level_values(0) == 'CA']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ouput the annual totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7ff47344a0d0>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAA30AAAHiCAYAAABcJaUGAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3XvUpGdZJvrrJh0IoBACLUIS7AwEFRhRiICbPQ6HPeEQ\nNeCGgDoSMJpRGQ/jMbq2Ew/ohHH2qLiE2dFEAqKBAZUMUUImEJ1ROSQcE44ZSEgYkEgCoihyuPcf\n9YZ8NN1Jd39Pd+V7vt9vrVpd9dRbVfd3pWulr+99663q7gAAADCn2617AAAAAA4epQ8AAGBiSh8A\nAMDElD4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIHAAAwsR3rHuBA3eMe9+hdu3atewwA\nAIC1uPzyy/+mu3fe2nZbtvTt2rUrl1122brHAAAAWIuqumZftnN4JwAAwMSUPgAAgIkpfQAAABNT\n+gAAACam9AEAAExM6QMAAJiY0gcAADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAmpvQB\nAABMTOkDAACYmNIHAAAwMaUPAABgYjvWPQAAAGwnu864cN0j7JOrzzpp3SMwiD19AAAAE1P6AAAA\nJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEAAExM\n6QMAAJiY0gcAADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIH\nAAAwMaUPAABgYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAA\nYGJKHwAAwMSUPgAAgIkpfQAAABO71dJXVedW1Uer6ooNa0dV1cVV9b7lz7st61VVz6uqq6rq7VX1\nkA2POXXZ/n1VdeqG9YdW1TuWxzyvqmr0DwkAALBd7cuevhcmefxua2ckuaS7j09yyXI7SZ6Q5Pjl\ncnqSFySrkpjkzCQPT/KwJGfeVBSXbb5vw+N2fy0AAAAO0K2Wvu7+8yQ37LZ8cpLzluvnJXnShvUX\n9crrkxxZVfdK8rgkF3f3Dd19Y5KLkzx+ue8u3f367u4kL9rwXAAAAGzSgX6m757d/eHl+keS3HO5\nfnSSazdsd92ydkvr1+1hfY+q6vSquqyqLrv++usPcHQAAIDtY9Mncln20PWAWfbltc7u7hO6+4Sd\nO3ceipcEAADY0g609P31cmhmlj8/uqx/KMmxG7Y7Zlm7pfVj9rAOAADAAAda+i5IctMZOE9N8soN\n689YzuL5iCSfWA4DvSjJiVV1t+UELicmuWi572+r6hHLWTufseG5AAAA2KQdt7ZBVf1BkkcluUdV\nXZfVWTjPSvKyqjotyTVJTlk2/5MkT0xyVZJPJXlWknT3DVX1S0netGz3i91908lhfjCrM4TeMcmf\nLhcAAAAGuNXS193fsZe7HruHbTvJs/fyPOcmOXcP65cledCtzQEAAMD+2/SJXAAAALjtUvoAAAAm\npvQBAABMTOkDAACYmNIHAAAwMaUPAABgYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzp\nAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEAAExM6QMAAJiY0gcA\nADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIHAAAwMaUPAABg\nYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSU\nPgAAgIkpfQAAABNT+gAAACa2Y90DAABw27brjAvXPcI+ufqsk9Y9Atwm2dMHAAAwMaUPAABgYkof\nAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAA\ngIkpfQAAABPbVOmrqn9XVVdW1RVV9QdVdURVHVdVb6iqq6rqpVV1+2XbOyy3r1ru37XheX5mWX9P\nVT1ucz8SAAAANzng0ldVRyf54SQndPeDkhyW5OlJnpvk17r7fkluTHLa8pDTkty4rP/asl2q6gHL\n4x6Y5PFJnl9Vhx3oXAAAANxss4d37khyx6rakeROST6c5DFJXr7cf16SJy3XT15uZ7n/sVVVy/r5\n3f3p7v5AkquSPGyTcwEAAJBNlL7u/lCS/5Tkg1mVvU8kuTzJx7v7s8tm1yU5erl+dJJrl8d+dtn+\n7hvX9/CYL1JVp1fVZVV12fXXX3+gowMAAGwbmzm8825Z7aU7Lsm9k9w5q8MzD5ruPru7T+juE3bu\n3HkwXwoAAGAKmzm88/9K8oHuvr67P5PkD5M8MsmRy+GeSXJMkg8t1z+U5NgkWe6/a5KPbVzfw2MA\nAADYhM2Uvg8meURV3Wn5bN5jk7wzyeuSPGXZ5tQkr1yuX7DcznL/a7u7l/WnL2f3PC7J8UneuIm5\nAAAAWOy49U32rLvfUFUvT/LmJJ9N8pYkZye5MMn5VfWcZe2c5SHnJHlxVV2V5IasztiZ7r6yql6W\nVWH8bJJnd/fnDnQuAAAAbnbApS9JuvvMJGfutvz+7OHsm939j0meupfn+eUkv7yZWQAAAPhSm/3K\nBgAAAG7DlD4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIHAAAwMaUPAABgYkofAADAxJQ+\nAACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAA\nABNT+gAAACam9AEAAExM6QMAAJiY0gcAADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAm\npvQBAABMTOkDAACYmNIHAAAwMaUPAABgYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzp\nAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEAAExM6QMAAJiY0gcA\nADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAAT27HuAQAAAA7UrjMuXPcI++Tqs05a22vb0wcAADAx\npQ8AAGBiSh8AAMDElD4AAICJbar0VdWRVfXyqnp3Vb2rqr6pqo6qqour6n3Ln3dbtq2qel5VXVVV\nb6+qh2x4nlOX7d9XVadu9ocCAABgZbN7+n4jyau7+2uSPDjJu5KckeSS7j4+ySXL7SR5QpLjl8vp\nSV6QJFV1VJIzkzw8ycOSnHlTUQQAAGBzDrj0VdVdk3xzknOSpLv/qbs/nuTkJOctm52X5EnL9ZOT\nvKhXXp/kyKq6V5LHJbm4u2/o7huTXJzk8Qc6FwAAADfbzPf0HZfk+iS/W1UPTnJ5kh9Jcs/u/vCy\nzUeS3HO5fnSSazc8/rplbW/rX6KqTs9qL2Huc5/7bGJ0AGB2vrsLYGUzh3fuSPKQJC/o7m9I8ve5\n+VDOJEl3d5LexGt8ke4+u7tP6O4Tdu7cOeppAQAAprWZ0nddkuu6+w3L7ZdnVQL/ejlsM8ufH13u\n/1CSYzc8/phlbW/rAAAAbNIBl77u/kiSa6vqq5elxyZ5Z5ILktx0Bs5Tk7xyuX5BkmcsZ/F8RJJP\nLIeBXpTkxKq623IClxOXNQAAADZpM5/pS5IfSvKSqrp9kvcneVZWRfJlVXVakmuSnLJs+ydJnpjk\nqiSfWrZNd99QVb+U5E3Ldr/Y3Tdsci4AAACyydLX3W9NcsIe7nrsHrbtJM/ey/Ocm+TczcwCAADA\nl9rs9/QBAABwG6b0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAA\nACam9AEAAExM6QMAAJiY0gcAADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAmpvQBAABM\nTOkDAACYmNIHAAAwMaUPAABgYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjS\nBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEAAExM6QMAAJiY0gcAADAxpQ8A\nAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIHAAAwMaUPAABgYkofAADA\nxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATGzHugcAAFZ2nXHhukfYJ1efddK6RwBgP9jTBwAAMDGl\nDwAAYGJKHwAAwMSUPgAAgIkpfQAAABPbdOmrqsOq6i1V9arl9nFV9YaquqqqXlpVt1/W77Dcvmq5\nf9eG5/iZZf09VfW4zc4EAADAyog9fT+S5F0bbj83ya919/2S3JjktGX9tCQ3Luu/tmyXqnpAkqcn\neWCSxyd5flUdNmAuAACAbW9Tpa+qjklyUpLfWW5XksckefmyyXlJnrRcP3m5neX+xy7bn5zk/O7+\ndHd/IMlVSR62mbkAAABY2eyevl9P8lNJPr/cvnuSj3f3Z5fb1yU5erl+dJJrk2S5/xPL9l9Y38Nj\nvkhVnV5Vl1XVZddff/0mRwcAAJjfAZe+qvqWJB/t7ssHznOLuvvs7j6hu0/YuXPnoXpZAACALWvH\nJh77yCTfVlVPTHJEkrsk+Y0kR1bVjmVv3jFJPrRs/6Ekxya5rqp2JLlrko9tWL/JxscAAACwCQe8\np6+7f6a7j+nuXVmdiOW13f1dSV6X5CnLZqcmeeVy/YLldpb7X9vdvaw/fTm753FJjk/yxgOdCwAA\ngJttZk/f3vx0kvOr6jlJ3pLknGX9nCQvrqqrktyQVVFMd19ZVS9L8s4kn03y7O7+3EGYCwAAYNsZ\nUvq6+9Ikly7X3589nH2zu/8xyVP38vhfTvLLI2YBAADgZiO+pw8AAIDbKKUPAABgYkofAADAxJQ+\nAACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAA\nABNT+gAAACam9AEAAExsx7oHuC3ZdcaF6x7hVl191knrHgEAANhC7OkDAACYmNIHAAAwMYd3AnDA\ntsJh8YlD4wHY3uzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEA\nAExM6QMAAJiY0gcAADCxHesegDntOuPCdY+wT64+66R1jwAAAAeVPX0AAAATU/oAAAAmpvQBAABM\nTOkDAACYmNIHAAAwMaUPAABgYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjS\nBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEAAExsx7oHAG7drjMuXPcI++Tq\ns05a9wgAAOzGnj4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIHAAAwsQMufVV1bFW9rqre\nWVVXVtWPLOtHVdXFVfW+5c+7LetVVc+rqquq6u1V9ZANz3Xqsv37qurUzf9YAAAAJJvb0/fZJD/e\n3Q9I8ogkz66qByQ5I8kl3X18kkuW20nyhCTHL5fTk7wgWZXEJGcmeXiShyU586aiCAAAwOYccOnr\n7g9395uX659M8q4kRyc5Ocl5y2bnJXnScv3kJC/qldcnObKq7pXkcUku7u4buvvGJBcnefyBzgUA\nAMDNhnymr6p2JfmGJG9Ics/u/vBy10eS3HO5fnSSazc87LplbW/re3qd06vqsqq67Prrrx8xOgAA\nwNQ2Xfqq6suSvCLJj3b33268r7s7SW/2NTY839ndfUJ3n7Bz585RTwsAADCtTZW+qjo8q8L3ku7+\nw2X5r5fDNrP8+dFl/UNJjt3w8GOWtb2tAwAAsEmbOXtnJTknybu6+z9vuOuCJDedgfPUJK/csP6M\n5Syej0jyieUw0IuSnFhVd1tO4HLisgYAAMAm7djEYx+Z5LuTvKOq3rqs/WySs5K8rKpOS3JNklOW\n+/4kyROTXJXkU0melSTdfUNV/VKSNy3b/WJ337CJuQAAAFgccOnr7v+ZpPZy92P3sH0nefZenuvc\nJOce6CwAAADs2ZCzdwIAAHDbpPQBAABMbDOf6QPYknadceG6R7hVV5910rpHAAAmYU8fAADAxJQ+\nAACAiSl9AAAAE1P6AAAAJqb0AQAATEzpAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAA\nABNT+gAAACam9AEAAExM6QMAAJiY0gcAADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAm\npvQBAABMTOkDAACYmNIHAAAwMaUPAABgYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATEzp\nAwAAmJjSBwAAMDGlDwAAYGJKHwAAwMSUPgAAgIkpfQAAABNT+gAAACam9AEAAExM6QMAAJiY0gcA\nADAxpQ8AAGBiSh8AAMDElD4AAICJKX0AAAATU/oAAAAmpvQBAABMTOkDAACYmNIHAAAwMaUPAABg\nYkofAADAxJQ+AACAiSl9AAAAE1P6AAAAJqb0AQAATOw2U/qq6vFV9Z6quqqqzlj3PAAAADO4TZS+\nqjosyW8leUKSByT5jqp6wHqnAgAA2PpuE6UvycOSXNXd7+/uf0pyfpKT1zwTAADAllfdve4ZUlVP\nSfL47v7e5fZ3J3l4d//b3bY7Pcnpy82vTvKeQzro/rtHkr9Z9xATkedY8hxLnuPIcix5jiXPcWQ5\nljzH2ip5flV377y1jXYciklG6e6zk5y97jn2VVVd1t0nrHuOWchzLHmOJc9xZDmWPMeS5ziyHEue\nY82W523l8M4PJTl2w+1jljUAAAA24bZS+t6U5PiqOq6qbp/k6UkuWPNMAAAAW95t4vDO7v5sVf3b\nJBclOSzJud195ZrHGmHLHIq6RchzLHmOJc9xZDmWPMeS5ziyHEueY02V523iRC4AAAAcHLeVwzsB\nAAA4CJQ+AACAiSl9AAAAE1P6AAAAJnabOHvnTKrqhCT/Ism9k/xDkiuSXNzdN651sC2oqo5I8i35\n0jwvnOTsroeMLMeqqmOy+mqZL8kzyZ929+fXON6WI89xvNfHkudY3utjyXOc7ZCls3cOUlXPSvJD\nST6Q5PIkH01yRJL7J3lkVn9xfq67P7i2IbeQqvqFrP5He2m+NM9HL9d/vLvfvq4ZtwpZjlVVv5vk\n6CSvSnJZvjTPhyY5o7v/fG1DbiHyHMd7fSx5juW9PpY8x9kuWSp9g1TVs7P6fsF/2Mv9X5/k7t19\nyaGdbGuqqpO6+8JbuP8rktynuy87hGNtSbIcq6oe1N1X3ML9t88qz6sO4VhbljzH8V4fS55jea+P\nJc9xtkuWSh/AAaqqo5Kku29Y9ywzkCdsD97rY8lznJmzdCKXQapqR1X9m6p6dVW9fbn8aVV9f1Ud\nvu75tpqqumtVnVVV766qG6rqY1X1rmXtyHXPt5XIcqyquk9VnV9V1yd5Q5I3VtVHl7Vd651u65Hn\nON7rY8lzLO/1seQ5znbJUukb58VJvj7Jzyd54nL5hSQPTvJ76xtry3pZkhuTPKq7j+ruu2d1XPWN\ny33sO1mO9dIkf5TkK7v7+O6+X5J7JfnjJOevdbKtSZ7jeK+PJc+xvNfHkuc42yJLh3cOUlXv7e77\n7+997FlVvae7v3p/7+NLyXKsqnpfdx+/v/exZ/Icx3t9LHmO5b0+ljzH2S5Z2tM3zg1V9dSq+kKm\nVXW7qnpaVr8VZP9cU1U/VVX3vGmhqu5ZVT+d5No1zrUVyXKsy6vq+VX18Kq693J5eFU9P8lb1j3c\nFiTPcbzXx5LnWN7rY8lznG2RpT19gyzH/D43yWNyc8k7MsnrsjrN6wfWM9nWVFV3S3JGkpOTfMWy\n/NdJLkjy3Bk/YHuwyHKsWp3F67Ss8jx6Wb4uyX9Lck53f3pds21F8hzHe30seY7lvT6WPMfZLlkq\nfQdBVd09Sbr7Y+ueBQAA2N4c3nkQdPfHNha+qvrKdc4zm6p6yLpnmIUsx6qqb1n3DDOR5zje62PJ\ncyzv9bHkOc5MWSp9h8Y56x5gMj+w7gEmIsuxvnHdA0xGnuN4r48lz7G818eS5zjTZOnwTgAAgInZ\n0zfQ8uWORy7Xd1XVU6rqQeueayurqhOq6slV9W1V9TXrnmerqtWXDD+tqn5suTzNlwuPV1X/at0z\nbEVVdZequu8e1r9uHfNsZVX1lTd9pKCqdlbVt1fVA9c91yyq6lfWPcMsquq45e+n/7cfgOXfnEcs\n16uqnlVVv1lVP1BVO9Y931ay/BvziHXPcbApfYNU1RlJ/izJ66vqe5O8OskTkry0qn5srcNtQVX1\nL6vqsiRnJTk3yelJzqmqS6vq2PVOt7VU1TOSvDnJo5Lcabk8OqtTFD9jjaPNyKHc+6mqTkny7iSv\nqKorq2rjoTQvXM9UW1NV/Zskf5XV/4d+IMmrkpyU5A+r6rS1DrcFVdXzdrv8ZpIfvOn2uufbaqrq\njzdcPznJa5N8a5JXVtUz1zXXFvYnufnf8Wdl9V5/Q1aHI569rqG2qJcmua6qXlxVT6yqw9Y90MHg\n8M5BqurKJCdk9Q/qq5P8s+6+vqrunOQN3W2P336oqrckOXHJ8Lgk/7m7n7zsSfnJ7j5xzSNuGVX1\nniQP7+6P77Z+t6z+bt5/PZNtTVV1wd7uSvKY7r7zoZxnq6uqtyZ5Qnd/uKoeluRFSX6mu/+oqt7S\n3d+w5hG3jKp6R5KHJ7ljkmuS3K+7P7K811/X3V+/1gG3mKq6Nqtf5r4mq/d3kvynJD+RJN193ppG\n25I2vp+r6i+TfFd3f6Cq7pHkku5+8Hon3Fqq6p3d/YDl+uVJvrG7P7/cfps8993yb87HJHlKkqcn\neVCSP0ryB939Z+ucbSS7f8f5XHf/Q1X9U5J/SPKxJOnuv6+qW34ke3JYd1+/XP9gkq9Kku6+uKp+\nfX1jbUmVZE+/3fl8bv6HDPvuXyT510n+brf1SvKwQz/OlndYd384Sbr7jVX16CSvWvbo+63k/vlM\nd38qyaeq6n9190eSpLtvrCpZ7r8HJPmlJI9P8hPd/b+r6kxl74Bt/Du446bvL+7uv6mqz69ppq3s\n2qp6THe/NqudDccmueamrw1jv3R335jkt5P89nKI/ClJzqqqY7p7iiPMlL5x3lxVv5/kzkkuSXJe\nVb06q98cvHOtk21Nl1XVOVkd/vFtSS5Nkqq6U5Ipd7sfRL+c1d/P1yS5dlm7T5J/ldU/aNg/r0/y\nqT399m/Zq8r++WRV3be7/1eSLHv8HpXkj5P4LNr+6ao6vLs/k9WhXkmS5bMqPs6xn7r7k0l+tKoe\nmuQlVXVh5LgZD66qv83qF2R3qKp7Le/328f/1w/E9yZ5UVX9fJJPJHnrcuTEkUl8rGj/fNEvwJdf\nmD0vyfOq6qvWM9J4Du8cZPnQ7FOz+k3Wy7P6jf93ZrWX6re6++/XON6WU1WHJ/m+rH7T+rYk53b3\n56rqjkm+oruvWeuAW8xyeNfjkhy9LH0oyUXLb7ZgbarqwUn+vruv2m398CSndPdL1jPZ1lNV90ny\nv7v7s7utH53ka7v7v69nsq2vVofs/GCSb+ruf73ueWaynFTsa7v7r9Y9y1ZUVV+b5P5Z7ci5Lsmb\nbjrMk31TVY/q7kvXPcfBpvQdBFV1VJJ09w3rnmUG8gQAgAPnMIVBllPnnl9VH83q7ElvrKqPLmu7\n1jvd1iPPQ2M58QODyHMseY4jy7HkOZY8x5LnODNl6TN947w0ya9ndTaqzyXJcsrXpyY5P8kj1jjb\nViTPQarq2/d2V5KvPJSzzECeY8lzHFmOJc+x5DmWPMfZLlk6vHOQqnpfdx+/v/exZ/Icp6o+k+Ql\n2fOZEJ/S3V9+iEfa0uQ5ljzHkeVY8hxLnmPJc5ztkqXSN0hVnZ/khiTn5eYzJB6b5NQk9+juU9Y1\n21Ykz3GW7+85tbuv2MN9185yKuJDRZ5jyXMcWY4lz7HkOZY8x9kuWTq8c5xnJDktyS/ki8+QeEGS\nc9Y11BYmz3F+NMnf7uW+Jx/KQSYhz7HkOY4sx5LnWPIcS57jbIss7ekDAACYmD19gyzf03dakifl\ni/dMvTLJOcuX5bKP5DnOhiyfnOTey7IsD5A8x5LnOLIcS55jyXMseY6zXbK0p2+QqvqDJB/P6jNo\n1y3Lx2T1GbSjuvtp65ptK5LnOLIcS55jyXMcWY4lz7HkOZY8x9kuWSp9g1TVe7v7/vt7H3smz3Fk\nOZY8x5LnOLIcS55jyXMseY6zXbL05ezj3FBVT62qL2RaVberqqcluXGNc21V8hxHlmPJcyx5jiPL\nseQ5ljzHkuc42yJLpW+cpyd5SpK/rqr3VtV7k3wkybcv97F/5DmOLMeS51jyHEeWY8lzLHmOJc9x\ntkWWDu88CKrq7knS3R9b9ywzkOc4shxLnmPJcxxZjiXPseQ5ljzHmTlLe/oGqqq7VNV9u/tjG/+y\nVNXXrXOurUqe48hyLHmOJc9xZDmWPMeS51jyHGc7ZKn0DVJVpyR5d5JXVNWVVfWNG+5+4Xqm2rrk\nOY4sx5LnWPIcR5ZjyXMseY4lz3G2S5ZK3zg/m+Sh3f31SZ6V5MVV9eTlvlrfWFuWPMeR5VjyHEue\n48hyLHmOJc+x5DnOtsjSl7OPc1h3fzhJuvuNVfXoJK+qqmOT+ODk/pPnOLIcS55jyXMcWY4lz7Hk\nOZY8x9kWWdrTN84nq+q+N91Y/vI8KsnJSR64rqG2MHmOI8ux5DmWPMeR5VjyHEueY8lznG2RpT19\n4/xAdtsF3N2frKrHJzllPSNtafIcR5ZjyXMseY4jy7HkOZY8x5LnONsiS1/ZAAAAMDGHdwIAAExM\n6QMAAJiY0gcAADAxpe8gq6rzquoFVfWgdc8yA3mOI8ux5DmWPMeR5VjyHEueY8lznNmydCKXg6yq\nvjHJfZI8rLt/et3zbHXyHEeWY8lzLHmOI8ux5DmWPMeS5zizZWlP38H3jiS3m+Evy22EPMeR5Vjy\nHEue48hZZo99AAAJhElEQVRyLHmOJc+x5DnOVFkqfQdBVR1WVU+sqhcnuSbJ09Y901Ymz3FkOZY8\nx5LnOLIcS55jyXMseY4zc5a+nH2gqvqXSb4zyROTvDHJI5Mc192fWutgW5Q8x5HlWPIcS57jyHIs\neY4lz7HkOc52yNJn+gapquuSfDDJC5L8cXd/sqo+0N3HrXm0LUme48hyLHmOJc9xZDmWPMeS51jy\nHGe7ZOnwznFenuTeWe0G/taqunMSjfrAyXMcWY4lz7HkOY4sx5LnWPIcS57jbIss7ekbqKoqyaOS\nfEdWu4fvmuS0JH/S3X+3xtG2JHmOI8ux5DmWPMeR5VjyHEueY8lznO2QpdJ3kFTV4Ukel9Vfnsd1\n9z3WPNKWJs9xZDmWPMeS5ziyHEueY8lzLHmOM2uWSt8gVbUzyc7ufudu6w9M8snu/uB6Jtua5DmO\nLMeS51jyHEeWY8lzLHmOJc9xtkuWPtM3zm8m2dNvAo5KctYhnmUG8hxHlmPJcyx5jiPLseQ5ljzH\nkuc42yJLe/oGqarLuvuEvdx3RXc/6FDPtJXJcxxZjiXPseQ5jizHkudY8hxLnuNslyzt6Rvny2/h\nvsMP2RTzkOc4shxLnmPJcxxZjiXPseQ5ljzH2RZZKn3jXFVVT9x9saqekOT9a5hnq5PnOLIcS55j\nyXMcWY4lz7HkOZY8x9kWWTq8c5Cqun+SVyX5yySXL8snJPmmJN/S3e9d12xbkTzHkeVY8hxLnuPI\ncix5jiXPseQ5znbJUukbqKrukOQ7k9x07O+VSX6/u/9xfVNtXfIcR5ZjyXMseY4jy7HkOZY8x5Ln\nONshS6VvkKqqvpUw92UbVuQ5jizHkudY8hxHlmPJcyx5jiXPcbZLlj7TN87rquqHquo+Gxer6vZV\n9ZiqOi/JqWuabSuS5ziyHEueY8lzHFmOJc+x5DmWPMfZFlna0zdIVR2R5HuSfFeS45J8PMkdsyrW\nr0ny/O5+y/om3FrkOY4sx5LnWPIcR5ZjyXMseY4lz3G2S5ZK30FQVYdn9SWP/9DdH1/3PFudPMeR\n5VjyHEue48hyLHmOJc+x5DnOzFkqfQAAABPzmT4AAICJKX0AAAATU/oAAAAmpvQBsF+q6ker6k6j\nthupqn52t9t/ueH6r1bVlcuf319VzziA5z+yqn5ww+17V9XLNzf1Pr3uM6vq3gfwuCdV1QMOxkwA\nbB1O5ALAfqmqq5Oc0N1/M2K7QTNVkkryt939ZXvZ5hNJjuruz23idXYleVV3P+hAn+MAX/fSJD/R\n3Zftx2N2JPmdrOY96MV099fu7s8eytcEYO/s6QNgr6rqzlV1YVW9raquqKozk9w7qy+zfd2yzQuq\n6rJlL9ovLGs/vIftTqyqv6qqN1fVf62qLylnVXW/qvrvy+u9uaruu6z/ZFW9qarevuE1dlXVe6rq\nRUmuSHJOkjtW1Vur6iXLNn+3/HlBki9LcnlVPa2qfr6qfmJvr1lVX1ZVlyy331FVJy8jnpXkvstr\n/OoywxXL8xxRVb+7bP+Wqnr0sv7MqvrDqnp1Vb2vqv7jLeR9WFW9cMn6HVX176rqKUlOSPKS5XXv\nWFX/fsnjiqo6eym9qapLq+rXq+qyJD+d5NuS/OryuPvu4fXuW1Vv3nD7+JtuV9VDq+rPquryqrqo\nqu61rH/f8tpvq6pX3LQ3d5n7v1TVG5Ls9WcEYA2628XFxcXFZY+XJP93kt/ecPuuSa5Oco8Na0ct\nfx6W5NIkX7fc/sJ2WX3v0Z8nufNy+6eT/Ps9vN4bkjx5uX5EkjslOTHJ2Vntybtdklcl+eYku5J8\nPskjNjz+73Z7vr/by/Wfz2rP2d5ec0eSu2yY/arl9XcluWLD83zhdpIfT3Lucv1rknxweb5nJnn/\nkt0RSa5Jcuxe8n5okos33D5y+fPSrPaaflHmy/UXJ/nWDds9f8N9L0zylFv5b/y6JF+/XP+VJD+U\n5PAkf5lk57L+tA0/2903PPY5SX5ow2u9Kslh6/576+Li4uLyxZcdAYC9e0eS/7eqnpvVYYL/Y9mp\ntNEpVXV6VkXpXkkekOTtu23ziGX9L5bH3z7JX23coKq+PMnR3f1HSdLd/7isn5hV8XvLsumXJTk+\nq1J1TXe//kB/uFt4zcOT/EpVfXNWxfLoJPe8laf7P5P85vI8766qa5Lcf7nvku7+xPLc70zyVUmu\n3cNzvD/JP6uq30xyYZLX7OW1Hl1VP5VVQT0qyZVJ/tty30tvZc7d/U6SZ1XVj2VV7h6W5KuTPCjJ\nxct/r8OSfHjZ/kFV9ZwkR2b13+KiDc/1X3sTh88CcHAofQDsVXe/t6oekuSJSZ5TVZdsvL+qjkvy\nE0m+sbtvrKoXZrU3a3eV1R6s7ziAMSrJf+ju/2+3196V5O8P4Pn2xXcl2Znkod39meXziXv6ufbV\npzdc/1z28v/fJcMHJ3lcku9PckqS79m4TVUdkeT5We35u7aqfn632fY3k1ckOTPJa5Nc3t0fq9VJ\nY67s7m/aw/YvTPKk7n5bVT0zyaM28doAHAI+0wfAXi3/+P9Ud/9ekl9N8pAkn0zy5csmd8nqH/qf\nqKp7JnnChodv3O71SR5ZVfdbnvfOVXX/5fp/qKond/cnk1xXVU9a1u+wfF7soiTfc9NnAKvq6Kr6\nir2M/JllL90+uYXXvGuSjy6F79FZ7Znb/Wfa3f/Iqixm+dnuk+Q9+zrL8rh7JLldd78iyf+TVd67\nv+5NBe9vlkyecgtPeUvzJvnC3s2Lkrwgye8uy+9JsrOqvmmZ6/CqeuBy35cn+fCS83ft688GwPoo\nfQDckn+e5I1V9das9gY9J6vP1726ql7X3W/L6rDLdyf5/SR/seGxG7e7PqvPtv1BVb09q0M7v2bD\na3xkuf7dSX542eYvk3xld79mee6/qqp3JHl59l5kzk7y9ptO5LKPvuQ1k7wkyQnL6z1j+fnS3R/L\n6hDVK6rqV3d7nucnud3ymJcmeWZ3fzr75+gkly55/16Sn1nWX5jkvyzrn07y21mdvOaiJG+6hec7\nP8lPLieW+ZITuWzwkqwOY31NknT3P2VVJp9bVW9L8tYk/8ey7c9l9TnIv8iSCwC3bb6yAYC1qqqL\nuvtx655jO6vVmUzv2t0/t+5ZABhP6QOAbayq/ijJfZM8pg/BdyoCcOg5kQsArMHyfXZ32G35u7v7\nHQfp9X4rySN3W/6N7n7ywXg9AG477OkDAACYmBO5AAAATEzpAwAAmJjSBwAAMDGlDwAAYGL/P/RV\n9xTQ8tCNAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7ff473e32bd0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ca_totals.workers_certified.plot.bar(figsize=(15, 7))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>workers_certified</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th>certification_start_year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"10\" valign=\"top\">CA</th>\n",
       "      <th>2008.0</th>\n",
       "      <td>3353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009.0</th>\n",
       "      <td>2941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010.0</th>\n",
       "      <td>2298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011.0</th>\n",
       "      <td>1774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>3249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>4291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>6011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>8529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>11131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017.0</th>\n",
       "      <td>6230</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                workers_certified\n",
       "state certification_start_year                   \n",
       "CA    2008.0                                 3353\n",
       "      2009.0                                 2941\n",
       "      2010.0                                 2298\n",
       "      2011.0                                 1774\n",
       "      2012.0                                 3249\n",
       "      2013.0                                 4291\n",
       "      2014.0                                 6011\n",
       "      2015.0                                 8529\n",
       "      2016.0                                11131\n",
       "      2017.0                                 6230"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ca_totals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trim down to the last five years of data and calculate California's percentage change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_last_five = ca_totals[\n",
    "    (ca_totals.index.get_level_values(1) > 2010) &\n",
    "    (ca_totals.index.get_level_values(1) < 2017)\n",
    "].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_pct_change = ca_last_five[[\n",
    "  'certification_start_year',\n",
    "  'workers_certified'\n",
    "]].set_index('certification_start_year').pct_change(\n",
    "    len(ca_last_five)-1\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percent change: 527.45%\n"
     ]
    }
   ],
   "source": [
    "print \"Percent change: %s%%\" % round(ca_pct_change.at[2016.0, 'workers_certified']*100, 2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output for a graphic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_totals.reset_index()[['certification_start_year', 'workers_certified']].to_csv(\n",
    "    \"./output/california-totals-graphics.csv\",\n",
    "    index=False\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: If this year’s hiring pace holds, that number will soar even higher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert the start date column to a datetime object"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.certification_start_date = pd.to_datetime(df.certification_start_date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pull out the first four months of this year versus last year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "first_four_16 = df[\n",
    "    (df.state == 'CA') &\n",
    "    (df.certification_start_date >= date(2016, 1, 1)) & \n",
    "    (df.certification_start_date < date(2016, 5, 1))\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "first_four_17 = df[\n",
    "    (df.state == 'CA') &\n",
    "    (df.certification_start_date >= date(2017, 1, 1)) & \n",
    "    (df.certification_start_date < date(2017, 5, 1))\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compare the total hires in that period"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4838"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_four_16.workers_certified.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6115"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_four_17.workers_certified.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: Counties on the Central Coast, from Ventura up to Santa Cruz, are driving the growth"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in all \"sub\" cases rather than the master cases. This allows for the farms where workers are actually employed to be mapped, rather than the the \"master cases\" of middlemen who sometimes file the \"master\" applications. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_df = pd.read_csv(os.path.join(output_dir, \"geocoded_all_cases.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert to a geodataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def create_point(row):\n",
    "    if row.lng and row.lat:\n",
    "        return Point(row.lng, row.lat)\n",
    "    return ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_df['geometry'] = combined_df.apply(create_point, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gdf = gp.GeoDataFrame(combined_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "gdf.crs = {'init' :'epsg:4269'}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exclude cases that could not be mapped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "valid_gdf = gdf[~gdf.lat.isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Spatial join with county polygons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "counties = gp.read_file(os.path.join(input_dir, \"cb_2016_us_county_5m.shp\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "gdf_with_county = gp.sjoin(counties, valid_gdf, how=\"inner\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter to California"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_gdf = gdf_with_county[gdf_with_county.STATEFP == '06']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a crosstab of the total number of workers by year in all California counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "county_crosstab = ca_gdf.groupby([\n",
    "    'NAME',\n",
    "    'certification_start_year'\n",
    "]).net_workers.sum().reset_index().set_index([\n",
    "    \"NAME\",\n",
    "    \"certification_start_year\"\n",
    "]).unstack(1).fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Strip all the pandas chrome off the crosstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "county_crosstab = county_crosstab.reset_index()\n",
    "county_crosstab.columns = county_crosstab.columns.droplevel(0)\n",
    "county_crosstab = county_crosstab.rename_axis(None, axis=1)\n",
    "county_crosstab = county_crosstab.set_index(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate the change in each county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "county_crosstab['change_11to16'] = county_crosstab.apply(\n",
    "    lambda x: x[2016.0] - x[2011.0],\n",
    "    axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rank them by their change"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2008.0</th>\n",
       "      <th>2009.0</th>\n",
       "      <th>2010.0</th>\n",
       "      <th>2011.0</th>\n",
       "      <th>2012.0</th>\n",
       "      <th>2013.0</th>\n",
       "      <th>2014.0</th>\n",
       "      <th>2015.0</th>\n",
       "      <th>2016.0</th>\n",
       "      <th>2017.0</th>\n",
       "      <th>change_11to16</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Monterey</th>\n",
       "      <td>396.0</td>\n",
       "      <td>806.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>266.0</td>\n",
       "      <td>366.0</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>2318.0</td>\n",
       "      <td>1947.0</td>\n",
       "      <td>2318.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Santa Barbara</th>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>909.0</td>\n",
       "      <td>1313.0</td>\n",
       "      <td>1450.0</td>\n",
       "      <td>1201.0</td>\n",
       "      <td>1450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ventura</th>\n",
       "      <td>37.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>255.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>849.0</td>\n",
       "      <td>991.0</td>\n",
       "      <td>806.0</td>\n",
       "      <td>991.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Santa Cruz</th>\n",
       "      <td>102.0</td>\n",
       "      <td>179.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>267.0</td>\n",
       "      <td>916.0</td>\n",
       "      <td>882.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>743.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>San Luis Obispo</th>\n",
       "      <td>11.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>83.0</td>\n",
       "      <td>286.0</td>\n",
       "      <td>639.0</td>\n",
       "      <td>285.0</td>\n",
       "      <td>627.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>San Diego</th>\n",
       "      <td>646.0</td>\n",
       "      <td>526.0</td>\n",
       "      <td>625.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>485.0</td>\n",
       "      <td>646.0</td>\n",
       "      <td>836.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>569.0</td>\n",
       "      <td>186.0</td>\n",
       "      <td>568.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Imperial</th>\n",
       "      <td>566.0</td>\n",
       "      <td>374.0</td>\n",
       "      <td>396.0</td>\n",
       "      <td>556.0</td>\n",
       "      <td>663.0</td>\n",
       "      <td>851.0</td>\n",
       "      <td>985.0</td>\n",
       "      <td>868.0</td>\n",
       "      <td>1069.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>513.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Santa Clara</th>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>310.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>310.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Siskiyou</th>\n",
       "      <td>730.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>495.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>631.0</td>\n",
       "      <td>769.0</td>\n",
       "      <td>772.0</td>\n",
       "      <td>803.0</td>\n",
       "      <td>146.0</td>\n",
       "      <td>308.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fresno</th>\n",
       "      <td>80.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>286.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>254.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 2008.0  2009.0  2010.0  2011.0  2012.0  2013.0  2014.0  \\\n",
       "                                                                          \n",
       "Monterey          396.0   806.0    80.0     0.0   204.0   266.0   366.0   \n",
       "Santa Barbara       0.0     2.0     0.0     0.0     0.0   158.0   909.0   \n",
       "Ventura            37.0     1.0     0.0     0.0    39.0   255.0   354.0   \n",
       "Santa Cruz        102.0   179.0   120.0   139.0     3.0     0.0   267.0   \n",
       "San Luis Obispo    11.0    14.0     7.0    12.0     6.0    52.0    83.0   \n",
       "San Diego         646.0   526.0   625.0     1.0   485.0   646.0   836.0   \n",
       "Imperial          566.0   374.0   396.0   556.0   663.0   851.0   985.0   \n",
       "Santa Clara         1.0     5.0     0.0     0.0     0.0     0.0     0.0   \n",
       "Siskiyou          730.0     0.0     0.0   495.0   565.0   631.0   769.0   \n",
       "Fresno             80.0    59.0    17.0    32.0    36.0     5.0    35.0   \n",
       "\n",
       "                 2015.0  2016.0  2017.0  change_11to16  \n",
       "                                                        \n",
       "Monterey         1023.0  2318.0  1947.0         2318.0  \n",
       "Santa Barbara    1313.0  1450.0  1201.0         1450.0  \n",
       "Ventura           849.0   991.0   806.0          991.0  \n",
       "Santa Cruz        916.0   882.0   273.0          743.0  \n",
       "San Luis Obispo   286.0   639.0   285.0          627.0  \n",
       "San Diego         660.0   569.0   186.0          568.0  \n",
       "Imperial          868.0  1069.0   233.0          513.0  \n",
       "Santa Clara       120.0   310.0    12.0          310.0  \n",
       "Siskiyou          772.0   803.0   146.0          308.0  \n",
       "Fresno            208.0   286.0    34.0          254.0  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_crosstab.sort_values(\"change_11to16\", ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output that for a graphic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "county_crosstab.to_csv(os.path.join(output_dir, \"county-crosstab.csv\"), encoding=\"utf-8\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Total up the workers by city for a map"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_location_totals = ca_gdf.groupby([\n",
    "    \"certification_start_year\",\n",
    "    \"geocoder_address\",\n",
    "    \"lat\",\n",
    "    'lng',\n",
    "]).net_workers.sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_location_totals['certification_start_year'] = ca_location_totals.certification_start_year.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_location_totals['net_workers'] = ca_location_totals.net_workers.astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_location_totals.columns = [\n",
    "    'year',\n",
    "    'address',\n",
    "    'lat',\n",
    "    'lng',\n",
    "    'workers'\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Output that for a graphic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_location_totals.to_csv(os.path.join(output_dir, \"ca_totals_by_location.csv\"), index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: Strawberries and lettuce crops have accounted for most of the new workers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Regroup subcases in the state by our cleaned up version of the crop column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ca_crops = ca_gdf.groupby([\n",
    "    \"certification_start_year\",\n",
    "    \"latimes_crop\"\n",
    "]).net_workers.sum().reset_index().sort_values(\"net_workers\", ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a crosstab by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "crops_crosstab = ca_crops.set_index([\n",
    "    \"certification_start_year\",\n",
    "    \"latimes_crop\"\n",
    "]).unstack(0).fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, strip the pandas chrome from the crosstab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "crops_crosstab = crops_crosstab.reset_index()\n",
    "crops_crosstab.columns = crops_crosstab.columns.droplevel(0)\n",
    "crops_crosstab = crops_crosstab.rename_axis(None, axis=1)\n",
    "crops_crosstab = crops_crosstab.set_index(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Calculate the change over the past five years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "crops_crosstab['change_11to16'] = crops_crosstab.apply(\n",
    "    lambda x: x[2016.0] - x[2011.0],\n",
    "    axis=1\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Rank the crops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2010.0</th>\n",
       "      <th>2011.0</th>\n",
       "      <th>2012.0</th>\n",
       "      <th>2014.0</th>\n",
       "      <th>2015.0</th>\n",
       "      <th>2016.0</th>\n",
       "      <th>2017.0</th>\n",
       "      <th>change_11to16</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Strawberries</th>\n",
       "      <td>0.0</td>\n",
       "      <td>830.0</td>\n",
       "      <td>660.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>953.0</td>\n",
       "      <td>3695.0</td>\n",
       "      <td>1620.0</td>\n",
       "      <td>2865.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lettuce</th>\n",
       "      <td>0.0</td>\n",
       "      <td>532.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1210.0</td>\n",
       "      <td>2052.0</td>\n",
       "      <td>2201.0</td>\n",
       "      <td>1520.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broccoli</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>748.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>748.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Raspberries</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>531.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>531.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tomatoes</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>350.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>475.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>475.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Onions</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>380.0</td>\n",
       "      <td>222.0</td>\n",
       "      <td>380.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Celery</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>220.0</td>\n",
       "      <td>364.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>364.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Berries</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>341.0</td>\n",
       "      <td>310.0</td>\n",
       "      <td>341.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sheep</th>\n",
       "      <td>8.0</td>\n",
       "      <td>370.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>204.0</td>\n",
       "      <td>723.0</td>\n",
       "      <td>662.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>292.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grapes</th>\n",
       "      <td>0.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>59.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>511.0</td>\n",
       "      <td>216.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              2010.0  2011.0  2012.0  2014.0  2015.0  2016.0  2017.0  \\\n",
       "                                                                       \n",
       "Strawberries     0.0   830.0   660.0     0.0   953.0  3695.0  1620.0   \n",
       "Lettuce          0.0   532.0    45.0     0.0  1210.0  2052.0  2201.0   \n",
       "Broccoli         0.0     0.0     0.0     0.0     0.0   748.0    12.0   \n",
       "Raspberries      0.0     0.0     0.0     0.0     0.0   531.0     0.0   \n",
       "Tomatoes         0.0     0.0   350.0     0.0    49.0   475.0    80.0   \n",
       "Onions           0.0     0.0     0.0     0.0     0.0   380.0   222.0   \n",
       "Celery           0.0     0.0     0.0     0.0   220.0   364.0    61.0   \n",
       "Berries          0.0     0.0     0.0     0.0    60.0   341.0   310.0   \n",
       "Sheep            8.0   370.0   260.0   204.0   723.0   662.0   148.0   \n",
       "Grapes           0.0    30.0    59.0     0.0    34.0   246.0   511.0   \n",
       "\n",
       "              change_11to16  \n",
       "                             \n",
       "Strawberries         2865.0  \n",
       "Lettuce              1520.0  \n",
       "Broccoli              748.0  \n",
       "Raspberries           531.0  \n",
       "Tomatoes              475.0  \n",
       "Onions                380.0  \n",
       "Celery                364.0  \n",
       "Berries               341.0  \n",
       "Sheep                 292.0  \n",
       "Grapes                216.0  "
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "crops_crosstab.sort_values(\"change_11to16\", ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ouput for a graphic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "crops_crosstab.to_csv(os.path.join(output_dir, \"crops-crosstab.csv\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: The Santa Maria Valley, straddling San Louis Obispo and Santa Barbara counties, leapt from six sheepherders in 2012 to more than 2,000 guest farm workers last year"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter down to subcases in those two counties"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "smvalley = gdf_with_county[gdf_with_county.NAME.isin(['Santa Barbara', 'San Luis Obispo'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Count the total number of workers there by year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "smvalley_crosstab = smvalley.groupby([\n",
    "    'certification_start_year'\n",
    "]).net_workers.sum().reset_index().set_index(\"certification_start_year\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ouput the totals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7ff467824e10>"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYEAAAEkCAYAAADJiI15AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3XucVXW9//HXGyTvF9TRTLAhD1aQOcakFmCWHQMtyR5p\n+OsoekoyNevX8YKe3+8hP0/2s7TLz/JYmJicTPOKVKSSeSvFBAVEUQEddYiAwLwkerx8fn+s7+B2\nnBlmZu9ZezHr/Xw89mPW/q7be/ae2Z+91nddFBGYmVk5Dah3ADMzqx8XATOzEnMRMDMrMRcBM7MS\ncxEwMysxFwEzsxJzETAzKzEXATOzEnMRMDMrMRcBM7MS26zeATZm5513jsbGxnrHMDPbZMyfP/9v\nEdHQnWkLXwQaGxuZN29evWOYmW0yJD3V3Wm9O8jMrMRcBMzMSsxFwMysxArfJ9CRV199ldbWVl5+\n+eV6RymdLbbYgiFDhjBo0KB6RzGzGtgki0BrayvbbrstjY2NSKp3nNKICNauXUtrayvDhg2rdxwz\nq4FNcnfQyy+/zE477eQCkDNJ7LTTTt4CM+tHNskiALgA1Ilfd7P+ZZMtAmZmVr1Nsk+gvcYpv63p\n8lrOP6ymy5s5cyZ77bUXI0aMqOlyOzJ16lS22WYbTjvttD5fl9mmphafFbX+fKg3bwnkYObMmTzy\nyCN9vp7XXnutrvOb2abHRaAXWlpaeP/7388JJ5zAyJEjOeSQQ1i/fj3Lly9n3LhxjBo1irFjx/Lo\no49yzz33MGvWLE4//XSamppYvnz525a3evVqRo0aBcDChQuRxNNPPw3AnnvuyUsvvURLSwuf+MQn\n+OAHP8jBBx+8Yfxxxx3HiSeeyP77788ZZ5zxluVeeumljB8/vtNsHc1/55130tTURFNTE/vuuy8v\nvPBCX76UZlZn/WJ3UD0sXbqUq666iksvvZSjjjqK66+/nssvv5yf/OQnDB8+nPvuu4+TTjqJP/zh\nDxx++OF8+tOf5vOf/3yHy9pll114+eWXef7557n77rtpbm7m7rvvZsyYMeyyyy5stdVWfO1rX2PS\npElMmjSJ6dOnc+qppzJz5kwgO2T2nnvuYeDAgUydOhWAH//4x8yZM4eZM2ey+eabM3ny5A6ztZ//\nM5/5DBdffDGjR4/mxRdfZIsttsjl9TSz+nAR6KVhw4bR1NQEwKhRo2hpaeGee+7hyCOP3DDNK6+8\n0u3lffSjH+VPf/oTd911F2effTY333wzEcHYsWMBuPfee7nhhhsAOOaYY97yrf/II49k4MCBG57P\nmDGDoUOHMnPmTAYNGsSLL77YZbbK+UePHs03v/lNvvjFL/K5z32OIUOG9ORlMbNNzEZ3B0kaKul2\nSY9IeljS11P7jpLmSFqafg5O7ZJ0kaRlkhZJ+lDFsial6ZdKmtR3v1bf23zzzTcMDxw4kHXr1rHD\nDjuwYMGCDY8lS5Z0e3kHHnggd999N0899RQTJkxg4cKF/PGPf9xQBLqy9dZbv+X53nvvTUtLC62t\nrQC88cYbXWarnH/KlCn87Gc/Y/369YwePXrDbiMz65+60yfwGvBvETECOAA4WdIIYApwW0QMB25L\nzwHGA8PTYzJwCWRFAzgH2B/YDzinrXD0B9tttx3Dhg3j2muvBbKzaxcuXAjAtttuu9F962PHjuUX\nv/gFw4cPZ8CAAey4447Mnj2bMWPGANmWwtVXXw3AlVde2WVx2HffffnpT3/K4Ycfzl/+8pcus7W3\nfPly9t57b84880w+/OEPuwiY9XMb3R0UESuBlWn4BUlLgN2BCcBBabIrgDuAM1P7jIgIYK6kHSTt\nlqadExHrACTNAcYBV1X7SxTlkK0rr7ySr371q3zrW9/i1VdfZeLEieyzzz5MnDiRE044gYsuuojr\nrruOPffc823zNjY2EhEceOCBAIwZM4bW1lYGD87q5I9+9COOP/54LrjgAhoaGrj88su7zDJmzBgu\nvPBCDjvsMObMmdNptvZ++MMfcvvttzNgwABGjhzJ+PHja/DKmFlRKfus7ubEUiNwF/AB4OmI2CG1\nC3g2InaQ9Bvg/Ij4Yxp3G1lxOAjYIiK+ldr/N7A+Ii7sap3Nzc3R/qYyS5Ys4f3vf3+3c1tt+fW3\nTVVZzhOQND8imrszbbcPEZW0DXA98I2IeL5yXPrW3/1qsvF1TZY0T9K8NWvW1GqxZmbWTreKgKRB\nZAXgyoi4ITWvSrt5SD9Xp/YVwNCK2Yekts7a3yYipkVEc0Q0NzR06zaZm4yTTz55w3H4bY+N7dox\nM+srG+0TSLt6LgOWRMT3K0bNAiYB56efN1W0nyLparJO4OciYqWkW4BvV3QGHwKc1dvgEbFJXszs\n4osvrneEqvRk96GZFV93zhMYDRwDPCRpQWo7m+zD/xpJXwKeAo5K42YDhwLLgJeA4wEiYp2k/wDu\nT9Od29ZJ3FNbbLEFa9eu9eWkc9Z2PwGfQGbWf3Tn6KA/Ap190h7cwfQBnNzJsqYD03sSsCNDhgyh\ntbUV9xfkr+3OYmbWP2ySZwwPGjTId7YyM6sBX0DOzKzEXATMzErMRcDMrMRcBMzMSsxFwMysxFwE\nzMxKzEXAzKzEXATMzErMRcDMrMRcBMzMSsxFwMysxFwEzMxKzEXAzKzEXATMzErMRcDMrMQ2WgQk\nTZe0WtLiirZfSVqQHi1tdxyT1ChpfcW4n1TMM0rSQ5KWSbpIviWYmVnddeemMj8HfgzMaGuIiC+0\nDUv6HvBcxfTLI6Kpg+VcApwA3Ed2C8pxwO96HtnMzGplo1sCEXEX0OG9gNO3+aOAq7pahqTdgO0i\nYm66/eQM4LM9j2tmZrVUbZ/AWGBVRCytaBsm6UFJd0oam9p2B1orpmlNbWZmVkfV3mP4aN66FbAS\n2CMi1koaBcyUNLKnC5U0GZgMsMcee1QZ0czMOtPrLQFJmwGfA37V1hYRr0TE2jQ8H1gO7AWsAIZU\nzD4ktXUoIqZFRHNENDc0NPQ2opmZbUQ1u4M+CTwaERt280hqkDQwDb8HGA48ERErgeclHZD6EY4F\nbqpi3WZmVgPdOUT0KuBe4L2SWiV9KY2ayNs7hA8EFqVDRq8DToyItk7lk4CfAcvIthB8ZJCZWZ1t\ntE8gIo7upP24DtquB67vZPp5wAd6mM/MzPqQzxg2MysxFwEzsxJzETAzKzEXATOzEnMRMDMrMRcB\nM7MScxEwMysxFwEzsxJzETAzKzEXATOzEqv2UtJmZhvVOOW3VS+j5fzDapDE2vOWgJlZibkImJmV\nmIuAmVmJuQiYmZVYd24qM13SakmLK9qmSlohaUF6HFox7ixJyyQ9JulTFe3jUtsySVNq/6uYmVlP\ndWdL4OfAuA7afxARTekxG0DSCLI7jo1M8/ynpIHplpMXA+OBEcDRaVozM6uj7txZ7C5Jjd1c3gTg\n6oh4BXhS0jJgvzRuWUQ8ASDp6jTtIz1ObGZmNVNNn8Apkhal3UWDU9vuwDMV07Smts7azcysjnpb\nBC4B9gSagJXA92qWCJA0WdI8SfPWrFlTy0WbmVmFXhWBiFgVEa9HxBvApby5y2cFMLRi0iGprbP2\nzpY/LSKaI6K5oaGhNxHNzKwbenXZCEm7RcTK9PQIoO3IoVnALyV9H3gXMBz4MyBguKRhZB/+E4H/\nUU1wM7NNUdEuobHRIiDpKuAgYGdJrcA5wEGSmoAAWoCvAETEw5KuIevwfQ04OSJeT8s5BbgFGAhM\nj4iHa/ZbmJlZr3Tn6KCjO2i+rIvpzwPO66B9NjC7R+nMzKxP+YxhM7MScxEwMysxFwEzsxJzETAz\nKzEXATOzEnMRMDMrMRcBM7MScxEwMysxFwEzsxJzETAzKzEXATOzEnMRMDMrMRcBM7MScxEwMysx\nFwEzsxJzETAzK7GNFgFJ0yWtlrS4ou0CSY9KWiTpRkk7pPZGSeslLUiPn1TMM0rSQ5KWSbpIkvrm\nVzIzs+7qzpbAz4Fx7drmAB+IiA8CjwNnVYxbHhFN6XFiRfslwAlk9x0e3sEyzcwsZxstAhFxF7Cu\nXdutEfFaejoXGNLVMiTtBmwXEXMjIoAZwGd7F9nMzGqlFn0C/wr8ruL5MEkPSrpT0tjUtjvQWjFN\na2ozM7M62uiN5rsi6d+B14ArU9NKYI+IWCtpFDBT0sheLHcyMBlgjz32qCaimZl1oddbApKOAz4N\nfDHt4iEiXomItWl4PrAc2AtYwVt3GQ1JbR2KiGkR0RwRzQ0NDb2NaGZmG9GrIiBpHHAGcHhEvFTR\n3iBpYBp+D1kH8BMRsRJ4XtIB6aigY4Gbqk5vZmZV2ejuIElXAQcBO0tqBc4hOxpoc2BOOtJzbjoS\n6EDgXEmvAm8AJ0ZEW6fySWRHGm1J1odQ2Y9gZmZ1sNEiEBFHd9B8WSfTXg9c38m4ecAHepTOzMz6\nlM8YNjMrMRcBM7MScxEwMysxFwEzsxJzETAzKzEXATOzEnMRMDMrMRcBM7MScxEwMysxFwEzsxJz\nETAzKzEXATOzEnMRMDMrMRcBM7MScxEwMysxFwEzsxLrVhGQNF3SakmLK9p2lDRH0tL0c3Bql6SL\nJC2TtEjShyrmmZSmXyppUu1/HTMz64nubgn8HBjXrm0KcFtEDAduS88BxpPdW3g4MBm4BLKiQXZr\nyv2B/YBz2gqHmZnVR7eKQETcBaxr1zwBuCINXwF8tqJ9RmTmAjtI2g34FDAnItZFxLPAHN5eWMzM\nLEfV9AnsGhEr0/BfgV3T8O7AMxXTtaa2ztrNzKxOatIxHBEBRC2WBSBpsqR5kuatWbOmVos1M7N2\nNqti3lWSdouIlWl3z+rUvgIYWjHdkNS2AjioXfsdHS04IqYB0wCam5trVlzMyqhxym+rXkbL+YfV\nIIkVUTVbArOAtiN8JgE3VbQfm44SOgB4Lu02ugU4RNLg1CF8SGozM7M66daWgKSryL7F7yyplewo\nn/OBayR9CXgKOCpNPhs4FFgGvAQcDxAR6yT9B3B/mu7ciGjf2WxmZjnqVhGIiKM7GXVwB9MGcHIn\ny5kOTO92OjMz61M+Y9jMrMRcBMzMSsxFwMysxFwEzMxKzEXAzKzEXATMzErMRcDMrMRcBMzMSsxF\nwMysxFwEzMxKzEXAzKzEXATMzErMRcDMrMRcBMzMSsxFwMysxFwEzMxKrNdFQNJ7JS2oeDwv6RuS\npkpaUdF+aMU8Z0laJukxSZ+qza9gZma91esbzUfEY0ATgKSBZDeSv5HsdpI/iIgLK6eXNAKYCIwE\n3gX8XtJeEfF6bzOYmVl1arU76GBgeUQ81cU0E4CrI+KViHiS7B7E+9Vo/WZm1gu1KgITgasqnp8i\naZGk6ZIGp7bdgWcqpmlNbWZmVidVFwFJ7wAOB65NTZcAe5LtKloJfK8Xy5wsaZ6keWvWrKk2opmZ\ndaIWWwLjgQciYhVARKyKiNcj4g3gUt7c5bMCGFox35DU9jYRMS0imiOiuaGhoQYRzcysI7UoAkdT\nsStI0m4V444AFqfhWcBESZtLGgYMB/5cg/WbmVkv9froIABJWwP/DHylovm7kpqAAFraxkXEw5Ku\nAR4BXgNO9pFBZmb1VVURiIh/ADu1azumi+nPA86rZp1mZlY7PmPYzKzEXATMzErMRcDMrMRcBMzM\nSsxFwMysxFwEzMxKzEXAzKzEXATMzErMRcDMrMRcBMzMSsxFwMysxFwEzMxKzEXAzKzEXATMzErM\nRcDMrMRcBMzMSqwWN5pvkfSQpAWS5qW2HSXNkbQ0/Ryc2iXpIknLJC2S9KFq129mZr1Xqy2Bj0dE\nU0Q0p+dTgNsiYjhwW3oO2U3ph6fHZOCSGq3fzMx6oa92B00ArkjDVwCfrWifEZm5wA7tbkxvZmY5\nqkURCOBWSfMlTU5tu0bEyjT8V2DXNLw78EzFvK2p7S0kTZY0T9K8NWvW1CCimZl1pKobzSdjImKF\npF2AOZIerRwZESEperLAiJgGTANobm7u0bxmZtZ9VW8JRMSK9HM1cCOwH7CqbTdP+rk6Tb4CGFox\n+5DUZmZmdVBVEZC0taRt24aBQ4DFwCxgUppsEnBTGp4FHJuOEjoAeK5it5GZmeWs2t1BuwI3Smpb\n1i8j4mZJ9wPXSPoS8BRwVJp+NnAosAx4CTi+yvWbmVkVqioCEfEEsE8H7WuBgztoD+DkatZpZma1\n4zOGzcxKzEXAzKzEXATMzErMRcDMrMRcBMzMSsxFwMysxFwEzMxKzEXAzKzEXATMzErMRcDMrMRc\nBMzMSsxFwMysxFwEzMxKzEXAzKzEXATMzEqs10VA0lBJt0t6RNLDkr6e2qdKWiFpQXocWjHPWZKW\nSXpM0qdq8QuYmVnvVXNTmdeAf4uIB9ItJudLmpPG/SAiLqycWNIIYCIwEngX8HtJe0XE61VkMDOz\nKvR6SyAiVkbEA2n4BWAJsHsXs0wAro6IVyLiSbJbTO7X2/WbmVn1atInIKkR2Be4LzWdImmRpOmS\nBqe23YFnKmZrpeuiYWZmfazqIiBpG+B64BsR8TxwCbAn0ASsBL7Xi2VOljRP0rw1a9ZUG9HMzDpR\nVRGQNIisAFwZETcARMSqiHg9It4ALuXNXT4rgKEVsw9JbW8TEdMiojkimhsaGqqJaGZmXajm6CAB\nlwFLIuL7Fe27VUx2BLA4Dc8CJkraXNIwYDjw596u38zMqlfN0UGjgWOAhyQtSG1nA0dLagICaAG+\nAhARD0u6BniE7Miik31kkJlZffW6CETEHwF1MGp2F/OcB5zX23WabUoap/y26mW0nH9YDZKYdc5n\nDJuZlZiLgJlZibkImJmVmIuAmVmJuQiYmZWYi4CZWYm5CJiZlZiLgJlZibkImJmVmIuAmVmJuQiY\nmZWYi4CZWYm5CJiZlZiLgJlZibkImJmVmIuAmVmJ5V4EJI2T9JikZZKm5L1+MzN7U65FQNJA4GJg\nPDCC7FaUI/LMYGZmb6rmHsO9sR+wLCKeAJB0NTCB7L7DmyzfRtDMNlV5F4HdgWcqnrcC+1ezQH8A\nv6kIr0URMhQph1nRKSLyW5n0eWBcRHw5PT8G2D8iTmk33WRgcnr6XuCxKla7M/C3KuavlSLkKEIG\nKEaOImSAYuQoQgYoRo4iZIDqc7w7Ihq6M2HeWwIrgKEVz4ektreIiGnAtFqsUNK8iGiuxbI29RxF\nyFCUHEXIUJQcRchQlBxFyJB3jryPDrofGC5pmKR3ABOBWTlnMDOzJNctgYh4TdIpwC3AQGB6RDyc\nZwYzM3tT3ruDiIjZwOwcV1mT3Uo1UIQcRcgAxchRhAxQjBxFyADFyFGEDJBjjlw7hs3MrFh82Qgz\nsxJzETAzKzEXATOzEsu9Y7hsJO0IEBHrSp5hV7IzxgFWRMSqMmZIOer+fhQhR4Hej7rnqGeGftUx\nLGl74Czgs8AuQACrgZuA8yPi7znl2AP4LnAw8HdAwHbAH4ApEdFShgwpRxPwE2B73jwxcEjKdFJE\nPFCSDEV5P+qeowjvR1FyFCEDEdFvHmTnH5wJvLOi7Z2p7dYcc9wLfAEYWNE2kOzkuLllyZDWuYDs\n0iDt2w8AFpYoQ1Hej7rnKML7UZQcRcjQ37YEHouI9/Z0XB/kWBoRw3s6rr9l6EaOZRHxT85QmPej\nCH+bubwfRclRhAz9rU/gKUlnAFdE2qeW9rUdx1uvXtrX5kv6T+CKivUOBSYBD5YoA8DvJP0WmNEu\nx7HAzSXKUJT3owg5ivB+FCVH3TP0ty2BwcAUsnsU7JKaV5Fdn+g7kVMHWLou0pdSjrbOnlbg18Bl\nEfFKGTJUZBnfLscKYFZkZ4+XIkNR3o8C5aj730RRctQ7Q78qAmZm1jOlOU9A0ofqnQFA0qedIZPu\nG+EMxXk/6p6jCO8HFCNHXhlKUwSAr9Y7QPLhegegGBkgOzyx3oqQoSjvRxFyFOH9gGLkyCWDdweZ\nmZVYfzs6CEkHAqsi4jFJo4GPAEsiovqbzvYsxzbAOLKe/teBx8nOVXgjxwzvo+MOpyV5ZeiKpOMj\n4vKc1vU+stfhvoh4saJ9XETkcxSGtB8QEXG/pBFkfx+P5t0Z2kGuGRFxbB3XPwbYD1gcEbfmuN79\nyT4bnpe0JdlBJR8CHgG+HRHP5ZDhVODGiMjz6MW3ZuhPWwKSfkj2x7QZ2YljBwO/Az4GPBgRp+eU\n4yjgNGAR8HHgHrJdb3sD/xIRi3LIcCZwNHA12dEfkJ2JOBG4OiLO7+sMGyPp6YjYI4f1nAqcDCwB\nmoCvR8RNadwDEdHn/UWSzgHGk/1tzgH2B24H/hm4JSLO6+sMKUf7O/mJ7G/0DwARcXgOGf4cEful\n4RPI3psbgUOAX+f1tynpYWCfyG52NQ14CbiO7HNjn4j4XA4ZngP+ASwHrgKujYg1fb3et8jjjLS8\nHsDDZH/UWwHPAlul9kFk3zLyyrGoYt07k/2TA3wQuCenDI8DgzpofwewNOfXoqPHQ8ArOWV4CNgm\nDTcC88gKAWRfDvLKMDD9bT4PbJfatwQW5fh+PAD8AjiI7MvRQcDKNPyxnDI8WDF8P9CQhrcGHsrx\ntVhS+bq0G7cgr9eC7AviIcBlwBqy8wMmAdvmkaG/7Q6KiAhJbbtc2jZz3iDfTnAB69PwP0jnLETE\nIknb5ZThDeBdwFPt2ndL4/KyK/ApsqJcSWRbSHkYEGkXUES0SDoIuE7Su8mvA/C1iHgdeEnS8oh4\nPuVZX/H3modm4OvAvwOnR8QCSesj4s4cMwxI5/QMINsbsQYgIv4h6bUccyyu2CW5UFJzRMyTtBfw\nak4ZIrJdxLcCt0oaRLbFeDRwIdDQ1wH6WxH4raS7gS2AnwHXSJpL9i3nrhxzzAZulnQX2X7fa2HD\nVRvz+tD5BnCbpKW8eSbiHsA/AafklAHgN2Tfwhe0HyHpjpwyrJLU1JYhIl5Mh0NOJ9tFl4f/lrRV\nRLwEjGprTBc9zK0IpA+cH0i6Nv1cRf6fA9sD88n+F0LSbhGxMvWj5XlUzpeB/yfpfwF/A+6V9AzZ\n/8uXc8rwlt83Il4lO7l1lqStcgmQNkn6DUkfIauucyXtCRwBPA1cF/l2yh4KjCC7CNSc1DaAbBdN\nXmdlDiDrI6nsGL4/fSMtDUlDyL6J/7WDcaMj4k85ZNi8o/dd0s7AbhHxUF9n6Iikw4DREXF2Pdbf\nLstWwK4R8WTO690OGEZWDFsjz8s4S3tFxON5ra/DDP2tCEAxrg9epBztSdomKo6QKXMOZyhWjiJk\nKEqOvDL0qyKgIlybu0A5OpPXUTmbQg5nKFaOImQoSo68MvS3PoGfA1+JiPsqGyUdAFwO7FOWHJK+\n2dkoYJu+Xn+RcjhDsXIUIUNRchQhQ3+7bMTW7T94ASJiLtnhZ2XK8W1gMLBtu8c25Pu+FyGHMxQr\nRxEyFCVH3TP0ty2Bul+bu0A5HgBmRsT89iMk5XXkQ1FyOEOxchQhQ1Fy1D1Dv+oTgPpfm7soOSS9\nF1gXHZx9KGnXvDqpi5DDGYqVowgZipKjEBn6WxEwM7Pu61d9ApK2l3S+pCWS1klam4bPl7RDmXJU\nZHi0IK9F3XI4Q7FyFCFDUXIUIUO/KgLANWSXJ/h4ROwYETuRXRzr72lcmXK0ZTioXYZnc8xQlBzO\nUKwcRchQlBx1z9CvdgdJeiwi3tvTcf0xRxEyFCWHMxQrRxEyFCVHETL0ty2BpySdoexMXSDrXFF2\nWeU8r9ddhBxFyFCUHM5QrBxFyFCUHHXP0N+KwBeAnYA7JT0raR1wB7AjcFTJchQhQ1FyOEOxchQh\nQ1Fy1D1Dv9odBKDsDlJDgLlRpztIFSVHETIUJYczFCtHETIUJUfdM0RON3DI4wGcCjwGzARagAkV\n4x4oU44iZChKDmcoVo4iZChKjkJkyOsFz+kFrfsdpIqSowgZipLDGYqVowgZipKjCBn622UjinAH\nqaLkKEKGouRwhmLlKEKGouSoe4b+1jG8StllnIHsDlLAp8nu85vXHaSKkqMIGYqSwxmKlaMIGYqS\no+4Z+lXHsApwB6mi5ChChqLkcIZi5ShChqLkKESG/lQEzMysZ/rb7iAzM+sBFwEzsxJzETAzKzEX\nASscSU2SDq14frikKWm4QdJ9kh6UNFbSbPXikruSDpL00YrnJ0o6tja/Qafr3EHSSb2c9+xa5zED\ndwxbwUjaDPgXoDkiTulg/ETgkxFR1a33JE0FXoyIC6tZTg/X2Qj8JiI+0IN5RHa8+PMRkdtN2CvW\nPzAiXs97vZYfbwlYn5F0rKRFkhZK+q/0Lf56Sfenx+g03dQ0/k/AfwHnAl+QtEDSFyQdJ+nH6Xjq\n7wIT0rgtJbVI2rmj9aW2z1RsOfxe2RUaG4ETgf+ZljM2ZTgtzdMkaW5a1o2SBqf2OyR9R9KfJT0u\naWwXv/vINN2CtJzhwPnAnqntAknbSLpN0gOSHpI0Ic3bKOkxSTOAxcBlwJZpvis7Wd+5kr5R8fw8\nSV9Pw6en13uRpP9TMc1MSfMlPSxpckX7i5K+J2kh8JEevOW2KcrjtGQ/yvcARgKPAzun5zsCvwTG\npOd7AEvS8FRgPrBlen4c8OOKZW143sG4FrITa962vvRzMG9u8X4Z+F7FOk+rWM6G58Ai4GNp+Fzg\nh2n4jor5DwV+38Xv/yPgi2n4HcCWZJcFWFwxzWbAdml4Z2AZ2bf+RuAN4ICKaV/cyOvdSLrWDNmX\nu+VkV6c8BJiWljsA+A1wYLvXaEuyYrNTeh7AUfX+G/Ijn0d/u2yEFccngGsj4m8AEbFO0ieBEdke\nDgC2k9S2i2NWRKyv5fpS+xDgV5J2I/swfrKrhUjaHtghIu5MTVcA11ZMckP6OZ/sg7cz9wL/nk4G\nuiEillb83htWB3xb0oFkH/q7A23XlX8qIuZ2lbVSZJccWCtp37SMByNiraRDyArBg2nSbYDhwF3A\nqZKOSO1DU/ta4HXg+u6u2zZtLgKWpwFk325frmxMH47/6KN1/gj4fkTMUnZdlqlVLu+V9PN1uvj/\niYhfSroPOAyYLekrwBPtJvsi0ACMiohXJbUAW6RxvXk9fka2pfROYHpqE/B/I+KnlROm1+KTwEci\n4iVJd1TEcEQEAAABf0lEQVSs++VwP0BpuE/A+sofgCMl7QQgaUfgVuBrbROo4pop7bwAbFuD9QFs\nD6xIw5M2to6IeA54tmJ//zHAne2n2xhJ7wGeiIiLgJuAD3awzu2B1akAfBx4dxeLfFXSoI2s9kZg\nHPBh4JbUdgvwr21bXJJ2l7RLWvezqQC8DzigZ7+h9RcuAtYnIuJh4DyyOyYtBL5Pdu305tRB+QhZ\n52xHbifbbbRA0heqWB9k3/yvlTQf+FvFLL8GjmjrGG63uEnABZIWAU1k/QI9dRSwWNIC4APAjIhY\nC/xJ0mJJFwBXkr0eDwHHAo92sbxpwKLOOoYBIuK/yV67a9q+yUfErWR9Mfem9VxHVohuBjaTtISs\nw7rbu56sf/Ehomb9hKQBwAPAkRGxtN55bNPgLQGzfkDSCLKji25zAbCe8JaAWRUkfQr4TrvmJyPi\niI6mr8H6dgJu62DUwWl3k1mPuAiYmZWYdweZmZWYi4CZWYm5CJiZlZiLgJlZibkImJmV2P8H1TM+\n/SISQTQAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7ff46e814b10>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "smvalley_crosstab.plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>net_workers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>certification_start_year</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2008.0</th>\n",
       "      <td>11.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009.0</th>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010.0</th>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011.0</th>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012.0</th>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.0</th>\n",
       "      <td>210.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.0</th>\n",
       "      <td>992.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.0</th>\n",
       "      <td>1599.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.0</th>\n",
       "      <td>2089.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017.0</th>\n",
       "      <td>1486.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          net_workers\n",
       "certification_start_year             \n",
       "2008.0                           11.0\n",
       "2009.0                           16.0\n",
       "2010.0                            7.0\n",
       "2011.0                           12.0\n",
       "2012.0                            6.0\n",
       "2013.0                          210.0\n",
       "2014.0                          992.0\n",
       "2015.0                         1599.0\n",
       "2016.0                         2089.0\n",
       "2017.0                         1486.0"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smvalley_crosstab"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Look at the crop for those 2012 workers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>case_number</th>\n",
       "      <th>employer</th>\n",
       "      <th>city</th>\n",
       "      <th>job_title</th>\n",
       "      <th>crop</th>\n",
       "      <th>net_workers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>C-11304-30370</td>\n",
       "      <td>ST. MARTIN JAUREGUY</td>\n",
       "      <td>PASO ROBLES</td>\n",
       "      <td>FARMWORKERS, FARM AND RANCH ANIMALS</td>\n",
       "      <td>Sheepherder</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>C-11361-31115</td>\n",
       "      <td>ST. MARTIN JAUREGUY</td>\n",
       "      <td>PASO ROBLES</td>\n",
       "      <td>SHEEPHERDER</td>\n",
       "      <td>Sheepherder</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>C-12033-32422</td>\n",
       "      <td>JEAN B JAUREGUY</td>\n",
       "      <td>PASO ROBLES</td>\n",
       "      <td>SHEEPHERDER</td>\n",
       "      <td>Sheepherder</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>C-12033-32422</td>\n",
       "      <td>ST. MARTIN JAUREGUY</td>\n",
       "      <td>PASO ROBLES</td>\n",
       "      <td>SHEEPHERDER</td>\n",
       "      <td>Sheepherder</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>735</th>\n",
       "      <td>C-12193-35263</td>\n",
       "      <td>JEAN B. JAUREGUY #01568</td>\n",
       "      <td>PASO ROBLES</td>\n",
       "      <td>FARMWORKERS, FARM AND RANCH ANIMALS</td>\n",
       "      <td>Sheepherder</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       case_number                 employer         city  \\\n",
       "735  C-11304-30370      ST. MARTIN JAUREGUY  PASO ROBLES   \n",
       "735  C-11361-31115      ST. MARTIN JAUREGUY  PASO ROBLES   \n",
       "735  C-12033-32422          JEAN B JAUREGUY  PASO ROBLES   \n",
       "735  C-12033-32422      ST. MARTIN JAUREGUY  PASO ROBLES   \n",
       "735  C-12193-35263  JEAN B. JAUREGUY #01568  PASO ROBLES   \n",
       "\n",
       "                               job_title         crop  net_workers  \n",
       "735  FARMWORKERS, FARM AND RANCH ANIMALS  Sheepherder          1.0  \n",
       "735                          SHEEPHERDER  Sheepherder          2.0  \n",
       "735                          SHEEPHERDER  Sheepherder          1.0  \n",
       "735                          SHEEPHERDER  Sheepherder          1.0  \n",
       "735  FARMWORKERS, FARM AND RANCH ANIMALS  Sheepherder          1.0  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smvalley[smvalley.certification_start_year == 2012][[\n",
    "    'case_number',\n",
    "    'employer',\n",
    "    'city',\n",
    "    'job_title',\n",
    "    'crop',\n",
    "    'net_workers'\n",
    "]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding: Strawberry workers account for most of the growth in that area"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "smvalley_crops = smvalley[smvalley.certification_start_year == 2016].groupby([\n",
    "    'latimes_crop'\n",
    "]).net_workers.sum().reset_index().set_index(\"latimes_crop\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>net_workers</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>latimes_crop</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Strawberries</th>\n",
       "      <td>1075.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lettuce</th>\n",
       "      <td>324.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blackberries</th>\n",
       "      <td>133.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Celery</th>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zucchini</th>\n",
       "      <td>73.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Broccoli</th>\n",
       "      <td>53.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Peppers</th>\n",
       "      <td>45.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Squash</th>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bok Choy</th>\n",
       "      <td>37.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              net_workers\n",
       "latimes_crop             \n",
       "Strawberries       1075.0\n",
       "Lettuce             324.0\n",
       "Blackberries        133.0\n",
       "Celery               95.0\n",
       "Other                75.0\n",
       "Zucchini             73.0\n",
       "Broccoli             53.0\n",
       "Peppers              45.0\n",
       "Squash               39.0\n",
       "Bok Choy             37.0"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "smvalley_crops.sort_values(\"net_workers\", ascending=False).head(10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12+"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}