MMP_clean_train_data.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "MMP clean train data.ipynb",
      "provenance": [],
      "machine_shape": "hm",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "accelerator": "GPU"
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ramprasathasokan/Microsoft-Malware-Prediction-Using-CatBoost/blob/master/MMP_clean_train_data.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "tmNuLdz3kDA0",
        "colab_type": "text"
      },
      "source": [
        "## Cleaning of Train Dataset"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-XemB9hZAZ7X",
        "colab_type": "text"
      },
      "source": [
        "**Mount Google Drive**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cTWReugDAXmH",
        "colab_type": "code",
        "outputId": "597c830d-0e46-4f9a-8534-3e5ea2385ec3",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/gdrive')"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Drive already mounted at /content/gdrive; to attempt to forcibly remount, call drive.mount(\"/content/gdrive\", force_remount=True).\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MqcITJ13Acqp",
        "colab_type": "text"
      },
      "source": [
        "**Install CatBoost**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c1XSGFqA_4W4",
        "colab_type": "code",
        "outputId": "7eafae00-0557-4187-b859-a468eebd90e4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 213
        }
      },
      "source": [
        "!pip install \"dask[dataframe]\""
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: dask[dataframe] in /usr/local/lib/python3.6/dist-packages (1.1.5)\n",
            "Requirement already satisfied: numpy>=1.11.0; extra == \"dataframe\" in /usr/local/lib/python3.6/dist-packages (from dask[dataframe]) (1.17.3)\n",
            "Requirement already satisfied: partd>=0.3.8; extra == \"dataframe\" in /usr/local/lib/python3.6/dist-packages (from dask[dataframe]) (1.0.0)\n",
            "Requirement already satisfied: cloudpickle>=0.2.1; extra == \"dataframe\" in /usr/local/lib/python3.6/dist-packages (from dask[dataframe]) (0.6.1)\n",
            "Requirement already satisfied: toolz>=0.7.3; extra == \"dataframe\" in /usr/local/lib/python3.6/dist-packages (from dask[dataframe]) (0.10.0)\n",
            "Requirement already satisfied: pandas>=0.19.0; extra == \"dataframe\" in /usr/local/lib/python3.6/dist-packages (from dask[dataframe]) (0.25.2)\n",
            "Requirement already satisfied: locket in /usr/local/lib/python3.6/dist-packages (from partd>=0.3.8; extra == \"dataframe\"->dask[dataframe]) (0.2.0)\n",
            "Requirement already satisfied: pytz>=2017.2 in /usr/local/lib/python3.6/dist-packages (from pandas>=0.19.0; extra == \"dataframe\"->dask[dataframe]) (2018.9)\n",
            "Requirement already satisfied: python-dateutil>=2.6.1 in /usr/local/lib/python3.6/dist-packages (from pandas>=0.19.0; extra == \"dataframe\"->dask[dataframe]) (2.6.1)\n",
            "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.6/dist-packages (from python-dateutil>=2.6.1->pandas>=0.19.0; extra == \"dataframe\"->dask[dataframe]) (1.12.0)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7FVuhUqcA2G7",
        "colab_type": "text"
      },
      "source": [
        "**Import packages**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8WSaYYqlifFN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import dask.dataframe as dd\n",
        "from sklearn import preprocessing\n",
        "import warnings, sys\n",
        "import gc\n",
        "\n",
        "gc.enable()\n",
        "\n",
        "if not sys.warnoptions:\n",
        "    warnings.simplefilter(\"ignore\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hLtkf8kWA76_",
        "colab_type": "text"
      },
      "source": [
        "**Load Data Paths**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L3uH0eQtA-P5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "train_path = '/content/gdrive/My Drive/kaggle/ms/train/train.csv'\n",
        "test_path = '/content/gdrive/My Drive/kaggle/ms/test/test.csv'\n",
        "data_desc = '/content/gdrive/My Drive/Colab Notebooks/Data_Description.xlsx'"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bqhi6RlnBAX5",
        "colab_type": "text"
      },
      "source": [
        "**Predefining Data Types**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mDGAkbcoBF2s",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "dtypes = {\n",
        "        'MachineIdentifier':                                    'category',\n",
        "        'ProductName':                                          'category',\n",
        "        'EngineVersion':                                        'category',\n",
        "        'AppVersion':                                           'category',\n",
        "        'AvSigVersion':                                         'category',\n",
        "        'IsBeta':                                               'int8',\n",
        "        'RtpStateBitfield':                                     'float16',\n",
        "        'IsSxsPassiveMode':                                     'int8',\n",
        "        'DefaultBrowsersIdentifier':                            'float16',\n",
        "        'AVProductStatesIdentifier':                            'float32',\n",
        "        'AVProductsInstalled':                                  'float16',\n",
        "        'AVProductsEnabled':                                    'float16',\n",
        "        'HasTpm':                                               'int8',\n",
        "        'CountryIdentifier':                                    'int16',\n",
        "        'CityIdentifier':                                       'float32',\n",
        "        'OrganizationIdentifier':                               'float16',\n",
        "        'GeoNameIdentifier':                                    'float16',\n",
        "        'LocaleEnglishNameIdentifier':                          'int8',\n",
        "        'Platform':                                             'category',\n",
        "        'Processor':                                            'category',\n",
        "        'OsVer':                                                'category',\n",
        "        'OsBuild':                                              'int16',\n",
        "        'OsSuite':                                              'int16',\n",
        "        'OsPlatformSubRelease':                                 'category',\n",
        "        'OsBuildLab':                                           'category',\n",
        "        'SkuEdition':                                           'category',\n",
        "        'IsProtected':                                          'float16',\n",
        "        'AutoSampleOptIn':                                      'int8',\n",
        "        'PuaMode':                                              'category',\n",
        "        'SMode':                                                'float16',\n",
        "        'IeVerIdentifier':                                      'float16',\n",
        "        'SmartScreen':                                          'category',\n",
        "        'Firewall':                                             'float16',\n",
        "        'UacLuaenable':                                         'float32',\n",
        "        'Census_MDC2FormFactor':                                'category',\n",
        "        'Census_DeviceFamily':                                  'category',\n",
        "        'Census_OEMNameIdentifier':                             'float16',\n",
        "        'Census_OEMModelIdentifier':                            'float32',\n",
        "        'Census_ProcessorCoreCount':                            'float16',\n",
        "        'Census_ProcessorManufacturerIdentifier':               'float16',\n",
        "        'Census_ProcessorModelIdentifier':                      'float16',\n",
        "        'Census_ProcessorClass':                                'category',\n",
        "        'Census_PrimaryDiskTotalCapacity':                      'float32',\n",
        "        'Census_PrimaryDiskTypeName':                           'category',\n",
        "        'Census_SystemVolumeTotalCapacity':                     'float32',\n",
        "        'Census_HasOpticalDiskDrive':                           'int8',\n",
        "        'Census_TotalPhysicalRAM':                              'float32',\n",
        "        'Census_ChassisTypeName':                               'category',\n",
        "        'Census_InternalPrimaryDiagonalDisplaySizeInInches':    'float16',\n",
        "        'Census_InternalPrimaryDisplayResolutionHorizontal':    'float16',\n",
        "        'Census_InternalPrimaryDisplayResolutionVertical':      'float16',\n",
        "        'Census_PowerPlatformRoleName':                         'category',\n",
        "        'Census_InternalBatteryType':                           'category',\n",
        "        'Census_InternalBatteryNumberOfCharges':                'float32',\n",
        "        'Census_OSVersion':                                     'category',\n",
        "        'Census_OSArchitecture':                                'category',\n",
        "        'Census_OSBranch':                                      'category',\n",
        "        'Census_OSBuildNumber':                                 'int16',\n",
        "        'Census_OSBuildRevision':                               'int32',\n",
        "        'Census_OSEdition':                                     'category',\n",
        "        'Census_OSSkuName':                                     'category',\n",
        "        'Census_OSInstallTypeName':                             'category',\n",
        "        'Census_OSInstallLanguageIdentifier':                   'float16',\n",
        "        'Census_OSUILocaleIdentifier':                          'int16',\n",
        "        'Census_OSWUAutoUpdateOptionsName':                     'category',\n",
        "        'Census_IsPortableOperatingSystem':                     'int8',\n",
        "        'Census_GenuineStateName':                              'category',\n",
        "        'Census_ActivationChannel':                             'category',\n",
        "        'Census_IsFlightingInternal':                           'float16',\n",
        "        'Census_IsFlightsDisabled':                             'float16',\n",
        "        'Census_FlightRing':                                    'category',\n",
        "        'Census_ThresholdOptIn':                                'float16',\n",
        "        'Census_FirmwareManufacturerIdentifier':                'float16',\n",
        "        'Census_FirmwareVersionIdentifier':                     'float32',\n",
        "        'Census_IsSecureBootEnabled':                           'int8',\n",
        "        'Census_IsWIMBootEnabled':                              'float16',\n",
        "        'Census_IsVirtualDevice':                               'float16',\n",
        "        'Census_IsTouchEnabled':                                'int8',\n",
        "        'Census_IsPenCapable':                                  'int8',\n",
        "        'Census_IsAlwaysOnAlwaysConnectedCapable':              'float16',\n",
        "        'Wdft_IsGamer':                                         'float16',\n",
        "        'Wdft_RegionIdentifier':                                'float16',\n",
        "        'HasDetections':                                        'int8'\n",
        "        }"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "viLmxhEppfmD",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def convert_types(df):\n",
        "    # Convert data types to reduce memory\n",
        "    for c in df:\n",
        "        col_type = str(df[c].dtypes)\n",
        "        numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n",
        "        \n",
        "        # Convert objects to category\n",
        "        if col_type == 'object':\n",
        "            df[c] = df[c].astype('category')\n",
        "        \n",
        "        # numerics\n",
        "        elif col_type in numerics:\n",
        "            c_min = df[c].min()\n",
        "            c_max = df[c].max()\n",
        "            if col_type[:3] == 'int':\n",
        "                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n",
        "                    df[c] = df[c].astype(np.int8)\n",
        "                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n",
        "                    df[c] = df[c].astype(np.int16)\n",
        "                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n",
        "                    df[c] = df[c].astype(np.int32)\n",
        "                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n",
        "                    df[c] = df[c].astype(np.int64)  \n",
        "            else:\n",
        "                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n",
        "                    df[c] = df[c].astype(np.float16)\n",
        "                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n",
        "                    df[c] = df[c].astype(np.float32)\n",
        "                else:\n",
        "                    df[c] = df[c].astype(np.float64)  \n",
        "        \n",
        "    return df"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EwGjzRIPjHh0",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "excel_table = pd.read_excel(data_desc)\n",
        "excel_table = excel_table[excel_table['Relevant']==1]\n",
        "relevant_features = [el.replace('\\xa0','') for el in excel_table['Feature']]"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1Q0e2V4KjaE1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "target = 'HasDetections'\n",
        "data_id = 'MachineIdentifier'\n",
        "relevant_features.append(target)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VSk8abh-iUKx",
        "colab_type": "code",
        "outputId": "f465cfc6-7a33-41e2-c6a1-338f78a18653",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "ddf = dd.read_csv(train_path, dtype = dtypes)\n",
        "df = ddf.compute()\n",
        "gc.collect()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 9
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "O89ZHYG5prY7",
        "colab_type": "code",
        "outputId": "8fc2e628-e0db-4fff-ea55-ef38d659ae75",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "df.shape"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(8921483, 83)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 10
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qqdbuCGhph0w",
        "colab_type": "code",
        "outputId": "f874eb7d-feb3-4068-b18b-a38472319750",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "df = df[relevant_features]\n",
        "df.shape"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(8921483, 70)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 11
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h4uki0MRpr_g",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = convert_types(df)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EXYyJ_g53yfX",
        "colab_type": "code",
        "outputId": "a0d85c35-ad8d-4194-a1ed-162c3c84b9a4",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 304
        }
      },
      "source": [
        "df.head()"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>AppVersion</th>\n",
              "      <th>AVProductsEnabled</th>\n",
              "      <th>AVProductsInstalled</th>\n",
              "      <th>AVProductStatesIdentifier</th>\n",
              "      <th>AvSigVersion</th>\n",
              "      <th>Census_ActivationChannel</th>\n",
              "      <th>Census_ChassisTypeName</th>\n",
              "      <th>Census_DeviceFamily</th>\n",
              "      <th>Census_FirmwareManufacturerIdentifier</th>\n",
              "      <th>Census_FirmwareVersionIdentifier</th>\n",
              "      <th>Census_FlightRing</th>\n",
              "      <th>Census_GenuineStateName</th>\n",
              "      <th>Census_HasOpticalDiskDrive</th>\n",
              "      <th>Census_InternalPrimaryDiagonalDisplaySizeInInches</th>\n",
              "      <th>Census_InternalPrimaryDisplayResolutionHorizontal</th>\n",
              "      <th>Census_InternalPrimaryDisplayResolutionVertical</th>\n",
              "      <th>Census_IsAlwaysOnAlwaysConnectedCapable</th>\n",
              "      <th>Census_IsPenCapable</th>\n",
              "      <th>Census_IsPortableOperatingSystem</th>\n",
              "      <th>Census_IsSecureBootEnabled</th>\n",
              "      <th>Census_IsTouchEnabled</th>\n",
              "      <th>Census_IsVirtualDevice</th>\n",
              "      <th>Census_MDC2FormFactor</th>\n",
              "      <th>Census_OEMModelIdentifier</th>\n",
              "      <th>Census_OEMNameIdentifier</th>\n",
              "      <th>Census_OSArchitecture</th>\n",
              "      <th>Census_OSBranch</th>\n",
              "      <th>Census_OSBuildNumber</th>\n",
              "      <th>Census_OSBuildRevision</th>\n",
              "      <th>Census_OSEdition</th>\n",
              "      <th>Census_OSInstallLanguageIdentifier</th>\n",
              "      <th>Census_OSInstallTypeName</th>\n",
              "      <th>Census_OSSkuName</th>\n",
              "      <th>Census_OSUILocaleIdentifier</th>\n",
              "      <th>Census_OSVersion</th>\n",
              "      <th>Census_OSWUAutoUpdateOptionsName</th>\n",
              "      <th>Census_PowerPlatformRoleName</th>\n",
              "      <th>Census_PrimaryDiskTotalCapacity</th>\n",
              "      <th>Census_PrimaryDiskTypeName</th>\n",
              "      <th>Census_ProcessorCoreCount</th>\n",
              "      <th>Census_ProcessorManufacturerIdentifier</th>\n",
              "      <th>Census_ProcessorModelIdentifier</th>\n",
              "      <th>Census_SystemVolumeTotalCapacity</th>\n",
              "      <th>Census_TotalPhysicalRAM</th>\n",
              "      <th>CityIdentifier</th>\n",
              "      <th>CountryIdentifier</th>\n",
              "      <th>EngineVersion</th>\n",
              "      <th>Firewall</th>\n",
              "      <th>GeoNameIdentifier</th>\n",
              "      <th>HasTpm</th>\n",
              "      <th>IeVerIdentifier</th>\n",
              "      <th>IsProtected</th>\n",
              "      <th>IsSxsPassiveMode</th>\n",
              "      <th>LocaleEnglishNameIdentifier</th>\n",
              "      <th>MachineIdentifier</th>\n",
              "      <th>OrganizationIdentifier</th>\n",
              "      <th>OsBuild</th>\n",
              "      <th>OsBuildLab</th>\n",
              "      <th>OsPlatformSubRelease</th>\n",
              "      <th>OsSuite</th>\n",
              "      <th>OsVer</th>\n",
              "      <th>Platform</th>\n",
              "      <th>Processor</th>\n",
              "      <th>RtpStateBitfield</th>\n",
              "      <th>SkuEdition</th>\n",
              "      <th>SmartScreen</th>\n",
              "      <th>SMode</th>\n",
              "      <th>Wdft_IsGamer</th>\n",
              "      <th>Wdft_RegionIdentifier</th>\n",
              "      <th>HasDetections</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>4.18.1807.18075</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>53447.0</td>\n",
              "      <td>1.273.1735.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>Windows.Desktop</td>\n",
              "      <td>628.0</td>\n",
              "      <td>36144.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>IS_GENUINE</td>\n",
              "      <td>0</td>\n",
              "      <td>18.906250</td>\n",
              "      <td>1440.0</td>\n",
              "      <td>900.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>9124.0</td>\n",
              "      <td>2668.0</td>\n",
              "      <td>amd64</td>\n",
              "      <td>rs4_release</td>\n",
              "      <td>17134</td>\n",
              "      <td>165</td>\n",
              "      <td>Professional</td>\n",
              "      <td>26.0</td>\n",
              "      <td>UUPUpgrade</td>\n",
              "      <td>PROFESSIONAL</td>\n",
              "      <td>119</td>\n",
              "      <td>10.0.17134.165</td>\n",
              "      <td>UNKNOWN</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>476940.0</td>\n",
              "      <td>HDD</td>\n",
              "      <td>4.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2340.0</td>\n",
              "      <td>299451.0</td>\n",
              "      <td>4096.0</td>\n",
              "      <td>128035.0</td>\n",
              "      <td>29</td>\n",
              "      <td>1.1.15100.1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>35.0</td>\n",
              "      <td>1</td>\n",
              "      <td>137.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>-85</td>\n",
              "      <td>0000028988387b115f69f31a3bf04f09</td>\n",
              "      <td>18.0</td>\n",
              "      <td>17134</td>\n",
              "      <td>17134.1.amd64fre.rs4_release.180410-1804</td>\n",
              "      <td>rs4</td>\n",
              "      <td>256</td>\n",
              "      <td>10.0.0.0</td>\n",
              "      <td>windows10</td>\n",
              "      <td>x64</td>\n",
              "      <td>7.0</td>\n",
              "      <td>Pro</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>10.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>4.13.17134.1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>53447.0</td>\n",
              "      <td>1.263.48.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>Notebook</td>\n",
              "      <td>Windows.Desktop</td>\n",
              "      <td>628.0</td>\n",
              "      <td>57858.0</td>\n",
              "      <td>NOT_SET</td>\n",
              "      <td>OFFLINE</td>\n",
              "      <td>0</td>\n",
              "      <td>13.898438</td>\n",
              "      <td>1366.0</td>\n",
              "      <td>768.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Notebook</td>\n",
              "      <td>91656.0</td>\n",
              "      <td>2668.0</td>\n",
              "      <td>amd64</td>\n",
              "      <td>rs4_release</td>\n",
              "      <td>17134</td>\n",
              "      <td>1</td>\n",
              "      <td>Professional</td>\n",
              "      <td>8.0</td>\n",
              "      <td>IBSClean</td>\n",
              "      <td>PROFESSIONAL</td>\n",
              "      <td>31</td>\n",
              "      <td>10.0.17134.1</td>\n",
              "      <td>UNKNOWN</td>\n",
              "      <td>Mobile</td>\n",
              "      <td>476940.0</td>\n",
              "      <td>HDD</td>\n",
              "      <td>4.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2404.0</td>\n",
              "      <td>102385.0</td>\n",
              "      <td>4096.0</td>\n",
              "      <td>1482.0</td>\n",
              "      <td>93</td>\n",
              "      <td>1.1.14600.4</td>\n",
              "      <td>1.0</td>\n",
              "      <td>119.0</td>\n",
              "      <td>1</td>\n",
              "      <td>137.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>64</td>\n",
              "      <td>000007535c3f730efa9ea0b7ef1bd645</td>\n",
              "      <td>18.0</td>\n",
              "      <td>17134</td>\n",
              "      <td>17134.1.amd64fre.rs4_release.180410-1804</td>\n",
              "      <td>rs4</td>\n",
              "      <td>256</td>\n",
              "      <td>10.0.0.0</td>\n",
              "      <td>windows10</td>\n",
              "      <td>x64</td>\n",
              "      <td>7.0</td>\n",
              "      <td>Pro</td>\n",
              "      <td>NaN</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>8.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>4.18.1807.18075</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>53447.0</td>\n",
              "      <td>1.273.1341.0</td>\n",
              "      <td>OEM:NONSLP</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>Windows.Desktop</td>\n",
              "      <td>142.0</td>\n",
              "      <td>52682.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>IS_GENUINE</td>\n",
              "      <td>0</td>\n",
              "      <td>21.500000</td>\n",
              "      <td>1920.0</td>\n",
              "      <td>1080.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>317701.0</td>\n",
              "      <td>4908.0</td>\n",
              "      <td>amd64</td>\n",
              "      <td>rs4_release</td>\n",
              "      <td>17134</td>\n",
              "      <td>165</td>\n",
              "      <td>Core</td>\n",
              "      <td>7.0</td>\n",
              "      <td>UUPUpgrade</td>\n",
              "      <td>CORE</td>\n",
              "      <td>30</td>\n",
              "      <td>10.0.17134.165</td>\n",
              "      <td>FullAuto</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>114473.0</td>\n",
              "      <td>SSD</td>\n",
              "      <td>4.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>1972.0</td>\n",
              "      <td>113907.0</td>\n",
              "      <td>4096.0</td>\n",
              "      <td>153579.0</td>\n",
              "      <td>86</td>\n",
              "      <td>1.1.15100.1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>64.0</td>\n",
              "      <td>1</td>\n",
              "      <td>137.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>49</td>\n",
              "      <td>000007905a28d863f6d0d597892cd692</td>\n",
              "      <td>18.0</td>\n",
              "      <td>17134</td>\n",
              "      <td>17134.1.amd64fre.rs4_release.180410-1804</td>\n",
              "      <td>rs4</td>\n",
              "      <td>768</td>\n",
              "      <td>10.0.0.0</td>\n",
              "      <td>windows10</td>\n",
              "      <td>x64</td>\n",
              "      <td>7.0</td>\n",
              "      <td>Home</td>\n",
              "      <td>RequireAdmin</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>4.18.1807.18075</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>53447.0</td>\n",
              "      <td>1.273.1527.0</td>\n",
              "      <td>OEM:NONSLP</td>\n",
              "      <td>MiniTower</td>\n",
              "      <td>Windows.Desktop</td>\n",
              "      <td>355.0</td>\n",
              "      <td>20050.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>IS_GENUINE</td>\n",
              "      <td>0</td>\n",
              "      <td>18.500000</td>\n",
              "      <td>1366.0</td>\n",
              "      <td>768.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>275890.0</td>\n",
              "      <td>1443.0</td>\n",
              "      <td>amd64</td>\n",
              "      <td>rs4_release</td>\n",
              "      <td>17134</td>\n",
              "      <td>228</td>\n",
              "      <td>Professional</td>\n",
              "      <td>17.0</td>\n",
              "      <td>UUPUpgrade</td>\n",
              "      <td>PROFESSIONAL</td>\n",
              "      <td>64</td>\n",
              "      <td>10.0.17134.228</td>\n",
              "      <td>FullAuto</td>\n",
              "      <td>Desktop</td>\n",
              "      <td>238475.0</td>\n",
              "      <td>UNKNOWN</td>\n",
              "      <td>4.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2272.0</td>\n",
              "      <td>227116.0</td>\n",
              "      <td>4096.0</td>\n",
              "      <td>20710.0</td>\n",
              "      <td>88</td>\n",
              "      <td>1.1.15100.1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>117.0</td>\n",
              "      <td>1</td>\n",
              "      <td>137.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>115</td>\n",
              "      <td>00000b11598a75ea8ba1beea8459149f</td>\n",
              "      <td>NaN</td>\n",
              "      <td>17134</td>\n",
              "      <td>17134.1.amd64fre.rs4_release.180410-1804</td>\n",
              "      <td>rs4</td>\n",
              "      <td>256</td>\n",
              "      <td>10.0.0.0</td>\n",
              "      <td>windows10</td>\n",
              "      <td>x64</td>\n",
              "      <td>7.0</td>\n",
              "      <td>Pro</td>\n",
              "      <td>ExistsNotSet</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>3.0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>4.18.1807.18075</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>53447.0</td>\n",
              "      <td>1.273.1379.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>Portable</td>\n",
              "      <td>Windows.Desktop</td>\n",
              "      <td>355.0</td>\n",
              "      <td>19844.0</td>\n",
              "      <td>Retail</td>\n",
              "      <td>IS_GENUINE</td>\n",
              "      <td>0</td>\n",
              "      <td>14.000000</td>\n",
              "      <td>1366.0</td>\n",
              "      <td>768.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>Notebook</td>\n",
              "      <td>331929.0</td>\n",
              "      <td>1443.0</td>\n",
              "      <td>amd64</td>\n",
              "      <td>rs4_release</td>\n",
              "      <td>17134</td>\n",
              "      <td>191</td>\n",
              "      <td>Core</td>\n",
              "      <td>8.0</td>\n",
              "      <td>Update</td>\n",
              "      <td>CORE</td>\n",
              "      <td>31</td>\n",
              "      <td>10.0.17134.191</td>\n",
              "      <td>FullAuto</td>\n",
              "      <td>Mobile</td>\n",
              "      <td>476940.0</td>\n",
              "      <td>HDD</td>\n",
              "      <td>4.0</td>\n",
              "      <td>5.0</td>\n",
              "      <td>2500.0</td>\n",
              "      <td>101900.0</td>\n",
              "      <td>6144.0</td>\n",
              "      <td>37376.0</td>\n",
              "      <td>18</td>\n",
              "      <td>1.1.15100.1</td>\n",
              "      <td>1.0</td>\n",
              "      <td>277.0</td>\n",
              "      <td>1</td>\n",
              "      <td>137.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>75</td>\n",
              "      <td>000014a5f00daa18e76b81417eeb99fc</td>\n",
              "      <td>NaN</td>\n",
              "      <td>17134</td>\n",
              "      <td>17134.1.amd64fre.rs4_release.180410-1804</td>\n",
              "      <td>rs4</td>\n",
              "      <td>768</td>\n",
              "      <td>10.0.0.0</td>\n",
              "      <td>windows10</td>\n",
              "      <td>x64</td>\n",
              "      <td>7.0</td>\n",
              "      <td>Home</td>\n",
              "      <td>RequireAdmin</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "        AppVersion  AVProductsEnabled  ...  Wdft_RegionIdentifier  HasDetections\n",
              "0  4.18.1807.18075                1.0  ...                   10.0              0\n",
              "1     4.13.17134.1                1.0  ...                    8.0              0\n",
              "2  4.18.1807.18075                1.0  ...                    3.0              0\n",
              "3  4.18.1807.18075                1.0  ...                    3.0              1\n",
              "4  4.18.1807.18075                1.0  ...                    1.0              1\n",
              "\n",
              "[5 rows x 70 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 13
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "XwCbJny6j2Td",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "numerical_features = excel_table[excel_table['FeatureType']=='Numeric']['Feature'].reset_index(drop=True)\n",
        "categorical_features = excel_table[excel_table['FeatureType']=='Category']['Feature'].reset_index(drop=True)\n",
        "binary_features = excel_table[excel_table['FeatureType']=='Boolean']['Feature'].reset_index(drop=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3xgDrqwYj5Ak",
        "colab_type": "code",
        "outputId": "b435e395-4a73-429c-fd82-fd1e072c2ac9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 263
        }
      },
      "source": [
        "binary_features"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0                  Census_HasOpticalDiskDrive\n",
              "1     Census_IsAlwaysOnAlwaysConnectedCapable\n",
              "2                         Census_IsPenCapable\n",
              "3            Census_IsPortableOperatingSystem\n",
              "4                  Census_IsSecureBootEnabled\n",
              "5                       Census_IsTouchEnabled\n",
              "6                      Census_IsVirtualDevice\n",
              "7                                    Firewall\n",
              "8                                      HasTpm\n",
              "9                                 IsProtected\n",
              "10                           IsSxsPassiveMode\n",
              "11                                      SMode\n",
              "12                               Wdft_IsGamer\n",
              "Name: Feature, dtype: object"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 15
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ymRd7q_hj8MO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "list_frequency_encoding = []\n",
        "list_nan_minus1 = []\n",
        "list_nan_0 = []\n",
        "list_nan_1 = []"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TpZD9o_7j-Il",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "for feature in numerical_features:\n",
        "    tmp1 = len(df)\n",
        "    df[feature] = df[feature].fillna(-1)\n",
        "    list_nan_minus1.append(feature)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DR6RbXC2kQFJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "for feature in binary_features:\n",
        "  df[feature] = df[feature].fillna(df[feature].mode()[0])\n",
        "  if df[feature].mode()[0] == 0:\n",
        "      list_nan_0.append(feature)\n",
        "  else:\n",
        "      list_nan_1.append(feature)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ty83XX1QkU30",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "correct_feature_by_hand = [] \n",
        "for feature in categorical_features:\n",
        "    t = pd.api.types.is_categorical_dtype(df[feature])\n",
        "    if not t:\n",
        "        df[feature] = df[feature].fillna(-1)\n",
        "        list_nan_minus1.append(feature)\n",
        "    else:\n",
        "        correct_feature_by_hand.append(feature)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HFYZ0hQYkbed",
        "colab_type": "code",
        "outputId": "85c73146-1ddc-4a70-fd3c-200c5ed59234",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 474
        }
      },
      "source": [
        "correct_feature_by_hand"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['AppVersion',\n",
              " 'AvSigVersion',\n",
              " 'Census_ActivationChannel',\n",
              " 'Census_ChassisTypeName',\n",
              " 'Census_DeviceFamily',\n",
              " 'Census_FlightRing',\n",
              " 'Census_GenuineStateName',\n",
              " 'Census_MDC2FormFactor',\n",
              " 'Census_OSArchitecture',\n",
              " 'Census_OSBranch',\n",
              " 'Census_OSEdition',\n",
              " 'Census_OSInstallTypeName',\n",
              " 'Census_OSSkuName',\n",
              " 'Census_OSVersion',\n",
              " 'Census_OSWUAutoUpdateOptionsName',\n",
              " 'Census_PowerPlatformRoleName',\n",
              " 'Census_PrimaryDiskTypeName',\n",
              " 'EngineVersion',\n",
              " 'MachineIdentifier',\n",
              " 'OsBuildLab',\n",
              " 'OsPlatformSubRelease',\n",
              " 'OsVer',\n",
              " 'Platform',\n",
              " 'Processor',\n",
              " 'SkuEdition',\n",
              " 'SmartScreen']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 20
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "QfHV1-5ukiF1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "for feature in correct_feature_by_hand:\n",
        "    df[feature] = df[feature].str.lower()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cvo5beOwkju9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "list_frequency_encoding.append('AppVersion')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ANBBlEplkndJ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['AvSigVersion'].replace(['1.2&#x17;3.1144.0'], '1.2173.1144.0', inplace=True)\n",
        "list_frequency_encoding.append('AvSigVersion')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IQpJxC_pkq4V",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rename_Census_ActivationChannel(x):\n",
        "    x = x.lower()\n",
        "    if 'oem' in x:\n",
        "        return 'oem'\n",
        "    elif 'volume' in x:\n",
        "        return 'volume'\n",
        "    elif 'retail' in x:\n",
        "        return 'retail'\n",
        "    else:\n",
        "        return x"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VmHOmXX8krlT",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_ActivationChannel'] = df['Census_ActivationChannel'].astype(str)\n",
        "df['Census_ActivationChannel'] = df['Census_ActivationChannel'].apply(rename_Census_ActivationChannel)\n",
        "df['Census_ActivationChannel'] = df['Census_ActivationChannel'].astype('category')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w9WJkdtokuXQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rename_Census_ChassisTypeName(x):\n",
        "    x = x.lower()\n",
        "    if 'laptop' in x:\n",
        "        return 'Notebook'\n",
        "    elif 'other' in x:\n",
        "        return 'unknown'                  \n",
        "    else:\n",
        "        return x"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qEndl_aDkwG9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_ChassisTypeName'] = df['Census_ChassisTypeName'].fillna('unknown')\n",
        "df['Census_ChassisTypeName'] = df['Census_ChassisTypeName'].astype(str)\n",
        "df['Census_ChassisTypeName'] = df['Census_ChassisTypeName'].apply(rename_Census_ChassisTypeName)\n",
        "df['Census_ChassisTypeName'] = df['Census_ChassisTypeName'].astype('category')\n",
        "df['Census_ChassisTypeName'] = df['Census_ChassisTypeName'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B3aUsQzukw1z",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_DeviceFamily'].replace(['windows'], 'windows.desktop', inplace=True)\n",
        "df['Census_DeviceFamily'] = df['Census_DeviceFamily'].astype('category')\n",
        "df['Census_DeviceFamily'] = df['Census_DeviceFamily'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WdIyutoBkz81",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_FlightRing'] = df['Census_FlightRing'].astype('category')\n",
        "df['Census_FlightRing'].replace(['disabled'], 'not_set', inplace=True)\n",
        "df['Census_FlightRing'].replace(['osg', 'canary', 'invalid'], 'unknown', inplace=True)\n",
        "df['Census_FlightRing'] = df['Census_FlightRing'].fillna('unknown')\n",
        "df['Census_FlightRing'] = df['Census_FlightRing'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CUrSs-s9k3qv",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_GenuineStateName'] = df['Census_GenuineStateName'].astype('category')\n",
        "df['Census_GenuineStateName'].replace(['tampered'], 'unknown', inplace=True)\n",
        "df['Census_GenuineStateName'] = df['Census_GenuineStateName'].fillna('unknown')\n",
        "df['Census_GenuineStateName'] = df['Census_GenuineStateName'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "olhPYQVyk6VN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_MDC2FormFactor_new'] = df['Census_MDC2FormFactor']\n",
        "correct_feature_by_hand.append('Census_MDC2FormFactor_new')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "j0_I8ir2k7JZ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rename_Census_MDC2FormFactor_new(x):\n",
        "    x = x.lower()\n",
        "    if 'server' in x:\n",
        "        return 'server'\n",
        "    elif 'tablet' in x:\n",
        "        return 'tablet'                  \n",
        "    else:\n",
        "        return x"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VgjbxJEIlAJW",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_MDC2FormFactor_new'] = df['Census_MDC2FormFactor_new'].astype(str)\n",
        "df['Census_MDC2FormFactor_new'] = df['Census_MDC2FormFactor_new'].apply(rename_Census_MDC2FormFactor_new)\n",
        "df['Census_MDC2FormFactor_new'] = df['Census_MDC2FormFactor_new'].astype('category')\n",
        "df['Census_MDC2FormFactor_new'] = df['Census_MDC2FormFactor_new'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vXl0Oq2ilB4D",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rename_Census_OSEdition(x):\n",
        "    x = x.lower()\n",
        "    if 'core' in x:\n",
        "        return 'core'\n",
        "    elif 'pro' in x:\n",
        "        return 'pro'\n",
        "    elif 'enterprise' in x:\n",
        "        return 'enterprise'\n",
        "    elif 'server' in x:\n",
        "        return 'server'\n",
        "    elif 'home' in x:\n",
        "        return 'home'\n",
        "    elif 'education' in x:\n",
        "        return 'education'\n",
        "    elif 'cloud' in x:\n",
        "        return 'cloud'\n",
        "    else:\n",
        "        return x"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zHmARXpIlD3E",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_OSEdition'] = df['Census_OSEdition'].astype('category')\n",
        "df['Census_OSEdition'] = df['Census_OSEdition'].cat.add_categories(['unknown'])\n",
        "df['Census_OSEdition'] = df['Census_OSEdition'].fillna('unknown')\n",
        "df['Census_OSEdition'] = df['Census_OSEdition'].astype(str)\n",
        "df['Census_OSEdition'] = df['Census_OSEdition'].apply(rename_Census_OSEdition)\n",
        "df['Census_OSEdition'] = df['Census_OSEdition'].astype('category')\n",
        "df['Census_OSEdition'] = df['Census_OSEdition'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "B9T19fg9lFg2",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rename_Census_OSSkuName(x):\n",
        "    x = x.lower()\n",
        "    if 'core' in x:\n",
        "        return 'core'\n",
        "    elif 'pro' in x:\n",
        "        return 'pro'\n",
        "    elif 'enterprise' in x:\n",
        "        return 'enterprise'\n",
        "    elif 'server' in x:\n",
        "        return 'server'\n",
        "    elif 'home' in x:\n",
        "        return 'home'\n",
        "    elif 'education' in x:\n",
        "        return 'education'\n",
        "    elif 'cloud' in x:\n",
        "        return 'cloud'\n",
        "    else:\n",
        "        return x"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TGaVX9rzlGMj",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_OSSkuName'] = df['Census_OSSkuName'].astype(str)\n",
        "df['Census_OSSkuName'] = df['Census_OSSkuName'].apply(rename_Census_OSSkuName)\n",
        "df['Census_OSSkuName'] = df['Census_OSSkuName'].astype('category')\n",
        "df['Census_OSSkuName'] = df['Census_OSSkuName'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "TWUe3EHqlIRB",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "list_frequency_encoding.append('Census_OSVersion')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "kTMI0P5clLLE",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_PowerPlatformRoleName'] = df['Census_PowerPlatformRoleName'].astype('category')\n",
        "df['Census_PowerPlatformRoleName'].replace(['unspecified'], 'unknown', inplace=True)\n",
        "df['Census_PowerPlatformRoleName'] = df['Census_PowerPlatformRoleName'].fillna('unknown')\n",
        "df['Census_PowerPlatformRoleName'] = df['Census_PowerPlatformRoleName'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4zb4yyX4lNYA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['Census_PrimaryDiskTypeName'] = df['Census_PrimaryDiskTypeName'].astype('category')\n",
        "df['Census_PrimaryDiskTypeName'].replace(['unspecified'], 'unknown', inplace=True)\n",
        "df['Census_PrimaryDiskTypeName'] = df['Census_PrimaryDiskTypeName'].fillna('unknown')\n",
        "df['Census_PrimaryDiskTypeName'] = df['Census_PrimaryDiskTypeName'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "k9caSsm_lPOo",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "list_frequency_encoding.append('EngineVersion')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PbpuAON4lRQI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['OsBuildLab'] = df['OsBuildLab'].astype('category')\n",
        "df['OsBuildLab'] = df['OsBuildLab'].cat.add_categories(['unknown'])\n",
        "df['OsBuildLab'] = df['OsBuildLab'].fillna('unknown')\n",
        "list_frequency_encoding.append('OsBuildLab')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NNHYUtcylS7V",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "def rename_SmartScreen(x):\n",
        "    x = x.lower()\n",
        "    if 'promt' in x:\n",
        "        return 'prompt'\n",
        "    elif 'requireadmin' in x:\n",
        "        return 'requireadmin'\n",
        "    elif 'existsnotset' in x:\n",
        "        return 'existsnotset'\n",
        "    elif 'off' in x:\n",
        "        return 'off'\n",
        "    elif 'warn' in x:\n",
        "        return 'warn'\n",
        "    elif 'prompt' in x:\n",
        "        return 'prompt'\n",
        "    elif 'block' in x:\n",
        "        return 'block'\n",
        "    elif 'on' in x:\n",
        "        return 'on'   \n",
        "    else:\n",
        "        return 'unknown'"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1AdZqrrwlVDu",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['SmartScreen'] = df['SmartScreen'].astype('category')\n",
        "df['SmartScreen'] = df['SmartScreen'].cat.add_categories(['unknown'])\n",
        "df['SmartScreen'] = df['SmartScreen'].fillna('unknown')\n",
        "df['SmartScreen'] = df['SmartScreen'].astype(str)\n",
        "df['SmartScreen'] = df['SmartScreen'].apply(rename_SmartScreen)\n",
        "df['SmartScreen'] = df['SmartScreen'].astype('category')\n",
        "df['SmartScreen'] = df['SmartScreen'].cat.remove_unused_categories()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ft8ZeyNqlZZX",
        "colab_type": "code",
        "outputId": "754f40d8-9b72-4636-9141-d755517d9dbc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "df.shape"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(8921483, 71)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 45
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nhR9xMYyxQyA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "train_clean_path = '/content/gdrive/My Drive/kaggle/ms/train/train_clean.csv'"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xa5pUGKulZyf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df.to_csv(train_clean_path, index = False)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DtED1HavlwSC",
        "colab_type": "code",
        "outputId": "9e0bad1d-d721-496a-c8d3-b5dee810f121",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 105
        }
      },
      "source": [
        "list_frequency_encoding"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['AppVersion',\n",
              " 'AvSigVersion',\n",
              " 'Census_OSVersion',\n",
              " 'EngineVersion',\n",
              " 'OsBuildLab']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 48
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Xu_PM9pslx05",
        "colab_type": "code",
        "outputId": "ae6acfdf-acf8-47a4-a26f-2bb1b477ec52",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 544
        }
      },
      "source": [
        "list_nan_minus1"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Census_InternalPrimaryDiagonalDisplaySizeInInches',\n",
              " 'Census_PrimaryDiskTotalCapacity',\n",
              " 'Census_ProcessorCoreCount',\n",
              " 'Census_SystemVolumeTotalCapacity',\n",
              " 'Census_TotalPhysicalRAM',\n",
              " 'AVProductsEnabled',\n",
              " 'AVProductsInstalled',\n",
              " 'AVProductStatesIdentifier',\n",
              " 'Census_FirmwareManufacturerIdentifier',\n",
              " 'Census_FirmwareVersionIdentifier',\n",
              " 'Census_InternalPrimaryDisplayResolutionHorizontal',\n",
              " 'Census_InternalPrimaryDisplayResolutionVertical',\n",
              " 'Census_OEMModelIdentifier',\n",
              " 'Census_OEMNameIdentifier',\n",
              " 'Census_OSBuildNumber',\n",
              " 'Census_OSBuildRevision',\n",
              " 'Census_OSInstallLanguageIdentifier',\n",
              " 'Census_OSUILocaleIdentifier',\n",
              " 'Census_ProcessorManufacturerIdentifier',\n",
              " 'Census_ProcessorModelIdentifier',\n",
              " 'CityIdentifier',\n",
              " 'CountryIdentifier',\n",
              " 'GeoNameIdentifier',\n",
              " 'IeVerIdentifier',\n",
              " 'LocaleEnglishNameIdentifier',\n",
              " 'OrganizationIdentifier',\n",
              " 'OsBuild',\n",
              " 'OsSuite',\n",
              " 'RtpStateBitfield',\n",
              " 'Wdft_RegionIdentifier']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 49
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "mKiM7dRLlyit",
        "colab_type": "code",
        "outputId": "4c6cb076-ea90-4875-d24d-4493e997959c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 193
        }
      },
      "source": [
        "list_nan_0"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Census_HasOpticalDiskDrive',\n",
              " 'Census_IsAlwaysOnAlwaysConnectedCapable',\n",
              " 'Census_IsPenCapable',\n",
              " 'Census_IsPortableOperatingSystem',\n",
              " 'Census_IsSecureBootEnabled',\n",
              " 'Census_IsTouchEnabled',\n",
              " 'Census_IsVirtualDevice',\n",
              " 'IsSxsPassiveMode',\n",
              " 'SMode',\n",
              " 'Wdft_IsGamer']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 50
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jU6DVNhKl0pF",
        "colab_type": "code",
        "outputId": "5c7c6431-cbd3-4a7b-9571-04628660665e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        }
      },
      "source": [
        "list_nan_1"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['Firewall', 'HasTpm', 'IsProtected']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 51
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IipBLbR-l2Nh",
        "colab_type": "code",
        "outputId": "f876e8be-8d3c-4f76-de27-5e6508b346af",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 491
        }
      },
      "source": [
        "correct_feature_by_hand"
      ],
      "execution_count": 0,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['AppVersion',\n",
              " 'AvSigVersion',\n",
              " 'Census_ActivationChannel',\n",
              " 'Census_ChassisTypeName',\n",
              " 'Census_DeviceFamily',\n",
              " 'Census_FlightRing',\n",
              " 'Census_GenuineStateName',\n",
              " 'Census_MDC2FormFactor',\n",
              " 'Census_OSArchitecture',\n",
              " 'Census_OSBranch',\n",
              " 'Census_OSEdition',\n",
              " 'Census_OSInstallTypeName',\n",
              " 'Census_OSSkuName',\n",
              " 'Census_OSVersion',\n",
              " 'Census_OSWUAutoUpdateOptionsName',\n",
              " 'Census_PowerPlatformRoleName',\n",
              " 'Census_PrimaryDiskTypeName',\n",
              " 'EngineVersion',\n",
              " 'MachineIdentifier',\n",
              " 'OsBuildLab',\n",
              " 'OsPlatformSubRelease',\n",
              " 'OsVer',\n",
              " 'Platform',\n",
              " 'Processor',\n",
              " 'SkuEdition',\n",
              " 'SmartScreen',\n",
              " 'Census_MDC2FormFactor_new']"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 52
        }
      ]
    }
  ]
}