drug_lobbying.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "if (!(\"Notification\" in window)) {\n",
       "    alert(\"This browser does not support desktop notifications, so the %%notify magic will not work.\");\n",
       "} else if (Notification.permission !== 'granted' && Notification.permission !== 'denied') {\n",
       "    Notification.requestPermission(function (permission) {\n",
       "        if(!('permission' in Notification)) {\n",
       "            Notification.permission = permission;\n",
       "        }\n",
       "    })\n",
       "}\n"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import psycopg2\n",
    "import us\n",
    "\n",
    "%load_ext jupyternotify"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drug lobbying"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will use lobbying data from the [National Institute on Money in Politics](https://www.followthemoney.org/) to determine the extent of pharmaceutical industry state legislative lobbying from 2008 through 2017."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create tables for analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, connect to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "with open(\"config.json\") as f:\n",
    "    conf = json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn_str = \"host={} dbname={} user={} password={}\".format(conf[\"host\"], conf[\"database\"], conf[\"user\"], conf[\"password\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "conn = psycopg2.connect(conn_str)\n",
    "conn.autocommit = True # Allow the notebook to commit transactions (like creating a table) to the connected database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Query the database to create a new table filtering the data to only lobbying conducted since 2008."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "$(document).ready(\n",
       "    function() {\n",
       "        function appendUniqueDiv(){\n",
       "            // append a div with our uuid so we can check that it's already\n",
       "            // been sent and avoid duplicates on page reload\n",
       "            var notifiedDiv = document.createElement(\"div\")\n",
       "            notifiedDiv.id = \"701f8d9f-0cf5-4c27-a31c-fcf1a76288ef\"\n",
       "            element.append(notifiedDiv)\n",
       "        }\n",
       "\n",
       "        // only send notifications if the pageload is complete; this will\n",
       "        // help stop extra notifications when a saved notebook is loaded,\n",
       "        // which during testing gives us state \"interactive\", not \"complete\"\n",
       "        if (document.readyState === 'complete') {\n",
       "            // check for the div that signifies that the notification\n",
       "            // was already sent\n",
       "            if (document.getElementById(\"701f8d9f-0cf5-4c27-a31c-fcf1a76288ef\") === null) {\n",
       "                var notificationPayload = {\"requireInteraction\": false, \"icon\": \"/static/base/images/favicon.ico\", \"body\": \"Cell execution has finished!\"};\n",
       "                if (Notification.permission !== 'denied') {\n",
       "                    if (Notification.permission !== 'granted') { \n",
       "                        Notification.requestPermission(function (permission) {\n",
       "                            if(!('permission' in Notification)) {\n",
       "                                Notification.permission = permission\n",
       "                            }\n",
       "                        })\n",
       "                    }\n",
       "                    if (Notification.permission === 'granted') {\n",
       "                    var notification = new Notification(\"Jupyter Notebook\", notificationPayload)\n",
       "                    appendUniqueDiv()\n",
       "                    notification.onclick = function () {\n",
       "                        window.focus();\n",
       "                        this.close();\n",
       "                        };\n",
       "                    } \n",
       "                }     \n",
       "            }\n",
       "        }\n",
       "    }\n",
       ")\n"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%notify\n",
    "lobbying_08_17 = pd.read_sql(\"\"\"CREATE TABLE IF NOT EXISTS lobbying_08_17 AS\n",
    "SELECT *\n",
    "FROM lobbying\n",
    "WHERE YEAR >= 2008;\n",
    "\n",
    "GRANT ALL ON TABLE lobbying_08_17 TO redash;\n",
    "\n",
    "\n",
    "SELECT *\n",
    "FROM lobbying_08_17;\"\"\", con=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll query the database to create a new table of pharmaceutical lobbying."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "$(document).ready(\n",
       "    function() {\n",
       "        function appendUniqueDiv(){\n",
       "            // append a div with our uuid so we can check that it's already\n",
       "            // been sent and avoid duplicates on page reload\n",
       "            var notifiedDiv = document.createElement(\"div\")\n",
       "            notifiedDiv.id = \"d0b2e2fc-f633-4b39-af9c-dc8d58da19b6\"\n",
       "            element.append(notifiedDiv)\n",
       "        }\n",
       "\n",
       "        // only send notifications if the pageload is complete; this will\n",
       "        // help stop extra notifications when a saved notebook is loaded,\n",
       "        // which during testing gives us state \"interactive\", not \"complete\"\n",
       "        if (document.readyState === 'complete') {\n",
       "            // check for the div that signifies that the notification\n",
       "            // was already sent\n",
       "            if (document.getElementById(\"d0b2e2fc-f633-4b39-af9c-dc8d58da19b6\") === null) {\n",
       "                var notificationPayload = {\"requireInteraction\": false, \"icon\": \"/static/base/images/favicon.ico\", \"body\": \"Cell execution has finished!\"};\n",
       "                if (Notification.permission !== 'denied') {\n",
       "                    if (Notification.permission !== 'granted') { \n",
       "                        Notification.requestPermission(function (permission) {\n",
       "                            if(!('permission' in Notification)) {\n",
       "                                Notification.permission = permission\n",
       "                            }\n",
       "                        })\n",
       "                    }\n",
       "                    if (Notification.permission === 'granted') {\n",
       "                    var notification = new Notification(\"Jupyter Notebook\", notificationPayload)\n",
       "                    appendUniqueDiv()\n",
       "                    notification.onclick = function () {\n",
       "                        window.focus();\n",
       "                        this.close();\n",
       "                        };\n",
       "                    } \n",
       "                }     \n",
       "            }\n",
       "        }\n",
       "    }\n",
       ")\n"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%notify\n",
    "registrations = pd.read_sql(\"\"\"CREATE TABLE IF NOT EXISTS pharma_lobbying AS\n",
    "SELECT *\n",
    "FROM lobbying_08_17\n",
    "WHERE year >= 2008\n",
    "  AND catcodebusiness = 'Pharmaceutical manufacturing';\n",
    "\n",
    "GRANT ALL ON TABLE pharma_lobbying TO redash;\n",
    "\n",
    "\n",
    "SELECT *\n",
    "FROM pharma_lobbying;\"\"\", con=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, we want a table of distinct lobbyists representing pharmaceutical interests by each state and year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "$(document).ready(\n",
       "    function() {\n",
       "        function appendUniqueDiv(){\n",
       "            // append a div with our uuid so we can check that it's already\n",
       "            // been sent and avoid duplicates on page reload\n",
       "            var notifiedDiv = document.createElement(\"div\")\n",
       "            notifiedDiv.id = \"caf0e701-c53e-4ad9-a568-114d8ff78f41\"\n",
       "            element.append(notifiedDiv)\n",
       "        }\n",
       "\n",
       "        // only send notifications if the pageload is complete; this will\n",
       "        // help stop extra notifications when a saved notebook is loaded,\n",
       "        // which during testing gives us state \"interactive\", not \"complete\"\n",
       "        if (document.readyState === 'complete') {\n",
       "            // check for the div that signifies that the notification\n",
       "            // was already sent\n",
       "            if (document.getElementById(\"caf0e701-c53e-4ad9-a568-114d8ff78f41\") === null) {\n",
       "                var notificationPayload = {\"requireInteraction\": false, \"icon\": \"/static/base/images/favicon.ico\", \"body\": \"Cell execution has finished!\"};\n",
       "                if (Notification.permission !== 'denied') {\n",
       "                    if (Notification.permission !== 'granted') { \n",
       "                        Notification.requestPermission(function (permission) {\n",
       "                            if(!('permission' in Notification)) {\n",
       "                                Notification.permission = permission\n",
       "                            }\n",
       "                        })\n",
       "                    }\n",
       "                    if (Notification.permission === 'granted') {\n",
       "                    var notification = new Notification(\"Jupyter Notebook\", notificationPayload)\n",
       "                    appendUniqueDiv()\n",
       "                    notification.onclick = function () {\n",
       "                        window.focus();\n",
       "                        this.close();\n",
       "                        };\n",
       "                    } \n",
       "                }     \n",
       "            }\n",
       "        }\n",
       "    }\n",
       ")\n"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%notify\n",
    "lobbyists = pd.read_sql(\"\"\"CREATE TABLE IF NOT EXISTS pharma_lobbyists AS\n",
    "SELECT jurisdiction,\n",
    "       year,\n",
    "       lobbyist\n",
    "FROM pharma_lobbying\n",
    "GROUP BY jurisdiction,\n",
    "         year,\n",
    "         lobbyist;\n",
    "\n",
    "GRANT ALL ON TABLE pharma_lobbyists TO redash;\n",
    "\n",
    "\n",
    "SELECT *\n",
    "FROM pharma_lobbyists;\"\"\", con=conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import and format state legislative data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "One way to measure the influence of pharmaceutical industry lobbying on state legislatures is to compare the number of lobbyists to the number of legislators. To do so, we will use data from the [National Conference of State Legislatures](http://www.ncsl.org/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/jyerardi/anaconda3/lib/python3.6/site-packages/pandas/io/excel.py:329: FutureWarning: The `sheetname` keyword is deprecated, use `sheet_name` instead\n",
      "  **kwds)\n"
     ]
    },
    {
     "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>state</th>\n",
       "      <th>legislators</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska*</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut*</td>\n",
       "      <td>187</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Florida</td>\n",
       "      <td>160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Maine</td>\n",
       "      <td>186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Montana</td>\n",
       "      <td>150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New York</td>\n",
       "      <td>213</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Texas</td>\n",
       "      <td>181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Utah</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Vermont*</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Washington</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>132</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  legislators\n",
       "0          Alabama          140\n",
       "1          Alaska*           60\n",
       "2          Arizona           90\n",
       "3         Arkansas          135\n",
       "4       California          120\n",
       "5         Colorado          100\n",
       "6     Connecticut*          187\n",
       "7         Delaware           62\n",
       "8          Florida          160\n",
       "9          Georgia          236\n",
       "10          Hawaii           76\n",
       "11           Idaho          105\n",
       "12        Illinois          177\n",
       "13         Indiana          150\n",
       "14            Iowa          150\n",
       "15          Kansas          165\n",
       "16        Kentucky          138\n",
       "17       Louisiana          144\n",
       "18           Maine          186\n",
       "19        Maryland          188\n",
       "20   Massachusetts          200\n",
       "21        Michigan          148\n",
       "22       Minnesota          201\n",
       "23    Mississippi           174\n",
       "24        Missouri          197\n",
       "25         Montana          150\n",
       "26        Nebraska           49\n",
       "27          Nevada           63\n",
       "28   New Hampshire          424\n",
       "29    New Jersey            120\n",
       "30      New Mexico          112\n",
       "31        New York          213\n",
       "32  North Carolina          170\n",
       "33  North Dakota            141\n",
       "34            Ohio          132\n",
       "35        Oklahoma          149\n",
       "36          Oregon           90\n",
       "37    Pennsylvania          253\n",
       "38   Rhode Island           113\n",
       "39  South Carolina          170\n",
       "40    South Dakota          105\n",
       "41       Tennessee          132\n",
       "42           Texas          181\n",
       "43            Utah          104\n",
       "44        Vermont*          180\n",
       "45        Virginia          140\n",
       "46      Washington          147\n",
       "47   West Virginia          134\n",
       "48       Wisconsin          132\n",
       "49         Wyoming           90"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "legislators = pd.read_excel(\"data/ncsl_partisan_composition_2000_2018.xlsx\", sheetname=\"2018\", header=1, names=[\"state\", \"legislators\"], usecols=[0, 1], nrows=50)\n",
    "legislators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Clean up the state column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "legislators[\"state\"] = legislators[\"state\"].str.replace(\"*\", \"\").str.strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to add state abbreviations in order to merge this data with the lobbying data later. To do so, we will use the [US package](https://github.com/unitedstates/python-us)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "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>abbreviation</th>\n",
       "      <th>state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AL</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AK</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AS</td>\n",
       "      <td>American Samoa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CO</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CT</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DK</td>\n",
       "      <td>Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DE</td>\n",
       "      <td>Delaware</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>FL</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>GA</td>\n",
       "      <td>Georgia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>GU</td>\n",
       "      <td>Guam</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>HI</td>\n",
       "      <td>Hawaii</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ID</td>\n",
       "      <td>Idaho</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IL</td>\n",
       "      <td>Illinois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IN</td>\n",
       "      <td>Indiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>IA</td>\n",
       "      <td>Iowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>KS</td>\n",
       "      <td>Kansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>KY</td>\n",
       "      <td>Kentucky</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>LA</td>\n",
       "      <td>Louisiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>ME</td>\n",
       "      <td>Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>MD</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>MA</td>\n",
       "      <td>Massachusetts</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MI</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MN</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MS</td>\n",
       "      <td>Mississippi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MO</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>MT</td>\n",
       "      <td>Montana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NE</td>\n",
       "      <td>Nebraska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NV</td>\n",
       "      <td>Nevada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NH</td>\n",
       "      <td>New Hampshire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NJ</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>NM</td>\n",
       "      <td>New Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NY</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>NC</td>\n",
       "      <td>North Carolina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>ND</td>\n",
       "      <td>North Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>MP</td>\n",
       "      <td>Northern Mariana Islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>OH</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>OK</td>\n",
       "      <td>Oklahoma</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>OR</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>OL</td>\n",
       "      <td>Orleans</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>PA</td>\n",
       "      <td>Pennsylvania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>PI</td>\n",
       "      <td>Philippine Islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>PR</td>\n",
       "      <td>Puerto Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>RI</td>\n",
       "      <td>Rhode Island</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>SC</td>\n",
       "      <td>South Carolina</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>SD</td>\n",
       "      <td>South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>TN</td>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>TX</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>UT</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>VT</td>\n",
       "      <td>Vermont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>VI</td>\n",
       "      <td>Virgin Islands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>VA</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>WA</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>WV</td>\n",
       "      <td>West Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>WI</td>\n",
       "      <td>Wisconsin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>WY</td>\n",
       "      <td>Wyoming</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   abbreviation                     state\n",
       "0            AL                   Alabama\n",
       "1            AK                    Alaska\n",
       "2            AS            American Samoa\n",
       "3            AZ                   Arizona\n",
       "4            AR                  Arkansas\n",
       "5            CA                California\n",
       "6            CO                  Colorado\n",
       "7            CT               Connecticut\n",
       "8            DK                    Dakota\n",
       "9            DE                  Delaware\n",
       "10           DC      District of Columbia\n",
       "11           FL                   Florida\n",
       "12           GA                   Georgia\n",
       "13           GU                      Guam\n",
       "14           HI                    Hawaii\n",
       "15           ID                     Idaho\n",
       "16           IL                  Illinois\n",
       "17           IN                   Indiana\n",
       "18           IA                      Iowa\n",
       "19           KS                    Kansas\n",
       "20           KY                  Kentucky\n",
       "21           LA                 Louisiana\n",
       "22           ME                     Maine\n",
       "23           MD                  Maryland\n",
       "24           MA             Massachusetts\n",
       "25           MI                  Michigan\n",
       "26           MN                 Minnesota\n",
       "27           MS               Mississippi\n",
       "28           MO                  Missouri\n",
       "29           MT                   Montana\n",
       "30           NE                  Nebraska\n",
       "31           NV                    Nevada\n",
       "32           NH             New Hampshire\n",
       "33           NJ                New Jersey\n",
       "34           NM                New Mexico\n",
       "35           NY                  New York\n",
       "36           NC            North Carolina\n",
       "37           ND              North Dakota\n",
       "38           MP  Northern Mariana Islands\n",
       "39           OH                      Ohio\n",
       "40           OK                  Oklahoma\n",
       "41           OR                    Oregon\n",
       "42           OL                   Orleans\n",
       "43           PA              Pennsylvania\n",
       "44           PI        Philippine Islands\n",
       "45           PR               Puerto Rico\n",
       "46           RI              Rhode Island\n",
       "47           SC            South Carolina\n",
       "48           SD              South Dakota\n",
       "49           TN                 Tennessee\n",
       "50           TX                     Texas\n",
       "51           UT                      Utah\n",
       "52           VT                   Vermont\n",
       "53           VI            Virgin Islands\n",
       "54           VA                  Virginia\n",
       "55           WA                Washington\n",
       "56           WV             West Virginia\n",
       "57           WI                 Wisconsin\n",
       "58           WY                   Wyoming"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states = pd.DataFrame.from_dict(us.states.mapping(\"abbr\", \"name\"), orient=\"index\", columns=[\"state\"])\n",
    "states.reset_index(inplace=True)\n",
    "states.rename(columns={\"index\": \"abbreviation\"}, inplace=True)\n",
    "states"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "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>state</th>\n",
       "      <th>legislators</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>140</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>90</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>135</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>120</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>100</td>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>187</td>\n",
       "      <td>CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>62</td>\n",
       "      <td>DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Florida</td>\n",
       "      <td>160</td>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>236</td>\n",
       "      <td>GA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>76</td>\n",
       "      <td>HI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>105</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>177</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>150</td>\n",
       "      <td>IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>150</td>\n",
       "      <td>IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>165</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>138</td>\n",
       "      <td>KY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>144</td>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Maine</td>\n",
       "      <td>186</td>\n",
       "      <td>ME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>188</td>\n",
       "      <td>MD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>200</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>148</td>\n",
       "      <td>MI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>201</td>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>174</td>\n",
       "      <td>MS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>197</td>\n",
       "      <td>MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Montana</td>\n",
       "      <td>150</td>\n",
       "      <td>MT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>49</td>\n",
       "      <td>NE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>63</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>424</td>\n",
       "      <td>NH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>120</td>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>112</td>\n",
       "      <td>NM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New York</td>\n",
       "      <td>213</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>170</td>\n",
       "      <td>NC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>141</td>\n",
       "      <td>ND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>132</td>\n",
       "      <td>OH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>149</td>\n",
       "      <td>OK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>90</td>\n",
       "      <td>OR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>253</td>\n",
       "      <td>PA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>113</td>\n",
       "      <td>RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>170</td>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>105</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>132</td>\n",
       "      <td>TN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Texas</td>\n",
       "      <td>181</td>\n",
       "      <td>TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Utah</td>\n",
       "      <td>104</td>\n",
       "      <td>UT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>180</td>\n",
       "      <td>VT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>140</td>\n",
       "      <td>VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Washington</td>\n",
       "      <td>147</td>\n",
       "      <td>WA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>134</td>\n",
       "      <td>WV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>132</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>90</td>\n",
       "      <td>WY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             state  legislators abbreviation\n",
       "0          Alabama          140           AL\n",
       "1           Alaska           60           AK\n",
       "2          Arizona           90           AZ\n",
       "3         Arkansas          135           AR\n",
       "4       California          120           CA\n",
       "5         Colorado          100           CO\n",
       "6      Connecticut          187           CT\n",
       "7         Delaware           62           DE\n",
       "8          Florida          160           FL\n",
       "9          Georgia          236           GA\n",
       "10          Hawaii           76           HI\n",
       "11           Idaho          105           ID\n",
       "12        Illinois          177           IL\n",
       "13         Indiana          150           IN\n",
       "14            Iowa          150           IA\n",
       "15          Kansas          165           KS\n",
       "16        Kentucky          138           KY\n",
       "17       Louisiana          144           LA\n",
       "18           Maine          186           ME\n",
       "19        Maryland          188           MD\n",
       "20   Massachusetts          200           MA\n",
       "21        Michigan          148           MI\n",
       "22       Minnesota          201           MN\n",
       "23     Mississippi          174           MS\n",
       "24        Missouri          197           MO\n",
       "25         Montana          150           MT\n",
       "26        Nebraska           49           NE\n",
       "27          Nevada           63           NV\n",
       "28   New Hampshire          424           NH\n",
       "29      New Jersey          120           NJ\n",
       "30      New Mexico          112           NM\n",
       "31        New York          213           NY\n",
       "32  North Carolina          170           NC\n",
       "33    North Dakota          141           ND\n",
       "34            Ohio          132           OH\n",
       "35        Oklahoma          149           OK\n",
       "36          Oregon           90           OR\n",
       "37    Pennsylvania          253           PA\n",
       "38    Rhode Island          113           RI\n",
       "39  South Carolina          170           SC\n",
       "40    South Dakota          105           SD\n",
       "41       Tennessee          132           TN\n",
       "42           Texas          181           TX\n",
       "43            Utah          104           UT\n",
       "44         Vermont          180           VT\n",
       "45        Virginia          140           VA\n",
       "46      Washington          147           WA\n",
       "47   West Virginia          134           WV\n",
       "48       Wisconsin          132           WI\n",
       "49         Wyoming           90           WY"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "legislators = legislators.merge(states, on=\"state\", how=\"inner\")\n",
    "legislators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyze the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many lobbyists were representing pharmaceutical interests in each year and state and how does this compare to the number of legislators in each year and state?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>year</th>\n",
       "      <th>lobbyists</th>\n",
       "      <th>state</th>\n",
       "      <th>legislators</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>17</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011</td>\n",
       "      <td>12</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  lobbyists   state  legislators abbreviation\n",
       "0  2008         17  Alaska           60           AK\n",
       "1  2009         12  Alaska           60           AK\n",
       "2  2010         10  Alaska           60           AK\n",
       "3  2011         12  Alaska           60           AK\n",
       "4  2012          6  Alaska           60           AK"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lobbying_by_year_state = lobbyists.groupby([\"year\", \"jurisdiction\"]).agg({\"lobbyist\": len})\n",
    "lobbying_by_year_state.reset_index(inplace=True)\n",
    "lobbying_by_year_state.rename(columns={\"jurisdiction\": \"state\", \"lobbyist\": \"lobbyists\"}, inplace=True)\n",
    "# And now join this with the legislators dataframe.\n",
    "lobbying_by_year_state = lobbying_by_year_state.merge(legislators, left_on=\"state\", right_on=\"abbreviation\", how=\"outer\")\n",
    "lobbying_by_year_state.drop(\"state_x\", axis=1, inplace=True)\n",
    "lobbying_by_year_state.rename(columns={\"state_y\": \"state\"}, inplace=True)\n",
    "lobbying_by_year_state.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to subtract one legislator from New York's total in years prior to 2013 when the state added a senator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>year</th>\n",
       "      <th>lobbyists</th>\n",
       "      <th>state</th>\n",
       "      <th>legislators</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>lobbyists_to_legislators</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>17</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "      <td>0.283333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009</td>\n",
       "      <td>12</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>10</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "      <td>0.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011</td>\n",
       "      <td>12</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "      <td>0.200000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012</td>\n",
       "      <td>6</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>60</td>\n",
       "      <td>AK</td>\n",
       "      <td>0.100000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year  lobbyists   state  legislators abbreviation  lobbyists_to_legislators\n",
       "0  2008         17  Alaska           60           AK                  0.283333\n",
       "1  2009         12  Alaska           60           AK                  0.200000\n",
       "2  2010         10  Alaska           60           AK                  0.166667\n",
       "3  2011         12  Alaska           60           AK                  0.200000\n",
       "4  2012          6  Alaska           60           AK                  0.100000"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lobbying_by_year_state.loc[(lobbying_by_year_state[\"abbreviation\"] == \"NY\") & (lobbying_by_year_state[\"year\"] <= 2012), \"legislators\"] = 212\n",
    "# Calculate the lobbyist-to-legislator ratio.\n",
    "lobbying_by_year_state[\"lobbyists_to_legislators\"] = lobbying_by_year_state[\"lobbyists\"] / lobbying_by_year_state[\"legislators\"]\n",
    "lobbying_by_year_state.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>lobbyists</th>\n",
       "      <th>legislators</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>2114</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>1940</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>2047</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>2302</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>1978</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>2413</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>2385</td>\n",
       "      <td>7320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>2642</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>2441</td>\n",
       "      <td>7320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>2287</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      lobbyists  legislators\n",
       "year                        \n",
       "2008       2114         7382\n",
       "2009       1940         7382\n",
       "2010       2047         7382\n",
       "2011       2302         7382\n",
       "2012       1978         7382\n",
       "2013       2413         7383\n",
       "2014       2385         7320\n",
       "2015       2642         7383\n",
       "2016       2441         7320\n",
       "2017       2287         7383"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lobbying_by_year_state.groupby(\"year\").agg({\"lobbyists\": sum, \"legislators\": sum})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We know the number of legislators should stay constant with the exception of New York's count going up by one after 2012. So why are the total number of legislators in 2014 and 2016 lower than other years?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "abbreviation\n",
       "AK    10\n",
       "AL    10\n",
       "AR    10\n",
       "AZ    10\n",
       "CA    10\n",
       "CO    10\n",
       "CT    10\n",
       "DE    10\n",
       "FL    10\n",
       "GA    10\n",
       "HI    10\n",
       "IA    10\n",
       "ID    10\n",
       "IL    10\n",
       "IN    10\n",
       "KS    10\n",
       "KY    10\n",
       "LA    10\n",
       "MA    10\n",
       "MD    10\n",
       "ME    10\n",
       "MI    10\n",
       "MN    10\n",
       "MO    10\n",
       "MS    10\n",
       "MT    10\n",
       "NC    10\n",
       "ND    10\n",
       "NE    10\n",
       "NH    10\n",
       "NJ    10\n",
       "NM    10\n",
       "NV     8\n",
       "NY    10\n",
       "OH    10\n",
       "OK    10\n",
       "OR    10\n",
       "PA    10\n",
       "RI    10\n",
       "SC    10\n",
       "SD    10\n",
       "TN    10\n",
       "TX    10\n",
       "UT    10\n",
       "VA    10\n",
       "VT    10\n",
       "WA    10\n",
       "WI    10\n",
       "WV    10\n",
       "WY    10\n",
       "Name: abbreviation, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lobbying_by_year_state.groupby([\"abbreviation\"])[\"abbreviation\"].size()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It appears that Nevada had no pharmaceutical industry lobbyists in those years. Let's create a dataframe with data for them and concatenate it to the existing dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>lobbyists</th>\n",
       "      <th>legislators</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>2114</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>1940</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>2047</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>2302</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>1978</td>\n",
       "      <td>7382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>2413</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>2385</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>2642</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>2441</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>2287</td>\n",
       "      <td>7383</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      lobbyists  legislators\n",
       "year                        \n",
       "2008       2114         7382\n",
       "2009       1940         7382\n",
       "2010       2047         7382\n",
       "2011       2302         7382\n",
       "2012       1978         7382\n",
       "2013       2413         7383\n",
       "2014       2385         7383\n",
       "2015       2642         7383\n",
       "2016       2441         7383\n",
       "2017       2287         7383"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "missing_nv = pd.DataFrame({\"year\": [2014, 2016],\n",
    "                           \"lobbyists\": [0, 0],\n",
    "                           \"state\": [\"Nevada\", \"Nevada\"],\n",
    "                           \"legislators\": [63, 63],\n",
    "                           \"abbreviation\": [\"NV\", \"NV\"],\n",
    "                           \"lobbyists_to_legislators\": [np.NaN, np.NaN]},\n",
    "                          index=[0, 1])\n",
    "frames = [lobbying_by_year_state, missing_nv]\n",
    "lobbying_by_year_state = pd.concat(frames)\n",
    "lobbying_by_year_state.groupby(\"year\").agg({\"lobbyists\": sum, \"legislators\": sum})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Perfect. Finally, export the data to Excel for visualization and further analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "lobbying_by_year_state.to_excel(\"data/drug_lobbying.xlsx\", index=False, sheet_name=\"Drug Lobbying\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}