capstone_project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Engineering of Immigration Data in the United States\n",
    "### Data Engineering Capstone Project\n",
    "\n",
    "#### Project Summary\n",
    "In this project, we will conduct a study on Immigration Data in the united states with regards to World Temperature Data, U.S. City Demographic Data, and Airport Code Data. That is to answer questions such as, what is the percentage of people that immigrate to the U.S from countries with cooler weather?, etc..\n",
    "\n",
    "The goal is to create ETL pipelines that extract each dataset from the source, apply several levels of transformation, then make it ready to be loaded into an analytics database (data warehouse) with a new consolidated data model that serves the analysis and study needs.\n",
    "\n",
    "The project follows the following 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": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Do all imports and installs here\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from pyspark.sql.types import DoubleType, StringType, IntegerType, FloatType, DateType, StructType, StructField\n",
    "\n",
    "import utils, schema_tables, clean_tables\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "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?**\n",
    "\n",
    "The scope of the project is to create a data warehouse that is suitable for analysis purposes using Apache Spark. It includes extracting data from several sources, transforming each dataset to a specific form, then loading them into a new data model.\n",
    "\n",
    "This data warehouse will be used to analyze the U.S. immigration data regarding several perspectives, such as world tempreture, demographic data, and others. In the end, every step needed to create the new data model will be pipelined to make it reusable and consistent.\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?**\n",
    "\n",
    "Here is an outline about the datasets:\n",
    "- **I94 Immigration Data:** This data comes from the US National Tourism and Trade Office. [This](https://travel.trade.gov/research/reports/i94/historical/2016.html) is where the data comes from. There's a sample file so you can take a look at the data in csv format before reading it all in. You do not have to use the entire dataset, just use what you need to accomplish the goal you set at the beginning of the project.\n",
    "- **World Temperature Data:** This dataset came from Kaggle. You can read more about it [here](https://www.kaggle.com/berkeleyearth/climate-change-earth-surface-temperature-data).\n",
    "- **U.S. City Demographic Data:** This data comes from OpenSoft. You can read more about it [here](https://public.opendatasoft.com/explore/dataset/us-cities-demographics/export/).\n",
    "- **Airport Code Table:** This is a simple table of airport codes and corresponding cities. It comes from [here](https://datahub.io/core/airport-codes#data)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "\n",
    "# create spark session\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')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3096313"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# size of the immigration data\n",
    "df_spark.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Immigration Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Read in a sample Immigration data \n",
    "immigration_df_sample = pd.read_csv('./data/immigration_data_sample.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>dtadfile</th>\n",
       "      <th>visapost</th>\n",
       "      <th>occup</th>\n",
       "      <th>entdepa</th>\n",
       "      <th>entdepd</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>20573.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160422</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>O</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>20568.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160423</td>\n",
       "      <td>MTR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>R</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>20571.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>O</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>20581.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160428</td>\n",
       "      <td>DOH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>O</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>20553.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160406</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Z</td>\n",
       "      <td>K</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",
       "</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  depdate  i94bir  i94visa  count  dtadfile visapost occup  \\\n",
       "0      1.0      HI  20573.0    61.0      2.0    1.0  20160422      NaN   NaN   \n",
       "1      1.0      TX  20568.0    26.0      2.0    1.0  20160423      MTR   NaN   \n",
       "2      1.0      FL  20571.0    76.0      2.0    1.0  20160407      NaN   NaN   \n",
       "3      1.0      CA  20581.0    25.0      2.0    1.0  20160428      DOH   NaN   \n",
       "4      3.0      NY  20553.0    19.0      2.0    1.0  20160406      NaN   NaN   \n",
       "\n",
       "  entdepa entdepd  entdepu matflag  biryear   dtaddto gender  insnum airline  \\\n",
       "0       G       O      NaN       M   1955.0  07202016      F     NaN      JL   \n",
       "1       G       R      NaN       M   1990.0  10222016      M     NaN     *GA   \n",
       "2       G       O      NaN       M   1940.0  07052016      M     NaN      LH   \n",
       "3       G       O      NaN       M   1991.0  10272016      M     NaN      QR   \n",
       "4       Z       K      NaN       M   1997.0  07042016      F     NaN     NaN   \n",
       "\n",
       "         admnum  fltno visatype  \n",
       "0  5.658267e+10  00782       WT  \n",
       "1  9.436200e+10  XBLNG       B2  \n",
       "2  5.578047e+10  00464       WT  \n",
       "3  9.478970e+10  00739       B2  \n",
       "4  4.232257e+10   LAND       WT  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max_columns', None)\n",
    "immigration_df_sample.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 29)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immigration_df_sample.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Data Dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "| Feature  |Description\n",
    "| --------|-----------\n",
    "| cicid    |Unique record ID\n",
    "| i94yr    |year\n",
    "| i94mon   |month of the arrival\n",
    "| i94cit   |3 digit code for immigrant country of citizenship\n",
    "| i94res   |3 digit code for immigrant country of residence\n",
    "| i94port  |Port of admission\n",
    "| arrdate  |Arrival Date in the USA\n",
    "| i94mode  |Mode of transportation (1 = Air; 2 = Sea; 3 = Land; 9 = Not reported)\n",
    "| i94addr  |USA State of arrival\n",
    "| depdate  |Departure Date from the USA\n",
    "| i94bir   |Age of Respondent in Years\n",
    "| i94visa  |Visa codes collapsed into three categories\n",
    "| count    |Field used for summary statistics\n",
    "| dtadfile |Character Date Field - Date added to I-94 Files\n",
    "| visapost |Department of State where Visa was issued\n",
    "| occup    |Occupation that will be performed in U.S\n",
    "| entdepa  |Arrival Flag - admitted or paroled into the U.S.and others\n",
    "| entdepd  |Departure Flag - Departed, lost I-94 or is deceased and others\n",
    "| entdepu  |Update Flag - Either apprehended, overstayed, adjusted to perm residence\n",
    "| matflag  |Match Flag - Match of arrival and departure records\n",
    "| biryear  |4 digit year of birth\n",
    "| dtaddto  |Character Date Field - Date to which admitted to U.S. (allowed to stay until)\n",
    "| gender   |Non-immigrant sex\n",
    "| insnum   |INS number\n",
    "| airline  |Airline used to arrive in U.S.\n",
    "| admnum   |Admission Number\n",
    "| fltno    |Flight number of Airline used to arrive in U.S.\n",
    "| visatype |Class of admission legally admitting the non-immigrant to temporarily stay in U.S."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "This dataset forms the core of the data warehouse and the provider repository has a year worth of data for the year 2016 and it is divided by month. For this project we have chosen going to work with data for the month of April. However, the ETL pipeline functions have been designed to work with any month's worth of data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# read immaigration data labels Descriptions\n",
    "i94cit_res, i94port, i94mode, i94addr, i94visa = utils.read_immigration_labels('./I94_SAS_Labels_Descriptions.SAS')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "The meaning of the flags in the columns *entdepa, entdepd, and entdepu* should be referred like below:\n",
    "- G -> Admitted into US\n",
    "- O -> Paroled into US\n",
    "- R -> Departed\n",
    "- K -> Lost I 94 or is deceased\n",
    "- N -> Apprehended\n",
    "- T -> Overstayed\n",
    "- Z -> Adjusted to perm residence"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Temperature data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# read temperature data\n",
    "fname = '../../data2/GlobalLandTemperaturesByCity.csv'\n",
    "temperature_df = pd.read_csv(fname)"
   ]
  },
  {
   "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>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": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temperature_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8599212, 7)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temperature_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Data Dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "| Feature | Description\n",
    "| --------|-----------\n",
    "| dt | Date\n",
    "| AverageTemperature | Average temperature in celsius\n",
    "| AverageTemperatureUncertainty | 95% confidence interval around average temperature\n",
    "| City | Name of city\n",
    "| Country | Name of country\n",
    "| Latitude | Latitude of city\n",
    "| Longitude | Longitude of city\n",
    "\n",
    "\n",
    "This dataset contains temperature data in various cities from the 1700’s to 2013. Although the data is only recorded until 2013, we can use this as an estimate of future years' temperature."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### U.S. City Demographic Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# read demographic data\n",
    "demographics_df = pd.read_csv('./data/us-cities-demographics.csv', sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demographics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2891, 12)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demographics_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Data Dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "| Feature\t| Description\n",
    "| --------|-----------\n",
    "| City\t| City Name\n",
    "| State\t| US State of the City\n",
    "| Median Age\t| The median population age\n",
    "| Male Population\t| Male population total\n",
    "| Female Population\t| Female population total\n",
    "| Total Population\t| Total population\n",
    "| Number of Veterans\t| Number of veterans living in the city\n",
    "| Foreign-born\t| Number of residents who were not born in the city\n",
    "| Average Household Size\t| Average size of houses in the city\n",
    "| State Code\t| Code of the state\n",
    "| Race\t| Race class\n",
    "| Count\t| Number of individuals in each race"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Airport Codes Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# read airport codes data\n",
    "airport_df = pd.read_csv('./data/airport-codes_csv.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(55075, 12)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Data Dictionary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "| Feature\t| Description\n",
    "| --------|-----------\n",
    "| ident\t| Unique ID\n",
    "| type\t| Airport type\n",
    "| name\t| Airport name\n",
    "| elevation_ft\t| Airport altitude\n",
    "| continent\t| Continent\n",
    "| iso_country\t| ISO Code of the airport's country\n",
    "| iso_region\t| ISO Code for the airport's region\n",
    "| municipality\t| City/Municipality where the airport is located\n",
    "| gps_code\t| Airport GPS Code\n",
    "| iata_code\t| Airport IATA Code\n",
    "| local_code\t| Airport local code\n",
    "| coordinates\t| Airport coordinates"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 2: Explore and Assess the Data\n",
    "#### Exploring & Cleaning the Data \n",
    "Identify data quality issues, like missing values, duplicate data, etc.\n",
    "\n",
    "Document steps necessary to clean the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Immigration Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>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>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>dtadfile</th>\n",
       "      <th>visapost</th>\n",
       "      <th>occup</th>\n",
       "      <th>entdepa</th>\n",
       "      <th>entdepd</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>6.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>692.0</td>\n",
       "      <td>692.0</td>\n",
       "      <td>XXX</td>\n",
       "      <td>20573.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>T</td>\n",
       "      <td>None</td>\n",
       "      <td>U</td>\n",
       "      <td>None</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>10282016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.897628e+09</td>\n",
       "      <td>None</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>254.0</td>\n",
       "      <td>276.0</td>\n",
       "      <td>ATL</td>\n",
       "      <td>20551.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20130811</td>\n",
       "      <td>SEO</td>\n",
       "      <td>None</td>\n",
       "      <td>G</td>\n",
       "      <td>None</td>\n",
       "      <td>Y</td>\n",
       "      <td>None</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>D/S</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>3.736796e+09</td>\n",
       "      <td>00296</td>\n",
       "      <td>F1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>WAS</td>\n",
       "      <td>20545.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MI</td>\n",
       "      <td>20691.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>T</td>\n",
       "      <td>O</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>M</td>\n",
       "      <td>None</td>\n",
       "      <td>OS</td>\n",
       "      <td>6.666432e+08</td>\n",
       "      <td>93</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>101.0</td>\n",
       "      <td>NYC</td>\n",
       "      <td>20545.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>MA</td>\n",
       "      <td>20567.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>AA</td>\n",
       "      <td>9.246846e+10</td>\n",
       "      <td>00199</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cicid   i94yr  i94mon  i94cit  i94res i94port  arrdate  i94mode i94addr  \\\n",
       "0    6.0  2016.0     4.0   692.0   692.0     XXX  20573.0      NaN    None   \n",
       "1    7.0  2016.0     4.0   254.0   276.0     ATL  20551.0      1.0      AL   \n",
       "2   15.0  2016.0     4.0   101.0   101.0     WAS  20545.0      1.0      MI   \n",
       "3   16.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MA   \n",
       "\n",
       "   depdate  i94bir  i94visa  count  dtadfile visapost occup entdepa entdepd  \\\n",
       "0      NaN    37.0      2.0    1.0      None     None  None       T    None   \n",
       "1      NaN    25.0      3.0    1.0  20130811      SEO  None       G    None   \n",
       "2  20691.0    55.0      2.0    1.0  20160401     None  None       T       O   \n",
       "3  20567.0    28.0      2.0    1.0  20160401     None  None       O       O   \n",
       "\n",
       "  entdepu matflag  biryear   dtaddto gender insnum airline        admnum  \\\n",
       "0       U    None   1979.0  10282016   None   None    None  1.897628e+09   \n",
       "1       Y    None   1991.0       D/S      M   None    None  3.736796e+09   \n",
       "2    None       M   1961.0  09302016      M   None      OS  6.666432e+08   \n",
       "3    None       M   1988.0  09302016   None   None      AA  9.246846e+10   \n",
       "\n",
       "   fltno visatype  \n",
       "0   None       B2  \n",
       "1  00296       F1  \n",
       "2     93       B2  \n",
       "3  00199       B2  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_spark.limit(4).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- cicid: double (nullable = true)\n",
      " |-- i94yr: double (nullable = true)\n",
      " |-- i94mon: double (nullable = true)\n",
      " |-- i94cit: double (nullable = true)\n",
      " |-- i94res: double (nullable = true)\n",
      " |-- i94port: string (nullable = true)\n",
      " |-- arrdate: double (nullable = true)\n",
      " |-- i94mode: double (nullable = true)\n",
      " |-- i94addr: string (nullable = true)\n",
      " |-- depdate: double (nullable = true)\n",
      " |-- i94bir: double (nullable = true)\n",
      " |-- i94visa: double (nullable = true)\n",
      " |-- count: double (nullable = true)\n",
      " |-- dtadfile: string (nullable = true)\n",
      " |-- visapost: string (nullable = true)\n",
      " |-- occup: string (nullable = true)\n",
      " |-- entdepa: string (nullable = true)\n",
      " |-- entdepd: string (nullable = true)\n",
      " |-- entdepu: string (nullable = true)\n",
      " |-- matflag: string (nullable = true)\n",
      " |-- biryear: double (nullable = true)\n",
      " |-- dtaddto: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- insnum: string (nullable = true)\n",
      " |-- airline: string (nullable = true)\n",
      " |-- admnum: double (nullable = true)\n",
      " |-- fltno: string (nullable = true)\n",
      " |-- visatype: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# show the immigration data schema\n",
    "df_spark.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>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>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>dtadfile</th>\n",
       "      <th>visapost</th>\n",
       "      <th>occup</th>\n",
       "      <th>entdepa</th>\n",
       "      <th>entdepd</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>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.007719</td>\n",
       "      <td>4.928184</td>\n",
       "      <td>4.600859</td>\n",
       "      <td>0.025902</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000032</td>\n",
       "      <td>60.757746</td>\n",
       "      <td>99.737559</td>\n",
       "      <td>0.007687</td>\n",
       "      <td>4.470769</td>\n",
       "      <td>99.98734</td>\n",
       "      <td>4.470769</td>\n",
       "      <td>0.025902</td>\n",
       "      <td>0.015405</td>\n",
       "      <td>13.379429</td>\n",
       "      <td>96.327632</td>\n",
       "      <td>2.700857</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.631364</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cicid  i94yr  i94mon  i94cit  i94res  i94port  arrdate   i94mode   i94addr  \\\n",
       "0    0.0    0.0     0.0     0.0     0.0      0.0      0.0  0.007719  4.928184   \n",
       "\n",
       "    depdate    i94bir  i94visa  count  dtadfile   visapost      occup  \\\n",
       "0  4.600859  0.025902      0.0    0.0  0.000032  60.757746  99.737559   \n",
       "\n",
       "    entdepa   entdepd   entdepu   matflag   biryear   dtaddto     gender  \\\n",
       "0  0.007687  4.470769  99.98734  4.470769  0.025902  0.015405  13.379429   \n",
       "\n",
       "      insnum   airline  admnum     fltno  visatype  \n",
       "0  96.327632  2.700857     0.0  0.631364       0.0  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# percentage of null values (drop columns with more than 90% null values)\n",
    "(df_spark.select([count(when(col(c).contains('None') | \\\n",
    "                            col(c).contains('NULL') | \\\n",
    "                            (col(c) == '' ) | \\\n",
    "                            col(c).isNull() | \\\n",
    "                            isnan(c), c \n",
    "                           )).alias(c)\n",
    "                    for c in df_spark.columns]).toPandas() / df_spark.count()) * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# number of duplicates rows (drop duplictaed rows)\n",
    "df_spark.distinct().count() == df_spark.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>dtadfile</th>\n",
       "      <th>visapost</th>\n",
       "      <th>occup</th>\n",
       "      <th>entdepa</th>\n",
       "      <th>entdepd</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>20573.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160422</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>O</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>20568.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160423</td>\n",
       "      <td>MTR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>R</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>20571.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160407</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>O</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>20581.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160428</td>\n",
       "      <td>DOH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>O</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>20553.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160406</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Z</td>\n",
       "      <td>K</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",
       "</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  depdate  i94bir  i94visa  count  dtadfile visapost occup  \\\n",
       "0      1.0      HI  20573.0    61.0      2.0    1.0  20160422      NaN   NaN   \n",
       "1      1.0      TX  20568.0    26.0      2.0    1.0  20160423      MTR   NaN   \n",
       "2      1.0      FL  20571.0    76.0      2.0    1.0  20160407      NaN   NaN   \n",
       "3      1.0      CA  20581.0    25.0      2.0    1.0  20160428      DOH   NaN   \n",
       "4      3.0      NY  20553.0    19.0      2.0    1.0  20160406      NaN   NaN   \n",
       "\n",
       "  entdepa entdepd  entdepu matflag  biryear   dtaddto gender  insnum airline  \\\n",
       "0       G       O      NaN       M   1955.0  07202016      F     NaN      JL   \n",
       "1       G       R      NaN       M   1990.0  10222016      M     NaN     *GA   \n",
       "2       G       O      NaN       M   1940.0  07052016      M     NaN      LH   \n",
       "3       G       O      NaN       M   1991.0  10272016      M     NaN      QR   \n",
       "4       Z       K      NaN       M   1997.0  07042016      F     NaN     NaN   \n",
       "\n",
       "         admnum  fltno visatype  \n",
       "0  5.658267e+10  00782       WT  \n",
       "1  9.436200e+10  XBLNG       B2  \n",
       "2  5.578047e+10  00464       WT  \n",
       "3  9.478970e+10  00739       B2  \n",
       "4  4.232257e+10   LAND       WT  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immigration_df_sample.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 29 columns):\n",
      "Unnamed: 0    1000 non-null int64\n",
      "cicid         1000 non-null float64\n",
      "i94yr         1000 non-null float64\n",
      "i94mon        1000 non-null float64\n",
      "i94cit        1000 non-null float64\n",
      "i94res        1000 non-null float64\n",
      "i94port       1000 non-null object\n",
      "arrdate       1000 non-null float64\n",
      "i94mode       1000 non-null float64\n",
      "i94addr       941 non-null object\n",
      "depdate       951 non-null float64\n",
      "i94bir        1000 non-null float64\n",
      "i94visa       1000 non-null float64\n",
      "count         1000 non-null float64\n",
      "dtadfile      1000 non-null int64\n",
      "visapost      382 non-null object\n",
      "occup         4 non-null object\n",
      "entdepa       1000 non-null object\n",
      "entdepd       954 non-null object\n",
      "entdepu       0 non-null float64\n",
      "matflag       954 non-null object\n",
      "biryear       1000 non-null float64\n",
      "dtaddto       1000 non-null object\n",
      "gender        859 non-null object\n",
      "insnum        35 non-null float64\n",
      "airline       967 non-null object\n",
      "admnum        1000 non-null float64\n",
      "fltno         992 non-null object\n",
      "visatype      1000 non-null object\n",
      "dtypes: float64(15), int64(2), object(12)\n",
      "memory usage: 226.6+ KB\n"
     ]
    }
   ],
   "source": [
    "# quick view of the data info.\n",
    "immigration_df_sample.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of null values:\n",
      "Unnamed: 0      0.0\n",
      "cicid           0.0\n",
      "i94yr           0.0\n",
      "i94mon          0.0\n",
      "i94cit          0.0\n",
      "i94res          0.0\n",
      "i94port         0.0\n",
      "arrdate         0.0\n",
      "i94mode         0.0\n",
      "i94addr         5.9\n",
      "depdate         4.9\n",
      "i94bir          0.0\n",
      "i94visa         0.0\n",
      "count           0.0\n",
      "dtadfile        0.0\n",
      "visapost       61.8\n",
      "occup          99.6\n",
      "entdepa         0.0\n",
      "entdepd         4.6\n",
      "entdepu       100.0\n",
      "matflag         4.6\n",
      "biryear         0.0\n",
      "dtaddto         0.0\n",
      "gender         14.1\n",
      "insnum         96.5\n",
      "airline         3.3\n",
      "admnum          0.0\n",
      "fltno           0.8\n",
      "visatype        0.0\n",
      "\n",
      "Columns to be dropped: ['occup', 'entdepu', 'insnum']\n",
      "\n",
      "Number of duplicate rows: 0\n",
      "\n",
      "Number of rows where all values are missing: 0\n",
      "\n",
      "Drop miss index columns if exist (Unnamed): ['Unnamed: 0']\n"
     ]
    }
   ],
   "source": [
    "# clean Immigration_df_sample table\n",
    "clean_immigration_df_sample = clean_tables.clean_immigration_table(immigration_df_sample, process_spark=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Temperature data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temperature_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('1743-11-01', '2013-09-01')"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# min and max date\n",
    "temperature_df['dt'].min(), temperature_df['dt'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 8599212 entries, 0 to 8599211\n",
      "Data columns (total 7 columns):\n",
      "dt                               object\n",
      "AverageTemperature               float64\n",
      "AverageTemperatureUncertainty    float64\n",
      "City                             object\n",
      "Country                          object\n",
      "Latitude                         object\n",
      "Longitude                        object\n",
      "dtypes: float64(2), object(5)\n",
      "memory usage: 459.2+ MB\n"
     ]
    }
   ],
   "source": [
    "# quick view of the data info.\n",
    "temperature_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of null values:\n",
      "dt                               0.0\n",
      "AverageTemperature               0.0\n",
      "AverageTemperatureUncertainty    0.0\n",
      "City                             0.0\n",
      "Country                          0.0\n",
      "Latitude                         0.0\n",
      "Longitude                        0.0\n",
      "\n",
      "No columns to be dropped\n",
      "\n",
      "Number of duplicate rows: 0\n",
      "\n",
      "Number of rows where all values are missing: 0\n",
      "\n",
      "Drop miss index columns if exist (Unnamed): []\n"
     ]
    }
   ],
   "source": [
    "# clean temperature table\n",
    "clean_temperature_df = clean_tables.clean_temperature_table(temperature_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYcAAAD8CAYAAACcjGjIAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvpW3flQAAFNhJREFUeJzt3X+MXeV95/H3tzY/LAgxhGTkta01VaxV3HibwAgsZbeahAgMiWIqBckRCibLylJEtqnWUmMabVGTIJFWNC1SmqwVrJgoqUPTRnjBqeMSrqpK4WchGOJQTwgKXiys1oYySUt20u/+cZ9RT+e5M3M9zMw5hvdLuppznvPcO5+ZO+PPnB/3OjITSZKafqXtAJKk7rEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVFnedoD5uvDCC3PdunVtx6j87Gc/45xzzmk7xqy6nrHr+cCMC6Hr+eD1l/Gxxx77h8x861CTM/O0vF1yySXZRQ888EDbEebU9Yxdz5dpxoXQ9XyZr7+MwKM55L+xHlaSJFUsB0lSxXKQJFUsB0lSxXKQJFUsB0lSxXKQJFUsB0lSxXKQJFVO27fPkKR1O+9b1MffsXGSG2b4HM/d9oFF/dxtc89BklSxHCRJFctBklSxHCRJFctBklSxHCRJFctBklSxHCRJFctBklSxHCRJFctBklSxHCRJFctBklSxHCRJFctBklSxHCRJFctBklQZqhwi4rmIOBQRT0TEo2Xsgog4GBFHysfzy3hExB0RMR4RT0bExY3H2VbmH4mIbY3xS8rjj5f7xkJ/oZKk4Z3KnsN7M/NdmTla1ncC92fmeuD+sg5wFbC+3LYDX4J+mQC3AJcBlwK3TBVKmbO9cb/N8/6KJEmv2Ws5rLQF2FOW9wDXNMbvyr4HgZURsQq4EjiYmScy8yRwENhctp2Xmd/PzATuajyWJKkF0f/3eI5JET8BTgIJ/O/M3BURL2Xmysack5l5fkTcC9yWmX9bxu8HPgWMAWdn5ufK+P8C/hnolfnvL+P/FfhUZn5wQI7t9PcwGBkZuWTv3r3z/sIXy8TEBOeee27bMWbV9YxdzwdmXAgLke/Q/315gdIMNrICXvznwds2rn7zon7uYZ3K9/G9733vY42jP7NaPuTnf09mvhARbwMORsSPZpk76HxBzmO8HszcBewCGB0dzbGxsVlDt6HX69HFXE1dz9j1fGDGhbAQ+W7Yed/ChJnBjo2T3H5o8D+Tz103tqife1iL9TwPdVgpM18oH48D36Z/zuDFckiI8vF4mX4UWNu4+xrghTnG1wwYlyS1ZM5yiIhzIuJNU8vAFcBTwD5g6oqjbcA9ZXkfcH25amkT8HJmHgMOAFdExPnlRPQVwIGy7ZWI2FSuUrq+8ViSpBYMc1hpBPh2ubp0OfCNzPyriHgEuDsibgR+Clxb5u8HrgbGgZ8DHwPIzBMR8VngkTLvM5l5oix/HPgqsAL4TrlJkloyZzlk5rPArw8Y/0fg8gHjCdw0w2PtBnYPGH8UeOcQeSVJS8BXSEuSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKliOUiSKpaDJKmyvO0Akk5/63bed8r32bFxkhvmcT8tDfccJEmVocshIpZFxOMRcW9ZvygiHoqIIxHxzYg4s4yfVdbHy/Z1jce4uYw/ExFXNsY3l7HxiNi5cF+eJGk+TmXP4ZPA4cb654EvZOZ64CRwYxm/ETiZmW8HvlDmEREbgK3ArwGbgT8thbMM+CJwFbAB+EiZK0lqyVDlEBFrgA8AXynrAbwP+FaZsge4pixvKeuU7ZeX+VuAvZn5amb+BBgHLi238cx8NjN/AewtcyVJLRn2hPQfA78DvKmsvwV4KTMny/pRYHVZXg08D5CZkxHxcpm/Gniw8ZjN+zw/bfyyQSEiYjuwHWBkZIRerzdk/KUzMTHRyVxNXc/Y9Xxgxul2bJyce9I0Iyvmd7+lNFvGrjz/i/U8z1kOEfFB4HhmPhYRY1PDA6bmHNtmGh+095IDxsjMXcAugNHR0RwbGxs0rVW9Xo8u5mrqesau5wMzTjefq452bJzk9kPdvmBytozPXTe2tGFmsFjP8zDPzHuAD0XE1cDZwHn09yRWRsTysvewBnihzD8KrAWORsRy4M3Aicb4lOZ9ZhqXJLVgznMOmXlzZq7JzHX0Tyh/LzOvAx4APlymbQPuKcv7yjpl+/cyM8v41nI100XAeuBh4BFgfbn66czyOfYtyFcnSZqX17JP9ylgb0R8DngcuLOM3wl8LSLG6e8xbAXIzKcj4m7gh8AkcFNm/hIgIj4BHACWAbsz8+nXkEuS9BqdUjlkZg/oleVn6V9pNH3OvwDXznD/W4FbB4zvB/afShZJ0uLxFdKSpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqzFkOEXF2RDwcET+IiKcj4vfL+EUR8VBEHImIb0bEmWX8rLI+XravazzWzWX8mYi4sjG+uYyNR8TOhf8yJUmnYpg9h1eB92XmrwPvAjZHxCbg88AXMnM9cBK4scy/ETiZmW8HvlDmEREbgK3ArwGbgT+NiGURsQz4InAVsAH4SJkrSWrJnOWQfRNl9YxyS+B9wLfK+B7gmrK8paxTtl8eEVHG92bmq5n5E2AcuLTcxjPz2cz8BbC3zJUktWT5MJPKX/ePAW+n/1f+j4GXMnOyTDkKrC7Lq4HnATJzMiJeBt5Sxh9sPGzzPs9PG79shhzbge0AIyMj9Hq9YeIvqYmJiU7maup6xq7nAzNOt2Pj5NyTphlZMb/7LaXZMnbl+V+s53mocsjMXwLvioiVwLeBdwyaVj7GDNtmGh+095IDxsjMXcAugNHR0RwbG5s9eAt6vR5dzNXU9YxdzwdmnO6Gnfed8n12bJzk9kND/RPUmtkyPnfd2NKGmcFiPc+ndLVSZr4E9IBNwMqImPqurQFeKMtHgbUAZfubgRPN8Wn3mWlcktSSYa5WemvZYyAiVgDvBw4DDwAfLtO2AfeU5X1lnbL9e5mZZXxruZrpImA98DDwCLC+XP10Jv2T1vsW4ouTJM3PMPt0q4A95bzDrwB3Z+a9EfFDYG9EfA54HLizzL8T+FpEjNPfY9gKkJlPR8TdwA+BSeCmcriKiPgEcABYBuzOzKcX7CuUJJ2yOcshM58E3j1g/Fn6VxpNH/8X4NoZHutW4NYB4/uB/UPklSQtAV8hLUmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpIrlIEmqWA6SpEq33y9X0tDWTXvb7B0bJ+f1VtoSuOcgSRrAcpAkVSwHSVLFcpAkVSwHSVLFcpAkVSwHSVLFcpAkVSwHSVLFcpAkVSwHSVLFcpAkVSwHSVLFcpAkVSwHSVLFcpAkVSwHSVJlznKIiLUR8UBEHI6IpyPik2X8gog4GBFHysfzy3hExB0RMR4RT0bExY3H2lbmH4mIbY3xSyLiULnPHRERi/HFSpKGM8yewySwIzPfAWwCboqIDcBO4P7MXA/cX9YBrgLWl9t24EvQLxPgFuAy4FLglqlCKXO2N+63+bV/aZKk+ZqzHDLzWGb+XVl+BTgMrAa2AHvKtD3ANWV5C3BX9j0IrIyIVcCVwMHMPJGZJ4GDwOay7bzM/H5mJnBX47EkSS04pXMOEbEOeDfwEDCSmcegXyDA28q01cDzjbsdLWOzjR8dMC5JasnyYSdGxLnAXwC/nZn/NMtpgUEbch7jgzJsp3/4iZGREXq93hypl97ExEQnczV1PWPX80E3M+7YOPnv1kdW1GNd0vV8MHvGrjz/i/WzOFQ5RMQZ9Ivh65n5l2X4xYhYlZnHyqGh42X8KLC2cfc1wAtlfGzaeK+Mrxkwv5KZu4BdAKOjozk2NjZoWqt6vR5dzNXU9YxdzwfdzHjDzvv+3fqOjZPcfmjov/+WXNfzwewZn7tubGnDzGCxfhaHuVopgDuBw5n5R41N+4CpK462Afc0xq8vVy1tAl4uh50OAFdExPnlRPQVwIGy7ZWI2FQ+1/WNx5IktWCY2n4P8FHgUEQ8UcZ+F7gNuDsibgR+Clxbtu0HrgbGgZ8DHwPIzBMR8VngkTLvM5l5oix/HPgqsAL4TrlJkloyZzlk5t8y+LwAwOUD5idw0wyPtRvYPWD8UeCdc2WRJC0NXyEtSapYDpKkiuUgSapYDpKkiuUgSapYDpKkSrdfniidhtZNe6WydDpyz0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVLEcJEkVy0GSVJmzHCJid0Qcj4inGmMXRMTBiDhSPp5fxiMi7oiI8Yh4MiIubtxnW5l/JCK2NcYviYhD5T53REQs9BcpSTo1w+w5fBXYPG1sJ3B/Zq4H7i/rAFcB68ttO/Al6JcJcAtwGXApcMtUoZQ52xv3m/65JElLbM5yyMy/AU5MG94C7CnLe4BrGuN3Zd+DwMqIWAVcCRzMzBOZeRI4CGwu287LzO9nZgJ3NR5LktSS+Z5zGMnMYwDl49vK+Grg+ca8o2VstvGjA8YlSS1avsCPN+h8Qc5jfPCDR2ynfwiKkZERer3ePCIuromJiU7maup6xq7ng9kz7tg4ubRhZjCyojtZBul6Ppg9Y1d+Rhfr92W+5fBiRKzKzGPl0NDxMn4UWNuYtwZ4oYyPTRvvlfE1A+YPlJm7gF0Ao6OjOTY2NtPU1vR6PbqYq6nrGbueD2bPeMPO+5Y2zAx2bJzk9kML/fffwul6Ppg943PXjS1tmBks1u/LfA8r7QOmrjjaBtzTGL++XLW0CXi5HHY6AFwREeeXE9FXAAfKtlciYlO5Sun6xmNJkloyZ21HxJ/R/6v/wog4Sv+qo9uAuyPiRuCnwLVl+n7gamAc+DnwMYDMPBERnwUeKfM+k5lTJ7k/Tv+KqBXAd8pNktSiOcshMz8yw6bLB8xN4KYZHmc3sHvA+KPAO+fKIUlaOr5CWpJUsRwkSRXLQZJUsRwkSRXLQZJU6fYrUKTXYN0ivhhtx8bJzrzYTVoM7jlIkiqWgySpYjlIkiqWgySpYjlIkiqWgySpYjlIkiqWgySpYjlIkiqWgySpYjlIkiq+t5IkzcNivnfXbJ677QNL8nncc5AkVSwHSVLFcpAkVSwHSVLFcpAkVbxaSYtqPld0+L+sSe1zz0GSVLEcJEkVy0GSVLEcJEmVzpyQjojNwJ8Ay4CvZOZtLUd6XWmeGPaEr6S5dGLPISKWAV8ErgI2AB+JiA3tppKkN66u7DlcCoxn5rMAEbEX2AL8sNVUC6ytN+qSpFPViT0HYDXwfGP9aBmTJLUgMrPtDETEtcCVmfnfy/pHgUsz839Mm7cd2F5W/xPwzJIGHc6FwD+0HWIOXc/Y9XxgxoXQ9Xzw+sv4HzPzrcNM7MphpaPA2sb6GuCF6ZMycxewa6lCzUdEPJqZo23nmE3XM3Y9H5hxIXQ9H7yxM3blsNIjwPqIuCgizgS2AvtaziRJb1id2HPIzMmI+ARwgP6lrLsz8+mWY0nSG1YnygEgM/cD+9vOsQA6fdir6HrGrucDMy6ErueDN3DGTpyQliR1S1fOOUiSOsRyWCAR8dmIeDIinoiI70bEfyjjERF3RMR42X5xS/n+MCJ+VDJ8OyJWNrbdXPI9ExFXtpGv5Lg2Ip6OiH+NiNFp2zqRsWTZXHKMR8TONrNMiYjdEXE8Ip5qjF0QEQcj4kj5eH6L+dZGxAMRcbg8x5/sUsaIODsiHo6IH5R8v1/GL4qIh0q+b5YLZloVEcsi4vGIuHdRM2amtwW4Aec1ln8L+HJZvhr4DhDAJuChlvJdASwvy58HPl+WNwA/AM4CLgJ+DCxrKeM76L9+pQeMNsa7lHFZ+fy/CpxZcm3owM/fbwAXA081xv4A2FmWd0495y3lWwVcXJbfBPx9eV47kbH8fp5bls8AHiq/r3cDW8v4l4GPd+C5/p/AN4B7y/qiZHTPYYFk5j81Vs8Bpk7mbAHuyr4HgZURsaqFfN/NzMmy+iD915JM5dubma9m5k+AcfpvZ7LkMvNwZg56YWNnMtJ4q5fM/AUw9VYvrcrMvwFOTBveAuwpy3uAa5Y0VENmHsvMvyvLrwCH6b8LQicylt/PibJ6Rrkl8D7gW2W81e8hQESsAT4AfKWsB4uU0XJYQBFxa0Q8D1wH/F4Z7uJbg/w3+nsz0M1803UpY5eyzGUkM49B/x9n4G0t5wEgItYB76b/13lnMpbDNU8Ax4GD9PcQX2r8UdWF5/qPgd8B/rWsv4VFymg5nIKI+OuIeGrAbQtAZn46M9cCXwc+MXW3AQ+1KJeIzZWvzPk0MFkyLmm+YTMOuttSZpxDl7KcdiLiXOAvgN+etrfdusz8ZWa+i/5e9aX0D3NW05Y21b+JiA8CxzPzsebwgKkLkrEzr3M4HWTm+4ec+g3gPuAWhnxrkIUwV76I2AZ8ELg8ywHKpcwHp/Q9bFrSjKdRlrm8GBGrMvNYOZR5vM0wEXEG/WL4emb+ZRnuVEaAzHwpInr0zzmsjIjl5S/ztp/r9wAfioirgbOB8+jvSSxKRvccFkhErG+sfgj4UVneB1xfrlraBLw8tRu9xPk2A58CPpSZP29s2gdsjYizIuIiYD3w8FLnm0OXMp5Ob/WyD9hWlrcB97QVpBwbvxM4nJl/1NjUiYwR8dapK/giYgXwfvrnRR4APtx2PoDMvDkz12TmOvo/d9/LzOtYrIxtn3l/vdzo/0X0FPAk8H+A1WU86P9HRj8GDtG4CmeJ843TP1b+RLl9ubHt0yXfM8BVLX4Pf5P+X+avAi8CB7qWsWS5mv7VNj8GPt32z17J9GfAMeD/le/hjfSPR98PHCkfL2gx33+hf7jjycbP4NVdyQj8Z+Dxku8p4PfK+K/S/0NkHPhz4Ky2n+uSa4x/u1ppUTL6CmlJUsXDSpKkiuUgSapYDpKkiuUgSapYDpKkiuUgSapYDpKkiuUgSar8f5XUP1tv5NXMAAAAAElFTkSuQmCC",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x7fb1f65ce438>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Histogram of Average Temperature values\n",
    "clean_temperature_df['AverageTemperature'].hist();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### U.S. City Demographic data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demographics_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2891 entries, 0 to 2890\n",
      "Data columns (total 12 columns):\n",
      "City                      2891 non-null object\n",
      "State                     2891 non-null object\n",
      "Median Age                2891 non-null float64\n",
      "Male Population           2888 non-null float64\n",
      "Female Population         2888 non-null float64\n",
      "Total Population          2891 non-null int64\n",
      "Number of Veterans        2878 non-null float64\n",
      "Foreign-born              2878 non-null float64\n",
      "Average Household Size    2875 non-null float64\n",
      "State Code                2891 non-null object\n",
      "Race                      2891 non-null object\n",
      "Count                     2891 non-null int64\n",
      "dtypes: float64(6), int64(2), object(4)\n",
      "memory usage: 271.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# quick view of the data info.\n",
    "demographics_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of null values:\n",
      "City                      0.00\n",
      "State                     0.00\n",
      "Median Age                0.00\n",
      "Male Population           0.10\n",
      "Female Population         0.10\n",
      "Total Population          0.00\n",
      "Number of Veterans        0.45\n",
      "Foreign-born              0.45\n",
      "Average Household Size    0.55\n",
      "State Code                0.00\n",
      "Race                      0.00\n",
      "Count                     0.00\n",
      "\n",
      "No columns to be dropped\n",
      "\n",
      "Number of duplicate rows: 0\n",
      "\n",
      "Number of rows where all values are missing: 0\n",
      "\n",
      "Drop miss index columns if exist (Unnamed): []\n"
     ]
    }
   ],
   "source": [
    "# clean demographics table\n",
    "clean_demographics_df = clean_tables.clean_demographics_table(demographics_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Airport Codes data"
   ]
  },
  {
   "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>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": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 55075 entries, 0 to 55074\n",
      "Data columns (total 12 columns):\n",
      "ident           55075 non-null object\n",
      "type            55075 non-null object\n",
      "name            55075 non-null object\n",
      "elevation_ft    48069 non-null float64\n",
      "continent       27356 non-null object\n",
      "iso_country     54828 non-null object\n",
      "iso_region      55075 non-null object\n",
      "municipality    49399 non-null object\n",
      "gps_code        41030 non-null object\n",
      "iata_code       9189 non-null object\n",
      "local_code      28686 non-null object\n",
      "coordinates     55075 non-null object\n",
      "dtypes: float64(1), object(11)\n",
      "memory usage: 5.0+ MB\n"
     ]
    }
   ],
   "source": [
    "# quick view of the data info.\n",
    "airport_df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Percentage of null values:\n",
      "ident            0.00\n",
      "type             0.00\n",
      "name             0.00\n",
      "elevation_ft    12.72\n",
      "continent       50.33\n",
      "iso_country      0.45\n",
      "iso_region       0.00\n",
      "municipality    10.31\n",
      "gps_code        25.50\n",
      "iata_code       83.46\n",
      "local_code      47.91\n",
      "coordinates      0.10\n",
      "\n",
      "No columns to be dropped\n",
      "\n",
      "Number of duplicate rows: 0\n",
      "\n",
      "Number of rows where all values are missing: 0\n",
      "\n",
      "Drop miss index columns if exist (Unnamed): []\n"
     ]
    }
   ],
   "source": [
    "# clean airport table\n",
    "clean_airport_df = clean_tables.clean_airport_table(airport_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 3: Define the Data Model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 3.1 Conceptual Data Model\n",
    "**Map out the conceptual data model and explain why you chose that model**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "![conceptual data model](conceptual_data_model.png \"Conceptual Data Model\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "The data model here represents immigration instances with regard to airport data, demographic data, temperature data, and others. The model is designed as a **Star schema** in the data warehouse, where the desired immigration facts and info are in the fact table, and other perspective information and features that include an object in the fact table are dimension tables. The star schema is suitable for the analytics purposes of immigration study, since it connects relevant information within a proper number of aggregations and links (fewer joins), and decreases the records duplication at an adequate level with respect to schema complexity.\n",
    "\n",
    "Some dimensions are a set of related information extracted from the original immigration dataset to construct a more-structured and less-duplicated schema. Other dimensions are datasets transformed to accommodate with immigration analysis level and reference with the fact table. The first case includes *data, visa, migrant, and status dimensions*. The second case includes:\n",
    "- *country temperature dimension*, which is formed by grouping the temperature dataset records by country and joining it with country code mapping information.\n",
    "- *Airport dimension*, which is formed by taking the records of unique IATA code values, since it has the highest intersection with i94port values in the fact table, which therefore provide more beneficial information for the analysis and make the best use of the airport dataset.\n",
    "- *demographics dimension*, which is formed by grouping the demographics dataset records by state, since we are interested in state analysis level with i94addr values in the fact table, and to gain more information out of the demographics dataset."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "**Fact table:**\n",
    "- Immigration table\n",
    "\n",
    "**Dimension tables:**\n",
    "1. Migrant\n",
    "1. Status\n",
    "1. Visa\n",
    "1. Demographics\n",
    "1. Airport\n",
    "1. Country Temperature\n",
    "1. Date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 3.2 Mapping Out Data Pipelines\n",
    "**List the steps necessary to pipeline the data into the chosen data model:**\n",
    "1. Clean the data (Applied in Step 2).\n",
    "1. Create Dimension tables.\n",
    "1. Create Fact table.\n",
    "1. Write data into parquet files.\n",
    "1. Perform data quality checks."
   ]
  },
  {
   "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": 31,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# specify the schema of each dataset to read them correctly\n",
    "# 1) immigration dataset\n",
    "immigration_schema = StructType([StructField(\"cicid\", FloatType(), True),\n",
    "                                 StructField(\"i94yr\", FloatType(), True),\n",
    "                                 StructField(\"i94mon\", FloatType(), True),\n",
    "                                 StructField(\"i94cit\", FloatType(), True),\n",
    "                                 StructField(\"i94res\", FloatType(), True),\n",
    "                                 StructField(\"i94port\", StringType(), True),\n",
    "                                 StructField(\"arrdate\", FloatType(), True),\n",
    "                                 StructField(\"i94mode\", FloatType(), True),\n",
    "                                 StructField(\"i94addr\", StringType(), True),\n",
    "                                 StructField(\"depdate\", FloatType(), True),\n",
    "                                 StructField(\"i94bir\", FloatType(), True),\n",
    "                                 StructField(\"i94visa\", FloatType(), True),\n",
    "                                 StructField(\"count\", FloatType(), True),\n",
    "                                 StructField(\"dtadfile\", StringType(), True),\n",
    "                                 StructField(\"visapost\", StringType(), True),\n",
    "                                 StructField(\"entdepa\", StringType(), True),\n",
    "                                 StructField(\"entdepd\", StringType(), True),\n",
    "                                 StructField(\"matflag\", StringType(), True),\n",
    "                                 StructField(\"biryear\", FloatType(), True),\n",
    "                                 StructField(\"dtaddto\", StringType(), True),\n",
    "                                 StructField(\"gender\", StringType(), True),\n",
    "                                 StructField(\"airline\", StringType(), True),\n",
    "                                 StructField(\"admnum\", FloatType(), True),\n",
    "                                 StructField(\"fltno\", StringType(), True),\n",
    "                                 StructField(\"visatype\", StringType(), True)])\n",
    "\n",
    "immigration_df_spark = spark.createDataFrame(clean_immigration_df_sample, schema=immigration_schema)\n",
    "\n",
    "# 2) temperature dataset\n",
    "temperature_schema = StructType([StructField(\"dt\", DateType(), True),\n",
    "                                 StructField(\"AverageTemperature\", FloatType(), True),\n",
    "                                 StructField(\"AverageTemperatureUncertainty\", FloatType(), True),\n",
    "                                 StructField(\"City\", StringType(), True),\n",
    "                                 StructField(\"Country\", StringType(), True),\n",
    "                                 StructField(\"Latitude\", FloatType(), True), \n",
    "                                 StructField(\"Longitude\", FloatType(), True)])\n",
    "\n",
    "temperature_df_spark = spark.createDataFrame(clean_temperature_df, schema=temperature_schema)\n",
    "\n",
    "# 3) demographics dataset\n",
    "demographics_schema = StructType([StructField(\"City\", StringType(), True), \n",
    "                                  StructField(\"State\", StringType(), True), \n",
    "                                  StructField(\"Median Age\", FloatType(), True), \n",
    "                                  StructField(\"Male Population\", FloatType(), True), \n",
    "                                  StructField(\"Female Population\", FloatType(), True), \n",
    "                                  StructField(\"Total Population\", IntegerType(), True), \n",
    "                                  StructField(\"Number of Veterans\", FloatType(), True), \n",
    "                                  StructField(\"Foreign-born\", FloatType(), True), \n",
    "                                  StructField(\"Average Household Size\", FloatType(), True), \n",
    "                                  StructField(\"State Code\", StringType(), True), \n",
    "                                  StructField(\"Race\", StringType(), True),\n",
    "                                  StructField(\"Count\", IntegerType(), True)])\n",
    "\n",
    "demographics_df_spark = spark.createDataFrame(clean_demographics_df, schema=demographics_schema)\n",
    "\n",
    "\n",
    "# 4) airport codes dataset\n",
    "airport_schema = StructType([StructField(\"ident\", StringType(), True), \n",
    "                             StructField(\"type\", StringType(), True), \n",
    "                             StructField(\"name\", StringType(), True), \n",
    "                             StructField(\"elevation_ft\", FloatType(), True), \n",
    "                             StructField(\"continent\", StringType(), True), \n",
    "                             StructField(\"iso_country\", StringType(), True), \n",
    "                             StructField(\"iso_region\", StringType(), True), \n",
    "                             StructField(\"municipality\", StringType(), True), \n",
    "                             StructField(\"gps_code\", StringType(), True), \n",
    "                             StructField(\"iata_code\", StringType(), True), \n",
    "                             StructField(\"local_code\", StringType(), True),\n",
    "                             StructField(\"longitude\", FloatType(), True),\n",
    "                             StructField(\"latitude\", FloatType(), True)])\n",
    "\n",
    "airport_df_spark = spark.createDataFrame(clean_airport_df, schema=airport_schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# path where to store fact and dimension tables\n",
    "output_path = \"./data-model-tables/\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Dimension tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 1. Migrant dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table migrant to ./data-model-tables/migrant\n",
      "Write completed with 13.79 sec !\n"
     ]
    }
   ],
   "source": [
    "# create migrant dimension table (dim_migrant)\n",
    "dim_migrant = schema_tables.create_migrant_dimension(immigration_df_spark, output_path, \"migrant\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>migrant_id</th>\n",
       "      <th>birth_year</th>\n",
       "      <th>gender</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16</td>\n",
       "      <td>1995.0</td>\n",
       "      <td>F</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>32</td>\n",
       "      <td>1982.0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>246</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>573</td>\n",
       "      <td>1972.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>21</td>\n",
       "      <td>1976.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   migrant_id  birth_year gender\n",
       "0          16      1995.0      F\n",
       "1          32      1982.0      M\n",
       "2         246      1959.0      M\n",
       "3         573      1972.0    NaN\n",
       "4          21      1976.0    NaN"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "migrant_table = spark.read.parquet(\"./data-model-tables/migrant\")\n",
    "migrant_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 2. Status dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table status to ./data-model-tables/status\n",
      "Write completed with 4.83 sec !\n"
     ]
    }
   ],
   "source": [
    "# create status dimension table (dim_status)\n",
    "dim_status = schema_tables.create_status_dimension(immigration_df_spark, output_path, \"status\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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>status_flag_id</th>\n",
       "      <th>arrival_flag</th>\n",
       "      <th>departure_flag</th>\n",
       "      <th>match_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>105</td>\n",
       "      <td>Z</td>\n",
       "      <td>O</td>\n",
       "      <td>M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>36</td>\n",
       "      <td>G</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>106</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   status_flag_id arrival_flag departure_flag match_flag\n",
       "0               8            O              O          M\n",
       "1              35            A              D          M\n",
       "2             105            Z              O          M\n",
       "3              36            G            NaN        NaN\n",
       "4             106            O            NaN        NaN"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "status_table = spark.read.parquet(\"./data-model-tables/status\")\n",
    "status_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 3. Visa dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table visa to ./data-model-tables/visa\n",
      "Write completed with 4.95 sec !\n"
     ]
    }
   ],
   "source": [
    "# create visa dimension table (dim_visa)\n",
    "dim_visa = schema_tables.create_visa_dimension(immigration_df_spark, output_path, \"visa\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>visa_id</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>visatype</th>\n",
       "      <th>visapost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>2.0</td>\n",
       "      <td>B2</td>\n",
       "      <td>MNL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>139</td>\n",
       "      <td>2.0</td>\n",
       "      <td>B2</td>\n",
       "      <td>GTM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>706</td>\n",
       "      <td>2.0</td>\n",
       "      <td>B2</td>\n",
       "      <td>CDJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>937</td>\n",
       "      <td>2.0</td>\n",
       "      <td>B2</td>\n",
       "      <td>NRB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>32</td>\n",
       "      <td>2.0</td>\n",
       "      <td>B2</td>\n",
       "      <td>CPT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   visa_id  i94visa visatype visapost\n",
       "0       13      2.0       B2      MNL\n",
       "1      139      2.0       B2      GTM\n",
       "2      706      2.0       B2      CDJ\n",
       "3      937      2.0       B2      NRB\n",
       "4       32      2.0       B2      CPT"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visa_table = spark.read.parquet(\"./data-model-tables/visa\")\n",
    "visa_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 4. Demographics dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table demographics to ./data-model-tables/demographics\n",
      "Write completed with 4.51 sec !\n"
     ]
    }
   ],
   "source": [
    "# create demographics dimension table (dim_demographics)\n",
    "dim_demographics = schema_tables.create_demographics_dimension(demographics_df_spark, output_path, \"demographics\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>state_code</th>\n",
       "      <th>state</th>\n",
       "      <th>median_age</th>\n",
       "      <th>total_population</th>\n",
       "      <th>male_population</th>\n",
       "      <th>female_population</th>\n",
       "      <th>foreign_born</th>\n",
       "      <th>average_household_size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DC</td>\n",
       "      <td>District of Columbia</td>\n",
       "      <td>33.80</td>\n",
       "      <td>3361140</td>\n",
       "      <td>1598525.0</td>\n",
       "      <td>1762615.0</td>\n",
       "      <td>475585.0</td>\n",
       "      <td>2.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AR</td>\n",
       "      <td>Arkansas</td>\n",
       "      <td>32.74</td>\n",
       "      <td>2882889</td>\n",
       "      <td>1400724.0</td>\n",
       "      <td>1482165.0</td>\n",
       "      <td>307753.0</td>\n",
       "      <td>2.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TN</td>\n",
       "      <td>Tennessee</td>\n",
       "      <td>34.31</td>\n",
       "      <td>10690165</td>\n",
       "      <td>5124189.0</td>\n",
       "      <td>5565976.0</td>\n",
       "      <td>900149.0</td>\n",
       "      <td>2.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LA</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>34.63</td>\n",
       "      <td>6502975</td>\n",
       "      <td>3134990.0</td>\n",
       "      <td>3367985.0</td>\n",
       "      <td>417095.0</td>\n",
       "      <td>2.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AZ</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>35.04</td>\n",
       "      <td>22497710</td>\n",
       "      <td>11137275.0</td>\n",
       "      <td>11360435.0</td>\n",
       "      <td>3411565.0</td>\n",
       "      <td>2.77</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  state_code                 state  median_age  total_population  \\\n",
       "0         DC  District of Columbia       33.80           3361140   \n",
       "1         AR              Arkansas       32.74           2882889   \n",
       "2         TN             Tennessee       34.31          10690165   \n",
       "3         LA             Louisiana       34.63           6502975   \n",
       "4         AZ               Arizona       35.04          22497710   \n",
       "\n",
       "   male_population  female_population  foreign_born  average_household_size  \n",
       "0        1598525.0          1762615.0      475585.0                    2.24  \n",
       "1        1400724.0          1482165.0      307753.0                    2.53  \n",
       "2        5124189.0          5565976.0      900149.0                    2.46  \n",
       "3        3134990.0          3367985.0      417095.0                    2.47  \n",
       "4       11137275.0         11360435.0     3411565.0                    2.77  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demographics_table = spark.read.parquet(\"./data-model-tables/demographics\")\n",
    "demographics_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 5. Airport dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table airport to ./data-model-tables/airport\n",
      "Write completed with 11.3 sec !\n"
     ]
    }
   ],
   "source": [
    "# create airport dimension table (dim_airport)\n",
    "dim_airport = schema_tables.create_airport_dimension(airport_df_spark, output_path, \"airport\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>iata_code</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>local_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AMW</td>\n",
       "      <td>KAMW</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Ames Municipal Airport</td>\n",
       "      <td>956.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-IA</td>\n",
       "      <td>Ames</td>\n",
       "      <td>KAMW</td>\n",
       "      <td>AMW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ATC</td>\n",
       "      <td>MYCA</td>\n",
       "      <td>medium_airport</td>\n",
       "      <td>Arthur's Town Airport</td>\n",
       "      <td>18.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BS</td>\n",
       "      <td>BS-CI</td>\n",
       "      <td>Arthur's Town</td>\n",
       "      <td>MYCA</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BIS</td>\n",
       "      <td>KBIS</td>\n",
       "      <td>medium_airport</td>\n",
       "      <td>Bismarck Municipal Airport</td>\n",
       "      <td>1661.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-ND</td>\n",
       "      <td>Bismarck</td>\n",
       "      <td>KBIS</td>\n",
       "      <td>BIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BPA</td>\n",
       "      <td>US-BPA</td>\n",
       "      <td>closed</td>\n",
       "      <td>Grumman Bethpage Airport</td>\n",
       "      <td>115.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-NY</td>\n",
       "      <td>Bethpage</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BPY</td>\n",
       "      <td>FMNQ</td>\n",
       "      <td>medium_airport</td>\n",
       "      <td>Besalampy Airport</td>\n",
       "      <td>125.0</td>\n",
       "      <td>AF</td>\n",
       "      <td>MG</td>\n",
       "      <td>MG-M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FMNQ</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  iata_code   ident            type                        name  elevation_ft  \\\n",
       "0       AMW    KAMW   small_airport      Ames Municipal Airport         956.0   \n",
       "1       ATC    MYCA  medium_airport       Arthur's Town Airport          18.0   \n",
       "2       BIS    KBIS  medium_airport  Bismarck Municipal Airport        1661.0   \n",
       "3       BPA  US-BPA          closed    Grumman Bethpage Airport         115.0   \n",
       "4       BPY    FMNQ  medium_airport           Besalampy Airport         125.0   \n",
       "\n",
       "  continent iso_country iso_region   municipality gps_code local_code  \n",
       "0       NaN          US      US-IA           Ames     KAMW        AMW  \n",
       "1       NaN          BS      BS-CI  Arthur's Town     MYCA        NaN  \n",
       "2       NaN          US      US-ND       Bismarck     KBIS        BIS  \n",
       "3       NaN          US      US-NY       Bethpage      NaN        NaN  \n",
       "4        AF          MG       MG-M            NaN     FMNQ        NaN  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_table = spark.read.parquet(\"./data-model-tables/airport\")\n",
    "airport_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 6. Country Temperature dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table country_temperature to ./data-model-tables/country_temperature\n",
      "Write completed with 19.8 sec !\n"
     ]
    }
   ],
   "source": [
    "# create country_temperature dimension table (dim_country_temperature)\n",
    "dim_country_temperature = schema_tables.create_country_temperature_dimension(i94cit_res, temperature_df_spark, output_path, \"country_temperature\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>country_code</th>\n",
       "      <th>country_name</th>\n",
       "      <th>average_temperature</th>\n",
       "      <th>average_temperature_uncertainty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>386.0</td>\n",
       "      <td>Benin</td>\n",
       "      <td>27.85</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>271.0</td>\n",
       "      <td>Cocos Islands</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>403.0</td>\n",
       "      <td>Invalid: American Samoa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>Invalid: Stateless</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>705.0</td>\n",
       "      <td>Invalid: Stateless</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country_code             country_name  average_temperature  \\\n",
       "0         386.0                    Benin                27.85   \n",
       "1         271.0            Cocos Islands                  NaN   \n",
       "2         403.0  Invalid: American Samoa                  NaN   \n",
       "3           0.0       Invalid: Stateless                  NaN   \n",
       "4         705.0       Invalid: Stateless                  NaN   \n",
       "\n",
       "   average_temperature_uncertainty  \n",
       "0                             0.49  \n",
       "1                              NaN  \n",
       "2                              NaN  \n",
       "3                              NaN  \n",
       "4                              NaN  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "country_temperature_table = spark.read.parquet(\"./data-model-tables/country_temperature\")\n",
    "country_temperature_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 7. Date dimension table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table date to ./data-model-tables/date\n",
      "Write completed with 21.95 sec !\n"
     ]
    }
   ],
   "source": [
    "# create date dimension table (dim_date)\n",
    "dim_date = schema_tables.create_date_dimension(immigration_df_spark, output_path, \"date\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>date_id</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>weekday</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20627.0</td>\n",
       "      <td>2016-06-22</td>\n",
       "      <td>2016</td>\n",
       "      <td>6</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20592.0</td>\n",
       "      <td>2016-05-18</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>18</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20674.0</td>\n",
       "      <td>2016-08-08</td>\n",
       "      <td>2016</td>\n",
       "      <td>8</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20663.0</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>2016</td>\n",
       "      <td>7</td>\n",
       "      <td>28</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20585.0</td>\n",
       "      <td>2016-05-11</td>\n",
       "      <td>2016</td>\n",
       "      <td>5</td>\n",
       "      <td>11</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   date_id        date  year  month  day  weekday\n",
       "0  20627.0  2016-06-22  2016      6   22        4\n",
       "1  20592.0  2016-05-18  2016      5   18        4\n",
       "2  20674.0  2016-08-08  2016      8    8        2\n",
       "3  20663.0  2016-07-28  2016      7   28        5\n",
       "4  20585.0  2016-05-11  2016      5   11        4"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "date_table = spark.read.parquet(\"./data-model-tables/date\")\n",
    "date_table.limit(5).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Fact tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Immigration fact table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing table fact_immigration to ./data-model-tables/fact_immigration\n",
      "Write completed with 3.09 sec !\n"
     ]
    }
   ],
   "source": [
    "# create immigration fact table (fact_immigration)\n",
    "fact_immigration = schema_tables.create_immigration_fact(immigration_df_spark, output_path, \"fact_immigration\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>cicid</th>\n",
       "      <th>date_id_arrdate</th>\n",
       "      <th>date_id_depdate</th>\n",
       "      <th>country_code_i94cit</th>\n",
       "      <th>country_code_i94res</th>\n",
       "      <th>iata_code_i94port</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>state_code_i94addr</th>\n",
       "      <th>visa_id</th>\n",
       "      <th>status_flag_id</th>\n",
       "      <th>migrant_id</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4084316.0</td>\n",
       "      <td>20566.0</td>\n",
       "      <td>20573.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>HHW</td>\n",
       "      <td>1.0</td>\n",
       "      <td>HI</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>JL</td>\n",
       "      <td>5.658268e+10</td>\n",
       "      <td>00782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4422636.0</td>\n",
       "      <td>20567.0</td>\n",
       "      <td>20568.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>582.0</td>\n",
       "      <td>MCA</td>\n",
       "      <td>1.0</td>\n",
       "      <td>TX</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>*GA</td>\n",
       "      <td>9.436199e+10</td>\n",
       "      <td>XBLNG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1195600.0</td>\n",
       "      <td>20551.0</td>\n",
       "      <td>20571.0</td>\n",
       "      <td>148.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>OGG</td>\n",
       "      <td>1.0</td>\n",
       "      <td>FL</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>LH</td>\n",
       "      <td>5.578047e+10</td>\n",
       "      <td>00464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5291768.0</td>\n",
       "      <td>20572.0</td>\n",
       "      <td>20581.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>297.0</td>\n",
       "      <td>LOS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>CA</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>QR</td>\n",
       "      <td>9.478970e+10</td>\n",
       "      <td>00739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>985523.0</td>\n",
       "      <td>20550.0</td>\n",
       "      <td>20553.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>CHM</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NY</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.232257e+10</td>\n",
       "      <td>LAND</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       cicid  date_id_arrdate  date_id_depdate  country_code_i94cit  \\\n",
       "0  4084316.0          20566.0          20573.0                209.0   \n",
       "1  4422636.0          20567.0          20568.0                582.0   \n",
       "2  1195600.0          20551.0          20571.0                148.0   \n",
       "3  5291768.0          20572.0          20581.0                297.0   \n",
       "4   985523.0          20550.0          20553.0                111.0   \n",
       "\n",
       "   country_code_i94res iata_code_i94port  i94mode state_code_i94addr  visa_id  \\\n",
       "0                209.0               HHW      1.0                 HI        0   \n",
       "1                582.0               MCA      1.0                 TX        1   \n",
       "2                112.0               OGG      1.0                 FL        0   \n",
       "3                297.0               LOS      1.0                 CA        3   \n",
       "4                111.0               CHM      3.0                 NY        0   \n",
       "\n",
       "   status_flag_id  migrant_id airline        admnum  fltno  \n",
       "0               0           0      JL  5.658268e+10  00782  \n",
       "1               1           1     *GA  9.436199e+10  XBLNG  \n",
       "2               0           2      LH  5.578047e+10  00464  \n",
       "3               0           3      QR  9.478970e+10  00739  \n",
       "4               4           4     NaN  4.232257e+10   LAND  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "immigration_table = spark.read.parquet(\"./data-model-tables/fact_immigration\")\n",
    "immigration_table.limit(5).toPandas()"
   ]
  },
  {
   "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": 39,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data quality check (record counting) passed for immigration table with 1,000 records\n",
      "Data quality check (record counting) passed for airport table with 9,042 records\n",
      "Data quality check (record counting) passed for country temperature table with 289 records\n",
      "Data quality check (record counting) passed for migrant table with 215 records\n",
      "Data quality check (record counting) passed for status table with 34 records\n",
      "Data quality check (record counting) passed for demographics table with 49 records\n",
      "Data quality check (record counting) passed for date table with 111 records\n",
      "Data quality check (record counting) passed for visa table with 149 records\n"
     ]
    }
   ],
   "source": [
    "tables = {\n",
    "    \"immigration\": immigration_table,\n",
    "    \"airport\": airport_table,\n",
    "    \"country temperature\": country_temperature_table,\n",
    "    \"migrant\": migrant_table,\n",
    "    \"status\": status_table,\n",
    "    \"demographics\": demographics_table,\n",
    "    \"date\": date_table,\n",
    "    \"visa\": visa_table\n",
    "}\n",
    "\n",
    "for table_name, table in tables.items():\n",
    "    utils.quality_check_count(table, table_name)"
   ]
  },
  {
   "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": [
    "- Refer to **data_dictionary.md** file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Step 5: Complete Project Write Up"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "- Rationale for the choice of tools and technologies for the project:\n",
    "    - This project uses a variety of big data processing technologies and others, such as: \n",
    "        - Apache Spark, for its capacity to process enormous amounts of data, as well as the use of its unified analytics engine and easy-to-use APIs.\n",
    "        - Pandas, for its practical dataframe manipulation features.\n",
    "        - Matplotlib, for plotting data and gaining additional insights."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "- Propose how often the data should be updated and why:\n",
    "    - The current I94 immigration data is updated monthly, and therefore the data should be updated accordingly."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "- Write a description of how you would approach the problem differently under the following scenarios:\n",
    "    - The data was increased by 100x.\n",
    "        - Even though Spark can handle the job but we would use more processing power and increase the number of nodes in the cluster. Also, we would implement the pipeline and engineering work with more sophisticated processing and storage functions such as Amazon Redshift and Amazon EMR.\n",
    "     \n",
    "    - The data populates a dashboard that must be updated on a daily basis by 7am every day.\n",
    "        - In this case, Apache Airflow will be used to schedule and run data pipelines, that is to ensure timeliness, consistency, and stability.\n",
    "    - The database needed to be accessed by 100+ people.\n",
    "        - In this scenario, we would move our analytics database into Amazon Redshift which can handle massive request volumes and is easily scalable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.6 ('base')",
   "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.7.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "ec786558109aefb7e7ee5b9551df7a7b67903598e09f9069265e378390dccf65"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}