analysing_flight_delay.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "analysing_flight_delay.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "private_outputs": true,
      "mount_file_id": "1RA7TpgGh3nfRDqrus5mym4CBfS1N8iOt",
      "authorship_tag": "ABX9TyPCvQpuPz4wIquPtPuL49UB",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    },
    "accelerator": "TPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/YBen1/Analyse_Flight_Data/blob/main/analysing_flight_delay.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Download Data\n",
        "Load all the data for the Year 2019 https://www.transtats.bts.gov/DL_SelectFields.asp?gnoyr_VQ=FGJ&QO_fu146_anzr=b0-gvzr"
      ],
      "metadata": {
        "id": "_1yrJEW8AyzD"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "!python -m pip install \"dask[complete]\"\n",
        "!pip install graphviz"
      ],
      "metadata": {
        "id": "Y82JTS3Pucd9"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "import numpy as np\n",
        "\n",
        "import pandas as pd\n",
        "\n",
        "import dask\n",
        "\n",
        "import dask.dataframe as dd\n",
        "\n",
        "import dask.array as da\n",
        "\n",
        "import dask.bag as db\n",
        "import dask.dataframe as dd"
      ],
      "metadata": {
        "id": "nru-fOe6IYZo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from dask.distributed import Client\n",
        "client = Client()\n",
        "client"
      ],
      "metadata": {
        "id": "feIvGjanytEq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive/')"
      ],
      "metadata": {
        "id": "nuBHXL94W5Kp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Load Data"
      ],
      "metadata": {
        "id": "_rSYy4yAX-XG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "first_time_running = True\n",
        "save_as_csv = False #can only be saved if not existing - if it allready exist, should be false 'False' "
      ],
      "metadata": {
        "id": "yR75sJl3HH_B"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "data_def = {'Div1Airport' : 'object', 'Div1TailNum': 'object', 'Div2Airport' : 'object', 'Div2TailNum' : 'object', 'Div3Airport': 'object', 'ArrTime': 'float64',\n",
        "       'CancellationCode': 'object',\n",
        "       'DepTime': 'float64',\n",
        "       'DepartureDelayGroups': 'float64',\n",
        "       'WheelsOff': 'float64',\n",
        "       'WheelsOn': 'float64','Div3TailNum': 'object' }"
      ],
      "metadata": {
        "id": "c5Eu9dI1D0Ve"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "def load_data(first_time_running, save_as_csv):\n",
        "  if first_time_running == True:\n",
        "    table = dd.read_csv('/content/drive/MyDrive/Analys_Flight_Data/data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2019_1.csv',  \n",
        "                        dtype = data_def,low_memory=False)\n",
        "    table.shape\n",
        "    for month in range(2,13): \n",
        "      month_str = str(month)\n",
        "      path_name = '/content/drive/MyDrive/Analys_Flight_Data/data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2019_'+ month_str +'.csv'\n",
        "      table_part = dd.read_csv(path_name,dtype = data_def, low_memory=False)\n",
        "      table = dd.concat([table,table_part])\n",
        "      print(month_str +'/12')\n",
        "    if save_as_csv == True:  \n",
        "      print(\"saving DataFrame as csv can take a few min...\")  \n",
        "      table_pd = pd.read_csv('/content/drive/MyDrive/Analys_Flight_Data/data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2019_1.csv'\n",
        "          ,low_memory=False)\n",
        "      for month in range(2,13): \n",
        "        month_str = str(month)\n",
        "        path_name = '/content/drive/MyDrive/Analys_Flight_Data/data/On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2019_'+ month_str +'.csv'\n",
        "        table_pd_part = pd.read_csv(path_name, low_memory=False)\n",
        "        table_pd = pd.concat([table_pd,table_pd_part])\n",
        "        print(month_str +'/12')  \n",
        "        table_pd.to_csv('/content/drive/MyDrive/Analys_Flight_Data/data/On_Time_Reporting_Carrier_On_Time_Performance_2019_all.csv')  \n",
        "  else:\n",
        "    table = dd.read_csv('/content/drive/MyDrive/Analys_Flight_Data/data/On_Time_Reporting_Carrier_On_Time_Performance_2019_all.csv',\n",
        "                        dtype = data_def,low_memory=False)\n",
        "  \n",
        "  #table = table.dropna(how = 'all') #Werfe alle Spalten mit \"NaN\" raus, da keien Information enthalten\n",
        "  return table  \n"
      ],
      "metadata": {
        "id": "D4BNc6yE3t5M"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "flights_table = load_data(first_time_running, save_as_csv)"
      ],
      "metadata": {
        "id": "Z6E4VytA7nhS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let see arriving delay per airport"
      ],
      "metadata": {
        "id": "QCbvsVLRC4Qd"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "flights_table.describe()"
      ],
      "metadata": {
        "id": "6Gg9bR_-77y6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "##Analyse\n",
        "\n",
        "There are many ways to analyse the data. In this case we will start by looking  at the airline delay."
      ],
      "metadata": {
        "id": "GBdMYHqZz8du"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "US_2019_Airport_Delay = flights_table.groupby(['Reporting_Airline'])['ArrDelayMinutes'].mean()\n",
        "US_2019_Airport_Delay = US_2019_Airport_Delay.compute()\n",
        "US_2019_Airport_Delay.plot(kind=\"bar\", figsize=(20, 9))"
      ],
      "metadata": {
        "id": "zjB5inRFDY9J"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "US_2019_Airport_Delay"
      ],
      "metadata": {
        "id": "S8hm8aJd2-b6"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "We see that ExpressJet Airlines(EV) has the highst averrage delay in 2019 followe by JetBlue Airways (B6)"
      ],
      "metadata": {
        "id": "IMRRLZ0P2SHp"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        ""
      ],
      "metadata": {
        "id": "yMypsWm6izJl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "list(flights_table.columns)"
      ],
      "metadata": {
        "id": "gNPA2ArsJYoR"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "US_2019_Airport_Delay = flights_table.groupby(['Reporting_Airline'])[['ArrDelay','CarrierDelay','WeatherDelay','NASDelay','SecurityDelay', 'LateAircraftDelay']].mean()\n",
        "US_2019_Airport_Delay.compute().plot(kind=\"bar\", figsize=(20, 9))\n",
        "#flights_table.loc[flights_table.ArrDelay > 20][['ArrDelay','CarrierDelay','WeatherDelay','NASDelay','SecurityDelay', 'LateAircraftDelay']].head()"
      ],
      "metadata": {
        "id": "OZDtHQNcbqsH"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "How are these categories defined?\n",
        "\n",
        "####Air Carrier:\n",
        "The cause of the cancellation or delay was due to circumstances within the airline's control (e.g. maintenance or crew problems, aircraft cleaning, baggage loading, fueling, etc.).\n",
        "####Extreme Weather:\n",
        " Significant meteorological conditions (actual or forecasted) that, in the judgment of the carrier, delays or prevents the operation of a flight such as tornado, blizzard or hurricane.\n",
        "####National Aviation System (NAS): \n",
        "Delays and cancellations attributable to the national aviation system that refer to a broad set of conditions, such as non-extreme weather conditions, airport operations, heavy traffic volume, and air traffic control.\n",
        "####Late-arriving aircraft: \n",
        "A previous flight with same aircraft arrived late, causing the present flight to depart late.\n",
        "####Security: \n",
        "Delays or cancellations caused by evacuation of a terminal or concourse, re-boarding of aircraft because of security breach, inoperative screening equipment and/or long lines in excess of 29 minutes at screening areas.\n"
      ],
      "metadata": {
        "id": "6Lf74e_k7lmX"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#flights_from_SA_df = flights_table.loc[flights_table.OriginCityName == 'San Francisco, CA'] # DataFrame mit fluegen aus San Francisco\n",
        "flights_to_LAX_df = flights_table.loc[flights_table.Dest== 'LAX'][['FlightDate','Reporting_Airline','OriginCityName','Origin','ArrDelayMinutes','DepDelayMinutes','CarrierDelay','WeatherDelay','NASDelay','SecurityDelay', 'LateAircraftDelay']].replace(np.nan, 0) # DataFrame mit fluegen nach San Francisco\n",
        "to_LAX_with_dalay = flights_to_LAX_df.loc[flights_table.ArrDelayMinutes > 0] #DataFrame mit Fluegen die Versaetungen hatten\n",
        "to_LAX_with_small_delay = to_LAX_with_dalay.loc[flights_table.ArrDelayMinutes< 5]"
      ],
      "metadata": {
        "id": "B15t3__ZP-xV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#Questions:\n",
        "-how many lights have been there from LAX? \\\\\n",
        "-how many had a delay? \\\\\n",
        "\n",
        "#Fragen Airline spezifisch:\n",
        "-welche Airlines hatten im Mittel die längsten Verspätungen? \\\\\n",
        "-wie Teilen sich die Verspätungsgründe auf?"
      ],
      "metadata": {
        "id": "fsVbeG92YOAJ"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%time\n",
        "\n",
        "#airline_delay_mean = flights_to_SF_df.groupby('Reporting_Airline')['DepDelayMinutes','ArrDelayMinutes'].mean()\n",
        "\n",
        "airline_delay_mean = flights_to_LAX_df.groupby(['Reporting_Airline']).mean()\n",
        "airline_delay_std = flights_to_LAX_df.groupby(['Reporting_Airline']).std(ddof = 0)\n",
        "\n",
        "city_delay_mean = flights_to_LAX_df.groupby(['OriginCityName','Origin']).mean() #[['ArrDelayMinutes', 'CarrierDelay','WeatherDelay','NASDelay','SecurityDelay','LateAircraftDelay']]\n",
        "city_delay_std = flights_to_LAX_df.groupby(['OriginCityName','Origin']).std(ddof = 0)\n",
        "\n",
        "airline_city_delay_mean = flights_to_LAX_df.groupby(['Reporting_Airline','Origin']).mean()\n",
        "airline_city_delay_std = flights_to_LAX_df.groupby(['Reporting_Airline','Origin']).std(ddof = 0)\n"
      ],
      "metadata": {
        "id": "oEUL3TaDg4s3"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "This Step can take a few Minuts.. depanding on your computer.\n"
      ],
      "metadata": {
        "id": "MrmrHd6cB2Pc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%time\n",
        "\n",
        "pd_airline_delay_mean, pd_airline_delay_std = dask.compute(airline_delay_mean, airline_delay_std)\n",
        "\n",
        "pd_city_delay_mean, pd_city_delay_std = dask.compute(city_delay_mean, city_delay_std)\n",
        "    \n",
        "pd_airline_city_delay_mean, pd_airline_city_delay_std = dask.compute(airline_city_delay_mean, airline_city_delay_std)\n"
      ],
      "metadata": {
        "id": "YubX1ADP_pt3"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%time\n",
        "delay_mean_res, delay_std_res  = dask.compute(delay_mean, delay_std)"
      ],
      "metadata": {
        "id": "hM2S5xr2Z2DX"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "airline_delay_std.head()"
      ],
      "metadata": {
        "id": "Y-tLqrzV2k9v"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%time\n",
        "#delay_mean_res, delay_std_res, airline_delay_mean_res, airline_delay_std_res = dask.compute(delay_mean, delay_std, airline_delay_mean, airline_delay_std )\n",
        "\n",
        "print(delay_mean_res)\n",
        "print(delay_std_res)"
      ],
      "metadata": {
        "id": "W4syoU2kejTO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "pd_airline_city_delay_mean.head()"
      ],
      "metadata": {
        "id": "MUQmTXS6Ju-0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "pd_airline_delay_mean[['CarrierDelay','WeatherDelay','NASDelay','SecurityDelay','LateAircraftDelay']].plot(kind=\"bar\", figsize=(20, 9))\n",
        "pd_city_delay_mean[['CarrierDelay','WeatherDelay','NASDelay','SecurityDelay','LateAircraftDelay']].plot(kind=\"bar\", figsize=(30, 10))\n",
        "pd_airline_delay_std[['CarrierDelay','WeatherDelay','NASDelay','SecurityDelay','LateAircraftDelay']].plot(kind=\"bar\", figsize=(20, 9))"
      ],
      "metadata": {
        "id": "zehMfxSa3VNY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_airline_delay = pd.merge(pd_airline_delay_mean, pd_airline_delay_std, on='Reporting_Airline', suffixes=('_mean', '_std'))\n",
        "df_airline_delay"
      ],
      "metadata": {
        "id": "B1T2JND1rKWI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df_city_delay = pd.merge(pd_city_delay_mean, pd_city_delay_std , on='OriginCityName', suffixes=('_mean', '_std'))\n",
        "df_city_delay"
      ],
      "metadata": {
        "id": "M-qAhdCqssHI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%matplotlib inline\n",
        "#flights_to_SFO_df.groupby(['FlightDate','Reporting_Airline']).mean()['ArrDelayMinutes'].compute().unstack().plot(figsize=(30, 10))\n"
      ],
      "metadata": {
        "id": "mXo9rf5zIDOi"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "flights_to_LAX_df.loc[flights_to_LAX_df.Reporting_Airline== 'AA'].groupby(['FlightDate'])[['CarrierDelay','WeatherDelay','NASDelay','SecurityDelay', 'LateAircraftDelay']].mean().compute().plot(figsize=(30, 10))"
      ],
      "metadata": {
        "id": "dbXCGEDiNHcb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "flights_to_LAX_df.loc[flights_to_LAX_df.Reporting_Airline== 'AA'].groupby(['FlightDate'])[['CarrierDelay','NASDelay','WeatherDelay']].mean().compute().plot(figsize=(30, 10))"
      ],
      "metadata": {
        "id": "FIG4VjJ6ZPQX"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}