etl.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Project Title\n",
    "### Data Engineering Capstone Project\n",
    "\n",
    "#### Project Summary\n",
    "--describe your project at a high level--\n",
    "\n",
    "The project follows the follow steps:\n",
    "* Step 1: Scope the Project and Gather Data\n",
    "* Step 2: Explore and Assess the Data\n",
    "* Step 3: Define the Data Model\n",
    "* Step 4: Run ETL to Model the Data\n",
    "* Step 5: Complete Project Write Up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Import libraries and get configuration settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import configparser\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "config = configparser.ConfigParser()\n",
    "config.read('capstone.cfg')\n",
    "\n",
    "# AWS config\n",
    "REGION = config.get('AWS','REGION')\n",
    "KEY = config.get('AWS','KEY')\n",
    "SECRET = config.get('AWS','SECRET')\n",
    "BUCKET = config.get('AWS','BUCKET')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 1: Scope the Project and Gather Data\n",
    "\n",
    "#### Scope \n",
    "Explain what you plan to do in the project in more detail. What data do you use? What is your end solution look like? What tools did you use? etc\n",
    "\n",
    "#### Describe and Gather Data \n",
    "Describe the data sets you're using. Where did it come from? What type of information is included? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_temp = pd.read_csv('../../data2/GlobalLandTemperaturesByCity.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_airport = pd.read_csv(\"airport-codes_csv.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "df_cities = pd.read_csv(\"us-cities-demographics.csv\", delimiter=\";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "spark = SparkSession.builder.\\\n",
    "config(\"spark.jars.repositories\", \"https://repos.spark-packages.org/\").\\\n",
    "config(\"spark.jars.packages\", \"saurfang:spark-sas7bdat:2.0.0-s_2.11\").\\\n",
    "enableHiveSupport().getOrCreate()\n",
    "\n",
    "df_spark = spark.read.format('com.github.saurfang.sas.spark').load('../../data/18-83510-I94-Data-2016/i94_apr16_sub.sas7bdat')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "#write to parquet\n",
    "df_spark.write.parquet(\"sas_data\")\n",
    "df_spark=spark.read.parquet(\"sas_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>...</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2027561</td>\n",
       "      <td>4084316.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>HHW</td>\n",
       "      <td>20566.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HI</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1955.0</td>\n",
       "      <td>07202016</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JL</td>\n",
       "      <td>5.658267e+10</td>\n",
       "      <td>00782</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2171295</td>\n",
       "      <td>4422636.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>MCA</td>\n",
       "      <td>20567.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>TX</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>10222016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*GA</td>\n",
       "      <td>9.436200e+10</td>\n",
       "      <td>XBLNG</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>589494</td>\n",
       "      <td>1195600.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>OGG</td>\n",
       "      <td>20551.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>FL</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>07052016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LH</td>\n",
       "      <td>5.578047e+10</td>\n",
       "      <td>00464</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2631158</td>\n",
       "      <td>5291768.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>LOS</td>\n",
       "      <td>20572.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>CA</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>10272016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QR</td>\n",
       "      <td>9.478970e+10</td>\n",
       "      <td>00739</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3032257</td>\n",
       "      <td>985523.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>CHM</td>\n",
       "      <td>20550.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>07042016</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.232257e+10</td>\n",
       "      <td>LAND</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0      cicid   i94yr  i94mon  i94cit  i94res i94port  arrdate  \\\n",
       "0     2027561  4084316.0  2016.0     4.0   209.0   209.0     HHW  20566.0   \n",
       "1     2171295  4422636.0  2016.0     4.0   582.0   582.0     MCA  20567.0   \n",
       "2      589494  1195600.0  2016.0     4.0   148.0   112.0     OGG  20551.0   \n",
       "3     2631158  5291768.0  2016.0     4.0   297.0   297.0     LOS  20572.0   \n",
       "4     3032257   985523.0  2016.0     4.0   111.0   111.0     CHM  20550.0   \n",
       "\n",
       "   i94mode i94addr    ...     entdepu  matflag  biryear   dtaddto  gender  \\\n",
       "0      1.0      HI    ...         NaN        M   1955.0  07202016       F   \n",
       "1      1.0      TX    ...         NaN        M   1990.0  10222016       M   \n",
       "2      1.0      FL    ...         NaN        M   1940.0  07052016       M   \n",
       "3      1.0      CA    ...         NaN        M   1991.0  10272016       M   \n",
       "4      3.0      NY    ...         NaN        M   1997.0  07042016       F   \n",
       "\n",
       "  insnum airline        admnum  fltno  visatype  \n",
       "0    NaN      JL  5.658267e+10  00782        WT  \n",
       "1    NaN     *GA  9.436200e+10  XBLNG        B2  \n",
       "2    NaN      LH  5.578047e+10  00464        WT  \n",
       "3    NaN      QR  9.478970e+10  00739        B2  \n",
       "4    NaN     NaN  4.232257e+10   LAND        WT  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_immigration = pd.read_csv(\"immigration_data_sample.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 2: Explore and Assess the Data\n",
    "#### Explore the Data \n",
    "Identify data quality issues, like missing values, duplicate data, etc.\n",
    "\n",
    "#### Cleaning Steps\n",
    "Document steps necessary to clean the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Performing cleaning tasks here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Clean the temperature data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "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>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1743-11-01</td>\n",
       "      <td>6.068</td>\n",
       "      <td>1.737</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1743-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1744-01-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1744-02-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1744-03-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty   City  \\\n",
       "0  1743-11-01               6.068                          1.737  Århus   \n",
       "1  1743-12-01                 NaN                            NaN  Århus   \n",
       "2  1744-01-01                 NaN                            NaN  Århus   \n",
       "3  1744-02-01                 NaN                            NaN  Århus   \n",
       "4  1744-03-01                 NaN                            NaN  Århus   \n",
       "\n",
       "   Country Latitude Longitude  \n",
       "0  Denmark   57.05N    10.33E  \n",
       "1  Denmark   57.05N    10.33E  \n",
       "2  Denmark   57.05N    10.33E  \n",
       "3  Denmark   57.05N    10.33E  \n",
       "4  Denmark   57.05N    10.33E  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the data\n",
    "df_temp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true
   },
   "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>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1743-11-01</td>\n",
       "      <td>6.068</td>\n",
       "      <td>1.737</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1744-04-01</td>\n",
       "      <td>5.788</td>\n",
       "      <td>3.624</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1744-05-01</td>\n",
       "      <td>10.644</td>\n",
       "      <td>1.283</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1744-06-01</td>\n",
       "      <td>14.051</td>\n",
       "      <td>1.347</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1744-07-01</td>\n",
       "      <td>16.082</td>\n",
       "      <td>1.396</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty   City  \\\n",
       "0  1743-11-01               6.068                          1.737  Århus   \n",
       "5  1744-04-01               5.788                          3.624  Århus   \n",
       "6  1744-05-01              10.644                          1.283  Århus   \n",
       "7  1744-06-01              14.051                          1.347  Århus   \n",
       "8  1744-07-01              16.082                          1.396  Århus   \n",
       "\n",
       "   Country Latitude Longitude  \n",
       "0  Denmark   57.05N    10.33E  \n",
       "5  Denmark   57.05N    10.33E  \n",
       "6  Denmark   57.05N    10.33E  \n",
       "7  Denmark   57.05N    10.33E  \n",
       "8  Denmark   57.05N    10.33E  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filter out the NaN values for temperature\n",
    "df_temp = df_temp[~df_temp['AverageTemperature'].isna()]\n",
    "df_temp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "137066    1743-11-01\n",
       "137071    1744-04-01\n",
       "137072    1744-05-01\n",
       "137073    1744-06-01\n",
       "137074    1744-07-01\n",
       "137076    1744-09-01\n",
       "137077    1744-10-01\n",
       "137078    1744-11-01\n",
       "137079    1744-12-01\n",
       "137080    1745-01-01\n",
       "137081    1745-02-01\n",
       "137082    1745-03-01\n",
       "137083    1745-04-01\n",
       "137140    1750-01-01\n",
       "137141    1750-02-01\n",
       "137142    1750-03-01\n",
       "137143    1750-04-01\n",
       "137144    1750-05-01\n",
       "137145    1750-06-01\n",
       "137146    1750-07-01\n",
       "137147    1750-08-01\n",
       "137148    1750-09-01\n",
       "137149    1750-10-01\n",
       "137151    1750-12-01\n",
       "137152    1751-01-01\n",
       "137153    1751-02-01\n",
       "137154    1751-03-01\n",
       "137155    1751-04-01\n",
       "137157    1751-06-01\n",
       "137158    1751-07-01\n",
       "             ...    \n",
       "49850     2011-04-01\n",
       "49851     2011-05-01\n",
       "49852     2011-06-01\n",
       "49853     2011-07-01\n",
       "49854     2011-08-01\n",
       "49855     2011-09-01\n",
       "49856     2011-10-01\n",
       "49857     2011-11-01\n",
       "49858     2011-12-01\n",
       "49859     2012-01-01\n",
       "49860     2012-02-01\n",
       "49861     2012-03-01\n",
       "49862     2012-04-01\n",
       "49863     2012-05-01\n",
       "49864     2012-06-01\n",
       "49865     2012-07-01\n",
       "49866     2012-08-01\n",
       "49867     2012-09-01\n",
       "49868     2012-10-01\n",
       "49869     2012-11-01\n",
       "49870     2012-12-01\n",
       "49871     2013-01-01\n",
       "49872     2013-02-01\n",
       "49873     2013-03-01\n",
       "49874     2013-04-01\n",
       "49875     2013-05-01\n",
       "49876     2013-06-01\n",
       "49877     2013-07-01\n",
       "49878     2013-08-01\n",
       "49879     2013-09-01\n",
       "Name: dt, Length: 3141, dtype: object"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_temp[df_temp['Country']== 'United States']['dt'].drop_duplicates().sort_values()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Clean the airport data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "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>ident</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>elevation_ft</th>\n",
       "      <th>continent</th>\n",
       "      <th>iso_country</th>\n",
       "      <th>iso_region</th>\n",
       "      <th>municipality</th>\n",
       "      <th>gps_code</th>\n",
       "      <th>iata_code</th>\n",
       "      <th>local_code</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00A</td>\n",
       "      <td>heliport</td>\n",
       "      <td>Total Rf Heliport</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-PA</td>\n",
       "      <td>Bensalem</td>\n",
       "      <td>00A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00A</td>\n",
       "      <td>-74.93360137939453, 40.07080078125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00AA</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Aero B Ranch Airport</td>\n",
       "      <td>3435.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-KS</td>\n",
       "      <td>Leoti</td>\n",
       "      <td>00AA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00AA</td>\n",
       "      <td>-101.473911, 38.704022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00AK</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Lowell Field</td>\n",
       "      <td>450.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AK</td>\n",
       "      <td>Anchor Point</td>\n",
       "      <td>00AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00AK</td>\n",
       "      <td>-151.695999146, 59.94919968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00AL</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Epps Airpark</td>\n",
       "      <td>820.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AL</td>\n",
       "      <td>Harvest</td>\n",
       "      <td>00AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00AL</td>\n",
       "      <td>-86.77030181884766, 34.86479949951172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00AR</td>\n",
       "      <td>closed</td>\n",
       "      <td>Newport Hospital &amp; Clinic Heliport</td>\n",
       "      <td>237.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AR</td>\n",
       "      <td>Newport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-91.254898, 35.6087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ident           type                                name  elevation_ft  \\\n",
       "0   00A       heliport                   Total Rf Heliport          11.0   \n",
       "1  00AA  small_airport                Aero B Ranch Airport        3435.0   \n",
       "2  00AK  small_airport                        Lowell Field         450.0   \n",
       "3  00AL  small_airport                        Epps Airpark         820.0   \n",
       "4  00AR         closed  Newport Hospital & Clinic Heliport         237.0   \n",
       "\n",
       "  continent iso_country iso_region  municipality gps_code iata_code  \\\n",
       "0       NaN          US      US-PA      Bensalem      00A       NaN   \n",
       "1       NaN          US      US-KS         Leoti     00AA       NaN   \n",
       "2       NaN          US      US-AK  Anchor Point     00AK       NaN   \n",
       "3       NaN          US      US-AL       Harvest     00AL       NaN   \n",
       "4       NaN          US      US-AR       Newport      NaN       NaN   \n",
       "\n",
       "  local_code                            coordinates  \n",
       "0        00A     -74.93360137939453, 40.07080078125  \n",
       "1       00AA                 -101.473911, 38.704022  \n",
       "2       00AK            -151.695999146, 59.94919968  \n",
       "3       00AL  -86.77030181884766, 34.86479949951172  \n",
       "4        NaN                    -91.254898, 35.6087  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the data\n",
    "df_airport.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        NaN\n",
       "223       OC\n",
       "10368     AF\n",
       "10369     AN\n",
       "10370     EU\n",
       "10379     AS\n",
       "10458     SA\n",
       "Name: continent, dtype: object"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# US doesn't seem to have a continent code\n",
    "# The North America continent code (NA) is missing\n",
    "df_airport['continent'].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        US\n",
       "174      PR\n",
       "10686    MX\n",
       "12077    BB\n",
       "12177    GL\n",
       "12346    GB\n",
       "12349    BM\n",
       "12776    BS\n",
       "12890    BZ\n",
       "12943    CA\n",
       "15736    CR\n",
       "16097    CU\n",
       "16771    PA\n",
       "17558    DO\n",
       "22701    GT\n",
       "23095    HN\n",
       "23181    HT\n",
       "24954    JM\n",
       "31392    PM\n",
       "32730    TC\n",
       "33268    GD\n",
       "33531    NI\n",
       "33930    SV\n",
       "33931    MF\n",
       "34211    KY\n",
       "43386    VI\n",
       "47627    AG\n",
       "47654    DM\n",
       "47761    GP\n",
       "47764    MQ\n",
       "47766    BL\n",
       "47803    KN\n",
       "47809    LC\n",
       "47924    AW\n",
       "47925    BQ\n",
       "47926    CW\n",
       "47928    SX\n",
       "47934    AI\n",
       "47979    MS\n",
       "48083    TT\n",
       "48099    VG\n",
       "48106    VC\n",
       "Name: iso_country, dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# All NaNs are from the NA continent\n",
    "df_airport[df_airport['continent'].isna()]['iso_country'].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "editable": true
   },
   "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>ident</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>elevation_ft</th>\n",
       "      <th>continent</th>\n",
       "      <th>iso_country</th>\n",
       "      <th>iso_region</th>\n",
       "      <th>municipality</th>\n",
       "      <th>gps_code</th>\n",
       "      <th>iata_code</th>\n",
       "      <th>local_code</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00A</td>\n",
       "      <td>heliport</td>\n",
       "      <td>Total Rf Heliport</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-PA</td>\n",
       "      <td>Bensalem</td>\n",
       "      <td>00A</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00A</td>\n",
       "      <td>-74.93360137939453, 40.07080078125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00AA</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Aero B Ranch Airport</td>\n",
       "      <td>3435.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-KS</td>\n",
       "      <td>Leoti</td>\n",
       "      <td>00AA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00AA</td>\n",
       "      <td>-101.473911, 38.704022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00AK</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Lowell Field</td>\n",
       "      <td>450.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AK</td>\n",
       "      <td>Anchor Point</td>\n",
       "      <td>00AK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00AK</td>\n",
       "      <td>-151.695999146, 59.94919968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00AL</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Epps Airpark</td>\n",
       "      <td>820.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AL</td>\n",
       "      <td>Harvest</td>\n",
       "      <td>00AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>00AL</td>\n",
       "      <td>-86.77030181884766, 34.86479949951172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00AR</td>\n",
       "      <td>closed</td>\n",
       "      <td>Newport Hospital &amp; Clinic Heliport</td>\n",
       "      <td>237.0</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AR</td>\n",
       "      <td>Newport</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-91.254898, 35.6087</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ident           type                                name  elevation_ft  \\\n",
       "0   00A       heliport                   Total Rf Heliport          11.0   \n",
       "1  00AA  small_airport                Aero B Ranch Airport        3435.0   \n",
       "2  00AK  small_airport                        Lowell Field         450.0   \n",
       "3  00AL  small_airport                        Epps Airpark         820.0   \n",
       "4  00AR         closed  Newport Hospital & Clinic Heliport         237.0   \n",
       "\n",
       "  continent iso_country iso_region  municipality gps_code iata_code  \\\n",
       "0        NA          US      US-PA      Bensalem      00A       NaN   \n",
       "1        NA          US      US-KS         Leoti     00AA       NaN   \n",
       "2        NA          US      US-AK  Anchor Point     00AK       NaN   \n",
       "3        NA          US      US-AL       Harvest     00AL       NaN   \n",
       "4        NA          US      US-AR       Newport      NaN       NaN   \n",
       "\n",
       "  local_code                            coordinates  \n",
       "0        00A     -74.93360137939453, 40.07080078125  \n",
       "1       00AA                 -101.473911, 38.704022  \n",
       "2       00AK            -151.695999146, 59.94919968  \n",
       "3       00AL  -86.77030181884766, 34.86479949951172  \n",
       "4        NaN                    -91.254898, 35.6087  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Replace continent NaNs with \"NA\"\n",
    "df_airport.loc[df_airport['continent'].isna(), 'continent'] = 'NA'\n",
    "df_airport.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Clean cities data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "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>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Median Age</th>\n",
       "      <th>Male Population</th>\n",
       "      <th>Female Population</th>\n",
       "      <th>Total Population</th>\n",
       "      <th>Number of Veterans</th>\n",
       "      <th>Foreign-born</th>\n",
       "      <th>Average Household Size</th>\n",
       "      <th>State Code</th>\n",
       "      <th>Race</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Silver Spring</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>33.8</td>\n",
       "      <td>40601.0</td>\n",
       "      <td>41862.0</td>\n",
       "      <td>82463</td>\n",
       "      <td>1562.0</td>\n",
       "      <td>30908.0</td>\n",
       "      <td>2.60</td>\n",
       "      <td>MD</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>25924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Quincy</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>41.0</td>\n",
       "      <td>44129.0</td>\n",
       "      <td>49500.0</td>\n",
       "      <td>93629</td>\n",
       "      <td>4147.0</td>\n",
       "      <td>32935.0</td>\n",
       "      <td>2.39</td>\n",
       "      <td>MA</td>\n",
       "      <td>White</td>\n",
       "      <td>58723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Hoover</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>38.5</td>\n",
       "      <td>38040.0</td>\n",
       "      <td>46799.0</td>\n",
       "      <td>84839</td>\n",
       "      <td>4819.0</td>\n",
       "      <td>8229.0</td>\n",
       "      <td>2.58</td>\n",
       "      <td>AL</td>\n",
       "      <td>Asian</td>\n",
       "      <td>4759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Rancho Cucamonga</td>\n",
       "      <td>California</td>\n",
       "      <td>34.5</td>\n",
       "      <td>88127.0</td>\n",
       "      <td>87105.0</td>\n",
       "      <td>175232</td>\n",
       "      <td>5821.0</td>\n",
       "      <td>33878.0</td>\n",
       "      <td>3.18</td>\n",
       "      <td>CA</td>\n",
       "      <td>Black or African-American</td>\n",
       "      <td>24437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Newark</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>34.6</td>\n",
       "      <td>138040.0</td>\n",
       "      <td>143873.0</td>\n",
       "      <td>281913</td>\n",
       "      <td>5829.0</td>\n",
       "      <td>86253.0</td>\n",
       "      <td>2.73</td>\n",
       "      <td>NJ</td>\n",
       "      <td>White</td>\n",
       "      <td>76402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               City          State  Median Age  Male Population  \\\n",
       "0     Silver Spring       Maryland        33.8          40601.0   \n",
       "1            Quincy  Massachusetts        41.0          44129.0   \n",
       "2            Hoover        Alabama        38.5          38040.0   \n",
       "3  Rancho Cucamonga     California        34.5          88127.0   \n",
       "4            Newark     New Jersey        34.6         138040.0   \n",
       "\n",
       "   Female Population  Total Population  Number of Veterans  Foreign-born  \\\n",
       "0            41862.0             82463              1562.0       30908.0   \n",
       "1            49500.0             93629              4147.0       32935.0   \n",
       "2            46799.0             84839              4819.0        8229.0   \n",
       "3            87105.0            175232              5821.0       33878.0   \n",
       "4           143873.0            281913              5829.0       86253.0   \n",
       "\n",
       "   Average Household Size State Code                       Race  Count  \n",
       "0                    2.60         MD         Hispanic or Latino  25924  \n",
       "1                    2.39         MA                      White  58723  \n",
       "2                    2.58         AL                      Asian   4759  \n",
       "3                    3.18         CA  Black or African-American  24437  \n",
       "4                    2.73         NJ                      White  76402  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the data\n",
    "df_cities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "editable": true
   },
   "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>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Median Age</th>\n",
       "      <th>Male Population</th>\n",
       "      <th>Female Population</th>\n",
       "      <th>Total Population</th>\n",
       "      <th>Number of Veterans</th>\n",
       "      <th>Foreign-born</th>\n",
       "      <th>Average Household Size</th>\n",
       "      <th>State Code</th>\n",
       "      <th>Race</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>San Juan</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>41.4</td>\n",
       "      <td>155408.0</td>\n",
       "      <td>186829.0</td>\n",
       "      <td>342237</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>335559</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Caguas</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>40.4</td>\n",
       "      <td>34743.0</td>\n",
       "      <td>42265.0</td>\n",
       "      <td>77008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>76349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258</th>\n",
       "      <td>Carolina</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>42.0</td>\n",
       "      <td>64758.0</td>\n",
       "      <td>77308.0</td>\n",
       "      <td>142066</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>American Indian and Alaska Native</td>\n",
       "      <td>12143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>333</th>\n",
       "      <td>The Villages</td>\n",
       "      <td>Florida</td>\n",
       "      <td>70.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72590</td>\n",
       "      <td>15231.0</td>\n",
       "      <td>4034.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FL</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>1066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>The Villages</td>\n",
       "      <td>Florida</td>\n",
       "      <td>70.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72590</td>\n",
       "      <td>15231.0</td>\n",
       "      <td>4034.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FL</td>\n",
       "      <td>Black or African-American</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>637</th>\n",
       "      <td>Carolina</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>42.0</td>\n",
       "      <td>64758.0</td>\n",
       "      <td>77308.0</td>\n",
       "      <td>142066</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>139967</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1437</th>\n",
       "      <td>The Villages</td>\n",
       "      <td>Florida</td>\n",
       "      <td>70.5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72590</td>\n",
       "      <td>15231.0</td>\n",
       "      <td>4034.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FL</td>\n",
       "      <td>White</td>\n",
       "      <td>72211</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1747</th>\n",
       "      <td>San Juan</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>41.4</td>\n",
       "      <td>155408.0</td>\n",
       "      <td>186829.0</td>\n",
       "      <td>342237</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>American Indian and Alaska Native</td>\n",
       "      <td>4031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1748</th>\n",
       "      <td>Mayagüez</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>38.1</td>\n",
       "      <td>30799.0</td>\n",
       "      <td>35782.0</td>\n",
       "      <td>66581</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Asian</td>\n",
       "      <td>235</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1995</th>\n",
       "      <td>Ponce</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>40.5</td>\n",
       "      <td>56968.0</td>\n",
       "      <td>64615.0</td>\n",
       "      <td>121583</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>120705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>Bayamón</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>39.4</td>\n",
       "      <td>80128.0</td>\n",
       "      <td>90131.0</td>\n",
       "      <td>170259</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>169155</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2441</th>\n",
       "      <td>San Juan</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>41.4</td>\n",
       "      <td>155408.0</td>\n",
       "      <td>186829.0</td>\n",
       "      <td>342237</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Asian</td>\n",
       "      <td>2452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2589</th>\n",
       "      <td>Guaynabo</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>42.2</td>\n",
       "      <td>33066.0</td>\n",
       "      <td>37426.0</td>\n",
       "      <td>70492</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>69936</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2597</th>\n",
       "      <td>Caguas</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>40.4</td>\n",
       "      <td>34743.0</td>\n",
       "      <td>42265.0</td>\n",
       "      <td>77008</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>American Indian and Alaska Native</td>\n",
       "      <td>624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2654</th>\n",
       "      <td>Guaynabo</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>42.2</td>\n",
       "      <td>33066.0</td>\n",
       "      <td>37426.0</td>\n",
       "      <td>70492</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>American Indian and Alaska Native</td>\n",
       "      <td>589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2746</th>\n",
       "      <td>Mayagüez</td>\n",
       "      <td>Puerto Rico</td>\n",
       "      <td>38.1</td>\n",
       "      <td>30799.0</td>\n",
       "      <td>35782.0</td>\n",
       "      <td>66581</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PR</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>65521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              City        State  Median Age  Male Population  \\\n",
       "111       San Juan  Puerto Rico        41.4         155408.0   \n",
       "155         Caguas  Puerto Rico        40.4          34743.0   \n",
       "258       Carolina  Puerto Rico        42.0          64758.0   \n",
       "333   The Villages      Florida        70.5              NaN   \n",
       "449   The Villages      Florida        70.5              NaN   \n",
       "637       Carolina  Puerto Rico        42.0          64758.0   \n",
       "1437  The Villages      Florida        70.5              NaN   \n",
       "1747      San Juan  Puerto Rico        41.4         155408.0   \n",
       "1748      Mayagüez  Puerto Rico        38.1          30799.0   \n",
       "1995         Ponce  Puerto Rico        40.5          56968.0   \n",
       "2004       Bayamón  Puerto Rico        39.4          80128.0   \n",
       "2441      San Juan  Puerto Rico        41.4         155408.0   \n",
       "2589      Guaynabo  Puerto Rico        42.2          33066.0   \n",
       "2597        Caguas  Puerto Rico        40.4          34743.0   \n",
       "2654      Guaynabo  Puerto Rico        42.2          33066.0   \n",
       "2746      Mayagüez  Puerto Rico        38.1          30799.0   \n",
       "\n",
       "      Female Population  Total Population  Number of Veterans  Foreign-born  \\\n",
       "111            186829.0            342237                 NaN           NaN   \n",
       "155             42265.0             77008                 NaN           NaN   \n",
       "258             77308.0            142066                 NaN           NaN   \n",
       "333                 NaN             72590             15231.0        4034.0   \n",
       "449                 NaN             72590             15231.0        4034.0   \n",
       "637             77308.0            142066                 NaN           NaN   \n",
       "1437                NaN             72590             15231.0        4034.0   \n",
       "1747           186829.0            342237                 NaN           NaN   \n",
       "1748            35782.0             66581                 NaN           NaN   \n",
       "1995            64615.0            121583                 NaN           NaN   \n",
       "2004            90131.0            170259                 NaN           NaN   \n",
       "2441           186829.0            342237                 NaN           NaN   \n",
       "2589            37426.0             70492                 NaN           NaN   \n",
       "2597            42265.0             77008                 NaN           NaN   \n",
       "2654            37426.0             70492                 NaN           NaN   \n",
       "2746            35782.0             66581                 NaN           NaN   \n",
       "\n",
       "      Average Household Size State Code                               Race  \\\n",
       "111                      NaN         PR                 Hispanic or Latino   \n",
       "155                      NaN         PR                 Hispanic or Latino   \n",
       "258                      NaN         PR  American Indian and Alaska Native   \n",
       "333                      NaN         FL                 Hispanic or Latino   \n",
       "449                      NaN         FL          Black or African-American   \n",
       "637                      NaN         PR                 Hispanic or Latino   \n",
       "1437                     NaN         FL                              White   \n",
       "1747                     NaN         PR  American Indian and Alaska Native   \n",
       "1748                     NaN         PR                              Asian   \n",
       "1995                     NaN         PR                 Hispanic or Latino   \n",
       "2004                     NaN         PR                 Hispanic or Latino   \n",
       "2441                     NaN         PR                              Asian   \n",
       "2589                     NaN         PR                 Hispanic or Latino   \n",
       "2597                     NaN         PR  American Indian and Alaska Native   \n",
       "2654                     NaN         PR  American Indian and Alaska Native   \n",
       "2746                     NaN         PR                 Hispanic or Latino   \n",
       "\n",
       "       Count  \n",
       "111   335559  \n",
       "155    76349  \n",
       "258    12143  \n",
       "333     1066  \n",
       "449      331  \n",
       "637   139967  \n",
       "1437   72211  \n",
       "1747    4031  \n",
       "1748     235  \n",
       "1995  120705  \n",
       "2004  169155  \n",
       "2441    2452  \n",
       "2589   69936  \n",
       "2597     624  \n",
       "2654     589  \n",
       "2746   65521  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# There are a few NaNs but nothing serious\n",
    "df_cities[df_cities.isna().any(axis=1)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Clean immigration data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>...</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2027561</td>\n",
       "      <td>4084316.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>HHW</td>\n",
       "      <td>20566.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HI</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1955.0</td>\n",
       "      <td>07202016</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JL</td>\n",
       "      <td>5.658267e+10</td>\n",
       "      <td>00782</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2171295</td>\n",
       "      <td>4422636.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>MCA</td>\n",
       "      <td>20567.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>TX</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>10222016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*GA</td>\n",
       "      <td>9.436200e+10</td>\n",
       "      <td>XBLNG</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>589494</td>\n",
       "      <td>1195600.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>OGG</td>\n",
       "      <td>20551.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>FL</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>07052016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LH</td>\n",
       "      <td>5.578047e+10</td>\n",
       "      <td>00464</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2631158</td>\n",
       "      <td>5291768.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>LOS</td>\n",
       "      <td>20572.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>CA</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>10272016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QR</td>\n",
       "      <td>9.478970e+10</td>\n",
       "      <td>00739</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3032257</td>\n",
       "      <td>985523.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>CHM</td>\n",
       "      <td>20550.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>07042016</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.232257e+10</td>\n",
       "      <td>LAND</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0      cicid   i94yr  i94mon  i94cit  i94res i94port  arrdate  \\\n",
       "0     2027561  4084316.0  2016.0     4.0   209.0   209.0     HHW  20566.0   \n",
       "1     2171295  4422636.0  2016.0     4.0   582.0   582.0     MCA  20567.0   \n",
       "2      589494  1195600.0  2016.0     4.0   148.0   112.0     OGG  20551.0   \n",
       "3     2631158  5291768.0  2016.0     4.0   297.0   297.0     LOS  20572.0   \n",
       "4     3032257   985523.0  2016.0     4.0   111.0   111.0     CHM  20550.0   \n",
       "\n",
       "   i94mode i94addr    ...     entdepu  matflag  biryear   dtaddto  gender  \\\n",
       "0      1.0      HI    ...         NaN        M   1955.0  07202016       F   \n",
       "1      1.0      TX    ...         NaN        M   1990.0  10222016       M   \n",
       "2      1.0      FL    ...         NaN        M   1940.0  07052016       M   \n",
       "3      1.0      CA    ...         NaN        M   1991.0  10272016       M   \n",
       "4      3.0      NY    ...         NaN        M   1997.0  07042016       F   \n",
       "\n",
       "  insnum airline        admnum  fltno  visatype  \n",
       "0    NaN      JL  5.658267e+10  00782        WT  \n",
       "1    NaN     *GA  9.436200e+10  XBLNG        B2  \n",
       "2    NaN      LH  5.578047e+10  00464        WT  \n",
       "3    NaN      QR  9.478970e+10  00739        B2  \n",
       "4    NaN     NaN  4.232257e+10   LAND        WT  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show the data\n",
    "df_immigration.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'cicid', 'i94yr', 'i94mon', 'i94cit', 'i94res', 'i94port',\n",
       "       'arrdate', 'i94mode', 'i94addr', 'depdate', 'i94bir', 'i94visa',\n",
       "       'count', 'dtadfile', 'visapost', 'occup', 'entdepa', 'entdepd',\n",
       "       'entdepu', 'matflag', 'biryear', 'dtaddto', 'gender', 'insnum',\n",
       "       'airline', 'admnum', 'fltno', 'visatype'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_immigration.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "Columns:\n",
    "- 'Unnamed: 0' = an ID?\n",
    "- 'cicid' = an ID?\n",
    "- 'i94yr' = year of record\n",
    "- 'i94mon' = month of record\n",
    "- 'i94cit' = citizenship of person (country)\n",
    "- 'i94res' = residency of person (country)\n",
    "- 'i94port = airport code\n",
    "- 'arrdate' = arrival date\n",
    "- 'i94mode' = mode of travel (land, air, sea unknown)\n",
    "- 'i94addr' = state, of person ?\n",
    "- 'depdate' = departure data\n",
    "- 'i94bir' = age of person\n",
    "- 'i94visa' = reason for travel (business, pleasure, student)\n",
    "- 'count' = statistics\n",
    "- 'dtadfile' = \n",
    "- 'visapost' =\n",
    "- 'occup' = Occupation that will be performed in U.S.\n",
    "- 'entdepa' = Department of State where where Visa was issued\n",
    "- 'entdepd'\n",
    "- 'entdepu'\n",
    "- 'matflag'\n",
    "- 'biryear'\n",
    "- 'dtaddto'\n",
    "- 'gender'\n",
    "- 'insnum'\n",
    "- 'airline'\n",
    "- 'admnum'\n",
    "- 'fltno'\n",
    "- 'visatype'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "editable": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>...</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2027561</td>\n",
       "      <td>4084316.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>HHW</td>\n",
       "      <td>2016-04-22</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HI</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1955.0</td>\n",
       "      <td>07202016</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>JL</td>\n",
       "      <td>5.658267e+10</td>\n",
       "      <td>00782</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2171295</td>\n",
       "      <td>4422636.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>MCA</td>\n",
       "      <td>2016-04-23</td>\n",
       "      <td>1.0</td>\n",
       "      <td>TX</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1990.0</td>\n",
       "      <td>10222016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>*GA</td>\n",
       "      <td>9.436200e+10</td>\n",
       "      <td>XBLNG</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>589494</td>\n",
       "      <td>1195600.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>OGG</td>\n",
       "      <td>2016-04-07</td>\n",
       "      <td>1.0</td>\n",
       "      <td>FL</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1940.0</td>\n",
       "      <td>07052016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LH</td>\n",
       "      <td>5.578047e+10</td>\n",
       "      <td>00464</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2631158</td>\n",
       "      <td>5291768.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>LOS</td>\n",
       "      <td>2016-04-28</td>\n",
       "      <td>1.0</td>\n",
       "      <td>CA</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>10272016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>QR</td>\n",
       "      <td>9.478970e+10</td>\n",
       "      <td>00739</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3032257</td>\n",
       "      <td>985523.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>CHM</td>\n",
       "      <td>2016-04-06</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NY</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1997.0</td>\n",
       "      <td>07042016</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.232257e+10</td>\n",
       "      <td>LAND</td>\n",
       "      <td>WT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0      cicid   i94yr  i94mon  i94cit  i94res i94port    arrdate  \\\n",
       "0     2027561  4084316.0  2016.0     4.0   209.0   209.0     HHW 2016-04-22   \n",
       "1     2171295  4422636.0  2016.0     4.0   582.0   582.0     MCA 2016-04-23   \n",
       "2      589494  1195600.0  2016.0     4.0   148.0   112.0     OGG 2016-04-07   \n",
       "3     2631158  5291768.0  2016.0     4.0   297.0   297.0     LOS 2016-04-28   \n",
       "4     3032257   985523.0  2016.0     4.0   111.0   111.0     CHM 2016-04-06   \n",
       "\n",
       "   i94mode i94addr    ...    entdepu  matflag  biryear   dtaddto  gender  \\\n",
       "0      1.0      HI    ...        NaN        M   1955.0  07202016       F   \n",
       "1      1.0      TX    ...        NaN        M   1990.0  10222016       M   \n",
       "2      1.0      FL    ...        NaN        M   1940.0  07052016       M   \n",
       "3      1.0      CA    ...        NaN        M   1991.0  10272016       M   \n",
       "4      3.0      NY    ...        NaN        M   1997.0  07042016       F   \n",
       "\n",
       "  insnum airline        admnum  fltno  visatype  \n",
       "0    NaN      JL  5.658267e+10  00782        WT  \n",
       "1    NaN     *GA  9.436200e+10  XBLNG        B2  \n",
       "2    NaN      LH  5.578047e+10  00464        WT  \n",
       "3    NaN      QR  9.478970e+10  00739        B2  \n",
       "4    NaN     NaN  4.232257e+10   LAND        WT  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Convert SAS date numeric fields to datetime\n",
    "df_immigration['arrdate'] = pd.to_timedelta(df_immigration['arrdate'], unit='D') + pd.Timestamp('1960-1-1')\n",
    "df_immigration['depdate'] = pd.to_timedelta(df_immigration['depdate'], unit='D') + pd.Timestamp('1960-1-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "editable": true
   },
   "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>Unnamed: 0</th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>...</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [Unnamed: 0, cicid, i94yr, i94mon, i94cit, i94res, i94port, arrdate, i94mode, i94addr, depdate, i94bir, i94visa, count, dtadfile, visapost, occup, entdepa, entdepd, entdepu, matflag, biryear, dtaddto, gender, insnum, airline, admnum, fltno, visatype]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 29 columns]"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_immigration[df_immigration['cicid'].duplicated()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 3: Define the Data Model\n",
    "#### 3.1 Conceptual Data Model\n",
    "Map out the conceptual data model and explain why you chose that model\n",
    "\n",
    "#### 3.2 Mapping Out Data Pipelines\n",
    "List the steps necessary to pipeline the data into the chosen data model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 4: Run Pipelines to Model the Data \n",
    "#### 4.1 Create the data model\n",
    "Build the data pipelines to create the data model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Write code here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 4.2 Data Quality Checks\n",
    "Explain the data quality checks you'll perform to ensure the pipeline ran as expected. These could include:\n",
    " * Integrity constraints on the relational database (e.g., unique key, data type, etc.)\n",
    " * Unit tests for the scripts to ensure they are doing the right thing\n",
    " * Source/Count checks to ensure completeness\n",
    " \n",
    "Run Quality Checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Perform quality checks here"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 4.3 Data dictionary \n",
    "Create a data dictionary for your data model. For each field, provide a brief description of what the data is and where it came from. You can include the data dictionary in the notebook or in a separate file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Step 5: Complete Project Write Up\n",
    "* Clearly state the rationale for the choice of tools and technologies for the project.\n",
    "* Propose how often the data should be updated and why.\n",
    "* Write a description of how you would approach the problem differently under the following scenarios:\n",
    " * The data was increased by 100x.\n",
    " * The data populates a dashboard that must be updated on a daily basis by 7am every day.\n",
    " * The database needed to be accessed by 100+ people."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}