do-not-call-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# FTC Do Not Call Registry Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "by [Nate Rattner](http://naterattner.com/)  \n",
    "This code analyzes datasets published by the Federal Trade Commission, which contain information about phone numbers on the FTC's Do Not Call Registry as well as complaints consumers submit to the FTC about unwanted phone calls. This data was used for multiple charts and a story published on Medium, both of which can be found [here](https://medium.com/@naterattner/the-rise-of-robocalls-in-six-charts-37787ac8402e). The visuals used in that post are also available in a story made with Flourish, which can be seen [here]( https://public.flourish.studio/story/39208/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.plotly as py\n",
    "import plotly.graph_objs as go\n",
    "import plotly.figure_factory as ff"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Per Capita Do Not Call Registrations by Fiscal Year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>FY</th>\n",
       "      <th>Registrations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>217,855,796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>222,841,544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>226,001,288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>229,816,164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>235,302,818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FY Registrations\n",
       "0  2014   217,855,796\n",
       "1  2015   222,841,544\n",
       "2  2016   226,001,288\n",
       "3  2017   229,816,164\n",
       "4  2018   235,302,818"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Load FTC Do Not Call registration data that includes registrations by FY (https://www.ftc.gov/reports/national-do-not-call-registry-data-book-fiscal-year-2018)\n",
    "regs_by_fy = pd.read_csv('do_not_call_registry_data_book_data_files_fy2018_0/2018_DNC_Registrations_OrganizationsAccess_5yr.csv', header=1)\n",
    "\n",
    "#Select the rows and columns that have the relevant data\n",
    "regs_by_fy = regs_by_fy[0:5]\n",
    "regs_by_fy = regs_by_fy[['FY', 'Registrations']]\n",
    "regs_by_fy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>FY</th>\n",
       "      <th>Registrations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>217855796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>222841544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>226001288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>229816164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>235302818</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FY  Registrations\n",
       "0  2014      217855796\n",
       "1  2015      222841544\n",
       "2  2016      226001288\n",
       "3  2017      229816164\n",
       "4  2018      235302818"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Change Registrations column from object to int. Have to remove commas first\n",
    "regs_by_fy['Registrations'] = regs_by_fy['Registrations'].str.replace(',', '')\n",
    "\n",
    "regs_by_fy['Registrations'] = regs_by_fy.Registrations.astype(int)\n",
    "\n",
    "regs_by_fy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>FY</th>\n",
       "      <th>Registrations</th>\n",
       "      <th>annual-pop</th>\n",
       "      <th>registrationspercapita</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>217855796</td>\n",
       "      <td>318386421</td>\n",
       "      <td>68424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>222841544</td>\n",
       "      <td>320742673</td>\n",
       "      <td>69476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>226001288</td>\n",
       "      <td>323071342</td>\n",
       "      <td>69953</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>229816164</td>\n",
       "      <td>325147121</td>\n",
       "      <td>70680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>235302818</td>\n",
       "      <td>327167434</td>\n",
       "      <td>71921</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FY  Registrations  annual-pop  registrationspercapita\n",
       "0  2014      217855796   318386421                   68424\n",
       "1  2015      222841544   320742673                   69476\n",
       "2  2016      226001288   323071342                   69953\n",
       "3  2017      229816164   325147121                   70680\n",
       "4  2018      235302818   327167434                   71921"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create list of 2014-2018 population data from U.S. Census (source: https://www.census.gov/data/datasets/time-series/demo/popest/2010s-state-total.html)\n",
    "pop_data_14_18 = [318386421, 320742673, 323071342, 325147121, 327167434]\n",
    "\n",
    "#Add this list to the registrations by FY dataframe\n",
    "regs_by_fy['annual-pop'] = pop_data_14_18\n",
    "\n",
    "#Calculate registrations per capita\n",
    "regs_by_fy['registrationspercapita'] = (regs_by_fy['Registrations']/regs_by_fy['annual-pop'])*100000\n",
    "\n",
    "#Change Registrations per capita to int\n",
    "regs_by_fy['registrationspercapita'] = regs_by_fy.registrationspercapita.astype(int)\n",
    "\n",
    "regs_by_fy\n",
    "\n",
    "#Export this data\n",
    "#regs_by_fy.to_csv('regs_by_fy_per_capita.csv', index = None, header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Do Not Call Complaints by Fiscal Year and Call Type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>FY</th>\n",
       "      <th>Complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2014</td>\n",
       "      <td>3,241,050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>3,578,695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016</td>\n",
       "      <td>5,340,207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>7,157,337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018</td>\n",
       "      <td>5,780,172</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     FY Complaints\n",
       "0  2014  3,241,050\n",
       "1  2015  3,578,695\n",
       "2  2016  5,340,207\n",
       "3  2017  7,157,337\n",
       "4  2018  5,780,172"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Load FTC Do Not Call registration data that includes complaints by call type and FY (https://www.ftc.gov/reports/national-do-not-call-registry-data-book-fiscal-year-2018)\n",
    "\n",
    "comps = pd.read_csv('do_not_call_registry_data_book_data_files_fy2018_0/2018_DNC_Complaints_by_CallType_5yr.csv', header=1)\n",
    "\n",
    "#Select the rows and columns that have the FY data\n",
    "comps_by_fy = comps.loc[3].to_frame().reset_index()\n",
    "comps_by_fy = comps_by_fy[1:].reset_index()\n",
    "comps_by_fy.columns = ['null','FY','Complaints']\n",
    "comps_by_fy = comps_by_fy[['FY','Complaints']]\n",
    "comps_by_fy\n",
    "\n",
    "#Export this data\n",
    "#comps_by_fy.to_csv('comps_by_fy.csv', index = None, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>CallType</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Live Caller</td>\n",
       "      <td>1,448,363</td>\n",
       "      <td>1,388,455</td>\n",
       "      <td>1,854,695</td>\n",
       "      <td>2,563,055</td>\n",
       "      <td>1,894,327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Robocall</td>\n",
       "      <td>1,734,586</td>\n",
       "      <td>2,125,968</td>\n",
       "      <td>3,401,610</td>\n",
       "      <td>4,501,960</td>\n",
       "      <td>3,790,614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Call Type Not Reported</td>\n",
       "      <td>58,101</td>\n",
       "      <td>64,272</td>\n",
       "      <td>83,902</td>\n",
       "      <td>92,322</td>\n",
       "      <td>95,231</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 CallType       2014       2015       2016       2017  \\\n",
       "0             Live Caller  1,448,363  1,388,455  1,854,695  2,563,055   \n",
       "1                Robocall  1,734,586  2,125,968  3,401,610  4,501,960   \n",
       "2  Call Type Not Reported     58,101     64,272     83,902     92,322   \n",
       "\n",
       "        2018  \n",
       "0  1,894,327  \n",
       "1  3,790,614  \n",
       "2     95,231  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Select the rows and columns that have the call type data\n",
    "comps_by_type = comps[0:3]\n",
    "comps_by_type\n",
    "\n",
    "#Export this data\n",
    "#comps_by_type.to_csv('comps_by_type.csv', index = None, header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Do Not Call Registrations by State, FY 18"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>FY</th>\n",
       "      <th>Registrations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2014</td>\n",
       "      <td>348309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>2014</td>\n",
       "      <td>3202106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2014</td>\n",
       "      <td>1906859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>2014</td>\n",
       "      <td>4378074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>2014</td>\n",
       "      <td>24700367</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State    FY  Registrations\n",
       "0      Alaska  2014         348309\n",
       "1     Alabama  2014        3202106\n",
       "2    Arkansas  2014        1906859\n",
       "3     Arizona  2014        4378074\n",
       "4  California  2014       24700367"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Load FTC National Do Not Call registration data (https://www.ftc.gov/reports/national-do-not-call-registry-data-book-fiscal-year-2018)\n",
    "regs_by_state = pd.read_csv('do_not_call_registry_data_book_data_files_fy2018_0/2018_DNC_State_Registrations_5y.csv', header=1)\n",
    "regs_by_state.head()"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>Registrations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>375719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>3487166</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2087259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>4710641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>26447425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Registrations\n",
       "0      Alaska         375719\n",
       "1     Alabama        3487166\n",
       "2    Arkansas        2087259\n",
       "3     Arizona        4710641\n",
       "4  California       26447425"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Keep only FY 18 data\n",
    "regs_by_state_18 = regs_by_state.loc[regs_by_state['FY'] == 2018].reset_index()[['State','Registrations']]\n",
    "regs_by_state_18.head()"
   ]
  },
  {
   "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-ABBREV</th>\n",
       "      <th>POPESTIMATE2018</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>NaN</td>\n",
       "      <td>327167434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Northeast Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56111079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Midwest Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68308744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>124753948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77993663</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME STATE-ABBREV  POPESTIMATE2018\n",
       "0     United States          NaN        327167434\n",
       "1  Northeast Region          NaN         56111079\n",
       "2    Midwest Region          NaN         68308744\n",
       "3      South Region          NaN        124753948\n",
       "4       West Region          NaN         77993663"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Load population data\n",
    "state_pop = pd.read_csv('state-pop-data.csv')\n",
    "\n",
    "#Keep only the columns needed\n",
    "state_pop_18 = state_pop[['NAME','STATE-ABBREV','POPESTIMATE2018']]\n",
    "state_pop_18.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>State</th>\n",
       "      <th>Registrations</th>\n",
       "      <th>STATE-ABBREV</th>\n",
       "      <th>POPESTIMATE2018</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>375719</td>\n",
       "      <td>AK</td>\n",
       "      <td>737438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>3487166</td>\n",
       "      <td>AL</td>\n",
       "      <td>4887871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>2087259</td>\n",
       "      <td>AR</td>\n",
       "      <td>3013825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>4710641</td>\n",
       "      <td>AZ</td>\n",
       "      <td>7171646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>26447425</td>\n",
       "      <td>CA</td>\n",
       "      <td>39557045</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Registrations STATE-ABBREV  POPESTIMATE2018\n",
       "0      Alaska         375719           AK           737438\n",
       "1     Alabama        3487166           AL          4887871\n",
       "2    Arkansas        2087259           AR          3013825\n",
       "3     Arizona        4710641           AZ          7171646\n",
       "4  California       26447425           CA         39557045"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Join population data to DNC registration data on state\n",
    "regs_by_state_18_joined = regs_by_state_18.join(state_pop_18.set_index('NAME'), on='State')\n",
    "regs_by_state_18_joined.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>State</th>\n",
       "      <th>Registrations</th>\n",
       "      <th>STATE-ABBREV</th>\n",
       "      <th>POPESTIMATE2018</th>\n",
       "      <th>registrations-per-capita-18</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>1245599</td>\n",
       "      <td>NH</td>\n",
       "      <td>1356458</td>\n",
       "      <td>91827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>638132</td>\n",
       "      <td>DC</td>\n",
       "      <td>702455</td>\n",
       "      <td>90843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>3234535</td>\n",
       "      <td>CT</td>\n",
       "      <td>3572665</td>\n",
       "      <td>90535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>5893707</td>\n",
       "      <td>MA</td>\n",
       "      <td>6902149</td>\n",
       "      <td>85389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Maine</td>\n",
       "      <td>1121496</td>\n",
       "      <td>ME</td>\n",
       "      <td>1338404</td>\n",
       "      <td>83793</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  State  Registrations STATE-ABBREV  POPESTIMATE2018  \\\n",
       "0         New Hampshire        1245599           NH          1356458   \n",
       "1  District of Columbia         638132           DC           702455   \n",
       "2           Connecticut        3234535           CT          3572665   \n",
       "3         Massachusetts        5893707           MA          6902149   \n",
       "4                 Maine        1121496           ME          1338404   \n",
       "\n",
       "   registrations-per-capita-18  \n",
       "0                        91827  \n",
       "1                        90843  \n",
       "2                        90535  \n",
       "3                        85389  \n",
       "4                        83793  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Add columns calculating Do Not Call registrations per capita (registrations per 100,000 people)\n",
    "regs_by_state_18_joined['registrations-per-capita-18'] = (regs_by_state_18_joined['Registrations']/regs_by_state_18_joined['POPESTIMATE2018'])*100000\n",
    "regs_per_capita_18 = regs_by_state_18_joined.sort_values('registrations-per-capita-18', ascending=False)\n",
    "\n",
    "#Round the registrations-per-capita column (change from float to integer)\n",
    "regs_per_capita_18[('registrations-per-capita-18')] = regs_per_capita_18[('registrations-per-capita-18')].astype(int)\n",
    "\n",
    "#Reset index\n",
    "regs_per_capita_18 = regs_per_capita_18.reset_index()[['State','Registrations','STATE-ABBREV','POPESTIMATE2018','registrations-per-capita-18']]\n",
    "\n",
    "regs_per_capita_18.head()\n",
    "\n",
    "#Export this data\n",
    "#regs_per_capita_18.to_csv('regs_per_capita_18.csv', index = None, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<iframe id=\"igraph\" scrolling=\"no\" style=\"border:none;\" seamless=\"seamless\" src=\"https://plot.ly/~naterattner/22.embed\" height=\"525px\" width=\"100%\"></iframe>"
      ],
      "text/plain": [
       "<chart_studio.tools.PlotlyDisplay object>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create choropleth map to check variation in state registrations\n",
    "\n",
    "scl = [\n",
    "    [0.0, 'rgb(242,240,247)'],\n",
    "    [0.2, 'rgb(218,218,235)'],\n",
    "    [0.4, 'rgb(188,189,220)'],\n",
    "    [0.6, 'rgb(158,154,200)'],\n",
    "    [0.8, 'rgb(117,107,177)'],\n",
    "    [1.0, 'rgb(84,39,143)']\n",
    "]\n",
    "\n",
    "\n",
    "data = [go.Choropleth(\n",
    "    colorscale = scl,\n",
    "    autocolorscale = False,\n",
    "    locations = regs_per_capita_18['STATE-ABBREV'],\n",
    "    z = regs_per_capita_18['registrations-per-capita-18'].astype(float),\n",
    "    locationmode = 'USA-states',\n",
    "    #text = regs_per_capita_18['State'] + regs_per_capita_18['Registrations'] + '<br>Total Registrations:' ,\n",
    "    marker = go.choropleth.Marker(\n",
    "        line = go.choropleth.marker.Line(\n",
    "            color = 'rgb(255,255,255)',\n",
    "            width = 2\n",
    "        )),\n",
    "    colorbar = go.choropleth.ColorBar(\n",
    "        title = \"Registrations Per Capita\")\n",
    ")]\n",
    "\n",
    "\n",
    "layout = go.Layout(\n",
    "    title = go.layout.Title(\n",
    "        text = 'PLACEHOLDER TITLE'\n",
    "    ),\n",
    "    geo = go.layout.Geo(\n",
    "        scope = 'usa',\n",
    "        projection = go.layout.geo.Projection(type = 'albers usa'),\n",
    "        showlakes = False,\n",
    "        lakecolor = 'rgb(255, 255, 255)'),\n",
    ")\n",
    "\n",
    "\n",
    "fig = go.Figure(data = data, layout = layout)\n",
    "py.iplot(fig, filename = 'd3-cloropleth-map')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Do Not Call Registrations by State, Percent Change FY 14 to FY 18"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>Registrations</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>348309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>3202106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>1906859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>4378074</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>24700367</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Registrations\n",
       "0      Alaska         348309\n",
       "1     Alabama        3202106\n",
       "2    Arkansas        1906859\n",
       "3     Arizona        4378074\n",
       "4  California       24700367"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create dataframe with only FY 14 data\n",
    "regs_by_state_14 = regs_by_state.loc[regs_by_state['FY'] == 2014].reset_index()[['State','Registrations']]\n",
    "regs_by_state_14.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>NAME</th>\n",
       "      <th>STATE-ABBREV</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States</td>\n",
       "      <td>NaN</td>\n",
       "      <td>318386421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Northeast Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>56015864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Midwest Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>67752238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>South Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>119657737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>West Region</td>\n",
       "      <td>NaN</td>\n",
       "      <td>74960582</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               NAME STATE-ABBREV  POPESTIMATE2014\n",
       "0     United States          NaN        318386421\n",
       "1  Northeast Region          NaN         56015864\n",
       "2    Midwest Region          NaN         67752238\n",
       "3      South Region          NaN        119657737\n",
       "4       West Region          NaN         74960582"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Create dataframe with 2014 population data\n",
    "state_pop_14 = state_pop[['NAME','STATE-ABBREV','POPESTIMATE2014']]\n",
    "state_pop_14.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>State</th>\n",
       "      <th>Registrations</th>\n",
       "      <th>STATE-ABBREV</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>348309</td>\n",
       "      <td>AK</td>\n",
       "      <td>736307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>3202106</td>\n",
       "      <td>AL</td>\n",
       "      <td>4842481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>1906859</td>\n",
       "      <td>AR</td>\n",
       "      <td>2967726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>4378074</td>\n",
       "      <td>AZ</td>\n",
       "      <td>6733840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>24700367</td>\n",
       "      <td>CA</td>\n",
       "      <td>38625139</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        State  Registrations STATE-ABBREV  POPESTIMATE2014\n",
       "0      Alaska         348309           AK           736307\n",
       "1     Alabama        3202106           AL          4842481\n",
       "2    Arkansas        1906859           AR          2967726\n",
       "3     Arizona        4378074           AZ          6733840\n",
       "4  California       24700367           CA         38625139"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Join population data to Do Not Call registration data on state\n",
    "regs_by_state_14_joined = regs_by_state_14.join(state_pop_14.set_index('NAME'), on='State')\n",
    "regs_by_state_14_joined.head()"
   ]
  },
  {
   "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>State</th>\n",
       "      <th>Registrations</th>\n",
       "      <th>STATE-ABBREV</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>registrations-per-capita-14</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>575799</td>\n",
       "      <td>DC</td>\n",
       "      <td>662513</td>\n",
       "      <td>86911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>1155347</td>\n",
       "      <td>NH</td>\n",
       "      <td>1333223</td>\n",
       "      <td>86658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>3020552</td>\n",
       "      <td>CT</td>\n",
       "      <td>3594783</td>\n",
       "      <td>84025</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>5531792</td>\n",
       "      <td>MA</td>\n",
       "      <td>6763652</td>\n",
       "      <td>81787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>4261903</td>\n",
       "      <td>CO</td>\n",
       "      <td>5351218</td>\n",
       "      <td>79643</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  State  Registrations STATE-ABBREV  POPESTIMATE2014  \\\n",
       "0  District of Columbia         575799           DC           662513   \n",
       "1         New Hampshire        1155347           NH          1333223   \n",
       "2           Connecticut        3020552           CT          3594783   \n",
       "3         Massachusetts        5531792           MA          6763652   \n",
       "4              Colorado        4261903           CO          5351218   \n",
       "\n",
       "   registrations-per-capita-14  \n",
       "0                        86911  \n",
       "1                        86658  \n",
       "2                        84025  \n",
       "3                        81787  \n",
       "4                        79643  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Add columns calculating Do Not Call registrations per capita (registrations per 100,000 people)\n",
    "regs_by_state_14_joined['registrations-per-capita-14'] = (regs_by_state_14_joined['Registrations']/regs_by_state_14_joined['POPESTIMATE2014'])*100000\n",
    "regs_per_capita_14 = regs_by_state_14_joined.sort_values('registrations-per-capita-14', ascending=False)\n",
    "\n",
    "#Round the per-capita column (change from float to integer)\n",
    "regs_per_capita_14[('registrations-per-capita-14')] = regs_per_capita_14[('registrations-per-capita-14')].astype(int)\n",
    "\n",
    "#Reset index\n",
    "regs_per_capita_14 = regs_per_capita_14.reset_index()[['State','Registrations','STATE-ABBREV','POPESTIMATE2014','registrations-per-capita-14']]\n",
    "\n",
    "regs_per_capita_14.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>State</th>\n",
       "      <th>STATE-ABBREV</th>\n",
       "      <th>registrations-per-capita-14</th>\n",
       "      <th>registrations-per-capita-18</th>\n",
       "      <th>per-capita-change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10784</td>\n",
       "      <td>12616</td>\n",
       "      <td>16.988131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>WV</td>\n",
       "      <td>62962</td>\n",
       "      <td>70955</td>\n",
       "      <td>12.694959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>IN</td>\n",
       "      <td>58066</td>\n",
       "      <td>64193</td>\n",
       "      <td>10.551786</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>MS</td>\n",
       "      <td>51140</td>\n",
       "      <td>56413</td>\n",
       "      <td>10.310911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "      <td>74479</td>\n",
       "      <td>81552</td>\n",
       "      <td>9.496637</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            State STATE-ABBREV  registrations-per-capita-14  \\\n",
       "51    Puerto Rico          NaN                        10784   \n",
       "42  West Virginia           WV                        62962   \n",
       "45        Indiana           IN                        58066   \n",
       "49    Mississippi           MS                        51140   \n",
       "17        Wyoming           WY                        74479   \n",
       "\n",
       "    registrations-per-capita-18  per-capita-change  \n",
       "51                        12616          16.988131  \n",
       "42                        70955          12.694959  \n",
       "45                        64193          10.551786  \n",
       "49                        56413          10.310911  \n",
       "17                        81552           9.496637  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Join the per capita dataframes\n",
    "regs_per_capita_14_18 = regs_per_capita_14.merge(regs_per_capita_18, on=['State', 'STATE-ABBREV'])\n",
    "\n",
    "#Keep only the relevant columns\n",
    "regs_per_capita_14_18 = regs_per_capita_14_18[['State','STATE-ABBREV','registrations-per-capita-14','registrations-per-capita-18']]\n",
    "\n",
    "#Calculate percent change from 2014 to 2018\n",
    "regs_per_capita_14_18[('per-capita-change')] = ((regs_per_capita_14_18['registrations-per-capita-18'] - regs_per_capita_14_18['registrations-per-capita-14'])/regs_per_capita_14_18['registrations-per-capita-14']) * 100\n",
    "regs_per_capita_14_18.sort_values('per-capita-change', ascending=False).head()\n",
    "\n",
    "\n",
    "#Export this data\n",
    "#regs_per_capita_14_18.to_csv('regs_per_capita_14_18.csv', index = None, header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Do Not Call Complaints by Topic, FY 18"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>Topic</th>\n",
       "      <th>Live Caller</th>\n",
       "      <th>Robocall</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Reducing debt</td>\n",
       "      <td>87,632</td>\n",
       "      <td>641,734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Medical &amp; prescriptions</td>\n",
       "      <td>101,020</td>\n",
       "      <td>340,439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Imposters</td>\n",
       "      <td>77,743</td>\n",
       "      <td>315,245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Vacation &amp; timeshares</td>\n",
       "      <td>37,044</td>\n",
       "      <td>120,212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Computer &amp; technical support</td>\n",
       "      <td>53,165</td>\n",
       "      <td>85,876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Warranties &amp; protection plans</td>\n",
       "      <td>45,544</td>\n",
       "      <td>73,900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Energy, solar, &amp; utilities</td>\n",
       "      <td>44,900</td>\n",
       "      <td>43,938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Home improvement &amp; cleaning</td>\n",
       "      <td>37,096</td>\n",
       "      <td>44,991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Home security &amp; alarms</td>\n",
       "      <td>11,589</td>\n",
       "      <td>41,429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Lotteries, prizes &amp; sweepstakes</td>\n",
       "      <td>13,937</td>\n",
       "      <td>36,200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Work from home</td>\n",
       "      <td>6,437</td>\n",
       "      <td>25,948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              Topic Live Caller Robocall\n",
       "0                     Reducing debt      87,632  641,734\n",
       "1           Medical & prescriptions     101,020  340,439\n",
       "2                         Imposters      77,743  315,245\n",
       "3             Vacation & timeshares      37,044  120,212\n",
       "4      Computer & technical support      53,165   85,876\n",
       "5     Warranties & protection plans      45,544   73,900\n",
       "6        Energy, solar, & utilities      44,900   43,938\n",
       "7       Home improvement & cleaning      37,096   44,991\n",
       "8            Home security & alarms      11,589   41,429\n",
       "9   Lotteries, prizes & sweepstakes      13,937   36,200\n",
       "10                   Work from home       6,437   25,948"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comps_by_topic = pd.read_csv('do_not_call_registry_data_book_data_files_fy2018_0/2018_DNC_Complaints_by_Topic_CallType_Month.csv', header=1)\n",
    "\n",
    "#Select only the rows needed\n",
    "comps_by_topic = comps_by_topic[0:11]\n",
    "\n",
    "#Select only the columns needed\n",
    "comps_by_topic = comps_by_topic[['Topic','Live Caller','Robocall']]\n",
    "\n",
    "comps_by_topic\n",
    "\n",
    "#Export this data\n",
    "#comps_by_topic.to_csv('comps_by_topic.csv', index = None, header=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Do Not Call Complaints by Month, FY 18"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Month</th>\n",
       "      <th>Live Caller</th>\n",
       "      <th>Robocall</th>\n",
       "      <th>Call Type Not Reported</th>\n",
       "      <th>Total Complaints</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>October</td>\n",
       "      <td>160,136</td>\n",
       "      <td>267,121</td>\n",
       "      <td>7,125</td>\n",
       "      <td>434,382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>November</td>\n",
       "      <td>149,543</td>\n",
       "      <td>266,448</td>\n",
       "      <td>6,793</td>\n",
       "      <td>422,784</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>December</td>\n",
       "      <td>126,110</td>\n",
       "      <td>229,747</td>\n",
       "      <td>6,002</td>\n",
       "      <td>361,859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>January</td>\n",
       "      <td>154,608</td>\n",
       "      <td>277,461</td>\n",
       "      <td>7,186</td>\n",
       "      <td>439,255</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>February</td>\n",
       "      <td>166,369</td>\n",
       "      <td>307,544</td>\n",
       "      <td>7,620</td>\n",
       "      <td>481,533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>March</td>\n",
       "      <td>173,516</td>\n",
       "      <td>326,772</td>\n",
       "      <td>8,460</td>\n",
       "      <td>508,748</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>April</td>\n",
       "      <td>165,212</td>\n",
       "      <td>329,698</td>\n",
       "      <td>8,573</td>\n",
       "      <td>503,483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>May</td>\n",
       "      <td>172,867</td>\n",
       "      <td>393,343</td>\n",
       "      <td>8,876</td>\n",
       "      <td>575,086</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>June</td>\n",
       "      <td>155,039</td>\n",
       "      <td>354,663</td>\n",
       "      <td>8,340</td>\n",
       "      <td>518,042</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>July</td>\n",
       "      <td>153,503</td>\n",
       "      <td>345,222</td>\n",
       "      <td>8,545</td>\n",
       "      <td>507,270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>August</td>\n",
       "      <td>169,033</td>\n",
       "      <td>358,642</td>\n",
       "      <td>9,464</td>\n",
       "      <td>537,139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>September</td>\n",
       "      <td>148,391</td>\n",
       "      <td>333,953</td>\n",
       "      <td>8,247</td>\n",
       "      <td>490,591</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Month Live Caller Robocall Call Type Not Reported Total Complaints\n",
       "0     October     160,136  267,121                  7,125          434,382\n",
       "1    November     149,543  266,448                  6,793          422,784\n",
       "2    December     126,110  229,747                  6,002          361,859\n",
       "3     January     154,608  277,461                  7,186          439,255\n",
       "4   February      166,369  307,544                  7,620          481,533\n",
       "5       March     173,516  326,772                  8,460          508,748\n",
       "6       April     165,212  329,698                  8,573          503,483\n",
       "7         May     172,867  393,343                  8,876          575,086\n",
       "8       June      155,039  354,663                  8,340          518,042\n",
       "9        July     153,503  345,222                  8,545          507,270\n",
       "10     August     169,033  358,642                  9,464          537,139\n",
       "11  September     148,391  333,953                  8,247          490,591"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comps_by_month = pd.read_csv('do_not_call_registry_data_book_data_files_fy2018_0/2018_DNC_Complaints_by_Topic_CallType_Month.csv', header=22)\n",
    "\n",
    "#Select only the rows needed\n",
    "comps_by_month = comps_by_month[0:12]\n",
    "\n",
    "comps_by_month\n",
    "\n",
    "#Export this data\n",
    "#comps_by_month.to_csv('comps_by_month.csv', index = None, header=True)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}