house_tracker.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import wikipediaapi\n",
    "import re\n",
    "import os\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'AL': 'Alabama',\n",
       " 'AK': 'Alaska',\n",
       " 'AZ': 'Arizona',\n",
       " 'AR': 'Arkansas',\n",
       " 'CA': 'California',\n",
       " 'CO': 'Colorado',\n",
       " 'CT': 'Connecticut',\n",
       " 'DE': 'Delaware',\n",
       " 'FL': 'Florida',\n",
       " 'GA': 'Georgia',\n",
       " 'HI': 'Hawaii',\n",
       " 'ID': 'Idaho',\n",
       " 'IL': 'Illinois',\n",
       " 'IN': 'Indiana',\n",
       " 'IA': 'Iowa',\n",
       " 'KS': 'Kansas',\n",
       " 'KY': 'Kentucky',\n",
       " 'LA': 'Louisiana',\n",
       " 'ME': 'Maine',\n",
       " 'MD': 'Maryland',\n",
       " 'MA': 'Massachusetts',\n",
       " 'MI': 'Michigan',\n",
       " 'MN': 'Minnesota',\n",
       " 'MS': 'Mississippi',\n",
       " 'MO': 'Missouri',\n",
       " 'MT': 'Montana',\n",
       " 'NE': 'Nebraska',\n",
       " 'NV': 'Nevada',\n",
       " 'NH': 'New Hampshire',\n",
       " 'NJ': 'New Jersey',\n",
       " 'NM': 'New Mexico',\n",
       " 'NY': 'New York',\n",
       " 'NC': 'North Carolina',\n",
       " 'ND': 'North Dakota',\n",
       " 'OH': 'Ohio',\n",
       " 'OK': 'Oklahoma',\n",
       " 'OR': 'Oregon',\n",
       " 'PA': 'Pennsylvania',\n",
       " 'RI': 'Rhode Island',\n",
       " 'SC': 'South Carolina',\n",
       " 'SD': 'South Dakota',\n",
       " 'TN': 'Tennessee',\n",
       " 'TX': 'Texas',\n",
       " 'UT': 'Utah',\n",
       " 'VT': 'Vermont',\n",
       " 'VA': 'Virginia',\n",
       " 'WA': 'Washington',\n",
       " 'WV': 'West Virginia',\n",
       " 'WI': 'Wisconsin',\n",
       " 'WY': 'Wyoming'}"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(os.path.join('public', '50_states.json'), 'r') as f:\n",
    "    states = json.load(f)\n",
    "states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1. James P. McGovern</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2. Gabe Vasquez</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3. Stephen F. Lynch</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4. Matt Cartwright</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5. Earl Blumenauer</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>187. Lloyd Doggett</td>\n",
       "      <td>Texas</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>188. Ken Buck</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>189. Josh Harder</td>\n",
       "      <td>California</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>190. Kevin Mullin</td>\n",
       "      <td>California</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>191. Chellie Pingree</td>\n",
       "      <td>Maine</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>191 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name          state  district\n",
       "0    1. James P. McGovern  Massachusetts         2\n",
       "1         2. Gabe Vasquez     New Mexico         2\n",
       "2     3. Stephen F. Lynch  Massachusetts         8\n",
       "3      4. Matt Cartwright   Pennsylvania         8\n",
       "4      5. Earl Blumenauer         Oregon         3\n",
       "..                    ...            ...       ...\n",
       "186    187. Lloyd Doggett          Texas        37\n",
       "187         188. Ken Buck       Colorado         4\n",
       "188      189. Josh Harder     California         9\n",
       "189     190. Kevin Mullin     California        15\n",
       "190  191. Chellie Pingree          Maine         1\n",
       "\n",
       "[191 rows x 3 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_signatures = pd.read_csv(os.path.join('public', '22march2024', 'all_signatures.txt'), delimiter='\\t', header=None)\n",
    "all_signatures = all_signatures.rename(columns={0: 'name', 1: 'state', 2: 'district'})\n",
    "all_signatures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>James P. McGovern</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gabe Vasquez</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Stephen F. Lynch</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Matt Cartwright</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Earl Blumenauer</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>Lloyd Doggett</td>\n",
       "      <td>Texas</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>Ken Buck</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Josh Harder</td>\n",
       "      <td>California</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Kevin Mullin</td>\n",
       "      <td>California</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Chellie Pingree</td>\n",
       "      <td>Maine</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>191 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name          state  district\n",
       "0    James P. McGovern  Massachusetts         2\n",
       "1         Gabe Vasquez     New Mexico         2\n",
       "2     Stephen F. Lynch  Massachusetts         8\n",
       "3      Matt Cartwright   Pennsylvania         8\n",
       "4      Earl Blumenauer         Oregon         3\n",
       "..                 ...            ...       ...\n",
       "186      Lloyd Doggett          Texas        37\n",
       "187           Ken Buck       Colorado         4\n",
       "188        Josh Harder     California         9\n",
       "189       Kevin Mullin     California        15\n",
       "190    Chellie Pingree          Maine         1\n",
       "\n",
       "[191 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# regex to match garbage, such as numbers '1. ...' before the full name\n",
    "garbage = re.compile(r'^\\d+\\.\\s+')\n",
    "all_signatures['name'] = all_signatures['name'].apply(lambda x: garbage.sub('', x))\n",
    "all_signatures"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Parse Dem Holdouts from PNG"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dem_holdouts = pd.read_csv(os.path.join('public', '22march2024', 'dem_holdouts.txt'), delimiter=',', header=None)\n",
    "dem_holdouts = dem_holdouts.rename(columns={0: 'name', 1: 'state'})\n",
    "dem_holdouts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Remove parentheses\n",
    "dem_holdouts.state = dem_holdouts.state.apply(lambda x: re.sub(r'\\(|\\)', '', x).strip())\n",
    "dem_holdouts.state = dem_holdouts.state.apply(lambda x: states[x] if x in states else x)\n",
    "dem_holdouts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fuzzy search: by name, search for the closest match in the all_signatures dataframe\n",
    "from fuzzywuzzy import process\n",
    "dem_holdouts['name'].apply(lambda x: (x, process.extractOne(x, all_signatures['name'])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import flor\n",
    "for i, row in flor.loop(\"us_rep\", dem_holdouts.iterrows()):\n",
    "    flor.log(\"name\", row[0])\n",
    "    flor.log(\"state\", row[1])\n",
    "    flor.log(\"signed\", False)\n",
    "    flor.log(\"party\", \"Democrat\")\n",
    "flor.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Enter Signatures into DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>state</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>James P. McGovern</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Gabe Vasquez</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Stephen F. Lynch</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Matt Cartwright</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Earl Blumenauer</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>Lloyd Doggett</td>\n",
       "      <td>Texas</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>Ken Buck</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Josh Harder</td>\n",
       "      <td>California</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>Kevin Mullin</td>\n",
       "      <td>California</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Chellie Pingree</td>\n",
       "      <td>Maine</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>191 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  name          state  district\n",
       "0    James P. McGovern  Massachusetts         2\n",
       "1         Gabe Vasquez     New Mexico         2\n",
       "2     Stephen F. Lynch  Massachusetts         8\n",
       "3      Matt Cartwright   Pennsylvania         8\n",
       "4      Earl Blumenauer         Oregon         3\n",
       "..                 ...            ...       ...\n",
       "186      Lloyd Doggett          Texas        37\n",
       "187           Ken Buck       Colorado         4\n",
       "188        Josh Harder     California         9\n",
       "189       Kevin Mullin     California        15\n",
       "190    Chellie Pingree          Maine         1\n",
       "\n",
       "[191 rows x 3 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_signatures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           name           state  district\n",
      "0             James P. McGovern   Massachusetts         2\n",
      "1                  Gabe Vasquez      New Mexico         2\n",
      "2              Stephen F. Lynch   Massachusetts         8\n",
      "3               Matt Cartwright    Pennsylvania         8\n",
      "4               Earl Blumenauer          Oregon         3\n",
      "5           Jonathan L. Jackson        Illinois         1\n",
      "6                Steny H. Hoyer        Maryland         5\n",
      "7               Donald G. Davis  North Carolina         1\n",
      "8               Scott H. Peters      California        50\n",
      "9                  Wiley Nickel  North Carolina        13\n",
      "10           Katherine M. Clark   Massachusetts         5\n",
      "11              Morgan McGarvey        Kentucky         3\n",
      "12           Hakeem S. Jeffries        New York         8\n",
      "13             Suzan K. DelBene      Washington         1\n",
      "14             Thomas R. Suozzi        New York         3\n",
      "15                   Susan Wild    Pennsylvania         7\n",
      "16                  Lucy McBath         Georgia         7\n",
      "17                Mike Thompson      California         4\n",
      "18           Bennie G. Thompson     Mississippi         2\n",
      "19              Emanuel Cleaver        Missouri         5\n",
      "20                Diana DeGette        Colorado         1\n",
      "21              Doris O. Matsui      California         7\n",
      "22            Salud O. Carbajal      California        24\n",
      "23                     Ami Bera      California         6\n",
      "24                 Joyce Beatty            Ohio         3\n",
      "25                 Val T. Hoyle          Oregon         4\n",
      "26              Richard E. Neal   Massachusetts         1\n",
      "27        Jennifer L. McClellan        Virginia         4\n",
      "28                  David Scott         Georgia        13\n",
      "29                 Patrick Ryan        New York        18\n",
      "30               Andrea Salinas          Oregon         6\n",
      "31           Marilyn Strickland      Washington        10\n",
      "32             Chrissy Houlahan    Pennsylvania         6\n",
      "33                 Mike Quigley        Illinois         5\n",
      "34                 Jahana Hayes     Connecticut         5\n",
      "35               John B. Larson     Connecticut         1\n",
      "36            Sanford D. Bishop         Georgia         2\n",
      "37                Shri Thanedar        Michigan        13\n",
      "38           William R. Keating   Massachusetts         9\n",
      "39             Jake Auchincloss   Massachusetts         4\n",
      "40               J. Luis Correa      California        46\n",
      "41           Valerie P. Foushee  North Carolina         4\n",
      "42                  Sean Casten        Illinois         6\n",
      "43                Jared Huffman      California         2\n",
      "44              Deborah K. Ross  North Carolina         2\n",
      "45             Lauren Underwood        Illinois        14\n",
      "46           Brittany Pettersen        Colorado         7\n",
      "47                 Jamie Raskin        Maryland         8\n",
      "48            Joseph D. Morelle        New York        25\n",
      "49              Colin Z. Allred           Texas        32\n",
      "50                     Gabe Amo    Rhode Island         1\n",
      "51                 Greg Stanton         Arizona         4\n",
      "52                  Juan Vargas      California        52\n",
      "53              Lizzie Fletcher           Texas         7\n",
      "54                   Paul Tonko        New York        20\n",
      "55                   Grace Meng        New York         6\n",
      "56      Robert C. \"Bobby\" Scott        Virginia         3\n",
      "57                  Sara Jacobs      California        51\n",
      "58               Seth Magaziner    Rhode Island         2\n",
      "59                Tony Cárdenas      California        29\n",
      "60             Suzanne Bonamici          Oregon         1\n",
      "61                Ann M. Kuster   New Hampshire         2\n",
      "62             Mary Gay Scanlon    Pennsylvania         5\n",
      "63               Robin L. Kelly        Illinois         2\n",
      "64              Rosa L. DeLauro     Connecticut         3\n",
      "65               Jill N. Tokuda          Hawaii         2\n",
      "66         Sydney Kamlager-Dove      California        37\n",
      "67          Emilia Strong Sykes            Ohio        13\n",
      "68          Frederica S. Wilson         Florida        24\n",
      "69                  Darren Soto         Florida         9\n",
      "70           Sheila Jackson Lee           Texas        18\n",
      "71                  Zoe Lofgren      California        18\n",
      "72               John Garamendi      California         8\n",
      "73               Troy A. Carter       Louisiana         2\n",
      "74                      Ed Case          Hawaii         1\n",
      "75          Hillary J. Scholten        Michigan         3\n",
      "76             Yvette D. Clarke        New York         9\n",
      "77        Bonnie Watson Coleman      New Jersey        12\n",
      "78             Gregory W. Meeks        New York         5\n",
      "79             James E. Clyburn  South Carolina         6\n",
      "80          Raja Krishnamoorthi        Illinois         8\n",
      "81                Jimmy Panetta      California        19\n",
      "82      Bradley Scott Schneider        Illinois        10\n",
      "83                  Steve Cohen       Tennessee         9\n",
      "84             Shontel M. Brown            Ohio        11\n",
      "85                  Rick Larsen      Washington         2\n",
      "86              Donald S. Beyer        Virginia         8\n",
      "87                     Ted Lieu      California        36\n",
      "88       Christopher R. Deluzio    Pennsylvania        17\n",
      "89             Daniel T. Kildee        Michigan         8\n",
      "90         Lisa Blunt Rochester        Delaware         0\n",
      "91               James A. Himes     Connecticut         4\n",
      "92             Jasmine Crockett           Texas        30\n",
      "93               Adam B. Schiff      California        30\n",
      "94                    Raul Ruiz      California        25\n",
      "95          Grace F. Napolitano      California        31\n",
      "96                 Dwight Evans    Pennsylvania         3\n",
      "97                 Jeff Jackson  North Carolina        14\n",
      "98        Nanette Diaz Barragán      California        44\n",
      "99                   Glenn Ivey        Maryland         4\n",
      "100              Yadira Caraveo        Colorado         8\n",
      "101  Sheila Cherfilus-McCormick         Florida        20\n",
      "102               Bill Pascrell      New Jersey         9\n",
      "103                Seth Moulton   Massachusetts         6\n",
      "104               Greg Landsman            Ohio         1\n",
      "105                Joe Courtney     Connecticut         2\n",
      "106     Henry C. \"Hank\" Johnson         Georgia         4\n",
      "107            Linda T. Sánchez      California        38\n",
      "108             Jennifer Wexton        Virginia        10\n",
      "109                  Mike Levin      California        49\n",
      "110    Abigail Davis Spanberger        Virginia         7\n",
      "111                Kathy Castor         Florida        14\n",
      "112        Janice D. Schakowsky        Illinois         9\n",
      "113               Anna G. Eshoo      California        16\n",
      "114               Alma S. Adams  North Carolina        12\n",
      "115             Josh Gottheimer      New Jersey         5\n",
      "116                Derek Kilmer      Washington         6\n",
      "117              Betty McCollum       Minnesota         4\n",
      "118                 Lori Trahan   Massachusetts         3\n",
      "119                  Adam Smith      Washington         9\n",
      "120               Ruben Gallego         Arizona         3\n",
      "121            Haley M. Stevens        Michigan        11\n",
      "122             Jared Moskowitz         Florida        23\n",
      "123                Brad Sherman      California        32\n",
      "124            Brendan F. Boyle    Pennsylvania         2\n",
      "125                Chris Pappas   New Hampshire         1\n",
      "126             Mark DeSaulnier      California        10\n",
      "127                  Gwen Moore       Wisconsin         4\n",
      "128     Marie Gluesenkamp Perez      Washington         3\n",
      "129            John P. Sarbanes        Maryland         3\n",
      "130                Marcy Kaptur            Ohio         9\n",
      "131             Nikki Budzinski        Illinois        13\n",
      "132                  Jason Crow        Colorado         6\n",
      "133      Teresa Leger Fernandez      New Mexico         3\n",
      "134              Frank J. Mrvan         Indiana         1\n",
      "135           Daniel S. Goldman        New York        10\n",
      "136           Adriano Espaillat        New York        13\n",
      "137                Becca Balint         Vermont         0\n",
      "138                    Al Green           Texas         9\n",
      "139             Norma J. Torres      California        35\n",
      "140            Kathy E. Manning  North Carolina         6\n",
      "141                Pete Aguilar      California        33\n",
      "142               Eric Swalwell      California        14\n",
      "143                 Mark Takano      California        39\n",
      "144                 Bill Foster        Illinois        11\n",
      "145                   Susie Lee          Nevada         3\n",
      "146              Julia Brownley      California        26\n",
      "147    Debbie Wasserman Schultz         Florida        25\n",
      "148               Frank Pallone      New Jersey         6\n",
      "149   C. A. Dutch Ruppersberger        Maryland         2\n",
      "150                   Jim Costa      California        21\n",
      "151                Kweisi Mfume        Maryland         7\n",
      "152             Robert Menendez      New Jersey         8\n",
      "153             Terri A. Sewell         Alabama         7\n",
      "154              Elissa Slotkin        Michigan         7\n",
      "155                    Judy Chu      California        28\n",
      "156              Marc A. Veasey           Texas        33\n",
      "157                Nancy Pelosi      California        11\n",
      "158               Maxine Waters      California        43\n",
      "159                  Dina Titus          Nevada         1\n",
      "160               Eric Sorensen        Illinois        17\n",
      "161            Sylvia R. Garcia           Texas        29\n",
      "162               Henry Cuellar           Texas        28\n",
      "163                  Joe Neguse        Colorado         2\n",
      "164              David J. Trone        Maryland         6\n",
      "165              Mikie Sherrill      New Jersey        11\n",
      "166              Jerrold Nadler        New York        12\n",
      "167                 Angie Craig       Minnesota         2\n",
      "168             Nikema Williams         Georgia         5\n",
      "169              Ritchie Torres        New York        15\n",
      "170            Vicente Gonzalez           Texas        34\n",
      "171              Madeleine Dean    Pennsylvania         4\n",
      "172                 Kim Schrier      Washington         8\n",
      "173        Melanie A. Stansbury      New Mexico         1\n",
      "174             Steven Horsford          Nevada         4\n",
      "175               Robert Garcia      California        42\n",
      "176             Donald Norcross      New Jersey         1\n",
      "177            Veronica Escobar           Texas        16\n",
      "178              Sharice Davids          Kansas         3\n",
      "179        Mary Sattler Peltola          Alaska         0\n",
      "180               Dean Phillips       Minnesota         3\n",
      "181                Katie Porter      California        47\n",
      "182                    Andy Kim      New Jersey         3\n",
      "183          Gerald E. Connolly        Virginia        11\n",
      "184             Donald M. Payne      New Jersey        10\n",
      "185              Danny K. Davis        Illinois         7\n",
      "186               Lloyd Doggett           Texas        37\n",
      "187                    Ken Buck        Colorado         4\n",
      "188                 Josh Harder      California         9\n",
      "189                Kevin Mullin      California        15\n",
      "190             Chellie Pingree           Maine         1\n"
     ]
    }
   ],
   "source": [
    "print(all_signatures.to_string())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "pattern = r'\\b(?:Democrat(?:ic)?|Republic(?:an)?|DNC|RNC|Democracy|Republican|GOP)\\sParty\\b|\\bDemocrat(?:ic)?\\b|\\bRepublic(?:an)?\\b'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "James P. McGovern ['Democratic Party', 'Democratic', 'Republican']\n",
      "Gabe Vasquez ['Democratic Party']\n",
      "Stephen F. Lynch ['Democrat', 'Democratic']\n",
      "Matt Cartwright ['Democratic Party', 'Democratic', 'Republican']\n",
      "Earl Blumenauer ['Democratic Party']\n",
      "Jonathan L. Jackson not found\n",
      "Steny H. Hoyer ['Democrat', 'Democrat', 'Republican', 'Democrat']\n",
      "Donald G. Davis ['Democratic Party']\n",
      "Scott H. Peters ['Democratic Party']\n",
      "Wiley Nickel ['Democratic']\n",
      "Katherine M. Clark ['Democratic']\n",
      "Morgan McGarvey ['Democrat', 'Democrat']\n",
      "Hakeem S. Jeffries ['Democratic', 'Democratic']\n",
      "Suzan K. DelBene ['Democratic', 'Republican', 'Republican', 'Democratic', 'Democrat']\n",
      "Thomas R. Suozzi ['Democratic Party', 'Republican', 'Democratic', 'Democratic', 'Democratic']\n",
      "Susan Wild ['Democrat', 'Democrat']\n",
      "Lucy McBath ['Democratic Party', 'Democratic', 'Republican', 'Democratic']\n",
      "Mike Thompson []\n",
      "Bennie G. Thompson ['Democratic Party', 'Democrat', 'Democrat']\n",
      "Emanuel Cleaver ['Democratic Party']\n",
      "Diana DeGette ['Democratic Party']\n",
      "Doris O. Matsui []\n",
      "Salud O. Carbajal ['Democratic Party']\n",
      "Ami Bera ['Democratic Party']\n",
      "Joyce Beatty ['Democratic Party', 'Democratic', 'Republican']\n",
      "Val T. Hoyle not found\n",
      "Richard E. Neal ['Democratic Party']\n",
      "Jennifer L. McClellan ['Democratic Party', 'Democratic', 'Democratic', 'Republican']\n",
      "David Scott []\n",
      "Patrick Ryan []\n",
      "Andrea Salinas ['Democratic Party']\n",
      "Marilyn Strickland ['Democratic Party']\n",
      "Chrissy Houlahan ['Democratic Party', 'Republican']\n",
      "Mike Quigley ['Democratic Party']\n",
      "Jahana Hayes ['Democratic Party', 'Democrat']\n",
      "John B. Larson ['Democratic Party', 'Democratic']\n",
      "Sanford D. Bishop ['Democratic Party']\n",
      "Shri Thanedar ['Democratic Party', 'Democratic']\n",
      "William R. Keating ['Democratic Party']\n",
      "Jake Auchincloss ['Democratic Party']\n",
      "J. Luis Correa ['Democratic Party']\n",
      "Valerie P. Foushee ['Democratic Party']\n",
      "Sean Casten ['Democratic Party', 'Democrat', 'Democratic', 'Republican']\n",
      "Jared Huffman ['Democratic Party', 'Republican']\n",
      "Deborah K. Ross ['Democratic Party', 'Democratic', 'Republican']\n",
      "Lauren Underwood ['Democratic Party', 'Republican', 'Democratic', 'Democratic']\n",
      "Brittany Pettersen ['Democratic Party']\n",
      "Jamie Raskin ['Democratic Party']\n",
      "Joseph D. Morelle ['Democrat']\n",
      "Colin Z. Allred not found\n",
      "Gabe Amo ['Democratic']\n",
      "Greg Stanton ['Democrat']\n",
      "Juan Vargas ['Democratic']\n",
      "Lizzie Fletcher ['Democrat']\n",
      "Paul Tonko ['Democratic Party', 'Democrat']\n",
      "Grace Meng ['Democratic Party']\n",
      "Robert C. \"Bobby\" Scott not found\n",
      "Sara Jacobs ['Democratic Party', 'Democratic']\n",
      "Seth Magaziner ['Democratic Party']\n",
      "Tony Cárdenas ['Democratic Party']\n",
      "Suzanne Bonamici ['Democrat']\n",
      "Ann M. Kuster ['Democratic Party', 'Democrat']\n",
      "Mary Gay Scanlon ['Democratic Party']\n",
      "Robin L. Kelly ['Democrat', 'Democratic', 'Democratic']\n",
      "Rosa L. DeLauro ['Democratic Party', 'Democratic']\n",
      "Jill N. Tokuda ['Democratic Party']\n",
      "Sydney Kamlager-Dove ['Democrat']\n",
      "Emilia Strong Sykes ['Democratic Party']\n",
      "Frederica S. Wilson ['Democratic Party']\n",
      "Darren Soto ['Democratic']\n",
      "Sheila Jackson Lee ['Democratic Party', 'Democratic', 'Democratic']\n",
      "Zoe Lofgren ['Democratic Party']\n",
      "John Garamendi ['Democratic Party', 'Democratic', 'Democratic']\n",
      "Troy A. Carter ['Democratic Party', 'Democrat']\n",
      "Ed Case ['Democratic', 'Democrat', 'Democratic', 'Democratic', 'Democratic', 'Republican', 'Democratic', 'Democrat', 'Democratic']\n",
      "Hillary J. Scholten not found\n",
      "Yvette D. Clarke ['Democratic Party']\n",
      "Bonnie Watson Coleman ['Democratic Party']\n",
      "Gregory W. Meeks ['Democratic Party']\n",
      "James E. Clyburn ['Democratic', 'Democratic', 'Democrat', 'Democrat', 'Democratic', 'democratic', 'Republican', 'Democratic', 'Democratic', 'Democratic', 'Democratic', 'Democratic']\n",
      "Raja Krishnamoorthi ['Democratic Party']\n",
      "Jimmy Panetta ['Democratic Party']\n",
      "Bradley Scott Schneider ['Democratic Party', 'Republican']\n",
      "Steve Cohen []\n",
      "Shontel M. Brown ['Democratic Party']\n",
      "Rick Larsen ['Democratic Party']\n",
      "Donald S. Beyer ['Democratic Party', 'Republican', 'Democratic', 'Republican']\n",
      "Ted Lieu ['Democratic', 'Democratic']\n",
      "Christopher R. Deluzio not found\n",
      "Daniel T. Kildee ['Democratic Party']\n",
      "Lisa Blunt Rochester ['Democratic Party']\n",
      "James A. Himes ['Democratic Party', 'Democrat']\n",
      "Jasmine Crockett ['Democratic Party', 'Democratic', 'Democratic', 'Democratic']\n",
      "Adam B. Schiff ['Democratic Party', 'Republican']\n",
      "Raul Ruiz []\n",
      "Grace F. Napolitano ['Democratic Party', 'Republican', 'Republican']\n",
      "Dwight Evans []\n",
      "Jeff Jackson []\n",
      "Nanette Diaz Barragán ['Democratic Party']\n",
      "Glenn Ivey ['Democratic', 'Republican', 'Democratic']\n",
      "Yadira Caraveo ['Democrat']\n",
      "Sheila Cherfilus-McCormick ['Democratic Party']\n",
      "Bill Pascrell ['Democratic Party']\n",
      "Seth Moulton ['Democratic Party', 'Democratic']\n",
      "Greg Landsman ['Democratic Party']\n",
      "Joe Courtney []\n",
      "Henry C. \"Hank\" Johnson not found\n",
      "Linda T. Sánchez ['Democratic Party', 'Democratic', 'Democratic']\n",
      "Jennifer Wexton ['Democrat', 'Republican', 'Democrat']\n",
      "Mike Levin ['Democratic Party']\n",
      "Abigail Davis Spanberger ['Democratic Party', 'Republican', 'Republican Party']\n",
      "Kathy Castor ['Democrat']\n",
      "Janice D. Schakowsky ['Democratic Party']\n",
      "Anna G. Eshoo ['Democratic Party']\n",
      "Alma S. Adams ['Democrat']\n",
      "Josh Gottheimer ['Democratic Party']\n",
      "Derek Kilmer ['Democratic Party']\n",
      "Betty McCollum ['Democratic']\n",
      "Lori Trahan ['Democrat']\n",
      "Adam Smith []\n",
      "Ruben Gallego ['Democrat', 'Democrat']\n",
      "Haley M. Stevens ['Democratic Party', 'Republican']\n",
      "Jared Moskowitz ['Democratic Party']\n",
      "Brad Sherman ['Democratic Party']\n",
      "Brendan F. Boyle ['Democratic']\n",
      "Chris Pappas []\n",
      "Mark DeSaulnier ['Democratic Party', 'Republican']\n",
      "Gwen Moore ['Democratic Party']\n",
      "Marie Gluesenkamp Perez ['Democratic Party']\n",
      "John P. Sarbanes ['Democratic Party']\n",
      "Marcy Kaptur ['Democratic Party']\n",
      "Nikki Budzinski ['Democratic Party']\n",
      "Jason Crow ['Democratic Party']\n",
      "Teresa Leger Fernandez []\n",
      "Frank J. Mrvan ['Democratic Party']\n",
      "Daniel S. Goldman ['Democratic Party']\n",
      "Adriano Espaillat ['Democrat', 'Democratic', 'Democratic', 'Democratic']\n",
      "Becca Balint ['Democratic Party']\n",
      "Al Green []\n",
      "Norma J. Torres ['Democratic Party']\n",
      "Kathy E. Manning ['Democratic Party']\n",
      "Pete Aguilar ['Democratic', 'Democratic']\n",
      "Eric Swalwell ['Democratic Party', 'Democratic', 'Democratic Party']\n",
      "Mark Takano ['Democratic Party']\n",
      "Bill Foster []\n",
      "Susie Lee ['Democrat']\n",
      "Julia Brownley ['Democrat']\n",
      "Debbie Wasserman Schultz ['Democratic Party', 'Democratic', 'Democratic', 'Democratic']\n",
      "Frank Pallone ['Democratic Party']\n",
      "C. A. Dutch Ruppersberger ['Democratic Party']\n",
      "Jim Costa ['Democratic Party']\n",
      "Kweisi Mfume ['Democratic Party', 'Democratic']\n",
      "Robert Menendez ['Democratic Party']\n",
      "Terri A. Sewell ['Democratic Party', 'Republican', 'Democrat', 'Democrat']\n",
      "Elissa Slotkin ['Democratic Party']\n",
      "Judy Chu ['Democratic Party', 'Republican', 'Republican']\n",
      "Marc A. Veasey ['Democratic']\n",
      "Nancy Pelosi ['Democratic Party', 'Democratic Party', 'Democratic', 'Republican Party', 'Democratic', 'Democratic', 'Democratic']\n",
      "Maxine Waters ['Democratic Party']\n",
      "Dina Titus ['Democratic Party', 'Democratic']\n",
      "Eric Sorensen []\n",
      "Sylvia R. Garcia ['Democratic Party']\n",
      "Henry Cuellar ['Democratic Party', 'Democrat', 'Democratic']\n",
      "Joe Neguse ['Democratic Party', 'Democratic']\n",
      "David J. Trone ['Democratic', 'Democratic']\n",
      "Mikie Sherrill ['Democratic Party']\n",
      "Jerrold Nadler ['Democratic Party']\n",
      "Angie Craig ['Democratic']\n",
      "Nikema Williams ['Democratic Party']\n",
      "Ritchie Torres ['Democratic Party', 'Democratic']\n",
      "Vicente Gonzalez []\n",
      "Madeleine Dean ['Democratic']\n",
      "Kim Schrier ['Democratic Party']\n",
      "Melanie A. Stansbury ['Democrat']\n",
      "Steven Horsford ['Democratic Party', 'Republican', 'Democrat']\n",
      "Robert Garcia []\n",
      "Donald Norcross ['Democratic Party', 'Democrat']\n",
      "Veronica Escobar ['Democratic Party']\n",
      "Sharice Davids ['Democratic Party', 'Democrat', 'Democrat', 'Republican']\n",
      "Mary Sattler Peltola ['Democratic Party', 'Democratic Party', 'Democrat', 'Democrat']\n",
      "Dean Phillips ['Democratic Party', 'Democratic Party', 'Republican', 'Republican', 'Democrat', 'Democrat', 'Democratic']\n",
      "Katie Porter ['Democratic']\n",
      "Andy Kim []\n",
      "Gerald E. Connolly ['Democratic Party']\n",
      "Donald M. Payne ['Democratic Party']\n",
      "Danny K. Davis ['Democrat', 'Democratic']\n",
      "Lloyd Doggett ['Democratic Party']\n",
      "Ken Buck ['Republican Party', 'Democrat', 'Republican Party']\n",
      "Josh Harder ['Democratic Party', 'Republican']\n",
      "Kevin Mullin ['Democratic Party']\n",
      "Chellie Pingree ['Democratic Party', 'Republican', 'Democratic']\n"
     ]
    }
   ],
   "source": [
    "wiki_wiki = wikipediaapi.Wikipedia(\"QuorumFinder (rogarcia@berkeley.edu; rlnsanz.github.io)\")\n",
    "\n",
    "for i, row in all_signatures.iterrows():\n",
    "    full_name = row[0]\n",
    "    us_state = row[1]\n",
    "    p = wiki_wiki.page(full_name)\n",
    "    if p.exists():\n",
    "        print(row[0], re.findall(pattern, p.summary, re.IGNORECASE))\n",
    "    else:\n",
    "        # We need to search for the page\n",
    "        print(row[0], \"not found\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}