Code-Example-003-Pandas-Dataframe-Loading-Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Loading Data into Pandas Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>...</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIR_SYSTEM_DELAY</th>\n",
       "      <th>SECURITY_DELAY</th>\n",
       "      <th>AIRLINE_DELAY</th>\n",
       "      <th>LATE_AIRCRAFT_DELAY</th>\n",
       "      <th>WEATHER_DELAY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>OO</td>\n",
       "      <td>4986</td>\n",
       "      <td>N719SK</td>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "      <td>538</td>\n",
       "      <td>...</td>\n",
       "      <td>654.0</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>AA</td>\n",
       "      <td>1178</td>\n",
       "      <td>N573AA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DFW</td>\n",
       "      <td>550</td>\n",
       "      <td>...</td>\n",
       "      <td>649.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>B6</td>\n",
       "      <td>794</td>\n",
       "      <td>N519JB</td>\n",
       "      <td>AUS</td>\n",
       "      <td>JFK</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>1013.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>OO</td>\n",
       "      <td>6235</td>\n",
       "      <td>N114SY</td>\n",
       "      <td>AUS</td>\n",
       "      <td>SFO</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>753.0</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>UA</td>\n",
       "      <td>572</td>\n",
       "      <td>N404UA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>IAH</td>\n",
       "      <td>630</td>\n",
       "      <td>...</td>\n",
       "      <td>751.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42062</th>\n",
       "      <td>2015</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>WN</td>\n",
       "      <td>5548</td>\n",
       "      <td>N790SW</td>\n",
       "      <td>AUS</td>\n",
       "      <td>BNA</td>\n",
       "      <td>1935</td>\n",
       "      <td>...</td>\n",
       "      <td>2115.0</td>\n",
       "      <td>-15.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42063</th>\n",
       "      <td>2015</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>AA</td>\n",
       "      <td>1187</td>\n",
       "      <td>N005AA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "      <td>2005</td>\n",
       "      <td>...</td>\n",
       "      <td>2212.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42064</th>\n",
       "      <td>2015</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>WN</td>\n",
       "      <td>433</td>\n",
       "      <td>N8602F</td>\n",
       "      <td>AUS</td>\n",
       "      <td>SAN</td>\n",
       "      <td>2035</td>\n",
       "      <td>...</td>\n",
       "      <td>2142.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42065</th>\n",
       "      <td>2015</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>WN</td>\n",
       "      <td>2125</td>\n",
       "      <td>N7747C</td>\n",
       "      <td>AUS</td>\n",
       "      <td>HOU</td>\n",
       "      <td>2055</td>\n",
       "      <td>...</td>\n",
       "      <td>2133.0</td>\n",
       "      <td>-17.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42066</th>\n",
       "      <td>2015</td>\n",
       "      <td>12</td>\n",
       "      <td>31</td>\n",
       "      <td>4</td>\n",
       "      <td>F9</td>\n",
       "      <td>1137</td>\n",
       "      <td>N918FR</td>\n",
       "      <td>AUS</td>\n",
       "      <td>LAS</td>\n",
       "      <td>2115</td>\n",
       "      <td>...</td>\n",
       "      <td>2202.0</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>42067 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       YEAR  MONTH  DAY  DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER  \\\n",
       "0      2015      1    1            4      OO           4986      N719SK   \n",
       "1      2015      1    1            4      AA           1178      N573AA   \n",
       "2      2015      1    1            4      B6            794      N519JB   \n",
       "3      2015      1    1            4      OO           6235      N114SY   \n",
       "4      2015      1    1            4      UA            572      N404UA   \n",
       "...     ...    ...  ...          ...     ...            ...         ...   \n",
       "42062  2015     12   31            4      WN           5548      N790SW   \n",
       "42063  2015     12   31            4      AA           1187      N005AA   \n",
       "42064  2015     12   31            4      WN            433      N8602F   \n",
       "42065  2015     12   31            4      WN           2125      N7747C   \n",
       "42066  2015     12   31            4      F9           1137      N918FR   \n",
       "\n",
       "      ORIGIN_AIRPORT DESTINATION_AIRPORT  SCHEDULED_DEPARTURE  ...  \\\n",
       "0                AUS                 LAX                  538  ...   \n",
       "1                AUS                 DFW                  550  ...   \n",
       "2                AUS                 JFK                  600  ...   \n",
       "3                AUS                 SFO                  600  ...   \n",
       "4                AUS                 IAH                  630  ...   \n",
       "...              ...                 ...                  ...  ...   \n",
       "42062            AUS                 BNA                 1935  ...   \n",
       "42063            AUS                 LAX                 2005  ...   \n",
       "42064            AUS                 SAN                 2035  ...   \n",
       "42065            AUS                 HOU                 2055  ...   \n",
       "42066            AUS                 LAS                 2115  ...   \n",
       "\n",
       "       ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  CANCELLATION_REASON  \\\n",
       "0             654.0          -16.0         0          0                  NaN   \n",
       "1             649.0           -1.0         0          0                  NaN   \n",
       "2            1013.0          -10.0         0          0                  NaN   \n",
       "3             753.0          -16.0         0          0                  NaN   \n",
       "4             751.0           20.0         0          0                  NaN   \n",
       "...             ...            ...       ...        ...                  ...   \n",
       "42062        2115.0          -15.0         0          0                  NaN   \n",
       "42063        2212.0           44.0         0          0                  NaN   \n",
       "42064        2142.0            2.0         0          0                  NaN   \n",
       "42065        2133.0          -17.0         0          0                  NaN   \n",
       "42066        2202.0          -13.0         0          0                  NaN   \n",
       "\n",
       "       AIR_SYSTEM_DELAY  SECURITY_DELAY  AIRLINE_DELAY  LATE_AIRCRAFT_DELAY  \\\n",
       "0                   NaN             NaN            NaN                  NaN   \n",
       "1                   NaN             NaN            NaN                  NaN   \n",
       "2                   NaN             NaN            NaN                  NaN   \n",
       "3                   NaN             NaN            NaN                  NaN   \n",
       "4                   0.0             0.0           20.0                  0.0   \n",
       "...                 ...             ...            ...                  ...   \n",
       "42062               NaN             NaN            NaN                  NaN   \n",
       "42063              44.0             0.0            0.0                  0.0   \n",
       "42064               NaN             NaN            NaN                  NaN   \n",
       "42065               NaN             NaN            NaN                  NaN   \n",
       "42066               NaN             NaN            NaN                  NaN   \n",
       "\n",
       "       WEATHER_DELAY  \n",
       "0                NaN  \n",
       "1                NaN  \n",
       "2                NaN  \n",
       "3                NaN  \n",
       "4                0.0  \n",
       "...              ...  \n",
       "42062            NaN  \n",
       "42063            0.0  \n",
       "42064            NaN  \n",
       "42065            NaN  \n",
       "42066            NaN  \n",
       "\n",
       "[42067 rows x 31 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Flights from Austin,TX data set\n",
    "flights = pd.read_csv(\"datasets/airline/flights_fromAus.csv\") \n",
    "# Preview the first 5 lines of the loaded data \n",
    "flights\n",
    "# or use the head() operation to print out the first 5 lines\n",
    "\n",
    "# You can access a large dataset that includes all flights in USA here \n",
    "# https://s3.amazonaws.com/utcs378/flights.csv.bz2\n",
    "\n",
    "type(flights)\n",
    "# dir(flights)\n",
    "\n",
    "flights"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 42067 entries, 0 to 42066\n",
      "Data columns (total 31 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   YEAR                 42067 non-null  int64  \n",
      " 1   MONTH                42067 non-null  int64  \n",
      " 2   DAY                  42067 non-null  int64  \n",
      " 3   DAY_OF_WEEK          42067 non-null  int64  \n",
      " 4   AIRLINE              42067 non-null  object \n",
      " 5   FLIGHT_NUMBER        42067 non-null  int64  \n",
      " 6   TAIL_NUMBER          41993 non-null  object \n",
      " 7   ORIGIN_AIRPORT       42067 non-null  object \n",
      " 8   DESTINATION_AIRPORT  42067 non-null  object \n",
      " 9   SCHEDULED_DEPARTURE  42067 non-null  int64  \n",
      " 10  DEPARTURE_TIME       41507 non-null  float64\n",
      " 11  DEPARTURE_DELAY      41507 non-null  float64\n",
      " 12  TAXI_OUT             41494 non-null  float64\n",
      " 13  WHEELS_OFF           41494 non-null  float64\n",
      " 14  SCHEDULED_TIME       42067 non-null  float64\n",
      " 15  ELAPSED_TIME         41392 non-null  float64\n",
      " 16  AIR_TIME             41392 non-null  float64\n",
      " 17  DISTANCE             42067 non-null  int64  \n",
      " 18  WHEELS_ON            41469 non-null  float64\n",
      " 19  TAXI_IN              41469 non-null  float64\n",
      " 20  SCHEDULED_ARRIVAL    42067 non-null  int64  \n",
      " 21  ARRIVAL_TIME         41469 non-null  float64\n",
      " 22  ARRIVAL_DELAY        41392 non-null  float64\n",
      " 23  DIVERTED             42067 non-null  int64  \n",
      " 24  CANCELLED            42067 non-null  int64  \n",
      " 25  CANCELLATION_REASON  578 non-null    object \n",
      " 26  AIR_SYSTEM_DELAY     7153 non-null   float64\n",
      " 27  SECURITY_DELAY       7153 non-null   float64\n",
      " 28  AIRLINE_DELAY        7153 non-null   float64\n",
      " 29  LATE_AIRCRAFT_DELAY  7153 non-null   float64\n",
      " 30  WEATHER_DELAY        7153 non-null   float64\n",
      "dtypes: float64(16), int64(10), object(5)\n",
      "memory usage: 9.9+ MB\n"
     ]
    }
   ],
   "source": [
    "# Use the info() operation to print out the Schema of the dataframe\n",
    "flights.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 1 - Find a list of Origin Airports\n",
    "The dataset includes all flights departering from Austin,TX. In this question, we want to find out all airports in Austin area. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    AUS\n",
      "Name: ORIGIN_AIRPORT, dtype: object\n"
     ]
    }
   ],
   "source": [
    "origin_airports=flights['ORIGIN_AIRPORT'].drop_duplicates()\n",
    "print(origin_airports)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AUS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  ORIGIN_AIRPORT\n",
       "0            AUS"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights[['ORIGIN_AIRPORT']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check what is the type of it\n",
    "type(origin_airports)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 2 - Find a list of all (Origin, Destination) pairs that represent a route between Austin and other cities. \n",
    "This question asks to retrieve all direct connections from Austin."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AUS</td>\n",
       "      <td>DFW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AUS</td>\n",
       "      <td>JFK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SFO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUS</td>\n",
       "      <td>IAH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42062</th>\n",
       "      <td>AUS</td>\n",
       "      <td>BNA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42063</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42064</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42065</th>\n",
       "      <td>AUS</td>\n",
       "      <td>HOU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42066</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LAS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>42067 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      ORIGIN_AIRPORT DESTINATION_AIRPORT\n",
       "0                AUS                 LAX\n",
       "1                AUS                 DFW\n",
       "2                AUS                 JFK\n",
       "3                AUS                 SFO\n",
       "4                AUS                 IAH\n",
       "...              ...                 ...\n",
       "42062            AUS                 BNA\n",
       "42063            AUS                 LAX\n",
       "42064            AUS                 SAN\n",
       "42065            AUS                 HOU\n",
       "42066            AUS                 LAS\n",
       "\n",
       "[42067 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights[['ORIGIN_AIRPORT','DESTINATION_AIRPORT']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AUS</td>\n",
       "      <td>DFW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AUS</td>\n",
       "      <td>JFK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SFO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AUS</td>\n",
       "      <td>IAH</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AUS</td>\n",
       "      <td>ATL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AUS</td>\n",
       "      <td>BWI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LGB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AUS</td>\n",
       "      <td>DEN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>AUS</td>\n",
       "      <td>PHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>AUS</td>\n",
       "      <td>DAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AUS</td>\n",
       "      <td>PHX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>AUS</td>\n",
       "      <td>HOU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>AUS</td>\n",
       "      <td>ORD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>AUS</td>\n",
       "      <td>EWR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>AUS</td>\n",
       "      <td>MDW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>AUS</td>\n",
       "      <td>FLL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>AUS</td>\n",
       "      <td>MCO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>AUS</td>\n",
       "      <td>HRL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>AUS</td>\n",
       "      <td>DCA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>AUS</td>\n",
       "      <td>MSY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>AUS</td>\n",
       "      <td>CLT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SLC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>AUS</td>\n",
       "      <td>ELP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>AUS</td>\n",
       "      <td>TPA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>AUS</td>\n",
       "      <td>BNA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>AUS</td>\n",
       "      <td>LBB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>AUS</td>\n",
       "      <td>OAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SEA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>AUS</td>\n",
       "      <td>BOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SJC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>AUS</td>\n",
       "      <td>DTW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1934</th>\n",
       "      <td>AUS</td>\n",
       "      <td>MSP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6899</th>\n",
       "      <td>AUS</td>\n",
       "      <td>MIA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11045</th>\n",
       "      <td>AUS</td>\n",
       "      <td>IAD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21544</th>\n",
       "      <td>AUS</td>\n",
       "      <td>SNA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21593</th>\n",
       "      <td>AUS</td>\n",
       "      <td>STL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21641</th>\n",
       "      <td>AUS</td>\n",
       "      <td>PDX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38791</th>\n",
       "      <td>AUS</td>\n",
       "      <td>MEM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      ORIGIN_AIRPORT DESTINATION_AIRPORT\n",
       "0                AUS                 LAX\n",
       "1                AUS                 DFW\n",
       "2                AUS                 JFK\n",
       "3                AUS                 SFO\n",
       "4                AUS                 IAH\n",
       "5                AUS                 ATL\n",
       "8                AUS                 BWI\n",
       "9                AUS                 LGB\n",
       "12               AUS                 DEN\n",
       "13               AUS                 PHL\n",
       "14               AUS                 LAS\n",
       "18               AUS                 DAL\n",
       "22               AUS                 PHX\n",
       "28               AUS                 HOU\n",
       "29               AUS                 SAN\n",
       "30               AUS                 ORD\n",
       "33               AUS                 EWR\n",
       "34               AUS                 MDW\n",
       "35               AUS                 FLL\n",
       "38               AUS                 MCO\n",
       "40               AUS                 HRL\n",
       "42               AUS                 DCA\n",
       "45               AUS                 MSY\n",
       "49               AUS                 CLT\n",
       "53               AUS                 SLC\n",
       "55               AUS                 ELP\n",
       "60               AUS                 TPA\n",
       "69               AUS                 BNA\n",
       "71               AUS                 LBB\n",
       "76               AUS                 OAK\n",
       "83               AUS                 SEA\n",
       "86               AUS                 BOS\n",
       "88               AUS                 SJC\n",
       "122              AUS                 DTW\n",
       "1934             AUS                 MSP\n",
       "6899             AUS                 MIA\n",
       "11045            AUS                 IAD\n",
       "21544            AUS                 SNA\n",
       "21593            AUS                 STL\n",
       "21641            AUS                 PDX\n",
       "38791            AUS                 MEM"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "origin_destinations=flights[['ORIGIN_AIRPORT','DESTINATION_AIRPORT']].drop_duplicates()\n",
    "origin_destinations\n",
    "# type(origin_destinations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['LAX' 'DFW' 'JFK' 'SFO' 'IAH' 'ATL' 'BWI' 'LGB' 'DEN' 'PHL' 'LAS' 'DAL'\n",
      " 'PHX' 'HOU' 'SAN' 'ORD' 'EWR' 'MDW' 'FLL' 'MCO' 'HRL' 'DCA' 'MSY' 'CLT'\n",
      " 'SLC' 'ELP' 'TPA' 'BNA' 'LBB' 'OAK' 'SEA' 'BOS' 'SJC' 'DTW' 'MSP' 'MIA'\n",
      " 'IAD' 'SNA' 'STL' 'PDX' 'MEM']\n"
     ]
    }
   ],
   "source": [
    "# We can also print out all destinations that one can reach with a direct flight from Austin.\n",
    "print(origin_destinations['DESTINATION_AIRPORT'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0         True\n",
       "1         True\n",
       "2         True\n",
       "3         True\n",
       "4         True\n",
       "         ...  \n",
       "42062    False\n",
       "42063    False\n",
       "42064    False\n",
       "42065    False\n",
       "42066    False\n",
       "Name: MONTH, Length: 42067, dtype: bool"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights['MONTH']==1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>...</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIR_SYSTEM_DELAY</th>\n",
       "      <th>SECURITY_DELAY</th>\n",
       "      <th>AIRLINE_DELAY</th>\n",
       "      <th>LATE_AIRCRAFT_DELAY</th>\n",
       "      <th>WEATHER_DELAY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>OO</td>\n",
       "      <td>4986</td>\n",
       "      <td>N719SK</td>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "      <td>538</td>\n",
       "      <td>...</td>\n",
       "      <td>654.0</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>AA</td>\n",
       "      <td>1178</td>\n",
       "      <td>N573AA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DFW</td>\n",
       "      <td>550</td>\n",
       "      <td>...</td>\n",
       "      <td>649.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>B6</td>\n",
       "      <td>794</td>\n",
       "      <td>N519JB</td>\n",
       "      <td>AUS</td>\n",
       "      <td>JFK</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>1013.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>OO</td>\n",
       "      <td>6235</td>\n",
       "      <td>N114SY</td>\n",
       "      <td>AUS</td>\n",
       "      <td>SFO</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>753.0</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>UA</td>\n",
       "      <td>572</td>\n",
       "      <td>N404UA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>IAH</td>\n",
       "      <td>630</td>\n",
       "      <td>...</td>\n",
       "      <td>751.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3371</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>B6</td>\n",
       "      <td>1038</td>\n",
       "      <td>N216JB</td>\n",
       "      <td>AUS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1845</td>\n",
       "      <td>...</td>\n",
       "      <td>2316.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3372</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>WN</td>\n",
       "      <td>3778</td>\n",
       "      <td>N528SW</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DAL</td>\n",
       "      <td>1855</td>\n",
       "      <td>...</td>\n",
       "      <td>1937.0</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>WN</td>\n",
       "      <td>4158</td>\n",
       "      <td>N7705A</td>\n",
       "      <td>AUS</td>\n",
       "      <td>BWI</td>\n",
       "      <td>1900</td>\n",
       "      <td>...</td>\n",
       "      <td>2240.0</td>\n",
       "      <td>-20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3374</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>WN</td>\n",
       "      <td>3089</td>\n",
       "      <td>N290WN</td>\n",
       "      <td>AUS</td>\n",
       "      <td>EWR</td>\n",
       "      <td>1900</td>\n",
       "      <td>...</td>\n",
       "      <td>2311.0</td>\n",
       "      <td>-19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3375</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>F9</td>\n",
       "      <td>221</td>\n",
       "      <td>N949FR</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1932</td>\n",
       "      <td>...</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>-29.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3376 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      YEAR  MONTH  DAY  DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER  \\\n",
       "0     2015      1    1            4      OO           4986      N719SK   \n",
       "1     2015      1    1            4      AA           1178      N573AA   \n",
       "2     2015      1    1            4      B6            794      N519JB   \n",
       "3     2015      1    1            4      OO           6235      N114SY   \n",
       "4     2015      1    1            4      UA            572      N404UA   \n",
       "...    ...    ...  ...          ...     ...            ...         ...   \n",
       "3371  2015      1   31            6      B6           1038      N216JB   \n",
       "3372  2015      1   31            6      WN           3778      N528SW   \n",
       "3373  2015      1   31            6      WN           4158      N7705A   \n",
       "3374  2015      1   31            6      WN           3089      N290WN   \n",
       "3375  2015      1   31            6      F9            221      N949FR   \n",
       "\n",
       "     ORIGIN_AIRPORT DESTINATION_AIRPORT  SCHEDULED_DEPARTURE  ...  \\\n",
       "0               AUS                 LAX                  538  ...   \n",
       "1               AUS                 DFW                  550  ...   \n",
       "2               AUS                 JFK                  600  ...   \n",
       "3               AUS                 SFO                  600  ...   \n",
       "4               AUS                 IAH                  630  ...   \n",
       "...             ...                 ...                  ...  ...   \n",
       "3371            AUS                 BOS                 1845  ...   \n",
       "3372            AUS                 DAL                 1855  ...   \n",
       "3373            AUS                 BWI                 1900  ...   \n",
       "3374            AUS                 EWR                 1900  ...   \n",
       "3375            AUS                 DEN                 1932  ...   \n",
       "\n",
       "      ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  CANCELLATION_REASON  \\\n",
       "0            654.0          -16.0         0          0                  NaN   \n",
       "1            649.0           -1.0         0          0                  NaN   \n",
       "2           1013.0          -10.0         0          0                  NaN   \n",
       "3            753.0          -16.0         0          0                  NaN   \n",
       "4            751.0           20.0         0          0                  NaN   \n",
       "...            ...            ...       ...        ...                  ...   \n",
       "3371        2316.0           -3.0         0          0                  NaN   \n",
       "3372        1937.0          -13.0         0          0                  NaN   \n",
       "3373        2240.0          -20.0         0          0                  NaN   \n",
       "3374        2311.0          -19.0         0          0                  NaN   \n",
       "3375        2016.0          -29.0         0          0                  NaN   \n",
       "\n",
       "      AIR_SYSTEM_DELAY  SECURITY_DELAY  AIRLINE_DELAY  LATE_AIRCRAFT_DELAY  \\\n",
       "0                  NaN             NaN            NaN                  NaN   \n",
       "1                  NaN             NaN            NaN                  NaN   \n",
       "2                  NaN             NaN            NaN                  NaN   \n",
       "3                  NaN             NaN            NaN                  NaN   \n",
       "4                  0.0             0.0           20.0                  0.0   \n",
       "...                ...             ...            ...                  ...   \n",
       "3371               NaN             NaN            NaN                  NaN   \n",
       "3372               NaN             NaN            NaN                  NaN   \n",
       "3373               NaN             NaN            NaN                  NaN   \n",
       "3374               NaN             NaN            NaN                  NaN   \n",
       "3375               NaN             NaN            NaN                  NaN   \n",
       "\n",
       "      WEATHER_DELAY  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               0.0  \n",
       "...             ...  \n",
       "3371            NaN  \n",
       "3372            NaN  \n",
       "3373            NaN  \n",
       "3374            NaN  \n",
       "3375            NaN  \n",
       "\n",
       "[3376 rows x 31 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_in_jan=flights[flights['MONTH']==1]\n",
    "\n",
    "flights_in_jan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 3 - Find the Origin airport that had the largest departure delay in the month of January\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>YEAR</th>\n",
       "      <th>MONTH</th>\n",
       "      <th>DAY</th>\n",
       "      <th>DAY_OF_WEEK</th>\n",
       "      <th>AIRLINE</th>\n",
       "      <th>FLIGHT_NUMBER</th>\n",
       "      <th>TAIL_NUMBER</th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DESTINATION_AIRPORT</th>\n",
       "      <th>SCHEDULED_DEPARTURE</th>\n",
       "      <th>...</th>\n",
       "      <th>ARRIVAL_TIME</th>\n",
       "      <th>ARRIVAL_DELAY</th>\n",
       "      <th>DIVERTED</th>\n",
       "      <th>CANCELLED</th>\n",
       "      <th>CANCELLATION_REASON</th>\n",
       "      <th>AIR_SYSTEM_DELAY</th>\n",
       "      <th>SECURITY_DELAY</th>\n",
       "      <th>AIRLINE_DELAY</th>\n",
       "      <th>LATE_AIRCRAFT_DELAY</th>\n",
       "      <th>WEATHER_DELAY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>OO</td>\n",
       "      <td>4986</td>\n",
       "      <td>N719SK</td>\n",
       "      <td>AUS</td>\n",
       "      <td>LAX</td>\n",
       "      <td>538</td>\n",
       "      <td>...</td>\n",
       "      <td>654.0</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>AA</td>\n",
       "      <td>1178</td>\n",
       "      <td>N573AA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DFW</td>\n",
       "      <td>550</td>\n",
       "      <td>...</td>\n",
       "      <td>649.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>B6</td>\n",
       "      <td>794</td>\n",
       "      <td>N519JB</td>\n",
       "      <td>AUS</td>\n",
       "      <td>JFK</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>1013.0</td>\n",
       "      <td>-10.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>OO</td>\n",
       "      <td>6235</td>\n",
       "      <td>N114SY</td>\n",
       "      <td>AUS</td>\n",
       "      <td>SFO</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>753.0</td>\n",
       "      <td>-16.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>UA</td>\n",
       "      <td>572</td>\n",
       "      <td>N404UA</td>\n",
       "      <td>AUS</td>\n",
       "      <td>IAH</td>\n",
       "      <td>630</td>\n",
       "      <td>...</td>\n",
       "      <td>751.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3371</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>B6</td>\n",
       "      <td>1038</td>\n",
       "      <td>N216JB</td>\n",
       "      <td>AUS</td>\n",
       "      <td>BOS</td>\n",
       "      <td>1845</td>\n",
       "      <td>...</td>\n",
       "      <td>2316.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3372</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>WN</td>\n",
       "      <td>3778</td>\n",
       "      <td>N528SW</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DAL</td>\n",
       "      <td>1855</td>\n",
       "      <td>...</td>\n",
       "      <td>1937.0</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>WN</td>\n",
       "      <td>4158</td>\n",
       "      <td>N7705A</td>\n",
       "      <td>AUS</td>\n",
       "      <td>BWI</td>\n",
       "      <td>1900</td>\n",
       "      <td>...</td>\n",
       "      <td>2240.0</td>\n",
       "      <td>-20.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3374</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>WN</td>\n",
       "      <td>3089</td>\n",
       "      <td>N290WN</td>\n",
       "      <td>AUS</td>\n",
       "      <td>EWR</td>\n",
       "      <td>1900</td>\n",
       "      <td>...</td>\n",
       "      <td>2311.0</td>\n",
       "      <td>-19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3375</th>\n",
       "      <td>2015</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>6</td>\n",
       "      <td>F9</td>\n",
       "      <td>221</td>\n",
       "      <td>N949FR</td>\n",
       "      <td>AUS</td>\n",
       "      <td>DEN</td>\n",
       "      <td>1932</td>\n",
       "      <td>...</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>-29.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3376 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      YEAR  MONTH  DAY  DAY_OF_WEEK AIRLINE  FLIGHT_NUMBER TAIL_NUMBER  \\\n",
       "0     2015      1    1            4      OO           4986      N719SK   \n",
       "1     2015      1    1            4      AA           1178      N573AA   \n",
       "2     2015      1    1            4      B6            794      N519JB   \n",
       "3     2015      1    1            4      OO           6235      N114SY   \n",
       "4     2015      1    1            4      UA            572      N404UA   \n",
       "...    ...    ...  ...          ...     ...            ...         ...   \n",
       "3371  2015      1   31            6      B6           1038      N216JB   \n",
       "3372  2015      1   31            6      WN           3778      N528SW   \n",
       "3373  2015      1   31            6      WN           4158      N7705A   \n",
       "3374  2015      1   31            6      WN           3089      N290WN   \n",
       "3375  2015      1   31            6      F9            221      N949FR   \n",
       "\n",
       "     ORIGIN_AIRPORT DESTINATION_AIRPORT  SCHEDULED_DEPARTURE  ...  \\\n",
       "0               AUS                 LAX                  538  ...   \n",
       "1               AUS                 DFW                  550  ...   \n",
       "2               AUS                 JFK                  600  ...   \n",
       "3               AUS                 SFO                  600  ...   \n",
       "4               AUS                 IAH                  630  ...   \n",
       "...             ...                 ...                  ...  ...   \n",
       "3371            AUS                 BOS                 1845  ...   \n",
       "3372            AUS                 DAL                 1855  ...   \n",
       "3373            AUS                 BWI                 1900  ...   \n",
       "3374            AUS                 EWR                 1900  ...   \n",
       "3375            AUS                 DEN                 1932  ...   \n",
       "\n",
       "      ARRIVAL_TIME  ARRIVAL_DELAY  DIVERTED  CANCELLED  CANCELLATION_REASON  \\\n",
       "0            654.0          -16.0         0          0                  NaN   \n",
       "1            649.0           -1.0         0          0                  NaN   \n",
       "2           1013.0          -10.0         0          0                  NaN   \n",
       "3            753.0          -16.0         0          0                  NaN   \n",
       "4            751.0           20.0         0          0                  NaN   \n",
       "...            ...            ...       ...        ...                  ...   \n",
       "3371        2316.0           -3.0         0          0                  NaN   \n",
       "3372        1937.0          -13.0         0          0                  NaN   \n",
       "3373        2240.0          -20.0         0          0                  NaN   \n",
       "3374        2311.0          -19.0         0          0                  NaN   \n",
       "3375        2016.0          -29.0         0          0                  NaN   \n",
       "\n",
       "      AIR_SYSTEM_DELAY  SECURITY_DELAY  AIRLINE_DELAY  LATE_AIRCRAFT_DELAY  \\\n",
       "0                  NaN             NaN            NaN                  NaN   \n",
       "1                  NaN             NaN            NaN                  NaN   \n",
       "2                  NaN             NaN            NaN                  NaN   \n",
       "3                  NaN             NaN            NaN                  NaN   \n",
       "4                  0.0             0.0           20.0                  0.0   \n",
       "...                ...             ...            ...                  ...   \n",
       "3371               NaN             NaN            NaN                  NaN   \n",
       "3372               NaN             NaN            NaN                  NaN   \n",
       "3373               NaN             NaN            NaN                  NaN   \n",
       "3374               NaN             NaN            NaN                  NaN   \n",
       "3375               NaN             NaN            NaN                  NaN   \n",
       "\n",
       "      WEATHER_DELAY  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               0.0  \n",
       "...             ...  \n",
       "3371            NaN  \n",
       "3372            NaN  \n",
       "3373            NaN  \n",
       "3374            NaN  \n",
       "3375            NaN  \n",
       "\n",
       "[3376 rows x 31 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights_in_jan=flights[flights['MONTH']==1]\n",
    "flights_in_jan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1450.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "flights['DEPARTURE_DELAY'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1116    AUS\n",
      "Name: ORIGIN_AIRPORT, dtype: object\n"
     ]
    }
   ],
   "source": [
    "flights_in_jan=flights[flights['MONTH']==1]\n",
    "\n",
    "\n",
    "max_delay_flight=flights_in_jan[flights_in_jan['DEPARTURE_DELAY'] >= flights_in_jan['DEPARTURE_DELAY'].max()]\n",
    "\n",
    "print(max_delay_flight['ORIGIN_AIRPORT'])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ORIGIN_AIRPORT</th>\n",
       "      <th>DEPARTURE_DELAY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1116</th>\n",
       "      <td>AUS</td>\n",
       "      <td>1450.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>472</th>\n",
       "      <td>AUS</td>\n",
       "      <td>513.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>219</th>\n",
       "      <td>AUS</td>\n",
       "      <td>431.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ORIGIN_AIRPORT  DEPARTURE_DELAY\n",
       "1116            AUS           1450.0\n",
       "472             AUS            513.0\n",
       "219             AUS            431.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp = flights_in_jan[['ORIGIN_AIRPORT', 'DEPARTURE_DELAY']]\n",
    "\n",
    "tmp.nlargest(3, 'DEPARTURE_DELAY' )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 4 - Find out which Airline has the largest delay on Weekends.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       DEPARTURE_DELAY AIRLINE  FLIGHT_NUMBER\n",
      "1116            1450.0      AA           1595\n",
      "14168           1102.0      UA           1010\n",
      "41562            986.0      AA           1187\n",
      "37902            918.0      F9           1490\n",
      "18706            862.0      AA             66\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1116     AA\n",
       "14168    UA\n",
       "41562    AA\n",
       "37902    F9\n",
       "18706    AA\n",
       "Name: AIRLINE, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "# Filter out the flights that are on days of week 6 or 7\n",
    "flights_on_weekends = flights[flights['DAY_OF_WEEK'].isin([6,7])]\n",
    "\n",
    "flight_on_weekend_with_largest_delay=flights_on_weekends.nlargest(5,['DEPARTURE_DELAY'])\n",
    "\n",
    "print(flight_on_weekend_with_largest_delay[['DEPARTURE_DELAY', 'AIRLINE', 'FLIGHT_NUMBER']])\n",
    "\n",
    "\n",
    "flight_on_weekend_with_largest_delay['AIRLINE']\n",
    "\n",
    "# AA has also the highest number of flights maybe. \n",
    "# It is better to calculate the ratios. Your task to do that!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 5 -  Which destination airport has the most cancellation of flights?\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "The largest cancelation is for the following Destition:  DESTINATION_AIRPORT\n",
      "DFW    136\n",
      "Name: count, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# First, we get all flights that are canceled \n",
    "cancelled_flights = flights[flights['CANCELLED']==1]\n",
    "# print(cancelled_flights)\n",
    "\n",
    "# There are many different ways that you can do this. \n",
    "# One of the most simple ways is the following: \n",
    "# Use Pandas Series and function value_counts()\n",
    "destinations = cancelled_flights['DESTINATION_AIRPORT'].value_counts()\n",
    "\n",
    "# print(destinations)\n",
    "\n",
    "print(\"\\n\\nThe largest cancelation is for the following Destition: \", destinations.nlargest(1))\n",
    "\n",
    "# flights['DESTINATION_AIRPORT'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 5.1: Which destination airport has the most cancellation of flights in relation to number of flights to that destination. Which destination has the highest cancalation ratio? \n",
    "The question 5 answer is the DAL airpot, but from Austin we have the most flights to Dallas. \n",
    "\n",
    "In this question, we want to find out the cancalation rate which is the rates of cancaled flights divided by number of flights to that destination. \n",
    "\n",
    "Students have to implement this.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 6 - Which airline carrier has the highest flight cancelation ratio?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           Count\n",
      "AIRLINE         \n",
      "AA        8044.0\n",
      "AS         400.0\n",
      "B6        2208.0\n",
      "DL        3449.0\n",
      "EV         328.0\n",
      "F9        1210.0\n",
      "OO        2910.0\n",
      "UA        4278.0\n",
      "US         469.0\n",
      "VX        1399.0\n",
      "WN       17372.0\n",
      "         Count\n",
      "AIRLINE       \n",
      "AA       210.0\n",
      "B6        25.0\n",
      "DL        10.0\n",
      "EV         5.0\n",
      "F9         2.0\n",
      "OO        50.0\n",
      "UA        47.0\n",
      "US         9.0\n",
      "VX        28.0\n",
      "WN       192.0\n",
      "         Count_left  Count_right\n",
      "AIRLINE                         \n",
      "AA            210.0       8044.0\n",
      "B6             25.0       2208.0\n",
      "DL             10.0       3449.0\n",
      "EV              5.0        328.0\n",
      "F9              2.0       1210.0\n",
      "OO             50.0       2910.0\n",
      "UA             47.0       4278.0\n",
      "US              9.0        469.0\n",
      "VX             28.0       1399.0\n",
      "WN            192.0      17372.0\n",
      "  AIRLINE  Count_left  Count_right     rates\n",
      "0      AA       210.0       8044.0  0.026106\n",
      "1      B6        25.0       2208.0  0.011322\n",
      "2      DL        10.0       3449.0  0.002899\n",
      "3      EV         5.0        328.0  0.015244\n",
      "4      F9         2.0       1210.0  0.001653\n",
      "5      OO        50.0       2910.0  0.017182\n",
      "6      UA        47.0       4278.0  0.010986\n",
      "7      US         9.0        469.0  0.019190\n",
      "8      VX        28.0       1399.0  0.020014\n",
      "9      WN       192.0      17372.0  0.011052\n"
     ]
    }
   ],
   "source": [
    "flights_count=flights['AIRLINE'].to_frame()\n",
    "type(flights_count)\n",
    "ones=np.ones(len(flights_count))\n",
    "flights_count['Count']=ones\n",
    "flights_count\n",
    "\n",
    "\n",
    "# # # Use groupby and sum to count values \n",
    "sum_for_each_airline = flights_count.groupby(['AIRLINE']).sum()\n",
    "print(sum_for_each_airline)\n",
    "\n",
    "\n",
    "# # # now, we get all of the cancelled flights \n",
    "cancelled_flights=(flights[flights['CANCELLED']==1])\n",
    "cancelled_flights_counts=cancelled_flights['AIRLINE'].to_frame()\n",
    "\n",
    "ones=np.ones(len(cancelled_flights_counts))\n",
    "cancelled_flights_counts['Count']=ones\n",
    "\n",
    "\n",
    "sum_for_each_airline_cancelled = cancelled_flights_counts.groupby(['AIRLINE']).sum()\n",
    "\n",
    "print(sum_for_each_airline_cancelled)\n",
    "\n",
    "# # # Join the two datasets on Airline key \n",
    "joined_df=sum_for_each_airline_cancelled.join(sum_for_each_airline, on='AIRLINE', how='left', lsuffix='_left', rsuffix='_right')\n",
    "\n",
    "print(joined_df)\n",
    "\n",
    "\n",
    "joined_df['rates']=joined_df['Count_left']/joined_df['Count_right']\n",
    "\n",
    "# print(joined_df)\n",
    "# # # Reset the index to access the column \"AIRLINE\"\n",
    "joined_df.reset_index(level=0, inplace=True)\n",
    "\n",
    "print(joined_df)\n",
    "\n",
    "\n",
    "# result=joined_df.drop(['Count_left','Count_right'],  axis=1)\n",
    "# result\n",
    "\n",
    "# result[result['rates'] >= result['rates'].max()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Another way was to use value_counts()\n",
    "\n",
    "flights_count=flights['AIRLINE'].value_counts()\n",
    "# print(flights_count)\n",
    "\n",
    "cancelled_flights=(flights[flights['CANCELLED']==1])\n",
    "cancelled_flights_counts=cancelled_flights['AIRLINE'].value_counts()\n",
    "# print(cancelled_flights_counts)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 7 - Find the largest departure delay for each carrier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Do it yourself"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 8 - Find the largest departure delay for each carrier in each month\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Do it yourself"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 9 - For each carrier find the average Departure delay for each month\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Do it yourself"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 10 - Which day of the week has the highest cancelation of flights?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DAY_OF_WEEK\n",
       "1              111\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Do it yourself\n",
    "cancelled_flights = flights[flights['CANCELLED']==1]\n",
    "\n",
    "results = cancelled_flights[['DAY_OF_WEEK']].value_counts()\n",
    "\n",
    "results.nlargest(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 11 - For each Airline which day of the week has the highest cancelation of flights? \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DAY_OF_WEEK  AIRLINE\n",
       "7            AA         46\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Do it yourself\n",
    "import numpy as np\n",
    "\n",
    "cancelled_flights = flights[flights['CANCELLED']==1]\n",
    "\n",
    "tmp = cancelled_flights[['DAY_OF_WEEK', 'AIRLINE']]\n",
    "\n",
    "\n",
    "result = tmp.value_counts()\n",
    "\n",
    "result.nlargest(1)\n",
    "\n",
    "# Another way to generate the results. \n",
    "# ones=np.ones(len(tmp))\n",
    "# # tmp['Count']=ones\n",
    "# tmp.loc[:, 'Count']=ones\n",
    "# tmp\n",
    "\n",
    "# result = tmp.groupby(['DAY_OF_WEEK', 'AIRLINE']).sum()\n",
    "\n",
    "# result.nlargest(1, 'Count')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Use the Airpot Dataset and define your own questions. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>iata</th>\n",
       "      <th>airport</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>country</th>\n",
       "      <th>lat</th>\n",
       "      <th>long</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00M</td>\n",
       "      <td>Thigpen</td>\n",
       "      <td>Bay Springs</td>\n",
       "      <td>MS</td>\n",
       "      <td>USA</td>\n",
       "      <td>31.953765</td>\n",
       "      <td>-89.234505</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00R</td>\n",
       "      <td>Livingston Municipal</td>\n",
       "      <td>Livingston</td>\n",
       "      <td>TX</td>\n",
       "      <td>USA</td>\n",
       "      <td>30.685861</td>\n",
       "      <td>-95.017928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00V</td>\n",
       "      <td>Meadow Lake</td>\n",
       "      <td>Colorado Springs</td>\n",
       "      <td>CO</td>\n",
       "      <td>USA</td>\n",
       "      <td>38.945749</td>\n",
       "      <td>-104.569893</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01G</td>\n",
       "      <td>Perry-Warsaw</td>\n",
       "      <td>Perry</td>\n",
       "      <td>NY</td>\n",
       "      <td>USA</td>\n",
       "      <td>42.741347</td>\n",
       "      <td>-78.052081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01J</td>\n",
       "      <td>Hilliard Airpark</td>\n",
       "      <td>Hilliard</td>\n",
       "      <td>FL</td>\n",
       "      <td>USA</td>\n",
       "      <td>30.688012</td>\n",
       "      <td>-81.905944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3371</th>\n",
       "      <td>ZEF</td>\n",
       "      <td>Elkin Municipal</td>\n",
       "      <td>Elkin</td>\n",
       "      <td>NC</td>\n",
       "      <td>USA</td>\n",
       "      <td>36.280024</td>\n",
       "      <td>-80.786069</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3372</th>\n",
       "      <td>ZER</td>\n",
       "      <td>Schuylkill Cty/Joe Zerbey</td>\n",
       "      <td>Pottsville</td>\n",
       "      <td>PA</td>\n",
       "      <td>USA</td>\n",
       "      <td>40.706449</td>\n",
       "      <td>-76.373147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3373</th>\n",
       "      <td>ZPH</td>\n",
       "      <td>Zephyrhills Municipal</td>\n",
       "      <td>Zephyrhills</td>\n",
       "      <td>FL</td>\n",
       "      <td>USA</td>\n",
       "      <td>28.228065</td>\n",
       "      <td>-82.155916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3374</th>\n",
       "      <td>ZUN</td>\n",
       "      <td>Black Rock</td>\n",
       "      <td>Zuni</td>\n",
       "      <td>NM</td>\n",
       "      <td>USA</td>\n",
       "      <td>35.083227</td>\n",
       "      <td>-108.791777</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3375</th>\n",
       "      <td>ZZV</td>\n",
       "      <td>Zanesville Municipal</td>\n",
       "      <td>Zanesville</td>\n",
       "      <td>OH</td>\n",
       "      <td>USA</td>\n",
       "      <td>39.944458</td>\n",
       "      <td>-81.892105</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3376 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     iata                    airport              city state country  \\\n",
       "0     00M                   Thigpen        Bay Springs    MS     USA   \n",
       "1     00R       Livingston Municipal        Livingston    TX     USA   \n",
       "2     00V                Meadow Lake  Colorado Springs    CO     USA   \n",
       "3     01G               Perry-Warsaw             Perry    NY     USA   \n",
       "4     01J           Hilliard Airpark          Hilliard    FL     USA   \n",
       "...   ...                        ...               ...   ...     ...   \n",
       "3371  ZEF            Elkin Municipal             Elkin    NC     USA   \n",
       "3372  ZER  Schuylkill Cty/Joe Zerbey        Pottsville    PA     USA   \n",
       "3373  ZPH      Zephyrhills Municipal       Zephyrhills    FL     USA   \n",
       "3374  ZUN                 Black Rock              Zuni    NM     USA   \n",
       "3375  ZZV       Zanesville Municipal        Zanesville    OH     USA   \n",
       "\n",
       "            lat        long  \n",
       "0     31.953765  -89.234505  \n",
       "1     30.685861  -95.017928  \n",
       "2     38.945749 -104.569893  \n",
       "3     42.741347  -78.052081  \n",
       "4     30.688012  -81.905944  \n",
       "...         ...         ...  \n",
       "3371  36.280024  -80.786069  \n",
       "3372  40.706449  -76.373147  \n",
       "3373  28.228065  -82.155916  \n",
       "3374  35.083227 -108.791777  \n",
       "3375  39.944458  -81.892105  \n",
       "\n",
       "[3376 rows x 7 columns]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airports = pd.read_csv(\"datasets/airline/airports.csv\") \n",
    "\n",
    "airports"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 12 : Which Destination City has the highst cancelation of flights? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Dallas-Fort Worth</th>\n",
       "      <td>136</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   count\n",
       "city                    \n",
       "Dallas-Fort Worth    136"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cancelled_flights = flights[flights['CANCELLED']==1]\n",
    "cancelled = cancelled_flights[['DESTINATION_AIRPORT']]\n",
    "\n",
    "\n",
    "tmp = pd.DataFrame(cancelled.value_counts())\n",
    "\n",
    "# we rest the index and make it nice looking \n",
    "tmp.reset_index(col_level=0, inplace=True)\n",
    "# Print it to see what we have so far\n",
    "# print(tmp)\n",
    "\n",
    "df2 = airports[['iata', 'city']]\n",
    "\n",
    "# Print it to see what we have so far\n",
    "# print(df2)\n",
    "\n",
    "tmp  = tmp.merge(df2, left_on='DESTINATION_AIRPORT', right_on='iata' )\n",
    "\n",
    "# Print it to see what we have so far\n",
    "# print(tmp)\n",
    "\n",
    "result = tmp[['count', 'city']]\n",
    "\n",
    "result = result.groupby('city').sum()\n",
    "result.nlargest(1, 'count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 13 : Which Destination State has the highst departure delay on Weekends? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Question 14: Define your own Question"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}