{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "### Problem Statement\n", "You are working for an airline company looking to enter the United States domestic market.\n", "Specifically, the company has decided to start with 5 round trip routes between medium and\n", "large US airports. An example of a round trip route is the combination of JFK to ORD and ORD\n", "to JFK. The airline company has to acquire 5 new airplanes (one per round trip route) and the\n", "upfront cost for each airplane is $90 million. The company's motto is \"On time, for you\", so\n", "punctuality is a big part of its brand image.\n", " \n", "You have been tasked with analyzing 1Q2019 data to identify:\n", "1. The 10 busiest round trip routes in terms of number of round trip flights in the quarter.\n", "Exclude canceled flights when performing the calculation.\n", "2. The 10 most profitable round trip routes (without considering the upfront airplane cost) in\n", "the quarter. Along with the profit, show total revenue, total cost, summary values of\n", "other key components and total round trip flights in the quarter for the top 10 most\n", "profitable routes. Exclude canceled flights from these calculations.\n", "3. The 5 round trip routes that you recommend to invest in based on any factors that you\n", "choose.\n", "4. The number of round trip flights it will take to breakeven on the upfront airplane cost for\n", "each of the 5 round trip routes that you recommend. Print key summary components for\n", "these routes.\n", "5. Key Performance Indicators (KPI's) that you recommend tracking in the future to\n", "measure the success of the round trip routes that you recommend." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Here is background information on the three datasets that you will analyze:\n", "1. Flights dataset: Contains data about available routes from origin to destination. For\n", "occupancy, use the data provided in this dataset.\n", "2. Tickets dataset: Ticket prices data (sample data only as the data is huge). Consider\n", "only round trips in your analysis.\n", "3. Airport Codes dataset: Identifies whether an airport is considered medium or large\n", "sized. Consider only medium and large airports in your analysis.\n", "Please do not use any data other than what has been provided to you.\n", "When joining these datasets together, use your best judgment on the join condition and\n", "document your choice.\n", "Again, keep in mind that these are real-world datasets that come with outliers and data issues\n", "that you need to address." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "You can make the following assumptions:\n", "\n", "● Each airplane is dedicated to one round trip route between the 2 airports\n", "\n", "● Costs:\n", "\n", "○ Fuel, Oil, Maintenance, Crew - $8 per mile total\n", "\n", "○ Depreciation, Insurance, Other - \\$1.18 per mile total\n", "\n", "○ Airport operational costs for the right to use the airports and related services are fixed at 5,000 dollars for medium airports and $10,000 for large airports. There is one charge for each airport where a flight lands. Thus, a round trip flight has a total of\n", "two airport charges.\n", "\n", "○ For each individual departure, the first 15 minutes of delays are free, otherwise each minute costs the airline $75 in added operational costs.\n", "\n", "○ For each individual arrival, the first 15 minutes of delays are free, otherwise each minute costs the airline $75 in added operational costs.\n", "\n", "● Revenue:\n", "\n", "○ Each plane can accommodate up to 200 passengers and each flight has an associated occupancy rate provided in the Flights data set. Do not use the Tickets data set to determine occupancy.\n", "\n", "○ Baggage fee is 35 dollars for each checked bag per flight. We expect 50\\% of passengers to check an average of 1 bag per flight. The fee is charged separately for each leg of a round trip flight, thus 50\\% of passengers will be charged a total of 70 dollars in baggage fees for a round trip flight.\n", "\n", "○ Disregard seasonal effects on ticket prices (i.e. ticket prices are the same in April as they are on Memorial Day or in December)" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "# import packages\n", "import pandas as pd\n", "import numpy as np\n", "import requests\n", "import zipfile\n", "import io\n", "import matplotlib.pyplot as plt\n", "pd.set_option(\"display.precision\", 2)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "# import data\n", "url = \"https://github.com/CapitalOneRecruiting/DA-Airline-Data-Challenge/blob/main/data.zip?raw=true\"\n", "r = requests.get(url)\n", "z = zipfile.ZipFile(io.BytesIO(r.content))\n", "airport_data = pd.read_csv(z.open('Airport_Codes.csv'))\n", "flight_data = pd.read_csv(z.open('Flights.csv'), low_memory=False)\n", "ticket_data = pd.read_csv(z.open('Tickets.csv'))" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TYPENAMEELEVATION_FTCONTINENTISO_COUNTRYMUNICIPALITYIATA_CODECOORDINATES
26565small_airportBlosser Municipal Airport1486.0NaNUSConcordiaCNK-97.6522979736, 39.549301147499996
2389heliportClayton Heliport261.0NaNUSBlytheNaN-114.6449966430664, 33.60390090942383
47939small_airportReb Folbre's Place Airport940.0NaNUSMillsapNaN-97.9728012084961, 32.76259994506836
49315heliportJuniper Hills Airport4120.0NaNUSBonanzaNaN-121.4580002, 42.1242981
31043large_airportSantiago de Compostela Airport1213.0EUESSantiago de CompostelaSCQ-8.415140151977539, 42.89630126953125
\n", "
" ], "text/plain": [ " TYPE NAME ELEVATION_FT CONTINENT \\\n", "26565 small_airport Blosser Municipal Airport 1486.0 NaN \n", "2389 heliport Clayton Heliport 261.0 NaN \n", "47939 small_airport Reb Folbre's Place Airport 940.0 NaN \n", "49315 heliport Juniper Hills Airport 4120.0 NaN \n", "31043 large_airport Santiago de Compostela Airport 1213.0 EU \n", "\n", " ISO_COUNTRY MUNICIPALITY IATA_CODE \\\n", "26565 US Concordia CNK \n", "2389 US Blythe NaN \n", "47939 US Millsap NaN \n", "49315 US Bonanza NaN \n", "31043 ES Santiago de Compostela SCQ \n", "\n", " COORDINATES \n", "26565 -97.6522979736, 39.549301147499996 \n", "2389 -114.6449966430664, 33.60390090942383 \n", "47939 -97.9728012084961, 32.76259994506836 \n", "49315 -121.4580002, 42.1242981 \n", "31043 -8.415140151977539, 42.89630126953125 " ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "airport_data.sample(5)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FL_DATEOP_CARRIERTAIL_NUMOP_CARRIER_FL_NUMORIGIN_AIRPORT_IDORIGINORIGIN_CITY_NAMEDEST_AIRPORT_IDDESTINATIONDEST_CITY_NAMEDEP_DELAYARR_DELAYCANCELLEDAIR_TIMEDISTANCEOCCUPANCY_RATE
19045462019-01-24UAN7585147811292DENDenver, CO13930ORDChicago, IL0.06.00.0114.0888.00.94
14032302019-01-29WNN7817J209613232MDWChicago, IL11292DENDenver, CONaNNaN1.0NaN895.00.84
17888622019-01-11WNN901WN39811278DCAWashington, DC10397ATLAtlanta, GA-6.0-23.00.087.0547.00.39
3197542019-03-12ASN260AK2713930ORDChicago, IL14747SEASeattle, WA-8.0-1.00.0243.01721.00.41
14025492019-01-09WNN900WN36911884GEGSpokane, WA13796OAKOakland, CA-1.014.00.0110.0723.00.62
\n", "
" ], "text/plain": [ " FL_DATE OP_CARRIER TAIL_NUM OP_CARRIER_FL_NUM ORIGIN_AIRPORT_ID \\\n", "1904546 2019-01-24 UA N75851 478 11292 \n", "1403230 2019-01-29 WN N7817J 2096 13232 \n", "1788862 2019-01-11 WN N901WN 398 11278 \n", "319754 2019-03-12 AS N260AK 27 13930 \n", "1402549 2019-01-09 WN N900WN 369 11884 \n", "\n", " ORIGIN ORIGIN_CITY_NAME DEST_AIRPORT_ID DESTINATION DEST_CITY_NAME \\\n", "1904546 DEN Denver, CO 13930 ORD Chicago, IL \n", "1403230 MDW Chicago, IL 11292 DEN Denver, CO \n", "1788862 DCA Washington, DC 10397 ATL Atlanta, GA \n", "319754 ORD Chicago, IL 14747 SEA Seattle, WA \n", "1402549 GEG Spokane, WA 13796 OAK Oakland, CA \n", "\n", " DEP_DELAY ARR_DELAY CANCELLED AIR_TIME DISTANCE OCCUPANCY_RATE \n", "1904546 0.0 6.0 0.0 114.0 888.0 0.94 \n", "1403230 NaN NaN 1.0 NaN 895.0 0.84 \n", "1788862 -6.0 -23.0 0.0 87.0 547.0 0.39 \n", "319754 -8.0 -1.0 0.0 243.0 1721.0 0.41 \n", "1402549 -1.0 14.0 0.0 110.0 723.0 0.62 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "flight_data.sample(5)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ITIN_IDYEARQUARTERORIGINORIGIN_COUNTRYORIGIN_STATE_ABRORIGIN_STATE_NMROUNDTRIPREPORTING_CARRIERPASSENGERSITIN_FAREDESTINATION
88700020191262427820191SBNUSINIndiana1.0G41.0612.0SFB
79852220191407977620191PIAUSILIllinois1.0UA1.0528.0ERI
7751262019184113520191PHLUSPAPennsylvania0.0AA1.0225.0ORD
110816920191127463420191SJCUSCACalifornia1.0AS3.0201.0SEA
70571220191475704920191ONTUSCACalifornia1.0WN69.0491.0OAK
\n", "
" ], "text/plain": [ " ITIN_ID YEAR QUARTER ORIGIN ORIGIN_COUNTRY ORIGIN_STATE_ABR \\\n", "887000 201912624278 2019 1 SBN US IN \n", "798522 201914079776 2019 1 PIA US IL \n", "775126 20191841135 2019 1 PHL US PA \n", "1108169 201911274634 2019 1 SJC US CA \n", "705712 201914757049 2019 1 ONT US CA \n", "\n", " ORIGIN_STATE_NM ROUNDTRIP REPORTING_CARRIER PASSENGERS ITIN_FARE \\\n", "887000 Indiana 1.0 G4 1.0 612.0 \n", "798522 Illinois 1.0 UA 1.0 528.0 \n", "775126 Pennsylvania 0.0 AA 1.0 225.0 \n", "1108169 California 1.0 AS 3.0 201.0 \n", "705712 California 1.0 WN 69.0 491.0 \n", "\n", " DESTINATION \n", "887000 SFB \n", "798522 ERI \n", "775126 ORD \n", "1108169 SEA \n", "705712 OAK " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ticket_data.sample(5)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Airport dataset has 55369 records and 8 columns\n", "Flight dataset has 1915886 records and 16 columns\n", "Ticket dataset has 1167285 records and 12 columns\n" ] } ], "source": [ "print(f'Airport dataset has {airport_data.shape[0]} records and {airport_data.shape[1]} columns\\n'\n", " f'Flight dataset has {flight_data.shape[0]} records and {flight_data.shape[1]} columns\\n'\n", " f'Ticket dataset has {ticket_data.shape[0]} records and {ticket_data.shape[1]} columns')" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Airport dataset has the following null values:\n", "TYPE 0\n", "NAME 0\n", "ELEVATION_FT 7015\n", "CONTINENT 27843\n", "ISO_COUNTRY 247\n", "MUNICIPALITY 5706\n", "IATA_CODE 46187\n", "COORDINATES 0\n", "dtype: int64\n", "\n", "Flight dataset has the following null values:\n", "FL_DATE 0\n", "OP_CARRIER 0\n", "TAIL_NUM 12156\n", "OP_CARRIER_FL_NUM 0\n", "ORIGIN_AIRPORT_ID 0\n", "ORIGIN 0\n", "ORIGIN_CITY_NAME 0\n", "DEST_AIRPORT_ID 0\n", "DESTINATION 0\n", "DEST_CITY_NAME 0\n", "DEP_DELAY 50351\n", "ARR_DELAY 55991\n", "CANCELLED 0\n", "AIR_TIME 56551\n", "DISTANCE 630\n", "OCCUPANCY_RATE 310\n", "dtype: int64\n", "\n", "Ticket dataset has the following null values:\n", "ITIN_ID 0\n", "YEAR 0\n", "QUARTER 0\n", "ORIGIN 0\n", "ORIGIN_COUNTRY 0\n", "ORIGIN_STATE_ABR 0\n", "ORIGIN_STATE_NM 0\n", "ROUNDTRIP 0\n", "REPORTING_CARRIER 0\n", "PASSENGERS 1977\n", "ITIN_FARE 960\n", "DESTINATION 0\n", "dtype: int64\n" ] } ], "source": [ "print(f'Airport dataset has the following null values:\\n{airport_data.isnull().sum()}')\n", "print()\n", "print(f'Flight dataset has the following null values:\\n{flight_data.isnull().sum()}')\n", "print()\n", "print(f'Ticket dataset has the following null values:\\n{ticket_data.isnull().sum()}')" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Data types of airport dataset:\n", "TYPE object\n", "NAME object\n", "ELEVATION_FT float64\n", "CONTINENT object\n", "ISO_COUNTRY object\n", "MUNICIPALITY object\n", "IATA_CODE object\n", "COORDINATES object\n", "dtype: object\n", "\n", "Data types of flight dataset:\n", "FL_DATE object\n", "OP_CARRIER object\n", "TAIL_NUM object\n", "OP_CARRIER_FL_NUM object\n", "ORIGIN_AIRPORT_ID int64\n", "ORIGIN object\n", "ORIGIN_CITY_NAME object\n", "DEST_AIRPORT_ID int64\n", "DESTINATION object\n", "DEST_CITY_NAME object\n", "DEP_DELAY float64\n", "ARR_DELAY float64\n", "CANCELLED float64\n", "AIR_TIME object\n", "DISTANCE object\n", "OCCUPANCY_RATE float64\n", "dtype: object\n", "\n", "Data types of ticket dataset:\n", "ITIN_ID int64\n", "YEAR int64\n", "QUARTER int64\n", "ORIGIN object\n", "ORIGIN_COUNTRY object\n", "ORIGIN_STATE_ABR object\n", "ORIGIN_STATE_NM object\n", "ROUNDTRIP float64\n", "REPORTING_CARRIER object\n", "PASSENGERS float64\n", "ITIN_FARE object\n", "DESTINATION object\n", "dtype: object\n" ] } ], "source": [ "print(f'Data types of airport dataset:\\n{airport_data.dtypes}')\n", "print()\n", "print(f'Data types of flight dataset:\\n{flight_data.dtypes}')\n", "print()\n", "print(f'Data types of ticket dataset:\\n{ticket_data.dtypes}')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Q1\n", "The 10 busiest round trip routes in terms of number of round trip flights in the quarter.\n", "Exclude canceled flights when performing the calculation." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FL_DATEOP_CARRIERTAIL_NUMOP_CARRIER_FL_NUMORIGIN_AIRPORT_IDORIGINORIGIN_CITY_NAMEDEST_AIRPORT_IDDESTINATIONDEST_CITY_NAMEDEP_DELAYARR_DELAYCANCELLEDAIR_TIMEDISTANCEOCCUPANCY_RATEORIGIN_TYPEDEST_TYPE
12427172019-01-01F9N701FR178112391ISPIslip, NY13204MCOOrlando, FL-1.0-15.00.0140.0971.00.83medium_airportlarge_airport
9895912019-03-21WNNaN34610423AUSAustin, TX14107PHXPhoenix, AZNaNNaN1.0NaN872.00.74closedlarge_airport
11483282019-01-28ASN280AK77311618EWRNewark, NJ14679SANSan Diego, CA-9.0-39.00.0329.02425.00.77large_airportlarge_airport
18321732019-02-18DLN964AT234910397ATLAtlanta, GA12217HSVHuntsville, AL-3.0-19.00.032.0151.00.96large_airportlarge_airport
18231652019-01-259EN8969A349011433DTWDetroit, MI15412TYSKnoxville, TN-5.0-22.00.067.0443.00.33large_airportlarge_airport
\n", "
" ], "text/plain": [ " FL_DATE OP_CARRIER TAIL_NUM OP_CARRIER_FL_NUM ORIGIN_AIRPORT_ID \\\n", "1242717 2019-01-01 F9 N701FR 1781 12391 \n", "989591 2019-03-21 WN NaN 346 10423 \n", "1148328 2019-01-28 AS N280AK 773 11618 \n", "1832173 2019-02-18 DL N964AT 2349 10397 \n", "1823165 2019-01-25 9E N8969A 3490 11433 \n", "\n", " ORIGIN ORIGIN_CITY_NAME DEST_AIRPORT_ID DESTINATION DEST_CITY_NAME \\\n", "1242717 ISP Islip, NY 13204 MCO Orlando, FL \n", "989591 AUS Austin, TX 14107 PHX Phoenix, AZ \n", "1148328 EWR Newark, NJ 14679 SAN San Diego, CA \n", "1832173 ATL Atlanta, GA 12217 HSV Huntsville, AL \n", "1823165 DTW Detroit, MI 15412 TYS Knoxville, TN \n", "\n", " DEP_DELAY ARR_DELAY CANCELLED AIR_TIME DISTANCE OCCUPANCY_RATE \\\n", "1242717 -1.0 -15.0 0.0 140.0 971.0 0.83 \n", "989591 NaN NaN 1.0 NaN 872.0 0.74 \n", "1148328 -9.0 -39.0 0.0 329.0 2425.0 0.77 \n", "1832173 -3.0 -19.0 0.0 32.0 151.0 0.96 \n", "1823165 -5.0 -22.0 0.0 67.0 443.0 0.33 \n", "\n", " ORIGIN_TYPE DEST_TYPE \n", "1242717 medium_airport large_airport \n", "989591 closed large_airport \n", "1148328 large_airport large_airport \n", "1832173 large_airport large_airport \n", "1823165 large_airport large_airport " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# merge airport data with flight data\n", "df1 = pd.DataFrame()\n", "df1['ORIGIN'] = airport_data['IATA_CODE']\n", "df1['ORIGIN_TYPE'] = airport_data['TYPE']\n", "temp = pd.merge(flight_data, df1, on = 'ORIGIN')\n", "df2 = pd.DataFrame()\n", "df2['DESTINATION'] = airport_data['IATA_CODE']\n", "df2['DEST_TYPE'] = airport_data['TYPE']\n", "flight= pd.merge(temp, df2, on = 'DESTINATION')\n", "flight.sample(5)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FL_DATEOP_CARRIERTAIL_NUMOP_CARRIER_FL_NUMORIGIN_AIRPORT_IDORIGINORIGIN_CITY_NAMEDEST_AIRPORT_IDDESTINATIONDEST_CITY_NAMEDEP_DELAYARR_DELAYCANCELLEDAIR_TIMEDISTANCEOCCUPANCY_RATEORIGIN_TYPEDEST_TYPEROUND_TRIP_ROUTE
4482202019-03-31YXN723YX359914683SATSan Antonio, TX13930ORDChicago, IL-5.0-22.00.0133.01041.00.73large_airportlarge_airport(ORD, SAT)
14226052019-02-07WNN969WN84914057PDXPortland, OR14831SJCSan Jose, CA46.036.00.082.0569.00.40large_airportlarge_airport(PDX, SJC)
17890292019-01-25OHN206PS543314100PHLPhiladelphia, PA11995GSOGreensboro/High Point, NC0.0-7.00.067.0365.00.48large_airportlarge_airport(GSO, PHL)
732452019-02-04YXN862RW363213930ORDChicago, IL13198MCIKansas City, MO-7.02.00.078.0403.00.65large_airportlarge_airport(MCI, ORD)
11877522019-01-20WNN7844A391713495MSYNew Orleans, LA10423AUSAustin, TX-4.0-13.00.070.0444.00.94large_airportclosed(AUS, MSY)
\n", "
" ], "text/plain": [ " FL_DATE OP_CARRIER TAIL_NUM OP_CARRIER_FL_NUM ORIGIN_AIRPORT_ID \\\n", "448220 2019-03-31 YX N723YX 3599 14683 \n", "1422605 2019-02-07 WN N969WN 849 14057 \n", "1789029 2019-01-25 OH N206PS 5433 14100 \n", "73245 2019-02-04 YX N862RW 3632 13930 \n", "1187752 2019-01-20 WN N7844A 3917 13495 \n", "\n", " ORIGIN ORIGIN_CITY_NAME DEST_AIRPORT_ID DESTINATION \\\n", "448220 SAT San Antonio, TX 13930 ORD \n", "1422605 PDX Portland, OR 14831 SJC \n", "1789029 PHL Philadelphia, PA 11995 GSO \n", "73245 ORD Chicago, IL 13198 MCI \n", "1187752 MSY New Orleans, LA 10423 AUS \n", "\n", " DEST_CITY_NAME DEP_DELAY ARR_DELAY CANCELLED AIR_TIME \\\n", "448220 Chicago, IL -5.0 -22.0 0.0 133.0 \n", "1422605 San Jose, CA 46.0 36.0 0.0 82.0 \n", "1789029 Greensboro/High Point, NC 0.0 -7.0 0.0 67.0 \n", "73245 Kansas City, MO -7.0 2.0 0.0 78.0 \n", "1187752 Austin, TX -4.0 -13.0 0.0 70.0 \n", "\n", " DISTANCE OCCUPANCY_RATE ORIGIN_TYPE DEST_TYPE \\\n", "448220 1041.0 0.73 large_airport large_airport \n", "1422605 569.0 0.40 large_airport large_airport \n", "1789029 365.0 0.48 large_airport large_airport \n", "73245 403.0 0.65 large_airport large_airport \n", "1187752 444.0 0.94 large_airport closed \n", "\n", " ROUND_TRIP_ROUTE \n", "448220 (ORD, SAT) \n", "1422605 (PDX, SJC) \n", "1789029 (GSO, PHL) \n", "73245 (MCI, ORD) \n", "1187752 (AUS, MSY) " ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# add a new feature\n", "flight['ROUND_TRIP_ROUTE'] = [frozenset([x, y]) for x, y in zip(flight['ORIGIN'], flight['DESTINATION'])]\n", "\n", "# exclude cancelled flights\n", "flight_filtered = flight[flight['CANCELLED'] == 0]\n", "flight_filtered.sample(5)" ] }, { "cell_type": "code", "execution_count": 31, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Text(0.5, 0, 'Total flights')" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" }, { "data": { "image/png": "iVBORw0KGgoAAAANSUhEUgAAAasAAAEWCAYAAADYRbjGAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMSwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy/YYfK9AAAACXBIWXMAAAsTAAALEwEAmpwYAAAxX0lEQVR4nO3deZwdVZ3+8c9DQ0hCElRAMGwNClFZTLBVFFwQBREGZVwgZnBhHETHn6MRHRhcYHQkbogogzIjIoowzBABZRRkEQZcoEMCCcgiJmAQBYUEhLCF5/dHnQuVSy+3t9tX8rxfr3rl1qmqc763A/3NOXWqjmwTERHRydYZ7wAiIiIGk2QVEREdL8kqIiI6XpJVRER0vCSriIjoeElWERHR8ZKsItYykuZIunC844gYiiSrWGtI+ktte1zSqtr+nFFq4+2Sfi7pQUk/6+P4TEkLyvEFkmYOUNepkh4p8d1fzn/1SGO0fbrtvUZSh6TXSFo+yDn1+O+R9FNJzx9Ju7W6Lel5o1FX/HVIsoq1hu0pjQ24HfibWtnpo9TMPcDxwLzmA5ImAOcC3wOeCXwHOLeU9+cLJd4NgZOA+ZK6RinWdmjEvzlwB/CtcY4n/kolWcVaT9L6ko6X9PuyHS9p/XLsNZKWS/oXSX+StGygXpjti2yfBfy+j8OvAdYFjrf9sO0TAAGvHSxG248D3weeBWxaYjta0vdq36O79DjWLfvvlvTb0itb2oi7lF9Ru+75pddzj6SbJL29duyNkm4oddwh6XBJGwA/BqbXeqbTB4l/FXAWMLNW9wsk/UzSCknXS9q/duxnkt5b238iZkmXl+JrS9sHlvL9JC0q9f1c0s616/+5xH9/+Y57DvYzj86SZBUBRwG7Uv0ifRHwUuATteObARtT9Q7eBZwsacYw2tkBuM5rvuPsulI+oNKbeiewFPhjC+dvAJwA7GN7KvAKYFE/5/2UKhE+G5gN/LukRkzfAt5X6tgRuMT2A8A+wO9rPdO+knNzO7OB35T99YAfAheWdv8fcHorP1fbryofX1Ta/i9JuwCnAO8DNgK+CZxX/iEyA/gg8JLyPfYGlg3WTnSWJKsImAP8q+27bN8NHAMc3HTOJ0tv6DLgfODtzZW0YAqwsqlsJTB1gGsOl7QCeIBqePGTtle32N7jwI6SJtm+0/b1fZyzH7DM9rdtP2b7GuBs4K3l+KPACyVNs31vOT4UjfjvB3bnyZ/rrlQ/j3m2H7F9CfAjqoQ2HP8AfNP2r2yvtv0d4OHSzmpg/fI91rO9zPatw2wnxkmSVQRMB26r7d9WyhruLb2J/o636i/AtKayaVS/yPvzJdvPACYBPcAXJe0zWEMl3gOBw4A7JZ3fz+SGrYGXlaGzFSWxzKHqTQK8BXgjcJukyyS9fLC2+4m/G1gFNHpO04HfleHNhtuoeq/DsTXw0abvsSUw3fZvgA8DRwN3STpzsGHL6DxJVhHV/aWta/tbseY9p2eWYaz+jrfqemBnSaqV7VzKB+TKEuBKYN9S/AAwuXbaZk3XXGD79cBzgBuB/+ij6t8Bl9l+Rm2bYvv9pY6rbb+JaqjuHKr7TgBDWq7B9u3APwFflTSJ6ue3paT676CtqCZhDPrd+vke/9b0PSbbPqO0/33bu1P9PRv4/FDij/GXZBUBZwCfkLSJpI2BT1HN2Ks7RtIESa+kGjr7774qktQlaSLVRIp1JE0s92cAfkY1JPWhci/lg6X8klaCLD2j3XkyuS0CXiVpK0kbAkfWzt1U0v4lyT5M1avra/jwR8D2kg6WtF7ZXlImP0xQ9UzWhrYfBe6r1fFHYKPSbkts/5QqSR0K/IoqIX28tPka4G+AM2vf7W8lTVY1Rf3vm6r7I7Btbf8/gMMkvUyVDSTtK2mqpBmSXqtq0sxDVD28VodSo1PYzpZtrduobrC/rnyeSDUZ4c6ynQBMLMdeAyynmoTxJ6op7wcPUO+7qf7lXt9OrR2fBSyg+oV5DTBrgLpOBR6hSjQPlLY/B6xTO+dEYAXVxIV/KO2tS9WbuozqntgKqkT5wlqMV9TqmEF1H+5u4M9UyXMmMAH4CXAvVaK6Gti9dt0p5fwVVMNtfcX/2aayA6l6T+tTTSxpxHgDcEDtvI2pJl/cT9WbPLop5sPK39UK4O2l7A0lxhXl2H9T3Q/cGbiq1HUPVYJ+SrzZOntT+UuOiD6Uf/F/z/YW4xxKxFotw4AREdHxkqwiIqLjZRgwIiI6XnpWERHR8dYd7wCerjbeeGN3d3ePdxgREX9VFixY8CfbmzSXJ1mNke7ubnp7e8c7jIiIvyqSbuurPMOAERHR8ZKsIiKi4yVZRUREx0uyioiIjpcJFmNk8R0r6T7i/PEOI2JYls3bd/CTItooPauIiOh4SVYREdHx2p6sJE0qK452SeqWtKSf8+ZKulHSYknXSjquti4QkmZJsqS9h9D2JpIelfS+sn+ipEWSbpC0qnxeJOmtkk6V9NY+rv/JcL97REQMz3j0rA4B5tvud/EzSYcBewG72t4JeAlwF9XS3g2zgSvKn616G/DLxjW2/9H2TKplu2+1PbNs/9PXxbbvploifLchtBkRESM0HslqDnDuIOccBbzf9goA24/Ynmf7PoCyLPhbqRaR26uszNqK2cBHgS0kbT6M2KFa2ntOXwckHSqpV1Lv6gdXDrP6iIho1tZkJWkCsK3tZQOcMxWYYnvpAFXtBiy1fSvVCqhvbKHtLYHNbF8FnEW1Yulw9AKv7OuA7ZNt99ju6Zrc8mrfERExiHb3rDamWnJ6IKJamrvakfYu95GWSXpFKZ4NnFk+n0lrQ4EHUSWpoVzTl7uA6cO8NiIihqHdz1mtAgYcsrN9n6QHJG1je6ntC4ALJP0ImCCpC3gLsL+ko6iS20aSptq+f4CqZwObSmoM4U2XtJ3tW4b4HSaW7xEREW3S1p6V7XuBrhbuMR0LnCTpGfDEParGNa8DrrW9pe1u21sDZwNvLude3Hw/StIMYAPbm5druksbBw3ja2wP9DmDMSIixsZ4TLC4ENi9tj9D0vLa9jbgJOAi4FeSrgOuBBaWbTbwg6Y6zwbeIWkd4HnAPU3H+7tmsKHAb9bi+kUp2wPIqykiItqo7cvaS5oFzLV98BjUvSNwiO25o113rY3LgTeVXmK/enp6nPWsIiKGRtIC2z3N5W3vWdleCFxa7j2Ndt1LxjhRbQIcN1iiioiI0TUuL7K1fcp4tDtS5aHgc8Y7joiItU3eDRgRER0vySoiIjpeklVERHS8JKuIiOh4SVYREdHxkqwiIqLjJVlFRETHS7KKiIiONy4PBa8NFt+xku4j8grBeHpYNm/f8Q4h1nLpWUVERMdrS7KSNEnSZfX3AUr6iKSHJG0oaaOywOIiSX+QdEdtf4KkvwyhrddIsqS/r5XNKmWH18oOl3SjpCWSrpX0zlI+QdLxkm6VdIukcyVtUTt2uaT0SCMi2qhdPatDgPm2V9fKZgNXAwfY/rPtmbZnAt8AvtLYt/3IMNpbzJrL1h8EXNvYkXQY8HrgpbZ3BF5FtYgjwOeAqcD2trejehfgfEkqsVzcVHdERIyxdiWrOcC5jR1JzwWmAJ9g+MvLD+R2YKKkTcvCjW8Aflw7/i/AB2zfB2B7pe3vSJoMvAf4SCOx2v428DDw2nLtOeX7REREm4x5spI0AdjW9rJa8WzgDOD/qBZffPYYNP0/wNuAVwDXUCUcJE0Fptq+tY9rngfc3khiNb3ADuXzEuAlfTUo6VBJvZJ6Vz+4chS+QkREQHt6VhsDK5rKDgLOtP04MJ8qqYy2s0q9jcTYIKC/FSf7O/ZEeelxPVKS3hpsn2y7x3ZP1+QNRxJ7RETUtCNZrQImNnYk7QxsB/xU0jKqxDXqQ4G2/wA8SnVv6uJa+X3AA5K27eOy3wBb95GIdgFuqO2vDzw0uhFHRER/xjxZlVV1uyQ1EtZs4Gjb3WWbDmwuaeuh1CvpAEnHDnLap4B/bprYAXAscKKkaaWuaZIOtf0A8B3guMbMxTJLcDJwSdnfCLjb9qNDiTciIoavXVOwLwR2By6i6knt03T8B6X88/1cP1nS8tr+cVSJtvne0hps/7yfQydRTfC4WtKjVD2wL5djRwJfAm6W9DhwI9WMxcbw4B7A/w7UbkREjC49+Tt4DBuRZgFzbR88inV+j2rW3t2jVWeL7c4HjrR900Dn9fT0uLe3t01RRUQ8PUhaYLunubwtPSvbCyVdKqmrjyG54db5d6NRz1CUmY3nDJaoIiJidLXtTQy2T2lXW2OlPBR82njHERGxtsm7ASMiouMlWUVERMdLsoqIiI6XZBURER0vySoiIjpeklVERHS8JKuIiOh4SVYREdHxsjz7GFl8x0q6jzh/vMOIGFXL5u073iHEWio9q4iI6HhJVhER0fHalqwkTZJ0maQuSd2SVklaJOkGSd+QtE6tfKGkX0u6StK7anXMlfSt2v4cSQOOtUmaLOl0SYslLZF0haQp5djqEkNjO6J23SaSHpX0vqb6LpL0zNH7yURExGDaec/qEGC+7dWSAG61PVPSulQLG74ZuKaUzwIoq/nOl7SO7W8DJwC9knYDrgc+C+w5SLv/BPzR9k6lzhlU61cBrLI9s5/r3gb8kmqxyG/Wyr8LfAD4t1a/eEREjEw7hwHnAOc2F9p+DPg58Lw+jv0WmAt8qHbuB4ATgS8Ap5RzBvIc4I5anTfZfriFeGcDHwW2kLR5rfy8ciwiItqkLcmqrAO1re1lfRybTNU7WtzP5dcAz2/slNV/fw28jiphDeYU4J8l/ULSZyVtVzs2qWkY8MAS05bAZravAs4CDqy1fy+wflnevvm7HCqpV1Lv6gdXthBaRES0ol09q42BFU1lz5W0CLgSON/2j/u5VmvsVPebeoD1gE0Ga9j2ImBb4IvAs6iWsn9BObzK9sza9l+l/CCqJAVwJk/tSd0FTO+jrZNt99ju6Zq84WChRUREi9p1z2oVMLGp7NYB7hfVzaLqSTUcA3wP+CPwFap7SwOy/RdgPtX9r8eBNzbV2Ww2sKmkOWV/uqTtbN9S9idSfaeIiGiDtvSsytBZl6TmhDUgSd3Al4Cvlf2dgH2BzwMnA1tLen05doCkY/uoY7fG7L0yHPlC4LYB2pwBbGB7c9vdtruBY6l6W6iaHbIZsGwo3yUiIoavnbMBLwR2By4a5LznSlpI1Xu5H/ia7W+XJHES8BHbDwFI+gBwmqSZwHOB+/qqDzipXL8OcD5wdjk2qQxFNvwEeAj4QVMdZ1MNB34GeDHwyzLZIyIi2kC229OQNAuYa/vgMar/e1SJ7O6xqL/WzleB82xfPNB5PT097u3tHctQIiKediQtsN3TXN62npXthZIuldRle/UY1P93o11nP5YMlqgiImJ0tfVFtrZPaWd7Y8H2f4x3DBERa5u8GzAiIjpeklVERHS8JKuIiOh4SVYREdHxBk1WkjaQtE75vL2k/SWtN/ahRUREVFrpWV0OTCxvHr8YeA9w6lgGFRERUddKspLtB4G/pXqbxAFUryyKiIhoi5aSlaSXU61H1ViVt63PZ0VExNqtlaTzYeBI4Ae2ry+r9146plE9DSy+YyXdR5w/+IkRf2WWzdt3vEOItdCgycr2ZcBlkjYo+7+lrNwbERHRDq3MBny5pBso6z9JepGkfx/zyCIiIopW7lkdD+wN/BnA9rXAqwa7SNIkSZdJ6ir7O0i6RNLNkm6R9MmybEfj/DdIukrSjWWJ+f+StFXt+NxybLGkayUdN9gUekmnSnprU9lfyp/dkizp/9WOfV3Suwe4dhNJPxnsu0dExOhq6aFg279rKmrlremHAPNtr5Y0CTgPmGd7e+BFwCuADwBI2pFqgcV32X5+WUH4dKC7HD8M2AvY1fZOwEuolpaf1Er8A7gL+KeyKOOgyvIjd0rabYTtRkTEELSSrH4n6RWAJU2QdDgDLwnfMAc4t3x+B3Cl7QsBylT4DwJHlOP/DHzO9hP12j7P9uVl9yjg/bZXlGOP2J5nu6/FFofibqpnx941hGvOofpuTyHpUEm9knpXP7hyhKFFRERDK8nqMOAfgc2B5cBMSo+oP6Wnsq3tZaVoB2BB/RzbtwJTJE0rx6/pp66pwBTbS1uItS9fLMOKi5pWBW6YB3y0MVzZgl7glX0dsH2y7R7bPV2TNxxmuBER0ayVZDXD9hzbm9p+dlnk8AWDXLMxsKK2L6C/JYnXKJe0UUksN5de3BrXStq7HF9WenyD+ZjtmY3tKY1XSfAqqt5fK+4Cprd4bkREjIJWktXXWiyrWwVMrO1fD6yxTHF5Xusvtu8vx3cBsP3nklROpupR3Qc8IGmbcvyCcnwJ0NK9phZ8jmoospWfx0Sq7xcREW3S7y/nMmX9o8AmZSZeYzsaGHDIzPa9QJekRsI6Hdhd0utK3ZOAE4AvlONfAI6SVO+xTa59PhY4SdIzyvWilgwlnSbppYN+2/7jvRG4AdivhdO3p0qUERHRJgP1JCYAU6geHJ5a2+4D3jrAdQ0XArsD2F4FvAn4hKSbgMXA1cDXy/HFwD8Bp5Xp6VdSDTV+v9R1EnAR8CtJ1wFXAgvLBrAzcGcLMQ3k34Atmsq+KWl52X5RyvbgyddORUREG8ju71ZSOUHa2vZtQ65YmgXMtX3wcINrsZ1pwLdsv20s26m1dznwptJ77FdPT497e3vbEVJExNOGpAW2e5rLW3k34KmSnpLRbL92oItsL5R0qaQu2608lzUs5Z5WuxLVJsBxgyWqiIgYXa0kq8NrnycCbwEea6Vy26cMJ6hOVR4KPme844iIWNu08iLbBU1FV0q6bIziiYiIeIpBk5WkZ9V21wFeDGw2ZhFFREQ0aWUYcAHVQ7miGv5bCvz9WAYVERFR18ow4DbtCCQiIqI/rQwDrge8nyeXBfkZ8E3bj45hXBEREU9oZRjwJGA9oLHg4sGl7L1jFVRERERdK8nqJbZfVNu/RNK1YxVQREREs1Ze3Lpa0nMbO+UFtGP2kG9ERESzVh8KvlTSb6lmBG4NvGdMo3oaWHzHSrqPyCsE4+lp2bx9xzuEWMsMmKzKgoQvArYDZlAlqxttP9yG2CIiIoBBhgHLO/32t/2w7etsX5tEFRER7dbKPaufS/q6pFdK2qWxDaURSZMkXSapS1K3pD7XgyrrZd0oabGkayUdV6bON47PkmRJe7fY7s8k3VRWFv61pENrx7aQdK6kWyTdKumrkiaUY5MlnV7iWCLpCklTJE2QdLmkVoZPIyJilLTyS7exdPy/1soMDPjW9SaHAPNtr67WTXwqSYcBewG72l5REsdcYBLQeKZrNnBF+fOCFtueY7u3vDbqVkmnlvrmAyfZflMZ7jyZak2rj1GtrfVH2zuV2GYAj9p+RNLFwIFUC0pGREQbtPIGiz1GoZ05wDsGOeco4FW2V5R2HwHmNQ6W1YHfCrwe+D9JE20/NIQYpgAPUM1kfC3wkO1vl7ZWS/oIsFTSp4HnAE+s4WX7plo951CtXPyUZFV6bocCdE3bZAihRUTEQFoZBhyR0kPa1vayAc6ZCkyxvXSAqnYDltq+leotGm9sMYTTy+rCNwGfKffhdqB65+ETyrpYtwPPA04B/lnSLyR9VtJ2tVOXAC/pqyHbJ9vusd3TNXnDFsOLiIjBjHmyAjYGVgxyjqiGFqsdae9yn2mZpMYw5GzgzPL5zLLfijm2dwa2Ag6XtHVze81x2F4EbAt8EXgWcLWkF8ATk04eKQk2IiLaoB0TBVZRLdrYL9v3SXpA0ja2l9q+ALhA0o+ACeWe0luA/SUdRZVUNpI01fb9rQRh+25J1wAvA64v9T1B0jRgS+DWcv5fqO5rzZf0OFVP7tfl9PWBoQxBRkTECAzas5I0sczSmy/pbEkfkTRg8qkrS8B3tXDNscBJkp5R2hVPJrnXAdfa3tJ2t+2tgbOBN5dzL5a0+SDfYzIwiyoZXQxMlvTOcqwL+DJwqu0HJe0m6Znl2ATghZR7WJI2Au7Oi3wjItqnlWHA06ju8XwN+DrwAuC7Q2znQmD32v4MSctr29uoXo57EfCrco/pSmBh2WYDP2iq82zgHZLWobrPdE8/bZ8uaRHVPapTbS+wbeAA4G2SbgFupuop/Uu55rnAZZIWl/Z7S3sAewD/O8TvHxERI6Dq9/YAJ0jXNr3Its+yQeqYBcy1ffDwwhyw7h2BQ2zPHe26+2lvPnBk0wzBp+jp6XFvb287QoqIeNqQtMB2T3N5Kz2rhZJ2rVX0MqpeT8tsL6R6v2DXUK5rse4lbUxUE4BzBktUERExulqZYPEy4J2Sbi/7WwG/LkNkLjPtBmX7lGHG2DHKs1+njXccERFrm1aS1RvGPIqIiIgB9JusJE0rD8r2OTXcdn8TGiIiIkbVQD2r7wP7Uc2iM9WzTQ2memg2IiJizPWbrGzvV551erXt2/s7LyIiYqwNtp6VeerzTREREW3VytT1X0rq88WtERER7dDKbMA9gPdJuo1qiY3Gy15bmrIeERExUq0kq33GPIqIiIgBtJKsPtv8miRJ3wVG/dVJTyeL71hJ9xHnj3cYEW23bN6+4x1CPA21cs9qh/pOeWXSi8cmnIiIiKfqN1lJOlLS/cDOku4r2/3AXcC5w2lM0iRJl0nqkrSOpBMkLZG0WNLVkrYp5y0rZYvKdkKtjnUl/UnSsS22ebSkw/s5tomkRyW9r1Y2VdKtjdWBJa1XYnmZpAmSLpfUjnXAIiKi6DdZ2T7W9lTgi7anlW2q7Y1sHznM9g4B5pfVdg8EpgM7296JasmOFbVz97A9s2wfqpXvRbVE/dvLc2Aj8Tbgl9RWHS6LOR4JnFiKDgd+bvtX5d2AF5fYIyKiTQYdBqwnJklHj7C9OTzZK3sOcKftx0s7y8tCjYOZDXwVuB3YdZBzW6nro8AW9cUbbZ8FPC7p48BhVMmr4ZzyPSIiok1auWdVt/9wGyrLa2xre1kpOgv4mzLM9+Wy5lXdpbVhwI+UOiYBewI/As6g1iMaRjxbApvZvqrE0txb+jDweaoJJvX3IC4B+nzuTNKhknol9a5+cOVwQ4uIiCZDTVYjGXbbmNown+3lwAyqXsvjwMWS9qydXx8G/Eop2w+41PaDVCv3HjCCNbIOokpSAGfy1MT3BuBOYMd6YRnCfETS1OYKbZ9su8d2T9fkDYcZVkRENBvqRIGRzAJcBUysF9h+GPgx8GNJfwTeTHVPqD+zgd0kLSv7G1E9tHzRMOKZDWwqqTGkN13SdrZvkTQd+BDwUqoe3rdsX1e7dn3goWG0GRERwzDQEiFfo3q7enM5AE2THgZl+94yC3Ci7Yck7QL8wfbvJa0D7Axc19/1kqYBuwNbliSHpPdQJZ2LyuzAq2wP+i5DSTOADWxvXis7hqq39RngK8DnbC+XNBc4UdKrbFvSRsDdth8dyvePiIjhG2gYsJdqeZCJwC7ALWWbCaweZnsXUiUcgGcDP5S0hCpJPQZ8vXZu/Z7VacDfApc0ElVxLrC/pPWBnYA/9NPuJyQtb2xUCa45qZ0NzJb0eqrVkL8FYPuHwL3AO8t5ewD/O9QvHhERw6fqxeoDnCBdCuzV6ElIWg+40PYeQ26smkQxt/mNGKNB0gW29x7tevtoZz5wpO2bBjqvp6fHvb29Yx1ORMTTiqQFtnuay1uZYDEdqE8mmFLKhsz2Qqoe03AnRQxUdzsS1QTgnMESVUREjK5WJljMAxaWHhbAq4Gjh9ug7VOGe+14Kw8FnzbecURErG0GTVa2vy3px8DLStERtvu7NxQRETHqWn3Oqgu4m2qiwfaSXjV2IUVERKxp0J6VpM9Tvd3heqqHd6Ga0n75GMYVERHxhFbuWb0ZmNE0ZTwiIqJtWhkG/C2w3lgHEhER0Z9WelYPAoskXQw80bsa6hssIiIihquVZHVe2SIiIsZFK1PXv9OOQCIiIvrTymzApfTxQlvb245JRE8Ti+9YSfcR5493GBExAsvm7TveIUTRyjBg/R1NE6mWgn/W2IQTERHxVK0sa//n2naH7eOB1459aBEREZVBk5WkXWpbj6TDWPPFtsMmaZKky8o6V91luRAkvUbSytoSIReV8qMlHV4+T5T0U0mfHqSNn0l6yht8y7EDJFnS82tlPZKWlJfWIum5kn4raZqknSSdOhrfPSIiWtfKMOCXa58fA5YBbx+l9g8B5tte3VjUseb/bO/X10UlkZwNLLB9zAjanw1cQbXo4tEAtnslXQ4cDnwOOBE4yvZ9wGJJW0jayvbtI2g3IiKGoJXZgENet2oI5gDvGOI16wJnArfYPmK4DUuaAuxGtZjieaz5Jvl/Aa6R9Biwnu0zasd+SJXcvjDctiMiYmhaGQbcUNJxknrL9mVJG4604dI72tb2sn5OeWVtGPCoWvnHgcdsf3iEIbwZ+Intm4F7JO3SOGB7BfB54FjgA03X9QKv7KtCSYc2fk6rH1w5wvAiIqKhldctnQLcTzX093bgPuDbo9D2xsCKAY7/n+2ZZfu3WvkVwMslbT/C9mdT9dAof85uOr4P8EfghU3ld9HP4pO2T7bdY7una/KI83lERBSt3LN6ru231PaPkbRoFNpeRTUVfqguB74D/FjSK23/fqgVSNqIakbjjpJMtQSKJX3ctiXtB2wI7A38QNIFth8sl08ssUdERJu00rNaJWn3xo6k3RiFX9a27wW6JA05Ydk+G/gi8BNJzyhxXSxp8xareCtwmu2tbXfb3hJYCuwuaRLVpJJ/tL0YOBeoD0NuDywZaswRETF8rfSs3g98p9ynEnAP8K5Rav9CYHfgohJLy8uQ2P6GpM2A8yS9AXheia0v50t6tHz+BbAJMK/pnLOpJnvsA5xj+4ZSfjTVi3xPtX0L1YSMvJoiIqKNZD/lTUp9nyhNKx8fBA60ffqIG5dmAXNtHyzpTcAc20OeFi9pR+AQ23NHGtMg7awPXAbsbvuxgc7t6elxb2/vWIYTEfG0I2mB7ac8G9vvMGB5CPZISV+X9HqqSRbvBH7DKD1nZXshcKmkzwD/SjX7bjj1LBnrRFVsBRwxWKKKiIjRNdAw4HeBe6mGzf6Basr4BODNtheNVgC2TykfPzladY6VMgx4y3jHERGxthkoWW1reycASf8J/AnYyvb9bYksIiKiGGg2YGNCArZXA0uTqCIiYjwM1LN6kaT7ymcBk8q+ANue1v+lERERo6ffZGW7q52BRERE9KeVh4IjIiLGVZJVRER0vCSriIjoeElWERHR8Vp5N2AMw+I7VtJ9RF4hGPF0tWzevuMdwlolPauIiOh4SVYREdHx2p6sJE2SdJmkLkndkvpdG0rSuZJ+0VR2gqRP1vaPknTiIG2+W9LX+zm2rqQ/STq2qXw/SQslXSvpBknvK+UflPSeVr5rRESMjvG4Z3UIMN/2akn9nlQWVdwF+IukbWwvLYc+QbW+1OmAgfcCs0YQz17ATcDbJf1LWSl4PeBk4KW2l5elQbrL+acAVwLfHkGbERExBOMxDDiHavXdwbwF+CFwJnBQo9D2fVQr934dOBH4lO0VI4hnNvBV4HZg11I2lSqR/7m0+bDtm8rnB4Flkl7aXJGkQyX1Supd/eDKEYQUERF1bU1WkiZQvc19WQunzwbOKNvs+gHbZwDPBKbZ/u4I4pkE7An8qN6O7XuA84DbJJ0haY6k+s+qF3hlc322T7bdY7una/KGww0rIiKatLtntTGwYrCTJG1KtUz9FbZvBh4rqwE3jm8BbAZMlzRlBPHsB1xaektnAwdI6gKw/V6qRHYVcDjV8F/DXcD0EbQbERFD0O5ktQqY2MJ5B1L1nJZKWkZ1v+ig2vGvAkcDZwGfHkE8s4HXlTYWABsBezQO2l5s+yvA66mGJRsmUn2XiIhog7YmK9v3Al2SBktYs4E32O623Q28mJKsJO0DPBs4DfgMVW/oheXYByV9sJVYJE0DdqdaULLRzj8CsyVNkfSa2ukzgdtq+9sD/c5ijIiI0TUeEywupEoSDTMkLa9tHwO2An7ZOKHMBLxP0quB44EPuPIA8HGqyRYAz6dMiujDu+vtAB8CLrH9cO2cc4H9gS7g45JukrQIOAZ4d+283YCLhvHdIyJiGGS7vQ1Ks4C5tg8eg7p/BPyt7UdGu+5aGy3F39PT497e3rEKIyLiaUnSAts9zeVt71nZXghc2pjIMMp17zeWiarYGPjkoGdFRMSoGZcX2do+ZfCzOpPtn453DBERa5u8GzAiIjpeklVERHS8JKuIiOh4SVYREdHxkqwiIqLjJVlFRETHS7KKiIiOl2QVEREdb1weCl4bLL5jJd1HnD/eYUTEX7Fl8/Yd7xA6RnpWERHR8ZKsIiKi441pspI0SdJlkrokdUvqcw0oSXMl3ShpsaRrJR0nab3a8VmSLGnvFtudIOl4SbdKukXSuWV14cbx1ZIWSVoi6YeSnlHKuyWtkrRQ0q8lXSXpXbXr9pN0zLB/IBERMSxj3bM6BJhve3V/J0g6DNgL2NX2TsBLqJaNn1Q7bTZwRfmzFZ8DpgLb294OOAeYL0nl+CrbM23vCNxDtehiw622Z9l+AdWCjx+R9J5y7Hxgf0mTW4wjIiJGwVgnqzlUCxoO5Cjg/bZXANh+xPY82/cBlATzVqrFD/cabJXhkkjeA3ykkSRtfxt4GHhtH5f8Ati8r7ps/xaYS7VQI64W//oZsF8/bR8qqVdS7+oHVw4UZkREDMGYJStJE4BtbS8b4JypwJSyEnB/dgOW2r6VKlG8cZCmnwfc3kh2Nb3ADk3tdwF7AucNUN81VCsQ1+t5ZV8n2j7Zdo/tnq7JGw4SZkREtGose1YbAysGOUfAE0sVS9q73EtaJukVpXg2cGb5fCaDDwWuUWc/5ZPKcvV/Bp4FDLRGlZr27wKmDxJDRESMorFMVquAAYfsSu/nAUnblP0LbM8ElgATSs/nLcCnJC0DvgbsU3pk/fkNsHUf5+wC3NCIrbSzNTCBNe9ZNZsF/Lq2P7F8t4iIaJMxS1a27wW6BrvHBBwLnFSbkSeeTHKvA661vaXtbttbA2cDby7nXixpjftNth8AvgMcV5Idkt4JTAYuaTp3JdX9qMPrsw8bJHUDX6JKkg3bUyXTiIhok7GeYHEhsHttf4ak5bXtbcBJwEXAryRdB1wJLCzbbOAHTXWeDbxD0jpU96fu6aPdI4GHgJsl3QK8DTigTJBYg+2FwLVUM/8AntuYug6cBXytTNBo2INqVmBERLSJ+vj9PXqVS7OAubYPHoO6dwQOsT13tOseoM1Nge/b3nOwc3t6etzb29uGqCIinj4kLbDd01w+pj2r0mu5tDEcN8p1L2lnoiq2Aj7a5jYjItZ6Y/4iW9unjHUb7WL76vGOISJibZR3A0ZERMdLsoqIiI6XZBURER0vySoiIjpeklVERHS8JKuIiOh4SVYREdHxxvw5q7XV4jtW0n1E3soUEWuXZfP2HZN607OKiIiOl2QVEREdr63JStIkSZdJ6pK0jqQTJC2RtFjS1Y11rcrii4vLQoyLGgsxStpB0iWSbpZ0i6RPliVFBmqzW1K/S3pIOlfSL5rKTpD0ydr+UZJOLJ+/JOm1I/k5RETE0LT7ntUhwHzbqyXNplpxd2fbj0vaAnigdu4etv/U2JE0iWr5+ffbvlDSZKrlQj4AnDicYMoaWrsAf5G0je2l5dAngEWSTqdaXfi9VIswQrW21X/QtDZWRESMnXYPA84Bzi2fnwPcaftxANvLy4KN/XkHcKXtC8v5DwIfBI4YQTxvAX4InMmT61k1VjA+Cvg6VSL8lO0V5dhtwEaSNhtBuxERMQRtS1aSJgDb2l5Wis4C/qYM8325rH1Vd2k59quyvwOwoH6C7VuBKZKmDTOs2cAZZZvdVPcZwDOBaba/23TdNcBuzZVJOlRSr6Te1Q+uHGZIERHRrJ09q42BFY0d28uBGVSr+j4OXCypvqjhHrZn2n5Z2RfVkFxfhryCZFlI8XnAFbZvBh4rCzo2jm8BbAZMlzSl6fK7qIYw1wzCPtl2j+2erskbDjWkiIjoRzuT1SpgYr3A9sO2f2z7Y8DngDcPcP31wBqrR0raFviL7fuHEc+BVD2npZKWAd3UhgKBrwJHU/UAP9107USq7xMREW3QtmRV7kd1SZoIIGkXSdPL53WAnYHbBqjidGB3Sa8r10wCTgC+UPZfKum0IYQ0G3iD7W7b3cCLKclK0j7As4HTgM8AB0h6Ye3a7YF+ZxhGRMToavcEiwuB3cvnZwM/LNPKrwMeo5rQ0Cfbq4A3AZ+QdBOwGLi6ds1W9N/bmSFpeW37WDn/l7X6lwL3SXo1cDzwAVceAD7eaEfSelTDh71D/fIRETE8sod8u2f4jVWTKObaPngM6v4i8F3b14123U3tHADsYvuTA53X09Pj3t7ks4iIoZC0wHZPc3lbn7OyvVDSpZK6bK8e5bo/Npr1DWBd4MttaisiIhiHF9naPqXdbY4m2/893jFERKxt8m7AiIjoeElWERHR8ZKsIiKi47V1NuDaRNL9wE3jHUcfNgb+NOhZ7depcUHnxpa4hqZT44LOjW084tra9ibNhVkpeOzc1Nf0y/EmqTdxDU2nxpa4hqZT44LOja2T4sowYEREdLwkq4iI6HhJVmPn5PEOoB+Ja+g6NbbENTSdGhd0bmwdE1cmWERERMdLzyoiIjpeklVERHS8JKtRJukNkm6S9BtJR7SpzVMk3VWWW2mUPUvSTyXdUv58Zu3YkSW+myTtXSt/saTF5dgJkjSCmLYsLy3+taTrJf1TJ8RV6pso6SpJ15bYjumU2EqdXZIWSvpRp8QlaVmpb5Gk3g6K6xmS/kfSjeW/tZd3SFwzys+qsd0n6cMdEttHyn/3SySdUf5/GPe4BmU72yhtQBdwK7AtMAG4FnhhG9p9FbALsKRW9gXgiPL5CODz5fMLS1zrA9uUeLvKsauAlwMCfgzsM4KYnkO1lArAVODm0va4xlXqEzClfF4P+BWwayfEVuqcC3wf+FEn/F2W+pYBGzeVdUJc3wHeWz5PAJ7RCXE1xdgF/AHYerxjAzYHlgKTyv5ZwLvHO66WYh/Lyte2rfzFXVDbPxI4sk1td7NmsroJeE75/Byqh5SfEhNwQYn7OcCNtfLZwDdHMb5zgdd3YFyTgWuAl3VCbMAWwMXAa3kyWXVCXMt4arIa17iAaVS/eNVJcfUR517AlZ0QG1Wy+h3wLKqXQvyoxNdRP7O+tgwDjq7GfwgNy0vZeNjU9p0A5c9nl/L+Yty8fG4uHzFJ3cAsqh5MR8RVhtoWAXcBP7XdKbEdT7Uy9eO1sk6Iy8CFkhZIOrRD4toWuBv4dhk2/U9JG3RAXM0OAs4on8c1Ntt3AF8CbgfuBFbavnC842pFktXo6mvMttOeDegvxjGJXdIU4Gzgw7bv65S4bK+2PZOqJ/NSSTuOd2yS9gPusr2g1UvaEVexm+1dgH2Af5T0qg6Ia12q4e+TbM8CHqAawhrvuJ5sUJoA7A8Mtg5eu/4beybwJqohvenABpL+brzjakWS1ehaDmxZ298C+P04xfJHSc8BKH/eVcr7i3F5+dxcPmyS1qNKVKfbnt8pcdXZXgH8DHhDB8S2G7C/pGXAmcBrJX2vA+LC9u/Ln3cBPwBe2gFxLQeWl14xwP9QJa/xjqtuH+Aa238s++Md2+uApbbvtv0oMB94RQfENagkq9F1NbCdpG3Kv6gOAs4bp1jOA95VPr+L6p5Ro/wgSetL2gbYDriqdP3vl7RrmdXzzto1Q1bq+Bbwa9vHdUpcJbZNJD2jfJ5E9T/wjeMdm+0jbW9hu5vqv51LbP/deMclaQNJUxufqe5xLBnvuGz/AfidpBmlaE/ghvGOq8lsnhwCbMQwnrHdDuwqaXKpb0/g1x0Q1+DG8obY2rgBb6Sa+XYrcFSb2jyDavz5Uap/8fw9sBHVjfpbyp/Pqp1/VInvJmozeIAeql9CtwJfp+nG9RBj2p1qWOA6YFHZ3jjecZX6dgYWltiWAJ8q5eMeW63e1/DkBIvx/rvclmpG2LXA9Y3/rsc7rlLfTKC3/F2eAzyzE+IqdU4G/gxsWCsb99iAY6j+cbYE+C7VTL9xj2uwLa9bioiIjpdhwIiI6HhJVhER0fGSrCIiouMlWUVERMdLsoqIiI6XZBUxziRtpCffzv0HSXfU9ic0nfthSZNbqPNnknr6KH9leeP2IkkvUHlTv6QeSScMUme3am/2bzr2bknTB4srYriSrCLGme0/257p6vVP3wC+0ti3/UjT6R+men5nuOYAXyptrarF0Gv7QyOo991Ur++JGBNJVhEdSNKe5eWsi1WtV7a+pA9RJYRLJV1azjtJUq9q63INUOd7gbcDn5J0etOx1+jJ9bM2UbWm0TWSvinpNkkbl1O7JP1Hae9CSZMkvZXqAdHTS49tkqR5km6QdJ2kL43yjyfWQklWEZ1nInAqcKDtnahe2Pp+2ydQvX9tD9t7lHOPst1D9VaOV0vaub9Kbf8n1etzPmZ7zgDtf5rqVU+7UL0HcKvase2AE23vAKwA3mL7f6jeIjGn9NgmAQcAO9jeGfjsUL58RF+SrCI6TxfVy0ZvLvvfoVpgsy9vl3QN1eujdqBaLG+kdqd6kS62fwLcWzu21Pai8nkB1Tpqze4DHgL+U9LfAg+OQkyxlkuyiug8D7RyUnmx6OHAnqUHcz5Vr2ykBlqe/OHa59VUvb412H6M6q3sZwNvBn4yCjHFWi7JKqLzTAS6JT2v7B8MXFY+3w9MLZ+nUSW2lZI2pVqOYjRcQXVvC0l7Ub0cdjBPxKVqDbMNbf8v1YSQmaMUV6zFnvKvoogYdw8B7wH+W9K6VEvPfKMcOxn4saQ7be8haSHVm9B/C1w5Su0fA5wh6UCqJHknVTKaMsA1pwLfkLSKKmmeK2kiVS/tI6MUV6zF8tb1iFiDpPWB1bYfk/RyqpV4Z45zWLGWS88qIpptBZwlaR3gEeAfxjmeiPSsIiKi82WCRUREdLwkq4iI6HhJVhER0fGSrCIiouMlWUVERMf7/4W6v789rRLqAAAAAElFTkSuQmCC\n", "text/plain": [ "
" ] }, "metadata": { "needs_background": "light" }, "output_type": "display_data" } ], "source": [ "# get top ten busiest round trip routes\n", "top_routes = flight_filtered['ROUND_TRIP_ROUTE'].value_counts()[:10]\n", "top_routes.plot(kind='barh', title='Top 10 Busiest Routes')\n", "plt.ylabel('Round-trip routes')\n", "plt.xlabel('Total flights')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Q2\n", "The 10 most profitable round trip routes (without considering the upfront airplane cost) in the quarter. Along with the profit, show total revenue, total cost, summary values of other key components and total round trip flights in the quarter for the top 10 most profitable routes. Exclude canceled flights from these calculations." ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "scrolled": true }, "outputs": [], "source": [ "ticket_data['ROUND_TRIP_ROUTE'] = [frozenset([x, y]) for x, y in zip(ticket_data['ORIGIN'], ticket_data['DESTINATION'])]" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Filtering out ticket price above 1656.0 and below 11.0\n" ] } ], "source": [ "# consider only roundtrips and drop nan values\n", "ticket_filtered = ticket_data[ticket_data['ROUNDTRIP'] == 1].dropna(subset='ITIN_FARE', axis=0)\n", "\n", "# convert fares to numeric values\n", "ticket_filtered['ITIN_FARE'] = pd.to_numeric(ticket_filtered['ITIN_FARE'], errors='coerce')\n", "\n", "# exclude top and bottom 1% outliers\n", "q_low = ticket_filtered['ITIN_FARE'].quantile(0.01)\n", "q_hi = ticket_filtered['ITIN_FARE'].quantile(0.99)\n", "ticket_filtered = ticket_filtered[(ticket_filtered['ITIN_FARE'] < q_hi) & (ticket_filtered['ITIN_FARE'] > q_low)]\n", "print(f'Filtering out ticket price above {q_hi} and below {q_low}')" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Before cleaning, ticket dataset has 1167285 records.\n", "After cleaning, ticket dataset has 663953 records.\n" ] } ], "source": [ "# check dataframe shape before and after cleaning\n", "print(f'Before cleaning, ticket dataset has {ticket_data.shape[0]} records.\\n'\n", " f'After cleaning, ticket dataset has {ticket_filtered.shape[0]} records.')" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ITIN_FAREOCCUPANCY_RATE
ROUND_TRIP_ROUTE
(SFO, CLT)719.720.64
(DTW, MSY)400.330.65
(LGA, BGR)343.860.66
(RNO, SJC)229.730.66
(CLE, APN)402.000.68
\n", "
" ], "text/plain": [ " ITIN_FARE OCCUPANCY_RATE\n", "ROUND_TRIP_ROUTE \n", "(SFO, CLT) 719.72 0.64\n", "(DTW, MSY) 400.33 0.65\n", "(LGA, BGR) 343.86 0.66\n", "(RNO, SJC) 229.73 0.66\n", "(CLE, APN) 402.00 0.68" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# merge fare and baggage revenues together\n", "fare = ticket_filtered.groupby('ROUND_TRIP_ROUTE')[['ITIN_FARE']].mean()\n", "baggage = flight_filtered.groupby('ROUND_TRIP_ROUTE')[['OCCUPANCY_RATE']].mean()\n", "revenue = pd.merge(fare, baggage, on='ROUND_TRIP_ROUTE')\n", "revenue.sample(5)" ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [], "source": [ "# key assumptions\n", "max_occupancy = 200\n", "baggage_fee = 35\n", "percentage_of_pax_checking_baggage = 0.5\n", "maint_cost = 8 # dollars per mile\n", "ins_cost = 1.18 # dollars per mile\n", "airpt_cost_large = 10000\n", "airpt_cost_medium = 5000\n", "delay_cost = 75 # per minute over 15 minutes" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ITIN_FAREOCCUPANCY_RATETICKET_REVBAGGAGE_REVTOTAL_REV
ROUND_TRIP_ROUTE
(MSP, SLC)586.870.6576383.544555.4180938.95
(BHM, DTW)552.690.6672497.554591.0177088.55
(SLC, BWI)601.670.6476874.014471.9081345.91
(MSP, SAT)483.920.6563018.524557.8467576.37
(ORD, OKC)420.310.6453983.874495.3658479.23
\n", "
" ], "text/plain": [ " ITIN_FARE OCCUPANCY_RATE TICKET_REV BAGGAGE_REV \\\n", "ROUND_TRIP_ROUTE \n", "(MSP, SLC) 586.87 0.65 76383.54 4555.41 \n", "(BHM, DTW) 552.69 0.66 72497.55 4591.01 \n", "(SLC, BWI) 601.67 0.64 76874.01 4471.90 \n", "(MSP, SAT) 483.92 0.65 63018.52 4557.84 \n", "(ORD, OKC) 420.31 0.64 53983.87 4495.36 \n", "\n", " TOTAL_REV \n", "ROUND_TRIP_ROUTE \n", "(MSP, SLC) 80938.95 \n", "(BHM, DTW) 77088.55 \n", "(SLC, BWI) 81345.91 \n", "(MSP, SAT) 67576.37 \n", "(ORD, OKC) 58479.23 " ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# calculate ticket revenue per round trip\n", "revenue['TICKET_REV'] = revenue['ITIN_FARE'] * max_occupancy * revenue['OCCUPANCY_RATE']\n", "\n", "# calculate baggage revenue per round trip (baggage fee * number of passengers * percentage of passengers checking baggage)\n", "revenue['BAGGAGE_REV'] = baggage_fee * 2 * max_occupancy * revenue['OCCUPANCY_RATE'] * percentage_of_pax_checking_baggage\n", "\n", "# calculate total revenue per round trip\n", "revenue['TOTAL_REV'] = revenue['TICKET_REV'] + revenue['BAGGAGE_REV']\n", "revenue.sample(5)" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "# keep medium and large airports data only\n", "flight_filtered = flight_filtered[flight_filtered['ORIGIN_TYPE'].isin(['medium_airport', 'large_airport'])].copy()\n", "flight_filtered = flight_filtered[flight_filtered['DEST_TYPE'].isin(['medium_airport', 'large_airport'])].copy()" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [], "source": [ "# column 'DISTANCE' contains mixed dtypes\n", "pd.api.types.infer_dtype(flight_filtered['DISTANCE'])\n", "\n", "# convert to numeric values\n", "flight_filtered['DISTANCE'] = pd.to_numeric(flight_filtered['DISTANCE'], errors='coerce')" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
FL_DATEOP_CARRIERTAIL_NUMOP_CARRIER_FL_NUMORIGIN_AIRPORT_IDORIGINORIGIN_CITY_NAMEDEST_AIRPORT_IDDESTINATIONDEST_CITY_NAME...OCCUPANCY_RATEORIGIN_TYPEDEST_TYPEROUND_TRIP_ROUTEMAINT_COSTINS_COSTAIRPT_OP_COSTDEP_COSTARR_COSTTOTAL_COST
3955862019-03-27OON641CA409012266IAHHouston, TX13487MSPMinneapolis, MN...0.39large_airportlarge_airport(MSP, IAH)8272.01220.12100000.00.019492.12
15131242019-01-18AAN204UW170214107PHXPhoenix, AZ12892LAXLos Angeles, CA...0.63large_airportlarge_airport(LAX, PHX)2960.0436.60100000.00.013396.60
12042272019-01-01AXN843HK461411292DENDenver, CO11540ELPEl Paso, TX...0.80large_airportmedium_airport(ELP, DEN)4504.0664.3450006150.07875.024193.34
10326472019-01-259EN607LR524312953LGANew York, NY13871OMAOmaha, NE...0.91large_airportlarge_airport(LGA, OMA)9184.01354.64100003900.02250.026688.64
4062712019-02-23UAN486UA64014908SNASanta Ana, CA12266IAHHouston, TX...0.68large_airportlarge_airport(IAH, SNA)10768.01588.28100000.00.022356.28
\n", "

5 rows × 25 columns

\n", "
" ], "text/plain": [ " FL_DATE OP_CARRIER TAIL_NUM OP_CARRIER_FL_NUM ORIGIN_AIRPORT_ID \\\n", "395586 2019-03-27 OO N641CA 4090 12266 \n", "1513124 2019-01-18 AA N204UW 1702 14107 \n", "1204227 2019-01-01 AX N843HK 4614 11292 \n", "1032647 2019-01-25 9E N607LR 5243 12953 \n", "406271 2019-02-23 UA N486UA 640 14908 \n", "\n", " ORIGIN ORIGIN_CITY_NAME DEST_AIRPORT_ID DESTINATION DEST_CITY_NAME \\\n", "395586 IAH Houston, TX 13487 MSP Minneapolis, MN \n", "1513124 PHX Phoenix, AZ 12892 LAX Los Angeles, CA \n", "1204227 DEN Denver, CO 11540 ELP El Paso, TX \n", "1032647 LGA New York, NY 13871 OMA Omaha, NE \n", "406271 SNA Santa Ana, CA 12266 IAH Houston, TX \n", "\n", " ... OCCUPANCY_RATE ORIGIN_TYPE DEST_TYPE ROUND_TRIP_ROUTE \\\n", "395586 ... 0.39 large_airport large_airport (MSP, IAH) \n", "1513124 ... 0.63 large_airport large_airport (LAX, PHX) \n", "1204227 ... 0.80 large_airport medium_airport (ELP, DEN) \n", "1032647 ... 0.91 large_airport large_airport (LGA, OMA) \n", "406271 ... 0.68 large_airport large_airport (IAH, SNA) \n", "\n", " MAINT_COST INS_COST AIRPT_OP_COST DEP_COST ARR_COST TOTAL_COST \n", "395586 8272.0 1220.12 10000 0.0 0.0 19492.12 \n", "1513124 2960.0 436.60 10000 0.0 0.0 13396.60 \n", "1204227 4504.0 664.34 5000 6150.0 7875.0 24193.34 \n", "1032647 9184.0 1354.64 10000 3900.0 2250.0 26688.64 \n", "406271 10768.0 1588.28 10000 0.0 0.0 22356.28 \n", "\n", "[5 rows x 25 columns]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# calculate 5 costs and add up to total cost\n", "flight_filtered['MAINT_COST'] = flight_filtered['DISTANCE'] * maint_cost\n", "flight_filtered['INS_COST'] = flight_filtered['DISTANCE'] * ins_cost\n", "flight_filtered['AIRPT_OP_COST'] = flight_filtered['DEST_TYPE'].apply(lambda x: airpt_cost_large if x == 'large_airport' else airpt_cost_medium)\n", "flight_filtered['DEP_COST'] = flight_filtered['DEP_DELAY'].apply(lambda x: (x - 15) * delay_cost if x > 15 else 0)\n", "flight_filtered['ARR_COST'] = flight_filtered['ARR_DELAY'].apply(lambda x: (x - 15) * delay_cost if x > 15 else 0)\n", "flight_filtered['TOTAL_COST'] = flight_filtered['MAINT_COST'] + flight_filtered['INS_COST'] + flight_filtered['AIRPT_OP_COST'] + flight_filtered['DEP_COST'] + flight_filtered['ARR_COST']\n", "flight_filtered.sample(5)" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TOTAL_COSTTOTAL_FLIGHTS
ROUND_TRIP_ROUTE
(CLT, DFW)39539.21895.0
(SEA, IAH)56292.52465.5
(BHM, DAL)32955.67179.0
(SYR, FLL)47243.43109.0
(ORD, CAK)26469.74451.5
\n", "
" ], "text/plain": [ " TOTAL_COST TOTAL_FLIGHTS\n", "ROUND_TRIP_ROUTE \n", "(CLT, DFW) 39539.21 895.0\n", "(SEA, IAH) 56292.52 465.5\n", "(BHM, DAL) 32955.67 179.0\n", "(SYR, FLL) 47243.43 109.0\n", "(ORD, CAK) 26469.74 451.5" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "cost = flight_filtered.groupby('ROUND_TRIP_ROUTE')['TOTAL_COST'].agg([np.mean, 'count'])\n", "cost.columns = ['TOTAL_COST', 'TOTAL_FLIGHTS'] # single-flight data\n", "cost['TOTAL_COST'] = cost['TOTAL_COST'] * 2 # double for round-trip cost\n", "cost['TOTAL_FLIGHTS'] = cost['TOTAL_FLIGHTS'] / 2 # number of round-trip flights\n", "cost.sample(5)" ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ITIN_FAREOCCUPANCY_RATETICKET_REVBAGGAGE_REVTOTAL_REVTOTAL_COSTTOTAL_FLIGHTSPROFITQUARTERLY_PROFIT
ROUND_TRIP_ROUTE
(ORD, DCA)532.120.6568954.404535.4573489.8534686.601847.538803.257.17e+07
(CLT, ATL)508.830.6566032.944542.1370575.0725910.981538.044664.096.87e+07
(LGA, DCA)479.760.6562619.184568.2867187.4528055.891679.539131.566.57e+07
(LGA, ATL)456.970.6559507.204557.7764064.9736959.332297.027105.646.23e+07
(DCA, ATL)467.180.6560773.294552.9965326.2832180.851744.033145.435.78e+07
\n", "
" ], "text/plain": [ " ITIN_FARE OCCUPANCY_RATE TICKET_REV BAGGAGE_REV \\\n", "ROUND_TRIP_ROUTE \n", "(ORD, DCA) 532.12 0.65 68954.40 4535.45 \n", "(CLT, ATL) 508.83 0.65 66032.94 4542.13 \n", "(LGA, DCA) 479.76 0.65 62619.18 4568.28 \n", "(LGA, ATL) 456.97 0.65 59507.20 4557.77 \n", "(DCA, ATL) 467.18 0.65 60773.29 4552.99 \n", "\n", " TOTAL_REV TOTAL_COST TOTAL_FLIGHTS PROFIT \\\n", "ROUND_TRIP_ROUTE \n", "(ORD, DCA) 73489.85 34686.60 1847.5 38803.25 \n", "(CLT, ATL) 70575.07 25910.98 1538.0 44664.09 \n", "(LGA, DCA) 67187.45 28055.89 1679.5 39131.56 \n", "(LGA, ATL) 64064.97 36959.33 2297.0 27105.64 \n", "(DCA, ATL) 65326.28 32180.85 1744.0 33145.43 \n", "\n", " QUARTERLY_PROFIT \n", "ROUND_TRIP_ROUTE \n", "(ORD, DCA) 7.17e+07 \n", "(CLT, ATL) 6.87e+07 \n", "(LGA, DCA) 6.57e+07 \n", "(LGA, ATL) 6.23e+07 \n", "(DCA, ATL) 5.78e+07 " ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# merge revenue and cost data\n", "profit = pd.merge(revenue, cost, on='ROUND_TRIP_ROUTE')\n", "\n", "# profit = total revenue - total cost\n", "profit['PROFIT'] = profit['TOTAL_REV'] - profit['TOTAL_COST']\n", "\n", "# quarterly profit = profit * number of round-trip flights\n", "profit['QUARTERLY_PROFIT'] = profit['PROFIT'] * profit['TOTAL_FLIGHTS']\n", "\n", "# get top profitable routes \n", "profit_by_quarter = profit.sort_values('QUARTERLY_PROFIT', ascending=False)\n", "profit_by_quarter.head(10) # top routes by quarterly profit" ] }, { "cell_type": "code", "execution_count": 23, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ITIN_FAREOCCUPANCY_RATETICKET_REVBAGGAGE_REVTOTAL_REVTOTAL_COSTTOTAL_FLIGHTSPROFITQUARTERLY_PROFIT
ROUND_TRIP_ROUTE
(ADK, ANC)1126.600.66148809.174623.04153432.2138316.6423.0115115.572.65e+06
(MDT, PHL)909.000.65117998.274543.39122541.6618535.19397.0104006.484.13e+07
(IAH, STT)838.270.88147534.936160.00153694.9353005.201.0100689.731.01e+05
(DEN, SUN)805.750.66105873.344598.90110472.2526315.2273.084157.036.14e+06
(GUM, HNL)1343.620.62167200.414355.42171555.8390292.2988.581263.547.19e+06
\n", "
" ], "text/plain": [ " ITIN_FARE OCCUPANCY_RATE TICKET_REV BAGGAGE_REV \\\n", "ROUND_TRIP_ROUTE \n", "(ADK, ANC) 1126.60 0.66 148809.17 4623.04 \n", "(MDT, PHL) 909.00 0.65 117998.27 4543.39 \n", "(IAH, STT) 838.27 0.88 147534.93 6160.00 \n", "(DEN, SUN) 805.75 0.66 105873.34 4598.90 \n", "(GUM, HNL) 1343.62 0.62 167200.41 4355.42 \n", "\n", " TOTAL_REV TOTAL_COST TOTAL_FLIGHTS PROFIT \\\n", "ROUND_TRIP_ROUTE \n", "(ADK, ANC) 153432.21 38316.64 23.0 115115.57 \n", "(MDT, PHL) 122541.66 18535.19 397.0 104006.48 \n", "(IAH, STT) 153694.93 53005.20 1.0 100689.73 \n", "(DEN, SUN) 110472.25 26315.22 73.0 84157.03 \n", "(GUM, HNL) 171555.83 90292.29 88.5 81263.54 \n", "\n", " QUARTERLY_PROFIT \n", "ROUND_TRIP_ROUTE \n", "(ADK, ANC) 2.65e+06 \n", "(MDT, PHL) 4.13e+07 \n", "(IAH, STT) 1.01e+05 \n", "(DEN, SUN) 6.14e+06 \n", "(GUM, HNL) 7.19e+06 " ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "profit_by_trip = profit.sort_values('PROFIT', ascending=False)\n", "profit_by_trip.head(10) # top routes by individual profit" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Q3\n", "The 5 round trip routes that you recommend to invest in based on any factors that you\n", "choose." ] }, { "cell_type": "code", "execution_count": 24, "metadata": { "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ARR_DELAY
ROUND_TRIP_ROUTE
(CLE, RSW)5.23
(CID, CLT)2.81
(CLT, MSN)4.59
(CHA, CLT)3.51
(CLT, SAV)2.28
\n", "
" ], "text/plain": [ " ARR_DELAY\n", "ROUND_TRIP_ROUTE \n", "(CLE, RSW) 5.23\n", "(CID, CLT) 2.81\n", "(CLT, MSN) 4.59\n", "(CHA, CLT) 3.51\n", "(CLT, SAV) 2.28" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# get on-time flights\n", "delay = flight_filtered.groupby('ROUND_TRIP_ROUTE')[['ARR_DELAY']].mean()\n", "on_time_flights = delay[delay['ARR_DELAY'] <= 15]\n", "on_time_flights.head()" ] }, { "cell_type": "code", "execution_count": 25, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/plain": [ "[frozenset({'MDT', 'PHL'}),\n", " frozenset({'ORD', 'XNA'}),\n", " frozenset({'CLT', 'GSP'}),\n", " frozenset({'CLT', 'MYR'}),\n", " frozenset({'CLT', 'GSO'})]" ] }, "execution_count": 25, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# choose routes that are both profitable at individual & quarter level and on time\n", "rec_routes = []\n", "for route in profit_by_trip.index:\n", " if route in profit_by_quarter.index[:50] and route in on_time_flights.index:\n", " rec_routes.append(route)\n", " if len(rec_routes) == 5:\n", " break\n", "rec_routes" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Q4\n", "The number of round trip flights it will take to breakeven on the upfront airplane cost for each of the 5 round trip routes that you recommend. Print key summary components for these routes." ] }, { "cell_type": "code", "execution_count": 26, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
ITIN_FAREOCCUPANCY_RATETICKET_REVBAGGAGE_REVTOTAL_REVTOTAL_COSTTOTAL_FLIGHTSPROFITQUARTERLY_PROFIT
ROUND_TRIP_ROUTE
(MDT, PHL)909.000.65117998.274543.39122541.6618535.19397.0104006.484.13e+07
(ORD, XNA)759.450.6598488.044538.93103026.9727869.23531.075157.743.99e+07
(CLT, GSP)718.330.6593322.974547.0697870.0323377.03773.574493.005.76e+07
(CLT, MYR)660.670.6585427.034525.6589952.6819303.31677.070649.374.78e+07
(GSO, CLT)686.000.6589322.094557.2593879.3423326.40743.570552.945.25e+07
\n", "
" ], "text/plain": [ " ITIN_FARE OCCUPANCY_RATE TICKET_REV BAGGAGE_REV \\\n", "ROUND_TRIP_ROUTE \n", "(MDT, PHL) 909.00 0.65 117998.27 4543.39 \n", "(ORD, XNA) 759.45 0.65 98488.04 4538.93 \n", "(CLT, GSP) 718.33 0.65 93322.97 4547.06 \n", "(CLT, MYR) 660.67 0.65 85427.03 4525.65 \n", "(GSO, CLT) 686.00 0.65 89322.09 4557.25 \n", "\n", " TOTAL_REV TOTAL_COST TOTAL_FLIGHTS PROFIT \\\n", "ROUND_TRIP_ROUTE \n", "(MDT, PHL) 122541.66 18535.19 397.0 104006.48 \n", "(ORD, XNA) 103026.97 27869.23 531.0 75157.74 \n", "(CLT, GSP) 97870.03 23377.03 773.5 74493.00 \n", "(CLT, MYR) 89952.68 19303.31 677.0 70649.37 \n", "(GSO, CLT) 93879.34 23326.40 743.5 70552.94 \n", "\n", " QUARTERLY_PROFIT \n", "ROUND_TRIP_ROUTE \n", "(MDT, PHL) 4.13e+07 \n", "(ORD, XNA) 3.99e+07 \n", "(CLT, GSP) 5.76e+07 \n", "(CLT, MYR) 4.78e+07 \n", "(GSO, CLT) 5.25e+07 " ] }, "execution_count": 26, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# breakeven analysis\n", "breakeven = profit_by_trip.loc[rec_routes]\n", "breakeven" ] }, { "cell_type": "code", "execution_count": 36, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PROFITQUARTERLY_PROFITROUND_TRIPS_TO_BREAKEVEN
ROUND_TRIP_ROUTE
(MDT, PHL)104006.484.13e+07865
(ORD, XNA)75157.743.99e+071197
(CLT, GSP)74493.005.76e+071208
(CLT, MYR)70649.374.78e+071273
(GSO, CLT)70552.945.25e+071275
\n", "
" ], "text/plain": [ " PROFIT QUARTERLY_PROFIT ROUND_TRIPS_TO_BREAKEVEN\n", "ROUND_TRIP_ROUTE \n", "(MDT, PHL) 104006.48 4.13e+07 865\n", "(ORD, XNA) 75157.74 3.99e+07 1197\n", "(CLT, GSP) 74493.00 5.76e+07 1208\n", "(CLT, MYR) 70649.37 4.78e+07 1273\n", "(GSO, CLT) 70552.94 5.25e+07 1275" ] }, "execution_count": 36, "metadata": {}, "output_type": "execute_result" } ], "source": [ "upfront_cost = 90_000_000\n", "breakeven['ROUND_TRIPS_TO_BREAKEVEN'] = (upfront_cost / breakeven['PROFIT']).astype(int)\n", "breakeven[['PROFIT', 'QUARTERLY_PROFIT', 'ROUND_TRIPS_TO_BREAKEVEN']]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Q5\n", "Key Performance Indicators (KPI's) that you recommend tracking in the future to measure the success of the round trip routes that you recommend." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "- Departure/arrival punctuality: the percentage of flights that depart/arrive on-time at the planned origin/destination airport. All flights that depart/arrive as scheduled or with a 15-minute window are considered on-time.\n", "- Seat load factor: the percentage of checked-in passengers in relation to an aircraft’s available seats. Seat load factor is an important measure of profitability.\n", "- Completion factor: the percentage of scheduled flights that are completed and not cancelled over a defined period. Cancellations will adversely affect profitability and brand image." ] }, { "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.12" }, "vscode": { "interpreter": { "hash": "d51b34bacaac6590d0d5e48d998469359231435c0c02e73b0ace81440b48943f" } } }, "nbformat": 4, "nbformat_minor": 2 }