<jupyter_start><jupyter_text>add polynomial. rigdge<jupyter_code>import pandas as pd
from datetime import datetime
import time
from sqlalchemy import create_engine
import numpy as np
from datetime import datetime, timezone
import pytz
# Google Sheet management
from google.colab import drive
from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials
zone_mtl = pytz.timezone('America/Montreal')
date_mtl = datetime.now(zone_mtl)
date_mtl<jupyter_output><empty_output><jupyter_text>@ Google Sheet Management  distribution of delays. small, large. Arrival delays and nice uniform delays.   load from Google Sheet<jupyter_code>from google.colab import drive
from google.colab import auth
auth.authenticate_user()
import gspread
from oauth2client.client import GoogleCredentials
drive.mount('/gdrive') 


file_path ='/gdrive/MyDrive/lhl/Mid_Term/flights_tests_filtered.csv'
df_test_filtered = pd.read_csv(file_path)#
df_test_filtered.info()


# save_to = '/gdrive/MyDrive/lhl/Mid_Term/flights_tests_filtered.csv'

# df_test_filtered.to_csv(save_to)

# for the mapping 
file_path ='/gdrive/MyDrive/lhl/Mid_Term/flights_18-19_OCT-NOV-DEC.csv'

# df = pd.read_csv(file_path,header=0,skiprows=range(1,6000000),nrows=3000000)
df_iatacode = pd.read_csv(file_path)#<jupyter_output><empty_output><jupyter_text>Air Port Mapping<jupyter_code>airportMapping = df_test_filtered.groupby("origin_airport_id").first()["origin"]

def get_origin_airport_code(self):
     origin_airport_iata = int(self['origin_airport_id'])

     return airportMapping[origin_airport_iata]


df_test_filtered['origin']= df_test_filtered.apply(get_origin_airport_code, axis=1)

def get_dest_airport_code(self):
     dest_airport_iata = int(self['dest_airport_id'])

     return airportMapping[dest_airport_iata]


df_test_filtered['dest']= df_test_filtered.apply(get_dest_airport_code, axis=1)
df_test_filtered['fl_date'] = df_test_filtered['fl_date'].astype(str)<jupyter_output>/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:9: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  if __name__ == '__main__':
/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:17: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy<jupyter_text>Weather   Weather from Google Drive<jupyter_code>weather_file_path = '/gdrive/MyDrive/lhl/Mid_Term/weatherDB2019final3.json'
import json
with open(weather_file_path, 'r') as fp:
    weatherDatabase2019 = json.load(fp)
    
weather_file_path2 = '/gdrive/MyDrive/lhl/Mid_Term/weatherDB2018.json'

with open(weather_file_path2, 'r') as fp2:
    weatherDatabase2018 = json.load(fp2)

weatherDatabase2019["DFW"]["2019-12-12"]
weatherDatabase2018["DFW"]["2018-12-12"]
WDB = {"2018":weatherDatabase2018,"2019":weatherDatabase2019,"2020":weatherDatabase2019}
def getwindspeedKmphOrig(frame):
    depTime = frame["crs_dep_time"]
    date = frame["fl_date"] # needs to be strings. 
    year = date.split('-')[0]
    origin = frame["origin"]
    depTime = depTime // 100 * 100
    if depTime == 2400:
        depTime = 2300    
    return int(WDB[year][origin][date]['time'][str(depTime)]["windspeedKmph"])

df_test_filtered["origwindspeedKmph"] = df_test_filtered.apply(getwindspeedKmphOrig, axis=1)

def getwindspeedKmphDest(frame):
    arrTime = frame["crs_arr_time"]
    date = frame["fl_date"] # needs to be strings. 
    year = date.split('-')[0]
    dest = frame["dest"]
    arrTime = arrTime // 100 * 100
    if arrTime == 2400:
        arrTime = 2300  
    return int(WDB[year][dest][date]['time'][str(arrTime)]["windspeedKmph"])

df_test_filtered["destwindspeedKmph"] = df_test_filtered.apply(getwindspeedKmphDest, axis=1)

def getTemperatureOrig(frame):
    date = frame["fl_date"] # needs to be strings. 
    origin = frame["origin"]
    year = date.split('-')[0]
    return int(WDB[year][origin][date]["avgtempC"])
    
df_test_filtered["origAvgTempC"] = df_test_filtered.apply(getTemperatureOrig, axis=1)


def getTemperatureDest(frame):
    date = frame["fl_date"]
    dest = frame["dest"]
    year = date.split('-')[0]
    return int(WDB[year][dest][date]["avgtempC"])

df_test_filtered["destAvgTempC"] = df_test_filtered.apply(getTemperatureDest, axis=1)


def getWeatherCodeOrig(frame):
    depTime = frame["crs_dep_time"]
    depTime = depTime // 100 * 100
    if depTime == 2400:
        depTime = 2300    
    date = frame["fl_date"]
    orig = frame["origin"]
    year = date.split('-')[0]
    return WDB[year][orig][date]["time"][str(depTime)]["weatherCode"]


df_test_filtered["origWeatherCode"] = df_test_filtered.apply(getWeatherCodeOrig, axis=1)


def getWeatherCodeDest(frame):
    arrTime = frame["crs_arr_time"]
    arrTime = arrTime // 100 * 100
    
    if arrTime == 2400:
        arrTime = 2300
    #We assume arrival date is the same as departure date
    date = frame["fl_date"]
    dest = frame["dest"]
    year = date.split('-')[0]
    return WDB[year][dest][date]["time"][str(arrTime)]["weatherCode"]


df_test_filtered["destWeatherCode"] = df_test_filtered.apply(getWeatherCodeDest, axis=1)


def getVisibilityDest(frame):
    arrTime = frame["crs_arr_time"]
    arrTime = arrTime // 100 * 100
    if arrTime == 2400:
        arrTime = 2300
    #We assume arrival date is the same as departure date
    date = frame["fl_date"]
    dest = frame["dest"]
    year = date.split('-')[0]
    return int(WDB[year][dest][date]["time"][str(arrTime)]["visibility"])

df_test_filtered["destVisibility"] = df_test_filtered.apply(getVisibilityDest, axis=1)


def getVisibilityOrig(frame):
    depTime = frame["crs_dep_time"]
    depTime = depTime // 100 * 100
    if depTime == 2400:
        depTime = 2300
    #We assume arrival date is the same as departure date
    date = frame["fl_date"]
    orig = frame["origin"]
    year = date.split('-')[0]
    return int(WDB[year][orig][date]["time"][str(depTime)]["visibility"])

df_test_filtered["origVisibility"] = df_test_filtered.apply(getVisibilityOrig, axis=1)


def getPrecipMMOrig(frame):
    depTime = frame["crs_dep_time"]
    depTime = depTime // 100 * 100
    if depTime == 2400:
        depTime = 2300
    #We assume arrival date is the same as departure date
    date = frame["fl_date"]
    orig = frame["origin"]
    year = date.split('-')[0]
    return float(WDB[year][orig][date]["time"][str(depTime)]["precipMM"])

df_test_filtered["origPrecipMM"] = df_test_filtered.apply(getPrecipMMOrig, axis=1)


def getPrecipMMDest(frame):
    destTime = frame["crs_arr_time"]
    destTime = destTime // 100 * 100
    if destTime == 2400:
        destTime = 2300
    #We assume arrival date is the same as departure date
    date = frame["fl_date"]
    dest = frame["dest"]
    year = date.split('-')[0]
    return float(WDB[year][dest][date]["time"][str(destTime)]["precipMM"])

df_test_filtered["destPrecipMM"] = df_test_filtered.apply(getPrecipMMDest, axis=1)<jupyter_output>/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:13: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  del sys.path[0]
/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:25: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:33: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.or[...]<jupyter_text>features we want to pass<jupyter_code>dfFinal = df_test_filtered[['destPrecipMM','origPrecipMM','origVisibility','destVisibility','destWeatherCode','origWeatherCode','origAvgTempC','destAvgTempC','dest_airport_id','origin_airport_id','tail_num','distance','crs_elapsed_time',"crs_dep_time", "crs_arr_time", "fl_date", "origin", "dest"]]<jupyter_output><empty_output><jupyter_text>Feature engineering<jupyter_code>#convert fl_date from string to date
dfFinal['fl_date'] = pd.to_datetime(dfFinal['fl_date'], format='%Y-%m-%d')
#Creating new columns for years n months 
# df['year'] = pd.DatetimeIndex(df['fl_date']).year
# df['month'] = pd.DatetimeIndex(df['fl_date']).month

dfFinal['year']=dfFinal['fl_date'].dt.year 
dfFinal['month']=dfFinal['fl_date'].dt.month 
dfFinal['day']=dfFinal['fl_date'].dt.day

dfFinal['dayofweek_num']=dfFinal['fl_date'].dt.dayofweek
list(dfFinal['month'].unique())




save_to = '/gdrive/MyDrive/lhl/Mid_Term/flights_test_final_df.csv'

dfFinal.to_csv(save_to)<jupyter_output>/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  
/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:7: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  import sys
/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:8: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pan[...]
{'fraction_non_alphanumeric': 0.13018035303146586, 'fraction_numerical': 0.02436684574059862, 'mean_word_length': 5.310532687651332, 'pattern_counts': {'":': 3, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '211492', 'license': 'MIT', 'n_tokens_mistral': 3939, 'n_tokens_neox': 3657, 'n_words': 711, 'path': 'notebooks/Mid_Term_Test_Data_Transformation.ipynb', 'repo_name': 'Some7hing0riginal/lighthouselabs-midterm1', 'size': '10424'}