Capstone Project Template.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "# Project Title\n",
    "### Data Engineering Capstone Project\n",
    "\n",
    "#### Project Summary\n",
    "--describe your project at a high level--\n",
    "\n",
    "The project follows the follow steps:\n",
    "* Step 1: Scope the Project and Gather Data\n",
    "* Step 2: Explore and Assess the Data\n",
    "* Step 3: Define the Data Model\n",
    "* Step 4: Run ETL to Model the Data\n",
    "* Step 5: Complete Project Write Up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# Do all imports and installs here\n",
    "import pandas as pd\n",
    "from pyspark.sql import SparkSession\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import datetime\n",
    "from pprint import pprint\n",
    "from pyspark.sql import SparkSession\n",
    "from pyspark.sql.functions import count, col, udf, year, month, avg, round, dayofweek, weekofyear, isnull\n",
    "from pyspark.sql.types import StringType, IntegerType\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 1: Scope the Project and Gather Data\n",
    "\n",
    "#### Scope \n",
    "The goal of this project its use multiple Data source to analyze the Immigration  in usa.\n",
    "This project was made using Spark in order to make a datawarehouse in parquet file format that reflects inmigration data in US airports. It's used a star schema with a facts table an dimensional tables.\n",
    "\n",
    "\n",
    "#### Describe and Gather Data \n",
    "I94 Immigration Data: This data comes from the US National Tourism and Trade Office.\n",
    "\n",
    "World Temperature Data: This dataset came from Kaggle\n",
    "\n",
    "U.S. City Demographic Data: This data comes from OpenSoft\n",
    "\n",
    "Airport Code Table: This is a simple table of airport codes and corresponding cities"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### load the Data into Pandas chunksize and explore the Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "fname = '../../data/18-83510-I94-Data-2016/i94_apr16_sub.sas7bdat'\n",
    "df = pd.read_sas(fname, 'sas7bdat', encoding=\"ISO-8859-1\",chunksize= 1000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "df2 = next(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3096313"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fname = '../../data/18-83510-I94-Data-2016/i94_apr16_sub.sas7bdat'\n",
    "df = pd.read_sas(fname, 'sas7bdat', encoding=\"ISO-8859-1\",chunksize= 50000)\n",
    "sum([len(i) for i in df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns',df2.shape[1] +1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T</td>\n",
       "      <td>NaN</td>\n",
       "      <td>U</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1979.0</td>\n",
       "      <td>10282016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.897628e+09</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>G</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1991.0</td>\n",
       "      <td>D/S</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>T</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1961.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1988.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AA</td>\n",
       "      <td>9.246846e+10</td>\n",
       "      <td>00199</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.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>4.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>2012.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AA</td>\n",
       "      <td>9.246846e+10</td>\n",
       "      <td>00199</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18.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>MI</td>\n",
       "      <td>20555.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247104e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19.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>NJ</td>\n",
       "      <td>20558.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>K</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1953.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247140e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20.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>NJ</td>\n",
       "      <td>20558.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>K</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247161e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>21.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>NY</td>\n",
       "      <td>20553.0</td>\n",
       "      <td>46.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247080e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>22.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>NY</td>\n",
       "      <td>20562.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160401</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247849e+10</td>\n",
       "      <td>00608</td>\n",
       "      <td>B1</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     NaN   \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",
       "4   17.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MA   \n",
       "5   18.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MI   \n",
       "6   19.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NJ   \n",
       "7   20.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NJ   \n",
       "8   21.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NY   \n",
       "9   22.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NY   \n",
       "\n",
       "   depdate  i94bir  i94visa  count  dtadfile visapost occup entdepa entdepd  \\\n",
       "0      NaN    37.0      2.0    1.0       NaN      NaN   NaN       T     NaN   \n",
       "1      NaN    25.0      3.0    1.0  20130811      SEO   NaN       G     NaN   \n",
       "2  20691.0    55.0      2.0    1.0  20160401      NaN   NaN       T       O   \n",
       "3  20567.0    28.0      2.0    1.0  20160401      NaN   NaN       O       O   \n",
       "4  20567.0     4.0      2.0    1.0  20160401      NaN   NaN       O       O   \n",
       "5  20555.0    57.0      1.0    1.0  20160401      NaN   NaN       O       O   \n",
       "6  20558.0    63.0      2.0    1.0  20160401      NaN   NaN       O       K   \n",
       "7  20558.0    57.0      2.0    1.0  20160401      NaN   NaN       O       K   \n",
       "8  20553.0    46.0      2.0    1.0  20160401      NaN   NaN       O       O   \n",
       "9  20562.0    48.0      1.0    1.0  20160401      NaN   NaN       O       O   \n",
       "\n",
       "  entdepu matflag  biryear   dtaddto gender insnum airline        admnum  \\\n",
       "0       U     NaN   1979.0  10282016    NaN    NaN     NaN  1.897628e+09   \n",
       "1       Y     NaN   1991.0       D/S      M    NaN     NaN  3.736796e+09   \n",
       "2     NaN       M   1961.0  09302016      M    NaN      OS  6.666432e+08   \n",
       "3     NaN       M   1988.0  09302016    NaN    NaN      AA  9.246846e+10   \n",
       "4     NaN       M   2012.0  09302016    NaN    NaN      AA  9.246846e+10   \n",
       "5     NaN       M   1959.0  09302016    NaN    NaN      AZ  9.247104e+10   \n",
       "6     NaN       M   1953.0  09302016    NaN    NaN      AZ  9.247140e+10   \n",
       "7     NaN       M   1959.0  09302016    NaN    NaN      AZ  9.247161e+10   \n",
       "8     NaN       M   1970.0  09302016    NaN    NaN      AZ  9.247080e+10   \n",
       "9     NaN       M   1968.0  09302016    NaN    NaN      AZ  9.247849e+10   \n",
       "\n",
       "   fltno visatype  \n",
       "0    NaN       B2  \n",
       "1  00296       F1  \n",
       "2     93       B2  \n",
       "3  00199       B2  \n",
       "4  00199       B2  \n",
       "5  00602       B1  \n",
       "6  00602       B2  \n",
       "7  00602       B2  \n",
       "8  00602       B2  \n",
       "9  00608       B1  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "insnum      1000000\n",
       "entdepu      999887\n",
       "occup        996654\n",
       "visapost     613499\n",
       "gender       143987\n",
       "entdepd       35744\n",
       "depdate       35744\n",
       "matflag       35744\n",
       "i94addr       34317\n",
       "dtaddto          42\n",
       "airline          15\n",
       "fltno            14\n",
       "i94mode          14\n",
       "entdepa          13\n",
       "dtadfile          1\n",
       "arrdate           0\n",
       "i94cit            0\n",
       "i94res            0\n",
       "i94port           0\n",
       "i94mon            0\n",
       "i94yr             0\n",
       "visatype          0\n",
       "i94bir            0\n",
       "i94visa           0\n",
       "count             0\n",
       "biryear           0\n",
       "admnum            0\n",
       "cicid             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.isna().sum().sort_values(ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "WT     452640\n",
       "B2     330937\n",
       "WB     111398\n",
       "B1      74183\n",
       "F1      18975\n",
       "E2       7355\n",
       "E1       1387\n",
       "I        1313\n",
       "F2       1024\n",
       "M1        531\n",
       "CP        130\n",
       "I1        104\n",
       "M2         17\n",
       "SBP         3\n",
       "CPL         3\n",
       "Name: visatype, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2['visatype'].value_counts()"
   ]
  },
  {
   "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>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>biryear</th>\n",
       "      <th>admnum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1.000000e+06</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>999986.000000</td>\n",
       "      <td>964256.000000</td>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1.000000e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>1.025475e+06</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>295.055807</td>\n",
       "      <td>294.121310</td>\n",
       "      <td>20549.822574</td>\n",
       "      <td>1.005696</td>\n",
       "      <td>20564.744405</td>\n",
       "      <td>41.279221</td>\n",
       "      <td>1.824807</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1974.720779</td>\n",
       "      <td>6.927069e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>6.010092e+05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>210.825147</td>\n",
       "      <td>209.727308</td>\n",
       "      <td>3.117242</td>\n",
       "      <td>0.080992</td>\n",
       "      <td>22.652658</td>\n",
       "      <td>17.172368</td>\n",
       "      <td>0.430807</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.172368</td>\n",
       "      <td>2.198342e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>6.000000e+00</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>101.000000</td>\n",
       "      <td>20545.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>20226.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1916.000000</td>\n",
       "      <td>4.406585e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>4.980408e+05</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>135.000000</td>\n",
       "      <td>130.000000</td>\n",
       "      <td>20547.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>20554.000000</td>\n",
       "      <td>30.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1962.000000</td>\n",
       "      <td>5.564424e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>1.018124e+06</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>209.000000</td>\n",
       "      <td>209.000000</td>\n",
       "      <td>20550.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>20559.000000</td>\n",
       "      <td>41.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1975.000000</td>\n",
       "      <td>5.592003e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.565416e+06</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>464.000000</td>\n",
       "      <td>464.000000</td>\n",
       "      <td>20553.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>20565.000000</td>\n",
       "      <td>54.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1986.000000</td>\n",
       "      <td>9.277287e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.041218e+06</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>999.000000</td>\n",
       "      <td>749.000000</td>\n",
       "      <td>20573.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>20716.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>9.330747e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              cicid      i94yr     i94mon          i94cit          i94res  \\\n",
       "count  1.000000e+06  1000000.0  1000000.0  1000000.000000  1000000.000000   \n",
       "mean   1.025475e+06     2016.0        4.0      295.055807      294.121310   \n",
       "std    6.010092e+05        0.0        0.0      210.825147      209.727308   \n",
       "min    6.000000e+00     2016.0        4.0      101.000000      101.000000   \n",
       "25%    4.980408e+05     2016.0        4.0      135.000000      130.000000   \n",
       "50%    1.018124e+06     2016.0        4.0      209.000000      209.000000   \n",
       "75%    1.565416e+06     2016.0        4.0      464.000000      464.000000   \n",
       "max    2.041218e+06     2016.0        4.0      999.000000      749.000000   \n",
       "\n",
       "              arrdate        i94mode        depdate          i94bir  \\\n",
       "count  1000000.000000  999986.000000  964256.000000  1000000.000000   \n",
       "mean     20549.822574       1.005696   20564.744405       41.279221   \n",
       "std          3.117242       0.080992      22.652658       17.172368   \n",
       "min      20545.000000       1.000000   20226.000000        0.000000   \n",
       "25%      20547.000000       1.000000   20554.000000       30.000000   \n",
       "50%      20550.000000       1.000000   20559.000000       41.000000   \n",
       "75%      20553.000000       1.000000   20565.000000       54.000000   \n",
       "max      20573.000000       9.000000   20716.000000      100.000000   \n",
       "\n",
       "              i94visa      count         biryear        admnum  \n",
       "count  1000000.000000  1000000.0  1000000.000000  1.000000e+06  \n",
       "mean         1.824807        1.0     1974.720779  6.927069e+10  \n",
       "std          0.430807        0.0       17.172368  2.198342e+10  \n",
       "min          1.000000        1.0     1916.000000  4.406585e+06  \n",
       "25%          2.000000        1.0     1962.000000  5.564424e+10  \n",
       "50%          2.000000        1.0     1975.000000  5.592003e+10  \n",
       "75%          2.000000        1.0     1986.000000  9.277287e+10  \n",
       "max          3.000000        1.0     2016.000000  9.330747e+10  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000000, 28)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### load the Data into spark  and explore the Data "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "spark = SparkSession.builder.\\\n",
    "config(\"spark.jars.packages\",\"saurfang:spark-sas7bdat:2.0.0-s_2.11\")\\\n",
    ".enableHiveSupport().getOrCreate()\n",
    "I94_df =spark.read.format('com.github.saurfang.sas.spark').load('../../data/18-83510-I94-Data-2016/i94_apr16_sub.sas7bdat')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "US_City_df=spark.read.csv('us-cities-demographics.csv',header=True,sep=';')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "airport_codes_df=spark.read.csv('airport-codes_csv.csv',header=True,)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "path = \"../../data2/GlobalLandTemperaturesByCity.csv\"\n",
    "\n",
    "temperature_df = spark.read.csv(path,header=True,sep=',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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"
     ]
    },
    {
     "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>...</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>...</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>...</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>...</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>...</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",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17.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>...</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>2012.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",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>18.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>MI</td>\n",
       "      <td>20555.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247104e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>19.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>NJ</td>\n",
       "      <td>20558.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1953.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247140e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>20.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>NJ</td>\n",
       "      <td>20558.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247161e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>21.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>NY</td>\n",
       "      <td>20553.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1970.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247080e+10</td>\n",
       "      <td>00602</td>\n",
       "      <td>B2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>22.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>NY</td>\n",
       "      <td>20562.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>M</td>\n",
       "      <td>1968.0</td>\n",
       "      <td>09302016</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>AZ</td>\n",
       "      <td>9.247849e+10</td>\n",
       "      <td>00608</td>\n",
       "      <td>B1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 28 columns</p>\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",
       "4   17.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MA   \n",
       "5   18.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      MI   \n",
       "6   19.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NJ   \n",
       "7   20.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NJ   \n",
       "8   21.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NY   \n",
       "9   22.0  2016.0     4.0   101.0   101.0     NYC  20545.0      1.0      NY   \n",
       "\n",
       "   depdate   ...     entdepu  matflag  biryear   dtaddto gender insnum  \\\n",
       "0      NaN   ...           U     None   1979.0  10282016   None   None   \n",
       "1      NaN   ...           Y     None   1991.0       D/S      M   None   \n",
       "2  20691.0   ...        None        M   1961.0  09302016      M   None   \n",
       "3  20567.0   ...        None        M   1988.0  09302016   None   None   \n",
       "4  20567.0   ...        None        M   2012.0  09302016   None   None   \n",
       "5  20555.0   ...        None        M   1959.0  09302016   None   None   \n",
       "6  20558.0   ...        None        M   1953.0  09302016   None   None   \n",
       "7  20558.0   ...        None        M   1959.0  09302016   None   None   \n",
       "8  20553.0   ...        None        M   1970.0  09302016   None   None   \n",
       "9  20562.0   ...        None        M   1968.0  09302016   None   None   \n",
       "\n",
       "  airline        admnum  fltno visatype  \n",
       "0    None  1.897628e+09   None       B2  \n",
       "1    None  3.736796e+09  00296       F1  \n",
       "2      OS  6.666432e+08     93       B2  \n",
       "3      AA  9.246846e+10  00199       B2  \n",
       "4      AA  9.246846e+10  00199       B2  \n",
       "5      AZ  9.247104e+10  00602       B1  \n",
       "6      AZ  9.247140e+10  00602       B2  \n",
       "7      AZ  9.247161e+10  00602       B2  \n",
       "8      AZ  9.247080e+10  00602       B2  \n",
       "9      AZ  9.247849e+10  00608       B1  \n",
       "\n",
       "[10 rows x 28 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "I94_df.printSchema()\n",
    "I94_df.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3096313"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "I94_df.count()"
   ]
  },
  {
   "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>airline</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>YFA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>TC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>FI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>R0E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>B01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>78B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>UA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>EA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Q7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>VP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>FYG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CHP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>3M</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>KI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>635</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>BW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>FC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>TB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>YNT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>WK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>MP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>AF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>SQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>DYA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>3S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>512</th>\n",
       "      <td>765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>JA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>NJE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>848</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>9H</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>WN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>518</th>\n",
       "      <td>BB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>519</th>\n",
       "      <td>U0C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>520</th>\n",
       "      <td>79A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>521</th>\n",
       "      <td>49B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>522</th>\n",
       "      <td>PP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>523</th>\n",
       "      <td>RX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>524</th>\n",
       "      <td>HB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>525</th>\n",
       "      <td>UT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>526</th>\n",
       "      <td>XD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>TJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>528</th>\n",
       "      <td>881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>529</th>\n",
       "      <td>62A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>530</th>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>531</th>\n",
       "      <td>AX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>532</th>\n",
       "      <td>078</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>533</th>\n",
       "      <td>9E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>534</th>\n",
       "      <td>L7L</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>535 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    airline\n",
       "0        LT\n",
       "1        MM\n",
       "2        DZ\n",
       "3        CI\n",
       "4       447\n",
       "5       YFA\n",
       "6        TC\n",
       "7        AZ\n",
       "8        FI\n",
       "9       R0E\n",
       "10      B01\n",
       "11      78B\n",
       "12       UA\n",
       "13       EA\n",
       "14       Q7\n",
       "15       VP\n",
       "16      FYG\n",
       "17      CHP\n",
       "18      743\n",
       "19       3M\n",
       "20       15\n",
       "21       KI\n",
       "22      635\n",
       "23       LA\n",
       "24       D5\n",
       "25       SB\n",
       "26       BW\n",
       "27       FC\n",
       "28       TB\n",
       "29      YNT\n",
       "..      ...\n",
       "505      WK\n",
       "506      MP\n",
       "507     278\n",
       "508      AF\n",
       "509      SQ\n",
       "510     DYA\n",
       "511      3S\n",
       "512     765\n",
       "513      JA\n",
       "514     NJE\n",
       "515     848\n",
       "516      9H\n",
       "517      WN\n",
       "518      BB\n",
       "519     U0C\n",
       "520     79A\n",
       "521     49B\n",
       "522      PP\n",
       "523      RX\n",
       "524      HB\n",
       "525      UT\n",
       "526      XD\n",
       "527      TJ\n",
       "528     881\n",
       "529     62A\n",
       "530      DK\n",
       "531      AX\n",
       "532     078\n",
       "533      9E\n",
       "534     L7L\n",
       "\n",
       "[535 rows x 1 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = I94_df.select('airline').drop_duplicates()\n",
    "c.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>I94BIR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>70.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>69.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>108.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>88.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>49.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>98.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>107.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>64.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>42.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>44.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>62.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>96.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>18.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>86.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>39.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>94.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>114.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>95.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>102.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>33.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>84.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>92.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>32.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>97.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>105.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>89.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>55.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>54.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>43.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>110</th>\n",
       "      <td>90.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>91.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>110.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>113 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     I94BIR\n",
       "0      70.0\n",
       "1      67.0\n",
       "2       8.0\n",
       "3       0.0\n",
       "4      69.0\n",
       "5       7.0\n",
       "6     108.0\n",
       "7      88.0\n",
       "8      49.0\n",
       "9     101.0\n",
       "10     98.0\n",
       "11     29.0\n",
       "12    107.0\n",
       "13     64.0\n",
       "14     75.0\n",
       "15     47.0\n",
       "16     42.0\n",
       "17     44.0\n",
       "18     62.0\n",
       "19     35.0\n",
       "20     96.0\n",
       "21      NaN\n",
       "22     18.0\n",
       "23     80.0\n",
       "24     86.0\n",
       "25     -3.0\n",
       "26     39.0\n",
       "27      1.0\n",
       "28     94.0\n",
       "29    114.0\n",
       "..      ...\n",
       "83     13.0\n",
       "84     99.0\n",
       "85     95.0\n",
       "86    100.0\n",
       "87    102.0\n",
       "88     33.0\n",
       "89     84.0\n",
       "90     92.0\n",
       "91      6.0\n",
       "92     20.0\n",
       "93     52.0\n",
       "94     32.0\n",
       "95     24.0\n",
       "96      5.0\n",
       "97     15.0\n",
       "98     97.0\n",
       "99    105.0\n",
       "100    89.0\n",
       "101    55.0\n",
       "102    26.0\n",
       "103     9.0\n",
       "104    65.0\n",
       "105    16.0\n",
       "106    12.0\n",
       "107    54.0\n",
       "108    43.0\n",
       "109    76.0\n",
       "110    90.0\n",
       "111    91.0\n",
       "112   110.0\n",
       "\n",
       "[113 rows x 1 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = I94_df.select('I94BIR').drop_duplicates()\n",
    "c.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2891"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US_City_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- City: string (nullable = true)\n",
      " |-- State: string (nullable = true)\n",
      " |-- Median Age: string (nullable = true)\n",
      " |-- Male Population: string (nullable = true)\n",
      " |-- Female Population: string (nullable = true)\n",
      " |-- Total Population: string (nullable = true)\n",
      " |-- Number of Veterans: string (nullable = true)\n",
      " |-- Foreign-born: string (nullable = true)\n",
      " |-- Average Household Size: string (nullable = true)\n",
      " |-- State Code: string (nullable = true)\n",
      " |-- Race: string (nullable = true)\n",
      " |-- Count: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "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</td>\n",
       "      <td>41862</td>\n",
       "      <td>82463</td>\n",
       "      <td>1562</td>\n",
       "      <td>30908</td>\n",
       "      <td>2.6</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</td>\n",
       "      <td>49500</td>\n",
       "      <td>93629</td>\n",
       "      <td>4147</td>\n",
       "      <td>32935</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</td>\n",
       "      <td>46799</td>\n",
       "      <td>84839</td>\n",
       "      <td>4819</td>\n",
       "      <td>8229</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</td>\n",
       "      <td>87105</td>\n",
       "      <td>175232</td>\n",
       "      <td>5821</td>\n",
       "      <td>33878</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</td>\n",
       "      <td>143873</td>\n",
       "      <td>281913</td>\n",
       "      <td>5829</td>\n",
       "      <td>86253</td>\n",
       "      <td>2.73</td>\n",
       "      <td>NJ</td>\n",
       "      <td>White</td>\n",
       "      <td>76402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Peoria</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>33.1</td>\n",
       "      <td>56229</td>\n",
       "      <td>62432</td>\n",
       "      <td>118661</td>\n",
       "      <td>6634</td>\n",
       "      <td>7517</td>\n",
       "      <td>2.4</td>\n",
       "      <td>IL</td>\n",
       "      <td>American Indian and Alaska Native</td>\n",
       "      <td>1343</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Avondale</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>29.1</td>\n",
       "      <td>38712</td>\n",
       "      <td>41971</td>\n",
       "      <td>80683</td>\n",
       "      <td>4815</td>\n",
       "      <td>8355</td>\n",
       "      <td>3.18</td>\n",
       "      <td>AZ</td>\n",
       "      <td>Black or African-American</td>\n",
       "      <td>11592</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>West Covina</td>\n",
       "      <td>California</td>\n",
       "      <td>39.8</td>\n",
       "      <td>51629</td>\n",
       "      <td>56860</td>\n",
       "      <td>108489</td>\n",
       "      <td>3800</td>\n",
       "      <td>37038</td>\n",
       "      <td>3.56</td>\n",
       "      <td>CA</td>\n",
       "      <td>Asian</td>\n",
       "      <td>32716</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>O'Fallon</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>36.0</td>\n",
       "      <td>41762</td>\n",
       "      <td>43270</td>\n",
       "      <td>85032</td>\n",
       "      <td>5783</td>\n",
       "      <td>3269</td>\n",
       "      <td>2.77</td>\n",
       "      <td>MO</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>2583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>High Point</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>35.5</td>\n",
       "      <td>51751</td>\n",
       "      <td>58077</td>\n",
       "      <td>109828</td>\n",
       "      <td>5204</td>\n",
       "      <td>16315</td>\n",
       "      <td>2.65</td>\n",
       "      <td>NC</td>\n",
       "      <td>Asian</td>\n",
       "      <td>11060</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   \n",
       "1            Quincy   Massachusetts       41.0           44129   \n",
       "2            Hoover         Alabama       38.5           38040   \n",
       "3  Rancho Cucamonga      California       34.5           88127   \n",
       "4            Newark      New Jersey       34.6          138040   \n",
       "5            Peoria        Illinois       33.1           56229   \n",
       "6          Avondale         Arizona       29.1           38712   \n",
       "7       West Covina      California       39.8           51629   \n",
       "8          O'Fallon        Missouri       36.0           41762   \n",
       "9        High Point  North Carolina       35.5           51751   \n",
       "\n",
       "  Female Population Total Population Number of Veterans Foreign-born  \\\n",
       "0             41862            82463               1562        30908   \n",
       "1             49500            93629               4147        32935   \n",
       "2             46799            84839               4819         8229   \n",
       "3             87105           175232               5821        33878   \n",
       "4            143873           281913               5829        86253   \n",
       "5             62432           118661               6634         7517   \n",
       "6             41971            80683               4815         8355   \n",
       "7             56860           108489               3800        37038   \n",
       "8             43270            85032               5783         3269   \n",
       "9             58077           109828               5204        16315   \n",
       "\n",
       "  Average Household Size State Code                               Race  Count  \n",
       "0                    2.6         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  \n",
       "5                    2.4         IL  American Indian and Alaska Native   1343  \n",
       "6                   3.18         AZ          Black or African-American  11592  \n",
       "7                   3.56         CA                              Asian  32716  \n",
       "8                   2.77         MO                 Hispanic or Latino   2583  \n",
       "9                   2.65         NC                              Asian  11060  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US_City_df.printSchema()\n",
    "US_City_df.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Saint George</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Worcester</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tyler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Springfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Caguas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Charleston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Pasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Corona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tempe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>North Las Vegas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Auburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Palatine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Thornton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Augusta-Richmond County consolidated government</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Bethlehem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Waukegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Hollywood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Pittsburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Toms River</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Pembroke Pines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Savannah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sandy Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Palo Alto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Coral Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Compton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Westland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Brooklyn Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Omaha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Anchorage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>Mountain View</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>Lawrence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>Warner Robins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>Carmichael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>Roseville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>Orem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>Davenport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>Meridian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>Eugene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>Bismarck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>Federal Way</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>548</th>\n",
       "      <td>Midland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>549</th>\n",
       "      <td>Cedar Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>550</th>\n",
       "      <td>Downey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>Pine Hills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>Denver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>553</th>\n",
       "      <td>Las Cruces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>Fort Worth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>555</th>\n",
       "      <td>Rapid City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>557</th>\n",
       "      <td>Fort Wayne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>Provo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>Nashua</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>Homestead</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>Pawtucket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>Modesto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>Apple Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>Deerfield Beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>Sioux Falls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>Kenosha</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>567 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                City\n",
       "0                                       Saint George\n",
       "1                                          Worcester\n",
       "2                                              Tyler\n",
       "3                                        Springfield\n",
       "4                                             Caguas\n",
       "5                                         Charleston\n",
       "6                                              Pasco\n",
       "7                                             Corona\n",
       "8                                              Tempe\n",
       "9                                    North Las Vegas\n",
       "10                                            Auburn\n",
       "11                                          Palatine\n",
       "12                                          Thornton\n",
       "13   Augusta-Richmond County consolidated government\n",
       "14                                         Bethlehem\n",
       "15                                           Phoenix\n",
       "16                                          Waukegan\n",
       "17                                         Hollywood\n",
       "18                                         Pittsburg\n",
       "19                                        Toms River\n",
       "20                                    Pembroke Pines\n",
       "21                                          Savannah\n",
       "22                                     Sandy Springs\n",
       "23                                         Palo Alto\n",
       "24                                     Coral Springs\n",
       "25                                           Compton\n",
       "26                                          Westland\n",
       "27                                     Brooklyn Park\n",
       "28                                             Omaha\n",
       "29                                         Anchorage\n",
       "..                                               ...\n",
       "537                                    Mountain View\n",
       "538                                         Lawrence\n",
       "539                                    Warner Robins\n",
       "540                                       Carmichael\n",
       "541                                        Roseville\n",
       "542                                             Orem\n",
       "543                                        Davenport\n",
       "544                                         Meridian\n",
       "545                                           Eugene\n",
       "546                                         Bismarck\n",
       "547                                      Federal Way\n",
       "548                                          Midland\n",
       "549                                       Cedar Park\n",
       "550                                           Downey\n",
       "551                                       Pine Hills\n",
       "552                                           Denver\n",
       "553                                       Las Cruces\n",
       "554                                       Fort Worth\n",
       "555                                       Rapid City\n",
       "556                                           Boston\n",
       "557                                       Fort Wayne\n",
       "558                                            Provo\n",
       "559                                           Nashua\n",
       "560                                        Homestead\n",
       "561                                        Pawtucket\n",
       "562                                          Modesto\n",
       "563                                     Apple Valley\n",
       "564                                  Deerfield Beach\n",
       "565                                      Sioux Falls\n",
       "566                                          Kenosha\n",
       "\n",
       "[567 rows x 1 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = US_City_df.select('City').drop_duplicates()\n",
    "c.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>OR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>KY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>MT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>MD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>ME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>WA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>ND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>MS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>OH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>TN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>PA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>GA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>FL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>OK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>UT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>HI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   State Code\n",
       "0          AZ\n",
       "1          SC\n",
       "2          LA\n",
       "3          MN\n",
       "4          NJ\n",
       "5          DC\n",
       "6          OR\n",
       "7          VA\n",
       "8          RI\n",
       "9          KY\n",
       "10         NH\n",
       "11         MI\n",
       "12         NV\n",
       "13         WI\n",
       "14         ID\n",
       "15         CA\n",
       "16         CT\n",
       "17         NE\n",
       "18         MT\n",
       "19         NC\n",
       "20         MD\n",
       "21         DE\n",
       "22         MO\n",
       "23         IL\n",
       "24         ME\n",
       "25         WA\n",
       "26         ND\n",
       "27         MS\n",
       "28         AL\n",
       "29         IN\n",
       "30         OH\n",
       "31         TN\n",
       "32         NM\n",
       "33         IA\n",
       "34         PA\n",
       "35         SD\n",
       "36         NY\n",
       "37         TX\n",
       "38         GA\n",
       "39         MA\n",
       "40         KS\n",
       "41         FL\n",
       "42         CO\n",
       "43         AK\n",
       "44         AR\n",
       "45         OK\n",
       "46         PR\n",
       "47         UT\n",
       "48         HI"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = US_City_df.select('State Code').drop_duplicates()\n",
    "c.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Saint George</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Worcester</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tyler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Springfield</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Caguas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Charleston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Pasco</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Corona</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tempe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>North Las Vegas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Auburn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Palatine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Thornton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Augusta-Richmond County consolidated government</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Bethlehem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Phoenix</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Waukegan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Hollywood</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Pittsburg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Toms River</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Pembroke Pines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Savannah</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Sandy Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Palo Alto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Coral Springs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Compton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Westland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Brooklyn Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Omaha</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Anchorage</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>537</th>\n",
       "      <td>Mountain View</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>538</th>\n",
       "      <td>Lawrence</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>539</th>\n",
       "      <td>Warner Robins</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>Carmichael</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>Roseville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>542</th>\n",
       "      <td>Orem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>Davenport</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>Meridian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>Eugene</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>Bismarck</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>Federal Way</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>548</th>\n",
       "      <td>Midland</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>549</th>\n",
       "      <td>Cedar Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>550</th>\n",
       "      <td>Downey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>551</th>\n",
       "      <td>Pine Hills</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>Denver</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>553</th>\n",
       "      <td>Las Cruces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>Fort Worth</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>555</th>\n",
       "      <td>Rapid City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>556</th>\n",
       "      <td>Boston</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>557</th>\n",
       "      <td>Fort Wayne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>Provo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>Nashua</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>Homestead</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>Pawtucket</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>562</th>\n",
       "      <td>Modesto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>Apple Valley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>564</th>\n",
       "      <td>Deerfield Beach</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>565</th>\n",
       "      <td>Sioux Falls</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>566</th>\n",
       "      <td>Kenosha</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>567 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                City\n",
       "0                                       Saint George\n",
       "1                                          Worcester\n",
       "2                                              Tyler\n",
       "3                                        Springfield\n",
       "4                                             Caguas\n",
       "5                                         Charleston\n",
       "6                                              Pasco\n",
       "7                                             Corona\n",
       "8                                              Tempe\n",
       "9                                    North Las Vegas\n",
       "10                                            Auburn\n",
       "11                                          Palatine\n",
       "12                                          Thornton\n",
       "13   Augusta-Richmond County consolidated government\n",
       "14                                         Bethlehem\n",
       "15                                           Phoenix\n",
       "16                                          Waukegan\n",
       "17                                         Hollywood\n",
       "18                                         Pittsburg\n",
       "19                                        Toms River\n",
       "20                                    Pembroke Pines\n",
       "21                                          Savannah\n",
       "22                                     Sandy Springs\n",
       "23                                         Palo Alto\n",
       "24                                     Coral Springs\n",
       "25                                           Compton\n",
       "26                                          Westland\n",
       "27                                     Brooklyn Park\n",
       "28                                             Omaha\n",
       "29                                         Anchorage\n",
       "..                                               ...\n",
       "537                                    Mountain View\n",
       "538                                         Lawrence\n",
       "539                                    Warner Robins\n",
       "540                                       Carmichael\n",
       "541                                        Roseville\n",
       "542                                             Orem\n",
       "543                                        Davenport\n",
       "544                                         Meridian\n",
       "545                                           Eugene\n",
       "546                                         Bismarck\n",
       "547                                      Federal Way\n",
       "548                                          Midland\n",
       "549                                       Cedar Park\n",
       "550                                           Downey\n",
       "551                                       Pine Hills\n",
       "552                                           Denver\n",
       "553                                       Las Cruces\n",
       "554                                       Fort Worth\n",
       "555                                       Rapid City\n",
       "556                                           Boston\n",
       "557                                       Fort Wayne\n",
       "558                                            Provo\n",
       "559                                           Nashua\n",
       "560                                        Homestead\n",
       "561                                        Pawtucket\n",
       "562                                          Modesto\n",
       "563                                     Apple Valley\n",
       "564                                  Deerfield Beach\n",
       "565                                      Sioux Falls\n",
       "566                                          Kenosha\n",
       "\n",
       "[567 rows x 1 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = US_City_df.select('City').drop_duplicates()\n",
    "c.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>Race</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Black or African-American</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hispanic or Latino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>White</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Asian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>American Indian and Alaska Native</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Race\n",
       "0          Black or African-American\n",
       "1                 Hispanic or Latino\n",
       "2                              White\n",
       "3                              Asian\n",
       "4  American Indian and Alaska Native"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rase = US_City_df.select('Race').drop_duplicates()\n",
    "rase.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2891"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US_City_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- ident: string (nullable = true)\n",
      " |-- type: string (nullable = true)\n",
      " |-- name: string (nullable = true)\n",
      " |-- elevation_ft: string (nullable = true)\n",
      " |-- continent: string (nullable = true)\n",
      " |-- iso_country: string (nullable = true)\n",
      " |-- iso_region: string (nullable = true)\n",
      " |-- municipality: string (nullable = true)\n",
      " |-- gps_code: string (nullable = true)\n",
      " |-- iata_code: string (nullable = true)\n",
      " |-- local_code: string (nullable = true)\n",
      " |-- coordinates: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "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</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-PA</td>\n",
       "      <td>Bensalem</td>\n",
       "      <td>00A</td>\n",
       "      <td>None</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</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-KS</td>\n",
       "      <td>Leoti</td>\n",
       "      <td>00AA</td>\n",
       "      <td>None</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</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AK</td>\n",
       "      <td>Anchor Point</td>\n",
       "      <td>00AK</td>\n",
       "      <td>None</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</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AL</td>\n",
       "      <td>Harvest</td>\n",
       "      <td>00AL</td>\n",
       "      <td>None</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</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AR</td>\n",
       "      <td>Newport</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>-91.254898, 35.6087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>00AS</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Fulton Airport</td>\n",
       "      <td>1100</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-OK</td>\n",
       "      <td>Alex</td>\n",
       "      <td>00AS</td>\n",
       "      <td>None</td>\n",
       "      <td>00AS</td>\n",
       "      <td>-97.8180194, 34.9428028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>00AZ</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Cordes Airport</td>\n",
       "      <td>3810</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-AZ</td>\n",
       "      <td>Cordes</td>\n",
       "      <td>00AZ</td>\n",
       "      <td>None</td>\n",
       "      <td>00AZ</td>\n",
       "      <td>-112.16500091552734, 34.305599212646484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>00CA</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Goldstone /Gts/ Airport</td>\n",
       "      <td>3038</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-CA</td>\n",
       "      <td>Barstow</td>\n",
       "      <td>00CA</td>\n",
       "      <td>None</td>\n",
       "      <td>00CA</td>\n",
       "      <td>-116.888000488, 35.350498199499995</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>00CL</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Williams Ag Airport</td>\n",
       "      <td>87</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-CA</td>\n",
       "      <td>Biggs</td>\n",
       "      <td>00CL</td>\n",
       "      <td>None</td>\n",
       "      <td>00CL</td>\n",
       "      <td>-121.763427, 39.427188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>00CN</td>\n",
       "      <td>heliport</td>\n",
       "      <td>Kitchen Creek Helibase Heliport</td>\n",
       "      <td>3350</td>\n",
       "      <td>NA</td>\n",
       "      <td>US</td>\n",
       "      <td>US-CA</td>\n",
       "      <td>Pine Valley</td>\n",
       "      <td>00CN</td>\n",
       "      <td>None</td>\n",
       "      <td>00CN</td>\n",
       "      <td>-116.4597417, 32.7273736</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ident           type                                name elevation_ft  \\\n",
       "0   00A       heliport                   Total Rf Heliport           11   \n",
       "1  00AA  small_airport                Aero B Ranch Airport         3435   \n",
       "2  00AK  small_airport                        Lowell Field          450   \n",
       "3  00AL  small_airport                        Epps Airpark          820   \n",
       "4  00AR         closed  Newport Hospital & Clinic Heliport          237   \n",
       "5  00AS  small_airport                      Fulton Airport         1100   \n",
       "6  00AZ  small_airport                      Cordes Airport         3810   \n",
       "7  00CA  small_airport             Goldstone /Gts/ Airport         3038   \n",
       "8  00CL  small_airport                 Williams Ag Airport           87   \n",
       "9  00CN       heliport     Kitchen Creek Helibase Heliport         3350   \n",
       "\n",
       "  continent iso_country iso_region  municipality gps_code iata_code  \\\n",
       "0        NA          US      US-PA      Bensalem      00A      None   \n",
       "1        NA          US      US-KS         Leoti     00AA      None   \n",
       "2        NA          US      US-AK  Anchor Point     00AK      None   \n",
       "3        NA          US      US-AL       Harvest     00AL      None   \n",
       "4        NA          US      US-AR       Newport     None      None   \n",
       "5        NA          US      US-OK          Alex     00AS      None   \n",
       "6        NA          US      US-AZ        Cordes     00AZ      None   \n",
       "7        NA          US      US-CA       Barstow     00CA      None   \n",
       "8        NA          US      US-CA         Biggs     00CL      None   \n",
       "9        NA          US      US-CA   Pine Valley     00CN      None   \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       None                      -91.254898, 35.6087  \n",
       "5       00AS                  -97.8180194, 34.9428028  \n",
       "6       00AZ  -112.16500091552734, 34.305599212646484  \n",
       "7       00CA       -116.888000488, 35.350498199499995  \n",
       "8       00CL                   -121.763427, 39.427188  \n",
       "9       00CN                 -116.4597417, 32.7273736  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_codes_df.printSchema()\n",
    "airport_codes_df.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+\n",
      "|continent|\n",
      "+---------+\n",
      "|       NA|\n",
      "|       SA|\n",
      "|       AS|\n",
      "|       AN|\n",
      "|       OC|\n",
      "|       EU|\n",
      "|       AF|\n",
      "+---------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "a = airport_codes_df.select('continent').drop_duplicates()\n",
    "a.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>iso_region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>US-TN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>US-OK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>US-VT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>US-SD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>US-WA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>US-IN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>US-AL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>US-NY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>US-MS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>US-MT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>US-NC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>US-CT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>US-OR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>US-IA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>US-NV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>US-RI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>US-AK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>US-WV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>US-MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>US-MD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>US-U-A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>US-AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>US-NH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>US-DC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>US-ME</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>US-DE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>US-CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>US-OH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>US-MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>US-GA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>US-NJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>US-CA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>US-PA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>US-ID</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>US-ND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>US-IL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>US-FL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>US-KY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>US-WY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>US-NM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>US-MO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>US-UT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>US-SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>US-KS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>US-TX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>US-WI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>US-VA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>US-MI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>US-AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>US-NE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>US-HI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>US-LA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   iso_region\n",
       "0       US-TN\n",
       "1       US-OK\n",
       "2       US-VT\n",
       "3       US-SD\n",
       "4       US-WA\n",
       "5       US-IN\n",
       "6       US-AL\n",
       "7       US-NY\n",
       "8       US-MS\n",
       "9       US-MT\n",
       "10      US-NC\n",
       "11      US-CT\n",
       "12      US-OR\n",
       "13      US-IA\n",
       "14      US-NV\n",
       "15      US-RI\n",
       "16      US-AK\n",
       "17      US-WV\n",
       "18      US-MA\n",
       "19      US-MD\n",
       "20     US-U-A\n",
       "21      US-AZ\n",
       "22      US-NH\n",
       "23      US-DC\n",
       "24      US-ME\n",
       "25      US-DE\n",
       "26      US-CO\n",
       "27      US-OH\n",
       "28      US-MN\n",
       "29      US-GA\n",
       "30      US-NJ\n",
       "31      US-CA\n",
       "32      US-PA\n",
       "33      US-ID\n",
       "34      US-ND\n",
       "35      US-IL\n",
       "36      US-FL\n",
       "37      US-KY\n",
       "38      US-WY\n",
       "39      US-NM\n",
       "40      US-MO\n",
       "41      US-UT\n",
       "42      US-SC\n",
       "43      US-KS\n",
       "44      US-TX\n",
       "45      US-WI\n",
       "46      US-VA\n",
       "47      US-MI\n",
       "48      US-AR\n",
       "49      US-NE\n",
       "50      US-HI\n",
       "51      US-LA"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = airport_codes_df.filter('iso_country = \"US\"').select('iso_region').drop_duplicates()\n",
    "a.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>iso_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>FI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>PM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>UA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>ZM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>KI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>RO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>SL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>SB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>LA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>BS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>BW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>MN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>PL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>RE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>PS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>MK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>MX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>PF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>ZZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>GL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>SY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>215</th>\n",
       "      <td>ER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>CO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>217</th>\n",
       "      <td>IM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>UY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>220</th>\n",
       "      <td>NG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>TF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>222</th>\n",
       "      <td>JE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>YT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>224</th>\n",
       "      <td>AR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>225</th>\n",
       "      <td>CF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>PW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>227</th>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>LU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>SZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>NF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>EH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>VN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>233</th>\n",
       "      <td>MP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>IS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>AF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>BB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>BQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>SR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>CD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>DK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>TJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>AO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>ST</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>244 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    iso_country\n",
       "0            DZ\n",
       "1            LT\n",
       "2            MM\n",
       "3            CI\n",
       "4            TC\n",
       "5            AZ\n",
       "6            FI\n",
       "7            SC\n",
       "8            PM\n",
       "9            UA\n",
       "10           ZM\n",
       "11           KI\n",
       "12           RO\n",
       "13           SL\n",
       "14           SB\n",
       "15           NL\n",
       "16           LA\n",
       "17           BS\n",
       "18           BW\n",
       "19           MN\n",
       "20           AM\n",
       "21           PL\n",
       "22           RE\n",
       "23           PS\n",
       "24           MK\n",
       "25           MX\n",
       "26           PF\n",
       "27           ZZ\n",
       "28           GL\n",
       "29           TV\n",
       "..          ...\n",
       "214          SY\n",
       "215          ER\n",
       "216          CO\n",
       "217          IM\n",
       "218          SS\n",
       "219          UY\n",
       "220          NG\n",
       "221          TF\n",
       "222          JE\n",
       "223          YT\n",
       "224          AR\n",
       "225          CF\n",
       "226          PW\n",
       "227          PR\n",
       "228          LU\n",
       "229          SZ\n",
       "230          NF\n",
       "231          EH\n",
       "232          VN\n",
       "233          MP\n",
       "234          IS\n",
       "235          AF\n",
       "236          BB\n",
       "237          BQ\n",
       "238          SR\n",
       "239          CD\n",
       "240          DK\n",
       "241          TJ\n",
       "242          AO\n",
       "243          ST\n",
       "\n",
       "[244 rows x 1 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c= airport_codes_df.select('iso_country').drop_duplicates()\n",
    "c.toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[ident: string, type: string, name: string, elevation_ft: string, continent: string, iso_country: string, iso_region: string, municipality: string, gps_code: string, iata_code: string, local_code: string, coordinates: string]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_codes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- dt: string (nullable = true)\n",
      " |-- AverageTemperature: string (nullable = true)\n",
      " |-- AverageTemperatureUncertainty: string (nullable = true)\n",
      " |-- City: string (nullable = true)\n",
      " |-- Country: string (nullable = true)\n",
      " |-- Latitude: string (nullable = true)\n",
      " |-- Longitude: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "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.7369999999999999</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>None</td>\n",
       "      <td>None</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>None</td>\n",
       "      <td>None</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>None</td>\n",
       "      <td>None</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>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1744-04-01</td>\n",
       "      <td>5.7879999999999985</td>\n",
       "      <td>3.6239999999999997</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1744-05-01</td>\n",
       "      <td>10.644</td>\n",
       "      <td>1.2830000000000001</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1744-06-01</td>\n",
       "      <td>14.050999999999998</td>\n",
       "      <td>1.347</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1744-07-01</td>\n",
       "      <td>16.082</td>\n",
       "      <td>1.396</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1744-08-01</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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.7369999999999999  Århus   \n",
       "1  1743-12-01                None                          None  Århus   \n",
       "2  1744-01-01                None                          None  Århus   \n",
       "3  1744-02-01                None                          None  Århus   \n",
       "4  1744-03-01                None                          None  Århus   \n",
       "5  1744-04-01  5.7879999999999985            3.6239999999999997  Århus   \n",
       "6  1744-05-01              10.644            1.2830000000000001  Århus   \n",
       "7  1744-06-01  14.050999999999998                         1.347  Århus   \n",
       "8  1744-07-01              16.082                         1.396  Århus   \n",
       "9  1744-08-01                None                          None  Å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  \n",
       "5  Denmark   57.05N    10.33E  \n",
       "6  Denmark   57.05N    10.33E  \n",
       "7  Denmark   57.05N    10.33E  \n",
       "8  Denmark   57.05N    10.33E  \n",
       "9  Denmark   57.05N    10.33E  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temperature_df.printSchema()\n",
    "temperature_df.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8599212"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temperature_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 2: Explore and Assess the Data\n",
    "#### Explore the Data \n",
    "Identify data quality issues, like missing values, duplicate data, etc.\n",
    "\n",
    "#### Cleaning Steps\n",
    "Document steps necessary to clean the data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "first we will deal with missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3096313"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# before we remove the missing data\n",
    "I94_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_i94_df = I94_df.dropna(how=\"any\", subset=[\"ARRDATE\",'I94PORT','i94addr','I94BIR','I94VISA',\n",
    "                                                  \"gender\" , 'visatype'])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2551014"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# aftter we remove the missing data\n",
    "cleaned_i94_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2891"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "US_City_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_US_City_df = US_City_df.dropna(how=\"any\", subset=[\"City\",'State','Median Age','Male Population',\n",
    "                                                          'Female Population',\"Total Population\" , 'State Code','Race' , 'Count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2888"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_US_City_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8599212"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temperature_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_temperature_df = temperature_df.dropna(how=\"any\", subset=['dt','AverageTemperature','City','Country'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8235082"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_temperature_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55075"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_codes_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_airport_codes_df = airport_codes_df.dropna(how=\"any\", subset=['ident','iso_country','iso_region',\n",
    "                                                                      'local_code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "28686"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_airport_codes_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "now we will correct some columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "49\n",
      "['MD' 'MA' 'AL' 'CA' 'NJ' 'IL' 'AZ' 'MO' 'NC' 'PA' 'KS' 'FL' 'TX' 'VA' 'NV'\n",
      " 'CO' 'MI' 'CT' 'MN' 'UT' 'AR' 'TN' 'OK' 'WA' 'NY' 'GA' 'NE' 'KY' 'SC' 'LA'\n",
      " 'NM' 'IA' 'RI' 'PR' 'DC' 'WI' 'OR' 'NH' 'ND' 'DE' 'OH' 'ID' 'IN' 'AK' 'MS'\n",
      " 'HI' 'SD' 'ME' 'MT']\n"
     ]
    }
   ],
   "source": [
    "state_code = cleaned_US_City_df.toPandas()[\"State Code\"].unique()\n",
    "print(len(state_code))\n",
    "print(state_code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "@udf(StringType())\n",
    "def validate_state(n):  \n",
    "    if n in state_code:\n",
    "        return n\n",
    "    return 'other'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_i94_df = cleaned_i94_df.withColumn(\"i94addr\", validate_state(cleaned_i94_df.i94addr))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2551014"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_i94_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_i94_df = cleaned_i94_df.filter('i94addr != \"other\"')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2435765"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_i94_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "@udf(StringType())\n",
    "def convert_datetime(x):\n",
    "    return (datetime.datetime(1960, 1, 1).date() + datetime.timedelta(x)).isoformat()\n",
    "   \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_i94_df = cleaned_i94_df.withColumn(\"arrdate\", convert_datetime(cleaned_i94_df.arrdate))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "correct the name of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_i94_df = cleaned_i94_df.selectExpr('cicid as ID' , 'i94addr as state_code' , 'arrdate as Date' ,'i94port as city_code',\n",
    "                                'i94bir as age' , 'i94visa as visa_code' , 'visatype','count').drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2888"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_US_City_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_US_City_df = cleaned_US_City_df.select('City','State'\n",
    "                                  ,'Median Age','Male Population','Female Population',\n",
    "                                'Total Population' ,\n",
    "                                                   'State Code' , 'Race' , 'Count').drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_US_City_df = cleaned_US_City_df.withColumnRenamed('Total Population','Total_Population')\\\n",
    "       .withColumnRenamed(\"Male Population\", \"Male_Population\")\\\n",
    "        .withColumnRenamed(\"Median Age\", \"Median_Age\") \\\n",
    "        .withColumnRenamed(\"State Code\", \"State_Code\") \\\n",
    "        .withColumnRenamed(\"City\", \"City_Name\") \\\n",
    "        .withColumnRenamed(\"State\", \"State_Name\") \\\n",
    "        .withColumnRenamed(\"Count\", \"count_of_Race_in_city\").drop_duplicates()\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2888"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_US_City_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_temperature_df = cleaned_temperature_df.filter('Country = \"United States\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "661524"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_temperature_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_temperature_df = cleaned_temperature_df.select('AverageTemperature','City').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "626244"
      ]
     },
     "execution_count": 133,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_temperature_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_airport_codes_df =  cleaned_airport_codes_df.filter('iso_country = \"US\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21236"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_airport_codes_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "cleaned_airport_codes_df = cleaned_airport_codes_df.selectExpr('ident as id','type','name','iso_country',\n",
    "                                                                      'local_code','coordinates').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21236"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_airport_codes_df.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "### Step 3: Define the Data Model\n",
    "#### 3.1 Conceptual Data Model\n",
    "we will use star schema as our data model because Starr schemas are designed to optimize user ease-of-use and minimizing the number of tables join to do the query.\n",
    "the schema:\n",
    "\n",
    "Dimension Tables :\n",
    "\n",
    "    city_df\n",
    "        'City_Name',\n",
    "         'State_Name',\n",
    "         'Median_Age',\n",
    "         'Male_Population',\n",
    "         'Female Population',\n",
    "         'Total_Population',\n",
    "          'State_Code',\n",
    "           'Race',\n",
    "         'count_of_Race_in_city'\n",
    "       \n",
    "    \n",
    "    immigrant_df\n",
    "        \n",
    "    \n",
    "    city_temp_df\n",
    "        'AverageTemperature'\n",
    "        'City'\n",
    "    \n",
    "    ariport_df\n",
    "        id'\n",
    "        'type'\n",
    "        'name'\n",
    "        'iso_country'\n",
    "         'local_code'\n",
    "         'coordinates'\n",
    "         \n",
    "        \n",
    "        \n",
    "    \n",
    "Fact Table\n",
    "\n",
    "    immigration_df:\n",
    "        id\n",
    "        state_code\n",
    "        city_code\n",
    "        Date\n",
    "        count\n",
    "\n",
    "\n"
   ]
  },
  {
   "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": 10,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def immigration_df_ETL():\n",
    "    cleaned_i94_df = I94_df.dropna(how=\"any\", subset=[\"ARRDATE\",'I94PORT','i94addr','I94BIR','I94VISA',\n",
    "                                                  \"gender\" , 'visatype'])\n",
    "    state_code = US_City_df.toPandas()[\"State Code\"].unique()\n",
    "    @udf(StringType())\n",
    "    def validate_state(n):  \n",
    "        if n in state_code:\n",
    "            return n\n",
    "        return 'other'\n",
    "\n",
    "    cleaned_i94_df = cleaned_i94_df.withColumn(\"i94addr\", validate_state(cleaned_i94_df.i94addr))\n",
    "    cleaned_i94_df = cleaned_i94_df.filter('i94addr != \"other\"')\n",
    "\n",
    "    @udf(StringType())\n",
    "    def convert_datetime(x):\n",
    "        return (datetime.datetime(1960, 1, 1).date() + datetime.timedelta(x)).isoformat()\n",
    "\n",
    "    cleaned_i94_df = cleaned_i94_df.selectExpr('cicid as ID' , 'i94addr as state_code' , 'arrdate as Date' ,'i94port as city_code',\n",
    "                                'i94bir as age' , 'i94visa as visa_code' , 'visatype','count').drop_duplicates()\n",
    "    \n",
    "    immigration_df = cleaned_i94_df.select('ID','state_code','city_code','Date','count')\n",
    "    return immigration_df\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def immigrant_df_ETL():\n",
    "    cleaned_i94_df = I94_df.dropna(how=\"any\", subset=[\"ARRDATE\",'I94PORT','i94addr','I94BIR','I94VISA',\n",
    "                                                  \"gender\" , 'visatype'])\n",
    "    state_code = US_City_df.toPandas()[\"State Code\"].unique()\n",
    "    @udf(StringType())\n",
    "    def validate_state(n):  \n",
    "        if n in state_code:\n",
    "            return n\n",
    "        return 'other'\n",
    "\n",
    "    cleaned_i94_df = cleaned_i94_df.withColumn(\"i94addr\", validate_state(cleaned_i94_df.i94addr))\n",
    "    cleaned_i94_df = cleaned_i94_df.filter('i94addr != \"other\"')\n",
    "\n",
    "    @udf(StringType())\n",
    "    def convert_datetime(x):\n",
    "        return (datetime.datetime(1960, 1, 1).date() + datetime.timedelta(x)).isoformat()\n",
    "\n",
    "    cleaned_i94_df = cleaned_i94_df.selectExpr('cicid as ID' , 'i94addr as state_code' , 'arrdate as Date' ,'i94port as city_code',\n",
    "                                'i94bir as age' , 'i94visa as visa_code' , 'visatype','count').drop_duplicates()\n",
    "    \n",
    "    immigration_df = cleaned_i94_df.select('ID','state_code','city_code','Date','count')\n",
    "    return immigration_df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def city_df_ETL():\n",
    "    cleaned_US_City_df = US_City_df.dropna(how=\"any\", subset=[\"City\",'State','Median Age','Male Population',\n",
    "                                                          'Female Population',\"Total Population\" , 'State Code','Race' , 'Count'])\n",
    "\n",
    "    cleaned_US_City_df = cleaned_US_City_df.select('City','State'\n",
    "                                  ,'Median Age','Male Population','Female Population',\n",
    "                                'Total Population' ,\n",
    "                                                   'State Code' , 'Race' , 'Count').drop_duplicates()\n",
    "\n",
    "    city_df = cleaned_US_City_df.withColumnRenamed('Total Population','Total_Population')\\\n",
    "       .withColumnRenamed(\"Male Population\", \"Male_Population\")\\\n",
    "        .withColumnRenamed(\"Median Age\", \"Median_Age\") \\\n",
    "        .withColumnRenamed(\"State Code\", \"State_Code\") \\\n",
    "        .withColumnRenamed(\"City\", \"City_Name\") \\\n",
    "        .withColumnRenamed(\"State\", \"State_Name\") \\\n",
    "        .withColumnRenamed(\"Count\", \"count_of_Race_in_city\").drop_duplicates()\n",
    "    return city_df\n",
    "    \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def city_temp_df_ETL():\n",
    "    cleaned_temperature_df = temperature_df.dropna(how=\"any\", subset=['dt','AverageTemperature','City','Country'])\n",
    "    city_temp_df = cleaned_temperature_df.filter('Country = \"United States\"')\n",
    "    city_temp_df = cleaned_temperature_df.select('AverageTemperature','City').drop_duplicates()\n",
    "    return city_temp_df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def ariport_df_ETL():\n",
    "    cleaned_airport_codes_df = airport_codes_df.dropna(how=\"any\", subset=['ident','iso_country','iso_region',\n",
    "                                                                      'local_code'])\n",
    "    \n",
    "    ariport_df =  cleaned_airport_codes_df.filter('iso_country = \"US\"')\n",
    "\n",
    "    \n",
    "    ariport_df = ariport_df.selectExpr('ident as id','type','name','iso_country',\n",
    "                                                                      'local_code','coordinates').drop_duplicates()\n",
    "\n",
    "    return ariport_df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "city_df = city_df_ETL()\n",
    "immigrant_df = immigrant_df_ETL()\n",
    "city_temp_df =  city_temp_df_ETL()\n",
    "ariport_df= ariport_df_ETL()\n",
    "immigration_df=immigration_df_ETL()"
   ]
  },
  {
   "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": 108,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['I94_df',\n",
       " 'US_City_df',\n",
       " 'airport_codes_df',\n",
       " 'temperature_df',\n",
       " 'c',\n",
       " 'a',\n",
       " '_23',\n",
       " 'cleaned_i94_df',\n",
       " 'cleaned_US_City_df',\n",
       " 'cleaned_temperature_df',\n",
       " 'cleaned_airport_codes_df',\n",
       " 'city_df',\n",
       " 'immigrant_df',\n",
       " 'city_temp_df',\n",
       " 'ariport_df',\n",
       " 'immigration_df']"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql import DataFrame\n",
    "allDataFrames = [k for (k, v) in globals().items() if isinstance(v, DataFrame)]\n",
    "allDataFrames"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "\n",
    "def cheak_table(*args) :\n",
    "    \n",
    "    for table in args:\n",
    "\n",
    "        print(isinstance(table,DataFrame))\n",
    "        \n",
    "cheak_table(immigrant_df,city_temp_df,city_df,ariport_df,immigration_df)        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def cheak_if_the_table_is_not_null(*args):\n",
    "    for table in args:\n",
    "        if table.count() > 0:\n",
    "            print(True)\n",
    "        else:\n",
    "             print(False)\n",
    "            \n",
    " \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "cheak_if_the_table_is_not_null(immigrant_df,city_temp_df,city_df,ariport_df,immigration_df)        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "def check_for_Exclude_duplicate(*args):\n",
    "    for table in args:\n",
    "        if table.count() > table.dropDuplicates().count():\n",
    "            print(False)\n",
    "        else:\n",
    "             print(True)\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "True\n",
      "True\n",
      "True\n",
      "True\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "check_for_Exclude_duplicate(immigrant_df,city_temp_df,city_df,ariport_df,immigration_df)        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### 4.3 Data dictionary \n",
    "Dimension Tables\n",
    "\n",
    "immigrant_df:\n",
    "\n",
    "    id: id of immigrant\n",
    "    age: age of immigrant\n",
    "    gender: gender of immigrant\n",
    "    visa_type: immigrant's visa type\n",
    "    visa_code : Visa codes collapsed into three categories:\n",
    "           1 = Business\n",
    "           2 = Pleasure\n",
    "           3 = Student\n",
    "    \n",
    "\n",
    "city_df:\n",
    "\n",
    "       City_Name: name of the city\n",
    "       State_Name: name of the  State\n",
    "       Median_Age: median age of the city\n",
    "       Male_Population: Population of Male in city\n",
    "       Female Population: Population of Female  in city\n",
    "       Total_Population: total Population in city\n",
    "       State_Code: state code of the city\n",
    "       Race:Race categories\n",
    "       Black or African-American\n",
    "        Hispanic or Latino\n",
    "        White\n",
    "        Asian\n",
    "        American Indian and Alaska Native\n",
    "       count_of_Race_in_city: count_of_Race_in_city\n",
    "\n",
    "\n",
    "city_temp_df:\n",
    "\n",
    "    City: city Name\n",
    "    AverageTemperature: average temperature in city \n",
    "\n",
    "ariport_df:\n",
    "\n",
    "    id: ariport ID\n",
    "    type:type of airport\n",
    "    name: name of airport\n",
    "    iso_country:  internationally recognized codes that designate every country\n",
    "    local_code:local_code of ariport\n",
    "    coordinates : coordinates of Flight \n",
    "    \n",
    "Fact Table\n",
    "\n",
    "immigration_df:\n",
    "\n",
    "    id: id\n",
    "    state_code: state code of arrival city\n",
    "    city_code: city port code of arrival city\n",
    "    date: date of arrival\n",
    "    count: count of immigrant's entries into the US\n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "#### Step 5: Complete Project Write Up\n",
    "* technologies: since the Data set is huge i decide to use pyspark to deal with the big data and use Pandas in first to divide the data into chunksize to explore the Data. \n",
    "* Update Cycles\n",
    "    1- reporting  \n",
    "    2- new data to be lowod into the system\n",
    "    3 - through Apache Airflow\n",
    "* Write a description of how you would approach the problem differently under the following scenarios:\n",
    " * The data was increased by 100x. We can get more nodes to spark cluster and upgrade the node power.\n",
    " * The data populates a dashboard that must be updated on a daily basis by 7am every day. we can uee Apache Airflow to schedule and automate the   data pipeline process.\n",
    " \n",
    " * The database needed to be accessed by 100+ people. We can use Amazon Redshift to handle the accessed by users and use IAM role which is an       IAM entity that defines a set of permissions to users for making AWS service requests. \n",
    "   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}