<jupyter_start><jupyter_text>Problem DefinitionSource: [Kaggle - Flight Delay Prediction](https://www.kaggle.com/divyansh22/flight-delay-prediction)<jupyter_code>from swat import CAS
import numpy as np
import seaborn as sn
s = CAS(hostname='localhost', port=5570, username='sas', password='<PASSWORD>')
s.setsessopt(messagelevel='NONE')<jupyter_output><empty_output><jupyter_text>Read local data<jupyter_code>column_names = ['OP_CARRIER',        # Code assigned by IATA and commonly used to identify a carrier.
                'OP_CARRIER_FL_NUM', # Flight number
                'ORIGIN',            # Origin
                'DEST',              # Destination
                'DAY_OF_MONTH',      # Day of month
                'DAY_OF_WEEK',       # Day of week
                'DEP_TIME',          # Departure Time
                'ARR_TIME',          # Arrival Time
                'DISTANCE',          # Flight Distance
                'CANCELLED',         # Cancel Indicator (Binary)
                'DIVERTED',          # DIVERTED Indicator (Binary)
                'ARR_DEL15',         # Delay Indicator (Binary)
                'YEAR']              # Year (will be used for partitioning)

data_types = {'OP_CARRIER_FL_NUM': str, 
              'ORIGIN':            str, 
              'DEST':              str, 
              'CANCELLED':         str, 
              'DIVERTED':          str, 
              'ARR_DEL15':         str}

flight_data = s.read_csv(filepath_or_buffer = 'data/flights_january_2019.csv', 
                         usecols            = column_names, 
                         dtype              = data_types, 
                         casout             = dict(name='flight_data', replace=True))[column_names]
flight_data.tableinfo()
flight_data.columninfo()
flight_data.head()
numeric_variables = flight_data.select_dtypes('numeric').columns.to_list()
numeric_variables.remove('YEAR') # Remove partition variable
categorical_variables = flight_data.select_dtypes('character').columns.to_list()

print("\nNumeric Variables:\n{}\n".format(numeric_variables))
print("\nCategorical Variables:\n{}".format(categorical_variables))<jupyter_output>Numeric Variables:
['DAY_OF_MONTH', 'DAY_OF_WEEK', 'DEP_TIME', 'ARR_TIME', 'DISTANCE']


Categorical Variables:
['OP_CARRIER', 'OP_CARRIER_FL_NUM', 'ORIGIN', 'DEST', 'CANCELLED', 'DIVERTED', 'ARR_DEL15']<jupyter_text>Simple Statistics & Plotting<jupyter_code>s.help()
s.simple?<jupyter_output><empty_output><jupyter_text>Simple descriptive statistics<jupyter_code>flight_data.simple.summary(inputs=numeric_variables)<jupyter_output><empty_output><jupyter_text>Frequency distribution for variables<jupyter_code># Simple frequency for OP_CARRIER
frequency_by_carrier = flight_data.simple.freq(inputs='OP_CARRIER')['Frequency']
frequency_by_carrier
frequency_by_carrier.plot.bar(x='CharVar', y='Frequency', figsize=(12,5), rot=0)
# Crosstab for OP_CARRIER -> Which airlines have the most delays?
flight_data_crosstab = flight_data.simple.crosstab(row='OP_CARRIER', aggregator='sum', includeMissing=False, col='ARR_DEL15')['Crosstab']
flight_data_crosstab.rename(columns={'Col1':'Pünktlich', 'Col2':'Verspätet'}, inplace=True)
flight_data_crosstab.plot.bar(x='OP_CARRIER', y=['Pünktlich','Verspätet'], stacked=True, figsize=(15,5))<jupyter_output><empty_output><jupyter_text>Correlation of numerical variables<jupyter_code>pearson_correlation = flight_data.simple.correlation(inputs=numeric_variables)
pearson_correlation
# SAS mirrors a lot of the Pandas-functionality
pearson_correlation = flight_data[numeric_variables].corr()
# Visualize with open source libraries
sn.set(font_scale=1.2)
sn.heatmap(pearson_correlation)<jupyter_output><empty_output><jupyter_text>SAS mirrors a lot of the pandas API functionality<jupyter_code>type(flight_data) # flight_data is a CAS-table
# Pandas: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.describe.html
# SAS: https://sassoftware.github.io/python-swat/generated/swat.cas.table.CASTable.describe.html
flight_data.describe(include='all')
# Pandas: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sample.html
# SAS: https://sassoftware.github.io/python-swat/generated/swat.cas.table.CASTable.sample.html
flight_data.sample(n=3).head()<jupyter_output><empty_output><jupyter_text>Modelling  Data Preparation1. We remove missing values2. We load data from 20203. We combine data from 2019 and 20204. We train our models on data from 2019 and evaluate its performance on data from 2020<jupyter_code># Remove missing values and create partition variable
flight_data_cleaned = flight_data.dropna()
print('\nDropped {} rows containing missing values from 2019 data.\n'.format(len(flight_data)-len(flight_data_cleaned)))
flight_data_cleaned.head()
# Load and clean test data
flight_data_test = s.read_csv('data/flights_january_2020.csv', 
                              usecols=column_names, 
                              dtype=data_types, 
                              casout=dict(name='flight_data_test', replace=True))[column_names]
flight_data_test_cleaned = flight_data_test.dropna()
print('\nDropped {} rows containing missing values from 2020 data.\n'.format(len(flight_data_test)-len(flight_data_test_cleaned)))
flight_data_test_cleaned.head()
# Combine data from 2019 and 2020
flight_data_cleaned_2019_2020 = flight_data_cleaned.append(flight_data_test_cleaned)
flight_data_cleaned_2019_2020.head()<jupyter_output><empty_output><jupyter_text>Logistic Regressionhttps://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=casactstat&docsetTarget=cas-regression-logistic.htm&locale=enPYTHON.cas-regression-logistic-partfit<jupyter_code>s.loadactionset('regression')
inputs = ['DAY_OF_MONTH',
          'DAY_OF_WEEK',
          'DEP_TIME',
          'DISTANCE',
          'OP_CARRIER']

nominals = ['DAY_OF_MONTH',
            'DAY_OF_WEEK',
            'OP_CARRIER',
            'ARR_DEL15']

target = 'ARR_DEL15'
s.regression.logistic(
    table             = flight_data_cleaned_2019_2020,                        # input table
    inputs            = inputs,                                               # input variables
    nominals          = nominals,                                             # nominal variables
    target            = target,                                               # target variable
    selection         = dict(method='STEPWISE'),                              # Variable selection
    partbyvar         = dict(name='YEAR', train='2019', validate='2020'),     # partition by YEAR variable
    partfit           = True,                                                 # fit-statistics for partitioned data
    display           = dict(names=['ModelInfo','FitStatistics']),            # display ModelInfo and Fit statistics
    store             = dict(name='logreg_model', replace=True)               # table to store regression model
)
s.regression.logisticscore(
    restore                 = 'logreg_model', 
    table                   = flight_data_cleaned, 
    casout                  = dict(name='logreg_scored', replace=True),
    copyVars                = 'ALL'
)
s.CASTable('logreg_scored').head(5)<jupyter_output><empty_output><jupyter_text>Gradient Boostinghttps://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=casanpg&docsetTarget=cas-decisiontree-gbtreetrain.htm&locale=enSAS.cas-decisiontree-gbtreetrain-nominals<jupyter_code>s.loadactionset('decisiontree')
s.help(actionset='decisiontree')
s.decisiontree.gbtreetrain(
    table             = flight_data_cleaned,                      # input table
    inputs            = inputs,                                   # input variables
    nominals          = nominals,                                 # nominal variables
    target            = target,                                   # target variable
    validtable        = flight_data_test_cleaned,                 # validation table
    ntree             = 10,                                       # Number of trees
    maxlevel          = 3,                                        # Maximum tree depth
    casOut            = dict(name='gbtree_model', replace=True)   # table to store boosting model
)
s.decisiontree.gbtreescore(
    modeltable              = 'gbtree_model', 
    table                   = flight_data_test_cleaned,
    casOut                  = dict(name='gbtree_scored', replace=True),
    copyVars                = list(flight_data_test_cleaned.columns)
)
s.CASTable('gbtree_scored').head(5)<jupyter_output><empty_output><jupyter_text>Auto ML<jupyter_code>s.loadactionset(actionset="dataSciencePilot")
flight_data_cleaned.columns
flight_data
inputs = ['DAY_OF_MONTH',
          'DAY_OF_WEEK',
          'DEP_TIME',
          'DISTANCE',
          'OP_CARRIER']

nominals = ['DAY_OF_MONTH',
            'DAY_OF_WEEK',
            'OP_CARRIER',
            'ARR_DEL15']
flight_data[inputs+[target]].head()
s.dataSciencePilot.dsAutoMl(
     table                   = flight_data[inputs+[target]],#{"name":'flight_data', "vars":inputs+[target]},
     target                  = "ARR_DEL15",
     explorationPolicy       = {},
     screenPolicy            = {},
     selectionPolicy         = {},
     transformationPolicy    = {"missing":True, "skewness":True, "kurtosis":True, "Outlier":True},
     modelTypes              = ["decisionTree","forest","gradboost","logistic"],
     objective               = "MCE",
     sampleSize              = 8, 
     topKPipelines           = 8,
     kFolds                  = 2,
     transformationOut       = {"name" : "TRANSFORMATION_OUT", "replace" : True},
     featureOut              = {"name" : "FEATURE_OUT", "replace" : True},
     pipelineOut             = {"name" : "PIPELINE_OUT", "replace" : True},
     saveState               = {"name" : "ASTORE_OUT", "replace" : True}
 )
# Display table of created pipelines
s.fetch(table = {"name": "PIPELINE_OUT"})
# Display table of created feature transformations
pd.set_option('display.max_colwidth', 0)
s.fetch(table = {"name": "FEATURE_OUT"}).Fetch[['FeatureId','InputVar1','Label','IsGenerated']]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11087148318792275, 'fraction_numerical': 0.012939907852171357, 'mean_word_length': 2.7125181950509463, 'pattern_counts': {'":': 8, '<': 45, '<?xml version=': 0, '>': 46, 'https://': 7, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 15, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '506875', 'license': 'Apache-2.0', 'n_tokens_mistral': 3324, 'n_tokens_neox': 3033, 'n_words': 504, 'path': 'swat/airport_statistics.ipynb', 'repo_name': 'Mentos05/SAS_AskTheExpert', 'size': '10196'}