nwac.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# scraping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import requests\n",
    "import bs4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_years():\n",
    "    url = 'http://www.nwac.us/avalanche-forecast/archives/'\n",
    "    r = requests.get(url)\n",
    "    soup = bs4.BeautifulSoup(r.text, 'html5lib')\n",
    "    dropdown = soup.find('select', attrs={'name': 'year'})\n",
    "    years = [obj.attrs['value'] for obj in dropdown.findAll('option')]\n",
    "    return years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2015', '2014', '2013', '2016']"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_years()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_months(year):\n",
    "    fmt = 'http://www.nwac.us/avalanche-forecast/archives/months/?year={year}'\n",
    "    r = requests.get(fmt.format(year=year))\n",
    "    soup = bs4.BeautifulSoup(r.text, 'html5lib')\n",
    "    dropdown = soup.find('select', attrs={'name': 'month'})\n",
    "    months = [obj.attrs['value'] for obj in dropdown.findAll('option')]\n",
    "    return months"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['December', 'October', 'June', 'April', 'March', 'February', 'January']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_months(2015)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import re\n",
    "renum = re.compile(r'\\d+')\n",
    "\n",
    "def get_forecast_list(month, year):\n",
    "    fmt = 'http://www.nwac.us/avalanche-forecast/archives/days/?month={month}&year={year}'\n",
    "    r = requests.get(fmt.format(year=year, month=month))\n",
    "    soup = bs4.BeautifulSoup(r.text, 'html5lib')\n",
    "    main = soup.find('div', attrs={'id': 'main-content'})\n",
    "    links = main.findAll('a')\n",
    "    results = []\n",
    "    for link in links:\n",
    "        url = link.attrs['href']\n",
    "        forecast_id = int(renum.search(url).group(0))\n",
    "        date = str(link.contents[0].lstrip())\n",
    "        results.append((date,forecast_id))\n",
    "    return results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Jan. 31, 2016', 498),\n",
       " ('Jan. 30, 2016', 497),\n",
       " ('Jan. 30, 2016', 496),\n",
       " ('Jan. 29, 2016', 495),\n",
       " ('Jan. 28, 2016', 494),\n",
       " ('Jan. 27, 2016', 493),\n",
       " ('Jan. 27, 2016', 492),\n",
       " ('Jan. 26, 2016', 491),\n",
       " ('Jan. 25, 2016', 490),\n",
       " ('Jan. 24, 2016', 489),\n",
       " ('Jan. 23, 2016', 488),\n",
       " ('Jan. 22, 2016', 487),\n",
       " ('Jan. 21, 2016', 486),\n",
       " ('Jan. 21, 2016', 485),\n",
       " ('Jan. 21, 2016', 484),\n",
       " ('Jan. 20, 2016', 483),\n",
       " ('Jan. 19, 2016', 482),\n",
       " ('Jan. 18, 2016', 481),\n",
       " ('Jan. 17, 2016', 480),\n",
       " ('Jan. 16, 2016', 479),\n",
       " ('Jan. 15, 2016', 478),\n",
       " ('Jan. 15, 2016', 477),\n",
       " ('Jan. 14, 2016', 476),\n",
       " ('Jan. 13, 2016', 475),\n",
       " ('Jan. 12, 2016', 474),\n",
       " ('Jan. 12, 2016', 473),\n",
       " ('Jan. 11, 2016', 472),\n",
       " ('Jan. 10, 2016', 471),\n",
       " ('Jan. 9, 2016', 470),\n",
       " ('Jan. 8, 2016', 469),\n",
       " ('Jan. 7, 2016', 468),\n",
       " ('Jan. 6, 2016', 467),\n",
       " ('Jan. 5, 2016', 466),\n",
       " ('Jan. 4, 2016', 465),\n",
       " ('Jan. 4, 2016', 464),\n",
       " ('Jan. 3, 2016', 463),\n",
       " ('Jan. 2, 2016', 462),\n",
       " ('Jan. 1, 2016', 461)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_forecast_list('January', '2016')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Forecasts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def trim(string, lead):\n",
    "    assert string[:len(lead)] == lead\n",
    "    return string[len(lead):]\n",
    "\n",
    "def get_forecast(forecast_id):\n",
    "    fmt = 'http://www.nwac.us/avalanche-forecast/avalanche-forecast/{num}/'\n",
    "    r = requests.get(fmt.format(num=forecast_id))\n",
    "    soup = bs4.BeautifulSoup(r.text, 'html5lib')\n",
    "\n",
    "    date = str(soup.find('div', attrs={'class': 'forecast-date'}).contents[0].strip())\n",
    "    author = str(soup.find('div', attrs={'class': 'forecaster'}).contents[0].strip())\n",
    "    forecasts = soup.findAll('a', attrs={'class': 'forecast'})\n",
    "    \n",
    "    results = []\n",
    "    for forecast in forecasts:\n",
    "        region = str(forecast.find('span').contents[0].strip())\n",
    "        url = forecast.attrs['href']\n",
    "        subforecast_id = int(renum.search(url).group(0))\n",
    "        results.append((region, subforecast_id))\n",
    "    \n",
    "    return trim(date, 'Issued: '), trim(author, 'by '), results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the archive, note that frequently all east-side regions link to the same report:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('6:38 AM PST Tuesday, December 23, 2014',\n",
       " 'Garth Ferber',\n",
       " [('Olympics', 921),\n",
       "  ('West Slopes North - Canadian Border to Skagit River', 922),\n",
       "  ('West Slopes Central - Skagit River to South of I-90', 923),\n",
       "  ('West Slopes South - South of I-90 to Columbia River', 923),\n",
       "  ('Stevens Pass', 923),\n",
       "  ('Snoqualmie Pass', 923),\n",
       "  ('East Slopes North - Canadian Border to Lake Chelan', 924),\n",
       "  ('East Slopes Central - Lake Chelan to South of I-90', 924),\n",
       "  ('East Slopes South - South of I-90 to Columbia River', 925),\n",
       "  ('Mt Hood', 926)])"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_forecast(274)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('7:57 AM PST Thursday, January 21, 2016',\n",
       " \"Dennis D'Amico\",\n",
       " [('Olympics', 1966),\n",
       "  ('West Slopes North - Canadian Border to Skagit River', 1967),\n",
       "  ('West Slopes Central - Skagit River to South of I-90', 1968),\n",
       "  ('West Slopes South - South of I-90 to Columbia River', 1968),\n",
       "  ('Stevens Pass', 1968),\n",
       "  ('Snoqualmie Pass', 1968),\n",
       "  ('East Slopes North - Canadian Border to Lake Chelan', 1969),\n",
       "  ('East Slopes Central - Lake Chelan to South of I-90', 1970),\n",
       "  ('East Slopes South - South of I-90 to Columbia River', 1970),\n",
       "  ('Mt Hood', 1971)])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_forecast(486)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('6:00 PM PST Thursday, December 31, 2015',\n",
       " \"Dennis D'Amico\",\n",
       " [('Olympics', 1850),\n",
       "  ('West Slopes North - Canadian Border to Skagit River', 1851),\n",
       "  ('West Slopes Central - Skagit River to South of I-90', 1851),\n",
       "  ('West Slopes South - South of I-90 to Columbia River', 1851),\n",
       "  ('Stevens Pass', 1851),\n",
       "  ('Snoqualmie Pass', 1851),\n",
       "  ('East Slopes North - Canadian Border to Lake Chelan', 1852),\n",
       "  ('East Slopes Central - Lake Chelan to South of I-90', 1852),\n",
       "  ('East Slopes South - South of I-90 to Columbia River', 1852),\n",
       "  ('Mt Hood', 1853)])"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_forecast(461)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('2:36 PM PST Tuesday, December 10, 2013',\n",
       " 'Kenny Kramer',\n",
       " [('Olympics', 73),\n",
       "  ('Stevens Pass', 73),\n",
       "  ('Snoqualmie Pass', 73),\n",
       "  ('Mt Hood', 73),\n",
       "  ('White Pass', 73),\n",
       "  ('WA Cascades near and west of crest - between Snoqualmie and White Pass',\n",
       "   73),\n",
       "  ('East slopes WA Cascades - between Stevens and Snoqualmie Pass', 73),\n",
       "  ('WA Cascades near and west of crest - north of Stevens Pass', 73),\n",
       "  ('WA Cascades near and west of crest - south of White Pass', 73),\n",
       "  ('East slopes WA Cascades - between Snoqualmie and White Pass', 73),\n",
       "  ('WA Cascades near and west of crest - between Stevens and Snoqualmie Pass',\n",
       "   73),\n",
       "  ('East slopes WA Cascades - north of Stevens Pass', 73),\n",
       "  ('East slopes WA Cascades - south of White Pass', 73)])"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_forecast(37)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "test date string conversion. it puts it in UTC for us"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2:36 PM PST Tuesday, December 10, 2013'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "_[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2013-12-10 22:36:00')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#pandas.to_datetime(_, utc=True)\n",
    "pd.to_datetime(_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reports\n",
    "\n",
    "A forecast contains multiple reports, each individualized to a geographic sub-region of the NWAC forecast area."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def get_report(subforecast_id):\n",
    "    fmt = 'http://www.nwac.us/avalanche-forecast/avalanche-region-forecast/{num}/'\n",
    "    r = requests.get(fmt.format(num=subforecast_id))\n",
    "    soup = bs4.BeautifulSoup(r.text, 'html5lib')\n",
    "\n",
    "    date = str(soup.find('div', attrs={'class': 'forecast-date'}).contents[0].strip())\n",
    "    author = str(soup.find('div', attrs={'class': 'forecaster'}).contents[0].strip())\n",
    "    region = str(soup.find('h2').contents[0].strip())\n",
    "    \n",
    "    summary = soup.find('div', attrs={'class': 'forecast-summary'})\n",
    "    if summary is None:\n",
    "        summary = soup.find('div', attrs={'class': 'special-header-notes'})\n",
    "    summary = str(summary.text).strip().replace(u'\\xa0', u' ')\n",
    "    \n",
    "    areas = ['treeline-above',\n",
    "             'treeline-near',\n",
    "             'treeline-below']\n",
    "    \n",
    "    ratings = []\n",
    "    for area in areas:\n",
    "        rating = find_rating(soup, area)\n",
    "        ratings.append((area, rating))\n",
    "    \n",
    "    return trim(date, 'Issued: '), trim(author, 'by '), region, summary, ratings\n",
    "\n",
    "def find_rating(soup, area):\n",
    "    chunk = soup.find('div', attrs={'id': area})\n",
    "    if chunk is None:\n",
    "        return None\n",
    "    rating = chunk.find(\n",
    "        'div', attrs={'class': 'elev-day1-column'}\n",
    "            ).find(\n",
    "        'div', attrs={'class': 'danger-description'}\n",
    "            ).find('h4').contents[0]\n",
    "    return str(rating)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('6:00 PM PST Thursday, December 31, 2015',\n",
       " \"Dennis D'Amico\",\n",
       " 'East Slopes North - Canadian Border to Lake Chelan',\n",
       " 'Watch for isolated areas of wind slab primarily on north through southeast aspects above treeline and for small loose avalanches near terrain traps on steeper slopes throughout the terrain.',\n",
       " [('treeline-above', 'Moderate'),\n",
       "  ('treeline-near', 'Low'),\n",
       "  ('treeline-below', 'Low')])"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_report(1852)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('10:11 AM PST Monday, June 2, 2014',\n",
       " 'Kenny Kramer',\n",
       " 'Olympics',\n",
       " 'Have a safe and enjoyable rest of the spring and we look forward to bringing you even better mountain weather and avalanche forecasts next season! A big thanks to all who help in supporting the Northwest Avalanche Center.  \\n\\nPlease note that regularly scheduled mountain weather and avalanche forecasts for the past winter season have ended.  However, weather and snow conditions will continue to be monitored through mid-June at the Northwest Weather and Avalanche Center with the information that remains available.  Additional forecasts or special statements will be issued when unusually severe avalanche conditions develop.  When issued such forecasts will be available on the NWAC Web site.',\n",
       " [('treeline-above', None), ('treeline-near', None), ('treeline-below', None)])"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_report(884)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Database schema\n",
    "\n",
    "Organized storage for forecasts and reports, as well as a join table to say which go with which"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlalchemy as sa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "postgres://tkb@localhost/avy\n"
     ]
    }
   ],
   "source": [
    "engine = sa.create_engine('postgres://%s@localhost/%s'%('tkb','avy'))\n",
    "print(engine.url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "metadata = sa.MetaData()\n",
    "\n",
    "forecast_table = sa.Table('forecasts', metadata,\n",
    "                          sa.Column('forecast_id', sa.Integer,\n",
    "                                    primary_key=True, autoincrement=False),\n",
    "                          sa.Column('date', sa.Text),\n",
    "                          sa.Column('datetime', sa.Text),\n",
    "                          sa.Column('datetime_utc', sa.Time),\n",
    "                          sa.Column('author', sa.Text),\n",
    "                         )\n",
    "\n",
    "report_table = sa.Table('reports', metadata,\n",
    "                        sa.Column('report_id', sa.Integer,\n",
    "                                  primary_key=True, autoincrement=False),\n",
    "                          sa.Column('datetime', sa.Text),\n",
    "                          sa.Column('datetime_utc', sa.DateTime),\n",
    "                          sa.Column('author', sa.Text),\n",
    "                          sa.Column('region', sa.Text),\n",
    "                          sa.Column('summary', sa.Text),\n",
    "                          sa.Column('treeline_above', sa.Text),\n",
    "                          sa.Column('treeline_near', sa.Text),\n",
    "                          sa.Column('treeline_below', sa.Text),\n",
    "                        )\n",
    "\n",
    "region_index = sa.Table('region_index', metadata,\n",
    "                        sa.Column('forecast_id', sa.Integer, primary_key=True),\n",
    "                        sa.Column('region', sa.Text, primary_key=True),\n",
    "                        sa.Column('report_id', sa.Integer)\n",
    "                       )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "#report_table.create(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlalchemy_utils as sau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "#sau.drop_database(engine.url)\n",
    "print(sau.database_exists(engine.url))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n"
     ]
    }
   ],
   "source": [
    "## create a database (if it doesn't exist)\n",
    "if not sau.database_exists(engine.url):\n",
    "    sau.create_database(engine.url)\n",
    "    metadata.create_all(engine)\n",
    "print(sau.database_exists(engine.url))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Feed in an example row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "with engine.begin() as conn:\n",
    "    print (conn.closed)\n",
    "    pass\n",
    "print (conn.closed)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# read in preliminary data.\n",
    "\n",
    "to empty it, do TRUNCATE TABLE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Insert all in one statement. If there's a problem with any one row, this whole thing fails"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def insert_forecasts_prelim(days):\n",
    "    with engine.begin() as conn:\n",
    "        data = [{'forecast_id': fid, 'date': date} for date,fid in days]\n",
    "        stmt = forecast_table.insert().values(data)\n",
    "        conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "batched queries. Depending on how these loops are nested, only individual things fail"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.exc import IntegrityError\n",
    "\n",
    "def insert_forecasts_prelim_batched(days):\n",
    "    for date,forecast_id in days:\n",
    "        with engine.begin() as conn:\n",
    "            stmt = forecast_table.insert().values(date=date, forecast_id=forecast_id)\n",
    "            try:\n",
    "                conn.execute(stmt)\n",
    "            except IntegrityError:\n",
    "                print(\"couldn't insert data for date \", date)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "for testing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "days = get_forecast_list('February', 2016)\n",
    "insert_forecasts_prelim_batched(days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "couldn't insert data for date  Jan. 31, 2016\n",
      "couldn't insert data for date  Jan. 30, 2016\n",
      "couldn't insert data for date  Jan. 30, 2016\n",
      "couldn't insert data for date  Jan. 29, 2016\n",
      "couldn't insert data for date  Jan. 28, 2016\n",
      "couldn't insert data for date  Jan. 27, 2016\n",
      "couldn't insert data for date  Jan. 27, 2016\n",
      "couldn't insert data for date  Jan. 26, 2016\n",
      "couldn't insert data for date  Jan. 25, 2016\n",
      "couldn't insert data for date  Jan. 24, 2016\n",
      "couldn't insert data for date  Jan. 23, 2016\n",
      "couldn't insert data for date  Jan. 22, 2016\n",
      "couldn't insert data for date  Jan. 21, 2016\n",
      "couldn't insert data for date  Jan. 21, 2016\n",
      "couldn't insert data for date  Jan. 21, 2016\n",
      "couldn't insert data for date  Jan. 20, 2016\n",
      "couldn't insert data for date  Jan. 19, 2016\n",
      "couldn't insert data for date  Jan. 18, 2016\n",
      "couldn't insert data for date  Jan. 17, 2016\n",
      "couldn't insert data for date  Jan. 16, 2016\n",
      "couldn't insert data for date  Jan. 15, 2016\n",
      "couldn't insert data for date  Jan. 15, 2016\n",
      "couldn't insert data for date  Jan. 14, 2016\n",
      "couldn't insert data for date  Jan. 13, 2016\n",
      "couldn't insert data for date  Jan. 12, 2016\n",
      "couldn't insert data for date  Jan. 12, 2016\n",
      "couldn't insert data for date  Jan. 11, 2016\n",
      "couldn't insert data for date  Jan. 10, 2016\n",
      "couldn't insert data for date  Jan. 9, 2016\n",
      "couldn't insert data for date  Jan. 8, 2016\n",
      "couldn't insert data for date  Jan. 7, 2016\n",
      "couldn't insert data for date  Jan. 6, 2016\n",
      "couldn't insert data for date  Jan. 5, 2016\n",
      "couldn't insert data for date  Jan. 4, 2016\n",
      "couldn't insert data for date  Jan. 4, 2016\n",
      "couldn't insert data for date  Jan. 3, 2016\n",
      "couldn't insert data for date  Jan. 2, 2016\n",
      "couldn't insert data for date  Jan. 1, 2016\n"
     ]
    }
   ],
   "source": [
    "days = get_forecast_list('January', 2016)\n",
    "insert_forecasts_prelim_batched(days)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# get list of all forecasts ever\n",
    "\n",
    "and put this in the database as preliminary datas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def gen_all_reports():\n",
    "    for year in get_years():\n",
    "        for month in get_months(year):\n",
    "            yield from get_forecast_list(month, year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "insert_forecasts_prelim(gen_all_reports())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>forecast_id</th>\n",
       "      <th>date</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_utc</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37</td>\n",
       "      <td>Dec. 11, 2013</td>\n",
       "      <td>2:36 PM PST Tuesday, December 10, 2013</td>\n",
       "      <td>22:36:00</td>\n",
       "      <td>Kenny Kramer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>38</td>\n",
       "      <td>Dec. 11, 2013</td>\n",
       "      <td>2:42 PM PST Tuesday, December 10, 2013</td>\n",
       "      <td>22:42:00</td>\n",
       "      <td>Kenny Kramer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>39</td>\n",
       "      <td>Dec. 11, 2013</td>\n",
       "      <td>9:24 PM PST Tuesday, December 10, 2013</td>\n",
       "      <td>05:24:00</td>\n",
       "      <td>Kenny Kramer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>40</td>\n",
       "      <td>Dec. 12, 2013</td>\n",
       "      <td>3:26 PM PST Wednesday, December 11, 2013</td>\n",
       "      <td>23:26:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>47</td>\n",
       "      <td>Dec. 13, 2013</td>\n",
       "      <td>7:07 PM PST Thursday, December 12, 2013</td>\n",
       "      <td>03:07:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   forecast_id           date                                  datetime  \\\n",
       "0           37  Dec. 11, 2013    2:36 PM PST Tuesday, December 10, 2013   \n",
       "1           38  Dec. 11, 2013    2:42 PM PST Tuesday, December 10, 2013   \n",
       "2           39  Dec. 11, 2013    9:24 PM PST Tuesday, December 10, 2013   \n",
       "3           40  Dec. 12, 2013  3:26 PM PST Wednesday, December 11, 2013   \n",
       "4           47  Dec. 13, 2013   7:07 PM PST Thursday, December 12, 2013   \n",
       "\n",
       "  datetime_utc          author  \n",
       "0     22:36:00    Kenny Kramer  \n",
       "1     22:42:00    Kenny Kramer  \n",
       "2     05:24:00    Kenny Kramer  \n",
       "3     23:26:00    Garth Ferber  \n",
       "4     03:07:00  Dennis D'Amico  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from forecasts order by forecast_id limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>forecast_id</th>\n",
       "      <th>date</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_utc</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>510</td>\n",
       "      <td>Feb. 10, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>509</td>\n",
       "      <td>Feb. 9, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>508</td>\n",
       "      <td>Feb. 8, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>507</td>\n",
       "      <td>Feb. 7, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>506</td>\n",
       "      <td>Feb. 6, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   forecast_id           date datetime datetime_utc author\n",
       "0          510  Feb. 10, 2016     None         None   None\n",
       "1          509   Feb. 9, 2016     None         None   None\n",
       "2          508   Feb. 8, 2016     None         None   None\n",
       "3          507   Feb. 7, 2016     None         None   None\n",
       "4          506   Feb. 6, 2016     None         None   None"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from forecasts order by forecast_id desc limit 5', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add region data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def load_forecast(forecast_id):\n",
    "    datetime,author,reg_index = get_forecast(forecast_id)\n",
    "    datetime_utc = pd.to_datetime(datetime)\n",
    "    values = dict(datetime=datetime, author=author, datetime_utc=datetime_utc)\n",
    "    \n",
    "    with engine.begin() as conn:\n",
    "        # fill in the rest of the forecast info (update one row)\n",
    "        stmt = forecast_table.update().where(\n",
    "                   forecast_table.c.forecast_id==forecast_id\n",
    "                   ).values(**values)\n",
    "        conn.execute(stmt)\n",
    "\n",
    "        # populate the region index (many rows)\n",
    "        data = [{'region': reg, 'report_id': rid, 'forecast_id': forecast_id} for reg,rid in reg_index]\n",
    "        stmt2 = region_index.insert().values(data)\n",
    "        conn.execute(stmt2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "load_forecast(37)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.read_sql_query('select * from forecasts order by forecast_id limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "pd.read_sql_query('select * from region_index', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# read in all detailed forecast data\n",
    "\n",
    "Only where data is missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>forecast_id</th>\n",
       "      <th>date</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_utc</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>493</td>\n",
       "      <td>Jan. 27, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>494</td>\n",
       "      <td>Jan. 28, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>495</td>\n",
       "      <td>Jan. 29, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>496</td>\n",
       "      <td>Jan. 30, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>497</td>\n",
       "      <td>Jan. 30, 2016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   forecast_id           date datetime datetime_utc author\n",
       "0          493  Jan. 27, 2016     None         None   None\n",
       "1          494  Jan. 28, 2016     None         None   None\n",
       "2          495  Jan. 29, 2016     None         None   None\n",
       "3          496  Jan. 30, 2016     None         None   None\n",
       "4          497  Jan. 30, 2016     None         None   None"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from forecasts where datetime_utc is null order by forecast_id limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def fill_forecasts(how_many=5):\n",
    "    stmt = sa.select(\n",
    "                    [forecast_table.c.forecast_id]\n",
    "                ).where(\n",
    "                    forecast_table.c.author == None\n",
    "                ).order_by(\n",
    "                    forecast_table.c.forecast_id.desc()\n",
    "                ).limit(how_many)\n",
    "    str(stmt)\n",
    "\n",
    "    with engine.begin() as conn:\n",
    "        result = conn.execute(stmt)\n",
    "\n",
    "    for fid, in result:\n",
    "        print(fid, end=' ')\n",
    "        load_forecast(fid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "510 509 508 507 506 "
     ]
    }
   ],
   "source": [
    "fill_forecasts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "505 504 502 501 500 "
     ]
    }
   ],
   "source": [
    "fill_forecasts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "499 498 497 496 495 494 493 "
     ]
    }
   ],
   "source": [
    "fill_forecasts(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>forecast_id</th>\n",
       "      <th>date</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_utc</th>\n",
       "      <th>author</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [forecast_id, date, datetime, datetime_utc, author]\n",
       "Index: []"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from forecasts where datetime_utc is null order by forecast_id limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def missing_forecasts():\n",
    "    \"\"\"How many forecasts are missing?\"\"\"\n",
    "    stmt = sa.select(\n",
    "                    [sa.func.count(forecast_table.c.forecast_id)]\n",
    "                ).where(\n",
    "                    forecast_table.c.author == None\n",
    "                )\n",
    "    str(stmt)\n",
    "\n",
    "    with engine.begin() as conn:\n",
    "        result = conn.execute(stmt)\n",
    "\n",
    "    missing, = result.fetchone()\n",
    "    return missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_forecasts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Read in Reports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>forecast_id</th>\n",
       "      <th>region</th>\n",
       "      <th>report_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37</td>\n",
       "      <td>Stevens Pass</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37</td>\n",
       "      <td>Snoqualmie Pass</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37</td>\n",
       "      <td>Mt Hood</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37</td>\n",
       "      <td>White Pass</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   forecast_id           region  report_id\n",
       "0           37         Olympics         73\n",
       "1           37     Stevens Pass         73\n",
       "2           37  Snoqualmie Pass         73\n",
       "3           37          Mt Hood         73\n",
       "4           37       White Pass         73"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select * from region_index limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>report_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   report_id\n",
       "0       2085\n",
       "1       2084\n",
       "2       2084\n",
       "3       2084\n",
       "4       2083"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select report_id from region_index order by report_id desc limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>report_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2083</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2081</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   report_id\n",
       "0       2085\n",
       "1       2084\n",
       "2       2083\n",
       "3       2082\n",
       "4       2081"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select distinct report_id from region_index order by report_id desc limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>report_id</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2085</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2084</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2083</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2082</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2081</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   report_id  count\n",
       "0       2085      1\n",
       "1       2084      3\n",
       "2       2083      5\n",
       "3       2082      1\n",
       "4       2081      1"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select report_id, count(report_id) from region_index '\n",
    "                  'group by report_id order by report_id desc limit 5', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('6:00 PM PST Tuesday, February 9, 2016',\n",
       " 'Kenny Kramer',\n",
       " 'Mt Hood',\n",
       " 'Cooler weather with a few light showers should maintain some shallow wet snow or crust layers. Watch for unexpected wet snow deeper than boot top. The cooler weather should limit the avalanche potential Wednesday.',\n",
       " [('treeline-above', 'Low'),\n",
       "  ('treeline-near', 'Low'),\n",
       "  ('treeline-below', 'Low')])"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_report(2085)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def load_report(report_id):\n",
    "    datetime,author,region,summary,conditions_list = get_report(report_id)\n",
    "    datetime_utc = pd.to_datetime(datetime)\n",
    "    conditions_dict = dict((k.replace('-','_'),v) for k,v in conditions_list)\n",
    "    values = dict(report_id=report_id,\n",
    "                  datetime=datetime, author=author, datetime_utc=datetime_utc,\n",
    "                 region=region, summary=summary, **conditions_dict)\n",
    "    \n",
    "    with engine.begin() as conn:\n",
    "        stmt = report_table.insert(\n",
    "                   ).values(**values)\n",
    "        conn.execute(stmt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "load_report(2085)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def fill_reports(how_many=5):\n",
    "    # select report ids from the region index\n",
    "    # which have no counterpart in the report table,\n",
    "    # ignoring duplicates (distinct). big nasty query:\n",
    "    stmt = sa.select(\n",
    "                    [region_index.c.report_id]\n",
    "                ).select_from(region_index.outerjoin(report_table,\n",
    "                        region_index.c.report_id == report_table.c.report_id)\n",
    "                ).where(\n",
    "                    report_table.c.report_id == None\n",
    "                #).where(\n",
    "                #    region_index.c.report_id <= 1180\n",
    "                ).order_by(\n",
    "                    region_index.c.report_id.desc()\n",
    "                ).distinct().limit(how_many)\n",
    "\n",
    "    with engine.begin() as conn:\n",
    "        result = conn.execute(stmt)\n",
    "\n",
    "    for rid, in result:\n",
    "        print(rid, end=' ')\n",
    "        load_report(rid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def missing_reports():\n",
    "    stmt = sa.select(\n",
    "                    [sa.func.count(region_index.c.report_id.distinct())]\n",
    "                ).select_from(region_index.outerjoin(report_table,\n",
    "                        region_index.c.report_id == report_table.c.report_id)\n",
    "                ).where(\n",
    "                    report_table.c.report_id == None\n",
    "                )\n",
    "    str(stmt)\n",
    "\n",
    "    with engine.begin() as conn:\n",
    "        result = conn.execute(stmt)\n",
    "\n",
    "    missing, = result.fetchone()\n",
    "    return missing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "198"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_reports()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2084 2083 2082 2081 2080 2078 2077 2076 2075 2074 2073 2072 2071 2070 2069 2068 2067 2066 2065 2064 "
     ]
    }
   ],
   "source": [
    "fill_reports(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2063 2062 2060 2059 2058 2057 2056 2055 2054 2047 2046 2045 2044 2043 2042 2041 2040 2039 2038 2037 2036 2035 2034 2033 2032 2031 2029 2028 2027 2026 2025 2024 2023 2022 2021 2020 2019 2018 2017 2016 "
     ]
    }
   ],
   "source": [
    "fill_reports(40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "138"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_reports()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 2003 270 269 268 267 266 265 264 263 260 259 258 257 256 255 251 246 245 244 243 238 237 236 235 234 233 232 231 "
     ]
    }
   ],
   "source": [
    "fill_reports(40)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "notice it kicked over and started getting the old ones, after recent ones finished arriving"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "fill_reports(85)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get backlog of data\n",
    "\n",
    "stagger our scraping. useful when filling DB, to not hammer NWAC too hard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 138)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_forecasts(), missing_reports()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import random\n",
    "random.randint(1,20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from time import sleep\n",
    "\n",
    "def get_staggered(seconds):\n",
    "    accumulated = 0\n",
    "    while accumulated <= seconds:\n",
    "        fill_forecasts(random.randint(1,3))\n",
    "        print('')\n",
    "        fill_reports(random.randint(4,12))\n",
    "        print('')\n",
    "        wait = random.randint(2,7)\n",
    "        accumulated += wait\n",
    "        sleep(wait)\n",
    "    return missing_forecasts(), missing_reports()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "get_staggered(60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "get_staggered(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# what regions do we have?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mt Hood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Stevens Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Snoqualmie Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>WA Cascades near and west of crest - south of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Olympics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               region\n",
       "0   East Slopes North - Canadian Border to Lake Ch...\n",
       "1   WA Cascades near and west of crest - between S...\n",
       "2                                             Mt Hood\n",
       "3     East slopes WA Cascades - north of Stevens Pass\n",
       "4   West Slopes South - South of I-90 to Columbia ...\n",
       "5                                        Stevens Pass\n",
       "6   WA Cascades near and west of crest - north of ...\n",
       "7   West Slopes North - Canadian Border to Skagit ...\n",
       "8                                     Snoqualmie Pass\n",
       "9   WA Cascades near and west of crest - between S...\n",
       "10                                         White Pass\n",
       "11  WA Cascades near and west of crest - south of ...\n",
       "12                                           Olympics\n",
       "13  East slopes WA Cascades - between Snoqualmie a...\n",
       "14  East slopes WA Cascades - between Stevens and ...\n",
       "15  East Slopes South - South of I-90 to Columbia ...\n",
       "16  East Slopes Central - Lake Chelan to South of ...\n",
       "17      East slopes WA Cascades - south of White Pass\n",
       "18  West Slopes Central - Skagit River to South of..."
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select distinct region from region_index;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mt Hood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Stevens Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Snoqualmie Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Olympics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               region\n",
       "0   East Slopes North - Canadian Border to Lake Ch...\n",
       "1   WA Cascades near and west of crest - between S...\n",
       "2                                             Mt Hood\n",
       "3     East slopes WA Cascades - north of Stevens Pass\n",
       "4   West Slopes South - South of I-90 to Columbia ...\n",
       "5                                        Stevens Pass\n",
       "6   WA Cascades near and west of crest - north of ...\n",
       "7   West Slopes North - Canadian Border to Skagit ...\n",
       "8                                     Snoqualmie Pass\n",
       "9                                          White Pass\n",
       "10                                           Olympics\n",
       "11  East slopes WA Cascades - between Snoqualmie a...\n",
       "12  East slopes WA Cascades - between Stevens and ...\n",
       "13  East Slopes South - South of I-90 to Columbia ...\n",
       "14  East Slopes Central - Lake Chelan to South of ...\n",
       "15      East slopes WA Cascades - south of White Pass\n",
       "16  West Slopes Central - Skagit River to South of..."
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select distinct region from reports;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'East Slopes Central - Lake Chelan to South of I-90',\n",
       " 'East Slopes North - Canadian Border to Lake Chelan',\n",
       " 'East Slopes South - South of I-90 to Columbia River',\n",
       " 'East slopes WA Cascades - between Snoqualmie and White Pass',\n",
       " 'East slopes WA Cascades - between Stevens and Snoqualmie Pass',\n",
       " 'East slopes WA Cascades - north of Stevens Pass',\n",
       " 'East slopes WA Cascades - south of White Pass',\n",
       " 'Mt Hood',\n",
       " 'Olympics',\n",
       " 'Snoqualmie Pass',\n",
       " 'Stevens Pass',\n",
       " 'WA Cascades near and west of crest - between Snoqualmie and White Pass',\n",
       " 'WA Cascades near and west of crest - between Stevens and Snoqualmie Pass',\n",
       " 'WA Cascades near and west of crest - north of Stevens Pass',\n",
       " 'WA Cascades near and west of crest - south of White Pass',\n",
       " 'West Slopes Central - Skagit River to South of I-90',\n",
       " 'West Slopes North - Canadian Border to Skagit River',\n",
       " 'West Slopes South - South of I-90 to Columbia River',\n",
       " 'White Pass'}"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "set(_68['region']) | set(_67['region'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Somewhere along the line, NWAC changed their regions. The 2 southern regions (E/W south of I-90, aka snoqualamie) used to be 4, with a further subdivision at white pass. In the north region, the border between North and central regions was moved north (from stevens pass to Skagit/Chelan.\n",
    "\n",
    "I'm blurring the line here between I-90 and 'S of I-90', so don't choose any sites in that zone\n",
    "\n",
    "I will define 10 regions, in place of the 6 existing, formed by the intersection of all these historical divisions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "redistricting = [\n",
    "    ('east-far-north',\n",
    "     ['East Slopes North - Canadian Border to Lake Chelan', 'East slopes WA Cascades - north of Stevens Pass']),\n",
    "    ('east-mid-north',\n",
    "     ['East Slopes Central - Lake Chelan to South of I-90', 'East slopes WA Cascades - north of Stevens Pass']),\n",
    "    ('east-central',\n",
    "     ['East Slopes Central - Lake Chelan to South of I-90', 'East slopes WA Cascades - between Stevens and Snoqualmie Pass']),\n",
    "    ('east-mid-south',\n",
    "     ['East Slopes South - South of I-90 to Columbia River', 'East slopes WA Cascades - between Snoqualmie and White Pass']),\n",
    "    ('east-far-south',\n",
    "     ['East Slopes South - South of I-90 to Columbia River', 'East slopes WA Cascades - south of White Pass']),\n",
    "    #deep breath\n",
    "    ('west-far-north',\n",
    "     ['West Slopes North - Canadian Border to Skagit River', 'WA Cascades near and west of crest - north of Stevens Pass']),\n",
    "    ('west-mid-north',\n",
    "     ['West Slopes Central - Skagit River to South of I-90', 'WA Cascades near and west of crest - north of Stevens Pass']),\n",
    "    ('west-central',\n",
    "     ['West Slopes Central - Skagit River to South of I-90', 'WA Cascades near and west of crest - between Stevens and Snoqualmie Pass']),\n",
    "    ('west-mid-south',\n",
    "     ['West Slopes South - South of I-90 to Columbia River', 'WA Cascades near and west of crest - between Snoqualmie and White Pass']),\n",
    "    ('west-far-south',\n",
    "     ['West Slopes South - South of I-90 to Columbia River', 'WA Cascades near and west of crest - south of White Pass']),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4776</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0   4776"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select count(region) from region_index;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1544</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count\n",
       "0   1544"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select count(region) from reports;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mt Hood</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Stevens Pass</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Snoqualmie Pass</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>White Pass</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>WA Cascades near and west of crest - south of ...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Olympics</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               region  count\n",
       "0   East Slopes North - Canadian Border to Lake Ch...    228\n",
       "1   WA Cascades near and west of crest - between S...    192\n",
       "2                                             Mt Hood    420\n",
       "3     East slopes WA Cascades - north of Stevens Pass    192\n",
       "4   West Slopes South - South of I-90 to Columbia ...    228\n",
       "5                                        Stevens Pass    420\n",
       "6   WA Cascades near and west of crest - north of ...    192\n",
       "7   West Slopes North - Canadian Border to Skagit ...    228\n",
       "8                                     Snoqualmie Pass    420\n",
       "9   WA Cascades near and west of crest - between S...    192\n",
       "10                                         White Pass    192\n",
       "11  WA Cascades near and west of crest - south of ...    192\n",
       "12                                           Olympics    420\n",
       "13  East slopes WA Cascades - between Snoqualmie a...    192\n",
       "14  East slopes WA Cascades - between Stevens and ...    192\n",
       "15  East Slopes South - South of I-90 to Columbia ...    228\n",
       "16  East Slopes Central - Lake Chelan to South of ...    228\n",
       "17      East slopes WA Cascades - south of White Pass    192\n",
       "18  West Slopes Central - Skagit River to South of...    228"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select region, count(region) from region_index group by region;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mt Hood</td>\n",
       "      <td>336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Stevens Pass</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Snoqualmie Pass</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>White Pass</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Olympics</td>\n",
       "      <td>373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               region  count\n",
       "0   East Slopes North - Canadian Border to Lake Ch...    215\n",
       "1   WA Cascades near and west of crest - between S...      1\n",
       "2                                             Mt Hood    336\n",
       "3     East slopes WA Cascades - north of Stevens Pass    120\n",
       "4   West Slopes South - South of I-90 to Columbia ...     26\n",
       "5                                        Stevens Pass    109\n",
       "6   WA Cascades near and west of crest - north of ...      9\n",
       "7   West Slopes North - Canadian Border to Skagit ...    215\n",
       "8                                     Snoqualmie Pass      4\n",
       "9                                          White Pass      6\n",
       "10                                           Olympics    373\n",
       "11  East slopes WA Cascades - between Snoqualmie a...     10\n",
       "12  East slopes WA Cascades - between Stevens and ...      2\n",
       "13  East Slopes South - South of I-90 to Columbia ...     42\n",
       "14  East Slopes Central - Lake Chelan to South of ...     49\n",
       "15      East slopes WA Cascades - south of White Pass      2\n",
       "16  West Slopes Central - Skagit River to South of...     25"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select region, count(region) from reports group by region;', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'east-central': ['East Slopes Central - Lake Chelan to South of I-90',\n",
       "  'East slopes WA Cascades - between Stevens and Snoqualmie Pass'],\n",
       " 'east-far-north': ['East Slopes North - Canadian Border to Lake Chelan',\n",
       "  'East slopes WA Cascades - north of Stevens Pass'],\n",
       " 'east-far-south': ['East Slopes South - South of I-90 to Columbia River',\n",
       "  'East slopes WA Cascades - south of White Pass'],\n",
       " 'east-mid-north': ['East Slopes Central - Lake Chelan to South of I-90',\n",
       "  'East slopes WA Cascades - north of Stevens Pass'],\n",
       " 'east-mid-south': ['East Slopes South - South of I-90 to Columbia River',\n",
       "  'East slopes WA Cascades - between Snoqualmie and White Pass'],\n",
       " 'west-central': ['West Slopes Central - Skagit River to South of I-90',\n",
       "  'WA Cascades near and west of crest - between Stevens and Snoqualmie Pass'],\n",
       " 'west-far-north': ['West Slopes North - Canadian Border to Skagit River',\n",
       "  'WA Cascades near and west of crest - north of Stevens Pass'],\n",
       " 'west-far-south': ['West Slopes South - South of I-90 to Columbia River',\n",
       "  'WA Cascades near and west of crest - south of White Pass'],\n",
       " 'west-mid-north': ['West Slopes Central - Skagit River to South of I-90',\n",
       "  'WA Cascades near and west of crest - north of Stevens Pass'],\n",
       " 'west-mid-south': ['West Slopes South - South of I-90 to Columbia River',\n",
       "  'WA Cascades near and west of crest - between Snoqualmie and White Pass']}"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict(redistricting)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>count_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mt Hood</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Stevens Pass</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Snoqualmie Pass</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>White Pass</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>WA Cascades near and west of crest - south of ...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Olympics</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "      <td>192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "      <td>228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               region  count_1\n",
       "0   East Slopes North - Canadian Border to Lake Ch...      228\n",
       "1   WA Cascades near and west of crest - between S...      192\n",
       "2                                             Mt Hood      420\n",
       "3     East slopes WA Cascades - north of Stevens Pass      192\n",
       "4   West Slopes South - South of I-90 to Columbia ...      228\n",
       "5                                        Stevens Pass      420\n",
       "6   WA Cascades near and west of crest - north of ...      192\n",
       "7   West Slopes North - Canadian Border to Skagit ...      228\n",
       "8                                     Snoqualmie Pass      420\n",
       "9   WA Cascades near and west of crest - between S...      192\n",
       "10                                         White Pass      192\n",
       "11  WA Cascades near and west of crest - south of ...      192\n",
       "12                                           Olympics      420\n",
       "13  East slopes WA Cascades - between Snoqualmie a...      192\n",
       "14  East slopes WA Cascades - between Stevens and ...      192\n",
       "15  East Slopes South - South of I-90 to Columbia ...      228\n",
       "16  East Slopes Central - Lake Chelan to South of ...      228\n",
       "17      East slopes WA Cascades - south of White Pass      192\n",
       "18  West Slopes Central - Skagit River to South of...      228"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stmt = sa.select([region_index.c.region,\n",
    "                  sa.func.count(region_index.c.region), \n",
    "                 ]).group_by(region_index.c.region)\n",
    "\n",
    "pd.read_sql_query(stmt, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT region_index.region, count(region_index.region) AS count_1 \n",
      "FROM region_index \n",
      "WHERE region_index.region = :region_1 OR region_index.region = :region_2 GROUP BY region_index.region\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>region</th>\n",
       "      <th>count_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mt Hood</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Olympics</td>\n",
       "      <td>420</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     region  count_1\n",
       "0   Mt Hood      420\n",
       "1  Olympics      420"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stmt = sa.select([region_index.c.region,\n",
    "                  sa.func.count(region_index.c.region), \n",
    "                 ]\n",
    "                ).group_by(region_index.c.region\n",
    "                ).where(\n",
    "    sa.or_(region_index.c.region == 'Olympics',\n",
    "           region_index.c.region == 'Mt Hood'\n",
    "          )\n",
    "    )\n",
    "print(stmt)\n",
    "pd.read_sql_query(stmt, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT count(region_index.region) AS count_1, region_index.region \n",
      "FROM region_index \n",
      "WHERE region_index.region = :region_1 OR region_index.region = :region_2 GROUP BY region_index.region\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228</td>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>192</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      228  West Slopes South - South of I-90 to Columbia ...\n",
       "1      192  WA Cascades near and west of crest - between S..."
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def count_subregions(subregions, column):\n",
    "    or_ = sa.or_(column == subr for subr in subregions)\n",
    "    stmt = sa.select([sa.func.count(column), column]).group_by(column).where(or_)\n",
    "    return stmt\n",
    "\n",
    "stmt = count_subregions(dict(redistricting)['west-mid-south'],\n",
    "                       region_index.c.region)\n",
    "print (stmt)\n",
    "pd.read_sql_query(stmt, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'east-far-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228</td>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>192</td>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      228  East Slopes North - Canadian Border to Lake Ch...\n",
       "1      192    East slopes WA Cascades - north of Stevens Pass"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-mid-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228</td>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>192</td>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      228  East Slopes Central - Lake Chelan to South of ...\n",
       "1      192    East slopes WA Cascades - north of Stevens Pass"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-central'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192</td>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>228</td>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      192  East slopes WA Cascades - between Stevens and ...\n",
       "1      228  East Slopes Central - Lake Chelan to South of ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-mid-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192</td>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>228</td>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      192  East slopes WA Cascades - between Snoqualmie a...\n",
       "1      228  East Slopes South - South of I-90 to Columbia ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-far-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192</td>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>228</td>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      192      East slopes WA Cascades - south of White Pass\n",
       "1      228  East Slopes South - South of I-90 to Columbia ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-far-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192</td>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>228</td>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      192  WA Cascades near and west of crest - north of ...\n",
       "1      228  West Slopes North - Canadian Border to Skagit ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-mid-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192</td>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>228</td>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      192  WA Cascades near and west of crest - north of ...\n",
       "1      228  West Slopes Central - Skagit River to South of..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-central'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>228</td>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      192  WA Cascades near and west of crest - between S...\n",
       "1      228  West Slopes Central - Skagit River to South of..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-mid-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228</td>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>192</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      228  West Slopes South - South of I-90 to Columbia ...\n",
       "1      192  WA Cascades near and west of crest - between S..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-far-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>228</td>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>192</td>\n",
       "      <td>WA Cascades near and west of crest - south of ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      228  West Slopes South - South of I-90 to Columbia ...\n",
       "1      192  WA Cascades near and west of crest - south of ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display\n",
    "\n",
    "for metaregion, real_regions in redistricting:\n",
    "    stmt = count_subregions(dict(redistricting)[metaregion], region_index.c.region)\n",
    "    display(metaregion)\n",
    "    display(pd.read_sql_query(stmt, engine))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'east-far-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>218</td>\n",
       "      <td>East Slopes North - Canadian Border to Lake Ch...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>128</td>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0      218  East Slopes North - Canadian Border to Lake Ch...\n",
       "1      128    East slopes WA Cascades - north of Stevens Pass"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-mid-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51</td>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>128</td>\n",
       "      <td>East slopes WA Cascades - north of Stevens Pass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       51  East Slopes Central - Lake Chelan to South of ...\n",
       "1      128    East slopes WA Cascades - north of Stevens Pass"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-central'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>51</td>\n",
       "      <td>East Slopes Central - Lake Chelan to South of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>East slopes WA Cascades - between Stevens and ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       51  East Slopes Central - Lake Chelan to South of ...\n",
       "1        2  East slopes WA Cascades - between Stevens and ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-mid-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>East slopes WA Cascades - between Snoqualmie a...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       12  East slopes WA Cascades - between Snoqualmie a...\n",
       "1       42  East Slopes South - South of I-90 to Columbia ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'east-far-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>East slopes WA Cascades - south of White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>42</td>\n",
       "      <td>East Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0        2      East slopes WA Cascades - south of White Pass\n",
       "1       42  East Slopes South - South of I-90 to Columbia ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-far-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>218</td>\n",
       "      <td>West Slopes North - Canadian Border to Skagit ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0        9  WA Cascades near and west of crest - north of ...\n",
       "1      218  West Slopes North - Canadian Border to Skagit ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-mid-north'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25</td>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9</td>\n",
       "      <td>WA Cascades near and west of crest - north of ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       25  West Slopes Central - Skagit River to South of...\n",
       "1        9  WA Cascades near and west of crest - north of ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-central'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25</td>\n",
       "      <td>West Slopes Central - Skagit River to South of...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       25  West Slopes Central - Skagit River to South of..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-mid-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       26  West Slopes South - South of I-90 to Columbia ...\n",
       "1        1  WA Cascades near and west of crest - between S..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/plain": [
       "'west-far-south'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count_1</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>26</td>\n",
       "      <td>West Slopes South - South of I-90 to Columbia ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   count_1                                             region\n",
       "0       26  West Slopes South - South of I-90 to Columbia ..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display\n",
    "\n",
    "for metaregion, real_regions in redistricting:\n",
    "    stmt = count_subregions(dict(redistricting)[metaregion], report_table.c.region)\n",
    "    display(metaregion)\n",
    "    display(pd.read_sql_query(stmt, engine))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "well that's kind of wild. two problems (in the other notebook): name change, and also it's not ok to just grab reports that have the label. report sharing means they usually point to something else."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# region shorthands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#this is for shortening\n",
    "region_names = [('Olympics', 'olympics'),\n",
    "                  ('West Slopes North - Canadian Border to Skagit River', 'west-north'),\n",
    "                  ('West Slopes Central - Skagit River to South of I-90', 'west-central'),\n",
    "                  ('West Slopes South - South of I-90 to Columbia River', 'west-south'),\n",
    "                  ('Stevens Pass', 'stevens'),\n",
    "                  ('Snoqualmie Pass', 'snoqualmie'),\n",
    "                  ('East Slopes North - Canadian Border to Lake Chelan', 'east-north'),\n",
    "                  ('East Slopes Central - Lake Chelan to South of I-90', 'east-central'),\n",
    "                  ('East Slopes South - South of I-90 to Columbia River', 'east-south'),\n",
    "                  ('Mt Hood', 'hood')]\n",
    "\n",
    "regions = [n for _,n in region_names]\n",
    "\n",
    "region_dict = dict(region_names)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# plotting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.rc('savefig', dpi=100)\n",
    "plt.rc('figure', figsize=(8,8))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Considerable': 'orange',\n",
       " 'Extreme': 'black',\n",
       " 'High': 'red',\n",
       " 'Low': 'green',\n",
       " 'Moderate': 'yellow',\n",
       " 'No Rating': 'gray'}"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings = ['No Rating', 'Low', 'Moderate', 'Considerable', 'High', 'Extreme']\n",
    "colors = ['gray', 'green', 'yellow', 'orange', 'red', 'black']\n",
    "coldict = {rating:color for rating, color in zip(ratings, colors)}\n",
    "coldict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ratdict = {rat:i for i,rat in enumerate(ratings)}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from matplotlib.patches import Rectangle\n",
    "from matplotlib.dates import date2num, num2date\n",
    "\n",
    "def color_timeline(data, ymin=0, dy=1, ax=None):\n",
    "    if ax is None:\n",
    "        ax = plt.gca()\n",
    "    for stamp,danger in data.iteritems():\n",
    "        #start = date2num(stamp)\n",
    "        start = date2num(stamp)\n",
    "        color = coldict.get(danger, 'm') # magenta for unknown (probably null) values\n",
    "        r = Rectangle((start, ymin), 1, dy, fc=color, ec='w')\n",
    "        ax.add_patch(r)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>treeline_above</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Moderate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>High</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Considerable</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  treeline_above\n",
       "0           None\n",
       "1       Moderate\n",
       "2           High\n",
       "3            Low\n",
       "4   Considerable"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query('select distinct treeline_above from reports', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'east-central': ['East Slopes Central - Lake Chelan to South of I-90',\n",
       "  'East slopes WA Cascades - between Stevens and Snoqualmie Pass'],\n",
       " 'east-far-north': ['East Slopes North - Canadian Border to Lake Chelan',\n",
       "  'East slopes WA Cascades - north of Stevens Pass'],\n",
       " 'east-far-south': ['East Slopes South - South of I-90 to Columbia River',\n",
       "  'East slopes WA Cascades - south of White Pass'],\n",
       " 'east-mid-north': ['East Slopes Central - Lake Chelan to South of I-90',\n",
       "  'East slopes WA Cascades - north of Stevens Pass'],\n",
       " 'east-mid-south': ['East Slopes South - South of I-90 to Columbia River',\n",
       "  'East slopes WA Cascades - between Snoqualmie and White Pass'],\n",
       " 'west-central': ['West Slopes Central - Skagit River to South of I-90',\n",
       "  'WA Cascades near and west of crest - between Stevens and Snoqualmie Pass'],\n",
       " 'west-far-north': ['West Slopes North - Canadian Border to Skagit River',\n",
       "  'WA Cascades near and west of crest - north of Stevens Pass'],\n",
       " 'west-far-south': ['West Slopes South - South of I-90 to Columbia River',\n",
       "  'WA Cascades near and west of crest - south of White Pass'],\n",
       " 'west-mid-north': ['West Slopes Central - Skagit River to South of I-90',\n",
       "  'WA Cascades near and west of crest - north of Stevens Pass'],\n",
       " 'west-mid-south': ['West Slopes South - South of I-90 to Columbia River',\n",
       "  'WA Cascades near and west of crest - between Snoqualmie and White Pass']}"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict(redistricting)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT reports.report_id, reports.datetime, reports.datetime_utc, reports.author, reports.region, reports.summary, reports.treeline_above, reports.treeline_near, reports.treeline_below \n",
      "FROM region_index JOIN reports ON reports.report_id = region_index.report_id \n",
      "WHERE region_index.region = :region_1\n"
     ]
    }
   ],
   "source": [
    "joined = region_index.join(report_table,\n",
    "                           report_table.c.report_id == region_index.c.report_id)\n",
    "stmt = sa.select([report_table]\n",
    "                ).select_from(\n",
    "                    joined\n",
    "                ).where(\n",
    "                    region_index.c.region == 'Mt Hood'\n",
    "                )\n",
    "print(stmt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Mt Hood']"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region = 'Mt Hood'\n",
    "dict(redistricting).get(region, [region,])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def fetch_ratings(range_min, range_max, region):\n",
    "    # if a redistricted region was given,\n",
    "    # get the list of regions to get ratings\n",
    "    # from. otherwise it's just the argument\n",
    "    regions = dict(redistricting).get(region, [region,])\n",
    "    joined = region_index.join(report_table,\n",
    "                           report_table.c.report_id == region_index.c.report_id)\n",
    "    stmt = sa.select([report_table, region_index.c.region]\n",
    "                ).select_from(\n",
    "                        joined\n",
    "                    ).where(\n",
    "                        sa.or_(region_index.c.region == subr for subr in regions)\n",
    "                    ).where(\n",
    "                        report_table.c.datetime_utc <= range_max\n",
    "                    ).where(\n",
    "                        report_table.c.datetime_utc >= range_min\n",
    "                    ).order_by(report_table.c.datetime_utc)\n",
    "\n",
    "    print(stmt)\n",
    "    df = pd.read_sql_query(stmt, engine)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def snow_year(year):\n",
    "    return pd.datetime(year - 1, 12, 1), pd.datetime(year, 6, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT reports.report_id, reports.datetime, reports.datetime_utc, reports.author, reports.region, reports.summary, reports.treeline_above, reports.treeline_near, reports.treeline_below, region_index.region \n",
      "FROM region_index JOIN reports ON reports.report_id = region_index.report_id \n",
      "WHERE region_index.region = :region_1 AND reports.datetime_utc <= :datetime_utc_1 AND reports.datetime_utc >= :datetime_utc_2 ORDER BY reports.datetime_utc\n",
      "154\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>report_id</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_utc</th>\n",
       "      <th>author</th>\n",
       "      <th>region</th>\n",
       "      <th>summary</th>\n",
       "      <th>treeline_above</th>\n",
       "      <th>treeline_near</th>\n",
       "      <th>treeline_below</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>232</td>\n",
       "      <td>5:32 PM PST Monday, January 6, 2014</td>\n",
       "      <td>2014-01-07 01:32:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The Bottom Line: New loading through this week...</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>Low</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>235</td>\n",
       "      <td>6:00 PM PST Tuesday, January 7, 2014</td>\n",
       "      <td>2014-01-08 02:00:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The Bottom Line: Look for increasing avalanche...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>243</td>\n",
       "      <td>6:00 PM PST Wednesday, January 8, 2014</td>\n",
       "      <td>2014-01-09 02:00:00</td>\n",
       "      <td>Kenny Kramer</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>Bottom Line: Cooling should help new snow prof...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>251</td>\n",
       "      <td>6:00 PM PST Thursday, January 9, 2014</td>\n",
       "      <td>2014-01-10 02:00:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>An active, complex weather pattern will cause ...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>257</td>\n",
       "      <td>8:54 AM PST Friday, January 10, 2014</td>\n",
       "      <td>2014-01-10 16:54:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>An active, complex weather pattern will cause ...</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>260</td>\n",
       "      <td>6:00 PM PST Friday, January 10, 2014</td>\n",
       "      <td>2014-01-11 02:00:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>A lot of changing weather is expected starting...</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>265</td>\n",
       "      <td>9:17 AM PST Saturday, January 11, 2014</td>\n",
       "      <td>2014-01-11 17:17:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>A lot of changing weather is expected starting...</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>268</td>\n",
       "      <td>6:06 PM PST Saturday, January 11, 2014</td>\n",
       "      <td>2014-01-12 02:06:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The storm cycle continues on Sunday, look for ...</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>272</td>\n",
       "      <td>6:13 PM PST Sunday, January 12, 2014</td>\n",
       "      <td>2014-01-13 02:13:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The avalanche danger should tick down a notch ...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>276</td>\n",
       "      <td>6:22 PM PST Sunday, January 12, 2014</td>\n",
       "      <td>2014-01-13 02:22:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The avalanche danger should tick down a notch ...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>White Pass</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   report_id                                datetime        datetime_utc  \\\n",
       "0        232     5:32 PM PST Monday, January 6, 2014 2014-01-07 01:32:00   \n",
       "1        235    6:00 PM PST Tuesday, January 7, 2014 2014-01-08 02:00:00   \n",
       "2        243  6:00 PM PST Wednesday, January 8, 2014 2014-01-09 02:00:00   \n",
       "3        251   6:00 PM PST Thursday, January 9, 2014 2014-01-10 02:00:00   \n",
       "4        257    8:54 AM PST Friday, January 10, 2014 2014-01-10 16:54:00   \n",
       "5        260    6:00 PM PST Friday, January 10, 2014 2014-01-11 02:00:00   \n",
       "6        265  9:17 AM PST Saturday, January 11, 2014 2014-01-11 17:17:00   \n",
       "7        268  6:06 PM PST Saturday, January 11, 2014 2014-01-12 02:06:00   \n",
       "8        272    6:13 PM PST Sunday, January 12, 2014 2014-01-13 02:13:00   \n",
       "9        276    6:22 PM PST Sunday, January 12, 2014 2014-01-13 02:22:00   \n",
       "\n",
       "           author    region  \\\n",
       "0  Dennis D'Amico  Olympics   \n",
       "1  Dennis D'Amico  Olympics   \n",
       "2    Kenny Kramer  Olympics   \n",
       "3    Garth Ferber  Olympics   \n",
       "4    Garth Ferber  Olympics   \n",
       "5    Garth Ferber  Olympics   \n",
       "6  Dennis D'Amico  Olympics   \n",
       "7  Dennis D'Amico  Olympics   \n",
       "8  Dennis D'Amico  Olympics   \n",
       "9  Dennis D'Amico  Olympics   \n",
       "\n",
       "                                             summary treeline_above  \\\n",
       "0  The Bottom Line: New loading through this week...       Moderate   \n",
       "1  The Bottom Line: Look for increasing avalanche...   Considerable   \n",
       "2  Bottom Line: Cooling should help new snow prof...   Considerable   \n",
       "3  An active, complex weather pattern will cause ...   Considerable   \n",
       "4  An active, complex weather pattern will cause ...           High   \n",
       "5  A lot of changing weather is expected starting...           High   \n",
       "6  A lot of changing weather is expected starting...           High   \n",
       "7  The storm cycle continues on Sunday, look for ...           High   \n",
       "8  The avalanche danger should tick down a notch ...   Considerable   \n",
       "9  The avalanche danger should tick down a notch ...   Considerable   \n",
       "\n",
       "  treeline_near treeline_below      region  \n",
       "0      Moderate            Low  White Pass  \n",
       "1      Moderate       Moderate  White Pass  \n",
       "2  Considerable       Moderate  White Pass  \n",
       "3  Considerable       Moderate  White Pass  \n",
       "4  Considerable       Moderate  White Pass  \n",
       "5          High   Considerable  White Pass  \n",
       "6          High   Considerable  White Pass  \n",
       "7          High   Considerable  White Pass  \n",
       "8  Considerable   Considerable  White Pass  \n",
       "9  Considerable   Considerable  White Pass  "
      ]
     },
     "execution_count": 173,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = fetch_ratings(*snow_year(2014), 'White Pass')\n",
    "print(len(df))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT reports.report_id, reports.datetime, reports.datetime_utc, reports.author, reports.region, reports.summary, reports.treeline_above, reports.treeline_near, reports.treeline_below, region_index.region \n",
      "FROM region_index JOIN reports ON reports.report_id = region_index.report_id \n",
      "WHERE (region_index.region = :region_1 OR region_index.region = :region_2) AND reports.datetime_utc <= :datetime_utc_1 AND reports.datetime_utc >= :datetime_utc_2 ORDER BY reports.datetime_utc\n",
      "154\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>report_id</th>\n",
       "      <th>datetime</th>\n",
       "      <th>datetime_utc</th>\n",
       "      <th>author</th>\n",
       "      <th>region</th>\n",
       "      <th>summary</th>\n",
       "      <th>treeline_above</th>\n",
       "      <th>treeline_near</th>\n",
       "      <th>treeline_below</th>\n",
       "      <th>region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>232</td>\n",
       "      <td>5:32 PM PST Monday, January 6, 2014</td>\n",
       "      <td>2014-01-07 01:32:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The Bottom Line: New loading through this week...</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>Low</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>235</td>\n",
       "      <td>6:00 PM PST Tuesday, January 7, 2014</td>\n",
       "      <td>2014-01-08 02:00:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The Bottom Line: Look for increasing avalanche...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>243</td>\n",
       "      <td>6:00 PM PST Wednesday, January 8, 2014</td>\n",
       "      <td>2014-01-09 02:00:00</td>\n",
       "      <td>Kenny Kramer</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>Bottom Line: Cooling should help new snow prof...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>251</td>\n",
       "      <td>6:00 PM PST Thursday, January 9, 2014</td>\n",
       "      <td>2014-01-10 02:00:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>An active, complex weather pattern will cause ...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>257</td>\n",
       "      <td>8:54 AM PST Friday, January 10, 2014</td>\n",
       "      <td>2014-01-10 16:54:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>An active, complex weather pattern will cause ...</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Moderate</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>260</td>\n",
       "      <td>6:00 PM PST Friday, January 10, 2014</td>\n",
       "      <td>2014-01-11 02:00:00</td>\n",
       "      <td>Garth Ferber</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>A lot of changing weather is expected starting...</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>265</td>\n",
       "      <td>9:17 AM PST Saturday, January 11, 2014</td>\n",
       "      <td>2014-01-11 17:17:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>A lot of changing weather is expected starting...</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>268</td>\n",
       "      <td>6:06 PM PST Saturday, January 11, 2014</td>\n",
       "      <td>2014-01-12 02:06:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The storm cycle continues on Sunday, look for ...</td>\n",
       "      <td>High</td>\n",
       "      <td>High</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>272</td>\n",
       "      <td>6:13 PM PST Sunday, January 12, 2014</td>\n",
       "      <td>2014-01-13 02:13:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The avalanche danger should tick down a notch ...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>276</td>\n",
       "      <td>6:22 PM PST Sunday, January 12, 2014</td>\n",
       "      <td>2014-01-13 02:22:00</td>\n",
       "      <td>Dennis D'Amico</td>\n",
       "      <td>Olympics</td>\n",
       "      <td>The avalanche danger should tick down a notch ...</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>Considerable</td>\n",
       "      <td>WA Cascades near and west of crest - between S...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   report_id                                datetime        datetime_utc  \\\n",
       "0        232     5:32 PM PST Monday, January 6, 2014 2014-01-07 01:32:00   \n",
       "1        235    6:00 PM PST Tuesday, January 7, 2014 2014-01-08 02:00:00   \n",
       "2        243  6:00 PM PST Wednesday, January 8, 2014 2014-01-09 02:00:00   \n",
       "3        251   6:00 PM PST Thursday, January 9, 2014 2014-01-10 02:00:00   \n",
       "4        257    8:54 AM PST Friday, January 10, 2014 2014-01-10 16:54:00   \n",
       "5        260    6:00 PM PST Friday, January 10, 2014 2014-01-11 02:00:00   \n",
       "6        265  9:17 AM PST Saturday, January 11, 2014 2014-01-11 17:17:00   \n",
       "7        268  6:06 PM PST Saturday, January 11, 2014 2014-01-12 02:06:00   \n",
       "8        272    6:13 PM PST Sunday, January 12, 2014 2014-01-13 02:13:00   \n",
       "9        276    6:22 PM PST Sunday, January 12, 2014 2014-01-13 02:22:00   \n",
       "\n",
       "           author    region  \\\n",
       "0  Dennis D'Amico  Olympics   \n",
       "1  Dennis D'Amico  Olympics   \n",
       "2    Kenny Kramer  Olympics   \n",
       "3    Garth Ferber  Olympics   \n",
       "4    Garth Ferber  Olympics   \n",
       "5    Garth Ferber  Olympics   \n",
       "6  Dennis D'Amico  Olympics   \n",
       "7  Dennis D'Amico  Olympics   \n",
       "8  Dennis D'Amico  Olympics   \n",
       "9  Dennis D'Amico  Olympics   \n",
       "\n",
       "                                             summary treeline_above  \\\n",
       "0  The Bottom Line: New loading through this week...       Moderate   \n",
       "1  The Bottom Line: Look for increasing avalanche...   Considerable   \n",
       "2  Bottom Line: Cooling should help new snow prof...   Considerable   \n",
       "3  An active, complex weather pattern will cause ...   Considerable   \n",
       "4  An active, complex weather pattern will cause ...           High   \n",
       "5  A lot of changing weather is expected starting...           High   \n",
       "6  A lot of changing weather is expected starting...           High   \n",
       "7  The storm cycle continues on Sunday, look for ...           High   \n",
       "8  The avalanche danger should tick down a notch ...   Considerable   \n",
       "9  The avalanche danger should tick down a notch ...   Considerable   \n",
       "\n",
       "  treeline_near treeline_below  \\\n",
       "0      Moderate            Low   \n",
       "1      Moderate       Moderate   \n",
       "2  Considerable       Moderate   \n",
       "3  Considerable       Moderate   \n",
       "4  Considerable       Moderate   \n",
       "5          High   Considerable   \n",
       "6          High   Considerable   \n",
       "7          High   Considerable   \n",
       "8  Considerable   Considerable   \n",
       "9  Considerable   Considerable   \n",
       "\n",
       "                                              region  \n",
       "0  WA Cascades near and west of crest - between S...  \n",
       "1  WA Cascades near and west of crest - between S...  \n",
       "2  WA Cascades near and west of crest - between S...  \n",
       "3  WA Cascades near and west of crest - between S...  \n",
       "4  WA Cascades near and west of crest - between S...  \n",
       "5  WA Cascades near and west of crest - between S...  \n",
       "6  WA Cascades near and west of crest - between S...  \n",
       "7  WA Cascades near and west of crest - between S...  \n",
       "8  WA Cascades near and west of crest - between S...  \n",
       "9  WA Cascades near and west of crest - between S...  "
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = fetch_ratings(*snow_year(2014), 'west-central')\n",
    "print(len(df))\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "that seems kinda weird but they really do batch olympics in with white pass some of the time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def stacked_color_timeline(df, ax=None):\n",
    "    if ax is None:\n",
    "        ax = plt.gca()\n",
    "    color_timeline(df.set_index('datetime_utc')['treeline_above'], ax=ax, ymin=2)\n",
    "    color_timeline(df.set_index('datetime_utc')['treeline_near'], ax=ax, ymin=1)\n",
    "    color_timeline(df.set_index('datetime_utc')['treeline_below'], ax=ax)\n",
    "    ax.set_ylim(0,3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.figure.Figure at 0x108495908>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAsAAAAKaCAYAAAAwOu9+AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAAPYQAAD2EBqD+naQAAIABJREFUeJzs3X2cJWV54P3fxfAO0mJGZ+iIvIkzGDcwPUl8GXkxiGR0\no0ZdQ49mASfGF3b1M3myusLzxE2ejSzL6mhEYp64vsXYWSMmIa4RBV8mEozZGcAsDkOUQckIA0a2\nEQbU9FzPH1Wnu071OadP91TTM9bv+/mcT1fVfdddV9131enr1KmujsxEkiRJaouDljoASZIk6bFk\nAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJklrFBFiSJEmtYgIs\nSZKkVmkkAY6I10fErRExWb7+NiJ+aY51zomIrRHxaETcEREXNhGLJEmSNEhTV4DvBt4KjAFrgS8A\n10bE03tVjogTgU8DNwCnA+8BPhAR5zUUjyRJktRTZObiNBzxz8BvZeaHepRdAazPzJ+tLJsARjLz\nhYsSkCRJksQi3AMcEQdFxAXAYcDf9Kn2LOD62rLrgGc3HY8kSZJUdXBTDUXEM4CbgMOBPcArM/Ob\nfaqvBHbXlu0GjomIwzLzhz3a/yngfOAu4NGm4pYkSVJjDgdOBK7LzH9e4lj6aiwBBm6nuJ93BHgF\n8KcRcXZm3txQ++cDf9JQW5IkSVo8rwI+vtRB9NNYApyZ/wLcWc7eHBG/ALwB+I0e1e8FVtSWrQAe\n7HX1t3QXwMc+9jFOO+20BUR4GbATWAdcAt/+BJzwyqLo1ssgE854RzH90E544jp42iXA+4r6nXqH\nHwer/t3M/BGjRb3q9GWXwc6dsG4dXFIru+N9ZbvM3tZll8Ho6Mw61bJ92VZnX/u1MWhb9X7jMmC0\nz/RONm3aw+bNf9GjLIF39Giv0r/z3Fb/en3KBvX9rZcVy0//veJntW51XIYdo864vPSl8MpX1tos\nt8XvVfa7336W051tPfml5XFb6dNO2dEnzY6/xz5v+vAeNv/RgDGq71e9L/odb73inT6PZsou+8Jl\n7HxgJycdexK/94udPminTZs2sXnz5qUOQwM4Rvsfx2T/tn37dl796ldDmbftr5q8Alx3ELCsT9lN\nwPrasheUy/t5FOC0005jbGxsAeHsBnZQPKhiDA76JJxRtrO7vBtjbKyYfmAHHD9WzPNIuU5Z75hj\ny+W1+fr0jh3FdL3skUe6169ua/duOPbY3nHsy7Y6+zqojX7bqvcbu4Fj+0zvYGTkp8vxqZfRVW+m\nvUr/znNb/ev1KZur7zvHAMyuWx2XYcdoxw446KAebVb7o7Pf/faznO5s66SDyuO20kan7NgjZ2+r\nxz6PHDHHGNX3q95Gv+OtV7zT59FM2e6/382Oh3dw5OOOXOC5/JNjZGSk9X2wv3OM9j+OyQFjv75d\ntannAL8jIs6MiBMi4hkRcTlwFvCxsvzyiPhIZZX3AydHxBURsSoi3khx28S7mohHkiRJ6qepK8BP\nAj4CHAdMAl8Hzs/ML5blK4HjO5Uz866IeBGwGXgT8E/AxsysPxlCkiRJalQjCXBm/voc5Rf3WLaF\n4p9mSJIkSY+Zxp8DrHYbH3/KUoegOYw/3zHaX4yPjy91CJqDY7T/cUzUBBNgNWp8/MSlDkFzGD/v\nxKUOQSV/ke//HKP9j2OiJpgAS5IkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJklrFBFiSJEmt\nYgIsSZKkVjEBliRJUquYAEuSJKlVTIAlSZLUKibAkiRJahUTYEmSJLWKCbAkSZJaxQRYkiRJrWIC\nLEmSpFYxAZYkSVKrmABLkiSpVRpJgCPibRHxtYh4MCJ2R8SfR8TT5ljn7IjYW3tNRcSTmohJkiRJ\n6qWpK8BnAu8Fngk8HzgE+FxEHDHHegmcCqwsX8dl5n0NxSRJkiTNcnATjWTmC6vzEXERcB+wFvjK\nHKvfn5kPNhGHJEmSNJfFugf48RRXd78/R70AbomI70bE5yLiOYsUjyRJkgQsQgIcEQG8G/hKZn5j\nQNV7gNcBLwdeBtwNfCkizmg6JkmSJKmjkVsgaq4Gng6sG1QpM+8A7qgs+mpEnAJsAi5chLgkSZKk\nZhPgiLgKeCFwZmbes4AmvsYcifOmTZsYGRnpWjY+Ps74+PgCNidJkqSFmJiYYGJiomvZ5OTkEkUz\nP40lwGXy+xLg7Mz8zgKbOYPi1oi+Nm/ezNjY2AKblyRJUhN6XYDctm0ba9euXaKIhtdIAhwRVwPj\nwIuBhyNiRVk0mZmPlnXeAfx0Zl5Yzr8Z2AncBhwOvBZ4HnBeEzFJkiRJvTR1Bfj1FE99+FJt+cXA\nR8vp44DjK2WHAu8ERoE9wNeBczNzS0MxSZIkSbM09RzgOZ8mkZkX1+avBK5sYvuSJEnSsBbrOcCS\nJEnSfskEWJIkSa1iAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJ\nklrFBFiSJEmtYgIsSZKkVjEBliRJUquYAEuSJKlVTIAlSZLUKibAkiRJahUTYEmSJLWKCbAkSZJa\nxQRYkiRJrWICLEmSpFYxAZYkSVKrNJIAR8TbIuJrEfFgROyOiD+PiKcNsd45EbE1Ih6NiDsi4sIm\n4pEkSZL6aeoK8JnAe4FnAs8HDgE+FxFH9FshIk4EPg3cAJwOvAf4QESc11BMkiRJ0iwHN9FIZr6w\nOh8RFwH3AWuBr/RZ7Q3AnZn5lnJ+R0Q8F9gEfL6JuCRJkqS6xboH+PFAAt8fUOdZwPW1ZdcBz16k\nmCRJkqRmrgBXRUQA7wa+kpnfGFB1JbC7tmw3cExEHJaZP2w2svXAamBdMXvs6TNFT38r/LDM1U+4\nAI5ZDU8s63XqA5yyEZYd1b1e5zPE6Ho4YrTc1HpYvRrWrZtd9sRae3t2wTGrZtYbHZ1ZpxpHtY35\nbmvFOTPxLjuyd3v9tlXvN9YDo32mB9XLcvqCWr1Kf7AR2AWs2sdt9Smr9/3k7TCyemafq6p1N26E\n5cvL5oYco864nF45zk6+qJx4M92H/qD9rG1r+rjdyHSfdo7Zo0+aHf+TX9J/n/uNUf0cqPfFrl2w\natXsfe4V7xNnj8P6U9ezevlqTjq2Eq8kSY+hyMy5a82nwYg/AM4H1mXmPQPq7QA+mJlXVJatp7gv\n+Mh6AhwRY8DWs846i5GRka62xsfHGR8fHxDVFLBsZjanIJaVy5kpm14+Rz2milyhU3fvFBxUTk9N\nwbJKG9WyanvV9evrVdepxzXstjrTnXV7lQ3c5x5lXf3Yb3oh9er928S2avOdfclyW4P2OZnpu73M\n9PGgMZprXILKsVSNF+bcz0HHQ69juxp/Z5+D2fV6tVEf8862p8pY5zy2B8c7tXeKZZWy+rwk6cAx\nMTHBxMRE17LJyUm2bNkCsDYzty1JYMPIzMZewFXAt4GnDFH3y8C7assuAh7oU38MyK1bt+bCbMjM\nNZl5aTl/Va1svJi8cUPmZ9Zk3tKpd2mt3ttmZm/cMFOvOr1hQ+aaNZmX9iirtlff1oYN3et0xbFh\nZt1ht9X5ueOqwfHO6psNtelBZcPWG+9Tr96/TWyrX9mAvs8N5at0S6VudVwGjVGvcbnqqh5t1rY1\ncD9r2+qMZc8+7RF/dT+GHaP6ftX7ou+x3SPeHmUbrtmQa96/JjdcU+0DSdJPgq1btybFpZixbDDH\nbPrV2C0QEXEV8BLg7Mz8zhCr3ETxvWjVC8rli2A7cDPF1+NQfNVeLStNbocHbi6+vgVgZ61edtft\nXEGvTm/fDjffXHwFXi+rtlff1vZae11xVLY97LYeKre1Z9fgeGf1TXU/5yobth596tX7t4lt9Ssb\n0PddMTLTd9A9LoPGqNe47NrVo83atgbuZ21bnbHs2ac94n9oPv1L7/2q90XfY7tHvD2O3+33b+fm\ne2vxSpL0GGrqOcBXA68CNgAPR8SK8nV4pc47IuIjldXeD5wcEVdExKqIeCPwCuBdTcQkSZIk9dLU\nUyBeDxwDfAn4buX1ykqd44DjOzOZeRfwIornBt9C8fizjZlZfzKEJEmS1JimngM8ZyKdmRf3WLaF\n4lnBkiRJ0mNisZ4DLEmSJO2XTIAlSZLUKibAkiRJahUTYEmSJLWKCbAkSZJaxQRYkiRJrWICLEmS\npFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIkSWoVE2BJkiS1igmwJEmSWsUEWJIkSa1iAixJkqRW\nMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElqlUYS4Ig4MyKujYhdEbE3Il48R/2zy3rV11REPKmJ\neCRJkqR+mroCfBRwC/BGIIdcJ4FTgZXl67jMvK+heCRJkqSeDm6ikcz8LPBZgIiIeax6f2Y+2EQM\nkiRJ0jCW8h7gAG6JiO9GxOci4jlLGIskSZJaYqkS4HuA1wEvB14G3A18KSLOWKJ4JEmS1BKN3AIx\nX5l5B3BHZdFXI+IUYBNw4VLEJEmSpHZYkgS4j68B6+aqtGnTJkZGRrqWjY+PMz4+vlhxSZIkqWZi\nYoKJiYmuZZOTk0sUzfzsTwnwGRS3Rgy0efNmxsbGHoNwJEmS1E+vC5Dbtm1j7dq1SxTR8BpJgCPi\nKOCpFH/YBnByRJwOfD8z746Iy4HRzLywrP9mYCdwG3A48FrgecB5TcQjSZIk9dPUFeCfA75I8Wzf\nBN5ZLv8I8BqK5/weX6l/aFlnFNgDfB04NzO3NBSPJEmS1FNTzwH+MgOeKJGZF9fmrwSubGLbkiRJ\n0nws5XOAJUmSpMecCbAkSZJaxQRYkiRJrWICLEmSpFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIk\nSWoVE2BJkiS1igmwJEmSWsUEWJIkSa1iAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElq\nFRNgSZIktYoJsCRJklrFBFiSJEmtYgIsSZKkVmkkAY6IMyPi2ojYFRF7I+LFQ6xzTkRsjYhHI+KO\niLiwiVgkSZKkQZq6AnwUcAvwRiDnqhwRJwKfBm4ATgfeA3wgIs5rKB5JkiSpp4ObaCQzPwt8FiAi\nYohV3gDcmZlvKed3RMRzgU3A55uISZIkSeplqe4BfhZwfW3ZdcCzlyAWSZIktUgjV4AXYCWwu7Zs\nN3BMRByWmT9sfpPrgdXAunL+9ErZW4HvF5MnXADHrIYnduqtq9TbSHG3R+npb2X6M8ToejhitNzU\neli9Gtatm11Wbe+UjbBnFxyzama90dGZdbriWA+Mzm5v0LY66644ZybeZUf2iKneN5VtzVk2bL3O\nnTEX1OrV+3cXsGoft9WvrNb3k7fDyOpKvYonVupu3AjLl5fVBoxRr3E5vXKcnXxROfFmug//QftZ\n29axnfY2MrtPT5od/5NfUtnORuD2sm6vbZXt1c+Bel/s2gWrVs3e517x9jh+15+6ntXLV3PSsZV4\nJUl6DEXmnLfszq/BiL3ASzPz2gF1dgAfzMwrKsvWU9wXfGSvBDgixoCtZ511FiMjI11l4+PjjI+P\n941pau8Uyw5aNmt+au8UwHTZ/OoBlHVzCqKc3jsFlTa6yqanp4pcIyr1qutV1xnUfn1bvco66/Zr\nv74tpmb2q2u6e77aV/V+y5wiYu56/fq33ka/7dbnu9fpV9aj77v6YAr2MtN3e4FlQ/T9oGNg7xQE\nM2Pf1b/Qu78r04OOh15jNL1/nbL6dgZsq95eZ1tZttEvjkHHVGV+0PhJkg4sExMTTExMdC2bnJxk\ny5YtAGszc9uSBDaMzGz0RZEyvHiOOl8G3lVbdhHwwIB1xoDcunVrLsSGazbkmvevyUtvuDQzM6/6\nu6u6ysY/Od6zXudnp+xt17+t2mpmXjp7+sYNmZ9Zk3lLj7KcaW9WvRs31NZZ07/9W/psq1rW+bnj\nqj7tD7GtAWUbrtkw3T/1fqvXG7Z/+7VRb69arxpHPd7usgF9nxvKV3b3XWbmhg2Zlw7R972OgR0z\nx1nfbfU7jnqOw1WV+fFaWbXNHvs85zjX2xu23+Z/3Kx5/5rccE01XknST4KtW7cmxaWYsWw4x2zy\ntVS3QNzErO+ceUG5fFFsv387N997M6uXF1//7vrBrq6yfvV2PrCzqyy7rphvZ+Zr6Mr05HZ44Obi\nK+BZ9Wbam1Vvcjtktb2bmfm6utZ+9tlWteyhclt7dvVpf4htDSir9ke93+r1pntsjv7t10a9vWq9\n7nHpjre7bEDfMxMjMNN3ANtr/d2v73sdA3tmjrOZNmvb6ncc9RyHXZV5amVVO2s/q/X69W+/enP1\n2/yOqc74SZK0VJp6DvBREXF6RJxRLjq5nD++LL88Ij5SWeX9ZZ0rImJVRLwReAXwribikSRJkvpp\n6ikQP0dxuWcrxWWedwLbgN8py1cCx3cqZ+ZdwIuA51M8P3gTsDEz60+GkCRJkhrV1HOAv8yAZDoz\nL+6xbAuwtontS5IkScNaqucAS5IkSUvCBFiSJEmtYgIsSZKkVjEBliRJUquYAEuSJKlVTIAlSZLU\nKibAkiRJahUTYEmSJLWKCbAkSZJaxQRYkiRJrWICLEmSpFYxAZYkSVKrmABLkiSpVUyAJUmS1Com\nwJIkSWoVE2BJkiS1igmwJEmSWsUEWJIkSa1iAixJkqRWaTQBjohLImJnRDwSEV+NiJ8fUPfsiNhb\ne01FxJOajEmSJEmqaiwBjohfBd4JvB1YA9wKXBcRyweslsCpwMrydVxm3tdUTJIkSVJdk1eANwF/\nmJkfzczbgdcDe4DXzLHe/Zl5X+fVYDySJEnSLI0kwBFxCLAWuKGzLDMTuB549qBVgVsi4rsR8bmI\neE4T8UiSJEn9NHUFeDmwDNhdW76b4taGXu4BXge8HHgZcDfwpYg4o6GYJEmSpFkOXqoNZ+YdwB2V\nRV+NiFMobqW4sN96mzZtYmRkpGvZ+Pg44+PjixKnJEmSZpuYmGBiYqJr2eTk5BJFMz9NJcDfA6aA\nFbXlK4B759HO14B1gyps3ryZsbGx+UUnSZKkRvW6ALlt2zbWrl27RBENr5FbIDLzx8BW4NzOsoiI\ncv5v59HUGRS3RkiSJEmLoslbIN4FfDgitlJcyd0EHAl8GCAiLgdGM/PCcv7NwE7gNuBw4LXA84Dz\nGoxJkiRJ6tJYApyZnyif+fu7FLc+3AKcn5n3l1VWAsdXVjmU4rnBoxSPS/s6cG5mbmkqJkmSJKmu\n0T+Cy8yrgav7lF1cm78SuLLJ7UuSJElzafRfIUuSJEn7OxNgSZIktYoJsCRJklrFBFiSJEmtYgIs\nSZKkVjEBliRJUquYAEuSJKlVTIAlSZLUKibAkiRJahUTYEmSJLWKCbAkSZJaxQRYkiRJrWICLEmS\npFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIkSWoVE2BJkiS1igmwJEmSWqXRBDgiLomInRHxSER8\nNSJ+fo7650TE1oh4NCLuiIgLm4xHj727vnLXUoegOUxM3LXUIag0MTGx1CFoDo7R/scxURMaS4Aj\n4leBdwJvB9YAtwLXRcTyPvVPBD4N3ACcDrwH+EBEnNdUTHrsfecr31nqEDSHiQnHaH/hL/L9n2O0\n/3FM1IQmrwBvAv4wMz+ambcDrwf2AK/pU/8NwJ2Z+ZbM3JGZ7wM+WbYjSZIkLYpGEuCIOARYS3E1\nF4DMTOB64Nl9VntWWV513YD6kiRJ0j5r6grwcmAZsLu2fDewss86K/vUPyYiDmsoLkmSJKnLwUsd\nwDwcDrB9+/YFrbziBytY9aNVHHH/EWzbto29u/aybdu26bLMZNu2bbPqdX526h2eh0/PwwrgCGBb\n9/S9K+ChVXDQEbCtVjb9k9n17l0Bk9V1VvVvf7KyTr827i5/fnsv7O3VfrXtPtsaULbiBys4Io/o\n6rc9P9xT9k93vWH7t1rWr3/r9apx1OPtLhvQ96wox7Qs7/QdwIoVcESP/h00zp2ynWXfd7VZ21a/\n46jnOOytzGetbEWlzR7HW1lvcnL2GPVub4hjds54+4/fih+sqJxL7TQ5Odn6PtjfOUb7H8dk/1bJ\n0w5fyjjmEsWdCvvYSHELxB7g5Zl5bWX5h4GRzPyVHut8Gdiamb9ZWXYRsDkzj+1RfwPwJ/scrCRJ\nkhbbqzLz40sdRD+NXAHOzB9HxFbgXOBagIiIcv73+6x2E7C+tuwF5fJergNeBdwFPLqPIUuSJKl5\nhwMnUuRt+61GrgADRMQrgQ9TPP3haxRPc3gFsDoz74+Iy4HRzLywrH8i8A/A1cAHKZLldwMvzMz6\nH8dJkiRJjWjsHuDM/ET5zN/fpbjh7xbg/My8v6yyEji+Uv+uiHgRsBl4E/BPwEaTX0mSJC2mxq4A\nS5IkSQeCRv8VsiRJkrS/MwGWDkARcXZE7I2IY5Y6FkmSDjTzSoAj4kPlL92piPhRRNwbEZ+LiIvL\npz4smoj4lXJb90XEZET8bUS8oEe9fxMR2yPikYi4NSLW18rPjIhrI2JXuS8vHrCf1ddnFnP/FkO5\nH59axPaPjYjfj4jbI2JPRHw7It5TT8rKen9SjtsDEfGBiDiqVuc9EfG/IuLRiOj7gMeI+K2I2FHW\nuzsi3rZY+7eYaufS3sr0yfNoZl73Lzles0XEh8u+v7pH2fvKsg8uwnYdiwWIiGeVY/JXi7ydn42I\nj0fEd8rxuS0i3tSn3pby9823I+I/1MpXluO3ozy/39VneyPl8fbdcnxuj4hfWqz92xdLdc7UtvOY\n5QNlvdMi4i8j4v9ExEMR8XcR8eTF2r+mxeLnAgdHxBUR8fWyf3ZFxEci4rhavcPKY+R7EfGDiPhk\nRDypVufSiLgxIh6OiO8P2OZF5Zg+EkUu+t75xr2QK8B/TfEHbScAvwR8AXgP8FcRsZhXlM8CPkfx\n6LQx4IvlNk/vVIiI5wAfB/4IOAP4S+AvIuLplXaOovgDvTcyOIH4a4o/5ltZvsYb25OfHKPAccBv\nAj8DXEhxTHygVu/jwGkUT/p4EcVY/mGtTgL/HfjTfhuLiN8HXlNubxXwYoonjhyoOudS53UcsHMR\nt+d4zZbAd4ALovIfKMvpceDb+7qBiOj1x8aOxcJspIj7/Ijo919GhxYRB0X0vHizluI/k74KeDrw\ne8DlEfHGyrqPo3jM006K30n/AfhPEfHrlXYOA+4D/l+K3zu9YjgEuB54CvAy4GnAa4Fd+7Rzi2fR\nz5khPGb5QEScAvwN8I1yu/+KYjx9HOuMIyn6+HeANcCvULzP/GWt3rsp3sdeTtGXo8A1tTqHAJ8A\n/qDfxiLiNynG4B0U5+fzWcgj1zJz6BfwIeBTPZY/j+JfVL2msmyE4s38PmCS4gT/2dp6v0zxZvYI\ncD9wzTzj+d/A/12Z/1Pg2lqdm4Cr+6y/F3jxsPt5oL2q+wGcT3ESPwB8D/gr4ORK3RPK/vgVig81\nD1O8MTxrntt8RTmeB5Xzq8t211TqnA/8C7Cyx/pvB7b1WH4a8CPgqUvdr02PTZ/yAN4G3EnxT2Zu\npvhHM53ys4Ep4IXArWWf3wT8jOM173H487IPxyvLx8s+/xTwwVpfDHMevRL4Ujl2/9axaGSsjgIe\nBH6xHIP/WCs/u+yvvucExQeNByh+99xW9stThtz+VcD1lfk3lMfAwZVllwPf6LP+F4F39Vj+euAf\ngWVL3cdD9kPT58wNwHtr21gO/BB43jziWqx8YAL4yFL3ewNj1skFdgJvqpXfDPx2rS82lmP5MHAH\n8Mvz3ObPUfyOenI5f0w5pr9SqdP596a/0GP9C4Hv91j++DKmc/a1Xxq5YpuZX6Q4GV5WWfxJ4Kco\nDv4xiv+Hen1EPB4gikegfQr4NMUnh3OArw67zfJT++OA6iXyZ1Mk2lXXlcvn65yI2F1+FXV1RDxh\nAW3sT44C3kkxFr9IcWD+eY96/xn4r8DpFAf9x+d5Zf/xwIOZubecfzbwQGbeXKlzPcWn7WfOo91/\nDXwLeHFE3BkROyPijyJi1n8N/AlxKfBq4DcoPuFuBv44Is6s1AmKsdpE8WZzP3BtRCybx3Ycr2Lf\nPkhxhbTjNRS/NOpXB4c9jy6nGLPTGP7KhGMx2K8CuzPzCxQXVzb2qTfXOXEk8JZy/Z+huEgzjBG6\nf988C9iSmf9SWXYdsCoiRoZsE4pk/Cbg6vKr3H+IiLct8jeq+6rJc+YDwHh5Jbzj14B/KnOLOS1W\nPlC2+yLgHyPis2VO8NWIeMmwbRzAfpviQ8S/Aj4D/EknfxvS4ymOk/9Tzq+lePTuDZ0KmbmD4tuE\n+eRo51EcY8dHxDeiuJ3rfyzklpQmT7DbKf7zBxHxXIo3n1dm5s2Z+a3MfAvFleBXlPUvBT6emb+b\nmTsy87bMvHIe2/sPFCfWJyrLVlJ8bVW1u1w+H38N/FuKk/UtFFcWPtPnq7IDQmZ+KjP/IjN3ZubX\ngV8H/lXt6yCAKzPzs5n5TYorSicATx1mG1E8B/r/pvsr2pXUfsFk5hTFG9V8xuVkiuPrFRSJ4YUU\nJ9SfzaON/c0vl/dBdV7/AyAiDqW4+vuazLw+M+/KzI9S/Cvw19Xa+E+Z+YXMvI2iT1ZSXMWfk+PV\n5U+A50bE8RFxAvAc4GP1SvM4jzZn5l9m5rczs/6eNItjMZTXUNzqAcX7/vKIOKtHvbnOiYOBN2Tm\nVzPzHzNzzq+yy6/TX8ns8en1+6ZTNqyTgX9D8ft4PcWz9P8v4LJ5tLEUmjpnPkWR0FSTygspkulh\nLVY+8CTgaOCtFEngeRTJ+6dqFyN+En0oMz+RmXdS5GtHA78wzIrl7TD/hSLHe6hcvBL4UWY+WKs+\n3zE5GVhG8TvyTRS3UzwB+Hyf2836auwfYVAcwJ17aH6W8tNYLWc8nCJ4KK76/n8L2lDEBuD/ofi6\n4nsLinaAzKyeRLdFxD9QXEE5h+JrrANORJxKcX/OMym+XjqIYryeQnFvU8c/VKbvoRjXJ1FcDR7U\n/uOA/0nxNdTvNBb4jIOAQ4Ffy8xvldvcCGyNiFMz8x8XYZuL7QsUX392TpKHy59PpbhK9fnah65D\nKL5J6UgLqXZkAAAgAElEQVQq35pk5gMRsYPiquNAjle3zPxeRHwauJhiPP5nZtbfv4iIp1IkKHOd\nR1uH3bZjMbeIWEXxy/cVAJn5SET8KcVV3C2VqsOcEz/KzP89j20/A/gLisT6hrnqL8BBFEnAb2Tx\nHe/N5dWs36K4z3G/1NQ5k5k/jIg/pviA88mIGKO4Mv/Lw8SxyPlA5yLhX2Tm75fTXy8/EL2e4taO\nn1TTuUBm7omIBylygYHKJPTPKMb4jXNUX4iDKHLXf985HyNiHLiX4nbczw/bUJMJ8GnM/AHP0cB3\nKa6c1q+adi6HP7KQjUTEBRSJ8yt6fD1yL8UfrlWtKJcvWGbujIjvUSQmB2QCTHHf1U6KT9/fpTiI\nbqP4xVj148p05wPNwG8KIuJoiq+W/g/wsvIqVce91E6a8uvIJzC/cbkH+JfOL/DS9vLnUyjuoTvQ\nPJyZvf7o7ejy5wspxqrqh/u6Ucerrw9R3Oc56I370wx3Hj3MEByLoW2kuOpzdy3B+mFE/LvM/ME8\n2hr6d095lfJ64P2ZeXmtuN/vm07ZsO6hSMqrf4S1HVgZEQfXbrHY3zR1znyAIvEfpUiov5CZd8+1\n8ccgH/gexT3322vLtwPr5tHO/mQvs/OyQ3rU+3FtPpk7F+gkv8cDv1i5+gtFvx8aEcfUrgLPd0zu\nKX9Oj0n5Yex7FO9nQ2vkFoiI+EWK+0Q+WS7aRnFJeyoz76y9OvfofJ3iL5vns51xiq/ALsjMz/ao\nclOPNs8rly9Y+Wn8p5jp+ANKef/y04D/nJlfLO+7+akeVXv+FewcbT+O4q9xH6H4BP6jWpWbgMdH\nxJrKsnMpTsC/m8embgQOjoiTKstWlTE/Fn91/Fj6BkWie0KP86f6l+FBcR9iMVPc0/k0Zr9ZU6nj\nePX3WYpfygdT9FGXps8jx2I4ZdL/axRPsDi99vou3U/o6XdOVK/OD7vdn6H4luZDmfnbParcBJxV\nu7/4BcCOzJycx6ZuZPZtZquAe/bz5BcaOmfKK/L/i+JvHjq/5wd6LPKBzPwx8PcU41H1NA6Ac6eP\n+ymeQANAFI9ePKl/9eFUkt+TgXMz84Fala0UHybOrayziiJpnU+OdmP5c3pMyuNsOfMck4VcAT4s\nIlZQfBpfQXHP0n8ErgX+GCAzr4+ImygeOfJWiq/Pf5riitanMnMbxVd910fEnRQ3Wh8CrM/M/9pr\no+XXHB+muOfj78sYAB6pfJp4D/ClKB6R8T8pTqS1FI+U6bRzFMWbTecT0MlRPDrl+5l5d1n+dopH\nc9xb1r2i3If5P2Zj//AA8M/Ab0TEvRT39V7O7F/U87rHufwF/nmKW1teRfHLulN8f2buzczbI+I6\n4I8i4g0Ub5bvBSYy895KW6dQ3DZzHHBEzDzO5hvlm9D1FB+sPhgRmyiOv6uAz5X3K//EyMyHIuK/\nAZvLX65fofgDnHXAZGb+caX6b0fxrMT7KB7VdD/F17WzOF6DZebeiFhdTvdKYhs7jxyLefllij+o\n+WD9Sm8Uzzb9dbpvp+t1TtQfxzRQedvDFyj+HuTdld83U5Wv2T9O8YdCH4yIKyguAr0JeHOtrdMp\njomjgSeW8z/KzM4H1T8ALoniUXXvpUiu3kbxyKj9WoPnDBTJ7FXAQ/R5D+t4rPKBctmVwJ9GxN9Q\nfAO8nuIPS88eFON+7AvAheXtK5MUudg+fdAqk99rKG5t/dfAIZUx+X5m/jgzH4yI/w68KyIeAH4A\n/D5wY2Z+rdLW8RTfcp0ALKu8n30zMx/OzH+MiGuB90TE68p2Lqf4kDu/b+hz/o/SmCpfP6RIEK+j\nx2N+KG5IfzdwN8Xz8u4CPgr8dKXOSyk+FTxCcQ/Unw3Y9hcr266+Plir93KKP8h7hOIq8/m18s6j\ncnq2Q/EL6bPlvj1K8RiqPwCeOJ++2h9ewEeAT5TT51LcY9h5pNaZ5X6/uCw/oZz/2cr6I+Wys/q0\nf3aPfuz07VMq9R5P8ccRkxRviH8EHDnk+FbbWUnxCXOS4srPB4DHL3U/L3Bsph9LM6DOv6c4qR8t\nj8fPAM+t9f0LKe7VegT4W+AZA9pzvOY5DhR/8FJ9pNMvzvc8ciz2eYyupfY4q0rZz5f7+oxhzgn6\nPFqpR7tv79Ond9bqPQP4cnk8fAf4rR5t9fp9U2/nmWWseyhuSXkrEEvd94/FOVOpdxRF8vv7Q8Tw\nmOQDlXoXUVwEe5jiw+S/XupxmOeYVXOBx1F8eHuAIi/7tXKfqo9B6zU+36fPIx2Zed/r9X52VqXe\nYRQf8r5Hkbj+GfCkHsdXr7GttnM0xXvhP1N8wP0zKrnlsK8oG9NPoIj4a+AfM3PWfzCSpJ8kEXE2\nxdWtY3P2X5prPxcRJwLfBNZm5q1LG81PFnOB3vbn5wxqgSLi8RHR+Ypm6L+IlKQD3AH7qMq2iuLf\n6K6keAb9TSa/zTEXGKzJp0Bo//FBiucw/7fM/KulDkaSHiN+pXngWUdxS8PtFM9DVnPMBQbwFghJ\nkiS1irdASJIkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJklrFBFiSJEmtYgIsSZKkVjEBliRJ\nUquYAEuSJKlVTIAlSZLUKo0kwBHx+oi4NSImy9ffRsQvzbHOORGxNSIejYg7IuLCJmKRJEmSBmnq\nCvDdwFuBMWAt8AXg2oh4eq/KEXEi8GngBuB04D3AByLivIbikSRJknqKzFychiP+GfitzPxQj7Ir\ngPWZ+bOVZRPASGa+cFECkiRJkliEe4Aj4qCIuAA4DPibPtWeBVxfW3Yd8Oym45EkSZKqDm6qoYh4\nBnATcDiwB3hlZn6zT/WVwO7ast3AMRFxWGb+sEf7PwWcD9wFPNpU3JIkSWrM4cCJwHWZ+c9LHEtf\njSXAwO0U9/OOAK8A/jQizs7Mmxtq/3zgTxpqS5IkSYvnVcDHlzqIfhpLgDPzX4A7y9mbI+IXgDcA\nv9Gj+r3AitqyFcCDva7+lu4C+NjHPsZpp5227wEf4O687E4OHT2UJ1/y5K5pbr0MHtoJT1wHT7uk\nrP0+4JKiLBPOeMfserdeVlQ9/feKn7deBkeMFmV3vG+mrVsvg8efDie8sncbR4yy6Q++yebNRwI7\ngXXFtrkMGJ09PVccFe/72vu45BeKOC77wmVkJu849x1c9oXL2PnATtYdv45LfuESLvvCZYwePTo9\nvZCyIkaAShydfujVN73KZu3zoP4Yoq9q9er7Mt/++OZEZ5z6jMWsbVf6o35MdPa5zzHRs6zazmWX\nwegoXDK4P+r7ttC+6m5j5tjqPgaYtd7wx86A47vPOdK3jXkcO/t6TMzedvne0RnnWcdH7RyZNuDY\nqY3ncHFUpgedcwOP4cq+cBmQwDt6xDszv2nTN9l80ZGz23/yS4v3wCGPv9nvC/M/33vu59En9Xyv\nXJB6+9Bs20MfO3PbtGkTmzdvbiY2NW779u28+tWvhjJv2181eQW47iBgWZ+ym4D1tWUvKJf38yjA\naaedxtjY2L5Hd4Dbu3svRx57JE8fe3rXNLt3wwM74PgxmO6nR4CxogyK5fV61TIo5o85tph/5JHu\n5ccdBGf0aeOYYxkZGWFs7J+AHcV2GaO4w+XY2dNzxVHxyM5Hpsd+99/vLquNsfvvd7Pj4R2MPXFs\nev7Y5cfuU9nMHTqVODr90KtvepXN2udB/TFEX9Xq1fdlvv0xM059xmLWtiv9UT8mOvvc55joWVbv\n02Or/da7P+r7ttC+6m5j5tjqPgaYtd7wx86A47vPOdK3jXkcO/t6TMze9iMzY97z+KidI9MGHDu1\n8Rwujsr0oHNu4DH8SC2G3jFV50dGRhhb+U+z2z+pfA8c8vib/b4w//O9534ee2TP98oFqbcPzbY9\n9LEzt+L8MQ84AOzXt6s29Rzgd0TEmRFxQkQ8IyIuB84CPlaWXx4RH6ms8n7g5Ii4IiJWRcQbKW6b\neFcT8UiSJEn9NHUF+EnAR4DjgEng68D5mfnFsnwlcHyncmbeFREvAjYDbwL+CdiYmfUnQ0iSJEmN\naiQBzsxfn6P84h7LtlD80wxJkiTpMdP4c4Cl8fHxpQ5BQ3Cclo59f2BwnPZPjouaYAKsxvnmdGBw\nnJaOfX9gcJz2T46LmmACLEmSpFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIkSWoVE2BJkiS1igmw\nJEmSWsUEWJIkSa1iAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJ\nklrFBFiSJEmtYgIsSZKkVmkkAY6It0XE1yLiwYjYHRF/HhFPm2OdsyNib+01FRFPaiImSZIkqZem\nrgCfCbwXeCbwfOAQ4HMRccQc6yVwKrCyfB2Xmfc1FJMkSZI0y8FNNJKZL6zOR8RFwH3AWuArc6x+\nf2Y+2EQckiRJ0lwW6x7gx1Nc3f3+HPUCuCUivhsRn4uI5yxSPJIkSRKwCAlwRATwbuArmfmNAVXv\nAV4HvBx4GXA38KWIOKPpmCRJkqSORm6BqLkaeDqwblClzLwDuKOy6KsRcQqwCbhwEeKSJEmSmk2A\nI+Iq4IXAmZl5zwKa+BpzJM6bNm1iZGSka9n4+Djj4+ML2JwkSZIWYmJigomJia5lk5OTSxTN/DSW\nAJfJ70uAszPzOwts5gyKWyP62rx5M2NjYwtsXpIkSU3odQFy27ZtrF27dokiGl4jCXBEXA2MAy8G\nHo6IFWXRZGY+WtZ5B/DTmXlhOf9mYCdwG3A48FrgecB5TcQkSZIk9dLUFeDXUzz14Uu15RcDHy2n\njwOOr5QdCrwTGAX2AF8Hzs3MLQ3FJEmSJM3S1HOA53yaRGZeXJu/Eriyie1LkiRJw1qs5wBLkiRJ\n+yUTYEmSJLWKCbAkSZJaxQRYkiRJrWICLEmSpFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIkSWoV\nE2BJkiS1igmwJEmSWsUEWJIkSa1iAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElqFRNg\nSZIktYoJsCRJklrFBFiSJEmt0kgCHBFvi4ivRcSDEbE7Iv48Ip42xHrnRMTWiHg0Iu6IiAubiEeS\nJEnqp6krwGcC7wWeCTwfOAT4XEQc0W+FiDgR+DRwA3A68B7gAxFxXkMxSZIkSbMc3EQjmfnC6nxE\nXATcB6wFvtJntTcAd2bmW8r5HRHxXGAT8Pkm4pIkSZLqFuse4McDCXx/QJ1nAdfXll0HPHuRYpIk\nSZKauQJcFREBvBv4SmZ+Y0DVlcDu2rLdwDERcVhm/rDp2H6SPGH9Ezh09NBZ04yuh2NWwxPXVWq/\npPhxykb48UPF9AkXdNcbXd+9gdH1cMRoMV1t65SN8LhTZ6b37IJjVs1eh/XAamBdZX509nQ93noc\n1b1Y9ZLp6Y1rNpIkABc84wJWL1/NuuOLNtafup7Ro0enpxdSVsRY04nxlI1w8NHdy55cxnbCBXDw\nUX32eVB/DNFXtXr1fcmcf38MHItZ2+7RF53+OGx57zaqx0SvY/Pki4qfb35zpfH+/TEw/nn0VXcb\nM8fWxjUbOfrQo6eXb1yzkeVHLp+zjdnHzoDju8850reNeRw7jR8T1MZ58nYYWV2p189G4Og+9brH\nc7g4KtP1c656Pg48hqvviRuhfP+YmV/ee77XMXzs6T3WGzBmC3lvHNRGJ46jT6Ix9fab1HNcpKUV\nnTfIxhqM+APgfGBdZt4zoN4O4IOZeUVl2XqK+4KPrCfAETEGbD3rrLMYGRnpamt8fJzx8fEG92L/\nllNJLIvZ0zlFxLJqRQiAZeV0p2yqWNaxdwoOWjYzDd3zBy2Dqani+4JYNnud6W0tmz09a3uV6Xob\n9TgOmllnai8sO2gZU2V8y8qyqb1T09P1+YWWdfdjGW8nnuq+zVpW2bdB/d2vPwaWddfrty/z2eeu\n/ayPWd/4p2Avlf2mzz4z+JioHmdDHjsD419AX3XmYebYqtbrLB+mjV5lvc/H3vs55/HXoz8W45jo\nVVb8ZOhtDeq3Yfu0bx/Uz7lBx07PY7g87rr6cMD8vNvvsS97p4iD+rQx5Pne9X44MI4F6vfeW3+P\nXoih31t0IJqYmGBiYqJr2eTkJFu2bAFYm5nbliSwYWRmYy/gKuDbwFOGqPtl4F21ZRcBD/SpPwbk\n1q1bU5m3bbgtv3Xpt2ZNZ27IzDWZeWmldjl944bMr4z3rnfjhuLVceOGzFvKslsu7V6+46qZ6c+s\nqZRvqGy3Hke97NLebdTjqO7FDTNxbLhmQ45/cnx6es3710yXb7hmQ9f0QsqKGGtxVGOs903fPhi2\nP4boq1q9+r4spD/mF0elP6rHxIL3M3v36YA25o5/uL7qbiP7HAOz1xv2uOqOb7j+mL3evrXRxDlS\n7Yte29pwTe9ztd5+td6wfdp3bOvn3JDHTvd74obMHK/NDzqm6+1fNbDerH0ZOsYB496zjd79vyDV\n9ge8Dy9Mr31usn3tT7Zu3ZoUX7GMZYM5ZtOvxm6BiIirKL5rPzszvzPEKjcx+3uQF5TLNYc92/dM\nf4NXnYbtwM0UX5117Cx+TG6vLKvV6yor5zvfDjy0s3v5kSfMTD9wc/HV1nSbWZmuxlEvy95t1OOo\n7sUDM3Fsv3971/TN997M6uWrp+ezjH2hZUWMNQ9V+rHeN9N91Gc/5+yPIfqqVq++Lwvpj/nF0aMv\n9mk/6d2nA9qYO/7h+qq7jZljq/sYmL3esMdVDrEvg2JcaJ82cUxUy6rnXK9t9dMvjkF9NfTY1s+5\nIY+d6ffB6eXU5rPPfK/+3jWw3qx9GTrGAePes40GVdsf8D68ML32WVpaTT0H+GrgVcAG4OGIWFG+\nDq/UeUdEfKSy2vuBkyPiiohYFRFvBF4BvKuJmCRJkqRemnoKxOuBY4AvAd+tvF5ZqXMccHxnJjPv\nAl5E8dzgWygef7YxM+tPhpAkSZIa09RzgOdMpDPz4h7LtlA8K1iSJEl6TCzWc4AlSZKk/ZIJsCRJ\nklrFBFiSJEmtYgIsSZKkVjEBliRJUquYAEuSJKlVTIAlSZLUKibAkiRJahUTYEmSJLWKCbAkSZJa\nxQRYkiRJrWICLEmSpFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIkSWoVE2BJkiS1igmwJEmSWsUE\nWJIkSa3SSAIcEWdGxLURsSsi9kbEi+eof3ZZr/qaiognNRGPJEmS1E9TV4CPAm4B3gjkkOskcCqw\nsnwdl5n3NRSPJEmS1NPBTTSSmZ8FPgsQETGPVe/PzAebiEGSJEkaxlLeAxzALRHx3Yj4XEQ8Zwlj\nkSRJUkssVQJ8D/A64OXAy4C7gS9FxBlLFI8kSZJaopFbIOYrM+8A7qgs+mpEnAJsAi4ctO6mTZsY\nGRnpWjY+Ps74+HjjcUqSJKm3iYkJJiYmupZNTk4uUTTzsyQJcB9fA9bNVWnz5s2MjY09BuFIkiSp\nn14XILdt28batWuXKKLh7U/PAT6D4tYISZIkadE0cgU4Io4Cnkrxh20AJ0fE6cD3M/PuiLgcGM3M\nC8v6bwZ2ArcBhwOvBZ4HnNdEPJIkSVI/Td0C8XPAFyme7ZvAO8vlHwFeQ/Gc3+Mr9Q8t64wCe4Cv\nA+dm5paG4pEkSZJ6auo5wF9mwO0UmXlxbf5K4Momti1JkiTNx/50D7AkSZK06EyAJUmS1ComwJIk\nSWoVE2BJkiS1igmwJEmSWsUEWJIkSa1iAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElq\nFRNgSZIktYoJsCRJklrFBFiSJEmtYgIsSZKkVjEBliRJUquYAEuSJKlVTIAlSZLUKibAkiRJapVG\nEuCIODMiro2IXRGxNyJePMQ650TE1oh4NCLuiIgLm4hFkiRJGqSpK8BHAbcAbwRyrsoRcSLwaeAG\n4HTgPcAHIuK8huKRJEmSejq4iUYy87PAZwEiIoZY5Q3AnZn5lnJ+R0Q8F9gEfL6JmCRJkqReluoe\n4GcB19eWXQc8ewlikSRJUossVQK8EthdW7YbOCYiDluCeCRJktQSjdwCocfeE9Y/gUNHD501DeuB\n1cC6Su2XFD9O2Qg/fqhcdkF3vdH13RsYXQ9HjBbTT6y0dcpGeNypM9N7dsExqyrbHu0TR71sdGY7\nx6ye2UY9juperHrJ9PTGNRvJ8nbzC55xAauXr2bd8UUb609dz+jRo9PTCykrYqzpxHjKRjj46O5l\nT+7EdgHFLfHz7Y8h+qpWr74vmfPvj/nF0aMvANgILF/AfgInX1T8XPXmSnv925g7/uH6qruNmWNr\n45qNHH3o0TN7tmYjy49cPmcb9bLu42i4/pi93r61sdBjolpW7aNinG8vt1fU66faj/V6/fpq6LGt\nn3PV83FgX9X3JWvzy/vM9+rv0wfWm7Uv1ffTBZ7vvds4icZU2x/wPrwwvfZZWlrReYNsrMGIvcBL\nM/PaAXW+DGzNzN+sLLsI2JyZx/ZZZwzYetZZZzEyMtJVNj4+zvj4eBPhHxByKollMXs6p4hYNl1v\nau8UAMsOWgY5BdNlU8CyaoMzZVmsMz2/dwo661eXd7VXn6+13zVfme603S+OSntTe4v96Nqnch+X\nHdS9z02WTU9Px1OJv7OsV1lletC26mO2kDaa3ufBMU4VecP0/jLvGKfbhFr/NTxmQ7YBM8dWtV5n\n+WMVx/7aRr0v5trWoH7b1z6dORY758b8zpFeMcw1v6/93XX+1N83hzzfh29jgfq9987a1vzN5/jT\ngWdiYoKJiYmuZZOTk2zZsgVgbWZuW5LAhpGZjb6AvcCL56jzX4Bba8s+DnxmwDpjQG7dujWVeduG\n2/Jbl35r1nTmhsxck5mXTte99IZy+sYNmV8Z71NvQ/nKynxZdstMW3njhswdVw1o49IhyyoxfWZN\nZRv1OGZM70dmbrhmQ45/cnx6es3710yXb7hmQ9f0Qss2XFOPo9e+1Puo934O2tawfTVXvIvZH7PH\npXJMzCPGallXv924YXp6X+JfyH5mZp99Xnj7TRx/TbSxr8dEvS/q9WafI737rVpvX/t01rk35PHX\n7/1jdvtzx3jV3101r31ZyHvjcG307v+FqbffXNvzOXZ04Nu6dWtSXCoZy4ZzzCZfjdwCERFHAU8F\nOk+AODkiTge+n5l3R8TlwGhmdp71+37gkoi4AvggcC7wCuCFTcTTBnu275n+Bq86DduBm+l8TQmw\n84GdxcTk9koL9XrVss582ehDO2cWT26HI08Y0EYOWZYz7T1wc3EbRM84ZkzvB7D9/u1d0zffezOr\nl6+ens/ym419KesRQY99KZc91KtsZnrQtobtq2HjXYz+mD0ulWNiHjFWy7r6bXI7NDBmC9lPmDm2\nuvd54e03cfw1eQwvtI36OVev18+gc2lf+3Tm2Jt9zs1nX/rFNEyMu36wa177spD3xuHaaFK9/QZb\nnsexIz1WmvojuJ+jOBu3UpxB7wS2Ab9Tlq8Eju9Uzsy7gBcBz6d4fvAmYGNm1p8MIUmSJDWqqecA\nf5kByXRmXtxj2RZgbRPblyRJkoa1VI9BkyRJkpaECbAkSZJaxQRYkiRJrWICLEmSpFYxAZYkSVKr\nmABLkiSpVUyAJUmS1ComwJIkSWoVE2BJkiS1igmwJEmSWsUEWJIkSa1iAixJkqRWMQGWJElSq5gA\nS5IkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJklrFBFiSJEmtYgIsSZKkVmk0AY6ISyJiZ0Q8\nEhFfjYifH1D37IjYW3tNRcSTmoxJkiRJqmosAY6IXwXeCbwdWAPcClwXEcsHrJbAqcDK8nVcZt7X\nVEySJElSXZNXgDcBf5iZH83M24HXA3uA18yx3v2ZeV/n1WA8kiRJ0iyNJMARcQiwFrihsywzE7ge\nePagVYFbIuK7EfG5iHhOE/FIkiRJ/TR1BXg5sAzYXVu+m+LWhl7uAV4HvBx4GXA38KWIOKOhmCRJ\nkqRZDl6qDWfmHcAdlUVfjYhTKG6luLDfeps2bWJkZKRr2fj4OOPj44sSpyRJkmabmJhgYmKia9nk\n5OQSRTM/TSXA3wOmgBW15SuAe+fRzteAdYMqbN68mbGxsflFJ0mSpEb1ugC5bds21q5du0QRDa+R\nWyAy88fAVuDczrKIiHL+b+fR1BkUt0ZIkiRJi6LJWyDeBXw4IrZSXMndBBwJfBggIi4HRjPzwnL+\nzcBO4DbgcOC1wPOA8xqMSZIkSerSWAKcmZ8on/n7uxS3PtwCnJ+Z95dVVgLHV1Y5lOK5waMUj0v7\nOnBuZm5pKiZJkiSprtE/gsvMq4Gr+5RdXJu/Eriyye1LkiRJc2n0XyFLkiRJ+zsTYEmSJLWKCbAk\nSZJaxQRYkiRJrWICLEmSpFYxAZYkSVKrmABLkiSpVUyAJUmS1ComwJIkSWoVE2BJkiS1igmwJEmS\nWsUEWJIkSa1iAixJkqRWMQGWJElSq5gAS5IkqVVMgCVJktQqJsCSJElqFRNgSZIktYoJsCRJklql\n0QQ4Ii6JiJ0R8UhEfDUifn6O+udExNaIeDQi7oiIC5uMR0tjYmJiqUPQEBynpWPfHxgcp/2T46Im\nNJYAR8SvAu8E3g6sAW4FrouI5X3qnwh8GrgBOB14D/CBiDivqZi0NHxzOjA4TkvHvj8wOE77J8dF\nTWjyCvAm4A8z86OZeTvwemAP8Jo+9d8A3JmZb8nMHZn5PuCTZTuSJEnSomgkAY6IQ4C1FFdzAcjM\nBK4Hnt1ntWeV5VXXDagvSZIk7bOmrgAvh/+/vXuPk6Oq8z7++SXhEogM0cCEUQiCmICYkMmqYLiz\nyA4KLILIDGqE8Ybsg098rTdwve0qIisBBZZ9RMALjA8CKrAIGAKiGMXNBFxDEvRJohjNECQOkAuX\nmd/zxzmdrq6uvk1qMjP09/169au7q06dOnVOnepfV52uZjzQl5reB0ytsMzUCul3M7OdciqXiIiI\niPqvzbYAACAASURBVEiJCSNdgAbsDLB8+fKRLseosKZ1DTtO3JEtvVtKXkMrMB2YCPQCMHH9RHp7\ne2FdK7hDb29GutaYc298bi3Oe3xiXIaQx5ZBGKyUx0T6+9fS25s9r+z1ulZ4djqMm5goV7IcRVu3\nA2h9phV3p7e3l9ZnWpn+/PSt81ufaWWiT9zmecDW9cUSZGxLuo6yt7PauirVYyN5DKU+1vavrbs+\nyttlIln7SiP1DRTrbV0r9IfX29JmQ2n35L5Vus0MOf9a6Qp1n/e2bOs+kZ6X7nPpdOV9hMx6S6bb\n1jot63t17n+Vjh/pMiXfV+ojg2sHG2qz8j7deH/PzqOVrGPl0KTzJ7e8G9l36tHf3z/kZWX4JeK0\nnUeyHLVYGKmwjZmEIRCbgNPc/bbE9OuBFnc/NWOZnwJL3P2jiWnvBRa4++SM9F3ADdtcWBEREREZ\nbme5+40jXYhKcjkD7O4vmNkS4DjgNgAzs/j+axUWWwx0pKa9JU7PcjdwFrAG2LKNRRYRERGR/O0M\n7EuI20atXM4AA5jZGcD1hLs/PES4m8PpwAx3X29mFwFt7j4vpt8X+B/gKuBaQrB8GXCiu6d/HCci\nIiIikovcxgC7+03xnr9fIAwgehg4wd3XxyRTgb0T6deY2VuBBcD5wJ+AbgW/IiIiIjKccjsDLCIi\nIiIyFuT6V8giIiIiIqOdAmCRMcrMjjKzQTPbbaTLIiIiMpY0FACb2XXxA3fAzJ43s3Vmdo+ZnR3v\n+jBszOzUuK4nzKzfzH5hZm/JSPcOM1tuZpvN7BEz60jNP8LMbjOztXFbTq6x3qtjuvPz3qbtIbbZ\nrcOY/2Qz+5qZrTCzTWb2BzO7PB2UxXQ3xLbbYGbXmNmuqTSXm9l/m9kWMyu7yaOZTYttkXwMmNkb\nh2v7hlOqPyW3Z78GsmloDJPaa2vZro/luSpj3pVx3rXDsF7V/xCZ2aFxG24f5vXMNLMbzeyPsY2W\nZR3/Y7oH4mfNH8zsY6n5U2Mbroz1fmlGHvMyjgGbhnP7tsVI9ZvUerZbLJA4Ricfdw7n9g0HG/44\nYIKZXWxmvzGzZ2OdfsvM9kql2ynuJ0+a2TNmdrOZ7ZlKc4GZPWhmG83sqQrryzqundFouYdyBvjH\nhB+0TQP+AVgEXA7cbmbDeUb5SOAewq3T2oH74jpnFRKY2ZuBG4FvAIcAPwJ+aGYHJfLZlfADvQ9T\nI3gws1OBNwFr89uMl5w2YC/go8DrgHmE/eKaVLobgQMJd/t4K6E9/zOVxoFvAt+rsj4HjiXsg1Pj\nupds0xaMrEJ/Sm7P6mFcn9orcOCPwJmW+OfJ+LoT+MO2rsDMsn5krPofum7CHYZOMLNK/zBaNzMb\nZ5Z54mYO4V9JzwIOAr4IXGRmH04s+zLCLZ5WEz6PPgZ8zszel8hnJ+AJ4F8JnzmV9FN6DJg21G3a\nDoa939Rhe8cCPyb8sL/QPp25bclLxy6Eev48MBs4lfBPLT9KpbuMcDw7jdCObcAtqTQ7ADcB/1Fj\nnfMotstewA8bLrW71/0ArgNuzZh+DDAInJOY1kI4qD9B6OALgZmp5U4iHNA2A+uBWxosz2+BTyfe\nfw+4LZVmMXBVheUHgZMrzHsloaMfSDjInd9I2UbLI9lmwAnAz4ANwJPA7cB+ibTTYp2cSvhis5Fw\ngDi0wXWeHtt0XHw/I+Y7O5HmBOBFYGrG8p8FejOmF8o3s5HyjNZHpf6UmG/Ap4BVhD+aWUr4s5nC\n/KOAAeBE4JFY54uB16m96qr7H8R660xM74z1fCtwbWr76+k7ZwD3x/Z6j+o/t/baFXiaEMz/DPhk\nav5RcVsr9gXCB+YGwufOMuB5YJ86138FsDDx/ty4H0xITLsIeLTC8vcBl2ZMnwc8NdL120A75N1v\n7gW+nlrHFOA54JgGyjUssQA1jtFj5UFpHFAWz8S2+0yqPrpje24EHgNOanCdf0f4fHpVfL9bbNdT\nE2mmx3W9MWP5in2jUns1+sjljK2730foEG9PTL4ZeAWhA7QT/lNxoZntDmDhFmi3AncQvjkcDfyy\n3nXGb+4vA5KnyA8jBNpJd8fpdYt5fxv4iru/lP57eVfgq4T2OJawc/4gI92/AV8BZhF2/BsbPLu/\nO/C0uw/G94cBG9x9aSLNQsK37jc1tAXBbWbWZ2Y/M7OThrD8WHEB8C7gA4QzUQuA75jZEYk0Rmir\n+YQDznpC/YxvYD3N2l5OuAf5OYlp5xA+LNJnBuvtOxcR2ulA6r8JfLPWfyPeCfS5+yLCiZXuCulq\n9YVdgI/H5V9HOEFTjxZKP2sOBR5w9xcT0+4GpptZS515FkwyszVxyEX6LOVolGe/uQbotPBvsgXv\nBv4U44qahjMWiI6O/WeFmV1lZi8fQh5j0WcIXyReD9wJ3FCI3+q0O2Ff+Vt8P4dw6917CwncfSXh\nRONQ2uVKM1tvZr8ys7OHsHyuP4JbQfjnD8zscMIB6Ax3X+ru/8/dP044E3x6TH8BcKO7f8HdV7r7\nMne/pIH1fYzQuW5KTJtKuHSV1BenN+KTwPPufkWDy41q7n6ru//Q3Ve7+2+A9wGvzzjgXuLud7n7\n7wlnl6YBr6lnHRbuBf1pSi/XTiX1QePuA4QDViNt8yzh0vE7CGd6fk64rPW2BvIYbU6KY6EKj/8L\nYGY7Es7+nuPuC919jbt/m/B34B9M5fE5d1/k7ssI35qnEs7i16T24gbgcDPb28ymAW8GvptO1EDf\nWeDuP3L3P7h7+lhURvVft3MIwz0gHPOnmNmRGelq9YUJwLnu/kt3/5271/xX0Xg5/QzK2yjrs6Yw\nr14rCdt2MmHIxTjgF2bW1kAeIyGvfnMrIWg+JbHYPEIwXa/hjAV+DLyHELx/nHCl4c4KQ2deaq5z\n95vcfRUhXpsE1PX7gTgk5suEGO/ZOHkqIa56OpV8KO3yL4Q++feEk61Xmdk/NZhHfn+EQdiJC+No\nZhK/kaX2k52Bwg98DgH+z5BWZNZFqICT3f3JIZW2ct5zCH/MMTvPfEcDMzuAMEbnTYTLTOMIbbYP\n8Ggi6f8kXv+F0LZ7Es4GV8v/ZcB/ES5HfT63gkfu/lfCGKKCJXGQ/ccIVxLGokWEf08sdJSN8fk1\nhLNVP0kdbHcgXE0pcBJXTtx9g5mtJJyBrErtBe7+pJndAZxNaIP/cvf0cQszew3hT35q9Z26x9eq\n/utjZtMJH7ynA7j7ZjP7HuEs7gOJpPX0hefd/bcNrPtgwtjCz7n7vbXSN8rdf0mizGa2GFhO+JL7\n2bzXl5e8+o27P2dm3yF8CbjZzNoJZ+brulIxnLEAhD/4SrxdZmb/A/w/whXrus5Qj2Fb4wB332Rm\nTxPigKribx++T2jnD9dIPiTu/sXE20cs/ED4Y4ShSnXLMwAujJWF8E3hz4RvS+lvSoXT4ZuHshIz\nO5MQOJ+ecYlkHWFQdFJrnF6vw4E9gMcTnXk8cKmZ/W93b+QX+qPN7YQ2eh+hfcYRxsLtmEr3QuJ1\n4UtN1asFZjaJcInpb8Db4xmrgnWkOk68LPlyGmubLA8Bx29jHiNpo7uvzpg+KT6fSGirpOe2daVq\nrxLXEQ6c1Q7Yd1Bf39lIHVT/DekmHIMfTwVYz5nZP7n7Mw3kVffnTjxLuRC42t0vSs2u9FlTmDck\n7v6imS2lzituIyyvfnMNsDSe9T4bWOTuj9da+XaIBcq4+2oze5LQPmM1AB6kPC7bISPdC6n3Tu04\noBD87g0cmzj7C6HudzSz3VJngbe5XQjHtX8xsx3cPV3uinIZAmFmxxLGidwcJ/USTmkPuPuq1KMw\nTuc3hF84N7KeTsJlsDPd/a6MJIsz8jw+Tq/XtwlnsGclHn8mjC07oZHyjiZx3NJrgX9z9/vi2JtX\nZCT1jGm18n4Z4Ve5mwnfxJ9PJVkM7G5mybPqxxE64a8aXV/KbMJZ6peaRwmB7rSMPpS8K4kRxiOG\nN2aTCe1ccey62qvMXYQP5AmEeimRd99R/dcvBv7vJgzlmEX5cTn5i/xKfSF5hr7e9b6OcHXmOnf/\nTEaSxcCRqfHFbwFWunt/o+tLrHcc4bN0LLRRLv0mnpH/b8JvHQqf8VVtp1gga72vImzDWGifStYT\n7poAgIVbML56WzNNBL/7Ace5+4ZUkiWEH/Iel1hmOuFqwDa1C+G4tqGR4BeGdgZ4JzNrJXwjbyXc\niuSTwG3AdwDcfWG8lPNDM/sE4dL5Kwlns251917CJb+FZraKMNB6B6DD3b+StdJ4qeN6wvCEX8cy\nAGxOfJu4HLjfzD5KuLTYSRh4/f5EPrsSvr0VvgHtZ+H2KU+5++Ox0UoazsxeANa5++8arq3RYwPw\nV+ADZraOMK73Iso/tBsa2xQ/zH9CGN5yFuGDuzB7vbsPuvsKM7sb+IaZnUs4aH4d6HH3dYm89icM\nndkLmGjF29osi2dG3kP45Xbhx0GnAe+l8g9ixix3f9bM/h1YED9kf074Ic5coN/dv5NI/hkL90t8\ngnDLpvVUuCWM2qucuw+a2Yz4OiuIza3vqP4bdhLhxzTXps/0Wriv6fsoHUqX1RfSt2KqKg57WEQY\n/3lZ4rNmIHGZ/UbCj4SuNbOLCUHr+cBHUnnNIuwXk4A94vvnPf642sz+hTAE4vdxOz9OCAjSt8Ub\ndXLsNxCC2SsI49ar3s5qe8UCcf5nCbfpWhfTXkyIZ+r9getotAiYF4ew9BNisRerL1JdDH5vIQxt\nfRuwQ6JdnnL3F9z9aTP7JuFq+gbgGeBrwIPu/lAir70JV7umAeMTx7Xfu/tGC79haCX0my2EL56f\nIpykbIw3fiuNgfh4jrBT3E3G7X4Ig9IvAx6PhVxDOLv6ykSafyR8K9hMGAj9/Srrvi+x7uTj2lS6\n0wg/yNtMOMt8Qmp+4XY5VfNJLbOKsXsbtG8BN8XXxxHGGxZuqXVE3PaT4/xp8f3MxPItcdqRFfI/\nKqMuC/W7TyLd7oQfSfQTDozfAHaps433ifPfQ7h89kzMYzGJW6qMtQd13GIH+F+EM1hbYn+7Ezg8\nVfcnEsZrbQZ+ARxcJT+1Vx11T/ilevJ2Tsc22ndU/7m0022kbmeVmPeGuL0H19MXqPOWY4SgJ6te\nV6XSHQz8NO4TfwT+OSOvrM+aVYn5lxKGCGwmnNG+vdY+9FLqN4l0uxKC36/VUYbtEgsQvqTeRTju\nbiHEAf8B7DHS7TCEdkvGAS8jfIHbQIjL3k24ap+8DVpWGz1FhVs7Ujz+ZR3Xjkyk24nwZf7JeFz6\nPrBnxj6W1b5HxvknxPL2E26N2Au8byj1YjFDeYkysx8Dv3P3MflPdiIitZjZUYQzW5O9/FfmMsqZ\n2b6Es+Bz3P2RkS3NS4/igGzD+c9tMoLMbPd4qeAowiVXEZGXsma4NdVLioW/0J1KuPf8YgW/+VIc\nUF2ed4GQ0eVawr2Y/93dbx/pwoiIDDNdzhx75hKGNKwg3K9a8qU4oAoNgRARERGRpqIhECIiIiLS\nVBQAi4iIiEhTUQAsIiIiIk1FAbCIiIiINBUFwCIiIiLSVBQAi4iIiEhTUQAsIiIiIk1FAbCIiIiI\nNBUFwCIiIiLSVBQAi4iIiEhTySUANrMPmdkjZtYfH78ws3+osczRZrbEzLaY2WNmNi+PsoiIiIiI\nVJPXGeDHgU8A7cAcYBFwm5kdlJXYzPYF7gDuBWYBlwPXmNnxOZVHRERERCSTufvwZGz2V+Cf3f26\njHkXAx3uPjMxrQdocfcTh6VAIiIiIiIMwxhgMxtnZmcCOwE/q5DsUGBhatrdwGF5l0dEREREJGlC\nXhmZ2cHAYmBnYBNwhrv/vkLyqUBfalofsJuZ7eTuz2Xk/wrgBGANsCWvcouIiIhIbnYG9gXudve/\njnBZKsotAAZWEMbztgCnA98zs6PcfWlO+Z8A3JBTXiIiIiIyfM4CbhzpQlSSWwDs7i8Cq+LbpWb2\nRuBc4AMZydcBralprcDTWWd/ozUA3/3udznwwAO3vcAybObPn8+CBQu2OZ8LF13I6g2rmbv3XM57\n43nAhUAbUHi9Gpgb3j9yIUxsg9eeB49dGZ4BrrwSzjuv+HzhhTBrFpxxRljm2dWwx9yYPp0/wBez\nSlaxHBcuupC2SW01y3vhoguZtecszjj4jDq2s23rMsl0Fy4KZfzisRllTNbHIxeCOxzypZJtnv8f\nv2fBe3cJ6Wcl87iybN3htQNfSmzLqzPqp7DslcV2SeefbIu2tvA6PX31apg7N7xPbkt6XjKPRy6E\n3WfBtDOy0wF8MaOu0nmky7t1f6qy/zU0D+bP3xT7SKJe/3BTKHvJOinfT9Pbkt6nC3VVVg7gppuK\n6TL2ia37S0kebcW2rNpfKvTH9Ot68yiZl7FfZfX3atuSTFd4nX5O5bG1j1Ss02QZS3aq0nTVypi5\nnbXSZZUDso9X28eqC1eBw35f2i9MqNbnsvaJSa9OHYeo2N/n//73LNhll8r5y4havnw573rXuyDG\nbaNVnmeA08YB4yvMWwx0pKa9JU6vZAvAgQceSHt7+7aXToZNS0tLLm3U9+s+Vm5cSfse7TG/PmAy\n4WYjfcDK+Lod+vpgt8nQ3g6bN4dnKL4uPPf1wbhxxdcbVsLe7TF9On/i67KSVSxH36/7mDxlcs3y\n9v26j3GvHEd7e3sd2zl56zLJdH2/DmXMrOtkffTFbUltc0tLC+1T/1Sct9XmsnWX1kdhW3bJqJ/C\nspuL7ZLOP9kWkyeXt1VfH6xcGV63t5dvS3re5MS8vcbBIRXSlW1noq4mZ9RVurzV9r+G5kFLy6sS\nbR3rddzNoewl66R8P02XMb1PF+qqrBzAzTdX3Sey85hcbMuq/aVCf0y/rjePknkZ+1VWf6+2LdWO\nCxXy2NpHKtZpsowlO1VpumplzNzOWumyykFGObafwb7BUILkdoYJ5Ymz9onJu5SnrdDfW1paaP9T\n1rFLRplRPVw1r/sAf8nMjjCzaWZ2sJldBBwJfDfOv8jMvpVY5GpgPzO72Mymm9mHCcMmLs2jPCIi\nIiIileR1BnhP4FvAXkA/8BvgBHe/L86fCuxdSOzua8zsrcAC4HzgT0C3u6fvDCEiIiIikqtcAmB3\nf1+N+WdnTHuA8KcZIiIiIiLbTe73ARbp7Owc6SJIHdROI0d1PzaonUYntYvkQQGw5E4Hp7FB7TRy\nVPdjg9ppdFK7SB4UAIuIiIhIU1EALCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuIiIhIU1EALCIi\nIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuIiIhIU1EALCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuI\niIhIU1EALCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuIiIhIU8klADazT5nZQ2b2tJn1mdkPzOy1\nNZY5yswGU48BM9szjzKJiIiIiGTJ6wzwEcDXgTcBfw/sANxjZhNrLOfAAcDU+NjL3Z/IqUwiIiIi\nImUm5JGJu5+YfG9m7wWeAOYAP6+x+Hp3fzqPcoiIiIiI1DJcY4B3J5zdfapGOgMeNrM/m9k9Zvbm\nYSqPiIiIiAgwDAGwmRlwGfBzd3+0StK/AB8ETgPeDjwO3G9mh+RdJhERERGRglyGQKRcBRwEzK2W\nyN0fAx5LTPqlme0PzAfmVVpu/vz5tLS0lEzr7Oyks7NzyAUWERERkcb09PTQ09NTMq2/v3+EStOY\nXANgM7sCOBE4wt3/MoQsHqJG4LxgwQLa29uHUjwRERERyUnWCcje3l7mzJkzQiWqX24BcAx+TwGO\ncvc/DjGbQwhDI0REREREhkUuAbCZXQV0AicDG82sNc7qd/ctMc2XgFe6+7z4/iPAamAZsDPwfuAY\n4Pg8yiQiIiIikiWvM8AfItz14f7U9LOBb8fXewF7J+btCHwVaAM2Ab8BjnP3B3Iqk4iIiIhImbzu\nA1zzbhLufnbq/SXAJXmsX0RERESkXsN1H2ARERERkVFJAbCIiIiINBUFwCIiIiLSVBQAi4iIiEhT\nUQAsIiIiIk1FAbCIiIiINBUFwCIiIiLSVBQAi4iIiEhTUQAsIiIiIk1FAbCIiIiINBUFwCIiIiLS\nVBQAi4iIiEhTUQAsIiIiIk1FAbCIiIiINBUFwCIiIiLSVBQAi4iIiEhTUQAsIiIiIk1FAbCIiIiI\nNBUFwCIiIiLSVHIJgM3sU2b2kJk9bWZ9ZvYDM3ttHcsdbWZLzGyLmT1mZvPyKI+IiIiISCV5nQE+\nAvg68Cbg74EdgHvMbGKlBcxsX+AO4F5gFnA5cI2ZHZ9TmUREREREykzIIxN3PzH53szeCzwBzAF+\nXmGxc4FV7v7x+H6lmR0OzAd+kke5RERERETShmsM8O6AA09VSXMosDA17W7gsGEqk4iIiIhI/gGw\nmRlwGfBzd3+0StKpQF9qWh+wm5ntlHe5REREREQgpyEQKVcBBwFzhyFvaSIdB3QwY8oM5u5d2JU6\ngLbE6xls3c3aOmBinLdHYtc75ZTS5+5u2Gef8Hr/bti0FibPqpB/Jd3ApMxydBzQQdukZB7Z5e04\noIOZe84Muc3uZu0za5nVWqkcbZn10XFAlTLu3w0TYhnbOsA9vJ52Juw2o1hHbVl5nFJhO2MenBm3\n5dVVlj2lWI4XN5YmmRvX3d0NkyYVpyfbaMUKmDGjWMZC23Z0hOmFPDo6oK2tmG73mcU81q6F6dOL\n6Srp7oYpU4p5pG3dnyq3e9V9s+p+lajXKYmLX/u9t/h6/27oXwEtM7K3paMDZs4spt1pSoVyAEcf\nHZ4P+gS8uDm8ztonJmbsw20dpenq7Y/p1/XmUTKvMO2EYpkn7BpeJ/t7oU/vNr183cl00zrD86tO\nKX2ulUfVMiZ9AlhDOM+TUcZke2ZuZ610WeUYWS/veHnxEAGhX23cmJ04a5+YlHE8qdbfq/VpkTqY\nu9dOVW9mZlcAJwFHuPsfa6T9KbDE3T+amPZeYIG7T85I3w4sOfLII2lpaSmZ19nZSWdnZw5bIKPF\nwOAA48eNz3zvPoBZcR6DA1BIW3jtAzAIjB8PAwOlz+llIKTfmucAUOk1FeZVK2PldOntrLRctfoo\nf50oY8XtSs3zAbDCjPE16iCRx9b3A+XLJvMv1HfhOd0WTnkbJZeD8nmV2rNaukrrTeeRLm9dddDI\nvAEGBim2aaGuBmM7WKyLcSTaL2NbqFD+knZPb2uiHFnpsvKou7+QXY9Z66qWR2a9VdmvPNZFrePC\nwEDMNpFP8jmdR1kfqVDGkv5D6bZsXS62Qc06SB9nhlJX25cPODbeiq8ZzO5zIUHl/Woo/TidTrar\nnp4eenp6Sqb19/fzwAMPAMxx994RKVg93D2XB3AF8DiwX53pvww8kpp2I3BnhfTtgC9ZssSlOXTd\n0uWzr57tF9x7wdb3hdfuXe4+293j+we73B+OrwvP7u4XXFD63NXlfsUVxWXunJ1I31XMz7viI7Nk\nqXTFcpSXsXK6K351ReZ2li93QWa6rlu6vOuWesvYmVmO7O2sVI50HlnrvqD0+cGu8Egq1HdXV7Fd\n3EvbaPbs4vtk26bnJfN4sMt95RWV03VVqKt0HpXKW6U9G5tXzL/rli7vvDnWa6HsJev08v00vS3p\nffrhSuXwYrpke6bzL8vjgux01bY5mUfVMtVbbxn7VVZ/r7YtyXSemlYtj7rLmJROV2/+ybwaratK\nx4LtY1nXMl/Wuaw4oVqfy9qv0v2ukEel/l41fxlJS5YsccLXvXbPKcYcjkcuQyDM7CqgEzgZ2Ghm\nrXFWv7tviWm+BLzS3Qv3+r0aOM/MLgauBY4DTgdK7ighzWv5+uUsXbeUGVNmbH3vW69YLAeWEi4D\nAv3Li5f5n11dzGT16tLn5cth2rTiMhuWhstvW/NM5l+xZKl0xXKUl7Fyumkt0zK3s3w5r1gf9Zcx\nOT1Rb5nbuToxr1oeWVaXPvdn5P9soi2SV6CSbbR0aXEIRLJt0/OSefQvh12mVU5XSTqPSuWt0p6N\nzUusOtmGm9ZmrJPy/TS9Lel9ulIfgTAsJF2OdP5leXh2umrb3J+q04plqrfeMvarrP5ebVuS6Qr5\nPLu6fF4j21kyLymdrt78k2VstK5G1qblm0onVOtzWftVZrIq/b1q/iK15fUjuA8BuwH3A39OPM5I\npNkL2Lvwxt3XAG8l3Df4YcLtz7rdPX1nCBERERGR3OR1H+CagbS7n50x7QHCvYJFRERERLaL4boP\nsIiIiIjIqKQAWERERESaigJgEREREWkqCoBFREREpKkoABYRERGRpqIAWERERESaigJgEREREWkq\nCoBFREREpKkoABYRERGRpqIAWERERESaigJgEREREWkqCoBFREREpKkoABYRERGRpqIAWERERESa\nigJgEREREWkqCoBFREREpKkoABYRERGRpqIAWERERESaigJgEREREWkqCoBFREREpKnkEgCb2RFm\ndpuZrTWzQTM7uUb6o2K65GPAzPbMozwiIiIiIpXkdQZ4V+Bh4MOA17mMAwcAU+NjL3d/IqfyiIiI\niIhkmpBHJu5+F3AXgJlZA4uud/en8yiDiIiIiEg9RnIMsAEPm9mfzeweM3vzCJZFRERERJrESAXA\nfwE+CJwGvB14HLjfzA4ZofKIiIiISJPIZQhEo9z9MeCxxKRfmtn+wHxgXrVl58+fT0tLS8m0zs5O\nOjs7cy+niIiIiGTr6emhp6enZFp/f/8IlaYxIxIAV/AQMLdWogULFtDe3r4diiMiIiIilWSdgOzt\n7WXOnDkjVKL6jab7AB9CGBohIiIiIjJscjkDbGa7Aq8h/LANYD8zmwU85e6Pm9lFQJu7z4vpPwKs\nBpYBOwPvB44Bjs+jPCIiIiIileQ1BOLvgPsI9/Z14Ktx+reAcwj3+d07kX7HmKYN2AT8BjjO3R/I\nqTwiIiIiIpnyug/wT6kynMLdz069vwS4JI91i4iIiIg0YjSNARYRERERGXYKgEVERESkqSgAOHvP\nlAAAGoBJREFUFhEREZGmogBYRERERJqKAmARERERaSoKgEVERESkqSgAFhEREZGmogBYRERERJqK\nAmARERERaSoKgEVERESkqSgAFhEREZGmogBYRERERJqKAmARERERaSoKgEVERESkqSgAFhEREZGm\nogBYRERERJqKAmARERERaSoKgEVERESkqSgAFhEREZGmkksAbGZHmNltZrbWzAbN7OQ6ljnazJaY\n2RYze8zM5uVRFhERERGRavI6A7wr8DDwYcBrJTazfYE7gHuBWcDlwDVmdnxO5RERERERyTQhj0zc\n/S7gLgAzszoWORdY5e4fj+9XmtnhwHzgJ3mUSUREREQky0iNAT4UWJiadjdw2AiURURERESayEgF\nwFOBvtS0PmA3M9tpBMojIiIiIk0ilyEQIsOh44AOZkyZwdy952593zaprTAXmAGEebR1wMQ4b4+5\nxUxOOaX0ubsb9tknvN6/GzathcmzEnkm86+kG5iUWY7yMmaXt+OADmbuOTPkNrubtc+sZVZrpXK0\nVayP+stYGJp/Zkk5srfzlDrzeHWVZePz/t3w4sbSJIX26e6GSZOK05NttGIFzJgR3ifbtqMjTJ87\nt/i+ra2YbveZxTzWroXp04vpKunuhilTinmkbd2fKrd7tbautl91HNCBe6zXKYkLYPu9t/h6/27o\nXwEtM7K3paMDZs4spt1pSoVyAEcfHV98AtgcXk47E3abUdzOZH0ny97WUZqu2jYn8yjLbyj1Vph2\nQrHME3YNr5P9vdCnd5tevu5kOjrD06tOKX3OyqPuMiZ9AlhDONeTLD/l7Zm5nRD2txVxffWUY2S9\nvOPlpb8A6u6GjRsrpM7YryZlHE+q9fdqfVqkDrb14JtXhmaDwD+6+21V0vwUWOLuH01Mey+wwN0n\nV1imHVhy5JFH0tLSUjKvs7OTzs7OPIovo8TA4ADjx43PfO8+gFlxHj4AhfdbXw+Eg7GNL07LTJeR\nBwNApddsfV+tjJVeD3VeY+mKZay8LZW2LSzbaDkGBsPyhdchTSL/Qv0ODsC4VFsMDoAR3g8MwPhE\nGQvp06/T75P5pfNIvk++Tq630rqS+1Pd9VjPvAEGBinWZbJ+CmXy2CZWZVuM7PpJ798l21qlvJX6\nSN39pYE8Gq63+Fypv0PlcpS0J6l8kvlXySNVxvJjUqL4mduSOCZVzT+jHEPe/7YPH3BsvBVfM5jd\n59JlrNan6+3HZfnL9tTT00NPT0/JtP7+fh544AGAOe7eOyIFq4e75/oABoGTa6T5MvBIatqNwJ1V\nlmkHfMmSJS7NoeuWLp999Wy/4N4Ltr4vvHbvcvfZ7p58X3h9QTGThy8ofX6wy33lFXXk0RUfmSXb\nmq5aGdOv0+mu+NUV25xH1y21yxhed1bZ5nQe2evuvLmzpBxZ6y6kL22nVLpkWxReJ6d3dbnPnu1+\nQUa6B7vc75xdIY8ud78iO4+urvDIks7/wez6KK/TavtOfftVsl6L+6WX1kt6m9Pbktyny+ojWQ5P\n7fvV9om8t3lb80g9l2xnYvuq5ZGs07JpdeaRmld6TEpK51GlPSsduxquq0rHgu1jWdcyX9a5rDih\nWp9LbkuhPsr6nWf343r6tIyoJUuWOOHrXrvnHGPm+chlCISZ7Qq8hvjdGtjPzGYBT7n742Z2EdDm\n7oV7/V4NnGdmFwPXAscBpwMn5lEeeWlYvn45S9ctZcaUGVvf+9YrFsuBpRQvDy6neP1tdTGTZ1eX\nPvcvh12m1ZHH8mol25quWhnTr9PpprVM2+Y86ilj6bZkbXPa6sx1b80hliPL6g2rS54z80+2RfIK\nVGH68uWwdGlxCEQyXf9y2LA0XDIty2M5MC07j+VV6iqdf/lWJfKvZ/+rf78qacNNa4uvn03sw+lt\nTm9Lcp8uq49kOZLrqLVPeIXXQ93mbc1jdelzyXYm6qpaHsk6LSzzbCrfBstYekxKSudRpT0rHbsa\nrquRtWn5ptIJ1fpcclsK9ZGZLKMfF+q7av4iteX1I7i/I/TGJYS9+qtAL/D5OH8qsHchsbuvAd4K\n/D3h/sHzgW53T98ZQkREREQkV3ndB/inVAmm3f3sjGkPAHPyWL+IiIiISL1G6jZoIiIiIiIjQgGw\niIiIiDQVBcAiIiIi0lQUAIuIiIhIU1EALCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuIiIhIU1EA\nLCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuIiIhIU1EALCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQU\nAIuIiIhIU1EALCIiIiJNRQGwiIiIiDQVBcAiIiIi0lQUAIuIiIhIU8k1ADaz88xstZltNrNfmtkb\nqqQ9yswGU48BM9szzzKJiIiIiCTlFgCb2TuBrwKfBWYDjwB3m9mUKos5cAAwNT72cvcn8iqTiIiI\niEhanmeA5wP/6e7fdvcVwIeATcA5NZZb7+5PFB45lkdEREREpEwuAbCZ7QDMAe4tTHN3BxYCh1Vb\nFHjYzP5sZveY2ZvzKI+IiIiISCV5nQGeAowH+lLT+whDG7L8BfggcBrwduBx4H4zOySnMomIiIiI\nlJkwUit298eAxxKTfmlm+xOGUsyrtNz8+fNpaWkpmdbZ2UlnZ+ewlFNEREREyvX09NDT01Myrb+/\nf4RK05i8AuAngQGgNTW9FVjXQD4PAXOrJViwYAHt7e2NlU5EREREcpV1ArK3t5c5c+aMUInql8sQ\nCHd/AVgCHFeYZmYW3/+igawOIQyNEBEREREZFnkOgbgUuN7MlhDO5M4HdgGuBzCzi4A2d58X338E\nWA0sA3YG3g8cAxyfY5lERERERErkFgC7+03xnr9fIAx9eBg4wd3XxyRTgb0Ti+xIuG9wG+F2ab8B\njnP3B/Iqk4iIiIhIWq4/gnP3q4CrKsw7O/X+EuCSPNcvIiIiIlJLrn+FLCIiIiIy2ikAFhEREZGm\nogBYRERERJqKAmARERERaSoKgEVERESkqSgAFhEREZGmogBYRERERJqKAmARERERaSoKgEVERESk\nqSgAFhEREZGmogBYRERERJqKAmARERERaSoKgEVERESkqSgAFhEREZGmogBYRERERJqKAmARERER\naSoKgEVERESkqSgAFhEREZGmogBYctfT0zPSRZA6qJ1Gjup+bFA7jU5qF8lDrgGwmZ1nZqvNbLOZ\n/dLM3lAj/dFmtsTMtpjZY2Y2L8/yyMjQwWlsUDuNHNX92KB2Gp3ULpKH3AJgM3sn8FXgs8Bs4BHg\nbjObUiH9vsAdwL3ALOBy4BozOz6vMomIiIiIpOV5Bng+8J/u/m13XwF8CNgEnFMh/bnAKnf/uLuv\ndPcrgZtjPiIiIiIiwyKXANjMdgDmEM7mAuDuDiwEDquw2KFxftLdVdKLiIiIiGyzCTnlMwUYD/Sl\npvcB0yssM7VC+t3MbCd3fy41b2eA5cuXb2NRZbj19/fT29u7zfm0PtPK9OenM3H9RHp7e2l9ppWJ\nPjHm3UrYtSYChfeF14Vn4PGJ0NtbfF7XClsGYbBWHq2xFFnbUUxXrYzp1+l0g2sHK86rNw+gQl2n\nt8UTr8M29/evpbc3azuL25Zct7uXlKP1mdaydRfKVnjOrMdkW/TH18npra0wfTpMzEi3rhWenQ7j\nsvJoBQbDutJ5tMZyZNVVOv+ydJX2j2r7TvX9qtBHkvXKHwr7ZaIuCuVLbnN6W5L7dFl9JMtBYh3Z\n+0R2efPY5m3NI/Vcsp2J7auWR7JOy6Zl51HsI9llLD0mJaXLkcg/3Z6Vjl0N1xWJZbe/Na1rwGFc\nbzyvVq3PJbelUB/PtZanzerHEyfSv3YtvVXzl5GUiNN2Hsly1GLhRO02ZmK2F7AWOMzdf5WYfjFw\npLuXndU1s5XAte5+cWJaB2Fc8C7pANjMuoAbtrmwIiIiIjLcznL3G0e6EJXkdQb4SWCA4tfQglZg\nXYVl1lVI/3TG2V8IwyPOAtYAW4ZcUhEREREZLjsD+xLitlErlwDY3V8wsyXAccBtAGZm8f3XKiy2\nGOhITXtLnJ61jr8Co/abhIiIiIgA8IuRLkAted4F4lLg/Wb2HjObAVwN7AJcD2BmF5nZtxLprwb2\nM7OLzWy6mX0YOD3mIyIiIiIyLPIaAoG73xTv+fsFwlCGh4ET3H19TDIV2DuRfo2ZvRVYAJwP/Ano\ndvf0nSFERERERHKTy4/gRERERETGilz/CllEREREZLRTACwyRpnZUWY2aGa7jXRZRERExpKGAmAz\nuy5+4A6Y2fNmts7M7jGzs+NdH4aNmZ0a1/WEmfWb2S/M7C0Z6d5hZsvNbLOZPRLvLZycf4SZ3WZm\na+O2nJyRx2djHs+a2VNm9hMze9Nwbt9wiW126zDmP9nMvmZmK8xsk5n9wcwuTwdlMd0Nse02mNk1\nZrZrKs3lZvbfZrbFzKre3dzMXmNmz5jZU8OxXdtDqj8NJl7v10A2DY1hUnsFZnZ9rO+rMuZdGedd\nOwzrVf0PkZkdGtvl9mFez0wzu9HM/hjbaJmZnV8h3QPxs+YPZvax1PypsQ1Xxn5d9QfeZnZm3L5h\nO15vq5HqN6n1bLdYIJX+6piubF8Y7bZDHDDBwg0NfmMhblprZt+y8B8RyXQ7xf3kyXg8utnM9kyl\nucDMHjSzjbWOV2b2cjP7U+xfDZ8IGsoZ4B8TftA2DfgHYBFwOXC7mQ3nGeUjgXsIt05rB+6L65xV\nSGBmbybcKu0bwCHAj4AfmtlBiXx2JfxA78NUDh5WAucBBwNzCfcevsfMXpHj9rxUtAF7AR8FXgfM\nI+wX16TS3QgcSLg13lsJ7fmfqTQOfBP4XrUVmtmEmN9Pt7Hso0GhPxUeewGrh3F9aq/AgT8CZ5rZ\nToWJ8XUn8IdtXUHc7jTV/9B1Aw8BJ5jZ1G3NzMzGmWWeuJlD+FfSs4CDgC8CF1m4U1Fh2ZcR7nG6\nmvB59DHgc2b2vkQ+OwFPAP9K+MypVpZ9gUuAB4a4OdvLsPebOmzPWKCQ36nAmwh/+CXldiHU8+eB\n2cCphL8q/FEq3WWE49lphHZsA25JpdkBuAn4jzrW+01q9K2q3L3uB3AdcGvG9GMI/0F6TmJaC+Gg\n/gTQDywEZqaWO4lwQNsMrAduabA8vwU+nXj/PeC2VJrFwFUVlh8ETq5jPS+LaY9ppHyj4ZFsM+AE\n4GfABsKfl9wO7JdIOy1u56mELzYb4851aIPrPD226bj4fkbMd3YizQnAi8DUjOU/C/RWyf9i4FuE\n4OGpka7jPNqmwnwDPgWsAjYBS4HTEvOPIvwBzYnAI7HOFwOvU3vVVfc/iPXWmZjeGev5VsI/VSa3\nv56+cwZwf2yv96j+c2uvXYGngWNjO3wyNf+oWGcV+0Lc/g2Ez51lwPPAPnWu/wpgYeL9uXE/mJCY\ndhHwaIXl7wMurTBvHPBz4Oxax4SRfgxDv7kX+HpqHVOA52jg85ZhjAWAVxKC/gMJX3jOH+l2GGK7\nFeKAsm2IbfeZVH10x/bcCDwGnNTgOv+O8Pn0qvh+t9iupybSTI/remPG8lWPV7EPLiLEnwPAbo3W\nSy5nbN39PkKHeHti8s3AKwgdoJ3wJ+ULzWx3AAu3QLuV8NfHhwBHA7+sd53xm/vLgOQp8sMIgXbS\n3XH6kJjZDsAHCQH60qHmM0rsCnyV0B7HEnaaH2Sk+zfgK8Aswo5/Y4Nn93cn/KPfYHx/GLDB3ZP1\nt5DwrbuhoSVmdizh2+N5jSw3Rl0AvAv4AOFM1ALgO2Z2RCKNEdpqPuGAsx64zczGN7CeZm0vB64F\nzklMO4fwYZE+M1hv37mI0E4HUv+/IDVr/TfinUCfuy8inFjprpCuVl/YBfh4XP51hBM09Wih9LPm\nUOABd38xMe1uYLqZtdSZZ8FnCdt2XYPLjZQ8+801QGf8nC14N/CnGFfUNJyxQMz728BX3H15I8u+\nBHyG8EXi9cCdwA2F+K1OuxP2lb/F93MIt969t5DA3VcSvlw02i4HAZ8m7CuDNZJXlNt9gIEVhIrC\nzA4nHID2dPcX4vyPx8sIpxN2+guAG939C4k8ljWwvo8ROtdNiWlTCZeukvri9IbEAP17hAPmE8Bb\n3f1v1Zca3dy9ZAxQvFz3hJkd5O6PJmZd4u53xTSfJXy7fg0hGK7Kwr2gP03p5dqppD5o3H0gju+p\nu23iEJTrgC53fzb76uWYc5KZPZN4f6e7v9PMdiSc/T3O3X8V562Jwe8HCWdVCj4XAwPMbB7hntqn\nEr6EVqX24gbgy2a2N+FM3JsJwdYxyUQN9J0F7p6+7FeR6r9u5xAud0I45l9mZke6e3rIQK2+MAE4\n191/W++K4+X0MwhnlwumEq7MJPUl5vXXmffhhDO/s2qlHWXy6je3Es6un0KxjeYR9tt6DWcs8Eng\neXe/osHlXgquc/ebIIzLJfxfwxsJw0+qikNivkyI8Z6Nk6cS6vLpVPKG2iV+Nt4I/LO7rzWz19S7\nbFqeAbBRHEczk/iNLHXQ3Rko/MDnEOD/DGlFZl3AvxAuWTw5pNLWtohwUJoCvB+4w8zmuPuYHQNk\nZgcQxui8ibBd4whttg+Q/BD/n8TrvxDadk9qBMBxXNx/EQLmz+dW8KJvADe4+4OFVQ7DOra3RcCH\nKG7Lxvj8GsKXr5+kxinuQLiaUuAkrpy4+wYzW0k4A1mV2gvc/Ukzu4MQhBjwX+6ePm4RD7JfoHbf\nWVLvulX/9TGz6YQP3tMB3H2zmX2PcBY3GQDX0xeebzD4PRj4ISGwvrdW+kaY2STC2cX3u/uGPPMe\nbnn1G3d/zsy+Q/iCc7OZtRPOzJ9UTzmGMxYwszmEoG92nvmOIVvjAHffZGZPE+KAquJvDr5PaOcP\n10g+FF8m7Ds9hVWmnuuWZwBcGB8DMAn4M2FcVrpQhbOom4eyEjM7kxA4n55xiWQd4V/oklrj9Ia4\n+2bCN/xVwENm9hjhm+mXGi706HE7oY3eR2ifcYSz7jum0r2QeF34UlN1CEQ8mN9NaN+3u/tAYvY6\nUh0nXpZ8OY21zTHA26z4a2sDxpnZ88AH3P36BvIaLTa6e9aP3ibF5xMJbZX03LauVO1V4jrCWahq\nB+w7qK/vbKQOqv+GdAPjgcdTAdZzZvZP7v5M9mKZ6v7ciZdZFwJXu/tFqdmVPmsK8+qxP2Hs+O2J\nL7nj4rqfB6ZXODaMFnn1m2uApWbWRgioF7n747VWvh1igcOBPSjd78YDl5rZ/3b3Ru7WM5oMUh6X\n7ZCR7oXUe6d2HFAIfvcGjk2c/YVQ9zua2W6ps8CNtssxwMFm9o7CauNjvZl90d3rPpmQSwAcx5m9\nnjDWB8IZqqnAgLv/scJivyH8wvlbDaynk9BZ3lm4RJ+yOOb5tcS04+P0bTWOsPOPSWb2cuC1hL+b\nfjBOOzwjadVfw1bIu/CL6M2Eb+LPp5IsBnY3s9mJcY3HEXbaX1G/Qyltg38kjOc7jPIgcax7lBDo\nTnP3n1dJZ4R6uRnC7bMI7VxxvJraq8xdhA/kATIu7+Xdd1T/9YuB/7sJd834SWr2Dwk/vipcSazU\nFx6lQWb2OsJYxevc/TMZSRYD/2Zm4xNfXt4CrHT3uoY/EPro61PTvkj48ns+UDMIHGG59Bt3/62Z\n/Tfhtw6d1HHWcDvFAt+mfJ+7J04fK+O1s6wn3IkGAAu3D3v1tmaaCH73I/yAMX1VYwnhh7zHEceB\nx6s7+9BYu7wdmJh4/0bC8KjDKR+WVNVQAuCdzKyVcGBtJdyK5JPAbcB3ANx9oZktJtx25BOES+ev\nJJzNutXdewmX/Baa2SrCWNsdgA53/0rWSuOljusJB4ZfxzIAbE58m7gcuN/MPkq4tNhJGHj9/kQ+\nuxIuLxe+Ae1n4fYpT7n742a2C3Bh3J6/EC7b/BPhdh3fH0J9jRYbgL8CHzCzdYQzDxdR/qHd0GWE\n+GH+E8LwlrMIH9yF2evdfdDdV5jZ3cA3zOxcwkHz60CPu69L5LU/YejMXsBEK97WZpm7vxgHzCfX\n/QZg8KX444Q4ZvPfgQUxCPg54Yc4c4F+d/9OIvln4vjQJwgfoOsJwUEZtVc5dx80sxnxdVYQm1vf\nUf037CTCj2muTZ/ptXBf0/dROpQuqy/UPSY75nswYWjSjwljjQufNQOJy+w3En4kdK2ZXUwIZM8H\nPpLKaxZhv5gE7BHfP+/uy+MXn0dT6f9G2A1HfRvl2G8gBDBXAM9S4dhVsL1igRjAlQRxZvYCsM7d\nf1etjKPcImBeHMLST4jFXqy+SHUx+L2FMLT1bcAOiXZ5yt1fcPenzeybhDPoG4BnCF9QHnT3hxJ5\n7U242jUNGJ84rv3e3cuumJrZHoQ2XJExvrg6b/xWGgPx8RzhtPXdZNzuhzAo/TLCt9gthHvpfht4\nZSLNPxK+FWwmDIT+fpV135dYd/JxbSrdaYQf5G0mnGU+ITW/cLuczHwI9268JZZ7M+FHFD8A2hup\nq9HyIJxhvym+Po4w3rBwS60j4rafHOdPi+9nJpZvidOOrJD/URl1WajffRLpdge+S+hwGwjjE3ep\ns40zb1XEGL6tUyx/zVseAf+L8CG5Jfa3O4HDU3V/ImG81mbgF8DBVfJTe9VR97HPJ2/ndGyjfUf1\nn0s73UbqdlaJeW+I23twPX2h3u0n3JUhq15XpdIdTLi38ibCL9n/OSOvrM+aVVXWXfOYMMLtkWu/\nSaTblRD8fq2OMmyXWKDCulcxNm+DlowDXkb4AreBEJe9m3DVPnkbtKw2eooKt3akePzLOq4dmUi3\nE+HL/JOEAPj7hJslpPexrPatFYM0fBs0ixnIS5SZ/Rj4nbuPuX+vERGph5kdRTizNdkbPQskI87C\nH4H8Hpjj7o+MbGleehQHZBvOf26TEWRmu5vZ2wjfjtLjmEREXmrG5F0umpmFv9CdSrj3/GIFv/lS\nHFBdnneBkNHlWsK9mP/d3W8f6cKIiAwzXc4ce+YShjSsAN5RI600TnFAFRoCISIiIiJNRUMgRERE\nRKSpKAAWERERkaaiAFhEREREmooCYBERERFpKgqARURERKSpKAAWERERkaaiAFhEREREmooCYBER\nERFpKv8fsQJXBoAkMjIAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x108495400>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "years = (2016, 2015, 2014)\n",
    "\n",
    "plt.figure(figsize=(12,2))\n",
    "\n",
    "f,axes = plt.subplots(len(years)) #, sharex=True)\n",
    "#plt.subplots_adjust(hspace=0)\n",
    "\n",
    "for ax,year in zip(axes, years):\n",
    "    drange = snow_year(year)\n",
    "    df = fetch_ratings(*drange)\n",
    "    stacked_color_timeline(df, ax=ax)\n",
    "    ax.set_xlim(drange)\n",
    "    \n",
    "#ax.xaxis.set_major_locator(matplotlib.dates.MonthLocator())\n",
    "#ax.xaxis.set_major_formatter(matplotlib.dates.DateFormatter('%e %b %y'))\n",
    "\n",
    "plt.savefig('rating_history.pdf')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.0"
  },
  "toc": {
   "toc_cell": false,
   "toc_number_sections": true,
   "toc_threshold": 4,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}