Cars365.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "lCGpSXyQokze",
        "outputId": "cdb5fcbc-34c2-4974-b2b3-2f7630d1e892"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Collecting pyspark\n",
            "  Using cached pyspark-3.3.1.tar.gz (281.4 MB)\n",
            "Collecting py4j==0.10.9.5\n",
            "  Downloading py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)\n",
            "\u001b[K     |████████████████████████████████| 199 kB 4.0 MB/s \n",
            "\u001b[?25hBuilding wheels for collected packages: pyspark\n",
            "  Building wheel for pyspark (setup.py) ... \u001b[?25l\u001b[?25hdone\n",
            "  Created wheel for pyspark: filename=pyspark-3.3.1-py2.py3-none-any.whl size=281845512 sha256=a9cae458934169c1180bd22123540790a3fca7fa8a9ac3b91caf35f2a965c81c\n",
            "  Stored in directory: /root/.cache/pip/wheels/43/dc/11/ec201cd671da62fa9c5cc77078235e40722170ceba231d7598\n",
            "Successfully built pyspark\n",
            "Installing collected packages: py4j, pyspark\n",
            "Successfully installed py4j-0.10.9.5 pyspark-3.3.1\n",
            "Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
            "Requirement already satisfied: pyspark in /usr/local/lib/python3.8/dist-packages (3.3.1)\n",
            "Requirement already satisfied: py4j==0.10.9.5 in /usr/local/lib/python3.8/dist-packages (from pyspark) (0.10.9.5)\n"
          ]
        }
      ],
      "source": [
        "!pip install pyspark"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "yxQzR3AXoblS"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "import numpy as np\n",
        "import matplotlib.pyplot as plt\n",
        "\n",
        "\n",
        "# Load the packages needed for this part\n",
        "# create spark and sparkcontext objects\n",
        "from pyspark.sql import SparkSession\n",
        "import numpy as np\n",
        "\n",
        "spark = SparkSession.builder.getOrCreate()\n",
        "sc = spark.sparkContext\n",
        "\n",
        "import pyspark\n",
        "from pyspark.ml import feature, regression, Pipeline, classification, pipeline, evaluation\n",
        "from pyspark.ml.classification import LogisticRegression\n",
        "from pyspark.sql import functions as fn, Row\n",
        "from pyspark.sql.functions import when, regexp_extract, col\n",
        "from pyspark import sql\n",
        "from pyspark.sql.functions import *\n",
        "\n",
        "from pyspark.ml.classification import DecisionTreeClassifier\n",
        "from pyspark.ml.feature import VectorIndexer\n",
        "\n",
        "import matplotlib.pyplot as plt\n",
        "import sys\n",
        "\n",
        "import seaborn as sns\n",
        "\n",
        "pd.set_option('display.max_columns', 500)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "4BEs-4qGoblS",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 514
        },
        "outputId": "bd9b22e7-f3a7-4d9d-94c9-50cb532995ab"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "ERROR:root:KeyboardInterrupt while sending command.\n",
            "Traceback (most recent call last):\n",
            "  File \"/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\", line 1038, in send_command\n",
            "    response = connection.send_command(command)\n",
            "  File \"/usr/local/lib/python3.8/dist-packages/py4j/clientserver.py\", line 511, in send_command\n",
            "    answer = smart_decode(self.stream.readline()[:-1])\n",
            "  File \"/usr/lib/python3.8/socket.py\", line 669, in readinto\n",
            "    return self._sock.recv_into(b)\n",
            "KeyboardInterrupt\n"
          ]
        },
        {
          "output_type": "error",
          "ename": "KeyboardInterrupt",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-9-bea9e058920a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Get Data\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0;31m#Added Data types for memory optimization and faster loading\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mspark\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcsv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'/content/drive/MyDrive/used_cars_data.csv'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mheader\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minferSchema\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mlimit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m50000\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pyspark/sql/readwriter.py\u001b[0m in \u001b[0;36mcsv\u001b[0;34m(self, path, schema, sep, encoding, quote, escape, comment, header, inferSchema, ignoreLeadingWhiteSpace, ignoreTrailingWhiteSpace, nullValue, nanValue, positiveInf, negativeInf, dateFormat, timestampFormat, maxColumns, maxCharsPerColumn, maxMalformedLogPerPartition, mode, columnNameOfCorruptRecord, multiLine, charToEscapeQuoteEscaping, samplingRatio, enforceSchema, emptyValue, locale, lineSep, pathGlobFilter, recursiveFileLookup, modifiedBefore, modifiedAfter, unescapedQuoteHandling)\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    534\u001b[0m             \u001b[0;32massert\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_spark\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jvm\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 535\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_df\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jreader\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcsv\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_spark\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jvm\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mPythonUtils\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtoSeq\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    536\u001b[0m         \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mpath\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mRDD\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    537\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m   1318\u001b[0m             \u001b[0mproto\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mEND_COMMAND_PART\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1319\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1320\u001b[0;31m         \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1321\u001b[0m         return_value = get_return_value(\n\u001b[1;32m   1322\u001b[0m             answer, self.gateway_client, self.target_id, self.name)\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command, retry, binary)\u001b[0m\n\u001b[1;32m   1036\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1037\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1038\u001b[0;31m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1039\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mbinary\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1040\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_create_connection_guard\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/clientserver.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command)\u001b[0m\n\u001b[1;32m    509\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    510\u001b[0m             \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 511\u001b[0;31m                 \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msmart_decode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstream\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreadline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    512\u001b[0m                 \u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Answer received: {0}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0manswer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    513\u001b[0m                 \u001b[0;31m# Happens when a the other end is dead. There might be an empty\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.8/socket.py\u001b[0m in \u001b[0;36mreadinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m    667\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    668\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 669\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sock\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    670\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    671\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_timeout_occurred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
          ]
        }
      ],
      "source": [
        "# Get Data\n",
        "#Added Data types for memory optimization and faster loading\n",
        "data = spark.read.csv('/content/drive/MyDrive/used_cars_data.csv', header=True, inferSchema=True).limit(50000)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OSoevgouiaYS"
      },
      "outputs": [],
      "source": [
        "data.count()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RBbwsIUIg-G1"
      },
      "outputs": [],
      "source": [
        "from pyspark.sql.types import IntegerType,BooleanType,DateType\n",
        "import pyspark.sql.functions as F\n",
        "\n",
        "def fromBooleanToInt(s):\n",
        "    \"\"\"\n",
        "    This is just a simple python function to move boolean to integers.\n",
        "    \n",
        "    >>> fromBooleanToInt(None)\n",
        "    >>> fromBooleanToInt(True)\n",
        "    1\n",
        "    >>> fromBooleanToInt(False)\n",
        "    1\n",
        "    \"\"\"\n",
        "    if s == True:\n",
        "        return 1\n",
        "    elif s==False:\n",
        "        return 0\n",
        "    else:\n",
        "        return None \n",
        "\n",
        "fromBooleanToInt_udf = F.udf(lambda x: fromBooleanToInt(x), IntegerType()) \n",
        "\n",
        "data = data.withColumn('fleet',fromBooleanToInt_udf(data['fleet'])) \n",
        "data = data.withColumn('frame_damaged',fromBooleanToInt_udf(data['frame_damaged'])) \n",
        "data = data.withColumn('franchise_dealer',fromBooleanToInt_udf(data['franchise_dealer'])) \n",
        "data = data.withColumn('has_accidents',fromBooleanToInt_udf(data['has_accidents'])) \n",
        "data = data.withColumn('isCab',fromBooleanToInt_udf(data['isCab'])) \n",
        "data = data.withColumn('is_cpo',fromBooleanToInt_udf(data['is_cpo'])) \n",
        "data = data.withColumn('is_new',fromBooleanToInt_udf(data['is_new'])) \n",
        "data = data.withColumn('is_oemcpo',fromBooleanToInt_udf(data['is_oemcpo'])) \n",
        "data = data.withColumn('salvage',fromBooleanToInt_udf(data['salvage'])) \n",
        "data = data.withColumn('theft_title',fromBooleanToInt_udf(data['theft_title'])) "
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "from google.colab import drive\n",
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "T98C3NDYUqKQ",
        "outputId": "629a6b38-d9a7-4705-e9b2-75fe2723fc5b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/drive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 496
        },
        "id": "OOfvio8jhyat",
        "outputId": "39c6ba83-328d-42f7-fa66-15b96a78cb1a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "ERROR:root:KeyboardInterrupt while sending command.\n",
            "Traceback (most recent call last):\n",
            "  File \"/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\", line 1038, in send_command\n",
            "    response = connection.send_command(command)\n",
            "  File \"/usr/local/lib/python3.8/dist-packages/py4j/clientserver.py\", line 511, in send_command\n",
            "    answer = smart_decode(self.stream.readline()[:-1])\n",
            "  File \"/usr/lib/python3.8/socket.py\", line 669, in readinto\n",
            "    return self._sock.recv_into(b)\n",
            "KeyboardInterrupt\n"
          ]
        },
        {
          "output_type": "error",
          "ename": "KeyboardInterrupt",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-8-79a5f06bf2ba>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdropDuplicates\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Distinct count: \"\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pyspark/sql/dataframe.py\u001b[0m in \u001b[0;36mcount\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    802\u001b[0m         \u001b[0;36m2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    803\u001b[0m         \"\"\"\n\u001b[0;32m--> 804\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcount\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    805\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    806\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mcollect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m->\u001b[0m \u001b[0mList\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mRow\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m   1318\u001b[0m             \u001b[0mproto\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mEND_COMMAND_PART\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1319\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1320\u001b[0;31m         \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1321\u001b[0m         return_value = get_return_value(\n\u001b[1;32m   1322\u001b[0m             answer, self.gateway_client, self.target_id, self.name)\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command, retry, binary)\u001b[0m\n\u001b[1;32m   1036\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1037\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1038\u001b[0;31m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1039\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mbinary\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1040\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_create_connection_guard\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/clientserver.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command)\u001b[0m\n\u001b[1;32m    509\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    510\u001b[0m             \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 511\u001b[0;31m                 \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msmart_decode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstream\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreadline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    512\u001b[0m                 \u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Answer received: {0}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0manswer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    513\u001b[0m                 \u001b[0;31m# Happens when a the other end is dead. There might be an empty\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.8/socket.py\u001b[0m in \u001b[0;36mreadinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m    667\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    668\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 669\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sock\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    670\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    671\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_timeout_occurred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
          ]
        }
      ],
      "source": [
        "data = data.dropDuplicates()\n",
        "print(\"Distinct count: \"+str(data.count()))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "jxYwuZxdu-Ql"
      },
      "outputs": [],
      "source": [
        "data = data.drop(\"listed_date\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "h_B_-DsSum7a",
        "outputId": "ed5b5637-e9fa-427e-ac86-dea0356d1f3c"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+---+---+------------+------+----------+----------+---------+------+----+-----------------+--------------------+------------+----------+----------------+-------------------+-----------+--------------+------+-------------+----------------+--------------+-------------+----------------+---------+-------------+------+--------------------+----------+--------------+------+------------+------+------+---------+--------+------+-------------+----------+---------+----------------+-------------+---------+---------------+-------+----------+-----------+------+-----+-------+--------------+-------------+-----+-------+-----------+------+------------+--------------------+------+---------+-----------------------+------------+--------------------+---------+-----+----+\n",
            "|_c0|vin|back_legroom|   bed|bed_height|bed_length|body_type| cabin|city|city_fuel_economy|combine_fuel_economy|daysonmarket|dealer_zip|engine_cylinders|engine_displacement|engine_type|exterior_color| fleet|frame_damaged|franchise_dealer|franchise_make|front_legroom|fuel_tank_volume|fuel_type|has_accidents|height|highway_fuel_economy|horsepower|interior_color| isCab|is_certified|is_cpo|is_new|is_oemcpo|latitude|length|listing_color|listing_id|longitude|main_picture_url|major_options|make_name|maximum_seating|mileage|model_name|owner_count| power|price|salvage|savings_amount|seller_rating|sp_id|sp_name|theft_title|torque|transmission|transmission_display|trimId|trim_name|vehicle_damage_category|wheel_system|wheel_system_display|wheelbase|width|year|\n",
            "+---+---+------------+------+----------+----------+---------+------+----+-----------------+--------------------+------------+----------+----------------+-------------------+-----------+--------------+------+-------------+----------------+--------------+-------------+----------------+---------+-------------+------+--------------------+----------+--------------+------+------------+------+------+---------+--------+------+-------------+----------+---------+----------------+-------------+---------+---------------+-------+----------+-----------+------+-----+-------+--------------+-------------+-----+-------+-----------+------+------------+--------------------+------+---------+-----------------------+------------+--------------------+---------+-----+----+\n",
            "|0.0|0.0|       4.994|99.152|    93.136|    93.136|    0.458|98.648| 0.0|           14.886|               100.0|         0.0|       0.0|           2.872|              5.344|      2.872|           0.0|39.884|       39.884|             0.0|        31.212|        4.994|           4.994|     2.47|       39.884| 4.994|              14.886|     5.344|           0.0|39.884|       100.0| 92.04|   0.0|   94.274|     0.0| 4.994|          0.0|       0.0|      0.0|          16.768|        7.966|      0.0|          4.994|  3.658|       0.0|     42.624|14.448|  0.0| 39.884|           0.0|        0.758|  0.0|    0.0|     39.884|15.568|       0.852|               0.852| 3.882|    3.908|                  100.0|        4.68|                4.68|    4.994|4.994| 0.0|\n",
            "+---+---+------------+------+----------+----------+---------+------+----+-----------------+--------------------+------------+----------+----------------+-------------------+-----------+--------------+------+-------------+----------------+--------------+-------------+----------------+---------+-------------+------+--------------------+----------+--------------+------+------------+------+------+---------+--------+------+-------------+----------+---------+----------------+-------------+---------+---------------+-------+----------+-----------+------+-----+-------+--------------+-------------+-----+-------+-----------+------+------------+--------------------+------+---------+-----------------------+------------+--------------------+---------+-----+----+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "# df = data.select([count(when(isnan(c) | col(c).isNull(), c)).alias(c) for c in data.columns]).toPandas().head()\n",
        "amount_missing_df = data.select([(count(when(isnan(c) | col(c).isNull(), c))*100/count(lit(1))).alias(c) for c in data.columns])\n",
        "amount_missing_df.show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "DsafjukdQBgD"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true,
          "base_uri": "https://localhost:8080/",
          "height": 468
        },
        "id": "zPbF6uM0M3Hg",
        "outputId": "ed391881-3b62-4deb-e3da-ee29b47c3233"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "\n",
              "  <div id=\"df-aff1680e-1fa0-4000-8723-f8a621f57db8\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>summary</th>\n",
              "      <th>_c0</th>\n",
              "      <th>vin</th>\n",
              "      <th>back_legroom</th>\n",
              "      <th>bed</th>\n",
              "      <th>bed_height</th>\n",
              "      <th>bed_length</th>\n",
              "      <th>body_type</th>\n",
              "      <th>cabin</th>\n",
              "      <th>city</th>\n",
              "      <th>city_fuel_economy</th>\n",
              "      <th>combine_fuel_economy</th>\n",
              "      <th>daysonmarket</th>\n",
              "      <th>dealer_zip</th>\n",
              "      <th>engine_cylinders</th>\n",
              "      <th>engine_displacement</th>\n",
              "      <th>engine_type</th>\n",
              "      <th>exterior_color</th>\n",
              "      <th>fleet</th>\n",
              "      <th>frame_damaged</th>\n",
              "      <th>franchise_dealer</th>\n",
              "      <th>franchise_make</th>\n",
              "      <th>front_legroom</th>\n",
              "      <th>fuel_tank_volume</th>\n",
              "      <th>fuel_type</th>\n",
              "      <th>has_accidents</th>\n",
              "      <th>height</th>\n",
              "      <th>highway_fuel_economy</th>\n",
              "      <th>horsepower</th>\n",
              "      <th>interior_color</th>\n",
              "      <th>isCab</th>\n",
              "      <th>is_certified</th>\n",
              "      <th>is_cpo</th>\n",
              "      <th>is_new</th>\n",
              "      <th>is_oemcpo</th>\n",
              "      <th>latitude</th>\n",
              "      <th>length</th>\n",
              "      <th>listing_color</th>\n",
              "      <th>listing_id</th>\n",
              "      <th>longitude</th>\n",
              "      <th>main_picture_url</th>\n",
              "      <th>major_options</th>\n",
              "      <th>make_name</th>\n",
              "      <th>maximum_seating</th>\n",
              "      <th>mileage</th>\n",
              "      <th>model_name</th>\n",
              "      <th>owner_count</th>\n",
              "      <th>power</th>\n",
              "      <th>price</th>\n",
              "      <th>salvage</th>\n",
              "      <th>savings_amount</th>\n",
              "      <th>seller_rating</th>\n",
              "      <th>sp_id</th>\n",
              "      <th>sp_name</th>\n",
              "      <th>theft_title</th>\n",
              "      <th>torque</th>\n",
              "      <th>transmission</th>\n",
              "      <th>transmission_display</th>\n",
              "      <th>trimId</th>\n",
              "      <th>trim_name</th>\n",
              "      <th>vehicle_damage_category</th>\n",
              "      <th>wheel_system</th>\n",
              "      <th>wheel_system_display</th>\n",
              "      <th>wheelbase</th>\n",
              "      <th>width</th>\n",
              "      <th>year</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>count</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1426707</td>\n",
              "      <td>9284</td>\n",
              "      <td>201277</td>\n",
              "      <td>201277</td>\n",
              "      <td>1493314</td>\n",
              "      <td>28245</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1254750</td>\n",
              "      <td>0</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1455242</td>\n",
              "      <td>1420546</td>\n",
              "      <td>1455242</td>\n",
              "      <td>1499996</td>\n",
              "      <td>802507</td>\n",
              "      <td>802507</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1212327</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1461382</td>\n",
              "      <td>802507</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1254750</td>\n",
              "      <td>1420546</td>\n",
              "      <td>1499895</td>\n",
              "      <td>802507</td>\n",
              "      <td>0</td>\n",
              "      <td>100987</td>\n",
              "      <td>1500000</td>\n",
              "      <td>74704</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1313002</td>\n",
              "      <td>1403537</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1427012</td>\n",
              "      <td>1500000</td>\n",
              "      <td>755623</td>\n",
              "      <td>1269983</td>\n",
              "      <td>1500000</td>\n",
              "      <td>802507</td>\n",
              "      <td>1500000</td>\n",
              "      <td>1482762</td>\n",
              "      <td>1499911</td>\n",
              "      <td>1500000</td>\n",
              "      <td>802507</td>\n",
              "      <td>1252794</td>\n",
              "      <td>1472973</td>\n",
              "      <td>1472973</td>\n",
              "      <td>1445368</td>\n",
              "      <td>1444974</td>\n",
              "      <td>0</td>\n",
              "      <td>1430436</td>\n",
              "      <td>1430436</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1426707</td>\n",
              "      <td>1500000</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>mean</td>\n",
              "      <td>749999.5</td>\n",
              "      <td>Infinity</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>22.365110978282527</td>\n",
              "      <td>None</td>\n",
              "      <td>76.40229266666667</td>\n",
              "      <td>29977.329819954914</td>\n",
              "      <td>None</td>\n",
              "      <td>2927.714554826102</td>\n",
              "      <td>None</td>\n",
              "      <td>5132.078242229368</td>\n",
              "      <td>0.18111742327481256</td>\n",
              "      <td>0.009238548698017587</td>\n",
              "      <td>0.8096326666666667</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>0.1681081909565898</td>\n",
              "      <td>None</td>\n",
              "      <td>29.17556724447101</td>\n",
              "      <td>245.496782927128</td>\n",
              "      <td>6876.763636363636</td>\n",
              "      <td>0.15378308226594908</td>\n",
              "      <td>None</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0.48086</td>\n",
              "      <td>1.0</td>\n",
              "      <td>38.32248923139961</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2.75414584440984E8</td>\n",
              "      <td>-80.75007164259848</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>31551.69577200472</td>\n",
              "      <td>1549.3704494125934</td>\n",
              "      <td>1.5207080779700988</td>\n",
              "      <td>None</td>\n",
              "      <td>29474.935587153337</td>\n",
              "      <td>0.005259767204522827</td>\n",
              "      <td>549.2656406666666</td>\n",
              "      <td>4.272535810139143</td>\n",
              "      <td>233948.8255536495</td>\n",
              "      <td>None</td>\n",
              "      <td>0.002924585081500847</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>131.22539267015708</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2017.631246</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>stddev</td>\n",
              "      <td>433012.8462297622</td>\n",
              "      <td>NaN</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>7.759637445251214</td>\n",
              "      <td>None</td>\n",
              "      <td>109.32362453306646</td>\n",
              "      <td>17238.34746019152</td>\n",
              "      <td>None</td>\n",
              "      <td>1309.5414969040357</td>\n",
              "      <td>None</td>\n",
              "      <td>20381.158701200682</td>\n",
              "      <td>0.3851156801203441</td>\n",
              "      <td>0.0956724062713005</td>\n",
              "      <td>0.39259102700481857</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>0.3739625667814825</td>\n",
              "      <td>None</td>\n",
              "      <td>7.071351875652732</td>\n",
              "      <td>88.62671162677182</td>\n",
              "      <td>31404.24037926155</td>\n",
              "      <td>0.3607409153867586</td>\n",
              "      <td>None</td>\n",
              "      <td>0.0</td>\n",
              "      <td>0.4996336926414701</td>\n",
              "      <td>0.0</td>\n",
              "      <td>4.683470199082295</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>8924250.127632566</td>\n",
              "      <td>5.946522447192903</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>45268.289282703445</td>\n",
              "      <td>723.8195469859019</td>\n",
              "      <td>0.9043390844163363</td>\n",
              "      <td>None</td>\n",
              "      <td>18869.85359565794</td>\n",
              "      <td>0.07233331579004001</td>\n",
              "      <td>1068.0758383671564</td>\n",
              "      <td>0.5059160374603836</td>\n",
              "      <td>131033.00770750956</td>\n",
              "      <td>None</td>\n",
              "      <td>0.05400032886251098</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>566.4438268480721</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>4.267554017876733</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>min</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>--</td>\n",
              "      <td>Long</td>\n",
              "      <td>--</td>\n",
              "      <td>--</td>\n",
              "      <td>Convertible</td>\n",
              "      <td>Crew Cab</td>\n",
              "      <td>Abbeville</td>\n",
              "      <td>7.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>01060</td>\n",
              "      <td>H4</td>\n",
              "      <td>700.0</td>\n",
              "      <td>H4</td>\n",
              "      <td>\"\"</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>Acura</td>\n",
              "      <td>--</td>\n",
              "      <td>--</td>\n",
              "      <td>Biodiesel</td>\n",
              "      <td>0</td>\n",
              "      <td>--</td>\n",
              "      <td>10.0</td>\n",
              "      <td>65.0</td>\n",
              "      <td>'4wd Supercrew 145'' Xlt'</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>18.3467</td>\n",
              "      <td>--</td>\n",
              "      <td>BLACK</td>\n",
              "      <td>20498116</td>\n",
              "      <td>-157.928</td>\n",
              "      <td>https://058cfc5fa3837fde14de-43d0ef0c16a1c26e4...</td>\n",
              "      <td>['101A Mid Equipment Group', '301A Mid Equipme...</td>\n",
              "      <td>AM General</td>\n",
              "      <td>--</td>\n",
              "      <td>0.0</td>\n",
              "      <td>1 Series</td>\n",
              "      <td>1.0</td>\n",
              "      <td>1,001 hp @ 6,000 RPM</td>\n",
              "      <td>165.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>41593.0</td>\n",
              "      <td>\\t Dijie Auto Sales and Service Co.</td>\n",
              "      <td>0</td>\n",
              "      <td>100 lb-ft @ 3,000 RPM</td>\n",
              "      <td>A</td>\n",
              "      <td>1-Speed Automatic</td>\n",
              "      <td>t10000</td>\n",
              "      <td>!</td>\n",
              "      <td>None</td>\n",
              "      <td>4WD</td>\n",
              "      <td>4X2</td>\n",
              "      <td>--</td>\n",
              "      <td>--</td>\n",
              "      <td>1915</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>25%</td>\n",
              "      <td>374988</td>\n",
              "      <td>1.2870719E7</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>18.0</td>\n",
              "      <td>None</td>\n",
              "      <td>14</td>\n",
              "      <td>17042.0</td>\n",
              "      <td>None</td>\n",
              "      <td>2000.0</td>\n",
              "      <td>None</td>\n",
              "      <td>70.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>25.0</td>\n",
              "      <td>175.0</td>\n",
              "      <td>20.0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>35.1692</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>274576666</td>\n",
              "      <td>-84.2834</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>6.0</td>\n",
              "      <td>1500.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>None</td>\n",
              "      <td>18000.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>4.0</td>\n",
              "      <td>63915.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2.5</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2017</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>50%</td>\n",
              "      <td>749911</td>\n",
              "      <td>5.262659E8</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>21.0</td>\n",
              "      <td>None</td>\n",
              "      <td>35</td>\n",
              "      <td>29708.0</td>\n",
              "      <td>None</td>\n",
              "      <td>2500.0</td>\n",
              "      <td>None</td>\n",
              "      <td>218.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>28.0</td>\n",
              "      <td>241.0</td>\n",
              "      <td>57.0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>40.1209</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>278525093</td>\n",
              "      <td>-81.0239</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>10258.0</td>\n",
              "      <td>1500.0</td>\n",
              "      <td>1.0</td>\n",
              "      <td>None</td>\n",
              "      <td>26430.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>4.333333333333333</td>\n",
              "      <td>281863.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2.5</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2019</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>6</th>\n",
              "      <td>75%</td>\n",
              "      <td>1124951</td>\n",
              "      <td>9.116310298E9</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>26.0</td>\n",
              "      <td>None</td>\n",
              "      <td>82</td>\n",
              "      <td>44039.0</td>\n",
              "      <td>None</td>\n",
              "      <td>3600.0</td>\n",
              "      <td>None</td>\n",
              "      <td>300.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>33.0</td>\n",
              "      <td>300.0</td>\n",
              "      <td>101.0</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>41.7764</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>280411086</td>\n",
              "      <td>-76.2382</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>44593.0</td>\n",
              "      <td>1500.0</td>\n",
              "      <td>2.0</td>\n",
              "      <td>None</td>\n",
              "      <td>37694.0</td>\n",
              "      <td>0</td>\n",
              "      <td>789</td>\n",
              "      <td>4.6</td>\n",
              "      <td>335618.0</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>3.2</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>None</td>\n",
              "      <td>2020</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>7</th>\n",
              "      <td>max</td>\n",
              "      <td>1499999</td>\n",
              "      <td>ZPBUA1ZLXKLA02012</td>\n",
              "      <td>59.8 in</td>\n",
              "      <td>Short</td>\n",
              "      <td>--</td>\n",
              "      <td>98.8 in</td>\n",
              "      <td>Wagon</td>\n",
              "      <td>Regular Cab</td>\n",
              "      <td>north hills</td>\n",
              "      <td>127.0</td>\n",
              "      <td>None</td>\n",
              "      <td>3573</td>\n",
              "      <td>99518</td>\n",
              "      <td>W8</td>\n",
              "      <td>8400.0</td>\n",
              "      <td>W8</td>\n",
              "      <td>yelow</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>smart</td>\n",
              "      <td>67 in</td>\n",
              "      <td>9.5 gal</td>\n",
              "      <td>Propane</td>\n",
              "      <td>1</td>\n",
              "      <td>99.2 in</td>\n",
              "      <td>127.0</td>\n",
              "      <td>1001.0</td>\n",
              "      <td>xDrive35i Premium AWD</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>1</td>\n",
              "      <td>61.2031</td>\n",
              "      <td>290 in</td>\n",
              "      <td>YELLOW</td>\n",
              "      <td>281800466</td>\n",
              "      <td>-66.0785</td>\n",
              "      <td>https://www.yourcarlot.net/car_pics/noles_auto...</td>\n",
              "      <td>['Z71 Package']</td>\n",
              "      <td>smart</td>\n",
              "      <td>9 seats</td>\n",
              "      <td>4290461.0</td>\n",
              "      <td>xD</td>\n",
              "      <td>19.0</td>\n",
              "      <td>99 hp @ 5,600 RPM</td>\n",
              "      <td>3299995.0</td>\n",
              "      <td>1</td>\n",
              "      <td>147414</td>\n",
              "      <td>5.0</td>\n",
              "      <td>440951.0</td>\n",
              "      <td>stream auto outlet</td>\n",
              "      <td>1</td>\n",
              "      <td>99 lb-ft @ 3,000 RPM</td>\n",
              "      <td>M</td>\n",
              "      <td>Manual</td>\n",
              "      <td>t9994</td>\n",
              "      <td>xDrive50i AWD</td>\n",
              "      <td>None</td>\n",
              "      <td>RWD</td>\n",
              "      <td>Rear-Wheel Drive</td>\n",
              "      <td>99.9 in</td>\n",
              "      <td>99.6 in</td>\n",
              "      <td>2021</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-aff1680e-1fa0-4000-8723-f8a621f57db8')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-aff1680e-1fa0-4000-8723-f8a621f57db8 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-aff1680e-1fa0-4000-8723-f8a621f57db8');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ],
            "text/plain": [
              "  summary                _c0                vin back_legroom    bed  \\\n",
              "0   count            1500000            1500000      1426707   9284   \n",
              "1    mean           749999.5           Infinity         None   None   \n",
              "2  stddev  433012.8462297622                NaN         None   None   \n",
              "3     min                  0                  0           --   Long   \n",
              "4     25%             374988        1.2870719E7         None   None   \n",
              "5     50%             749911         5.262659E8         None   None   \n",
              "6     75%            1124951      9.116310298E9         None   None   \n",
              "7     max            1499999  ZPBUA1ZLXKLA02012      59.8 in  Short   \n",
              "\n",
              "  bed_height bed_length    body_type        cabin         city  \\\n",
              "0     201277     201277      1493314        28245      1500000   \n",
              "1       None       None         None         None         None   \n",
              "2       None       None         None         None         None   \n",
              "3         --         --  Convertible     Crew Cab    Abbeville   \n",
              "4       None       None         None         None         None   \n",
              "5       None       None         None         None         None   \n",
              "6       None       None         None         None         None   \n",
              "7         --    98.8 in        Wagon  Regular Cab  north hills   \n",
              "\n",
              "    city_fuel_economy combine_fuel_economy        daysonmarket  \\\n",
              "0             1254750                    0             1500000   \n",
              "1  22.365110978282527                 None   76.40229266666667   \n",
              "2   7.759637445251214                 None  109.32362453306646   \n",
              "3                 7.0                 None                   0   \n",
              "4                18.0                 None                  14   \n",
              "5                21.0                 None                  35   \n",
              "6                26.0                 None                  82   \n",
              "7               127.0                 None                3573   \n",
              "\n",
              "           dealer_zip engine_cylinders engine_displacement engine_type  \\\n",
              "0             1500000          1455242             1420546     1455242   \n",
              "1  29977.329819954914             None   2927.714554826102        None   \n",
              "2   17238.34746019152             None  1309.5414969040357        None   \n",
              "3               01060               H4               700.0          H4   \n",
              "4             17042.0             None              2000.0        None   \n",
              "5             29708.0             None              2500.0        None   \n",
              "6             44039.0             None              3600.0        None   \n",
              "7               99518               W8              8400.0          W8   \n",
              "\n",
              "       exterior_color                fleet         frame_damaged  \\\n",
              "0             1499996               802507                802507   \n",
              "1   5132.078242229368  0.18111742327481256  0.009238548698017587   \n",
              "2  20381.158701200682   0.3851156801203441    0.0956724062713005   \n",
              "3                  \"\"                    0                     0   \n",
              "4                70.0                    0                     0   \n",
              "5               218.0                    0                     0   \n",
              "6               300.0                    0                     0   \n",
              "7               yelow                    1                     1   \n",
              "\n",
              "      franchise_dealer franchise_make front_legroom fuel_tank_volume  \\\n",
              "0              1500000        1212327       1426707          1426707   \n",
              "1   0.8096326666666667           None          None             None   \n",
              "2  0.39259102700481857           None          None             None   \n",
              "3                    0          Acura            --               --   \n",
              "4                    1           None          None             None   \n",
              "5                    1           None          None             None   \n",
              "6                    1           None          None             None   \n",
              "7                    1          smart         67 in          9.5 gal   \n",
              "\n",
              "   fuel_type       has_accidents   height highway_fuel_economy  \\\n",
              "0    1461382              802507  1426707              1254750   \n",
              "1       None  0.1681081909565898     None    29.17556724447101   \n",
              "2       None  0.3739625667814825     None    7.071351875652732   \n",
              "3  Biodiesel                   0       --                 10.0   \n",
              "4       None                   0     None                 25.0   \n",
              "5       None                   0     None                 28.0   \n",
              "6       None                   0     None                 33.0   \n",
              "7    Propane                   1  99.2 in                127.0   \n",
              "\n",
              "          horsepower             interior_color                isCab  \\\n",
              "0            1420546                    1499895               802507   \n",
              "1   245.496782927128          6876.763636363636  0.15378308226594908   \n",
              "2  88.62671162677182          31404.24037926155   0.3607409153867586   \n",
              "3               65.0  '4wd Supercrew 145'' Xlt'                    0   \n",
              "4              175.0                       20.0                    0   \n",
              "5              241.0                       57.0                    0   \n",
              "6              300.0                      101.0                    0   \n",
              "7             1001.0      xDrive35i Premium AWD                    1   \n",
              "\n",
              "  is_certified  is_cpo              is_new is_oemcpo           latitude  \\\n",
              "0            0  100987             1500000     74704            1500000   \n",
              "1         None     1.0             0.48086       1.0  38.32248923139961   \n",
              "2         None     0.0  0.4996336926414701       0.0  4.683470199082295   \n",
              "3         None       1                   0         1            18.3467   \n",
              "4         None       1                   0         1            35.1692   \n",
              "5         None       1                   0         1            40.1209   \n",
              "6         None       1                   1         1            41.7764   \n",
              "7         None       1                   1         1            61.2031   \n",
              "\n",
              "    length listing_color          listing_id           longitude  \\\n",
              "0  1426707       1500000             1500000             1500000   \n",
              "1     None          None  2.75414584440984E8  -80.75007164259848   \n",
              "2     None          None   8924250.127632566   5.946522447192903   \n",
              "3       --         BLACK            20498116            -157.928   \n",
              "4     None          None           274576666            -84.2834   \n",
              "5     None          None           278525093            -81.0239   \n",
              "6     None          None           280411086            -76.2382   \n",
              "7   290 in        YELLOW           281800466            -66.0785   \n",
              "\n",
              "                                    main_picture_url  \\\n",
              "0                                            1313002   \n",
              "1                                               None   \n",
              "2                                               None   \n",
              "3  https://058cfc5fa3837fde14de-43d0ef0c16a1c26e4...   \n",
              "4                                               None   \n",
              "5                                               None   \n",
              "6                                               None   \n",
              "7  https://www.yourcarlot.net/car_pics/noles_auto...   \n",
              "\n",
              "                                       major_options   make_name  \\\n",
              "0                                            1403537     1500000   \n",
              "1                                               None        None   \n",
              "2                                               None        None   \n",
              "3  ['101A Mid Equipment Group', '301A Mid Equipme...  AM General   \n",
              "4                                               None        None   \n",
              "5                                               None        None   \n",
              "6                                               None        None   \n",
              "7                                    ['Z71 Package']       smart   \n",
              "\n",
              "  maximum_seating             mileage          model_name         owner_count  \\\n",
              "0         1426707             1427012             1500000              755623   \n",
              "1            None   31551.69577200472  1549.3704494125934  1.5207080779700988   \n",
              "2            None  45268.289282703445   723.8195469859019  0.9043390844163363   \n",
              "3              --                 0.0            1 Series                 1.0   \n",
              "4            None                 6.0              1500.0                 1.0   \n",
              "5            None             10258.0              1500.0                 1.0   \n",
              "6            None             44593.0              1500.0                 2.0   \n",
              "7         9 seats           4290461.0                  xD                19.0   \n",
              "\n",
              "                  power               price               salvage  \\\n",
              "0               1269983             1500000                802507   \n",
              "1                  None  29474.935587153337  0.005259767204522827   \n",
              "2                  None   18869.85359565794   0.07233331579004001   \n",
              "3  1,001 hp @ 6,000 RPM               165.0                     0   \n",
              "4                  None             18000.0                     0   \n",
              "5                  None             26430.0                     0   \n",
              "6                  None             37694.0                     0   \n",
              "7     99 hp @ 5,600 RPM           3299995.0                     1   \n",
              "\n",
              "       savings_amount       seller_rating               sp_id  \\\n",
              "0             1500000             1482762             1499911   \n",
              "1   549.2656406666666   4.272535810139143   233948.8255536495   \n",
              "2  1068.0758383671564  0.5059160374603836  131033.00770750956   \n",
              "3                   0                 1.0             41593.0   \n",
              "4                   0                 4.0             63915.0   \n",
              "5                   0   4.333333333333333            281863.0   \n",
              "6                 789                 4.6            335618.0   \n",
              "7              147414                 5.0            440951.0   \n",
              "\n",
              "                               sp_name           theft_title  \\\n",
              "0                              1500000                802507   \n",
              "1                                 None  0.002924585081500847   \n",
              "2                                 None   0.05400032886251098   \n",
              "3  \\t Dijie Auto Sales and Service Co.                     0   \n",
              "4                                 None                     0   \n",
              "5                                 None                     0   \n",
              "6                                 None                     0   \n",
              "7                   stream auto outlet                     1   \n",
              "\n",
              "                  torque transmission transmission_display   trimId  \\\n",
              "0                1252794      1472973              1472973  1445368   \n",
              "1                   None         None                 None     None   \n",
              "2                   None         None                 None     None   \n",
              "3  100 lb-ft @ 3,000 RPM            A    1-Speed Automatic   t10000   \n",
              "4                   None         None                 None     None   \n",
              "5                   None         None                 None     None   \n",
              "6                   None         None                 None     None   \n",
              "7   99 lb-ft @ 3,000 RPM            M               Manual    t9994   \n",
              "\n",
              "            trim_name vehicle_damage_category wheel_system  \\\n",
              "0             1444974                       0      1430436   \n",
              "1  131.22539267015708                    None         None   \n",
              "2   566.4438268480721                    None         None   \n",
              "3                   !                    None          4WD   \n",
              "4                 2.5                    None         None   \n",
              "5                 2.5                    None         None   \n",
              "6                 3.2                    None         None   \n",
              "7       xDrive50i AWD                    None          RWD   \n",
              "\n",
              "  wheel_system_display wheelbase    width               year  \n",
              "0              1430436   1426707  1426707            1500000  \n",
              "1                 None      None     None        2017.631246  \n",
              "2                 None      None     None  4.267554017876733  \n",
              "3                  4X2        --       --               1915  \n",
              "4                 None      None     None               2017  \n",
              "5                 None      None     None               2019  \n",
              "6                 None      None     None               2020  \n",
              "7     Rear-Wheel Drive   99.9 in  99.6 in               2021  "
            ]
          },
          "execution_count": 109,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "data.summary().toPandas()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6jGDInFxusTs"
      },
      "outputs": [],
      "source": [
        "data = data.drop(\"bed\",\"bed_height\",\"bed_length\",\"cabin\",\"combine_fuel_economy\",\"is_certified\",\"is_cpo\",\"is_oemcpo\",\"salvage\",\"theft_title\",\"vehicle_damage_category\",\"engine_cylinders\", \"owner_count\",\"fleet\", \"isCab\", \"frame_damaged\", \"main_picture_url\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true,
          "base_uri": "https://localhost:8080/"
        },
        "id": "4YM74ME96Rbm",
        "outputId": "6e25cb52-ba65-4988-f3ea-ff0627cc1307"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+------------+-----+\n",
            "|back_legroom|count|\n",
            "+------------+-----+\n",
            "|        null|73293|\n",
            "|     38.3 in|59125|\n",
            "|     43.6 in|47178|\n",
            "|          --|44649|\n",
            "|     35.7 in|40530|\n",
            "|     37.4 in|38575|\n",
            "|       39 in|37315|\n",
            "|     40.4 in|36412|\n",
            "|     38.6 in|35435|\n",
            "|     39.9 in|35350|\n",
            "|       38 in|33575|\n",
            "|     35.2 in|29310|\n",
            "|     37.9 in|27246|\n",
            "|     38.1 in|26735|\n",
            "|     39.7 in|26665|\n",
            "|     39.6 in|26534|\n",
            "|     38.4 in|25298|\n",
            "|     40.9 in|24852|\n",
            "|     36.5 in|24736|\n",
            "|     36.1 in|24493|\n",
            "+------------+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.groupBy('back_legroom').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "a72ILG1vi_A0",
        "outputId": "3c818436-b7ae-441b-fc15-aa454528f7a5"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0\n"
          ]
        }
      ],
      "source": [
        "data = data.withColumn(\"back_legroom\",split(data[\"back_legroom\"],\" \").getItem(0))\n",
        "data.withColumn(\"back_legroom\",col(\"back_legroom\").cast(\"integer\"))\n",
        "df_mean = data.select(mean(col('back_legroom')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('back_legroom', when(data.back_legroom.isNull(), lit(avg)).otherwise(data.back_legroom))\n",
        "print(data.filter(col(\"back_legroom\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true,
          "base_uri": "https://localhost:8080/",
          "height": 516
        },
        "id": "5q2rND5Cx3pS",
        "outputId": "4cff7206-f329-462d-f5c7-019d96af3842"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+---------------+------+\n",
            "|      body_type| count|\n",
            "+---------------+------+\n",
            "|SUV / Crossover|739196|\n",
            "|          Sedan|360121|\n",
            "|   Pickup Truck|220497|\n",
            "|      Hatchback| 40944|\n",
            "|        Minivan| 40165|\n",
            "|          Coupe| 33009|\n",
            "|            Van| 26734|\n",
            "|          Wagon| 19761|\n",
            "|    Convertible| 12887|\n",
            "|           null|  6686|\n",
            "+---------------+------+\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.groupBy('body_type').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QXB4WdqpzIbS",
        "outputId": "7c8d7388-1b69-4a0f-9757-e806ee32f6fb"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0\n"
          ]
        }
      ],
      "source": [
        "data = data.withColumn('body_type', when(data.body_type.isNull(), lit('Unknown')).otherwise(data.body_type))\n",
        "print(data.filter(col(\"body_type\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "qjMNG9CrzR0T",
        "outputId": "580c834d-9378-452e-a790-082e6fcb1a3e"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-----------------+------+\n",
            "|city_fuel_economy| count|\n",
            "+-----------------+------+\n",
            "|             null|245250|\n",
            "|             18.0|103319|\n",
            "|             19.0| 98075|\n",
            "|             21.0| 92727|\n",
            "|             22.0| 88825|\n",
            "|             20.0| 84518|\n",
            "|             16.0| 84431|\n",
            "|             17.0| 79379|\n",
            "|             26.0| 77640|\n",
            "|             25.0| 70227|\n",
            "|             27.0| 69338|\n",
            "|             15.0| 69110|\n",
            "|             23.0| 59110|\n",
            "|             24.0| 56219|\n",
            "|             28.0| 47750|\n",
            "|             30.0| 36804|\n",
            "|             29.0| 30286|\n",
            "|             14.0| 22041|\n",
            "|             31.0| 16512|\n",
            "|             13.0| 12679|\n",
            "+-----------------+------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.groupBy('city_fuel_economy').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "7669Qzm6z0cg",
        "outputId": "a0fad1a9-c744-4f26-e7ec-17628fc1d126"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "0\n"
          ]
        }
      ],
      "source": [
        "df_mean = data.select(mean(col('city_fuel_economy')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('city_fuel_economy', when(data.city_fuel_economy.isNull(), lit(avg)).otherwise(data.city_fuel_economy))\n",
        "print(data.filter(col(\"city_fuel_economy\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "AFC3ZTVS0kNz"
      },
      "outputs": [],
      "source": [
        "# This is better represented in the horsepower column, we can drop this column.\n",
        "data = data.drop(\"engine_displacement\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "ozfA1ICaCPGU",
        "outputId": "34cc038b-25f1-40ed-d83a-b41ff0a785cc"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+------+\n",
            "|         engine_type| count|\n",
            "+--------------------+------+\n",
            "|                  I4|724887|\n",
            "|                  V6|371361|\n",
            "|                  V8|132054|\n",
            "|                null| 44758|\n",
            "|                  H4| 39230|\n",
            "|V8 Flex Fuel Vehicle| 37175|\n",
            "|V6 Flex Fuel Vehicle| 34600|\n",
            "|           I4 Hybrid| 31578|\n",
            "|                  I3| 28909|\n",
            "|                  I6| 13950|\n",
            "|           I6 Diesel|  8692|\n",
            "|        V8 Biodiesel|  8589|\n",
            "|I4 Flex Fuel Vehicle|  4574|\n",
            "|           I4 Diesel|  3506|\n",
            "|           V6 Diesel|  3198|\n",
            "|                  I5|  3018|\n",
            "|           V8 Diesel|  2692|\n",
            "|                  H6|  2281|\n",
            "|        V6 Biodiesel|  1533|\n",
            "|           V6 Hybrid|  1397|\n",
            "+--------------------+------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.groupBy('engine_type').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "zxmHOVe605lX"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn('engine_type', when(data.body_type.isNull(), lit('Unknown')).otherwise(data.engine_type))\n",
        "#print(data.filter(col(\"engine_type\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "VQ-7VqAk1duk",
        "outputId": "de3b6bba-a47d-40d9-dbef-fac5c16e5e41"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+--------------------+-----+\n",
            "|      exterior_color|count|\n",
            "+--------------------+-----+\n",
            "|               Black|84883|\n",
            "|               White|59331|\n",
            "|        Summit White|46956|\n",
            "|                Gray|38891|\n",
            "|              Silver|37783|\n",
            "|                Blue|27824|\n",
            "|        Oxford White|27477|\n",
            "|                None|23463|\n",
            "|                 Red|21917|\n",
            "|Bright White Clea...|20484|\n",
            "| Silver Ice Metallic|19153|\n",
            "|        Gun Metallic|15237|\n",
            "|Mosaic Black Meta...|14939|\n",
            "|Diamond Black Cry...|14645|\n",
            "|   Magnetic Metallic|13031|\n",
            "|Satin Steel Metallic|13004|\n",
            "|               BLACK|12897|\n",
            "| Crystal Black Pearl|12718|\n",
            "|Granite Crystal M...|12394|\n",
            "|Modern Steel Meta...|10985|\n",
            "+--------------------+-----+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.groupBy('exterior_color').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "OmxXGyt71yis"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn('exterior_color', when(data.body_type.isNull(), lit('Black')).otherwise(data.exterior_color))\n",
        "#print(data.filter(col(\"exterior_color\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "UETni4ts17Xv"
      },
      "outputs": [],
      "source": [
        "# Recategorize the exterior_color"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KK-GLUhz3gBd"
      },
      "outputs": [],
      "source": [
        "# The information in this column closely matches the information in the make_name column. So we can drop this column from the dataset due to redundancy.\n",
        "data = data.drop(\"franchise_make\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "background_save": true
        },
        "id": "zCL0-8-p5Ttk",
        "outputId": "e95287f0-deb8-4c2a-947d-4cf99f916181"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+-------------+------+\n",
            "|front_legroom| count|\n",
            "+-------------+------+\n",
            "|      40.9 in|116477|\n",
            "|      41.3 in| 76096|\n",
            "|        41 in| 73540|\n",
            "|         null| 73293|\n",
            "|      43.9 in| 70572|\n",
            "|      41.2 in| 67424|\n",
            "|      42.3 in| 57873|\n",
            "|      41.5 in| 50396|\n",
            "|        43 in| 47323|\n",
            "|      40.8 in| 45924|\n",
            "|      44.5 in| 45268|\n",
            "|      42.2 in| 43806|\n",
            "|      41.1 in| 41269|\n",
            "|      40.3 in| 39805|\n",
            "|        42 in| 39297|\n",
            "|      42.4 in| 38882|\n",
            "|      41.8 in| 34107|\n",
            "|      43.1 in| 34026|\n",
            "|      41.4 in| 32238|\n",
            "|      45.3 in| 31493|\n",
            "+-------------+------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.groupBy('front_legroom').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "xhgIF-WM4335"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn(\"front_legroom\",split(data[\"front_legroom\"],\" \").getItem(0))\n",
        "data.withColumn(\"front_legroom\",col(\"front_legroom\").cast(\"float\"))\n",
        "df_mean = data.select(mean(col('front_legroom')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('front_legroom', when(data.front_legroom.isNull(), lit(avg)).otherwise(data.front_legroom))\n",
        "#print(data.filter(col(\"front_legroom\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "q04JqILl5ph4"
      },
      "outputs": [],
      "source": [
        "# Combining front_legroom and back_legroom\n",
        "data = data.withColumn('legroom', expr(\"front_legroom + back_legroom\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "0Hnu45s96LIj"
      },
      "outputs": [],
      "source": [
        "data.groupBy('legroom').count().orderBy('count', ascending=False).show()\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "MrDHaVpjXAXV"
      },
      "outputs": [],
      "source": [
        "data = data.drop(\"front_legroom\", \"back_legroom\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-pEHrKU56x97"
      },
      "outputs": [],
      "source": [
        "data.groupBy('fuel_tank_volume').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "rUqNME7i8VJh",
        "outputId": "a6d30055-e4cd-49bd-c5c8-8ab7546acb4c"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[_c0: int, vin: string, body_type: string, city: string, city_fuel_economy: double, daysonmarket: int, dealer_zip: string, engine_type: string, exterior_color: string, franchise_dealer: int, fuel_tank_volume: float, fuel_type: string, has_accidents: int, height: string, highway_fuel_economy: double, horsepower: double, interior_color: string, is_new: int, latitude: double, length: string, listing_color: string, listing_id: int, longitude: double, major_options: string, make_name: string, maximum_seating: string, mileage: double, model_name: string, power: string, price: double, savings_amount: int, seller_rating: double, sp_id: double, sp_name: string, torque: string, transmission: string, transmission_display: string, trimId: string, trim_name: string, wheel_system: string, wheel_system_display: string, wheelbase: string, width: string, year: int, legroom: double]"
            ]
          },
          "metadata": {},
          "execution_count": 20
        }
      ],
      "source": [
        "data = data.replace({'--':None}, subset=['fuel_tank_volume'])\n",
        "data = data.withColumn(\"fuel_tank_volume\",split(data[\"fuel_tank_volume\"],\" \").getItem(0))\n",
        "data.withColumn(\"fuel_tank_volume\",col(\"fuel_tank_volume\").cast(\"float\"))\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fjWXhngfXDyW"
      },
      "outputs": [],
      "source": [
        "data.groupBy('fuel_tank_volume').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "lal4z2ui83M3"
      },
      "outputs": [],
      "source": [
        "# Replace nulls with 0 when fuel_type = \"Electric\" and rest with mean"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "KSA43afLBCzi"
      },
      "outputs": [],
      "source": [
        "data.groupBy('fuel_type').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "5F2xIgKZBf4a"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn('fuel_type', when(data.fuel_type.isNull(), lit('Gasoline')).otherwise(data.fuel_type))\n",
        "#print(data.filter(col(\"fuel_type\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "HD1E_va1BuYN"
      },
      "outputs": [],
      "source": [
        "data.groupBy('has_accidents').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-x-cbcM0ByJ8"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn('has_accidents', when(data.has_accidents.isNull(), lit(0)).otherwise(data.has_accidents))\n",
        "#print(data.filter(col(\"has_accidents\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "TKwlpZFYCA5U"
      },
      "outputs": [],
      "source": [
        "data.groupBy('height').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "uJAzqZQIDvwm",
        "outputId": "2d84483a-d42b-42d1-cc64-32a6d47e0662"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "DataFrame[_c0: int, vin: string, body_type: string, city: string, city_fuel_economy: double, daysonmarket: int, dealer_zip: string, engine_type: string, exterior_color: string, franchise_dealer: int, fuel_tank_volume: string, fuel_type: string, has_accidents: int, height: float, highway_fuel_economy: double, horsepower: double, interior_color: string, is_new: int, latitude: double, length: string, listing_color: string, listing_id: int, longitude: double, major_options: string, make_name: string, maximum_seating: string, mileage: double, model_name: string, power: string, price: double, savings_amount: int, seller_rating: double, sp_id: double, sp_name: string, torque: string, transmission: string, transmission_display: string, trimId: string, trim_name: string, wheel_system: string, wheel_system_display: string, wheelbase: string, width: string, year: int, legroom: double]"
            ]
          },
          "metadata": {},
          "execution_count": 23
        }
      ],
      "source": [
        "data = data.replace({'--':None}, subset=['height'])\n",
        "data = data.withColumn(\"height\",split(data[\"height\"],\" \").getItem(0))\n",
        "data.withColumn(\"height\",col(\"height\").cast(\"float\"))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uXgOuJYsEhl6"
      },
      "outputs": [],
      "source": [
        "df_mean = data.select(mean(col('height')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('height', when(data.height.isNull(), lit(avg)).otherwise(data.height))\n",
        "#print(data.filter(col(\"height\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "QX3v1Y2XE8b-"
      },
      "outputs": [],
      "source": [
        "df_mean = data.select(mean(col('highway_fuel_economy')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('highway_fuel_economy', when(data.highway_fuel_economy.isNull(), lit(avg)).otherwise(data.highway_fuel_economy))\n",
        "#print(data.filter(col(\"highway_fuel_economy\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UX6ZagCrFfta"
      },
      "outputs": [],
      "source": [
        "data.groupBy('horsepower').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "XcZqF8xuFbBd"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn('horsepower', when(data.horsepower.isNull(), lit(170)).otherwise(data.horsepower))\n",
        "#print(data.filter(col(\"horsepower\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1Eq6jDG0F50g"
      },
      "outputs": [],
      "source": [
        "data.groupBy('interior_color').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "P3GQsL0eGDvn"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn('interior_color', when(data.interior_color.isNull(), lit(\"Other\")).otherwise(data.interior_color))\n",
        "#print(data.filter(col(\"interior_color\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hDpNyB1eIz6I"
      },
      "outputs": [],
      "source": [
        "data.groupBy('length').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Cd_8L1qdGUVF"
      },
      "outputs": [],
      "source": [
        "data = data.replace({'--':None}, subset=['length'])\n",
        "data = data.withColumn(\"length\",split(data[\"length\"],\" \").getItem(0))\n",
        "data.withColumn(\"length\",col(\"length\").cast(\"float\"))\n",
        "df_mean = data.select(mean(col('length')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('length', when(data.length.isNull(), lit(avg)).otherwise(data.length))\n",
        "#print(data.filter(col(\"length\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "CgA4YPZugdhI"
      },
      "outputs": [],
      "source": [
        "data = data.replace({'--':None}, subset=['wheelbase'])\n",
        "data = data.withColumn(\"wheelbase\",split(data[\"wheelbase\"],\" \").getItem(0))\n",
        "data.withColumn(\"wheelbase\",col(\"wheelbase\").cast(\"float\"))\n",
        "df_mean = data.select(mean(col('wheelbase')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('wheelbase', when(data.wheelbase.isNull(), \n",
        "                                         lit(avg)).otherwise(data.wheelbase))\n",
        "#print(data.filter(col(\"wheelbase\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "nMy1VHlvgjfW"
      },
      "outputs": [],
      "source": [
        "data = data.replace({'--':None}, subset=['width'])\n",
        "data = data.withColumn(\"width\",split(data[\"width\"],\" \").getItem(0))\n",
        "data.withColumn(\"width\",col(\"width\").cast(\"float\"))\n",
        "df_mean = data.select(mean(col('width')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('width', when(data.width.isNull(), lit(avg)).otherwise(data.width))\n",
        "#print(data.filter(col(\"width\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "qQrgw3mJLbAe"
      },
      "outputs": [],
      "source": [
        "data.groupBy('major_options').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1Wj-ERgZMdsE"
      },
      "outputs": [],
      "source": [
        "data = data.withColumn(\"major_options_count\",size(split(col(\"major_options\"),\",\")))\n",
        "#data.groupBy('major_options_count').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "acvzYGaIODGr"
      },
      "outputs": [],
      "source": [
        "df_mean = data.select(mean(col('major_options_count')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('major_options_count', when(data.major_options_count.isNull(), lit(avg)).otherwise(data.major_options_count))\n",
        "data = data.drop(\"major_options\")\n",
        "#print(data.filter(col(\"major_options_count\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NLrvtlKVOrbd"
      },
      "outputs": [],
      "source": [
        "data.groupBy('maximum_seating').count().orderBy('count', ascending=False).show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "FAfykVZeO60F"
      },
      "outputs": [],
      "source": [
        "data = data.replace({'--':None}, subset=['maximum_seating'])\n",
        "data = data.withColumn(\"maximum_seating\",split(data[\"maximum_seating\"],\" \").getItem(0))\n",
        "data.withColumn(\"maximum_seating\",col(\"maximum_seating\").cast(\"int\"))\n",
        "df_mean = data.select(mean(col('maximum_seating')).alias('avg')).collect()\n",
        "avg = df_mean[0]['avg']\n",
        "data = data.withColumn('maximum_seating', when(data.maximum_seating.isNull(), lit(avg)).otherwise(data.maximum_seating))\n",
        "#print(data.filter(col(\"maximum_seating\").isNull()).count())"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "hLVTQhPdSvQk"
      },
      "outputs": [],
      "source": [
        "amount_missing_df = data.select([(count(when(isnan(c) | col(c).isNull(), c))*100/count(lit(1))).alias(c) for c in data.columns])\n",
        "amount_missing_df.show()"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "We9-HsfDS77D"
      },
      "source": [
        "# Model"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "cz8hOACBS-Cf",
        "outputId": "5dc09769-0ce1-4d3d-eb7a-0dd80591618e"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "+-------+-----------------+---------------+----------+------------------+------------+----------+-----------+--------------------+----------------+----------------+---------+-------------+-----------------+--------------------+----------+--------------------+------+--------+------------------+-------------+----------+---------+---------+-----------------+-------+--------------------+-----+-------+--------------+------------------+--------+--------------------+------+------------+--------------------+------+-----------+------------+--------------------+------------------+-----------------+----+-----------------+-------------------+\n",
            "|    _c0|              vin|      body_type|      city| city_fuel_economy|daysonmarket|dealer_zip|engine_type|      exterior_color|franchise_dealer|fuel_tank_volume|fuel_type|has_accidents|           height|highway_fuel_economy|horsepower|      interior_color|is_new|latitude|            length|listing_color|listing_id|longitude|make_name|  maximum_seating|mileage|          model_name|power|  price|savings_amount|     seller_rating|   sp_id|             sp_name|torque|transmission|transmission_display|trimId|  trim_name|wheel_system|wheel_system_display|         wheelbase|            width|year|          legroom|major_options_count|\n",
            "+-------+-----------------+---------------+----------+------------------+------------+----------+-----------+--------------------+----------------+----------------+---------+-------------+-----------------+--------------------+----------+--------------------+------+--------+------------------+-------------+----------+---------+---------+-----------------+-------+--------------------+-----+-------+--------------+------------------+--------+--------------------+------+------------+--------------------+------+-----------+------------+--------------------+------------------+-----------------+----+-----------------+-------------------+\n",
            "| 165265|3C7WRNFLXGG142265|   Pickup Truck|  Abington|22.365110978282527|         105|      2351|       null|               WHITE|               0|            null| Gasoline|            1|65.88426874843415|   29.17556724447101|     170.0|                None|     0| 42.0994|192.95317048549902|        WHITE| 272695692| -70.9612|      RAM|5.454915665353393|79499.0|        5500 Chassis| null|36995.0|             0|               3.0|154666.0|          Auto Towne|  null|           A|           Automatic|t93393|LWB DRW 4WD|        null|                null|114.79503438858377|78.30550440387708|2016|80.09870508467453|                1.0|\n",
            "| 223473|1FD0X5HT6KEF89743|   Pickup Truck|    Agawam|22.365110978282527|          78|      1001|       null|               White|               1|            null|   Diesel|            0|65.88426874843415|   29.17556724447101|     170.0|          EARTH GRAY|     1| 42.0889|192.95317048549902|        WHITE| 274723781| -72.6383|     Ford|5.454915665353393|  120.0|F-550 Super Duty ...| null|68365.0|             0|               4.5| 56284.0|          Sarat Ford|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2019|80.09870508467453|               -1.0|\n",
            "|1120761|5UXCR6C08M9D83691|SUV / Crossover|    Athens|22.365110978282527|           7|     30606|       null|Arctic Gray Metallic|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|          BLACK/MCSW|     1| 33.9438|192.95317048549902|         GRAY| 281213818| -83.4405|      BMW|5.454915665353393|    6.0|                  X5| null|73745.0|             0|               4.5| 60649.0|          Athens BMW|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "|1144906|5UX2V5C07M9E02166|SUV / Crossover|   Atlanta|22.365110978282527|          11|     30339|       null|Black Sapphire Me...|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|Black Vernasca Le...|     1| 33.8989|192.95317048549902|        BLACK| 280812129| -84.4497|      BMW|5.454915665353393|    3.0|                  X4| null|70770.0|             0| 4.395604395604396|290453.0|  Global Imports BMW|  null|           A|   8-Speed Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|                4.0|\n",
            "|1125542|1HTKJPVK5LH251808|   Pickup Truck|   Atlanta|22.365110978282527|         161|     30341|       null|        Summit White|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|Dark Ash seats wi...|     1| 33.9084|192.95317048549902|        WHITE| 269945232| -84.2919|Chevrolet|5.454915665353393|    5.0|      Silverado 1500| null|70942.0|             0| 4.461538461538462|275648.0|Jim Ellis Chevrol...|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2020|80.09870508467453|                4.0|\n",
            "|1172659|3KPF24AD4ME266101|          Sedan|   Augusta|22.365110978282527|          13|     30904|       null|        Aurora Black|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     1| 33.4461|192.95317048549902|        BLACK| 280649440| -82.0309|      Kia|5.454915665353393|    4.0|               Forte| null|18893.0|             0|               4.4|327398.0|Stokes-Hodges Kia...|  null|        null|                null|t93989|     LX FWD|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|                5.0|\n",
            "|1178745|JTEPU5JRXM5837906|SUV / Crossover|   Augusta|22.365110978282527|           2|     30907|       null|               White|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|                None|     1| 33.5163|192.95317048549902|        WHITE| 281557118| -82.0746|   Toyota|5.454915665353393|    2.0|             4Runner| null|42912.0|             0|               4.5|296867.0|Milton Ruben Supe...|  null|           A|5-Speed Automatic...|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "| 140373|JM3KFBCM2M0303425|SUV / Crossover| Bay Shore|22.365110978282527|           7|     11706|       null|           Jet Black|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     1| 40.7314|192.95317048549902|        BLACK| 280996916| -73.2653|    Mazda|5.454915665353393|   null|                CX-5| null|29455.0|             0|            3.6875|311179.0|Bright Bay Auto G...|  null|        null|                null|t94037|     GS AWD|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "| 140810|JM3TCBCY2M0501941|SUV / Crossover| Bay Shore|22.365110978282527|           5|     11706|       null|        Machine Gray|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     1| 40.7314|192.95317048549902|         GRAY| 281258164| -73.2653|    Mazda|5.454915665353393|   null|                CX-9| null|41305.0|             0|            3.6875|311179.0|Bright Bay Auto G...|  null|        null|                null|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "| 816230|3C7WRNBL3KG704831|        Unknown|   Bedford|22.365110978282527|         230|     24523|       null|Bright White Clea...|               1|            null|   Diesel|            0|65.88426874843415|   29.17556724447101|     170.0|   Diesel Gray/Black|     1| 37.3475|192.95317048549902|        WHITE| 263972337| -79.5039|      RAM|5.454915665353393|   20.0|        5500 Chassis| null|54990.0|             0| 4.833333333333333| 63422.0| Dominion of Bedford|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2019|80.09870508467453|                4.0|\n",
            "|1236776|1G1ZD5ST8MF003112|          Sedan|Birmingham|22.365110978282527|           1|     35242|       null|Mosaic Black Meta...|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|     medium ash gray|     1| 33.5358|192.95317048549902|        BLACK| 281624106| -86.7205|Chevrolet|5.454915665353393|   null|              Malibu| null|28605.0|             0| 4.304347826086956| 47078.0|Edwards Chevrolet...|  null|         CVT|Continuously Vari...|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "| 564761|KM8R1DHE0MU173524|        Unknown|  Boardman|22.365110978282527|          34|     44512|       null|          Rainforest|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     1| 41.0243|192.95317048549902|      UNKNOWN| 278583639| -80.6533|  Hyundai|5.454915665353393|   13.0|            Palisade| null|34814.0|             0|               5.0|276627.0|     Preston Hyundai|  null|           A|   8-Speed Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|                5.0|\n",
            "| 240842|WA1EVAF11MD002183|SUV / Crossover| Brookline|22.365110978282527|           5|      2445|       null|               White|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     1|   42.33|192.95317048549902|        WHITE| 281294557| -71.1248|     Audi|5.454915665353393|   25.0|                  Q8| null|80960.0|             0|3.7142857142857135| 42822.0|      Audi Brookline|  null|           A|8-Speed Automatic...|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "|1201481|KM8R54HE6MU204831|        Unknown| Brunswick|22.365110978282527|          11|     31525|       null|                None|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|                None|     1| 31.2084|192.95317048549902|      UNKNOWN| 280771640| -81.4819|  Hyundai|5.454915665353393|    3.0|            Palisade| null|47275.0|             0|               5.0|289947.0|   Hyundai Brunswick|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               13.0|\n",
            "| 518343|3KPF34AD7ME271225|          Sedan|   Buffalo|22.365110978282527|           8|     14224|       null|       Deep Sea Blue|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|Black w/Cloth & L...|     1| 42.8542|192.95317048549902|         BLUE| 280910038|  -78.697|      Kia|5.454915665353393|    0.0|               Forte| null|22105.0|             0| 4.083333333333333|326139.0|Transitowne Kia o...|  null|         CVT|Continuously Vari...|t93989|     LX FWD|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|                2.0|\n",
            "| 524121|      0F91M517678|          Coupe|   Buffalo|22.365110978282527|         329|     14214|       null|              Yellow|               0|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     0| 42.9487|192.95317048549902|       YELLOW| 255338381| -78.8287|  Mercury|5.454915665353393|50039.0|              Cougar| null|78500.0|             0|              4.75|280919.0|  Four M Sales, Inc.|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|1970|80.09870508467453|               -1.0|\n",
            "|1088405|1C6SRFPTXMN500295|   Pickup Truck|    Buford|22.365110978282527|           0|     30518|       null|Maximum Steel Met...|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|               Black|     1| 34.0952|192.95317048549902|      UNKNOWN| 281663361| -84.0116|      RAM|5.454915665353393|    0.0|                1500| null|69009.0|             0| 4.761904761904762| 60753.0|Mall of Georgia C...|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "|1132960|         AMS19449|          Coupe|  Cadillac|22.365110978282527|         217|     49601|       null|              Silver|               0|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|                Gray|     0| 44.2811|192.95317048549902|       SILVER| 265180307| -85.4575|Chevrolet|5.454915665353393| 1582.0|            Corvette| null|36495.0|             0| 4.409090909090909|377630.0|   Classic Car Deals|  null|           M|      4-Speed Manual|  null|       null|        null|                null|114.79503438858377|78.30550440387708|1978|80.09870508467453|                1.0|\n",
            "| 598347|1FDUF5HT2LDA01090|   Pickup Truck|  Carnegie|22.365110978282527|         208|     15106|       null|        Oxford White|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|                None|     1| 40.3875|192.95317048549902|        WHITE| 265854596| -80.0912|     Ford|5.454915665353393|    3.0|F-550 Super Duty ...| null|56643.0|             0| 4.195121951219512| 57550.0|   Woltz & Wind Ford|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2020|80.09870508467453|                8.0|\n",
            "| 912809|WBA73AK06M7G32474|          Coupe| Charlotte|22.365110978282527|           7|     28269|       null|Black Sapphire Me...|               1|            null| Gasoline|            0|65.88426874843415|   29.17556724447101|     170.0|        Oyster/Black|     1| 35.3146|192.95317048549902|        BLACK| 281168448| -80.7835|      BMW|5.454915665353393|    0.0|            2 Series| null|41795.0|             0| 4.473684210526316|305522.0|Hendrick BMW - No...|  null|           A|           Automatic|  null|       null|        null|                null|114.79503438858377|78.30550440387708|2021|80.09870508467453|               -1.0|\n",
            "+-------+-----------------+---------------+----------+------------------+------------+----------+-----------+--------------------+----------------+----------------+---------+-------------+-----------------+--------------------+----------+--------------------+------+--------+------------------+-------------+----------+---------+---------+-----------------+-------+--------------------+-----+-------+--------------+------------------+--------+--------------------+------+------------+--------------------+------+-----------+------------+--------------------+------------------+-----------------+----+-----------------+-------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "data.show()"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "data = data.drop('_c0', 'vin', 'city','dealer_zip', 'exterior_color', 'interior_color', 'listing_id', 'make_name','model_name', 'power', 'sp_name', 'torque',\t'trimId',\t'trim_name',\t'wheel_system_display', 'sp_id')"
      ],
      "metadata": {
        "id": "WS39j0wOQqme"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "dummy = data.toPandas()\n"
      ],
      "metadata": {
        "id": "7jzjIww9LjET"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 305
        },
        "id": "IW7TJF4nY1-L",
        "outputId": "db2fc07e-099d-4b97-d01c-90224f5ce076"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "   city_fuel_economy  daysonmarket  franchise_dealer fuel_tank_volume  \\\n",
              "0          22.365111           105                 0             None   \n",
              "1          22.365111            78                 1             None   \n",
              "2          22.365111             7                 1             None   \n",
              "3          22.365111            11                 1             None   \n",
              "4          22.365111           161                 1             None   \n",
              "\n",
              "   has_accidents             height  highway_fuel_economy  horsepower  is_new  \\\n",
              "0              1  65.88426874843415             29.175567       170.0       0   \n",
              "1              0  65.88426874843415             29.175567       170.0       1   \n",
              "2              0  65.88426874843415             29.175567       170.0       1   \n",
              "3              0  65.88426874843415             29.175567       170.0       1   \n",
              "4              0  65.88426874843415             29.175567       170.0       1   \n",
              "\n",
              "   latitude              length  longitude    maximum_seating  mileage  \\\n",
              "0   42.0994  192.95317048549902   -70.9612  5.454915665353393  79499.0   \n",
              "1   42.0889  192.95317048549902   -72.6383  5.454915665353393    120.0   \n",
              "2   33.9438  192.95317048549902   -83.4405  5.454915665353393      6.0   \n",
              "3   33.8989  192.95317048549902   -84.4497  5.454915665353393      3.0   \n",
              "4   33.9084  192.95317048549902   -84.2919  5.454915665353393      5.0   \n",
              "\n",
              "     price  savings_amount  seller_rating           wheelbase  \\\n",
              "0  36995.0               0       3.000000  114.79503438858377   \n",
              "1  68365.0               0       4.500000  114.79503438858377   \n",
              "2  73745.0               0       4.500000  114.79503438858377   \n",
              "3  70770.0               0       4.395604  114.79503438858377   \n",
              "4  70942.0               0       4.461538  114.79503438858377   \n",
              "\n",
              "               width  year    legroom  major_options_count  \\\n",
              "0  78.30550440387708  2016  80.098705                  1.0   \n",
              "1  78.30550440387708  2019  80.098705                 -1.0   \n",
              "2  78.30550440387708  2021  80.098705                 -1.0   \n",
              "3  78.30550440387708  2021  80.098705                  4.0   \n",
              "4  78.30550440387708  2020  80.098705                  4.0   \n",
              "\n",
              "   body_type_Convertible  body_type_Coupe  body_type_Hatchback  \\\n",
              "0                      0                0                    0   \n",
              "1                      0                0                    0   \n",
              "2                      0                0                    0   \n",
              "3                      0                0                    0   \n",
              "4                      0                0                    0   \n",
              "\n",
              "   body_type_Minivan  body_type_Pickup Truck  body_type_SUV / Crossover  \\\n",
              "0                  0                       1                          0   \n",
              "1                  0                       1                          0   \n",
              "2                  0                       0                          1   \n",
              "3                  0                       0                          1   \n",
              "4                  0                       1                          0   \n",
              "\n",
              "   body_type_Sedan  body_type_Unknown  body_type_Van  body_type_Wagon  \\\n",
              "0                0                  0              0                0   \n",
              "1                0                  0              0                0   \n",
              "2                0                  0              0                0   \n",
              "3                0                  0              0                0   \n",
              "4                0                  0              0                0   \n",
              "\n",
              "   engine_type_H4  engine_type_H4 Hybrid  engine_type_H6  engine_type_I2  \\\n",
              "0               0                      0               0               0   \n",
              "1               0                      0               0               0   \n",
              "2               0                      0               0               0   \n",
              "3               0                      0               0               0   \n",
              "4               0                      0               0               0   \n",
              "\n",
              "   engine_type_I3  engine_type_I3 Hybrid  engine_type_I4  \\\n",
              "0               0                      0               0   \n",
              "1               0                      0               0   \n",
              "2               0                      0               0   \n",
              "3               0                      0               0   \n",
              "4               0                      0               0   \n",
              "\n",
              "   engine_type_I4 Compressed Natural Gas  engine_type_I4 Diesel  \\\n",
              "0                                      0                      0   \n",
              "1                                      0                      0   \n",
              "2                                      0                      0   \n",
              "3                                      0                      0   \n",
              "4                                      0                      0   \n",
              "\n",
              "   engine_type_I4 Flex Fuel Vehicle  engine_type_I4 Hybrid  engine_type_I5  \\\n",
              "0                                 0                      0               0   \n",
              "1                                 0                      0               0   \n",
              "2                                 0                      0               0   \n",
              "3                                 0                      0               0   \n",
              "4                                 0                      0               0   \n",
              "\n",
              "   engine_type_I5 Biodiesel  engine_type_I5 Diesel  engine_type_I6  \\\n",
              "0                         0                      0               0   \n",
              "1                         0                      0               0   \n",
              "2                         0                      0               0   \n",
              "3                         0                      0               0   \n",
              "4                         0                      0               0   \n",
              "\n",
              "   engine_type_I6 Diesel  engine_type_I6 Hybrid  engine_type_R2  \\\n",
              "0                      0                      0               0   \n",
              "1                      0                      0               0   \n",
              "2                      0                      0               0   \n",
              "3                      0                      0               0   \n",
              "4                      0                      0               0   \n",
              "\n",
              "   engine_type_V10  engine_type_V10 Diesel  engine_type_V12  engine_type_V6  \\\n",
              "0                0                       0                0               0   \n",
              "1                0                       0                0               0   \n",
              "2                0                       0                0               0   \n",
              "3                0                       0                0               0   \n",
              "4                0                       0                0               0   \n",
              "\n",
              "   engine_type_V6 Biodiesel  engine_type_V6 Compressed Natural Gas  \\\n",
              "0                         0                                      0   \n",
              "1                         0                                      0   \n",
              "2                         0                                      0   \n",
              "3                         0                                      0   \n",
              "4                         0                                      0   \n",
              "\n",
              "   engine_type_V6 Diesel  engine_type_V6 Flex Fuel Vehicle  \\\n",
              "0                      0                                 0   \n",
              "1                      0                                 0   \n",
              "2                      0                                 0   \n",
              "3                      0                                 0   \n",
              "4                      0                                 0   \n",
              "\n",
              "   engine_type_V6 Hybrid  engine_type_V8  engine_type_V8 Biodiesel  \\\n",
              "0                      0               0                         0   \n",
              "1                      0               0                         0   \n",
              "2                      0               0                         0   \n",
              "3                      0               0                         0   \n",
              "4                      0               0                         0   \n",
              "\n",
              "   engine_type_V8 Compressed Natural Gas  engine_type_V8 Diesel  \\\n",
              "0                                      0                      0   \n",
              "1                                      0                      0   \n",
              "2                                      0                      0   \n",
              "3                                      0                      0   \n",
              "4                                      0                      0   \n",
              "\n",
              "   engine_type_V8 Flex Fuel Vehicle  engine_type_V8 Hybrid  \\\n",
              "0                                 0                      0   \n",
              "1                                 0                      0   \n",
              "2                                 0                      0   \n",
              "3                                 0                      0   \n",
              "4                                 0                      0   \n",
              "\n",
              "   engine_type_V8 Propane  engine_type_W12  engine_type_W12 Flex Fuel Vehicle  \\\n",
              "0                       0                0                                  0   \n",
              "1                       0                0                                  0   \n",
              "2                       0                0                                  0   \n",
              "3                       0                0                                  0   \n",
              "4                       0                0                                  0   \n",
              "\n",
              "   engine_type_W16  engine_type_W8  fuel_type_Biodiesel  \\\n",
              "0                0               0                    0   \n",
              "1                0               0                    0   \n",
              "2                0               0                    0   \n",
              "3                0               0                    0   \n",
              "4                0               0                    0   \n",
              "\n",
              "   fuel_type_Compressed Natural Gas  fuel_type_Diesel  fuel_type_Electric  \\\n",
              "0                                 0                 0                   0   \n",
              "1                                 0                 1                   0   \n",
              "2                                 0                 0                   0   \n",
              "3                                 0                 0                   0   \n",
              "4                                 0                 0                   0   \n",
              "\n",
              "   fuel_type_Flex Fuel Vehicle  fuel_type_Gasoline  fuel_type_Hybrid  \\\n",
              "0                            0                   1                 0   \n",
              "1                            0                   0                 0   \n",
              "2                            0                   1                 0   \n",
              "3                            0                   1                 0   \n",
              "4                            0                   1                 0   \n",
              "\n",
              "   fuel_type_Propane  listing_color_BLACK  listing_color_BLUE  \\\n",
              "0                  0                    0                   0   \n",
              "1                  0                    0                   0   \n",
              "2                  0                    0                   0   \n",
              "3                  0                    1                   0   \n",
              "4                  0                    0                   0   \n",
              "\n",
              "   listing_color_BROWN  listing_color_GOLD  listing_color_GRAY  \\\n",
              "0                    0                   0                   0   \n",
              "1                    0                   0                   0   \n",
              "2                    0                   0                   1   \n",
              "3                    0                   0                   0   \n",
              "4                    0                   0                   0   \n",
              "\n",
              "   listing_color_GREEN  listing_color_ORANGE  listing_color_PINK  \\\n",
              "0                    0                     0                   0   \n",
              "1                    0                     0                   0   \n",
              "2                    0                     0                   0   \n",
              "3                    0                     0                   0   \n",
              "4                    0                     0                   0   \n",
              "\n",
              "   listing_color_PURPLE  listing_color_RED  listing_color_SILVER  \\\n",
              "0                     0                  0                     0   \n",
              "1                     0                  0                     0   \n",
              "2                     0                  0                     0   \n",
              "3                     0                  0                     0   \n",
              "4                     0                  0                     0   \n",
              "\n",
              "   listing_color_TEAL  listing_color_UNKNOWN  listing_color_WHITE  \\\n",
              "0                   0                      0                    1   \n",
              "1                   0                      0                    1   \n",
              "2                   0                      0                    0   \n",
              "3                   0                      0                    0   \n",
              "4                   0                      0                    1   \n",
              "\n",
              "   listing_color_YELLOW  transmission_A  transmission_CVT  \\\n",
              "0                     0               1                 0   \n",
              "1                     0               1                 0   \n",
              "2                     0               1                 0   \n",
              "3                     0               1                 0   \n",
              "4                     0               1                 0   \n",
              "\n",
              "   transmission_Dual Clutch  transmission_M  \\\n",
              "0                         0               0   \n",
              "1                         0               0   \n",
              "2                         0               0   \n",
              "3                         0               0   \n",
              "4                         0               0   \n",
              "\n",
              "   transmission_display_1-Speed Automatic  transmission_display_1-Speed CVT  \\\n",
              "0                                       0                                 0   \n",
              "1                                       0                                 0   \n",
              "2                                       0                                 0   \n",
              "3                                       0                                 0   \n",
              "4                                       0                                 0   \n",
              "\n",
              "   transmission_display_1-Speed Dual Clutch  \\\n",
              "0                                         0   \n",
              "1                                         0   \n",
              "2                                         0   \n",
              "3                                         0   \n",
              "4                                         0   \n",
              "\n",
              "   transmission_display_10-Speed Automatic  transmission_display_10-Speed CVT  \\\n",
              "0                                        0                                  0   \n",
              "1                                        0                                  0   \n",
              "2                                        0                                  0   \n",
              "3                                        0                                  0   \n",
              "4                                        0                                  0   \n",
              "\n",
              "   transmission_display_2-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_3-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_3-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_3-Speed Manual  \\\n",
              "0                                    0   \n",
              "1                                    0   \n",
              "2                                    0   \n",
              "3                                    0   \n",
              "4                                    0   \n",
              "\n",
              "   transmission_display_4-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_4-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_4-Speed CVT  transmission_display_4-Speed Dual Clutch  \\\n",
              "0                                 0                                         0   \n",
              "1                                 0                                         0   \n",
              "2                                 0                                         0   \n",
              "3                                 0                                         0   \n",
              "4                                 0                                         0   \n",
              "\n",
              "   transmission_display_4-Speed Manual  \\\n",
              "0                                    0   \n",
              "1                                    0   \n",
              "2                                    0   \n",
              "3                                    0   \n",
              "4                                    0   \n",
              "\n",
              "   transmission_display_4-Speed Manual Overdrive  \\\n",
              "0                                              0   \n",
              "1                                              0   \n",
              "2                                              0   \n",
              "3                                              0   \n",
              "4                                              0   \n",
              "\n",
              "   transmission_display_5-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_5-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_5-Speed Dual Clutch  \\\n",
              "0                                         0   \n",
              "1                                         0   \n",
              "2                                         0   \n",
              "3                                         0   \n",
              "4                                         0   \n",
              "\n",
              "   transmission_display_5-Speed Manual  \\\n",
              "0                                    0   \n",
              "1                                    0   \n",
              "2                                    0   \n",
              "3                                    0   \n",
              "4                                    0   \n",
              "\n",
              "   transmission_display_5-Speed Manual Overdrive  \\\n",
              "0                                              0   \n",
              "1                                              0   \n",
              "2                                              0   \n",
              "3                                              0   \n",
              "4                                              0   \n",
              "\n",
              "   transmission_display_6-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_6-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_6-Speed CVT  transmission_display_6-Speed Dual Clutch  \\\n",
              "0                                 0                                         0   \n",
              "1                                 0                                         0   \n",
              "2                                 0                                         0   \n",
              "3                                 0                                         0   \n",
              "4                                 0                                         0   \n",
              "\n",
              "   transmission_display_6-Speed Manual  \\\n",
              "0                                    0   \n",
              "1                                    0   \n",
              "2                                    0   \n",
              "3                                    0   \n",
              "4                                    0   \n",
              "\n",
              "   transmission_display_6-Speed Manual Overdrive  \\\n",
              "0                                              0   \n",
              "1                                              0   \n",
              "2                                              0   \n",
              "3                                              0   \n",
              "4                                              0   \n",
              "\n",
              "   transmission_display_7-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_7-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_7-Speed CVT  transmission_display_7-Speed Dual Clutch  \\\n",
              "0                                 0                                         0   \n",
              "1                                 0                                         0   \n",
              "2                                 0                                         0   \n",
              "3                                 0                                         0   \n",
              "4                                 0                                         0   \n",
              "\n",
              "   transmission_display_7-Speed Manual  \\\n",
              "0                                    0   \n",
              "1                                    0   \n",
              "2                                    0   \n",
              "3                                    0   \n",
              "4                                    0   \n",
              "\n",
              "   transmission_display_7-Speed Manual Overdrive  \\\n",
              "0                                              0   \n",
              "1                                              0   \n",
              "2                                              0   \n",
              "3                                              0   \n",
              "4                                              0   \n",
              "\n",
              "   transmission_display_8-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       1   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_8-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_8-Speed CVT  transmission_display_8-Speed Dual Clutch  \\\n",
              "0                                 0                                         0   \n",
              "1                                 0                                         0   \n",
              "2                                 0                                         0   \n",
              "3                                 0                                         0   \n",
              "4                                 0                                         0   \n",
              "\n",
              "   transmission_display_8-Speed Manual  \\\n",
              "0                                    0   \n",
              "1                                    0   \n",
              "2                                    0   \n",
              "3                                    0   \n",
              "4                                    0   \n",
              "\n",
              "   transmission_display_9-Speed Automatic  \\\n",
              "0                                       0   \n",
              "1                                       0   \n",
              "2                                       0   \n",
              "3                                       0   \n",
              "4                                       0   \n",
              "\n",
              "   transmission_display_9-Speed Automatic Overdrive  \\\n",
              "0                                                 0   \n",
              "1                                                 0   \n",
              "2                                                 0   \n",
              "3                                                 0   \n",
              "4                                                 0   \n",
              "\n",
              "   transmission_display_Automatic  \\\n",
              "0                               1   \n",
              "1                               1   \n",
              "2                               1   \n",
              "3                               0   \n",
              "4                               1   \n",
              "\n",
              "   transmission_display_Continuously Variable Transmission  \\\n",
              "0                                                  0         \n",
              "1                                                  0         \n",
              "2                                                  0         \n",
              "3                                                  0         \n",
              "4                                                  0         \n",
              "\n",
              "   transmission_display_Manual  wheel_system_4WD  wheel_system_4X2  \\\n",
              "0                            0                 0                 0   \n",
              "1                            0                 0                 0   \n",
              "2                            0                 0                 0   \n",
              "3                            0                 0                 0   \n",
              "4                            0                 0                 0   \n",
              "\n",
              "   wheel_system_AWD  wheel_system_FWD  wheel_system_RWD  \n",
              "0                 0                 0                 0  \n",
              "1                 0                 0                 0  \n",
              "2                 0                 0                 0  \n",
              "3                 0                 0                 0  \n",
              "4                 0                 0                 0  "
            ],
            "text/html": [
              "\n",
              "  <div id=\"df-b73f4da4-547a-4503-b790-7e856127cf53\">\n",
              "    <div class=\"colab-df-container\">\n",
              "      <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>city_fuel_economy</th>\n",
              "      <th>daysonmarket</th>\n",
              "      <th>franchise_dealer</th>\n",
              "      <th>fuel_tank_volume</th>\n",
              "      <th>has_accidents</th>\n",
              "      <th>height</th>\n",
              "      <th>highway_fuel_economy</th>\n",
              "      <th>horsepower</th>\n",
              "      <th>is_new</th>\n",
              "      <th>latitude</th>\n",
              "      <th>length</th>\n",
              "      <th>longitude</th>\n",
              "      <th>maximum_seating</th>\n",
              "      <th>mileage</th>\n",
              "      <th>price</th>\n",
              "      <th>savings_amount</th>\n",
              "      <th>seller_rating</th>\n",
              "      <th>wheelbase</th>\n",
              "      <th>width</th>\n",
              "      <th>year</th>\n",
              "      <th>legroom</th>\n",
              "      <th>major_options_count</th>\n",
              "      <th>body_type_Convertible</th>\n",
              "      <th>body_type_Coupe</th>\n",
              "      <th>body_type_Hatchback</th>\n",
              "      <th>body_type_Minivan</th>\n",
              "      <th>body_type_Pickup Truck</th>\n",
              "      <th>body_type_SUV / Crossover</th>\n",
              "      <th>body_type_Sedan</th>\n",
              "      <th>body_type_Unknown</th>\n",
              "      <th>body_type_Van</th>\n",
              "      <th>body_type_Wagon</th>\n",
              "      <th>engine_type_H4</th>\n",
              "      <th>engine_type_H4 Hybrid</th>\n",
              "      <th>engine_type_H6</th>\n",
              "      <th>engine_type_I2</th>\n",
              "      <th>engine_type_I3</th>\n",
              "      <th>engine_type_I3 Hybrid</th>\n",
              "      <th>engine_type_I4</th>\n",
              "      <th>engine_type_I4 Compressed Natural Gas</th>\n",
              "      <th>engine_type_I4 Diesel</th>\n",
              "      <th>engine_type_I4 Flex Fuel Vehicle</th>\n",
              "      <th>engine_type_I4 Hybrid</th>\n",
              "      <th>engine_type_I5</th>\n",
              "      <th>engine_type_I5 Biodiesel</th>\n",
              "      <th>engine_type_I5 Diesel</th>\n",
              "      <th>engine_type_I6</th>\n",
              "      <th>engine_type_I6 Diesel</th>\n",
              "      <th>engine_type_I6 Hybrid</th>\n",
              "      <th>engine_type_R2</th>\n",
              "      <th>engine_type_V10</th>\n",
              "      <th>engine_type_V10 Diesel</th>\n",
              "      <th>engine_type_V12</th>\n",
              "      <th>engine_type_V6</th>\n",
              "      <th>engine_type_V6 Biodiesel</th>\n",
              "      <th>engine_type_V6 Compressed Natural Gas</th>\n",
              "      <th>engine_type_V6 Diesel</th>\n",
              "      <th>engine_type_V6 Flex Fuel Vehicle</th>\n",
              "      <th>engine_type_V6 Hybrid</th>\n",
              "      <th>engine_type_V8</th>\n",
              "      <th>engine_type_V8 Biodiesel</th>\n",
              "      <th>engine_type_V8 Compressed Natural Gas</th>\n",
              "      <th>engine_type_V8 Diesel</th>\n",
              "      <th>engine_type_V8 Flex Fuel Vehicle</th>\n",
              "      <th>engine_type_V8 Hybrid</th>\n",
              "      <th>engine_type_V8 Propane</th>\n",
              "      <th>engine_type_W12</th>\n",
              "      <th>engine_type_W12 Flex Fuel Vehicle</th>\n",
              "      <th>engine_type_W16</th>\n",
              "      <th>engine_type_W8</th>\n",
              "      <th>fuel_type_Biodiesel</th>\n",
              "      <th>fuel_type_Compressed Natural Gas</th>\n",
              "      <th>fuel_type_Diesel</th>\n",
              "      <th>fuel_type_Electric</th>\n",
              "      <th>fuel_type_Flex Fuel Vehicle</th>\n",
              "      <th>fuel_type_Gasoline</th>\n",
              "      <th>fuel_type_Hybrid</th>\n",
              "      <th>fuel_type_Propane</th>\n",
              "      <th>listing_color_BLACK</th>\n",
              "      <th>listing_color_BLUE</th>\n",
              "      <th>listing_color_BROWN</th>\n",
              "      <th>listing_color_GOLD</th>\n",
              "      <th>listing_color_GRAY</th>\n",
              "      <th>listing_color_GREEN</th>\n",
              "      <th>listing_color_ORANGE</th>\n",
              "      <th>listing_color_PINK</th>\n",
              "      <th>listing_color_PURPLE</th>\n",
              "      <th>listing_color_RED</th>\n",
              "      <th>listing_color_SILVER</th>\n",
              "      <th>listing_color_TEAL</th>\n",
              "      <th>listing_color_UNKNOWN</th>\n",
              "      <th>listing_color_WHITE</th>\n",
              "      <th>listing_color_YELLOW</th>\n",
              "      <th>transmission_A</th>\n",
              "      <th>transmission_CVT</th>\n",
              "      <th>transmission_Dual Clutch</th>\n",
              "      <th>transmission_M</th>\n",
              "      <th>transmission_display_1-Speed Automatic</th>\n",
              "      <th>transmission_display_1-Speed CVT</th>\n",
              "      <th>transmission_display_1-Speed Dual Clutch</th>\n",
              "      <th>transmission_display_10-Speed Automatic</th>\n",
              "      <th>transmission_display_10-Speed CVT</th>\n",
              "      <th>transmission_display_2-Speed Automatic</th>\n",
              "      <th>transmission_display_3-Speed Automatic</th>\n",
              "      <th>transmission_display_3-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_3-Speed Manual</th>\n",
              "      <th>transmission_display_4-Speed Automatic</th>\n",
              "      <th>transmission_display_4-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_4-Speed CVT</th>\n",
              "      <th>transmission_display_4-Speed Dual Clutch</th>\n",
              "      <th>transmission_display_4-Speed Manual</th>\n",
              "      <th>transmission_display_4-Speed Manual Overdrive</th>\n",
              "      <th>transmission_display_5-Speed Automatic</th>\n",
              "      <th>transmission_display_5-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_5-Speed Dual Clutch</th>\n",
              "      <th>transmission_display_5-Speed Manual</th>\n",
              "      <th>transmission_display_5-Speed Manual Overdrive</th>\n",
              "      <th>transmission_display_6-Speed Automatic</th>\n",
              "      <th>transmission_display_6-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_6-Speed CVT</th>\n",
              "      <th>transmission_display_6-Speed Dual Clutch</th>\n",
              "      <th>transmission_display_6-Speed Manual</th>\n",
              "      <th>transmission_display_6-Speed Manual Overdrive</th>\n",
              "      <th>transmission_display_7-Speed Automatic</th>\n",
              "      <th>transmission_display_7-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_7-Speed CVT</th>\n",
              "      <th>transmission_display_7-Speed Dual Clutch</th>\n",
              "      <th>transmission_display_7-Speed Manual</th>\n",
              "      <th>transmission_display_7-Speed Manual Overdrive</th>\n",
              "      <th>transmission_display_8-Speed Automatic</th>\n",
              "      <th>transmission_display_8-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_8-Speed CVT</th>\n",
              "      <th>transmission_display_8-Speed Dual Clutch</th>\n",
              "      <th>transmission_display_8-Speed Manual</th>\n",
              "      <th>transmission_display_9-Speed Automatic</th>\n",
              "      <th>transmission_display_9-Speed Automatic Overdrive</th>\n",
              "      <th>transmission_display_Automatic</th>\n",
              "      <th>transmission_display_Continuously Variable Transmission</th>\n",
              "      <th>transmission_display_Manual</th>\n",
              "      <th>wheel_system_4WD</th>\n",
              "      <th>wheel_system_4X2</th>\n",
              "      <th>wheel_system_AWD</th>\n",
              "      <th>wheel_system_FWD</th>\n",
              "      <th>wheel_system_RWD</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>22.365111</td>\n",
              "      <td>105</td>\n",
              "      <td>0</td>\n",
              "      <td>None</td>\n",
              "      <td>1</td>\n",
              "      <td>65.88426874843415</td>\n",
              "      <td>29.175567</td>\n",
              "      <td>170.0</td>\n",
              "      <td>0</td>\n",
              "      <td>42.0994</td>\n",
              "      <td>192.95317048549902</td>\n",
              "      <td>-70.9612</td>\n",
              "      <td>5.454915665353393</td>\n",
              "      <td>79499.0</td>\n",
              "      <td>36995.0</td>\n",
              "      <td>0</td>\n",
              "      <td>3.000000</td>\n",
              "      <td>114.79503438858377</td>\n",
              "      <td>78.30550440387708</td>\n",
              "      <td>2016</td>\n",
              "      <td>80.098705</td>\n",
              "      <td>1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>22.365111</td>\n",
              "      <td>78</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>65.88426874843415</td>\n",
              "      <td>29.175567</td>\n",
              "      <td>170.0</td>\n",
              "      <td>1</td>\n",
              "      <td>42.0889</td>\n",
              "      <td>192.95317048549902</td>\n",
              "      <td>-72.6383</td>\n",
              "      <td>5.454915665353393</td>\n",
              "      <td>120.0</td>\n",
              "      <td>68365.0</td>\n",
              "      <td>0</td>\n",
              "      <td>4.500000</td>\n",
              "      <td>114.79503438858377</td>\n",
              "      <td>78.30550440387708</td>\n",
              "      <td>2019</td>\n",
              "      <td>80.098705</td>\n",
              "      <td>-1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>22.365111</td>\n",
              "      <td>7</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>65.88426874843415</td>\n",
              "      <td>29.175567</td>\n",
              "      <td>170.0</td>\n",
              "      <td>1</td>\n",
              "      <td>33.9438</td>\n",
              "      <td>192.95317048549902</td>\n",
              "      <td>-83.4405</td>\n",
              "      <td>5.454915665353393</td>\n",
              "      <td>6.0</td>\n",
              "      <td>73745.0</td>\n",
              "      <td>0</td>\n",
              "      <td>4.500000</td>\n",
              "      <td>114.79503438858377</td>\n",
              "      <td>78.30550440387708</td>\n",
              "      <td>2021</td>\n",
              "      <td>80.098705</td>\n",
              "      <td>-1.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>22.365111</td>\n",
              "      <td>11</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>65.88426874843415</td>\n",
              "      <td>29.175567</td>\n",
              "      <td>170.0</td>\n",
              "      <td>1</td>\n",
              "      <td>33.8989</td>\n",
              "      <td>192.95317048549902</td>\n",
              "      <td>-84.4497</td>\n",
              "      <td>5.454915665353393</td>\n",
              "      <td>3.0</td>\n",
              "      <td>70770.0</td>\n",
              "      <td>0</td>\n",
              "      <td>4.395604</td>\n",
              "      <td>114.79503438858377</td>\n",
              "      <td>78.30550440387708</td>\n",
              "      <td>2021</td>\n",
              "      <td>80.098705</td>\n",
              "      <td>4.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>22.365111</td>\n",
              "      <td>161</td>\n",
              "      <td>1</td>\n",
              "      <td>None</td>\n",
              "      <td>0</td>\n",
              "      <td>65.88426874843415</td>\n",
              "      <td>29.175567</td>\n",
              "      <td>170.0</td>\n",
              "      <td>1</td>\n",
              "      <td>33.9084</td>\n",
              "      <td>192.95317048549902</td>\n",
              "      <td>-84.2919</td>\n",
              "      <td>5.454915665353393</td>\n",
              "      <td>5.0</td>\n",
              "      <td>70942.0</td>\n",
              "      <td>0</td>\n",
              "      <td>4.461538</td>\n",
              "      <td>114.79503438858377</td>\n",
              "      <td>78.30550440387708</td>\n",
              "      <td>2020</td>\n",
              "      <td>80.098705</td>\n",
              "      <td>4.0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>1</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "      <td>0</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>\n",
              "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b73f4da4-547a-4503-b790-7e856127cf53')\"\n",
              "              title=\"Convert this dataframe to an interactive table.\"\n",
              "              style=\"display:none;\">\n",
              "        \n",
              "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
              "       width=\"24px\">\n",
              "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
              "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
              "  </svg>\n",
              "      </button>\n",
              "      \n",
              "  <style>\n",
              "    .colab-df-container {\n",
              "      display:flex;\n",
              "      flex-wrap:wrap;\n",
              "      gap: 12px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert {\n",
              "      background-color: #E8F0FE;\n",
              "      border: none;\n",
              "      border-radius: 50%;\n",
              "      cursor: pointer;\n",
              "      display: none;\n",
              "      fill: #1967D2;\n",
              "      height: 32px;\n",
              "      padding: 0 0 0 0;\n",
              "      width: 32px;\n",
              "    }\n",
              "\n",
              "    .colab-df-convert:hover {\n",
              "      background-color: #E2EBFA;\n",
              "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
              "      fill: #174EA6;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert {\n",
              "      background-color: #3B4455;\n",
              "      fill: #D2E3FC;\n",
              "    }\n",
              "\n",
              "    [theme=dark] .colab-df-convert:hover {\n",
              "      background-color: #434B5C;\n",
              "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
              "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
              "      fill: #FFFFFF;\n",
              "    }\n",
              "  </style>\n",
              "\n",
              "      <script>\n",
              "        const buttonEl =\n",
              "          document.querySelector('#df-b73f4da4-547a-4503-b790-7e856127cf53 button.colab-df-convert');\n",
              "        buttonEl.style.display =\n",
              "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
              "\n",
              "        async function convertToInteractive(key) {\n",
              "          const element = document.querySelector('#df-b73f4da4-547a-4503-b790-7e856127cf53');\n",
              "          const dataTable =\n",
              "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
              "                                                     [key], {});\n",
              "          if (!dataTable) return;\n",
              "\n",
              "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
              "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
              "            + ' to learn more about interactive tables.';\n",
              "          element.innerHTML = '';\n",
              "          dataTable['output_type'] = 'display_data';\n",
              "          await google.colab.output.renderOutput(dataTable, element);\n",
              "          const docLink = document.createElement('div');\n",
              "          docLink.innerHTML = docLinkHtml;\n",
              "          element.appendChild(docLink);\n",
              "        }\n",
              "      </script>\n",
              "    </div>\n",
              "  </div>\n",
              "  "
            ]
          },
          "metadata": {},
          "execution_count": 41
        }
      ],
      "source": [
        "dummy_cols = ['body_type','engine_type', 'fuel_type', 'listing_color', 'transmission', 'transmission_display', 'wheel_system']\n",
        "dummy_df = pd.get_dummies(dummy, columns=dummy_cols)\n",
        "dummy_df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "NLJLT1KjW9i7"
      },
      "outputs": [],
      "source": [
        "#drop columns\n",
        "#vin, dealer_zip, exterior_color, interior_color, listing_id, model_name, power, sp_name, torque\ttrimId\ttrim_name\twheel_system_display, \n",
        "dummy_df = dummy_df.drop(['_c0', 'vin', 'city','dealer_zip', 'exterior_color', 'interior_color', 'listing_id', 'make_name','model_name', 'power', 'sp_name', 'torque',\t'trimId',\t'trim_name',\t'wheel_system_display', 'sp_id' ], axis = 1)\n",
        "dummy_df.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "JmfbdvvTcFkb"
      },
      "outputs": [],
      "source": [
        "data_reg = spark.createDataFrame(dummy_df)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "b4KnLQfIfoNU",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "adbb81e8-44b5-4837-db69-46ac6943ee03"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "['city_fuel_economy',\n",
              " 'daysonmarket',\n",
              " 'franchise_dealer',\n",
              " 'fuel_tank_volume',\n",
              " 'has_accidents',\n",
              " 'height',\n",
              " 'highway_fuel_economy',\n",
              " 'horsepower',\n",
              " 'is_new',\n",
              " 'latitude',\n",
              " 'length',\n",
              " 'longitude',\n",
              " 'maximum_seating',\n",
              " 'mileage',\n",
              " 'price',\n",
              " 'savings_amount',\n",
              " 'seller_rating',\n",
              " 'wheelbase',\n",
              " 'width',\n",
              " 'year',\n",
              " 'legroom',\n",
              " 'major_options_count',\n",
              " 'body_type_Convertible',\n",
              " 'body_type_Coupe',\n",
              " 'body_type_Hatchback',\n",
              " 'body_type_Minivan',\n",
              " 'body_type_Pickup Truck',\n",
              " 'body_type_SUV / Crossover',\n",
              " 'body_type_Sedan',\n",
              " 'body_type_Unknown',\n",
              " 'body_type_Van',\n",
              " 'body_type_Wagon',\n",
              " 'engine_type_H4',\n",
              " 'engine_type_H4 Hybrid',\n",
              " 'engine_type_H6',\n",
              " 'engine_type_I2',\n",
              " 'engine_type_I3',\n",
              " 'engine_type_I3 Hybrid',\n",
              " 'engine_type_I4',\n",
              " 'engine_type_I4 Compressed Natural Gas',\n",
              " 'engine_type_I4 Diesel',\n",
              " 'engine_type_I4 Flex Fuel Vehicle',\n",
              " 'engine_type_I4 Hybrid',\n",
              " 'engine_type_I5',\n",
              " 'engine_type_I5 Biodiesel',\n",
              " 'engine_type_I5 Diesel',\n",
              " 'engine_type_I6',\n",
              " 'engine_type_I6 Diesel',\n",
              " 'engine_type_I6 Hybrid',\n",
              " 'engine_type_R2',\n",
              " 'engine_type_V10',\n",
              " 'engine_type_V10 Diesel',\n",
              " 'engine_type_V12',\n",
              " 'engine_type_V6',\n",
              " 'engine_type_V6 Biodiesel',\n",
              " 'engine_type_V6 Compressed Natural Gas',\n",
              " 'engine_type_V6 Diesel',\n",
              " 'engine_type_V6 Flex Fuel Vehicle',\n",
              " 'engine_type_V6 Hybrid',\n",
              " 'engine_type_V8',\n",
              " 'engine_type_V8 Biodiesel',\n",
              " 'engine_type_V8 Compressed Natural Gas',\n",
              " 'engine_type_V8 Diesel',\n",
              " 'engine_type_V8 Flex Fuel Vehicle',\n",
              " 'engine_type_V8 Hybrid',\n",
              " 'engine_type_V8 Propane',\n",
              " 'engine_type_W12',\n",
              " 'engine_type_W12 Flex Fuel Vehicle',\n",
              " 'engine_type_W16',\n",
              " 'engine_type_W8',\n",
              " 'fuel_type_Biodiesel',\n",
              " 'fuel_type_Compressed Natural Gas',\n",
              " 'fuel_type_Diesel',\n",
              " 'fuel_type_Electric',\n",
              " 'fuel_type_Flex Fuel Vehicle',\n",
              " 'fuel_type_Gasoline',\n",
              " 'fuel_type_Hybrid',\n",
              " 'fuel_type_Propane',\n",
              " 'listing_color_BLACK',\n",
              " 'listing_color_BLUE',\n",
              " 'listing_color_BROWN',\n",
              " 'listing_color_GOLD',\n",
              " 'listing_color_GRAY',\n",
              " 'listing_color_GREEN',\n",
              " 'listing_color_ORANGE',\n",
              " 'listing_color_PINK',\n",
              " 'listing_color_PURPLE',\n",
              " 'listing_color_RED',\n",
              " 'listing_color_SILVER',\n",
              " 'listing_color_TEAL',\n",
              " 'listing_color_UNKNOWN',\n",
              " 'listing_color_WHITE',\n",
              " 'listing_color_YELLOW',\n",
              " 'transmission_A',\n",
              " 'transmission_CVT',\n",
              " 'transmission_Dual Clutch',\n",
              " 'transmission_M',\n",
              " 'transmission_display_1-Speed Automatic',\n",
              " 'transmission_display_1-Speed CVT',\n",
              " 'transmission_display_1-Speed Dual Clutch',\n",
              " 'transmission_display_10-Speed Automatic',\n",
              " 'transmission_display_10-Speed CVT',\n",
              " 'transmission_display_2-Speed Automatic',\n",
              " 'transmission_display_3-Speed Automatic',\n",
              " 'transmission_display_3-Speed Automatic Overdrive',\n",
              " 'transmission_display_3-Speed Manual',\n",
              " 'transmission_display_4-Speed Automatic',\n",
              " 'transmission_display_4-Speed Automatic Overdrive',\n",
              " 'transmission_display_4-Speed CVT',\n",
              " 'transmission_display_4-Speed Dual Clutch',\n",
              " 'transmission_display_4-Speed Manual',\n",
              " 'transmission_display_4-Speed Manual Overdrive',\n",
              " 'transmission_display_5-Speed Automatic',\n",
              " 'transmission_display_5-Speed Automatic Overdrive',\n",
              " 'transmission_display_5-Speed Dual Clutch',\n",
              " 'transmission_display_5-Speed Manual',\n",
              " 'transmission_display_5-Speed Manual Overdrive',\n",
              " 'transmission_display_6-Speed Automatic',\n",
              " 'transmission_display_6-Speed Automatic Overdrive',\n",
              " 'transmission_display_6-Speed CVT',\n",
              " 'transmission_display_6-Speed Dual Clutch',\n",
              " 'transmission_display_6-Speed Manual',\n",
              " 'transmission_display_6-Speed Manual Overdrive',\n",
              " 'transmission_display_7-Speed Automatic',\n",
              " 'transmission_display_7-Speed Automatic Overdrive',\n",
              " 'transmission_display_7-Speed CVT',\n",
              " 'transmission_display_7-Speed Dual Clutch',\n",
              " 'transmission_display_7-Speed Manual',\n",
              " 'transmission_display_7-Speed Manual Overdrive',\n",
              " 'transmission_display_8-Speed Automatic',\n",
              " 'transmission_display_8-Speed Automatic Overdrive',\n",
              " 'transmission_display_8-Speed CVT',\n",
              " 'transmission_display_8-Speed Dual Clutch',\n",
              " 'transmission_display_8-Speed Manual',\n",
              " 'transmission_display_9-Speed Automatic',\n",
              " 'transmission_display_9-Speed Automatic Overdrive',\n",
              " 'transmission_display_Automatic',\n",
              " 'transmission_display_Continuously Variable Transmission',\n",
              " 'transmission_display_Manual',\n",
              " 'wheel_system_4WD',\n",
              " 'wheel_system_4X2',\n",
              " 'wheel_system_AWD',\n",
              " 'wheel_system_FWD',\n",
              " 'wheel_system_RWD']"
            ]
          },
          "metadata": {},
          "execution_count": 43
        }
      ],
      "source": [
        "data_reg.columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PzIsVxRcfWh0"
      },
      "outputs": [],
      "source": [
        "columns_list = ['city_fuel_economy',\n",
        " 'daysonmarket',\n",
        " 'franchise_dealer',\n",
        " 'fuel_tank_volume',\n",
        " 'has_accidents',\n",
        " 'height',\n",
        " 'highway_fuel_economy',\n",
        " 'horsepower',\n",
        " 'is_new',\n",
        " 'latitude',\n",
        " 'length',\n",
        " 'longitude',\n",
        " 'maximum_seating',\n",
        " 'mileage',\n",
        " 'price',\n",
        " 'savings_amount',\n",
        " 'seller_rating',\n",
        " 'wheelbase',\n",
        " 'width',\n",
        " 'year',\n",
        " 'legroom',\n",
        " 'major_options_count']\n",
        "for col in columns_list:\n",
        "  data_reg = data_reg.withColumn(col, data_reg[col].cast('Integer'))\n",
        "     "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "UD_C8WhdrRqP"
      },
      "outputs": [],
      "source": [
        "data_reg = data_reg.na.fill(0)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "Llv5ira7f3W6",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 478
        },
        "outputId": "a788037c-8d7e-4bff-e835-3a02d8462d93"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "ERROR:root:KeyboardInterrupt while sending command.\n",
            "Traceback (most recent call last):\n",
            "  File \"/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\", line 1038, in send_command\n",
            "    response = connection.send_command(command)\n",
            "  File \"/usr/local/lib/python3.8/dist-packages/py4j/clientserver.py\", line 511, in send_command\n",
            "    answer = smart_decode(self.stream.readline()[:-1])\n",
            "  File \"/usr/lib/python3.8/socket.py\", line 669, in readinto\n",
            "    return self._sock.recv_into(b)\n",
            "KeyboardInterrupt\n"
          ]
        },
        {
          "output_type": "error",
          "ename": "KeyboardInterrupt",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-46-feb2be0f5f97>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdata_reg\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshow\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m20\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pyspark/sql/dataframe.py\u001b[0m in \u001b[0;36mshow\u001b[0;34m(self, n, truncate, vertical)\u001b[0m\n\u001b[1;32m    604\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    605\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtruncate\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbool\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mtruncate\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 606\u001b[0;31m             \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_jdf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshowString\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m20\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvertical\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    607\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    608\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m   1318\u001b[0m             \u001b[0mproto\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mEND_COMMAND_PART\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1319\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1320\u001b[0;31m         \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgateway_client\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1321\u001b[0m         return_value = get_return_value(\n\u001b[1;32m   1322\u001b[0m             answer, self.gateway_client, self.target_id, self.name)\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/java_gateway.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command, retry, binary)\u001b[0m\n\u001b[1;32m   1036\u001b[0m         \u001b[0mconnection\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1037\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1038\u001b[0;31m             \u001b[0mresponse\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msend_command\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcommand\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1039\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mbinary\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1040\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_create_connection_guard\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/py4j/clientserver.py\u001b[0m in \u001b[0;36msend_command\u001b[0;34m(self, command)\u001b[0m\n\u001b[1;32m    509\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    510\u001b[0m             \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 511\u001b[0;31m                 \u001b[0manswer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msmart_decode\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstream\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreadline\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m-\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    512\u001b[0m                 \u001b[0mlogger\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdebug\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Answer received: {0}\"\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0manswer\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    513\u001b[0m                 \u001b[0;31m# Happens when a the other end is dead. There might be an empty\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/lib/python3.8/socket.py\u001b[0m in \u001b[0;36mreadinto\u001b[0;34m(self, b)\u001b[0m\n\u001b[1;32m    667\u001b[0m         \u001b[0;32mwhile\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    668\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 669\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_sock\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrecv_into\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    670\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0mtimeout\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    671\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_timeout_occurred\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
          ]
        }
      ],
      "source": [
        "data_reg.show(20)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-Px6rmd0lVhI"
      },
      "outputs": [],
      "source": [
        "from pyspark.ml import Pipeline\n",
        "from pyspark.ml.feature import VectorAssembler, StandardScaler\n",
        "from pyspark.ml.evaluation import RegressionEvaluator\n",
        "from pyspark.ml.evaluation import BinaryClassificationEvaluator"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "o3PZqMpnl6zP"
      },
      "outputs": [],
      "source": [
        "input_cols = ['city_fuel_economy',\n",
        " 'daysonmarket',\n",
        " 'franchise_dealer',\n",
        " 'fuel_tank_volume',\n",
        " 'has_accidents',\n",
        " 'height',\n",
        " 'highway_fuel_economy',\n",
        " 'horsepower',\n",
        " 'is_new',\n",
        " 'latitude',\n",
        " 'length',\n",
        " 'longitude',\n",
        " 'maximum_seating',\n",
        " 'mileage',\n",
        " 'savings_amount',\n",
        " 'seller_rating',\n",
        " 'wheelbase',\n",
        " 'width',\n",
        " 'year',\n",
        " 'legroom',\n",
        " 'major_options_count',\n",
        " 'body_type_Convertible',\n",
        " 'body_type_Coupe',\n",
        " 'body_type_Hatchback',\n",
        " 'body_type_Minivan',\n",
        " 'body_type_Pickup Truck',\n",
        " 'body_type_SUV / Crossover',\n",
        " 'body_type_Sedan',\n",
        " 'body_type_Unknown',\n",
        " 'body_type_Van',\n",
        " 'body_type_Wagon',\n",
        " 'engine_type_H4',\n",
        " 'engine_type_I3',\n",
        " 'engine_type_I4',\n",
        " 'engine_type_I4 Diesel',\n",
        " 'engine_type_I4 Flex Fuel Vehicle',\n",
        " 'engine_type_I4 Hybrid',\n",
        " 'engine_type_I5',\n",
        " 'engine_type_I6',\n",
        " 'engine_type_I6 Diesel',\n",
        " 'engine_type_R2',\n",
        " 'engine_type_V10',\n",
        " 'engine_type_V12',\n",
        " 'engine_type_V6',\n",
        " 'engine_type_V6 Diesel',\n",
        " 'engine_type_V6 Flex Fuel Vehicle',\n",
        " 'engine_type_V6 Hybrid',\n",
        " 'engine_type_V8',\n",
        " 'engine_type_V8 Biodiesel',\n",
        " 'engine_type_V8 Diesel',\n",
        " 'engine_type_V8 Flex Fuel Vehicle',\n",
        " 'engine_type_W12',\n",
        " 'fuel_type_Biodiesel',\n",
        " 'fuel_type_Diesel',\n",
        " 'fuel_type_Electric',\n",
        " 'fuel_type_Flex Fuel Vehicle',\n",
        " 'fuel_type_Gasoline',\n",
        " 'fuel_type_Hybrid',\n",
        " 'listing_color_BLACK',\n",
        " 'listing_color_BLUE',\n",
        " 'listing_color_BROWN',\n",
        " 'listing_color_GOLD',\n",
        " 'listing_color_GRAY',\n",
        " 'listing_color_GREEN',\n",
        " 'listing_color_ORANGE',\n",
        " 'listing_color_PURPLE',\n",
        " 'listing_color_RED',\n",
        " 'listing_color_SILVER',\n",
        " 'listing_color_TEAL',\n",
        " 'listing_color_UNKNOWN',\n",
        " 'listing_color_WHITE',\n",
        " 'listing_color_YELLOW',\n",
        " 'transmission_A',\n",
        " 'transmission_CVT',\n",
        " 'transmission_Dual Clutch',\n",
        " 'transmission_M',\n",
        " 'transmission_display_1-Speed Automatic',\n",
        " 'transmission_display_2-Speed Automatic',\n",
        " 'transmission_display_3-Speed Automatic',\n",
        " 'transmission_display_4-Speed Automatic',\n",
        " 'transmission_display_4-Speed Automatic Overdrive',\n",
        " 'transmission_display_5-Speed Automatic',\n",
        " 'transmission_display_5-Speed Automatic Overdrive',\n",
        " 'transmission_display_5-Speed Manual',\n",
        " 'transmission_display_6-Speed Automatic',\n",
        " 'transmission_display_6-Speed Automatic Overdrive',\n",
        " 'transmission_display_6-Speed Dual Clutch',\n",
        " 'transmission_display_6-Speed Manual',\n",
        " 'transmission_display_6-Speed Manual Overdrive',\n",
        " 'transmission_display_7-Speed Automatic',\n",
        " 'transmission_display_7-Speed CVT',\n",
        " 'transmission_display_7-Speed Dual Clutch',\n",
        " 'transmission_display_8-Speed Automatic',\n",
        " 'transmission_display_8-Speed Automatic Overdrive',\n",
        " 'transmission_display_9-Speed Automatic',\n",
        " 'transmission_display_9-Speed Automatic Overdrive',\n",
        " 'transmission_display_Automatic',\n",
        " 'transmission_display_Continuously Variable Transmission',\n",
        " 'transmission_display_Manual',\n",
        " 'wheel_system_4WD',\n",
        " 'wheel_system_4X2',\n",
        " 'wheel_system_AWD',\n",
        " 'wheel_system_FWD',\n",
        " 'wheel_system_RWD']\n",
        "vectorAssembler = VectorAssembler(inputCols = input_cols, outputCol = 'features')\n",
        "vdata_reg = vectorAssembler.transform(data_reg)\n",
        "vdata_reg = vdata_reg.select(['features', 'price'])\n",
        "#vdata_reg.show(10)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "buF1vjN-f5PR"
      },
      "outputs": [],
      "source": [
        "#Splitting the dataset \n",
        "splits = vdata_reg.randomSplit([0.7, 0.3])\n",
        "train_df = splits[0]\n",
        "test_df = splits[1]     "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "RZ5nNk2PnxQJ"
      },
      "outputs": [],
      "source": [
        "from pyspark.ml.regression import LinearRegression\n",
        "lr = LinearRegression(featuresCol = 'features', labelCol='price', maxIter=10, regParam=0.3, elasticNetParam=0.8)\n",
        "lr_model = lr.fit(train_df)\n",
        "print(\"Coefficients: \" + str(lr_model.coefficients))\n",
        "print(\"Intercept: \" + str(lr_model.intercept))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ri0GQzejsIri",
        "outputId": "a4848bcb-ef00-4048-edbe-93595b252f5f"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "RMSE: 11798.249436\n",
            "r2: 0.690410\n"
          ]
        }
      ],
      "source": [
        "trainingSummary = lr_model.summary\n",
        "print(\"RMSE: %f\" % trainingSummary.rootMeanSquaredError)\n",
        "print(\"r2: %f\" % trainingSummary.r2)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "009H9DtXsJLv",
        "outputId": "20cdeecc-ef95-4d8e-8d29-a7eb16b9706b"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+------------------+-----+--------------------+\n",
            "|        prediction|price|            features|\n",
            "+------------------+-----+--------------------+\n",
            "|118053.74002655968|82980|(149,[0,1,2,3,4,5...|\n",
            "| 23797.74296258227|17600|(149,[0,1,2,3,4,5...|\n",
            "| 24462.97811441496|10969|(149,[0,1,2,3,4,5...|\n",
            "|38109.741744517814|23500|(149,[0,1,2,3,4,5...|\n",
            "| 38129.89629907068|28778|(149,[0,1,2,3,4,5...|\n",
            "+------------------+-----+--------------------+\n",
            "only showing top 5 rows\n",
            "\n",
            "R Squared (R2) on test data = 0.648903\n"
          ]
        }
      ],
      "source": [
        "lr_predictions = lr_model.transform(test_df)\n",
        "lr_predictions.select(\"prediction\",\"price\",\"features\").show(5)\n",
        "from pyspark.ml.evaluation import RegressionEvaluator\n",
        "lr_evaluator = RegressionEvaluator(predictionCol=\"prediction\", \\\n",
        "                 labelCol=\"price\",metricName=\"r2\")\n",
        "print(\"R Squared (R2) on test data = %g\" % lr_evaluator.evaluate(lr_predictions))"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "M-62Jlf3shN6",
        "outputId": "e0afa704-c448-441b-839e-95b32971075b"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Root Mean Squared Error (RMSE) on test data = 13458.2\n"
          ]
        }
      ],
      "source": [
        "test_result = lr_model.evaluate(test_df)\n",
        "print(\"Root Mean Squared Error (RMSE) on test data = %g\" % test_result.rootMeanSquaredError)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "MzAKTLHYshp2",
        "outputId": "19710639-bce2-4b24-df73-41f13860dd01"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+------------------+-----+--------------------+\n",
            "|        prediction|price|            features|\n",
            "+------------------+-----+--------------------+\n",
            "|118053.74002655968|82980|(149,[0,1,2,3,4,5...|\n",
            "| 23797.74296258227|17600|(149,[0,1,2,3,4,5...|\n",
            "| 24462.97811441496|10969|(149,[0,1,2,3,4,5...|\n",
            "|38109.741744517814|23500|(149,[0,1,2,3,4,5...|\n",
            "| 38129.89629907068|28778|(149,[0,1,2,3,4,5...|\n",
            "|42100.182450144785|30500|(149,[0,1,2,3,4,5...|\n",
            "| 2866.174145102501| 5800|(149,[0,1,2,3,4,5...|\n",
            "|12743.750435486203| 6888|(149,[0,1,2,3,4,5...|\n",
            "|16021.165657255799|16432|(149,[0,1,2,3,4,5...|\n",
            "|14650.719623595709|17980|(149,[0,1,2,3,4,5...|\n",
            "| 14499.15415922366|17926|(149,[0,1,2,3,4,5...|\n",
            "|17155.402737981174|22991|(149,[0,1,2,3,4,5...|\n",
            "| 25805.63406400825|28711|(149,[0,1,2,3,4,5...|\n",
            "| 17402.47273917636|13990|(149,[0,1,2,3,4,5...|\n",
            "|25010.154622446746|19995|(149,[0,1,2,3,4,5...|\n",
            "|16347.005683500087|15480|(149,[0,1,2,3,4,5...|\n",
            "| 26129.29545766604|20369|(149,[0,1,2,3,4,5...|\n",
            "|23879.640407619067|22495|(149,[0,1,2,3,4,5...|\n",
            "|27390.429085957585|31881|(149,[0,1,2,3,4,5...|\n",
            "| 26987.21653714939|25988|(149,[0,1,2,3,4,5...|\n",
            "+------------------+-----+--------------------+\n",
            "only showing top 20 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "predictions = lr_model.transform(test_df)\n",
        "predictions.select(\"prediction\",\"price\",\"features\").show()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "uIXKJbiJspYA"
      },
      "outputs": [],
      "source": [
        "# Decision tree"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "zzLaiCwYsvkg",
        "outputId": "c5c40a82-663a-4937-bb4d-e86c6760e46b"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Root Mean Squared Error (RMSE) on test data = 15191.3\n"
          ]
        }
      ],
      "source": [
        "from pyspark.ml.regression import DecisionTreeRegressor\n",
        "dt = DecisionTreeRegressor(featuresCol ='features', labelCol = 'price')\n",
        "dt_model = dt.fit(train_df)\n",
        "dt_predictions = dt_model.transform(test_df)\n",
        "dt_evaluator = RegressionEvaluator(\n",
        "    labelCol=\"price\", predictionCol=\"prediction\", metricName=\"rmse\")\n",
        "rmse = dt_evaluator.evaluate(dt_predictions)\n",
        "print(\"Root Mean Squared Error (RMSE) on test data = %g\" % rmse)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "AnbDZDxKszG3",
        "outputId": "6a99f5d1-2a83-4660-f4cf-e09e1f200202"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "SparseVector(149, {3: 0.0332, 7: 0.3754, 13: 0.3123, 18: 0.0187, 19: 0.0253, 20: 0.0159, 26: 0.0049, 38: 0.0108, 42: 0.0441, 83: 0.1559, 147: 0.0034})"
            ]
          },
          "execution_count": 55,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "dt_model.featureImportances"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "F-i3R1rjs950"
      },
      "outputs": [],
      "source": [
        "d = data_reg.take(1)\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "c8ZjkbQQZ_qD"
      },
      "outputs": [],
      "source": [
        "a = data_reg.toPandas()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "QYJLwt4Rbbev",
        "outputId": "a4765a5b-fd7e-4ef4-db20-43b0e32baf8c"
      },
      "outputs": [
        {
          "data": {
            "text/plain": [
              "0        0\n",
              "1        0\n",
              "2        0\n",
              "3        0\n",
              "4        0\n",
              "        ..\n",
              "49995    0\n",
              "49996    0\n",
              "49997    0\n",
              "49998    0\n",
              "49999    0\n",
              "Name: make_name_Alfa Romeo, Length: 50000, dtype: int64"
            ]
          },
          "execution_count": 90,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "a.iloc[:,82]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "1LFVBwwktGl-"
      },
      "outputs": [],
      "source": [
        "# gboost"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "GOUhznD5uuhZ",
        "outputId": "58f023ec-cadb-41d5-d141-d43fa825535a"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "+------------------+-----+--------------------+\n",
            "|        prediction|price|            features|\n",
            "+------------------+-----+--------------------+\n",
            "| 72491.85175068559|82980|(149,[0,1,2,3,4,5...|\n",
            "|14654.013965511542|17600|(149,[0,1,2,3,4,5...|\n",
            "| 7080.732430647494|10969|(149,[0,1,2,3,4,5...|\n",
            "|29691.781854025496|23500|(149,[0,1,2,3,4,5...|\n",
            "|  43027.4566443404|28778|(149,[0,1,2,3,4,5...|\n",
            "+------------------+-----+--------------------+\n",
            "only showing top 5 rows\n",
            "\n"
          ]
        }
      ],
      "source": [
        "from pyspark.ml.regression import GBTRegressor\n",
        "gbt = GBTRegressor(featuresCol = 'features', labelCol = 'price', maxIter=10)\n",
        "gbt_model = gbt.fit(train_df)\n",
        "gbt_predictions = gbt_model.transform(test_df)\n",
        "gbt_predictions.select('prediction', 'price', 'features').show(5)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "d7hXvqEtuxTs",
        "outputId": "4f3185d8-8a54-499a-a3ee-e9250bac274a"
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "Root Mean Squared Error (RMSE) on test data = 13636\n"
          ]
        }
      ],
      "source": [
        "gbt_evaluator = RegressionEvaluator(\n",
        "    labelCol=\"price\", predictionCol=\"prediction\", metricName=\"rmse\")\n",
        "rmse = gbt_evaluator.evaluate(gbt_predictions)\n",
        "print(\"Root Mean Squared Error (RMSE) on test data = %g\" % rmse)"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [
        "xKYp69Hk8xfM"
      ],
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.8.8"
    },
    "vscode": {
      "interpreter": {
        "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}