Airport_Pandas_results.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Import pandas and data\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "airlines=pd.read_csv('/Users/vyshnavigovindankutty/Documents/Project_1_Airport_data/Airport_data/airlines.csv',sep=',');\n",
    "airport_code=pd.read_csv('/Users/vyshnavigovindankutty/Documents/Project_1_Airport_data/Airport_data/airportcode.csv',sep=',');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>DayofMonth</th>\n",
       "      <th>DayOfWeek</th>\n",
       "      <th>DepTime</th>\n",
       "      <th>CRSDepTime</th>\n",
       "      <th>ArrTime</th>\n",
       "      <th>CRSArrTime</th>\n",
       "      <th>UniqueCarrier</th>\n",
       "      <th>FlightNum</th>\n",
       "      <th>...</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>CancellationCode</th>\n",
       "      <th>Diverted</th>\n",
       "      <th>CarrierDelay</th>\n",
       "      <th>WeatherDelay</th>\n",
       "      <th>NASDelay</th>\n",
       "      <th>SecurityDelay</th>\n",
       "      <th>LateAircraftDelay</th>\n",
       "      <th>IsArrDelayed</th>\n",
       "      <th>IsDepDelayed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>16</td>\n",
       "      <td>3</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>1735</td>\n",
       "      <td>1959.0</td>\n",
       "      <td>2021</td>\n",
       "      <td>OH</td>\n",
       "      <td>5367</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</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>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>1717.0</td>\n",
       "      <td>1701</td>\n",
       "      <td>1915.0</td>\n",
       "      <td>1855</td>\n",
       "      <td>OH</td>\n",
       "      <td>4977</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>YES</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>1220.0</td>\n",
       "      <td>1225</td>\n",
       "      <td>1440.0</td>\n",
       "      <td>1504</td>\n",
       "      <td>OH</td>\n",
       "      <td>5352</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</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>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>1530.0</td>\n",
       "      <td>1530</td>\n",
       "      <td>1645.0</td>\n",
       "      <td>1637</td>\n",
       "      <td>OH</td>\n",
       "      <td>5426</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</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>YES</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008</td>\n",
       "      <td>1</td>\n",
       "      <td>17</td>\n",
       "      <td>4</td>\n",
       "      <td>1203.0</td>\n",
       "      <td>1205</td>\n",
       "      <td>1429.0</td>\n",
       "      <td>1429</td>\n",
       "      <td>OH</td>\n",
       "      <td>5441</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</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>NO</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Year  Month  DayofMonth  DayOfWeek  DepTime  CRSDepTime  ArrTime  \\\n",
       "0  2008      1          16          3   1725.0        1735   1959.0   \n",
       "1  2008      1          17          4   1717.0        1701   1915.0   \n",
       "2  2008      1          17          4   1220.0        1225   1440.0   \n",
       "3  2008      1          17          4   1530.0        1530   1645.0   \n",
       "4  2008      1          17          4   1203.0        1205   1429.0   \n",
       "\n",
       "   CRSArrTime UniqueCarrier  FlightNum  ... Cancelled  CancellationCode  \\\n",
       "0        2021            OH       5367  ...         0               NaN   \n",
       "1        1855            OH       4977  ...         0               NaN   \n",
       "2        1504            OH       5352  ...         0               NaN   \n",
       "3        1637            OH       5426  ...         0               NaN   \n",
       "4        1429            OH       5441  ...         0               NaN   \n",
       "\n",
       "   Diverted  CarrierDelay  WeatherDelay  NASDelay SecurityDelay  \\\n",
       "0         0           NaN           NaN       NaN           NaN   \n",
       "1         0          16.0           0.0       4.0           0.0   \n",
       "2         0           NaN           NaN       NaN           NaN   \n",
       "3         0           NaN           NaN       NaN           NaN   \n",
       "4         0           NaN           NaN       NaN           NaN   \n",
       "\n",
       "  LateAircraftDelay  IsArrDelayed  IsDepDelayed  \n",
       "0               NaN            NO            NO  \n",
       "1               0.0           YES           YES  \n",
       "2               NaN            NO            NO  \n",
       "3               NaN           YES            NO  \n",
       "4               NaN            NO            NO  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airlines.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>IATA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Abbotsford</td>\n",
       "      <td>BC</td>\n",
       "      <td>Canada</td>\n",
       "      <td>YXX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Aberdeen</td>\n",
       "      <td>SD</td>\n",
       "      <td>USA</td>\n",
       "      <td>ABR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Abilene</td>\n",
       "      <td>TX</td>\n",
       "      <td>USA</td>\n",
       "      <td>ABI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Akron</td>\n",
       "      <td>OH</td>\n",
       "      <td>USA</td>\n",
       "      <td>CAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alamosa</td>\n",
       "      <td>CO</td>\n",
       "      <td>USA</td>\n",
       "      <td>ALS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         City State Country IATA\n",
       "0  Abbotsford    BC  Canada  YXX\n",
       "1    Aberdeen    SD     USA  ABR\n",
       "2     Abilene    TX     USA  ABI\n",
       "3       Akron    OH     USA  CAK\n",
       "4     Alamosa    CO     USA  ALS"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airport_code.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### ANALYSIS QUERIES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "54233"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##1 Count of flights that departed late at origin and reached their destination early or on time\n",
    "\n",
    "len(airlines[(airlines.IsDepDelayed=='YES')&(airlines.IsArrDelayed=='NO')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "40104"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##2 Count of flights which departed late from origin by more than 60 minutes\n",
    "\n",
    "len(airlines[((airlines.IsDepDelayed=='YES')&((airlines['DepTime'].apply(pd.to_numeric,errors='coerce'))>airlines.CRSDepTime+100))|((airlines.IsDepDelayed=='YES')&((airlines['DepTime'].apply(pd.to_numeric,errors='coerce'))<airlines.CRSDepTime)&(((2400+(airlines['DepTime'].apply(pd.to_numeric,errors='coerce')))-airlines.CRSDepTime)>100))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "132792"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##3 Count of flights which departed early or on time but arrived late by at least 15 minutes\n",
    "\n",
    "len(airlines[((airlines.IsDepDelayed=='YES')&((airlines['DepTime'].apply(pd.to_numeric,errors='coerce'))>airlines.CRSDepTime+15))|((airlines.IsDepDelayed=='YES')&((airlines['DepTime'].apply(pd.to_numeric,errors='coerce'))<airlines.CRSDepTime)&(((2400+(airlines['DepTime'].apply(pd.to_numeric,errors='coerce')))-airlines.CRSDepTime)>15))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "118212"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##4 Count of flights departed from following major airports - ORD, DFW, ATL, LAX, SFO\n",
    "\n",
    "len(airlines[airlines['Origin'].isin(['ORD','DFW','ATL','LAX','SFO'])])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "##5 Add a column FlightDate by using Year, Month and DayOfMonth. Format should be yyyyMMdd\n",
    "\n",
    "airlines['FlightDate']=airlines['Year'].astype(str).str.cat(airlines['Month'].astype(str).apply(lambda x:x.zfill(2))).str.cat(airlines['DayofMonth'].astype(str).apply(lambda x:x.zfill(2)));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73653"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##6 Count of flights that departed late between January 1 2008 to January 9 2008 using FlightDate\n",
    "\n",
    "len(airlines[(airlines.IsDepDelayed=='YES')&(pd.to_datetime(airlines['FlightDate'])>'20080101')&(pd.to_datetime(airlines['FlightDate'])<'20080109')])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "34708"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##7 Count of flights that departed late on Sundays\n",
    "\n",
    "len(airlines[(airlines.IsDepDelayed=='YES')&(airlines['DayOfWeek']==7)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "##8 Get number of flights that had delayed departure and number of flights delayed in arrival for each day along with number of flights departed for each day for January 2009\n",
    "       #i. Output should contain 4 columns - FlightDate, FlightCount, DepDelayedCount, ArrDelayedCount\n",
    "       #ii.FlightDate should be of YYYY-MM-dd format.\n",
    "       #iii. Data should be sorted in ascending order by flightDate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>State</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CA</th>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TX</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AK</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NY</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FL</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MI</th>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MT</th>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PA</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IL</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CO</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NC</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WY</th>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NE</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WI</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KS</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WA</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GA</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NM</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HI</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MN</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ND</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AZ</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MO</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IA</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AR</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MA</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WV</th>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VA</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OR</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SD</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MS</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ME</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LA</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AL</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IN</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TN</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SC</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OH</th>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OK</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>KY</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NH</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MD</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VT</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NV</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NJ</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UT</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CT</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hawaii</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DE</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RI</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Count\n",
       "State        \n",
       "CA         29\n",
       "TX         26\n",
       "AK         25\n",
       "NY         18\n",
       "FL         18\n",
       "MI         18\n",
       "MT         14\n",
       "PA         13\n",
       "IL         12\n",
       "CO         12\n",
       "NC         10\n",
       "WY         10\n",
       "NE          9\n",
       "WI          9\n",
       "KS          9\n",
       "WA          9\n",
       "GA          9\n",
       "NM          9\n",
       "HI          9\n",
       "MN          8\n",
       "ND          8\n",
       "AZ          8\n",
       "MO          8\n",
       "IA          8\n",
       "AR          8\n",
       "MA          8\n",
       "WV          8\n",
       "VA          7\n",
       "OR          7\n",
       "SD          7\n",
       "MS          7\n",
       "ME          7\n",
       "LA          7\n",
       "AL          6\n",
       "IN          6\n",
       "TN          6\n",
       "SC          6\n",
       "ID          6\n",
       "OH          6\n",
       "OK          5\n",
       "KY          4\n",
       "NH          3\n",
       "MD          3\n",
       "VT          3\n",
       "NV          3\n",
       "NJ          3\n",
       "UT          2\n",
       "CT          2\n",
       "Hawaii      2\n",
       "DE          1\n",
       "RI          1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##9 Get number of airports (IATA Codes) for each state in the US. Sort the data in descending order by count\n",
    "\n",
    "airport_code[airport_code.Country=='USA'].groupby('State').size().to_frame('Count').sort_values('Count',ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Origin\n",
       "ABE     413\n",
       "ABI     240\n",
       "ABQ    3447\n",
       "ABY     102\n",
       "ACT     209\n",
       "       ... \n",
       "WRG      62\n",
       "XNA    1199\n",
       "YAK      62\n",
       "YKM      33\n",
       "YUM     380\n",
       "Length: 270, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##10 Get number of flights departed from each US airport\n",
    "\n",
    "airlines.merge(airport_code[airport_code.Country=='USA'],left_on='Origin',right_on='IATA',how='inner').groupby('Origin').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "State\n",
       "AK     2818\n",
       "AL     3931\n",
       "AR     2928\n",
       "AZ    20768\n",
       "CA    72853\n",
       "dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##11 Get number of flights departed from each US state\n",
    "\n",
    "airlines.merge(airport_code[airport_code.Country=='USA'],left_on='Origin',right_on='IATA',how='inner').groupby('State').size().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ABR</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>322</th>\n",
       "      <td>ACK</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>AHN</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AIA</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>AKN</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALS</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>ALW</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>AOO</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>APF</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>APN</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    IATA Country\n",
       "1    ABR     USA\n",
       "322  ACK     USA\n",
       "20   AHN     USA\n",
       "10   AIA     USA\n",
       "242  AKN     USA\n",
       "4    ALS     USA\n",
       "496  ALW     USA\n",
       "12   AOO     USA\n",
       "323  APF     USA\n",
       "11   APN     USA"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##12 Get the list of airports in the US from which flights have not departed\n",
    "\n",
    "airport_code[~(airport_code['IATA'].isin(airlines['Origin']))&(airport_code['Country']=='USA')][['IATA','Country']].sort_values('IATA',ascending=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['HDN', 'SJU', 'ITO', 'KOA', 'STT', 'OTZ', 'BQN', 'STX', 'PMD',\n",
       "       'CEC', 'PSE', 'SCC', 'SLE', 'CDC', 'PSG', 'ADK'], dtype=object)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##13 Check if there are any origins in airlines data which do not have record in airport-codes\n",
    "\n",
    "airlines[~(airlines['Origin'].isin(airport_code['IATA']))].Origin.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5585"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##14 Get the total number of flights from the airports that do not contain entries in airport-codes\n",
    "\n",
    "len(airlines[~(airlines['Origin'].isin(airport_code['IATA']))])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Origin\n",
       "ADK       9\n",
       "BQN     124\n",
       "CDC      48\n",
       "CEC      88\n",
       "HDN     429\n",
       "ITO     786\n",
       "KOA    1316\n",
       "OTZ      92\n",
       "PMD      57\n",
       "PSE     110\n",
       "PSG      62\n",
       "SCC      62\n",
       "SJU    1997\n",
       "SLE      54\n",
       "STT     311\n",
       "STX      40\n",
       "dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "##15 Get the total number of flights per airport that do not contain entries in airport-codes\n",
    "\n",
    "airlines[~(airlines['Origin'].isin(airport_code['IATA']))].groupby('Origin').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}