{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Challenge 1\n# Airplane delay: Analysis and Prediction","metadata":{}},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns\nimport matplotlib.pyplot as plt","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_dtype = {\n    'ID'                   : int,\n    'YEAR'                 : int,  \n    'MONTH'                : int,  \n    'DAY'                  : int,  \n    'DAY_OF_WEEK'          : int,  \n    'AIRLINE'              : str, \n    'FLIGHT_NUMBER'        : str,  \n    'TAIL_NUMBER'          : str, \n    'ORIGIN_AIRPORT'       : str, \n    'DESTINATION_AIRPORT'  : str, \n    'SCHEDULED_DEPARTURE'  : str,  \n    'DEPARTURE_TIME'       : str, \n    'DEPARTURE_DELAY'      : float,\n    'TAXI_OUT'             : str, \n    'WHEELS_OFF'           : str,\n    'SCHEDULED_TIME'       : float,\n    'AIR_TIME'             : float,\n    'DISTANCE'             : int,\n    'SCHEDULED_ARRIVAL'    : str,\n    'DIVERTED'             : int,  \n    'CANCELLED'            : int,  \n    'CANCELLATION_REASON'  : str\n}\n\ny_dtype = {\n    'ID'                   : int,\n    \"ARRIVAL_DELAY\"        : float\n}\n\nX_train_df = pd.read_csv(\"/kaggle/input/eurecom-aml-2021-challenge-1/data/train_features.csv\", dtype=X_dtype)\ny_train_df = pd.read_csv(\"/kaggle/input/eurecom-aml-2021-challenge-1/data/train_targets.csv\", dtype=y_dtype)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Convert date-time features from str to datetime format\nfrom datetime import datetime\ndef parse_hhmm(x):\n    try: return pd.datetime.strptime(x, '%H%M')#.time()\n    except: return pd.NaT\n\nX_train_df.DEPARTURE_TIME = X_train_df.DEPARTURE_TIME.apply(parse_hhmm)\nX_train_df.SCHEDULED_DEPARTURE = X_train_df.SCHEDULED_DEPARTURE.apply(parse_hhmm)\nX_train_df.SCHEDULED_ARRIVAL = X_train_df.SCHEDULED_ARRIVAL.apply(parse_hhmm)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Merge feature dataframe and target dataframe for data exploration\ndf = pd.merge(X_train_df, y_train_df, on='ID')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Create new columns used for data exploration\ndf['DELAYED'] = (df.ARRIVAL_DELAY > 0).astype(str)\ndf['DATE'] = pd.to_datetime(df[['YEAR', 'MONTH', 'DAY']])\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Data analysis\n\n- Basic queries:\n  - How many unique origin airports?\n  - How many unique destination airports?\n  - How many carriers?\n  - How many flights that have a scheduled departure time later than 18h00?\n\n\n- Statistics on flight volume: this kind of statistics are helpful to reason about delays. Indeed, it is plausible to assume that \"*the more flights in an airport, the higher the probability of delay*\".\n  - How many flights in each month of the year?\n  - Is there any relationship between the number of flights and the days of week?\n  - How many flights in different days of months and in different hours of days?\n  - Which are the top 20 busiest airports (this depends on inbound and outbound traffic)?\n  - Which are the top 20 busiest carriers?\n\n\n- Statistics on the fraction of delayed flights\n  - What is the percentage of delayed flights (over total flights) for different hours of the day?\n  - Which hours of the day are characterized by the longest flight delay?\n  - What are the fluctuation of the percentage of delayed flights over different time granularities?\n  - What is the percentage of delayed flights which depart from one of the top 20 busiest airports?\n  - What is the percentage of delayed flights which belong to one of the top 20 busiest carriers?","metadata":{}},{"cell_type":"markdown","source":"## 1.2 Basic queries\n\n","metadata":{}},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\n\nHow many flights in overall?\n\n</div>","metadata":{}},{"cell_type":"code","source":"df['ID'].nunique()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\nHow many unique flight number?\n</div>","metadata":{}},{"cell_type":"code","source":"df['FLIGHT_NUMBER'].nunique()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\nHow many unique tail number?\n</div>","metadata":{}},{"cell_type":"code","source":"df['TAIL_NUMBER'].nunique()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\nHow many carriers (Airlines)?\n</div>","metadata":{}},{"cell_type":"code","source":"df['AIRLINE'].nunique()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import the airlines data  \n# in order to evaluate about getting a distribution of the delays per airline\ndf_airlines = pd.read_csv('/kaggle/input/eurecom-aml-2021-challenge-1/data/airlines.csv')\ndf_airlines\n#abbr_companies = df_airlines.set_index('IATA_CODE')['AIRLINE'].to_dict()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# joining airlines\ndf = df.merge(df_airlines, left_on='AIRLINE', right_on='IATA_CODE', how='inner')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# dropping old column and rename new one\ndf = df.drop(['AIRLINE_x'], axis=1)\ndf = df.rename(columns={\"AIRLINE_y\":\"AIRLINE\"})","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#df2 = df.loc[:, ['AIRLINE', 'DEPARTURE_DELAY']]\ndf2 = df.drop(['DAY_OF_WEEK', 'DIVERTED', 'CANCELLED', 'CANCELLATION_REASON'], axis=1)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Return the day names and month names of the Date**","metadata":{}},{"cell_type":"markdown","source":"<div class=\"alert alert-danger\">\nThere is a discrepancy between the conversion of the days of week by the 'DATE' col wrt the actual calender (year 2021) and by 'DAY_OF_WEEK' feature column therefore we droped it in advance.\n</div>","metadata":{}},{"cell_type":"code","source":"df2['DAY_OF_WEEK'] = df2['DATE'].dt.day_name()\ndf2['Month_of_Year'] = df2['DATE'].dt.month_name()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df2 = df2.sort_values(by='DATE')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#Analyzing the proportion of flights with respect to the companies.\nplt.rcParams['font.size'] = 12\nplt.rcParams[\"font.weight\"] = \"bold\"\n\nflights_by_carrier = ( df2.groupby(df2.AIRLINE)\n                          .agg({'ID':'count'})\n                          .rename(columns = {'ID' : 'Number of Flights'})\n                          .sort_values(by = 'Number of Flights', ascending = False)\n                          .reset_index()\n                      )\nimport matplotlib.pyplot as plt\nfig_dim = (14,18)\nf, ax = plt.subplots(figsize=fig_dim)\nquality=flights_by_carrier['AIRLINE']                    \nsize=df2[\"AIRLINE\"].value_counts()\n\nplt.pie(size,labels= quality, autopct='%1.1f%%')\ncentre_circle = plt.Circle((0,0),0.75,color='white', fc='white',linewidth=1.25)\nfig = plt.gcf()\nfig.gca().add_artist(centre_circle)\n\nplt.show()\nplt.savefig('airline1.jpeg')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# I extract a subset of columns and redefine the airlines labeling \ndf_2 = df2.loc[:, ['IATA_CODE','AIRLINE','DEPARTURE_DELAY','ARRIVAL_DELAY']]\n#df3['AIRLINE'] = df3['AIRLINE'].replace(abbr_companies)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_2.head(3)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#overview of delays by airlines companies\n#importing seaborn for statistical plots\nimport seaborn as sns\nsns.set(style=\"whitegrid\")\nfig = plt.figure(figsize=(20,7))\n\n# initialize the figure\nsns.despine(bottom=True, left=True)\n\n# Show each observation with a scatterplot\nax = sns.stripplot(x=\"DEPARTURE_DELAY\", y=\"AIRLINE\",\n              data=df_2, dodge=True, jitter=True\n            )\nax.set_xticklabels(['{:2.0f}h{:2.0f}m'.format(*[int(y) for y in divmod(x,60)])\n                         for x in ax.get_xticks()])\n\nplt.show()\nplt.savefig('airline2.png')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fig = plt.figure(figsize=(20,7))\n\n# initialize the figure\nsns.despine(bottom=True, left=True)\n\n# Show each observation with a scatterplot\nax = sns.stripplot(x=\"ARRIVAL_DELAY\", y=\"AIRLINE\",\n              data=df_2, dodge=True, jitter=True\n            )\nax.set_xticklabels(['{:2.0f}h{:2.0f}m'.format(*[int(y) for y in divmod(x,60)])\n                         for x in ax.get_xticks()])\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def boxgrid(x, y, **kwargs):\n    \"\"\" Quick hack for creating box plots with seaborn's PairGrid. \"\"\"\n    default_color = sns.color_palette()[0]\n    sns.boxplot(x, y, color = default_color)\n    \nbelow_120 = df2[df2['ARRIVAL_DELAY'] <120]\n\nplt.figure(figsize = [20, 20])\ng = sns.PairGrid(data = below_120, y_vars = ['ARRIVAL_DELAY'], x_vars = ['Month_of_Year'],\n                size = 7, aspect = 1.5)\ng.map(boxgrid)\nplt.show();","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **AIRLINES MEAN DELAYES: DEPARTURE DELAY and ARRIVAL DELAY**","metadata":{}},{"cell_type":"code","source":"import matplotlib as mpl\nimport matplotlib.pyplot as plt\nimport matplotlib.patches as patches\nfrom matplotlib.patches import ConnectionPatch\n\nmpl.rcParams.update(mpl.rcParamsDefault)\nmpl.rcParams['hatch.linewidth'] = 2.0  \n\nfig = plt.figure(figsize=(10,4))\nax = sns.barplot(x=\"DEPARTURE_DELAY\", y=\"AIRLINE\", data=df_2, color=\"lightgreen\", ci=None)\nax = sns.barplot(x=\"ARRIVAL_DELAY\", y=\"AIRLINE\", data=df_2, color=\"r\", hatch = '///', alpha = 0.0, ci=None)\nax.set(xlabel=\"Mean delay time (Arrival: HATCH LINES, Departure: LIGHTGREEN)\")\nplt.show()\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\n\nTwo way of plotting amount of delayed or not delayed flights per airline(in percent by normalization/simple counting):\n\n</div>","metadata":{}},{"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport seaborn as sns\nsns.set(color_codes=True)\n\nx,y = 'AIRLINE', 'DELAYED'\n\ng=(\ndf2.groupby(x)[y]\n.value_counts(normalize=True)\n.mul(100)\n.rename('percent')\n.reset_index()\n.pipe((sns.catplot,'data'), x=x,y='percent',hue=y, kind='bar', height=4, aspect=26/4)\n  )\ng.ax.set_ylim(0,100)\n\nfor p in g.ax.patches:\n    txt = str(p.get_height().round(2)) + '%'\n    txt_x = p.get_x() \n    txt_y = p.get_height()\n    g.ax.text(txt_x,txt_y,txt)\nplt.show()\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.countplot(y=\"AIRLINE\", hue=\"DELAYED\"\n                , data=df2,\n                  saturation=1,\n                  edgecolor=(0,0,0),\n                  linewidth=2,\n                  palette=\"Dark2\");\n\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"delay_type = lambda x:(((0,1)[x > 0],2)[x > 5],3)[x > 30]\ndf2['DELAY_LEVEL'] = df2['DEPARTURE_DELAY'].apply(delay_type)\n\nfig = plt.figure(figsize=(10,10))\nax = sns.countplot(y=\"AIRLINE\", hue='DELAY_LEVEL', data=df2)\n\n\nplt.xlabel('Flight count', fontsize=16, weight = 'bold', labelpad=10)\n\n# Set the legend\nL = plt.legend()\nL.get_texts()[0].set_text('earlier than on time (t < 0 min)')\nL.get_texts()[1].set_text('on time (t < 5 min)')\nL.get_texts()[2].set_text('small delay (5 < t < 30 min)')\nL.get_texts()[3].set_text('large delay (t > 30 min)')\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Percentage of amount of delay time in overall**","metadata":{}},{"cell_type":"code","source":"#factors = df.loc[:, ['DELAY_LEVEL']]\n\nfig_dim = (5,5)\nf, ax = plt.subplots(figsize=fig_dim)\nlabel=['earlier than on time (t < 0 min)','small delay (5 < t < 30 min)','large delay (t > 30 min)','on time (t < 5 min)']                    \nsize=df2[\"DELAY_LEVEL\"].value_counts()\n\nplt.pie(size,labels=label, autopct='%1.1f%%')\ncentre_circle = plt.Circle((0,0),0.75,color='white', fc='white',linewidth=1.25)\nfig = plt.gcf()\nfig.gca().add_artist(centre_circle)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\nIs there any relationship between the number of flights and the days of week?\n</div>","metadata":{}},{"cell_type":"markdown","source":"**There is a huge drop in number of flights on Sundays, probably related to the fact that this is the second day of weekend.\nAlso it seems that Friday is the most busiest day which is the day before weekend.**\n","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(8,4))\n#size=df['ID'].value_counts()\nax = sns.countplot(x='DAY_OF_WEEK',data=df2)\nfor p in ax.patches:\n        ax.annotate('{:1.0f}'.format(p.get_height()), (p.get_x(), p.get_height()))\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\n\nTwo way of plotting amount of delayed or not delayed flights per days of week (in percent by normalization/simple counting)\n\n</div>","metadata":{}},{"cell_type":"code","source":"plt.figure(figsize=(8,4))\nax = sns.countplot(x=\"DAY_OF_WEEK\", hue=\"DELAYED\"\n                , data=df2,\n                  saturation=1,\n                  edgecolor=(0,0,0),\n                  linewidth=2,\n                  palette=\"Dark2\");\n#plt.title('**********')\n#plt.xlabel('Number of Axles')\n#plt.ylabel('Frequency [%]')\nfor p in ax.patches:\n        ax.annotate('{:1.0f}'.format(p.get_height()), (p.get_x(), p.get_height()+2))\n#sns.countplot(x='DAY_OF_WEEK',data=df)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#sns.histplot(data=df, x='DAY_OF_WEEK', hue=\"DELAYED\", stat=\"probability\", multiple=\"fill\")\nx,y = 'DAY_OF_WEEK', 'DELAYED'\n\ng=(\ndf2.groupby(x)[y]\n.value_counts(normalize=True)\n.mul(100)\n.rename('percent')\n.reset_index()\n.pipe((sns.catplot,'data'), x=x,y='percent',hue=y, kind='bar', height=4, aspect=7/4)\n  )\ng.ax.set_ylim(0,100)\n\nfor p in g.ax.patches:\n    txt = str(p.get_height().round(2)) + '%'\n    txt_x = p.get_x() \n    txt_y = p.get_height()+2\n    g.ax.text(txt_x,txt_y,txt)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# The most/least busiest months of the year\n**Flights per month**","metadata":{}},{"cell_type":"markdown","source":"<div class=\"alert alert-info\">\nHow many flights in each month of the year?\n</div>\n","metadata":{}},{"cell_type":"code","source":"# Create dataframe of flights counts by month\n# It is in ascending order so we know the first one is January etc\nflights_per_month_df = ( df2.groupby(df2.MONTH)\n                            .agg({'ID':'count'})\n                            .rename(columns={'ID': 'Number of Flights'})\n                            .sort_values(by=['MONTH']) )\n\nflights_per_month_df.plot.line()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**It seems like there are more flights in the summer (especially July-August), probably because of holidays, as well as some increases in the beginning of Spring (Easter, Spring break), and the least active period is in February.**","metadata":{}},{"cell_type":"code","source":"figsize=plt.subplots(figsize=(14,6))\n#size=df['ID'].value_counts()\nsns.countplot(x='Month_of_Year',data=df2)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.figure(figsize=(14,4))\nax = sns.countplot(x=\"Month_of_Year\", hue=\"DELAYED\"\n                , data=df2,\n                  saturation=1,\n                  edgecolor=(0,0,0),\n                  linewidth=2,\n                  palette=\"Dark2\");\n#plt.title('**********')\n#plt.xlabel('Number of Axles')\n#plt.ylabel('Frequency [%]')\n\nfor p in ax.patches:\n        ax.annotate('{:1.0f}'.format(p.get_height()), (p.get_x(), p.get_height()))\n#sns.countplot(x='DAY_OF_WEEK',data=df)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"x,y = 'Month_of_Year', 'DELAYED'\n\ng=(\ndf2.groupby(x)[y]\n.value_counts(normalize=True)\n.mul(100)\n.rename('percent')\n.reset_index()\n.pipe((sns.catplot,'data'), x=x,y='percent',hue=y, kind='bar', height=4, aspect=15/4)\n  )\ng.ax.set_ylim(0,100)\n\nfor p in g.ax.patches:\n    txt = str(p.get_height().round(2)) + '%'\n    txt_x = p.get_x() \n    txt_y = p.get_height()+2\n    g.ax.text(txt_x,txt_y,txt)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Airline Flight Speed (miles/hour)**","metadata":{}},{"cell_type":"code","source":"df2['fly_speed'] = 60*df2['DISTANCE']/df2['AIR_TIME'] #fly_speed= (miles)*60/min = miles/hour\nsns.set_context(\"notebook\", font_scale=2.5)\nsns.set(style=\"ticks\", palette=\"muted\", color_codes=True)\nax = sns.violinplot(x=\"fly_speed\", y=\"AIRLINE\", data=df2);\nsns.despine(trim=True) #Remove the top and right spines from plot(s)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **Arrival & Departure Delays**","metadata":{}},{"cell_type":"code","source":"df_airports = pd.read_csv('/kaggle/input/eurecom-aml-2021-challenge-1/data/airports.csv')\n\nabbr_airport = df_airports.set_index('IATA_CODE')['AIRPORT'].to_dict()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"conda install -c conda-forge basemap-data-hires","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from collections import OrderedDict\nfrom mpl_toolkits.basemap import Basemap\ncount_flights = df['ORIGIN_AIRPORT'].value_counts()\n\nplt.figure(figsize=(15,15))\n\n# define properties of markers and labels\ncolors = ['cyan', 'yellow', 'lime', 'red', 'blue']\nsize_limits = [1, 100, 1000, 10000, 100000, 1000000]\nlabels = []\nfor i in range(len(size_limits)-1):\n    labels.append(\"{} <  < {}\".format(size_limits[i], size_limits[i+1])) \n\n\nmap = Basemap(resolution='i',llcrnrlon=-180, urcrnrlon=-50, llcrnrlat=10, urcrnrlat=75, lat_0=0, lon_0=0,)\nmap.shadedrelief()\nmap.drawcoastlines()\nmap.drawcountries(linewidth = 3)\nmap.drawstates(color='0.3')\n\n# put airports on map\nfor index, (code, y,x) in df_airports[['IATA_CODE', 'LATITUDE', 'LONGITUDE']].iterrows():\n    x, y = map(x, y)\n    isize = [i for i, val in enumerate(size_limits) if val < count_flights[code]]\n    ind = isize[-1]\n    map.plot(x, y, marker= '8', markersize = ind+5, markeredgewidth = 1, color = colors[ind],\n             markeredgecolor='k', label = labels[ind])\n\n# remove duplicate labels and set their order\nhandles, labels = plt.gca().get_legend_handles_labels()\nby_label = OrderedDict(zip(labels, handles))\nkey_order = ('1 <  < 100', '100 <  < 1000', '1000 <  < 10000',\n             '10000 <  < 100000', '100000 <  < 1000000')\nnew_label = OrderedDict()\nfor key in key_order:\n    new_label[key] = by_label[key]\nplt.legend(new_label.values(), new_label.keys(), loc = 1, prop= {'size':11},\n           title='Number of flights per year', frameon = True, framealpha = 1)\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**How many flights in different days of months and in different hours of days?**","metadata":{}},{"cell_type":"code","source":"# Create list of flights counts by day of the MONTHS\nflights_per_dayMonths_df = ( df2.groupby(df2.DAY)\n                          .agg({'ID':'count'})\n                          .rename(columns={'ID':'Number of Flights'})\n                          .sort_values(by=['DAY']) )\n\nflights_per_dayMonths_df.plot.bar()\n\n## For the day 31 it's normal because only half the months has a 31_th day","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Which are the top 20 busiest airports (this depends on inbound and outbound traffic)?**","metadata":{}},{"cell_type":"code","source":"plt.rcParams['font.size'] = 12\nplt.rcParams[\"font.weight\"] = \"bold\"\n# Dataframe of the outbound activity for each airport.\nairports_outbound = ( df2.groupby(df2.ORIGIN_AIRPORT, as_index = False)\n                         .agg({'ID':'count'})\n                         .sort_values(by='ID', ascending = False)\n                         .rename(columns={'ORIGIN_AIRPORT' : 'Airport', 'ID' : 'outbound_count'}) )\n## Save the top 20 outbound\ntop_airports_outbound = airports_outbound.head(20)\nprint('TOP 20 AIRPORT FOR OUTBOUND FLIGHTS')\nprint(top_airports_outbound)\n\n\n# Dataframe of the inbound activity for each airport.\nairports_inbound = ( df2.groupby(df2.DESTINATION_AIRPORT, as_index = False)\n                         .agg({'ID':'count'})\n                         .sort_values(by='ID', ascending = False)\n                         .rename(columns={'DESTINATION_AIRPORT' : 'Airport', 'ID' : 'inbound_count'}) )\n## Save the top 20 inbound\ntop_airports_inbound = airports_inbound.head(20)\nprint('TOP 20 AIRPORT FOR INBOUND FLIGHTS')\nprint(top_airports_inbound)\n\n\n# Merge inbound and outbound into one dataframe\nairports_total = pd.merge(airports_outbound, airports_inbound, on=['Airport'])\n## Sum the inbound and outbound and add a column\nairports_total['total'] = airports_total['outbound_count'] + airports_total['inbound_count']\n## Order by the total sum by airport\nairports_total = airports_total[['Airport', 'total']].sort_values(by='total', ascending = False)\n## Save the top 20 total flights\ntop_airports_total = airports_total.head(20)\n\nprint('TOP 20 AIRPORT FOR TOTAL FLIGHTS')\n\nprint(top_airports_total)\n\nfig_dim = (18,18)\nf, ax = plt.subplots(figsize=fig_dim)\n#top_airports_total['Airport']  = top_airports_total['Airport'] .replace(abbr_airport)\n\nquality = top_airports_total['Airport']                    \nsize= top_airports_total['total'] \n\ndef absolute_value(val):\n    a  = np.int(np.round(val/100.*size.sum(), 0))\n    return a\n\nplt.pie(size,labels= quality, autopct=absolute_value)\ncentre_circle = plt.Circle((0,0),0.75,color='white', fc='white',linewidth=1.25)\nfig = plt.gcf()\nfig.gca().add_artist(centre_circle)\n\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Which are the top busiest carriers?**","metadata":{}},{"cell_type":"code","source":"flights_by_carrier = ( df2.groupby(df2.AIRLINE)\n                          .agg({'ID':'count'})\n                          .rename(columns = {'ID' : 'Number of Flights'})\n                          .sort_values(by = 'Number of Flights', ascending = False)\n                          .reset_index()\n                      )\n        \n# Save the top 20 outbound\ntop_carrier = flights_by_carrier.head(20)\n\n# Show\nprint(top_carrier)\nsns.countplot(y=\"AIRLINE\",data=df_2, palette=\"Paired\");","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**What is the percentage of delayed flights (over total flights) for different hours of the day?**","metadata":{}},{"cell_type":"code","source":"# Function to round a timestamp to the nearest hour\ndef rounder(t):\n    # Rounds to nearest hour by adding a timedelta hour if minute >= 30\n    if t.minute >= 30:\n        if t.hour == 23 :\n            return t.replace(second=0, microsecond=0, minute=0, hour=0)\n        else :\n            return t.replace(second=0, microsecond=0, minute=0, hour=t.hour+1)\n    else:\n        return t.replace(second=0, microsecond=0, minute=0)\n\n# We use it to create a new column using this function.\ndf2['SCHEDULED_ARRIVAL_HOUR'] = df2.apply(lambda row : rounder(row.SCHEDULED_ARRIVAL).hour, axis= 1)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Grouping by the hours and count total delayed flights by hours. \n# We define a delay when it's more than 5 minutes\ndelayed_flights_by_hours = ( df2[df2.ARRIVAL_DELAY.astype(int) > 5].groupby(df2.SCHEDULED_ARRIVAL_HOUR)\n                              .agg({'ID':'count'})\n                              .rename(columns = {'ID' : 'Number of Flights'})\n                              .reset_index()\n                              .sort_values(by = ['SCHEDULED_ARRIVAL_HOUR']) )\n# Counting the number of total flights\ntotal_flights = df2.shape[0]\n\n# Now we compute for each hour, the frequency of delayed flights. \ndelayed_flights_by_hours[\"Percent\"] = 100 * delayed_flights_by_hours['Number of Flights'] / total_flights\n\n#Plotting the bar chart\ndelayed_flights_by_hours.plot.bar(x= 'SCHEDULED_ARRIVAL_HOUR', y= 'Percent')\n#_______________________________________________________________________________________\n# import libraries\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport numpy as np\n%matplotlib inline\n\n# set font\nplt.rcParams['font.family'] = 'sans-serif'\nplt.rcParams['font.sans-serif'] = 'Helvetica'\n\n# set the style of the axes and the text color\nplt.rcParams['axes.edgecolor']='#333F4B'\nplt.rcParams['axes.linewidth']=0.8\nplt.rcParams['xtick.color']='#333F4B'\nplt.rcParams['ytick.color']='#333F4B'\nplt.rcParams['text.color']='#333F4B'\n\n# we first need a numeric placeholder for the y axis\nmy_range=list(range(0,len(delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'])))\n\nfig, ax = plt.subplots(figsize=(5, 6))\n\n# create for each expense type an horizontal line that starts at x = 0 with the length \n# represented by the specific expense percentage value.\nplt.hlines(y=delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'], xmin=0, xmax=delayed_flights_by_hours['Percent'], color='#007ACC', alpha=0.2, linewidth=5)\n\n# create for each expense type a dot at the level of the expense percentage value\nplt.plot(delayed_flights_by_hours['Percent'], delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'], \"o\", markersize=5, color='#007ACC', alpha=0.6)\n\n# set labels\nax.set_xlabel('Percent', fontsize=15, fontweight='black', color = '#333F4B')\nax.set_ylabel('Schedualed Arrival Hour', fontsize=15, fontweight='black', color = '#333F4B')\n\n# set axis\nax.tick_params(axis='both', which='major', labelsize=12)\nplt.yticks(my_range, delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'])\n\n\n\n# add an horizonal label for the y axis \nfig.text(-0.23, 0.96, 'Percentage of delayed flights', fontsize=15, fontweight='black', color = '#333F4B')\n\n# change the style of the axis spines\nax.spines['top'].set_visible(False)\nax.spines['right'].set_visible(False)\n\nax.spines['left'].set_position(('outward', 8))\nax.spines['bottom'].set_position(('outward', 5))\n\nplt.savefig('hist2.png', dpi=300, bbox_inches='tight')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**Which hours of the day are characterized by the longest flight delay?**\n","metadata":{}},{"cell_type":"code","source":"delayed_flights_by_hours = ( df2[df2.ARRIVAL_DELAY.astype(int) > 5]\n                              .groupby(df2.SCHEDULED_ARRIVAL_HOUR)\n                              .agg({'ARRIVAL_DELAY':'mean'})\n                              .rename(columns = {'ARRIVAL_DELAY' : 'Mean Arrival Delay'})\n                              .reset_index()\n                              .sort_values(by = ['SCHEDULED_ARRIVAL_HOUR']) )\n\n\n#Plotting the bar chart\ndelayed_flights_by_hours.plot.bar(x= 'SCHEDULED_ARRIVAL_HOUR', y= 'Mean Arrival Delay', color='gold')\n#___________________________________________________________________________________________\n%matplotlib inline\n\n# set font\nplt.rcParams['font.family'] = 'sans-serif'\nplt.rcParams['font.sans-serif'] = 'Helvetica'\n\n# set the style of the axes and the text color\nplt.rcParams['axes.edgecolor']='#333F4B'\nplt.rcParams['axes.linewidth']=0.8\nplt.rcParams['xtick.color']='#333F4B'\nplt.rcParams['ytick.color']='#333F4B'\nplt.rcParams['text.color']='#333F4B'\n\n# we first need a numeric placeholder for the y axis\nmy_range=list(range(0,len(delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'])))\n\nfig, ax = plt.subplots(figsize=(5, 6))\n\n# create for each expense type an horizontal line that starts at x = 0 with the length \n# represented by the specific expense percentage value.\nplt.hlines(y=delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'], xmin=0, xmax=delayed_flights_by_hours['Mean Arrival Delay'], color='#CC7A00', alpha=0.2, linewidth=5)\n\n# create for each expense type a dot at the level of the expense percentage value\nplt.plot(delayed_flights_by_hours['Mean Arrival Delay'], delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'], \"o\", markersize=5, color='#CC7A00', alpha=0.6)\n\n# set labels\nax.set_xlabel('Mean Arrival Delay', fontsize=15, fontweight='black', color = '#333F4B')\nax.set_ylabel('Schedualed Arrival Hour', fontsize=15, fontweight='black', color = '#333F4B')\n\n# set axis\nax.tick_params(axis='both', which='major', labelsize=12)\nplt.yticks(my_range, delayed_flights_by_hours['SCHEDULED_ARRIVAL_HOUR'])\n\n\n\n# add an horizonal label for the y axis \nfig.text(-0.23, 0.96, 'Mean Arrival Delay', fontsize=15, fontweight='black', color = '#333F4B')\n\n# change the style of the axis spines\nax.spines['top'].set_visible(False)\nax.spines['right'].set_visible(False)\n\nax.spines['left'].set_position(('outward', 8))\nax.spines['bottom'].set_position(('outward', 5))\n\nplt.savefig('hist2.png', dpi=300, bbox_inches='tight')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**What is the percentage of delayed flights which depart from one of the top 20 busiest airports?**","metadata":{}},{"cell_type":"code","source":"# Dataframe of the delayed flights of the top 20 airports\ndelayed_flights = df2[df2.ARRIVAL_DELAY.astype(int) > 5]\ntop_airports_list = top_airports_total['Airport'].to_list()\ndelayed_flights_top_airports = ( delayed_flights[\n                                                    delayed_flights['ORIGIN_AIRPORT'].isin(top_airports_list) \n                                                  | delayed_flights['DESTINATION_AIRPORT'].isin(top_airports_list)\n                                                ]\n                               )\n\n# Outbound\ndelayed_flights_top_airports_outbound = ( delayed_flights_top_airports\n                                         .groupby(df2.ORIGIN_AIRPORT)\n                                         .agg({'ID':'count'})\n                                         .sort_values(by='ID', ascending = False)\n                                         .reset_index()\n                                         .rename(columns={'ORIGIN_AIRPORT' : 'Airport', 'ID' : 'outbound_count'}) \n                                        )\n\n\n# Inbound\ndelayed_flights_top_airports_inbound = ( delayed_flights_top_airports\n                                        .groupby(df2.DESTINATION_AIRPORT)\n                                        .agg({'ID':'count'})\n                                        .sort_values(by='ID', ascending = False)\n                                        .reset_index()\n                                        .rename(columns={'DESTINATION_AIRPORT' : 'Airport', 'ID' : 'inbound_count'}) \n                                       )\n# Merge inbound and outbound into one dataframe\ndelayed_flights_top_airports = pd.merge(delayed_flights_top_airports_outbound, \n                                        delayed_flights_top_airports_inbound,\n                                        on= 'Airport'\n                                       ) \n\n# Computing frequency\ndelayed_flights_top_airports['Delay(%)'] = 100 * ( delayed_flights_top_airports['outbound_count'] \n                                            + delayed_flights_top_airports['inbound_count']\n                                            ) / total_flights\n\n# Left join on the top 20 most active airports (delayed + other flights) to keep the top 20 initial order\ndelayed_flights_top_airports = top_airports_total.merge(delayed_flights_top_airports, how= 'left', on= 'Airport')\ndelayed_flights_top_airports['Airport']  = delayed_flights_top_airports['Airport'] .replace(abbr_airport)\n\n\n#print(delayed_flights_top_airports)\n#delayed_flights_top_airports[['Airport', 'Delay(%)']].plot.bar(x= 'Airport', color='red')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%matplotlib inline\ndelayed_flights_top_airports['Airport']  = delayed_flights_top_airports['Airport'] .replace(abbr_airport)\n\n# set font\nplt.rcParams['font.family'] = 'sans-serif'\nplt.rcParams['font.sans-serif'] = 'Helvetica'\n\n# set the style of the axes and the text color\nplt.rcParams['axes.edgecolor']='#333F4B'\nplt.rcParams['axes.linewidth']=0.8\nplt.rcParams['xtick.color']='#333F4B'\nplt.rcParams['ytick.color']='#333F4B'\nplt.rcParams['text.color']='#333F4B'\n\n# we first need a numeric placeholder for the y axis\nmy_range=list(range(0,len(delayed_flights_top_airports['Airport'])))\n\nfig, ax = plt.subplots(figsize=(5, 6))\n\n# create for each expense type an horizontal line that starts at x = 0 with the length \n# represented by the specific expense percentage value.\nplt.hlines(y=delayed_flights_top_airports['Airport'], xmin=0, xmax=delayed_flights_top_airports['Delay(%)'], color='#4E8200', alpha=0.2, linewidth=5)\n\n# create for each expense type a dot at the level of the expense percentage value\nplt.plot(delayed_flights_top_airports['Delay(%)'], delayed_flights_top_airports['Airport'], \"o\", markersize=5, color='#4E8200', alpha=0.6)\n\n# set labels\nax.set_xlabel('Percent', fontsize=15, fontweight='black', color = '#333F4B')\nax.set_ylabel('Top busiest Airport', fontsize=15, fontweight='black', color = '#333F4B')\n\n# set axis\nax.tick_params(axis='both', which='major', labelsize=12)\nplt.yticks(my_range, delayed_flights_top_airports['Airport'])\n\n\n\n# add an horizonal label for the y axis \nfig.text(-0.23, 0.96, ' Percentage of delayed flights', fontsize=15, fontweight='black', color = '#333F4B')\n\n# change the style of the axis spines\nax.spines['top'].set_visible(False)\nax.spines['right'].set_visible(False)\n\nax.spines['left'].set_position(('outward', 8))\nax.spines['bottom'].set_position(('outward', 5))\n\nplt.savefig('hist2.png', dpi=300, bbox_inches='tight')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"**What is the percentage of delayed flights which belong to one of the top 20 busiest carriers?**","metadata":{}},{"cell_type":"code","source":"# Dataframe of the delayed flights of the top 20 airports\ndelayed_flights = df2[df2.ARRIVAL_DELAY.astype(int) > 5]\ntop_carriers_list = top_carrier['AIRLINE'].to_list()\ndelayed_flights_top_carriers = ( delayed_flights[\n                                                    delayed_flights['AIRLINE'].isin(top_carriers_list) \n                                                ]\n                               )\n\n\n# Count number of delayed flights by carriers\ndelayed_flights_top_carriers = ( delayed_flights_top_carriers\n                                        .groupby(df2.AIRLINE)\n                                        .agg({'ID':'count'})\n                                        .sort_values(by='ID', ascending = False)\n                                        .reset_index()\n                                        .rename(columns={'ID' : 'delayed_flights_count'}) \n                                       )\n\n\n# Computing frequency\ndelayed_flights_top_carriers['Frequency'] = 100 * delayed_flights_top_carriers['delayed_flights_count'] / total_flights\n\n# Left join on the top 20 most active carriers (delayed + other flights) to keep the top 20 initial order\ndelayed_flights_top_carriers = top_carrier.merge(delayed_flights_top_carriers, how= 'left', on= 'AIRLINE')\n\nprint(delayed_flights_top_carriers)\ndelayed_flights_top_carriers[['AIRLINE', 'Frequency']].plot.bar(x= 'AIRLINE')","metadata":{"trusted":true},"execution_count":null,"outputs":[]}]}