spark_tranform_exploration/Exploration_NB.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "1fe14682-96ab-4ce6-9f04-f778b8702fb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import findspark\n",
    "findspark.init()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "08e73af2-6f5d-4962-952d-a69fa98b6c22",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "spark = SparkSession \\\n",
    "    .builder \\\n",
    "    .appName(\"EDA\") \\\n",
    "    .config(\"spark.jars.packages\", \"org.apache.hadoop:hadoop-aws:3.3.1\") \\\n",
    "    .config(\"spark.jars.packages\",\"saurfang:spark-sas7bdat:2.0.0-s_2.11\") \\\n",
    "    .getOrCreate()\n",
    "df_imm=spark.read.parquet(\"../data/sas_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "b894df8f-392b-47f5-95fa-9c9e0318c29b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2215673"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_imm.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cb03c755-771e-47fc-9f48-2a813ca75abc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_states_mapping=spark.read.option(\"header\",\"true\").format(\"csv\").csv(\"../data/State_mapping.csv\",)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "76dc39e0-7335-4013-96cc-4098b636a4b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(code='AL', state='ALABAMA'),\n",
       " Row(code='AK', state='ALASKA'),\n",
       " Row(code='AZ', state='ARIZONA'),\n",
       " Row(code='AR', state='ARKANSAS'),\n",
       " Row(code='CA', state='CALIFORNIA')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_states_mapping.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "b0576e82-cb11-4720-9e63-17e3408537a9",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "22/08/17 20:00:02 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n"
     ]
    }
   ],
   "source": [
    "df_imm.createOrReplaceTempView(\"i94_data\")\n",
    "df_states_mapping.createOrReplaceTempView(\"states_mapping\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "b8477e01-a066-4342-af67-5a6386b63e8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_frame=spark.sql(\"\"\"\n",
    "SELECT * FROM\n",
    "i94_data as id LEFT JOIN states_mapping as sm ON id.i94ADDR = sm.code\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "36dd86a6-6301-4050-b208-ac6a41a45c4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pyspark.sql.functions as F"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "320968f8-cad8-4738-b3e8-7d5c4ac72c07",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(cicid=5748517.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='CA', depdate=20582.0, i94bir=40.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1976.0, dtaddto='10292016', gender='F', insnum=None, airline='QF', admnum=94953870030.0, fltno='00011', visatype='B1', code='CA', state='CALIFORNIA'),\n",
       " Row(cicid=5748518.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='NV', depdate=20591.0, i94bir=32.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1984.0, dtaddto='10292016', gender='F', insnum=None, airline='VA', admnum=94955622830.0, fltno='00007', visatype='B1', code='NV', state='NEVADA'),\n",
       " Row(cicid=5748519.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='WA', depdate=20582.0, i94bir=29.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1987.0, dtaddto='10292016', gender='M', insnum=None, airline='DL', admnum=94956406530.0, fltno='00040', visatype='B1', code='WA', state='WASHINGTON'),\n",
       " Row(cicid=5748520.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='WA', depdate=20588.0, i94bir=29.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1987.0, dtaddto='10292016', gender='F', insnum=None, airline='DL', admnum=94956451430.0, fltno='00040', visatype='B1', code='WA', state='WASHINGTON'),\n",
       " Row(cicid=5748521.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='WA', depdate=20588.0, i94bir=28.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1988.0, dtaddto='10292016', gender='M', insnum=None, airline='DL', admnum=94956388130.0, fltno='00040', visatype='B1', code='WA', state='WASHINGTON')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out_frame.filter(F.col('matflag') == 'M').head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "bf8c941b-a1b8-48b5-8674-209b37b33408",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import udf, col\n",
    "from pyspark.sql.types import IntegerType, DoubleType\n",
    "import datetime\n",
    "from datetime import datetime as dtt\n",
    "def format_ts(value):\n",
    "    \"\"\"\n",
    "    This is an udf function which reads the value which is unix epoch time and convert that into timestamp in the defined format.\n",
    "    It returns a string as output.\n",
    "    \"\"\"\n",
    "    epoch = dtt(1960, 1, 1)\n",
    "    if value:\n",
    "        ts=(epoch + datetime.timedelta(days=int(value))).strftime(\"%Y-%m-%d\")\n",
    "        return ts\n",
    "    return None\n",
    "\n",
    "def days_of_stay(d1,d2):\n",
    "    if d1 and d2:\n",
    "        return (dtt.strptime(d1, \"%Y-%m-%d\") - dtt.strptime(d2, \"%Y-%m-%d\")).days\n",
    "    else:\n",
    "        return -99999"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1bbd2c4c-fde5-4e7f-a5b6-239f7bb1891f",
   "metadata": {},
   "outputs": [],
   "source": [
    "get_timestamp = udf(lambda x: format_ts(x))\n",
    "get_daysofstay = udf(days_of_stay, IntegerType())\n",
    "imm_data = out_frame.withColumn(\"arrdate_1\", get_timestamp(out_frame.arrdate))\n",
    "imm_data = imm_data.withColumn(\"depdate_1\", get_timestamp(imm_data.depdate))\n",
    "imm_data = imm_data.withColumn(\"days_of_stay\", get_daysofstay(imm_data.depdate_1, imm_data.arrdate_1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f0145e1c-e506-4158-ad5a-2eb03d27a04f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------+------+------+------+-------+-------+-------+-------+-------+------+-------+-----+--------+--------+-----+-------+-------+-------+-------+-------+--------+------+------+-------+--------------+-----+--------+----+----------+----------+----------+------------+\n",
      "|    cicid| i94yr|i94mon|i94cit|i94res|i94port|arrdate|i94mode|i94addr|depdate|i94bir|i94visa|count|dtadfile|visapost|occup|entdepa|entdepd|entdepu|matflag|biryear| dtaddto|gender|insnum|airline|        admnum|fltno|visatype|code|     state| arrdate_1| depdate_1|days_of_stay|\n",
      "+---------+------+------+------+------+-------+-------+-------+-------+-------+------+-------+-----+--------+--------+-----+-------+-------+-------+-------+-------+--------+------+------+-------+--------------+-----+--------+----+----------+----------+----------+------------+\n",
      "|5748517.0|2016.0|   4.0| 245.0| 438.0|    LOS|20574.0|    1.0|     CA|20582.0|  40.0|    1.0|  1.0|20160430|     SYD| null|      G|      O|   null|      M| 1976.0|10292016|     F|  null|     QF|9.495387003E10|00011|      B1|  CA|CALIFORNIA|2016-04-30|2016-05-08|           8|\n",
      "|5748518.0|2016.0|   4.0| 245.0| 438.0|    LOS|20574.0|    1.0|     NV|20591.0|  32.0|    1.0|  1.0|20160430|     SYD| null|      G|      O|   null|      M| 1984.0|10292016|     F|  null|     VA|9.495562283E10|00007|      B1|  NV|    NEVADA|2016-04-30|2016-05-17|          17|\n",
      "|5748519.0|2016.0|   4.0| 245.0| 438.0|    LOS|20574.0|    1.0|     WA|20582.0|  29.0|    1.0|  1.0|20160430|     SYD| null|      G|      O|   null|      M| 1987.0|10292016|     M|  null|     DL|9.495640653E10|00040|      B1|  WA|WASHINGTON|2016-04-30|2016-05-08|           8|\n",
      "|5748520.0|2016.0|   4.0| 245.0| 438.0|    LOS|20574.0|    1.0|     WA|20588.0|  29.0|    1.0|  1.0|20160430|     SYD| null|      G|      O|   null|      M| 1987.0|10292016|     F|  null|     DL|9.495645143E10|00040|      B1|  WA|WASHINGTON|2016-04-30|2016-05-14|          14|\n",
      "|5748521.0|2016.0|   4.0| 245.0| 438.0|    LOS|20574.0|    1.0|     WA|20588.0|  28.0|    1.0|  1.0|20160430|     SYD| null|      G|      O|   null|      M| 1988.0|10292016|     M|  null|     DL|9.495638813E10|00040|      B1|  WA|WASHINGTON|2016-04-30|2016-05-14|          14|\n",
      "+---------+------+------+------+------+-------+-------+-------+-------+-------+------+-------+-----+--------+--------+-----+-------+-------+-------+-------+-------+--------+------+------+-------+--------------+-----+--------+----+----------+----------+----------+------------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "imm_data.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "21a6e3e4-8fe1-4809-9dda-f8d1ba8eae29",
   "metadata": {},
   "outputs": [],
   "source": [
    "imm_data=imm_data.filter(F.col('cicid').isNotNull())\n",
    "dropDisDF=imm_data.dropDuplicates([\"cicid\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "eceebadc-2930-4a2e-a36e-d3aae7ae8e6e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "2215673"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dropDisDF.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "b6668713-c4db-492c-816d-5ee4d4870b0b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2215673"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imm_data.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e36d7520-2e2b-4f92-9303-6d822fd81908",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "us_demog = spark.read.option(\"header\",\"true\").option(\"delimiter\", \";\").format(\"csv\").csv(\"../data/us-cities-demographics.csv\", inferSchema=True)\n",
    "global_temp_states = spark.read.option(\"header\",\"true\").format(\"csv\").csv(\"../data/GlobalLandTemperaturesByState.csv\", inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "50a76688-e27c-4f9c-9e79-fce3eee6a533",
   "metadata": {},
   "outputs": [],
   "source": [
    "global_temp_us = global_temp_states.filter(F.col('Country') == \"United States\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "42872286-af2a-4fca-b06e-a9fac0b97f42",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- dt: string (nullable = true)\n",
      " |-- AverageTemperature: double (nullable = true)\n",
      " |-- AverageTemperatureUncertainty: double (nullable = true)\n",
      " |-- State: string (nullable = true)\n",
      " |-- Country: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "global_temp_us.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "6b248fd7-b6f0-4e19-8a98-641f1bd73607",
   "metadata": {},
   "outputs": [],
   "source": [
    "median_temperatures=global_temp_us.groupby(\"State\").agg(F.percentile_approx(\"AverageTemperature\", 0.5).alias(\"median_temperatures\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "690e145d-375e-4012-be97-08ce93c2f31c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Utah': 8.209,\n",
       " 'Hawaii': 22.506,\n",
       " 'Minnesota': 5.635,\n",
       " 'Ohio': 10.389,\n",
       " 'Arkansas': 15.797000000000002,\n",
       " 'Oregon': 7.7810000000000015,\n",
       " 'District Of Columbia': 11.783,\n",
       " 'Georgia (State)': 17.441000000000006,\n",
       " 'Texas': 18.378,\n",
       " 'North Dakota': 5.39,\n",
       " 'Pennsylvania': 9.079,\n",
       " 'Connecticut': 8.809000000000003,\n",
       " 'Nebraska': 9.318999999999999,\n",
       " 'Vermont': 5.579,\n",
       " 'Nevada': 9.26,\n",
       " 'Washington': 7.4460000000000015,\n",
       " 'Illinois': 11.373,\n",
       " 'Oklahoma': 15.699000000000002,\n",
       " 'Delaware': 11.501,\n",
       " 'Alaska': -5.8039999999999985,\n",
       " 'New Mexico': 11.614999999999998,\n",
       " 'West Virginia': 10.947,\n",
       " 'Missouri': 12.765999999999998,\n",
       " 'Rhode Island': 8.758000000000001,\n",
       " 'Montana': 5.122999999999999,\n",
       " 'Michigan': 6.68,\n",
       " 'Virginia': 12.707999999999998,\n",
       " 'North Carolina': 14.637,\n",
       " 'Wyoming': 4.86,\n",
       " 'Kansas': 12.418,\n",
       " 'New Jersey': 10.387,\n",
       " 'Maryland': 11.9,\n",
       " 'Alabama': 17.01,\n",
       " 'Arizona': 15.088,\n",
       " 'Iowa': 9.525,\n",
       " 'Massachusetts': 7.524,\n",
       " 'Kentucky': 13.041,\n",
       " 'Louisiana': 19.313,\n",
       " 'Mississippi': 17.575,\n",
       " 'New Hampshire': 5.709,\n",
       " 'Tennessee': 14.055,\n",
       " 'Florida': 21.66,\n",
       " 'Indiana': 11.138,\n",
       " 'Idaho': 5.1770000000000005,\n",
       " 'South Carolina': 16.861,\n",
       " 'South Dakota': 7.564,\n",
       " 'California': 13.98,\n",
       " 'New York': 7.184000000000001,\n",
       " 'Wisconsin': 6.837999999999999,\n",
       " 'Colorado': 6.747000000000001,\n",
       " 'Maine': 4.619}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "median_temperatures=median_temperatures.cache()\n",
    "mt_df=median_temperatures.toPandas()\n",
    "mt_df.head()\n",
    "mf_dt={}\n",
    "for value in mt_df.iterrows():\n",
    "    mf_dt[value[1][0]] = value[1][1]\n",
    "mf_dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "ab5fcf1a-3a51-4d93-b0a7-c5f2f2db3b51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ALC': 'ALCAN',\n",
       " 'ANC': 'ANCHORAGE',\n",
       " 'BAR': 'BAKERAAF-BAKERISLAND',\n",
       " 'DAC': 'DALTONSCACHE',\n",
       " 'PIZ': 'DEWSTATIONPTLAYDEW',\n",
       " 'DTH': 'DUTCHHARBOR',\n",
       " 'EGL': 'EAGLE',\n",
       " 'FRB': 'FAIRBANKS',\n",
       " 'HOM': 'HOMER',\n",
       " 'HYD': 'HYDER',\n",
       " 'JUN': 'JUNEAU',\n",
       " '5KE': 'KETCHIKAN',\n",
       " 'KET': 'KETCHIKAN',\n",
       " 'MOS': 'MOSESPOINTINTERMEDIATE',\n",
       " 'NIK': 'NIKISKI',\n",
       " 'NOM': 'NOM',\n",
       " 'PKC': 'POKERCREEK',\n",
       " 'ORI': 'PORTLIONSSPB',\n",
       " 'SKA': 'SKAGWAY',\n",
       " 'SNP': 'ST.PAULISLAND',\n",
       " 'TKI': 'TOKEEN',\n",
       " 'WRA': 'WRANGELL',\n",
       " 'HSV': 'MADISONCOUNTY-HUNTSVILLE',\n",
       " 'MOB': 'MOBILE',\n",
       " 'LIA': 'LITTLEROCK',\n",
       " 'ROG': 'ROGERSARPT',\n",
       " 'DOU': 'DOUGLAS',\n",
       " 'LUK': 'LUKEVILLE',\n",
       " 'MAP': 'MARIPOSAAZ',\n",
       " 'NAC': 'NACO',\n",
       " 'NOG': 'NOGALES',\n",
       " 'PHO': 'PHOENIX',\n",
       " 'POR': 'PORTAL',\n",
       " 'SLU': 'SANLUIS',\n",
       " 'SAS': 'SASABE',\n",
       " 'TUC': 'TUCSON',\n",
       " 'YUI': 'YUMA',\n",
       " 'AND': 'ANDRADE',\n",
       " 'BUR': 'BURBANK',\n",
       " 'CAL': 'CALEXICO',\n",
       " 'CAO': 'CAMPO',\n",
       " 'FRE': 'FRESNO',\n",
       " 'ICP': 'IMPERIALCOUNTY',\n",
       " 'LNB': 'LONGBEACH',\n",
       " 'LOS': 'LOSANGELES',\n",
       " 'BFL': 'MEADOWSFIELD-BAKERSFIELD',\n",
       " 'OAK': 'OAKLAND',\n",
       " 'ONT': 'ONTARIO',\n",
       " 'OTM': 'OTAYMESA',\n",
       " 'BLT': 'PACIFIC',\n",
       " 'PSP': 'PALMSPRINGS',\n",
       " 'SAC': 'SACRAMENTO',\n",
       " 'SLS': 'SALINAS',\n",
       " 'SDP': 'SANDIEGO',\n",
       " 'SFR': 'SANFRANCISCO',\n",
       " 'SNJ': 'SANJOSE',\n",
       " 'SLO': 'SANLUISOBISPO',\n",
       " 'SLI': 'SANLUISOBISPO',\n",
       " 'SPC': 'SANPEDRO',\n",
       " 'SYS': 'SANYSIDRO',\n",
       " 'SAA': 'SANTAANA',\n",
       " 'STO': 'STOCKTON',\n",
       " 'TEC': 'TECATE',\n",
       " 'TRV': 'TRAVIS-AFB',\n",
       " 'APA': 'ARAPAHOECOUNTY',\n",
       " 'ASE': 'ASPEN',\n",
       " 'COS': 'COLORADOSPRINGS',\n",
       " 'DEN': 'DENVER',\n",
       " 'DRO': 'LAPLATA-DURANGO',\n",
       " 'BDL': 'BRADLEYINTERNATIONAL',\n",
       " 'BGC': 'BRIDGEPORT',\n",
       " 'GRT': 'GROTON',\n",
       " 'HAR': 'HARTFORD',\n",
       " 'NWH': 'NEWHAVEN',\n",
       " 'NWL': 'NEWLONDON',\n",
       " 'TST': 'NEWINGTONDATACENTERTEST',\n",
       " 'WAS': 'WASHINGTONDC',\n",
       " 'DOV': 'DOVERAFB',\n",
       " 'DVD': 'DOVER-AFB',\n",
       " 'WLL': 'WILMINGTON',\n",
       " 'BOC': 'BOCAGRANDE',\n",
       " 'SRQ': 'BRADENTON-SARASOTA',\n",
       " 'CAN': 'CAPECANAVERAL',\n",
       " 'DAB': 'DAYTONABEACHINTERNATIONAL',\n",
       " 'FRN': 'FERNANDINA',\n",
       " 'FTL': 'FORTLAUDERDALE',\n",
       " 'FMY': 'FORTMYERS',\n",
       " 'FPF': 'FORTPIERCE',\n",
       " 'HUR': 'HURLBURTFIELD',\n",
       " 'GNV': 'JRALISONMUNI-GAINESVILLE',\n",
       " 'JAC': 'JACKSONVILLE',\n",
       " 'KEY': 'KEYWEST',\n",
       " 'LEE': 'LEESBURGMUNICIPALAIRPORT',\n",
       " 'MLB': 'MELBOURNE',\n",
       " 'MIA': 'MIAMI',\n",
       " 'APF': 'NAPLES',\n",
       " 'OPF': 'OPALOCKA',\n",
       " 'ORL': 'ORLANDO',\n",
       " 'PAN': 'PANAMACITY',\n",
       " 'PEN': 'PENSACOLA',\n",
       " 'PCF': 'PORTCANAVERAL',\n",
       " 'PEV': 'PORTEVERGLADES',\n",
       " 'PSJ': 'PORTSTJOE',\n",
       " 'SFB': 'SANFORD',\n",
       " 'SGJ': 'STAUGUSTINEARPT',\n",
       " 'SAU': 'STAUGUSTINE',\n",
       " 'FPR': 'STLUCIECOUNTY',\n",
       " 'SPE': 'STPETERSBURG',\n",
       " 'TAM': 'TAMPA',\n",
       " 'WPB': 'WESTPALMBEACH',\n",
       " 'ATL': 'ATLANTA',\n",
       " 'BRU': 'BRUNSWICK',\n",
       " 'AGS': 'BUSHFIELD-AUGUSTA',\n",
       " 'SAV': 'SAVANNAH',\n",
       " 'AGA': 'AGANA',\n",
       " 'HHW': 'HONOLULU',\n",
       " 'OGG': 'KAHULUI-MAUI',\n",
       " 'KOA': 'KEAHOLE-KONA',\n",
       " 'LIH': 'LIHUE',\n",
       " 'CID': 'CEDARRAPIDS/IOWACITY',\n",
       " 'DSM': 'DESMOINES',\n",
       " 'BOI': 'AIRTERM.(GOWENFLD)BOISE',\n",
       " 'EPI': 'EASTPORT',\n",
       " 'IDA': 'FANNINGFIELD-IDAHOFALLS',\n",
       " 'PTL': 'PORTHILL',\n",
       " 'SPI': 'SPRINGFIELD',\n",
       " 'CHI': 'CHICAGO',\n",
       " 'DPA': 'DUPAGECOUNTY',\n",
       " 'PIA': 'GREATERPEORIA',\n",
       " 'RFD': 'GREATERROCKFORD',\n",
       " 'UGN': 'MEMORIAL-WAUKEGAN',\n",
       " 'GAR': 'GARY',\n",
       " 'HMM': 'HAMMOND',\n",
       " 'INP': 'INDIANAPOLIS',\n",
       " 'MRL': 'MERRILLVILLE',\n",
       " 'SBN': 'SOUTHBEND',\n",
       " 'ICT': 'MID-CONTINENT-WITCHITA',\n",
       " 'LEX': 'BLUEGRASS-LEXINGTON',\n",
       " 'LOU': 'LOUISVILLE',\n",
       " 'BTN': 'BATONROUGE',\n",
       " 'LKC': 'LAKECHARLES',\n",
       " 'LAK': 'LAKECHARLES',\n",
       " 'MLU': 'MONROE',\n",
       " 'MGC': 'MORGANCITY',\n",
       " 'NOL': 'NEWORLEANS',\n",
       " 'BOS': 'BOSTON',\n",
       " 'GLO': 'GLOUCESTER',\n",
       " 'BED': 'HANSCOMFIELD-BEDFORD',\n",
       " 'LYN': 'LYNDEN',\n",
       " 'ADW': 'ANDREWSAFB',\n",
       " 'BAL': 'BALTIMORE',\n",
       " 'MKG': 'MUSKEGON',\n",
       " 'PAX': 'PATUXENTRIVER',\n",
       " 'BGM': 'BANGOR',\n",
       " 'BOO': 'BOOTHBAYHARBOR',\n",
       " 'BWM': 'BRIDGEWATER',\n",
       " 'BCK': 'BUCKPORT',\n",
       " 'CLS': 'CALAIS',\n",
       " 'CRB': 'CARIBOU',\n",
       " 'COB': 'COBURNGORE',\n",
       " 'EST': 'EASTCOURT',\n",
       " 'EPT': 'EASTPORTMUNICIPAL',\n",
       " 'EPM': 'EASTPORT',\n",
       " 'FOR': 'FORESTCITY',\n",
       " 'FTF': 'FORTFAIRFIELD',\n",
       " 'FTK': 'FORTKENT',\n",
       " 'HML': 'HAMIIN',\n",
       " 'HTM': 'HOULTON',\n",
       " 'JKM': 'JACKMAN',\n",
       " 'KAL': 'KALISPEL',\n",
       " 'LIM': 'LIMESTONE',\n",
       " 'LUB': 'LUBEC',\n",
       " 'MAD': 'MADAWASKA',\n",
       " 'POM': 'PORTLAND',\n",
       " 'RGM': 'RANGELEY',\n",
       " 'SBR': 'SOUTHBREWER',\n",
       " 'SRL': 'STAURELIE',\n",
       " 'SPA': 'STPAMPILE',\n",
       " 'VNB': 'VANBUREN',\n",
       " 'VCB': 'VANCEBORO',\n",
       " 'AGN': 'ALGONAC',\n",
       " 'ALP': 'ALPENA',\n",
       " 'BCY': 'BAYCITY',\n",
       " 'DET': 'DETROIT',\n",
       " 'GRP': 'GRANDRAPIDS',\n",
       " 'GRO': 'GROSSEISLE',\n",
       " 'ISL': 'ISLEROYALE',\n",
       " 'MRC': 'MARINECITY',\n",
       " 'MRY': 'MARYSVILLE',\n",
       " 'PTK': 'OAKLANDCOUNTY-PONTIAC',\n",
       " 'PHU': 'PORTHURON',\n",
       " 'RBT': 'ROBERTSLANDING',\n",
       " 'SAG': 'SAGINAW',\n",
       " 'SSM': 'SAULTSTE.MARIE',\n",
       " 'SCL': 'STCLAIR',\n",
       " 'YIP': 'WILLOWRUN-YPSILANTI',\n",
       " 'BAU': 'BAUDETTE',\n",
       " 'CAR': 'CARIBOUMUNICIPALAIRPORT',\n",
       " 'GTF': 'CollapsedintoINT',\n",
       " 'INL': 'CollapsedintoINT',\n",
       " 'CRA': 'CRANELAKE',\n",
       " 'MIC': 'CRYSTALMUNICIPALAIRPORT',\n",
       " 'DUL': 'DULUTH',\n",
       " 'ELY': 'ELY',\n",
       " 'GPM': 'GRANDPORTAGE',\n",
       " 'SVC': 'GRANTCOUNTY-SILVERCITY',\n",
       " 'INT': 'INTLFALLS',\n",
       " 'LAN': 'LANCASTER',\n",
       " 'MSP': 'MINN./STPAUL',\n",
       " 'LIN': 'NORTHERNSVCCENTER',\n",
       " 'NOY': 'NOYES',\n",
       " 'PIN': 'PINECREEK',\n",
       " '48Y': 'PINECREEKBORDERARPT',\n",
       " 'RAN': 'RAINER',\n",
       " 'RST': 'ROCHESTER',\n",
       " 'ROS': 'ROSEAU',\n",
       " 'SPM': 'STPAUL',\n",
       " 'WSB': 'WARROADINTL',\n",
       " 'WAR': 'WARROAD',\n",
       " 'KAN': 'KANSASCITY',\n",
       " 'SGF': 'SPRINGFIELD-BRANSON',\n",
       " 'STL': 'STLOUIS',\n",
       " 'WHI': 'WHITETAIL',\n",
       " 'WHM': 'WILDHORSE',\n",
       " 'GPT': 'BILOXIREGIONAL',\n",
       " 'GTR': 'GOLDENTRIANGLELOWNDESCNTY',\n",
       " 'GUL': 'GULFPORT',\n",
       " 'PAS': 'PASCAGOULA',\n",
       " 'JAN': 'THOMPSONFIELD-JACKSON',\n",
       " 'BIL': 'BILLINGS',\n",
       " 'BTM': 'BUTTE',\n",
       " 'CHF': 'CHIEFMT',\n",
       " 'CTB': 'CUTBANKMUNICIPAL',\n",
       " 'CUT': 'CUTBANK',\n",
       " 'DLB': 'DELBONITA',\n",
       " 'EUR': 'EUREKA',\n",
       " 'BZN': 'GALLATINFIELD-BOZEMAN',\n",
       " 'FCA': 'GLACIERNATIONALPARK',\n",
       " 'GGW': 'GLASGOW',\n",
       " 'GRE': 'GREATFALLS',\n",
       " 'HVR': 'HAVRE',\n",
       " 'HEL': 'HELENA',\n",
       " 'LWT': 'LEWISTON',\n",
       " 'MGM': 'MORGAN',\n",
       " 'OPH': 'OPHEIM',\n",
       " 'PIE': 'PIEGAN',\n",
       " 'RAY': 'RAYMOND',\n",
       " 'ROO': 'ROOSVILLE',\n",
       " 'SCO': 'SCOBEY',\n",
       " 'SWE': 'SWEETGTASS',\n",
       " 'TRL': 'TRIALCREEK',\n",
       " 'TUR': 'TURNER',\n",
       " 'WCM': 'WILLOWCREEK',\n",
       " 'CLT': 'CHARLOTTE',\n",
       " 'FAY': 'FAYETTEVILLE',\n",
       " 'MRH': 'MOREHEADCITY',\n",
       " 'FOP': 'MORRISFIELDSAAF',\n",
       " 'GSO': 'PIEDMONTTRIADINTLAIRPORT',\n",
       " 'RDU': 'RALEIGH/DURHAM',\n",
       " 'SSC': 'SHAWAFB-SUMTER',\n",
       " 'WIL': 'WILMINGTON',\n",
       " 'AMB': 'AMBROSE',\n",
       " 'ANT': 'ANTLER',\n",
       " 'CRY': 'CARBURY',\n",
       " 'DNS': 'DUNSEITH',\n",
       " 'FAR': 'FARGO',\n",
       " 'FRT': 'FORTUNA',\n",
       " 'GRF': 'GRANDFORKS',\n",
       " 'HNN': 'HANNAH',\n",
       " 'HNS': 'HANSBORO',\n",
       " 'MAI': 'MAIDA',\n",
       " 'MND': 'MINOT',\n",
       " 'NEC': 'NECHE',\n",
       " 'NOO': 'NOONAN',\n",
       " 'NRG': 'NORTHGATE',\n",
       " 'PEM': 'PEMBINA',\n",
       " 'SAR': 'SARLES',\n",
       " 'SHR': 'SHERWOOD',\n",
       " 'SJO': 'STJOHN',\n",
       " 'WAL': 'WALHALLA',\n",
       " 'WHO': 'WESTHOPE',\n",
       " 'WND': 'WILLISTON',\n",
       " 'OMA': 'OMAHA',\n",
       " 'LEB': 'LEBANON',\n",
       " 'MHT': 'MANCHESTER',\n",
       " 'PNH': 'PITTSBURG',\n",
       " 'PSM': 'PORTSMOUTH',\n",
       " 'BYO': 'BAYONNE',\n",
       " 'CNJ': 'CAMDEN',\n",
       " 'HOB': 'HOBOKEN',\n",
       " 'JER': 'JERSEYCITY',\n",
       " 'WRI': 'MCGUIREAFB-WRIGHTSOWN',\n",
       " 'MMU': 'MORRISTOWN',\n",
       " 'NEW': 'NEWARK',\n",
       " 'PER': 'PERTHAMBOY',\n",
       " 'ACY': 'POMONAFIELD-ATLANTICCITY',\n",
       " 'ALA': 'ALAMAGORDO',\n",
       " 'ABQ': 'ALBUQUERQUE',\n",
       " 'ANP': 'ANTELOPEWELLS',\n",
       " 'CRL': 'CARLSBAD',\n",
       " 'COL': 'COLUMBUS',\n",
       " 'CDD': 'CRANELAKE-ST.LOUISCNTY',\n",
       " 'DNM': 'DEMING',\n",
       " 'LAS': 'LASCRUCES',\n",
       " 'LOB': 'LORDSBURG',\n",
       " 'RUI': 'RUIDOSO',\n",
       " 'STR': 'SANTATERESA',\n",
       " 'RNO': 'CANNONINTL-RENO/TAHOE',\n",
       " 'FLX': 'FALLONMUNICIPALAIRPORT',\n",
       " 'LVG': 'LASVEGAS',\n",
       " 'REN': 'RENO',\n",
       " 'ALB': 'ALBANY',\n",
       " 'AXB': 'ALEXANDRIABAY',\n",
       " 'BUF': 'BUFFALO',\n",
       " 'CNH': 'CANNONCORNERS',\n",
       " 'CAP': 'CAPEVINCENT',\n",
       " 'CHM': 'CHAMPLAIN',\n",
       " 'CHT': 'CHATEAUGAY',\n",
       " 'CLA': 'CLAYTON',\n",
       " 'FTC': 'FORTCOVINGTON',\n",
       " 'LAG': 'LAGUARDIA',\n",
       " 'LEW': 'LEWISTON',\n",
       " 'MAS': 'MASSENA',\n",
       " 'MAG': 'MCGUIREAFB',\n",
       " 'MOO': 'MOORES',\n",
       " 'MRR': 'MORRISTOWN',\n",
       " 'NYC': 'NEWYORK',\n",
       " 'NIA': 'NIAGARAFALLS',\n",
       " 'OGD': 'OGDENSBURG',\n",
       " 'OSW': 'OSWEGO',\n",
       " 'ELM': 'REGIONALARPT-HORSEHEAD',\n",
       " 'ROC': 'ROCHESTER',\n",
       " 'ROU': 'ROUSESPOINT',\n",
       " 'SWF': 'STEWART-ORANGECNTY',\n",
       " 'SYR': 'SYRACUSE',\n",
       " 'THO': 'THOUSANDISLANDBRIDGE',\n",
       " 'TRO': 'TROUTRIVER',\n",
       " 'WAT': 'WATERTOWN',\n",
       " 'HPN': 'WESTCHESTER-WHITEPLAINS',\n",
       " 'WRB': 'WHIRLPOOLBRIDGE',\n",
       " 'YOU': 'YOUNGSTOWN',\n",
       " 'AKR': 'AKRON',\n",
       " 'ATB': 'ASHTABULA',\n",
       " 'CIN': 'CINCINNATI',\n",
       " 'CLE': 'CLEVELAND',\n",
       " 'CLM': 'COLUMBUS',\n",
       " 'LOR': 'LORAIN',\n",
       " 'MBO': 'MARBLEHEADS',\n",
       " 'SDY': 'SANDUSKY',\n",
       " 'TOL': 'TOLEDO',\n",
       " 'OKC': 'OKLAHOMACITY',\n",
       " 'TUL': 'TULSA',\n",
       " 'AST': 'ASTORIA',\n",
       " 'COO': 'COOSBAY',\n",
       " 'HIO': 'HILLSBORO',\n",
       " 'MED': 'MEDFORD',\n",
       " 'NPT': 'NEWPORT',\n",
       " 'POO': 'PORTLAND',\n",
       " 'PUT': 'PUT-IN-BAY',\n",
       " 'RDM': 'ROBERTSFIELDS-REDMOND',\n",
       " 'ERI': 'ERIE',\n",
       " 'MDT': 'HARRISBURG',\n",
       " 'HSB': 'HARRISONBURG',\n",
       " 'PHI': 'PHILADELPHIA',\n",
       " 'PIT': 'PITTSBURG',\n",
       " 'AGU': 'AGUADILLA',\n",
       " 'BQN': 'BORINQUEN-AGUADILLO',\n",
       " 'JCP': 'CULEBRA-BENJAMINRIVERA',\n",
       " 'ENS': 'ENSENADA',\n",
       " 'FAJ': 'FAJARDO',\n",
       " 'HUM': 'HUMACAO',\n",
       " 'JOB': 'JOBOS',\n",
       " 'MAY': 'MAYAGUEZ',\n",
       " 'PON': 'PONCE',\n",
       " 'PSE': 'PONCE-MERCEDITA',\n",
       " 'SAJ': 'SANJUAN',\n",
       " 'VQS': 'VIEQUES-ARPT',\n",
       " 'PRO': 'PROVIDENCE',\n",
       " 'PVD': 'THEODOREFRANCIS-WARWICK',\n",
       " 'CHL': 'CHARLESTON',\n",
       " 'GEO': 'GEORGETOWN',\n",
       " 'GSP': 'GREENVILLE',\n",
       " 'GRR': 'GREER',\n",
       " 'MYR': 'MYRTLEBEACH',\n",
       " 'SPF': 'BLACKHILLS',\n",
       " 'HON': 'HOWESREGIONALARPT-HURON',\n",
       " 'SAI': 'SAIPAN',\n",
       " 'TYS': 'MCGHEETYSON-ALCOA',\n",
       " 'MEM': 'MEMPHIS',\n",
       " 'NSV': 'NASHVILLE',\n",
       " 'TRI': 'TRICITYARPT',\n",
       " 'ADS': 'ADDISONAIRPORT-ADDISON',\n",
       " 'ADT': 'AMISTADDAM',\n",
       " 'ANZ': 'ANZALDUAS',\n",
       " 'AUS': 'AUSTIN',\n",
       " 'BEA': 'BEAUMONT',\n",
       " 'BBP': 'BIGBENDPARK',\n",
       " 'SCC': 'BPSPECCOORD.CTR',\n",
       " 'BTC': 'BPTACTICALUNIT',\n",
       " 'BOA': 'BRIDGEOFAMERICAS',\n",
       " 'BRO': 'BROWNSVILLE',\n",
       " 'CRP': 'CORPUSCHRISTI',\n",
       " 'DAL': 'DALLAS',\n",
       " 'DLR': 'DELRIO',\n",
       " 'DNA': 'DONNA',\n",
       " 'EGP': 'EAGLEPASS',\n",
       " 'ELP': 'ELPASO',\n",
       " 'FAB': 'FABENS',\n",
       " 'FAL': 'FALCONHEIGHTS',\n",
       " 'FTH': 'FORTHANCOCK',\n",
       " 'AFW': 'FORTWORTHALLIANCE',\n",
       " 'FPT': 'FREEPORT',\n",
       " 'GAL': 'GALVESTON',\n",
       " 'HLG': 'HARLINGEN',\n",
       " 'HID': 'HIDALGO',\n",
       " 'HOU': 'HOUSTON',\n",
       " 'SGR': 'HULLFIELD',\n",
       " 'LLB': 'JUAREZ-LINCOLNBRIDGE',\n",
       " 'LCB': 'LAREDOCOLUMBIABRIDGE',\n",
       " 'LRN': 'LAREDONORTH',\n",
       " 'LAR': 'LAREDO',\n",
       " 'LSE': 'LOSEBANOS',\n",
       " 'IND': 'LOSINDIOS',\n",
       " 'LOI': 'LOSINDIOS',\n",
       " 'MRS': 'MARFA',\n",
       " 'MCA': 'MCALLEN',\n",
       " 'MAF': 'ODESSAREGIONAL',\n",
       " 'PDN': 'PASODELNORTE',\n",
       " 'PBB': 'PEACEBRIDGE',\n",
       " 'PHR': 'PHARR',\n",
       " 'PAR': 'PORTARTHUR',\n",
       " 'ISB': 'PORTISABEL',\n",
       " 'POE': 'PORTOFELPASO',\n",
       " 'PRE': 'PRESIDIO',\n",
       " 'PGR': 'PROGRESO',\n",
       " 'RIO': 'RIOGRANDECITY',\n",
       " 'ROM': 'ROMA',\n",
       " 'SNA': 'SANANTONIO',\n",
       " 'SNN': 'SANDERSON',\n",
       " 'VIB': 'VETERANINTLBRIDGE',\n",
       " 'YSL': 'YSLETA',\n",
       " 'CHA': 'CHARLOTTEAMALIE',\n",
       " 'CHR': 'CHRISTIANSTED',\n",
       " 'CRU': 'CRUZBAY',\n",
       " 'FRK': 'FREDERIKSTED',\n",
       " 'STT': 'STTHOMAS',\n",
       " 'LGU': 'CACHEAIRPORT-LOGAN',\n",
       " 'SLC': 'SALTLAKECITY',\n",
       " 'CHO': 'ALBEMARLECHARLOTTESVILLE',\n",
       " 'DAA': 'DAVISONAAF-FAIRFAXCNTY',\n",
       " 'HOP': 'HOPEWELL',\n",
       " 'HEF': 'MANASSAS',\n",
       " 'NWN': 'NEWPORT',\n",
       " 'NOR': 'NORFOLK',\n",
       " 'RCM': 'RICHMOND',\n",
       " 'ABS': 'ALBURGSPRINGS',\n",
       " 'ABG': 'ALBURG',\n",
       " 'BEB': 'BEEBEPLAIN',\n",
       " 'BEE': 'BEECHERFALLS',\n",
       " 'BRG': 'BURLINGTON',\n",
       " 'CNA': 'CANAAN',\n",
       " 'DER': 'DERBYLINE',\n",
       " 'DLV': 'DERBYLINE',\n",
       " 'ERC': 'EASTRICHFORD',\n",
       " 'HIG': 'HIGHGATESPRINGS',\n",
       " 'MOR': 'MORSESLINE',\n",
       " 'NPV': 'NEWPORT',\n",
       " 'NRT': 'NORTHTROY',\n",
       " 'NRN': 'NORTON',\n",
       " 'PIV': 'PINNACLEROAD',\n",
       " 'RIF': 'RICHFORT',\n",
       " 'STA': 'STALBANS',\n",
       " 'SWB': 'SWANTON',\n",
       " 'WBE': 'WESTBERKSHIRE',\n",
       " 'ABE': 'ABERDEEN',\n",
       " 'ANA': 'ANACORTES',\n",
       " 'BEL': 'BELLINGHAM',\n",
       " 'BLI': 'BELLINGHAM',\n",
       " 'BLA': 'BLAINE',\n",
       " 'BWA': 'BOUNDARY',\n",
       " 'CUR': 'CURLEW',\n",
       " 'DVL': 'DANVILLE',\n",
       " 'EVE': 'EVERETT',\n",
       " 'FER': 'FERRY',\n",
       " 'FRI': 'FRIDAYHARBOR',\n",
       " 'FWA': 'FRONTIER',\n",
       " 'KLM': 'KALAMA',\n",
       " 'LAU': 'LAURIER',\n",
       " 'LON': 'LONGVIEW',\n",
       " 'MET': 'METALINEFALLS',\n",
       " 'MWH': 'MOSESLAKEGRANTCOUNTYARPT',\n",
       " 'NEA': 'NEAHBAY',\n",
       " 'NIG': 'NIGHTHAWK',\n",
       " 'OLY': 'OLYMPIA',\n",
       " 'ORO': 'OROVILLE',\n",
       " 'PWB': 'PASCO',\n",
       " 'PIR': 'POINTROBERTS',\n",
       " 'PNG': 'PORTANGELES',\n",
       " 'PTO': 'PORTTOWNSEND',\n",
       " 'SEA': 'SEATTLE',\n",
       " 'SPO': 'SPOKANE',\n",
       " 'SUM': 'SUMAS',\n",
       " 'TAC': 'TACOMA',\n",
       " 'PSC': 'TRI-CITIES-PASCO',\n",
       " 'VAN': 'VANCOUVER',\n",
       " 'AGM': 'ALGOMA',\n",
       " 'BAY': 'BAYFIELD',\n",
       " 'GRB': 'GREENBAY',\n",
       " 'MNW': 'MANITOWOC',\n",
       " 'MIL': 'MILWAUKEE',\n",
       " 'MSN': 'TRUAXFIELD-DANECOUNTY',\n",
       " 'CHS': 'CHARLESTON',\n",
       " 'CLK': 'CLARKSBURG',\n",
       " 'BLF': 'MERCERCOUNTY',\n",
       " 'CSP': 'CASPER',\n",
       " 'XXX': 'NOTREPORTED/UNKNOWN',\n",
       " '888': 'UNIDENTIFEDAIR/SEAPORT',\n",
       " 'UNK': 'UNKNOWNPOE',\n",
       " 'CLG': 'CALGARY',\n",
       " 'EDA': 'EDMONTON',\n",
       " 'YHC': 'HAKAIPASS',\n",
       " 'HAL': 'Halifax',\n",
       " 'MON': 'MONTREAL',\n",
       " 'OTT': 'OTTAWA',\n",
       " 'YXE': 'SASKATOON',\n",
       " 'TOR': 'TORONTO',\n",
       " 'VCV': 'VANCOUVER',\n",
       " 'VIC': 'VICTORIA',\n",
       " 'WIN': 'WINNIPEG',\n",
       " 'AMS': 'AMSTERDAM-SCHIPHOL',\n",
       " 'ARB': 'ARUBA',\n",
       " 'BAN': 'BANKOK',\n",
       " 'BEI': 'BEICA#ARPT',\n",
       " 'PEK': 'BEIJINGCAPITALINTL',\n",
       " 'BDA': 'KINDLEYFIELD',\n",
       " 'BOG': 'BOGOTA',\n",
       " 'EZE': 'BUENOSAIRES',\n",
       " 'CUN': 'CANCUN',\n",
       " 'CRQ': 'CARAVELAS',\n",
       " 'MVD': 'CARRASCO',\n",
       " 'DUB': 'DUBLIN',\n",
       " 'FOU': 'FOUGAMOU#ARPT',\n",
       " 'FBA': 'FREEPORT',\n",
       " 'MTY': 'GENM.ESCOBEDO',\n",
       " 'HMO': 'GENPESQUEIRAGARCIA',\n",
       " 'GCM': 'GRANDCAYMAN',\n",
       " 'GDL': 'GUADALAJARA',\n",
       " 'HAM': 'HAMILTON',\n",
       " 'ICN': 'INCHON',\n",
       " 'IWA': 'INVALID-IWAKUNI',\n",
       " 'CND': 'KOGALNICEANU',\n",
       " 'LAH': 'LABUHAARPT',\n",
       " 'DUR': 'LOUISBOTHA',\n",
       " 'MAL': 'MANGOLEARPT',\n",
       " 'MDE': 'MEDELLIN',\n",
       " 'MEX': 'JUAREZINTL',\n",
       " 'LHR': 'MIDDLESEX',\n",
       " 'NBO': 'NAIROBI',\n",
       " 'NAS': 'NASSAU',\n",
       " 'NCA': 'NORTHCAICOS',\n",
       " 'PTY': 'OMARTORRIJOS',\n",
       " 'SPV': 'PAPUA',\n",
       " 'UIO': 'QUITO(MARISCALSUCR)',\n",
       " 'RIT': 'ROME',\n",
       " 'SNO': 'SAKONNAKHON#ARPT',\n",
       " 'SLP': 'SANLUISPOTOSI#ARPT',\n",
       " 'SAN': 'SANSALVADOR',\n",
       " 'SRO': 'SANTANARAMOS#ARPT',\n",
       " 'GRU': 'GUARULHOSINTL',\n",
       " 'SHA': 'SHANNON',\n",
       " 'HIL': 'SHILLAVO',\n",
       " 'TOK': 'TOROKINA#ARPT',\n",
       " 'VER': 'VERACRUZ',\n",
       " 'LGW': 'WESTSUSSEX',\n",
       " 'ZZZ': 'MEXICOLand(BancodeMexico)',\n",
       " 'CHN': 'NoPORTCode(CHN)',\n",
       " 'CNC': 'CANNONCORNERS',\n",
       " 'MAA': 'AbuDhabi',\n",
       " 'AG0': 'MAGNOLIA',\n",
       " 'BHM': 'BARHARBOR',\n",
       " 'BHX': 'BIRMINGHAM',\n",
       " 'CAK': 'AKRON',\n",
       " 'FOK': 'SUFFOLKCOUNTY',\n",
       " 'LND': 'LANDER',\n",
       " 'MAR': 'MARFA',\n",
       " 'MLI': 'MOLINE',\n",
       " 'RIV': 'RIVERSIDE',\n",
       " 'RME': 'ROME',\n",
       " 'VNY': 'VANNUYS',\n",
       " 'YUM': 'YUMA',\n",
       " 'FRG': 'Collapsed(FOK)06/15',\n",
       " 'HRL': 'Collapsed(HLG)06/15',\n",
       " 'ISP': 'Collapsed(FOK)06/15',\n",
       " 'JSJ': 'Collapsed(SAJ)06/15',\n",
       " 'BUS': 'Collapsed(BUF)06/15',\n",
       " 'IAG': 'Collapsed(NIA)06/15',\n",
       " 'PHN': 'Collapsed(PHU)06/15',\n",
       " 'STN': 'Collapsed(STR)06/15',\n",
       " 'VMB': 'Collapsed(VNB)06/15',\n",
       " 'T01': 'Collapsed(SEA)06/15',\n",
       " 'PHF': 'NoPORTCode(PHF)',\n",
       " 'DRV': 'NoPORTCode(DRV)',\n",
       " 'FTB': 'NoPORTCode(FTB)',\n",
       " 'GAC': 'NoPORTCode(GAC)',\n",
       " 'GMT': 'NoPORTCode(GMT)',\n",
       " 'JFA': 'NoPORTCode(JFA)',\n",
       " 'JMZ': 'NoPORTCode(JMZ)',\n",
       " 'NC8': 'NoPORTCode(NC8)',\n",
       " 'NYL': 'NoPORTCode(NYL)',\n",
       " 'OAI': 'NoPORTCode(OAI)',\n",
       " 'PCW': 'NoPORTCode(PCW)',\n",
       " 'WA5': 'NoPORTCode(WAS)',\n",
       " 'WTR': 'NoPORTCode(WTR)',\n",
       " 'X96': 'NoPORTCode(X96)',\n",
       " 'XNA': 'NoPORTCode(XNA)',\n",
       " 'YGF': 'NoPORTCode(YGF)',\n",
       " '5T6': 'NoPORTCode(5T6)',\n",
       " '060': 'NoPORTCode(60)',\n",
       " 'SP0': 'NoPORTCode(SP0)',\n",
       " 'W55': 'NoPORTCode(W55)',\n",
       " 'X44': 'NoPORTCode(X44)',\n",
       " 'AUH': 'NoPORTCode(AUH)',\n",
       " 'RYY': 'NoPORTCode(RYY)',\n",
       " 'SUS': 'NoPORTCode(SUS)',\n",
       " '74S': 'NoPORTCode(74S)',\n",
       " 'ATW': 'NoPORTCode(ATW)',\n",
       " 'CPX': 'NoPORTCode(CPX)',\n",
       " 'MTH': 'NoPORTCode(MTH)',\n",
       " 'PFN': 'NoPORTCode(PFN)',\n",
       " 'SCH': 'NoPORTCode(SCH)',\n",
       " 'ASI': 'NoPORTCode(ASI)',\n",
       " 'BKF': 'NoPORTCode(BKF)',\n",
       " 'DAY': 'NoPORTCode(DAY)',\n",
       " 'Y62': 'NoPORTCode(Y62)',\n",
       " 'AG\\t': 'NoPORTCode(AG)',\n",
       " 'BCM': 'NoPORTCode(BCM)',\n",
       " 'DEC': 'NoPORTCode(DEC)',\n",
       " 'PLB': 'NoPORTCode(PLB)',\n",
       " 'CXO': 'NoPORTCode(CXO)',\n",
       " 'JBQ': 'NoPORTCode(JBQ)',\n",
       " 'JIG': 'NoPORTCode(JIG)',\n",
       " 'OGS': 'NoPORTCode(OGS)',\n",
       " 'TIW': 'NoPORTCode(TIW)',\n",
       " 'OTS': 'NoPORTCode(OTS)',\n",
       " 'AMT': 'NoPORTCode(AMT)',\n",
       " 'EGE': 'NoPORTCode(EGE)',\n",
       " 'GPI': 'NoPORTCode(GPI)',\n",
       " 'NGL': 'NoPORTCode(NGL)',\n",
       " 'OLM': 'NoPORTCode(OLM)',\n",
       " '.GA': 'NoPORTCode(.GA)',\n",
       " 'CLX': 'NoPORTCode(CLX)',\n",
       " 'CP': 'NoPORTCode(CP)',\n",
       " 'FSC': 'NoPORTCode(FSC)',\n",
       " 'NK': 'NoPORTCode(NK)',\n",
       " 'ADU': 'NoPORTCode(ADU)',\n",
       " 'AKT': 'NoPORTCode(AKT)',\n",
       " 'LIT': 'NoPORTCode(LIT)',\n",
       " 'A2A': 'NoPORTCode(A2A)',\n",
       " 'OSN': 'NoPORTCode(OSN)'}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "city_mappings = spark.read.option(\"header\",\"true\").option(\"delimiter\", \",\").format(\"csv\").csv(\"../data/city-mappings.csv\", inferSchema=True)\n",
    "city_mappings=city_mappings.cache()\n",
    "cm_df=city_mappings.toPandas()\n",
    "cm_df.head()\n",
    "\n",
    "cf_dt={}\n",
    "for value in cm_df.iterrows():\n",
    "    cf_dt[value[1][0]] = value[1][1]\n",
    "cf_dt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "57fa0dc8-ed56-4f0a-a194-5dce0e8d4f91",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len((cm_df[cm_df['C_Code'] == 'ALC']['City']).values)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d2ae66be-fa7e-4ec4-b670-125f0b7bb183",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Some null impute by median.\n",
    "def impute_temperatures(temp,state):\n",
    "    if temp:\n",
    "        return temp\n",
    "    return float(mt_df[mt_df['State'] == state]['median_temperatures'].values[0])\n",
    "\n",
    "def get_cities(c_code):\n",
    "    if len((cm_df[cm_df['C_Code'] == 'ALC']['City']).values) > 0:\n",
    "        return (cm_df[cm_df['C_Code'] == c_code]['City'].values[0]).capitalize()\n",
    "    return \"UNKNW\"\n",
    "\n",
    "def apply_impute_temp_func(mapping_broadcasted):\n",
    "    def f(temp,state):\n",
    "        if temp:\n",
    "            return temp\n",
    "        return mapping_broadcasted.value.get(state)\n",
    "    return F.udf(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9c31652c-ed2b-49eb-a575-285b0e5cd609",
   "metadata": {},
   "outputs": [],
   "source": [
    "#avg_temps = global_temp_us.groupBy(F.col(\"State\")).avg(\"AverageTemperature\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "bf4a6553-3ebd-4255-ad7a-7c99b38dcef9",
   "metadata": {},
   "outputs": [],
   "source": [
    "b_mf_dt = spark.sparkContext.broadcast(mf_dt)\n",
    "global_temp_us = global_temp_us.withColumn(\"AverageTemperature_1\", apply_impute_temp_func(b_mf_dt)(F.col('AverageTemperature'), F.col('State')))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "491f3308-bacf-47fb-a7ef-fb6c7e43f5fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(dt='1743-11-01', AverageTemperature=10.722000000000001, AverageTemperatureUncertainty=2.898, State='Alabama', Country='United States', AverageTemperature_1='10.722000000000001'),\n",
       " Row(dt='1743-12-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1744-01-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1744-02-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1744-03-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1744-04-01', AverageTemperature=19.075, AverageTemperatureUncertainty=2.902, State='Alabama', Country='United States', AverageTemperature_1='19.075'),\n",
       " Row(dt='1744-05-01', AverageTemperature=21.197, AverageTemperatureUncertainty=2.844, State='Alabama', Country='United States', AverageTemperature_1='21.197'),\n",
       " Row(dt='1744-06-01', AverageTemperature=25.29, AverageTemperatureUncertainty=2.879, State='Alabama', Country='United States', AverageTemperature_1='25.29'),\n",
       " Row(dt='1744-07-01', AverageTemperature=26.42, AverageTemperatureUncertainty=2.841, State='Alabama', Country='United States', AverageTemperature_1='26.42'),\n",
       " Row(dt='1744-08-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1744-09-01', AverageTemperature=21.735, AverageTemperatureUncertainty=2.866, State='Alabama', Country='United States', AverageTemperature_1='21.735'),\n",
       " Row(dt='1744-10-01', AverageTemperature=15.63, AverageTemperatureUncertainty=2.872, State='Alabama', Country='United States', AverageTemperature_1='15.63'),\n",
       " Row(dt='1744-11-01', AverageTemperature=11.198, AverageTemperatureUncertainty=2.806, State='Alabama', Country='United States', AverageTemperature_1='11.198'),\n",
       " Row(dt='1744-12-01', AverageTemperature=7.063, AverageTemperatureUncertainty=2.823, State='Alabama', Country='United States', AverageTemperature_1='7.063'),\n",
       " Row(dt='1745-01-01', AverageTemperature=6.931, AverageTemperatureUncertainty=2.838, State='Alabama', Country='United States', AverageTemperature_1='6.931'),\n",
       " Row(dt='1745-02-01', AverageTemperature=8.621, AverageTemperatureUncertainty=2.75, State='Alabama', Country='United States', AverageTemperature_1='8.621'),\n",
       " Row(dt='1745-03-01', AverageTemperature=11.232000000000001, AverageTemperatureUncertainty=2.757, State='Alabama', Country='United States', AverageTemperature_1='11.232000000000001'),\n",
       " Row(dt='1745-04-01', AverageTemperature=16.799, AverageTemperatureUncertainty=2.749, State='Alabama', Country='United States', AverageTemperature_1='16.799'),\n",
       " Row(dt='1745-05-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1745-06-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1745-07-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1745-08-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1745-09-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1745-10-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01'),\n",
       " Row(dt='1745-11-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1='17.01')]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "global_temp_us.head(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "2c017e53-357e-4398-b3c4-8b9db758c02f",
   "metadata": {},
   "outputs": [],
   "source": [
    "impute_temperatures = udf(impute_temperatures, DoubleType())\n",
    "global_temp_us = global_temp_us.withColumn(\"AverageTemperature_1\", impute_temperatures(global_temp_us.AverageTemperature, global_temp_us.State))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b352bbe-b0af-43d5-b3ba-90579c35420d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "0f5b4396-54bf-4504-abf5-46444c02a73f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(dt='1743-11-01', AverageTemperature=10.722000000000001, AverageTemperatureUncertainty=2.898, State='Alabama', Country='United States', AverageTemperature_1=10.722000000000001),\n",
       " Row(dt='1743-12-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1=17.01),\n",
       " Row(dt='1744-01-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1=17.01),\n",
       " Row(dt='1744-02-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1=17.01),\n",
       " Row(dt='1744-03-01', AverageTemperature=None, AverageTemperatureUncertainty=None, State='Alabama', Country='United States', AverageTemperature_1=17.01)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "global_temp_us.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "d61a7b4a-e50e-40f2-999f-6733b3da07c7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Row(City='Silver Spring', State='Maryland', Median Age=33.8, Male Population=40601, Female Population=41862, Total Population=82463, Number of Veterans=1562, Foreign-born=30908, Average Household Size=2.6, State Code='MD', Race='Hispanic or Latino', Count=25924)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_demog.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "f6ba5e7e-c2cb-4c3f-b8ee-85d8daf2d127",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop Nulls from State column\n",
    "global_temp_us=global_temp_us.filter(F.col('State').isNotNull())\n",
    "us_demog=us_demog.filter(F.col('State').isNotNull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "7c479fd5-e695-4fef-b913-721bdc0ce030",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract YYYY MM DD Columns Separately.\n",
    "global_temp_us = global_temp_us.withColumn(\"year\", F.year(\"dt\")) \\\n",
    "             .withColumn(\"month\", F.month(\"dt\")) \\\n",
    "             .withColumn(\"dayofmonth\", F.dayofmonth(\"dt\")) \\\n",
    "             .withColumn(\"weekofyear\", F.weekofyear(\"dt\")) \\\n",
    "             .withColumn(\"weekday\", F.dayofweek(\"dt\"))\n",
    "\n",
    "imm_data = imm_data.withColumn(\"year\", F.year(\"arrdate_1\")) \\\n",
    "             .withColumn(\"month\", F.month(\"arrdate_1\")) \\\n",
    "             .withColumn(\"dayofmonth\", F.dayofmonth(\"arrdate_1\")) \\\n",
    "             .withColumn(\"weekofyear\", F.weekofyear(\"arrdate_1\")) \\\n",
    "             .withColumn(\"weekday\", F.dayofweek(\"arrdate_1\"))\n",
    "get_city = udf(lambda x: get_cities(x))\n",
    "imm_data = imm_data.withColumn(\"adj_city\", get_city(imm_data.i94port))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "642bfde1-8e5d-4737-9eb4-176eaaaf736e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(cicid=5748517.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='CA', depdate=20582.0, i94bir=40.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1976.0, dtaddto='10292016', gender='F', insnum=None, airline='QF', admnum=94953870030.0, fltno='00011', visatype='B1', code='CA', state='CALIFORNIA', arrdate_1='2016-04-30', depdate_1='2016-05-08', days_of_stay=8, year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, adj_city='Losangeles'),\n",
       " Row(cicid=5748518.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='NV', depdate=20591.0, i94bir=32.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1984.0, dtaddto='10292016', gender='F', insnum=None, airline='VA', admnum=94955622830.0, fltno='00007', visatype='B1', code='NV', state='NEVADA', arrdate_1='2016-04-30', depdate_1='2016-05-17', days_of_stay=17, year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, adj_city='Losangeles'),\n",
       " Row(cicid=5748519.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='WA', depdate=20582.0, i94bir=29.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1987.0, dtaddto='10292016', gender='M', insnum=None, airline='DL', admnum=94956406530.0, fltno='00040', visatype='B1', code='WA', state='WASHINGTON', arrdate_1='2016-04-30', depdate_1='2016-05-08', days_of_stay=8, year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, adj_city='Losangeles'),\n",
       " Row(cicid=5748520.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='WA', depdate=20588.0, i94bir=29.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1987.0, dtaddto='10292016', gender='F', insnum=None, airline='DL', admnum=94956451430.0, fltno='00040', visatype='B1', code='WA', state='WASHINGTON', arrdate_1='2016-04-30', depdate_1='2016-05-14', days_of_stay=14, year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, adj_city='Losangeles'),\n",
       " Row(cicid=5748521.0, i94yr=2016.0, i94mon=4.0, i94cit=245.0, i94res=438.0, i94port='LOS', arrdate=20574.0, i94mode=1.0, i94addr='WA', depdate=20588.0, i94bir=28.0, i94visa=1.0, count=1.0, dtadfile='20160430', visapost='SYD', occup=None, entdepa='G', entdepd='O', entdepu=None, matflag='M', biryear=1988.0, dtaddto='10292016', gender='M', insnum=None, airline='DL', admnum=94956388130.0, fltno='00040', visatype='B1', code='WA', state='WASHINGTON', arrdate_1='2016-04-30', depdate_1='2016-05-14', days_of_stay=14, year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, adj_city='Losangeles')]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imm_data.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "4b2dfa35-56bc-4328-8971-159e6a7eb508",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create temp view for each of the staging data to create SQL Statements of them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "deb28721-15f7-4c3b-8cec-af656d0ab646",
   "metadata": {},
   "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",
      " |-- code: string (nullable = true)\n",
      " |-- state: string (nullable = true)\n",
      " |-- arrdate_1: string (nullable = true)\n",
      " |-- depdate_1: string (nullable = true)\n",
      " |-- days_of_stay: integer (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- month: integer (nullable = true)\n",
      " |-- dayofmonth: integer (nullable = true)\n",
      " |-- weekofyear: integer (nullable = true)\n",
      " |-- weekday: integer (nullable = true)\n",
      " |-- adj_city: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "imm_data.printSchema()\n",
    "imm_data.count()\n",
    "imm_data.createOrReplaceTempView(\"imm_data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2a53e0da-7cd9-4eaa-9ab3-63014cfed893",
   "metadata": {},
   "outputs": [],
   "source": [
    "#imm_data.filter((F.col('arrdate_1') > '2013-01-01') & (F.col('arrdate_1') < '2017-12-31')).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "f2fe590b-348b-425d-9053-1908d5c64077",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- dt: string (nullable = true)\n",
      " |-- AverageTemperature: double (nullable = true)\n",
      " |-- AverageTemperatureUncertainty: double (nullable = true)\n",
      " |-- State: string (nullable = true)\n",
      " |-- Country: string (nullable = true)\n",
      " |-- AverageTemperature_1: double (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- month: integer (nullable = true)\n",
      " |-- dayofmonth: integer (nullable = true)\n",
      " |-- weekofyear: integer (nullable = true)\n",
      " |-- weekday: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "global_temp_us.printSchema()\n",
    "global_temp_us.createOrReplaceTempView(\"global_temp_us\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "503867c9-26b3-4807-8958-8f21a5d1872e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- City: string (nullable = true)\n",
      " |-- State: string (nullable = true)\n",
      " |-- Median Age: double (nullable = true)\n",
      " |-- male_pop: integer (nullable = true)\n",
      " |-- female_pop: integer (nullable = true)\n",
      " |-- total_pop: integer (nullable = true)\n",
      " |-- Number of Veterans: integer (nullable = true)\n",
      " |-- Foreign-born: integer (nullable = true)\n",
      " |-- Average Household Size: double (nullable = true)\n",
      " |-- state_code: string (nullable = true)\n",
      " |-- Race: string (nullable = true)\n",
      " |-- Count: integer (nullable = true)\n",
      " |-- city_1: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "us_demog = us_demog.withColumnRenamed(\"Male Population\", \"male_pop\") \\\n",
    "                   .withColumnRenamed(\"Female Population\", \"female_pop\") \\\n",
    "                   .withColumnRenamed(\"Total Population\", \"total_pop\") \\\n",
    "                   .withColumnRenamed(\"State Code\", \"state_code\")\n",
    "capitalize_city = udf(lambda x: x.lower().replace(\" \", \"\").capitalize())\n",
    "us_demog = us_demog.withColumn(\"city_1\", capitalize_city(us_demog.City))\n",
    "us_demog.printSchema()\n",
    "us_demog.createOrReplaceTempView(\"us_demog\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "822a0bfa-5e55-4789-aa0f-c9e4dc16be87",
   "metadata": {},
   "outputs": [],
   "source": [
    "flight_details = spark.read.option(\"header\",\"true\").option(\"delimiter\", \",\").format(\"csv\").csv(\"../data/airport-codes_csv.csv\", inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "6d60623c-f33c-4f81-86c9-2b9ac7243f6c",
   "metadata": {},
   "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: integer (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"
     ]
    }
   ],
   "source": [
    "flight_details.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "9abde061-316b-491b-be1b-29c049c77f35",
   "metadata": {},
   "outputs": [],
   "source": [
    "state_code_udf = udf(lambda x: x.split(\"-\")[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "abbc277f-eafa-412d-8395-a588838b4efc",
   "metadata": {},
   "outputs": [],
   "source": [
    "flight_details = flight_details.withColumn(\"state_code\", state_code_udf(flight_details.iso_region))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "4f0f451d-7449-4e28-93cf-733da93304e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(ident='00A', type='heliport', name='Total Rf Heliport', elevation_ft=11, continent='NA', iso_country='US', iso_region='US-PA', municipality='Bensalem', gps_code='00A', iata_code=None, local_code='00A', coordinates='-74.93360137939453, 40.07080078125', state_code='PA'),\n",
       " Row(ident='00AA', type='small_airport', name='Aero B Ranch Airport', elevation_ft=3435, continent='NA', iso_country='US', iso_region='US-KS', municipality='Leoti', gps_code='00AA', iata_code=None, local_code='00AA', coordinates='-101.473911, 38.704022', state_code='KS')]"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flight_details.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "79c524ff-86c2-4e0a-9d4a-620db208a414",
   "metadata": {},
   "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: integer (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",
      " |-- state_code: string (nullable = true)\n",
      " |-- code: string (nullable = true)\n",
      " |-- state: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "flight_details.createOrReplaceTempView(\"flight_details\")\n",
    "joined_flight_details = spark.sql(\"\"\"\n",
    "SELECT * FROM flight_details as fd LEFT JOIN states_mapping sm ON fd.state_code = sm.code\n",
    "\"\"\")\n",
    "joined_flight_details.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "c1741a4e-1168-4a4b-a63f-c6e79229fea6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "55075"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "joined_flight_details.filter(F.col('name').isNull()).count()\n",
    "joined_flight_details.createOrReplaceTempView(\"flight_info\")\n",
    "joined_flight_details.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "a0e8a56d-9b7d-4a45-a9bb-0fd0b6a09943",
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_flight_stats = joined_flight_details.groupby(F.col(\"state\")).agg(F.count(\"state\").alias(\"count_of_flights\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7199ab0d-8aa4-4f51-8ed3-9d736912ec6e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(state='NEW JERSEY', count_of_flights=455),\n",
       " Row(state='PENNSYLVANIA', count_of_flights=1344),\n",
       " Row(state='ILLINOIS', count_of_flights=903),\n",
       " Row(state='MARYLAND', count_of_flights=257),\n",
       " Row(state='DIST. OF COLUMBIA', count_of_flights=21)]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_flight_stats.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "342d5d54-b505-46d9-97d8-a2a39a191944",
   "metadata": {},
   "outputs": [],
   "source": [
    "arrival_info = spark.sql(\n",
    "\"\"\"\n",
    "SELECT cicid, arrdate_1 as arrival_dt, depdate_1 as dep_dt, year, month, dayofmonth, weekofyear, weekday, days_of_stay, visatype, state, gender, biryear as birthyear, i94visa as visa_type\n",
    "FROM imm_data\n",
    "\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "9d03a357-8f5c-481d-a746-7144c5990830",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- cicid: double (nullable = true)\n",
      " |-- arrival_dt: string (nullable = true)\n",
      " |-- dep_dt: string (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- month: integer (nullable = true)\n",
      " |-- dayofmonth: integer (nullable = true)\n",
      " |-- weekofyear: integer (nullable = true)\n",
      " |-- weekday: integer (nullable = true)\n",
      " |-- days_of_stay: integer (nullable = true)\n",
      " |-- visatype: string (nullable = true)\n",
      " |-- state: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- birthyear: double (nullable = true)\n",
      " |-- visa_type: double (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "arrival_info.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "980cc822-61c2-4885-9678-c9672edca296",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(cicid=5748517.0, arrival_dt='2016-04-30', dep_dt='2016-05-08', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=8, visatype='B1', state='CALIFORNIA', gender='F', birthyear=1976.0, visa_type=1.0),\n",
       " Row(cicid=5748518.0, arrival_dt='2016-04-30', dep_dt='2016-05-17', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=17, visatype='B1', state='NEVADA', gender='F', birthyear=1984.0, visa_type=1.0),\n",
       " Row(cicid=5748519.0, arrival_dt='2016-04-30', dep_dt='2016-05-08', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=8, visatype='B1', state='WASHINGTON', gender='M', birthyear=1987.0, visa_type=1.0),\n",
       " Row(cicid=5748520.0, arrival_dt='2016-04-30', dep_dt='2016-05-14', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=14, visatype='B1', state='WASHINGTON', gender='F', birthyear=1987.0, visa_type=1.0),\n",
       " Row(cicid=5748521.0, arrival_dt='2016-04-30', dep_dt='2016-05-14', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=14, visatype='B1', state='WASHINGTON', gender='M', birthyear=1988.0, visa_type=1.0)]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arrival_info.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "62a5510c-c2ad-4ad3-beb9-0891170df86c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- airport_ident: string (nullable = true)\n",
      " |-- airport_name: string (nullable = true)\n",
      " |-- iata_code: string (nullable = true)\n",
      " |-- state: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(airport_ident='00A', airport_name='Total Rf Heliport', iata_code=None, state='PENNSYLVANIA'),\n",
       " Row(airport_ident='00AA', airport_name='Aero B Ranch Airport', iata_code=None, state='KANSAS'),\n",
       " Row(airport_ident='00AK', airport_name='Lowell Field', iata_code=None, state='ALASKA'),\n",
       " Row(airport_ident='00AL', airport_name='Epps Airpark', iata_code=None, state='ALABAMA'),\n",
       " Row(airport_ident='00AR', airport_name='Newport Hospital & Clinic Heliport', iata_code=None, state='ARKANSAS')]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flight_info = spark.sql(\"\"\"\n",
    "SELECT ident as airport_ident, name as airport_name, iata_code, state from  flight_info\n",
    "\"\"\")\n",
    "flight_info.printSchema()\n",
    "flight_info.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "051635f7-b695-4f4e-9b9d-3192c05b7eee",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- date: string (nullable = true)\n",
      " |-- year: integer (nullable = true)\n",
      " |-- month: integer (nullable = true)\n",
      " |-- dayofmonth: integer (nullable = true)\n",
      " |-- weekofyear: integer (nullable = true)\n",
      " |-- weekday: integer (nullable = true)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(date='2016-04-30', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7),\n",
       " Row(date='2016-04-30', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7),\n",
       " Row(date='2016-04-30', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7),\n",
       " Row(date='2016-04-30', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7),\n",
       " Row(date='2016-04-30', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7)]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Discard\n",
    "arrival_times = spark.sql(\"\"\"\n",
    "SELECT arrdate_1 as date, year, month, dayofmonth, weekofyear, weekday FROM imm_data\n",
    "\"\"\")\n",
    "arrival_times.printSchema()\n",
    "arrival_times.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "95d196bf-c053-49f4-92c7-e8c99f3ee5cf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- date: string (nullable = true)\n",
      " |-- average_temp: double (nullable = true)\n",
      " |-- state: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(date='1743-11-01', average_temp=10.722000000000001, state='Alabama'),\n",
       " Row(date='1743-12-01', average_temp=17.01, state='Alabama'),\n",
       " Row(date='1744-01-01', average_temp=17.01, state='Alabama'),\n",
       " Row(date='1744-02-01', average_temp=17.01, state='Alabama'),\n",
       " Row(date='1744-03-01', average_temp=17.01, state='Alabama')]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "global_temperatures = spark.sql(\n",
    "\"\"\"\n",
    "SELECT dt as date, AverageTemperature_1 as average_temp, state from global_temp_us\n",
    "\"\"\")\n",
    "global_temperatures.printSchema()\n",
    "global_temperatures.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "05eee43e-913f-40be-9295-6fe14c86067e",
   "metadata": {},
   "outputs": [],
   "source": [
    "demographics_us = spark.sql(\n",
    "\"\"\"\n",
    "SELECT row_number() OVER (PARTITION BY '' ORDER BY '') as demog_id, city_1 as city, State as state, male_pop, female_pop, total_pop, Race as race\n",
    "FROM us_demog\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "1591d13e-7ae3-47e4-b5f1-c9871bd1f1f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- demog_id: integer (nullable = false)\n",
      " |-- city: string (nullable = true)\n",
      " |-- state: string (nullable = true)\n",
      " |-- male_pop: integer (nullable = true)\n",
      " |-- female_pop: integer (nullable = true)\n",
      " |-- total_pop: integer (nullable = true)\n",
      " |-- race: string (nullable = true)\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(demog_id=1, city='Silverspring', state='Maryland', male_pop=40601, female_pop=41862, total_pop=82463, race='Hispanic or Latino'),\n",
       " Row(demog_id=2, city='Quincy', state='Massachusetts', male_pop=44129, female_pop=49500, total_pop=93629, race='White'),\n",
       " Row(demog_id=3, city='Hoover', state='Alabama', male_pop=38040, female_pop=46799, total_pop=84839, race='Asian'),\n",
       " Row(demog_id=4, city='Ranchocucamonga', state='California', male_pop=88127, female_pop=87105, total_pop=175232, race='Black or African-American'),\n",
       " Row(demog_id=5, city='Newark', state='New Jersey', male_pop=138040, female_pop=143873, total_pop=281913, race='White')]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "demographics_us.printSchema()\n",
    "demographics_us.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "1f1afc76-50ab-483e-8647-bb112d41e4d2",
   "metadata": {},
   "outputs": [],
   "source": [
    "city_list_demog = demographics_us.groupby(\"city\").count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "e8b549c0-7f93-4f92-9796-a3e4ecc9426b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------+------------------+----------------+------------------+\n",
      "|               state|total_male_pop|      avg_male_pop|total_female_pop|    avg_female_pop|\n",
      "+--------------------+--------------+------------------+----------------+------------------+\n",
      "|                Utah|       2586752|53890.666666666664|         2532925|52769.270833333336|\n",
      "|              Hawaii|        884035|          176807.0|          879795|          175959.0|\n",
      "|           Minnesota|       3478803| 64422.27777777778|         3565362| 66025.22222222222|\n",
      "|                Ohio|       5853254|119454.16326530612|         6243296|127414.20408163265|\n",
      "|            Arkansas|       1400724|  48300.8275862069|         1482165|51109.137931034486|\n",
      "|              Oregon|       3537215|         88430.375|         3645330|          91133.25|\n",
      "|               Texas|      34862194|127700.34432234432|        35691659|130738.67765567766|\n",
      "|        North Dakota|        476175|           47617.5|          471275|           47127.5|\n",
      "|        Pennsylvania|       5514704|167112.24242424243|         5988097|181457.48484848486|\n",
      "|         Connecticut|       2123435| 54447.05128205128|         2231661| 57222.07692307692|\n",
      "|            Nebraska|       1786665|          178666.5|         1819500|          181950.0|\n",
      "|              Nevada|       5593225|124293.88888888889|         5610495|124677.66666666667|\n",
      "|         Puerto Rico|        930052| 71542.46153846153|         1100795| 84676.53846153847|\n",
      "|          Washington|       6228025| 73270.88235294117|         6272510| 73794.23529411765|\n",
      "|            Illinois|      10943864|120262.24175824175|        11570526|127148.63736263737|\n",
      "|            Oklahoma|       3572865|          119095.5|         3672110|122403.66666666667|\n",
      "|District of Columbia|       1598525|          319705.0|         1762615|          352523.0|\n",
      "|            Delaware|        163400|           32680.0|          196385|           39277.0|\n",
      "|              Alaska|        764725|          152945.0|          728750|          145750.0|\n",
      "|          New Mexico|       2045050|          102252.5|         2150160|          107508.0|\n",
      "+--------------------+--------------+------------------+----------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "49"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stats_us_demog = demographics_us.groupBy(F.col(\"state\")).agg(F.sum(\"male_pop\").alias(\"total_male_pop\"), F.avg(\"male_pop\").alias(\"avg_male_pop\"),F.sum(\"female_pop\").alias(\"total_female_pop\"), F.avg(\"female_pop\").alias(\"avg_female_pop\") )\n",
    "stats_us_demog.createOrReplaceTempView(\"stats_us_demog\")\n",
    "stats_us_demog.show()\n",
    "stats_us_demog.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65320e9c-a9d9-4a4e-b3eb-d9c289337181",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "101eb2b2-0526-4f53-bce8-a3957365b591",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(cicid=5748517.0, arrival_dt='2016-04-30', dep_dt='2016-05-08', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=8, visatype='B1', state='CALIFORNIA', state_code='CA', city='Losangeles', gender='F', birthyear=1976.0, visa_category=1.0, state_1='California'),\n",
       " Row(cicid=5748518.0, arrival_dt='2016-04-30', dep_dt='2016-05-17', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=17, visatype='B1', state='NEVADA', state_code='NV', city='Losangeles', gender='F', birthyear=1984.0, visa_category=1.0, state_1='Nevada'),\n",
       " Row(cicid=5748519.0, arrival_dt='2016-04-30', dep_dt='2016-05-08', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=8, visatype='B1', state='WASHINGTON', state_code='WA', city='Losangeles', gender='M', birthyear=1987.0, visa_category=1.0, state_1='Washington'),\n",
       " Row(cicid=5748520.0, arrival_dt='2016-04-30', dep_dt='2016-05-14', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=14, visatype='B1', state='WASHINGTON', state_code='WA', city='Losangeles', gender='F', birthyear=1987.0, visa_category=1.0, state_1='Washington'),\n",
       " Row(cicid=5748521.0, arrival_dt='2016-04-30', dep_dt='2016-05-14', year=2016, month=4, dayofmonth=30, weekofyear=17, weekday=7, days_of_stay=14, visatype='B1', state='WASHINGTON', state_code='WA', city='Losangeles', gender='M', birthyear=1988.0, visa_category=1.0, state_1='Washington')]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the arrival information from immigration data.\n",
    "arrival_info = spark.sql(\"\"\"\n",
    "    SELECT cicid, arrdate_1 as arrival_dt, depdate_1 as dep_dt, year, month, dayofmonth, weekofyear, weekday, days_of_stay, visatype, state, i94addr as state_code, adj_city as city, gender, biryear as birthyear,\n",
    "    i94visa as visa_category\n",
    "    FROM imm_data\n",
    "    \"\"\"\n",
    ")\n",
    "capitalize_state = udf(lambda x: x.lower().capitalize() if x else None)\n",
    "arrival_info = arrival_info.withColumn(\"state_1\", capitalize_state(arrival_info.state))\n",
    "arrival_info.createOrReplaceTempView(\"arrival_info\")\n",
    "arrival_info.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "9458c7b2-8191-4bcf-84a5-45d8fad05fab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(arrival_id=5748517.0, state='CALIFORNIA', visatype='B1', visa_category=1.0, state_1='California'),\n",
       " Row(arrival_id=5748518.0, state='NEVADA', visatype='B1', visa_category=1.0, state_1='Nevada'),\n",
       " Row(arrival_id=5748519.0, state='WASHINGTON', visatype='B1', visa_category=1.0, state_1='Washington'),\n",
       " Row(arrival_id=5748520.0, state='WASHINGTON', visatype='B1', visa_category=1.0, state_1='Washington'),\n",
       " Row(arrival_id=5748521.0, state='WASHINGTON', visatype='B1', visa_category=1.0, state_1='Washington')]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the arrival information from immigration data.\n",
    "visa_info = spark.sql(\"\"\"\n",
    "    SELECT cicid as arrival_id, state,visatype,i94visa as visa_category\n",
    "    FROM imm_data\n",
    "    \"\"\"\n",
    ")\n",
    "capitalize_state = udf(lambda x: x.lower().capitalize() if x else None)\n",
    "visa_info = visa_info.withColumn(\"state_1\", capitalize_state(visa_info.state))\n",
    "visa_info.createOrReplaceTempView(\"visa_info\")\n",
    "visa_info.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "024d8816-dddd-484a-a52a-4c8ffce8c281",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(airport_ident='00A', airport_name='Total Rf Heliport', iata_code=None, state='Pennsylvania', state_code='PA'),\n",
       " Row(airport_ident='00AA', airport_name='Aero B Ranch Airport', iata_code=None, state='Kansas', state_code='KS'),\n",
       " Row(airport_ident='00AK', airport_name='Lowell Field', iata_code=None, state='Alaska', state_code='AK'),\n",
       " Row(airport_ident='00AL', airport_name='Epps Airpark', iata_code=None, state='Alabama', state_code='AL'),\n",
       " Row(airport_ident='00AR', airport_name='Newport Hospital & Clinic Heliport', iata_code=None, state='Arkansas', state_code='AR')]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get the airport information (This is an independent table can is having no relationship to the fact and dimensions)\n",
    "airport_info = spark.sql(\"\"\"\n",
    "SELECT ident as airport_ident, name as airport_name, iata_code, state, state_code from  flight_info\n",
    "\"\"\")\n",
    "airport_info = airport_info.withColumn(\"state\", capitalize_state(airport_info.state))\n",
    "airport_info.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "bc763f7e-7fad-49ad-938d-cf23f9bc20bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+------------+-----+\n",
      "|date|average_temp|state|\n",
      "+----+------------+-----+\n",
      "+----+------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Global temperatures by date and state in USA. Unfortunately the dataset spans only until 2013, but immigration data is of 2016. Therefore, a simulation of dataset is created to extend the global temperatures.\n",
    "\n",
    "global_temperatures = spark.sql(\"\"\"\n",
    "SELECT dt as date, AverageTemperature_1 as average_temp, state from global_temp_us\n",
    "\"\"\")\n",
    "global_temperatures.filter(F.col(\"date\") > \"2016-01-01\").show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "512ba163-33bb-4913-8c9f-782398da0659",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy\n",
    "from datetime import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "median_state_mapping=mt_df.to_numpy()\n",
    "start_date = \"2014-01-01\"\n",
    "end_date = \"2016-12-31\"\n",
    "days = int((datetime.strptime(end_date, '%Y-%m-%d') - datetime.strptime(start_date, '%Y-%m-%d')).days)\n",
    "simulate_mappings = {\"date\":[],\"average_temp\": [], \"state\":[]}\n",
    "for state_mapping in median_state_mapping:\n",
    "    for _ in range(days):\n",
    "        if state_mapping[1] < 0:\n",
    "            random_temp = numpy.random.uniform(low=state_mapping[1],high=10)\n",
    "        else:\n",
    "            random_temp = numpy.random.uniform(low=0,high=state_mapping[1])\n",
    "        simulate_mappings[\"date\"].append(start_date)\n",
    "        simulate_mappings[\"average_temp\"].append(random_temp)\n",
    "        simulate_mappings[\"state\"].append(state_mapping[0])\n",
    "        temp_date = str(datetime.strptime(start_date, '%Y-%m-%d') + relativedelta(days=1)).split(\" \")[0]\n",
    "        start_date = temp_date\n",
    "        #print(start_date)\n",
    "simulate_df=pd.DataFrame.from_dict(simulate_mappings)\n",
    "simulate_df.to_csv(\"../data/simulate_temp.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "389c4091-5e4f-4a39-8f56-87d90ab66474",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(date='1743-11-01', average_temp=10.722000000000001, state='Alabama', state_1='Alabama'),\n",
       " Row(date='1743-12-01', average_temp=17.01, state='Alabama', state_1='Alabama'),\n",
       " Row(date='1744-01-01', average_temp=17.01, state='Alabama', state_1='Alabama'),\n",
       " Row(date='1744-02-01', average_temp=17.01, state='Alabama', state_1='Alabama'),\n",
       " Row(date='1744-03-01', average_temp=17.01, state='Alabama', state_1='Alabama')]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "simulate_data = spark.read.option(\"header\",\"true\").option(\"delimiter\", \",\").format(\"csv\").csv(\"../data/simulate_temp.csv\", inferSchema=True)\n",
    "simulate_data = simulate_data.select([\"date\", \"average_temp\", \"state\"])\n",
    "global_temperatures = global_temperatures.union(simulate_data)\n",
    "global_temperatures = global_temperatures.withColumn(\"state_1\", capitalize_state(global_temperatures.state))\n",
    "global_temperatures.createOrReplaceTempView(\"global_temperatures\")\n",
    "global_temperatures.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "61c44610-1612-40f3-b5c5-da6fd952c21a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#arrival_info.withColumnRenamed(\"arrival_dt\", \"date\").join(global_temperatures, on=[\"date\",\"state_1\"]).select([\"cicid\", \"date\", \"average_temp\"]).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b98e9a7-80b9-4558-bd6f-66ed359d8a71",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "8f5a2d69-d0dc-4607-8700-539e5103cbe9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(demog_id=1, city='Silverspring', state='Maryland', male_pop=40601, female_pop=41862, total_pop=82463, race='Hispanic or Latino'),\n",
       " Row(demog_id=2, city='Quincy', state='Massachusetts', male_pop=44129, female_pop=49500, total_pop=93629, race='White'),\n",
       " Row(demog_id=3, city='Hoover', state='Alabama', male_pop=38040, female_pop=46799, total_pop=84839, race='Asian'),\n",
       " Row(demog_id=4, city='Ranchocucamonga', state='California', male_pop=88127, female_pop=87105, total_pop=175232, race='Black or African-American'),\n",
       " Row(demog_id=5, city='Newark', state='Newjersey', male_pop=138040, female_pop=143873, total_pop=281913, race='White')]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Demographics Data information by State and City in USA.\n",
    "demographics_us = spark.sql(\"\"\"\n",
    "SELECT row_number() OVER (PARTITION BY '' ORDER BY '') as demog_id, city_1 as city, State as state, male_pop, female_pop, total_pop, Race as race\n",
    "FROM us_demog\n",
    "\"\"\")\n",
    "demographics_us = demographics_us.withColumn(\"state\", capitalize_city(demographics_us.state))\n",
    "demographics_us.createOrReplaceTempView(\"demographics_us\")\n",
    "demographics_us.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71be4d29-5ed0-4618-99d2-a120426ed3f8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "37dc8fd8-b649-44e8-872e-20efd5c5606d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fact Table combining information from dimensions: arrival, global_temperature, demographics_us\n",
    "imm_dynamics_us = spark.sql(\"\"\"\n",
    "SELECT row_number() OVER (PARTITION BY '' ORDER BY '') as imm_dyn_id, ai.cicid, ai.arrival_dt, ai.state_1 as state, vi.visa_category, ai.arrival_dt, gt.average_temp, sm.code\n",
    "FROM arrival_info ai Left JOIN global_temperatures gt ON ai.arrival_dt = gt.date AND ai.state_1 = gt.state JOIN visa_info vi ON vi.arrival_id = ai.cicid LEFT JOIN states_mapping sm ON sm.code = ai.state_code\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "3f6821e1-df94-410e-981f-7084bf9d10fd",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[Row(imm_dyn_id=1, cicid=7.0, arrival_dt='2016-04-07', state='Alabama', visa_category=3.0, arrival_dt='2016-04-07', average_temp=None, code='AL'),\n",
       " Row(imm_dyn_id=2, cicid=67.0, arrival_dt='2016-04-01', state='Alabama', visa_category=2.0, arrival_dt='2016-04-01', average_temp=None, code='AL')]"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imm_dynamics_us.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "39a3236d-ecb0-4f69-a33c-2c460a3af4d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "2215673"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imm_dynamics_us.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8f4d248-757a-482e-be68-d4c6f4640702",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "a4d19a9a54512cde574559537a658c7dd6ca6c34aafcda1db7f10877beaf0f28"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}