DB-NLP-Web-of-Support.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Pandas as a Database\n",
    "Rather than installing postrgres and Django and configuring all that You can just use a Pandas collection of DataFrame tables as your database Lets see if we can find a \"primary key\" that we can use to connect a couple of these tables\n",
    "Thanks [Jeremy Tanner](https://twitter.com/Penguin) for the max_rows trick"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_rows', 6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>id_nbr</th>\n",
       "      <th>candidate_office</th>\n",
       "      <th>candidate_file_rsn</th>\n",
       "      <th>cand_ballot_name_txt</th>\n",
       "      <th>prfx_name_cd</th>\n",
       "      <th>first_name</th>\n",
       "      <th>mdle_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>sufx_name</th>\n",
       "      <th>mailing_city_name</th>\n",
       "      <th>residence_addr_line_1</th>\n",
       "      <th>residence_addr_line_2</th>\n",
       "      <th>residence_city_name</th>\n",
       "      <th>residence_st_cd</th>\n",
       "      <th>residence_zip_code</th>\n",
       "      <th>residence_zip_plus_four</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>election_txt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2012 Primary Election</th>\n",
       "      <td>1914</td>\n",
       "      <td>District Attorney, Hood River County</td>\n",
       "      <td>12291</td>\n",
       "      <td>Brian Aaron</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brian</td>\n",
       "      <td>Douglas</td>\n",
       "      <td>Aaron</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hood River</td>\n",
       "      <td>1822 Columbia St</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hood River</td>\n",
       "      <td>OR</td>\n",
       "      <td>97031</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012 Primary Election</th>\n",
       "      <td>1461</td>\n",
       "      <td>Judge of the Circuit Court, 3rd District, Posi...</td>\n",
       "      <td>11374</td>\n",
       "      <td>Donald D Abar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Donald</td>\n",
       "      <td>D</td>\n",
       "      <td>Abar</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Salem</td>\n",
       "      <td>Exempt from public record</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012 General Election</th>\n",
       "      <td>1205</td>\n",
       "      <td>State Representative, 5th District</td>\n",
       "      <td>13540</td>\n",
       "      <td>Sandra A Abercrombie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sandra</td>\n",
       "      <td>A</td>\n",
       "      <td>Abercrombie</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medford</td>\n",
       "      <td>1935 Hart</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Medford</td>\n",
       "      <td>OR</td>\n",
       "      <td>97501</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010 General Election</th>\n",
       "      <td>1215</td>\n",
       "      <td>State Representative, 15th District</td>\n",
       "      <td>9725</td>\n",
       "      <td>Andy Olson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>andy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>olson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albany</td>\n",
       "      <td>34499 Mountain View Pl NE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albany</td>\n",
       "      <td>OR</td>\n",
       "      <td>97322</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014 Primary Election</th>\n",
       "      <td>1236</td>\n",
       "      <td>State Representative, 36th District</td>\n",
       "      <td>14997</td>\n",
       "      <td>Jennifer Williamson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>jennifer</td>\n",
       "      <td>a</td>\n",
       "      <td>williamson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Portland</td>\n",
       "      <td>615 NW 22nd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Portland</td>\n",
       "      <td>OR</td>\n",
       "      <td>97210</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014 General Election</th>\n",
       "      <td>1236</td>\n",
       "      <td>State Representative, 36th District</td>\n",
       "      <td>15488</td>\n",
       "      <td>Jennifer Williamson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>jennifer</td>\n",
       "      <td>a</td>\n",
       "      <td>williamson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Portland</td>\n",
       "      <td>615 NW 22nd</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Portland</td>\n",
       "      <td>OR</td>\n",
       "      <td>97210</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2353 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       id_nbr  \\\n",
       "election_txt                    \n",
       "2012 Primary Election    1914   \n",
       "2012 Primary Election    1461   \n",
       "2012 General Election    1205   \n",
       "...                       ...   \n",
       "2010 General Election    1215   \n",
       "2014 Primary Election    1236   \n",
       "2014 General Election    1236   \n",
       "\n",
       "                                                        candidate_office  \\\n",
       "election_txt                                                               \n",
       "2012 Primary Election               District Attorney, Hood River County   \n",
       "2012 Primary Election  Judge of the Circuit Court, 3rd District, Posi...   \n",
       "2012 General Election                 State Representative, 5th District   \n",
       "...                                                                  ...   \n",
       "2010 General Election                State Representative, 15th District   \n",
       "2014 Primary Election                State Representative, 36th District   \n",
       "2014 General Election                State Representative, 36th District   \n",
       "\n",
       "                       candidate_file_rsn  cand_ballot_name_txt prfx_name_cd  \\\n",
       "election_txt                                                                   \n",
       "2012 Primary Election               12291           Brian Aaron          NaN   \n",
       "2012 Primary Election               11374         Donald D Abar          NaN   \n",
       "2012 General Election               13540  Sandra A Abercrombie          NaN   \n",
       "...                                   ...                   ...          ...   \n",
       "2010 General Election                9725            Andy Olson          NaN   \n",
       "2014 Primary Election               14997   Jennifer Williamson          NaN   \n",
       "2014 General Election               15488   Jennifer Williamson          NaN   \n",
       "\n",
       "                      first_name mdle_name    last_name sufx_name  \\\n",
       "election_txt                                                        \n",
       "2012 Primary Election      Brian   Douglas        Aaron       NaN   \n",
       "2012 Primary Election     Donald         D         Abar       NaN   \n",
       "2012 General Election     Sandra         A  Abercrombie       NaN   \n",
       "...                          ...       ...          ...       ...   \n",
       "2010 General Election       andy       NaN        olson       NaN   \n",
       "2014 Primary Election   jennifer         a   williamson       NaN   \n",
       "2014 General Election   jennifer         a   williamson       NaN   \n",
       "\n",
       "                      mailing_city_name      residence_addr_line_1  \\\n",
       "election_txt                                                         \n",
       "2012 Primary Election        Hood River           1822 Columbia St   \n",
       "2012 Primary Election             Salem  Exempt from public record   \n",
       "2012 General Election           Medford                  1935 Hart   \n",
       "...                                 ...                        ...   \n",
       "2010 General Election            Albany  34499 Mountain View Pl NE   \n",
       "2014 Primary Election          Portland                615 NW 22nd   \n",
       "2014 General Election          Portland                615 NW 22nd   \n",
       "\n",
       "                      residence_addr_line_2 residence_city_name  \\\n",
       "election_txt                                                      \n",
       "2012 Primary Election                   NaN          Hood River   \n",
       "2012 Primary Election                   NaN                 NaN   \n",
       "2012 General Election                   NaN             Medford   \n",
       "...                                     ...                 ...   \n",
       "2010 General Election                   NaN              Albany   \n",
       "2014 Primary Election                   NaN            Portland   \n",
       "2014 General Election                   NaN            Portland   \n",
       "\n",
       "                      residence_st_cd  residence_zip_code  \\\n",
       "election_txt                                                \n",
       "2012 Primary Election              OR               97031   \n",
       "2012 Primary Election             NaN                 NaN   \n",
       "2012 General Election              OR               97501   \n",
       "...                               ...                 ...   \n",
       "2010 General Election              OR               97322   \n",
       "2014 Primary Election              OR               97210   \n",
       "2014 General Election              OR               97210   \n",
       "\n",
       "                       residence_zip_plus_four  \n",
       "election_txt                                    \n",
       "2012 Primary Election                      NaN  \n",
       "2012 Primary Election                      NaN  \n",
       "2012 General Election                      NaN  \n",
       "...                                        ...  \n",
       "2010 General Election                      NaN  \n",
       "2014 Primary Election                      NaN  \n",
       "2014 General Election                      NaN  \n",
       "\n",
       "[2353 rows x 16 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates = pd.DataFrame.from_csv('public.raw_candidate_filings.csv').sort_values(by='last_name')  # id_nmbr\n",
    "candidates[[col for col in candidates.columns if 'name' in col or 'id' in col]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>committee_name</th>\n",
       "      <th>committee_type</th>\n",
       "      <th>committee_subtype</th>\n",
       "      <th>candidate_office</th>\n",
       "      <th>candidate_office_group</th>\n",
       "      <th>treasurer_first_name</th>\n",
       "      <th>treasurer_last_name</th>\n",
       "      <th>candidate_first_name</th>\n",
       "      <th>candidate_last_name</th>\n",
       "      <th>candidate_maling_address</th>\n",
       "      <th>candidate_work_phone</th>\n",
       "      <th>candidate_residence_phone</th>\n",
       "      <th>candidate_fax</th>\n",
       "      <th>candidate_email</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>committee_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5505</th>\n",
       "      <td>13 Enviros PAC</td>\n",
       "      <td>PAC</td>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kevin</td>\n",
       "      <td>Neely</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15790</th>\n",
       "      <td>19thofJune Green Chapter</td>\n",
       "      <td>PAC</td>\n",
       "      <td>Political Party</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Jorden</td>\n",
       "      <td>Leonard</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13986</th>\n",
       "      <td>2020 Vision for Oregon</td>\n",
       "      <td>PAC</td>\n",
       "      <td>Measure</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Al</td>\n",
       "      <td>LeMay</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12896</th>\n",
       "      <td>shepherd for sheriff committee</td>\n",
       "      <td>CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Klamath County</td>\n",
       "      <td>County Sheriff</td>\n",
       "      <td>peter</td>\n",
       "      <td>shepherd</td>\n",
       "      <td>peter</td>\n",
       "      <td>shepherd</td>\n",
       "      <td>5640 Jennifer Lane Klamath Falls OR 97603</td>\n",
       "      <td>(541)885-6629</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>shepstir@msn.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16132</th>\n",
       "      <td>susiejonesformhccboard</td>\n",
       "      <td>CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mt. Hood Community College, Zone 1</td>\n",
       "      <td>Director</td>\n",
       "      <td>Samuel</td>\n",
       "      <td>Solano</td>\n",
       "      <td>Susie</td>\n",
       "      <td>Jones</td>\n",
       "      <td>404 SE Hale Dr. Gresham OR 97080</td>\n",
       "      <td>503 621-6316</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(503)661-3924</td>\n",
       "      <td>susie.jones52@frontier.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9406</th>\n",
       "      <td>von Nagy for Beaverton Schools</td>\n",
       "      <td>CC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Beaverton School District, Zone 4</td>\n",
       "      <td>Director</td>\n",
       "      <td>Ivan</td>\n",
       "      <td>von Nagy</td>\n",
       "      <td>Denise</td>\n",
       "      <td>von Nagy</td>\n",
       "      <td>7510 SW 189th Avenue Aloha OR 97007</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2596 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              committee_name committee_type committee_subtype  \\\n",
       "committee_id                                                                    \n",
       "5505                          13 Enviros PAC            PAC     Miscellaneous   \n",
       "15790               19thofJune Green Chapter            PAC   Political Party   \n",
       "13986                 2020 Vision for Oregon            PAC           Measure   \n",
       "...                                      ...            ...               ...   \n",
       "12896         shepherd for sheriff committee             CC               NaN   \n",
       "16132                 susiejonesformhccboard             CC               NaN   \n",
       "9406          von Nagy for Beaverton Schools             CC               NaN   \n",
       "\n",
       "                                candidate_office candidate_office_group  \\\n",
       "committee_id                                                              \n",
       "5505                                         NaN                    NaN   \n",
       "15790                                        NaN                    NaN   \n",
       "13986                                        NaN                    NaN   \n",
       "...                                          ...                    ...   \n",
       "12896                             Klamath County         County Sheriff   \n",
       "16132         Mt. Hood Community College, Zone 1               Director   \n",
       "9406           Beaverton School District, Zone 4               Director   \n",
       "\n",
       "             treasurer_first_name treasurer_last_name candidate_first_name  \\\n",
       "committee_id                                                                 \n",
       "5505                        Kevin               Neely                        \n",
       "15790                      Jorden             Leonard                        \n",
       "13986                          Al               LeMay                        \n",
       "...                           ...                 ...                  ...   \n",
       "12896                       peter            shepherd                peter   \n",
       "16132                      Samuel              Solano                Susie   \n",
       "9406                         Ivan            von Nagy               Denise   \n",
       "\n",
       "             candidate_last_name                   candidate_maling_address  \\\n",
       "committee_id                                                                  \n",
       "5505                                                                    NaN   \n",
       "15790                                                                   NaN   \n",
       "13986                                                                   NaN   \n",
       "...                          ...                                        ...   \n",
       "12896                   shepherd  5640 Jennifer Lane Klamath Falls OR 97603   \n",
       "16132                      Jones           404 SE Hale Dr. Gresham OR 97080   \n",
       "9406                    von Nagy        7510 SW 189th Avenue Aloha OR 97007   \n",
       "\n",
       "             candidate_work_phone  candidate_residence_phone  candidate_fax  \\\n",
       "committee_id                                                                  \n",
       "5505                          NaN                        NaN            NaN   \n",
       "15790                         NaN                        NaN            NaN   \n",
       "13986                         NaN                        NaN            NaN   \n",
       "...                           ...                        ...            ...   \n",
       "12896               (541)885-6629                        NaN            NaN   \n",
       "16132                503 621-6316                        NaN  (503)661-3924   \n",
       "9406                          NaN                        NaN            NaN   \n",
       "\n",
       "                         candidate_email  \n",
       "committee_id                              \n",
       "5505                                 NaN  \n",
       "15790                                NaN  \n",
       "13986                                NaN  \n",
       "...                                  ...  \n",
       "12896                   shepstir@msn.com  \n",
       "16132         susie.jones52@frontier.com  \n",
       "9406                                 NaN  \n",
       "\n",
       "[2596 rows x 14 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pacs = pd.DataFrame.from_csv('public.raw_committees.csv').sort_values(by='committee_name')  # no ID that I can find\n",
    "pacs[[col for col in pacs.columns if 'name' in col or 'id' in col or 'type' in col]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>name</th>\n",
       "      <th>acronym</th>\n",
       "      <th>pac_type</th>\n",
       "      <th>filing_effective_from</th>\n",
       "      <th>filing_type</th>\n",
       "      <th>address</th>\n",
       "      <th>campaign_phone</th>\n",
       "      <th>treasurer_name</th>\n",
       "      <th>treasurer_mailing_address</th>\n",
       "      <th>treasurer_work_phone_home_phone_fax</th>\n",
       "      <th>...</th>\n",
       "      <th>candidate_candidate_address</th>\n",
       "      <th>candidate_work_phone_home_phone_fax</th>\n",
       "      <th>candidate_mailing_address</th>\n",
       "      <th>candidate_email_address</th>\n",
       "      <th>candidate_occupation</th>\n",
       "      <th>candidate_employer</th>\n",
       "      <th>measure_election</th>\n",
       "      <th>measure_support</th>\n",
       "      <th>measure_details</th>\n",
       "      <th>committee_type</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17268</th>\n",
       "      <td>21st Century Veterans for Progress</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>01/15/2015 to present</td>\n",
       "      <td>Original</td>\n",
       "      <td>1320 Edgewater St. Suite 230 Salem, OR 97304</td>\n",
       "      <td>(971)273-0114</td>\n",
       "      <td>Jeffery D Julum</td>\n",
       "      <td>1320 Edgewater St. Suite 230 Salem, OR 97304</td>\n",
       "      <td>(703)944-8119 (503)364-1933</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15508</th>\n",
       "      <td>A Better Choice Political Action Committee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>06/10/2014 to 06/10/2014</td>\n",
       "      <td>Discontinuation</td>\n",
       "      <td>439 Pyle Drive Grants Pass, OR 97527</td>\n",
       "      <td>(541)659-4313</td>\n",
       "      <td>Jack A Brown Jr</td>\n",
       "      <td>1252 Redwood Avenue #68 Grants Pass, OR 97527</td>\n",
       "      <td>(541)659-4313 (541)476-7001</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PAC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17425</th>\n",
       "      <td>reelectbob</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/14/2015 to present</td>\n",
       "      <td>Original</td>\n",
       "      <td>59157 Cooperspur CT Saint Helens, OR 97051 - 9311</td>\n",
       "      <td>(503)397-3023</td>\n",
       "      <td>Robert J Braud</td>\n",
       "      <td>59157 Cooperspur CT Saint Helens, OR 97051 - 9311</td>\n",
       "      <td>(503)397-3023 (503)397-0181</td>\n",
       "      <td>...</td>\n",
       "      <td>59157 Cooperspur CT Saint Helens, OR 97051 - 9311</td>\n",
       "      <td>(503)397-3023 (503)397-0181</td>\n",
       "      <td>59157 Cooperspur CT Saint Helens, OR 97051 - 9311</td>\n",
       "      <td>rbraud6@gmail.com</td>\n",
       "      <td>Not Employed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13147</th>\n",
       "      <td>unbiased city council</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>05/18/2010 to 05/18/2010</td>\n",
       "      <td>Discontinuation</td>\n",
       "      <td>445 kalapuya way cottage grove, OR 97424 - 1385</td>\n",
       "      <td>(541)942-6113</td>\n",
       "      <td>diane conrad</td>\n",
       "      <td>445 kalapuya way cottage grove, OR 97424 - 1385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PAC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1415 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             name acronym       pac_type  \\\n",
       "id                                                                         \n",
       "17268          21st Century Veterans for Progress     NaN  Miscellaneous   \n",
       "15508  A Better Choice Political Action Committee     NaN            NaN   \n",
       "...                                           ...     ...            ...   \n",
       "17425                                  reelectbob     NaN            NaN   \n",
       "13147                       unbiased city council     NaN            NaN   \n",
       "\n",
       "          filing_effective_from      filing_type  \\\n",
       "id                                                 \n",
       "17268     01/15/2015 to present         Original   \n",
       "15508  06/10/2014 to 06/10/2014  Discontinuation   \n",
       "...                         ...              ...   \n",
       "17425     05/14/2015 to present         Original   \n",
       "13147  05/18/2010 to 05/18/2010  Discontinuation   \n",
       "\n",
       "                                                 address campaign_phone  \\\n",
       "id                                                                        \n",
       "17268       1320 Edgewater St. Suite 230 Salem, OR 97304  (971)273-0114   \n",
       "15508               439 Pyle Drive Grants Pass, OR 97527  (541)659-4313   \n",
       "...                                                  ...            ...   \n",
       "17425  59157 Cooperspur CT Saint Helens, OR 97051 - 9311  (503)397-3023   \n",
       "13147    445 kalapuya way cottage grove, OR 97424 - 1385  (541)942-6113   \n",
       "\n",
       "        treasurer_name                          treasurer_mailing_address  \\\n",
       "id                                                                          \n",
       "17268  Jeffery D Julum       1320 Edgewater St. Suite 230 Salem, OR 97304   \n",
       "15508  Jack A Brown Jr      1252 Redwood Avenue #68 Grants Pass, OR 97527   \n",
       "...                ...                                                ...   \n",
       "17425   Robert J Braud  59157 Cooperspur CT Saint Helens, OR 97051 - 9311   \n",
       "13147     diane conrad    445 kalapuya way cottage grove, OR 97424 - 1385   \n",
       "\n",
       "      treasurer_work_phone_home_phone_fax      ...        \\\n",
       "id                                             ...         \n",
       "17268         (703)944-8119 (503)364-1933      ...         \n",
       "15508         (541)659-4313 (541)476-7001      ...         \n",
       "...                                   ...      ...         \n",
       "17425         (503)397-3023 (503)397-0181      ...         \n",
       "13147                                 NaN      ...         \n",
       "\n",
       "                             candidate_candidate_address  \\\n",
       "id                                                         \n",
       "17268                                                NaN   \n",
       "15508                                                NaN   \n",
       "...                                                  ...   \n",
       "17425  59157 Cooperspur CT Saint Helens, OR 97051 - 9311   \n",
       "13147                                                NaN   \n",
       "\n",
       "      candidate_work_phone_home_phone_fax  \\\n",
       "id                                          \n",
       "17268                                 NaN   \n",
       "15508                                 NaN   \n",
       "...                                   ...   \n",
       "17425         (503)397-3023 (503)397-0181   \n",
       "13147                                 NaN   \n",
       "\n",
       "                               candidate_mailing_address  \\\n",
       "id                                                         \n",
       "17268                                                NaN   \n",
       "15508                                                NaN   \n",
       "...                                                  ...   \n",
       "17425  59157 Cooperspur CT Saint Helens, OR 97051 - 9311   \n",
       "13147                                                NaN   \n",
       "\n",
       "      candidate_email_address candidate_occupation candidate_employer  \\\n",
       "id                                                                      \n",
       "17268                     NaN                  NaN                NaN   \n",
       "15508                     NaN                  NaN                NaN   \n",
       "...                       ...                  ...                ...   \n",
       "17425       rbraud6@gmail.com         Not Employed                NaN   \n",
       "13147                     NaN                  NaN                NaN   \n",
       "\n",
       "      measure_election measure_support measure_details committee_type  \n",
       "id                                                                     \n",
       "17268              NaN             NaN             NaN            PAC  \n",
       "15508              NaN             NaN             NaN            PAC  \n",
       "...                ...             ...             ...            ...  \n",
       "17425              NaN             NaN             NaN             CC  \n",
       "13147              NaN             NaN             NaN            PAC  \n",
       "\n",
       "[1415 rows x 24 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pacs_scraped = pd.DataFrame.from_csv('public.raw_committees_scraped.csv', index_col='id').sort_values(by='name')  # id\n",
    "pacs_scraped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>election_year</th>\n",
       "      <th>office_group</th>\n",
       "      <th>id_nbr</th>\n",
       "      <th>office</th>\n",
       "      <th>candidate_office</th>\n",
       "      <th>candidate_file_rsn</th>\n",
       "      <th>file_mthd_ind</th>\n",
       "      <th>filetype_descr</th>\n",
       "      <th>party_descr</th>\n",
       "      <th>major_party_ind</th>\n",
       "      <th>...</th>\n",
       "      <th>residence_city_name</th>\n",
       "      <th>residence_st_cd</th>\n",
       "      <th>residence_zip_code</th>\n",
       "      <th>residence_zip_plus_four</th>\n",
       "      <th>home_phone</th>\n",
       "      <th>cell_phone</th>\n",
       "      <th>fax_phone</th>\n",
       "      <th>email</th>\n",
       "      <th>work_phone</th>\n",
       "      <th>web_address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>election_txt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2014 General Election</th>\n",
       "      <td>2014</td>\n",
       "      <td>US Representative</td>\n",
       "      <td>303</td>\n",
       "      <td>3rd District</td>\n",
       "      <td>US Representative, 3rd District</td>\n",
       "      <td>15772</td>\n",
       "      <td>P</td>\n",
       "      <td>Minor Party</td>\n",
       "      <td>Constitution</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>Portland</td>\n",
       "      <td>OR</td>\n",
       "      <td>97214</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(503)314-6597</td>\n",
       "      <td>(503)314-6597</td>\n",
       "      <td>(503)573-1936</td>\n",
       "      <td>jbuchal@mbllp.com</td>\n",
       "      <td>(503)227-1011</td>\n",
       "      <td>www.buchal.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014 General Election</th>\n",
       "      <td>2014</td>\n",
       "      <td>US Representative</td>\n",
       "      <td>304</td>\n",
       "      <td>4th District</td>\n",
       "      <td>US Representative, 4th District</td>\n",
       "      <td>15773</td>\n",
       "      <td>P</td>\n",
       "      <td>Minor Party</td>\n",
       "      <td>Progressive</td>\n",
       "      <td>N</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>campaign@defazioforcongress.org</td>\n",
       "      <td>(541)485-1622</td>\n",
       "      <td>www.defazioforcongress.org</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008 Primary Election</th>\n",
       "      <td>2008</td>\n",
       "      <td>State Representative</td>\n",
       "      <td>1253</td>\n",
       "      <td>53rd District</td>\n",
       "      <td>State Representative, 53rd District</td>\n",
       "      <td>221</td>\n",
       "      <td>P</td>\n",
       "      <td>Fee</td>\n",
       "      <td>Republican</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>Sunriver</td>\n",
       "      <td>OR</td>\n",
       "      <td>97707</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(541)598-7560</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>electgenew@msn.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>www.genewhisnant.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008 Primary Election</th>\n",
       "      <td>2008</td>\n",
       "      <td>State Representative</td>\n",
       "      <td>1226</td>\n",
       "      <td>26th District</td>\n",
       "      <td>State Representative, 26th District</td>\n",
       "      <td>41</td>\n",
       "      <td>W</td>\n",
       "      <td>Fee</td>\n",
       "      <td>Republican</td>\n",
       "      <td>Y</td>\n",
       "      <td>...</td>\n",
       "      <td>Wilsonville</td>\n",
       "      <td>OR</td>\n",
       "      <td>97070</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(503)351-2955</td>\n",
       "      <td>NaN</td>\n",
       "      <td>(509)479-6170</td>\n",
       "      <td>wingardreport@yahoo.com</td>\n",
       "      <td>(503)351-2955</td>\n",
       "      <td>www.MattWingard.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2353 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                       election_year          office_group  id_nbr  \\\n",
       "election_txt                                                         \n",
       "2014 General Election           2014     US Representative     303   \n",
       "2014 General Election           2014     US Representative     304   \n",
       "...                              ...                   ...     ...   \n",
       "2008 Primary Election           2008  State Representative    1253   \n",
       "2008 Primary Election           2008  State Representative    1226   \n",
       "\n",
       "                              office                     candidate_office  \\\n",
       "election_txt                                                                \n",
       "2014 General Election   3rd District      US Representative, 3rd District   \n",
       "2014 General Election   4th District      US Representative, 4th District   \n",
       "...                              ...                                  ...   \n",
       "2008 Primary Election  53rd District  State Representative, 53rd District   \n",
       "2008 Primary Election  26th District  State Representative, 26th District   \n",
       "\n",
       "                       candidate_file_rsn file_mthd_ind filetype_descr  \\\n",
       "election_txt                                                             \n",
       "2014 General Election               15772             P    Minor Party   \n",
       "2014 General Election               15773             P    Minor Party   \n",
       "...                                   ...           ...            ...   \n",
       "2008 Primary Election                 221             P            Fee   \n",
       "2008 Primary Election                  41             W            Fee   \n",
       "\n",
       "                        party_descr major_party_ind  \\\n",
       "election_txt                                          \n",
       "2014 General Election  Constitution               N   \n",
       "2014 General Election   Progressive               N   \n",
       "...                             ...             ...   \n",
       "2008 Primary Election    Republican               Y   \n",
       "2008 Primary Election    Republican               Y   \n",
       "\n",
       "                                  ...             residence_city_name  \\\n",
       "election_txt                      ...                                   \n",
       "2014 General Election             ...                        Portland   \n",
       "2014 General Election             ...                             NaN   \n",
       "...                               ...                             ...   \n",
       "2008 Primary Election             ...                        Sunriver   \n",
       "2008 Primary Election             ...                     Wilsonville   \n",
       "\n",
       "                      residence_st_cd residence_zip_code  \\\n",
       "election_txt                                               \n",
       "2014 General Election              OR              97214   \n",
       "2014 General Election             NaN                NaN   \n",
       "...                               ...                ...   \n",
       "2008 Primary Election              OR              97707   \n",
       "2008 Primary Election              OR              97070   \n",
       "\n",
       "                      residence_zip_plus_four     home_phone     cell_phone  \\\n",
       "election_txt                                                                  \n",
       "2014 General Election                     NaN  (503)314-6597  (503)314-6597   \n",
       "2014 General Election                     NaN            NaN            NaN   \n",
       "...                                       ...            ...            ...   \n",
       "2008 Primary Election                     NaN  (541)598-7560            NaN   \n",
       "2008 Primary Election                     NaN  (503)351-2955            NaN   \n",
       "\n",
       "                           fax_phone                            email  \\\n",
       "election_txt                                                            \n",
       "2014 General Election  (503)573-1936                jbuchal@mbllp.com   \n",
       "2014 General Election            NaN  campaign@defazioforcongress.org   \n",
       "...                              ...                              ...   \n",
       "2008 Primary Election            NaN               electgenew@msn.com   \n",
       "2008 Primary Election  (509)479-6170          wingardreport@yahoo.com   \n",
       "\n",
       "                          work_phone                 web_address  \n",
       "election_txt                                                      \n",
       "2014 General Election  (503)227-1011              www.buchal.com  \n",
       "2014 General Election  (541)485-1622  www.defazioforcongress.org  \n",
       "...                              ...                         ...  \n",
       "2008 Primary Election            NaN        www.genewhisnant.com  \n",
       "2008 Primary Election  (503)351-2955         www.MattWingard.com  \n",
       "\n",
       "[2353 rows x 51 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "candidates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "pd.set_option(\"display.max_rows\", 101)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Vectorizing\n",
    "Thanks [@larsmans](http://stackoverflow.com/users/166749/larsmans) \n",
    "for a speedy [stemming vectorizer](http://stackoverflow.com/a/26212970/623735)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "!pip install pystemmer\n",
    "import Stemmer\n",
    "english_stemmer = Stemmer.Stemmer('en')\n",
    "class StemmedTfidfVectorizer(TfidfVectorizer):\n",
    "    def build_analyzer(self):\n",
    "        analyzer = super(TfidfVectorizer, self).build_analyzer()\n",
    "        return lambda doc: english_stemmer.stemWords(analyzer(doc))\n",
    "     \n",
    "tfidf = StemmedTfidfVectorizer(min_df=1, stop_words='english', analyzer='word', ngram_range=(1, 2))\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}