# date lib
from datetime import datetime

# Pandas and NumPy
import pandas as pd
import numpy as np

# 1. Flight delay: any flight with (real_departure - planned_departure >= 15 minutes)
# 2. The Brazilian Federal Agency for Civil Aviation (ANAC) does not define exactly what is a "flight delay" (in minutes)
# 3. Anyway, the ANAC has a resolution for this subject: https://goo.gl/YBwbMy (last access: nov, 15th, 2017)
# ---
# DELAY, for this analysis, is defined as greater than 15 minutes (local flights only)
DELAY = 15

"""
Explanation: Flight delays (capstone project)
Data wrangling (kind of), some dataset merge process etc...
End of explanation
"""


#[flights] dataset_01 => all "Active Regular Flights" from 2017, from january to september
#source: http://www.anac.gov.br/assuntos/dados-e-estatisticas/historico-de-voos
#Last access this website: nov, 14th, 2017
flights = pd.read_csv('data/arf2017ISO.csv', sep = ';', parse_dates = True)

flights.head()

# Airports four-letters-code from object to string
flights['airportA'] = flights['airportA'].astype('str')
flights['airportB'] = flights['airportB'].astype('str')

flights.info()

"""
Explanation: Local flights dataset. For now, only flights from jan to sep, 2017...
A note about date columns on this dataset
* In the original dataset (CSV file from ANAC), the date was not in ISO8601 format (e.g. '2017-10-31 09:03:00')
* To fix this I used regex (regular expression) to transform this column directly on CSV file
* The original date was "31/10/2017 09:03" (october, 31, 2017 09:03)
The regex:
 find dates with 
"([0-9]{2})/([0-9]{2})/([0-9]{4}) ([0-9]{2}):([0-9]{2})", and
* replace with 
"\3-\2-\1 \4:\5:00*"
End of explanation
"""


# Airports dataset: all brazilian public airports (updated until october, 2017)
airports = pd.read_csv('data/brazilianPublicAirports-out2017.csv', sep = ';')

airports.info()

airports.head()

"""
Explanation: Local airports (list with all the ~600 brazilian public airports)
Source: https://goo.gl/mNFuPt (a XLS spreadsheet in portuguese; last access on nov, 15th, 2017)
End of explanation
"""


# ------------------------------------------------------------------
# List of codes (two letters) used to justify a delay on the flight
# - delayCodesShortlist.csv: list with YYY codes
# - delayCodesLongList.csv: list with XXX codes
# ------------------------------------------------------------------
delaycodes = pd.read_csv('data/delayCodesShortlist.csv', sep = ';')
delaycodesLongList = pd.read_csv('data/delayCodesLonglist.csv', sep = ';')

delaycodes.head()

"""
Explanation: List of codes (two letters) used when there was a flight delay (departure)
I have found two lists that define two-letter codes used by the aircraft crew to justify the delay of the flights: a short and a long one.
Source: https://goo.gl/vUC8BX (last access: nov, 15th, 2017)
End of explanation
"""


# Transform columns with date/time from 'object' to date format
flights['departureest'] = pd.to_datetime(flights['departureest'],errors='ignore')
flights['departurereal'] = pd.to_datetime(flights['departurereal'],errors='ignore')
flights['arrivalest'] = pd.to_datetime(flights['arrivalest'],errors='ignore')
flights['arrivalreal'] = pd.to_datetime(flights['arrivalreal'],errors='ignore')

# Merge "flights" dataset with "airports" in order to identify 
#   local flights (origin and destination are in Brazil)
flights = pd.merge(flights, airports, left_on="airportA", right_on="airport", how='left')

flights = pd.merge(flights, airports, left_on="airportB", right_on="airport", how='left')

flights.head()

"""
Explanation: Merging datasets and adjusting the data
End of explanation
"""


dfFlights = flights[['airportA']]

dfAirports = airports[['airport']]

dfFlights.info()

dfAirports.info()

display('dfFlights', 'dfAirports', pd.merge(dfFlights, dfAirports, left_on="airportA", right_on="airport"))

# Not working: 
# pd.merge('dfFlights', 'dfAirports', pd.merge(dfFlights, dfAirports, left_on='airportA', right_on='airport'))
# ------------------------

# A more easy attempt
df1 = pd.DataFrame({'airportA': ['SBBH', 'SBCG', 'SBKP', 'KMIA', 'HONO'],
                    'airportB': ['SBBR', 'SBCT', 'SBAM', 'GERM', 'ENGL']},
                   columns=['airportA', 'airportB'])
df2 = pd.DataFrame({'airport': ['SBBH', 'SBCG', 'SBKP', 'SBBR', 'SBCT', 'SBAM'],
                    'state': ['MG', 'SP', 'SP', 'DF', 'PR', 'AM']},
                   columns=['airport', 'state'])

df1

df2

display('df1', 'df2', pd.merge(df1, df2, left_on="airportA", right_on="airport", how='left'))

df3 = pd.merge(df1, df2, left_on="airportA", right_on="airport", how='left')

df3

display('df3', 'df2', pd.merge(df3, df2, left_on="airportB", right_on="airport", how='left'))

"""
Explanation: --- Sandbox. Test area: Danger! Do Not Trepass Beyond This Point! --
End of explanation
"""


sampleFlights = pd.read_csv('data/sampleFlights.csv', sep = ';', parse_dates = True)
sampleAirports = pd.read_csv('data/sampleAirports.csv', sep = ';', parse_dates = True)

sampleFlights.head()

sampleAirports.head()

display('sampleFlights', 'sampleAirports', 
        pd.merge(sampleFlights, sampleAirports, left_on="airportA", right_on="airport", how='left'))

sampleMergeTemp = pd.merge(sampleFlights, sampleAirports, left_on="airportA", right_on="airport", how='left')

sampleMergeTemp

display('sampleMergeTemp', 'sampleAirports', 
        pd.merge(sampleMergeTemp, sampleAirports, left_on="airportB", right_on="airport", how='left'))

sampleMergeFinal = pd.merge(sampleMergeTemp, sampleAirports, left_on="airportB", right_on="airport", how='left')

sampleMergeFinal

"""
Explanation: More tests...
End of explanation
"""


