fec_api_vs_bulk.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Populating the interactive namespace from numpy and matplotlib\n"
     ]
    }
   ],
   "source": [
    "%pylab inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import json\n",
    "from copy import deepcopy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Object `logging.basicConfig` not found.\n"
     ]
    }
   ],
   "source": [
    "logging.basicConfig?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import logging\n",
    "logging.basicConfig(format='%(asctime)s : %(levelname)s : %(message)s')\n",
    "logging.getLogger(\"requests\").setLevel(logging.ERROR) # silencing requests logging \n",
    "\n",
    "# Logging for this notebook\n",
    "logger = logging.getLogger()\n",
    "logger.setLevel(logging.INFO) # set this to whatever you'd like"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "BASE_URL = 'http://api.open.fec.gov/v1'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "API_KEY = open(os.path.expanduser('~/.api-keys/data.gov'),'r').read().strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Utils"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def all_results(endpoint, params):\n",
    "    _params = deepcopy(params)\n",
    "    _params.update({'api_key': API_KEY})\n",
    "    _url = BASE_URL+endpoint\n",
    "    logging.info('querying endpoint: {}'.format(_url))\n",
    "    \n",
    "    initial_resp = requests.get(_url, params=_params)\n",
    "    \n",
    "    logging.debug('full url eg: {}'.format(initial_resp.url))\n",
    "            \n",
    "    initial_data = initial_resp.json()\n",
    "    \n",
    "    num_pages = initial_data['pagination']['pages']\n",
    "    num_records = initial_data['pagination']['count']\n",
    "    logging.info('{p} pages to be retrieved, with {n} records'.format(\n",
    "            p=num_pages, n=num_records))\n",
    "    \n",
    "    current_page = initial_data['pagination']['page']\n",
    "    logging.debug('page {} retrieved'.format(current_page))\n",
    "    \n",
    "    for record in initial_data['results']:\n",
    "        yield record\n",
    "    \n",
    "    while current_page < num_pages:\n",
    "        current_page += 1\n",
    "        _params.update({'page': current_page})\n",
    "        _data = requests.get(_url, params=_params).json()\n",
    "        logging.debug('page {} retrieved'.format(current_page))\n",
    "        for record in _data['results']:\n",
    "            yield record\n",
    "            \n",
    "    logging.info('all pages retrieved')\n",
    "    \n",
    "def count_results(endpoint, params):\n",
    "    _params = deepcopy(params)\n",
    "    _params.update({'api_key': API_KEY})\n",
    "    _url = BASE_URL+endpoint\n",
    "    \n",
    "    _data = requests.get(_url, params=_params).json()\n",
    "    \n",
    "    return _data['pagination']['count']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Candidates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "q_all_2012_candidates = {\n",
    "    \"cycle\": 2012,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3022"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_results('/candidates', q_all_2012_candidates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "INFO:root:querying endpoint: http://api.open.fec.gov/v1/candidates\n",
      "INFO:root:152 pages to be retrieved, with 3022 records\n",
      "INFO:root:all pages retrieved\n"
     ]
    }
   ],
   "source": [
    "candidates_2012 = [c for c in all_results('/candidates', q_all_2012_candidates)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{u'active_through': 2012,\n",
       "  u'candidate_id': u'P80003338',\n",
       "  u'candidate_status': u'N',\n",
       "  u'candidate_status_full': u'Not yet a statutory candidate',\n",
       "  u'cycles': [2008, 2010, 2012],\n",
       "  u'district': None,\n",
       "  u'election_years': [2008, 2012],\n",
       "  u'incumbent_challenge': u'I',\n",
       "  u'incumbent_challenge_full': u'Incumbent',\n",
       "  u'name': u'OBAMA, BARACK',\n",
       "  u'office': u'P',\n",
       "  u'office_full': u'President',\n",
       "  u'party': u'DEM',\n",
       "  u'party_full': u'Democratic Party',\n",
       "  u'state': u'US'}]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[c for c in candidates_2012 if 'OBAMA' in c['name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "candidates_2012_df = pd.DataFrame(candidates_2012)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>2011</th>\n",
       "      <th>2331</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>active_through</th>\n",
       "      <td>2012</td>\n",
       "      <td>2012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>candidate_id</th>\n",
       "      <td>P80003338</td>\n",
       "      <td>P80003353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>candidate_status</th>\n",
       "      <td>N</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>candidate_status_full</th>\n",
       "      <td>Not yet a statutory candidate</td>\n",
       "      <td>Statutory candidate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cycles</th>\n",
       "      <td>[2008, 2010, 2012]</td>\n",
       "      <td>[2008, 2010, 2012]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>district</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>election_years</th>\n",
       "      <td>[2008, 2012]</td>\n",
       "      <td>[2008, 2012]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>incumbent_challenge</th>\n",
       "      <td>I</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>incumbent_challenge_full</th>\n",
       "      <td>Incumbent</td>\n",
       "      <td>Challenger</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>OBAMA, BARACK</td>\n",
       "      <td>ROMNEY, MITT / PAUL D. RYAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>office</th>\n",
       "      <td>P</td>\n",
       "      <td>P</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>office_full</th>\n",
       "      <td>President</td>\n",
       "      <td>President</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>party</th>\n",
       "      <td>DEM</td>\n",
       "      <td>REP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>party_full</th>\n",
       "      <td>Democratic Party</td>\n",
       "      <td>Republican Party</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <td>US</td>\n",
       "      <td>US</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   2011  \\\n",
       "active_through                                     2012   \n",
       "candidate_id                                  P80003338   \n",
       "candidate_status                                      N   \n",
       "candidate_status_full     Not yet a statutory candidate   \n",
       "cycles                               [2008, 2010, 2012]   \n",
       "district                                           None   \n",
       "election_years                             [2008, 2012]   \n",
       "incumbent_challenge                                   I   \n",
       "incumbent_challenge_full                      Incumbent   \n",
       "name                                      OBAMA, BARACK   \n",
       "office                                                P   \n",
       "office_full                                   President   \n",
       "party                                               DEM   \n",
       "party_full                             Democratic Party   \n",
       "state                                                US   \n",
       "\n",
       "                                                 2331  \n",
       "active_through                                   2012  \n",
       "candidate_id                                P80003353  \n",
       "candidate_status                                    C  \n",
       "candidate_status_full             Statutory candidate  \n",
       "cycles                             [2008, 2010, 2012]  \n",
       "district                                         None  \n",
       "election_years                           [2008, 2012]  \n",
       "incumbent_challenge                                 C  \n",
       "incumbent_challenge_full                   Challenger  \n",
       "name                      ROMNEY, MITT / PAUL D. RYAN  \n",
       "office                                              P  \n",
       "office_full                                 President  \n",
       "party                                             REP  \n",
       "party_full                           Republican Party  \n",
       "state                                              US  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates_2012_df[candidates_2012_df.name.str.match('romney|obama', case=False)].T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "104               BACHMANN, MICHELE\n",
       "141           BARR, ROSEANNE CHERRI\n",
       "333                 BROWN, HARLEY D\n",
       "1186                HERMAN, RAPHAEL\n",
       "1208            HILL, CHRISTOPHER V\n",
       "1404                   KARGER, FRED\n",
       "1614                  LINDSAY, PETA\n",
       "1769           MCCALL, JAMES HATTON\n",
       "1838                MESPLAY, KENT P\n",
       "2203        RAKOWITZ, ARTHUR FABIAN\n",
       "2331    ROMNEY, MITT / PAUL D. RYAN\n",
       "2697          TERRY, RANDALL A. MR.\n",
       "2877          WELLS, ROBERT CARR JR\n",
       "2909      WIFORD, SAMUEL TIMOTHY II\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates_2012_df[(candidates_2012_df.office == 'P') & (candidates_2012_df.candidate_status.str.match('C'))].name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bulk CSV (cn12.txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "resp = requests.get('http://www.fec.gov/finance/disclosure/metadata/cn_header_file.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cn_headers = resp.content.strip().split(',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cn12 = pd.read_csv('_data/cn.txt', sep='|', header=None, names=cn_headers,\n",
    "                   dtype={'CAND_OFFICE_DISTRICT':np.object, 'CAND_ELECTION_YR':np.object})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CAND_ID',\n",
       " 'CAND_NAME',\n",
       " 'CAND_PTY_AFFILIATION',\n",
       " 'CAND_ELECTION_YR',\n",
       " 'CAND_OFFICE_ST',\n",
       " 'CAND_OFFICE',\n",
       " 'CAND_OFFICE_DISTRICT',\n",
       " 'CAND_ICI',\n",
       " 'CAND_STATUS',\n",
       " 'CAND_PCC',\n",
       " 'CAND_ST1',\n",
       " 'CAND_ST2',\n",
       " 'CAND_CITY',\n",
       " 'CAND_ST',\n",
       " 'CAND_ZIP']"
      ]
     },
     "execution_count": 328,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cn_headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "map_fieldnames = {\n",
    "    'CAND_PTY_AFFILIATION': 'party',\n",
    "    'CAND_ELECTION_YR': 'cycle',\n",
    "    'CAND_OFFICE_ST': 'state',\n",
    "    'CAND_OFFICE': 'office',\n",
    "    'CAND_OFFICE_DISTRICT': 'district',\n",
    "    'CAND_ICI': 'incumbent_challenge',\n",
    "    'CAND_STATUS': 'candidate_status',\n",
    "    'CAND_NAME': 'name',\n",
    "    'CAND_ID': 'candidate_id',\n",
    "    'CAND_PCC': 'principal_committee',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cn12_rename = cn12.rename(columns=map_fieldnames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "comparison = cn12_rename.set_index('candidate_id').join(\n",
    "    candidates_2012_df.set_index('candidate_id'),\n",
    "    how='left', lsuffix='__CSV', rsuffix='__API')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "H    2066\n",
       "P     427\n",
       "S     423\n",
       "dtype: int64"
      ]
     },
     "execution_count": 332,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison.office__API.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "H    2595\n",
       "S     481\n",
       "P     438\n",
       "dtype: int64"
      ]
     },
     "execution_count": 333,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison.office__CSV[comparison.cycle == '2012'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2916, 28)"
      ]
     },
     "execution_count": 339,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison[comparison.office__API.notnull()].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1281, 28)"
      ]
     },
     "execution_count": 342,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison[(comparison.candidate_status__API.notnull()) & (comparison.candidate_status__API != comparison.candidate_status__CSV)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 28)"
      ]
     },
     "execution_count": 343,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison[(comparison.office__API.notnull()) & (comparison.office__API != comparison.office__CSV)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "00    689\n",
       "02    243\n",
       "01    242\n",
       "03    218\n",
       "04    160\n",
       "05    138\n",
       "07    114\n",
       "08    102\n",
       "06     98\n",
       "09     65\n",
       "13     63\n",
       "10     63\n",
       "11     46\n",
       "12     42\n",
       "17     33\n",
       "14     33\n",
       "19     31\n",
       "20     26\n",
       "23     24\n",
       "24     22\n",
       "16     20\n",
       "18     20\n",
       "15     18\n",
       "25     16\n",
       "27     14\n",
       "28     13\n",
       "21     13\n",
       "22     12\n",
       "26     11\n",
       "29     10\n",
       "36     10\n",
       "33      9\n",
       "32      9\n",
       "30      9\n",
       "47      8\n",
       "37      7\n",
       "50      6\n",
       "31      5\n",
       "52      5\n",
       "35      4\n",
       "45      4\n",
       "34      4\n",
       "48      4\n",
       "39      3\n",
       "46      3\n",
       "42      3\n",
       "51      3\n",
       "53      3\n",
       "38      2\n",
       "43      2\n",
       "41      2\n",
       "49      1\n",
       "44      1\n",
       "40      1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 346,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison[comparison.name__API.isnull()].district__CSV.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "nonmatches = comparison[comparison.name__API.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>office__CSV</th>\n",
       "      <th>H</th>\n",
       "      <th>P</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>district__CSV</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00</th>\n",
       "      <td>70</td>\n",
       "      <td>181</td>\n",
       "      <td>438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>242</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02</th>\n",
       "      <td>243</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03</th>\n",
       "      <td>218</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04</th>\n",
       "      <td>160</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05</th>\n",
       "      <td>138</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06</th>\n",
       "      <td>98</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>07</th>\n",
       "      <td>114</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>08</th>\n",
       "      <td>102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09</th>\n",
       "      <td>65</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>63</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>26</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>14</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "office__CSV      H    P    S\n",
       "district__CSV               \n",
       "00              70  181  438\n",
       "01             242  NaN  NaN\n",
       "02             243  NaN  NaN\n",
       "03             218  NaN  NaN\n",
       "04             160  NaN  NaN\n",
       "05             138  NaN  NaN\n",
       "06              98  NaN  NaN\n",
       "07             114  NaN  NaN\n",
       "08             102  NaN  NaN\n",
       "09              65  NaN  NaN\n",
       "10              63  NaN  NaN\n",
       "11              46  NaN  NaN\n",
       "12              42  NaN  NaN\n",
       "13              63  NaN  NaN\n",
       "14              33  NaN  NaN\n",
       "15              18  NaN  NaN\n",
       "16              20  NaN  NaN\n",
       "17              33  NaN  NaN\n",
       "18              20  NaN  NaN\n",
       "19              31  NaN  NaN\n",
       "20              26  NaN  NaN\n",
       "21              13  NaN  NaN\n",
       "22              12  NaN  NaN\n",
       "23              24  NaN  NaN\n",
       "24              22  NaN  NaN\n",
       "25              16  NaN  NaN\n",
       "26              11  NaN  NaN\n",
       "27              14  NaN  NaN\n",
       "28              13  NaN  NaN\n",
       "29              10  NaN  NaN\n",
       "30               9  NaN  NaN\n",
       "31               5  NaN  NaN\n",
       "32               9  NaN  NaN\n",
       "33               9  NaN  NaN\n",
       "34               4  NaN  NaN\n",
       "35               4  NaN  NaN\n",
       "36              10  NaN  NaN\n",
       "37               7  NaN  NaN\n",
       "38               2  NaN  NaN\n",
       "39               3  NaN  NaN\n",
       "40               1  NaN  NaN\n",
       "41               2  NaN  NaN\n",
       "42               3  NaN  NaN\n",
       "43               2  NaN  NaN\n",
       "44               1  NaN  NaN\n",
       "45               4  NaN  NaN\n",
       "46               3  NaN  NaN\n",
       "47               8  NaN  NaN\n",
       "48               4  NaN  NaN\n",
       "49               1  NaN  NaN\n",
       "50               6  NaN  NaN\n",
       "51               3  NaN  NaN\n",
       "52               5  NaN  NaN\n",
       "53               3  NaN  NaN"
      ]
     },
     "execution_count": 350,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nonmatches.pivot_table(index='district__CSV',\n",
    "                       columns='office__CSV',\n",
    "                       values='name__CSV',\n",
    "                       aggfunc=np.size)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>candidate_id</th>\n",
       "      <th>name</th>\n",
       "      <th>party</th>\n",
       "      <th>cycle</th>\n",
       "      <th>state</th>\n",
       "      <th>office</th>\n",
       "      <th>district</th>\n",
       "      <th>incumbent_challenge</th>\n",
       "      <th>candidate_status</th>\n",
       "      <th>principal_committee</th>\n",
       "      <th>CAND_ST1</th>\n",
       "      <th>CAND_ST2</th>\n",
       "      <th>CAND_CITY</th>\n",
       "      <th>CAND_ST</th>\n",
       "      <th>CAND_ZIP</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [candidate_id, name, party, cycle, state, office, district, incumbent_challenge, candidate_status, principal_committee, CAND_ST1, CAND_ST2, CAND_CITY, CAND_ST, CAND_ZIP]\n",
       "Index: []"
      ]
     },
     "execution_count": 352,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cn12_rename[cn12_rename.district == \"99\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>office__CSV</th>\n",
       "      <th>H</th>\n",
       "      <th>P</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>district__CSV</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00</th>\n",
       "      <td>129</td>\n",
       "      <td>608</td>\n",
       "      <td>861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>423</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>02</th>\n",
       "      <td>468</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>03</th>\n",
       "      <td>366</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>04</th>\n",
       "      <td>321</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>05</th>\n",
       "      <td>258</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06</th>\n",
       "      <td>238</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>07</th>\n",
       "      <td>223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>08</th>\n",
       "      <td>201</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>09</th>\n",
       "      <td>160</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>126</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>92</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>112</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>27</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>45</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>42</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "office__CSV      H    P    S\n",
       "district__CSV               \n",
       "00             129  608  861\n",
       "01             423  NaN  NaN\n",
       "02             468  NaN  NaN\n",
       "03             366  NaN  NaN\n",
       "04             321  NaN  NaN\n",
       "05             258  NaN  NaN\n",
       "06             238  NaN  NaN\n",
       "07             223  NaN  NaN\n",
       "08             201  NaN  NaN\n",
       "09             160  NaN  NaN\n",
       "10             126  NaN  NaN\n",
       "11             106  NaN  NaN\n",
       "12              92  NaN  NaN\n",
       "13             112  NaN  NaN\n",
       "14              78  NaN  NaN\n",
       "15              46  NaN  NaN\n",
       "16              45  NaN  NaN\n",
       "17              51  NaN  NaN\n",
       "18              51  NaN  NaN\n",
       "19              51  NaN  NaN\n",
       "20              40  NaN  NaN\n",
       "21              28  NaN  NaN\n",
       "22              27  NaN  NaN\n",
       "23              45  NaN  NaN\n",
       "24              36  NaN  NaN\n",
       "25              42  NaN  NaN\n",
       "26              41  NaN  NaN\n",
       "27              32  NaN  NaN\n",
       "28              20  NaN  NaN\n",
       "29              15  NaN  NaN\n",
       "30              21  NaN  NaN\n",
       "31              12  NaN  NaN\n",
       "32              17  NaN  NaN\n",
       "33              31  NaN  NaN\n",
       "34              17  NaN  NaN\n",
       "35              15  NaN  NaN\n",
       "36              38  NaN  NaN\n",
       "37              10  NaN  NaN\n",
       "38               4  NaN  NaN\n",
       "39               8  NaN  NaN\n",
       "40               2  NaN  NaN\n",
       "41               7  NaN  NaN\n",
       "42               9  NaN  NaN\n",
       "43               4  NaN  NaN\n",
       "44               5  NaN  NaN\n",
       "45               8  NaN  NaN\n",
       "46               8  NaN  NaN\n",
       "47              16  NaN  NaN\n",
       "48               8  NaN  NaN\n",
       "49               5  NaN  NaN\n",
       "50              11  NaN  NaN\n",
       "51               9  NaN  NaN\n",
       "52              12  NaN  NaN\n",
       "53               5  NaN  NaN"
      ]
     },
     "execution_count": 351,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comparison.pivot_table(index='district__CSV',\n",
    "                       columns='office__CSV',\n",
    "                       values='name__CSV',\n",
    "                       aggfunc=np.size)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Committees"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}