01 Importing 3m FCC records from SQL .ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### This Notebook will <li> import the 3 million records from a SQL dataset dataset <li> expand out the column of addresses <li> expand out the international addresses <li> clean up the zip codes <li> extract US addresses inside the international address column <li> identify fake zip codes from the international address column <li> import US postal codes and match & merge state abbreviation with the zip codes <li> store and save the cleaned dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import packages\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import numpy as np\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import 3 million records using PostgresSQL "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>date_submission</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>confirmation_number</th>\n",
       "      <th>text_data</th>\n",
       "      <th>index</th>\n",
       "      <th>filers</th>\n",
       "      <th>addressentity</th>\n",
       "      <th>internationaladdressentity</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>201707112383008210</td>\n",
       "      <td>Allowing broadband providers to throttle their...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Yaroslav Kalinina\"}]</td>\n",
       "      <td>{\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...</td>\n",
       "      <td>{\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...</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>2017071190138844</td>\n",
       "      <td>We need net neutralityto continue. A free and ...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Galina Medvedeva\"}]</td>\n",
       "      <td>{\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...</td>\n",
       "      <td>{\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...</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>20170711233022335</td>\n",
       "      <td>make sure net neutrality does not dissapear. I...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Philemon Butusov\"}]</td>\n",
       "      <td>{\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...</td>\n",
       "      <td>{\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...</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>201707112678519835</td>\n",
       "      <td>We need net neutralityto continue. A free and ...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Barbara Ulyanova\"}]</td>\n",
       "      <td>{\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...</td>\n",
       "      <td>{\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...</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>201707111845703590</td>\n",
       "      <td>Please save the internet from the corporations...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Matilda Zhdanova\"}]</td>\n",
       "      <td>{\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...</td>\n",
       "      <td>{\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...</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 confirmation_number  \\\n",
       "0  YaroslavKalinina@pornhub.com  201707112383008210   \n",
       "1   GalinaMedvedeva@pornhub.com    2017071190138844   \n",
       "2   PhilemonButusov@pornhub.com   20170711233022335   \n",
       "3   BarbaraUlyanova@pornhub.com  201707112678519835   \n",
       "4   MatildaZhdanova@pornhub.com  201707111845703590   \n",
       "\n",
       "                                           text_data           index  \\\n",
       "0  Allowing broadband providers to throttle their...  filings.2017.7   \n",
       "1  We need net neutralityto continue. A free and ...  filings.2017.7   \n",
       "2  make sure net neutrality does not dissapear. I...  filings.2017.7   \n",
       "3  We need net neutralityto continue. A free and ...  filings.2017.7   \n",
       "4  Please save the internet from the corporations...  filings.2017.7   \n",
       "\n",
       "                           filers  \\\n",
       "0  [{\"name\":\"Yaroslav Kalinina\"}]   \n",
       "1   [{\"name\":\"Galina Medvedeva\"}]   \n",
       "2   [{\"name\":\"Philemon Butusov\"}]   \n",
       "3   [{\"name\":\"Barbara Ulyanova\"}]   \n",
       "4   [{\"name\":\"Matilda Zhdanova\"}]   \n",
       "\n",
       "                                       addressentity  \\\n",
       "0  {\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...   \n",
       "1  {\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...   \n",
       "2  {\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...   \n",
       "3  {\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...   \n",
       "4  {\"city\":\"\",\"address_line_1\":\"\",\"state\":\"\",\"zip...   \n",
       "\n",
       "                          internationaladdressentity  \n",
       "0  {\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...  \n",
       "1  {\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...  \n",
       "2  {\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...  \n",
       "3  {\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...  \n",
       "4  {\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Import to connect to PostgreSQL \n",
    "import psycopg2\n",
    "\n",
    "#connection\n",
    "try: \n",
    "    conn = psycopg2.connect(host=\"localhost\",database=\"proc_17_108\", user=\"postgres\", password=\"********\")\n",
    "except:\n",
    "    print(\"I am unable to connect to the database\")\n",
    "\n",
    "#SQL Query    \n",
    "sql = \"SELECT * FROM proc_17_108 LIMIT 3000000\"\n",
    "df_3M = pd.read_sql(sql, conn)\n",
    "\n",
    "# save to CSV file for 3M records\n",
    "df_3M.to_csv('FCC_comments_3M.csv', index=False)\n",
    "\n",
    "conn.close()\n",
    "df_3M.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explore the dataset <br>\n",
    "Notice the pornhub email addresses above, the identical submission times down to the second, and the Russian names? There is also something odd about contents of the International Address Entity column. We will investigate all these."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_3M = pd.read_csv('FCC_comments_3M.csv', encoding='utf8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A section of my EDA has to do with zipcode analysis. To evaluate the zipcodes from the domestic address columns I will remove any NaN.  "
   ]
  },
  {
   "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>id</th>\n",
       "      <th>text_data</th>\n",
       "      <th>addressentity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1101755</th>\n",
       "      <td>59f8c83250b733300f176af3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1917656</th>\n",
       "      <td>59f8d93b50b733300f23f38c</td>\n",
       "      <td>IF YOU WANT CHINA TO TAKE OVER YOUR INTERNET B...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               id  \\\n",
       "1101755  59f8c83250b733300f176af3   \n",
       "1917656  59f8d93b50b733300f23f38c   \n",
       "\n",
       "                                                 text_data addressentity  \n",
       "1101755                                                NaN           NaN  \n",
       "1917656  IF YOU WANT CHINA TO TAKE OVER YOUR INTERNET B...           NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Since most of the work will happen on addressentity, remove records with NaN for addressentity\n",
    "# Explore addressentity NaN records\n",
    "\n",
    "delete_address = df_3M[df_3M['addressentity'].isnull()]\n",
    "delete_address[['id','text_data','addressentity']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2999998 entries, 0 to 2999999\n",
      "Data columns (total 9 columns):\n",
      "id                            object\n",
      "date_submission               object\n",
      "contact_email                 object\n",
      "confirmation_number           float64\n",
      "text_data                     object\n",
      "index                         object\n",
      "filers                        object\n",
      "addressentity                 object\n",
      "internationaladdressentity    object\n",
      "dtypes: float64(1), object(8)\n",
      "memory usage: 228.9+ MB\n"
     ]
    }
   ],
   "source": [
    "# Remove the 2 records with NaN for addressentity\n",
    "df_3M = df_3M[~df_3M['addressentity'].isnull()]\n",
    "df_3M.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Expand dictionaries in addressentity field into their own dataframe "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#expand dictionary using eval() in the addressentity column to get the keys/values\n",
    "#address_line_1\n",
    "#address_line_1\n",
    "#city\n",
    "#state\n",
    "#zip4\n",
    "#zipcode\n",
    "\n",
    "df_3M[\"addressentity\"] = df_3M[\"addressentity\"].apply(lambda x : dict(eval(x)))\n",
    "\n",
    "#then apply pd.Series to turn the values into a dataframe\n",
    "df_address = df_3M[\"addressentity\"].apply(pd.Series)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2999998 entries, 0 to 2999999\n",
      "Data columns (total 11 columns):\n",
      "address_line_1                object\n",
      "address_line_2                object\n",
      "city                          object\n",
      "contact_email                 object\n",
      "express_comment               float64\n",
      "internationaladdressentity    object\n",
      "state                         object\n",
      "text_data                     object\n",
      "zip4                          object\n",
      "zip_code                      object\n",
      "zipcode                       object\n",
      "dtypes: float64(1), object(10)\n",
      "memory usage: 274.7+ MB\n"
     ]
    }
   ],
   "source": [
    "#explore the expanded address dataframe\n",
    "df_address.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are more fields here than there are supposed to be for a United States address. The inclusion of following keys from the dictionary are surprises: <li> an <i> additional </i> contact_email <li> an express_comment column <li> an <i>additional</i>  text_data column <li> <i>2</i> zipcode columns <li> an <i>additional</i>  internationaladdressentity column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's investigate the peculiar 'express_comment' column, which is of type float"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select rows where express comment is not null\n",
    "express_comment = df_address[~df_address['express_comment'].isnull()]\n",
    "\n",
    "#display rows without street address details\n",
    "express_comment[['city','contact_email','express_comment','zip_code','internationaladdressentity','zipcode']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "'johndoe@gmail.com'? It appears we have some falsified entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows where contat_email is \"johndoe@gmail.com\" = 15027.\n"
     ]
    }
   ],
   "source": [
    "print('Number of rows where contat_email is \"johndoe@gmail.com\" = {}.'.format(\\\n",
    "len(df_address[df_address['contact_email']=='johndoe@gmail.com'])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And the internationaladdressentity column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows where internationaladdressentity is not blank = 15027.\n"
     ]
    }
   ],
   "source": [
    "#select rows where internationaladdressentity is not null\n",
    "print('Number of rows where internationaladdressentity is not blank = {}.'.format(\\\n",
    "len(df_address[~df_address['internationaladdressentity'].isnull()]\\\n",
    "    [['city','contact_email','express_comment','zip_code','internationaladdressentity','zipcode']])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows where email is johndoe AND internationaladdressentity is not blank = 15027.\n"
     ]
    }
   ],
   "source": [
    "#select rows where internationaladdressentity is not null & johndoe\n",
    "print('Number of rows where email is johndoe AND internationaladdressentity is not blank = {}.'.format(\\\n",
    "len(df_address[(~df_address['internationaladdressentity'].isnull()) & \\\n",
    "               (df_address['contact_email']=='johndoe@gmail.com')]\\\n",
    "    [['city','contact_email','express_comment','zip_code','internationaladdressentity','zipcode']])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So all the 15,027 rows are good candidates for being fake filings where the field: addressentity was stuffed with additional keys, a fake email and similar submission times."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also the pair of zipcodes might cause some confusion. Let's see if there any null 'zip_code'?"
   ]
  },
  {
   "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>city</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>text_data</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7228</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7851</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8368</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16373</th>\n",
       "      <td>WORCESTER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19144</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35902</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35907</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38055</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38060</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38087</th>\n",
       "      <td>ENCINITAS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92024</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            city contact_email  express_comment text_data zip_code zipcode\n",
       "7228   ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "7851   ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "8368   ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "16373  WORCESTER           NaN              NaN       NaN      NaN   01609\n",
       "19144  ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "35902  ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "35907  ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "38055  ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "38060  ENCINITAS           NaN              NaN       NaN      NaN   92024\n",
       "38087  ENCINITAS           NaN              NaN       NaN      NaN   92024"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#select rows where zipcode is not null\n",
    "df_address[~df_address['zipcode'].isnull()][['city','contact_email','express_comment','text_data','zip_code','zipcode']].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The duplicate entries of this zipcode also indicate a fake filing (the redacted addresses from this all uppercase ENCITAS address are identical). Lets see what some of these records look like in the original data set:  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "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>date_submission</th>\n",
       "      <th>text_data</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>35902</th>\n",
       "      <td>2017-07-11T21:33:07.293Z</td>\n",
       "      <td>To whom it may concern:\\r\\nI would like my vid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38055</th>\n",
       "      <td>2017-07-11T21:32:15.635Z</td>\n",
       "      <td>To whom it may concern:\\r\\nI would like my vid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38060</th>\n",
       "      <td>2017-07-11T21:32:31.579Z</td>\n",
       "      <td>To whom it may concern:\\r\\nI would like my vid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38087</th>\n",
       "      <td>2017-07-11T21:32:33.863Z</td>\n",
       "      <td>To whom it may concern:\\r\\nI would like my vid...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                date_submission  \\\n",
       "35902  2017-07-11T21:33:07.293Z   \n",
       "38055  2017-07-11T21:32:15.635Z   \n",
       "38060  2017-07-11T21:32:31.579Z   \n",
       "38087  2017-07-11T21:32:33.863Z   \n",
       "\n",
       "                                               text_data  \n",
       "35902  To whom it may concern:\\r\\nI would like my vid...  \n",
       "38055  To whom it may concern:\\r\\nI would like my vid...  \n",
       "38060  To whom it may concern:\\r\\nI would like my vid...  \n",
       "38087  To whom it may concern:\\r\\nI would like my vid...  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_3M.loc[[35902,38055,38060,38087]][['date_submission','text_data']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice that the same text_data and similiarity in time of filing, these are within 1 minute. Also, not shown, the filing address(redacted) and filing email(redacted) are identical"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many of these in the df_address have text_data entries?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non null text_data rows: 15027, the same as 'johndoe@gmail' and 'express_comment'.\n"
     ]
    },
    {
     "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>city</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>text_data</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2287451</th>\n",
       "      <td>Plain City</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I support Net Neutrality</td>\n",
       "      <td>43064</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2287545</th>\n",
       "      <td>Boise</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Please do not over rule net neutrality as that...</td>\n",
       "      <td>83716</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2287588</th>\n",
       "      <td>Kenneth City</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>33709</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2287598</th>\n",
       "      <td>Kaysville</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>84037</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2287638</th>\n",
       "      <td>Deltona</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>32725</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2287693</th>\n",
       "      <td>Pacific Junction</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>51561</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2287824</th>\n",
       "      <td>truckee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>THere will be a revolution by the people if th...</td>\n",
       "      <td>96161</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2288016</th>\n",
       "      <td>Sandy Springs</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>This is an important issue for me and my family.</td>\n",
       "      <td>30350</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2288055</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Please keep Net Neutrality don't listen to the...</td>\n",
       "      <td>85739</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2288967</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2289435</th>\n",
       "      <td>hinsdale</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>You will kill the value of the internet if you...</td>\n",
       "      <td>60521</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2289606</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2290425</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2290664</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2290837</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2291119</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2291183</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2291635</th>\n",
       "      <td>Dundee</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>I believe strongly in Net Neutrality.  I belon...</td>\n",
       "      <td>97115</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2291654</th>\n",
       "      <td>Seattle</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td></td>\n",
       "      <td>98105</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2291695</th>\n",
       "      <td>Tucson</td>\n",
       "      <td>johndoe@gmail.com</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Please keep Net Neutrality don't listen to the...</td>\n",
       "      <td>85739</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     city      contact_email  express_comment  \\\n",
       "2287451        Plain City  johndoe@gmail.com              1.0   \n",
       "2287545             Boise  johndoe@gmail.com              1.0   \n",
       "2287588      Kenneth City  johndoe@gmail.com              1.0   \n",
       "2287598         Kaysville  johndoe@gmail.com              1.0   \n",
       "2287638           Deltona  johndoe@gmail.com              1.0   \n",
       "2287693  Pacific Junction  johndoe@gmail.com              1.0   \n",
       "2287824           truckee  johndoe@gmail.com              1.0   \n",
       "2288016     Sandy Springs  johndoe@gmail.com              1.0   \n",
       "2288055            Tucson  johndoe@gmail.com              1.0   \n",
       "2288967            Dundee  johndoe@gmail.com              1.0   \n",
       "2289435          hinsdale  johndoe@gmail.com              1.0   \n",
       "2289606            Dundee  johndoe@gmail.com              1.0   \n",
       "2290425            Dundee  johndoe@gmail.com              1.0   \n",
       "2290664            Dundee  johndoe@gmail.com              1.0   \n",
       "2290837            Dundee  johndoe@gmail.com              1.0   \n",
       "2291119            Dundee  johndoe@gmail.com              1.0   \n",
       "2291183            Dundee  johndoe@gmail.com              1.0   \n",
       "2291635            Dundee  johndoe@gmail.com              1.0   \n",
       "2291654           Seattle  johndoe@gmail.com              1.0   \n",
       "2291695            Tucson  johndoe@gmail.com              1.0   \n",
       "\n",
       "                                                 text_data zip_code zipcode  \n",
       "2287451                          I support Net Neutrality     43064     NaN  \n",
       "2287545  Please do not over rule net neutrality as that...    83716     NaN  \n",
       "2287588                                                       33709     NaN  \n",
       "2287598                                                       84037     NaN  \n",
       "2287638                                                       32725     NaN  \n",
       "2287693                                                       51561     NaN  \n",
       "2287824  THere will be a revolution by the people if th...    96161     NaN  \n",
       "2288016   This is an important issue for me and my family.    30350     NaN  \n",
       "2288055  Please keep Net Neutrality don't listen to the...    85739     NaN  \n",
       "2288967  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2289435  You will kill the value of the internet if you...    60521     NaN  \n",
       "2289606  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2290425  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2290664  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2290837  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2291119  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2291183  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2291635  I believe strongly in Net Neutrality.  I belon...    97115     NaN  \n",
       "2291654                                                       98105     NaN  \n",
       "2291695  Please keep Net Neutrality don't listen to the...    85739     NaN  "
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Non null text_data rows: {}, the same as 'johndoe@gmail' and 'express_comment'.\"\\\n",
    "        .format(len(df_address[~df_address['text_data'].isnull()])))\n",
    "df_address[~df_address['text_data'].isnull()]\\\n",
    "[['city','contact_email','express_comment','text_data','zip_code','zipcode']].head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>address_line_1</th>\n",
       "      <th>address_line_2</th>\n",
       "      <th>city</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2999993</th>\n",
       "      <td>794 Sheridan Ave.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Vineland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NJ</td>\n",
       "      <td>08361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999994</th>\n",
       "      <td>xxxxxxx</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Anaheim</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CA</td>\n",
       "      <td>92801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999995</th>\n",
       "      <td>53 sinclair dr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>greenlawn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NY</td>\n",
       "      <td>11740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999996</th>\n",
       "      <td>119 Arcaro drive</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC</td>\n",
       "      <td>27455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999997</th>\n",
       "      <td>2621 Fulton Ave; Apt#61</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sacramento</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CA</td>\n",
       "      <td>95821</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  address_line_1 address_line_2        city  express_comment  \\\n",
       "2999993        794 Sheridan Ave.            NaN    Vineland              NaN   \n",
       "2999994                  xxxxxxx            NaN     Anaheim              NaN   \n",
       "2999995           53 sinclair dr            NaN   greenlawn              NaN   \n",
       "2999996         119 Arcaro drive            NaN  Greensboro              NaN   \n",
       "2999997  2621 Fulton Ave; Apt#61            NaN  Sacramento              NaN   \n",
       "\n",
       "        state zip_code  \n",
       "2999993    NJ    08361  \n",
       "2999994    CA    92801  \n",
       "2999995    NY    11740  \n",
       "2999996    NC    27455  \n",
       "2999997    CA    95821  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_address.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Having two zip code fields is confusing. To what extent is there an entry in <i>both</i> columns?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Non null zipcode & zip_code rows = 0. There is no overlap between the 2 zip code columns.\n"
     ]
    },
    {
     "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>city</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>text_data</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>zipcode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [city, contact_email, express_comment, text_data, zip_code, zipcode]\n",
       "Index: []"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"Non null zipcode & zip_code rows = {}. There is no overlap between the 2 zip code columns.\".format(len\\\n",
    "       (df_address[~df_address['zipcode'].isnull() & ~df_address['zip_code'].isnull()])))\n",
    "\n",
    "#show rows where both zip_code and zipcode are present\n",
    "df_address[~df_address['zipcode'].isnull() & ~df_address['zip_code'].isnull()]\\\n",
    "[['city','contact_email','express_comment','text_data','zip_code','zipcode']].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Wrangling <br>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now clean up the df_address dataframe, and then merge it back to the main dataframe to continue the analysis."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets start by merging the zip code fields into a new column and adding leading 0's and deleting the old columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge zipcodes into new column and fill NA cells\n",
    "df_address['zipcode_blended'] = df_address['zip_code'].fillna('') + df_address['zipcode'].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unnecessary columns\n",
    "df_address.drop(['zip_code','zipcode'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add leading zeros to make all zip codes 5 digits as a string\n",
    "df_address['zipcode_blended'] = df_address['zipcode_blended'].str.zfill(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets drop the zip4 field and internationaladdressentity columns. In the first case we do not need the detail that the zip4 provides. For the internationaladdressentity field that, like the express_comment column, is only present when the contact_email is 'johndoe'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unnecessary columns\n",
    "df_address.drop(['internationaladdressentity','zip4'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets rename contact_email and text_data so there are no issues when this is merged back to the main dataset, and zip code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename columns\n",
    "df_address=df_address.rename(columns = {'contact_email':'contact_emailx','text_data':'text_datax','zipcode_blended':'zip_code'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2999998 entries, 0 to 2999999\n",
      "Data columns (total 8 columns):\n",
      "address_line_1     object\n",
      "address_line_2     object\n",
      "city               object\n",
      "contact_emailx     object\n",
      "express_comment    float64\n",
      "state              object\n",
      "text_datax         object\n",
      "zip_code           object\n",
      "dtypes: float64(1), object(7)\n",
      "memory usage: 206.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df_address.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 306,
   "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>address_line_2</th>\n",
       "      <th>city</th>\n",
       "      <th>contact_emailx</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>state</th>\n",
       "      <th>text_datax</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2999995</th>\n",
       "      <td></td>\n",
       "      <td>Vineland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NJ</td>\n",
       "      <td>NaN</td>\n",
       "      <td>08361</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999996</th>\n",
       "      <td></td>\n",
       "      <td>Anaheim</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999997</th>\n",
       "      <td></td>\n",
       "      <td>greenlawn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>11740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999998</th>\n",
       "      <td></td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>27455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999999</th>\n",
       "      <td></td>\n",
       "      <td>Sacramento</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>95821</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        address_line_2        city contact_emailx  express_comment state  \\\n",
       "2999995                   Vineland            NaN              NaN    NJ   \n",
       "2999996                    Anaheim            NaN              NaN    CA   \n",
       "2999997                  greenlawn            NaN              NaN    NY   \n",
       "2999998                 Greensboro            NaN              NaN    NC   \n",
       "2999999                 Sacramento            NaN              NaN    CA   \n",
       "\n",
       "        text_datax zip_code  \n",
       "2999995        NaN    08361  \n",
       "2999996        NaN    92801  \n",
       "2999997        NaN    11740  \n",
       "2999998        NaN    27455  \n",
       "2999999        NaN    95821  "
      ]
     },
     "execution_count": 306,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_address.iloc[:,1:].tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the main dataset, lets explore the internationaladdressentity column <br> These address appear legitimate but they do not exist."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "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>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   0\n",
       "0  {\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...\n",
       "1  {\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...\n",
       "2  {\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...\n",
       "3  {\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...\n",
       "4  {\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb..."
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_expanded_nonUS = df_3M['internationaladdressentity'].apply(pd.Series)\n",
    "df_expanded_nonUS.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets rename the column, then expand out the the 5 digit zipcodes using regex and take a look at some in more detail. I suspect the zipcodes are for non existant addresses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [],
   "source": [
    "#rename column\n",
    "df_expanded_nonUS.columns = ['intl_address']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define function to extract zip code from free form field \"international address entity\"\n",
    "def get_zip (y):\n",
    "    \"\"\"Takes 5 digits from string field for zip code - \n",
    "    returns false value if regex expression does not find 5 digit sequence\"\"\"\n",
    "    reg = re.compile('\\d{5}')\n",
    "    z = reg.findall(y)\n",
    "    if len(z) == 0:\n",
    "        #return \"no zip code\"\n",
    "        return float('nan')\n",
    "    else:\n",
    "        return z[0] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [],
   "source": [
    "# apply zip code funtion to extract 5 digit zip codes or replace with NaN\n",
    "df_expanded_nonUS['probably_fake_zip'] = df_expanded_nonUS['intl_address'].\\\n",
    "                                        apply(lambda x: get_zip(x) if type(x) == str else x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 236,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"addresstext\":\"4702 Ashmor Drive\\\\n55802,Duluth, United States\"}'"
      ]
     },
     "execution_count": 236,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#inspect first cell\n",
    "df_expanded_nonUS.loc[2]['intl_address']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the following details of this address <li> the escape character ('\\\\n') <li> the missing State abreviation <li> United States spelled out in full <br> These are not the way most Americans would enter address information, espcially if they went to the trouble to provide street address and zipcode, they would not omit the state. <br><br> This address, by the way, does not exist."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Another address is in Cyrillic, nothing odd about that in the international address field. Except that it is repeated mulitple times."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 237,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "340000    {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...\n",
       "340001    {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...\n",
       "340002    {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...\n",
       "340003    {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...\n",
       "Name: intl_address, dtype: object"
      ]
     },
     "execution_count": 237,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_expanded_nonUS.loc[340000:340003]['intl_address']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No 5 digit sequence exists here, so NaN is returned as expected."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 239,
   "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>intl_address</th>\n",
       "      <th>probably_fake_zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>340000</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340001</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340002</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340003</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             intl_address probably_fake_zip\n",
       "340000  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...               NaN\n",
       "340001  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...               NaN\n",
       "340002  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...               NaN\n",
       "340003  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...               NaN"
      ]
     },
     "execution_count": 239,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_expanded_nonUS[340000:340004]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What do these row numbers return for the df_address and the main dataset df_3M? <li> A zip code of 00000 <li> contact emails from pornhub and hurra.de accounts <li> identically timed submissions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "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>address_line_1</th>\n",
       "      <th>address_line_2</th>\n",
       "      <th>city</th>\n",
       "      <th>contact_emailx</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>state</th>\n",
       "      <th>text_datax</th>\n",
       "      <th>zip_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>340000</th>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340001</th>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340002</th>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>00000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340003</th>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td></td>\n",
       "      <td>NaN</td>\n",
       "      <td>00000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       address_line_1 address_line_2 city contact_emailx  express_comment  \\\n",
       "340000                           NaN                 NaN              NaN   \n",
       "340001                           NaN                 NaN              NaN   \n",
       "340002                           NaN                 NaN              NaN   \n",
       "340003                           NaN                 NaN              NaN   \n",
       "\n",
       "       state text_datax zip_code  \n",
       "340000              NaN    00000  \n",
       "340001              NaN    00000  \n",
       "340002              NaN    00000  \n",
       "340003              NaN    00000  "
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_address[340000:340004]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 296,
   "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>id</th>\n",
       "      <th>date_submission</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>confirmation_number</th>\n",
       "      <th>text_data</th>\n",
       "      <th>index</th>\n",
       "      <th>filers</th>\n",
       "      <th>addressentity</th>\n",
       "      <th>internationaladdressentity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>340000</th>\n",
       "      <td>59f8b9cb50b733300f0bd155</td>\n",
       "      <td>2017-07-12T03:04:06.043Z</td>\n",
       "      <td>TrifonAbramovich@pornhub.com</td>\n",
       "      <td>2.017071e+17</td>\n",
       "      <td>The Open Internet rules (net neutrality) are e...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Trifon Abramovich\"}]</td>\n",
       "      <td>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340001</th>\n",
       "      <td>59f8b9cb50b733300f0bd156</td>\n",
       "      <td>2017-07-12T03:04:06.088Z</td>\n",
       "      <td>TeresaSukhorukova@hurra.de</td>\n",
       "      <td>2.017071e+16</td>\n",
       "      <td>On July 12 is the Protect Net Neutrality Day o...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Teresa Sukhorukova\"}]</td>\n",
       "      <td>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340002</th>\n",
       "      <td>59f8b9cb50b733300f0bd157</td>\n",
       "      <td>2017-07-12T03:04:06.166Z</td>\n",
       "      <td>RinatTretyakov@hurra.de</td>\n",
       "      <td>2.017071e+16</td>\n",
       "      <td>On July 12 is the Protect Net Neutrality Day o...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Rinat Tretyakov\"}]</td>\n",
       "      <td>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340003</th>\n",
       "      <td>59f8b9cb50b733300f0bd158</td>\n",
       "      <td>2017-07-12T03:04:06.331Z</td>\n",
       "      <td>MuslimShcherbakov@pornhub.com</td>\n",
       "      <td>2.017071e+16</td>\n",
       "      <td>The Open Internet rules (net neutrality) are e...</td>\n",
       "      <td>filings.2017.7</td>\n",
       "      <td>[{\"name\":\"Muslim Shcherbakov\"}]</td>\n",
       "      <td>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              id           date_submission  \\\n",
       "340000  59f8b9cb50b733300f0bd155  2017-07-12T03:04:06.043Z   \n",
       "340001  59f8b9cb50b733300f0bd156  2017-07-12T03:04:06.088Z   \n",
       "340002  59f8b9cb50b733300f0bd157  2017-07-12T03:04:06.166Z   \n",
       "340003  59f8b9cb50b733300f0bd158  2017-07-12T03:04:06.331Z   \n",
       "\n",
       "                        contact_email  confirmation_number  \\\n",
       "340000   TrifonAbramovich@pornhub.com         2.017071e+17   \n",
       "340001     TeresaSukhorukova@hurra.de         2.017071e+16   \n",
       "340002        RinatTretyakov@hurra.de         2.017071e+16   \n",
       "340003  MuslimShcherbakov@pornhub.com         2.017071e+16   \n",
       "\n",
       "                                                text_data           index  \\\n",
       "340000  The Open Internet rules (net neutrality) are e...  filings.2017.7   \n",
       "340001  On July 12 is the Protect Net Neutrality Day o...  filings.2017.7   \n",
       "340002  On July 12 is the Protect Net Neutrality Day o...  filings.2017.7   \n",
       "340003  The Open Internet rules (net neutrality) are e...  filings.2017.7   \n",
       "\n",
       "                                 filers  \\\n",
       "340000   [{\"name\":\"Trifon Abramovich\"}]   \n",
       "340001  [{\"name\":\"Teresa Sukhorukova\"}]   \n",
       "340002     [{\"name\":\"Rinat Tretyakov\"}]   \n",
       "340003  [{\"name\":\"Muslim Shcherbakov\"}]   \n",
       "\n",
       "                                            addressentity  \\\n",
       "340000  {'city': '', 'address_line_1': '', 'state': ''...   \n",
       "340001  {'city': '', 'address_line_1': '', 'state': ''...   \n",
       "340002  {'city': '', 'address_line_1': '', 'state': ''...   \n",
       "340003  {'city': '', 'address_line_1': '', 'state': ''...   \n",
       "\n",
       "                               internationaladdressentity  \n",
       "340000  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...  \n",
       "340001  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...  \n",
       "340002  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...  \n",
       "340003  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...  "
      ]
     },
     "execution_count": 296,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_3M[340000:340004]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many of these records have that address written in Cyrillic?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 240,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Isolate the address in Cyrillic\n",
    "x = df_expanded_nonUS.loc[340000]['intl_address']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 245,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows that have the same address written in Cyrillic: 444715!\n"
     ]
    }
   ],
   "source": [
    "#Print the number of rows\n",
    "print('Number of rows that have the same address written in Cyrillic: {}!'\\\n",
    "      .format(len(df_expanded_nonUS[df_expanded_nonUS['intl_address']==x])))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "What are the most common zip codes present in our dataset? The 00000s are from the filling out the zip code string."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Zip Code    QTY\n",
      "00000    694675\n",
      "94110      1376\n",
      "97115      1353\n",
      "10025      1315\n",
      "95060      1230\n",
      "98103      1226\n",
      "10011      1214\n",
      "14850      1197\n",
      "11229      1184\n",
      "11201      1172\n",
      "48103      1134\n",
      "97402      1133\n",
      "11215      1087\n",
      "97202      1081\n",
      "60647      1065\n",
      "Name: zip_code, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print('Zip Code    QTY')\n",
    "print(df_address['zip_code'].value_counts(sort=True, dropna=False).head(15))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the df_address data to the df_3M"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 308,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df_3M, df_address], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 309,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 2999998 entries, 0 to 2999999\n",
      "Data columns (total 17 columns):\n",
      "id                            object\n",
      "date_submission               object\n",
      "contact_email                 object\n",
      "confirmation_number           float64\n",
      "text_data                     object\n",
      "index                         object\n",
      "filers                        object\n",
      "addressentity                 object\n",
      "internationaladdressentity    object\n",
      "address_line_1                object\n",
      "address_line_2                object\n",
      "city                          object\n",
      "contact_emailx                object\n",
      "express_comment               float64\n",
      "state                         object\n",
      "text_datax                    object\n",
      "zip_code                      object\n",
      "dtypes: float64(2), object(15)\n",
      "memory usage: 492.0+ MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 310,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge text_data column and fill NA cells\n",
    "df['text_data'] = df['text_data'].fillna('') + df['text_datax'].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 311,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge text_data column and fill NA cells\n",
    "df['contact_email'] = df['contact_email'].fillna('') + df['contact_emailx'].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unnecessary columns\n",
    "df.drop(['text_datax','contact_emailx'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 314,
   "metadata": {},
   "outputs": [],
   "source": [
    "#save the progress\n",
    "df_3M.to_csv('df_3M.csv',index=False)\n",
    "df_address.to_csv('df_address.csv',index=False)\n",
    "df.to_csv('df.csv',index=False)\n",
    "df_expanded_nonUS.to_csv('df_expanded_nonUS.csv',index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets clean up the expanded international address dataframe so only US address zip codes are present and drop zip from the remaining overseas addresses.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 367,
   "metadata": {},
   "outputs": [],
   "source": [
    "#US addresses in international address field\n",
    "#with 5 digits in address that are likely fake\n",
    "df_expanded_nonUS['fake_zip'] = df_expanded_nonUS[ \\\n",
    "                  (df_expanded_nonUS['intl_address']!='{\"addresstext\":\"\"}') & \\ #empty contents\n",
    "                  (~df_expanded_nonUS['intl_address'].isnull()) &\\ #null contents\n",
    "                  (df_expanded_nonUS['intl_address']!='{}') & \\ #'blank contents\n",
    "                  (df_expanded_nonUS['intl_address'].str.contains(\"United States\")) & \\ #United States specified- though no cities\n",
    "                  (~df_expanded_nonUS['probably_fake_zip'].isnull())] \\ #has an extracted 5 digit string zip code\n",
    "                 ['probably_fake_zip'] # copy contents to fake_zip column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 370,
   "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>intl_address</th>\n",
       "      <th>probably_fake_zip</th>\n",
       "      <th>fake_zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...</td>\n",
       "      <td>55792</td>\n",
       "      <td>55792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...</td>\n",
       "      <td>55415</td>\n",
       "      <td>55415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...</td>\n",
       "      <td>55802</td>\n",
       "      <td>55802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...</td>\n",
       "      <td>55406</td>\n",
       "      <td>55406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...</td>\n",
       "      <td>39401</td>\n",
       "      <td>39401</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        intl_address probably_fake_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",
       "  fake_zip  \n",
       "0    55792  \n",
       "1    55415  \n",
       "2    55802  \n",
       "3    55406  \n",
       "4    39401  "
      ]
     },
     "execution_count": 370,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_expanded_nonUS.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 371,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unnecessary columns\n",
    "df_expanded_nonUS.drop(['probably_fake_zip'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 375,
   "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>intl_address</th>\n",
       "      <th>fake_zip</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>340000</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340001</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340002</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340003</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340004</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340005</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340006</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340007</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340008</th>\n",
       "      <td>{\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>340009</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             intl_address fake_zip\n",
       "340000  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340001  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340002  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340003  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340004  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340005  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340006  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340007  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340008  {\"addresstext\":\"улица Полевая \\n кв. 391 \\n Че...      NaN\n",
       "340009                                                NaN      NaN"
      ]
     },
     "execution_count": 375,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_expanded_nonUS[340000:340010]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the international address fake zip information with the main dataset df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 377,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df, df_expanded_nonUS], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets remove some more unnecessary fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 381,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop unnecessary columns\n",
    "df.drop(['confirmation_number','index','address_line_2','internationaladdressentity'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>id</th>\n",
       "      <th>date_submission</th>\n",
       "      <th>contact_email</th>\n",
       "      <th>text_data</th>\n",
       "      <th>filers</th>\n",
       "      <th>addressentity</th>\n",
       "      <th>address_line_1</th>\n",
       "      <th>city</th>\n",
       "      <th>express_comment</th>\n",
       "      <th>state</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>intl_address</th>\n",
       "      <th>fake_zip</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>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>NaN</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",
       "    </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>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>NaN</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",
       "    </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>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>NaN</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",
       "    </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>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>NaN</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",
       "    </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>{'city': '', 'address_line_1': '', 'state': ''...</td>\n",
       "      <td>NaN</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",
       "    </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  \\\n",
       "0  [{\"name\":\"Yaroslav Kalinina\"}]   \n",
       "1   [{\"name\":\"Galina Medvedeva\"}]   \n",
       "2   [{\"name\":\"Philemon Butusov\"}]   \n",
       "3   [{\"name\":\"Barbara Ulyanova\"}]   \n",
       "4   [{\"name\":\"Matilda Zhdanova\"}]   \n",
       "\n",
       "                                       addressentity address_line_1 city  \\\n",
       "0  {'city': '', 'address_line_1': '', 'state': ''...            NaN  NaN   \n",
       "1  {'city': '', 'address_line_1': '', 'state': ''...            NaN  NaN   \n",
       "2  {'city': '', 'address_line_1': '', 'state': ''...            NaN  NaN   \n",
       "3  {'city': '', 'address_line_1': '', 'state': ''...            NaN  NaN   \n",
       "4  {'city': '', 'address_line_1': '', 'state': ''...            NaN  NaN   \n",
       "\n",
       "   express_comment state zip_code  \\\n",
       "0              NaN   NaN      NaN   \n",
       "1              NaN   NaN      NaN   \n",
       "2              NaN   NaN      NaN   \n",
       "3              NaN   NaN      NaN   \n",
       "4              NaN   NaN      NaN   \n",
       "\n",
       "                                        intl_address fake_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  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Convert fake_zip to integer from float, convert to string and zero fill for 5 characters."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "#amend fake_zip field where it is not null\n",
    "df['fake_zip']=df[~df['fake_zip'].isnull()]['fake_zip'].astype(int).astype(str).str.zfill(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fake Zip QTY\n",
      "39201    473\n",
      "90017    291\n",
      "39211    226\n",
      "49503    187\n",
      "48075    152\n",
      "39213    127\n",
      "19108    124\n",
      "30303    102\n",
      "94612    101\n",
      "21202     98\n",
      "10016     97\n",
      "10013     96\n",
      "07102     94\n",
      "39402     93\n",
      "10011     93\n",
      "Name: fake_zip, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print('Fake Zip QTY')\n",
    "print(df['fake_zip'].value_counts(sort=True).head(15))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Confirm that there is no overlap in zip_code and fake_zip rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Number of rows where there is both a zip_code and fake_zip entry\n",
    "len(df[~df['zip_code'].isnull() & ~df['fake_zip'].isnull()])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the zip code and fake zip fields. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge zip codes column and fill NA cells\n",
    "df['combined_zip'] =df['zip_code'].fillna(df['fake_zip'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['combined_zip']=df[~df['combined_zip'].isnull()]['combined_zip'].astype(int).astype(str).str.zfill(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a flagged field for the fake_zip for use in identify fake fillings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['flagged_zip'] = df['fake_zip'].apply(lambda x: 0 if pd.isnull(x) else 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop more unnecessary columns\n",
    "df.drop(['fake_zip','zip_code','addressentity'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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",
       "    </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  flagged_zip  \n",
       "0  {\"addresstext\":\"3575 Eagle Lane\\n55792,Virgini...        55792            1  \n",
       "1  {\"addresstext\":\"2245 Lodgeville Road\\n55415,Mi...        55415            1  \n",
       "2  {\"addresstext\":\"4702 Ashmor Drive\\n55802,Dulut...        55802            1  \n",
       "3  {\"addresstext\":\"2323 Lodgeville Road\\n55406,Mi...        55406            1  \n",
       "4  {\"addresstext\":\"2135 Lena Lane\\n39401,Hattiesb...        39401            1  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Reading in the US Postal Codes dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Zip_Code_num</th>\n",
       "      <th>Place_Name</th>\n",
       "      <th>State</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>County</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Zip_Code_str</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>501</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>40.8154</td>\n",
       "      <td>-73.0451</td>\n",
       "      <td>501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>544</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>40.8154</td>\n",
       "      <td>-73.0451</td>\n",
       "      <td>544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>Agawam</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>42.0702</td>\n",
       "      <td>-72.6227</td>\n",
       "      <td>1001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1002</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>42.3671</td>\n",
       "      <td>-72.4646</td>\n",
       "      <td>1002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1003</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>42.3919</td>\n",
       "      <td>-72.5248</td>\n",
       "      <td>1003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Zip_Code_num  Place_Name          State State_Abbreviation     County  \\\n",
       "0           501  Holtsville       New York                 NY    Suffolk   \n",
       "1           544  Holtsville       New York                 NY    Suffolk   \n",
       "2          1001      Agawam  Massachusetts                 MA    Hampden   \n",
       "3          1002     Amherst  Massachusetts                 MA  Hampshire   \n",
       "4          1003     Amherst  Massachusetts                 MA  Hampshire   \n",
       "\n",
       "   Latitude  Longitude  Zip_Code_str  \n",
       "0   40.8154   -73.0451           501  \n",
       "1   40.8154   -73.0451           544  \n",
       "2   42.0702   -72.6227          1001  \n",
       "3   42.3671   -72.4646          1002  \n",
       "4   42.3919   -72.5248          1003  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# import zip codes, cities, states and geo coordinates \n",
    "us_postal_codes = pd.read_csv('us_postal_codes_as_str.csv', encoding='utf8')\n",
    "us_postal_codes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Zip_Code_num</th>\n",
       "      <th>Place_Name</th>\n",
       "      <th>State</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "      <th>County</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>Zip_Code_str</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>501</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>40.8154</td>\n",
       "      <td>-73.0451</td>\n",
       "      <td>00501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>544</td>\n",
       "      <td>Holtsville</td>\n",
       "      <td>New York</td>\n",
       "      <td>NY</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>40.8154</td>\n",
       "      <td>-73.0451</td>\n",
       "      <td>00544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1001</td>\n",
       "      <td>Agawam</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampden</td>\n",
       "      <td>42.0702</td>\n",
       "      <td>-72.6227</td>\n",
       "      <td>01001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1002</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>42.3671</td>\n",
       "      <td>-72.4646</td>\n",
       "      <td>01002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1003</td>\n",
       "      <td>Amherst</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>MA</td>\n",
       "      <td>Hampshire</td>\n",
       "      <td>42.3919</td>\n",
       "      <td>-72.5248</td>\n",
       "      <td>01003</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Zip_Code_num  Place_Name          State State_Abbreviation     County  \\\n",
       "0           501  Holtsville       New York                 NY    Suffolk   \n",
       "1           544  Holtsville       New York                 NY    Suffolk   \n",
       "2          1001      Agawam  Massachusetts                 MA    Hampden   \n",
       "3          1002     Amherst  Massachusetts                 MA  Hampshire   \n",
       "4          1003     Amherst  Massachusetts                 MA  Hampshire   \n",
       "\n",
       "   Latitude  Longitude Zip_Code_str  \n",
       "0   40.8154   -73.0451        00501  \n",
       "1   40.8154   -73.0451        00544  \n",
       "2   42.0702   -72.6227        01001  \n",
       "3   42.3671   -72.4646        01002  \n",
       "4   42.3919   -72.5248        01003  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#convert zip code integer to string and fill out to 5 places\n",
    "us_postal_codes['Zip_Code_str'] = us_postal_codes['Zip_Code_str'].astype(str).str.zfill(5)\n",
    "us_postal_codes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge dfs of US zipcodes with us_postal_codes to create an appropriate mapping for state_abbreviations\n",
    "df = pd.merge(df, us_postal_codes, left_on='combined_zip',right_on='Zip_Code_str', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#drop more unnecessary columns\n",
    "df.drop(['Latitude','Longitude','County','State','Zip_Code_str','Zip_Code_num'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('df_5.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Stored 'df' (DataFrame)\n"
     ]
    }
   ],
   "source": [
    "%store df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": 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>Zip_Code_num</th>\n",
       "      <th>Place_Name</th>\n",
       "      <th>State_Abbreviation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2999993</th>\n",
       "      <td>59f8eeec50b733300f347152</td>\n",
       "      <td>2017-07-17T23:16:41+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cable and phone companies provide access to th...</td>\n",
       "      <td>[{\"name\":\"Ismael Rodriguez\"}]</td>\n",
       "      <td>794 Sheridan Ave.</td>\n",
       "      <td>Vineland</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NJ</td>\n",
       "      <td>{\"addresstext\":\"\"}</td>\n",
       "      <td>08361</td>\n",
       "      <td>0</td>\n",
       "      <td>8361.0</td>\n",
       "      <td>Vineland</td>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999994</th>\n",
       "      <td>59f8eeec50b733300f3482a5</td>\n",
       "      <td>2017-07-17T23:19:39+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Net Neutrality is not negotiable. It's essenti...</td>\n",
       "      <td>[{\"name\":\"Angelica Munoz\"}]</td>\n",
       "      <td>xxxxxxx</td>\n",
       "      <td>Anaheim</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CA</td>\n",
       "      <td>{\"addresstext\":\"\"}</td>\n",
       "      <td>92801</td>\n",
       "      <td>0</td>\n",
       "      <td>92801.0</td>\n",
       "      <td>Anaheim</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999995</th>\n",
       "      <td>59f8eeec50b733300f347153</td>\n",
       "      <td>2017-07-17T23:16:41+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cable and phone companies provide access to th...</td>\n",
       "      <td>[{\"name\":\"Richard Krueger\"}]</td>\n",
       "      <td>53 sinclair dr</td>\n",
       "      <td>greenlawn</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NY</td>\n",
       "      <td>{\"addresstext\":\"\"}</td>\n",
       "      <td>11740</td>\n",
       "      <td>0</td>\n",
       "      <td>11740.0</td>\n",
       "      <td>Greenlawn</td>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999996</th>\n",
       "      <td>59f8eeec50b733300f347154</td>\n",
       "      <td>2017-07-17T23:16:41+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cable and phone companies provide access to th...</td>\n",
       "      <td>[{\"name\":\"Kathi Brinkman\"}]</td>\n",
       "      <td>119 Arcaro drive</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NC</td>\n",
       "      <td>{\"addresstext\":\"\"}</td>\n",
       "      <td>27455</td>\n",
       "      <td>0</td>\n",
       "      <td>27455.0</td>\n",
       "      <td>Greensboro</td>\n",
       "      <td>NC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2999997</th>\n",
       "      <td>59f8eeec50b733300f347155</td>\n",
       "      <td>2017-07-17T23:16:41+00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cable and phone companies provide access to th...</td>\n",
       "      <td>[{\"name\":\"Sandara Stotenburg\"}]</td>\n",
       "      <td>2621 Fulton Ave; Apt#61</td>\n",
       "      <td>Sacramento</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CA</td>\n",
       "      <td>{\"addresstext\":\"\"}</td>\n",
       "      <td>95821</td>\n",
       "      <td>0</td>\n",
       "      <td>95821.0</td>\n",
       "      <td>Sacramento</td>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               id            date_submission contact_email  \\\n",
       "2999993  59f8eeec50b733300f347152  2017-07-17T23:16:41+00:00           NaN   \n",
       "2999994  59f8eeec50b733300f3482a5  2017-07-17T23:19:39+00:00           NaN   \n",
       "2999995  59f8eeec50b733300f347153  2017-07-17T23:16:41+00:00           NaN   \n",
       "2999996  59f8eeec50b733300f347154  2017-07-17T23:16:41+00:00           NaN   \n",
       "2999997  59f8eeec50b733300f347155  2017-07-17T23:16:41+00:00           NaN   \n",
       "\n",
       "                                                 text_data  \\\n",
       "2999993  Cable and phone companies provide access to th...   \n",
       "2999994  Net Neutrality is not negotiable. It's essenti...   \n",
       "2999995  Cable and phone companies provide access to th...   \n",
       "2999996  Cable and phone companies provide access to th...   \n",
       "2999997  Cable and phone companies provide access to th...   \n",
       "\n",
       "                                  filers           address_line_1        city  \\\n",
       "2999993    [{\"name\":\"Ismael Rodriguez\"}]        794 Sheridan Ave.    Vineland   \n",
       "2999994      [{\"name\":\"Angelica Munoz\"}]                  xxxxxxx     Anaheim   \n",
       "2999995     [{\"name\":\"Richard Krueger\"}]           53 sinclair dr   greenlawn   \n",
       "2999996      [{\"name\":\"Kathi Brinkman\"}]         119 Arcaro drive  Greensboro   \n",
       "2999997  [{\"name\":\"Sandara Stotenburg\"}]  2621 Fulton Ave; Apt#61  Sacramento   \n",
       "\n",
       "         express_comment state        intl_address combined_zip  flagged_zip  \\\n",
       "2999993              NaN    NJ  {\"addresstext\":\"\"}        08361            0   \n",
       "2999994              NaN    CA  {\"addresstext\":\"\"}        92801            0   \n",
       "2999995              NaN    NY  {\"addresstext\":\"\"}        11740            0   \n",
       "2999996              NaN    NC  {\"addresstext\":\"\"}        27455            0   \n",
       "2999997              NaN    CA  {\"addresstext\":\"\"}        95821            0   \n",
       "\n",
       "         Zip_Code_num  Place_Name State_Abbreviation  \n",
       "2999993        8361.0    Vineland                 NJ  \n",
       "2999994       92801.0     Anaheim                 CA  \n",
       "2999995       11740.0   Greenlawn                 NY  \n",
       "2999996       27455.0  Greensboro                 NC  \n",
       "2999997       95821.0  Sacramento                 CA  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.tail()"
   ]
  }
 ],
 "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
}