<jupyter_start><jupyter_text>Feature Selection and Data Cleaning GoalsThe goal of this notebook is to provide functions to:- clean data- remove unneccessary/undesired features- create dummy variable- provide basis for feature engineering --- **INPUT**: flights csv with nulls removed **OUTPUT**: flights cleaned csv  **Next Steps**: obtain scaled data ---  Variables to work with based on flights_test information **fl_date**: Flight Date (yyyy-mm-dd)- split into 3 columns:   - fl_month   - fl_day_of_week   - fl_week_of_month**mkt_unique_carrier**: Unique Marketing Carrier Code. When the same code has been used by multiple carriers, a numeric suffix is used for earlier users, for example, PA, PA(1), PA(2). Use this field for analysis across a range of years.- combine with op_unique_carrier    - Label Encode**branded_code_share**: Reporting Carrier Operated or Branded Code Share Partners- Detemine is or is not "codeshare"- codeshare = 1- not codeshare = 0**mkt_carrier**: Code assigned by IATA and commonly used to identify a carrier. As the same code may have been assigned to different carriers over time, the code is not always unique. For analysis, use the Unique Carrier Code.- drop this information**mkt_carrier_fl_num**: Flight Number- determine flight type**op_unique_carrier**: Unique Scheduled Operating Carrier Code. When the same code has been used by multiple carriers, a numeric suffix is used for earlier users,for example, PA, PA(1), PA(2). Use this field for analysis across a range of years.- combine with mkt_unique_carrier    - Label Encode**tail_num**: Tail Number- drop this value**op_carrier_fl_num**: Flight Number- drop this value**origin_airport_id**: Origin Airport, Airport ID. An identification number assigned by US DOT to identify a unique airport. Use this field for airport analysis across a range of years because an airport can change its airport code and airport codes can be reused.- drop this value**origin**: Origin Airport- Label Encode**origin_city_name**: Origin Airport, City Name- plan to drop this column- option to create a state variable from this as indicated by a a two letter abreviation     - move as dummy**dest_airport_id**: Destination Airport, Airport ID. An identification number assigned by US DOT to identify a unique airport. Use this field for airport analysis across a range of years because an airport can change its airport code and airport codes can be reused.- drop this value**dest**: Destination Airport- use dummy variables**dest_city_name**: Destination Airport, City Name- plan to drop this column- option to create a state variable from this as indicated by a a two letter abreviation     - move as dummy**crs_dep_time**: CRS Departure Time (local time: hhmm)- create binned by hour- use to add four features:    - med_hist_arr_delay    - med_hist_dep_delay    - m_hist_arr_delay    - m_hist_dep_delay**crs_arr_time**: CRS Arrival Time (local time: hhmm)- create binned by hour**dup**: Duplicate flag marked Y if the flight is swapped based on Form-3A data- in the 5,000,000 sample file we do not have any Y- we will check for any rows containing Y in full dataset -- drop rows if present- drop entire column    - Technical info from [link](https://www.bts.gov/sites/bts.dot.gov/files/docs/explore-topics-and-geography/topics/airlines-and-airports/207741/technical-directive-no-27-time-2018.pdf) states - Form 3A:  On-Time Performance Data for Codeshare Flights (Long)    - assumed to be an infrequent form**crs_elapsed_time**: CRS Elapsed Time of Flight, in Minutes- keep as is**flights**: Number of Flights- in the 5,000,000 sample file all values are equal to 1- we will check for any rows containing different values in full dataset -- drop rows if present- drop entire column**distance**: Distance between airports (miles)- keep as is    - option to bin ---  Package Imports<jupyter_code>import pandas as pd
import numpy as np
import datetime
import calendar
from sklearn.preprocessing import LabelEncoder
from pickle import dump
pd.set_option('display.max_columns', None)
import warnings
warnings.filterwarnings('ignore')<jupyter_output><empty_output><jupyter_text>---  Function Definition *binning into months*<jupyter_code>def get_month(date):
    """Returns the month the flight occurs
    INPUT date in form YYYY-MM-DD
    RETURNS month"""
    DATE = datetime.datetime.strptime(date, "%Y-%m-%d")
    return DATE.month<jupyter_output><empty_output><jupyter_text>*binning into day of week*<jupyter_code>def get_day_of_week(date):
    """Returns the day of the week
    INPUT date in the form YYYY-MM-DD
    RETURNS number of day of the week:
            where 0 = Monday and 6 = Sunday"""
    DATE = datetime.datetime.strptime(date, "%Y-%m-%d")
    return DATE.weekday()<jupyter_output><empty_output><jupyter_text>*binning into week of month*<jupyter_code>calendar.setfirstweekday(6)

def get_week_of_month(date):
    date = str(date).split("-")
    year, month, day = int(date[0]), int(date[1]), int(date[2])
    x = np.array(calendar.monthcalendar(year, month))
    week_of_month = np.where(x==day)[0][0] + 1
    return(week_of_month)<jupyter_output><empty_output><jupyter_text>*checking if codeshare*<jupyter_code>def check_codeshare(branded_codeshare):
    words = branded_codeshare.split("_")
    if len(words) == 2:
        return 1
    return 0<jupyter_output><empty_output><jupyter_text>*binning into hours*<jupyter_code>def check_time_block(time):
    """Determine the hour of the day
    INPUT time HHMM
    RETURNS hour 0-23 where 0 == 12am and 23 == 11pm"""
    if type(time) == 'numpy.ndarray':
        time = time.astype(int)
    time = int(time)
    
    t = time // 100
    if t == 0:
        return 0
    return t-1<jupyter_output><empty_output><jupyter_text>*binning into haul length* (not used)<jupyter_code>def define_haul_length(distance):
    """Determine the haul length of a given distance
    INPUT distance (in miles)
    RETURNS {0: short haul, 1: medium haul, 2: long haul}"""
    
    if distance < 700:
        return 0 
    elif distance < 3000:
        return 1
    else: 
        return 2<jupyter_output><empty_output><jupyter_text>**NEW FEATURE** get unique mkt_op_combo> must apply dummy variables also<jupyter_code>def mkt_op_combo(mkt_unique, op_unique):
    """Output a string for the unique combination of the mkt_unique and op_unique
        INPUT strings mkt_unique and op_unique
        RETURNS string of unique combo"""
    combo = mkt_unique+op_unique
    return combo<jupyter_output><empty_output><jupyter_text>**NEW FEATURE** Flight type<jupyter_code>def flight_type(fl_num):
    """Takes in a fl_num and returns the type of flight
        INPUT fl_num
        RETURNs flight_type 
                2: premium, 1 : regular, 0 : regional, ferry, codeshare"""
    fl_num = str(fl_num)
    if len(fl_num) < 3:
        return 2 #preimum
    if len(fl_num) == 3:
        return 1 #reg
    else: 
        return 0 #regional affiliate, ferry, codeshare, etc.<jupyter_output><empty_output><jupyter_text>**NEW FEATURE** Historical mean dep_delay by crs_dep_hour<jupyter_code>def get_hist_m_dep_delay(dep_hour):
    """returns the mean dep_delay for the hour based on 2018-2019 data
        INPUT departure hour
        OUTPUT historical_mean_dep_delay"""
    historical_delays = {0: 6.711716493969764, 1: 9.675249362096961, 2: 10.619793205317578, 3: 6.630692167577413, 4: -0.4136475056746801,
                         5: 0.11429594186434255, 6: 1.6161646966248562, 7: 2.9157571077032296, 8: 4.135963064069914, 9: 5.26489329589765,
                         10: 6.13392546097791, 11: 7.0181790875984875, 12: 8.131680643107183, 13: 9.510919588229374, 14: 10.304524506138106,
                         15: 11.336050018124949, 16: 12.382205588783819, 17: 13.536146413960237, 18: 13.756275088751552, 19: 13.289175306396258,
                         20: 12.634817154337778, 21: 10.544540416974117, 22: 7.987786063255336}
    hour = int(dep_hour)
    return historical_delays[hour]<jupyter_output><empty_output><jupyter_text>**NEW FEATURE** Historical mean arr_delay by crs_dep_hour<jupyter_code>def get_hist_m_arr_delay(dep_hour):
    """returns the mean arr_delay for the hour based on 2018-2019 data
        INPUT departure hour
        OUTPUT historical_mean_arr_delay"""
    historical_delays = {0: -1.6642602344148123, 1: 3.440501043841336, 2: 4.343870014771049, 3: 1.3802367941712204, 4: -5.484170909895949,
                         5: -4.606989162142505, 6: -2.907391092361358, 7: -1.8630207558845115, 8: -1.085556598557783, 9: 0.08580366114366288,
                         10: 0.5798795108102116, 11: 2.115288435274042, 12: 3.1173279785990315, 13: 4.805705640109731, 14: 5.449820220021156,
                         15: 6.739257741261025, 16: 7.6573414085094385, 17: 8.877695016385436, 18: 8.378205975005564, 19: 7.965689735642226,
                         20: 6.8922934459978995, 21: 4.922862682118726, 22: 0.5166495242993058}
    
    hour = int(dep_hour)
    return historical_delays[hour]<jupyter_output><empty_output><jupyter_text>**NEW FEATURE** Historical median dep_delay by crs_dep_hour<jupyter_code>def get_hist_med_dep_delay(dep_hour):
    """returns the median dep_delay for the hour based on 2018-2019 data
        INPUT departure hour
        OUTPUT historical_median_dep_delay"""
    historical_delays = {0: -2.0, 1: -2.0, 2: -3.0, 3: -4.0, 4: -4.0, 5: -4.0, 6: -4.0, 7: -3.0, 8: -3.0, 9: -3.0, 10: -2.0, 11: -2.0,
                         12: -2.0, 13: -1.0, 14: -1.0, 15: -1.0, 16: -1.0, 17: -1.0, 18: -1.0, 19: -1.0, 20: -1.0, 21: -1.0, 22: -2.0}
    hour = int(dep_hour)
    return historical_delays[hour]<jupyter_output><empty_output><jupyter_text>**NEW FEATURE** Historical median arr_delay by crs_dep_hour<jupyter_code>def get_hist_med_arr_delay(dep_hour):
    """returns the median arr_delay for the hour based on 2018-2019 data
        INPUT departure hour
        OUTPUT historical_med_arr_delay"""
    historical_delays = {0: -9.0, 1: -6.0, 2: -6.0, 3: -7.0, 4: -9.0, 5: -9.0, 6: -8.0, 7: -8.0, 8: -7.0, 9: -7.0, 10: -7.0, 11: -6.0,
                         12: -6.0, 13: -5.0, 14: -5.0, 15: -4.0, 16: -4.0, 17: -3.0, 18: -3.0, 19: -3.0, 20: -4.0, 21: -4.0, 22: -7.0}
    hour = int(dep_hour)
    return historical_delays[hour]<jupyter_output><empty_output><jupyter_text>**Target Prep**(where required)  **CARRIER_DELAY, WEATHER_DELAY, NAS_DELAY, SECURITY_DELAY, LATE_AIRCRAFT_DELAY** to single y<jupyter_code>def get_delay_type(carrier_delay, weather_delay, nas_delay, security_delay, late_aircraft_delay):
    """Takes in all potential delay causes and returns the primary cause
    in case of 2 identical delays the first is returned
    INPUT carrier_delay, weather_delay, nas_delay, security_delay, late_aircraft_delay
    RETURNS {0 : no delay cause noted,
            1 : carrier_delay,
            2 : weather_delay,
            3 : NAS_delay,
            4 : security_delay,
            5 : late_aircraft_delay}"""
    
    if carrier_delay == weather_delay == nas_delay == security_delay == late_aircraft_delay == 0:
        return 0
    
    delays = {}
    delays[1] = carrier_delay
    delays[2] = weather_delay
    delays[3] = nas_delay
    delays[4] = security_delay
    delays[5] = late_aircraft_delay
    
    return max(delays, key=delays.get)<jupyter_output><empty_output><jupyter_text>---  Applying Above**Step 1**: Remove outliers (any -225 **Step 2**: Check 'flights' and 'dup' for unexpected values **Step 3**: Drop Columns ('mkt_carrier', 'tail_num', 'op_carrier_fl_num', 'origin_airport_id', 'dest_airport_id', 'dup', 'flights') **Step 4**: Get Columns 'fl_month', 'fl_day_of_week' and 'fl_week_of_month' from 'fl_date' $\;\;\;\;\;\;$*Step 4a*: Drop 'fl_date'**Step 5**: Use 'mkt_unique_carrier' and 'op_unique_carrier' to obtain 'mkt_op_combo' $\;\;\;\;\;\;$*Step 5a*: Drop 'mkt_unique_carrier' and 'op_unique_carrier' **Step 6**: Binarize 'branded_code_share' **Step 7**: Replace 'mkt_carrier_fl_num' with flight_type 'A-C' **Step 8**: Replace 'crs_dep_time' with hour $\;\;\;\;\;\;$*Step 8a* Get Column med_dep_arr_delay $\;\;\;\;\;\;$*Step 8b* Get Column med_dep_dep_delay $\;\;\;\;\;\;$*Step 8c* Get Column m_dep_arr_delay $\;\;\;\;\;\;$*Step 8d* Get Column m_dep_dep_delay **Step 9**: Replace 'crs_arr_time' with hour **Step 10**: Get label encodings for ('mkt_op_combo', 'origin_airport', 'dest_airport') $\;\;\;\;\;\;$*Step 11a* drop original columns for ('mkt_op_combo', 'origin_airport', 'dest_airport') **Step 11**: Get column for delay_type $\;\;\;\;\;\;$*Step 11a* drop columns ('carrier_delay', 'weather_delay', 'nas_delay', 'security_delay', 'late_aircraft_delay') ***Special Notes for full flights database***- Also need to drop the following: 'dep_time', 'dep_delay', 'taxi_out', 'wheels_off', 'wheels_on', 'taxi_in', 'arr_time', 'cancellation_code', 'diverted', 'actual_elapsed_time', 'first_dep_time', 'total_add_gtime', 'longest_add_gtime' ---  Clean the Data<jupyter_code>df = pd.read_csv("flights_nullsremoved.csv")<jupyter_output>/opt/anaconda3/lib/python3.8/site-packages/IPython/core/interactiveshell.py:3145: DtypeWarning: Columns (25) have mixed types.Specify dtype option on import or set low_memory=False.
  has_raised = await self.run_ast_nodes(code_ast.body, cell_name,<jupyter_text>*Pre-Step*<jupyter_code>df.drop(columns=['dep_time', 'dep_delay', 'taxi_out', 'wheels_off', 'wheels_on', 'taxi_in','arr_time','cancellation_code', 'diverted', 'actual_elapsed_time', 'first_dep_time', 'total_add_gtime', 'longest_add_gtime'], inplace = True)<jupyter_output><empty_output><jupyter_text>*Step 1*<jupyter_code>df = df[df.arr_delay >= -225]
df = df[df.arr_delay <= 225]<jupyter_output><empty_output><jupyter_text>*Step 2*<jupyter_code>df.flights.unique()
df.dup.unique()<jupyter_output><empty_output><jupyter_text>*Step 3*<jupyter_code>df.drop(columns = ['mkt_carrier', 'tail_num', 'op_carrier_fl_num', 'origin_airport_id', 'dest_airport_id', 'dup', 'flights'], inplace = True)<jupyter_output><empty_output><jupyter_text>*Step 4*<jupyter_code>#fl_month
df.loc[:,'fl_month'] = df['fl_date'].apply(get_month)
#fl_day_of_week
df.loc[:,'fl_day_of_week'] = df['fl_date'].apply(get_day_of_week)
#week of month
df.loc[:,'fl_week_of_month'] = df['fl_date'].apply(get_week_of_month)
#drop_fl_date
df.drop(columns = 'fl_date', inplace=True)<jupyter_output><empty_output><jupyter_text>*Step 5*<jupyter_code>#get mkt_op_combo
df.loc[:,"mkt_op_combo"] = df.apply(lambda x: mkt_op_combo(x.mkt_unique_carrier, x.op_unique_carrier), axis=1)
df.drop(columns = ['mkt_unique_carrier', 'op_unique_carrier'], inplace = True)<jupyter_output><empty_output><jupyter_text>*Step 6*<jupyter_code>df.loc[:,"branded_code_share"] = df["branded_code_share"].apply(check_codeshare)<jupyter_output><empty_output><jupyter_text>*Step 7*<jupyter_code>#get fl_type
df.loc[:,"fl_type"] = df["mkt_carrier_fl_num"].apply(flight_type)
df.drop(columns = 'mkt_carrier_fl_num', inplace=True)<jupyter_output><empty_output><jupyter_text>*Step 8*<jupyter_code>df.loc[:,'crs_dep_time'] = df['crs_dep_time'].apply(check_time_block)
df.loc[:,'m_hist_dep_delay'] = df['crs_dep_time'].apply(get_hist_m_dep_delay)
df.loc[:,'med_hist_dep_delay'] = df['crs_dep_time'].apply(get_hist_med_dep_delay)
df.loc[:,'m_hist_arr_delay'] = df['crs_dep_time'].apply(get_hist_m_arr_delay)
df.loc[:,'med_hist_arr_delay'] = df['crs_dep_time'].apply(get_hist_med_arr_delay)<jupyter_output><empty_output><jupyter_text>*Step 9*<jupyter_code>df.loc[:,'crs_arr_time'] = df['crs_arr_time'].apply(check_time_block)<jupyter_output><empty_output><jupyter_text>*Step 10*<jupyter_code>le_origin = LabelEncoder()
le_origin.fit(df.origin)
df.loc[:,"origin"] = le_origin.transform(df.origin)
output = open('origin_encoder.pkl', 'wb')
dump(le_origin, output)
le_dest = LabelEncoder()
le_dest.fit(df.dest)
df.loc[:,"dest"] = le_dest.transform(df.dest)
output = open('dest_encoder.pkl', 'wb')
dump(le_dest, output)
le_mktopcombo = LabelEncoder()
le_mktopcombo.fit(df.mkt_op_combo)
df.loc[:,"mkt_op_combo"] = le_mktopcombo.transform(df.mkt_op_combo)
output = open('mktopcombo_encoder.pkl', 'wb')
dump(le_mktopcombo, output)<jupyter_output><empty_output><jupyter_text>*Step 12*<jupyter_code>df.loc[:,"delay_type"] = df.apply(lambda x: get_delay_type(x.carrier_delay, 
                                                           x.weather_delay, 
                                                           x.nas_delay, 
                                                           x.security_delay, 
                                                           x.late_aircraft_delay), axis=1)
df.drop(columns = ['carrier_delay', "weather_delay", 'nas_delay', 'security_delay', 'late_aircraft_delay'], inplace = True)
columns = df.columns
for c in columns: 
    if len(df[df[c].isna()==True]) > 0:
        print(c, len(df[df[c].isna()==True]))
df = df[df.crs_elapsed_time.isna()==False]
df.to_csv("flights_cleaned.csv", index = False)<jupyter_output><empty_output><jupyter_text>---  Label Encoder vs One Hot EncodingOne of the major challenges encountered by our team was the decision on whether to proceed with label encoding or one hot encoding. The data in the notebooks preceeding this one use the data that was label encoded. We had worked with a small sample to determine improvements in the model with the use of one hot encoding however we were unable to arrive at an improved model outcome. We were unable to devote the time desire to following this branch through however it would be a desired next step for the project. The below code was for producing the OneHotEncoded sample and removing multicolinearity.<jupyter_code>import scipy.sparse as sp
from sklearn.preprocessing import OneHotEncoder
import matplotlib.pyplot as plt
df.columns<jupyter_output><empty_output><jupyter_text>*Obtain a sample of the above created dataframe and drop potentially unnecessary features*<jupyter_code>df_temp = df.copy()
df_temp.drop(columns = ['origin', 'dest','m_hist_dep_delay', 'med_hist_dep_delay', 'm_hist_arr_delay','med_hist_arr_delay','mkt_op_combo'],
            inplace = True)
df_samp = df_temp.sample(10000)
df_samp.columns<jupyter_output><empty_output><jupyter_text>*Determine features to one_hot_encode and fit the encoder* **Note**: The use of one hot encoding rather than dummies allows for the export of the encoder to use on the evaluation data.<jupyter_code>to_one_hot_encode =['crs_dep_time', 'crs_arr_time', 'fl_month',
                    'fl_day_of_week', 'fl_week_of_month', 'fl_type']
enc = OneHotEncoder(drop = 'first')
enc.fit(df[to_one_hot_encode])
encoded = enc.transform(df[to_one_hot_encode])
encoded = sp.csr_matrix.toarray(encoded)
df_encoded = pd.DataFrame(encoded)<jupyter_output><empty_output><jupyter_text>*Combine the results with the removed columns into a new dataframe*<jupyter_code>df_samp.drop(columns=to_one_hot_encode, inplace = True)
df_samp.reset_index(inplace=True)
df_samp_encoded = df_samp.merge(df_encoded, right_index=True, left_index=True)
df_samp_encoded
df_samp_encoded.drop(columns='index', inplace=True)<jupyter_output><empty_output><jupyter_text>*Look into correlation and Multicollinearity*<jupyter_code>f = plt.figure(figsize=(11, 8))
plt.matshow(df_samp_encoded.corr(), fignum=f.number)
plt.xticks(range(df_samp_encoded.shape[1]), df_samp_encoded.columns, fontsize=14, rotation=45)
plt.yticks(range(df_samp_encoded.shape[1]), df_samp_encoded.columns, fontsize=14)
cb = plt.colorbar()
cb.ax.tick_params(labelsize=14)
plt.title('Correlation Matrix', fontsize=16);<jupyter_output><empty_output><jupyter_text>**Note**: the standard scaler was done in this notebook strictly for the newly created one_hot_encoded dataframe, for the other data see notebook 3_Training_Prep<jupyter_code>from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
y = df_samp_encoded[['arr_delay', 'cancelled', 'delay_type']]
X = df_samp_encoded.drop(columns = ['arr_delay', 'cancelled', 'delay_type']).values
scaler.fit(X)
X_scale=scaler.transform(X)
trainX = pd.DataFrame(data = X_scale,
              columns = ['branded_code_share', 'crs_elapsed_time', 'air_time', 'distance', 0, 1, 2, 3, 4, 5,6,7,8,9,10,
                          11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,
                          41,42,43,44,45,46,47,48,49,50, 51, 52, 53, 54, 55,56,57,58,59,60,61, 62, 63, 64, 65,66,67,68])
train = trainX.merge(y, right_index=True, left_index=True)
train.to_csv('hotencode_samp.csv', index=False)<jupyter_output><empty_output><jupyter_text>*Use the Variance Threshold to remove unnecessary features*<jupyter_code>from sklearn.feature_selection import VarianceThreshold

vt = VarianceThreshold(0.1)
df_transformed = vt.fit_transform(trainX)
trainX.shape<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1229318968834167, 'fraction_numerical': 0.06415929203539823, 'mean_word_length': 4.670302699754568, 'pattern_counts': {'":': 0, '<': 140, '<?xml version=': 0, '>': 139, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '34', 'dir': 'jupyter-structured-clean-dedup', 'id': '526120', 'license': 'MIT', 'n_tokens_mistral': 8172, 'n_tokens_neox': 7196, 'n_words': 1811, 'path': 'Notebooks/3_Feature_Selection_and_Data_Cleaning.ipynb', 'repo_name': 'Isabelle-Dr/MidTerm-Project', 'size': '20792'}