05 Capstone State Pop estimates 2016 and Comments percentages.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This Notebook will <li> import the 2016 population estimates <li> import the state table to join the abbreviations to state name <li> calculate the percentage of the US population in each state and DC <li> read in the stored data of grouped submissions by state <li> merge the datasets and calculate the difference from the percentage of submissions by that state"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To do the EDA of the source of comments we import the latest estimated census data, from 2016 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "## import packages\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate % of US Population in each state & DC from 2016 Census Estimates."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in the 2016 population estimates\n",
    "pop_df = pd.read_csv('sub-est2016_all.csv', encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUMLEV</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>PLACE</th>\n",
       "      <th>COUSUB</th>\n",
       "      <th>CONCIT</th>\n",
       "      <th>PRIMGEO_FLAG</th>\n",
       "      <th>FUNCSTAT</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>CENSUS2010POP</th>\n",
       "      <th>ESTIMATESBASE2010</th>\n",
       "      <th>POPESTIMATE2010</th>\n",
       "      <th>POPESTIMATE2011</th>\n",
       "      <th>POPESTIMATE2012</th>\n",
       "      <th>POPESTIMATE2013</th>\n",
       "      <th>POPESTIMATE2014</th>\n",
       "      <th>POPESTIMATE2015</th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4779736</td>\n",
       "      <td>4780131</td>\n",
       "      <td>4785492</td>\n",
       "      <td>4799918</td>\n",
       "      <td>4815960</td>\n",
       "      <td>4829479</td>\n",
       "      <td>4843214</td>\n",
       "      <td>4853875</td>\n",
       "      <td>4863300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>162</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>124</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>Abbeville city</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>2688</td>\n",
       "      <td>2688</td>\n",
       "      <td>2683</td>\n",
       "      <td>2685</td>\n",
       "      <td>2647</td>\n",
       "      <td>2631</td>\n",
       "      <td>2619</td>\n",
       "      <td>2616</td>\n",
       "      <td>2603</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>162</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>460</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>Adamsville city</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>4522</td>\n",
       "      <td>4522</td>\n",
       "      <td>4517</td>\n",
       "      <td>4495</td>\n",
       "      <td>4472</td>\n",
       "      <td>4447</td>\n",
       "      <td>4428</td>\n",
       "      <td>4395</td>\n",
       "      <td>4360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>162</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>484</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>Addison town</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>758</td>\n",
       "      <td>756</td>\n",
       "      <td>754</td>\n",
       "      <td>753</td>\n",
       "      <td>748</td>\n",
       "      <td>748</td>\n",
       "      <td>747</td>\n",
       "      <td>740</td>\n",
       "      <td>738</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>162</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>676</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>Akron town</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>356</td>\n",
       "      <td>356</td>\n",
       "      <td>355</td>\n",
       "      <td>345</td>\n",
       "      <td>345</td>\n",
       "      <td>342</td>\n",
       "      <td>337</td>\n",
       "      <td>337</td>\n",
       "      <td>334</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUMLEV  STATE  COUNTY  PLACE  COUSUB  CONCIT  PRIMGEO_FLAG FUNCSTAT  \\\n",
       "0      40      1       0      0       0       0             0        A   \n",
       "1     162      1       0    124       0       0             0        A   \n",
       "2     162      1       0    460       0       0             0        A   \n",
       "3     162      1       0    484       0       0             0        A   \n",
       "4     162      1       0    676       0       0             0        A   \n",
       "\n",
       "              NAME   STNAME CENSUS2010POP  ESTIMATESBASE2010  POPESTIMATE2010  \\\n",
       "0          Alabama  Alabama       4779736            4780131          4785492   \n",
       "1   Abbeville city  Alabama          2688               2688             2683   \n",
       "2  Adamsville city  Alabama          4522               4522             4517   \n",
       "3     Addison town  Alabama           758                756              754   \n",
       "4       Akron town  Alabama           356                356              355   \n",
       "\n",
       "   POPESTIMATE2011  POPESTIMATE2012  POPESTIMATE2013  POPESTIMATE2014  \\\n",
       "0          4799918          4815960          4829479          4843214   \n",
       "1             2685             2647             2631             2619   \n",
       "2             4495             4472             4447             4428   \n",
       "3              753              748              748              747   \n",
       "4              345              345              342              337   \n",
       "\n",
       "   POPESTIMATE2015  POPESTIMATE2016  \n",
       "0          4853875          4863300  \n",
       "1             2616             2603  \n",
       "2             4395             4360  \n",
       "3              740              738  \n",
       "4              337              334  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#explore\n",
    "pop_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select only the columns we need\n",
    "pop_df = pop_df[['POPESTIMATE2016',\"NAME\",\"STNAME\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#trim down rows so that we have excluded county population estimates\n",
    "pop_df = pop_df[pop_df.STNAME==pop_df.NAME]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STNAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4863300</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>741894</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1452</th>\n",
       "      <td>6931071</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1673</th>\n",
       "      <td>2988248</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Arkansas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2848</th>\n",
       "      <td>39250017</td>\n",
       "      <td>California</td>\n",
       "      <td>California</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3925</th>\n",
       "      <td>5540545</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4616</th>\n",
       "      <td>3576452</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>Connecticut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4901</th>\n",
       "      <td>952065</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>Delaware</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5025</th>\n",
       "      <td>681170</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>District of Columbia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5027</th>\n",
       "      <td>681170</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>District of Columbia</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      POPESTIMATE2016                  NAME                STNAME\n",
       "0             4863300               Alabama               Alabama\n",
       "1105           741894                Alaska                Alaska\n",
       "1452          6931071               Arizona               Arizona\n",
       "1673          2988248              Arkansas              Arkansas\n",
       "2848         39250017            California            California\n",
       "3925          5540545              Colorado              Colorado\n",
       "4616          3576452           Connecticut           Connecticut\n",
       "4901           952065              Delaware              Delaware\n",
       "5025           681170  District of Columbia  District of Columbia\n",
       "5027           681170  District of Columbia  District of Columbia"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#explore trimed rows - ! notice DC is both a county and state and is duplicated\n",
    "pop_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicate rows of DC\n",
    "pop = pop_df.drop_duplicates(subset=['NAME','STNAME'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>perc_pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4863300</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>741894</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1452</th>\n",
       "      <td>6931071</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>2.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1673</th>\n",
       "      <td>2988248</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2848</th>\n",
       "      <td>39250017</td>\n",
       "      <td>California</td>\n",
       "      <td>California</td>\n",
       "      <td>12.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3925</th>\n",
       "      <td>5540545</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4616</th>\n",
       "      <td>3576452</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>1.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4901</th>\n",
       "      <td>952065</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>0.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5025</th>\n",
       "      <td>681170</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      POPESTIMATE2016                  NAME                STNAME  perc_pop\n",
       "0             4863300               Alabama               Alabama      1.51\n",
       "1105           741894                Alaska                Alaska      0.23\n",
       "1452          6931071               Arizona               Arizona      2.14\n",
       "1673          2988248              Arkansas              Arkansas      0.92\n",
       "2848         39250017            California            California     12.15\n",
       "3925          5540545              Colorado              Colorado      1.71\n",
       "4616          3576452           Connecticut           Connecticut      1.11\n",
       "4901           952065              Delaware              Delaware      0.29\n",
       "5025           681170  District of Columbia  District of Columbia      0.21"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#explore\n",
    "pop.head(9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in table of State abbreviations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#read in state abbreviation names\n",
    "states_df = pd.read_csv('state_table.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Florida</td>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>GA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Hawaii</td>\n",
       "      <td>HI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Illinois</td>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Indiana</td>\n",
       "      <td>IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Kentucky</td>\n",
       "      <td>KY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Louisiana</td>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Maine</td>\n",
       "      <td>ME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Maryland</td>\n",
       "      <td>MD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Michigan</td>\n",
       "      <td>MI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>MS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Missouri</td>\n",
       "      <td>MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Montana</td>\n",
       "      <td>MT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>NE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Nevada</td>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>New Hampshire</td>\n",
       "      <td>NH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>New Jersey</td>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>NM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>North Carolina</td>\n",
       "      <td>NC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>ND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>OH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Oklahoma</td>\n",
       "      <td>OK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Oregon</td>\n",
       "      <td>OR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>PA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>South Dakota</td>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Tennessee</td>\n",
       "      <td>TN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Texas</td>\n",
       "      <td>TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>Utah</td>\n",
       "      <td>UT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Vermont</td>\n",
       "      <td>VT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>Washington</td>\n",
       "      <td>WA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>West Virginia</td>\n",
       "      <td>WV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Wisconsin</td>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Wyoming</td>\n",
       "      <td>WY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Washington DC</td>\n",
       "      <td>DC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name abbreviation\n",
       "0          Alabama           AL\n",
       "1           Alaska           AK\n",
       "2          Arizona           AZ\n",
       "3         Arkansas           AR\n",
       "4       California           CA\n",
       "5         Colorado           CO\n",
       "6      Connecticut           CT\n",
       "7         Delaware           DE\n",
       "8          Florida           FL\n",
       "9          Georgia           GA\n",
       "10          Hawaii           HI\n",
       "11           Idaho           ID\n",
       "12        Illinois           IL\n",
       "13         Indiana           IN\n",
       "14            Iowa           IA\n",
       "15          Kansas           KS\n",
       "16        Kentucky           KY\n",
       "17       Louisiana           LA\n",
       "18           Maine           ME\n",
       "19        Maryland           MD\n",
       "20   Massachusetts           MA\n",
       "21        Michigan           MI\n",
       "22       Minnesota           MN\n",
       "23     Mississippi           MS\n",
       "24        Missouri           MO\n",
       "25         Montana           MT\n",
       "26        Nebraska           NE\n",
       "27          Nevada           NV\n",
       "28   New Hampshire           NH\n",
       "29      New Jersey           NJ\n",
       "30      New Mexico           NM\n",
       "31        New York           NY\n",
       "32  North Carolina           NC\n",
       "33    North Dakota           ND\n",
       "34            Ohio           OH\n",
       "35        Oklahoma           OK\n",
       "36          Oregon           OR\n",
       "37    Pennsylvania           PA\n",
       "38    Rhode Island           RI\n",
       "39  South Carolina           SC\n",
       "40    South Dakota           SD\n",
       "41       Tennessee           TN\n",
       "42           Texas           TX\n",
       "43            Utah           UT\n",
       "44         Vermont           VT\n",
       "45        Virginia           VA\n",
       "46      Washington           WA\n",
       "47   West Virginia           WV\n",
       "48       Wisconsin           WI\n",
       "49         Wyoming           WY\n",
       "50   Washington DC           DC"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#explore\n",
    "states_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reduce to state name and abbreviation\n",
    "states_df = states_df[['name','abbreviation']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Do the calculations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "# calculate % of pop in eact state and round to nearest 2 decimal places\n",
    "pop['perc_pop'] = ((pop['POPESTIMATE2016']/pop['POPESTIMATE2016'].sum())*100).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>perc_pop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4863300</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1105</th>\n",
       "      <td>741894</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1452</th>\n",
       "      <td>6931071</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>2.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1673</th>\n",
       "      <td>2988248</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>0.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2848</th>\n",
       "      <td>39250017</td>\n",
       "      <td>California</td>\n",
       "      <td>California</td>\n",
       "      <td>12.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3925</th>\n",
       "      <td>5540545</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4616</th>\n",
       "      <td>3576452</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>1.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4901</th>\n",
       "      <td>952065</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>0.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5025</th>\n",
       "      <td>681170</td>\n",
       "      <td>Washington DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>0.21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      POPESTIMATE2016           NAME                STNAME  perc_pop\n",
       "0             4863300        Alabama               Alabama      1.51\n",
       "1105           741894         Alaska                Alaska      0.23\n",
       "1452          6931071        Arizona               Arizona      2.14\n",
       "1673          2988248       Arkansas              Arkansas      0.92\n",
       "2848         39250017     California            California     12.15\n",
       "3925          5540545       Colorado              Colorado      1.71\n",
       "4616          3576452    Connecticut           Connecticut      1.11\n",
       "4901           952065       Delaware              Delaware      0.29\n",
       "5025           681170  Washington DC  District of Columbia      0.21"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop.head(9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace District of Columbia with Washington DC as NAME, STNAME."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "#replacing the contents of a cell across a column\n",
    "pop['STNAME'] = pop['STNAME'].replace({\"District of Columbia\":\"Washington DC\" })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge 2 dataframes on state name\n",
    "pop_perc = pd.merge(pop, states_df, left_on='NAME',right_on='name', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "      <th>NAME</th>\n",
       "      <th>STNAME</th>\n",
       "      <th>perc_pop</th>\n",
       "      <th>name</th>\n",
       "      <th>abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4863300</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>1.51</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>741894</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>0.23</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6931071</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>2.14</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2988248</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>0.92</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39250017</td>\n",
       "      <td>California</td>\n",
       "      <td>California</td>\n",
       "      <td>12.15</td>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5540545</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.71</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3576452</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>1.11</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>952065</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>0.29</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>681170</td>\n",
       "      <td>Washington DC</td>\n",
       "      <td>Washington DC</td>\n",
       "      <td>0.21</td>\n",
       "      <td>Washington DC</td>\n",
       "      <td>DC</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POPESTIMATE2016           NAME         STNAME  perc_pop           name  \\\n",
       "0          4863300        Alabama        Alabama      1.51        Alabama   \n",
       "1           741894         Alaska         Alaska      0.23         Alaska   \n",
       "2          6931071        Arizona        Arizona      2.14        Arizona   \n",
       "3          2988248       Arkansas       Arkansas      0.92       Arkansas   \n",
       "4         39250017     California     California     12.15     California   \n",
       "5          5540545       Colorado       Colorado      1.71       Colorado   \n",
       "6          3576452    Connecticut    Connecticut      1.11    Connecticut   \n",
       "7           952065       Delaware       Delaware      0.29       Delaware   \n",
       "8           681170  Washington DC  Washington DC      0.21  Washington DC   \n",
       "\n",
       "  abbreviation  \n",
       "0           AL  \n",
       "1           AK  \n",
       "2           AZ  \n",
       "3           AR  \n",
       "4           CA  \n",
       "5           CO  \n",
       "6           CT  \n",
       "7           DE  \n",
       "8           DC  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_perc.head(9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# delete unwanted columns\n",
    "del pop_perc['NAME']\n",
    "del pop_perc['STNAME']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save as CSV file\n",
    "pop_perc.to_csv('FCC_pop_perc.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate percentage of comments from each state and DC"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# >> Stored 'data' (str)\n",
    "%store -r df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date_submission</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>text_data</th>\n",
       "      <th>filers</th>\n",
       "      <th>address_line_1</th>\n",
       "      <th>city</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>state</th>\n",
       "      <th>intl_address</th>\n",
       "      <th>combined_zip</th>\n",
       "      <th>flagged_zip</th>\n",
       "      <th>Place_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59f8b2bb50b733300f069dc9</td>\n",
       "      <td>2017-07-11T15:53:55.361Z</td>\n",
       "      <td>YaroslavKalinina@pornhub.com</td>\n",
       "      <td>Allowing broadband providers to throttle their...</td>\n",
       "      <td>[{\"name\":\"Yaroslav Kalinina\"}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...</td>\n",
       "      <td>55792</td>\n",
       "      <td>1</td>\n",
       "      <td>Virginia</td>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>59f8b2bb50b733300f069dca</td>\n",
       "      <td>2017-07-11T15:53:55.390Z</td>\n",
       "      <td>GalinaMedvedeva@pornhub.com</td>\n",
       "      <td>We need net neutralityto continue. A free and ...</td>\n",
       "      <td>[{\"name\":\"Galina Medvedeva\"}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...</td>\n",
       "      <td>55415</td>\n",
       "      <td>1</td>\n",
       "      <td>Minneapolis</td>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>59f8b2bb50b733300f069dcb</td>\n",
       "      <td>2017-07-11T15:53:55.449Z</td>\n",
       "      <td>PhilemonButusov@pornhub.com</td>\n",
       "      <td>make sure net neutrality does not dissapear. I...</td>\n",
       "      <td>[{\"name\":\"Philemon Butusov\"}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...</td>\n",
       "      <td>55802</td>\n",
       "      <td>1</td>\n",
       "      <td>Duluth</td>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>59f8b2bb50b733300f069dcc</td>\n",
       "      <td>2017-07-11T15:53:55.473Z</td>\n",
       "      <td>BarbaraUlyanova@pornhub.com</td>\n",
       "      <td>We need net neutralityto continue. A free and ...</td>\n",
       "      <td>[{\"name\":\"Barbara Ulyanova\"}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...</td>\n",
       "      <td>55406</td>\n",
       "      <td>1</td>\n",
       "      <td>Minneapolis</td>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>59f8b2bb50b733300f069dcd</td>\n",
       "      <td>2017-07-11T15:53:55.512Z</td>\n",
       "      <td>MatildaZhdanova@pornhub.com</td>\n",
       "      <td>Please save the internet from the corporations...</td>\n",
       "      <td>[{\"name\":\"Matilda Zhdanova\"}]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>{\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...</td>\n",
       "      <td>39401</td>\n",
       "      <td>1</td>\n",
       "      <td>Hattiesburg</td>\n",
       "      <td>MS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         id           date_submission  \\\n",
       "0  59f8b2bb50b733300f069dc9  2017-07-11T15:53:55.361Z   \n",
       "1  59f8b2bb50b733300f069dca  2017-07-11T15:53:55.390Z   \n",
       "2  59f8b2bb50b733300f069dcb  2017-07-11T15:53:55.449Z   \n",
       "3  59f8b2bb50b733300f069dcc  2017-07-11T15:53:55.473Z   \n",
       "4  59f8b2bb50b733300f069dcd  2017-07-11T15:53:55.512Z   \n",
       "\n",
       "                  contact_email  \\\n",
       "0  YaroslavKalinina@pornhub.com   \n",
       "1   GalinaMedvedeva@pornhub.com   \n",
       "2   PhilemonButusov@pornhub.com   \n",
       "3   BarbaraUlyanova@pornhub.com   \n",
       "4   MatildaZhdanova@pornhub.com   \n",
       "\n",
       "                                           text_data  \\\n",
       "0  Allowing broadband providers to throttle their...   \n",
       "1  We need net neutralityto continue. A free and ...   \n",
       "2  make sure net neutrality does not dissapear. I...   \n",
       "3  We need net neutralityto continue. A free and ...   \n",
       "4  Please save the internet from the corporations...   \n",
       "\n",
       "                           filers address_line_1 city  express_comment state  \\\n",
       "0  [{\"name\":\"Yaroslav Kalinina\"}]            NaN  NaN              NaN   NaN   \n",
       "1   [{\"name\":\"Galina Medvedeva\"}]            NaN  NaN              NaN   NaN   \n",
       "2   [{\"name\":\"Philemon Butusov\"}]            NaN  NaN              NaN   NaN   \n",
       "3   [{\"name\":\"Barbara Ulyanova\"}]            NaN  NaN              NaN   NaN   \n",
       "4   [{\"name\":\"Matilda Zhdanova\"}]            NaN  NaN              NaN   NaN   \n",
       "\n",
       "                                        intl_address combined_zip  \\\n",
       "0  {\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...        55792   \n",
       "1  {\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...        55415   \n",
       "2  {\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...        55802   \n",
       "3  {\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...        55406   \n",
       "4  {\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...        39401   \n",
       "\n",
       "   flagged_zip   Place_Name State_Abbreviation  \n",
       "0            1     Virginia                 MN  \n",
       "1            1  Minneapolis                 MN  \n",
       "2            1       Duluth                 MN  \n",
       "3            1  Minneapolis                 MN  \n",
       "4            1  Hattiesburg                 MS  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1 = df.groupby( [ \"State_Abbreviation\"] ).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>id</th>\n",
       "      <th>date_submission</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>text_data</th>\n",
       "      <th>filers</th>\n",
       "      <th>address_line_1</th>\n",
       "      <th>city</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>state</th>\n",
       "      <th>intl_address</th>\n",
       "      <th>combined_zip</th>\n",
       "      <th>flagged_zip</th>\n",
       "      <th>Place_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>4441</td>\n",
       "      <td>4441</td>\n",
       "      <td>2743</td>\n",
       "      <td>4434</td>\n",
       "      <td>4441</td>\n",
       "      <td>4411</td>\n",
       "      <td>4412</td>\n",
       "      <td>40</td>\n",
       "      <td>4413</td>\n",
       "      <td>2948</td>\n",
       "      <td>4441</td>\n",
       "      <td>4441</td>\n",
       "      <td>4441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>29970</td>\n",
       "      <td>29970</td>\n",
       "      <td>22519</td>\n",
       "      <td>29961</td>\n",
       "      <td>29970</td>\n",
       "      <td>29903</td>\n",
       "      <td>29910</td>\n",
       "      <td>109</td>\n",
       "      <td>29910</td>\n",
       "      <td>22473</td>\n",
       "      <td>29970</td>\n",
       "      <td>29970</td>\n",
       "      <td>29970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AP</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AR</td>\n",
       "      <td>15605</td>\n",
       "      <td>15605</td>\n",
       "      <td>12221</td>\n",
       "      <td>15599</td>\n",
       "      <td>15605</td>\n",
       "      <td>15561</td>\n",
       "      <td>15562</td>\n",
       "      <td>64</td>\n",
       "      <td>15562</td>\n",
       "      <td>10785</td>\n",
       "      <td>15605</td>\n",
       "      <td>15605</td>\n",
       "      <td>15605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>36573</td>\n",
       "      <td>36573</td>\n",
       "      <td>23569</td>\n",
       "      <td>36537</td>\n",
       "      <td>36573</td>\n",
       "      <td>36298</td>\n",
       "      <td>36321</td>\n",
       "      <td>576</td>\n",
       "      <td>36321</td>\n",
       "      <td>23448</td>\n",
       "      <td>36573</td>\n",
       "      <td>36573</td>\n",
       "      <td>36573</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CA</td>\n",
       "      <td>216119</td>\n",
       "      <td>216119</td>\n",
       "      <td>129713</td>\n",
       "      <td>215879</td>\n",
       "      <td>216119</td>\n",
       "      <td>213781</td>\n",
       "      <td>214037</td>\n",
       "      <td>2010</td>\n",
       "      <td>214034</td>\n",
       "      <td>137250</td>\n",
       "      <td>216119</td>\n",
       "      <td>216119</td>\n",
       "      <td>216119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CO</td>\n",
       "      <td>37914</td>\n",
       "      <td>37914</td>\n",
       "      <td>23635</td>\n",
       "      <td>37872</td>\n",
       "      <td>37914</td>\n",
       "      <td>37578</td>\n",
       "      <td>37619</td>\n",
       "      <td>349</td>\n",
       "      <td>37619</td>\n",
       "      <td>23848</td>\n",
       "      <td>37914</td>\n",
       "      <td>37914</td>\n",
       "      <td>37914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CT</td>\n",
       "      <td>24834</td>\n",
       "      <td>24834</td>\n",
       "      <td>10725</td>\n",
       "      <td>24816</td>\n",
       "      <td>24834</td>\n",
       "      <td>24649</td>\n",
       "      <td>24679</td>\n",
       "      <td>204</td>\n",
       "      <td>24679</td>\n",
       "      <td>18250</td>\n",
       "      <td>24834</td>\n",
       "      <td>24834</td>\n",
       "      <td>24834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DC</td>\n",
       "      <td>4962</td>\n",
       "      <td>4962</td>\n",
       "      <td>3366</td>\n",
       "      <td>4863</td>\n",
       "      <td>4962</td>\n",
       "      <td>4892</td>\n",
       "      <td>4901</td>\n",
       "      <td>27</td>\n",
       "      <td>4901</td>\n",
       "      <td>2750</td>\n",
       "      <td>4962</td>\n",
       "      <td>4962</td>\n",
       "      <td>4962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>DE</td>\n",
       "      <td>4723</td>\n",
       "      <td>4723</td>\n",
       "      <td>3208</td>\n",
       "      <td>4719</td>\n",
       "      <td>4723</td>\n",
       "      <td>4686</td>\n",
       "      <td>4691</td>\n",
       "      <td>48</td>\n",
       "      <td>4691</td>\n",
       "      <td>3328</td>\n",
       "      <td>4723</td>\n",
       "      <td>4723</td>\n",
       "      <td>4723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>FL</td>\n",
       "      <td>125912</td>\n",
       "      <td>125912</td>\n",
       "      <td>75080</td>\n",
       "      <td>125840</td>\n",
       "      <td>125912</td>\n",
       "      <td>125314</td>\n",
       "      <td>125384</td>\n",
       "      <td>920</td>\n",
       "      <td>125385</td>\n",
       "      <td>87103</td>\n",
       "      <td>125912</td>\n",
       "      <td>125912</td>\n",
       "      <td>125912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>FM</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>GA</td>\n",
       "      <td>66270</td>\n",
       "      <td>66270</td>\n",
       "      <td>39703</td>\n",
       "      <td>66243</td>\n",
       "      <td>66270</td>\n",
       "      <td>66088</td>\n",
       "      <td>66112</td>\n",
       "      <td>417</td>\n",
       "      <td>66107</td>\n",
       "      <td>47424</td>\n",
       "      <td>66270</td>\n",
       "      <td>66270</td>\n",
       "      <td>66270</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>HI</td>\n",
       "      <td>7905</td>\n",
       "      <td>7905</td>\n",
       "      <td>5796</td>\n",
       "      <td>7894</td>\n",
       "      <td>7905</td>\n",
       "      <td>7833</td>\n",
       "      <td>7846</td>\n",
       "      <td>70</td>\n",
       "      <td>7848</td>\n",
       "      <td>5735</td>\n",
       "      <td>7905</td>\n",
       "      <td>7905</td>\n",
       "      <td>7905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>IA</td>\n",
       "      <td>18150</td>\n",
       "      <td>18150</td>\n",
       "      <td>9866</td>\n",
       "      <td>18136</td>\n",
       "      <td>18150</td>\n",
       "      <td>18025</td>\n",
       "      <td>18034</td>\n",
       "      <td>97</td>\n",
       "      <td>18034</td>\n",
       "      <td>12800</td>\n",
       "      <td>18150</td>\n",
       "      <td>18150</td>\n",
       "      <td>18150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ID</td>\n",
       "      <td>9551</td>\n",
       "      <td>9551</td>\n",
       "      <td>6252</td>\n",
       "      <td>9540</td>\n",
       "      <td>9551</td>\n",
       "      <td>9457</td>\n",
       "      <td>9465</td>\n",
       "      <td>54</td>\n",
       "      <td>9466</td>\n",
       "      <td>6223</td>\n",
       "      <td>9551</td>\n",
       "      <td>9551</td>\n",
       "      <td>9551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>IL</td>\n",
       "      <td>75767</td>\n",
       "      <td>75767</td>\n",
       "      <td>39596</td>\n",
       "      <td>75699</td>\n",
       "      <td>75767</td>\n",
       "      <td>75189</td>\n",
       "      <td>75244</td>\n",
       "      <td>592</td>\n",
       "      <td>75246</td>\n",
       "      <td>50682</td>\n",
       "      <td>75767</td>\n",
       "      <td>75767</td>\n",
       "      <td>75767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>IN</td>\n",
       "      <td>40901</td>\n",
       "      <td>40901</td>\n",
       "      <td>24159</td>\n",
       "      <td>40879</td>\n",
       "      <td>40901</td>\n",
       "      <td>40662</td>\n",
       "      <td>40674</td>\n",
       "      <td>194</td>\n",
       "      <td>40675</td>\n",
       "      <td>29064</td>\n",
       "      <td>40901</td>\n",
       "      <td>40901</td>\n",
       "      <td>40901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>KS</td>\n",
       "      <td>15841</td>\n",
       "      <td>15841</td>\n",
       "      <td>11311</td>\n",
       "      <td>15830</td>\n",
       "      <td>15841</td>\n",
       "      <td>15728</td>\n",
       "      <td>15741</td>\n",
       "      <td>98</td>\n",
       "      <td>15738</td>\n",
       "      <td>10404</td>\n",
       "      <td>15841</td>\n",
       "      <td>15841</td>\n",
       "      <td>15841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>KY</td>\n",
       "      <td>25027</td>\n",
       "      <td>25027</td>\n",
       "      <td>16450</td>\n",
       "      <td>25013</td>\n",
       "      <td>25027</td>\n",
       "      <td>24876</td>\n",
       "      <td>24886</td>\n",
       "      <td>119</td>\n",
       "      <td>24888</td>\n",
       "      <td>17423</td>\n",
       "      <td>25027</td>\n",
       "      <td>25027</td>\n",
       "      <td>25027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>LA</td>\n",
       "      <td>25048</td>\n",
       "      <td>25048</td>\n",
       "      <td>19259</td>\n",
       "      <td>25034</td>\n",
       "      <td>25048</td>\n",
       "      <td>24963</td>\n",
       "      <td>24969</td>\n",
       "      <td>94</td>\n",
       "      <td>24972</td>\n",
       "      <td>17649</td>\n",
       "      <td>25048</td>\n",
       "      <td>25048</td>\n",
       "      <td>25048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>MA</td>\n",
       "      <td>50252</td>\n",
       "      <td>50252</td>\n",
       "      <td>21536</td>\n",
       "      <td>50198</td>\n",
       "      <td>50252</td>\n",
       "      <td>49867</td>\n",
       "      <td>49898</td>\n",
       "      <td>439</td>\n",
       "      <td>49901</td>\n",
       "      <td>35959</td>\n",
       "      <td>50252</td>\n",
       "      <td>50252</td>\n",
       "      <td>50252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>MD</td>\n",
       "      <td>38133</td>\n",
       "      <td>38133</td>\n",
       "      <td>17237</td>\n",
       "      <td>38100</td>\n",
       "      <td>38133</td>\n",
       "      <td>37489</td>\n",
       "      <td>37505</td>\n",
       "      <td>273</td>\n",
       "      <td>37504</td>\n",
       "      <td>28462</td>\n",
       "      <td>38133</td>\n",
       "      <td>38133</td>\n",
       "      <td>38133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>ME</td>\n",
       "      <td>7834</td>\n",
       "      <td>7834</td>\n",
       "      <td>4965</td>\n",
       "      <td>7825</td>\n",
       "      <td>7834</td>\n",
       "      <td>7752</td>\n",
       "      <td>7756</td>\n",
       "      <td>68</td>\n",
       "      <td>7757</td>\n",
       "      <td>5366</td>\n",
       "      <td>7834</td>\n",
       "      <td>7834</td>\n",
       "      <td>7834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>MI</td>\n",
       "      <td>64121</td>\n",
       "      <td>64121</td>\n",
       "      <td>33906</td>\n",
       "      <td>64073</td>\n",
       "      <td>64121</td>\n",
       "      <td>63087</td>\n",
       "      <td>63114</td>\n",
       "      <td>374</td>\n",
       "      <td>63117</td>\n",
       "      <td>44380</td>\n",
       "      <td>64121</td>\n",
       "      <td>64121</td>\n",
       "      <td>64121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MN</td>\n",
       "      <td>34478</td>\n",
       "      <td>34478</td>\n",
       "      <td>18492</td>\n",
       "      <td>34443</td>\n",
       "      <td>34478</td>\n",
       "      <td>33778</td>\n",
       "      <td>33798</td>\n",
       "      <td>212</td>\n",
       "      <td>33799</td>\n",
       "      <td>23776</td>\n",
       "      <td>34478</td>\n",
       "      <td>34478</td>\n",
       "      <td>34478</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MO</td>\n",
       "      <td>32909</td>\n",
       "      <td>32909</td>\n",
       "      <td>23757</td>\n",
       "      <td>32889</td>\n",
       "      <td>32909</td>\n",
       "      <td>32725</td>\n",
       "      <td>32738</td>\n",
       "      <td>209</td>\n",
       "      <td>32739</td>\n",
       "      <td>20824</td>\n",
       "      <td>32909</td>\n",
       "      <td>32909</td>\n",
       "      <td>32909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MP</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>22</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>0</td>\n",
       "      <td>27</td>\n",
       "      <td>22</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>MS</td>\n",
       "      <td>20764</td>\n",
       "      <td>20764</td>\n",
       "      <td>16050</td>\n",
       "      <td>20759</td>\n",
       "      <td>20764</td>\n",
       "      <td>18830</td>\n",
       "      <td>18832</td>\n",
       "      <td>46</td>\n",
       "      <td>18833</td>\n",
       "      <td>16625</td>\n",
       "      <td>20764</td>\n",
       "      <td>20764</td>\n",
       "      <td>20764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>MT</td>\n",
       "      <td>7671</td>\n",
       "      <td>7671</td>\n",
       "      <td>4336</td>\n",
       "      <td>7662</td>\n",
       "      <td>7671</td>\n",
       "      <td>7621</td>\n",
       "      <td>7629</td>\n",
       "      <td>44</td>\n",
       "      <td>7629</td>\n",
       "      <td>5354</td>\n",
       "      <td>7671</td>\n",
       "      <td>7671</td>\n",
       "      <td>7671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NC</td>\n",
       "      <td>59854</td>\n",
       "      <td>59854</td>\n",
       "      <td>35565</td>\n",
       "      <td>59818</td>\n",
       "      <td>59854</td>\n",
       "      <td>59611</td>\n",
       "      <td>59637</td>\n",
       "      <td>371</td>\n",
       "      <td>59640</td>\n",
       "      <td>42534</td>\n",
       "      <td>59854</td>\n",
       "      <td>59854</td>\n",
       "      <td>59854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>ND</td>\n",
       "      <td>4340</td>\n",
       "      <td>4340</td>\n",
       "      <td>2370</td>\n",
       "      <td>4338</td>\n",
       "      <td>4340</td>\n",
       "      <td>4324</td>\n",
       "      <td>4325</td>\n",
       "      <td>12</td>\n",
       "      <td>4325</td>\n",
       "      <td>3122</td>\n",
       "      <td>4340</td>\n",
       "      <td>4340</td>\n",
       "      <td>4340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NE</td>\n",
       "      <td>10326</td>\n",
       "      <td>10326</td>\n",
       "      <td>6801</td>\n",
       "      <td>10322</td>\n",
       "      <td>10326</td>\n",
       "      <td>10282</td>\n",
       "      <td>10289</td>\n",
       "      <td>44</td>\n",
       "      <td>10290</td>\n",
       "      <td>6759</td>\n",
       "      <td>10326</td>\n",
       "      <td>10326</td>\n",
       "      <td>10326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NH</td>\n",
       "      <td>9500</td>\n",
       "      <td>9500</td>\n",
       "      <td>4798</td>\n",
       "      <td>9488</td>\n",
       "      <td>9500</td>\n",
       "      <td>9430</td>\n",
       "      <td>9436</td>\n",
       "      <td>89</td>\n",
       "      <td>9437</td>\n",
       "      <td>6782</td>\n",
       "      <td>9500</td>\n",
       "      <td>9500</td>\n",
       "      <td>9500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>NJ</td>\n",
       "      <td>62151</td>\n",
       "      <td>62151</td>\n",
       "      <td>25906</td>\n",
       "      <td>62107</td>\n",
       "      <td>62151</td>\n",
       "      <td>61825</td>\n",
       "      <td>61854</td>\n",
       "      <td>472</td>\n",
       "      <td>61857</td>\n",
       "      <td>46454</td>\n",
       "      <td>62151</td>\n",
       "      <td>62151</td>\n",
       "      <td>62151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NM</td>\n",
       "      <td>11697</td>\n",
       "      <td>11697</td>\n",
       "      <td>8362</td>\n",
       "      <td>11687</td>\n",
       "      <td>11697</td>\n",
       "      <td>11566</td>\n",
       "      <td>11582</td>\n",
       "      <td>129</td>\n",
       "      <td>11582</td>\n",
       "      <td>7844</td>\n",
       "      <td>11697</td>\n",
       "      <td>11697</td>\n",
       "      <td>11697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>NV</td>\n",
       "      <td>15830</td>\n",
       "      <td>15830</td>\n",
       "      <td>10711</td>\n",
       "      <td>15817</td>\n",
       "      <td>15830</td>\n",
       "      <td>15728</td>\n",
       "      <td>15738</td>\n",
       "      <td>112</td>\n",
       "      <td>15739</td>\n",
       "      <td>9655</td>\n",
       "      <td>15830</td>\n",
       "      <td>15830</td>\n",
       "      <td>15830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>NY</td>\n",
       "      <td>117763</td>\n",
       "      <td>117763</td>\n",
       "      <td>57095</td>\n",
       "      <td>117649</td>\n",
       "      <td>117763</td>\n",
       "      <td>116767</td>\n",
       "      <td>116875</td>\n",
       "      <td>942</td>\n",
       "      <td>116881</td>\n",
       "      <td>83205</td>\n",
       "      <td>117763</td>\n",
       "      <td>117763</td>\n",
       "      <td>117763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>OH</td>\n",
       "      <td>70473</td>\n",
       "      <td>70473</td>\n",
       "      <td>40260</td>\n",
       "      <td>70432</td>\n",
       "      <td>70473</td>\n",
       "      <td>70195</td>\n",
       "      <td>70229</td>\n",
       "      <td>411</td>\n",
       "      <td>70233</td>\n",
       "      <td>49689</td>\n",
       "      <td>70473</td>\n",
       "      <td>70473</td>\n",
       "      <td>70473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>OK</td>\n",
       "      <td>20296</td>\n",
       "      <td>20296</td>\n",
       "      <td>15567</td>\n",
       "      <td>20285</td>\n",
       "      <td>20296</td>\n",
       "      <td>20230</td>\n",
       "      <td>20241</td>\n",
       "      <td>83</td>\n",
       "      <td>20241</td>\n",
       "      <td>13836</td>\n",
       "      <td>20296</td>\n",
       "      <td>20296</td>\n",
       "      <td>20296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>OR</td>\n",
       "      <td>42222</td>\n",
       "      <td>42222</td>\n",
       "      <td>29045</td>\n",
       "      <td>42159</td>\n",
       "      <td>42222</td>\n",
       "      <td>35811</td>\n",
       "      <td>35849</td>\n",
       "      <td>1551</td>\n",
       "      <td>35850</td>\n",
       "      <td>29656</td>\n",
       "      <td>42222</td>\n",
       "      <td>42222</td>\n",
       "      <td>42222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>PA</td>\n",
       "      <td>79664</td>\n",
       "      <td>79664</td>\n",
       "      <td>43362</td>\n",
       "      <td>79611</td>\n",
       "      <td>79664</td>\n",
       "      <td>79155</td>\n",
       "      <td>79197</td>\n",
       "      <td>524</td>\n",
       "      <td>79202</td>\n",
       "      <td>56751</td>\n",
       "      <td>79664</td>\n",
       "      <td>79664</td>\n",
       "      <td>79664</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>PW</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>RI</td>\n",
       "      <td>7160</td>\n",
       "      <td>7160</td>\n",
       "      <td>3121</td>\n",
       "      <td>7158</td>\n",
       "      <td>7160</td>\n",
       "      <td>7115</td>\n",
       "      <td>7116</td>\n",
       "      <td>51</td>\n",
       "      <td>7117</td>\n",
       "      <td>5325</td>\n",
       "      <td>7160</td>\n",
       "      <td>7160</td>\n",
       "      <td>7160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>SC</td>\n",
       "      <td>30038</td>\n",
       "      <td>30038</td>\n",
       "      <td>18824</td>\n",
       "      <td>30022</td>\n",
       "      <td>30038</td>\n",
       "      <td>29958</td>\n",
       "      <td>29964</td>\n",
       "      <td>138</td>\n",
       "      <td>29965</td>\n",
       "      <td>22351</td>\n",
       "      <td>30038</td>\n",
       "      <td>30038</td>\n",
       "      <td>30038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>SD</td>\n",
       "      <td>4113</td>\n",
       "      <td>4113</td>\n",
       "      <td>2512</td>\n",
       "      <td>4109</td>\n",
       "      <td>4113</td>\n",
       "      <td>4101</td>\n",
       "      <td>4102</td>\n",
       "      <td>22</td>\n",
       "      <td>4102</td>\n",
       "      <td>2716</td>\n",
       "      <td>4113</td>\n",
       "      <td>4113</td>\n",
       "      <td>4113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>TN</td>\n",
       "      <td>41243</td>\n",
       "      <td>41243</td>\n",
       "      <td>26198</td>\n",
       "      <td>41229</td>\n",
       "      <td>41243</td>\n",
       "      <td>41079</td>\n",
       "      <td>41100</td>\n",
       "      <td>195</td>\n",
       "      <td>41101</td>\n",
       "      <td>29217</td>\n",
       "      <td>41243</td>\n",
       "      <td>41243</td>\n",
       "      <td>41243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>TX</td>\n",
       "      <td>135882</td>\n",
       "      <td>135882</td>\n",
       "      <td>93550</td>\n",
       "      <td>135769</td>\n",
       "      <td>135882</td>\n",
       "      <td>135406</td>\n",
       "      <td>135471</td>\n",
       "      <td>790</td>\n",
       "      <td>135484</td>\n",
       "      <td>85572</td>\n",
       "      <td>135882</td>\n",
       "      <td>135882</td>\n",
       "      <td>135882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>UT</td>\n",
       "      <td>18077</td>\n",
       "      <td>18077</td>\n",
       "      <td>11071</td>\n",
       "      <td>18058</td>\n",
       "      <td>18077</td>\n",
       "      <td>17993</td>\n",
       "      <td>18009</td>\n",
       "      <td>133</td>\n",
       "      <td>18011</td>\n",
       "      <td>10850</td>\n",
       "      <td>18077</td>\n",
       "      <td>18077</td>\n",
       "      <td>18077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>VA</td>\n",
       "      <td>64805</td>\n",
       "      <td>64805</td>\n",
       "      <td>40173</td>\n",
       "      <td>64736</td>\n",
       "      <td>64805</td>\n",
       "      <td>64553</td>\n",
       "      <td>64582</td>\n",
       "      <td>366</td>\n",
       "      <td>64585</td>\n",
       "      <td>36286</td>\n",
       "      <td>64805</td>\n",
       "      <td>64805</td>\n",
       "      <td>64805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>VT</td>\n",
       "      <td>4118</td>\n",
       "      <td>4118</td>\n",
       "      <td>2707</td>\n",
       "      <td>4116</td>\n",
       "      <td>4118</td>\n",
       "      <td>4054</td>\n",
       "      <td>4065</td>\n",
       "      <td>43</td>\n",
       "      <td>4065</td>\n",
       "      <td>2729</td>\n",
       "      <td>4118</td>\n",
       "      <td>4118</td>\n",
       "      <td>4118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>WA</td>\n",
       "      <td>54910</td>\n",
       "      <td>54910</td>\n",
       "      <td>29399</td>\n",
       "      <td>54809</td>\n",
       "      <td>54910</td>\n",
       "      <td>53655</td>\n",
       "      <td>53716</td>\n",
       "      <td>467</td>\n",
       "      <td>53714</td>\n",
       "      <td>36881</td>\n",
       "      <td>54910</td>\n",
       "      <td>54910</td>\n",
       "      <td>54910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>WI</td>\n",
       "      <td>35367</td>\n",
       "      <td>35367</td>\n",
       "      <td>18131</td>\n",
       "      <td>35334</td>\n",
       "      <td>35367</td>\n",
       "      <td>35153</td>\n",
       "      <td>35172</td>\n",
       "      <td>225</td>\n",
       "      <td>35173</td>\n",
       "      <td>25835</td>\n",
       "      <td>35367</td>\n",
       "      <td>35367</td>\n",
       "      <td>35367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>WV</td>\n",
       "      <td>9188</td>\n",
       "      <td>9188</td>\n",
       "      <td>6423</td>\n",
       "      <td>9183</td>\n",
       "      <td>9188</td>\n",
       "      <td>9152</td>\n",
       "      <td>9154</td>\n",
       "      <td>32</td>\n",
       "      <td>9154</td>\n",
       "      <td>6566</td>\n",
       "      <td>9188</td>\n",
       "      <td>9188</td>\n",
       "      <td>9188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>WY</td>\n",
       "      <td>3077</td>\n",
       "      <td>3077</td>\n",
       "      <td>2291</td>\n",
       "      <td>3076</td>\n",
       "      <td>3077</td>\n",
       "      <td>3068</td>\n",
       "      <td>3069</td>\n",
       "      <td>14</td>\n",
       "      <td>3069</td>\n",
       "      <td>2064</td>\n",
       "      <td>3077</td>\n",
       "      <td>3077</td>\n",
       "      <td>3077</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   State_Abbreviation      id  date_submission  contact_email  text_data  \\\n",
       "0                  AK    4441             4441           2743       4434   \n",
       "1                  AL   29970            29970          22519      29961   \n",
       "2                  AP       3                3              2          3   \n",
       "3                  AR   15605            15605          12221      15599   \n",
       "4                  AZ   36573            36573          23569      36537   \n",
       "5                  CA  216119           216119         129713     215879   \n",
       "6                  CO   37914            37914          23635      37872   \n",
       "7                  CT   24834            24834          10725      24816   \n",
       "8                  DC    4962             4962           3366       4863   \n",
       "9                  DE    4723             4723           3208       4719   \n",
       "10                 FL  125912           125912          75080     125840   \n",
       "11                 FM       3                3              2          3   \n",
       "12                 GA   66270            66270          39703      66243   \n",
       "13                 HI    7905             7905           5796       7894   \n",
       "14                 IA   18150            18150           9866      18136   \n",
       "15                 ID    9551             9551           6252       9540   \n",
       "16                 IL   75767            75767          39596      75699   \n",
       "17                 IN   40901            40901          24159      40879   \n",
       "18                 KS   15841            15841          11311      15830   \n",
       "19                 KY   25027            25027          16450      25013   \n",
       "20                 LA   25048            25048          19259      25034   \n",
       "21                 MA   50252            50252          21536      50198   \n",
       "22                 MD   38133            38133          17237      38100   \n",
       "23                 ME    7834             7834           4965       7825   \n",
       "24                 MI   64121            64121          33906      64073   \n",
       "25                 MN   34478            34478          18492      34443   \n",
       "26                 MO   32909            32909          23757      32889   \n",
       "27                 MP      27               27             22         27   \n",
       "28                 MS   20764            20764          16050      20759   \n",
       "29                 MT    7671             7671           4336       7662   \n",
       "30                 NC   59854            59854          35565      59818   \n",
       "31                 ND    4340             4340           2370       4338   \n",
       "32                 NE   10326            10326           6801      10322   \n",
       "33                 NH    9500             9500           4798       9488   \n",
       "34                 NJ   62151            62151          25906      62107   \n",
       "35                 NM   11697            11697           8362      11687   \n",
       "36                 NV   15830            15830          10711      15817   \n",
       "37                 NY  117763           117763          57095     117649   \n",
       "38                 OH   70473            70473          40260      70432   \n",
       "39                 OK   20296            20296          15567      20285   \n",
       "40                 OR   42222            42222          29045      42159   \n",
       "41                 PA   79664            79664          43362      79611   \n",
       "42                 PW       1                1              1          1   \n",
       "43                 RI    7160             7160           3121       7158   \n",
       "44                 SC   30038            30038          18824      30022   \n",
       "45                 SD    4113             4113           2512       4109   \n",
       "46                 TN   41243            41243          26198      41229   \n",
       "47                 TX  135882           135882          93550     135769   \n",
       "48                 UT   18077            18077          11071      18058   \n",
       "49                 VA   64805            64805          40173      64736   \n",
       "50                 VT    4118             4118           2707       4116   \n",
       "51                 WA   54910            54910          29399      54809   \n",
       "52                 WI   35367            35367          18131      35334   \n",
       "53                 WV    9188             9188           6423       9183   \n",
       "54                 WY    3077             3077           2291       3076   \n",
       "\n",
       "    filers  address_line_1    city  express_comment   state  intl_address  \\\n",
       "0     4441            4411    4412               40    4413          2948   \n",
       "1    29970           29903   29910              109   29910         22473   \n",
       "2        3               3       3                0       3             2   \n",
       "3    15605           15561   15562               64   15562         10785   \n",
       "4    36573           36298   36321              576   36321         23448   \n",
       "5   216119          213781  214037             2010  214034        137250   \n",
       "6    37914           37578   37619              349   37619         23848   \n",
       "7    24834           24649   24679              204   24679         18250   \n",
       "8     4962            4892    4901               27    4901          2750   \n",
       "9     4723            4686    4691               48    4691          3328   \n",
       "10  125912          125314  125384              920  125385         87103   \n",
       "11       3               3       3                0       3             2   \n",
       "12   66270           66088   66112              417   66107         47424   \n",
       "13    7905            7833    7846               70    7848          5735   \n",
       "14   18150           18025   18034               97   18034         12800   \n",
       "15    9551            9457    9465               54    9466          6223   \n",
       "16   75767           75189   75244              592   75246         50682   \n",
       "17   40901           40662   40674              194   40675         29064   \n",
       "18   15841           15728   15741               98   15738         10404   \n",
       "19   25027           24876   24886              119   24888         17423   \n",
       "20   25048           24963   24969               94   24972         17649   \n",
       "21   50252           49867   49898              439   49901         35959   \n",
       "22   38133           37489   37505              273   37504         28462   \n",
       "23    7834            7752    7756               68    7757          5366   \n",
       "24   64121           63087   63114              374   63117         44380   \n",
       "25   34478           33778   33798              212   33799         23776   \n",
       "26   32909           32725   32738              209   32739         20824   \n",
       "27      27              27      27                0      27            22   \n",
       "28   20764           18830   18832               46   18833         16625   \n",
       "29    7671            7621    7629               44    7629          5354   \n",
       "30   59854           59611   59637              371   59640         42534   \n",
       "31    4340            4324    4325               12    4325          3122   \n",
       "32   10326           10282   10289               44   10290          6759   \n",
       "33    9500            9430    9436               89    9437          6782   \n",
       "34   62151           61825   61854              472   61857         46454   \n",
       "35   11697           11566   11582              129   11582          7844   \n",
       "36   15830           15728   15738              112   15739          9655   \n",
       "37  117763          116767  116875              942  116881         83205   \n",
       "38   70473           70195   70229              411   70233         49689   \n",
       "39   20296           20230   20241               83   20241         13836   \n",
       "40   42222           35811   35849             1551   35850         29656   \n",
       "41   79664           79155   79197              524   79202         56751   \n",
       "42       1               1       1                0       1             1   \n",
       "43    7160            7115    7116               51    7117          5325   \n",
       "44   30038           29958   29964              138   29965         22351   \n",
       "45    4113            4101    4102               22    4102          2716   \n",
       "46   41243           41079   41100              195   41101         29217   \n",
       "47  135882          135406  135471              790  135484         85572   \n",
       "48   18077           17993   18009              133   18011         10850   \n",
       "49   64805           64553   64582              366   64585         36286   \n",
       "50    4118            4054    4065               43    4065          2729   \n",
       "51   54910           53655   53716              467   53714         36881   \n",
       "52   35367           35153   35172              225   35173         25835   \n",
       "53    9188            9152    9154               32    9154          6566   \n",
       "54    3077            3068    3069               14    3069          2064   \n",
       "\n",
       "    combined_zip  flagged_zip  Place_Name  \n",
       "0           4441         4441        4441  \n",
       "1          29970        29970       29970  \n",
       "2              3            3           3  \n",
       "3          15605        15605       15605  \n",
       "4          36573        36573       36573  \n",
       "5         216119       216119      216119  \n",
       "6          37914        37914       37914  \n",
       "7          24834        24834       24834  \n",
       "8           4962         4962        4962  \n",
       "9           4723         4723        4723  \n",
       "10        125912       125912      125912  \n",
       "11             3            3           3  \n",
       "12         66270        66270       66270  \n",
       "13          7905         7905        7905  \n",
       "14         18150        18150       18150  \n",
       "15          9551         9551        9551  \n",
       "16         75767        75767       75767  \n",
       "17         40901        40901       40901  \n",
       "18         15841        15841       15841  \n",
       "19         25027        25027       25027  \n",
       "20         25048        25048       25048  \n",
       "21         50252        50252       50252  \n",
       "22         38133        38133       38133  \n",
       "23          7834         7834        7834  \n",
       "24         64121        64121       64121  \n",
       "25         34478        34478       34478  \n",
       "26         32909        32909       32909  \n",
       "27            27           27          27  \n",
       "28         20764        20764       20764  \n",
       "29          7671         7671        7671  \n",
       "30         59854        59854       59854  \n",
       "31          4340         4340        4340  \n",
       "32         10326        10326       10326  \n",
       "33          9500         9500        9500  \n",
       "34         62151        62151       62151  \n",
       "35         11697        11697       11697  \n",
       "36         15830        15830       15830  \n",
       "37        117763       117763      117763  \n",
       "38         70473        70473       70473  \n",
       "39         20296        20296       20296  \n",
       "40         42222        42222       42222  \n",
       "41         79664        79664       79664  \n",
       "42             1            1           1  \n",
       "43          7160         7160        7160  \n",
       "44         30038        30038       30038  \n",
       "45          4113         4113        4113  \n",
       "46         41243        41243       41243  \n",
       "47        135882       135882      135882  \n",
       "48         18077        18077       18077  \n",
       "49         64805        64805       64805  \n",
       "50          4118         4118        4118  \n",
       "51         54910        54910       54910  \n",
       "52         35367        35367       35367  \n",
       "53          9188         9188        9188  \n",
       "54          3077         3077        3077  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g1.reset_index() # note territories and "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "g1.reset_index(level=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "g2 = g1[[\"State_Abbreviation\",\"filers\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>filers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>4441</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>29970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AP</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AR</td>\n",
       "      <td>15605</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>36573</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Abbreviation  filers\n",
       "0                 AK    4441\n",
       "1                 AL   29970\n",
       "2                 AP       3\n",
       "3                 AR   15605\n",
       "4                 AZ   36573"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "# calculate percentage proportions\n",
    "g2['perc_comments'] = ((g2['filers']/g2['filers'].sum())*100).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>filers</th>\n",
       "      <th>perc_comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AK</td>\n",
       "      <td>4441</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AL</td>\n",
       "      <td>29970</td>\n",
       "      <td>1.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AP</td>\n",
       "      <td>3</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AR</td>\n",
       "      <td>15605</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>36573</td>\n",
       "      <td>1.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CA</td>\n",
       "      <td>216119</td>\n",
       "      <td>11.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CO</td>\n",
       "      <td>37914</td>\n",
       "      <td>1.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CT</td>\n",
       "      <td>24834</td>\n",
       "      <td>1.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>DC</td>\n",
       "      <td>4962</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_Abbreviation  filers  perc_comments\n",
       "0                 AK    4441           0.23\n",
       "1                 AL   29970           1.53\n",
       "2                 AP       3           0.00\n",
       "3                 AR   15605           0.80\n",
       "4                 AZ   36573           1.87\n",
       "5                 CA  216119          11.06\n",
       "6                 CO   37914           1.94\n",
       "7                 CT   24834           1.27\n",
       "8                 DC    4962           0.25"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g2.head(9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge population percentage with FCC comments percentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge 2 dataframes on state name\n",
    "pop_perc = pd.merge(pop_perc, g2, left_on='abbreviation',right_on='State_Abbreviation', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "      <th>perc_pop</th>\n",
       "      <th>name</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>filers</th>\n",
       "      <th>perc_comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4863300</td>\n",
       "      <td>1.51</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>29970</td>\n",
       "      <td>1.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>741894</td>\n",
       "      <td>0.23</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>AK</td>\n",
       "      <td>4441</td>\n",
       "      <td>0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6931071</td>\n",
       "      <td>2.14</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>AZ</td>\n",
       "      <td>36573</td>\n",
       "      <td>1.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2988248</td>\n",
       "      <td>0.92</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>AR</td>\n",
       "      <td>15605</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39250017</td>\n",
       "      <td>12.15</td>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>CA</td>\n",
       "      <td>216119</td>\n",
       "      <td>11.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5540545</td>\n",
       "      <td>1.71</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>CO</td>\n",
       "      <td>37914</td>\n",
       "      <td>1.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3576452</td>\n",
       "      <td>1.11</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "      <td>CT</td>\n",
       "      <td>24834</td>\n",
       "      <td>1.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>952065</td>\n",
       "      <td>0.29</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE</td>\n",
       "      <td>4723</td>\n",
       "      <td>0.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>681170</td>\n",
       "      <td>0.21</td>\n",
       "      <td>Washington DC</td>\n",
       "      <td>DC</td>\n",
       "      <td>DC</td>\n",
       "      <td>4962</td>\n",
       "      <td>0.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POPESTIMATE2016  perc_pop           name abbreviation State_Abbreviation  \\\n",
       "0          4863300      1.51        Alabama           AL                 AL   \n",
       "1           741894      0.23         Alaska           AK                 AK   \n",
       "2          6931071      2.14        Arizona           AZ                 AZ   \n",
       "3          2988248      0.92       Arkansas           AR                 AR   \n",
       "4         39250017     12.15     California           CA                 CA   \n",
       "5          5540545      1.71       Colorado           CO                 CO   \n",
       "6          3576452      1.11    Connecticut           CT                 CT   \n",
       "7           952065      0.29       Delaware           DE                 DE   \n",
       "8           681170      0.21  Washington DC           DC                 DC   \n",
       "\n",
       "   filers  perc_comments  \n",
       "0   29970           1.53  \n",
       "1    4441           0.23  \n",
       "2   36573           1.87  \n",
       "3   15605           0.80  \n",
       "4  216119          11.06  \n",
       "5   37914           1.94  \n",
       "6   24834           1.27  \n",
       "7    4723           0.24  \n",
       "8    4962           0.25  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pop_perc.head(9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculate Difference between comments and population proportions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_perc.to_csv('FCC_pop_comments_perc.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "FCC_pop_comments_perc= pop_perc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "FCC_pop_comments_perc['diff_perc_pop_comments'] = FCC_pop_comments_perc['perc_pop']-FCC_pop_comments_perc['perc_comments']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>POPESTIMATE2016</th>\n",
       "      <th>perc_pop</th>\n",
       "      <th>name</th>\n",
       "      <th>abbreviation</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>filers</th>\n",
       "      <th>perc_comments</th>\n",
       "      <th>diff_prop_pop_prop_com</th>\n",
       "      <th>diff_perc_pop_comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4863300</td>\n",
       "      <td>1.51</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>AL</td>\n",
       "      <td>AL</td>\n",
       "      <td>29970</td>\n",
       "      <td>1.53</td>\n",
       "      <td>-0.02</td>\n",
       "      <td>-0.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>741894</td>\n",
       "      <td>0.23</td>\n",
       "      <td>Alaska</td>\n",
       "      <td>AK</td>\n",
       "      <td>AK</td>\n",
       "      <td>4441</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6931071</td>\n",
       "      <td>2.14</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>AZ</td>\n",
       "      <td>AZ</td>\n",
       "      <td>36573</td>\n",
       "      <td>1.87</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2988248</td>\n",
       "      <td>0.92</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>AR</td>\n",
       "      <td>AR</td>\n",
       "      <td>15605</td>\n",
       "      <td>0.80</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>39250017</td>\n",
       "      <td>12.15</td>\n",
       "      <td>California</td>\n",
       "      <td>CA</td>\n",
       "      <td>CA</td>\n",
       "      <td>216119</td>\n",
       "      <td>11.06</td>\n",
       "      <td>1.09</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5540545</td>\n",
       "      <td>1.71</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>CO</td>\n",
       "      <td>CO</td>\n",
       "      <td>37914</td>\n",
       "      <td>1.94</td>\n",
       "      <td>-0.23</td>\n",
       "      <td>-0.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3576452</td>\n",
       "      <td>1.11</td>\n",
       "      <td>Connecticut</td>\n",
       "      <td>CT</td>\n",
       "      <td>CT</td>\n",
       "      <td>24834</td>\n",
       "      <td>1.27</td>\n",
       "      <td>-0.16</td>\n",
       "      <td>-0.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>952065</td>\n",
       "      <td>0.29</td>\n",
       "      <td>Delaware</td>\n",
       "      <td>DE</td>\n",
       "      <td>DE</td>\n",
       "      <td>4723</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>681170</td>\n",
       "      <td>0.21</td>\n",
       "      <td>Washington DC</td>\n",
       "      <td>DC</td>\n",
       "      <td>DC</td>\n",
       "      <td>4962</td>\n",
       "      <td>0.25</td>\n",
       "      <td>-0.04</td>\n",
       "      <td>-0.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>20612439</td>\n",
       "      <td>6.38</td>\n",
       "      <td>Florida</td>\n",
       "      <td>FL</td>\n",
       "      <td>FL</td>\n",
       "      <td>125912</td>\n",
       "      <td>6.44</td>\n",
       "      <td>-0.06</td>\n",
       "      <td>-0.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   POPESTIMATE2016  perc_pop           name abbreviation State_Abbreviation  \\\n",
       "0          4863300      1.51        Alabama           AL                 AL   \n",
       "1           741894      0.23         Alaska           AK                 AK   \n",
       "2          6931071      2.14        Arizona           AZ                 AZ   \n",
       "3          2988248      0.92       Arkansas           AR                 AR   \n",
       "4         39250017     12.15     California           CA                 CA   \n",
       "5          5540545      1.71       Colorado           CO                 CO   \n",
       "6          3576452      1.11    Connecticut           CT                 CT   \n",
       "7           952065      0.29       Delaware           DE                 DE   \n",
       "8           681170      0.21  Washington DC           DC                 DC   \n",
       "9         20612439      6.38        Florida           FL                 FL   \n",
       "\n",
       "   filers  perc_comments  diff_prop_pop_prop_com  diff_perc_pop_comments  \n",
       "0   29970           1.53                   -0.02                   -0.02  \n",
       "1    4441           0.23                    0.00                    0.00  \n",
       "2   36573           1.87                    0.27                    0.27  \n",
       "3   15605           0.80                    0.12                    0.12  \n",
       "4  216119          11.06                    1.09                    1.09  \n",
       "5   37914           1.94                   -0.23                   -0.23  \n",
       "6   24834           1.27                   -0.16                   -0.16  \n",
       "7    4723           0.24                    0.05                    0.05  \n",
       "8    4962           0.25                   -0.04                   -0.04  \n",
       "9  125912           6.44                   -0.06                   -0.06  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "FCC_pop_comments_perc.head(10) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "#CHANGED TO PERC2\n",
    "FCC_pop_comments_perc.to_csv('FCC_pop_comments_perc2.csv',index=False)"
   ]
  }
 ],
 "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
}