preprocessing_2_zipcodes.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "9198425d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import uszipcode\n",
    "from uszipcode import SearchEngine\n",
    "import geopy\n",
    "from geopy.geocoders import Nominatim\n",
    "import pandas as pd\n",
    "import os\n",
    "import csv\n",
    "from pathlib import Path\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "43d36549",
   "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>RegionID</th>\n",
       "      <th>RegionName</th>\n",
       "      <th>StateName</th>\n",
       "      <th>SizeRank</th>\n",
       "      <th>2011-01</th>\n",
       "      <th>2011-02</th>\n",
       "      <th>2011-03</th>\n",
       "      <th>2011-04</th>\n",
       "      <th>2011-05</th>\n",
       "      <th>2011-06</th>\n",
       "      <th>...</th>\n",
       "      <th>2019-06</th>\n",
       "      <th>2019-07</th>\n",
       "      <th>2019-08</th>\n",
       "      <th>2019-09</th>\n",
       "      <th>2019-10</th>\n",
       "      <th>2019-11</th>\n",
       "      <th>2019-12</th>\n",
       "      <th>2020-01</th>\n",
       "      <th>2020-02</th>\n",
       "      <th>2020-03</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6181</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>1</td>\n",
       "      <td>462700.0</td>\n",
       "      <td>466200.0</td>\n",
       "      <td>465600.0</td>\n",
       "      <td>469500.0</td>\n",
       "      <td>471300.0</td>\n",
       "      <td>468600.0</td>\n",
       "      <td>...</td>\n",
       "      <td>563200.0</td>\n",
       "      <td>570500.0</td>\n",
       "      <td>572800.0</td>\n",
       "      <td>569900.0</td>\n",
       "      <td>560800.0</td>\n",
       "      <td>571500.0</td>\n",
       "      <td>575100.0</td>\n",
       "      <td>571700.0</td>\n",
       "      <td>568300.0</td>\n",
       "      <td>573600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17426</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>4</td>\n",
       "      <td>188500.0</td>\n",
       "      <td>189400.0</td>\n",
       "      <td>186300.0</td>\n",
       "      <td>177700.0</td>\n",
       "      <td>167900.0</td>\n",
       "      <td>174700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>271500.0</td>\n",
       "      <td>266500.0</td>\n",
       "      <td>264900.0</td>\n",
       "      <td>265000.0</td>\n",
       "      <td>264100.0</td>\n",
       "      <td>264300.0</td>\n",
       "      <td>270000.0</td>\n",
       "      <td>281400.0</td>\n",
       "      <td>302900.0</td>\n",
       "      <td>309200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18959</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>8</td>\n",
       "      <td>115600.0</td>\n",
       "      <td>114000.0</td>\n",
       "      <td>110800.0</td>\n",
       "      <td>109600.0</td>\n",
       "      <td>108200.0</td>\n",
       "      <td>107500.0</td>\n",
       "      <td>...</td>\n",
       "      <td>266000.0</td>\n",
       "      <td>268200.0</td>\n",
       "      <td>268400.0</td>\n",
       "      <td>270400.0</td>\n",
       "      <td>272200.0</td>\n",
       "      <td>274400.0</td>\n",
       "      <td>277500.0</td>\n",
       "      <td>278900.0</td>\n",
       "      <td>283000.0</td>\n",
       "      <td>284700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38128</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>10</td>\n",
       "      <td>191900.0</td>\n",
       "      <td>184500.0</td>\n",
       "      <td>174500.0</td>\n",
       "      <td>152000.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>171400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>300600.0</td>\n",
       "      <td>301100.0</td>\n",
       "      <td>301500.0</td>\n",
       "      <td>310100.0</td>\n",
       "      <td>314600.0</td>\n",
       "      <td>316900.0</td>\n",
       "      <td>307100.0</td>\n",
       "      <td>321500.0</td>\n",
       "      <td>315500.0</td>\n",
       "      <td>321700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10920</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>19</td>\n",
       "      <td>89100.0</td>\n",
       "      <td>86000.0</td>\n",
       "      <td>79800.0</td>\n",
       "      <td>79300.0</td>\n",
       "      <td>78600.0</td>\n",
       "      <td>82300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>152600.0</td>\n",
       "      <td>154800.0</td>\n",
       "      <td>158500.0</td>\n",
       "      <td>157700.0</td>\n",
       "      <td>162900.0</td>\n",
       "      <td>166600.0</td>\n",
       "      <td>172700.0</td>\n",
       "      <td>175300.0</td>\n",
       "      <td>173600.0</td>\n",
       "      <td>177600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1092</th>\n",
       "      <td>48710</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>3704</td>\n",
       "      <td>266300.0</td>\n",
       "      <td>244200.0</td>\n",
       "      <td>217800.0</td>\n",
       "      <td>191200.0</td>\n",
       "      <td>201500.0</td>\n",
       "      <td>208400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>326500.0</td>\n",
       "      <td>317800.0</td>\n",
       "      <td>293100.0</td>\n",
       "      <td>281000.0</td>\n",
       "      <td>279100.0</td>\n",
       "      <td>279400.0</td>\n",
       "      <td>287100.0</td>\n",
       "      <td>305300.0</td>\n",
       "      <td>325600.0</td>\n",
       "      <td>345100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1093</th>\n",
       "      <td>12306</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>3714</td>\n",
       "      <td>272300.0</td>\n",
       "      <td>291500.0</td>\n",
       "      <td>305600.0</td>\n",
       "      <td>342300.0</td>\n",
       "      <td>342600.0</td>\n",
       "      <td>329400.0</td>\n",
       "      <td>...</td>\n",
       "      <td>339800.0</td>\n",
       "      <td>371300.0</td>\n",
       "      <td>358600.0</td>\n",
       "      <td>366800.0</td>\n",
       "      <td>362400.0</td>\n",
       "      <td>396600.0</td>\n",
       "      <td>398200.0</td>\n",
       "      <td>404100.0</td>\n",
       "      <td>397100.0</td>\n",
       "      <td>416200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1094</th>\n",
       "      <td>33433</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>3716</td>\n",
       "      <td>203600.0</td>\n",
       "      <td>175600.0</td>\n",
       "      <td>170200.0</td>\n",
       "      <td>184800.0</td>\n",
       "      <td>198900.0</td>\n",
       "      <td>191800.0</td>\n",
       "      <td>...</td>\n",
       "      <td>206200.0</td>\n",
       "      <td>198200.0</td>\n",
       "      <td>214700.0</td>\n",
       "      <td>211800.0</td>\n",
       "      <td>217500.0</td>\n",
       "      <td>250200.0</td>\n",
       "      <td>270300.0</td>\n",
       "      <td>260200.0</td>\n",
       "      <td>224300.0</td>\n",
       "      <td>216600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1095</th>\n",
       "      <td>32133</td>\n",
       "      <td>Idyllwild</td>\n",
       "      <td>California</td>\n",
       "      <td>3718</td>\n",
       "      <td>182900.0</td>\n",
       "      <td>225000.0</td>\n",
       "      <td>230500.0</td>\n",
       "      <td>240100.0</td>\n",
       "      <td>182500.0</td>\n",
       "      <td>183700.0</td>\n",
       "      <td>...</td>\n",
       "      <td>313500.0</td>\n",
       "      <td>309100.0</td>\n",
       "      <td>322400.0</td>\n",
       "      <td>321300.0</td>\n",
       "      <td>342400.0</td>\n",
       "      <td>330500.0</td>\n",
       "      <td>353800.0</td>\n",
       "      <td>355500.0</td>\n",
       "      <td>389300.0</td>\n",
       "      <td>360100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1096</th>\n",
       "      <td>50339</td>\n",
       "      <td>Sea Isle City</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>3722</td>\n",
       "      <td>500500.0</td>\n",
       "      <td>538200.0</td>\n",
       "      <td>543800.0</td>\n",
       "      <td>533600.0</td>\n",
       "      <td>552300.0</td>\n",
       "      <td>536300.0</td>\n",
       "      <td>...</td>\n",
       "      <td>718400.0</td>\n",
       "      <td>743300.0</td>\n",
       "      <td>762300.0</td>\n",
       "      <td>820300.0</td>\n",
       "      <td>783400.0</td>\n",
       "      <td>789700.0</td>\n",
       "      <td>776600.0</td>\n",
       "      <td>811700.0</td>\n",
       "      <td>815700.0</td>\n",
       "      <td>795900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1097 rows × 115 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RegionID     RegionName     StateName  SizeRank   2011-01   2011-02  \\\n",
       "0         6181       New York      New York         1  462700.0  466200.0   \n",
       "1        17426        Chicago      Illinois         4  188500.0  189400.0   \n",
       "2        18959      Las Vegas        Nevada         8  115600.0  114000.0   \n",
       "3        38128         Dallas         Texas        10  191900.0  184500.0   \n",
       "4        10920       Columbus          Ohio        19   89100.0   86000.0   \n",
       "...        ...            ...           ...       ...       ...       ...   \n",
       "1092     48710         Arnold    California      3704  266300.0  244200.0   \n",
       "1093     12306      Langhorne  Pennsylvania      3714  272300.0  291500.0   \n",
       "1094     33433  Pingree Grove      Illinois      3716  203600.0  175600.0   \n",
       "1095     32133      Idyllwild    California      3718  182900.0  225000.0   \n",
       "1096     50339  Sea Isle City    New Jersey      3722  500500.0  538200.0   \n",
       "\n",
       "       2011-03   2011-04   2011-05   2011-06  ...   2019-06   2019-07  \\\n",
       "0     465600.0  469500.0  471300.0  468600.0  ...  563200.0  570500.0   \n",
       "1     186300.0  177700.0  167900.0  174700.0  ...  271500.0  266500.0   \n",
       "2     110800.0  109600.0  108200.0  107500.0  ...  266000.0  268200.0   \n",
       "3     174500.0  152000.0  162900.0  171400.0  ...  300600.0  301100.0   \n",
       "4      79800.0   79300.0   78600.0   82300.0  ...  152600.0  154800.0   \n",
       "...        ...       ...       ...       ...  ...       ...       ...   \n",
       "1092  217800.0  191200.0  201500.0  208400.0  ...  326500.0  317800.0   \n",
       "1093  305600.0  342300.0  342600.0  329400.0  ...  339800.0  371300.0   \n",
       "1094  170200.0  184800.0  198900.0  191800.0  ...  206200.0  198200.0   \n",
       "1095  230500.0  240100.0  182500.0  183700.0  ...  313500.0  309100.0   \n",
       "1096  543800.0  533600.0  552300.0  536300.0  ...  718400.0  743300.0   \n",
       "\n",
       "       2019-08   2019-09   2019-10   2019-11   2019-12   2020-01   2020-02  \\\n",
       "0     572800.0  569900.0  560800.0  571500.0  575100.0  571700.0  568300.0   \n",
       "1     264900.0  265000.0  264100.0  264300.0  270000.0  281400.0  302900.0   \n",
       "2     268400.0  270400.0  272200.0  274400.0  277500.0  278900.0  283000.0   \n",
       "3     301500.0  310100.0  314600.0  316900.0  307100.0  321500.0  315500.0   \n",
       "4     158500.0  157700.0  162900.0  166600.0  172700.0  175300.0  173600.0   \n",
       "...        ...       ...       ...       ...       ...       ...       ...   \n",
       "1092  293100.0  281000.0  279100.0  279400.0  287100.0  305300.0  325600.0   \n",
       "1093  358600.0  366800.0  362400.0  396600.0  398200.0  404100.0  397100.0   \n",
       "1094  214700.0  211800.0  217500.0  250200.0  270300.0  260200.0  224300.0   \n",
       "1095  322400.0  321300.0  342400.0  330500.0  353800.0  355500.0  389300.0   \n",
       "1096  762300.0  820300.0  783400.0  789700.0  776600.0  811700.0  815700.0   \n",
       "\n",
       "       2020-03  \n",
       "0     573600.0  \n",
       "1     309200.0  \n",
       "2     284700.0  \n",
       "3     321700.0  \n",
       "4     177600.0  \n",
       "...        ...  \n",
       "1092  345100.0  \n",
       "1093  416200.0  \n",
       "1094  216600.0  \n",
       "1095  360100.0  \n",
       "1096  795900.0  \n",
       "\n",
       "[1097 rows x 115 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load reduced data source with city and state names\n",
    "city_file = Path(\"./resources/Sale_Prices_City/Sale_Prices_City_sample.csv\")\n",
    "city_data = pd.read_csv(city_file, low_memory=False)\n",
    "city_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d8c9e467",
   "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>RegionName</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Springfield</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Washington</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Franklin</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Westfield</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bristol</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Louisville</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Medford</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Richmond</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Helena</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Portage</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>66 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     RegionName  0\n",
       "0   Springfield  4\n",
       "1    Washington  3\n",
       "2      Franklin  3\n",
       "3     Westfield  3\n",
       "4       Bristol  3\n",
       "..          ... ..\n",
       "61   Louisville  2\n",
       "62      Medford  2\n",
       "63     Richmond  2\n",
       "64       Helena  2\n",
       "65      Portage  2\n",
       "\n",
       "[66 rows x 2 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check to see if there are duplicate city names\n",
    "city_names_pivot = city_data.pivot_table(columns=['RegionName'], aggfunc='size').sort_values(ascending=False)\n",
    "city_duplicates_df = city_names_pivot.reset_index()\n",
    "city_duplicates_df.head(66)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7070aa8",
   "metadata": {},
   "source": [
    "## CREATE ENGINE TO PAIR ZIP CODES W/ USZIPCODE LIBRARY"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "08d2d3db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 10001 New York NY\n",
      "2 60601 Chicago IL\n",
      "3 89101 Las Vegas NV\n",
      "4 75201 Dallas TX\n",
      "5 43085 Columbus OH\n",
      "6 40202 Louisville KY\n",
      "7 32801 Orlando FL\n",
      "8 80202 Denver CO\n",
      "9 20001 Washington DC\n",
      "10 97201 Portland OR\n",
      "11 37201 Nashville TN\n",
      "12 53202 Milwaukee WI\n",
      "13 68102 Omaha NE\n",
      "14 73102 Oklahoma City OK\n",
      "15 27601 Raleigh NC\n",
      "16 80902 Colorado Springs CO\n",
      "17 55401 Minneapolis MN\n",
      "18 70801 Baton Rouge LA\n",
      "19 80010 Aurora CO\n",
      "20 92801 Anaheim CA\n",
      "21 27395 Greensboro NC\n",
      "22 40502 Lexington KY\n",
      "23 46802 Fort Wayne IN\n",
      "24 55101 Saint Paul MN\n",
      "25 34102 Naples FL\n",
      "26 32301 Tallahassee FL\n",
      "27 89002 Henderson NV\n",
      "28 68502 Lincoln NE\n",
      "29 99201 Spokane WA\n",
      "30 32501 Pensacola FL\n",
      "31 53703 Madison WI\n",
      "32 92602 Irvine CA\n",
      "33 89030 North Las Vegas NV\n",
      "34 22201 Arlington VA\n",
      "35 65802 Springfield MO\n",
      "36 50307 Des Moines IA\n",
      "37 22191 Woodbridge VA\n",
      "38 32601 Gainesville FL\n",
      "39 10701 Yonkers NY\n",
      "40 60502 Aurora IL\n",
      "41 28401 Wilmington NC\n",
      "42 92646 Huntington Beach CA\n",
      "43 97401 Eugene OR\n",
      "44 34952 Port Saint Lucie FL\n",
      "45 93901 Salinas CA\n",
      "46 72201 Little Rock AR\n",
      "47 47708 Evansville IN\n",
      "48 66204 Overland Park KS\n",
      "49 97301 Salem OR\n",
      "50 97003 Beaverton OR\n",
      "51 92025 Escondido CA\n",
      "52 29401 Charleston SC\n",
      "53 37127 Murfreesboro TN\n",
      "54 91701 Rancho Cucamonga CA\n",
      "55 22401 Fredericksburg VA\n",
      "56 76541 Killeen TX\n",
      "57 93550 Palmdale CA\n",
      "58 37040 Clarksville TN\n",
      "59 60540 Naperville IL\n",
      "60 73003 Edmond OK\n",
      "61 23075 Henrico VA\n",
      "62 80722 Atwood CO\n",
      "63 91766 Pomona CA\n",
      "64 48103 Ann Arbor MI\n",
      "65 94085 Sunnyvale CA\n",
      "66 48906 Lansing MI\n",
      "67 27511 Cary NC\n",
      "68 65201 Columbia MO\n",
      "69 75149 Mesquite TX\n",
      "70 70001 Metairie LA\n",
      "71 90501 Torrance CA\n",
      "72 33755 Clearwater FL\n",
      "73 90220 Compton CA\n",
      "74 90301 Inglewood CA\n",
      "75 66061 Olathe KS\n",
      "76 80241 Thornton CO\n",
      "77 80301 Boulder CO\n",
      "78 75006 Carrollton TX\n",
      "79 91360 Thousand Oaks CA\n",
      "80 92392 Victorville CA\n",
      "81 45011 Hamilton OH\n",
      "82 77581 Pearland TX\n",
      "83 62701 Springfield IL\n",
      "84 18101 Allentown PA\n",
      "85 92562 Murrieta CA\n",
      "86 60431 Joliet IL\n",
      "87 73026 Norman OK\n",
      "88 83651 Nampa ID\n",
      "89 80030 Westminster CO\n",
      "90 93611 Clovis CA\n",
      "91 94801 Richmond CA\n",
      "92 80002 Arvada CO\n",
      "93 22901 Charlottesville VA\n",
      "94 35401 Tuscaloosa AL\n",
      "95 91731 El Monte CA\n",
      "96 93001 Ventura CA\n",
      "97 75057 Lewisville TX\n",
      "98 91790 West Covina CA\n",
      "99 60120 Elgin IL\n",
      "100 27834 Greenville NC\n",
      "101 20109 Manassas VA\n",
      "102 97123 Hillsboro OR\n",
      "103 54301 Green Bay WI\n",
      "104 75080 Richardson TX\n",
      "105 19380 West Chester PA\n",
      "106 90650 Norwalk CA\n",
      "107 94509 Antioch CA\n",
      "108 94533 Fairfield CA\n",
      "109 98201 Everett WA\n",
      "110 16501 Erie PA\n",
      "111 33569 Riverview FL\n",
      "112 95687 Vacaville CA\n",
      "113 81501 Grand Junction CO\n",
      "114 92344 Hesperia CA\n",
      "115 94401 San Mateo CA\n",
      "116 37064 Franklin TN\n",
      "117 95340 Merced CA\n",
      "118 29072 Lexington SC\n",
      "119 27514 Chapel Hill NC\n",
      "120 92376 Rialto CA\n",
      "121 21044 Columbia MD\n",
      "122 83642 Meridian ID\n",
      "123 66044 Lawrence KS\n",
      "--------Highlands Ranch, Colorado not found--------\n",
      "125 80631 Greeley CO\n",
      "126 77573 League City TX\n",
      "127 92691 Mission Viejo CA\n",
      "128 94550 Livermore CA\n",
      "129 23701 Portsmouth VA\n",
      "130 90250 Hawthorne CA\n",
      "131 37601 Johnson City TN\n",
      "132 83401 Idaho Falls ID\n",
      "133 28801 Asheville NC\n",
      "134 21401 Annapolis MD\n",
      "135 20164 Sterling VA\n",
      "136 32703 Apopka FL\n",
      "137 97501 Medford OR\n",
      "138 75002 Allen TX\n",
      "139 60544 Plainfield IL\n",
      "140 02740 New Bedford MA\n",
      "141 30075 Roswell GA\n",
      "142 01901 Lynn MA\n",
      "143 37311 Cleveland TN\n",
      "144 23185 Williamsburg VA\n",
      "145 64063 Lees Summit MO\n",
      "146 30263 Newnan GA\n",
      "147 48187 Canton MI\n",
      "148 20170 Herndon VA\n",
      "149 86001 Flagstaff AZ\n",
      "150 91708 Chino CA\n",
      "151 34945 Fort Pierce FL\n",
      "152 02720 Fall River MA\n",
      "153 92660 Newport Beach CA\n",
      "154 60085 Waukegan IL\n",
      "155 78660 Pflugerville TX\n",
      "156 55796 Winton MN\n",
      "157 80501 Longmont CO\n",
      "158 07011 Clifton NJ\n",
      "159 38301 Jackson TN\n",
      "160 99201 Spokane WA\n",
      "161 97030 Gresham OR\n",
      "162 48083 Troy MI\n",
      "163 37660 Kingsport TN\n",
      "164 02169 Quincy MA\n",
      "165 84401 Ogden UT\n",
      "166 22554 Stafford VA\n",
      "167 95336 Manteca CA\n",
      "168 93010 Camarillo CA\n",
      "169 48331 Farmington MI\n",
      "170 92307 Apple Valley CA\n",
      "171 32765 Oviedo FL\n",
      "172 29464 Mount Pleasant SC\n",
      "174 91722 Covina CA\n",
      "175 91784 Upland CA\n",
      "176 32725 Deltona FL\n",
      "177 90745 Carson CA\n",
      "178 17601 Lancaster PA\n",
      "179 55007 Brook Park MN\n",
      "180 94040 Mountain View CA\n",
      "181 27502 Apex NC\n",
      "182 77459 Missouri City TX\n",
      "183 77406 Richmond TX\n",
      "184 99301 Pasco WA\n",
      "185 85122 Casa Grande AZ\n",
      "186 94582 San Ramon CA\n",
      "187 12601 Poughkeepsie NY\n",
      "--------Plymouth, Minnesota not found--------\n",
      "189 18015 Bethlehem PA\n",
      "--------Cheektowaga, New York not found--------\n",
      "191 39042 Brandon MS\n",
      "192 92630 Lake Forest CA\n",
      "193 10801 New Rochelle NY\n",
      "194 34683 Palm Harbor FL\n",
      "195 48033 Southfield MI\n",
      "196 28115 Mooresville NC\n",
      "197 91709 Chino Hills CA\n",
      "198 08723 Brick NJ\n",
      "199 92530 Lake Elsinore CA\n",
      "200 30024 Suwanee GA\n",
      "201 23059 Glen Allen VA\n",
      "202 75022 Flower Mound TX\n",
      "203 48327 Waterford MI\n",
      "204 47201 Columbus IN\n",
      "205 47906 West Lafayette IN\n",
      "206 92886 Yorba Linda CA\n",
      "207 84040 Layton UT\n",
      "208 60440 Bolingbrook IL\n",
      "209 48197 Ypsilanti MI\n",
      "210 34769 Saint Cloud FL\n",
      "211 33410 Palm Beach Gardens FL\n",
      "212 94501 Alameda CA\n",
      "213 34285 Venice FL\n",
      "214 32922 Cocoa FL\n",
      "215 94301 Palo Alto CA\n",
      "216 21801 Salisbury MD\n",
      "217 32570 Milton FL\n",
      "218 97526 Grants Pass OR\n",
      "219 70062 Kenner LA\n",
      "220 55311 Maple Grove MN\n",
      "221 97330 Corvallis OR\n",
      "222 32162 The Villages FL\n",
      "--------Eagan, Minnesota not found--------\n",
      "224 60462 Orland Park IL\n",
      "225 93401 San Luis Obispo CA\n",
      "226 76180 North Richland Hills TX\n",
      "227 96706 Ewa Beach HI\n",
      "228 30214 Fayetteville GA\n",
      "229 90660 Pico Rivera CA\n",
      "230 48640 Midland MI\n",
      "231 37075 Hendersonville TN\n",
      "232 55044 Lakeville MN\n",
      "233 33543 Wesley Chapel FL\n",
      "234 33594 Valrico FL\n",
      "235 92677 Laguna Niguel CA\n",
      "236 91770 Rosemead CA\n",
      "237 54601 La Crosse WI\n",
      "238 44256 Medina OH\n",
      "239 20190 Reston VA\n",
      "240 66203 Shawnee KS\n",
      "241 89701 Carson City NV\n",
      "242 90640 Montebello CA\n",
      "243 56301 Saint Cloud MN\n",
      "244 55344 Eden Prairie MN\n",
      "245 92672 San Clemente CA\n",
      "246 80020 Broomfield CO\n",
      "247 56572 Pelican Rapids MN\n",
      "248 56011 Belle Plaine MN\n",
      "249 21122 Pasadena MD\n",
      "250 32127 Port Orange FL\n",
      "251 90631 La Habra CA\n",
      "252 55306 Burnsville MN\n",
      "253 34637 Land O Lakes FL\n",
      "254 91754 Monterey Park CA\n",
      "255 97321 Albany OR\n",
      "256 50265 West Des Moines IA\n",
      "257 13501 Utica NY\n",
      "258 95020 Gilroy CA\n",
      "259 43130 Lancaster OH\n",
      "260 45069 West Chester OH\n",
      "261 06010 Bristol CT\n",
      "262 37087 Lebanon TN\n",
      "263 91702 Azusa CA\n",
      "264 90247 Gardena CA\n",
      "265 22003 Annandale VA\n",
      "266 60076 Skokie IL\n",
      "267 95014 Cupertino CA\n",
      "268 25401 Martinsburg WV\n",
      "269 28078 Huntersville NC\n",
      "270 48067 Royal Oak MI\n",
      "271 63376 Saint Peters MO\n",
      "272 54901 Oshkosh WI\n",
      "273 30458 Statesboro GA\n",
      "274 33122 Miami FL\n",
      "275 60187 Wheaton IL\n",
      "276 06040 Manchester CT\n",
      "277 48843 Howell MI\n",
      "278 15601 Greensburg PA\n",
      "--------Bartlett, Tennessee not found--------\n",
      "280 07083 Union NJ\n",
      "281 70053 Gretna LA\n",
      "282 08854 Piscataway NJ\n",
      "283 60515 Downers Grove IL\n",
      "284 92708 Fountain Valley CA\n",
      "285 46526 Goshen IN\n",
      "286 44276 Sterling OH\n",
      "287 60402 Berwyn IL\n",
      "288 92324 Colton CA\n",
      "289 46307 Crown Point IN\n",
      "290 73852 Mooreland OK\n",
      "291 60025 Glenview IL\n",
      "292 01013 Chicopee MA\n",
      "293 85138 Maricopa AZ\n",
      "294 08527 Jackson NJ\n",
      "295 30052 Loganville GA\n",
      "296 59601 Helena MT\n",
      "297 07470 Wayne NJ\n",
      "298 43701 Zanesville OH\n",
      "299 02188 Weymouth MA\n",
      "300 20151 Chantilly VA\n",
      "301 22101 Mc Lean VA\n",
      "302 32547 Fort Walton Beach FL\n",
      "303 37813 Morristown TN\n",
      "304 30240 Lagrange GA\n",
      "305 76039 Euless TX\n",
      "306 99352 Richland WA\n",
      "307 48374 Novi MI\n",
      "308 91740 Glendora CA\n",
      "309 94506 Danville CA\n",
      "310 34737 Howey In The Hills FL\n",
      "311 34134 Bonita Springs FL\n",
      "312 32750 Longwood FL\n",
      "313 93215 Delano CA\n",
      "314 94546 Castro Valley CA\n",
      "315 14075 Hamburg NY\n",
      "316 59901 Kalispell MT\n",
      "317 91745 Hacienda Heights CA\n",
      "318 85326 Buckeye AZ\n",
      "319 66215 Lenexa KS\n",
      "320 23832 Chesterfield VA\n",
      "321 07030 Hoboken NJ\n",
      "322 75115 Desoto TX\n",
      "323 92399 Yucaipa CA\n",
      "324 70433 Covington LA\n",
      "325 38555 Crossville TN\n",
      "326 37167 Smyrna TN\n",
      "327 34711 Clermont FL\n",
      "328 61761 Normal IL\n",
      "329 60148 Lombard IL\n",
      "--------Kentwood, Michigan not found--------\n",
      "331 19054 Levittown PA\n",
      "332 30019 Dacula GA\n",
      "333 60477 Tinley Park IL\n",
      "334 50021 Ankeny IA\n",
      "335 06497 Stratford CT\n",
      "336 92870 Placentia CA\n",
      "337 95023 Hollister CA\n",
      "338 71291 West Monroe LA\n",
      "339 70072 Marrero LA\n",
      "340 27529 Garner NC\n",
      "341 55329 Eden Valley MN\n",
      "342 06108 East Hartford CT\n",
      "343 80022 Commerce City CO\n",
      "344 96734 Kailua HI\n",
      "345 20814 Bethesda MD\n",
      "346 75165 Waxahachie TX\n",
      "347 92262 Palm Springs CA\n",
      "348 75068 Little Elm TX\n",
      "349 90638 La Mirada CA\n",
      "350 90630 Cypress CA\n",
      "351 37122 Mount Juliet TN\n",
      "352 90703 Cerritos CA\n",
      "353 95658 Newcastle CA\n",
      "354 92656 Aliso Viejo CA\n",
      "--------Milwaukie, Oregon not found--------\n",
      "356 75104 Cedar Hill TX\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "357 60169 Hoffman Estates IL\n",
      "358 96744 Kaneohe HI\n",
      "359 94568 Dublin CA\n",
      "360 97470 Roseburg OR\n",
      "361 74017 Claremore OK\n",
      "362 47025 Lawrenceburg IN\n",
      "363 49002 Portage MI\n",
      "--------Murray, Utah not found--------\n",
      "365 80120 Littleton CO\n",
      "366 27284 Kernersville NC\n",
      "367 75088 Rowlett TX\n",
      "368 70401 Hammond LA\n",
      "369 55007 Brook Park MN\n",
      "370 54982 Wautoma WI\n",
      "371 07003 Bloomfield NJ\n",
      "372 38017 Collierville TN\n",
      "373 48066 Roseville MI\n",
      "374 14850 Ithaca NY\n",
      "375 55345 Minnetonka MN\n",
      "376 60126 Elmhurst IL\n",
      "--------Dunwoody, Georgia not found--------\n",
      "378 48114 Brighton MI\n",
      "379 31313 Hinesville GA\n",
      "380 44060 Mentor OH\n",
      "381 60115 Dekalb IL\n",
      "382 08816 East Brunswick NJ\n",
      "383 75098 Wylie TX\n",
      "384 96707 Kapolei HI\n",
      "385 30809 Evans GA\n",
      "386 96720 Hilo HI\n",
      "--------Evesham Township, New Jersey not found--------\n",
      "388 77449 Katy TX\n",
      "389 27526 Fuquay Varina NC\n",
      "390 23690 Yorktown VA\n",
      "391 55379 Shakopee MN\n",
      "392 14586 West Henrietta NY\n",
      "393 08807 Bridgewater NJ\n",
      "394 45613 Beaver OH\n",
      "395 27360 Thomasville NC\n",
      "396 70448 Mandeville LA\n",
      "397 61265 Moline IL\n",
      "398 02474 Arlington MA\n",
      "399 40324 Georgetown KY\n",
      "400 02630 Barnstable MA\n",
      "401 11772 Patchogue NY\n",
      "402 91748 Rowland Heights CA\n",
      "403 86314 Prescott Valley AZ\n",
      "404 45014 Fairfield OH\n",
      "405 22015 Burke VA\n",
      "406 02703 Attleboro MA\n",
      "407 94560 Newark CA\n",
      "408 40356 Nicholasville KY\n",
      "409 11757 Lindenhurst NY\n",
      "410 34737 Howey In The Hills FL\n",
      "411 01201 Pittsfield MA\n",
      "412 28560 New Bern NC\n",
      "413 91016 Monrovia CA\n",
      "414 70769 Prairieville LA\n",
      "415 37027 Brentwood TN\n",
      "416 03301 Concord NH\n",
      "417 63005 Chesterfield MO\n",
      "418 97034 Lake Oswego OR\n",
      "419 11801 Hicksville NY\n",
      "420 32746 Lake Mary FL\n",
      "421 28376 Raeford NC\n",
      "422 32955 Rockledge FL\n",
      "423 60188 Carol Stream IL\n",
      "424 76244 Keller TX\n",
      "425 07032 Kearny NJ\n",
      "426 60012 Crystal Lake IL\n",
      "427 08054 Mount Laurel NJ\n",
      "428 37743 Greeneville TN\n",
      "429 63026 Fenton MO\n",
      "430 07036 Linden NJ\n",
      "431 01085 Westfield MA\n",
      "432 50322 Urbandale IA\n",
      "433 13088 Liverpool NY\n",
      "434 48167 Northville MI\n",
      "435 08902 North Brunswick NJ\n",
      "436 60101 Addison IL\n",
      "437 70737 Gonzales LA\n",
      "438 01453 Leominster MA\n",
      "439 07416 Franklin NJ\n",
      "440 93446 Paso Robles CA\n",
      "441 63011 Ballwin MO\n",
      "442 91744 La Puente CA\n",
      "443 07746 Marlboro NJ\n",
      "444 96701 Aiea HI\n",
      "445 07666 Teaneck NJ\n",
      "446 11003 Elmont NY\n",
      "447 53005 Brookfield WI\n",
      "448 85351 Sun City AZ\n",
      "449 60107 Streamwood IL\n",
      "450 90275 Rancho Palos Verdes CA\n",
      "451 60060 Mundelein IL\n",
      "452 53146 New Berlin WI\n",
      "453 20735 Clinton MD\n",
      "454 32707 Casselberry FL\n",
      "455 38138 Germantown TN\n",
      "456 60103 Bartlett IL\n",
      "457 84660 Spanish Fork UT\n",
      "458 37620 Bristol TN\n",
      "459 84065 Riverton UT\n",
      "460 11704 West Babylon NY\n",
      "461 76513 Belton TX\n",
      "462 01801 Woburn MA\n",
      "463 80601 Brighton CO\n",
      "464 94561 Oakley CA\n",
      "465 68046 Papillion NE\n",
      "466 21236 Nottingham MD\n",
      "467 63385 Wentzville MO\n",
      "468 12866 Saratoga Springs NY\n",
      "469 30736 Ringgold GA\n",
      "470 52302 Marion IA\n",
      "471 45342 Miamisburg OH\n",
      "472 92253 La Quinta CA\n",
      "473 91763 Montclair CA\n",
      "474 94044 Pacifica CA\n",
      "475 24153 Salem VA\n",
      "476 60068 Park Ridge IL\n",
      "477 63010 Arnold MO\n",
      "478 48170 Plymouth MI\n",
      "479 34761 Ocoee FL\n",
      "480 43551 Perrysburg OH\n",
      "481 76053 Hurst TX\n",
      "482 80110 Englewood CO\n",
      "--------Northglenn, Colorado not found--------\n",
      "484 19709 Middletown DE\n",
      "485 53531 Deerfield WI\n",
      "486 35473 Northport AL\n",
      "487 44814 Berlin Heights OH\n",
      "488 02184 Braintree MA\n",
      "489 20747 District Heights MD\n",
      "490 97128 Mcminnville OR\n",
      "491 84067 Roy UT\n",
      "492 06611 Trumbull CT\n",
      "493 91711 Claremont CA\n",
      "494 91001 Altadena CA\n",
      "495 11554 East Meadow NY\n",
      "496 50613 Cedar Falls IA\n",
      "497 21784 Sykesville MD\n",
      "498 07676 Township Of Washington NJ\n",
      "499 30813 Grovetown GA\n",
      "500 53051 Menomonee Falls WI\n",
      "501 92675 San Juan Capistrano CA\n",
      "502 55355 Litchfield MN\n",
      "503 46123 Avon IN\n",
      "504 92595 Wildomar CA\n",
      "505 64780 Rockville MO\n",
      "506 18301 East Stroudsburg PA\n",
      "--------Boardman, Ohio not found--------\n",
      "508 93060 Santa Paula CA\n",
      "509 97756 Redmond OR\n",
      "510 91780 Temple City CA\n",
      "511 56284 Renville MN\n",
      "512 40391 Winchester KY\n",
      "513 48382 Commerce Township MI\n",
      "514 37086 La Vergne TN\n",
      "515 45103 Batavia OH\n",
      "516 45373 Troy OH\n",
      "517 01810 Andover MA\n",
      "518 55016 Cottage Grove MN\n",
      "519 37160 Shelbyville TN\n",
      "520 21136 Reisterstown MD\n",
      "521 43147 Pickerington OH\n",
      "522 95030 Los Gatos CA\n",
      "523 92311 Barstow CA\n",
      "524 53154 Oak Creek WI\n",
      "525 22079 Lorton VA\n",
      "526 21740 Hagerstown MD\n",
      "527 45814 Arlington OH\n",
      "528 94523 Pleasant Hill CA\n",
      "529 43065 Powell OH\n",
      "530 46368 Portage IN\n",
      "531 95901 Marysville CA\n",
      "532 39110 Madison MS\n",
      "533 55076 Inver Grove Heights MN\n",
      "534 02368 Randolph MA\n",
      "535 90266 Manhattan Beach CA\n",
      "536 02472 Watertown MA\n",
      "537 21144 Severn MD\n",
      "538 66206 Leawood KS\n",
      "539 30542 Flowery Branch GA\n",
      "540 92301 Adelanto CA\n",
      "541 34688 Tarpon Springs FL\n",
      "542 81301 Durango CO\n",
      "543 22551 Spotsylvania VA\n",
      "544 93561 Tehachapi CA\n",
      "545 81401 Montrose CO\n",
      "546 02420 Lexington MA\n",
      "547 20155 Gainesville VA\n",
      "548 43560 Sylvania OH\n",
      "--------Ken Caryl, Colorado not found--------\n",
      "550 02038 Franklin MA\n",
      "--------Cottonwood Heights, Utah not found--------\n",
      "552 48178 South Lyon MI\n",
      "553 13440 Rome NY\n",
      "554 21113 Odenton MD\n",
      "555 60174 Saint Charles IL\n",
      "556 48021 Eastpointe MI\n",
      "557 29063 Irmo SC\n",
      "558 21133 Randallstown MD\n",
      "559 44039 North Ridgeville OH\n",
      "560 21075 Elkridge MD\n",
      "561 11510 Baldwin NY\n",
      "562 28104 Matthews NC\n",
      "563 72120 Sherwood AR\n",
      "564 55372 Prior Lake MN\n",
      "565 55318 Chaska MN\n",
      "566 53090 West Bend WI\n",
      "567 93422 Atascadero CA\n",
      "568 15102 Bethel Park PA\n",
      "569 07753 Neptune NJ\n",
      "570 98467 University Place WA\n",
      "571 95070 Saratoga CA\n",
      "572 39208 Pearl MS\n",
      "573 28607 Boone NC\n",
      "574 55304 Andover MN\n",
      "575 28348 Hope Mills NC\n",
      "576 16652 Huntingdon PA\n",
      "577 77536 Deer Park TX\n",
      "578 44070 North Olmsted OH\n",
      "579 60714 Niles IL\n",
      "580 22980 Waynesboro VA\n",
      "--------Security-Widefield, Colorado not found--------\n",
      "583 27302 Mebane NC\n",
      "584 35007 Alabaster AL\n",
      "585 35593 Vina AL\n",
      "586 94010 Burlingame CA\n",
      "587 21234 Parkville MD\n",
      "588 14120 North Tonawanda NY\n",
      "589 46074 Westfield IN\n",
      "590 48383 White Lake MI\n",
      "591 03820 Dover NH\n",
      "592 78108 Cibolo TX\n",
      "593 34748 Leesburg FL\n",
      "594 93940 Monterey CA\n",
      "595 21009 Abingdon MD\n",
      "596 22630 Front Royal VA\n",
      "597 07090 Westfield NJ\n",
      "598 72015 Benton AR\n",
      "599 44281 Wadsworth OH\n",
      "600 01845 North Andover MA\n",
      "601 80033 Wheat Ridge CO\n",
      "602 20772 Upper Marlboro MD\n",
      "603 01876 Tewksbury MA\n",
      "604 39564 Ocean Springs MS\n",
      "--------Des Moines, Washington not found--------\n",
      "606 02492 Needham MA\n",
      "607 34450 Inverness FL\n",
      "608 78640 Kyle TX\n",
      "609 20186 Warrenton VA\n",
      "610 19026 Drexel Hill PA\n",
      "611 97068 West Linn OR\n",
      "612 93662 Selma CA\n",
      "613 49464 Zeeland MI\n",
      "614 27012 Clemmons NC\n",
      "615 65721 Ozark MO\n",
      "616 94070 San Carlos CA\n",
      "617 13039 Cicero NY\n",
      "618 76092 Southlake TX\n",
      "619 64068 Liberty MO\n",
      "620 20740 College Park MD\n",
      "621 70068 La Place LA\n",
      "622 49028 Bronson MI\n",
      "623 46530 Granger IN\n",
      "624 56401 Brainerd MN\n",
      "625 97838 Hermiston OR\n",
      "626 65714 Nixa MO\n",
      "627 16066 Cranberry Township PA\n",
      "628 48195 Southgate MI\n",
      "629 40065 Shelbyville KY\n",
      "630 11720 Centereach NY\n",
      "631 93420 Arroyo Grande CA\n",
      "632 60048 Libertyville IL\n",
      "633 06066 Vernon Rockville CT\n",
      "634 02919 Johnston RI\n",
      "635 34690 Holiday FL\n",
      "636 37659 Jonesborough TN\n",
      "637 97140 Sherwood OR\n",
      "638 96150 South Lake Tahoe CA\n",
      "639 60134 Geneva IL\n",
      "640 06512 East Haven CT\n",
      "641 64659 Meadville MO\n",
      "642 85340 Litchfield Park AZ\n",
      "643 97132 Newberg OR\n",
      "644 49341 Rockford MI\n",
      "645 68701 Norfolk NE\n",
      "646 84074 Tooele UT\n",
      "647 20705 Beltsville MD\n",
      "648 92860 Norco CA\n",
      "649 21771 Mount Airy MD\n",
      "650 97086 Happy Valley OR\n",
      "651 07110 Nutley NJ\n",
      "652 01757 Milford MA\n",
      "653 56045 Hollandale MN\n",
      "654 34990 Palm City FL\n",
      "655 48867 Owosso MI\n",
      "656 01060 Northampton MA\n",
      "657 95961 Olivehurst CA\n",
      "658 02072 Stoughton MA\n",
      "659 97502 Central Point OR\n",
      "660 23834 Colonial Heights VA\n",
      "661 76548 Harker Heights TX\n",
      "662 32533 Cantonment FL\n",
      "663 15146 Monroeville PA\n",
      "664 30746 Sugar Valley GA\n",
      "665 96732 Kahului HI\n",
      "666 90680 Stanton CA\n",
      "667 40004 Bardstown KY\n",
      "668 80817 Fountain CO\n",
      "669 98370 Poulsbo WA\n",
      "670 02186 Milton MA\n",
      "671 48101 Allen Park MI\n",
      "672 55731 Ely MN\n",
      "673 01923 Danvers MA\n",
      "674 94585 Suisun City CA\n",
      "675 02176 Melrose MA\n",
      "676 28785 Waynesville NC\n",
      "677 92694 Ladera Ranch CA\n",
      "678 27540 Holly Springs NC\n",
      "679 13104 Manlius NY\n",
      "--------Fitchburg, Wisconsin not found--------\n",
      "681 21146 Severna Park MD\n",
      "682 98382 Sequim WA\n",
      "683 11763 Medford NY\n",
      "684 37172 Springfield TN\n",
      "685 07652 Paramus NJ\n",
      "686 84044 Magna UT\n",
      "687 60156 Lake In The Hills IL\n",
      "688 64093 Warrensburg MO\n",
      "689 97062 Tualatin OR\n",
      "690 37055 Dickson TN\n",
      "691 97071 Woodburn OR\n",
      "692 07430 Mahwah NJ\n",
      "693 55024 Farmington MN\n",
      "694 36535 Foley AL\n",
      "695 60142 Huntley IL\n",
      "696 77532 Crosby TX\n",
      "697 49428 Jenison MI\n",
      "698 97116 Forest Grove OR\n",
      "699 01880 Wakefield MA\n",
      "700 64443 Easton MO\n",
      "701 32578 Niceville FL\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------Shoreview, Minnesota not found--------\n",
      "703 28734 Franklin NC\n",
      "704 22025 Dumfries VA\n",
      "705 43537 Maumee OH\n",
      "706 32132 Edgewater FL\n",
      "707 60091 Wilmette IL\n",
      "708 23139 Powhatan VA\n",
      "709 63755 Jackson MO\n",
      "710 27560 Morrisville NC\n",
      "711 35125 Pell City AL\n",
      "712 28031 Cornelius NC\n",
      "713 32789 Winter Park FL\n",
      "715 91945 Lemon Grove CA\n",
      "716 07480 West Milford NJ\n",
      "717 21627 Crocheron MD\n",
      "718 81212 Canon City CO\n",
      "719 94510 Benicia CA\n",
      "720 11784 Selden NY\n",
      "721 03054 Merrimack NH\n",
      "722 28658 Newton NC\n",
      "723 47933 Crawfordsville IN\n",
      "724 80401 Golden CO\n",
      "725 35752 Hollywood AL\n",
      "726 07450 Ridgewood NJ\n",
      "727 37355 Manchester TN\n",
      "728 86326 Cottonwood AZ\n",
      "729 80439 Evergreen CO\n",
      "730 37771 Lenoir City TN\n",
      "731 54956 Neenah WI\n",
      "732 84054 North Salt Lake UT\n",
      "733 24551 Forest VA\n",
      "734 34491 Summerfield FL\n",
      "735 97520 Ashland OR\n",
      "736 70791 Zachary LA\n",
      "737 32566 Navarre FL\n",
      "738 43004 Blacklick OH\n",
      "739 48864 Okemos MI\n",
      "740 19454 North Wales PA\n",
      "741 40403 Berea KY\n",
      "742 02081 Walpole MA\n",
      "743 03051 Hudson NH\n",
      "744 53108 Caledonia WI\n",
      "745 24557 Gretna VA\n",
      "--------Columbine, Colorado not found--------\n",
      "747 54650 Onalaska WI\n",
      "748 60510 Batavia IL\n",
      "749 48380 Milford MI\n",
      "750 17201 Chambersburg PA\n",
      "751 31324 Richmond Hill GA\n",
      "752 20855 Derwood MD\n",
      "753 97420 Coos Bay OR\n",
      "754 48094 Washington MI\n",
      "755 98040 Mercer Island WA\n",
      "756 49459 Walkerville MI\n",
      "757 94530 El Cerrito CA\n",
      "--------Carney, Maryland not found--------\n",
      "759 19064 Springfield PA\n",
      "760 32043 Green Cove Springs FL\n",
      "--------Vinings, Georgia not found--------\n",
      "762 84005 Eagle Mountain UT\n",
      "763 92118 Coronado CA\n",
      "764 63052 Imperial MO\n",
      "765 60053 Morton Grove IL\n",
      "766 21601 Easton MD\n",
      "767 61571 Washington IL\n",
      "768 36526 Daphne AL\n",
      "769 37388 Tullahoma TN\n",
      "770 60439 Lemont IL\n",
      "771 30528 Cleveland GA\n",
      "772 17522 Ephrata PA\n",
      "773 20659 Mechanicsville MD\n",
      "774 60050 Mchenry IL\n",
      "775 28572 Pink Hill NC\n",
      "776 15090 Wexford PA\n",
      "777 37865 Seymour TN\n",
      "778 55309 Big Lake MN\n",
      "779 20169 Haymarket VA\n",
      "780 92780 Tustin CA\n",
      "781 37148 Portland TN\n",
      "782 21114 Crofton MD\n",
      "783 08055 Medford NJ\n",
      "784 91302 Calabasas CA\n",
      "785 55316 Champlin MN\n",
      "786 93041 Port Hueneme CA\n",
      "787 55068 Rosemount MN\n",
      "788 95620 Dixon CA\n",
      "789 41005 Burlington KY\n",
      "790 91384 Castaic CA\n",
      "791 20832 Olney MD\n",
      "792 73064 Mustang OK\n",
      "793 32763 Orange City FL\n",
      "--------Anthem, Arizona not found--------\n",
      "795 60178 Sycamore IL\n",
      "796 46158 Mooresville IN\n",
      "797 60441 Lockport IL\n",
      "798 55317 Chanhassen MN\n",
      "799 04106 South Portland ME\n",
      "800 89410 Gardnerville NV\n",
      "802 02021 Canton MA\n",
      "803 13630 De Kalb Junction NY\n",
      "804 80132 Monument CO\n",
      "805 23061 Gloucester VA\n",
      "806 49315 Byron Center MI\n",
      "807 60561 Darien IL\n",
      "808 11530 Garden City NY\n",
      "809 40031 La Grange KY\n",
      "810 77586 Seabrook TX\n",
      "811 06437 Guilford CT\n",
      "812 44138 Olmsted Falls OH\n",
      "813 31558 Saint Marys GA\n",
      "814 44236 Hudson OH\n",
      "815 29045 Elgin SC\n",
      "816 56073 New Ulm MN\n",
      "817 64012 Belton MO\n",
      "818 98028 Kenmore WA\n",
      "819 29316 Boiling Springs SC\n",
      "820 63043 Maryland Heights MO\n",
      "821 92082 Valley Center CA\n",
      "823 60181 Villa Park IL\n",
      "824 48176 Saline MI\n",
      "825 29016 Blythewood SC\n",
      "826 98671 Washougal WA\n",
      "827 08869 Raritan NJ\n",
      "828 97070 Wilsonville OR\n",
      "829 92371 Phelan CA\n",
      "830 90745 Carson CA\n",
      "831 29142 Santee SC\n",
      "832 72113 Maumelle AR\n",
      "833 75189 Royse City TX\n",
      "834 37934 Farragut TN\n",
      "835 91008 Duarte CA\n",
      "836 92354 Loma Linda CA\n",
      "837 28532 Havelock NC\n",
      "838 94547 Hercules CA\n",
      "839 08091 West Berlin NJ\n",
      "840 03110 Bedford NH\n",
      "841 74037 Jenks OK\n",
      "842 03801 Portsmouth NH\n",
      "843 25414 Charles Town WV\n",
      "844 33573 Sun City Center FL\n",
      "845 84306 Collinston UT\n",
      "846 84075 Syracuse UT\n",
      "--------Mountain Brook, Alabama not found--------\n",
      "848 83634 Kuna ID\n",
      "849 60131 Franklin Park IL\n",
      "851 21012 Arnold MD\n",
      "852 43110 Canal Winchester OH\n",
      "853 55329 Eden Valley MN\n",
      "854 21037 Edgewater MD\n",
      "855 47546 Jasper IN\n",
      "856 33852 Lake Placid FL\n",
      "857 20646 La Plata MD\n",
      "858 56089 Twin Lakes MN\n",
      "859 37178 Tennessee Ridge TN\n",
      "860 55075 South Saint Paul MN\n",
      "861 07871 Sparta NJ\n",
      "862 80026 Lafayette CO\n",
      "864 33040 Key West FL\n",
      "865 48820 Dewitt MI\n",
      "866 48169 Pinckney MI\n",
      "867 70785 Walker LA\n",
      "868 30512 Blairsville GA\n",
      "869 21811 Berlin MD\n",
      "870 41091 Union KY\n",
      "871 44137 Maple Heights OH\n",
      "872 06840 New Canaan CT\n",
      "873 40014 Crestwood KY\n",
      "--------Corinth, Texas not found--------\n",
      "875 75048 Sachse TX\n",
      "876 35124 Pelham AL\n",
      "877 07730 Hazlet NJ\n",
      "878 49855 Marquette MI\n",
      "879 98043 Mountlake Terrace WA\n",
      "880 93023 Ojai CA\n",
      "881 78148 Universal City TX\n",
      "882 53172 South Milwaukee WI\n",
      "883 31326 Rincon GA\n",
      "884 65606 Alton MO\n",
      "885 14806 Andover NY\n",
      "886 46706 Auburn IN\n",
      "887 41018 Erlanger KY\n",
      "888 20706 Lanham MD\n",
      "889 31005 Bonaire GA\n",
      "890 02536 East Falmouth MA\n",
      "891 07960 Morristown NJ\n",
      "892 27591 Wendell NC\n",
      "893 87544 Los Alamos NM\n",
      "894 17225 Greencastle PA\n",
      "895 93444 Nipomo CA\n",
      "896 43616 Oregon OH\n",
      "897 50125 Indianola IA\n",
      "898 91214 La Crescenta CA\n",
      "899 80027 Louisville CO\n",
      "900 08028 Glassboro NJ\n",
      "901 60513 Brookfield IL\n",
      "902 44116 Rocky River OH\n",
      "903 60805 Evergreen Park IL\n",
      "904 22124 Oakton VA\n",
      "905 30277 Sharpsburg GA\n",
      "906 65738 Republic MO\n",
      "907 17013 Carlisle PA\n",
      "908 32778 Tavares FL\n",
      "909 44137 Maple Heights OH\n",
      "910 44142 Brookpark OH\n",
      "911 94564 Pinole CA\n",
      "912 44017 Berea OH\n",
      "913 76437 Cisco TX\n",
      "914 73008 Bethany OK\n",
      "915 31548 Kingsland GA\n",
      "916 04330 Augusta ME\n",
      "917 17070 New Cumberland PA\n",
      "918 31008 Byron GA\n",
      "919 19958 Lewes DE\n",
      "920 04072 Saco ME\n",
      "921 48433 Flushing MI\n",
      "922 20657 Lusby MD\n",
      "923 94563 Orinda CA\n",
      "--------Sherrelwood, Colorado not found--------\n",
      "925 61611 East Peoria IL\n",
      "926 22963 Palmyra VA\n",
      "927 50009 Altoona IA\n",
      "928 17866 Coal Township PA\n",
      "929 21050 Forest Hill MD\n",
      "930 80516 Erie CO\n",
      "931 78635 Hye TX\n",
      "932 07067 Colonia NJ\n",
      "933 22066 Great Falls VA\n",
      "934 74021 Collinsville OK\n",
      "935 46774 New Haven IN\n",
      "936 12061 East Greenbush NY\n",
      "--------Tinton Falls, New Jersey not found--------\n",
      "938 50263 Waukee IA\n",
      "939 80831 Peyton CO\n",
      "940 01950 Newburyport MA\n",
      "--------Bon Air, Virginia not found--------\n",
      "943 24281 Rose Hill VA\n",
      "944 32561 Gulf Breeze FL\n",
      "945 30677 Watkinsville GA\n",
      "946 18847 Susquehanna PA\n",
      "947 37862 Sevierville TN\n",
      "948 63548 Lancaster MO\n",
      "949 46122 Danville IN\n",
      "950 94507 Alamo CA\n",
      "951 20895 Kensington MD\n",
      "952 19014 Aston PA\n",
      "953 84401 Ogden UT\n",
      "954 97103 Astoria OR\n",
      "955 16001 Butler PA\n",
      "956 50131 Johnston IA\n",
      "957 91902 Bonita CA\n",
      "958 49331 Lowell MI\n",
      "959 11733 East Setauket NY\n",
      "960 35080 Helena AL\n",
      "--------Delran Township, New Jersey not found--------\n",
      "962 41048 Hebron KY\n",
      "963 30548 Hoschton GA\n",
      "964 33534 Gibsonton FL\n",
      "965 27592 Willow Spring NC\n",
      "968 84045 Saratoga Springs UT\n",
      "969 02351 Abington MA\n",
      "970 46140 Greenfield IN\n",
      "--------Texas, Michigan not found--------\n",
      "972 48356 Highland MI\n",
      "974 38016 Cordova TN\n",
      "975 63367 Lake Saint Louis MO\n",
      "976 29585 Pawleys Island SC\n",
      "977 32937 Satellite Beach FL\n",
      "978 08012 Blackwood NJ\n",
      "979 84651 Payson UT\n",
      "980 44470 Southington OH\n",
      "981 80550 Windsor CO\n",
      "982 44720 North Canton OH\n",
      "983 34275 Nokomis FL\n",
      "984 96022 Cottonwood CA\n",
      "985 77619 Groves TX\n",
      "986 93950 Pacific Grove CA\n",
      "987 81521 Fruita CO\n",
      "989 32541 Destin FL\n",
      "990 84401 Ogden UT\n",
      "991 85087 New River AZ\n",
      "992 55918 Brownsdale MN\n",
      "993 85253 Paradise Valley AZ\n",
      "994 37341 Harrison TN\n",
      "995 20105 Aldie VA\n",
      "996 22508 Locust Grove VA\n",
      "997 27596 Youngsville NC\n",
      "998 02806 Barrington RI\n",
      "--------Lyndhurst, Ohio not found--------\n",
      "1000 29369 Moore SC\n",
      "1001 19425 Chester Springs PA\n",
      "--------Middle Valley, Tennessee not found--------\n",
      "1003 94517 Clayton CA\n",
      "1004 48118 Chelsea MI\n",
      "1005 19320 Coatesville PA\n",
      "1006 43054 New Albany OH\n",
      "1007 76036 Crowley TX\n",
      "1008 37150 Red Boiling Springs TN\n",
      "1009 80863 Woodland Park CO\n",
      "1010 70447 Madisonville LA\n",
      "1011 33801 Lakeland FL\n",
      "1012 55101 Saint Paul MN\n",
      "1013 48017 Clawson MI\n",
      "--------Lakeside, Virginia not found--------\n",
      "1015 37073 Greenbrier TN\n",
      "1016 21903 Perryville MD\n",
      "1017 98065 Snoqualmie WA\n",
      "1018 92075 Solana Beach CA\n",
      "1019 92314 Big Bear City CA\n",
      "1020 97355 Lebanon OR\n",
      "1021 28374 Pinehurst NC\n",
      "1022 95451 Kelseyville CA\n",
      "--------Crestwood, Missouri not found--------\n",
      "1024 35071 Gardendale AL\n",
      "1025 49684 Traverse City MI\n",
      "--------Country Walk, Florida not found--------\n",
      "1027 19335 Downingtown PA\n",
      "1028 19428 Conshohocken PA\n",
      "1030 30620 Bethlehem GA\n",
      "1031 64089 Smithville MO\n",
      "1032 33455 Hobe Sound FL\n",
      "1033 96019 Shasta Lake CA\n",
      "1034 78873 Leakey TX\n",
      "1035 07840 Hackettstown NJ\n",
      "1036 85118 Gold Canyon AZ\n",
      "1037 89423 Minden NV\n",
      "1038 33480 Palm Beach FL\n",
      "1039 64029 Grain Valley MO\n",
      "1040 08096 Woodbury NJ\n",
      "1041 07950 Morris Plains NJ\n",
      "1042 37377 Signal Mountain TN\n",
      "1043 03818 Conway NH\n",
      "1044 72719 Centerton AR\n",
      "1045 38060 Oakland TN\n",
      "1046 19007 Bristol PA\n",
      "1048 07928 Chatham NJ\n",
      "1049 17050 Mechanicsburg PA\n",
      "1050 32951 Melbourne Beach FL\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1051 18951 Quakertown PA\n",
      "1052 28613 Conover NC\n",
      "1053 39525 Diamondhead MS\n",
      "1054 08879 South Amboy NJ\n",
      "1055 19067 Morrisville PA\n",
      "1056 32569 Mary Esther FL\n",
      "1057 80549 Wellington CO\n",
      "1058 33947 Rotonda West FL\n",
      "1059 07921 Bedminster NJ\n",
      "1060 93546 Mammoth Lakes CA\n",
      "1061 55362 Monticello MN\n",
      "1062 08203 Brigantine NJ\n",
      "1063 17011 Camp Hill PA\n",
      "1064 89067 Coyote Springs NV\n",
      "--------Williamsburg, Florida not found--------\n",
      "1067 33931 Fort Myers Beach FL\n",
      "1068 17356 Red Lion PA\n",
      "1069 80421 Bailey CO\n",
      "1070 30549 Jefferson GA\n",
      "1071 80517 Estes Park CO\n",
      "1072 19053 Feasterville Trevose PA\n",
      "1073 75409 Anna TX\n",
      "1074 19002 Ambler PA\n",
      "1075 80534 Johnstown CO\n",
      "1076 18055 Hellertown PA\n",
      "1077 32003 Fleming Island FL\n",
      "1078 15108 Coraopolis PA\n",
      "--------Timber Pines, Florida not found--------\n",
      "1081 19426 Collegeville PA\n",
      "1082 15017 Bridgeville PA\n",
      "1083 33957 Sanibel FL\n",
      "1084 95467 Hidden Valley Lake CA\n",
      "1085 19468 Royersford PA\n",
      "1086 08260 Wildwood NJ\n",
      "1087 32550 Miramar Beach FL\n",
      "1088 96738 Waikoloa HI\n",
      "1089 76262 Roanoke TX\n",
      "1090 85641 Vail AZ\n",
      "1091 34228 Longboat Key FL\n",
      "1092 18938 New Hope PA\n",
      "1093 95223 Arnold CA\n",
      "1094 19047 Langhorne PA\n",
      "1095 60081 Spring Grove IL\n",
      "1097 08243 Sea Isle City NJ\n"
     ]
    }
   ],
   "source": [
    "engine = SearchEngine()\n",
    "zipcodes_dict = {\"RegionID\": [], \"City\": [], \"State\": [], \"Zip Code\": []}\n",
    "bad_cities = {\"RegionID\": [], \"City\": [], \"State\": []}\n",
    "for i in city_data.index:\n",
    "    try:\n",
    "        city=city_data['RegionName'].iloc[i]\n",
    "        state=city_data['StateName'].iloc[i]\n",
    "        regionId=city_data['RegionID'].iloc[i]\n",
    "        zipcodes = engine.by_city_and_state(city=city, state=state)\n",
    "        for zipcode in zipcodes:\n",
    "        # Add only the first located zip code for each location to dictionary\n",
    "            if regionId not in zipcodes_dict[\"RegionID\"]:\n",
    "                zipcodes_dict[\"RegionID\"].append(regionId)\n",
    "                zipcodes_dict[\"City\"].append(city)\n",
    "                zipcodes_dict['State'].append(state)\n",
    "                zipcodes_dict['Zip Code'].append(zipcode.zipcode)\n",
    "                print(int(i)+1, zipcode.zipcode, zipcode.major_city, zipcode.state)\n",
    "    except:\n",
    "        # Add cities with errors to a dictionary of bad locations\n",
    "        bad_cities[\"RegionID\"].append(regionId)\n",
    "        bad_cities[\"City\"].append(city)\n",
    "        bad_cities[\"State\"].append(state)\n",
    "        print(f\"--------{city}, {state} not found--------\")\n",
    "        pass\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "fc5750ec",
   "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>RegionID</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>38992</td>\n",
       "      <td>Highlands Ranch</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>26561</td>\n",
       "      <td>Plymouth</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37840</td>\n",
       "      <td>Cheektowaga</td>\n",
       "      <td>New York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17845</td>\n",
       "      <td>Eagan</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10264</td>\n",
       "      <td>Bartlett</td>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>25383</td>\n",
       "      <td>Kentwood</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>12751</td>\n",
       "      <td>Milwaukie</td>\n",
       "      <td>Oregon</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6102</td>\n",
       "      <td>Murray</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>49352</td>\n",
       "      <td>Dunwoody</td>\n",
       "      <td>Georgia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>51952</td>\n",
       "      <td>Evesham Township</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>26213</td>\n",
       "      <td>Northglenn</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3731</td>\n",
       "      <td>Boardman</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>119842</td>\n",
       "      <td>Ken Caryl</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14825</td>\n",
       "      <td>Cottonwood Heights</td>\n",
       "      <td>Utah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>396536</td>\n",
       "      <td>Security-Widefield</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>24372</td>\n",
       "      <td>Des Moines</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>11441</td>\n",
       "      <td>Fitchburg</td>\n",
       "      <td>Wisconsin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>54410</td>\n",
       "      <td>Shoreview</td>\n",
       "      <td>Minnesota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>119404</td>\n",
       "      <td>Columbine</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>157222</td>\n",
       "      <td>Carney</td>\n",
       "      <td>Maryland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>396606</td>\n",
       "      <td>Vinings</td>\n",
       "      <td>Georgia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>3418</td>\n",
       "      <td>Anthem</td>\n",
       "      <td>Arizona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>26027</td>\n",
       "      <td>Mountain Brook</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>51547</td>\n",
       "      <td>Corinth</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>120381</td>\n",
       "      <td>Sherrelwood</td>\n",
       "      <td>Colorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>54702</td>\n",
       "      <td>Tinton Falls</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>35084</td>\n",
       "      <td>Bon Air</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>11083</td>\n",
       "      <td>Delran Township</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>397390</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Michigan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>8823</td>\n",
       "      <td>Lyndhurst</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>228223</td>\n",
       "      <td>Middle Valley</td>\n",
       "      <td>Tennessee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>32403</td>\n",
       "      <td>Lakeside</td>\n",
       "      <td>Virginia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>29879</td>\n",
       "      <td>Crestwood</td>\n",
       "      <td>Missouri</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>124049</td>\n",
       "      <td>Country Walk</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>126662</td>\n",
       "      <td>Williamsburg</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>126414</td>\n",
       "      <td>Timber Pines</td>\n",
       "      <td>Florida</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    RegionID                City       State\n",
       "0      38992     Highlands Ranch    Colorado\n",
       "1      26561            Plymouth   Minnesota\n",
       "2      37840         Cheektowaga    New York\n",
       "3      17845               Eagan   Minnesota\n",
       "4      10264            Bartlett   Tennessee\n",
       "5      25383            Kentwood    Michigan\n",
       "6      12751           Milwaukie      Oregon\n",
       "7       6102              Murray        Utah\n",
       "8      49352            Dunwoody     Georgia\n",
       "9      51952    Evesham Township  New Jersey\n",
       "10     26213          Northglenn    Colorado\n",
       "11      3731            Boardman        Ohio\n",
       "12    119842           Ken Caryl    Colorado\n",
       "13     14825  Cottonwood Heights        Utah\n",
       "14    396536  Security-Widefield    Colorado\n",
       "15     24372          Des Moines  Washington\n",
       "16     11441           Fitchburg   Wisconsin\n",
       "17     54410           Shoreview   Minnesota\n",
       "18    119404           Columbine    Colorado\n",
       "19    157222              Carney    Maryland\n",
       "20    396606             Vinings     Georgia\n",
       "21      3418              Anthem     Arizona\n",
       "22     26027      Mountain Brook     Alabama\n",
       "23     51547             Corinth       Texas\n",
       "24    120381         Sherrelwood    Colorado\n",
       "25     54702        Tinton Falls  New Jersey\n",
       "26     35084             Bon Air    Virginia\n",
       "27     11083     Delran Township  New Jersey\n",
       "28    397390               Texas    Michigan\n",
       "29      8823           Lyndhurst        Ohio\n",
       "30    228223       Middle Valley   Tennessee\n",
       "31     32403            Lakeside    Virginia\n",
       "32     29879           Crestwood    Missouri\n",
       "33    124049        Country Walk     Florida\n",
       "34    126662        Williamsburg     Florida\n",
       "35    126414        Timber Pines     Florida"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a dataframe of the bad locations to see how many we're removing from the datasource\n",
    "bad_cities_df = pd.DataFrame(bad_cities, columns=[\"RegionID\", \"City\", \"State\"])\n",
    "bad_cities_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "eba04e66",
   "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>RegionID</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Zip Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6181</td>\n",
       "      <td>New York</td>\n",
       "      <td>New York</td>\n",
       "      <td>10001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17426</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>60601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>18959</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>89101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>38128</td>\n",
       "      <td>Dallas</td>\n",
       "      <td>Texas</td>\n",
       "      <td>75201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10920</td>\n",
       "      <td>Columbus</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>43085</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1039</th>\n",
       "      <td>49592</td>\n",
       "      <td>New Hope</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>18938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1040</th>\n",
       "      <td>48710</td>\n",
       "      <td>Arnold</td>\n",
       "      <td>California</td>\n",
       "      <td>95223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1041</th>\n",
       "      <td>12306</td>\n",
       "      <td>Langhorne</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>19047</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1042</th>\n",
       "      <td>33433</td>\n",
       "      <td>Pingree Grove</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>60081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1043</th>\n",
       "      <td>50339</td>\n",
       "      <td>Sea Isle City</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>08243</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1035 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      RegionID           City         State Zip Code\n",
       "0         6181       New York      New York    10001\n",
       "1        17426        Chicago      Illinois    60601\n",
       "2        18959      Las Vegas        Nevada    89101\n",
       "3        38128         Dallas         Texas    75201\n",
       "4        10920       Columbus          Ohio    43085\n",
       "...        ...            ...           ...      ...\n",
       "1039     49592       New Hope  Pennsylvania    18938\n",
       "1040     48710         Arnold    California    95223\n",
       "1041     12306      Langhorne  Pennsylvania    19047\n",
       "1042     33433  Pingree Grove      Illinois    60081\n",
       "1043     50339  Sea Isle City    New Jersey    08243\n",
       "\n",
       "[1035 rows x 4 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create new dataframe from dictionary of locations with zip codes\n",
    "zipcodes_df = pd.DataFrame(zipcodes_dict, columns=['RegionID', 'City', 'State', 'Zip Code'])\n",
    "zipcodes_df = zipcodes_df.drop_duplicates(subset='Zip Code', keep='first')\n",
    "zipcodes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "17b0cd7e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create temporary csv to feed into geocoding script\n",
    "zipcodes_df.to_csv('./resources/zipcodes.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a5e97aaa",
   "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>Zip Code</th>\n",
       "      <th>0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01013</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>70769</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>64093</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>64443</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>64659</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>64780</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>65201</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>65606</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>65714</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>65721</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Zip Code  0\n",
       "0    01013  1\n",
       "1    70769  1\n",
       "2    64093  1\n",
       "3    64443  1\n",
       "4    64659  1\n",
       "5    64780  1\n",
       "6    65201  1\n",
       "7    65606  1\n",
       "8    65714  1\n",
       "9    65721  1"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check to see if there are duplicate zip codes\n",
    "# zipcodes_df = pd.read_csv('./resources/zipcodes.csv')\n",
    "zipcodes_pivot = zipcodes_df.pivot_table(columns=['Zip Code'], aggfunc='size').sort_values(ascending=False)\n",
    "zipcodes_dup_df = zipcodes_pivot.reset_index()\n",
    "zipcodes_dup_df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "68132aa5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# zipcodes_df = zipcodes_df.drop_duplicates(subset='Zip Code', keep='first')\n",
    "# zipcodes_df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "mlenv",
   "language": "python",
   "name": "mlenv"
  },
  "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.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}