# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: 'Python 3.9.5 64-bit (''ga15pyd'': conda)'
#     name: python3
# ---

# # Flights Table Builder
#
# By <NAME>
#
# Released under the MIT license
#
#
# This program converts various source files into a table from which airline route maps can be generated.
#
#
# This program also makes use of the Global Airport Database, which is copyrighted by Arash Partow.
#
# "Free use of The Global Airport Database is permitted under the guidelines and in accordance with the MIT License." (See https://www.partow.net/miscellaneous/airportdatabase/index.html#GlobalAirportDatabaseLicense )
#
#
# **Note**: The following files were not included in the GitHub repository because their size exceeds 100MB:
# 1. routes_planes_coordinates.csv
# 2. 21503323_T_T100_SEGMENT_ALL_CARRIER.csv
# 3. routes_planes_coordinates_for_mapping_2018.csv
#
# You can instead retrieve these files from my copy of this project on Google Drive, available at https://drive.google.com/drive/folders/1jRTjoZtT6OWCXRTNstG9D4CxmjXe4W8q?usp=sharing .

# ### Downloading flight data from the Bureau of Transportation Statistics
#
# The Bureau of Transportation Statistics (BTS) is a **great** resource for flight information because the data is entirely free. As its website states, "There are no copyright restrictions or charges for use on DOT publications." https://transportation.libanswers.com/faq/156950
#
# For this program, I downloaded a .csv file containing "Carriers : T-100 Segment (All Carriers)" data from all available months in 2018 (or another year of your choice).
#
# To find this data, go to the Bureau of Transportation Statistics' Transtats homepage (https://www.transtats.bts.gov/Homepage.asp), then select "Aviation" under the "Data Finder" list on the left of the page. Next, select "Air Carrier Statistics (Form 41 Traffic)- All Carriers", and within that list, select "T-100 Segment (All Carriers)". This link may work: https://www.transtats.bts.gov/Fields.asp?gnoyr_VQ=FMG
#
# Once you're on the T-100 Segment (All Carriers) page, you can download the database by selecting "Download" within the Data Tools list on the left. You can click "Select All Fields" to choose all available fields, or you can choose individual fields that you'd like to keep.
#
# Once you click download, it may take the webpage quite a bit of time (e.g. a minute or longer) to begin the download process (perhaps because it needs to compile and zip the data beforehand), so be patient and don't close your browser!
#
# I believe I downloaded all available fields (although you have the option to select only certain ones).

# First, I'll import several libraries and then read in a list of airports from the Global Airport Database.

# +
import pandas as pd
import numpy as np

data_year = '2018' # The year in which the flights in the BTS air travel
# dataset took place. This value will be added to the file name of
# the .csv file containing route data.
data_file = '21503323_T_T100_SEGMENT_ALL_CARRIER.csv' # the BTS data to be
# imported into the program

airport_list = pd.read_csv('original_data\\GlobalAirportDatabase.txt',
delimiter=':',names=['icao_code', 'iata_code', 'airport_name', 'city/town', 
'country', 'lat_deg', 'lat_min', 'lat_sec', 'lat_dir', 'lon_deg', 'lon_min', 
'lon_sec', 'lon_dir', 'altitude', 'latitude_dec_deg', 'longitude_dec_deg']) 
# Headers are not included in the CSV, so they are instead based on those in the
# dictionary here:
# https://www.partow.net/miscellaneous/airportdatabase/index.html )

airport_list
# -

# Next, I'll create two lists that feature each airport's IATA code, latitude, and longitude. These lists will then be merged into flight data from the Bureau of Transportation Statistics.

origin_airport_list_for_merge = airport_list[['iata_code', 'latitude_dec_deg', 
'longitude_dec_deg']].copy()
origin_airport_list_for_merge.rename(columns={'iata_code': 'origin_iata_code',
'latitude_dec_deg':'origin_lat','longitude_dec_deg':'origin_lon'},inplace=True)
origin_airport_list_for_merge

destination_airport_list_for_merge = airport_list[['iata_code', 
'latitude_dec_deg', 'longitude_dec_deg']].copy()
destination_airport_list_for_merge.rename(columns={'iata_code': 
'destination_iata_code','latitude_dec_deg':'destination_lat',
'longitude_dec_deg':'destination_lon'},inplace=True)
destination_airport_list_for_merge

# Next, I'll import a csv file containing flight data that I downloaded from the Bureau of Transportation Statistics:

route_list = pd.read_csv(
    'original_data\\'+data_file, 
    low_memory = False) 
route_list

# Here are the fields available within this list:

route_list.columns

# The same page from which I downloaded the routes data also contains links to lookup tables that provide translations of aircraft codes. In the following cell, I import these codes into DataFrames that can then be merged with the routes table. 

aircraft_type = pd.read_csv('original_data\\L_AIRCRAFT_TYPE.csv')
aircraft_type.rename(columns={'Description':'Plane_Type_Text'},inplace=True)
aircraft_group = pd.read_csv('original_data\\L_AIRCRAFT_GROUP.csv')
aircraft_group.rename(columns={'Description':'Plane_Group_Text'},inplace=True)
aircraft_config = pd.read_csv('original_data\\L_AIRCRAFT_CONFIG.csv')
aircraft_config.rename(columns={'Description':'Plane_Config_Text'},inplace=True)
aircraft_type

# Next, I'll merge all of these tables together. Note that both the origin and destination airport lists are merged onto the routes table; this allows for the coordinates of both airport types to be plotted when creating route maps.

df_routes_planes_airports = route_list.merge(aircraft_type, how = 'left', 
left_on = 'AIRCRAFT_TYPE', right_on = 'Code')
df_routes_planes_airports = df_routes_planes_airports.merge(aircraft_group, 
how = 'left', left_on = 'AIRCRAFT_GROUP', right_on = 'Code')
df_routes_planes_airports = df_routes_planes_airports.merge(aircraft_config, 
how = 'left', left_on = 'AIRCRAFT_CONFIG', right_on = 'Code')
df_routes_planes_airports = df_routes_planes_airports.merge(
    origin_airport_list_for_merge, how = 'left', left_on = 'ORIGIN',
    right_on = 'origin_iata_code') 
df_routes_planes_airports = df_routes_planes_airports.merge(
    destination_airport_list_for_merge, how = 'left', left_on = 'DEST',
    right_on = 'destination_iata_code') 
df_routes_planes_airports

df_routes_planes_airports.to_csv('routes_planes_coordinates.csv')

# This file will now be copied and modified to make it more suitable for route map creation. 
#
#
# First, to prevent flights from 'Null Island' (https://en.wikipedia.org/wiki/Null_Island) from being displayed and to prevent other mapping errors, I'll remove rows with missing airport data and with coordinates of 0. (This assumes that no airport is exactly on the equator or on the prime meridian.)

df_routes_planes_airports_for_mapping = df_routes_planes_airports.copy()
df_routes_planes_airports_for_mapping.dropna(axis=0,subset=['origin_iata_code',
 'destination_iata_code', 'destination_lat', 'destination_lon'],inplace=True) 
df_routes_planes_airports_for_mapping = df_routes_planes_airports_for_mapping.query(
    "origin_lat != 0 and origin_lon != 0 and destination_lat != 0 \
        and destination_lon != 0")
df_routes_planes_airports_for_mapping.reset_index(
    drop=True,inplace=True)
    

# The following cell creates a new column showing the origin and destination airports separated by an underscore. These airports are sorted alphabetically, which allows flights from Airport B to Airport A and from Airport A to Airport B to be consolidated together within the route mapping notebook. Using np.where() as opposed to a for loop saved a considerable amount of time.
#
#

# +
df_routes_planes_airports_for_mapping['ORIGIN_DEST'] = \
np.where(df_routes_planes_airports_for_mapping['ORIGIN'] < \
df_routes_planes_airports_for_mapping['DEST'], 
df_routes_planes_airports_for_mapping['ORIGIN'] + '_' + \
df_routes_planes_airports_for_mapping['DEST'], 
df_routes_planes_airports_for_mapping['DEST'] + \
'_' + df_routes_planes_airports_for_mapping['ORIGIN'])

# The following for loop took over 23 minutes to run, 
# whereas the np.where() approach took 0.2 seconds!
# df_routes_planes_airports_for_mapping_v2.reset_index(drop=True,inplace=True)
# df_routes_planes_airports_for_mapping_v2['ORIGIN_DEST'] = ''
# loop_start_time = time.time()
# for i in range(len(df_routes_planes_airports_for_mapping_v2)):
#     if i % 1000 == 0:
#         print("Now on row",i)
#         print("elapsed time (in seconds):", time.time() - loop_start_time)
#     origin_airport = \
#     df_routes_planes_airports_for_mapping_v2.iloc[i, 
#     df_routes_planes_airports_for_mapping_v2.columns.get_loc('ORIGIN')]
#     destination_airport = \
#     df_routes_planes_airports_for_mapping_v2.iloc[i, 
#     df_routes_planes_airports_for_mapping_v2.columns.get_loc('DEST')]
#     if origin_airport < destination_airport:
#         origin_dest = origin_airport+'_'+destination_airport
#     else:
#         origin_dest = destination_airport+'_'+origin_airport
#     df_routes_planes_airports_for_mapping_v2.iloc[i, 
#     df_routes_planes_airports_for_mapping_v2.columns.get_loc('ORIGIN_DEST')] = \
#             origin_dest

# -

df_routes_planes_airports_for_mapping

# Finally, I'll save this file as a .csv so that it can be imported into route_maps_builder_v2.ipynb.

df_routes_planes_airports_for_mapping.to_csv(
    'routes_planes_coordinates_for_mapping_'+data_year+'.csv')

#

StarcoderdataJupyterScriptsDedupFiltered