clean_zipcode.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "c1920c62",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "83f5f5b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "raw_zip = json.load(open('data/zip_code_list.json'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "48ba7baa",
   "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>raw_zip</th>\n",
       "      <th>adj_zip</th>\n",
       "      <th>country</th>\n",
       "      <th>timezone</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32928</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>07010-3107</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91730-5743</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65085</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78530</th>\n",
       "      <td>497791331</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78531</th>\n",
       "      <td>95666-9698</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78532</th>\n",
       "      <td>40216-2302</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78533</th>\n",
       "      <td>945016365</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78534</th>\n",
       "      <td>100277221</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78535 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          raw_zip  adj_zip  country  timezone  longitude  latitude\n",
       "0             NaN        0        0         0          0         0\n",
       "1           32928        0        0         0          0         0\n",
       "2      07010-3107        0        0         0          0         0\n",
       "3      91730-5743        0        0         0          0         0\n",
       "4           65085        0        0         0          0         0\n",
       "...           ...      ...      ...       ...        ...       ...\n",
       "78530   497791331        0        0         0          0         0\n",
       "78531  95666-9698        0        0         0          0         0\n",
       "78532  40216-2302        0        0         0          0         0\n",
       "78533   945016365        0        0         0          0         0\n",
       "78534   100277221        0        0         0          0         0\n",
       "\n",
       "[78535 rows x 6 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_df = pd.DataFrame(raw_zip, columns =['raw_zip'])\n",
    "zip_df['adj_zip'] = 0\n",
    "zip_df['country'] = 0\n",
    "zip_df['timezone'] = 0\n",
    "zip_df['longitude'] = 0\n",
    "zip_df['latitude'] = 0\n",
    "zip_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "6d7366d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def set_country(row):\n",
    "    # null cases\n",
    "    if pd.isna(row[\"raw_zip\"]) == True:\n",
    "        return np.nan\n",
    "    \n",
    "    # when zip contains only numbers\n",
    "    elif row[\"raw_zip\"].isnumeric() == True:\n",
    "        return len(row[\"raw_zip\"])\n",
    "    \n",
    "    # when zip contains '-'\n",
    "    elif (\"-\" in row[\"raw_zip\"]) == True:\n",
    "        return len(row[\"raw_zip\"].split(\"-\")[0])\n",
    "    \n",
    "    # other cases: when zip contains characters and special symbols\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "zip_df = zip_df.assign(country=zip_df.apply(set_country, axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "f32cecf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def set_zip(row):\n",
    "    if (row[\"country\"] == 5.0) == True:\n",
    "        if (\"-\" in row[\"raw_zip\"]) == True:\n",
    "            return row[\"raw_zip\"].split(\"-\")[0]\n",
    "        else:\n",
    "            return row[\"raw_zip\"]\n",
    "\n",
    "zip_df = zip_df.assign(adj_zip=zip_df.apply(set_zip, axis=1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "d05ab98b",
   "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>raw_zip</th>\n",
       "      <th>adj_zip</th>\n",
       "      <th>country</th>\n",
       "      <th>timezone</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32928</td>\n",
       "      <td>32928</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>07010-3107</td>\n",
       "      <td>07010</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91730-5743</td>\n",
       "      <td>91730</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65085</td>\n",
       "      <td>65085</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78530</th>\n",
       "      <td>497791331</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78531</th>\n",
       "      <td>95666-9698</td>\n",
       "      <td>95666</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78532</th>\n",
       "      <td>40216-2302</td>\n",
       "      <td>40216</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78533</th>\n",
       "      <td>945016365</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78534</th>\n",
       "      <td>100277221</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78535 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          raw_zip adj_zip  country  timezone  longitude  latitude\n",
       "0             NaN    None      NaN         0          0         0\n",
       "1           32928   32928      5.0         0          0         0\n",
       "2      07010-3107   07010      5.0         0          0         0\n",
       "3      91730-5743   91730      5.0         0          0         0\n",
       "4           65085   65085      5.0         0          0         0\n",
       "...           ...     ...      ...       ...        ...       ...\n",
       "78530   497791331    None      9.0         0          0         0\n",
       "78531  95666-9698   95666      5.0         0          0         0\n",
       "78532  40216-2302   40216      5.0         0          0         0\n",
       "78533   945016365    None      9.0         0          0         0\n",
       "78534   100277221    None      9.0         0          0         0\n",
       "\n",
       "[78535 rows x 6 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "8e2dc741",
   "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</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>city</th>\n",
       "      <th>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>zcta</th>\n",
       "      <th>parent_zcta</th>\n",
       "      <th>population</th>\n",
       "      <th>density</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_weights</th>\n",
       "      <th>county_names_all</th>\n",
       "      <th>county_fips_all</th>\n",
       "      <th>imprecise</th>\n",
       "      <th>military</th>\n",
       "      <th>timezone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00601</td>\n",
       "      <td>18.18005</td>\n",
       "      <td>-66.75218</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>PR</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17113.0</td>\n",
       "      <td>102.7</td>\n",
       "      <td>72001</td>\n",
       "      <td>Adjuntas</td>\n",
       "      <td>{\"72001\": \"99.43\", \"72141\": \"0.57\"}</td>\n",
       "      <td>Adjuntas|Utuado</td>\n",
       "      <td>72001|72141</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00602</td>\n",
       "      <td>18.36074</td>\n",
       "      <td>-67.17519</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>PR</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37751.0</td>\n",
       "      <td>476.0</td>\n",
       "      <td>72003</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>{\"72003\": \"100\"}</td>\n",
       "      <td>Aguada</td>\n",
       "      <td>72003</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00603</td>\n",
       "      <td>18.45440</td>\n",
       "      <td>-67.12201</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>PR</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47081.0</td>\n",
       "      <td>574.9</td>\n",
       "      <td>72005</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>{\"72005\": \"100\"}</td>\n",
       "      <td>Aguadilla</td>\n",
       "      <td>72005</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00606</td>\n",
       "      <td>18.16721</td>\n",
       "      <td>-66.93828</td>\n",
       "      <td>Maricao</td>\n",
       "      <td>PR</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6392.0</td>\n",
       "      <td>58.3</td>\n",
       "      <td>72093</td>\n",
       "      <td>Maricao</td>\n",
       "      <td>{\"72093\": \"94.88\", \"72153\": \"3.78\", \"72121\": \"...</td>\n",
       "      <td>Maricao|Yauco|Sabana Grande</td>\n",
       "      <td>72093|72153|72121</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00610</td>\n",
       "      <td>18.29032</td>\n",
       "      <td>-67.12244</td>\n",
       "      <td>Anasco</td>\n",
       "      <td>PR</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26686.0</td>\n",
       "      <td>286.9</td>\n",
       "      <td>72011</td>\n",
       "      <td>Añasco</td>\n",
       "      <td>{\"72011\": \"99.45\", \"72003\": \"0.55\"}</td>\n",
       "      <td>Añasco|Aguada</td>\n",
       "      <td>72011|72003</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Puerto_Rico</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     zip       lat       lng       city state_id   state_name  zcta  \\\n",
       "0  00601  18.18005 -66.75218   Adjuntas       PR  Puerto Rico  True   \n",
       "1  00602  18.36074 -67.17519     Aguada       PR  Puerto Rico  True   \n",
       "2  00603  18.45440 -67.12201  Aguadilla       PR  Puerto Rico  True   \n",
       "3  00606  18.16721 -66.93828    Maricao       PR  Puerto Rico  True   \n",
       "4  00610  18.29032 -67.12244     Anasco       PR  Puerto Rico  True   \n",
       "\n",
       "   parent_zcta  population  density  county_fips county_name  \\\n",
       "0          NaN     17113.0    102.7        72001    Adjuntas   \n",
       "1          NaN     37751.0    476.0        72003      Aguada   \n",
       "2          NaN     47081.0    574.9        72005   Aguadilla   \n",
       "3          NaN      6392.0     58.3        72093     Maricao   \n",
       "4          NaN     26686.0    286.9        72011      Añasco   \n",
       "\n",
       "                                      county_weights  \\\n",
       "0                {\"72001\": \"99.43\", \"72141\": \"0.57\"}   \n",
       "1                                   {\"72003\": \"100\"}   \n",
       "2                                   {\"72005\": \"100\"}   \n",
       "3  {\"72093\": \"94.88\", \"72153\": \"3.78\", \"72121\": \"...   \n",
       "4                {\"72011\": \"99.45\", \"72003\": \"0.55\"}   \n",
       "\n",
       "              county_names_all    county_fips_all  imprecise  military  \\\n",
       "0              Adjuntas|Utuado        72001|72141      False     False   \n",
       "1                       Aguada              72003      False     False   \n",
       "2                    Aguadilla              72005      False     False   \n",
       "3  Maricao|Yauco|Sabana Grande  72093|72153|72121      False     False   \n",
       "4                Añasco|Aguada        72011|72003      False     False   \n",
       "\n",
       "              timezone  \n",
       "0  America/Puerto_Rico  \n",
       "1  America/Puerto_Rico  \n",
       "2  America/Puerto_Rico  \n",
       "3  America/Puerto_Rico  \n",
       "4  America/Puerto_Rico  "
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read external data file\n",
    "ref_zip = pd.read_csv('data/uszips.csv')\n",
    "#  补齐0，变成5位数\n",
    "ref_zip['zip'] = ref_zip['zip'].astype(str).str.zfill(5)\n",
    "ref_zip[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "2efe80ac",
   "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>raw_zip</th>\n",
       "      <th>adj_zip</th>\n",
       "      <th>country</th>\n",
       "      <th>zip</th>\n",
       "      <th>lat</th>\n",
       "      <th>lng</th>\n",
       "      <th>city</th>\n",
       "      <th>state_id</th>\n",
       "      <th>state_name</th>\n",
       "      <th>zcta</th>\n",
       "      <th>...</th>\n",
       "      <th>population</th>\n",
       "      <th>density</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>county_weights</th>\n",
       "      <th>county_names_all</th>\n",
       "      <th>county_fips_all</th>\n",
       "      <th>imprecise</th>\n",
       "      <th>military</th>\n",
       "      <th>timezone_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32928</td>\n",
       "      <td>32928</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>07010-3107</td>\n",
       "      <td>07010</td>\n",
       "      <td>5.0</td>\n",
       "      <td>07010</td>\n",
       "      <td>40.82211</td>\n",
       "      <td>-73.98794</td>\n",
       "      <td>Cliffside Park</td>\n",
       "      <td>NJ</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>25126.0</td>\n",
       "      <td>10162.9</td>\n",
       "      <td>34003.0</td>\n",
       "      <td>Bergen</td>\n",
       "      <td>{\"34003\": \"100\"}</td>\n",
       "      <td>Bergen</td>\n",
       "      <td>34003</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/New_York</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>91730-5743</td>\n",
       "      <td>91730</td>\n",
       "      <td>5.0</td>\n",
       "      <td>91730</td>\n",
       "      <td>34.09955</td>\n",
       "      <td>-117.57847</td>\n",
       "      <td>Rancho Cucamonga</td>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>72407.0</td>\n",
       "      <td>1930.1</td>\n",
       "      <td>6071.0</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>{\"06071\": \"100\"}</td>\n",
       "      <td>San Bernardino</td>\n",
       "      <td>06071</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>65085</td>\n",
       "      <td>65085</td>\n",
       "      <td>5.0</td>\n",
       "      <td>65085</td>\n",
       "      <td>38.40179</td>\n",
       "      <td>-92.07865</td>\n",
       "      <td>Westphalia</td>\n",
       "      <td>MO</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>1252.0</td>\n",
       "      <td>8.6</td>\n",
       "      <td>29151.0</td>\n",
       "      <td>Osage</td>\n",
       "      <td>{\"29151\": \"100\"}</td>\n",
       "      <td>Osage</td>\n",
       "      <td>29151</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Chicago</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>78530</th>\n",
       "      <td>497791331</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78531</th>\n",
       "      <td>95666-9698</td>\n",
       "      <td>95666</td>\n",
       "      <td>5.0</td>\n",
       "      <td>95666</td>\n",
       "      <td>38.53205</td>\n",
       "      <td>-120.36845</td>\n",
       "      <td>Pioneer</td>\n",
       "      <td>CA</td>\n",
       "      <td>California</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>5164.0</td>\n",
       "      <td>35.8</td>\n",
       "      <td>6005.0</td>\n",
       "      <td>Amador</td>\n",
       "      <td>{\"06005\": \"100\"}</td>\n",
       "      <td>Amador</td>\n",
       "      <td>06005</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78532</th>\n",
       "      <td>40216-2302</td>\n",
       "      <td>40216</td>\n",
       "      <td>5.0</td>\n",
       "      <td>40216</td>\n",
       "      <td>38.18765</td>\n",
       "      <td>-85.83979</td>\n",
       "      <td>Louisville</td>\n",
       "      <td>KY</td>\n",
       "      <td>Kentucky</td>\n",
       "      <td>True</td>\n",
       "      <td>...</td>\n",
       "      <td>42029.0</td>\n",
       "      <td>1122.5</td>\n",
       "      <td>21111.0</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>{\"21111\": \"100\"}</td>\n",
       "      <td>Jefferson</td>\n",
       "      <td>21111</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>America/Kentucky/Louisville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78533</th>\n",
       "      <td>945016365</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78534</th>\n",
       "      <td>100277221</td>\n",
       "      <td>None</td>\n",
       "      <td>9.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78535 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          raw_zip adj_zip  country    zip       lat        lng  \\\n",
       "0             NaN    None      NaN    NaN       NaN        NaN   \n",
       "1           32928   32928      5.0    NaN       NaN        NaN   \n",
       "2      07010-3107   07010      5.0  07010  40.82211  -73.98794   \n",
       "3      91730-5743   91730      5.0  91730  34.09955 -117.57847   \n",
       "4           65085   65085      5.0  65085  38.40179  -92.07865   \n",
       "...           ...     ...      ...    ...       ...        ...   \n",
       "78530   497791331    None      9.0    NaN       NaN        NaN   \n",
       "78531  95666-9698   95666      5.0  95666  38.53205 -120.36845   \n",
       "78532  40216-2302   40216      5.0  40216  38.18765  -85.83979   \n",
       "78533   945016365    None      9.0    NaN       NaN        NaN   \n",
       "78534   100277221    None      9.0    NaN       NaN        NaN   \n",
       "\n",
       "                   city state_id  state_name  zcta  ...  population  density  \\\n",
       "0                   NaN      NaN         NaN   NaN  ...         NaN      NaN   \n",
       "1                   NaN      NaN         NaN   NaN  ...         NaN      NaN   \n",
       "2        Cliffside Park       NJ  New Jersey  True  ...     25126.0  10162.9   \n",
       "3      Rancho Cucamonga       CA  California  True  ...     72407.0   1930.1   \n",
       "4            Westphalia       MO    Missouri  True  ...      1252.0      8.6   \n",
       "...                 ...      ...         ...   ...  ...         ...      ...   \n",
       "78530               NaN      NaN         NaN   NaN  ...         NaN      NaN   \n",
       "78531           Pioneer       CA  California  True  ...      5164.0     35.8   \n",
       "78532        Louisville       KY    Kentucky  True  ...     42029.0   1122.5   \n",
       "78533               NaN      NaN         NaN   NaN  ...         NaN      NaN   \n",
       "78534               NaN      NaN         NaN   NaN  ...         NaN      NaN   \n",
       "\n",
       "       county_fips     county_name    county_weights county_names_all  \\\n",
       "0              NaN             NaN               NaN              NaN   \n",
       "1              NaN             NaN               NaN              NaN   \n",
       "2          34003.0          Bergen  {\"34003\": \"100\"}           Bergen   \n",
       "3           6071.0  San Bernardino  {\"06071\": \"100\"}   San Bernardino   \n",
       "4          29151.0           Osage  {\"29151\": \"100\"}            Osage   \n",
       "...            ...             ...               ...              ...   \n",
       "78530          NaN             NaN               NaN              NaN   \n",
       "78531       6005.0          Amador  {\"06005\": \"100\"}           Amador   \n",
       "78532      21111.0       Jefferson  {\"21111\": \"100\"}        Jefferson   \n",
       "78533          NaN             NaN               NaN              NaN   \n",
       "78534          NaN             NaN               NaN              NaN   \n",
       "\n",
       "      county_fips_all imprecise military               timezone_right  \n",
       "0                 NaN       NaN      NaN                          NaN  \n",
       "1                 NaN       NaN      NaN                          NaN  \n",
       "2               34003     False    False             America/New_York  \n",
       "3               06071     False    False          America/Los_Angeles  \n",
       "4               29151     False    False              America/Chicago  \n",
       "...               ...       ...      ...                          ...  \n",
       "78530             NaN       NaN      NaN                          NaN  \n",
       "78531           06005     False    False          America/Los_Angeles  \n",
       "78532           21111     False    False  America/Kentucky/Louisville  \n",
       "78533             NaN       NaN      NaN                          NaN  \n",
       "78534             NaN       NaN      NaN                          NaN  \n",
       "\n",
       "[78535 rows x 21 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_zip = zip_df.merge(ref_zip, how='left',left_on='adj_zip',right_on='zip',suffixes=('_left', '_right'))\n",
    "merge_zip.drop('timezone_left', axis=1, inplace=True)\n",
    "merge_zip.drop('longitude',axis=1, inplace=True)\n",
    "merge_zip.drop('latitude', axis=1, inplace = True)\n",
    "merge_zip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "a0b34883",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "raw_zip             78534\n",
       "adj_zip             72047\n",
       "country             78534\n",
       "zip                 62747\n",
       "lat                 62747\n",
       "lng                 62747\n",
       "city                62747\n",
       "state_id            62747\n",
       "state_name          62747\n",
       "zcta                62747\n",
       "parent_zcta             0\n",
       "population          62707\n",
       "density             62707\n",
       "county_fips         62747\n",
       "county_name         62747\n",
       "county_weights      62747\n",
       "county_names_all    62747\n",
       "county_fips_all     62747\n",
       "imprecise           62747\n",
       "military            62747\n",
       "timezone_right      62747\n",
       "dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merge_zip.count()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}