Used_Electronics_Price_Prediction_Weekend_Hackathon.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Used Electronics Price Prediction: Weekend Hackathon #7.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "37sBXBYOgpr5",
        "MH0FeVbUQweF",
        "snpJd9NUAToR",
        "AaarOjeOAZGE",
        "Fgegf27gvEhf",
        "Isn_JD-sQzDC",
        "2SUYaXKwsAA-",
        "zZ9WrYRovyjh",
        "PHo742lrQ0uV",
        "d1y2YhacwWG9",
        "AipKXeGgQ2a7",
        "KENWbXl6ySlA"
      ],
      "authorship_tag": "ABX9TyPfIQSzecTrz7eg+Cp4xfDb",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/ArpanSM/MachineLearningHackathons/blob/master/Used_Electronics_Price_Prediction_Weekend_Hackathon.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "37sBXBYOgpr5",
        "colab_type": "text"
      },
      "source": [
        "##Load Data"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w9sgDMtPEvQ7",
        "colab_type": "code",
        "outputId": "e327c10f-2d81-49b7-a436-f5ef1b736134",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 201
        }
      },
      "source": [
        "!wget https://www.machinehack.com/wp-content/uploads/2020/06/Used_Electronics_Participants_Data.zip"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "--2020-06-07 19:16:54--  https://www.machinehack.com/wp-content/uploads/2020/06/Used_Electronics_Participants_Data.zip\n",
            "Resolving www.machinehack.com (www.machinehack.com)... 107.180.71.20\n",
            "Connecting to www.machinehack.com (www.machinehack.com)|107.180.71.20|:443... connected.\n",
            "HTTP request sent, awaiting response... 200 OK\n",
            "Length: 184210 (180K) [application/zip]\n",
            "Saving to: ‘Used_Electronics_Participants_Data.zip’\n",
            "\n",
            "\r          Used_Elec   0%[                    ]       0  --.-KB/s               \r         Used_Elect  31%[=====>              ]  56.00K   236KB/s               \r        Used_Electr  93%[=================>  ] 168.00K   353KB/s               \rUsed_Electronics_Pa 100%[===================>] 179.89K   378KB/s    in 0.5s    \n",
            "\n",
            "2020-06-07 19:16:54 (378 KB/s) - ‘Used_Electronics_Participants_Data.zip’ saved [184210/184210]\n",
            "\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "njRKYuk0E8AO",
        "colab_type": "code",
        "outputId": "ca4382ad-7e53-4838-f632-5924fe68dc2f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 150
        }
      },
      "source": [
        "!unzip Used_Electronics_Participants_Data.zip"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Archive:  Used_Electronics_Participants_Data.zip\n",
            "   creating: Used_Electronics_Participants_Data/\n",
            "  inflating: Used_Electronics_Participants_Data/Test.csv  \n",
            "  inflating: __MACOSX/Used_Electronics_Participants_Data/._Test.csv  \n",
            "  inflating: Used_Electronics_Participants_Data/Sample_Submission.xlsx  \n",
            "  inflating: __MACOSX/Used_Electronics_Participants_Data/._Sample_Submission.xlsx  \n",
            "  inflating: Used_Electronics_Participants_Data/Train.csv  \n",
            "  inflating: __MACOSX/Used_Electronics_Participants_Data/._Train.csv  \n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "S15kh2ty1iqP",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import warnings\n",
        "warnings.filterwarnings(\"ignore\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fwdD2IJjFDi9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import re\n",
        "import matplotlib.pyplot as plt\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1DydLOW6FHSK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.read_csv(\"/content/Used_Electronics_Participants_Data/Train.csv\")\n",
        "dft = pd.read_csv(\"/content/Used_Electronics_Participants_Data/Test.csv\")"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3bzaIRgLFXXV",
        "colab_type": "code",
        "outputId": "6172f51d-be22-46af-f7f3-d7372b574929",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "df.head()"
      ],
      "execution_count": 6,
      "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>Brand</th>\n",
              "      <th>Model_Info</th>\n",
              "      <th>Additional_Description</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 name234 64gb space grey</td>\n",
              "      <td>1yesr old mobile number 999two905two99 bill c...</td>\n",
              "      <td>878</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>15000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 7 name42 name453 new condition box acce...</td>\n",
              "      <td>101004800 1010065900 7000</td>\n",
              "      <td>1081</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>18800</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 x 256gb leess used good condition</td>\n",
              "      <td>1010010000 seperate screen guard 3 back cover...</td>\n",
              "      <td>495</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>50000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6s plus 64 gb space grey</td>\n",
              "      <td>without 1010020100 id 1010010300 colour 10100...</td>\n",
              "      <td>287</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16500</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 7 sealed pack brand new factory outet p...</td>\n",
              "      <td>101008700 10100000 xs max 64 gb made 10100850...</td>\n",
              "      <td>342</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>26499</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Brand                                         Model_Info  ... State  Price\n",
              "0      1                      name0 name234 64gb space grey  ...     2  15000\n",
              "1      1   phone 7 name42 name453 new condition box acce...  ...     0  18800\n",
              "2      1            name0 x 256gb leess used good condition  ...     4  50000\n",
              "3      1                     name0 6s plus 64 gb space grey  ...     7  16500\n",
              "4      1   phone 7 sealed pack brand new factory outet p...  ...     0  26499\n",
              "\n",
              "[5 rows x 7 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KlIqovc4FbuO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "S = ['16gb','16 gb','32gb','32 gb','64gb','64 gb','128gb','128 gb','256gb','256 gb','512gb','512 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in S)\n",
        "df['Storage'] = df['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "df['Storage1'] = df['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "\n",
        "df.Storage.fillna(df.Storage1, inplace=True)\n",
        "del df['Storage1']\n",
        "\n",
        "df[\"Storage\"].replace({\"16gb\": 16,\n",
        "                           \"16 gb\": 16,\n",
        "                           \"32gb\": 32,\n",
        "                           \"32 gb\": 32,\n",
        "                           \"64gb\": 64,\n",
        "                           \"64 gb\": 64,\n",
        "                           \"128 gb\": 128,\n",
        "                           \"128gb\": 128,\n",
        "                           \"256 gb\": 256,\n",
        "                           \"256gb\": 256,\n",
        "                           \"512 gb\": 512,\n",
        "                           \"512gb\": 512}, inplace=True)  \n",
        "\n",
        "\n",
        "S = ['16gb','16 gb','32gb','32 gb','64gb','64 gb','128gb','128 gb','256gb','256 gb','512gb','512 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in S)\n",
        "dft['Storage'] = dft['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "dft['Storage1'] = dft['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "\n",
        "dft.Storage.fillna(dft.Storage1, inplace=True)\n",
        "del dft['Storage1']\n",
        "\n",
        "dft[\"Storage\"].replace({\"16gb\": 16,\n",
        "                           \"16 gb\": 16,\n",
        "                           \"32gb\": 32,\n",
        "                           \"32 gb\": 32,\n",
        "                           \"64gb\": 64,\n",
        "                           \"64 gb\": 64,\n",
        "                           \"128 gb\": 128,\n",
        "                           \"128gb\": 128,\n",
        "                           \"256 gb\": 256,\n",
        "                           \"256gb\": 256,\n",
        "                           \"512 gb\": 512,\n",
        "                           \"512gb\": 512}, inplace=True)  "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WXD26BkXX4IB",
        "colab_type": "code",
        "outputId": "3ad8b213-f1be-435a-f4eb-8dddd423ebdf",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "df.head(5)"
      ],
      "execution_count": 8,
      "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>Brand</th>\n",
              "      <th>Model_Info</th>\n",
              "      <th>Additional_Description</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Price</th>\n",
              "      <th>Storage</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 name234 64gb space grey</td>\n",
              "      <td>1yesr old mobile number 999two905two99 bill c...</td>\n",
              "      <td>878</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>15000</td>\n",
              "      <td>64.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 7 name42 name453 new condition box acce...</td>\n",
              "      <td>101004800 1010065900 7000</td>\n",
              "      <td>1081</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>18800</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 x 256gb leess used good condition</td>\n",
              "      <td>1010010000 seperate screen guard 3 back cover...</td>\n",
              "      <td>495</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>50000</td>\n",
              "      <td>256.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6s plus 64 gb space grey</td>\n",
              "      <td>without 1010020100 id 1010010300 colour 10100...</td>\n",
              "      <td>287</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16500</td>\n",
              "      <td>64.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 7 sealed pack brand new factory outet p...</td>\n",
              "      <td>101008700 10100000 xs max 64 gb made 10100850...</td>\n",
              "      <td>342</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>26499</td>\n",
              "      <td>64.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Brand                                         Model_Info  ...  Price  Storage\n",
              "0      1                      name0 name234 64gb space grey  ...  15000     64.0\n",
              "1      1   phone 7 name42 name453 new condition box acce...  ...  18800      NaN\n",
              "2      1            name0 x 256gb leess used good condition  ...  50000    256.0\n",
              "3      1                     name0 6s plus 64 gb space grey  ...  16500     64.0\n",
              "4      1   phone 7 sealed pack brand new factory outet p...  ...  26499     64.0\n",
              "\n",
              "[5 rows x 8 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "J-OviAE0AYd3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "!pip install h2o\n",
        "import h2o\n",
        "h2o.init()\n",
        "from h2o.automl import H2OAutoML"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zOqtt-pfQ9z3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "# applying groupby() function to group the data on team value. \n",
        "gk = df.groupby('Brand') \n",
        "model0 = gk.get_group(0)\n",
        "model1 = gk.get_group(1) \n",
        "model2 = gk.get_group(2)\n",
        "model3 = gk.get_group(3) \n",
        "\n",
        "gk_test = dft.groupby('Brand') \n",
        "model0_test = gk_test.get_group(0)\n",
        "model1_test = gk_test.get_group(1) \n",
        "model2_test = gk_test.get_group(2)\n",
        "model3_test = gk_test.get_group(3) "
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MH0FeVbUQweF",
        "colab_type": "text"
      },
      "source": [
        "##Model0"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "snpJd9NUAToR",
        "colab_type": "text"
      },
      "source": [
        "### Data PreProcessing"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "11SkV_eHUjYq",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "indices= model0['Price'] <= 5500\n",
        "model0.loc[indices,'Storage'] = model0.loc[indices,'Storage'].fillna(16)\n",
        "\n",
        "indices=  model0['Price'] <= 11500\n",
        "model0.loc[indices,'Storage'] = model0.loc[indices,'Storage'].fillna(32)\n",
        "\n",
        "model0['Storage'].fillna(64 , inplace=True)\n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model0['RAM1'] = model0['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model0['RAM2'] = model0['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "\n",
        "model0.RAM1.fillna(model0.RAM2, inplace=True)\n",
        "del model0['RAM2']\n",
        "\n",
        "\n",
        "model0[\"RAM1\"].replace({\"2gb\": 2,\n",
        "                           \"2 gb\": 2,\n",
        "                           \"3gb\": 3,\n",
        "                           \"3 gb\": 3,\n",
        "                           \"4gb\": 4,\n",
        "                           \"4 gb\": 4,\n",
        "                           \"6 gb\": 6,\n",
        "                           \"6gb\": 6}, inplace=True)  \n",
        "\n",
        "indices= model0['Storage'] == 16\n",
        "model0.loc[indices,'RAM1'] = model0.loc[indices,'RAM1'].fillna(2)\n",
        "\n",
        "indices= model0['Storage'] == 32\n",
        "model0.loc[indices,'RAM1'] = model0.loc[indices,'RAM1'].fillna(3)\n",
        "\n",
        "indices= model0['Storage'] == 64\n",
        "model0.loc[indices,'RAM1'] = model0.loc[indices,'RAM1'].fillna(4)\n",
        "\n",
        "model0['RAM1'].fillna(6 , inplace=True)\n",
        "\n",
        "M = ['10 lite','10lite','p20 lite','9 lite','p20lite','9lite','8 lite','8lite','7x','7c','7a', '8c,''8x','nova','view','mate','9','9n','6x','8','7','10','5','5x','5 x','honor']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model0['Model1'] = model0['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model0['Model2'] = model0['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "\n",
        "model0.Model1.fillna(model0.Model2, inplace=True)\n",
        "del model0['Model2']\n",
        "\n",
        "model0[\"Model1\"].replace({\"10 lite\": \"10L\", \"10lite\": \"10L\", \"10\": \"10L\",\n",
        "                        \"9 lite\": \"9L\", \"9lite\": \"9L\", \"9\": \"9L\",\"9n\": \"9L\",\n",
        "                        \"8 lite\": \"8L\", \"8lite\": \"8L\", \"8\": \"8L\",\"8x\": \"8L\",\"8c\": \"8L\",\n",
        "                        \"7x\": \"7L\", \"7c\": \"7L\", \"7\": \"7L\", \"7a\": \"7L\",\n",
        "                        \"6x\": \"6L\",\n",
        "                        \"p20 lite\": \"P2L\",\"p20lite\": \"P2L\",\n",
        "                        \"5x\": \"5L\", \"5 x\": \"5L\", \"5\": \"5L\"\n",
        "                        }, inplace=True)\n",
        "\n",
        "model0['Model1'].fillna(\"Other\",inplace=True)   \n",
        "\n",
        "\n",
        "M = ['10 lite','10lite','p20 lite','9 lite','p20lite','9lite','8 lite','8lite','7x','7c','7a', '8c,''8x','nova','view','mate','9','9n','6x','8','7','10','5','5x','5 x','honor']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model0_test['Model1'] = model0_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model0_test['Model2'] = model0_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "\n",
        "model0_test.Model1.fillna(model0_test.Model2, inplace=True)\n",
        "del model0_test['Model2']\n",
        "\n",
        "model0_test[\"Model1\"].replace({\"10 lite\": \"10L\", \"10lite\": \"10L\", \"10\": \"10L\",\n",
        "                        \"9 lite\": \"9L\", \"9lite\": \"9L\", \"9\": \"9L\",\"9n\": \"9L\",\n",
        "                        \"8 lite\": \"8L\", \"8lite\": \"8L\", \"8\": \"8L\",\"8x\": \"8L\",\"8c\": \"8L\",\n",
        "                        \"7x\": \"7L\", \"7c\": \"7L\", \"7\": \"7L\", \"7a\": \"7L\",\n",
        "                        \"6x\": \"6L\",\n",
        "                        \"p20 lite\": \"P2L\",\"p20lite\": \"P2L\",\n",
        "                        \"5x\": \"5L\", \"5 x\": \"5L\", \"5\": \"5L\"\n",
        "                        }, inplace=True)\n",
        "\n",
        "model0_test['Model1'].fillna(\"Other\",inplace=True)   \n",
        "\n",
        "indices= model0_test['Model1'] == \"10L\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(32)\n",
        "\n",
        "indices= model0_test['Model1'] == \"9L\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(64)\n",
        "\n",
        "indices= model0_test['Model1'] == \"7L\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(64)\n",
        "\n",
        "indices= model0_test['Model1'] == \"6L\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(64)\n",
        "\n",
        "indices= model0_test['Model1'] == \"mate\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(128)\n",
        "\n",
        "indices= model0_test['Model1'] == \"honor\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(32)\n",
        "\n",
        "indices= model0_test['Model1'] == \"Other\"\n",
        "model0_test.loc[indices,'Storage'] = model0_test.loc[indices,'Storage'].fillna(32)\n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model0_test['RAM1'] = model0_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model0_test['RAM2'] = model0_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "\n",
        "model0_test.RAM1.fillna(model0_test.RAM2, inplace=True)\n",
        "del model0_test['RAM2']\n",
        "\n",
        "\n",
        "model0_test[\"RAM1\"].replace({\"2gb\": 2,\n",
        "                           \"2 gb\": 2,\n",
        "                           \"3gb\": 3,\n",
        "                           \"3 gb\": 3,\n",
        "                           \"4gb\": 4,\n",
        "                           \"4 gb\": 4,\n",
        "                           \"6 gb\": 6,\n",
        "                           \"6gb\": 6}, inplace=True)  \n",
        "\n",
        "indices= model0_test['Storage'] == 16\n",
        "model0_test.loc[indices,'RAM1'] = model0_test.loc[indices,'RAM1'].fillna(2)\n",
        "\n",
        "indices= model0_test['Storage'] == 32\n",
        "model0_test.loc[indices,'RAM1'] = model0_test.loc[indices,'RAM1'].fillna(3)\n",
        "\n",
        "indices= model0_test['Storage'] == 64\n",
        "model0_test.loc[indices,'RAM1'] = model0_test.loc[indices,'RAM1'].fillna(4)\n",
        "\n",
        "model0_test['RAM1'].fillna(6 , inplace=True)\n"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "S-TyeFvlCNMh",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "del model0['Additional_Description']\n",
        "del model0['Model_Info']\n",
        "del model0_test['Additional_Description']\n",
        "del model0_test['Model_Info']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1JckasNGBUOK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "model0.to_csv(\"model0.csv\",index=True)\n",
        "model0_test.to_csv(\"model0_test.csv\",index=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AaarOjeOAZGE",
        "colab_type": "text"
      },
      "source": [
        "### Modelling"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tjKe35v7Ork1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ax2LMmXGj4g8",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 84
        },
        "outputId": "21a5645f-fdf1-4b5c-ae39-487b4f396156"
      },
      "source": [
        "df_model0 = h2o.import_file('/content/model0.csv')\n",
        "dft_model0 = h2o.import_file('/content/model0_test.csv')\n",
        "\n",
        "df_model0['Brand'] = df_model0['Brand'].asfactor()\n",
        "df_model0['Locality'] = df_model0['Locality'].asfactor()\n",
        "df_model0['State'] = df_model0['State'].asfactor()\n",
        "dft_model0['Brand'] = dft_model0['Brand'].asfactor()\n",
        "dft_model0['Locality'] = dft_model0['Locality'].asfactor()\n",
        "dft_model0['State'] = dft_model0['State'].asfactor()\n",
        "ignored_col_list = \"C1\"\n",
        "\n",
        "print(df_model0.types)\n",
        "print(dft_model0.types)\n",
        "\n",
        "df_train, df_val, df_test = df_model0.split_frame(ratios=[.9,.05])\n",
        "y = \"Price\"\n",
        "x = df_model0.columns\n",
        "x.remove(y)\n",
        "x.remove(ignored_col_list)"
      ],
      "execution_count": 138,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Price': 'int', 'Storage': 'int', 'RAM1': 'int', 'Model1': 'enum'}\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Flx4QmkxDgTU",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "33612c23-a509-493a-d589-00d6a32df4ab"
      },
      "source": [
        "aml = H2OAutoML(nfolds=15,\n",
        "                balance_classes=False,\n",
        "                max_runtime_secs=240,\n",
        "                max_models=0,\n",
        "                exclude_algos=['DeepLearning','GLM'],\n",
        "                verbosity = \"info\",\n",
        "                sort_metric='RMSLE',\n",
        "                stopping_metric='RMSLE')\n",
        "aml.train(x=x,y=y,training_frame=df_train,validation_frame=df_val)\n",
        "aml.leaderboard"
      ],
      "execution_count": 139,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "AutoML progress: |\n",
            "20:56:57.708: Project: AutoML_20200607_205657708\n",
            "20:56:57.708: User specified a validation frame with cross-validation still enabled. Please note that the models will still be validated using cross-validation only, the validation frame will be used to provide purely informative validation metrics on the trained models.\n",
            "20:56:57.708: Setting stopping tolerance adaptively based on the training frame: 0.05\n",
            "20:56:57.708: Build control seed: -1 (random)\n",
            "20:56:57.708: training frame: Frame key: automl_training_py_4566_sid_ab9d    cols: 9    rows: 74  chunks: 1    size: 9482  checksum: -1660705785103500\n",
            "20:56:57.708: validation frame: Frame key: py_4567_sid_ab9d    cols: 9    rows: 7  chunks: 1    size: 8680  checksum: -343692596488320\n",
            "20:56:57.708: leaderboard frame: NULL\n",
            "20:56:57.708: blending frame: NULL\n",
            "20:56:57.708: response column: Price\n",
            "20:56:57.708: fold column: null\n",
            "20:56:57.708: weights column: null\n",
            "20:56:57.708: Loading execution steps: [{XGBoost : defaults}, {GLM : defaults}, {DRF : [def_1]}, {GBM : defaults}, {DeepLearning : defaults}, {DRF : [XRT]}, {XGBoost : grids}, {GBM : grids}, {DeepLearning : grids}, {GBM : [lr_annealing]}, {XGBoost : [lr_search]}, {StackedEnsemble : defaults}]\n",
            "20:56:57.708: Disabling Algo: DeepLearning as requested by the user.\n",
            "20:56:57.709: Disabling Algo: GLM as requested by the user.\n",
            "20:56:57.709: AutoML job created: 2020.06.07 20:56:57.708\n",
            "20:56:57.710: AutoML build started: 2020.06.07 20:56:57.710\n",
            "20:56:57.710: AutoML: starting XGBoost_1_AutoML_20200607_205657 model training\n",
            "\n",
            "█\n",
            "20:56:59.712: New leader: XGBoost_1_AutoML_20200607_205657, rmsle: 0.6481642660561722\n",
            "20:56:59.713: AutoML: starting XGBoost_2_AutoML_20200607_205657 model training\n",
            "\n",
            "█\n",
            "20:57:00.714: AutoML: starting XGBoost_3_AutoML_20200607_205657 model training\n",
            "\n",
            "██\n",
            "20:57:02.716: New leader: XGBoost_3_AutoML_20200607_205657, rmsle: 0.6053545776928556\n",
            "20:57:02.717: AutoML: starting DRF_1_AutoML_20200607_205657 model training\n",
            "20:57:03.719: New leader: DRF_1_AutoML_20200607_205657, rmsle: 0.493406570211936\n",
            "20:57:03.719: AutoML: starting GBM_1_AutoML_20200607_205657 model training\n",
            "\n",
            "██\n",
            "20:57:04.722: AutoML: starting GBM_2_AutoML_20200607_205657 model training\n",
            "\n",
            "██\n",
            "20:57:05.724: AutoML: starting GBM_3_AutoML_20200607_205657 model training\n",
            "\n",
            "██\n",
            "20:57:06.726: AutoML: starting GBM_4_AutoML_20200607_205657 model training\n",
            "20:57:07.733: AutoML: starting GBM_5_AutoML_20200607_205657 model training\n",
            "20:57:07.734: Skipping training of model GBM_5_AutoML_20200607_205657 due to exception: water.exceptions.H2OModelBuilderIllegalArgumentException: Illegal argument(s) for GBM model: GBM_5_AutoML_20200607_205657.  Details: ERRR on field: _min_rows: The dataset size is too small to split for min_rows=100.0: must have at least 200.0 (weighted) rows, but have only 74.0.\n",
            "\n",
            "20:57:07.734: AutoML: starting XRT_1_AutoML_20200607_205657 model training\n",
            "\n",
            "█████\n",
            "20:57:09.736: AutoML: starting XGBoost_grid__1_AutoML_20200607_205657 hyperparameter search\n",
            "\n",
            "████████████████\n",
            "20:57:42.593: New leader: XGBoost_grid__1_AutoML_20200607_205657_model_16, rmsle: 0.4829436582590031\n",
            "\n",
            "█\n",
            "20:57:48.597: New leader: XGBoost_grid__1_AutoML_20200607_205657_model_19, rmsle: 0.4703716405045248\n",
            "\n",
            "████\n",
            "20:58:25.663: AutoML: starting GBM_grid__1_AutoML_20200607_205657 hyperparameter search\n",
            "\n",
            "████████\n",
            "20:58:33.874: AutoML: starting StackedEnsemble_BestOfFamily_AutoML_20200607_205657 model training\n",
            "\n",
            "███\n",
            "20:58:34.877: AutoML: starting StackedEnsemble_AllModels_AutoML_20200607_205657 model training\n",
            "\n",
            "████████\n",
            "20:58:36.880: Actual modeling steps: [{XGBoost : [def_1 (10), def_2 (10), def_3 (10)]}, {DRF : [def_1 (10)]}, {GBM : [def_1 (10), def_2 (10), def_3 (10), def_4 (10)]}, {DRF : [XRT (10)]}, {XGBoost : [grid_1 (100)]}, {GBM : [grid_1 (60)]}, {StackedEnsemble : [best (10), all (10)]}]\n",
            "20:58:36.880: AutoML build stopped: 2020.06.07 20:58:36.880\n",
            "20:58:36.881: AutoML build done: built 65 models\n",
            "20:58:36.881: AutoML duration:  1 min 39.170 sec\n",
            "\n",
            "█| 100%\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th>model_id                                       </th><th style=\"text-align: right;\">   rmsle</th><th style=\"text-align: right;\">  mean_residual_deviance</th><th style=\"text-align: right;\">    rmse</th><th style=\"text-align: right;\">        mse</th><th style=\"text-align: right;\">    mae</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205657_model_19</td><td style=\"text-align: right;\">0.470372</td><td style=\"text-align: right;\">             1.02769e+08</td><td style=\"text-align: right;\">10137.5 </td><td style=\"text-align: right;\">1.02769e+08</td><td style=\"text-align: right;\">4416.58</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205657_model_16</td><td style=\"text-align: right;\">0.482944</td><td style=\"text-align: right;\">             9.6073e+07 </td><td style=\"text-align: right;\"> 9801.68</td><td style=\"text-align: right;\">9.6073e+07 </td><td style=\"text-align: right;\">4491.82</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205657_model_26</td><td style=\"text-align: right;\">0.486055</td><td style=\"text-align: right;\">             1.01381e+08</td><td style=\"text-align: right;\">10068.8 </td><td style=\"text-align: right;\">1.01381e+08</td><td style=\"text-align: right;\">4556.9 </td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205657_model_25</td><td style=\"text-align: right;\">0.49233 </td><td style=\"text-align: right;\">             1.06991e+08</td><td style=\"text-align: right;\">10343.6 </td><td style=\"text-align: right;\">1.06991e+08</td><td style=\"text-align: right;\">4669.7 </td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_205657_model_5     </td><td style=\"text-align: right;\">0.493397</td><td style=\"text-align: right;\">             1.00467e+08</td><td style=\"text-align: right;\">10023.3 </td><td style=\"text-align: right;\">1.00467e+08</td><td style=\"text-align: right;\">4533.88</td></tr>\n",
              "<tr><td>DRF_1_AutoML_20200607_205657                   </td><td style=\"text-align: right;\">0.493407</td><td style=\"text-align: right;\">             1.06581e+08</td><td style=\"text-align: right;\">10323.8 </td><td style=\"text-align: right;\">1.06581e+08</td><td style=\"text-align: right;\">4367.9 </td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205657_model_3 </td><td style=\"text-align: right;\">0.496556</td><td style=\"text-align: right;\">             9.72985e+07</td><td style=\"text-align: right;\"> 9864   </td><td style=\"text-align: right;\">9.72985e+07</td><td style=\"text-align: right;\">4870.54</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205657_model_36</td><td style=\"text-align: right;\">0.496837</td><td style=\"text-align: right;\">             9.68252e+07</td><td style=\"text-align: right;\"> 9839.98</td><td style=\"text-align: right;\">9.68252e+07</td><td style=\"text-align: right;\">4910.79</td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_205657_model_18    </td><td style=\"text-align: right;\">0.520412</td><td style=\"text-align: right;\">             1.00892e+08</td><td style=\"text-align: right;\">10044.5 </td><td style=\"text-align: right;\">1.00892e+08</td><td style=\"text-align: right;\">5009.45</td></tr>\n",
              "<tr><td>XRT_1_AutoML_20200607_205657                   </td><td style=\"text-align: right;\">0.520709</td><td style=\"text-align: right;\">             1.16616e+08</td><td style=\"text-align: right;\">10798.9 </td><td style=\"text-align: right;\">1.16616e+08</td><td style=\"text-align: right;\">5374.38</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 139
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bAxWqRLWDgMO",
        "colab_type": "code",
        "outputId": "0f6d939d-58ef-4c74-c253-4ce8d27a9c4c",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "pred = aml.leader.predict(df_test)"
      ],
      "execution_count": 140,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "xgboost prediction progress: |████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "tenpF3nbDgJw",
        "colab_type": "code",
        "outputId": "7bdeb61d-0632-4704-8f04-85f7046f81f1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 116
        }
      },
      "source": [
        "pred.head()"
      ],
      "execution_count": 141,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  9545.83</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 12955.6 </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  5635.13</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 12776.3 </td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 141
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "6d2OiDFREAO3",
        "colab_type": "code",
        "outputId": "65c14de8-6368-42c8-8120-2f37a3974186",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 167
        }
      },
      "source": [
        "aml.leader.model_performance(df_test)"
      ],
      "execution_count": 142,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "ModelMetricsRegression: xgboost\n",
            "** Reported on test data. **\n",
            "\n",
            "MSE: 14036578.628154576\n",
            "RMSE: 3746.5422229243027\n",
            "MAE: 2500.3978271484375\n",
            "RMSLE: 0.4330040620730148\n",
            "Mean Residual Deviance: 14036578.628154576\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 142
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rSrNAVkDJHR4",
        "colab_type": "code",
        "outputId": "ef404491-f620-4531-87a3-1772490a31c5",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "dft_model0['predict'] = aml.leader.predict(dft_model0)"
      ],
      "execution_count": 143,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "xgboost prediction progress: |████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oZZKM60OJVdk",
        "colab_type": "code",
        "outputId": "8e3dbf2c-84a5-43b7-8761-166ea519da7a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 234
        }
      },
      "source": [
        "dft_model0"
      ],
      "execution_count": 144,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  C1</th><th style=\"text-align: right;\">  Brand</th><th style=\"text-align: right;\">  Locality</th><th style=\"text-align: right;\">  City</th><th style=\"text-align: right;\">  State</th><th style=\"text-align: right;\">  Storage</th><th>Model1  </th><th style=\"text-align: right;\">  RAM1</th><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  15</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       361</td><td style=\"text-align: right;\">     0</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       64</td><td>9L      </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\"> 12523   </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  17</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       547</td><td style=\"text-align: right;\">    10</td><td style=\"text-align: right;\">      7</td><td style=\"text-align: right;\">       64</td><td>7L      </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  9044.53</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  21</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       174</td><td style=\"text-align: right;\">    10</td><td style=\"text-align: right;\">      7</td><td style=\"text-align: right;\">       16</td><td>Other   </td><td style=\"text-align: right;\">     2</td><td style=\"text-align: right;\">  5576.69</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  40</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       128</td><td style=\"text-align: right;\">    13</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">       32</td><td>Other   </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\"> 10678   </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  50</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       157</td><td style=\"text-align: right;\">    10</td><td style=\"text-align: right;\">      7</td><td style=\"text-align: right;\">       32</td><td>Other   </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  9263.8 </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  62</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       827</td><td style=\"text-align: right;\">     0</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       64</td><td>9L      </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\"> 12523   </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  69</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       640</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       64</td><td>7L      </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  9031.4 </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  85</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       970</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       64</td><td>9L      </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\"> 10144.1 </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  94</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       615</td><td style=\"text-align: right;\">    13</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">       32</td><td>honor   </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  8456.8 </td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 125</td><td style=\"text-align: right;\">      0</td><td style=\"text-align: right;\">       318</td><td style=\"text-align: right;\">    11</td><td style=\"text-align: right;\">      4</td><td style=\"text-align: right;\">       32</td><td>honor   </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  9514.86</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 144
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DtUWMASivOMR",
        "colab_type": "text"
      },
      "source": [
        "#### Output"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "l-izIut_L9qN",
        "colab_type": "code",
        "outputId": "d2c0a206-ea59-4499-a3fc-1e5041d53481",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "h2o.download_csv(dft_model0, \"dft_model0.csv\")"
      ],
      "execution_count": 145,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'/content/dft_model0.csv'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 145
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "W1C7pFiyEAH5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "h2o_out = pd.read_csv('/content/dft_model0.csv')\n",
        "h2o_out.set_index('C1', inplace=True)\n",
        "#h2o_out.head()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pZB2nIj8MPOy",
        "colab_type": "code",
        "outputId": "f5ac445c-2131-4e06-f36b-1621c98eab3a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model0_test.head()"
      ],
      "execution_count": 147,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>0</td>\n",
              "      <td>361</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>64.0</td>\n",
              "      <td>9L</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>0</td>\n",
              "      <td>547</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>64.0</td>\n",
              "      <td>7L</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>0</td>\n",
              "      <td>174</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16.0</td>\n",
              "      <td>Other</td>\n",
              "      <td>2.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>0</td>\n",
              "      <td>128</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Other</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>0</td>\n",
              "      <td>157</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Other</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    Brand  Locality  City  State  Storage Model1  RAM1\n",
              "15      0       361     0      1     64.0     9L   4.0\n",
              "17      0       547    10      7     64.0     7L   4.0\n",
              "21      0       174    10      7     16.0  Other   2.0\n",
              "40      0       128    13      5     32.0  Other   3.0\n",
              "50      0       157    10      7     32.0  Other   3.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 147
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gUzbWBl6MWgN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "model0_test['Price'] = h2o_out['predict']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "I-jO5AB4Mb9B",
        "colab_type": "code",
        "outputId": "56e6f012-3955-4188-eb3d-1f34a64a097d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model0_test.head()"
      ],
      "execution_count": 149,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>0</td>\n",
              "      <td>361</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>64.0</td>\n",
              "      <td>9L</td>\n",
              "      <td>4.0</td>\n",
              "      <td>12523.043945</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>0</td>\n",
              "      <td>547</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>64.0</td>\n",
              "      <td>7L</td>\n",
              "      <td>4.0</td>\n",
              "      <td>9044.527344</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>0</td>\n",
              "      <td>174</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16.0</td>\n",
              "      <td>Other</td>\n",
              "      <td>2.0</td>\n",
              "      <td>5576.688965</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>0</td>\n",
              "      <td>128</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Other</td>\n",
              "      <td>3.0</td>\n",
              "      <td>10677.984375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>0</td>\n",
              "      <td>157</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Other</td>\n",
              "      <td>3.0</td>\n",
              "      <td>9263.803711</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    Brand  Locality  City  State  Storage Model1  RAM1         Price\n",
              "15      0       361     0      1     64.0     9L   4.0  12523.043945\n",
              "17      0       547    10      7     64.0     7L   4.0   9044.527344\n",
              "21      0       174    10      7     16.0  Other   2.0   5576.688965\n",
              "40      0       128    13      5     32.0  Other   3.0  10677.984375\n",
              "50      0       157    10      7     32.0  Other   3.0   9263.803711"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 149
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Fgegf27gvEhf",
        "colab_type": "text"
      },
      "source": [
        "#### Model Selection"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ysOdbQk9IR8v",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "model_ids = list(aml.leaderboard['model_id'].as_data_frame().iloc[:,0])\n",
        "model_ids"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hgb8vbIvIE5H",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "h2o.get_model([mid for mid in model_ids if \"XGBoost\" in mid][0])"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "eihCPOUrIg_g",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "out = h2o.get_model([mid for mid in model_ids if \"XGBoost\" in mid][0])\n",
        "out.convert_H2OXGBoostParams_2_XGBoostParams()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Isn_JD-sQzDC",
        "colab_type": "text"
      },
      "source": [
        "##Model1"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "2SUYaXKwsAA-",
        "colab_type": "text"
      },
      "source": [
        "### Data Preprocessing"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "MFIx07LGKUKx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "IP = ['iphone x','iphonex','iphone 8','iphone 8 plus','iphone 7','iphone 7 plus',\n",
        "      'iphone 6','iphone 6s','phone 7','phone 6','phone 8','7','6','8','7plus','6s','xs max','xs','5s',\n",
        "      'iphone xs','iphonexs','iphone8','8plus','iphone7','iphone7plus',\n",
        "      'iphone6','iphone6s','phone7','phone6','space grey','iphone','5','max','x','se','ipad','apple','xr','rose gold','watch','air pods','iphones','series','4s','4','iphones']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in IP)\n",
        "model1['Model1'] = model1['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1['Model2'] = model1['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1.Model1.fillna(model1.Model2, inplace=True)\n",
        "del model1['Model2']\n",
        "\n",
        "model1[\"Model1\"].replace({\"iphone x\": \"X\", \"iphonex\": \"X\", \"x\": \"X\", \"xr\": \"X\", \"xs\": \"X\", \"xs max\":\"X\", \"iphonexs\": \"X\", \"iphone xs\": \"X\",\"max\": \"X\",\n",
        "                          \"iphone 8\": \"E\", \"iphone8\": \"E\", \"iphone 8 plus\": \"E\", \"phone 8\": \"E\", \"8\": \"E\",\"8plus\": \"E\",\n",
        "                          \"iphone 7\": \"E\", \"iphone7\": \"E\", \"iphone 7 plus\": \"E\", \"phone 7\": \"E\", \"7\": \"E\", \"7plus\": \"E\",\"phone7\": \"E\",\n",
        "                          \"iphone 6\": \"Si\", \"iphone6\": \"Si\", \"iphone 6s\": \"Si\", \"phone 6\": \"Si\", \"6\": \"Si\", \"6s\": \"Si\",\"iphone6s\": \"Si\",\n",
        "                          \"iphone 5\": \"F\", \"iphone5\": \"F\", \"5s\": \"F\", \"5\": \"F\", \"4\": \"F\", \"4s\": \"F\",\"se\" : \"F\",\"space grey\":\"F\",\n",
        "                          \"iphone\": \"Si\",\"ipad\": \"F\",\"rose gold\": \"Si\",\"series\": \"F\",\"air pods\": \"F\",\"apple\": \"F\",\"iphones\": \"Si\",\"watch\":\"F\"\n",
        "                          }, inplace=True)\n",
        "model1['Model1'].fillna(\"Others\" , inplace=True)\n",
        "indices= model1['Price'] <= 25000\n",
        "model1.loc[indices,'Storage'] = model1.loc[indices,'Storage'].fillna(32)\n",
        "indices=  model1['Price'] <= 60000\n",
        "model1.loc[indices,'Storage'] = model1.loc[indices,'Storage'].fillna(64)\n",
        "model1['Storage'].fillna(256 , inplace=True)\n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model1['RAM1'] = model1['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1['RAM2'] = model1['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1.RAM1.fillna(model1.RAM2, inplace=True)\n",
        "del model1['RAM2']\n",
        "model1[\"RAM1\"].replace({\"2gb\": 2, \"2 gb\": 2,\"3gb\": 3,\"3 gb\": 3, \"4gb\": 4, \"4 gb\": 4, \"6 gb\": 6, \"6gb\": 6}, inplace=True)  \n",
        "model1['RAM1'].fillna(3 , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uoQ7sfagIABm",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "M = ['iphone x','iphonex','iphone 8','iphone 8 plus','iphone 7','iphone 7 plus',\n",
        "      'iphone 6','iphone 6s','phone 7','phone 6','phone 8','7','6','8','7plus','6s','xs max','xs','5s',\n",
        "      'iphone xs','iphonexs','iphone8','8plus','iphone7','iphone7plus',\n",
        "      'iphone6','iphone6s','phone7','phone6','space grey','iphone','5','max','x','se','ipad','apple','xr','rose gold','watch','air pods','iphones','series','4s','4','iphones']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model1_test['Model1'] = model1_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1_test['model1'] = model1_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1_test.Model1.fillna(model1_test.model1, inplace=True)\n",
        "del model1_test['model1']\n",
        "model1_test[\"Model1\"].replace({\"iphone x\": \"X\", \"iphonex\": \"X\", \"x\": \"X\", \"xr\": \"X\", \"xs\": \"X\", \"xs max\":\"X\", \"iphonexs\": \"X\", \"iphone xs\": \"X\",\"max\": \"X\",\n",
        "                          \"iphone 8\": \"E\", \"iphone8\": \"E\", \"iphone 8 plus\": \"E\", \"phone 8\": \"E\", \"8\": \"E\",\"8plus\": \"E\",\n",
        "                          \"iphone 7\": \"E\", \"iphone7\": \"E\", \"iphone 7 plus\": \"E\", \"phone 7\": \"E\", \"7\": \"E\", \"7plus\": \"E\",\"phone7\": \"E\",\n",
        "                          \"iphone 6\": \"Si\", \"iphone6\": \"Si\", \"iphone 6s\": \"Si\", \"phone 6\": \"Si\", \"6\": \"Si\", \"6s\": \"Si\",\"iphone6s\": \"Si\",\n",
        "                          \"iphone 5\": \"F\", \"iphone5\": \"F\", \"5s\": \"F\", \"5\": \"F\", \"4\": \"F\", \"4s\": \"F\",\"se\" : \"F\",\"space grey\":\"F\",\n",
        "                          \"iphone\": \"Si\",\"ipad\": \"F\",\"rose gold\": \"Si\",\"series\": \"F\",\"air pods\": \"F\",\"apple\": \"F\",\"iphones\": \"Si\",\"watch\":\"F\"\n",
        "                          }, inplace=True)\n",
        "model1_test['Model1'].fillna(\"Others\" , inplace=True)\n",
        "\n",
        "model1_test['Storage'].fillna(32,inplace=True)   \n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model1_test['RAM1'] = model1_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1_test['RAM2'] = model1_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model1_test.RAM1.fillna(model1_test.RAM2, inplace=True)\n",
        "del model1_test['RAM2']\n",
        "model1_test[\"RAM1\"].replace({\"2gb\": 2,\"2 gb\": 2,\"3gb\": 3, \"3 gb\": 3,\"4gb\": 4, \"4 gb\": 4,\"6 gb\": 6, \"6gb\": 6}, inplace=True)  \n",
        "model1_test['RAM1'].fillna(3 , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OT6WkwM6Koae",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "del model1['Additional_Description']\n",
        "del model1['Model_Info']\n",
        "del model1_test['Additional_Description']\n",
        "del model1_test['Model_Info']\n",
        "\n",
        "model1.to_csv(\"model1.csv\",index=True)\n",
        "model1_test.to_csv(\"model1_test.csv\",index=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "v5UKld1wsE-r",
        "colab_type": "text"
      },
      "source": [
        "### Modelling"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ByL-2TGksPbT",
        "colab_type": "code",
        "outputId": "c6262eb5-5c31-4e0e-a589-fcc318111b65",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 84
        }
      },
      "source": [
        "from h2o.automl import H2OAutoML\n",
        "df_model1 = h2o.import_file('/content/model1.csv')\n",
        "dft_model1 = h2o.import_file('/content/model1_test.csv')\n",
        "\n",
        "df_model1['Brand'] = df_model1['Brand'].asfactor()\n",
        "df_model1['Locality'] = df_model1['Locality'].asfactor()\n",
        "df_model1['State'] = df_model1['State'].asfactor()\n",
        "dft_model1['Brand'] = dft_model1['Brand'].asfactor()\n",
        "dft_model1['Locality'] = dft_model1['Locality'].asfactor()\n",
        "dft_model1['State'] = dft_model1['State'].asfactor()\n",
        "ignored_col_list = \"C1\"\n",
        "\n",
        "print(df_model1.types)\n",
        "print(dft_model1.types)\n",
        "df_train, df_val, df_test = df_model1.split_frame(ratios=[0.9,0.04])\n",
        "y = \"Price\"\n",
        "x = df_model1.columns\n",
        "x.remove(y)\n",
        "x.remove(ignored_col_list)"
      ],
      "execution_count": 87,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Price': 'int', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "dI9uzmThsIS1",
        "colab_type": "code",
        "outputId": "b5f50653-31f9-4afa-8cc1-83657f4f2945",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "aml = H2OAutoML(nfolds=15,\n",
        "                balance_classes=False,\n",
        "                max_runtime_secs=240,\n",
        "                max_models=0,\n",
        "                exclude_algos=['DeepLearning','GLM'],\n",
        "                verbosity = \"info\",\n",
        "                sort_metric='RMSLE',\n",
        "                stopping_metric='RMSLE')\n",
        "aml.train(x=x,y=y,training_frame=df_train,validation_frame=df_val)\n",
        "aml.leaderboard"
      ],
      "execution_count": 92,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "AutoML progress: |\n",
            "20:31:58.542: Project: AutoML_20200607_203158542\n",
            "20:31:58.542: User specified a validation frame with cross-validation still enabled. Please note that the models will still be validated using cross-validation only, the validation frame will be used to provide purely informative validation metrics on the trained models.\n",
            "20:31:58.542: Setting stopping tolerance adaptively based on the training frame: 0.02297181937696983\n",
            "20:31:58.542: Build control seed: -1 (random)\n",
            "20:31:58.542: training frame: Frame key: automl_training_py_3424_sid_ab9d    cols: 9    rows: 1895  chunks: 8    size: 106273  checksum: -2477688304978400\n",
            "20:31:58.543: validation frame: Frame key: py_3425_sid_ab9d    cols: 9    rows: 96  chunks: 8    size: 81728  checksum: -3416609799296862\n",
            "20:31:58.543: leaderboard frame: NULL\n",
            "20:31:58.543: blending frame: NULL\n",
            "20:31:58.543: response column: Price\n",
            "20:31:58.543: fold column: null\n",
            "20:31:58.543: weights column: null\n",
            "20:31:58.543: Loading execution steps: [{XGBoost : defaults}, {GLM : defaults}, {DRF : [def_1]}, {GBM : defaults}, {DeepLearning : defaults}, {DRF : [XRT]}, {XGBoost : grids}, {GBM : grids}, {DeepLearning : grids}, {GBM : [lr_annealing]}, {XGBoost : [lr_search]}, {StackedEnsemble : defaults}]\n",
            "20:31:58.543: Disabling Algo: DeepLearning as requested by the user.\n",
            "20:31:58.543: Disabling Algo: GLM as requested by the user.\n",
            "20:31:58.543: AutoML job created: 2020.06.07 20:31:58.542\n",
            "20:31:58.544: AutoML build started: 2020.06.07 20:31:58.544\n",
            "20:31:58.544: AutoML: starting XGBoost_1_AutoML_20200607_203158 model training\n",
            "\n",
            "██\n",
            "20:32:03.553: New leader: XGBoost_1_AutoML_20200607_203158, rmsle: 0.5387670241429564\n",
            "20:32:03.553: AutoML: starting XGBoost_2_AutoML_20200607_203158 model training\n",
            "\n",
            "██\n",
            "20:32:08.555: New leader: XGBoost_2_AutoML_20200607_203158, rmsle: 0.532990723030628\n",
            "20:32:08.555: AutoML: starting XGBoost_3_AutoML_20200607_203158 model training\n",
            "\n",
            "█\n",
            "20:32:11.557: New leader: XGBoost_3_AutoML_20200607_203158, rmsle: 0.5224728672816515\n",
            "20:32:11.558: AutoML: starting DRF_1_AutoML_20200607_203158 model training\n",
            "\n",
            "████\n",
            "20:32:21.561: AutoML: starting GBM_1_AutoML_20200607_203158 model training\n",
            "\n",
            "█\n",
            "20:32:26.563: AutoML: starting GBM_2_AutoML_20200607_203158 model training\n",
            "\n",
            "███\n",
            "20:32:33.572: AutoML: starting GBM_3_AutoML_20200607_203158 model training\n",
            "\n",
            "██\n",
            "20:32:41.584: AutoML: starting GBM_4_AutoML_20200607_203158 model training\n",
            "\n",
            "██\n",
            "20:32:49.622: AutoML: starting GBM_5_AutoML_20200607_203158 model training\n",
            "\n",
            "█\n",
            "20:32:54.630: AutoML: starting XRT_1_AutoML_20200607_203158 model training\n",
            "\n",
            "███\n",
            "20:33:04.638: AutoML: starting XGBoost_grid__1_AutoML_20200607_203158 hyperparameter search\n",
            "\n",
            "████████████████\n",
            "20:34:26.517: New leader: XGBoost_grid__1_AutoML_20200607_203158_model_12, rmsle: 0.5192928479652998\n",
            "\n",
            "███\n",
            "20:34:42.522: New leader: XGBoost_grid__1_AutoML_20200607_203158_model_15, rmsle: 0.5186477295080147\n",
            "20:34:42.523: AutoML: starting GBM_grid__1_AutoML_20200607_203158 hyperparameter search\n",
            "\n",
            "████████████\n",
            "20:35:40.823: AutoML: starting StackedEnsemble_BestOfFamily_AutoML_20200607_203158 model training\n",
            "\n",
            "██\n",
            "20:35:42.837: AutoML: starting StackedEnsemble_AllModels_AutoML_20200607_203158 model training\n",
            "\n",
            "██| 100%\n",
            "\n",
            "20:35:45.839: Actual modeling steps: [{XGBoost : [def_1 (10), def_2 (10), def_3 (10)]}, {DRF : [def_1 (10)]}, {GBM : [def_1 (10), def_2 (10), def_3 (10), def_4 (10), def_5 (10)]}, {DRF : [XRT (10)]}, {XGBoost : [grid_1 (100)]}, {GBM : [grid_1 (60)]}, {StackedEnsemble : [best (10), all (10)]}]\n",
            "20:35:45.839: AutoML build stopped: 2020.06.07 20:35:45.839\n",
            "20:35:45.839: AutoML build done: built 33 models\n",
            "20:35:45.839: AutoML duration:  3 min 47.295 sec\n",
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th>model_id                                           </th><th style=\"text-align: right;\">   rmsle</th><th style=\"text-align: right;\">  mean_residual_deviance</th><th style=\"text-align: right;\">   rmse</th><th style=\"text-align: right;\">        mse</th><th style=\"text-align: right;\">    mae</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_203158_model_15    </td><td style=\"text-align: right;\">0.518648</td><td style=\"text-align: right;\">             2.05358e+08</td><td style=\"text-align: right;\">14330.3</td><td style=\"text-align: right;\">2.05358e+08</td><td style=\"text-align: right;\">8640   </td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_203158_model_12    </td><td style=\"text-align: right;\">0.519293</td><td style=\"text-align: right;\">             1.87482e+08</td><td style=\"text-align: right;\">13692.4</td><td style=\"text-align: right;\">1.87482e+08</td><td style=\"text-align: right;\">8364.98</td></tr>\n",
              "<tr><td>StackedEnsemble_AllModels_AutoML_20200607_203158   </td><td style=\"text-align: right;\">0.522138</td><td style=\"text-align: right;\">             1.83578e+08</td><td style=\"text-align: right;\">13549.1</td><td style=\"text-align: right;\">1.83578e+08</td><td style=\"text-align: right;\">8350.71</td></tr>\n",
              "<tr><td>XGBoost_3_AutoML_20200607_203158                   </td><td style=\"text-align: right;\">0.522473</td><td style=\"text-align: right;\">             1.92242e+08</td><td style=\"text-align: right;\">13865.1</td><td style=\"text-align: right;\">1.92242e+08</td><td style=\"text-align: right;\">8440.95</td></tr>\n",
              "<tr><td>XRT_1_AutoML_20200607_203158                       </td><td style=\"text-align: right;\">0.524386</td><td style=\"text-align: right;\">             1.95635e+08</td><td style=\"text-align: right;\">13987  </td><td style=\"text-align: right;\">1.95635e+08</td><td style=\"text-align: right;\">8444.36</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_203158_model_5     </td><td style=\"text-align: right;\">0.524969</td><td style=\"text-align: right;\">             1.9272e+08 </td><td style=\"text-align: right;\">13882.4</td><td style=\"text-align: right;\">1.9272e+08 </td><td style=\"text-align: right;\">8528.97</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_203158_model_8     </td><td style=\"text-align: right;\">0.525052</td><td style=\"text-align: right;\">             1.9024e+08 </td><td style=\"text-align: right;\">13792.7</td><td style=\"text-align: right;\">1.9024e+08 </td><td style=\"text-align: right;\">8500.61</td></tr>\n",
              "<tr><td>StackedEnsemble_BestOfFamily_AutoML_20200607_203158</td><td style=\"text-align: right;\">0.525325</td><td style=\"text-align: right;\">             1.86619e+08</td><td style=\"text-align: right;\">13660.9</td><td style=\"text-align: right;\">1.86619e+08</td><td style=\"text-align: right;\">8419.6 </td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_203158_model_1     </td><td style=\"text-align: right;\">0.528873</td><td style=\"text-align: right;\">             1.93848e+08</td><td style=\"text-align: right;\">13922.9</td><td style=\"text-align: right;\">1.93848e+08</td><td style=\"text-align: right;\">8518.86</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_203158_model_4     </td><td style=\"text-align: right;\">0.529404</td><td style=\"text-align: right;\">             1.92654e+08</td><td style=\"text-align: right;\">13880  </td><td style=\"text-align: right;\">1.92654e+08</td><td style=\"text-align: right;\">8565.22</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 92
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "8VIvVxZBvyjV",
        "outputId": "6fbf4d99-c9da-4c09-e835-54e649f8cf69",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "pred = aml.leader.predict(df_test)"
      ],
      "execution_count": 93,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "xgboost prediction progress: |████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "IeVpmXeLvyjZ",
        "outputId": "33f18c15-391a-4c9c-a076-10b4a73c82e2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 234
        }
      },
      "source": [
        "pred.head()"
      ],
      "execution_count": 94,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  19359.5</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  49213  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  13609.1</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  49213  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  12446.1</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  18810.9</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  13934.5</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  31273  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  61719.8</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  10090.6</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 94
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "q9uirtS2vyja",
        "outputId": "99ce322e-83a1-4cd1-8b9c-885f3ca49f11",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 167
        }
      },
      "source": [
        "aml.leader.model_performance(df_test)"
      ],
      "execution_count": 95,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "ModelMetricsRegression: xgboost\n",
            "** Reported on test data. **\n",
            "\n",
            "MSE: 104637737.93851574\n",
            "RMSE: 10229.258914433427\n",
            "MAE: 7219.014228515625\n",
            "RMSLE: 0.4351338209870794\n",
            "Mean Residual Deviance: 104637737.93851574\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 95
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "outputId": "fef6237e-a43e-49eb-e992-26bc8435cf66",
        "id": "q8hIWnRXvyjc",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "dft_model1['predict'] = aml.leader.predict(dft_model1)"
      ],
      "execution_count": 96,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "xgboost prediction progress: |████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "ExanWKmlvyje",
        "outputId": "4a6d2a72-aecc-4e13-970e-26470505d14a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 234
        }
      },
      "source": [
        "dft_model1"
      ],
      "execution_count": 97,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  C1</th><th style=\"text-align: right;\">  Brand</th><th style=\"text-align: right;\">  Locality</th><th style=\"text-align: right;\">  City</th><th style=\"text-align: right;\">  State</th><th style=\"text-align: right;\">  Storage</th><th>Model1  </th><th style=\"text-align: right;\">  RAM1</th><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">   0</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       570</td><td style=\"text-align: right;\">    11</td><td style=\"text-align: right;\">      4</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  11534.4</td></tr>\n",
              "<tr><td style=\"text-align: right;\">   1</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       762</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">      256</td><td>F       </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  39705.8</td></tr>\n",
              "<tr><td style=\"text-align: right;\">   2</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">        60</td><td style=\"text-align: right;\">    13</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  13628  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">   3</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       640</td><td style=\"text-align: right;\">    15</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">       64</td><td>X       </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  44090  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">   4</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       816</td><td style=\"text-align: right;\">     2</td><td style=\"text-align: right;\">      6</td><td style=\"text-align: right;\">       32</td><td>F       </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  10188  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">   5</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       552</td><td style=\"text-align: right;\">    13</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">      128</td><td>E       </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  24188  </td></tr>\n",
              "<tr><td style=\"text-align: right;\">   6</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       389</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       64</td><td>Si      </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  19660.7</td></tr>\n",
              "<tr><td style=\"text-align: right;\">   7</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       926</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       32</td><td>F       </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  11978.9</td></tr>\n",
              "<tr><td style=\"text-align: right;\">   8</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       850</td><td style=\"text-align: right;\">     2</td><td style=\"text-align: right;\">      6</td><td style=\"text-align: right;\">       32</td><td>Si      </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  13864.3</td></tr>\n",
              "<tr><td style=\"text-align: right;\">   9</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       404</td><td style=\"text-align: right;\">     2</td><td style=\"text-align: right;\">      6</td><td style=\"text-align: right;\">       32</td><td>F       </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  10188  </td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 97
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "zZ9WrYRovyjh"
      },
      "source": [
        "#### Output"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "outputId": "c0ee5125-e996-457a-91dc-6cd615fa0ade",
        "id": "CEj5akSQvyjj",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "h2o.download_csv(dft_model1, \"dft_model1.csv\")"
      ],
      "execution_count": 98,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'/content/dft_model1.csv'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 98
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "KaD95yZ7vyjl",
        "outputId": "1d8c7820-f6b6-4e19-adf7-ac36894cae08",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        }
      },
      "source": [
        "h2o_out = pd.read_csv('/content/dft_model1.csv')\n",
        "h2o_out.set_index('C1', inplace=True)\n",
        "h2o_out.head()"
      ],
      "execution_count": 99,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>predict</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>C1</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>570</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32</td>\n",
              "      <td>Others</td>\n",
              "      <td>3</td>\n",
              "      <td>11534.413086</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>762</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>256</td>\n",
              "      <td>F</td>\n",
              "      <td>3</td>\n",
              "      <td>39705.828125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>60</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>32</td>\n",
              "      <td>Others</td>\n",
              "      <td>3</td>\n",
              "      <td>13628.038086</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64</td>\n",
              "      <td>X</td>\n",
              "      <td>3</td>\n",
              "      <td>44089.964844</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>816</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32</td>\n",
              "      <td>F</td>\n",
              "      <td>4</td>\n",
              "      <td>10187.971680</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    Brand  Locality  City  State  Storage  Model1  RAM1       predict\n",
              "C1                                                                   \n",
              "0       1       570    11      4       32  Others     3  11534.413086\n",
              "1       1       762     8      2      256       F     3  39705.828125\n",
              "2       1        60    13      5       32  Others     3  13628.038086\n",
              "3       1       640    15      5       64       X     3  44089.964844\n",
              "4       1       816     2      6       32       F     4  10187.971680"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 99
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "H-LwzEmnvyjn",
        "outputId": "88211cf6-b2a3-4869-9e8a-65012e4b778e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model1_test.head()"
      ],
      "execution_count": 100,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>570</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>762</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>256.0</td>\n",
              "      <td>F</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>60</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>X</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>816</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>F</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Brand  Locality  City  State  Storage  Model1  RAM1\n",
              "0      1       570    11      4     32.0  Others   3.0\n",
              "1      1       762     8      2    256.0       F   3.0\n",
              "2      1        60    13      5     32.0  Others   3.0\n",
              "3      1       640    15      5     64.0       X   3.0\n",
              "4      1       816     2      6     32.0       F   4.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 100
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "JynymF67vyjo",
        "colab": {}
      },
      "source": [
        "model1_test['Price'] = h2o_out['predict']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "Zw0bgSCtvyjq",
        "outputId": "8176bb3c-0ada-46af-84c7-6e8b1e46532e",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model1_test.head()"
      ],
      "execution_count": 102,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>570</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "      <td>11534.413086</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>762</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>256.0</td>\n",
              "      <td>F</td>\n",
              "      <td>3.0</td>\n",
              "      <td>39705.828125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>60</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "      <td>13628.038086</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>X</td>\n",
              "      <td>3.0</td>\n",
              "      <td>44089.964844</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>816</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>F</td>\n",
              "      <td>4.0</td>\n",
              "      <td>10187.971680</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Brand  Locality  City  State  Storage  Model1  RAM1         Price\n",
              "0      1       570    11      4     32.0  Others   3.0  11534.413086\n",
              "1      1       762     8      2    256.0       F   3.0  39705.828125\n",
              "2      1        60    13      5     32.0  Others   3.0  13628.038086\n",
              "3      1       640    15      5     64.0       X   3.0  44089.964844\n",
              "4      1       816     2      6     32.0       F   4.0  10187.971680"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 102
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PHo742lrQ0uV",
        "colab_type": "text"
      },
      "source": [
        "##Model2"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "SDkINeHk-SG9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "indices= model2['Price'] <= 2200\n",
        "model2.loc[indices,'Storage'] = model2.loc[indices,'Storage'].fillna(16)\n",
        "indices= model2['Price'] <= 5200\n",
        "model2.loc[indices,'Storage'] = model2.loc[indices,'Storage'].fillna(32)\n",
        "model2['Storage'].fillna(64 , inplace=True)\n",
        "\n",
        "\n",
        "M = ['k5','k 5','k3','k 3','k4','k 4','k8 plus','k8plus','zuk','vibe p1m', 'vibep1m','k6','k 6','k8','k 8','vibe','note','phab','lenovo','iphone']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model2['Model1'] = model2['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2['Model2'] = model2['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2.Model1.fillna(model2.Model2, inplace=True)\n",
        "del model2['Model2']\n",
        "model2[\"Model1\"].replace({\"k5\": \"K5\", \"k 5\": \"K5\",\n",
        "                          \"k4\": \"K4\", \"k 4\": \"K4\",\n",
        "                          \"k3\": \"K3\", \"k 3\": \"K3\",\n",
        "                          \"k2\": \"K2\", \"k 2\": \"K2\",\n",
        "                          \"k8\": \"K8\", \"k 8\": \"K8\",\n",
        "                          \"k6\": \"K6\", \"k 6\": \"K6\",\n",
        "                          \"k8 plus\": \"K8\", \"k8plus\": \"K8\",\n",
        "                          \"vibe p1m\": \"VIBE\", \"vibep1m\": \"VIBE\",\"vibe\":\"VIBE\",\n",
        "                          \"iphone\": \"APPLE\",\n",
        "                          \"zuk\": \"LENOVO\",\"note\": \"LENOVO\",\"phab\": \"LENOVO\",\"lenovo\": \"LENOVO\"}, inplace=True)\n",
        "model2['Model1'].fillna(\"Others\" , inplace=True)\n",
        "\n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model2['RAM1'] = model2['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2['RAM2'] = model2['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2.RAM1.fillna(model2.RAM2, inplace=True)\n",
        "del model2['RAM2']\n",
        "model2[\"RAM1\"].replace({\"2gb\": 2,\"2 gb\": 2, \"3gb\": 3, \"3 gb\": 3, \"4gb\": 4, \"4 gb\": 4,\"6 gb\": 6, \"6gb\": 6}, inplace=True)  \n",
        "\n",
        "indices= model2['Storage'] == 16\n",
        "model2.loc[indices,'RAM1'] = model2.loc[indices,'RAM1'].fillna(2)\n",
        "indices= model2['Storage'] == 32\n",
        "model2.loc[indices,'RAM1'] = model2.loc[indices,'RAM1'].fillna(3)\n",
        "indices= model2['Storage'] == 64\n",
        "model2.loc[indices,'RAM1'] = model2.loc[indices,'RAM1'].fillna(4)\n",
        "model2['RAM1'].fillna(6 , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WJEGMSprkQZb",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "M = ['k5','k 5','k3','k 3','k4','k 4','k8 plus','k8plus','zuk','vibe p1m', 'vibep1m','k6','k 6','k8','k 8','vibe','note','phab','lenovo','iphone']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model2_test['Model1'] = model2_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2_test['Model2'] = model2_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2_test.Model1.fillna(model2_test.Model2, inplace=True)\n",
        "del model2_test['Model2']\n",
        "model2_test[\"Model1\"].replace({\"k5\": \"K5\", \"k 5\": \"K5\", \"k4\": \"K4\", \"k 4\": \"K4\",\"k3\": \"K3\", \"k 3\": \"K3\", \"k2\": \"K2\", \"k 2\": \"K2\", \"k8\": \"K8\", \"k 8\": \"K8\", \"k6\": \"K6\", \"k 6\": \"K6\", \"k8 plus\": \"K8\", \"k8plus\": \"K8\", \"vibe p1m\": \"VIBE\", \"vibep1m\": \"VIBE\",\"vibe\":\"VIBE\", \"iphone\": \"APPLE\", \"zuk\": \"LENOVO\",\"note\": \"LENOVO\",\"phab\": \"LENOVO\",\"lenovo\": \"LENOVO\"}, inplace=True)\n",
        "model2_test['Model1'].fillna(\"Others\" , inplace=True)\n",
        "\n",
        "model2_test['Storage'].fillna(32,inplace=True)   \n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model2_test['RAM1'] = model2_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2_test['RAM2'] = model2_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model2_test.RAM1.fillna(model2_test.RAM2, inplace=True)\n",
        "del model2_test['RAM2']\n",
        "model2_test[\"RAM1\"].replace({\"2gb\": 2,\"2 gb\": 2,\"3gb\": 3, \"3 gb\": 3,\"4gb\": 4, \"4 gb\": 4,\"6 gb\": 6, \"6gb\": 6}, inplace=True)  \n",
        "\n",
        "indices= model2_test['Storage'] == 16\n",
        "model2_test.loc[indices,'RAM1'] = model2_test.loc[indices,'RAM1'].fillna(2)\n",
        "indices= model2_test['Storage'] == 32\n",
        "model2_test.loc[indices,'RAM1'] = model2_test.loc[indices,'RAM1'].fillna(3)\n",
        "indices= model2_test['Storage'] == 64\n",
        "model2_test.loc[indices,'RAM1'] = model2_test.loc[indices,'RAM1'].fillna(4)\n",
        "model2_test['RAM1'].fillna(6 , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YUUyqCA5CHOZ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "del model2['Additional_Description']\n",
        "del model2['Model_Info']\n",
        "del model2_test['Additional_Description']\n",
        "del model2_test['Model_Info']\n",
        "\n",
        "model2.to_csv(\"model2.csv\",index=True)\n",
        "model2_test.to_csv(\"model2_test.csv\",index=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "OgEWHWknCHLi",
        "colab_type": "code",
        "outputId": "7f013fc9-ab74-44cd-a835-a6f8258c5e8f",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 84
        }
      },
      "source": [
        "from h2o.automl import H2OAutoML\n",
        "df_model2 = h2o.import_file('/content/model2.csv')\n",
        "dft_model2 = h2o.import_file('/content/model2_test.csv')\n",
        "\n",
        "df_model2['Brand'] = df_model2['Brand'].asfactor()\n",
        "df_model2['Locality'] = df_model2['Locality'].asfactor()\n",
        "df_model2['State'] = df_model2['State'].asfactor()\n",
        "dft_model2['Brand'] = dft_model2['Brand'].asfactor()\n",
        "dft_model2['Locality'] = dft_model2['Locality'].asfactor()\n",
        "dft_model2['State'] = dft_model2['State'].asfactor()\n",
        "ignored_col_list = \"C1\"\n",
        "\n",
        "print(df_model2.types)\n",
        "print(dft_model2.types)\n",
        "\n",
        "df_train, df_val, df_test = df_model2.split_frame(ratios=[.9,.05])\n",
        "y = \"Price\"\n",
        "x = df_model2.columns\n",
        "x.remove(y)\n",
        "x.remove(ignored_col_list)"
      ],
      "execution_count": 106,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Price': 'int', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "HNrDIjNug6Mx",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "d6bd243f-0e22-49f6-a2c9-90f89495319f"
      },
      "source": [
        "aml = H2OAutoML(nfolds=15,\n",
        "                balance_classes=False,\n",
        "                max_runtime_secs=240,\n",
        "                max_models=0,\n",
        "                exclude_algos=['DeepLearning','GLM'],\n",
        "                verbosity = \"info\",\n",
        "                sort_metric='RMSLE',\n",
        "                stopping_metric='RMSLE')\n",
        "aml.train(x=x,y=y,training_frame=df_train,validation_frame=df_val)\n",
        "aml.leaderboard"
      ],
      "execution_count": 108,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "AutoML progress: |\n",
            "20:42:26.519: Project: AutoML_20200607_204226518\n",
            "20:42:26.519: User specified a validation frame with cross-validation still enabled. Please note that the models will still be validated using cross-validation only, the validation frame will be used to provide purely informative validation metrics on the trained models.\n",
            "20:42:26.519: Setting stopping tolerance adaptively based on the training frame: 0.05\n",
            "20:42:26.519: Build control seed: -1 (random)\n",
            "20:42:26.519: training frame: Frame key: automl_training_py_4231_sid_ab9d    cols: 9    rows: 93  chunks: 1    size: 10854  checksum: -269037482319336\n",
            "20:42:26.519: validation frame: Frame key: py_4232_sid_ab9d    cols: 9    rows: 3  chunks: 1    size: 9938  checksum: -191534906202178\n",
            "20:42:26.519: leaderboard frame: NULL\n",
            "20:42:26.519: blending frame: NULL\n",
            "20:42:26.519: response column: Price\n",
            "20:42:26.519: fold column: null\n",
            "20:42:26.519: weights column: null\n",
            "20:42:26.519: Loading execution steps: [{XGBoost : defaults}, {GLM : defaults}, {DRF : [def_1]}, {GBM : defaults}, {DeepLearning : defaults}, {DRF : [XRT]}, {XGBoost : grids}, {GBM : grids}, {DeepLearning : grids}, {GBM : [lr_annealing]}, {XGBoost : [lr_search]}, {StackedEnsemble : defaults}]\n",
            "20:42:26.519: Disabling Algo: DeepLearning as requested by the user.\n",
            "20:42:26.519: Disabling Algo: GLM as requested by the user.\n",
            "20:42:26.519: AutoML job created: 2020.06.07 20:42:26.518\n",
            "20:42:26.524: AutoML build started: 2020.06.07 20:42:26.524\n",
            "20:42:26.524: AutoML: starting XGBoost_1_AutoML_20200607_204226 model training\n",
            "\n",
            "█\n",
            "20:42:28.534: New leader: XGBoost_1_AutoML_20200607_204226, rmsle: 0.3834582959134098\n",
            "20:42:28.534: AutoML: starting XGBoost_2_AutoML_20200607_204226 model training\n",
            "\n",
            "█\n",
            "20:42:29.536: New leader: XGBoost_2_AutoML_20200607_204226, rmsle: 0.3706736318853426\n",
            "20:42:29.536: AutoML: starting XGBoost_3_AutoML_20200607_204226 model training\n",
            "\n",
            "█\n",
            "20:42:31.560: AutoML: starting DRF_1_AutoML_20200607_204226 model training\n",
            "\n",
            "█\n",
            "20:42:32.561: New leader: DRF_1_AutoML_20200607_204226, rmsle: 0.36762595109164825\n",
            "20:42:32.562: AutoML: starting GBM_1_AutoML_20200607_204226 model training\n",
            "\n",
            "██\n",
            "20:42:33.563: New leader: GBM_1_AutoML_20200607_204226, rmsle: 0.36760637706048144\n",
            "20:42:33.564: AutoML: starting GBM_2_AutoML_20200607_204226 model training\n",
            "\n",
            "██\n",
            "20:42:34.577: New leader: GBM_2_AutoML_20200607_204226, rmsle: 0.3661885505658472\n",
            "20:42:34.578: AutoML: starting GBM_3_AutoML_20200607_204226 model training\n",
            "20:42:35.579: New leader: GBM_3_AutoML_20200607_204226, rmsle: 0.3653501585885366\n",
            "20:42:35.579: AutoML: starting GBM_4_AutoML_20200607_204226 model training\n",
            "\n",
            "██\n",
            "20:42:36.581: AutoML: starting GBM_5_AutoML_20200607_204226 model training\n",
            "20:42:36.582: Skipping training of model GBM_5_AutoML_20200607_204226 due to exception: water.exceptions.H2OModelBuilderIllegalArgumentException: Illegal argument(s) for GBM model: GBM_5_AutoML_20200607_204226.  Details: ERRR on field: _min_rows: The dataset size is too small to split for min_rows=100.0: must have at least 200.0 (weighted) rows, but have only 93.0.\n",
            "\n",
            "20:42:36.582: AutoML: starting XRT_1_AutoML_20200607_204226 model training\n",
            "\n",
            "██████\n",
            "20:42:38.600: AutoML: starting XGBoost_grid__1_AutoML_20200607_204226 hyperparameter search\n",
            "\n",
            "███████████\n",
            "20:42:48.477: New leader: XGBoost_grid__1_AutoML_20200607_204226_model_2, rmsle: 0.3603655496165008\n",
            "\n",
            "███\n",
            "20:43:06.511: AutoML: starting GBM_grid__1_AutoML_20200607_204226 hyperparameter search\n",
            "\n",
            "████████████\n",
            "20:43:10.672: AutoML: starting StackedEnsemble_BestOfFamily_AutoML_20200607_204226 model training\n",
            "\n",
            "████\n",
            "20:43:11.674: AutoML: starting StackedEnsemble_AllModels_AutoML_20200607_204226 model training\n",
            "\n",
            "██████████| 100%\n",
            "\n",
            "20:43:13.677: Actual modeling steps: [{XGBoost : [def_1 (10), def_2 (10), def_3 (10)]}, {DRF : [def_1 (10)]}, {GBM : [def_1 (10), def_2 (10), def_3 (10), def_4 (10)]}, {DRF : [XRT (10)]}, {XGBoost : [grid_1 (100)]}, {GBM : [grid_1 (60)]}, {StackedEnsemble : [best (10), all (10)]}]\n",
            "20:43:13.677: AutoML build stopped: 2020.06.07 20:43:13.677\n",
            "20:43:13.677: AutoML build done: built 28 models\n",
            "20:43:13.677: AutoML duration: 47.153 sec\n",
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th>model_id                                      </th><th style=\"text-align: right;\">   rmsle</th><th style=\"text-align: right;\">  mean_residual_deviance</th><th style=\"text-align: right;\">   rmse</th><th style=\"text-align: right;\">        mse</th><th style=\"text-align: right;\">    mae</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_204226_model_2</td><td style=\"text-align: right;\">0.360366</td><td style=\"text-align: right;\">             3.03448e+06</td><td style=\"text-align: right;\">1741.98</td><td style=\"text-align: right;\">3.03448e+06</td><td style=\"text-align: right;\">1156.31</td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_204226_model_7    </td><td style=\"text-align: right;\">0.364476</td><td style=\"text-align: right;\">             2.8512e+06 </td><td style=\"text-align: right;\">1688.55</td><td style=\"text-align: right;\">2.8512e+06 </td><td style=\"text-align: right;\">1122.36</td></tr>\n",
              "<tr><td>GBM_3_AutoML_20200607_204226                  </td><td style=\"text-align: right;\">0.36535 </td><td style=\"text-align: right;\">             2.89668e+06</td><td style=\"text-align: right;\">1701.96</td><td style=\"text-align: right;\">2.89668e+06</td><td style=\"text-align: right;\">1150.04</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_204226_model_1</td><td style=\"text-align: right;\">0.365653</td><td style=\"text-align: right;\">             2.98533e+06</td><td style=\"text-align: right;\">1727.81</td><td style=\"text-align: right;\">2.98533e+06</td><td style=\"text-align: right;\">1230.4 </td></tr>\n",
              "<tr><td>GBM_2_AutoML_20200607_204226                  </td><td style=\"text-align: right;\">0.366189</td><td style=\"text-align: right;\">             2.94936e+06</td><td style=\"text-align: right;\">1717.37</td><td style=\"text-align: right;\">2.94936e+06</td><td style=\"text-align: right;\">1136.08</td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_204226_model_5    </td><td style=\"text-align: right;\">0.366444</td><td style=\"text-align: right;\">             2.95828e+06</td><td style=\"text-align: right;\">1719.97</td><td style=\"text-align: right;\">2.95828e+06</td><td style=\"text-align: right;\">1125.91</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_204226_model_4</td><td style=\"text-align: right;\">0.366649</td><td style=\"text-align: right;\">             3.17347e+06</td><td style=\"text-align: right;\">1781.42</td><td style=\"text-align: right;\">3.17347e+06</td><td style=\"text-align: right;\">1147.72</td></tr>\n",
              "<tr><td>GBM_1_AutoML_20200607_204226                  </td><td style=\"text-align: right;\">0.367606</td><td style=\"text-align: right;\">             3.00888e+06</td><td style=\"text-align: right;\">1734.61</td><td style=\"text-align: right;\">3.00888e+06</td><td style=\"text-align: right;\">1130.01</td></tr>\n",
              "<tr><td>DRF_1_AutoML_20200607_204226                  </td><td style=\"text-align: right;\">0.367626</td><td style=\"text-align: right;\">             3.09942e+06</td><td style=\"text-align: right;\">1760.52</td><td style=\"text-align: right;\">3.09942e+06</td><td style=\"text-align: right;\">1178.64</td></tr>\n",
              "<tr><td>XGBoost_2_AutoML_20200607_204226              </td><td style=\"text-align: right;\">0.370674</td><td style=\"text-align: right;\">             2.9384e+06 </td><td style=\"text-align: right;\">1714.18</td><td style=\"text-align: right;\">2.9384e+06 </td><td style=\"text-align: right;\">1189.04</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 108
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "ufuiAdvlwWGz",
        "outputId": "e0295943-db07-4d49-e166-c7b6a68e6c4d",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "pred = aml.leader.predict(df_test)"
      ],
      "execution_count": 109,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "xgboost prediction progress: |████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "DJnSEsMEwWG3",
        "outputId": "59e1950e-f36c-41ca-8d2a-c227c1ce21d0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 214
        }
      },
      "source": [
        "pred.head()"
      ],
      "execution_count": 110,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  4836.49</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  3286.17</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  4059.24</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  5664.41</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  5511.48</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  4187.28</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  3594.17</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  4328.39</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  5177.05</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 110
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "WQTUkddfwWG4",
        "outputId": "0c4b12ec-2640-43f2-cf31-50e712f59fd9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 167
        }
      },
      "source": [
        "aml.leader.model_performance(df_test)"
      ],
      "execution_count": 111,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "ModelMetricsRegression: xgboost\n",
            "** Reported on test data. **\n",
            "\n",
            "MSE: 911740.8147728046\n",
            "RMSE: 954.8512003306089\n",
            "MAE: 739.7121310763889\n",
            "RMSLE: 0.22601690672171643\n",
            "Mean Residual Deviance: 911740.8147728046\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 111
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "GZfuvswCwWG6",
        "outputId": "30984cb1-1dd3-4e7b-f06f-64e80d41b785",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "dft_model2['predict'] = aml.leader.predict(dft_model2)"
      ],
      "execution_count": 112,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "xgboost prediction progress: |████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "KcD8cSE9wWG7",
        "outputId": "e4521dc5-08ff-4104-9706-053664be3870",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 234
        }
      },
      "source": [
        "dft_model2"
      ],
      "execution_count": 113,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  C1</th><th style=\"text-align: right;\">  Brand</th><th style=\"text-align: right;\">  Locality</th><th style=\"text-align: right;\">  City</th><th style=\"text-align: right;\">  State</th><th style=\"text-align: right;\">  Storage</th><th>Model1  </th><th style=\"text-align: right;\">  RAM1</th><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  18</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       193</td><td style=\"text-align: right;\">    15</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">       64</td><td>Others  </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  7199.33</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  42</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">      1129</td><td style=\"text-align: right;\">    11</td><td style=\"text-align: right;\">      4</td><td style=\"text-align: right;\">       32</td><td>VIBE    </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4320.36</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  73</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       769</td><td style=\"text-align: right;\">     2</td><td style=\"text-align: right;\">      6</td><td style=\"text-align: right;\">       32</td><td>K3      </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4736.18</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 108</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">      1061</td><td style=\"text-align: right;\">    11</td><td style=\"text-align: right;\">      4</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4247.55</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 124</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       909</td><td style=\"text-align: right;\">    10</td><td style=\"text-align: right;\">      7</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  4952.39</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 166</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">        92</td><td style=\"text-align: right;\">    11</td><td style=\"text-align: right;\">      4</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\">  5075.47</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 179</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       427</td><td style=\"text-align: right;\">     0</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4238.26</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 181</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       275</td><td style=\"text-align: right;\">     0</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4238.26</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 186</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       634</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4116.4 </td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 187</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       451</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       32</td><td>VIBE    </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4013.04</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 113
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "d1y2YhacwWG9"
      },
      "source": [
        "#### Output"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "outputId": "6d1b5a0d-1de5-438a-8173-bba4cf99c424",
        "id": "UpYcV_vxwWG_",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "h2o.download_csv(dft_model2, \"dft_model2.csv\")"
      ],
      "execution_count": 114,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'/content/dft_model2.csv'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 114
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "aIVn80vPwWHC",
        "outputId": "b05b21af-2741-4ba9-8797-ee5a3f5a27c2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        }
      },
      "source": [
        "h2o_out = pd.read_csv('/content/dft_model2.csv')\n",
        "h2o_out.set_index('C1', inplace=True)\n",
        "h2o_out.head()"
      ],
      "execution_count": 115,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>predict</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>C1</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>2</td>\n",
              "      <td>193</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64</td>\n",
              "      <td>Others</td>\n",
              "      <td>4</td>\n",
              "      <td>7199.330078</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>2</td>\n",
              "      <td>1129</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32</td>\n",
              "      <td>VIBE</td>\n",
              "      <td>3</td>\n",
              "      <td>4320.359375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>73</th>\n",
              "      <td>2</td>\n",
              "      <td>769</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32</td>\n",
              "      <td>K3</td>\n",
              "      <td>3</td>\n",
              "      <td>4736.182617</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>108</th>\n",
              "      <td>2</td>\n",
              "      <td>1061</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32</td>\n",
              "      <td>Others</td>\n",
              "      <td>3</td>\n",
              "      <td>4247.548340</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>124</th>\n",
              "      <td>2</td>\n",
              "      <td>909</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32</td>\n",
              "      <td>Others</td>\n",
              "      <td>4</td>\n",
              "      <td>4952.388672</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Brand  Locality  City  State  Storage  Model1  RAM1      predict\n",
              "C1                                                                   \n",
              "18       2       193    15      5       64  Others     4  7199.330078\n",
              "42       2      1129    11      4       32    VIBE     3  4320.359375\n",
              "73       2       769     2      6       32      K3     3  4736.182617\n",
              "108      2      1061    11      4       32  Others     3  4247.548340\n",
              "124      2       909    10      7       32  Others     4  4952.388672"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 115
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "fdi-3o_0wWHF",
        "outputId": "76411ede-86d4-4145-e213-7206025b83bb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model2_test.head()"
      ],
      "execution_count": 116,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>2</td>\n",
              "      <td>193</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>2</td>\n",
              "      <td>1129</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>VIBE</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>73</th>\n",
              "      <td>2</td>\n",
              "      <td>769</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>K3</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>108</th>\n",
              "      <td>2</td>\n",
              "      <td>1061</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>124</th>\n",
              "      <td>2</td>\n",
              "      <td>909</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>4.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Brand  Locality  City  State  Storage  Model1  RAM1\n",
              "18       2       193    15      5     64.0  Others   4.0\n",
              "42       2      1129    11      4     32.0    VIBE   3.0\n",
              "73       2       769     2      6     32.0      K3   3.0\n",
              "108      2      1061    11      4     32.0  Others   3.0\n",
              "124      2       909    10      7     32.0  Others   4.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 116
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "-IoIlfjnwWHG",
        "colab": {}
      },
      "source": [
        "model2_test['Price'] = h2o_out['predict']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "7QIcr-J6wWHI",
        "outputId": "b24eccc4-0d51-436e-cc56-96bdccce98e9",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model2_test.head()"
      ],
      "execution_count": 118,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>2</td>\n",
              "      <td>193</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>4.0</td>\n",
              "      <td>7199.330078</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>2</td>\n",
              "      <td>1129</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>VIBE</td>\n",
              "      <td>3.0</td>\n",
              "      <td>4320.359375</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>73</th>\n",
              "      <td>2</td>\n",
              "      <td>769</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>K3</td>\n",
              "      <td>3.0</td>\n",
              "      <td>4736.182617</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>108</th>\n",
              "      <td>2</td>\n",
              "      <td>1061</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "      <td>4247.548340</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>124</th>\n",
              "      <td>2</td>\n",
              "      <td>909</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>4.0</td>\n",
              "      <td>4952.388672</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Brand  Locality  City  State  Storage  Model1  RAM1        Price\n",
              "18       2       193    15      5     64.0  Others   4.0  7199.330078\n",
              "42       2      1129    11      4     32.0    VIBE   3.0  4320.359375\n",
              "73       2       769     2      6     32.0      K3   3.0  4736.182617\n",
              "108      2      1061    11      4     32.0  Others   3.0  4247.548340\n",
              "124      2       909    10      7     32.0  Others   4.0  4952.388672"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 118
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AipKXeGgQ2a7",
        "colab_type": "text"
      },
      "source": [
        "##Model3"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FDfVxsOy2Of8",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "indices= model3['Price'] <= 10000\n",
        "model3.loc[indices,'Storage'] = model3.loc[indices,'Storage'].fillna(32)\n",
        "indices=  model3['Price'] <= 20000\n",
        "model3.loc[indices,'Storage'] = model3.loc[indices,'Storage'].fillna(64)\n",
        "model3['Storage'].fillna(128 , inplace=True)\n",
        "\n",
        "\n",
        "M = ['lg','vivo','iphone','nexus']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model3['Model1'] = model3['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3['Model2'] = model3['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3.Model1.fillna(model3.Model2, inplace=True)\n",
        "del model3['Model2']\n",
        "model3[\"Model1\"].replace({\"lg\": \"LG\", \"vivo\": \"VIVO\", \"iphone\": \"APPLE\",\"nexus\": \"LG\" }, inplace=True)\n",
        "model3['Model1'].fillna(\"Others\" , inplace=True)\n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model3['RAM1'] = model3['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3['RAM2'] = model3['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3.RAM1.fillna(model3.RAM2, inplace=True)\n",
        "del model3['RAM2']\n",
        "model3[\"RAM1\"].replace({\"2gb\": 2,\"2 gb\": 2,\"3gb\": 3, \"3 gb\": 3,\"4gb\": 4, \"4 gb\": 4,\"6 gb\": 6, \"6gb\": 6}, inplace=True)  \n",
        "\n",
        "indices= model3['Storage'] == 16\n",
        "model3.loc[indices,'RAM1'] = model3.loc[indices,'RAM1'].fillna(2)\n",
        "indices= model3['Storage'] == 32\n",
        "model3.loc[indices,'RAM1'] = model3.loc[indices,'RAM1'].fillna(3)\n",
        "indices= model3['Storage'] == 64\n",
        "model3.loc[indices,'RAM1'] = model3.loc[indices,'RAM1'].fillna(4)\n",
        "model3['RAM1'].fillna(6 , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "7UDPCo4t32P9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "M = ['lg','vivo','iphone','nexus']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model3_test['Model1'] = model3_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3_test['Model2'] = model3_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3_test.Model1.fillna(model3_test.Model2, inplace=True)\n",
        "del model3_test['Model2']\n",
        "model3_test[\"Model1\"].replace({\"lg\": \"LG\", \"vivo\": \"VIVO\", \"iphone\": \"APPLE\",\"nexus\": \"LG\" }, inplace=True)\n",
        "model3_test['Model1'].fillna(\"Others\" , inplace=True)\n",
        "\n",
        "model3_test['Storage'].fillna(32,inplace=True)   \n",
        "\n",
        "M = ['2gb','2 gb','3gb','3 gb','4 gb','4gb','6gb','6 gb']\n",
        "pat = '|'.join(r\"\\b{}\\b\".format(x) for x in M)\n",
        "model3_test['RAM1'] = model3_test['Model_Info'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3_test['RAM2'] = model3_test['Additional_Description'].str.extract('('+ pat + ')', expand=False, flags=re.I)\n",
        "model3_test.RAM1.fillna(model3_test.RAM2, inplace=True)\n",
        "del model3_test['RAM2']\n",
        "model3_test[\"RAM1\"].replace({\"2gb\": 2,\"2 gb\": 2,\"3gb\": 3, \"3 gb\": 3,\"4gb\": 4, \"4 gb\": 4,\"6 gb\": 6, \"6gb\": 6}, inplace=True)  \n",
        "\n",
        "indices= model3_test['Storage'] == 16\n",
        "model3_test.loc[indices,'RAM1'] = model3_test.loc[indices,'RAM1'].fillna(2)\n",
        "indices= model3_test['Storage'] == 32\n",
        "model3_test.loc[indices,'RAM1'] = model3_test.loc[indices,'RAM1'].fillna(3)\n",
        "indices= model3_test['Storage'] == 64\n",
        "model3_test.loc[indices,'RAM1'] = model3_test.loc[indices,'RAM1'].fillna(4)\n",
        "model3_test['RAM1'].fillna(6 , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IB-XqfwgnHSG",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "del model3['Additional_Description']\n",
        "del model3['Model_Info']\n",
        "del model3_test['Additional_Description']\n",
        "del model3_test['Model_Info']\n",
        "\n",
        "model3.to_csv(\"model3.csv\",index=True)\n",
        "model3_test.to_csv(\"model3_test.csv\",index=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "CidzZQ1LstdL",
        "colab_type": "code",
        "outputId": "938920bd-b8a5-4269-ce9a-be4b549073af",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 84
        }
      },
      "source": [
        "from h2o.automl import H2OAutoML\n",
        "df_model3 = h2o.import_file('/content/model3.csv')\n",
        "dft_model3 = h2o.import_file('/content/model3_test.csv')\n",
        "\n",
        "df_model3['Brand'] = df_model3['Brand'].asfactor()\n",
        "df_model3['Locality'] = df_model3['Locality'].asfactor()\n",
        "df_model3['State'] = df_model3['State'].asfactor()\n",
        "dft_model3['Brand'] = dft_model3['Brand'].asfactor()\n",
        "dft_model3['Locality'] = dft_model3['Locality'].asfactor()\n",
        "dft_model3['State'] = dft_model3['State'].asfactor()\n",
        "ignored_col_list = \"C1\"\n",
        "\n",
        "print(df_model3.types)\n",
        "print(dft_model3.types)\n",
        "\n",
        "df_train, df_val, df_test = df_model3.split_frame(ratios=[.8,.15])\n",
        "y = \"Price\"\n",
        "x = df_model3.columns\n",
        "x.remove(y)\n",
        "x.remove(ignored_col_list)"
      ],
      "execution_count": 122,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "Parse progress: |█████████████████████████████████████████████████████████| 100%\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Price': 'int', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n",
            "{'C1': 'int', 'Brand': 'enum', 'Locality': 'enum', 'City': 'int', 'State': 'enum', 'Storage': 'int', 'Model1': 'enum', 'RAM1': 'int'}\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IvnWJRr39Dtl",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        },
        "outputId": "52aa75a6-0221-4554-83a5-844d51151893"
      },
      "source": [
        "aml = H2OAutoML(nfolds=15,\n",
        "                balance_classes=False,\n",
        "                max_runtime_secs=240,\n",
        "                max_models=0,\n",
        "                exclude_algos=['DeepLearning','GLM'],\n",
        "                verbosity = \"info\",\n",
        "                sort_metric='RMSLE',\n",
        "                stopping_metric='RMSLE')\n",
        "aml.train(x=x,y=y,training_frame=df_train,validation_frame=df_val)\n",
        "aml.leaderboard"
      ],
      "execution_count": 123,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "AutoML progress: |\n",
            "20:50:37.416: Project: AutoML_20200607_205037416\n",
            "20:50:37.417: User specified a validation frame with cross-validation still enabled. Please note that the models will still be validated using cross-validation only, the validation frame will be used to provide purely informative validation metrics on the trained models.\n",
            "20:50:37.417: Setting stopping tolerance adaptively based on the training frame: 0.05\n",
            "20:50:37.417: Build control seed: -1 (random)\n",
            "20:50:37.417: training frame: Frame key: automl_training_py_4367_sid_ab9d    cols: 9    rows: 37  chunks: 1    size: 6022  checksum: -1764757347637578\n",
            "20:50:37.417: validation frame: Frame key: py_4368_sid_ab9d    cols: 9    rows: 4  chunks: 1    size: 5688  checksum: -489787895203034\n",
            "20:50:37.417: leaderboard frame: NULL\n",
            "20:50:37.417: blending frame: NULL\n",
            "20:50:37.417: response column: Price\n",
            "20:50:37.417: fold column: null\n",
            "20:50:37.417: weights column: null\n",
            "20:50:37.418: Loading execution steps: [{XGBoost : defaults}, {GLM : defaults}, {DRF : [def_1]}, {GBM : defaults}, {DeepLearning : defaults}, {DRF : [XRT]}, {XGBoost : grids}, {GBM : grids}, {DeepLearning : grids}, {GBM : [lr_annealing]}, {XGBoost : [lr_search]}, {StackedEnsemble : defaults}]\n",
            "20:50:37.418: Disabling Algo: DeepLearning as requested by the user.\n",
            "20:50:37.418: Disabling Algo: GLM as requested by the user.\n",
            "20:50:37.418: AutoML job created: 2020.06.07 20:50:37.416\n",
            "20:50:37.419: AutoML build started: 2020.06.07 20:50:37.419\n",
            "20:50:37.420: AutoML: starting XGBoost_1_AutoML_20200607_205037 model training\n",
            "\n",
            "\n",
            "20:50:38.423: New leader: XGBoost_1_AutoML_20200607_205037, rmsle: 0.8235769421658524\n",
            "20:50:38.423: AutoML: starting XGBoost_2_AutoML_20200607_205037 model training\n",
            "\n",
            "█\n",
            "20:50:39.424: New leader: XGBoost_2_AutoML_20200607_205037, rmsle: 0.6828958106027093\n",
            "20:50:39.425: AutoML: starting XGBoost_3_AutoML_20200607_205037 model training\n",
            "\n",
            "███\n",
            "20:50:41.426: AutoML: starting DRF_1_AutoML_20200607_205037 model training\n",
            "\n",
            "██\n",
            "20:50:42.428: AutoML: starting GBM_1_AutoML_20200607_205037 model training\n",
            "20:50:43.430: AutoML: starting GBM_2_AutoML_20200607_205037 model training\n",
            "\n",
            "█\n",
            "20:50:44.433: AutoML: starting GBM_3_AutoML_20200607_205037 model training\n",
            "\n",
            "██\n",
            "20:50:45.434: New leader: GBM_3_AutoML_20200607_205037, rmsle: 0.6715544017622531\n",
            "20:50:45.435: AutoML: starting GBM_4_AutoML_20200607_205037 model training\n",
            "\n",
            "███\n",
            "20:50:46.436: New leader: GBM_4_AutoML_20200607_205037, rmsle: 0.6627909058592271\n",
            "20:50:46.436: AutoML: starting GBM_5_AutoML_20200607_205037 model training\n",
            "20:50:46.437: Skipping training of model GBM_5_AutoML_20200607_205037 due to exception: water.exceptions.H2OModelBuilderIllegalArgumentException: Illegal argument(s) for GBM model: GBM_5_AutoML_20200607_205037.  Details: ERRR on field: _min_rows: The dataset size is too small to split for min_rows=100.0: must have at least 200.0 (weighted) rows, but have only 37.0.\n",
            "\n",
            "20:50:46.437: AutoML: starting XRT_1_AutoML_20200607_205037 model training\n",
            "\n",
            "██\n",
            "20:50:47.440: AutoML: starting XGBoost_grid__1_AutoML_20200607_205037 hyperparameter search\n",
            "\n",
            "█████████████████████\n",
            "20:51:51.469: AutoML: starting GBM_grid__1_AutoML_20200607_205037 hyperparameter search\n",
            "\n",
            "█\n",
            "20:51:52.700: New leader: GBM_grid__1_AutoML_20200607_205037_model_3, rmsle: 0.6543448098922836\n",
            "\n",
            "███████████\n",
            "20:52:02.722: AutoML: starting StackedEnsemble_BestOfFamily_AutoML_20200607_205037 model training\n",
            "\n",
            "███\n",
            "20:52:03.742: StackedEnsemble_BestOfFamily_AutoML_20200607_205037 [StackedEnsemble best (built using top model from each algorithm type)] failed: java.lang.RuntimeException: water.exceptions.H2OIllegalArgumentException: Not enough data to create 15 random cross-validation splits. Either reduce nfolds, specify a larger dataset (or specify another random number seed, if applicable).\n",
            "20:52:03.743: AutoML: starting StackedEnsemble_AllModels_AutoML_20200607_205037 model training\n",
            "\n",
            "██████| 100%\n",
            "\n",
            "20:52:04.748: StackedEnsemble_AllModels_AutoML_20200607_205037 [StackedEnsemble all (built using all AutoML models)] failed: java.lang.RuntimeException: water.exceptions.H2OIllegalArgumentException: Not enough data to create 15 random cross-validation splits. Either reduce nfolds, specify a larger dataset (or specify another random number seed, if applicable).\n",
            "20:52:04.748: Actual modeling steps: [{XGBoost : [def_1 (10), def_2 (10), def_3 (10)]}, {DRF : [def_1 (10)]}, {GBM : [def_1 (10), def_2 (10), def_3 (10), def_4 (10)]}, {DRF : [XRT (10)]}, {XGBoost : [grid_1 (100)]}, {GBM : [grid_1 (60)]}, {StackedEnsemble : [best (10), all (10)]}]\n",
            "20:52:04.748: AutoML build stopped: 2020.06.07 20:52:04.748\n",
            "20:52:04.748: AutoML build done: built 62 models\n",
            "20:52:04.748: AutoML duration:  1 min 27.329 sec\n",
            "\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th>model_id                                       </th><th style=\"text-align: right;\">   rmsle</th><th style=\"text-align: right;\">  mean_residual_deviance</th><th style=\"text-align: right;\">   rmse</th><th style=\"text-align: right;\">        mse</th><th style=\"text-align: right;\">    mae</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_205037_model_3     </td><td style=\"text-align: right;\">0.654345</td><td style=\"text-align: right;\">             7.03902e+07</td><td style=\"text-align: right;\">8389.89</td><td style=\"text-align: right;\">7.03902e+07</td><td style=\"text-align: right;\">4829.66</td></tr>\n",
              "<tr><td>GBM_4_AutoML_20200607_205037                   </td><td style=\"text-align: right;\">0.662791</td><td style=\"text-align: right;\">             7.77587e+07</td><td style=\"text-align: right;\">8818.09</td><td style=\"text-align: right;\">7.77587e+07</td><td style=\"text-align: right;\">4948.06</td></tr>\n",
              "<tr><td>GBM_3_AutoML_20200607_205037                   </td><td style=\"text-align: right;\">0.671554</td><td style=\"text-align: right;\">             7.85603e+07</td><td style=\"text-align: right;\">8863.42</td><td style=\"text-align: right;\">7.85603e+07</td><td style=\"text-align: right;\">5043.89</td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_205037_model_11    </td><td style=\"text-align: right;\">0.676069</td><td style=\"text-align: right;\">             7.52108e+07</td><td style=\"text-align: right;\">8672.41</td><td style=\"text-align: right;\">7.52108e+07</td><td style=\"text-align: right;\">5156.28</td></tr>\n",
              "<tr><td>XGBoost_2_AutoML_20200607_205037               </td><td style=\"text-align: right;\">0.682896</td><td style=\"text-align: right;\">             7.66445e+07</td><td style=\"text-align: right;\">8754.68</td><td style=\"text-align: right;\">7.66445e+07</td><td style=\"text-align: right;\">5008.05</td></tr>\n",
              "<tr><td>DRF_1_AutoML_20200607_205037                   </td><td style=\"text-align: right;\">0.683349</td><td style=\"text-align: right;\">             9.1375e+07 </td><td style=\"text-align: right;\">9559.03</td><td style=\"text-align: right;\">9.1375e+07 </td><td style=\"text-align: right;\">5192.94</td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_205037_model_29    </td><td style=\"text-align: right;\">0.68955 </td><td style=\"text-align: right;\">             7.11588e+07</td><td style=\"text-align: right;\">8435.57</td><td style=\"text-align: right;\">7.11588e+07</td><td style=\"text-align: right;\">4873.36</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205037_model_4 </td><td style=\"text-align: right;\">0.690012</td><td style=\"text-align: right;\">             7.4531e+07 </td><td style=\"text-align: right;\">8633.13</td><td style=\"text-align: right;\">7.4531e+07 </td><td style=\"text-align: right;\">4965.39</td></tr>\n",
              "<tr><td>GBM_grid__1_AutoML_20200607_205037_model_6     </td><td style=\"text-align: right;\">0.691842</td><td style=\"text-align: right;\">             8.79348e+07</td><td style=\"text-align: right;\">9377.36</td><td style=\"text-align: right;\">8.79348e+07</td><td style=\"text-align: right;\">5222.54</td></tr>\n",
              "<tr><td>XGBoost_grid__1_AutoML_20200607_205037_model_22</td><td style=\"text-align: right;\">0.694648</td><td style=\"text-align: right;\">             7.58156e+07</td><td style=\"text-align: right;\">8707.22</td><td style=\"text-align: right;\">7.58156e+07</td><td style=\"text-align: right;\">4377.44</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 123
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "K_mJLEkCxKeG",
        "outputId": "ea69ffc0-8cd8-4e98-8718-dcc432606f48",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "pred = aml.leader.predict(df_test)"
      ],
      "execution_count": 124,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "gbm prediction progress: |████████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "b_eVsDR7xKeJ",
        "outputId": "fa668f27-762a-4397-86be-0367f2071605",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 116
        }
      },
      "source": [
        "pred.head()"
      ],
      "execution_count": 125,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  4357.06</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 24242.5 </td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 17134.2 </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  4357.06</td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 125
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "lHtrDdyDxKeK",
        "outputId": "fc315fa2-0300-4c80-8e65-d6a8d729b6e0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 167
        }
      },
      "source": [
        "aml.leader.model_performance(df_test)"
      ],
      "execution_count": 126,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "\n",
            "ModelMetricsRegression: gbm\n",
            "** Reported on test data. **\n",
            "\n",
            "MSE: 31605950.087146264\n",
            "RMSE: 5621.91694061254\n",
            "MAE: 4455.341451921979\n",
            "RMSLE: 0.658742341252289\n",
            "Mean Residual Deviance: 31605950.087146264\n"
          ],
          "name": "stdout"
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 126
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "CEr9BJcAxKeN",
        "outputId": "5c1811fe-ed2c-4608-cb9b-887bdbc71df0",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "dft_model3['predict'] = aml.leader.predict(dft_model3)"
      ],
      "execution_count": 127,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "gbm prediction progress: |████████████████████████████████████████████████| 100%\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "7_fKKsoSxKeP",
        "outputId": "fe4690eb-3001-4a88-e435-9e3359271f6a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 234
        }
      },
      "source": [
        "dft_model3"
      ],
      "execution_count": 128,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/html": [
              "<table>\n",
              "<thead>\n",
              "<tr><th style=\"text-align: right;\">  C1</th><th style=\"text-align: right;\">  Brand</th><th style=\"text-align: right;\">  Locality</th><th style=\"text-align: right;\">  City</th><th style=\"text-align: right;\">  State</th><th style=\"text-align: right;\">  Storage</th><th>Model1  </th><th style=\"text-align: right;\">  RAM1</th><th style=\"text-align: right;\">  predict</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "<tr><td style=\"text-align: right;\">  30</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       216</td><td style=\"text-align: right;\">    13</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">      128</td><td>LG      </td><td style=\"text-align: right;\">     6</td><td style=\"text-align: right;\"> 20501   </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  49</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       170</td><td style=\"text-align: right;\">    10</td><td style=\"text-align: right;\">      7</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  3772.05</td></tr>\n",
              "<tr><td style=\"text-align: right;\">  59</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       529</td><td style=\"text-align: right;\">    11</td><td style=\"text-align: right;\">      4</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  6437.8 </td></tr>\n",
              "<tr><td style=\"text-align: right;\">  60</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       346</td><td style=\"text-align: right;\">     8</td><td style=\"text-align: right;\">      2</td><td style=\"text-align: right;\">       32</td><td>LG      </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  4622.17</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 140</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       640</td><td style=\"text-align: right;\">    15</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">      256</td><td>LG      </td><td style=\"text-align: right;\">     6</td><td style=\"text-align: right;\"> 24242.5 </td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 153</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       681</td><td style=\"text-align: right;\">    15</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">       32</td><td>LG      </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  6721.68</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 162</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       551</td><td style=\"text-align: right;\">    13</td><td style=\"text-align: right;\">      5</td><td style=\"text-align: right;\">      128</td><td>LG      </td><td style=\"text-align: right;\">     6</td><td style=\"text-align: right;\"> 16403.9 </td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 194</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       547</td><td style=\"text-align: right;\">    10</td><td style=\"text-align: right;\">      7</td><td style=\"text-align: right;\">       64</td><td>Others  </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\"> 13454.3 </td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 373</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       839</td><td style=\"text-align: right;\">     1</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">       32</td><td>Others  </td><td style=\"text-align: right;\">     3</td><td style=\"text-align: right;\">  3533.22</td></tr>\n",
              "<tr><td style=\"text-align: right;\"> 418</td><td style=\"text-align: right;\">      3</td><td style=\"text-align: right;\">        50</td><td style=\"text-align: right;\">     0</td><td style=\"text-align: right;\">      1</td><td style=\"text-align: right;\">       64</td><td>LG      </td><td style=\"text-align: right;\">     4</td><td style=\"text-align: right;\"> 13366.2 </td></tr>\n",
              "</tbody>\n",
              "</table>"
            ]
          },
          "metadata": {
            "tags": []
          }
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              ""
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 128
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "colab_type": "text",
        "id": "FFBdUMoixKeR"
      },
      "source": [
        "#### Output"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "outputId": "ec37687a-fecd-4d1d-a492-1e47358e22f4",
        "id": "jS_7GGcjxKeR",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 33
        }
      },
      "source": [
        "h2o.download_csv(dft_model3, \"dft_model3.csv\")"
      ],
      "execution_count": 129,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'/content/dft_model3.csv'"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 129
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "nju9SiG5xKeT",
        "outputId": "3b433732-dca4-4517-9f51-aaa10c911a36",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 226
        }
      },
      "source": [
        "h2o_out = pd.read_csv('/content/dft_model3.csv')\n",
        "h2o_out.set_index('C1', inplace=True)\n",
        "h2o_out.head()"
      ],
      "execution_count": 130,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>predict</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>C1</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>3</td>\n",
              "      <td>216</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>128</td>\n",
              "      <td>LG</td>\n",
              "      <td>6</td>\n",
              "      <td>20500.953858</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>3</td>\n",
              "      <td>170</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32</td>\n",
              "      <td>Others</td>\n",
              "      <td>3</td>\n",
              "      <td>3772.053653</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>59</th>\n",
              "      <td>3</td>\n",
              "      <td>529</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32</td>\n",
              "      <td>Others</td>\n",
              "      <td>3</td>\n",
              "      <td>6437.796977</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>60</th>\n",
              "      <td>3</td>\n",
              "      <td>346</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32</td>\n",
              "      <td>LG</td>\n",
              "      <td>3</td>\n",
              "      <td>4622.170664</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>140</th>\n",
              "      <td>3</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>256</td>\n",
              "      <td>LG</td>\n",
              "      <td>6</td>\n",
              "      <td>24242.477750</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Brand  Locality  City  State  Storage  Model1  RAM1       predict\n",
              "C1                                                                    \n",
              "30       3       216    13      5      128      LG     6  20500.953858\n",
              "49       3       170    10      7       32  Others     3   3772.053653\n",
              "59       3       529    11      4       32  Others     3   6437.796977\n",
              "60       3       346     8      2       32      LG     3   4622.170664\n",
              "140      3       640    15      5      256      LG     6  24242.477750"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 130
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "pV49jXR2xKeV",
        "outputId": "ab9bd7ea-2d8f-4bf5-88f5-7e1e894befb7",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model3_test.head()"
      ],
      "execution_count": 131,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>3</td>\n",
              "      <td>216</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>128.0</td>\n",
              "      <td>LG</td>\n",
              "      <td>6.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>3</td>\n",
              "      <td>170</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>59</th>\n",
              "      <td>3</td>\n",
              "      <td>529</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>60</th>\n",
              "      <td>3</td>\n",
              "      <td>346</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32.0</td>\n",
              "      <td>LG</td>\n",
              "      <td>3.0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>140</th>\n",
              "      <td>3</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>256.0</td>\n",
              "      <td>LG</td>\n",
              "      <td>6.0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Brand  Locality  City  State  Storage  Model1  RAM1\n",
              "30       3       216    13      5    128.0      LG   6.0\n",
              "49       3       170    10      7     32.0  Others   3.0\n",
              "59       3       529    11      4     32.0  Others   3.0\n",
              "60       3       346     8      2     32.0      LG   3.0\n",
              "140      3       640    15      5    256.0      LG   6.0"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 131
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "9O266DDLxKeX",
        "colab": {}
      },
      "source": [
        "model3_test['Price'] = h2o_out['predict']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab_type": "code",
        "id": "IVNFmLZkxKeZ",
        "outputId": "11812d78-931e-41c4-ee21-16f5bd9007ba",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "model3_test.head()"
      ],
      "execution_count": 133,
      "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>Brand</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Model1</th>\n",
              "      <th>RAM1</th>\n",
              "      <th>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>3</td>\n",
              "      <td>216</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>128.0</td>\n",
              "      <td>LG</td>\n",
              "      <td>6.0</td>\n",
              "      <td>20500.953858</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>3</td>\n",
              "      <td>170</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "      <td>3772.053653</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>59</th>\n",
              "      <td>3</td>\n",
              "      <td>529</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>Others</td>\n",
              "      <td>3.0</td>\n",
              "      <td>6437.796977</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>60</th>\n",
              "      <td>3</td>\n",
              "      <td>346</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32.0</td>\n",
              "      <td>LG</td>\n",
              "      <td>3.0</td>\n",
              "      <td>4622.170664</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>140</th>\n",
              "      <td>3</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>256.0</td>\n",
              "      <td>LG</td>\n",
              "      <td>6.0</td>\n",
              "      <td>24242.477750</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     Brand  Locality  City  State  Storage  Model1  RAM1         Price\n",
              "30       3       216    13      5    128.0      LG   6.0  20500.953858\n",
              "49       3       170    10      7     32.0  Others   3.0   3772.053653\n",
              "59       3       529    11      4     32.0  Others   3.0   6437.796977\n",
              "60       3       346     8      2     32.0      LG   3.0   4622.170664\n",
              "140      3       640    15      5    256.0      LG   6.0  24242.477750"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 133
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "2CeLKt66xW_a",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "KENWbXl6ySlA",
        "colab_type": "text"
      },
      "source": [
        "## Test"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "xVTUsV5xzfUt",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "temp = dft"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PL1Jeuh0Cxx4",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "del temp['Price_m0']\n",
        "del temp['Price_m1']\n",
        "del temp['Price_m2']\n",
        "del temp['Price_m3']"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bho49tlxzqEK",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "temp['Price_m0'] = model0_test['Price']\n",
        "temp['Price_m1'] = model1_test['Price']\n",
        "temp['Price_m2'] = model2_test['Price']\n",
        "temp['Price_m3'] = model3_test['Price']\n",
        "temp['Price_m0'].fillna(temp['Price_m1'] , inplace=True)\n",
        "temp['Price_m0'].fillna(temp['Price_m2'] , inplace=True)\n",
        "temp['Price_m0'].fillna(temp['Price_m3'] , inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "P1v3VfTfzBv_",
        "colab_type": "code",
        "outputId": "b30fdc1f-b590-4331-d65b-e6ec8005e30a",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 1000
        }
      },
      "source": [
        "temp.head(60)"
      ],
      "execution_count": 152,
      "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>Brand</th>\n",
              "      <th>Model_Info</th>\n",
              "      <th>Additional_Description</th>\n",
              "      <th>Locality</th>\n",
              "      <th>City</th>\n",
              "      <th>State</th>\n",
              "      <th>Storage</th>\n",
              "      <th>Price_m0</th>\n",
              "      <th>Price_m1</th>\n",
              "      <th>Price_m2</th>\n",
              "      <th>Price_m3</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 55s66s66s778xxsxsmax etc</td>\n",
              "      <td>good condition 11months old single scratch we...</td>\n",
              "      <td>570</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11534.413086</td>\n",
              "      <td>11534.413086</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>1</td>\n",
              "      <td>slightly used excellent condition name0 5 sale</td>\n",
              "      <td>101008700 1010030600 1010034300 10100192200 1...</td>\n",
              "      <td>762</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>256.0</td>\n",
              "      <td>39705.828125</td>\n",
              "      <td>39705.828125</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 sx ios12 top letast model bill call</td>\n",
              "      <td>1010017300 delivery</td>\n",
              "      <td>60</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13628.038086</td>\n",
              "      <td>13628.038086</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 name0 x 64gb going lowest 41900</td>\n",
              "      <td>phone 1010023400 64 gb excellent condition sale</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>44089.964844</td>\n",
              "      <td>44089.964844</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 5s proper condition one handedly used</td>\n",
              "      <td>full kit available 10100248300 condition 4gb ...</td>\n",
              "      <td>816</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>10187.971680</td>\n",
              "      <td>10187.971680</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 7 plus 128 gb name75 gold</td>\n",
              "      <td>101006600 galaxy advance hai ok ram 512 call</td>\n",
              "      <td>552</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>128.0</td>\n",
              "      <td>24187.962891</td>\n",
              "      <td>24187.962891</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>1</td>\n",
              "      <td>brand new rosegold name87 iphone name234 64gb...</td>\n",
              "      <td>office gurgaon karol bagh new 101008700 10100...</td>\n",
              "      <td>389</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>64.0</td>\n",
              "      <td>19660.714844</td>\n",
              "      <td>19660.714844</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 se 32gb</td>\n",
              "      <td>101008700 iphone 4s new brand refurbished pac...</td>\n",
              "      <td>926</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32.0</td>\n",
              "      <td>11978.862305</td>\n",
              "      <td>11978.862305</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>8</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6s name753</td>\n",
              "      <td>101006200 bill good battery backup good looking</td>\n",
              "      <td>850</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13864.305664</td>\n",
              "      <td>13864.305664</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>9</th>\n",
              "      <td>1</td>\n",
              "      <td>apple phone 8 offer price</td>\n",
              "      <td>brand new 101006600 galaxy s10 plus box best ...</td>\n",
              "      <td>404</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10187.971680</td>\n",
              "      <td>10187.971680</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>10</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 aripod2 new seal pack</td>\n",
              "      <td>1010023500 1010015600 101005400 phone urgent ...</td>\n",
              "      <td>83</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13578.496094</td>\n",
              "      <td>13578.496094</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>11</th>\n",
              "      <td>1</td>\n",
              "      <td>name58 watch</td>\n",
              "      <td>vivo 1010065200 664gb bill date 2442019 15000...</td>\n",
              "      <td>174</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13715.628906</td>\n",
              "      <td>13715.628906</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>12</th>\n",
              "      <td>1</td>\n",
              "      <td>brand name54 blackberry name1768 4 9900 name5...</td>\n",
              "      <td>looks 1010035500 10100000 7 32gb 1010048300 1...</td>\n",
              "      <td>193</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>32.0</td>\n",
              "      <td>10519.235352</td>\n",
              "      <td>10519.235352</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>13</th>\n",
              "      <td>1</td>\n",
              "      <td>6 64gb iphone</td>\n",
              "      <td>101009900 available box 1 year 1010037500 war...</td>\n",
              "      <td>676</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>64.0</td>\n",
              "      <td>20772.375000</td>\n",
              "      <td>20772.375000</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>14</th>\n",
              "      <td>1</td>\n",
              "      <td>want sell iphone name414</td>\n",
              "      <td>iphone 7 128 gb storage 101008900 headphones ...</td>\n",
              "      <td>292</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>128.0</td>\n",
              "      <td>19564.294922</td>\n",
              "      <td>19564.294922</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>15</th>\n",
              "      <td>0</td>\n",
              "      <td>name233 9 name1482 464 gb name2659 storage</td>\n",
              "      <td>complete 101006200 accessories 1010011300 sli...</td>\n",
              "      <td>361</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>12523.043945</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>16</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 7 name92 name42 32 gb name14</td>\n",
              "      <td>101005000 105 brand new unused 1010035500 bat...</td>\n",
              "      <td>365</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32.0</td>\n",
              "      <td>17690.390625</td>\n",
              "      <td>17690.390625</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>17</th>\n",
              "      <td>0</td>\n",
              "      <td>name615 name233 7x 4gb64gb</td>\n",
              "      <td>101005100 phone</td>\n",
              "      <td>547</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9044.527344</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>18</th>\n",
              "      <td>2</td>\n",
              "      <td>name340 name983 4gb 64gb</td>\n",
              "      <td>101008400 max 32gb excellent condition 2 year...</td>\n",
              "      <td>193</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>7199.330078</td>\n",
              "      <td>NaN</td>\n",
              "      <td>7199.330078</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>19</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 name0 7 32gb 14500 name42 name156 avai...</td>\n",
              "      <td>brand 101005400 phone 7 accessories proper 10...</td>\n",
              "      <td>15</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32.0</td>\n",
              "      <td>17690.390625</td>\n",
              "      <td>17690.390625</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>20</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 iphone 8 name92 64gb 15 year name243</td>\n",
              "      <td>next 16th bday father purchasing new mobile g...</td>\n",
              "      <td>501</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>64.0</td>\n",
              "      <td>26920.478516</td>\n",
              "      <td>26920.478516</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>21</th>\n",
              "      <td>0</td>\n",
              "      <td>name233 play</td>\n",
              "      <td>10100000 6s 16gb silver charger available 101...</td>\n",
              "      <td>174</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16.0</td>\n",
              "      <td>5576.688965</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>22</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone 7 plus best price</td>\n",
              "      <td>10100000 7 32 gb black 10100244800 sale 1 mon...</td>\n",
              "      <td>100</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>19329.025391</td>\n",
              "      <td>19329.025391</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>23</th>\n",
              "      <td>1</td>\n",
              "      <td>10000 mha power name179 good condition new po...</td>\n",
              "      <td>vivo 1010078600 pro 8gb 128gb brand new seale...</td>\n",
              "      <td>1134</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>128.0</td>\n",
              "      <td>28229.435547</td>\n",
              "      <td>28229.435547</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>24</th>\n",
              "      <td>1</td>\n",
              "      <td>sell iphone 6s 16gb rosegold</td>\n",
              "      <td>101005400 condition 101006600</td>\n",
              "      <td>593</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>16.0</td>\n",
              "      <td>10860.627930</td>\n",
              "      <td>10860.627930</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 7 red colour</td>\n",
              "      <td>samsung 1010019500 1010025100 like 1010032600...</td>\n",
              "      <td>477</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>NaN</td>\n",
              "      <td>18332.195312</td>\n",
              "      <td>18332.195312</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>26</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 name0 6 32gb neat condition</td>\n",
              "      <td>good condition</td>\n",
              "      <td>1130</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>13864.305664</td>\n",
              "      <td>13864.305664</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>27</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone 7 32gb name113 offer name54 name120 co...</td>\n",
              "      <td>10100130000 peace need urgent money</td>\n",
              "      <td>659</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>32.0</td>\n",
              "      <td>18875.496094</td>\n",
              "      <td>18875.496094</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>28</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 name0 x 256gb accessories great</td>\n",
              "      <td>looks brand new 101004800 1010020400 10x 10 m...</td>\n",
              "      <td>831</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>256.0</td>\n",
              "      <td>55770.515625</td>\n",
              "      <td>55770.515625</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>29</th>\n",
              "      <td>1</td>\n",
              "      <td>apple latest model available best cost</td>\n",
              "      <td>kind samsung vivo oppo apple iphone model buy...</td>\n",
              "      <td>418</td>\n",
              "      <td>1</td>\n",
              "      <td>3</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9497.807617</td>\n",
              "      <td>9497.807617</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>30</th>\n",
              "      <td>3</td>\n",
              "      <td>lg v40 thinq plus 128gb version blue color</td>\n",
              "      <td>urgent requried money</td>\n",
              "      <td>216</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>128.0</td>\n",
              "      <td>20500.953858</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>20500.953858</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>31</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone name234 16gb awesome condition</td>\n",
              "      <td>brand 101005400 condition good 101005400 1010...</td>\n",
              "      <td>193</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>16.0</td>\n",
              "      <td>10090.616211</td>\n",
              "      <td>10090.616211</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>32</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 8 plus 64gb</td>\n",
              "      <td>brandufit make phone call directly smart watc...</td>\n",
              "      <td>697</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>64.0</td>\n",
              "      <td>26920.478516</td>\n",
              "      <td>26920.478516</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>33</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 6 available</td>\n",
              "      <td>1010032600 101004200 64gb original accessorie...</td>\n",
              "      <td>180</td>\n",
              "      <td>17</td>\n",
              "      <td>7</td>\n",
              "      <td>64.0</td>\n",
              "      <td>20661.728516</td>\n",
              "      <td>20661.728516</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>34</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone6s phone brand new bill box 15 days old</td>\n",
              "      <td>neat condition complete box want exchange one...</td>\n",
              "      <td>145</td>\n",
              "      <td>17</td>\n",
              "      <td>7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>14296.959961</td>\n",
              "      <td>14296.959961</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 xs max og condition 64 gb</td>\n",
              "      <td>10100000 7 32 gb sale good condition 10100610...</td>\n",
              "      <td>538</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>64.0</td>\n",
              "      <td>44089.964844</td>\n",
              "      <td>44089.964844</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>36</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6s 64 gp gold colour</td>\n",
              "      <td>101006600 101005100</td>\n",
              "      <td>217</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13864.305664</td>\n",
              "      <td>13864.305664</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>37</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6 name1442 name119 name183</td>\n",
              "      <td>original charger earphone</td>\n",
              "      <td>1008</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>NaN</td>\n",
              "      <td>11676.122070</td>\n",
              "      <td>11676.122070</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>38</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 name0 6s 16gb acc</td>\n",
              "      <td>10100000 7 brand new condition 128gb warranty...</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>16.0</td>\n",
              "      <td>10090.616211</td>\n",
              "      <td>10090.616211</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>39</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 7 plus 128gb going lowest 25900</td>\n",
              "      <td>want sell oneplus a3003 phone good stateno in...</td>\n",
              "      <td>640</td>\n",
              "      <td>15</td>\n",
              "      <td>5</td>\n",
              "      <td>128.0</td>\n",
              "      <td>24187.962891</td>\n",
              "      <td>24187.962891</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>40</th>\n",
              "      <td>0</td>\n",
              "      <td>name615 name1797 name49</td>\n",
              "      <td>colours variants available</td>\n",
              "      <td>128</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>NaN</td>\n",
              "      <td>10677.984375</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>41</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone x 64 new condistion</td>\n",
              "      <td>good condition 2month balance warranty</td>\n",
              "      <td>1161</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>37914.183594</td>\n",
              "      <td>37914.183594</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>42</th>\n",
              "      <td>2</td>\n",
              "      <td>name271 vibe k5 note 3gb 32gb 13mp 55in</td>\n",
              "      <td>accessories</td>\n",
              "      <td>1129</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>4320.359375</td>\n",
              "      <td>NaN</td>\n",
              "      <td>4320.359375</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>43</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 name683 name561 name103 colour</td>\n",
              "      <td>brand new condition 6 months old accesories 1...</td>\n",
              "      <td>778</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>NaN</td>\n",
              "      <td>13508.252930</td>\n",
              "      <td>13508.252930</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>44</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 name420 brand new 16gb warranty</td>\n",
              "      <td>samsung galaxy 1010031000 edge 4gb 32gb minor...</td>\n",
              "      <td>980</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>11779.061523</td>\n",
              "      <td>11779.061523</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>45</th>\n",
              "      <td>1</td>\n",
              "      <td>phone 5s 16gb</td>\n",
              "      <td>90 condition bill box</td>\n",
              "      <td>132</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>16.0</td>\n",
              "      <td>12130.122070</td>\n",
              "      <td>12130.122070</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>46</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 7 32gb gold colour</td>\n",
              "      <td>samsung 1010011600 512 gb black brand new six...</td>\n",
              "      <td>815</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>32.0</td>\n",
              "      <td>19329.025391</td>\n",
              "      <td>19329.025391</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>47</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone 5s 16 gb grey colour</td>\n",
              "      <td>oppo 1010020400 1010082900 1010023500 colour ...</td>\n",
              "      <td>261</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>16.0</td>\n",
              "      <td>11708.818359</td>\n",
              "      <td>11708.818359</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>48</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6 32gb</td>\n",
              "      <td>101005100 phone</td>\n",
              "      <td>114</td>\n",
              "      <td>4</td>\n",
              "      <td>0</td>\n",
              "      <td>32.0</td>\n",
              "      <td>13508.252930</td>\n",
              "      <td>13508.252930</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>49</th>\n",
              "      <td>3</td>\n",
              "      <td>name397 g6 black 4gb64gb internal good condit...</td>\n",
              "      <td>1010037500 xperia xa1 ultra black imported mo...</td>\n",
              "      <td>170</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3772.053653</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>3772.053653</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50</th>\n",
              "      <td>0</td>\n",
              "      <td>honor7a want money</td>\n",
              "      <td>looking exchange interested pls call 10100934...</td>\n",
              "      <td>157</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>NaN</td>\n",
              "      <td>9263.803711</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>51</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 x 64gb</td>\n",
              "      <td>box charger available clme 8143 one one two e...</td>\n",
              "      <td>330</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>64.0</td>\n",
              "      <td>49212.984375</td>\n",
              "      <td>49212.984375</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>52</th>\n",
              "      <td>1</td>\n",
              "      <td>name87 6 16gb</td>\n",
              "      <td>perfectly maintained phone even single scratc...</td>\n",
              "      <td>547</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16.0</td>\n",
              "      <td>11710.439453</td>\n",
              "      <td>11710.439453</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>53</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone name554 16gb</td>\n",
              "      <td>phone 6s 32 gb box bill charger 10100211800 p...</td>\n",
              "      <td>547</td>\n",
              "      <td>10</td>\n",
              "      <td>7</td>\n",
              "      <td>16.0</td>\n",
              "      <td>11710.439453</td>\n",
              "      <td>11710.439453</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>54</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 xs name229 64gb brand new condition</td>\n",
              "      <td>screen got crack mobile working fine</td>\n",
              "      <td>148</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>64.0</td>\n",
              "      <td>49212.984375</td>\n",
              "      <td>49212.984375</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>55</th>\n",
              "      <td>1</td>\n",
              "      <td>iphone 7 plus red</td>\n",
              "      <td>scratches dents excellent conditionas good ne...</td>\n",
              "      <td>749</td>\n",
              "      <td>2</td>\n",
              "      <td>6</td>\n",
              "      <td>NaN</td>\n",
              "      <td>19329.025391</td>\n",
              "      <td>19329.025391</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>56</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6 32gb space grey</td>\n",
              "      <td>excellent condition 101006600 s9 plus 1010016...</td>\n",
              "      <td>976</td>\n",
              "      <td>8</td>\n",
              "      <td>2</td>\n",
              "      <td>32.0</td>\n",
              "      <td>13609.146484</td>\n",
              "      <td>13609.146484</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>57</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 6s plus 128gb</td>\n",
              "      <td>101003300 use good condition full box 1010061...</td>\n",
              "      <td>318</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>128.0</td>\n",
              "      <td>16246.870117</td>\n",
              "      <td>16246.870117</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>58</th>\n",
              "      <td>1</td>\n",
              "      <td>name0 7 plus 128gb like new accessories</td>\n",
              "      <td>awesome condition good working</td>\n",
              "      <td>577</td>\n",
              "      <td>13</td>\n",
              "      <td>5</td>\n",
              "      <td>128.0</td>\n",
              "      <td>24187.962891</td>\n",
              "      <td>24187.962891</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>59</th>\n",
              "      <td>3</td>\n",
              "      <td>name397 v20 handset spigen case</td>\n",
              "      <td>urgent sale 101005000 31 3gb ram 32gb interna...</td>\n",
              "      <td>529</td>\n",
              "      <td>11</td>\n",
              "      <td>4</td>\n",
              "      <td>32.0</td>\n",
              "      <td>6437.796977</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>6437.796977</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "    Brand  ...      Price_m3\n",
              "0       1  ...           NaN\n",
              "1       1  ...           NaN\n",
              "2       1  ...           NaN\n",
              "3       1  ...           NaN\n",
              "4       1  ...           NaN\n",
              "5       1  ...           NaN\n",
              "6       1  ...           NaN\n",
              "7       1  ...           NaN\n",
              "8       1  ...           NaN\n",
              "9       1  ...           NaN\n",
              "10      1  ...           NaN\n",
              "11      1  ...           NaN\n",
              "12      1  ...           NaN\n",
              "13      1  ...           NaN\n",
              "14      1  ...           NaN\n",
              "15      0  ...           NaN\n",
              "16      1  ...           NaN\n",
              "17      0  ...           NaN\n",
              "18      2  ...           NaN\n",
              "19      1  ...           NaN\n",
              "20      1  ...           NaN\n",
              "21      0  ...           NaN\n",
              "22      1  ...           NaN\n",
              "23      1  ...           NaN\n",
              "24      1  ...           NaN\n",
              "25      1  ...           NaN\n",
              "26      1  ...           NaN\n",
              "27      1  ...           NaN\n",
              "28      1  ...           NaN\n",
              "29      1  ...           NaN\n",
              "30      3  ...  20500.953858\n",
              "31      1  ...           NaN\n",
              "32      1  ...           NaN\n",
              "33      1  ...           NaN\n",
              "34      1  ...           NaN\n",
              "35      1  ...           NaN\n",
              "36      1  ...           NaN\n",
              "37      1  ...           NaN\n",
              "38      1  ...           NaN\n",
              "39      1  ...           NaN\n",
              "40      0  ...           NaN\n",
              "41      1  ...           NaN\n",
              "42      2  ...           NaN\n",
              "43      1  ...           NaN\n",
              "44      1  ...           NaN\n",
              "45      1  ...           NaN\n",
              "46      1  ...           NaN\n",
              "47      1  ...           NaN\n",
              "48      1  ...           NaN\n",
              "49      3  ...   3772.053653\n",
              "50      0  ...           NaN\n",
              "51      1  ...           NaN\n",
              "52      1  ...           NaN\n",
              "53      1  ...           NaN\n",
              "54      1  ...           NaN\n",
              "55      1  ...           NaN\n",
              "56      1  ...           NaN\n",
              "57      1  ...           NaN\n",
              "58      1  ...           NaN\n",
              "59      3  ...   6437.796977\n",
              "\n",
              "[60 rows x 11 columns]"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 152
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "x8cWQJE-y3Iq",
        "colab_type": "code",
        "outputId": "bfb12f48-236a-46bc-e0bc-644d3e101188",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "sub = pd.read_excel('/content/Used_Electronics_Participants_Data/Sample_Submission.xlsx')\n",
        "sub.head()"
      ],
      "execution_count": 153,
      "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>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>21773.398095</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>21277.682833</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>64172.642738</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>24174.489232</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>16453.462895</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          Price\n",
              "0  21773.398095\n",
              "1  21277.682833\n",
              "2  64172.642738\n",
              "3  24174.489232\n",
              "4  16453.462895"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 153
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "GCbHR0dI2TXh",
        "colab_type": "code",
        "outputId": "b1d3f76a-b5a1-4c5c-d9f7-a889ef557697",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 196
        }
      },
      "source": [
        "sub['Price'] = temp['Price_m0']\n",
        "sub.head()"
      ],
      "execution_count": 154,
      "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>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>11534.413086</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>39705.828125</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>13628.038086</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>44089.964844</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>10187.971680</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "          Price\n",
              "0  11534.413086\n",
              "1  39705.828125\n",
              "2  13628.038086\n",
              "3  44089.964844\n",
              "4  10187.971680"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 154
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AEscmeCOlhkP",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 286
        },
        "outputId": "a21289f6-ed6c-4bcf-8c9d-112d744c4ca4"
      },
      "source": [
        "sub.describe()"
      ],
      "execution_count": 155,
      "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>Price</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>count</th>\n",
              "      <td>997.000000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>mean</th>\n",
              "      <td>22210.044985</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>std</th>\n",
              "      <td>13584.303383</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>min</th>\n",
              "      <td>2579.346680</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>25%</th>\n",
              "      <td>13428.382812</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>50%</th>\n",
              "      <td>18875.496094</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>75%</th>\n",
              "      <td>26040.193359</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>max</th>\n",
              "      <td>65432.230469</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "              Price\n",
              "count    997.000000\n",
              "mean   22210.044985\n",
              "std    13584.303383\n",
              "min     2579.346680\n",
              "25%    13428.382812\n",
              "50%    18875.496094\n",
              "75%    26040.193359\n",
              "max    65432.230469"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 155
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "qAaCvZYY2eGH",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "sub.to_excel('out.xlsx')"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aoapB8aH2tLN",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}