<jupyter_start><jupyter_text>So we have 83 features and 8,921,483 observations, each one unique, we are not time series.Target variable `HasDetections` is categorical and we're being evaluated based on area under the ROC curveLets see how many of these 83 columns are missing values<jupyter_code>na_df = df.isna().sum()/df.shape[0]
plt.figure(figsize=(20,50))

na_df[na_df!=0].sort_values().plot(kind='barh')
plt.show()
na_some = na_df[na_df!=0]
na_some.shape
na_some[na_some<0.5].shape
na_some[na_some>=0.5].shape
df.shape[1]-45<jupyter_output><empty_output><jupyter_text>So we have 7 features that have more than 50% missing valuesand37 with less than 50 % missing values38 features with all values1st pass, drop all columns with missing features, normalize, and see what kind accuracy we get2nd pass, start inspecting the 37 features with missing values and see what kind of improvement each provides us3rd pass, see if there's any additional signal available in the remaining 7 with more than 50% missing, not likley, maybe?<jupyter_code>df_stg1 = df.drop(na_some.index, axis=1)
df_stg1.shape<jupyter_output><empty_output><jupyter_text>Okay, before we can being, each categorical feature needs converted<jupyter_code>df_stg1.dtypes[df_stg1.dtypes==object].index

uniques = df_stg1[df_stg1.dtypes[df_stg1.dtypes==object].index].nunique().sort_values()
uniques<jupyter_output><empty_output><jupyter_text>Many of these have so many categories that perhaps we can 'reduce' the dimensionality, specially with the version numberingLets go one by one from the smallest to the largest to see if there's any obvious reductions that need done<jupyter_code>uniques[uniques.index[0]]
def print_unique(df, column):
    print(f"{column} -> ", df[column].unique())

for column in uniques.index:
    if uniques[column]<30:
        print_unique(df_stg1, column)
# I suspect that PRocessor and Census_OSArchitecture have a strong mapping?
df_stg1[(df_stg1['Processor']=='x64') & (df_stg1['Census_OSArchitecture']!='amd64')].head()

# this is false, processor architecture and OS architecutre can be different, they both stay<jupyter_output><empty_output><jupyter_text>Looking at all of the rest makes me believe that they are all 'valid enough' categories that should stayMoving onto the features that have 30 or more categories<jupyter_code>for column in uniques.index:
    if uniques[column]>=30:
        print_unique(df_stg1, column)
# Lets see if there's much additional signal between Census_OSSkuName and Census_OSEdition

df_stg1.groupby(['Census_OSSkuName','Census_OSEdition']).size().reset_index().rename(columns={0:'count'})<jupyter_output><empty_output><jupyter_text>Nothing easy to combine by, so they stay...we're going to have a lot of one-hot encoded values here, fear of course is that we have a sparse dataset from so many categorical values, perhaps some feature compression will be needed after one-hot encoding<jupyter_code># Now onto AppVersion
df_stg1['AppVersion'].nunique()
df_stg1['AppVersion'].unique()
def split_versions(df, column):
    new_columns = [column + part for part in ['Major', 'Minor', 'Build', 'Revision']]
    df[new_columns] = df[column].str.split('.', expand=True)
    df.drop(column, inplace=True, axis=1)
    return df
# thinking the best way to handle this is to do a AppVersionMajor, AppVersionMinor, AppVersionBuild, AppVersionRevision
for column in ['OsVer', 'EngineVersion', 'AppVersion', 'Census_OSVersion', 'AvSigVersion']:
    print("doing", column)
    df_stg1 = split_versions(df_stg1, column)
df_stg1.head()
# alright, lets see how many more object oriented categories we have left
df_stg1.dtypes[df_stg1.dtypes==object]
# now we need to convert objects to ints where appropriate
for column in df_stg1.dtypes[df_stg1.dtypes==object].index:
    df_stg1[column] = df_stg1[column].astype(int, errors='ignore')
df_stg1.dtypes[df_stg1.dtypes==object]
uniques_v2 = df_stg1[df_stg1.dtypes[df_stg1.dtypes==object].index].nunique().sort_values()
uniques_v2
for column in uniques_v2.index:
    print_unique(df_stg1, column)
# all of those look good to be 1-hot encoded EXCEPT, MachineIdentifier, this is our PrimaryKey and won't train on this anyway, and...
# AvSigVersionMinor has a bad value apparently....


## we have so many observations, that I'm going to drop this one...instead of trying to build a model for this single veature
df_stg1.drop(df_stg1[df_stg1['AvSigVersionMinor']=='2&#x17;3'].index, inplace=True)

# Now lets re-encode this
df_stg1['AvSigVersionMinor'] = df_stg1['AvSigVersionMinor'].astype(int)
# Lets cleanup things that are still objects and need
objs = df_stg1.dtypes[df_stg1.dtypes==object].index.drop('MachineIdentifier')
df_stg1 = pd.get_dummies(df_stg1, columns=objs)
df_stg1.to_csv('../data/processed/stg1.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10486891385767791, 'fraction_numerical': 0.02184769038701623, 'mean_word_length': 6.294385432473445, 'pattern_counts': {'":': 0, '<': 27, '<?xml version=': 0, '>': 28, 'https://': 0, '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': '74694', 'license': 'MIT', 'n_tokens_mistral': 1651, 'n_tokens_neox': 1579, 'n_words': 473, 'path': 'code/.ipynb_checkpoints/1 - Explore Data-checkpoint.ipynb', 'repo_name': 'kfarr3/Kaggle-MS-Malware-Prediction-2019', 'size': '4806'}