from plotly import express as px
from keras.models import Sequential
from tensorflow.keras.layers import Dropout, Dense, LSTM
import numpy as np
import pandas as pd
import plotly.io as pio
import matplotlib.pyplot as plt
from sklearn import preprocessing
from sklearn.preprocessing import OneHotEncoder
import sqlite3#sqlite3 python module that lets us interact wtih squile
import tensorflow as tf
from tensorflow.keras.layers.experimental import preprocessing
from tensorflow.keras import layers
import warnings
warnings.filterwarnings('ignore')
from datetime import datetime
import re
import time
import datetime

Flight2016=pd.read_csv("2016.csv")
Flight2015=pd.read_csv("2015.csv")
Flight2017=pd.read_csv("2017.csv")
Flight2018=pd.read_csv("2018.csv")
Flight2014=pd.read_csv("2014.csv")
Flight2013=pd.read_csv("2013.csv")
Flight2012=pd.read_csv("2012.csv")

Flight2016.head()

conn=sqlite3.connect("project.db")#create data base called project
def flight_data_each_year(Flight):
    Flight.fillna(0,inplace=True)#because NA means there is no delay so we can fill it with 0
    LAX=Flight[Flight["ORIGIN"]=="LAX"]#Because there is too much data for each year of takeoff, hence we will study the planes taking off from LAX
    LAX["delay"]=(LAX[["CARRIER_DELAY","WEATHER_DELAY","NAS_DELAY","SECURITY_DELAY","LATE_AIRCRAFT_DELAY"]] != 0).astype(int).sum(axis=1)
    #There are many reasons for flight delay, and we only care whether a flight is delayed, so we need to add all the reasons, 
    #if it is greater than or equal to 1which means it is delayed
    LAX["delay"][LAX.delay>1]=1#Turn multiple reasons into 1
    cols=["FL_DATE","delay"]#
    LAX=LAX[cols]
    LAX.to_sql("LAX", conn, if_exists = "replace", index = False)#put LAX.csv to the data base
    #Call sql to calculate the total number of flights per day and the total number of delayed flights 
    #(because when delay==0, it means there is no delay, so you only need to add all the delays to know the total number of delays)
    cmd = """
       SELECT *, count() AS Total_flight_per_day, SUM(delay) AS Total_Delay_per_day
       FROM LAX
       GROUP BY FL_DATE
       
         """
    LAX_flight = pd.read_sql_query(cmd, conn)
    LAX_flight["punctuality rate"]=1-LAX_flight["Total_Delay_per_day"]/LAX_flight["Total_flight_per_day"]#计算每一天的准点率
    LAX_flight["week"] = ''#Create a new column named week
    for i in range(len(LAX_flight["FL_DATE"])):
        #Call the datetime package to standardize the time to get the day of the week for each day
        LAX_flight["week"][i]=datetime.datetime.strptime(LAX_flight["FL_DATE"].iloc[i],"%Y-%m-%d").isoweekday()
        #Because the output result only corresponds to the number of the week, so we need to convert it into English text
        LAX_flight["week"]=LAX_flight["week"].replace({5:'Friday',
                       1:'Monday',
                       2:'Tuesday',
                       3:'Wednesday',
                       4:'Thursday',
                       6:'Saturday',
                       7:'Sunday'
                                    })
    #Because we need to use a neural network in the end, we need to use OneHotEncode for categorical variables
    encoder = OneHotEncoder(handle_unknown='ignore')
    #perform one-hot encoding on 'week' column 
    encoder_df = pd.DataFrame(encoder.fit_transform(LAX_flight[['week']]).toarray())                         
    # #merge one-hot encoded columns back with original DataFrame
    LAX_flight = LAX_flight.join(encoder_df)
    # #view final df
    #LAX_flight.drop(labels=['week'], axis=1, inplace=True)
    colNameDict = {0:'Friday',
                   1:'Monday',
                   2:'Saturday',
                   3:'Sunday',
                   4:'Thursday',
                   5:'Tuesday',
                   6:'Wednesday',
                  }                  
    LAX_flight.rename(columns = colNameDict,inplace=True)
    LAX_flight.drop(labels=['Total_Delay_per_day','delay'],axis=1, inplace=True)
    return LAX_flight

LAX_flight2016=flight_data_each_year(Flight2016)
LAX_flight2015=flight_data_each_year(Flight2015)
LAX_flight2017=flight_data_each_year(Flight2017)
LAX_flight2018=flight_data_each_year(Flight2018)
LAX_flight2014=flight_data_each_year(Flight2014)
LAX_flight2013=flight_data_each_year(Flight2013)
LAX_flight2012=flight_data_each_year(Flight2012)

LAX_flight=pd.concat([LAX_flight2015,LAX_flight2016,LAX_flight2017,LAX_flight2018])
#Because the amount of data is too large, it is necessary to group the data sets to combine
LAX_flight=pd.concat([LAX_flight,LAX_flight2012,LAX_flight2013,LAX_flight2014])

LAX_flight

fig = px.line(data_frame = LAX_flight2015, # data that needs to be plotted
                 x = "FL_DATE", # column name for x-axis
                 y = "Total_flight_per_day",
                 markers=True,
                 color = "week", # column name for color coding
                 width = 1000,
                 height = 500)

# reduce whitespace
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
# show the plot
fig.show()

fig = px.line(data_frame = LAX_flight2015, # data that needs to be plotted
                 x = "FL_DATE", # column name for x-axis
                 y = "punctuality rate",
                 markers=True,
                 color = "week", # column name for color coding
                 width = 1000,
                 height = 500)

# reduce whitespace
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
# show the plot
fig.show()
pio.write_html(fig, file='flight2.html', auto_open=True)

weather = pd.read_csv("weather2012_2018.csv") 

weather.head()

def weather_data_each_year(Weather):
    Weather.fillna(0,inplace=True)
    Weather['local_time LA (airport)'] = pd.to_datetime(Weather['local_time LA (airport)'])#normalize time
    Weather["Day_time"]=Weather["local_time LA (airport)"].apply(lambda x:str(x)[0:10])#only need day don't care about hours and min
    cols=["Day_time",'local_time LA (airport)',"Ff","N","RRR","VV"]
    Weather=Weather[cols]
    Weather.columns = ["Day_time",'Observation_time', 'wind_speed',"cloud", 'precipitation', 'visibility']#rename
    Weather["precipitation"]=Weather["precipitation"].replace("Signs of precipitation", 0)
    Weather["cloud"]=Weather["cloud"].replace({'100%.':1,#Convert percentages to corresponding decimals for subsequent calculations
                          '70 -80%.':0.75,
                          '50%.':0.5,
                          0:0,
                         'The sky is not visible due to fog and/or other meteorological phenomena.':0,
                         '20-0%.':0.1,
                          'Cloudless':0
                                   })
    Weather.to_sql("Weather", conn, if_exists = "replace", index = False)#put Weather.csv to the data base"\
    #cmd will calculate average wind speed, average visibility, total precipitation per day
    cmd = """
   
       SELECT Day_time,AVG(wind_speed) AS wind_speed_perday,AVG(visibility) AS visibility_perday, AVG(cloud) AS cloud_perday ,SUM(precipitation) AS precipitation_perday
       FROM Weather
       GROUP BY Day_time
       
         """
    LAX_weather= pd.read_sql_query(cmd, conn)
    #Convert real cloud cover according to real weather
    for i in range(len(LAX_weather["cloud_perday"])):
        if(LAX_weather["cloud_perday"][i]==0):
            LAX_weather["cloud_perday"][i]='cloudless'
        elif(LAX_weather["cloud_perday"][i]<0.3):
            LAX_weather["cloud_perday"][i]='less cloudy'
        elif(LAX_weather["cloud_perday"][i]<0.7):
            LAX_weather["cloud_perday"][i]='cloudy'
        else:
            LAX_weather["cloud_perday"][i]="overcast"  
    #Convert precipitation to heavy, medium and light rain
    for i in range(len(LAX_weather["precipitation_perday"])):
        if(LAX_weather["precipitation_perday"][i]==0):
            LAX_weather["precipitation_perday"][i]="no_rain"
        elif(LAX_weather["precipitation_perday"][i]<10):
            LAX_weather["precipitation_perday"][i]="light_rain"
        elif(LAX_weather["precipitation_perday"][i]<25):
            LAX_weather["precipitation_perday"][i]="moderate_rain"
        else:
            LAX_weather["precipitation_perday"][i]="heavy_rain"   
    #creating instance of one-hot-encoder
    encoder = OneHotEncoder(handle_unknown='ignore')

    #perform one-hot encoding on ['cloud_perday','precipitation_perday'] column 
    encoder_df = pd.DataFrame(encoder.fit_transform(LAX_weather[['cloud_perday','precipitation_perday']]).toarray())

    #merge one-hot encoded columns back with original DataFrame
    LAX_weather = LAX_weather.join(encoder_df)

    #view final df
    LAX_weather.drop(labels=['cloud_perday','precipitation_perday'], axis=1, inplace=True)
    colNameDict = {0:'cloudless',
                   1:'cloudy',
                   2:'fog',
                   3:'less cloudy',
                   4:'overcast',
                   5:'heavy_rain',
                   6:'light_rain',
                   7:'moderate_rain',
                   8:'no_rain'
                  }                  
    LAX_weather.rename(columns = colNameDict,inplace=True)
    return LAX_weather

LAX_weather=weather_data_each_year(weather)
LAX_weather

LAX_weather.to_sql("LAX_weather", conn, if_exists = "replace", index = False)#put temps.csv to the data base"
LAX_flight.to_sql("LAX_flight", conn, if_exists = "replace", index = False)#put temps.csv to the data base"

cmd = """
   SELECT *
   FROM LAX_flight
   LEFT JOIN LAX_weather ON LAX_weather.Day_time = LAX_flight.FL_DATE
       
     """
    
data = pd.read_sql_query(cmd, conn)

#min max normalization
for column in data[["wind_speed_perday","visibility_perday","Total_flight_per_day"]]:
    data[column] = (data[column] - data[column].min()) / (data[column].max() - data[column].min())    

sorted_df = data.sort_values(by='FL_DATE')

sorted_df=sorted_df.drop(labels=["Day_time","week"],axis=1)

sorted_df 

# 60% train, 20% validation, 20% test

train_size = int(0.6*len(sorted_df )) 
val_size = int(0.2*len(sorted_df ))

train = sorted_df [:train_size]
val = sorted_df [train_size : train_size + val_size]
test = sorted_df [train_size + val_size:]

from sklearn.model_selection import train_test_split
#split into X and y
X_train=train.drop(['Total_flight_per_day','punctuality rate','FL_DATE'],axis=1)
y_train=train[['Total_flight_per_day','punctuality rate']]
#split into X and y
X_val=val.drop(['Total_flight_per_day','punctuality rate','FL_DATE'],axis=1)
y_val=val[['Total_flight_per_day','punctuality rate']]
#split into X and y
X_test=test.drop(['Total_flight_per_day','punctuality rate','FL_DATE'],axis=1)
y_test=test[['Total_flight_per_day','punctuality rate']]

def create_sequence(values, time_steps):
    output = []
    for i in range(len(values) - time_steps):
        output.append(values[i : (i + time_steps)])
    return np.stack(output)

X_train = create_sequence(X_train, 7)
X_test  = create_sequence(X_test, 7)
X_val = create_sequence(X_val, 7)
y_train = y_train[-X_train.shape[0]:]
y_test  = y_test[-X_test.shape[0]:]
y_val = y_test[-X_val.shape[0]:]

X_train.shape

from tensorflow.keras.layers import Bidirectional
model = Sequential()
# Adding a LSTM layer
model.add(LSTM(32,return_sequences=False,input_shape=(X_train.shape[1],X_train.shape[-1])))
model.add(Dense(2))
model.add(tf.keras.layers.Activation('sigmoid'))
model.compile(loss='binary_crossentropy',optimizer='AdaDelta',metrics=['accuracy'])

model.summary()

callback = tf.keras.callbacks.EarlyStopping(monitor='loss', patience=20)
history = model.fit(X_train,
                    y_train,
                    validation_data=(X_val, y_val),
                    epochs = 500,
                    verbose = False,
                    batch_size=50, 
                    callbacks=[callback]
)

plt.plot(history.history['loss'])
plt.plot(history.history['val_loss'])
plt.title('model train vs validation loss')
plt.ylabel('loss')
plt.xlabel('epoch')
plt.legend(['train','testing'], loc='upper right')
plt.show()

import matplotlib.pyplot as plt
plt.plot(history.history["accuracy"], label = "training")
plt.plot(history.history["val_accuracy"], label = "vaildation")
plt.gca().set(xlabel = "epoch", ylabel = "accuracy")
plt.legend()

model.evaluate(X_test,y_test)

@app.route('/Data_preparation/')
def Data_preparation():
    df0 = pd.read_csv(r"data\flights_original.csv").iloc[:,:10]
    df1 = web_helper.LAX_table()
    df2 = web_helper.weather_data()
    df3 = web_helper.weather_data_each_year(pd.read_csv(r"data\weather2012_2018.csv"))
    df4 = web_helper.merge_data(df3, df1)
    return render_template('Data_preparation.html', table0=[df0.iloc[:10].to_html(classes='data', header="true")],
                                                    table1=[df1.iloc[:10].to_html(classes='data', header="true")],
                                                    table2=[df2.iloc[:10].to_html(classes='data', header="true")],
                                                    table3=[df3.iloc[:10].to_html(classes='data', header="true")],
                                                    table4=[df4.iloc[:10].to_html(classes='data', header="true")])

<figure class="table">
  
  <figcaption><small>Original Flights Data</small></figcaption>
</figure>

def plotly_flights(year):
    LAX_flight = pd.read_csv(r"data\LAX_flight.csv")

    fig = px.line(data_frame = LAX_flight[LAX_flight['FL_DATE'].str.contains(pat = str(year))], # data that needs to be plotted
                 x = "FL_DATE", # column name for x-axis
                 y = "Total_flight_per_day",
                 markers=True,
                 color = "week", # column name for color coding
                 width = 800,
                 height = 500)

    # reduce whitespace
    fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
    # show the plot
    return fig

fig1 = web_helper.plotly_flights(request.form["year"])
graphJSON1 = json.dumps(fig1, cls=plotly.utils.PlotlyJSONEncoder)
return render_template('Data_preparation.html', graphJSON_object = graphJSON1)

<span id="chart1"></span>

<script src='https://cdn.plot.ly/plotly-latest.min.js'></script>
<script type='text/javascript'>
  var graphs1 = ;
  Plotly.newPlot('chart1',graphs1,{});
</script>