<jupyter_start><jupyter_text>Data preparationBefore the data can be sent through the KNN model it needs to be tidied. Here I will: * Remove nulls;* Create dummies as required;* Normalise values to prevent exponential scales;* Further reduce the data set to only relevant classifiers.<jupyter_code># Import libraries

import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns 

from sklearn.preprocessing import StandardScaler
from sqlalchemy import create_engine
# Create connections to data and read data to pandas dataframe
engine = create_engine('sqlite:///../data/orgs_customer_behaviours.db')
df_initial_all = pd.read_sql_table('customers_with_behaviours', engine)
df_initial_orgs = pd.read_sql_table('orgs_organisations', engine,)<jupyter_output><empty_output><jupyter_text>Introducing additional data for features and or classifiers do the data set.<jupyter_code>df_ic2019 = pd.read_csv('../data/ic2019.csv', index_col='UNITID')
df_adm2019 = pd.read_csv('../data/adm2019.csv', index_col='UNITID')<jupyter_output><empty_output><jupyter_text>Reduce the files to only the required columns.<jupyter_code>df_ic2019_cls_col = ['PEO1ISTR', 'PEO2ISTR', 'PEO3ISTR', 'PEO4ISTR', 'PEO5ISTR', 'PEO6ISTR', 
                     'CNTLAFFI', 'PUBPRIME', 'PUBSECON', 'RELAFFIL', 'LEVEL1', 'LEVEL2', 
                     'LEVEL3', 'LEVEL4', 'LEVEL5', 'LEVEL6', 'LEVEL7', 'LEVEL8', 'LEVEL12',
                     'LEVEL17', 'LEVEL18', 'LEVEL19', 'CALSYS', 'FT_UG', 'FT_FTUG', 'FTGDNIDP', 
                     'PT_UG', 'PT_FTUG', 'PTGDNIDP', 'DOCPP', 'DOCPPSP', 'OPENADMP', 'CREDITS1', 
                     'CREDITS2', 'CREDITS3', 'CREDITS4', 'STUSRV2', 'STUSRV3', 'STUSRV4', 
                     'STUSRV8', 'LIBRES1', 'LIBRES2', 'LIBRES3', 'LIBRES4', 'LIBRES5', 'TUITPL', 
                     'TUITPL1', 'TUITPL2', 'TUITPL3', 'TUITPL4', 'DSTNUGC', 'DSTNUGP', 'DSTNUGN', 
                     'DSTNGC', 'DSTNGP', 'DSTNGN', 'DISTCRS', 'DISTPGS', 'DSTNCED1', 'DSTNCED2', 
                     'DSTNCED3', 'DISTNCED', 'DISAB', 'ROOM', 'ROOMCAP', 'BOARD']

df_cls_ic2019 = df_ic2019[df_ic2019_cls_col]

df_adm2019_cls_cols = ['APPLCN', 'APPLCNM', 'APPLCNW', 'ADMSSN', 'ADMSSNM', 'ADMSSNW', 
                       'ENRLT', 'ENRLM', 'ENRLW', 'ENRLFT', 'ENRLFTM', 'ENRLFTW', 
                       'ENRLPT', 'ENRLPTM', 'ENRLPTW', 'SATNUM', 'SATPCT', 'ACTNUM', 
                       'ACTPCT', 'SATVR25', 'SATVR75', 'SATMT25', 'SATMT75']

df_cls_adm2019 = df_adm2019[df_adm2019_cls_cols]<jupyter_output><empty_output><jupyter_text>Join the data sets together<jupyter_code>df_combined_features = df_cls_adm2019.merge(df_cls_ic2019, on='UNITID')
df_orgs_data = df_initial_orgs.merge(df_combined_features, on='UNITID')
print(f"\nThe dataframe df_all_data is shaped with {df_orgs_data.shape[1]} columns and {df_orgs_data.shape[0]} rows\n\n")
print(f"Here is the head of the dataframe ... \n\n {df_orgs_data.head()}")<jupyter_output><empty_output><jupyter_text>Here we will remove the `-1` and `-2` placeholders in the data, that was introduced with the data merging.<jupyter_code>df_orgs_data.replace([-2, '-2', -1, '-1'], 0, inplace=True)<jupyter_output><empty_output><jupyter_text>Remove unnecessary uniqueness columns from the data set, and check for any `NaN` values.<jupyter_code>unnecessary_uniqueness_columns = ['INSTNM', 'IALIAS', 'FIPS', 'OBEREG', 'GENTELE', 'EIN', 'DUNS', 'OPEID', 'CNGDSTCD']
df_orgs_data.drop(unnecessary_uniqueness_columns, axis=1, inplace=True)
data_not_null = df_orgs_data.isnull().sum().sum()
col_count = df_orgs_data.shape[1]
row_count = df_orgs_data.shape[0]

print(f"The size of the data is {col_count * row_count} with {data_not_null} NaN items")
df_data_null = df_orgs_data.isnull().sum().to_frame('NaN')
df_data_null[df_data_null['NaN'] > 0]
def impute_nulls_with_column_median(list_of_columns, dataframe):

    """
    The function takes a list of applicable columns and a corresponding dataframe. 
    The median is calculated per column and then identifies NaNs to be replaced with the median

    INPUT: 

    list_of_columns: a list of columns with null values
    dataframe: the dataframe relating to the specified list.

    OUTPUT:

    None
        The dataframe values are replaced inplace to there is no need to return the frame
    """

    for col in list_of_columns:
        impute_median = int(dataframe[col].median())
        dataframe[col].fillna(value=impute_median, inplace=True)
    
    df_dataframe_nulls = dataframe.isnull().sum().to_frame('Nulls')
    list_null_cols_vals = df_dataframe_nulls[df_dataframe_nulls['Nulls'] > 0]
    
    print(f'\nImputing of values complete, you can find the df header below: \n\n {dataframe.head()}')
    print(f'\nLets look for the amount of nulls remaining in the dataframe: \n\n')

    for rows in list_null_cols_vals.items():
        print(f'{rows} \n')
columns_with_null_data = list(df_data_null[df_data_null['NaN'] > 0].index)
impute_nulls_with_column_median(columns_with_null_data, df_orgs_data)<jupyter_output><empty_output><jupyter_text>Now that we've improved the data quality by removing the `NaN` values we need to standardise the values for the KNN model.<jupyter_code>categorical_columns = df_orgs_data.select_dtypes(include=['object'])

list_of_categorical_columns = list(categorical_columns.columns)

list_of_binary_features_columns = ['PEO1ISTR', 'PEO2ISTR', 'PEO3ISTR', 'PEO4ISTR', 'PEO5ISTR', 'PEO6ISTR', 'LEVEL1', 'LEVEL2', 'LEVEL3',
                                   'LEVEL4', 'LEVEL5', 'LEVEL6', 'LEVEL7', 'LEVEL8', 'LEVEL12', 'LEVEL17', 'LEVEL18', 'LEVEL19', 'DOCPP', 
                                   'CREDITS1', 'CREDITS2', 'CREDITS3', 'CREDITS4', 'STUSRV2', 'STUSRV3', 'STUSRV4', 'STUSRV8', 'LIBRES1', 
                                   'LIBRES2', 'LIBRES3', 'LIBRES4', 'LIBRES5', 'TUITPL1', 'TUITPL2', 'TUITPL3', 'TUITPL4', 'DSTNUGC', 
                                   'DSTNUGP', 'DSTNUGN', 'DSTNGC', 'DSTNGP', 'DSTNGN', 'DISTCRS', 'DISTPGS', 'DSTNCED1', 'DSTNCED2', 
                                   'DSTNCED3', 'CONVERTED', 'CONTACTED']

list_of_nonbinary_features_columns = ['OPEFLAG', 'SECTOR', 'ICLEVEL', 'CONTROL', 'HLOFFER', 'GROFFER', 'HDEGOFR1', 'DEGGRANT', 'HBCU',
                                      'HOSPITAL', 'MEDICAL', 'TRIBAL', 'LOCALE', 'POSTSEC', 'PSEFLAG', 'PSET4FLG', 'RPTMTH', 'INSTCAT',
                                      'C18BASIC', 'C18IPUG', 'C18IPGRD', 'C18UGPRF', 'C18ENPRF', 'C18SZSET', 'C15BASIC', 'CCBASIC', 
                                      'CARNEGIE', 'LANDGRNT', 'INSTSIZE', 'F1SYSTYP', 'F1SYSCOD', 'APPLCN', 'APPLCNM', 'APPLCNW', 
                                      'ADMSSN', 'ADMSSNM', 'ADMSSNW', 'ENRLT', 'ENRLM', 'ENRLW', 'ENRLFT', 'ENRLFTM', 'ENRLFTW', 'ENRLPT', 
                                      'ENRLPTM', 'ENRLPTW', 'SATNUM', 'SATPCT', 'ACTNUM', 'ACTPCT', 'SATVR25', 'SATVR75', 'SATMT25', 
                                      'SATMT75', 'CNTLAFFI', 'PUBPRIME', 'PUBSECON', 'RELAFFIL', 'CALSYS', 'FT_UG', 'FT_FTUG', 'FTGDNIDP', 
                                      'PT_UG', 'PT_FTUG', 'PTGDNIDP', 'DOCPPSP', 'OPENADMP', 'TUITPL', 'DISTNCED', 'DISAB', 'ROOM', 'BOARD']

list_of_unneeded_features_columns = ['DEATHYR', 'CYACTIVE']

target_feature = ['CONVERTED']

columns_with_only_zero_vals = ['DEATHYR', 'CYACTIVE', 'LEVEL12', 'OPENADMP']<jupyter_output><empty_output><jupyter_text>The write the data to the sqlite dB<jupyter_code>df_orgs_data.to_sql('all_data', engine, if_exists='replace', index=False)
df = pd.DataFrame(df_orgs_data.drop(df_orgs_data.select_dtypes(include=['object']), axis=1))
df.drop(columns=columns_with_only_zero_vals, index=1, inplace=True)
df.to_sql('features_target_class', engine, if_exists='replace', index=False)

df.head()<jupyter_output><empty_output><jupyter_text>Standardise the data using the `StandardScalar()` from `sklearn`<jupyter_code>scaler = StandardScaler()

scaler.fit(df.drop('CONVERTED',axis=1))
scaled_features = scaler.transform(df.drop('CONVERTED',axis=1))
feature_columns = list(df.columns)
feature_columns.remove('UNITID')
df_feat = pd.DataFrame(scaled_features, columns=feature_columns)
df_feat.to_sql('normalised_features', engine, if_exists='replace', index=False)
sns.set(font_scale=1.5)
plt.subplots(figsize=(125,100))
sns.heatmap(df_feat.corr(), annot=True, linewidths=2, cmap="YlGnBu", fmt=".2f");<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14775032820145603, 'fraction_numerical': 0.02446592672156582, 'mean_word_length': 4.230961298377029, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 40, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '380880', 'license': 'MIT', 'n_tokens_mistral': 3274, 'n_tokens_neox': 3080, 'n_words': 616, 'path': 'src/2_data_preperation.ipynb', 'repo_name': 'MiloAlphaKilo/Udacity_DSND_Final_Project', 'size': '8379'}