# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Exploring US Data Flights Exercise Solutions
#
# * Below are 5 asnwers for all the questions asked in flights.ipynb.
# * The code assumes that the files "515364771_T_ONTIME_REPORTING.csv" and "L_UNIQUE_CARRIERS.csv" are located in the same folder as this notebook.
# * Each exercise reads the dataset anew, so they can be run independantly.
#
# > <NAME> <br />
# > Α.Μ. 3160245
#
# ## Question 1
#
# * We are called to present 3 tables. The first contains the number of delays along with chance of having a delayed flight for every airport in the dataset.
# * The dataset contains a multitude of cells without actual data (NaN values). Since we will only be utilising the origin and departure delay columns, we will exclusively check for NaN values in the latter.
# * We proceed to count the total number of flights. This number will be used to find the probability of delay later.
# * We remove the flights with no delay, along with the outliers, by grouping the airports by their origin name and using the quantile function.
# * We now create a dataframe for the "misery index", the airports sorted by their number of delayed flights. 
# * To illustrate the probabiities easier, we round them up, reducing the number of decimal places

# +
import pandas as pd
import numpy as np

# QUESTION 1
# read datatset and create index. Remove unnecessary columns
# DEP_DELAY appears to contain the sum of the specific delays mentioned on the rest of the columns
# since we only care about the sum, we can safely drop the rest
dataset = pd.read_csv("515364771_T_ONTIME_REPORTING.csv", sep=",")
df = dataset.set_index(['ORIGIN'])

# update the dataframe, dropping NaN values from the DEP_DELAY column. make note of the total flights
df = df[np.isfinite(df.DEP_DELAY)]
flight_num = len(df)

# remove flights with no departure delay, along with the outliers
df = df.loc[df.DEP_DELAY  >  0]
df = df.loc[df.groupby('ORIGIN').size() > df.groupby('ORIGIN').size().quantile(0.01)]

# create new dataframe that will contain the misery index, along with the average and median delay of each airport
# first add the number of delays of each airport
flights = df.groupby('ORIGIN').size().to_frame()
flights.columns = ['NUMBER OF DELAYS']

# next, calculate the chance of delay
flights['CHANCE OF DELAY'] = (flights['NUMBER OF DELAYS']/flight_num).round(3)
flights = flights.sort_values(by = ['CHANCE OF DELAY'], ascending = False)
flights
# -

# * We return on the original dataframe to calculate the mean and median values of the delayed flights.
# * We group the airlines by their origin name and use mean()/median() on their respective departure delay cells
# * These values are stored and shown from a new dataframe, sorted by the average delay column.

# +
# calculate the average and median delay of each airport
mean_med = pd.DataFrame(columns = ['MEAN', 'MEDIAN'])
mean_med['MEAN'] = df.groupby('ORIGIN')['DEP_DELAY'].mean().round(1)
mean_med['MEDIAN'] = df.groupby('ORIGIN')['DEP_DELAY'].median()

# sort by average
mean_med = mean_med.sort_values(by = ['MEAN'], ascending = False)
mean_med
# -

# * With the two dataframes in hand, we concatenate and sort them by the airport's chance of a delayed flight.

# concatenate the two dataframes and sort by the probability of having a delayed departure
airport_delay = pd.concat([flights, mean_med], sort = False, axis = 1).reindex(flights.index)
airport_delay = airport_delay.sort_values(by = ['CHANCE OF DELAY'], ascending = False)
airport_delay

# ## Question 2
#
# * For this question, we will be mostly using the same code as above.
# * Instead of aiports, we will be presenting airline carriers.
# * We will also read the full name of the airlines from another cvs file. For each dataframe we create, we will merge those names with the airline codes.

# +
# QUESTION 2
import pandas as pd
import numpy as np

# create a dataframe with a new index to represent the carriers
# also load carrier names from another csv file
df = pd.read_csv("515364771_T_ONTIME_REPORTING.csv", sep=",")
df = df.set_index(['CARRIER'])
carrier_names = pd.read_csv("L_UNIQUE_CARRIERS.csv", sep=",")
carrier_names = carrier_names.rename(columns = {'Code' : 'CARRIER'}).set_index('CARRIER')

# update the dataframe, dropping NaN values from the DEP_DELAY column. make note of the total flights
df = df[np.isfinite(df.DEP_DELAY)]
flight_num = len(df)

# remove flights with no departure delay. this time we keep the outliers
df = df.loc[df.DEP_DELAY  >  0]

# create new dataframe that will contain the misery index, along with the average and median delay of each carrier
# first add the number of delays of each carrier
carriers = df.groupby('CARRIER').size().to_frame()
carriers.columns = ['NUMBER OF DELAYS']

# next, calculate the chance of delay
carriers['CHANCE OF DELAY'] = (carriers['NUMBER OF DELAYS']/flight_num).round(3)
# include the names of the carriers by merginf the two dataframes
carriers = carriers.merge(carrier_names, how = 'inner', on = 'CARRIER')
carriers = carriers[['Description', 'NUMBER OF DELAYS', 'CHANCE OF DELAY']].rename(columns = {'Description' : 'CARRIER NAME'})
# sort the carriers by their chance of havinf a delay and print
carriers = carriers.sort_values(by = ['CHANCE OF DELAY'], ascending = False)
carriers
# -

# * Same code once more, only our mean/median dataframe is merged with the airline names' dataframe.

# +
# calculate the average and median delay of each carrier
mean_med_c = pd.DataFrame(columns = ['MEAN', 'MEDIAN'])
mean_med_c['MEAN'] = df.groupby('CARRIER')['DEP_DELAY'].mean().round(1)
mean_med_c['MEDIAN'] = df.groupby('CARRIER')['DEP_DELAY'].median()

# sort by average
mean_med_c = mean_med_c.sort_values(by = ['MEAN'], ascending = False)
# include the names of the carriers
mean_med_c = mean_med_c.merge(carrier_names, how = 'inner', on = 'CARRIER')
mean_med_c = mean_med_c[['Description', 'MEAN', 'MEDIAN']].rename(columns = {'Description' : 'CARRIER NAME'})
mean_med_c
# -

# * As in question 1, the final dataframe concatenates the previous two, now merged with the airline names.

# concatenate the two dataframes and sort by the probability of having a delayed departure
carrier_delay = pd.concat([carriers, mean_med_c], sort = False, axis = 1).reindex(carriers.index)
carrier_delay = carrier_delay.sort_values(by = ['CHANCE OF DELAY'], ascending = False)
# include the names of the carriers
carrier_delay = carrier_delay.merge(carrier_names, how = 'inner', on = 'CARRIER')
carrier_delay = carrier_delay[['Description', 'NUMBER OF DELAYS', 'CHANCE OF DELAY', 'MEAN', 'MEDIAN']].rename(columns = {'Description' : 'CARRIER NAME'})
carrier_delay

# ## Question 3
#
# * To create the histogram, first we read our dataset and as before, drop the NaN values in the departure delay column.
# * We also drop all the unecessary columns of the dataset, keeping only the origin and destination columns.
# * We group those by origin name and count the number of flights for each one.
# * Finally, we create the histogram.

# +
# QUESTION 3
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# %matplotlib inline

# create the dataframe
df = pd.read_csv("515364771_T_ONTIME_REPORTING.csv", sep=",")

# update the dataframe, dropping NaN values from the DEP_DELAY column.
df = df[np.isfinite(df['DEP_DELAY'])]

df = df[['ORIGIN', 'DEST']].set_index('ORIGIN')
df = df.groupby('ORIGIN').count()

df.hist(bins = [0, 50000, 100000, 150000, 200000, 250000, 300000, 350000, 400000], edgecolor = 'black', figsize = [15, 10])
plt.xticks(fontsize = 14)
plt.yticks(fontsize = 12)
plt.xlabel("Flights range", fontsize=16)
plt.ylabel("Number of airports with departing flights", fontsize=14)
plt.show()
# -

# ## Question 4
#
# * To create this plot, first we will create a new dataframe with the two necessary columns.
# * This time, we'll be keeping the fl_date column, after we parse it with parse_dates. This will allow us to group flights based on their dates.
# * "Number of flights" will be filled with the total number of flights of every airport, for each month.
# * "Number of delayed flights" will only count the delayed flights of every airport, for each month.
# * Before we create our plot, we write a list with month names. We'll use them as tick labels on the x-axis of the plot.
# * We create the plot and use a couple of matplotlib function to better visualise the data.

# +
# QUESTION 4
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
# %matplotlib inline

# create the dataframe. parse_dates parses the given dates of the FL_date column and allows 
# for easier grouping of our data in months
df = pd.read_csv("515364771_T_ONTIME_REPORTING.csv", sep=",", parse_dates = ['FL_DATE'])

# update the dataframe, dropping NaN values from the DEP_DELAY column.
df = df[np.isfinite(df['DEP_DELAY'])]

# fill the dataframe with the number of flights and the number of delays for each month
pf = pd.DataFrame(columns = ['NUMBER OF FLIGHTS', 'NUMBER OF DELAYED FLIGHTS'])
pf['NUMBER OF FLIGHTS'] = df.groupby(df['FL_DATE'].dt.month).size()
pf['NUMBER OF DELAYED FLIGHTS'] = df.loc[df['DEP_DELAY']  >  0].groupby(df['FL_DATE'].dt.month).size()

# create plot, replace x-axis labels with months (instead of index numbers)
months = ['JAN','FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP', 'OCT', 'NOV', 'DEC']
pf_plot = pf.plot(x_compat = True, figsize = [15, 10])
pf_plot.set_xticklabels(months)
plt.xticks([x for x in range(1, 13)], fontsize = 14)
plt.xlabel("2018", fontsize = 16)
plt.ylabel("Number of Departures", fontsize = 14)
plt.show()
# -

# ## Question 5
#
# * After the usual preprocessing, we keep the four columns that concern our problem: Carrier code, origin and destination names and the departure delay.
# * We group the first three and find the mean values of the delays.
# * We sort those values and then keep only the first one for each origin-dest-carrier.
# * Meaning, we now have the carrier with the best mean delay for the specific origin - destination set.
# * We remove the carrier from the index of the table (we'll be using it on the next cell).
# * We print the first 25 rows of our resulting table.

# +
# QUESTION 5
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# create the default dataframe
df = pd.read_csv("515364771_T_ONTIME_REPORTING.csv", sep=",")

# update the dataframe, dropping NaN values from the DEP_DELAY column.
df = df[np.isfinite(df['DEP_DELAY'])]

df = df[['CARRIER', 'ORIGIN', 'DEST', 'DEP_DELAY']].set_index(['ORIGIN', 'DEST'])
df = df.groupby(['ORIGIN', 'DEST', 'CARRIER']).mean().round(2).rename(columns = {'DEP_DELAY' : 'AVERAGE DELAY OF CARRIER'})
df = df.sort_values(['ORIGIN', 'DEST', 'AVERAGE DELAY OF CARRIER'], ascending = True)
df = df[~df.index.get_level_values(1).duplicated()]
df = df.reset_index(level = ['CARRIER'])
df.head(25)
# -

# * The above table is obviously not that useful on its current state. Finding a specific row can be time consuming.
# * Below is a piece of code that recieves input (the desired origin and destination set) and searches the dataframe for the best carrier.
# * The input is case-agnostic.

# user can now search for the best airline code for a specific origin-destination point
print("Enter the origin code of your departure airport")
org = input().upper()
print("Enter the destination code of your destination")
dest = input().upper()
print("Finding best airline carrier...\n")
answer = df.loc[(df.index.get_level_values(0) == org) & (df.index.get_level_values(1) == dest)]
if answer.empty:
    print("No carrier found! Origin/Destination might not exist. Check for mistakes on your input.")
else:
    print(answer)

StarcoderdataJupyterScriptsDedupFiltered