Predict the Cost of Used Cars.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Predicting The Costs Of Used Cars - Hackathon By Imarticus Learning\n",
    "\n",
    "FEATURES:\n",
    "\n",
    "    Name: The brand and model of the car.\n",
    "    Location: The location in which the car is being sold or is available for purchase.\n",
    "    Year: The year or edition of the model.\n",
    "    Kilometers_Driven: The total kilometres driven in the car by the previous owner(s) in KM.\n",
    "    Fuel_Type: The type of fuel used by the car.\n",
    "    Transmission: The type of transmission used by the car.\n",
    "    Owner_Type: Whether the ownership is Firsthand, Second hand or other.\n",
    "    Mileage: The standard mileage offered by the car company in kmpl or km/kg\n",
    "    Engine: The displacement volume of the engine in cc.\n",
    "    Power: The maximum power of the engine in bhp.\n",
    "    Seats: The number of seats in the car.\n",
    "    New_Price: The price of a new car of the same model.\n",
    "    Price: The price of the used car in INR Lakhs."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import warnings\n",
    "import seaborn as sns\n",
    "sns.set_style('darkgrid')\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.read_excel('Data_Train.xlsx')\n",
    "test = pd.read_excel('Data_Test.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((6019, 13), (1234, 12))"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.shape, test.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(0, 1)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.duplicated().sum(), test.duplicated().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Location</th>\n",
       "      <th>Year</th>\n",
       "      <th>Kilometers_Driven</th>\n",
       "      <th>Fuel_Type</th>\n",
       "      <th>Transmission</th>\n",
       "      <th>Owner_Type</th>\n",
       "      <th>Mileage</th>\n",
       "      <th>Engine</th>\n",
       "      <th>Power</th>\n",
       "      <th>Seats</th>\n",
       "      <th>New_Price</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maruti Wagon R LXI CNG</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>2010</td>\n",
       "      <td>72000</td>\n",
       "      <td>CNG</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>26.6 km/kg</td>\n",
       "      <td>998 CC</td>\n",
       "      <td>58.16 bhp</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hyundai Creta 1.6 CRDi SX Option</td>\n",
       "      <td>Pune</td>\n",
       "      <td>2015</td>\n",
       "      <td>41000</td>\n",
       "      <td>Diesel</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>19.67 kmpl</td>\n",
       "      <td>1582 CC</td>\n",
       "      <td>126.2 bhp</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Name Location  Year  Kilometers_Driven  \\\n",
       "0            Maruti Wagon R LXI CNG   Mumbai  2010              72000   \n",
       "1  Hyundai Creta 1.6 CRDi SX Option     Pune  2015              41000   \n",
       "\n",
       "  Fuel_Type Transmission Owner_Type     Mileage   Engine      Power  Seats  \\\n",
       "0       CNG       Manual      First  26.6 km/kg   998 CC  58.16 bhp    5.0   \n",
       "1    Diesel       Manual      First  19.67 kmpl  1582 CC  126.2 bhp    5.0   \n",
       "\n",
       "  New_Price  Price  \n",
       "0       NaN   1.75  \n",
       "1       NaN  12.50  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 6019 entries, 0 to 6018\n",
      "Data columns (total 13 columns):\n",
      "Name                 6019 non-null object\n",
      "Location             6019 non-null object\n",
      "Year                 6019 non-null int64\n",
      "Kilometers_Driven    6019 non-null int64\n",
      "Fuel_Type            6019 non-null object\n",
      "Transmission         6019 non-null object\n",
      "Owner_Type           6019 non-null object\n",
      "Mileage              6017 non-null object\n",
      "Engine               5983 non-null object\n",
      "Power                5983 non-null object\n",
      "Seats                5977 non-null float64\n",
      "New_Price            824 non-null object\n",
      "Price                6019 non-null float64\n",
      "dtypes: float64(2), int64(2), object(9)\n",
      "memory usage: 611.4+ KB\n"
     ]
    }
   ],
   "source": [
    "train.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name                    0\n",
       "Location                0\n",
       "Year                    0\n",
       "Kilometers_Driven       0\n",
       "Fuel_Type               0\n",
       "Transmission            0\n",
       "Owner_Type              0\n",
       "Mileage                 2\n",
       "Engine                 36\n",
       "Power                  36\n",
       "Seats                  42\n",
       "New_Price            5195\n",
       "Price                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name                 1876\n",
       "Location               11\n",
       "Year                   22\n",
       "Kilometers_Driven    3093\n",
       "Fuel_Type               5\n",
       "Transmission            2\n",
       "Owner_Type              4\n",
       "Mileage               442\n",
       "Engine                146\n",
       "Power                 372\n",
       "Seats                   9\n",
       "New_Price             540\n",
       "Price                1373\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data pre-processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "train = train[train['Kilometers_Driven'] < 700000]\n",
    "train = train[train['Kilometers_Driven'] > 999]   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6012"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = train[train['Fuel_Type'] != 'Electric']\n",
    "len(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6006"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = train[train['Name'] != 'Ambassador Classic Nova Diesel']\n",
    "train = train[train['Name'] != 'Lamborghini Gallardo Coupe']\n",
    "train = train[train['Name'] != 'Force One LX 4x4']\n",
    "train = train[train['Name'] != 'Force One LX ABS 7 Seating']\n",
    "train = train[train['Name'] != 'Smart Fortwo CDI AT']\n",
    "len(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = train.append(test, ignore_index=True, sort=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Location</th>\n",
       "      <th>Year</th>\n",
       "      <th>Kilometers_Driven</th>\n",
       "      <th>Fuel_Type</th>\n",
       "      <th>Transmission</th>\n",
       "      <th>Owner_Type</th>\n",
       "      <th>Mileage</th>\n",
       "      <th>Engine</th>\n",
       "      <th>Power</th>\n",
       "      <th>Seats</th>\n",
       "      <th>New_Price</th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maruti Wagon R LXI CNG</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>2010</td>\n",
       "      <td>72000</td>\n",
       "      <td>CNG</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>26.6 km/kg</td>\n",
       "      <td>998 CC</td>\n",
       "      <td>58.16 bhp</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Hyundai Creta 1.6 CRDi SX Option</td>\n",
       "      <td>Pune</td>\n",
       "      <td>2015</td>\n",
       "      <td>41000</td>\n",
       "      <td>Diesel</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>19.67 kmpl</td>\n",
       "      <td>1582 CC</td>\n",
       "      <td>126.2 bhp</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Honda Jazz V</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>2011</td>\n",
       "      <td>46000</td>\n",
       "      <td>Petrol</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>18.2 kmpl</td>\n",
       "      <td>1199 CC</td>\n",
       "      <td>88.7 bhp</td>\n",
       "      <td>5.0</td>\n",
       "      <td>8.61 Lakh</td>\n",
       "      <td>4.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Maruti Ertiga VDI</td>\n",
       "      <td>Chennai</td>\n",
       "      <td>2012</td>\n",
       "      <td>87000</td>\n",
       "      <td>Diesel</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>20.77 kmpl</td>\n",
       "      <td>1248 CC</td>\n",
       "      <td>88.76 bhp</td>\n",
       "      <td>7.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Audi A4 New 2.0 TDI Multitronic</td>\n",
       "      <td>Coimbatore</td>\n",
       "      <td>2013</td>\n",
       "      <td>40670</td>\n",
       "      <td>Diesel</td>\n",
       "      <td>Automatic</td>\n",
       "      <td>Second</td>\n",
       "      <td>15.2 kmpl</td>\n",
       "      <td>1968 CC</td>\n",
       "      <td>140.8 bhp</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               Name    Location  Year  Kilometers_Driven  \\\n",
       "0            Maruti Wagon R LXI CNG      Mumbai  2010              72000   \n",
       "1  Hyundai Creta 1.6 CRDi SX Option        Pune  2015              41000   \n",
       "2                      Honda Jazz V     Chennai  2011              46000   \n",
       "3                 Maruti Ertiga VDI     Chennai  2012              87000   \n",
       "4   Audi A4 New 2.0 TDI Multitronic  Coimbatore  2013              40670   \n",
       "\n",
       "  Fuel_Type Transmission Owner_Type     Mileage   Engine      Power  Seats  \\\n",
       "0       CNG       Manual      First  26.6 km/kg   998 CC  58.16 bhp    5.0   \n",
       "1    Diesel       Manual      First  19.67 kmpl  1582 CC  126.2 bhp    5.0   \n",
       "2    Petrol       Manual      First   18.2 kmpl  1199 CC   88.7 bhp    5.0   \n",
       "3    Diesel       Manual      First  20.77 kmpl  1248 CC  88.76 bhp    7.0   \n",
       "4    Diesel    Automatic     Second   15.2 kmpl  1968 CC  140.8 bhp    5.0   \n",
       "\n",
       "   New_Price  Price  \n",
       "0        NaN   1.75  \n",
       "1        NaN  12.50  \n",
       "2  8.61 Lakh   4.50  \n",
       "3        NaN   6.00  \n",
       "4        NaN  17.74  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Car_Age'] = 2019 - df['Year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Mileage'] = df['Mileage'].apply(lambda x : str(x).split(' ')[0]).astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Engine'] = df['Engine'].apply(lambda x : str(x).split(\" \")[0]).astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Power'] = df['Power'].replace('null bhp','0 bhp').apply(lambda x : str(x).split(' ')[0]).astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Seats'] = df['Seats'].fillna(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['New_Price'] = df['New_Price'].fillna('-1 Lakhs').apply(lambda x: x.split(' ')[0]) \n",
    "df['New_Price'] = df['New_Price'].astype(float).replace(-1, np.nan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Year'] = df['Year'].astype('category')\n",
    "df['Location'] = df['Location'].astype('category')\n",
    "df['Fuel_Type'] = df['Fuel_Type'].astype('category')\n",
    "df['Transmission'] = df['Transmission'].astype('category')\n",
    "df['Owner_Type'] = df['Owner_Type'].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name                    0\n",
       "Location                0\n",
       "Year                    0\n",
       "Kilometers_Driven       0\n",
       "Fuel_Type               0\n",
       "Transmission            0\n",
       "Owner_Type              0\n",
       "Mileage                 0\n",
       "Engine                 46\n",
       "Power                  46\n",
       "Seats                   0\n",
       "New_Price            6236\n",
       "Price                1234\n",
       "Car_Age                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Car_Brand1'] = df['Name'].apply(lambda x: ' '.join(x.split(' ')[:2]))\n",
    "\n",
    "df['Engine'] = df.groupby(['Car_Brand1']).transform(lambda x: x.fillna(x.median()))['Engine']\n",
    "df['Power'] = df.groupby(['Car_Brand1']).transform(lambda x: x.fillna(x.median()))['Power']\n",
    "df['New_Price'] = df.groupby(['Car_Brand1']).transform(lambda x: x.fillna(x.mean()))['New_Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name                    0\n",
       "Location                0\n",
       "Year                    0\n",
       "Kilometers_Driven       0\n",
       "Fuel_Type               0\n",
       "Transmission            0\n",
       "Owner_Type              0\n",
       "Mileage                 0\n",
       "Engine                  0\n",
       "Power                   0\n",
       "Seats                   0\n",
       "New_Price            1504\n",
       "Price                1234\n",
       "Car_Age                 0\n",
       "Car_Brand1              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Car_Brand2'] = df['Name'].apply(lambda x: x.split(' ')[0])\n",
    "\n",
    "df['New_Price'] = df.groupby(['Car_Brand2']).transform(lambda x: x.fillna(x.mean()))['New_Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Name                    0\n",
       "Location                0\n",
       "Year                    0\n",
       "Kilometers_Driven       0\n",
       "Fuel_Type               0\n",
       "Transmission            0\n",
       "Owner_Type              0\n",
       "Mileage                 0\n",
       "Engine                  0\n",
       "Power                   0\n",
       "Seats                   0\n",
       "New_Price             156\n",
       "Price                1234\n",
       "Car_Age                 0\n",
       "Car_Brand1              0\n",
       "Car_Brand2              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "def aggregate_functions(df):        \n",
    "    \n",
    "    agg_func = {\n",
    "        'Location' : ['count'],\n",
    "        'Mileage' : ['mean'],\n",
    "        'Power' : ['mean'],\n",
    "        'Engine' : ['mean'] }\n",
    "    \n",
    "    agg_df = df.groupby(['Car_Brand1']).agg(agg_func)\n",
    "    agg_df.columns = ['_'.join(col).strip() for col in agg_df.columns.values]\n",
    "    agg_df.reset_index(inplace=True)\n",
    "    \n",
    "    agg_df = pd.merge(df, agg_df, on='Car_Brand1', how='left')\n",
    "    \n",
    "    return agg_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = aggregate_functions(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>Location</th>\n",
       "      <th>Year</th>\n",
       "      <th>Kilometers_Driven</th>\n",
       "      <th>Fuel_Type</th>\n",
       "      <th>Transmission</th>\n",
       "      <th>Owner_Type</th>\n",
       "      <th>Mileage</th>\n",
       "      <th>Engine</th>\n",
       "      <th>Power</th>\n",
       "      <th>Seats</th>\n",
       "      <th>New_Price</th>\n",
       "      <th>Price</th>\n",
       "      <th>Car_Age</th>\n",
       "      <th>Car_Brand1</th>\n",
       "      <th>Car_Brand2</th>\n",
       "      <th>Location_count</th>\n",
       "      <th>Mileage_mean</th>\n",
       "      <th>Power_mean</th>\n",
       "      <th>Engine_mean</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maruti Wagon R LXI CNG</td>\n",
       "      <td>Mumbai</td>\n",
       "      <td>2010</td>\n",
       "      <td>72000</td>\n",
       "      <td>CNG</td>\n",
       "      <td>Manual</td>\n",
       "      <td>First</td>\n",
       "      <td>26.6</td>\n",
       "      <td>998.0</td>\n",
       "      <td>58.16</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5.439318</td>\n",
       "      <td>1.75</td>\n",
       "      <td>9</td>\n",
       "      <td>Maruti Wagon</td>\n",
       "      <td>Maruti</td>\n",
       "      <td>177</td>\n",
       "      <td>20.912373</td>\n",
       "      <td>66.201808</td>\n",
       "      <td>1014.056497</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     Name Location  Year  Kilometers_Driven Fuel_Type  \\\n",
       "0  Maruti Wagon R LXI CNG   Mumbai  2010              72000       CNG   \n",
       "\n",
       "  Transmission Owner_Type  Mileage  Engine  Power  Seats  New_Price  Price  \\\n",
       "0       Manual      First     26.6   998.0  58.16    5.0   5.439318   1.75   \n",
       "\n",
       "   Car_Age    Car_Brand1 Car_Brand2  Location_count  Mileage_mean  Power_mean  \\\n",
       "0        9  Maruti Wagon     Maruti             177     20.912373   66.201808   \n",
       "\n",
       "   Engine_mean  \n",
       "0  1014.056497  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "tf1 = TfidfVectorizer(lowercase=True, stop_words='english', ngram_range=(1, 1), max_df=1.0, min_df=2, binary=True)\n",
    "df_name = tf1.fit_transform(df['Name'])\n",
    "df_name = pd.DataFrame(data=df_name.toarray(), columns=tf1.get_feature_names())\n",
    "\n",
    "df = pd.concat([df, df_name], axis=1) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['Name','Car_Brand1','Car_Brand2'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df = df[df['Price'].isnull()!=True]\n",
    "test_df = df[df['Price'].isnull()==True]\n",
    "test_df.drop('Price', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((6006, 670), (1234, 669))"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train_df.shape, test_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "train_df['Price'] = np.log1p(train_df['Price'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Train test split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "X = train_df.drop(labels=['Price'], axis=1)\n",
    "y = train_df['Price'].values\n",
    "\n",
    "from sklearn.model_selection import train_test_split\n",
    "X_train, X_cv, y_train, y_cv = train_test_split(X, y, test_size=0.25, random_state=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((4504, 669), (4504,), (1502, 669), (1502,))"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X_train.shape, y_train.shape, X_cv.shape, y_cv.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Build the model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "from math import sqrt \n",
    "from sklearn.metrics import mean_squared_log_error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Training until validation scores don't improve for 50 rounds.\n",
      "[100]\tvalid_0's rmse: 0.161074\n",
      "[200]\tvalid_0's rmse: 0.153335\n",
      "[300]\tvalid_0's rmse: 0.150883\n",
      "[400]\tvalid_0's rmse: 0.14974\n",
      "[500]\tvalid_0's rmse: 0.149047\n",
      "Early stopping, best iteration is:\n",
      "[500]\tvalid_0's rmse: 0.149047\n",
      "RMSLE: 0.1490468988971752\n"
     ]
    }
   ],
   "source": [
    "import lightgbm as lgb\n",
    "train_data = lgb.Dataset(X_train, label=y_train)\n",
    "test_data = lgb.Dataset(X_cv, label=y_cv)\n",
    "\n",
    "param = {'objective': 'regression',\n",
    "         'boosting': 'gbdt',  \n",
    "         'metric': 'l2_root',\n",
    "         'learning_rate': 0.05, \n",
    "         'num_iterations': 1000,\n",
    "         'num_leaves': 30,\n",
    "         'max_depth': -1,\n",
    "         'min_data_in_leaf': 4,\n",
    "         'bagging_fraction': 0.78,\n",
    "         'bagging_freq': 1,\n",
    "         'feature_fraction': 0.65,\n",
    "         }\n",
    "\n",
    "lgbm = lgb.train(params=param,\n",
    "                 verbose_eval=100,\n",
    "                 early_stopping_rounds=50,\n",
    "                 train_set=train_data,\n",
    "                 valid_sets=[test_data])\n",
    "\n",
    "y_pred_lgbm = lgbm.predict(X_cv)\n",
    "print('RMSLE:', sqrt(mean_squared_log_error(np.expm1(y_cv), np.expm1(y_pred_lgbm))))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAABZgAAALICAYAAADyhJW9AAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDMuMC4wLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvqOYd8AAAIABJREFUeJzs3Xm0ZXdd5/33DUEkGCyGelpQcfYn9gNtK6AgQ0CQSR+aQbQFaQRFkEGatmkfzSNoO0G3oIiCMjYQBluxWwQJyow4NCAICj8HEBEbOwEKIjGpkNznj3PSfQ2VVNVO3TpVt16vtbJqn733+e3vvvz+YH3Wd33P1vb2dgAAAAAAcLRO23QBAAAAAACcnATMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIqdvugAAADgRjTG+uHrPnPNzDnHtx6u/nHO+4Cq+/+DqfnPObznEtXtWXz/n/NH159Orx1YPrK5ZnVG9q/oPc865vuevq4urf2zVKHKN6ufnnL+y4/r+6p/NOf/hCnU8r/q2OeevHaLGn68+cIUSf3TO+ZtX9m5XZYzxo9W75pz/fcn3AQA4uQiYAQDgKF0eDF8Nt6yuv+PzC6prV9805/xo1RjjO6vXjjG+akdg/IA559vW17+w+vMxxm/POT+0vn5+dZ/1epd7UPX3V1HLmw8Vgl8Nd6r+7BiuBwDACUzADAAAR2mM8fxW3c3/eYxxj+pJ1aXVO6s7V7dd33qjMcYrq5tUn66+s7pu9fDqGmOMT1Qvr76p+pI554WXP2PO+eIxxvXX9//vjuQdrld96grXXtSqC/oF6zq/qPqc6n0L3/Oh1fe36pj+aPWoOef7xhhfWf1idWZ1o/V7f3v10OoW1X8aY1xa3evyv9N6vef3f/5uf139YXXz6oerP6qevv5bXbN66Zzzp9bd3b9QfWN1SfX+6rt3dmkDALA5ZjADAMBCY4wbVC+sHjjn/Jrq9dXn77jlS6sfmHPerHpT9YNzzj+snlm9bM75I9XtqrfsDJcvN+d8+pzz73acOmeM8c4xxvuqP66eOef8+I7rr6z+xRjjRuvP39U/7WY+lNut17z8v2eu3+0O1b+pbjfn/JfVk6vfWH/ne6v/Muf8hurLqy+p7jnn/MXqbdW/n3P+xmc86TO9Z8550/W9L6yeO+f8uupW1Z3HGPevbl2dVf2L9bX3twqlAQA4AehgBgCA5W5f/dmc811Vc87/MsZ42o7rfzTn/Mv18Ttbja+4oq1q+/IPY4xRvWz98XOrJ885n7H+vHNExpe0GqHxp3POl6yvH6x+rVWn9M+26iq+Q3W/q3iHKxuRcc9W4fFbVyVVdb11V/V/qO4yxnh89ZXVjVt1Sh+tN6/f5TrrOq8/xviP62ufU31N9ZpW3eF/OMY4t/r1OecfLXgWAAC7QAczAAAs9+lWAfFOl+04vmTH8fYh7q16a3WbMcY1q+bK16w7ot9YXedQD55zfqD6zVYh904vqB44xrjNermPHenLXME1qhfuqOVrW42/+Hj1kuph1Qerp1bvuJJ3u+I7f9YVrl8+5uIa6/tus+N531D91JzzQPUvqh9sFTS/bIzx/QvfCQCAY0zADAAAy/1e9ZVjjJtXjTHuW+1rR0fylfh0qznDrUdmvL564RjjhpffMMb46lYdvJceaoEdXb//pJt3vd61q5+qnn/Ub/R/nFv96x3jNh5evXZ9fNfqx+ecl3daf32rkPifvFt1XqtQujHGjdf1foY55yerP6get753X6u/7b3GGN+yfu5b55xPbBWg3/JqvBcAAMeQERkAAHDlrjPGuOKPyd368oM558fGGP+6esEY47JW84c/XX3GPOUreF314jHGL8w5H91qVvIjq1etO5mvX32o+qXqeTu+d84Y4x9bBdjXaTXH+Xl9phdWj6pefYTv+RnmnK8ZYzyp+p31u32yus+cc3uM8cPVb4wxPlV9olWn9Zevv/qb1U+PMT6r1Y/znTPGmNVfr9/7ynxn9fQxxrtbdTq/ZM55zhjjGtXdq/es/7f4eKsZ0AAAnAC2trcP11wBAAAcyhjjutXZ1RPnnBeOMb621Q/t3XjO6f9oAwCw5wmYAQDgahhj/ER171bzli+pHjfnfPNmqwIAgONDwAwAAAAAwCJ+5A8AAAAAgEUEzAAAAAAALHL6pgvg6G1vb29/+tOXbboM2DXXuMZWl15qfA97lz3OXmZ/s9fZ4+x19jh7mf3NXnes9/g1r3mN86v9h7tPwHwS2t6uAwcu3HQZsGv27TvDHmdPs8fZy+xv9jp7nL3OHmcvs7/Z6471Ht+//8wPHsl9RmQAAAAAALCIgBkAAAAAgEUEzAAAAAAALLK1vW24+clme3t7e2tra9NlAAAAAMCed+nBS/rYJy7adBmHtQszmN9e3eJw9/mRv5PQ1tZW5z3jRZsuAwAAAAD2vP2PeGB14gfMm2JEBgAAAAAAiwiYAQAAAABYRMAMAAAAAMAiAmYAAAAAABYRMAMAAAAAsIiAGQAAAACARQTMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIgJmAAAAAAAWETADAAAAALDI6bux6BjjrOrhc87vWH++X/XE6o/W/z6k+sic85nH+Lk3q64353zTMV73i6s/qd5RbVXXql4053z6Ie79ueopc86/OZY1AAAAAACcaHYlYN5pjPEd1b+vvmnO+ffrc7v1uPtWH6mOacC89mdzzrOqxhjXrP7bGOODc85X7LxpzvnYXXg2AAAAAMAJZ1cD5jHGd1WPru485/z4GOMN1cOvcM/PVrddf3zxnPPnxxjPry6pvqhVt/BLq2+tblLda875V2OMn65u32rMx1Oqt1YPrg6OMd5RXbv6yerS6q+q76se0Kp7+rTqCdV3VV9WfXb1n+ecLzuS95pzXjLG+PnqQWOMd1evqD5avaq6x/odX1Tdb87512OMb1u/449Wz6lusF7qMXPOd48x/qL6vWpUf1/dd8556ZHUAgAAAACwKbs5g/l21cOq63clQfYY41uqL6m+oVUA+53rMRdVfz3n/ObqvdWXzDnvUf169a1jjLuvz31jdcfqR6pPVc9vFTb/j+pZ1X3mnHeoPtwqfK76+Jzztq3Gddyxuk919+oaR/l+f1/dcH38edU3zzmfvOP6c6oHrY8fvK7nh6vXzjnvuP7bPGN9/Uur/2/Oeetqf3XLo6wFAAAAAOC4282A+X9Wd6l+rnrRGONQz7pp9eY55/ac85LqD6qvXl97x/rfA9WfrY8/3qrb+GbV1607ol9dXbNVt/Pl9lc3qn51fc83t+p+rppVc84LqkdVv1K9rFWn9NH4oupv18cfmHMevML1c6r7jTFuXF13zvmedd0PWdf0rOp663vPn3N+aH38ofU7AgAAAACc0HYzYP7LOedF6x/CO9iqy/iK3tt6PMZ6rvFtqr9YX9u+irXfV71+PRP5TtWvVu+vLmv1Tue3Cn/vtb7nJ6vXr7972fp5N6q+bs557+qe1ZPHGEc0MmSMca3qB1qN7vjfa+405/xk9fbqqdXzdtT91HVN928VQh/uXQEAAAAATki7GTDv9JBWM5C3dp6cc/5W9YExxu+36l7+tTnnOw7x/St6RfUPY4w3twpxt9cdyW9v1ZV8h1YB8CvHGG+tvr96zxXW+Ej1eWOMP65+p9UM5k9fxTO/eozxhjHG66rfrf7rnPN3D1Pns1qN37h8tvNPVvff0Xl9xZoAAAAAAE4aW9vbmmdPQtvnPeNFm64BAAAAAPa8/Y94YOedd8GmyzisffvO6MCBC4/Zevv3n/n26haHu++IRkKcKsYYP9pq5MYVffec8wPHux4AAAAAgBOZgHmHOeePVz++6ToAAAAAAE4Gx2sGMwAAAAAAe4yAGQAAAACARQTMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIgJmAAAAAAAWETADAAAAALCIgBkAAAAAgEVO33QBHL3t7e32P+KBmy4DAAAAAPa8Sw9esukSTmgC5pPQ9nadf/4Fmy4Dds2+fWd04MCFmy4Ddo09zl5mf7PX2ePsdfY4e5n9DbvDiAwAAAAAABYRMAMAAAAAsIiAGQAAAACARQTMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWOT0TRfA0dva2m7//jM3XQbsKnucvc4eZy+zvzkRXXrwYB/7xMWbLgMAYM8RMJ+EtrZO6+9+8XGbLgMAAE4aN37kUyoBMwDAsWZEBgAAAAAAiwiYAQAAAABYRMAMAAAAAMAiAmYAAAAAABYRMAMAAAAAsIiAGQAAAACARQTMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIgJmAAAAAAAWOX3TBWzaGOOfV0+uzqg+p3pV9cQ55/bVXPc3q60557de/SoBAAAAAE48p3QH8xhjX/XS6rFzzjtW31DdrPq+q7nuF7YKq683xvjSq10oAAAAAMAJ6FTvYL5X9bo5519UzTkvHWM8qLpkjPHs6gurG1S/Pef8/8YYz19/vkF1zznnx69k3YdW/736x+r7qx+sGmM8tHpU9bHqYPWy6pzqmdVXtAr8z55zvuHYvyoAAAAAwLF1SncwVzeu3r/zxJzzH6obVX8w57xrddvqETtued2c8zZXFi6PMU6rvrN6Yavu6G8fY1x7jHHD6j9U31h9c3Wd9Ve+pzp/znn7VoH3Lx6rlwMAAAAA2E2negfzB6uv3XlijPElrTqXbznGuGP1yepaO26Zh1nzrtWZ1YvXny8PnP+0+rM554Xr57x1ff1m1e3GGF+//nz6GOMGc86PLnslAAAAAIDj41TvYP6t6m5jjC+rGmNcs3pK9TXVgTnnA6qfrc4YY2ytv3PZYdb8nup75px3m3Perbp/9cjqL6uvWnczn1bdan3/+6qXzDnPqu5e/dfqykZvAAAAAACcME7pgHnO+cnq31TPGmO8ofqD6l3Va6t7rLuMn1H9RatxGldpjPF/VV9fnbvjGb9XfXb1ldWTqjdXr66uXV1S/XKr4PmN1VurD845DxdiAwAAAABs3Kk+IqM559urOx3i0s0Oce7Bh1nrf1VfcIjzXz3GOL2645zzFlVjjDdVH5pzXlw96GjrBgAAAADYtFM+YF5ijPFZ1WsOcWnOOb/vUN+Zc356jHGdMcY7qoPVH7bqZgYAAAAAOCkJmBeYcx6szlrwvR+ufviYFwQAAAAAsAGn9AxmAAAAAACWEzADAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwyOmbLoCjt719WTd+5FM2XQYAAJw0Lj14cNMlAADsSQLmk9D29lbnn3/BpsuAXbNv3xkdOHDhpsuAXWOPs5fZ3wAAcGoxIgMAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFjl90wVw9La2ttu//8xNlwG7yh5nr7PH2cvs7/r0wYv7+CcObroMAADYdQLmk9DW1mn9/q98y6bLAADgStz6Yb9VCZgBANj7jMgAAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIgJmAAAAAAAWETADAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwyOmbLmA3jTHOqv5bdbM554fW536met+c8/nH6BnPr762+li13epv+vA5559e4b6fq54y5/ybY/FcAAAAAIBNOxU6mA9WzxtjbO3iMx4/5zxrznnH6qeq/3jFG+acjxUuAwAAAAB7yZ7uYF57Xasg/ZHV0y8/OcZ4dPWdrbqOX1qdU712zvk1Y4xbV6+s9lc3qp4z57zrET7v+tU/jDG+uHpF9dHqVdU9qoe36nR+frWv2qoeVP2v6jnVDdZrPGbO+e5lrwsAAAAAcHycCgFz1SOqPxpjnLv+fEb17dVtWwXMv1udW310jPGF1d2qD1VfV92i+o3DrP/kMcYPVZdWf1c9vrpW9XnV1805D44x7rG+90eq35xzPnOMcafqVtXNW4XbzxhjfEX1vHVtAAAAAAAnrFMiYJ5zfnSM8dhWncO/V31O9UXVa9e3XK/68lZB8j2q21RPqu6yPn7oYR7x+Dnnq3eeWHcwf2DOefAK947queu6Xre+9wHVncYY376jHgAAAACAE9qpMIO5qjnnK6pZPbi6uPrT6o5zzrNaBc/vbvWDgN9ZfbL67epfVdeac35k4WMvO8S591a3rBpj3H6M8aTqfdVT17Xcv9W4DgAAAACAE9opEzCvPbb6x+oTrbqX3zLGeFv1FdWH55x/W312q3EVH68+3WoW87H0U9W9xhhvqH6s+uXqJ6v7r8+9unrPMX4mAAAAAMAxt7W9vb3pGjh627//K9+y6RoAALgSt37Yb3XeeRdsugx2wb59Z3TgwIWbLgN2jT3OXmZ/s9cd6z2+f/+Zb2/1+3RX6ZSYwXx1jTFuUr3gEJfeOOd8wvGuBwAAAADgRCBgPgJzzr+pztp0HQAAAAAAJ5JTbQYzAAAAAADHiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABYRMAMAAAAAMAiAmYAAAAAABYRMAMAAAAAsMjpmy6Ao7e9fVm3fthvbboMAACuxKcPXrzpEgAA4LgQMJ+Etre3Ov/8CzZdBuyaffvO6MCBCzddBuwae5y9zP4GAIBTixEZAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIgJmAAAAAAAWETADAAAAALDI6ZsugKO3tbXd/v1nbroM2FX2OHudPc5ecMnBizvwiYObLgMAANggAfNJaGvrtH7teXfbdBkAwCnuft/96krADAAApzIjMgAAAAAAWETADAAAAADAIgJmAAAAAAAWETADAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFTt90ASeTMcZZ1a9Wf1ZtV9euzplz/sIm6wIAAAAA2AQdzEfvdXPOs+acd6zuUP27Mca+TRcFAAAAAHC86WC+es6sLq1uNsb46fXxRdX3Vo+r3jLn/LUxxrnVq+ecTx1jPLt6bnXN6ifX3/mr6vuqB1QPaRX8P2HO+drj/UIAAAAAAEdKB/PRu9MY4w1jjNdV51SPrp5aPWrOeYfql6qnVC+v7j7GuHa1r7rzGGOr+trq96tnVfdZf+fD1YPX6398znlb4TIAAAAAcKLTwXz0Xjfn/I6dJ8YYz55zvnP98U3Vz1RvqX6+umP169X9qtu1Cpf3VzeqfnWMUatZzq9p1ck8j8M7AAAAAABcbTqYj42/G2PcfH18h+rP55yXVW+rHt8qPH5L9eRWnc3nV39b3WvOeVarURmvX3//suNYNwAAAADAYjqYj43vrZ6+HoHx6eqh6/Mvr55fvas6t/o31RvnnJeNMX6geuUY47Tqk9WDqpsc78IBAAAAAJYSMB+FOecbqjcc4vwfV7c/xPnfrv7Z+uO51Q12XHtNq87mnZ5/bCoFAAAAANh9RmQAAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYJHTN10AR297+7Lu992v3nQZAMAp7pKDF2+6BAAAYMMEzCeh7e2tzj//gk2XAbtm374zOnDgwk2XAbvGHgcAAGCvMCIDAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABYRMAMAAAAAMAiAmYAAAAAABY5fdMFcPS2trbbv//MTZcBu8oeZ6+zxzlRHLzkoj5x4JJNlwEAAJykBMwnoa2t03raOXfddBkAwB7wmAecWwmYAQCAZYzIAAAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABYRMAMAAAAAMAip2+6gBPNGOOs6vXVd8w5X7bj/J9U76iuO+e8zxjjDdXD55zv20ihAAAAAAAbpoP50N5X/evLP4wxblZdp2rOeZ9NFQUAAAAAcCLRwXxo76q+coyxb855oHpgdU51kzHGR+acn3f5jWOMz62eU91gfeoxc853jzEeVd2numb1ifXxNaoXVDeuPlTdfs5543WA/bRqq/po9ZA55yeOx4sCAAAAACylg/nKvby69xhjq7pV9dYrue+Hq9fOOe9YPax6xhjjtFaB853nnLdrFTLfcn39A3POb6yeWP2z9RrPqh455zyrelX1+F15IwAAAACAY0gH85V7cfWM6v3Vm6/ivptVdxpjfPv68/XmnJeNMQ5WLxlj/EP1Ba1C5ptWr66ac75vjHHe+js3rX5pjNH6vj8/1i8DAAAAAHCs6WC+EnPO97eau/yY6kVXcev7qqeuu4/vX50zxrh59a/mnN9ePbrV33mrek9166oxxpdVN7z8cdWD1ms8vnrlsX4fAAAAAIBjTcB81V5WfeGc86o6in+yuv8Y4w2tupPfU/1l9akxxtuq36n+Z6u5y8+pvniM8aZWIzIuWq/xiOoFY4w3Vz9T/cmxfxUAAAAAgGNra3t7e9M1nDLGGLepPmfO+ZoxxldUr55zftmCpbafds5dj3F1AMCp6DEPOLfzzrvgmK23b98ZHThw4TFbD0409jh7nT3OXmZ/s9cd6z2+f/+Zb69ucbj7zGA+vt7fai7zE1rNWn7khusBAAAAAFhMwHwczTk/Ut1x03UAAAAAABwLZjADAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFBMwAAAAAACxy+qYL4Ohtb1/WYx5w7qbLAAD2gIOXXLTpEgAAgJOYgPkktL291fnnX7DpMmDX7Nt3RgcOXLjpMmDX2OMAAADsFUZkAAAAAACwiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABY5PRNF8ACW9vt33/mpquAXWWPs9fZ40fv4ksu7pMHDm66DAAAAHYQMJ+ETts6re/+jbttugwAOK6ed+9XVwJmAACAE4kRGQAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABYRMAMAAAAAMAiAuZqjHHWGOOlx3jNm4wxvnV9/HNjjJscy/UBAAAAADbt9E0XsIfdqfqq6hVzzsduuhgAAAAAgGNNwHwlxhh3qX6iuqj6aPWQ6pPV06pbVZ9VPaH6reqXqy+sblD9dvXE6oeqM8YYb60eVz28+kj1ouq6rf72Z885XzfG+JPqjdXNq+3qXnPOTxyXFwUAAAAAWMiIjEMYY2xVv1LdZ855h1bh79nVvaobzjlvVd2tumWrYPkP5px3rW5bPWLOeWn1M9WL55y/uWPps6vfmXPevvq26jljjNNaBc4vWT/rw9Xdj8d7AgAAAABcHQLmQ7th9ck554fXn99U/fNqVL9fNef8yJzz7Opj1S3HGOdUT62udRXr3nS9Vuu1P1ntX1/74/W/H6o++9i9CgAAAADA7hAwH9r51XXHGDdaf75D9efVe1t1LTfG+NwxxrnVg6sDc84HVD/baizGVnVZn/n3fW91u/X3P7+6XqvxG7UajQEAAAAAcNIwg/n/+OYxxtt2fP7p6uVjjMuqj7cKkj9a3XmM8ZZWf7sfq/6meukY43bVp6q/qG5cvbv6kTHGO3as+VPVc8cY96uuXT1szvnpMcbuvhkAAAAAwC4QMFdzzjdU1z/EpV85xLlHH+LczQ5x7sOtRmpUvXTH+X91iOd/8Y7jH7qyOgEAAAAATiRGZAAAAAAAsIiAGQAAAACARQTMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIgJmAAAAAAAWETADAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgkdM3XQBH77Lty3revV+96TIA4Li6+JKLN10CAAAAVyBgPhltb3Xe+RdsugrYNfv2ndGBAxduugzYNfY4AAAAe4URGQAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwyOmbLoAFtrbbv//MTVcBu8oeZ6/bi3v8oksOdsGBizddBgAAAMeRgPkkdNrWad3jN35i02UAwD/xqnuf3QUJmAEAAE4lRmQAAAAAALCIgBkAAAAAgEUEzAAAAAAALCJgBgAAAABgEQEzAAAAAACLCJgBAAAAAFhEwAwAAAAAwCICZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABY5IQJmMcYZ40xXnqM17zJGONb18c/N8a4ybFc/1gbY9xsjHH7TdcBAAAAAHAkTt90AbvsTtVXVa+Ycz5208UcgftWH6netOlCAAAAAAAO54QOmMcYd6l+orqo+mj1kOqT1dOqW1WfVT2h+q3ql6svrG5Q/Xb1xOqHqjPGGG+tHlc9vFWA+6Lquq3e/+w55+vGGH9SvbG6ebVd3WvO+Ykrqesrqmevn39h9R3VdarnVNdcf/8xc853jTE+Muf8vPX3Xlo9s/ri6h7VGdWXVU+qfqeH61bxAAAgAElEQVR6cHVwjPGOOecfXa0/HgAAAADALjthRmRc0Rhjq/qV6j5zzju0Cn/Pru5V3XDOeavqbtUtWwXLfzDnvGt12+oRc85Lq5+pXjzn/M0dS59d/c6c8/bVt1XPGWOc1ipwfsn6WR+u7n4V5f3n6qfnnLduFWz/y/W5p63X/YFWYfNV+dw557dU/0/1Q3POD1fPr54iXAYAAAAATgYnbMBc3bD65Dp4rdXYiH9ejer3q+acH5lznl19rLrlGOOc6qnVta5i3Zuu12q99ier/etrf7z+90PVZ1/FGjtr+NU552uusO47W4XeV7S14/idR/gsAAAAAIAT0okcMJ9fXXeMcaP15ztUf169t1XXcmOMzx1jnNtqtMSBOecDqp9tNRZjq7qsz3zH91a3W3//86vrtRq/UavRFkdiZw0PGGM8+grrfk2rURxV1xxjfM4Y47NaBeSXO9SzDlUvAAAAAMAJ6USbwfzNY4y37fj809XLxxiXVR9vFSR/tLrzGOMtrer/sepvqpeOMW5Xfar6i+rG1burHxljvGPHmj9VPXeMcb/q2tXD5pyfHmMcTZ3/vvrlMcbZrWYwP7B6RfWsMcYPtprD/ND1vT9X/UH1/uqDh1n37dV/GmO8d875+qMpCAAAAADgeNva3j7Spl1OINv3+I2f2HQNAPBPvOreZ3feeRdsugw2bN++Mzpw4MJNlwG7xh5nr7PH2cvsb/a6Y73H9+8/8+3VLQ5334nWwXzCWI+0eM0hLs055/cd73oAAAAAAE40AuYrMec8WJ216ToAAAAAAE5UflAOAAAAAIBFBMwAAAAAACwiYAYAAAAAYBEBMwAAAAAAiwiYAQAAAABYRMAMAAAAAMAiAmYAAAAAABYRMAMAAAAAsIiAGQAAAACARU7fdAEcvcu2L+tV9z5702UAwD9x0SUHN10CAAAAx5mA+WS0vdV551+w6Spg1+zbd0YHDly46TJg19jjAAAA7BVGZAAAAAAAsIiAGQAAAACARQTMAAAAAAAsImAGAAAAAGARATMAAAAAAIsImAEAAAAAWETADAAAAADAIqdvugAW2Kr9+8/cdBWwq+xx9rrjsccvuuSSLjhw0a4/BwAAgFOXgPkkdNrWVvf89WdvugwATnCvvO/3dEECZgAAAHbPEQfMY4zrVl9UvX/O+andKwkAAAAAgJPBEc1gHmPcr3pj9eLqcWOMs3e1KgAAAAAATnhH+iN//7b6hur86ieqe+9aRQAAAAAAnBSONGC+bM55cbU959yujMgAAAAAADjFHWnA/OYxxourLxhjPLP6H7tYEwAAAAAAJ4Ej/ZG/J1W3rv64et+c8xW7VxIAAAAAACeDIw2YXznnvG316t0sBgAAAACAk8eRBswfG2P8QDWry6rmnK/ZtaoAAAAAADjhHWnA/NHqa9b/VW1XAmYAAAAAgFPYEQXMc87v3u1CAAAAAAA4uRxRwDzG+J+tupa3qutX759z3nQ3CwMAAAAA4MR2pB3MN7r8eIzxRdUTd6sgAAAAAABODqcd7RfmnB+svmoXagEAAAAA4CRypCMyXtJqREbVjaq/37WKDl/LWdWvVn+2runa1Tlzzl/YVE0AAAAAAKeiIwqYq2fuOL6oetsu1HI0Xjfn/I6qMca1qjnGeOGc88CG6wIAAAAAOGVcZcA8xrhGdY3qB6pvb/Ujf6dVv1PdaderOzJnVpdWNxtj/PT6+KLqe6vHVW+Zc/7aGOPc6tVzzqeOMZ5dPbe6ZvWT6+/8VfV91QOqh7R6zyfMOV97xQeOMR5cfWur7ukbVT9f3av6v6sfnHP+9zHGt62ff+m6hh8aY3xB9Yzqs6sbVD8+5/xvY4w/qd5Y3bxVV/a95pyfOMZ/JwAAAACAY+pwM5gfUs3q7ut/Z/We6m92ua7DudMY4w1jjNdV51SPrp5aPWrOeYfql6qnVC+v7j7GuHa1r7rzGGOr+trq96tnVfdZf+fD1YPX6398znnbQ4XLO5w557xH9aTqEdV9qodV3z3GuH71Y9U3zTlvW33+GOMurWZX/+yc8y7Vo6pHrte6bvWSHXXc/Wr+fQAAAAAAdt1VdjDPOZ9VPWuM8ZA553OPU01H4n+PyLjcGOPZc853rj++qfqZ6i2tuovvWP16db/qdq3C5f2tuo9/dYxRq27k17TqZJ5HUMMfr/89UL13zrk9xvh4q+7kL1+v/6r12mdWX7qu5+wxxkNbdSpf8xDrfWi9BgAAAADACe1wHcyXe9MY4/8dY/zoGOMJY4xf3tWqlvm7McbN18d3qP58znlZq3nRj28VHr+lenKrzubzq79tNY7irFajMl6//v5lR/C87au49oFWQfFd1mv/QvWH1X+sXjDn/K71s7aOcD0AAAAAgBPOkQbML1j/e9vqS1rNDz7RfG/19DHGm1vNjP636/Mvr25avas6t/qK6o3r8PkHqleOMd5afX+r8R9X25zzvFYjOt44xvjDViMv/rz6r9XT1jXepbrhsXgeAAAAAMAmbG1vH75xdozxu3POO48xnjvnfMgY481zztsdh/o4tO17/vqzN10DACe4V973ezrvvAs2XQanmH37zujAgQs3XQbsGnucvc4eZy+zv9nrjvUe37//zLdXtzjcfVc5g3mHrTHG51VnjjGuU13/6hR3shhj/FL11Ye4dPc55z8e73oAAAAAAE4kRxow/1h17+qFreYLv+Cqb98b5pzfv+kaAAAAAABOVEcUMM853zTGeGf1RdWXzjn/YXfLAgAAAADgRHdEP/I3xrhv9cbqxdW/HWOcvatVAQAAAABwwjuigLl6XPUN1fnVT7QalwEAAAAAwCnsSAPmy+acF1fbc87t6lO7WBMAAAAAACeBIw2Y3zzGeEn1BWOMZ1b/YxdrAgAAAADgJHCVAfPls5bnnD9cvaJ6VvXKOee/Ow61AQAAAABwAjv9MNfv1GrmctX3zDnvtMv1AAAAAABwkjjciIytKzkGAAAAAOAUd7iAeftKjgEAAAAAOMUdbkTG140x3tqqe/mrdxxvzzlvs+vVAQAAAABwwjpcwHzz41IFR+Wy7e1eed/v2XQZAJzgLrrkkk2XAAAAwB53lQHznPODx6sQjsJ2nXf+BZuuAnbNvn1ndODAhZsuA3aNPQ4AAMBecbgZzAAAAAAAcEgCZgAAAAAAFhEwAwAAAACwiIAZAAAAAIBFBMwAAAAAACwiYAYAAAAAYJHTN10AC2zV/v1nbroK2FX2OHvdmfuu3QUH/nHTZQAAAMDVImA+CZ22tdW3/trLN10GAFfDK+53ny7YdBEAAABwNRmRAQAAAADAIgJmAAAAAAAWETADAAD8/+3de5SlZ0Hn+2/FBmI02A40Yo6IjJdnEFFRo4KATYQDiheIMKDjICheOFzUNQgzKOJyZIlH1GPmHBy5nYgXUBGOMgKjC4mDMMCAgIyYRxEvUQHTQkMghM6lzh97Z2xDQ6fedPXuKj6ftWpVv89+9rt/u3lWr+JXT54NAMAiCmYAAAAAABZRMAMAAAAAsIiCGQAAAACARRTMAAAAAAAsomAGAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIgvkUGWO8cIzx74+7/uQxxhxjfNEmcwEAAAAA7BYF86nzvdWjxhifv75+evXMOedbNpgJAAAAAGDXHNh0gP1iznlkjPGY6tljjP9QfXarwvnO1UXVVvWP1XdUH6h+obpddavqZXPOJ48xLl5f36q6/5zzvaf/nQAAAAAA3Dh2MJ9Cc86XVJdWF1cPn3NuV8+qHj3nPFy9tHpCq2L5tXPO+1Z3rx513G1+f855N+UyAAAAAHCms4P51Htedc6c8+/W13esnjHGqLpZ9WfVe6rzxxj3qt5f3eK458/TmBUAAAAAYDE7mHffrB623sH8hOp3qodXR+ec/6b66eqcMcbWev51mwgJAAAAALBTdjDvvkdVzxtjfML6+jurP61eMMa4R/XB6s+r8zaUDwAAAABgEQXzKTbnvKS65LjrN1aHTzD1zicYe/huZAIAAAAA2A2OyAAAAAAAYBEFMwAAAAAAiyiYAQAAAABYRMEMAAAAAMAiCmYAAAAAABZRMAMAAAAAsIiCGQAAAACARRTMAAAAAAAsomAGAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgc2HYCdu257u5c86MJNxwDgJrjq6ms2HQEAAABuMgXzXrRdlx+5YtMpYNccPHhOR49euekYsGsOHjynK45+aNMxAAAA4CZzRAYAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALHJg0wFYYKsOHTp30ylgV1nj7GfHrrl20xEAAADglFAw70FnbW31wN/8w03HAGChF3/z3TcdAQAAAE4JR2QAAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYBEFMwAAAAAAixzYdIAz3RjjcPXr1duOG758zvngHdzj4dV75py/fWrTAQAAAABsjoL5xvn9OedDlz55znnxKcwCAAAAAHBGUDAvNMa4pHpz9QXVLasHzzn/eozx5OqB1eXVOdWTq8PVu6pLqydWx6o7VL8253zqGON21TOrs6urqu+ec152Wt8QAAAAAMAOKZhvnAvWhfL1fmf9/fVzzu8fYzy1+pYxxsuqr63Or25evfUE97p99YXVLaq/r55aPb26aM75sjHG11RPq/7NrrwTAAAAAIBTRMF843zEERljjPtXb1pfXlbdtrpjq9L52upDY4w3nOBeb51zXlNdM8b40HrsztWTxhhPrLZa7XAGAAAAADijKZhvmu0bXP9J9dgxxlnVzaq73Ijn1OrojKfPOV8zxvhX1Vef2pgAAAAAAKeegvnGueERGVWfeMNJc863jjFeWr22OlJdvf46mcdXPz/GOHt93++7aXEBAAAAAHafgvkk5pyXVLc5yZz/XDXGuE313jnnl48xbtFqR/Nlc84fPW76Jcc977br7++o7ntKgwMAAAAA7DIF86l1pDp/jPE/Wh2F8ew5599sOBMAAAAAwK5QMJ9Cc87rqkdsOgcAAAAAwOlw1qYDAAAAAACwNymYAQAAAABYRMEMAAAAAMAiCmYAAAAAABZRMAMAAAAAsIiCGQAAAACARRTMAAAAAAAsomAGAAAAAGARBTMAAAAAAIsomAEAAAAAWOTApgOwc9dtb/fib777pmMAsNCxa67ddAQAAAA4JRTMe9F2XX7kik2ngF1z8OA5HT165aZjwK45ePCcTUcAAACAU8IRGQAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwAAAACwyIFNB2CBrTp06NxNp4BdZY2zV3346mt7/9ErNx0DAAAATgsF8x501tZWj3vxZZuOAcAJXPTA2206AgAAAJw2jsgAAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIHNh3gxhhjHK5+vXrbccOXzzkfvIN7PLx6z5zzt09tOgAAAACAj097omBe+/0550OXPnnOefEpzAIAAAAA8HFvLxXMH2GMcUn15uoLqltWD55z/vUY48nVA6vLq3OqJ1eHq3dVl1ZPrI5Vd6h+bc751DHG7apnVmdXV1XfPee87KO87o9Wn1PduvoX1TOqb64+r/r2OedrxxiPrb612q5eMOe8aIzxBdXPtDqa5GD1uDnna8YYf169uhrVu6tvnnNee6r+ngAAAAAAdsNeOoP5gjHGJcd9/eB6/PVzzntXv1d9yxjji6qvrc6vHlB9+gnudftWhfBdqyesx55eXTTnvNf6z087SZ4PzTnvV72o+ro55zesn/PQMcbnVw+p7r7+esAYY1R3qv7dOu/PVI9Y3+tfVk+ec961OrTODgAAAABwRttLO5g/4oiMMcb9qzetLy+rblvdsVXpfG31oTHGG05wr7fOOa+prhljfGg9dufqSWOMJ1ZbrXY4fyx/tP5+tH86G/q9rXZAf0GrEvsV6/FPbbXj+e+qJ69f89zq/evHjxy3W/qy9T0AAAAAAM5oe2kH80ezfYPrP6nOH2OcNca4RXWXG/GcWh+dMec8XH1P9cIdvu7x5jrHvdb3u7h6a3VR9ZQ557evr7duxL0AAAAAAM5Ie2kH8wXrM5eP94k3nDTnfOsY46XVa6sj1dXrr5N5fPXzY4yz1/f9vqVB55xvGWO8ovrDdcn9+la7l3+5+q0xxrurv211hjMAAAAAwJ60tb29vzbPjjFuUz1ozvmMdbn7J9UFc86/2XC0U2n7cS8+4ecPArBhFz3wdl1++RUfc87Bg+d09OiVpykRnF7WN/udNc5+Z42zn1nf7Heneo0fOnTuG6svO9m8vbSD+cY60uqIjP/R6uiJZy8tl8cYL6r+xQ2G3zfn/KabmBEAAAAAYM/bdwXznPO66hGn6F4Xnor7AAAAAADsR/vhQ/4AAAAAANgABTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFjkwKYDsHPXbW930QNvt+kYAJzAh6++dtMRAAAA4LRRMO9F23X5kSs2nQJ2zcGD53T06JWbjgEAAADASTgiAwAAAACARRTMAAAAAAAsomAGAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgc2HYCd29qqQ4fO3XQM2FXW+N5x9dXXdfToBzcdAwAAANgABfMetLW11YteeGTTMQCquvBBt950BAAAAGBDHJEBAAAAAMAiCmYAAAAAABZRMAMAAAAAsIiCGQAAAACARRTMAAAAAAAsomAGAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALHJg0wFOZIxxuHpl9dA5568dN/7H1R9Vt5xzXjjGuKT63jnnpRsJCgAAAADwcexM3sF8afUt11+MMe5cfVLVnPPCTYUCAAAAAGDljNzBvPaW6vPGGAfnnEerb6t+pfrMMca75py3vX7iGONTqudUt1oPPW7O+dYxxmOqC6ubVe9b//kTqudV51WXVfecc563LrAvqraqf6y+Y875vhMFG2NcXF1d3b66RfWC6huqz6y+ac75F2OMn6ju2arE/5k552+MMb66esr6NudUD6uOVc9fZ/ns6vVzzkfdhL83AAAAAIDT4kzewVz1ouqBY4yt6sur13yUeU+qXjHnvFf13dXPjzHOalU433vOeY9WJfP568f/cs75VdWPVp+2vsezqkfPOQ9XL62ecJJsfzXn/N+rP63uMOf8uuo3q28YY3zteuyrqntVPzTGOFjdqfq2OecF1W9XD17f6/Oq71y/x68bY9w2AAAAAIAz3Jm8g7nqV6ufr95RvepjzLtzdcEY4yHr60+dc143xjhWPX+M8YHqM1qVzHesXl4157x0jHH5+jl3rJ4xxmg9789Oku2P1t+PtjrOo+q91dnrPF+6PiP6+vvdvvq76qJ1nv+tevX68bfPOa+oGmO8c30PAAAAAIAz2hm9g3nO+Y5W5y4/rvrljzH10upn17uP/3X1K2OML6weMOd8SPXYVu91q/qf1V2rxhifXd36+perHra+xxOq3zlJvO2T5Hnl+l4XVL/eqiR/dvWIOefDq79f5znZvQAAAAAAzkhndMG89mvV7eacH2tH8VOrf73eMfzyViXy26sPjjHeUP1e9c5W5y4/p/qsMcZ/a3VExlXrezyqet4Y41XV06o/vgmZX1J9YH2vN1bb6x3Kv1S9bozx6urcdR4AAAAAgD1pa3v742vz7BjjbtUnzzl/d4zxudXL55yfvelcO7T9ohce2XQGgKoufNCtu/zyKzYdY085ePCcjh69ctMxYFdY3+x31jj7nTXOfmZ9s9+d6jV+6NC5b6y+7GTzzvQzmHfDO1qdy/yUVmcjP/pEk8YYN69+9wQPzTnn9+xiPgAAAACAPeHjrmCec76ruteNmHesOrzrgQAAAAAA9qi9cAYzAAAAAABnIAUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYBEFMwAAAAAAixzYdAB2bnt7uwsfdOtNxwCo6uqrr9t0BAAAAGBDFMx70PZ2HTlyxaZjwK45ePCcjh69ctMxAAAAADgJR2QAAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIHNh2AndvaqkOHzt10DNhVp2qNX3Psut77vg+eknsBAAAA8M8pmPegra2t3vTsf9h0DNgT7vLI22w6AgAAAMC+5YgMAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYJEDmw6w1BjjDtXTq1tVN6veUj1xznnFaczwg9X9q4PVedXb1g99zZzz2tOVAwAAAABgE/ZkwTzG+MTqt6tHzjlftx779ur51defrhxzzp+qfmqMcbj63jnnQ0/XawMAAAAAbNqeLJhb7Rr+g+vL5ao55y+OMX54jPH+Oectxxh3rX6nOlR9evWcVgX011XnVJ9d/eSc8+Ixxp2ri6qt6h+r76juUv1kdax65pzzl25suDHGd1efO+f8wTHGJ1Rvrh5U/XL1zuozqpfNOX9ojHG76pnV2dVV1XfPOS9b/DcDAAAAAHCa7NUzmP9l9RcnGH9D9Unr0vZ+1WXVl1bfWL14PedT5pxfvx779+uxZ1WPnnMerl5aPWE9fvac8x47KZfXnl89YF0u3696ZfXh6rOqh1fnVxeMMb6k1TEfF80577X+89N2+FoAAAAAABuxVwvmv2tV1t7Q51Y/0GqX8t1a7UC+T6sdz//fes6b198va7VruOqO1TPGGJe02r183np8Lgm3Pgf6D6r7Vo+onr1+6C1zzvesz2d+XTWqO1dPWr/2j1S3WfKaAAAAAACn214tmH+rus8Y48uvHxhjPLK6vHpR9a3V+6uXVQ+objHnfNd66vYJ7jerh613MD+h1dEaVdfdhIzPqh5Z3WbO+cfrsTuOMc5Z72z+ilYfCnhpqw8nPFx9T/XCm/CaAAAAAACnzZ4smOecH6i+ofrhMcarxxiva1XYfsuc829b7Ux+xZzzvdU1/VNh/NE8qnreGONVrY6o+OOTzL8xGV9XfU71K8cNH6t+o9Xu5d+ac76lenz1lDHGH1TPOxWvDQAAAABwOmxtb59oQy831RjjrOrV1X3nnO8fY3xW9YI551eegttvv+nZ/3AKbgP7310eeZsuv/yKTceAf+bgwXM6evTKTceAXWF9s99Z4+x31jj7mfXNfneq1/ihQ+e+sfqyk807cMpecR8bY/xIdcEJHnrEnPMvTzD/Dq0+VPAX5pzv3+18AAAAAACboGC+EeacP1b92A7m/2X1xTcY+6vqVOxeBgAAAAA4I+zJM5gBAAAAANg8BTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLHNh0AHZue3u7uzzyNpuOAXvCNceu23QEAAAAgH1LwbwHbW/XkSNXbDoG7JqDB8/p6NErNx0DAAAAgJNwRAYAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALHJg0wHYua3q0KFzNx2DU+jaY9f2nvdduekYAAAAALAjCuY9aOusrd719LdvOgan0G0f/zmbjgAAAAAAO+aIDAAAAAAAFlEwAwAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYBEFMwAAAAAAiyiYAQAAAABYRMEMAAAAAMAiCmYAAAAAABZRMAMAAAAAsIiCGQAAAACARRTMAAAAAAAsomAGAAAAAGARBfMuGGOcPcZ45Md4/J5jjC88nZkAAAAAAE41BfPuuG31UQvm6juq805TFgAAAACAXXFg0wH2qR+qPn+M8SPV+dXZ1a2qH6suq+5XfckY423VN1YXVjer3lddOOc8tpHUAAAAAAA7YAfz7nhq9bbqNdVPzznvUz2mevSc843Vy6snVH/bqni+95zzHq1K5vM3ExkAAAAAYGfsYN5d76x+eIzxndV2qwL5f5lzXjfGOFY9f4zxgeozbjgHAAAAAOBMZQfz7riu1d/tf6yeN+f8t9Urq63jH19/0N8D5pwPqR67fs7WCe4HAAAAAHDGsYN5d/xDdfPqTtVFY4x3tTp7+dbrx19XPa36luqDY4w3VB9utePZh/8BAAAAAHuCgnkXzDmvqr74Yzz+C9UvrC8vOC2hAAAAAABOMUdkAAAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYBEFMwAAAAAAiyiYAQAAAABYRMEMAAAAAMAiCmYAAAAAABZRMAMAAAAAsIiCGQAAAACARRTMAAAAAAAsomAGAAAAAGCRA5sOwM5tX7fdbR//OZuOwSAW6b0AAApISURBVCl07bFrNx0BAAAAAHZMwbwHbVdHLr9i0zEAAAAAgI9zjsgAAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALDIgU0HYOe2turQoXM3HYOb6Npj1/Se931o0zEAAAAAYDEF8x60tbXVuy+6ZNMxuIk+7XGHNx0BAAAAAG4SR2QAAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYBEFMwAAAAAAiyiYb6IxxuExxgsWPO9+Y4yLdyESAAAAAMBpoWAGAAAAAGCRA5sOsNeMMT6vuri6urqmeu56/JzqRdUvzTl/ZYzx09Xd10/71Tnnz40x7rie/8H113vXz31MdWF1s+p91YVzzmOn7U0BAAAAACxgB/PO3ad6Y3Xv6qnVp1afXL2kesa6XP766g7VV7Yqmb91jHHn6j9WPzLnvHf1mqoxxlnVrap7zznv0apkPv/0viUAAAAAgJ1TMO/cc6oj1curx7TaxfzV1SdWt1jPuWP1qjnn9pzz6uq11edXd6pev57z6qo553XVser5Y4znVJ/RqmQGAAAAADijKZh37ptalcdfU/1G9cTqd6oHVk8dY5xX/Wnr4zHGGDer7lb9eXVpddf1fc5fP/6F1QPmnA+pHtvqf5Ot0/ZuAAAAAAAWUjDv3BtaFcmvqr63+k9Vc853V0+p/t9WhfNfjjH+e6vdyy+cc/5R9X9UTxpjvKL6ivX93l59cIzxhur3qndW553G9wMAAAAAsIgP+duhOedf9E+7kG/42POr568vH3+Cx99Z3fMET73glAUEAAAAADhN7GAGAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALCIghkAAAAAgEUUzAAAAAAALKJgBgAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIHNh2Andve3u7THnd40zG4ia49ds2mIwAAAADATaJg3oO2t+vIkSs2HQMAAAAA+DjniAwAAAAAABZRMAMAAAAAsIiCGQAAAACARba2t7c3nYGdu7z6602HAAAAAAD2rdtXh042ScEMAAAAAMAijsgAAAAAAGARBTMAAAAAAIsomAEAAAAAWETBDAAAAADAIgpmAAAAAAAWUTADAAAAALDIgU0H4MYbY5xVPaP6ourD1SPnnG/fbCrYuTHGzarnVp9V3aL68ept1cXVdvU/q0fPOa8bYzylun91TfX9c87XbyIz7NQY4zbVG6v7tFq/F2d9s0+MMf5D9Y3VzVv9bPIHWePsE+ufU36x1c8p11bflX/H2SfGGF9R/eSc8/AY43O6kev6o83dxHuAj+YG6/uLq//U6t/xD1cPm3O+e4zxXdX3tFrfPz7n/C9jjFtXv1p9YvX31SPmnFdu5l3AR3f8Gj9u7Furx84577q+3sgat4N5b3lAdfZ60fz76qc3nAeW+rbqH+ec96i+tvq/q5+pfng9tlV90xjjS6qvrr6iemj1/2woL+zIupz4hepD6yHrm31jjHG4ulv1Va3W8O2yxtlfvq46MOe8W/Vj1VOzxtkHxhhPqJ5dnb0e2sm6/oi5pzM7nMwJ1vfPtSrdDlcvqp44xrht9bhWP8Pct/qJMcYtqh+pfnW9vt/UqpyDM8oJ1njrX6R8Z6t/l9vkGlcw7y13r15eNed8bfVlm40Di/1G9eTjrq+pvrTVDriql1X3brXmf3fOuT3n/JvqwBjj0GlNCss8vfrPrX47XNY3+8t9q7dWL65eUv2XrHH2lz9rtV7Pqm5ZXZ01zv7wF9WFx13vZF2faC6cSW64vh8653zz+s8HqquqL69ePef88JzzfdXbqy/suK4l65sz1z9b42OMW1VPq77/uDkbW+MK5r3lltX7jru+dozhmBP2nDnnB+acV4wxzq1eWP1wtTXn3F5PuaL6lD5yzV8/DmesMcbDq8vnnP/1uGHrm/3k1q1+yf3g6nurX6nOssbZRz7Q6niMS6tnVRfl33H2gTnnb7b6hcn1drKuTzQXzhg3XN9zzndWjTHuVj2m+tk++vo+ftz65ox0/BofY3xC9ZzqB1qt2ettbI0rmPeW91fnHnd91pzzmk2FgZtijHG76pXVL805f7U6/gy3c6ujfeSav34czmTfUd1njHFJ9cXV86rbHPe49c1e94/Vf51zHptzzlY7go7/IdUaZ6/7gVZr/PNaffbJL7Y6b/x61jj7xU5+/j7RXDijjTEe0uq/Krz/nPPyPvr6Pn7c+mYv+NLqc6ufr15Qff4Y4/9qg2tcwby3vLrVmXCNMb6y1X+eCnvOGOPTqt+tnjjnfO56+E3rcz1rdS7zq1qt+fuOMc4aY3xmq1+qHDntgWEH5pz3nHN+9fq8tzdXD6teZn2zj/xhdb8xxtYY47zqk6pXWOPsI+/tn3b5vKe6WX5OYX/aybo+0Vw4Y40xvq3VzuXDc853rIdfX91jjHH2GONTqju2+tDK/9W1ZH2zB8w5Xz/nvNP6/3M+tHrbnPP72+Aad7zC3vLiVrviXtPqAO9HbDgPLPWk6lOrJ48xrj+L+fuqi8YYN6/+tHrhnPPaMcarqv/e6hdij95IWrjp/l31LOub/WD9SdT3bPUD7PVr9y+zxtk/frZ67nr93rzVzy1vyBpn/9nJzycfMXcTgeHGWB8fcFH1N9WLxhhVfzDnfMoY46JW5dpZ1Q/NOa8aY/x49YtjjO+qjlTfuqHocJPMOd+1qTW+tb29ffJZAAAAAABwA47IAAAAAABgEQUzAAAAAACLKJgBAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwDAHjPG+G9jjAtuMPZzY4xHnmDuZ40xXnv60gEA8PFEwQwAAHvPM6uHXX8xxrh59Q3V8zeWCACAj0sHNh0AAADYsRdWTx1jnDPnvLL6pup3qy8bYzxlPeecViX0seufNMb4q+pfzTmvGmM8rbp0znnxGOMnqnu22oDyM3PO3zh9bwUAgL3MDmYAANhj5pxXVb9VPXA99IhWu5rvVH3bnPOC6rerB5/sXmOMr63uMOf8qupe1Q+NMQ7uSnAAAPYdBTMAAOxNz6r+7RjjvOpT55x/VP1dddEY4+JWZfHNPsbzt9bf71x96Rjjkurl6+fcfrdCAwCwvyiYAQBgD5pzvrU6t/q+6rnr4WdXj5hzPrz6+/6pRL7eVdWnjzG2qi9ej11avXLOebi6oPr16h27Gh4AgH3DGcwAALB3Pbf6qeoz19e/VL1ujPHe6t3VeTeY/39WL63+qnrveuwl1eExxquqT65ePOe8YpdzAwCwT2xtb29vOgMAAAAAAHuQIzIAAAAAAFhEwQwAAAAAwCIKZgAAAAAAFlEwAwAAAACwiIIZAAAAAIBFFMwAAAAAACyiYAYAAAAAYJH/H9ZN2adwYk/WAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "feature_imp = pd.DataFrame(sorted(zip(lgbm.feature_importance(), X.columns), reverse=True)[:15], \n",
    "                           columns=['Value','Feature'])\n",
    "plt.figure(figsize=(20, 10))\n",
    "sns.barplot(x=\"Value\", y=\"Feature\", data=feature_imp.sort_values(by=\"Value\", ascending=False))\n",
    "plt.title('LightGBM Features')\n",
    "plt.tight_layout()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Predict on test set"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "Xtest = test_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RMSLE LGBM:  0.12592288410413363\n",
      "RMSLE LGBM:  0.12834891488731617\n",
      "RMSLE LGBM:  0.11645801214951844\n",
      "RMSLE LGBM:  0.12620720243806346\n",
      "RMSLE LGBM:  0.11826384568882042\n",
      "RMSLE LGBM:  0.1424869889073636\n",
      "RMSLE LGBM:  0.20411278008061975\n",
      "RMSLE LGBM:  0.103502604793448\n",
      "RMSLE LGBM:  0.13660550298952415\n",
      "RMSLE LGBM:  0.1296016144204825\n",
      "RMSLE LGBM:  0.11033051817729324\n",
      "RMSLE LGBM:  0.1118180193232434\n",
      "RMSLE LGBM:  0.11952170542423937\n",
      "RMSLE LGBM:  0.12657572795773187\n",
      "RMSLE LGBM:  0.1472177605927138\n"
     ]
    }
   ],
   "source": [
    "from sklearn.model_selection import KFold\n",
    "from lightgbm import LGBMRegressor\n",
    "\n",
    "errlgb = []\n",
    "y_pred_totlgb = []\n",
    "\n",
    "fold = KFold(n_splits=15, shuffle=True, random_state=42)\n",
    "\n",
    "for train_index, test_index in fold.split(X):\n",
    "    X_train, X_test = X.loc[train_index], X.loc[test_index]\n",
    "    y_train, y_test = y[train_index], y[test_index]\n",
    "\n",
    "    param = {'objective': 'regression',\n",
    "         'boosting': 'gbdt',  \n",
    "         'metric': 'l2_root',\n",
    "         'learning_rate': 0.05, \n",
    "         'num_iterations': 1000,\n",
    "         'num_leaves': 30,\n",
    "         'max_depth': -1,\n",
    "         'min_data_in_leaf': 4,\n",
    "         'bagging_fraction': 0.78,\n",
    "         'bagging_freq': 1,\n",
    "         'feature_fraction': 0.65\n",
    "         }\n",
    "\n",
    "    lgbm = LGBMRegressor(**param)\n",
    "    lgbm.fit(X_train, y_train, eval_set=[(X_test, y_test)], verbose=0, early_stopping_rounds=50)\n",
    "\n",
    "    y_pred_lgbm = lgbm.predict(X_test)\n",
    "    print(\"RMSLE LGBM: \", sqrt(mean_squared_log_error(np.expm1(y_test), np.expm1(y_pred_lgbm))))\n",
    "\n",
    "    errlgb.append(sqrt(mean_squared_log_error(np.expm1(y_test), np.expm1(y_pred_lgbm))))\n",
    "    p = lgbm.predict(Xtest)\n",
    "    \n",
    "    y_pred_totlgb.append(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1297982721289675"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.mean(errlgb) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "lgbm_final = np.expm1(np.mean(y_pred_totlgb,0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sub = pd.DataFrame(data=lgbm_final, columns=['Price'])\n",
    "writer = pd.ExcelWriter('Output.xlsx', engine='xlsxwriter')\n",
    "df_sub.to_excel(writer,sheet_name='Sheet1', index=False)\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1234, 1)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sub.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.593238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.956951</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>17.088192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.881875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.511628</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Price\n",
       "0   2.593238\n",
       "1   2.956951\n",
       "2  17.088192\n",
       "3   3.881875\n",
       "4   4.511628"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sub.head()"
   ]
  }
 ],
 "metadata": {
  "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.6.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "308.6px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}