Airport_Analysis_PySpark.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Airport_Analysis_PySpark.ipynb",
      "provenance": [],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BVqTTiSE-jNi"
      },
      "source": [
        "# **Installing and Initializing Spark on Google Colab**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0BIRBEhu-0Eu"
      },
      "source": [
        "!apt-get install openjdk-8-jdk-headless -qq > /dev/null\n",
        "\n",
        "!wget -q https://downloads.apache.org/spark/spark-3.0.2/spark-3.0.2-bin-hadoop2.7.tgz\n",
        "\n",
        "!tar xf spark-3.0.2-bin-hadoop2.7.tgz\n",
        "\n",
        "!pip install -q findspark"
      ],
      "execution_count": 2,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x6us2lUp_Txh"
      },
      "source": [
        "import os\n",
        "os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"\n",
        "os.environ[\"SPARK_HOME\"] = \"/content/spark-3.0.2-bin-hadoop2.7\""
      ],
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 35
        },
        "id": "2qL5BQ_c_W0W",
        "outputId": "d1543145-992c-471a-997a-d1d712045052"
      },
      "source": [
        "import findspark\n",
        "findspark.find()"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            },
            "text/plain": [
              "'/content/spark-3.0.2-bin-hadoop2.7'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_wcoEUk5_cfH"
      },
      "source": [
        "findspark.init()"
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "erwZOUo8_oyv"
      },
      "source": [
        "from pyspark.sql import SparkSession\n",
        "\n",
        "spark = SparkSession.builder\\\n",
        "        .master(\"local\")\\\n",
        "        .appName(\"Colab\")\\\n",
        "        .config('spark.ui.port', '4050')\\\n",
        "        .getOrCreate()\n",
        "\n",
        "from pyspark.sql import SQLContext\n",
        "sqlContext = SQLContext(spark)"
      ],
      "execution_count": 6,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 214
        },
        "id": "_zN-fJSA_twW",
        "outputId": "1773b3f9-c270-4051-b1c3-18f6f793ff81"
      },
      "source": [
        "spark"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "            <div>\n",
              "                <p><b>SparkSession - in-memory</b></p>\n",
              "                \n",
              "        <div>\n",
              "            <p><b>SparkContext</b></p>\n",
              "\n",
              "            <p><a href=\"http://bee0e9ea0b55:4050\">Spark UI</a></p>\n",
              "\n",
              "            <dl>\n",
              "              <dt>Version</dt>\n",
              "                <dd><code>v3.0.2</code></dd>\n",
              "              <dt>Master</dt>\n",
              "                <dd><code>local</code></dd>\n",
              "              <dt>AppName</dt>\n",
              "                <dd><code>Colab</code></dd>\n",
              "            </dl>\n",
              "        </div>\n",
              "        \n",
              "            </div>\n",
              "        "
            ],
            "text/plain": [
              "<pyspark.sql.session.SparkSession at 0x7ff9bb0a7210>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 7
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_TDT7b2x99xW"
      },
      "source": [
        "# **Reading CSV File using Spark**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0VeeTW-eAKFv"
      },
      "source": [
        "df = spark.read.csv(\"/content/drive/MyDrive/spark_file/Airports2.csv\", header=True, inferSchema=True)\n",
        "df.registerTempTable('df')"
      ],
      "execution_count": 8,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "YzXNqV9V-HE0"
      },
      "source": [
        "# **Basic Insights into Data**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "gRstmRZmAWiz",
        "outputId": "1ccb8bdb-db74-4f7e-ce72-81495064d282"
      },
      "source": [
        "df.printSchema()"
      ],
      "execution_count": 9,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "root\n",
            " |-- Origin_airport: string (nullable = true)\n",
            " |-- Destination_airport: string (nullable = true)\n",
            " |-- Origin_city: string (nullable = true)\n",
            " |-- Destination_city: string (nullable = true)\n",
            " |-- Passengers: integer (nullable = true)\n",
            " |-- Seats: integer (nullable = true)\n",
            " |-- Flights: integer (nullable = true)\n",
            " |-- Distance: integer (nullable = true)\n",
            " |-- Fly_date: string (nullable = true)\n",
            " |-- Origin_population: integer (nullable = true)\n",
            " |-- Destination_population: integer (nullable = true)\n",
            " |-- Org_airport_lat: string (nullable = true)\n",
            " |-- Org_airport_long: string (nullable = true)\n",
            " |-- Dest_airport_lat: string (nullable = true)\n",
            " |-- Dest_airport_long: string (nullable = true)\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "aR1atRnSAjpn",
        "outputId": "aa2a6faf-1aef-485d-ac88-9dece26348d3"
      },
      "source": [
        "df.count()"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "3606803"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "K0lw2qIQAuLX",
        "outputId": "b5505d38-7833-4852-ad21-e3a9bfaf6a2c"
      },
      "source": [
        "df.describe().show()"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------+--------------+-------------------+--------------+----------------+------------------+-----------------+------------------+-----------------+----------+-----------------+----------------------+------------------+------------------+-----------------+------------------+\n",
            "|summary|Origin_airport|Destination_airport|   Origin_city|Destination_city|        Passengers|            Seats|           Flights|         Distance|  Fly_date|Origin_population|Destination_population|   Org_airport_lat|  Org_airport_long| Dest_airport_lat| Dest_airport_long|\n",
            "+-------+--------------+-------------------+--------------+----------------+------------------+-----------------+------------------+-----------------+----------+-----------------+----------------------+------------------+------------------+-----------------+------------------+\n",
            "|  count|       3606803|            3606803|       3606803|         3606803|           3606803|          3606803|           3606803|          3606803|   3606803|          3606803|               3606803|           3606803|           3606803|          3606803|           3606803|\n",
            "|   mean|          null|               null|          null|            null|2688.9104331453646|4048.297368888736| 37.22889855642241|697.3190326724249|      null| 5871502.49894491|      5897982.44118434|37.750289955901664|-91.86177935715813|37.74090832973531|-91.83432738662697|\n",
            "| stddev|          null|               null|          null|            null|  4347.61704769634|6200.871210153885|49.619697799496414|604.4165108483492|      null|7858061.601821028|     7906127.406405261| 5.765453283976701|16.537733702683525|5.736555536048647|16.472284529884853|\n",
            "|    min|           1B1|                1B1|  Aberdeen, SD|    Aberdeen, SD|                 0|                0|                 0|                0|1990-01-01|            13005|                 12887|   19.721399307251|      -100.2860031|  19.721399307251|      -100.2860031|\n",
            "|    max|           ZZV|                ZZV|Zanesville, OH|  Zanesville, OH|             89597|           147062|              1128|             5095|2009-12-01|         38139592|              38139592|                NA|                NA|               NA|                NA|\n",
            "+-------+--------------+-------------------+--------------+----------------+------------------+-----------------+------------------+-----------------+----------+-----------------+----------------------+------------------+------------------+-----------------+------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NwEHr9pv-MB5"
      },
      "source": [
        "# **Spark Transformation and Action Operations**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0BXUCJS1AdvE",
        "outputId": "7e4f20dd-748b-408c-af26-427d1b527b87"
      },
      "source": [
        "df.show(5)"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-------------------+-------------+----------------+----------+-----+-------+--------+----------+-----------------+----------------------+----------------+-----------------+----------------+-----------------+\n",
            "|Origin_airport|Destination_airport|  Origin_city|Destination_city|Passengers|Seats|Flights|Distance|  Fly_date|Origin_population|Destination_population| Org_airport_lat| Org_airport_long|Dest_airport_lat|Dest_airport_long|\n",
            "+--------------+-------------------+-------------+----------------+----------+-----+-------+--------+----------+-----------------+----------------------+----------------+-----------------+----------------+-----------------+\n",
            "|           MHK|                AMW|Manhattan, KS|        Ames, IA|        21|   30|      1|     254|2008-10-01|           122049|                 86219| 39.140998840332|-96.6707992553711|              NA|               NA|\n",
            "|           EUG|                RDM|   Eugene, OR|        Bend, OR|        41|  396|     22|     103|1990-11-01|           284093|                 76034|44.1245994567871| -123.21199798584|      44.2541008|     -121.1500015|\n",
            "|           EUG|                RDM|   Eugene, OR|        Bend, OR|        88|  342|     19|     103|1990-12-01|           284093|                 76034|44.1245994567871| -123.21199798584|      44.2541008|     -121.1500015|\n",
            "|           EUG|                RDM|   Eugene, OR|        Bend, OR|        11|   72|      4|     103|1990-10-01|           284093|                 76034|44.1245994567871| -123.21199798584|      44.2541008|     -121.1500015|\n",
            "|           MFR|                RDM|  Medford, OR|        Bend, OR|         0|   18|      1|     156|1990-02-01|           147300|                 76034|42.3741989135742|-122.873001098633|      44.2541008|     -121.1500015|\n",
            "+--------------+-------------------+-------------+----------------+----------+-----+-------+--------+----------+-----------------+----------------------+----------------+-----------------+----------------+-----------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IvAsTyn3AnK_",
        "outputId": "42852508-ef31-4b30-ce3a-bcebe90c41f7"
      },
      "source": [
        "df.select(\"Origin_airport\",\"Destination_airport\",\"Passengers\",\"Seats\").show(15)"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-------------------+----------+-----+\n",
            "|Origin_airport|Destination_airport|Passengers|Seats|\n",
            "+--------------+-------------------+----------+-----+\n",
            "|           MHK|                AMW|        21|   30|\n",
            "|           EUG|                RDM|        41|  396|\n",
            "|           EUG|                RDM|        88|  342|\n",
            "|           EUG|                RDM|        11|   72|\n",
            "|           MFR|                RDM|         0|   18|\n",
            "|           MFR|                RDM|        11|   18|\n",
            "|           MFR|                RDM|         2|   72|\n",
            "|           MFR|                RDM|         7|   18|\n",
            "|           MFR|                RDM|         7|   36|\n",
            "|           SEA|                RDM|         8|   18|\n",
            "|           SEA|                RDM|       453| 3128|\n",
            "|           SEA|                RDM|       784| 2720|\n",
            "|           SEA|                RDM|       749| 2992|\n",
            "|           SEA|                RDM|        11|   18|\n",
            "|           PDX|                RDM|       349|  851|\n",
            "+--------------+-------------------+----------+-----+\n",
            "only showing top 15 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "YKz7gfpaAzrm",
        "outputId": "45a163a7-d492-452b-f990-c087e47a2e9d"
      },
      "source": [
        "from pyspark.sql import functions as F\n",
        "from pyspark.sql.functions import col\n",
        "from pyspark.sql.functions import desc\n",
        "\n",
        "airportAgg_DF = df.groupBy(\"Origin_airport\").agg(F.sum(\"Passengers\"))\n",
        "airportAgg_DF.show(10)"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+---------------+\n",
            "|Origin_airport|sum(Passengers)|\n",
            "+--------------+---------------+\n",
            "|           BGM|        1876537|\n",
            "|           CRS|             29|\n",
            "|           VWD|              0|\n",
            "|           MOR|              0|\n",
            "|           MSY|       83279662|\n",
            "|           RDG|          87401|\n",
            "|           GEG|       23872254|\n",
            "|           DRT|          75152|\n",
            "|           HVR|           1193|\n",
            "|           MML|              0|\n",
            "+--------------+---------------+\n",
            "only showing top 10 rows\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ul8pv3nR-Ygi"
      },
      "source": [
        "# **Spark SQL**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "HKCP7j9gcDnN"
      },
      "source": [
        "## **Highest Flight Departures Airport**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "h93KQAGABQ23",
        "outputId": "da0a01a1-a18a-4256-c38c-5dcdafc74fcc"
      },
      "source": [
        "originAirports = sqlContext.sql(\"\"\"select Origin_Airport, sum(Flights) as Flights \n",
        "                                    from df group by Origin_Airport order by sum(Flights) DESC limit 10\"\"\")\n",
        "originAirports.show()"
      ],
      "execution_count": 14,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------------+-------+\n",
            "|Origin_Airport|Flights|\n",
            "+--------------+-------+\n",
            "|           ORD|6908482|\n",
            "|           ATL|6558015|\n",
            "|           DFW|5994638|\n",
            "|           LAX|4099901|\n",
            "|           DTW|3452613|\n",
            "|           PHX|3213108|\n",
            "|           MSP|3204923|\n",
            "|           IAH|3195062|\n",
            "|           STL|3181102|\n",
            "|           CLT|2840773|\n",
            "+--------------+-------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "uJdfcSQOcZgo"
      },
      "source": [
        "## **Highest Passenger Arrival Airport**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "03ITa_gmGCxA",
        "outputId": "e89ddc7e-5d98-4a45-a1f4-6db06d938c98"
      },
      "source": [
        "destinationAirports = sqlContext.sql(\"\"\"select Destination_airport, sum(Passengers) as Passengers \n",
        "                                    from df group by Destination_airport order by sum(Passengers) DESC limit 10\"\"\")\n",
        "destinationAirports.show()"
      ],
      "execution_count": 24,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------------------+----------+\n",
            "|Destination_airport|Passengers|\n",
            "+-------------------+----------+\n",
            "|                ATL| 577954147|\n",
            "|                ORD| 528648148|\n",
            "|                DFW| 458322527|\n",
            "|                LAX| 389476602|\n",
            "|                PHX| 295580444|\n",
            "|                LAS| 269145891|\n",
            "|                DTW| 251467874|\n",
            "|                MSP| 245774036|\n",
            "|                SFO| 242283245|\n",
            "|                IAH| 229105403|\n",
            "+-------------------+----------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "biRXKITtcdoJ"
      },
      "source": [
        "## **Airports with Most Flights**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "u_1r47arHomo",
        "outputId": "4dbb33b0-74ce-453b-858b-08afd6d70566"
      },
      "source": [
        "MostFlightsByAirports = sqlContext.sql(\"\"\"with destination as (select Destination_airport as Airport, sum(Flights) as Out_Flights \n",
        "                                    from df group by Destination_airport),\n",
        "                                    origin as (select Origin_airport as Airport, sum(Flights) as In_Flights \n",
        "                                    from df group by Origin_airport)\n",
        "                                    select origin.Airport, (destination.Out_Flights+origin.In_Flights) as Total_Flights\n",
        "                                    from origin, destination \n",
        "                                    where origin.Airport = destination.Airport\n",
        "                                    order by (origin.In_Flights + destination.Out_Flights) DESC\n",
        "                                    limit 15\"\"\")\n",
        "MostFlightsByAirports.show()"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------+-------------+\n",
            "|Airport|Total_Flights|\n",
            "+-------+-------------+\n",
            "|    ORD|     13804767|\n",
            "|    ATL|     13102774|\n",
            "|    DFW|     11982524|\n",
            "|    LAX|      8196603|\n",
            "|    DTW|      6900655|\n",
            "|    PHX|      6421985|\n",
            "|    MSP|      6405105|\n",
            "|    IAH|      6391830|\n",
            "|    STL|      6358741|\n",
            "|    CLT|      5677880|\n",
            "|    EWR|      5620104|\n",
            "|    LGA|      5368743|\n",
            "|    PHL|      5344278|\n",
            "|    SEA|      5158456|\n",
            "|    LAS|      5040914|\n",
            "+-------+-------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JQvKMn-Gcml2"
      },
      "source": [
        "## **Airports with Most Passengers**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "tV3D1J6FK7tT",
        "outputId": "e17fac6f-be0d-446c-8776-f9582e8e2266"
      },
      "source": [
        "MostPassengersByAirports = sqlContext.sql(\"\"\"with destination as (select Destination_airport as Airport, sum(Passengers*Flights) as Out_Passengers \n",
        "                                    from df group by Destination_airport),\n",
        "                                    origin as (select Origin_airport as Airport, sum(Passengers) as In_Passengers\n",
        "                                    from df group by Origin_airport)\n",
        "                                    select origin.Airport, (destination.Out_Passengers+origin.In_Passengers) as Total_Passengers\n",
        "                                    from origin, destination \n",
        "                                    where origin.Airport = destination.Airport\n",
        "                                    order by (origin.In_Passengers + destination.Out_Passengers) DESC\n",
        "                                    limit 15\"\"\")\n",
        "MostPassengersByAirports.show()"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+-------+----------------+\n",
            "|Airport|Total_Passengers|\n",
            "+-------+----------------+\n",
            "|    DFW|     64193553706|\n",
            "|    ATL|     62429740643|\n",
            "|    ORD|     56896329462|\n",
            "|    LAX|     53533015275|\n",
            "|    LGA|     32947558489|\n",
            "|    PHX|     32559529060|\n",
            "|    LAS|     29100039374|\n",
            "|    SFO|     27096863160|\n",
            "|    BOS|     24112128142|\n",
            "|    SEA|     23575268222|\n",
            "|    HNL|     23235437905|\n",
            "|    DCA|     20108810076|\n",
            "|    MCO|     19771422559|\n",
            "|    IAH|     19289444015|\n",
            "|    STL|     19122558315|\n",
            "+-------+----------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "f-EIXuLp3GU9"
      },
      "source": [
        "## **Occupancy Rates for Routes with Most Flights**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5WO8R--kJEqY",
        "outputId": "74317df1-46ec-4d5b-9213-535675812ae8"
      },
      "source": [
        "distanceQuery = sqlContext.sql(\"\"\"with table1 as \n",
        "                                    (select least(Origin_airport, Destination_airport) as Airport1, \n",
        "                                    greatest(Destination_airport, Origin_airport) as Airport2, \n",
        "                                    sum(Flights) as Flights,\n",
        "                                    sum(Passengers) as Passengers,\n",
        "                                    sum(Seats) as Seats\n",
        "                                    from df\n",
        "                                    group by least(Origin_airport, Destination_airport), greatest(Destination_airport, Origin_airport)\n",
        "                                    order by 1,2)\n",
        "                                    select t.*, (Passengers*100/Seats) as Occupancy_Rate\n",
        "                                    from table1 t\n",
        "                                    order by Flights DESC, Seats DESC, Passengers DESC, Occupancy_Rate DESC\n",
        "                                    limit 15;\"\"\")\n",
        "distanceQuery = distanceQuery.filter((col(\"Occupancy_Rate\").isNotNull()) & (col(\"Occupancy_Rate\")<=100.0))\n",
        "distanceQuery.show(15)"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+--------+-------+----------+--------+------------------+\n",
            "|Airport1|Airport2|Flights|Passengers|   Seats|    Occupancy_Rate|\n",
            "+--------+--------+-------+----------+--------+------------------+\n",
            "|     HNL|     OGG| 784873|  62109354|96640901| 64.26818599300931|\n",
            "|     LAX|     SFO| 636449|  51119989|79405656|  64.3782717442697|\n",
            "|     LAS|     LAX| 588151|  52511530|80532768| 65.20517213564546|\n",
            "|     PDX|     SEA| 565707|  18475771|34650955| 53.31965886654495|\n",
            "|     LAX|     PHX| 515093|  42695385|65619395| 65.06519147273455|\n",
            "|     BOS|     LGA| 470737|  31242486|64897330| 48.14140427657039|\n",
            "|     MSP|     ORD| 467514|  31301666|55325318|56.577471457100344|\n",
            "|     LAS|     PHX| 460104|  42979048|64844100| 66.28058373853597|\n",
            "|     DCA|     LGA| 439107|  29471657|60663368|   48.582295991215|\n",
            "|     LAX|     SAN| 431076|  11686171|22820096|51.209999291852235|\n",
            "|     LGA|     ORD| 424272|  39981416|59616532| 67.06431028225526|\n",
            "|     DAL|     HOU| 408273|  35573141|53054549| 67.05012420329876|\n",
            "|     ATL|     DFW| 399696|  42941213|59978776| 71.59401352238332|\n",
            "|     LAX|     OAK| 381677|  30429561|47045709| 64.68084262477583|\n",
            "|     EWR|     ORD| 372054|  31122785|50434853| 61.70888413216947|\n",
            "+--------+--------+-------+----------+--------+------------------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "5K1z2Wjy9Q4q"
      },
      "source": [
        "## **Number of Flights vs Distance - Part 1**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NtwG1fxboqS_",
        "outputId": "8960db55-4371-4ce9-da71-1a5601be3f1d"
      },
      "source": [
        "distanceQuery = sqlContext.sql(\"\"\"with table1 as \n",
        "                                    (select least(Origin_airport, Destination_airport) as Airport1, \n",
        "                                    greatest(Destination_airport, Origin_airport) as Airport2, \n",
        "                                    mean(Distance) as Distance,\n",
        "                                    sum(Flights) as Flights\n",
        "                                    from df\n",
        "                                    group by least(Origin_airport, Destination_airport), greatest(Destination_airport, Origin_airport)\n",
        "                                    order by 1,2)\n",
        "                                    select t.*\n",
        "                                    from table1 t\n",
        "                                    where Flights>0\n",
        "                                    order by Distance DESC\n",
        "                                    limit 15;\"\"\")\n",
        "# distanceQuery = distanceQuery.filter((col(\"Occupancy_Rate\").isNotNull()) & (col(\"Occupancy_Rate\")<=100.0))\n",
        "distanceQuery.show(15)"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+--------+--------+-------+\n",
            "|Airport1|Airport2|Distance|Flights|\n",
            "+--------+--------+--------+-------+\n",
            "|     BDL|     HNL|  5018.0|      3|\n",
            "|     HNL|     JFK|  4983.0|    430|\n",
            "|     HIK|     JFK|  4983.0|      3|\n",
            "|     HNL|     LGA|  4976.0|      1|\n",
            "|     EWR|     HNL|  4962.0|   8320|\n",
            "|     JFK|     OGG|  4924.0|      1|\n",
            "|     HNL|     PHL|  4919.0|      1|\n",
            "|     HIK|     MIA|  4862.0|      1|\n",
            "|     HNL|     MIA|  4862.0|      9|\n",
            "|     HNL|     ISO|  4860.0|      1|\n",
            "|     OGG|     PHL|  4859.0|      1|\n",
            "|     BWI|     HNL|  4855.0|      7|\n",
            "|     HNL|     IAD|  4817.0|      3|\n",
            "|     BWI|     OGG|  4793.0|      1|\n",
            "|     HIK|     POB|  4785.0|      1|\n",
            "+--------+--------+--------+-------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gdUeHE_C9gGk"
      },
      "source": [
        "## **Number of Flights vs Distance - Part 2**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0wO-6g-p3kcr",
        "outputId": "88dfb925-8e9f-421d-c31c-60b54956ba48"
      },
      "source": [
        "distanceQuery = sqlContext.sql(\"\"\"with table1 as \n",
        "                                    (select least(Origin_airport, Destination_airport) as Airport1, \n",
        "                                    greatest(Destination_airport, Origin_airport) as Airport2, \n",
        "                                    mean(Distance) as Distance,\n",
        "                                    sum(Flights) as Flights\n",
        "                                    from df\n",
        "                                    group by least(Origin_airport, Destination_airport), greatest(Destination_airport, Origin_airport)\n",
        "                                    order by 1,2)\n",
        "                                    select t.*\n",
        "                                    from table1 t\n",
        "                                    where Flights>0\n",
        "                                    order by Flights DESC\n",
        "                                    limit 15;\"\"\")\n",
        "# distanceQuery = distanceQuery.filter((col(\"Occupancy_Rate\").isNotNull()) & (col(\"Occupancy_Rate\")<=100.0))\n",
        "distanceQuery.show(15)"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "+--------+--------+------------------+-------+\n",
            "|Airport1|Airport2|          Distance|Flights|\n",
            "+--------+--------+------------------+-------+\n",
            "|     HNL|     OGG|             100.0| 784873|\n",
            "|     LAX|     SFO|             337.0| 636449|\n",
            "|     LAS|     LAX|             236.0| 588151|\n",
            "|     PDX|     SEA|             129.0| 565707|\n",
            "|     LAX|     PHX|             370.0| 515093|\n",
            "|     BOS|     LGA|             185.0| 470737|\n",
            "|     MSP|     ORD|             334.0| 467514|\n",
            "|     LAS|     PHX|255.96021840873635| 460104|\n",
            "|     DCA|     LGA|             214.0| 439107|\n",
            "|     LAX|     SAN|             109.0| 431076|\n",
            "|     LGA|     ORD|             733.0| 424272|\n",
            "|     DAL|     HOU|             239.0| 408273|\n",
            "|     ATL|     DFW| 731.9746309301993| 399696|\n",
            "|     LAX|     OAK|             337.0| 381677|\n",
            "|     EWR|     ORD|             719.0| 372054|\n",
            "+--------+--------+------------------+-------+\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    }
  ]
}