<jupyter_start><jupyter_text>Microsoft Malware PredictionThe goal of this competition is to predict a Windows machine’s probability of getting infected by various families of malware, based on different properties of that machine. It is really important to find out whether the computer is infected and cure it.We have a huge dataset of data, where most features are categorical. I think that correct mean encoding should be important. Also the number of columns is quite high so it could be tempting to make some automatical processing for all columns. I personally think that it is important to analyze each variable and it could help to do a better processing.In this kernel I'll do a detailed EDA, feature engineering and modelling.![](https://storage.googleapis.com/kaggle-competitions/kaggle/10683/logos/thumb76_76.png?t=2018-09-19-16-55-15)<jupyter_code>#libraries
import numpy as np 
import pandas as pd 
import os
import seaborn as sns 
import matplotlib.pyplot as plt
%matplotlib inline
plt.style.use('ggplot')
import lightgbm as lgb
import xgboost as xgb
import time
import datetime

from sklearn.preprocessing import LabelEncoder
from sklearn.model_selection import StratifiedKFold, KFold, TimeSeriesSplit
from sklearn.metrics import mean_squared_error, roc_auc_score
from sklearn.linear_model import LogisticRegression, LogisticRegressionCV
import gc
from catboost import CatBoostClassifier
from tqdm import tqdm_notebook
import plotly.offline as py
py.init_notebook_mode(connected=True)
import plotly.graph_objs as go
import plotly.tools as tls

import warnings
warnings.filterwarnings("ignore")

import logging

logging.basicConfig(filename='log.txt',level=logging.DEBUG, format='%(asctime)s %(message)s')

pd.set_option('max_colwidth', 500)
pd.set_option('max_columns', 500)
pd.set_option('max_rows', 100)
import os
print(os.listdir("../input/"))<jupyter_output><empty_output><jupyter_text>Loading dataLet's try loading data in a naive way<jupyter_code>#https://www.kaggle.com/theoviel/load-the-totality-of-the-data
dtypes = {
        'MachineIdentifier':                                    'category',
        'ProductName':                                          'category',
        'EngineVersion':                                        'category',
        'AppVersion':                                           'category',
        'AvSigVersion':                                         'category',
        'IsBeta':                                               'int8',
        'RtpStateBitfield':                                     'float16',
        'IsSxsPassiveMode':                                     'int8',
        'DefaultBrowsersIdentifier':                            'float16',
        'AVProductStatesIdentifier':                            'float32',
        'AVProductsInstalled':                                  'float16',
        'AVProductsEnabled':                                    'float16',
        'HasTpm':                                               'int8',
        'CountryIdentifier':                                    'int16',
        'CityIdentifier':                                       'float32',
        'OrganizationIdentifier':                               'float16',
        'GeoNameIdentifier':                                    'float16',
        'LocaleEnglishNameIdentifier':                          'int8',
        'Platform':                                             'category',
        'Processor':                                            'category',
        'OsVer':                                                'category',
        'OsBuild':                                              'int16',
        'OsSuite':                                              'int16',
        'OsPlatformSubRelease':                                 'category',
        'OsBuildLab':                                           'category',
        'SkuEdition':                                           'category',
        'IsProtected':                                          'float16',
        'AutoSampleOptIn':                                      'int8',
        'PuaMode':                                              'category',
        'SMode':                                                'float16',
        'IeVerIdentifier':                                      'float16',
        'SmartScreen':                                          'category',
        'Firewall':                                             'float16',
        'UacLuaenable':                                         'float32',
        'Census_MDC2FormFactor':                                'category',
        'Census_DeviceFamily':                                  'category',
        'Census_OEMNameIdentifier':                             'float16',
        'Census_OEMModelIdentifier':                            'float32',
        'Census_ProcessorCoreCount':                            'float16',
        'Census_ProcessorManufacturerIdentifier':               'float16',
        'Census_ProcessorModelIdentifier':                      'float16',
        'Census_ProcessorClass':                                'category',
        'Census_PrimaryDiskTotalCapacity':                      'float32',
        'Census_PrimaryDiskTypeName':                           'category',
        'Census_SystemVolumeTotalCapacity':                     'float32',
        'Census_HasOpticalDiskDrive':                           'int8',
        'Census_TotalPhysicalRAM':                              'float32',
        'Census_ChassisTypeName':                               'category',
        'Census_InternalPrimaryDiagonalDisplaySizeInInches':    'float16',
        'Census_InternalPrimaryDisplayResolutionHorizontal':    'float16',
        'Census_InternalPrimaryDisplayResolutionVertical':      'float16',
        'Census_PowerPlatformRoleName':                         'category',
        'Census_InternalBatteryType':                           'category',
        'Census_InternalBatteryNumberOfCharges':                'float32',
        'Census_OSVersion':                                     'category',
        'Census_OSArchitecture':                                'category',
        'Census_OSBranch':                                      'category',
        'Census_OSBuildNumber':                                 'int16',
        'Census_OSBuildRevision':                               'int32',
        'Census_OSEdition':                                     'category',
        'Census_OSSkuName':                                     'category',
        'Census_OSInstallTypeName':                             'category',
        'Census_OSInstallLanguageIdentifier':                   'float16',
        'Census_OSUILocaleIdentifier':                          'int16',
        'Census_OSWUAutoUpdateOptionsName':                     'category',
        'Census_IsPortableOperatingSystem':                     'int8',
        'Census_GenuineStateName':                              'category',
        'Census_ActivationChannel':                             'category',
        'Census_IsFlightingInternal':                           'float16',
        'Census_IsFlightsDisabled':                             'float16',
        'Census_FlightRing':                                    'category',
        'Census_ThresholdOptIn':                                'float16',
        'Census_FirmwareManufacturerIdentifier':                'float16',
        'Census_FirmwareVersionIdentifier':                     'float32',
        'Census_IsSecureBootEnabled':                           'int8',
        'Census_IsWIMBootEnabled':                              'float16',
        'Census_IsVirtualDevice':                               'float16',
        'Census_IsTouchEnabled':                                'int8',
        'Census_IsPenCapable':                                  'int8',
        'Census_IsAlwaysOnAlwaysConnectedCapable':              'float16',
        'Wdft_IsGamer':                                         'float16',
        'Wdft_RegionIdentifier':                                'float16',
        'HasDetections':                                        'int8'
        }

def reduce_mem_usage(df, verbose=True):
    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']
    start_mem = df.memory_usage(deep=True).sum() / 1024**2    
    for col in df.columns:
        col_type = df[col].dtypes
        if col_type in numerics:
            c_min = df[col].min()
            c_max = df[col].max()
            if str(col_type)[:3] == 'int':
                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:
                    df[col] = df[col].astype(np.int8)
                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:
                    df[col] = df[col].astype(np.int16)
                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:
                    df[col] = df[col].astype(np.int32)
                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:
                    df[col] = df[col].astype(np.int64)  
            else:
                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:
                    df[col] = df[col].astype(np.float16)
                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:
                    df[col] = df[col].astype(np.float32)
                else:
                    df[col] = df[col].astype(np.float64)    
    end_mem = df.memory_usage(deep=True).sum() / 1024**2
    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))
    return df
numerics = ['int8', 'int16', 'int32', 'int64', 'float16', 'float32', 'float64']
numerical_columns = [c for c,v in dtypes.items() if v in numerics]
categorical_columns = [c for c,v in dtypes.items() if v not in numerics]
%%time
train = pd.read_csv('../input/train.csv', dtype=dtypes)
train = reduce_mem_usage(train)
stats = []
for col in train.columns:
    stats.append((col, train[col].nunique(), train[col].isnull().sum() * 100 / train.shape[0], train[col].value_counts(normalize=True, dropna=False).values[0] * 100, train[col].dtype))
    
stats_df = pd.DataFrame(stats, columns=['Feature', 'Unique_values', 'Percentage of missing values', 'Percentage of values in the biggest category', 'type'])
stats_df.sort_values('Percentage of missing values', ascending=False)<jupyter_output><empty_output><jupyter_text>We can see several interesting things here:* PuaMode and Census_ProcessorClass have 99%+ missing values, which means that these columns are useless and should be dropped;* In DefaultBrowsersIdentifier column 95% values belong to one category, so I think this columns is also useless;* Census_IsFlightingInternal is strange, but maybe analysis of it will make sense;* There are 26 columns in total in which one category contains 90% values. I think that these imbalanced columns should be removed from the dataset;* One more important point is that there are many columns which are considered to be numerical (from this kernel: https://www.kaggle.com/theoviel/load-the-totality-of-the-data), but I think they are categorical - like CityIdentifier. We'll see more in EDA below;I see that all columns except Census_SystemVolumeTotalCapacity are categorical. Also there are 3 columns, where most of the values are missing. Let's drop them.<jupyter_code>good_cols = list(train.columns)
for col in train.columns:
    rate = train[col].value_counts(normalize=True, dropna=False).values[0]
    if rate > 0.9:
        good_cols.remove(col)
train = train[good_cols]<jupyter_output><empty_output><jupyter_text>Now we can read test data.<jupyter_code>test_dtypes = {k: v for k, v in dtypes.items() if k in good_cols}
test = pd.read_csv('../input/test.csv', dtype=test_dtypes, usecols=good_cols[:-1])
test.loc[6529507, 'OsBuildLab'] = '17134.1.amd64fre.rs4_release.180410-1804'
test['OsBuildLab'] = test['OsBuildLab'].fillna('17134.1.amd64fre.rs4_release.180410-1804')
test = reduce_mem_usage(test)<jupyter_output>Mem. usage decreased to 1826.49 Mb (0.0% reduction)<jupyter_text>Data exploration<jupyter_code>train.head()
# function to plot data
def plot_categorical_feature(col, only_bars=False, top_n=10, by_touch=False):
    top_n = top_n if train[col].nunique() > top_n else train[col].nunique()
    print(f"{col} has {train[col].nunique()} unique values and type: {train[col].dtype}.")
    print(train[col].value_counts(normalize=True, dropna=False).head())
    if not by_touch:
        if not only_bars:
            df = train.groupby([col]).agg({'HasDetections': ['count', 'mean']})
            df = df.sort_values(('HasDetections', 'count'), ascending=False).head(top_n).sort_index()
            data = [go.Bar(x=df.index, y=df['HasDetections']['count'].values, name='counts'),
                    go.Scatter(x=df.index, y=df['HasDetections']['mean'], name='Detections rate', yaxis='y2')]

            layout = go.Layout(dict(title = f"Counts of {col} by top-{top_n} categories and mean target value",
                                xaxis = dict(title = f'{col}',
                                             showgrid=False,
                                             zeroline=False,
                                             showline=False,),
                                yaxis = dict(title = 'Counts',
                                             showgrid=False,
                                             zeroline=False,
                                             showline=False,),
                                yaxis2=dict(title='Detections rate', overlaying='y', side='right')),
                           legend=dict(orientation="v"))

        else:
            top_cat = list(train[col].value_counts(dropna=False).index[:top_n])
            df0 = train.loc[(train[col].isin(top_cat)) & (train['HasDetections'] == 1), col].value_counts().head(10).sort_index()
            df1 = train.loc[(train[col].isin(top_cat)) & (train['HasDetections'] == 0), col].value_counts().head(10).sort_index()
            data = [go.Bar(x=df0.index, y=df0.values, name='Has Detections'),
                    go.Bar(x=df1.index, y=df1.values, name='No Detections')]

            layout = go.Layout(dict(title = f"Counts of {col} by top-{top_n} categories",
                                xaxis = dict(title = f'{col}',
                                             showgrid=False,
                                             zeroline=False,
                                             showline=False,),
                                yaxis = dict(title = 'Counts',
                                             showgrid=False,
                                             zeroline=False,
                                             showline=False,),
                                ),
                           legend=dict(orientation="v"), barmode='group')
        
        py.iplot(dict(data=data, layout=layout))
        
    else:
        top_n = 10
        top_cat = list(train[col].value_counts(dropna=False).index[:top_n])
        df = train.loc[train[col].isin(top_cat)]

        df1 = train.loc[train['Census_IsTouchEnabled'] == 1]
        df0 = train.loc[train['Census_IsTouchEnabled'] == 0]

        df0_ = df0.groupby([col]).agg({'HasDetections': ['count', 'mean']})
        df0_ = df0_.sort_values(('HasDetections', 'count'), ascending=False).head(top_n).sort_index()
        df1_ = df1.groupby([col]).agg({'HasDetections': ['count', 'mean']})
        df1_ = df1_.sort_values(('HasDetections', 'count'), ascending=False).head(top_n).sort_index()
        data1 = [go.Bar(x=df0_.index, y=df0_['HasDetections']['count'].values, name='Nontouch device counts'),
                go.Scatter(x=df0_.index, y=df0_['HasDetections']['mean'], name='Detections rate for nontouch devices', yaxis='y2')]
        data2 = [go.Bar(x=df1_.index, y=df1_['HasDetections']['count'].values, name='Touch device counts'),
                go.Scatter(x=df1_.index, y=df1_['HasDetections']['mean'], name='Detections rate for touch devices', yaxis='y2')]

        layout = go.Layout(dict(title = f"Counts of {col} by top-{top_n} categories for nontouch devices",
                            xaxis = dict(title = f'{col}',
                                         showgrid=False,
                                         zeroline=False,
                                         showline=False,
                                         type='category'),
                            yaxis = dict(title = 'Counts',
                                         showgrid=False,
                                         zeroline=False,
                                         showline=False,),
                                    yaxis2=dict(title='Detections rate', overlaying='y', side='right'),
                            ),
                       legend=dict(orientation="v"), barmode='group')

        py.iplot(dict(data=data1, layout=layout))
        layout['title'] = f"Counts of {col} by top-{top_n} categories for touch devices"
        py.iplot(dict(data=data2, layout=layout))<jupyter_output><empty_output><jupyter_text>Target<jupyter_code>train['HasDetections'].value_counts()<jupyter_output><empty_output><jupyter_text>The target is balanced, which is nice.  Census_IsTouchEnabled<jupyter_code>plot_categorical_feature('Census_IsTouchEnabled', True)<jupyter_output>Census_IsTouchEnabled has 2 unique values and type: int8.
0    0.874457
1    0.125543
Name: Census_IsTouchEnabled, dtype: float64<jupyter_text>As expected Microsoft has much more computers that touch devices. The rate of infections is lower for touch devices, but not by much.  Variables with high amount of unique valuesAt first I'll have a look at variables  with a lot of categories. Then I'll move to more interesting variables with a limited amount of categories.  EngineVersion<jupyter_code>plot_categorical_feature('EngineVersion', by_touch=True)<jupyter_output>EngineVersion has 70 unique values and type: category.
1.1.15200.1    0.430990
1.1.15100.1    0.412030
1.1.15000.2    0.029728
1.1.14901.4    0.023809
1.1.14600.4    0.018000
Name: EngineVersion, dtype: float64<jupyter_text>We can see that two categories take 84% of all values. The difference in detection rates is noticable. Other categories have different detection rate, but it is simply due to the low number of samples in them.Patterns for touch and non-touch devices are quite similar.  AppVersion<jupyter_code>plot_categorical_feature('AppVersion')<jupyter_output>AppVersion has 110 unique values and type: category.
4.18.1807.18075    0.576050
4.18.1806.18062    0.095380
4.12.16299.15      0.040338
4.10.209.0         0.030539
4.13.17134.1       0.028837
Name: AppVersion, dtype: float64<jupyter_text>In this case we have one main category with 0.53 detection rate, other categories are much smaller.  AvSigVersion<jupyter_code>plot_categorical_feature('AvSigVersion')<jupyter_output>AvSigVersion has 8531 unique values and type: category.
1.273.1420.0    0.011469
1.263.48.0      0.010987
1.275.1140.0    0.010899
1.275.727.0     0.010362
1.273.371.0     0.009748
Name: AvSigVersion, dtype: float64<jupyter_text>This feature has a huge amount of categories, it seems this is a version of an often updated software.  AVProductStatesIdentifier<jupyter_code>plot_categorical_feature('AVProductStatesIdentifier', True, 10)<jupyter_output>AVProductStatesIdentifier has 28970 unique values and type: float32.
53447.0    0.652870
7945.0     0.053343
47238.0    0.036727
62773.0    0.029901
46413.0    0.012652
Name: AVProductStatesIdentifier, dtype: float64<jupyter_text>This is definitely a categorical variable.<jupyter_code>train['AVProductStatesIdentifier'] = train['AVProductStatesIdentifier'].astype('category')
test['AVProductStatesIdentifier'] = test['AVProductStatesIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>AVProductsInstalled<jupyter_code>plot_categorical_feature('AVProductsInstalled', True)<jupyter_output>AVProductsInstalled has 8 unique values and type: float16.
 1.0    0.695949
 2.0    0.275628
 3.0    0.023326
NaN     0.004060
 4.0    0.000982
Name: AVProductsInstalled, dtype: float64<jupyter_text>Hm, interesting. If a computer has an antivirus, it is less likely to be infected. But having two antiviruses has an opposite effect. Maybe those who install 2 antiviruses tend to have less experience with working on PC?Also other categories have really low samples, let's combine them.<jupyter_code># train.loc[train['AVProductsInstalled'].isin([1, 2]) == False, 'AVProductsInstalled'] = 3
# test.loc[test['AVProductsInstalled'].isin([1, 2]) == False, 'AVProductsInstalled'] = 3
train['AVProductsInstalled'] = train['AVProductsInstalled'].astype('category')
test['AVProductsInstalled'] = test['AVProductsInstalled'].astype('category')
plot_categorical_feature('AVProductsInstalled', True, by_touch=True)<jupyter_output>AVProductsInstalled has 8 unique values and type: category.
 1.0    0.695949
 2.0    0.275628
 3.0    0.023326
NaN     0.004060
 4.0    0.000982
Name: AVProductsInstalled, dtype: float64<jupyter_text>It is interesting, that even on touch devices people sometimes install 2 antiviruses.  CountryIdentifier<jupyter_code>plot_categorical_feature('CountryIdentifier', True, 20)<jupyter_output>CountryIdentifier has 222 unique values and type: int16.
43     0.044519
29     0.039006
141    0.037372
93     0.031791
171    0.031449
Name: CountryIdentifier, dtype: float64<jupyter_text>Once again we have a categorical column which was defined as numerical. While most countries have rate of detections ~50%, there are some countries where there are much more infected devices.<jupyter_code>train['CountryIdentifier'] = train['CountryIdentifier'].astype('category')
test['CountryIdentifier'] = test['CountryIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>CityIdentifierThe same situation for cities.<jupyter_code>plot_categorical_feature('CityIdentifier', True, 20)
train['CityIdentifier'] = train['CityIdentifier'].astype('category')
test['CityIdentifier'] = test['CityIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>OrganizationIdentifier<jupyter_code>plot_categorical_feature('OrganizationIdentifier', True)<jupyter_output>OrganizationIdentifier has 49 unique values and type: float16.
 27.0    0.470377
NaN      0.308415
 18.0    0.197745
 48.0    0.007156
 50.0    0.005100
Name: OrganizationIdentifier, dtype: float64<jupyter_text>This is quuite strange. 2 organizations cover ~66% of all computers, while unknown organizations have 30% more. Maybe these are some specific industries? Let's combine values.<jupyter_code># train.loc[train['OrganizationIdentifier'].isin([27, 18]) == False, 'OrganizationIdentifier'] = 48
# test.loc[test['OrganizationIdentifier'].isin([27, 18]) == False, 'OrganizationIdentifier'] = 48
train['OrganizationIdentifier'] = train['OrganizationIdentifier'].astype('category')
test['OrganizationIdentifier'] = test['OrganizationIdentifier'].astype('category')
plot_categorical_feature('OrganizationIdentifier', True, by_touch=True)<jupyter_output>OrganizationIdentifier has 49 unique values and type: category.
 27.0    0.470377
NaN      0.308415
 18.0    0.197745
 48.0    0.007156
 50.0    0.005100
Name: OrganizationIdentifier, dtype: float64<jupyter_text>GeoNameIdentifier<jupyter_code>plot_categorical_feature('GeoNameIdentifier', True)
train['GeoNameIdentifier'] = train['GeoNameIdentifier'].astype('category')
test['GeoNameIdentifier'] = test['GeoNameIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>LocaleEnglishNameIdentifier<jupyter_code>plot_categorical_feature('LocaleEnglishNameIdentifier', True)
train['LocaleEnglishNameIdentifier'] = train['LocaleEnglishNameIdentifier'].astype('category')
test['LocaleEnglishNameIdentifier'] = test['LocaleEnglishNameIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>OsPlatformSubRelease<jupyter_code>plot_categorical_feature('OsPlatformSubRelease', True, by_touch=True)<jupyter_output>OsPlatformSubRelease has 9 unique values and type: category.
rs4    0.438887
rs3    0.280635
rs2    0.087460
rs1    0.081917
th2    0.046136
Name: OsPlatformSubRelease, dtype: float64<jupyter_text>It is interesting that most computers have Windows 10 (rs*). I suppose Microsoft specifically chose them, so that we would work with modern devices?  OsBuildLab<jupyter_code>plot_categorical_feature('OsBuildLab', True)<jupyter_output>OsBuildLab has 663 unique values and type: category.
17134.1.amd64fre.rs4_release.180410-1804                 0.410044
16299.431.amd64fre.rs3_release_svc_escrow.180502-1908    0.140411
16299.15.amd64fre.rs3_release.170928-1534                0.107724
15063.0.amd64fre.rs2_release.170317-1834                 0.080484
17134.1.x86fre.rs4_release.180410-1804                   0.028815
Name: OsBuildLab, dtype: float64<jupyter_text>IeVerIdentifier<jupyter_code>plot_categorical_feature('IeVerIdentifier', True)
train['IeVerIdentifier'] = train['IeVerIdentifier'].astype('category')
test['IeVerIdentifier'] = test['IeVerIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_OEMNameIdentifier<jupyter_code>plot_categorical_feature('Census_OEMNameIdentifier', True)
train['Census_OEMNameIdentifier'] = train['Census_OEMNameIdentifier'].astype('category')
test['Census_OEMNameIdentifier'] = test['Census_OEMNameIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_OEMModelIdentifier<jupyter_code>plot_categorical_feature('Census_OEMModelIdentifier', True)
train['Census_OEMModelIdentifier'] = train['Census_OEMModelIdentifier'].astype('category')
test['Census_OEMModelIdentifier'] = test['Census_OEMModelIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_ProcessorCoreCount<jupyter_code>plot_categorical_feature('Census_ProcessorCoreCount', True, by_touch=True)<jupyter_output>Census_ProcessorCoreCount has 45 unique values and type: float16.
4.0     0.608665
2.0     0.259146
8.0     0.096957
12.0    0.010391
1.0     0.007890
Name: Census_ProcessorCoreCount, dtype: float64<jupyter_text>Most computers have 2, 4 or 8 cores. For touch deviced 4 cores are much more common than other configurations. And these 3 variants cover 95% of all samples,<jupyter_code># train.loc[train['Census_ProcessorCoreCount'].isin([2, 4, 8, 12]) == False, 'Census_ProcessorCoreCount'] = 1
# test.loc[test['Census_ProcessorCoreCount'].isin([2, 4, 8, 12]) == False, 'Census_ProcessorCoreCount'] = 1<jupyter_output><empty_output><jupyter_text>Census_ProcessorModelIdentifier<jupyter_code>plot_categorical_feature('Census_ProcessorModelIdentifier', True)
train['Census_ProcessorModelIdentifier'] = train['Census_ProcessorModelIdentifier'].astype('category')
test['Census_ProcessorModelIdentifier'] = test['Census_ProcessorModelIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_PrimaryDiskTotalCapacity<jupyter_code>plot_categorical_feature('Census_PrimaryDiskTotalCapacity', True)<jupyter_output>Census_PrimaryDiskTotalCapacity has 5735 unique values and type: float32.
476940.0    0.318504
953869.0    0.243881
305245.0    0.053199
122104.0    0.052576
244198.0    0.050696
Name: Census_PrimaryDiskTotalCapacity, dtype: float64<jupyter_text>Census_SystemVolumeTotalCapacity<jupyter_code>plot_categorical_feature('Census_SystemVolumeTotalCapacity', True)<jupyter_output>Census_SystemVolumeTotalCapacity has 536848 unique values and type: float32.
NaN          0.005941
 28542.0     0.005828
 926992.0    0.005653
 476389.0    0.004981
 953253.0    0.004660
Name: Census_SystemVolumeTotalCapacity, dtype: float64<jupyter_text>I think that this is a first truly numerical variable. Census_PrimaryDiskTotalCapacity could be numerical, but it has too little unique values and every category could be considered a separate disk model.  Census_TotalPhysicalRAM<jupyter_code>plot_categorical_feature('Census_TotalPhysicalRAM', True, by_touch=True)<jupyter_output>Census_TotalPhysicalRAM has 3446 unique values and type: float32.
4096.0     0.458950
8192.0     0.246204
2048.0     0.123015
16384.0    0.059582
6144.0     0.044687
Name: Census_TotalPhysicalRAM, dtype: float64<jupyter_text>Quite interesting that most computers have <=8 Gb RAM.<jupyter_code># top_10 = train['Census_TotalPhysicalRAM'].value_counts(dropna=False, normalize=True).cumsum().index[:10]
# train.loc[train['Census_TotalPhysicalRAM'].isin(top_10) == False, 'Census_TotalPhysicalRAM'] = 1000
# test.loc[test['Census_TotalPhysicalRAM'].isin(top_10) == False, 'Census_TotalPhysicalRAM'] = 1000<jupyter_output><empty_output><jupyter_text>Census_InternalPrimaryDiagonalDisplaySizeInInches<jupyter_code>plot_categorical_feature('Census_InternalPrimaryDiagonalDisplaySizeInInches', True, by_touch=True)<jupyter_output>Census_InternalPrimaryDiagonalDisplaySizeInInches has 785 unique values and type: float16.
15.500000    0.341583
13.898438    0.106717
14.000000    0.060803
11.601562    0.035799
21.500000    0.030862
Name: Census_InternalPrimaryDiagonalDisplaySizeInInches, dtype: float64<jupyter_text>I'm really surprised that most computers have 15 inch screens. And this is a rare situation, when for some categories detection rate on PC is higher than on touch devices.  Census_InternalPrimaryDisplayResolutionHorizontal<jupyter_code>plot_categorical_feature('Census_InternalPrimaryDisplayResolutionHorizontal', True, by_touch=True)<jupyter_output>Census_InternalPrimaryDisplayResolutionHorizontal has 2050 unique values and type: float16.
1366.0    0.506089
1920.0    0.248910
1280.0    0.059119
1600.0    0.056189
1024.0    0.038404
Name: Census_InternalPrimaryDisplayResolutionHorizontal, dtype: float64<jupyter_text>Census_InternalPrimaryDisplayResolutionVertical<jupyter_code>plot_categorical_feature('Census_InternalPrimaryDisplayResolutionVertical', True, by_touch=True)<jupyter_output>Census_InternalPrimaryDisplayResolutionVertical has 1552 unique values and type: float16.
768.0     0.557488
1080.0    0.240812
900.0     0.073436
800.0     0.029374
1024.0    0.020885
Name: Census_InternalPrimaryDisplayResolutionVertical, dtype: float64<jupyter_text>Census_InternalBatteryNumberOfCharges<jupyter_code>plot_categorical_feature('Census_InternalBatteryNumberOfCharges', True, by_touch=True)<jupyter_output>Census_InternalBatteryNumberOfCharges has 41087 unique values and type: float32.
 0.000000e+00    0.566431
 4.294967e+09    0.253769
NaN              0.030124
 1.000000e+00    0.006032
 2.000000e+00    0.003153
Name: Census_InternalBatteryNumberOfCharges, dtype: float64<jupyter_text>Interesting and somewhat strange results. It is expected that most PCs have 0 charges. 4.294967e+09 seems to be some kind of technical value. But having several charges for PC is strange. Anyway, this is definitely a categorical variable.<jupyter_code>train['Census_InternalBatteryNumberOfCharges'] = train['Census_InternalBatteryNumberOfCharges'].astype('category')
test['Census_InternalBatteryNumberOfCharges'] = test['Census_InternalBatteryNumberOfCharges'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_OSVersion<jupyter_code>plot_categorical_feature('Census_OSVersion', True)<jupyter_output>Census_OSVersion has 469 unique values and type: category.
10.0.17134.228    0.158452
10.0.17134.165    0.100848
10.0.16299.431    0.061262
10.0.17134.285    0.052713
10.0.16299.547    0.038878
Name: Census_OSVersion, dtype: float64<jupyter_text>Census_OSBranch<jupyter_code>plot_categorical_feature('Census_OSBranch', True)<jupyter_output>Census_OSBranch has 32 unique values and type: category.
rs4_release               0.449382
rs3_release               0.138690
rs3_release_svc_escrow    0.134481
rs2_release               0.089342
rs1_release               0.088050
Name: Census_OSBranch, dtype: float64<jupyter_text>Census_OSBuildNumber<jupyter_code>plot_categorical_feature('Census_OSBuildNumber', True)
train['Census_OSBuildNumber'] = train['Census_OSBuildNumber'].astype('category')
test['Census_OSBuildNumber'] = test['Census_OSBuildNumber'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_OSBuildRevision<jupyter_code>plot_categorical_feature('Census_OSBuildRevision', True)
train['Census_OSBuildRevision'] = train['Census_OSBuildRevision'].astype('category')
test['Census_OSBuildRevision'] = test['Census_OSBuildRevision'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_FirmwareManufacturerIdentifier<jupyter_code>plot_categorical_feature('Census_FirmwareManufacturerIdentifier', True)
train['Census_FirmwareManufacturerIdentifier'] = train['Census_FirmwareManufacturerIdentifier'].astype('category')
test['Census_FirmwareManufacturerIdentifier'] = test['Census_FirmwareManufacturerIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_FirmwareVersionIdentifier<jupyter_code>plot_categorical_feature('Census_FirmwareVersionIdentifier', True)
train['Census_FirmwareVersionIdentifier'] = train['Census_FirmwareVersionIdentifier'].astype('category')
test['Census_FirmwareVersionIdentifier'] = test['Census_FirmwareVersionIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>OsBuild<jupyter_code>plot_categorical_feature('OsBuild', True)
train['OsBuild'] = train['OsBuild'].astype('category')
test['OsBuild'] = test['OsBuild'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_ChassisTypeName<jupyter_code>plot_categorical_feature('Census_ChassisTypeName', True, by_touch=True)<jupyter_output>Census_ChassisTypeName has 52 unique values and type: category.
Notebook    0.588334
Desktop     0.209845
Laptop      0.076846
Portable    0.040453
AllinOne    0.022899
Name: Census_ChassisTypeName, dtype: float64<jupyter_text>Census_InternalBatteryType<jupyter_code># https://www.kaggle.com/youhanlee/my-eda-i-want-to-see-all
# grouping battary types by name
def group_battery(x):
    x = x.lower()
    if 'li' in x:
        return 1
    else:
        return 0
    
train['Census_InternalBatteryType'] = train['Census_InternalBatteryType'].apply(group_battery)
test['Census_InternalBatteryType'] = test['Census_InternalBatteryType'].apply(group_battery)
plot_categorical_feature('Census_InternalBatteryType', True)<jupyter_output>Census_InternalBatteryType has 2 unique values and type: int64.
0    0.732778
1    0.267222
Name: Census_InternalBatteryType, dtype: float64<jupyter_text>Census_OSEditionI'll combine similar versions into one.<jupyter_code>def rename_edition(x):
    x = x.lower()
    if 'core' in x:
        return 'Core'
    elif 'pro' in x:
        return 'pro'
    elif 'enterprise' in x:
        return 'Enterprise'
    elif 'server' in x:
        return 'Server'
    elif 'home' in x:
        return 'Home'
    elif 'education' in x:
        return 'Education'
    elif 'cloud' in x:
        return 'Cloud'
    else:
        return x
train['Census_OSEdition'] = train['Census_OSEdition'].astype(str)
test['Census_OSEdition'] = test['Census_OSEdition'].astype(str)
train['Census_OSEdition'] = train['Census_OSEdition'].apply(rename_edition)
test['Census_OSEdition'] = test['Census_OSEdition'].apply(rename_edition)
train['Census_OSEdition'] = train['Census_OSEdition'].astype('category')
test['Census_OSEdition'] = test['Census_OSEdition'].astype('category')
plot_categorical_feature('Census_OSEdition', True, by_touch=True)<jupyter_output>Census_OSEdition has 8 unique values and type: category.
Core          0.626167
pro           0.360472
Enterprise    0.006373
Education     0.004667
Server        0.001615
Name: Census_OSEdition, dtype: float64<jupyter_text>Census_OSSkuNameAlmost the same as previous variable<jupyter_code>train['Census_OSSkuName'] = train['Census_OSSkuName'].astype(str)
test['Census_OSSkuName'] = test['Census_OSSkuName'].astype(str)
train['Census_OSSkuName'] = train['Census_OSSkuName'].apply(rename_edition)
test['Census_OSSkuName'] = test['Census_OSSkuName'].apply(rename_edition)
train['Census_OSSkuName'] = train['Census_OSSkuName'].astype('category')
test['Census_OSSkuName'] = test['Census_OSSkuName'].astype('category')
plot_categorical_feature('Census_OSSkuName', True, by_touch=True)<jupyter_output>Census_OSSkuName has 10 unique values and type: category.
Core          0.626093
pro           0.360543
Enterprise    0.006374
Education     0.004680
Server        0.001616
Name: Census_OSSkuName, dtype: float64<jupyter_text>Census_OSInstallLanguageIdentifierSadly we don't know what mean these numbers.<jupyter_code>plot_categorical_feature('Census_OSInstallLanguageIdentifier', True)
train['Census_OSInstallLanguageIdentifier'] = train['Census_OSInstallLanguageIdentifier'].astype('category')
test['Census_OSInstallLanguageIdentifier'] = test['Census_OSInstallLanguageIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_OSUILocaleIdentifier<jupyter_code>plot_categorical_feature('Census_OSUILocaleIdentifier', True)
train['Census_OSUILocaleIdentifier'] = train['Census_OSUILocaleIdentifier'].astype('category')
test['Census_OSUILocaleIdentifier'] = test['Census_OSUILocaleIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>Census_OSUILocaleIdentifier<jupyter_code>plot_categorical_feature('Census_OSUILocaleIdentifier', True)<jupyter_output>Census_OSUILocaleIdentifier has 147 unique values and type: category.
31     0.355414
34     0.116577
30     0.057613
125    0.055847
49     0.048948
Name: Census_OSUILocaleIdentifier, dtype: float64<jupyter_text>OsSuite<jupyter_code>plot_categorical_feature('OsSuite', True)
train['OsSuite'] = train['OsSuite'].astype('category')
test['OsSuite'] = test['OsSuite'].astype('category')<jupyter_output><empty_output><jupyter_text>Wdft_RegionIdentifier<jupyter_code>plot_categorical_feature('Wdft_RegionIdentifier', True)
train['Wdft_RegionIdentifier'] = train['Wdft_RegionIdentifier'].astype('category')
test['Wdft_RegionIdentifier'] = test['Wdft_RegionIdentifier'].astype('category')<jupyter_output><empty_output><jupyter_text>SkuEdition<jupyter_code>train['SkuEdition'].value_counts(dropna=False, normalize=True)<jupyter_output><empty_output><jupyter_text>Home and Pro editions together give 97.9%+ of all values. Condidering that other categories are for Enterprise mostly, I combine them with Pro.<jupyter_code># train.loc[train['SkuEdition'] != 'Home', 'SkuEdition'] = 'Pro'
# test.loc[test['SkuEdition'] != 'Home', 'SkuEdition'] = 'Pro'

# train['SkuEdition'] = train['SkuEdition'].cat.remove_unused_categories()
# test['SkuEdition'] = test['SkuEdition'].cat.remove_unused_categories()
pd.crosstab(train['SkuEdition'], train['Census_OSEdition'], normalize='columns')<jupyter_output><empty_output><jupyter_text>It seems that Home Sku edition corresponds to Core OS Edition, and Pro Sku edition corresponts to all other OS editions.  SmartScreen<jupyter_code>train['SmartScreen'].value_counts(dropna=False, normalize=True).cumsum()<jupyter_output><empty_output><jupyter_text>As per description: If the value exists but is blank, the value "ExistsNotSet" is sent in telemetry.So missing values and ExistsNotSet are in fact the same. This category + RequireAdmin + Off + Warn are 99.3% of all values. I'll combine all other values into Prompt.<jupyter_code># train.loc[train['SmartScreen'].isnull(), 'SmartScreen'] = 'ExistsNotSet'
# test.loc[test['SmartScreen'].isnull(), 'SmartScreen'] = 'ExistsNotSet'
# train.loc[train['SmartScreen'].isin(['RequireAdmin', 'ExistsNotSet', 'Off', 'Warn']) == False, 'SmartScreen'] = 'Prompt'
# test.loc[test['SmartScreen'].isin(['RequireAdmin', 'ExistsNotSet', 'Off', 'Warn']) == False, 'SmartScreen'] = 'Prompt'

# train['SmartScreen'] = train['SmartScreen'].cat.remove_unused_categories()
# test['SmartScreen'] = test['SmartScreen'].cat.remove_unused_categories()
sns.set(rc={'figure.figsize':(15, 8)})
sns.countplot(x="SmartScreen", hue="HasDetections",  palette="PRGn", data=train)
plt.title("SmartScreen counts")
plt.xticks(rotation='vertical')
plt.show()<jupyter_output><empty_output><jupyter_text>Census_MDC2FormFactor<jupyter_code>train['Census_MDC2FormFactor'].value_counts(dropna=False, normalize=True).cumsum()
# top_cats = list(train['Census_MDC2FormFactor'].value_counts().index[:5])
# train.loc[train['Census_MDC2FormFactor'].isin(top_cats) == False, 'Census_MDC2FormFactor'] = 'PCOther'
# test.loc[test['Census_MDC2FormFactor'].isin(top_cats) == False, 'Census_MDC2FormFactor'] = 'PCOther'

# train['Census_MDC2FormFactor'] = train['Census_MDC2FormFactor'].cat.remove_unused_categories()
# test['Census_MDC2FormFactor'] = test['Census_MDC2FormFactor'].cat.remove_unused_categories()
plot_categorical_feature('Census_MDC2FormFactor', True)
sns.catplot(x="Census_PrimaryDiskTypeName", hue="HasDetections", col="Census_MDC2FormFactor",
                data=train, kind="count",col_wrap=3);<jupyter_output><empty_output><jupyter_text>It is nice to see that these two variables interact reasonably.Detachable devices usually have SSD, desktop PC have HDD and SDD and Notebooks usually have HDD.<jupyter_code>train['Census_PrimaryDiskTypeName'].value_counts(dropna=False, normalize=True).cumsum()
# train.loc[train['Census_PrimaryDiskTypeName'].isin(['HDD', 'SSD']) == False, 'Census_PrimaryDiskTypeName'] = 'UNKNOWN'
# test.loc[test['Census_PrimaryDiskTypeName'].isin(['HDD', 'SSD']) == False, 'Census_PrimaryDiskTypeName'] = 'UNKNOWN'

# train['Census_PrimaryDiskTypeName'] = train['Census_PrimaryDiskTypeName'].cat.remove_unused_categories()
# test['Census_PrimaryDiskTypeName'] = test['Census_PrimaryDiskTypeName'].cat.remove_unused_categories()<jupyter_output><empty_output><jupyter_text>Census_ProcessorManufacturerIdentifier<jupyter_code>train['Census_ProcessorManufacturerIdentifier'].value_counts(dropna=False, normalize=True).cumsum()
# train.loc[train['Census_ProcessorManufacturerIdentifier'].isin([5.0, 1.0]) == False, 'Census_ProcessorManufacturerIdentifier'] = 0.0
# test.loc[test['Census_ProcessorManufacturerIdentifier'].isin([5.0, 1.0]) == False, 'Census_ProcessorManufacturerIdentifier'] = 0.0

# train['Census_ProcessorManufacturerIdentifier'] = train['Census_ProcessorManufacturerIdentifier'].astype('category')
# test['Census_ProcessorManufacturerIdentifier'] = test['Census_ProcessorManufacturerIdentifier'].astype('category')
plot_categorical_feature('Census_ProcessorManufacturerIdentifier', True)<jupyter_output>Census_ProcessorManufacturerIdentifier has 7 unique values and type: float16.
 5.0     0.878701
 1.0     0.116605
NaN      0.004631
 10.0    0.000038
 3.0     0.000024
Name: Census_ProcessorManufacturerIdentifier, dtype: float64<jupyter_text>Once again anonymized variable.  Census_PowerPlatformRoleName<jupyter_code>train['Census_PowerPlatformRoleName'].value_counts(dropna=False, normalize=True).cumsum()
# train.loc[train['Census_PowerPlatformRoleName'].isin(['Mobile', 'Desktop', 'Slate']) == False, 'Census_PowerPlatformRoleName'] = 'UNKNOWN'
# test.loc[test['Census_PowerPlatformRoleName'].isin(['Mobile', 'Desktop', 'Slate']) == False, 'Census_PowerPlatformRoleName'] = 'UNKNOWN'

# train['Census_PowerPlatformRoleName'] = train['Census_PowerPlatformRoleName'].cat.remove_unused_categories()
# test['Census_PowerPlatformRoleName'] = test['Census_PowerPlatformRoleName'].cat.remove_unused_categories()
plot_categorical_feature('Census_PowerPlatformRoleName', True)<jupyter_output>Census_PowerPlatformRoleName has 10 unique values and type: category.
Mobile         0.693036
Desktop        0.231645
Slate          0.055208
Workstation    0.012294
SOHOServer     0.004242
Name: Census_PowerPlatformRoleName, dtype: float64<jupyter_text>For some reason slates have quite a low rate of detections. Maybe no hackers are interested in them? :)  Census_OSInstallTypeName<jupyter_code>plot_categorical_feature('Census_OSInstallTypeName', True)
# top_cats = list(train['Census_OSWUAutoUpdateOptionsName'].value_counts().index[:3])
# train.loc[train['Census_OSWUAutoUpdateOptionsName'].isin(top_cats) == False, 'Census_OSWUAutoUpdateOptionsName'] = 'Off'
# test.loc[test['Census_OSWUAutoUpdateOptionsName'].isin(top_cats) == False, 'Census_OSWUAutoUpdateOptionsName'] = 'Off'

# train['Census_OSWUAutoUpdateOptionsName'] = train['Census_OSWUAutoUpdateOptionsName'].cat.remove_unused_categories()
# test['Census_OSWUAutoUpdateOptionsName'] = test['Census_OSWUAutoUpdateOptionsName'].cat.remove_unused_categories()
# train.loc[train['Census_GenuineStateName'] == 'UNKNOWN', 'Census_GenuineStateName'] = 'OFFLINE'
# test.loc[test['Census_GenuineStateName'] == 'UNKNOWN', 'Census_GenuineStateName'] = 'OFFLINE'

# train['Census_GenuineStateName'] = train['Census_GenuineStateName'].cat.remove_unused_categories()
# test['Census_GenuineStateName'] = test['Census_GenuineStateName'].cat.remove_unused_categories()
# train.loc[train['Census_ActivationChannel'].isin(['Retail', 'OEM:DM']) == False, 'Census_ActivationChannel'] = 'Volume:GVLK'
# test.loc[test['Census_ActivationChannel'].isin(['Retail', 'OEM:DM']) == False, 'Census_ActivationChannel'] = 'Volume:GVLK'

# train['Census_ActivationChannel'] = train['Census_ActivationChannel'].cat.remove_unused_categories()
# test['Census_ActivationChannel'] = test['Census_ActivationChannel'].cat.remove_unused_categories()<jupyter_output><empty_output><jupyter_text>Feature engineering and transformation<jupyter_code>train.head()
train['OsBuildLab'] = train['OsBuildLab'].cat.add_categories(['0.0.0.0.0-0'])
train['OsBuildLab'] = train['OsBuildLab'].fillna('0.0.0.0.0-0')
test['OsBuildLab'] = test['OsBuildLab'].cat.add_categories(['0.0.0.0.0-0'])
test['OsBuildLab'] = test['OsBuildLab'].fillna('0.0.0.0.0-0')
def fe(df):
    df['EngineVersion_2'] = df['EngineVersion'].apply(lambda x: x.split('.')[2]).astype('category')
    df['EngineVersion_3'] = df['EngineVersion'].apply(lambda x: x.split('.')[3]).astype('category')

    df['AppVersion_1'] = df['AppVersion'].apply(lambda x: x.split('.')[1]).astype('category')
    df['AppVersion_2'] = df['AppVersion'].apply(lambda x: x.split('.')[2]).astype('category')
    df['AppVersion_3'] = df['AppVersion'].apply(lambda x: x.split('.')[3]).astype('category')

    df['AvSigVersion_0'] = df['AvSigVersion'].apply(lambda x: x.split('.')[0]).astype('category')
    df['AvSigVersion_1'] = df['AvSigVersion'].apply(lambda x: x.split('.')[1]).astype('category')
    df['AvSigVersion_2'] = df['AvSigVersion'].apply(lambda x: x.split('.')[2]).astype('category')

    df['OsBuildLab_0'] = df['OsBuildLab'].apply(lambda x: x.split('.')[0]).astype('category')
    df['OsBuildLab_1'] = df['OsBuildLab'].apply(lambda x: x.split('.')[1]).astype('category')
    df['OsBuildLab_2'] = df['OsBuildLab'].apply(lambda x: x.split('.')[2]).astype('category')
    df['OsBuildLab_3'] = df['OsBuildLab'].apply(lambda x: x.split('.')[3]).astype('category')
    # df['OsBuildLab_40'] = df['OsBuildLab'].apply(lambda x: x.split('.')[-1].split('-')[0]).astype('category')
    # df['OsBuildLab_41'] = df['OsBuildLab'].apply(lambda x: x.split('.')[-1].split('-')[1]).astype('category')

    df['Census_OSVersion_0'] = df['Census_OSVersion'].apply(lambda x: x.split('.')[0]).astype('category')
    df['Census_OSVersion_1'] = df['Census_OSVersion'].apply(lambda x: x.split('.')[1]).astype('category')
    df['Census_OSVersion_2'] = df['Census_OSVersion'].apply(lambda x: x.split('.')[2]).astype('category')
    df['Census_OSVersion_3'] = df['Census_OSVersion'].apply(lambda x: x.split('.')[3]).astype('category')

    # https://www.kaggle.com/adityaecdrid/simple-feature-engineering-xd
    df['primary_drive_c_ratio'] = df['Census_SystemVolumeTotalCapacity']/ df['Census_PrimaryDiskTotalCapacity']
    df['non_primary_drive_MB'] = df['Census_PrimaryDiskTotalCapacity'] - df['Census_SystemVolumeTotalCapacity']

    df['aspect_ratio'] = df['Census_InternalPrimaryDisplayResolutionHorizontal']/ df['Census_InternalPrimaryDisplayResolutionVertical']

    df['monitor_dims'] = df['Census_InternalPrimaryDisplayResolutionHorizontal'].astype(str) + '*' + df['Census_InternalPrimaryDisplayResolutionVertical'].astype('str')
    df['monitor_dims'] = df['monitor_dims'].astype('category')

    df['dpi'] = ((df['Census_InternalPrimaryDisplayResolutionHorizontal']**2 + df['Census_InternalPrimaryDisplayResolutionVertical']**2)**.5)/(df['Census_InternalPrimaryDiagonalDisplaySizeInInches'])

    df['dpi_square'] = df['dpi'] ** 2

    df['MegaPixels'] = (df['Census_InternalPrimaryDisplayResolutionHorizontal'] * df['Census_InternalPrimaryDisplayResolutionVertical'])/1e6

    df['Screen_Area'] = (df['aspect_ratio']* (df['Census_InternalPrimaryDiagonalDisplaySizeInInches']**2))/(df['aspect_ratio']**2 + 1)

    df['ram_per_processor'] = df['Census_TotalPhysicalRAM']/ df['Census_ProcessorCoreCount']

    df['new_num_0'] = df['Census_InternalPrimaryDiagonalDisplaySizeInInches'] / df['Census_ProcessorCoreCount']

    df['new_num_1'] = df['Census_ProcessorCoreCount'] * df['Census_InternalPrimaryDiagonalDisplaySizeInInches']
    
    df['Census_IsFlightingInternal'] = df['Census_IsFlightingInternal'].fillna(1)
    df['Census_ThresholdOptIn'] = df['Census_ThresholdOptIn'].fillna(1)
    df['Census_IsWIMBootEnabled'] = df['Census_IsWIMBootEnabled'].fillna(1)
    df['Wdft_IsGamer'] = df['Wdft_IsGamer'].fillna(0)
    
    return df
train = fe(train)
test = fe(test)
cat_cols = [col for col in train.columns if col not in ['MachineIdentifier', 'Census_SystemVolumeTotalCapacity', 'HasDetections'] and str(train[col].dtype) == 'category']
len(cat_cols)
more_cat_cols = []
add_cat_feats = [
 'Census_OSBuildRevision',
 'OsBuildLab',
 'SmartScreen',
'AVProductsInstalled']
for col1 in add_cat_feats:
    for col2 in add_cat_feats:
        if col1 != col2:
            train[col1 + '__' + col2] = train[col1].astype(str) + train[col2].astype(str)
            train[col1 + '__' + col2] = train[col1 + '__' + col2].astype('category')
            
            test[col1 + '__' + col2] = test[col1].astype(str) + test[col2].astype(str)
            test[col1 + '__' + col2] = test[col1 + '__' + col2].astype('category')
            more_cat_cols.append(col1 + '__' + col2)
            
cat_cols = cat_cols + more_cat_cols
to_encode = []
for col in cat_cols:
    if train[col].nunique() > 1000:
        print(col, train[col].nunique())
        to_encode.append(col)<jupyter_output>AvSigVersion 8531
AVProductStatesIdentifier 28970
CityIdentifier 107366
Census_OEMNameIdentifier 2564
Census_OEMModelIdentifier 175365
Census_ProcessorModelIdentifier 2583
Census_InternalBatteryNumberOfCharges 41087
Census_FirmwareVersionIdentifier 50494
AvSigVersion_2 2766
monitor_dims 10061
Census_OSBuildRevision__OsBuildLab 15367
Census_OSBuildRevision__SmartScreen 1763
Census_OSBuildRevision__AVProductsInstalled 1169
OsBuildLab__Census_OSBuildRevision 15367
OsBuildLab__SmartScreen 2307
OsBuildLab__AVProductsInstalled 1951
SmartScreen__Census_OSBuildRevision 1763
SmartScreen__OsBuildLab 2307
AVProductsInstalled__Census_OSBuildRevision 1169
AVProductsInstalled__OsBuildLab 1951<jupyter_text>Frequency encodingFrom this kernel:  https://www.kaggle.com/fabiendaniel/detecting-malwares-with-lgbmI do frequency and label encoding on the full train dataset, because I think I'll get more correct values this way.<jupyter_code>train = reduce_mem_usage(train)
test = reduce_mem_usage(test)
gc.collect()
def frequency_encoding(variable):
    t = pd.concat([train[variable], test[variable]]).value_counts().reset_index()
    t = t.reset_index()
    t.loc[t[variable] == 1, 'level_0'] = np.nan
    t.set_index('index', inplace=True)
    max_label = t['level_0'].max() + 1
    t.fillna(max_label, inplace=True)
    return t.to_dict()['level_0']
for col in tqdm_notebook(to_encode):
    freq_enc_dict = frequency_encoding(col)
    train[col] = train[col].map(lambda x: freq_enc_dict.get(x, np.nan))
    test[col] = test[col].map(lambda x: freq_enc_dict.get(x, np.nan))
    cat_cols.remove(col)<jupyter_output><empty_output><jupyter_text>Label Encoding<jupyter_code>%%time
indexer = {}
for col in cat_cols:
    # print(col)
    _, indexer[col] = pd.factorize(train[col].astype(str), sort=True)
    
for col in tqdm_notebook(cat_cols):
    # print(col)
    train[col] = indexer[col].get_indexer(train[col].astype(str))
    test[col] = indexer[col].get_indexer(test[col].astype(str))
    
    train = reduce_mem_usage(train, verbose=False)
    test = reduce_mem_usage(test, verbose=False)
del indexer
train.head()<jupyter_output><empty_output><jupyter_text>Idea of training on all dataLet's try training model on three subsets of train data separately and blend the predictions.<jupyter_code>y = train['HasDetections']
train = train.drop(['HasDetections', 'MachineIdentifier'], axis=1)
test = test.drop(['MachineIdentifier'], axis=1)
gc.collect()
train.sort_values('AvSigVersion')
train1 = train[:4000000]
train = train[4000000:8000000]

y1 = y[:4000000]
y = y[4000000:8000000]<jupyter_output><empty_output><jupyter_text>ModellingAdded possibility to do rank averaging.<jupyter_code>n_fold = 5
folds = StratifiedKFold(n_splits=n_fold, shuffle=True, random_state=15)
# folds = TimeSeriesSplit(n_splits=5)
from numba import jit
# fast roc_auc computation: https://www.kaggle.com/c/microsoft-malware-prediction/discussion/76013
@jit
def fast_auc(y_true, y_prob):
    y_true = np.asarray(y_true)
    y_true = y_true[np.argsort(y_prob)]
    nfalse = 0
    auc = 0
    n = len(y_true)
    for i in range(n):
        y_i = y_true[i]
        nfalse += (1 - y_i)
        auc += y_i * nfalse
    auc /= (nfalse * (n - nfalse))
    return auc

def eval_auc(preds, dtrain):
    labels = dtrain.get_label()
    return 'auc', fast_auc(labels, preds), True

# idea from this kernel: https://www.kaggle.com/fabiendaniel/detecting-malwares-with-lgbm
def predict_chunk(model, test):
    initial_idx = 0
    chunk_size = 1000000
    current_pred = np.zeros(len(test))
    while initial_idx < test.shape[0]:
        final_idx = min(initial_idx + chunk_size, test.shape[0])
        idx = range(initial_idx, final_idx)
        current_pred[idx] = model.predict(test.iloc[idx], num_iteration=model.best_iteration)
        initial_idx = final_idx
    #predictions += current_pred / min(folds.n_splits, max_iter)
    return current_pred


def train_model(X=train, X_test=test, y=y, params=None, folds=folds, model_type='lgb', plot_feature_importance=False, averaging='usual', make_oof=False):
    result_dict = {}
    if make_oof:
        oof = np.zeros(len(X))
    prediction = np.zeros(len(X_test))
    scores = []
    feature_importance = pd.DataFrame()
    for fold_n, (train_index, valid_index) in enumerate(folds.split(X, y)):
        gc.collect()
        print('Fold', fold_n + 1, 'started at', time.ctime())
        X_train, X_valid = X.iloc[train_index], X.iloc[valid_index]
        y_train, y_valid = y.iloc[train_index], y.iloc[valid_index]
        
        
        if model_type == 'lgb':
            train_data = lgb.Dataset(X_train, label=y_train, categorical_feature = cat_cols)
            valid_data = lgb.Dataset(X_valid, label=y_valid, categorical_feature = cat_cols)
            
            model = lgb.train(params,
                    train_data,
                    num_boost_round=2000,
                    valid_sets = [train_data, valid_data],
                    verbose_eval=500,
                    early_stopping_rounds = 200,
                    feval=eval_auc)

            del train_data, valid_data
            
            y_pred_valid = model.predict(X_valid, num_iteration=model.best_iteration)
            del X_valid
            gc.collect()
            # print('predicting on test')
            # y_pred = model.predict(X_test, num_iteration=model.best_iteration)
            y_pred = predict_chunk(model, X_test)
            # print('predicted')
            
        if model_type == 'xgb':
            train_data = xgb.DMatrix(data=X_train, label=y_train)
            valid_data = xgb.DMatrix(data=X_valid, label=y_valid)

            watchlist = [(train_data, 'train'), (valid_data, 'valid_data')]
            model = xgb.train(dtrain=train_data, num_boost_round=20000, evals=watchlist, early_stopping_rounds=200, verbose_eval=500, params=params)
            y_pred_valid = model.predict(xgb.DMatrix(X_valid), ntree_limit=model.best_ntree_limit)
            #y_pred = model.predict(xgb.DMatrix(X_test), ntree_limit=model.best_ntree_limit)
            y_pred = predict_chunk(model, xgb.DMatrix(X_test))
            
        if model_type == 'lcv':
            model = LogisticRegressionCV(scoring='roc_auc', cv=3)
            model.fit(X_train, y_train)

            y_pred_valid = model.predict(X_valid)
            # y_pred = model.predict(X_test)
            y_pred = predict_chunk(model, X_test)
            
        if model_type == 'cat':
            model = CatBoostRegressor(iterations=20000,  eval_metric='AUC', **params)
            model.fit(X_train, y_train, eval_set=(X_valid, y_valid), cat_features=[], use_best_model=True, verbose=False)

            y_pred_valid = model.predict(X_valid)
            # y_pred = model.predict(X_test)
            y_pred = predict_chunk(model, X_test)
        
        if make_oof:
            oof[valid_index] = y_pred_valid.reshape(-1,)
            
        scores.append(fast_auc(y_valid, y_pred_valid))
        print('Fold roc_auc:', roc_auc_score(y_valid, y_pred_valid))
        print('')
        
        if averaging == 'usual':
            prediction += y_pred
        elif averaging == 'rank':
            prediction += pd.Series(y_pred).rank().values
        
        if model_type == 'lgb':
            # feature importance
            fold_importance = pd.DataFrame()
            fold_importance["feature"] = X.columns
            fold_importance["importance"] = model.feature_importance()
            fold_importance["fold"] = fold_n + 1
            feature_importance = pd.concat([feature_importance, fold_importance], axis=0)

    prediction /= n_fold
    
    print('CV mean score: {0:.4f}, std: {1:.4f}.'.format(np.mean(scores), np.std(scores)))
    
    if model_type == 'lgb':
        
        if plot_feature_importance:
            feature_importance["importance"] /= n_fold
            cols = feature_importance[["feature", "importance"]].groupby("feature").mean().sort_values(
                by="importance", ascending=False)[:50].index

            best_features = feature_importance.loc[feature_importance.feature.isin(cols)]
            logging.info('Top features')
            for f in best_features.sort_values(by="importance", ascending=False)['feature'].values:
                logging.info(f)

            plt.figure(figsize=(16, 12));
            sns.barplot(x="importance", y="feature", data=best_features.sort_values(by="importance", ascending=False));
            plt.title('LGB Features (avg over folds)');
            
            result_dict['feature_importance'] = feature_importance
            
    result_dict['prediction'] = prediction
    if make_oof:
        result_dict['oof'] = oof
    
    return result_dict
# params from https://www.kaggle.com/fabiendaniel/detecting-malwares-with-lgbm
params = {'num_leaves': 256,
         'min_data_in_leaf': 42,
         'objective': 'binary',
         'max_depth': 5,
         'learning_rate': 0.05,
         "boosting": "gbdt",
         "feature_fraction": 0.8,
         "bagging_freq": 5,
         "bagging_fraction": 0.8,
         "bagging_seed": 11,
         "lambda_l1": 0.15,
         "lambda_l2": 0.15,
         "random_state": 42,          
         "verbosity": -1}
del stats_df, freq_enc_dict
result_dict1 = train_model(X=train1, X_test=test, y=y1, params=params, model_type='lgb', plot_feature_importance=True, averaging='rank')
del train1, y1
# params = {'num_leaves': 64,
#          'min_data_in_leaf': 20,
#          'objective': 'binary',
#          'max_depth': 5,
#          'learning_rate': 0.05,
#          "boosting": "gbdt",
#          "feature_fraction": 0.9,
#          "bagging_freq": 5,
#          "bagging_fraction": 0.7,
#          "bagging_seed": 11,
#          "lambda_l1": 0.2,
#          "lambda_l2": 0.2,
#          "random_state": 13,          
#          "verbosity": -1}
# result_dict = train_model(X=train, X_test=test, y=y, params=params, model_type='lgb', plot_feature_importance=False, averaging='rank')
submission = pd.read_csv('../input/sample_submission.csv')

# submission['HasDetections'] = (result_dict['prediction'] + result_dict1['prediction'] + result_dict2['prediction']) / 3
# submission['HasDetections'] = (result_dict['prediction'] + result_dict1['prediction']) / 2
submission['HasDetections'] = result_dict1['prediction']
submission.to_csv('lgb_rank.csv', index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11940420924853595, 'fraction_numerical': 0.04233854431521793, 'mean_word_length': 3.7093086133642332, 'pattern_counts': {'":': 18, '<': 250, '<?xml version=': 0, '>': 251, 'https://': 9, 'lorem ipsum': 0, 'www.': 8, 'xml': 0}, 'pii_count': 5, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 98, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '68', 'dir': 'jupyter-structured-clean-dedup', 'id': '604129', 'license': 'MIT', 'n_tokens_mistral': 20788, 'n_tokens_neox': 18951, 'n_words': 3387, 'path': '11 microsoft software prediction/is-this-malware-eda-fe-and-lgb-updated.ipynb', 'repo_name': 'MLVPRASAD/KaggleProjects', 'size': '60961'}