Project_3.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exploring Ebay Car Sales Data\n",
    "\n",
    "---\n",
    "\n",
    "## 1.  Introduction\n",
    "\n",
    "\n",
    "This project is created to demonstrate the basics of data cleaning and data exploration using *pandas*.\n",
    "\n",
    "The dataset used in this project contain records of used cars from eBay-Kleinanzeigen, a classifieds section of the German eBay website.\n",
    "\n",
    "The dataset was originally scraped and uploaded to [Kaggle](https://www.kaggle.com/orgesleka/used-cars-database/data), but a few modifications have been made by DataQuest to the original dataset. About 50,000 data points were sampled from the full dataset, and the data was dirtied to make it more closely resemble what would be expected from a scraped dataset (the version uploaded to Kaggle was cleaned to be easier to work with).\n",
    "\n",
    "The data dictionary provided with data is as follows:\n",
    "- **dateCrawled** - When this ad was first crawled. All field-values are taken from this date.\n",
    "- **name** - Name of the car.\n",
    "- **seller** - Whether the seller is private or a dealer.\n",
    "- **offerType** - The type of listing\n",
    "- **price** - The price on the ad to sell the car.\n",
    "- **abtest** - Whether the listing is included in an A/B test.\n",
    "- **vehicleType** - The vehicle Type.\n",
    "- **yearOfRegistration** - The year in which the car was first registered.\n",
    "- **gearbox** - The transmission type.\n",
    "- **powerPS** - The power of the car in PS.\n",
    "- **model** - The car model name.\n",
    "- **kilometer** - How many kilometers the car has driven.\n",
    "- **monthOfRegistration** - The month in which the car was first registered.\n",
    "- **fuelType** - What type of fuel the car uses.\n",
    "- **brand** - The brand of the car.\n",
    "- **notRepairedDamage** - If the car has a damage which is not yet repaired.\n",
    "- **dateCreated** - The date on which the eBay listing was created.\n",
    "- **nrOfPictures** - The number of pictures in the ad.\n",
    "- **postalCode** - The postal code for the location of the vehicle.\n",
    "- **lastSeenOnline** - When the crawler saw this ad last online.\n",
    "\n",
    "   Note: The fields **lastSeenOnline** and **dateCreated** could be used to estimate how long a car will be at least online before it is sold.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.  Goal & Objective of This Project\n",
    "\n",
    "This project aims to clean the data and analyze the included used car listings.\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. General Observation of the Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Import the pandas and NumPy libraries.\n",
    "### Reading the dataset into pandas.\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "autos = pd.read_csv(\"autos.csv\", encoding = \"Latin-1\")\n",
    "\n",
    "### Note: Common encoding is \"UTF-8\" / \"Latin-1\" / \"Windows-1252\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>dateCrawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offerType</th>\n",
       "      <th>price</th>\n",
       "      <th>abtest</th>\n",
       "      <th>vehicleType</th>\n",
       "      <th>yearOfRegistration</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerPS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>monthOfRegistration</th>\n",
       "      <th>fuelType</th>\n",
       "      <th>brand</th>\n",
       "      <th>notRepairedDamage</th>\n",
       "      <th>dateCreated</th>\n",
       "      <th>nrOfPictures</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>lastSeen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$5,000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manuell</td>\n",
       "      <td>158</td>\n",
       "      <td>andere</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>3</td>\n",
       "      <td>lpg</td>\n",
       "      <td>peugeot</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>79588</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$4,350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49995</td>\n",
       "      <td>2016-03-27 14:38:19</td>\n",
       "      <td>Audi_Q5_3.0_TDI_qu._S_tr.__Navi__Panorama__Xenon</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$24,900</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2011</td>\n",
       "      <td>automatik</td>\n",
       "      <td>239</td>\n",
       "      <td>q5</td>\n",
       "      <td>100,000km</td>\n",
       "      <td>1</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-27 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>82131</td>\n",
       "      <td>2016-04-01 13:47:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49996</td>\n",
       "      <td>2016-03-28 10:50:25</td>\n",
       "      <td>Opel_Astra_F_Cabrio_Bertone_Edition___TÜV_neu+...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,980</td>\n",
       "      <td>control</td>\n",
       "      <td>cabrio</td>\n",
       "      <td>1996</td>\n",
       "      <td>manuell</td>\n",
       "      <td>75</td>\n",
       "      <td>astra</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>5</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-28 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>44807</td>\n",
       "      <td>2016-04-02 14:18:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49997</td>\n",
       "      <td>2016-04-02 14:44:48</td>\n",
       "      <td>Fiat_500_C_1.2_Dualogic_Lounge</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$13,200</td>\n",
       "      <td>test</td>\n",
       "      <td>cabrio</td>\n",
       "      <td>2014</td>\n",
       "      <td>automatik</td>\n",
       "      <td>69</td>\n",
       "      <td>500</td>\n",
       "      <td>5,000km</td>\n",
       "      <td>11</td>\n",
       "      <td>benzin</td>\n",
       "      <td>fiat</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-02 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>73430</td>\n",
       "      <td>2016-04-04 11:47:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49998</td>\n",
       "      <td>2016-03-08 19:25:42</td>\n",
       "      <td>Audi_A3_2.0_TDI_Sportback_Ambition</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$22,900</td>\n",
       "      <td>control</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2013</td>\n",
       "      <td>manuell</td>\n",
       "      <td>150</td>\n",
       "      <td>a3</td>\n",
       "      <td>40,000km</td>\n",
       "      <td>11</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-08 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35683</td>\n",
       "      <td>2016-04-05 16:45:07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49999</td>\n",
       "      <td>2016-03-14 00:42:12</td>\n",
       "      <td>Opel_Vectra_1.6_16V</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,250</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1996</td>\n",
       "      <td>manuell</td>\n",
       "      <td>101</td>\n",
       "      <td>vectra</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>1</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-13 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>45897</td>\n",
       "      <td>2016-04-06 21:18:48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50000 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               dateCrawled                                               name  \\\n",
       "0      2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1      2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2      2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3      2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4      2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "...                    ...                                                ...   \n",
       "49995  2016-03-27 14:38:19   Audi_Q5_3.0_TDI_qu._S_tr.__Navi__Panorama__Xenon   \n",
       "49996  2016-03-28 10:50:25  Opel_Astra_F_Cabrio_Bertone_Edition___TÜV_neu+...   \n",
       "49997  2016-04-02 14:44:48                     Fiat_500_C_1.2_Dualogic_Lounge   \n",
       "49998  2016-03-08 19:25:42                 Audi_A3_2.0_TDI_Sportback_Ambition   \n",
       "49999  2016-03-14 00:42:12                                Opel_Vectra_1.6_16V   \n",
       "\n",
       "       seller offerType    price   abtest vehicleType  yearOfRegistration  \\\n",
       "0      privat   Angebot   $5,000  control         bus                2004   \n",
       "1      privat   Angebot   $8,500  control   limousine                1997   \n",
       "2      privat   Angebot   $8,990     test   limousine                2009   \n",
       "3      privat   Angebot   $4,350  control  kleinwagen                2007   \n",
       "4      privat   Angebot   $1,350     test       kombi                2003   \n",
       "...       ...       ...      ...      ...         ...                 ...   \n",
       "49995  privat   Angebot  $24,900  control   limousine                2011   \n",
       "49996  privat   Angebot   $1,980  control      cabrio                1996   \n",
       "49997  privat   Angebot  $13,200     test      cabrio                2014   \n",
       "49998  privat   Angebot  $22,900  control       kombi                2013   \n",
       "49999  privat   Angebot   $1,250  control   limousine                1996   \n",
       "\n",
       "         gearbox  powerPS   model   odometer  monthOfRegistration fuelType  \\\n",
       "0        manuell      158  andere  150,000km                    3      lpg   \n",
       "1      automatik      286     7er  150,000km                    6   benzin   \n",
       "2        manuell      102    golf   70,000km                    7   benzin   \n",
       "3      automatik       71  fortwo   70,000km                    6   benzin   \n",
       "4        manuell        0   focus  150,000km                    7   benzin   \n",
       "...          ...      ...     ...        ...                  ...      ...   \n",
       "49995  automatik      239      q5  100,000km                    1   diesel   \n",
       "49996    manuell       75   astra  150,000km                    5   benzin   \n",
       "49997  automatik       69     500    5,000km                   11   benzin   \n",
       "49998    manuell      150      a3   40,000km                   11   diesel   \n",
       "49999    manuell      101  vectra  150,000km                    1   benzin   \n",
       "\n",
       "            brand notRepairedDamage          dateCreated  nrOfPictures  \\\n",
       "0         peugeot              nein  2016-03-26 00:00:00             0   \n",
       "1             bmw              nein  2016-04-04 00:00:00             0   \n",
       "2      volkswagen              nein  2016-03-26 00:00:00             0   \n",
       "3           smart              nein  2016-03-12 00:00:00             0   \n",
       "4            ford              nein  2016-04-01 00:00:00             0   \n",
       "...           ...               ...                  ...           ...   \n",
       "49995        audi              nein  2016-03-27 00:00:00             0   \n",
       "49996        opel              nein  2016-03-28 00:00:00             0   \n",
       "49997        fiat              nein  2016-04-02 00:00:00             0   \n",
       "49998        audi              nein  2016-03-08 00:00:00             0   \n",
       "49999        opel              nein  2016-03-13 00:00:00             0   \n",
       "\n",
       "       postalCode             lastSeen  \n",
       "0           79588  2016-04-06 06:45:54  \n",
       "1           71034  2016-04-06 14:45:08  \n",
       "2           35394  2016-04-06 20:15:37  \n",
       "3           33729  2016-03-15 03:16:28  \n",
       "4           39218  2016-04-01 14:38:50  \n",
       "...           ...                  ...  \n",
       "49995       82131  2016-04-01 13:47:40  \n",
       "49996       44807  2016-04-02 14:18:02  \n",
       "49997       73430  2016-04-04 11:47:27  \n",
       "49998       35683  2016-04-05 16:45:07  \n",
       "49999       45897  2016-04-06 21:18:48  \n",
       "\n",
       "[50000 rows x 20 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Showing a preview of the dataset.\n",
    "\n",
    "autos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "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>dateCrawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offerType</th>\n",
       "      <th>price</th>\n",
       "      <th>abtest</th>\n",
       "      <th>vehicleType</th>\n",
       "      <th>yearOfRegistration</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerPS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>monthOfRegistration</th>\n",
       "      <th>fuelType</th>\n",
       "      <th>brand</th>\n",
       "      <th>notRepairedDamage</th>\n",
       "      <th>dateCreated</th>\n",
       "      <th>nrOfPictures</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>lastSeen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$5,000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manuell</td>\n",
       "      <td>158</td>\n",
       "      <td>andere</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>3</td>\n",
       "      <td>lpg</td>\n",
       "      <td>peugeot</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>79588</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$4,350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dateCrawled                                               name  \\\n",
       "0  2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1  2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2  2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3  2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4  2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "\n",
       "   seller offerType   price   abtest vehicleType  yearOfRegistration  \\\n",
       "0  privat   Angebot  $5,000  control         bus                2004   \n",
       "1  privat   Angebot  $8,500  control   limousine                1997   \n",
       "2  privat   Angebot  $8,990     test   limousine                2009   \n",
       "3  privat   Angebot  $4,350  control  kleinwagen                2007   \n",
       "4  privat   Angebot  $1,350     test       kombi                2003   \n",
       "\n",
       "     gearbox  powerPS   model   odometer  monthOfRegistration fuelType  \\\n",
       "0    manuell      158  andere  150,000km                    3      lpg   \n",
       "1  automatik      286     7er  150,000km                    6   benzin   \n",
       "2    manuell      102    golf   70,000km                    7   benzin   \n",
       "3  automatik       71  fortwo   70,000km                    6   benzin   \n",
       "4    manuell        0   focus  150,000km                    7   benzin   \n",
       "\n",
       "        brand notRepairedDamage          dateCreated  nrOfPictures  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00             0   \n",
       "1         bmw              nein  2016-04-04 00:00:00             0   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00             0   \n",
       "3       smart              nein  2016-03-12 00:00:00             0   \n",
       "4        ford              nein  2016-04-01 00:00:00             0   \n",
       "\n",
       "   postalCode             lastSeen  \n",
       "0       79588  2016-04-06 06:45:54  \n",
       "1       71034  2016-04-06 14:45:08  \n",
       "2       35394  2016-04-06 20:15:37  \n",
       "3       33729  2016-03-15 03:16:28  \n",
       "4       39218  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Showing the first 5 rows of data.\n",
    "\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 50000 entries, 0 to 49999\n",
      "Data columns (total 20 columns):\n",
      "dateCrawled            50000 non-null object\n",
      "name                   50000 non-null object\n",
      "seller                 50000 non-null object\n",
      "offerType              50000 non-null object\n",
      "price                  50000 non-null object\n",
      "abtest                 50000 non-null object\n",
      "vehicleType            44905 non-null object\n",
      "yearOfRegistration     50000 non-null int64\n",
      "gearbox                47320 non-null object\n",
      "powerPS                50000 non-null int64\n",
      "model                  47242 non-null object\n",
      "odometer               50000 non-null object\n",
      "monthOfRegistration    50000 non-null int64\n",
      "fuelType               45518 non-null object\n",
      "brand                  50000 non-null object\n",
      "notRepairedDamage      40171 non-null object\n",
      "dateCreated            50000 non-null object\n",
      "nrOfPictures           50000 non-null int64\n",
      "postalCode             50000 non-null int64\n",
      "lastSeen               50000 non-null object\n",
      "dtypes: int64(5), object(15)\n",
      "memory usage: 7.6+ MB\n"
     ]
    }
   ],
   "source": [
    "### Getting the overview of all the dtypes used in the dataset, along with its shape, columns, and other information.\n",
    "\n",
    "autos.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dateCrawled               0\n",
       "name                      0\n",
       "seller                    0\n",
       "offerType                 0\n",
       "price                     0\n",
       "abtest                    0\n",
       "vehicleType            5095\n",
       "yearOfRegistration        0\n",
       "gearbox                2680\n",
       "powerPS                   0\n",
       "model                  2758\n",
       "odometer                  0\n",
       "monthOfRegistration       0\n",
       "fuelType               4482\n",
       "brand                     0\n",
       "notRepairedDamage      9829\n",
       "dateCreated               0\n",
       "nrOfPictures              0\n",
       "postalCode                0\n",
       "lastSeen                  0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Check columns that have null values.\n",
    "\n",
    "check_null = autos.isnull().sum()\n",
    "check_null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observations from the above:**\n",
    "\n",
    "- The dataset contains 20 columns, most of which are strings (objects).\n",
    "\n",
    "- Some columns have null values, but none have more than ~20% null values.\n",
    "\n",
    "- Most of the dates columns are stored as strings (objects).\n",
    "\n",
    "- The column names use [camelcase](https://en.wikipedia.org/wiki/Camel_case) instead of Python's preferred [snakecase](https://en.wikipedia.org/wiki/Snake_case), which means we can't just replace spaces with underscores.\n",
    "\n",
    "Let's start by cleaning the column names so that the dataset is easier to work with.\n",
    "\n",
    "We will use snakecase for the column names."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Cleaning Column Names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original Column Names:\n",
      "----------------------\n",
      "Index(['dateCrawled', 'name', 'seller', 'offerType', 'price', 'abtest',\n",
      "       'vehicleType', 'yearOfRegistration', 'gearbox', 'powerPS', 'model',\n",
      "       'odometer', 'monthOfRegistration', 'fuelType', 'brand',\n",
      "       'notRepairedDamage', 'dateCreated', 'nrOfPictures', 'postalCode',\n",
      "       'lastSeen'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(\"Original Column Names:\")\n",
    "print(\"----------------------\")\n",
    "print(autos.columns)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Define a customized function to rename the column of this dataset.\n",
    "### There are other more advanced technic to do this, but we will just use a simple basic function.\n",
    "\n",
    "def rename_column(column_name):\n",
    "    if column_name == \"yearOfRegistration\":\n",
    "        new_name = \"registration_year\"\n",
    "    elif column_name == \"monthOfRegistration\":\n",
    "        new_name = \"registration_month\"   \n",
    "    elif column_name == \"notRepairedDamage\":\n",
    "        new_name = \"unrepaired_damage\" \n",
    "    elif column_name == \"dateCreated\":\n",
    "        new_name = \"ad_created\" \n",
    "    elif column_name == \"dateCrawled\":\n",
    "        new_name = \"date_crawled\" \n",
    "    elif column_name == \"offerType\":\n",
    "        new_name = \"offer_type\" \n",
    "    elif column_name == \"abtest\":\n",
    "        new_name = \"ab_test\" \n",
    "    elif column_name == \"vehicleType\":\n",
    "        new_name = \"vehicle_type\" \n",
    "    elif column_name == \"gearbox\":\n",
    "        new_name = \"gear_box\" \n",
    "    elif column_name == \"powerPS\":\n",
    "        new_name = \"power_PS\" \n",
    "    elif column_name == \"fuelType\":\n",
    "        new_name = \"fuel_type\" \n",
    "    elif column_name == \"nrOfPictures\":\n",
    "        new_name = \"no_of_pictures\" \n",
    "    elif column_name == \"postalCode\":\n",
    "        new_name = \"postal_code\" \n",
    "    elif column_name == \"lastSeen\":\n",
    "        new_name = \"last_seen\" \n",
    "    else:\n",
    "        new_name = column_name\n",
    "    return new_name\n",
    "\n",
    "\n",
    "### Looping through each column in the dataset and call the function to rename the column.\n",
    "\n",
    "new_columns = []\n",
    "\n",
    "for c in autos.columns:\n",
    "    new_name = rename_column(c)\n",
    "    new_columns.append(new_name)\n",
    "\n",
    "autos.columns = new_columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Alternatively, can also directly assign the column names as shown below.\n",
    "\n",
    "autos.columns = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',\n",
    "       'vehicle_type', 'registration_year', 'gear_box', 'power_PS', 'model',\n",
    "       'odometer', 'registration_month', 'fuel_type', 'brand',\n",
    "       'unrepaired_damage', 'ad_created', 'no_of_pictures', 'postal_code',\n",
    "       'last_seen']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Renamed Column Names:\n",
      "---------------------\n",
      "Index(['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',\n",
      "       'vehicle_type', 'registration_year', 'gear_box', 'power_PS', 'model',\n",
      "       'odometer', 'registration_month', 'fuel_type', 'brand',\n",
      "       'unrepaired_damage', 'ad_created', 'no_of_pictures', 'postal_code',\n",
      "       'last_seen'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(\"Renamed Column Names:\")\n",
    "print(\"---------------------\")\n",
    "print(autos.columns)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>ab_test</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gear_box</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>no_of_pictures</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$5,000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manuell</td>\n",
       "      <td>158</td>\n",
       "      <td>andere</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>3</td>\n",
       "      <td>lpg</td>\n",
       "      <td>peugeot</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>79588</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$4,350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled                                               name  \\\n",
       "0  2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1  2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2  2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3  2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4  2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "\n",
       "   seller offer_type   price  ab_test vehicle_type  registration_year  \\\n",
       "0  privat    Angebot  $5,000  control          bus               2004   \n",
       "1  privat    Angebot  $8,500  control    limousine               1997   \n",
       "2  privat    Angebot  $8,990     test    limousine               2009   \n",
       "3  privat    Angebot  $4,350  control   kleinwagen               2007   \n",
       "4  privat    Angebot  $1,350     test        kombi               2003   \n",
       "\n",
       "    gear_box  power_PS   model   odometer  registration_month fuel_type  \\\n",
       "0    manuell       158  andere  150,000km                   3       lpg   \n",
       "1  automatik       286     7er  150,000km                   6    benzin   \n",
       "2    manuell       102    golf   70,000km                   7    benzin   \n",
       "3  automatik        71  fortwo   70,000km                   6    benzin   \n",
       "4    manuell         0   focus  150,000km                   7    benzin   \n",
       "\n",
       "        brand unrepaired_damage           ad_created  no_of_pictures  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00               0   \n",
       "1         bmw              nein  2016-04-04 00:00:00               0   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00               0   \n",
       "3       smart              nein  2016-03-12 00:00:00               0   \n",
       "4        ford              nein  2016-04-01 00:00:00               0   \n",
       "\n",
       "   postal_code            last_seen  \n",
       "0        79588  2016-04-06 06:45:54  \n",
       "1        71034  2016-04-06 14:45:08  \n",
       "2        35394  2016-04-06 20:15:37  \n",
       "3        33729  2016-03-15 03:16:28  \n",
       "4        39218  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Showing the first 5 rows of data with new column names.\n",
    "\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Initial Exploration - Checking General Statistics of the Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>registration_year</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>no_of_pictures</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>50000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>2005.073280</td>\n",
       "      <td>116.355920</td>\n",
       "      <td>5.723360</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50813.627300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>105.712813</td>\n",
       "      <td>209.216627</td>\n",
       "      <td>3.711984</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25779.747957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1067.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1999.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30451.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>2003.000000</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49577.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>71540.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>9999.000000</td>\n",
       "      <td>17700.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99998.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       registration_year      power_PS  registration_month  no_of_pictures  \\\n",
       "count       50000.000000  50000.000000        50000.000000         50000.0   \n",
       "mean         2005.073280    116.355920            5.723360             0.0   \n",
       "std           105.712813    209.216627            3.711984             0.0   \n",
       "min          1000.000000      0.000000            0.000000             0.0   \n",
       "25%          1999.000000     70.000000            3.000000             0.0   \n",
       "50%          2003.000000    105.000000            6.000000             0.0   \n",
       "75%          2008.000000    150.000000            9.000000             0.0   \n",
       "max          9999.000000  17700.000000           12.000000             0.0   \n",
       "\n",
       "        postal_code  \n",
       "count  50000.000000  \n",
       "mean   50813.627300  \n",
       "std    25779.747957  \n",
       "min     1067.000000  \n",
       "25%    30451.000000  \n",
       "50%    49577.000000  \n",
       "75%    71540.000000  \n",
       "max    99998.000000  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Check statistics for only numeric columns.\n",
    "\n",
    "autos.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>ab_test</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>gear_box</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>44905</td>\n",
       "      <td>47320</td>\n",
       "      <td>47242</td>\n",
       "      <td>50000</td>\n",
       "      <td>45518</td>\n",
       "      <td>50000</td>\n",
       "      <td>40171</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>unique</td>\n",
       "      <td>48213</td>\n",
       "      <td>38754</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2357</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "      <td>245</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>76</td>\n",
       "      <td>39481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>top</td>\n",
       "      <td>2016-03-30 19:48:02</td>\n",
       "      <td>Ford_Fiesta</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$0</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>manuell</td>\n",
       "      <td>golf</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-03 00:00:00</td>\n",
       "      <td>2016-04-07 06:17:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>freq</td>\n",
       "      <td>3</td>\n",
       "      <td>78</td>\n",
       "      <td>49999</td>\n",
       "      <td>49999</td>\n",
       "      <td>1421</td>\n",
       "      <td>25756</td>\n",
       "      <td>12859</td>\n",
       "      <td>36993</td>\n",
       "      <td>4024</td>\n",
       "      <td>32424</td>\n",
       "      <td>30107</td>\n",
       "      <td>10687</td>\n",
       "      <td>35232</td>\n",
       "      <td>1946</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               date_crawled         name  seller offer_type  price ab_test  \\\n",
       "count                 50000        50000   50000      50000  50000   50000   \n",
       "unique                48213        38754       2          2   2357       2   \n",
       "top     2016-03-30 19:48:02  Ford_Fiesta  privat    Angebot     $0    test   \n",
       "freq                      3           78   49999      49999   1421   25756   \n",
       "\n",
       "       vehicle_type gear_box  model   odometer fuel_type       brand  \\\n",
       "count         44905    47320  47242      50000     45518       50000   \n",
       "unique            8        2    245         13         7          40   \n",
       "top       limousine  manuell   golf  150,000km    benzin  volkswagen   \n",
       "freq          12859    36993   4024      32424     30107       10687   \n",
       "\n",
       "       unrepaired_damage           ad_created            last_seen  \n",
       "count              40171                50000                50000  \n",
       "unique                 2                   76                39481  \n",
       "top                 nein  2016-04-03 00:00:00  2016-04-07 06:17:27  \n",
       "freq               35232                 1946                    8  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Check statistics for object columns (non-numeric columns).\n",
    "\n",
    "autos.describe(include=['O'])"
   ]
  },
  {
   "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>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>ab_test</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gear_box</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>no_of_pictures</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>44905</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>47320</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>47242</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>45518</td>\n",
       "      <td>50000</td>\n",
       "      <td>40171</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>unique</td>\n",
       "      <td>48213</td>\n",
       "      <td>38754</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2357</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>245</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>top</td>\n",
       "      <td>2016-03-30 19:48:02</td>\n",
       "      <td>Ford_Fiesta</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$0</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>manuell</td>\n",
       "      <td>NaN</td>\n",
       "      <td>golf</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>NaN</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-03 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-04-07 06:17:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>freq</td>\n",
       "      <td>3</td>\n",
       "      <td>78</td>\n",
       "      <td>49999</td>\n",
       "      <td>49999</td>\n",
       "      <td>1421</td>\n",
       "      <td>25756</td>\n",
       "      <td>12859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4024</td>\n",
       "      <td>32424</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30107</td>\n",
       "      <td>10687</td>\n",
       "      <td>35232</td>\n",
       "      <td>1946</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005.073280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>116.355920</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.723360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50813.627300</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.712813</td>\n",
       "      <td>NaN</td>\n",
       "      <td>209.216627</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.711984</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25779.747957</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1067.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30451.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2003.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49577.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>71540.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9999.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17700.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99998.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               date_crawled         name  seller offer_type  price ab_test  \\\n",
       "count                 50000        50000   50000      50000  50000   50000   \n",
       "unique                48213        38754       2          2   2357       2   \n",
       "top     2016-03-30 19:48:02  Ford_Fiesta  privat    Angebot     $0    test   \n",
       "freq                      3           78   49999      49999   1421   25756   \n",
       "mean                    NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "std                     NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "min                     NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "25%                     NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "50%                     NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "75%                     NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "max                     NaN          NaN     NaN        NaN    NaN     NaN   \n",
       "\n",
       "       vehicle_type  registration_year gear_box      power_PS  model  \\\n",
       "count         44905       50000.000000    47320  50000.000000  47242   \n",
       "unique            8                NaN        2           NaN    245   \n",
       "top       limousine                NaN  manuell           NaN   golf   \n",
       "freq          12859                NaN    36993           NaN   4024   \n",
       "mean            NaN        2005.073280      NaN    116.355920    NaN   \n",
       "std             NaN         105.712813      NaN    209.216627    NaN   \n",
       "min             NaN        1000.000000      NaN      0.000000    NaN   \n",
       "25%             NaN        1999.000000      NaN     70.000000    NaN   \n",
       "50%             NaN        2003.000000      NaN    105.000000    NaN   \n",
       "75%             NaN        2008.000000      NaN    150.000000    NaN   \n",
       "max             NaN        9999.000000      NaN  17700.000000    NaN   \n",
       "\n",
       "         odometer  registration_month fuel_type       brand unrepaired_damage  \\\n",
       "count       50000        50000.000000     45518       50000             40171   \n",
       "unique         13                 NaN         7          40                 2   \n",
       "top     150,000km                 NaN    benzin  volkswagen              nein   \n",
       "freq        32424                 NaN     30107       10687             35232   \n",
       "mean          NaN            5.723360       NaN         NaN               NaN   \n",
       "std           NaN            3.711984       NaN         NaN               NaN   \n",
       "min           NaN            0.000000       NaN         NaN               NaN   \n",
       "25%           NaN            3.000000       NaN         NaN               NaN   \n",
       "50%           NaN            6.000000       NaN         NaN               NaN   \n",
       "75%           NaN            9.000000       NaN         NaN               NaN   \n",
       "max           NaN           12.000000       NaN         NaN               NaN   \n",
       "\n",
       "                 ad_created  no_of_pictures   postal_code            last_seen  \n",
       "count                 50000         50000.0  50000.000000                50000  \n",
       "unique                   76             NaN           NaN                39481  \n",
       "top     2016-04-03 00:00:00             NaN           NaN  2016-04-07 06:17:27  \n",
       "freq                   1946             NaN           NaN                    8  \n",
       "mean                    NaN             0.0  50813.627300                  NaN  \n",
       "std                     NaN             0.0  25779.747957                  NaN  \n",
       "min                     NaN             0.0   1067.000000                  NaN  \n",
       "25%                     NaN             0.0  30451.000000                  NaN  \n",
       "50%                     NaN             0.0  49577.000000                  NaN  \n",
       "75%                     NaN             0.0  71540.000000                  NaN  \n",
       "max                     NaN             0.0  99998.000000                  NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Check statistics for all columns.\n",
    "\n",
    "autos.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "Columns that have mostly one value (or where almost all of the values are the same) are candidates to be dropped:\n",
    "\n",
    "- `seller` (almost all records are \"privat\")\n",
    "- `offer_type` (almost all records are \"Angebot\")\n",
    "\n",
    "The `no_of_pictures` column looks odd, we'll need to investigate this further."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    50000\n",
       "Name: no_of_pictures, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Further investigation on the 'no_of_pictures' column.\n",
    "### All the values in this column is 0.\n",
    "\n",
    "autos[\"no_of_pictures\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Dropping Columns\n",
    "\n",
    "Drop the following columns that have mostly one value (or where almost all of the values are the same):\n",
    "- `seller` (almost all records are \"privat\")\n",
    "- `offer_type` (almost all records are \"Angebot\")\n",
    "- `no_of_pictures` (all records are \"0\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos = autos.drop([\"seller\", \"offer_type\", \"no_of_pictures\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Cleaning Numeric Data Stored As Text\n",
    "\n",
    "### 7.1 Converting the 'price' and 'odometer' column to numeric type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Replace dollar sign and comma with empty string before converting the 'price' column to int type.\n",
    "\n",
    "autos[\"price\"] = autos[\"price\"].str.replace(\"$\",\"\").str.replace(\",\",\"\").astype(int)\n",
    "\n",
    "\n",
    "### Replace 'km' with empty string before converting the 'odometer' column to int type.\n",
    "\n",
    "autos[\"odometer\"] = autos[\"odometer\"].str.replace(\"km\",\"\").str.replace(\",\",\"\").astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7.2 Renaming the 'odometer' column to 'odometer_km'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Renaming the 'odometer' column to 'odometer_km' so that we know the numeric values are in km.\n",
    "\n",
    "autos.rename({\"odometer\":\"odometer_km\"}, axis=1, inplace=True)\n",
    "\n",
    "### Note: Either use inplace=True or assign the result back to the dataframe; otherwise, the modifications will be lost.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Identify Outliers (values that look unrealistically high or low)\n",
    "\n",
    "### 8.1 Inspecting the 'price' Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2357,)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the number of unique price values.\n",
    "\n",
    "autos[\"price\"].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    5.000000e+04\n",
       "mean     9.840044e+03\n",
       "std      4.811044e+05\n",
       "min      0.000000e+00\n",
       "25%      1.100000e+03\n",
       "50%      2.950000e+03\n",
       "75%      7.200000e+03\n",
       "max      1.000000e+08\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking statistics of the price column.\n",
    "\n",
    "autos[\"price\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           1421\n",
       "1            156\n",
       "2              3\n",
       "3              1\n",
       "5              2\n",
       "            ... \n",
       "10000000       1\n",
       "11111111       2\n",
       "12345678       3\n",
       "27322222       1\n",
       "99999999       1\n",
       "Name: price, Length: 2357, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the count for each unique price value.\n",
    "\n",
    "autos[\"price\"].value_counts().sort_index(ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     0.02842\n",
       "1     0.00312\n",
       "2     0.00006\n",
       "3     0.00002\n",
       "5     0.00004\n",
       "8     0.00002\n",
       "9     0.00002\n",
       "10    0.00014\n",
       "11    0.00004\n",
       "12    0.00006\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"price\"].value_counts(normalize=True).sort_index().head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99999999    1\n",
       "27322222    1\n",
       "12345678    3\n",
       "11111111    2\n",
       "10000000    1\n",
       "3890000     1\n",
       "1300000     1\n",
       "1234566     1\n",
       "999999      2\n",
       "999990      1\n",
       "350000      1\n",
       "345000      1\n",
       "299000      1\n",
       "295000      1\n",
       "265000      1\n",
       "259000      1\n",
       "250000      1\n",
       "220000      1\n",
       "198000      1\n",
       "197000      1\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Take a closer look on prices that are on the high end (sort by descending order).\n",
    "\n",
    "autos[\"price\"].value_counts().sort_index(ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "- There are 2357 unique price values.\n",
    "\n",
    "- 1421 records are with 0 price (which is about 2.8% of the listing), and the maximum price is one hundred million (99,999,999). \n",
    "\n",
    "- For prices that are on the high end, it seems like prices increased steadily until 350,000 and from there jump to unrealistically high values. There are 14 records with prices greater than 350,000.\n",
    "\n",
    "- These unrealistic records, which are less than 3% of the listing, shall be removed later."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.2 Inspecting the 'odometer_km' Column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13,)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the number of unique mileage values.\n",
    "\n",
    "autos[\"odometer_km\"].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     50000.000000\n",
       "mean     125732.700000\n",
       "std       40042.211706\n",
       "min        5000.000000\n",
       "25%      125000.000000\n",
       "50%      150000.000000\n",
       "75%      150000.000000\n",
       "max      150000.000000\n",
       "Name: odometer_km, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking statistics of the mileage.\n",
    "\n",
    "autos[\"odometer_km\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5000        967\n",
       "10000       264\n",
       "20000       784\n",
       "30000       789\n",
       "40000       819\n",
       "50000      1027\n",
       "60000      1164\n",
       "70000      1230\n",
       "80000      1436\n",
       "90000      1757\n",
       "100000     2169\n",
       "125000     5170\n",
       "150000    32424\n",
       "Name: odometer_km, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the count for each unique mileage value.\n",
    "\n",
    "autos[\"odometer_km\"].value_counts().sort_index(ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "There seems to be more records with high mileage than low mileage in the listing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.3 Inspecting Dates\n",
    "\n",
    "As seen below, dates for `date_crawled`, `ad_created`, and `last_seen` are all identified as string values by pandas.\n",
    "We need to convert the data into a numerical representation so we can understand it quantitatively.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>date_crawled</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled           ad_created            last_seen\n",
       "0  2016-03-26 17:47:46  2016-03-26 00:00:00  2016-04-06 06:45:54\n",
       "1  2016-04-04 13:38:56  2016-04-04 00:00:00  2016-04-06 14:45:08\n",
       "2  2016-03-26 18:57:24  2016-03-26 00:00:00  2016-04-06 20:15:37\n",
       "3  2016-03-12 16:58:10  2016-03-12 00:00:00  2016-03-15 03:16:28\n",
       "4  2016-04-01 14:38:50  2016-04-01 00:00:00  2016-04-01 14:38:50"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Showing the first 5 records of 'date_crawled', 'ad_created', and 'last_seen'.\n",
    "\n",
    "autos[['date_crawled','ad_created','last_seen']][0:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.3.1 Inspecting 'date_crawled'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-05    1269\n",
       "2016-03-06     697\n",
       "2016-03-07    1798\n",
       "2016-03-08    1665\n",
       "2016-03-09    1661\n",
       "2016-03-10    1606\n",
       "2016-03-11    1624\n",
       "2016-03-12    1839\n",
       "2016-03-13     778\n",
       "2016-03-14    1831\n",
       "2016-03-15    1699\n",
       "2016-03-16    1475\n",
       "2016-03-17    1576\n",
       "2016-03-18     653\n",
       "2016-03-19    1745\n",
       "2016-03-20    1891\n",
       "2016-03-21    1876\n",
       "2016-03-22    1647\n",
       "2016-03-23    1619\n",
       "2016-03-24    1455\n",
       "2016-03-25    1587\n",
       "2016-03-26    1624\n",
       "2016-03-27    1552\n",
       "2016-03-28    1742\n",
       "2016-03-29    1709\n",
       "2016-03-30    1681\n",
       "2016-03-31    1596\n",
       "2016-04-01    1690\n",
       "2016-04-02    1770\n",
       "2016-04-03    1934\n",
       "2016-04-04    1826\n",
       "2016-04-05     655\n",
       "2016-04-06     159\n",
       "2016-04-07      71\n",
       "Name: date_crawled, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Select only the first 10 characters of the date to generate a distribution, and then sort by the index.\n",
    "### To include missing values in the distribution with 'dropna=False'\n",
    "### Use sort_index() to rank by date in ascending order.\n",
    "\n",
    "autos[\"date_crawled\"].str[:10].value_counts(dropna=False).sort_index()\n",
    "\n",
    "# Following code is to use percentages instead of counts with 'normalize=True'.\n",
    "# autos[\"date_crawled\"].str[:10].value_counts(normalize=True, dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count          50000\n",
       "unique            34\n",
       "top       2016-04-03\n",
       "freq            1934\n",
       "Name: date_crawled, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"date_crawled\"].str[:10].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "- There are 34 unique date value for `date_crawled`.\n",
    "- Looks like the website is crawled daily for about a month in March and April 2016 (from 2016-03-05 to 2016-04-07).\n",
    "- The distribution of listings crawled on each day is roughly uniform.\n",
    "- The date with the highest number of ads crawled, 1934, is on 2016-04-03."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.3.2 Inspecting 'ad_created' Date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count          50000\n",
       "unique            76\n",
       "top       2016-04-03\n",
       "freq            1946\n",
       "Name: ad_created, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"ad_created\"].str[:10].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2015-06-11       1\n",
       "2015-08-10       1\n",
       "2015-09-09       1\n",
       "2015-11-10       1\n",
       "2015-12-05       1\n",
       "              ... \n",
       "2016-04-03    1946\n",
       "2016-04-04    1844\n",
       "2016-04-05     592\n",
       "2016-04-06     163\n",
       "2016-04-07      64\n",
       "Name: ad_created, Length: 76, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Very few ads are created on 2015.\n",
    "### Let's check further below.\n",
    "\n",
    "autos[\"ad_created\"].str[:10].value_counts(dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2015-06-11       1\n",
       "2015-08-10       1\n",
       "2015-09-09       1\n",
       "2015-11-10       1\n",
       "2015-12-05       1\n",
       "2015-12-30       1\n",
       "2016-01-03       1\n",
       "2016-01-07       1\n",
       "2016-01-10       2\n",
       "2016-01-13       1\n",
       "2016-01-14       1\n",
       "2016-01-16       1\n",
       "2016-01-22       1\n",
       "2016-01-27       3\n",
       "2016-01-29       1\n",
       "2016-02-01       1\n",
       "2016-02-02       2\n",
       "2016-02-05       2\n",
       "2016-02-07       1\n",
       "2016-02-08       1\n",
       "2016-02-09       2\n",
       "2016-02-11       1\n",
       "2016-02-12       3\n",
       "2016-02-14       2\n",
       "2016-02-16       1\n",
       "2016-02-17       1\n",
       "2016-02-18       2\n",
       "2016-02-19       3\n",
       "2016-02-20       2\n",
       "2016-02-21       3\n",
       "2016-02-22       1\n",
       "2016-02-23       4\n",
       "2016-02-24       2\n",
       "2016-02-25       3\n",
       "2016-02-26       2\n",
       "2016-02-27       6\n",
       "2016-02-28      10\n",
       "2016-02-29       8\n",
       "2016-03-01       5\n",
       "2016-03-02       5\n",
       "2016-03-03      43\n",
       "2016-03-04      72\n",
       "2016-03-05    1152\n",
       "2016-03-06     756\n",
       "2016-03-07    1737\n",
       "2016-03-08    1667\n",
       "2016-03-09    1662\n",
       "2016-03-10    1593\n",
       "2016-03-11    1639\n",
       "2016-03-12    1831\n",
       "Name: ad_created, dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Noticed that there are significant number of ads created on and after March 2016.\n",
    "### Very few ads are created before March 2016.\n",
    "\n",
    "autos[\"ad_created\"].str[:10].value_counts(dropna=False).sort_index().head(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-02-18       2\n",
       "2016-02-19       3\n",
       "2016-02-20       2\n",
       "2016-02-21       3\n",
       "2016-02-22       1\n",
       "2016-02-23       4\n",
       "2016-02-24       2\n",
       "2016-02-25       3\n",
       "2016-02-26       2\n",
       "2016-02-27       6\n",
       "2016-02-28      10\n",
       "2016-02-29       8\n",
       "2016-03-01       5\n",
       "2016-03-02       5\n",
       "2016-03-03      43\n",
       "2016-03-04      72\n",
       "2016-03-05    1152\n",
       "2016-03-06     756\n",
       "2016-03-07    1737\n",
       "2016-03-08    1667\n",
       "2016-03-09    1662\n",
       "2016-03-10    1593\n",
       "2016-03-11    1639\n",
       "2016-03-12    1831\n",
       "2016-03-13     846\n",
       "2016-03-14    1761\n",
       "2016-03-15    1687\n",
       "2016-03-16    1500\n",
       "2016-03-17    1560\n",
       "2016-03-18     686\n",
       "2016-03-19    1692\n",
       "2016-03-20    1893\n",
       "2016-03-21    1886\n",
       "2016-03-22    1640\n",
       "2016-03-23    1609\n",
       "2016-03-24    1454\n",
       "2016-03-25    1594\n",
       "2016-03-26    1628\n",
       "2016-03-27    1545\n",
       "2016-03-28    1748\n",
       "2016-03-29    1707\n",
       "2016-03-30    1672\n",
       "2016-03-31    1596\n",
       "2016-04-01    1690\n",
       "2016-04-02    1754\n",
       "2016-04-03    1946\n",
       "2016-04-04    1844\n",
       "2016-04-05     592\n",
       "2016-04-06     163\n",
       "2016-04-07      64\n",
       "Name: ad_created, dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"ad_created\"].str[:10].value_counts(dropna=False).sort_index().tail(50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "- There are 76 unique date value for `ad_created`.\n",
    "\n",
    "- Noticed that there are significant number of ads created on and after March 2016, particularly during the crawling period which is from 2016-03-05 to 2016-04-07.\n",
    "\n",
    "- Very few ads are created before March 2016.\n",
    "\n",
    "- The date with the highest number of ads created, 1946, is on 2016-04-03.\n",
    "\n",
    "- This is also the same date where the highest number of ads are crawled."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.3.3 Inspecting 'last_seen' Date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count          50000\n",
       "unique            34\n",
       "top       2016-04-06\n",
       "freq           11050\n",
       "Name: last_seen, dtype: object"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"last_seen\"].str[:10].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-05       54\n",
       "2016-03-06      221\n",
       "2016-03-07      268\n",
       "2016-03-08      380\n",
       "2016-03-09      493\n",
       "2016-03-10      538\n",
       "2016-03-11      626\n",
       "2016-03-12     1191\n",
       "2016-03-13      449\n",
       "2016-03-14      640\n",
       "2016-03-15      794\n",
       "2016-03-16      822\n",
       "2016-03-17     1396\n",
       "2016-03-18      371\n",
       "2016-03-19      787\n",
       "2016-03-20     1035\n",
       "2016-03-21     1037\n",
       "2016-03-22     1079\n",
       "2016-03-23      929\n",
       "2016-03-24      978\n",
       "2016-03-25      960\n",
       "2016-03-26      848\n",
       "2016-03-27      801\n",
       "2016-03-28     1043\n",
       "2016-03-29     1117\n",
       "2016-03-30     1242\n",
       "2016-03-31     1192\n",
       "2016-04-01     1155\n",
       "2016-04-02     1245\n",
       "2016-04-03     1268\n",
       "2016-04-04     1231\n",
       "2016-04-05     6214\n",
       "2016-04-06    11050\n",
       "2016-04-07     6546\n",
       "Name: last_seen, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### There is significant number of ads that are last seen on 2016-04-06.\n",
    "\n",
    "autos[\"last_seen\"].str[:10].value_counts(dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "- There are 34 unique date value for `last_seen`.\n",
    "\n",
    "- There is a spike of records (6214, 11050, 6546) with `last seen` date on the last 3 days of the crawling period.\n",
    "\n",
    "- The highest number of records (11050) are last seen on 2016-04-06, a day before the crawling period ends.\n",
    "\n",
    "- This is unlikely due to a spike in sales."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8.4 Inspecting 'registration_year'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    50000.000000\n",
       "mean      2005.073280\n",
       "std        105.712813\n",
       "min       1000.000000\n",
       "25%       1999.000000\n",
       "50%       2003.000000\n",
       "75%       2008.000000\n",
       "max       9999.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"registration_year\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000    3354\n",
       "2005    3015\n",
       "1999    3000\n",
       "2004    2737\n",
       "2003    2727\n",
       "        ... \n",
       "1931       1\n",
       "1929       1\n",
       "1001       1\n",
       "9996       1\n",
       "1952       1\n",
       "Name: registration_year, Length: 97, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"registration_year\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000    1\n",
       "1001    1\n",
       "1111    1\n",
       "1500    1\n",
       "1800    2\n",
       "       ..\n",
       "6200    1\n",
       "8888    1\n",
       "9000    2\n",
       "9996    1\n",
       "9999    4\n",
       "Name: registration_year, Length: 97, dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"registration_year\"].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "The year that the car was first registered will likely indicate the age of the car. Looking at this column, we note some odd values. The minimum value is 1000, long before cars were invented and the maximum is 9999, many years into the future.\n",
    "\n",
    "Because a car can't be first registered after the listing was seen, any vehicle with a registration year above 2016 is definitely inaccurate. Determining the earliest valid year is more difficult. Realistically, it could be somewhere in the first few decades of the 1900s.\n",
    "\n",
    "Let's count the number of listings with cars that fall outside the 1900 - 2016 interval and see if it's safe to remove those rows entirely, or if we need more custom logic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>ab_test</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gear_box</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer_km</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>10</td>\n",
       "      <td>2016-03-15 01:41:36</td>\n",
       "      <td>VW_Golf_Tuning_in_siber/grau</td>\n",
       "      <td>999</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>manuell</td>\n",
       "      <td>90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150000</td>\n",
       "      <td>4</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-14 00:00:00</td>\n",
       "      <td>86157</td>\n",
       "      <td>2016-04-07 03:16:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>55</td>\n",
       "      <td>2016-03-07 02:47:54</td>\n",
       "      <td>Mercedes_E320_AMG_zu_Tauschen!</td>\n",
       "      <td>1</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>automatik</td>\n",
       "      <td>224</td>\n",
       "      <td>e_klasse</td>\n",
       "      <td>125000</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-06 00:00:00</td>\n",
       "      <td>22111</td>\n",
       "      <td>2016-03-08 05:45:44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>65</td>\n",
       "      <td>2016-04-04 19:30:39</td>\n",
       "      <td>Ford_Fiesta_zum_ausschlachten</td>\n",
       "      <td>250</td>\n",
       "      <td>control</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>manuell</td>\n",
       "      <td>65</td>\n",
       "      <td>fiesta</td>\n",
       "      <td>125000</td>\n",
       "      <td>9</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>65606</td>\n",
       "      <td>2016-04-05 12:22:12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>68</td>\n",
       "      <td>2016-04-03 17:36:59</td>\n",
       "      <td>Mini_cooper_s_clubman_/vollausstattung_/_Navi/...</td>\n",
       "      <td>10990</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>manuell</td>\n",
       "      <td>174</td>\n",
       "      <td>clubman</td>\n",
       "      <td>100000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>mini</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-03 00:00:00</td>\n",
       "      <td>83135</td>\n",
       "      <td>2016-04-05 17:26:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>84</td>\n",
       "      <td>2016-03-27 19:52:54</td>\n",
       "      <td>Renault_twingo</td>\n",
       "      <td>900</td>\n",
       "      <td>control</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>60</td>\n",
       "      <td>twingo</td>\n",
       "      <td>150000</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>renault</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-03-27 00:00:00</td>\n",
       "      <td>40589</td>\n",
       "      <td>2016-04-05 18:46:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49796</td>\n",
       "      <td>2016-03-09 09:38:38</td>\n",
       "      <td>Opel_corsa_1.4_zu_verkaufen</td>\n",
       "      <td>4500</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>manuell</td>\n",
       "      <td>90</td>\n",
       "      <td>corsa</td>\n",
       "      <td>70000</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-09 00:00:00</td>\n",
       "      <td>88433</td>\n",
       "      <td>2016-03-17 20:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49841</td>\n",
       "      <td>2016-03-11 15:37:02</td>\n",
       "      <td>Passat_abzugeben.</td>\n",
       "      <td>600</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>manuell</td>\n",
       "      <td>101</td>\n",
       "      <td>passat</td>\n",
       "      <td>150000</td>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-03-11 00:00:00</td>\n",
       "      <td>53804</td>\n",
       "      <td>2016-03-11 16:41:14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49880</td>\n",
       "      <td>2016-03-30 08:52:57</td>\n",
       "      <td>E39_528i_an_Bastler</td>\n",
       "      <td>0</td>\n",
       "      <td>control</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>manuell</td>\n",
       "      <td>193</td>\n",
       "      <td>5er</td>\n",
       "      <td>150000</td>\n",
       "      <td>4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>bmw</td>\n",
       "      <td>ja</td>\n",
       "      <td>2016-03-30 00:00:00</td>\n",
       "      <td>65468</td>\n",
       "      <td>2016-04-07 01:15:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49910</td>\n",
       "      <td>2016-04-03 21:39:15</td>\n",
       "      <td>Schoener_fast_neuer_Opel_Mokka_in_Zell_Mosel_m...</td>\n",
       "      <td>22200</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9000</td>\n",
       "      <td>automatik</td>\n",
       "      <td>140</td>\n",
       "      <td>andere</td>\n",
       "      <td>10000</td>\n",
       "      <td>3</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-04-03 00:00:00</td>\n",
       "      <td>56856</td>\n",
       "      <td>2016-04-05 22:18:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49935</td>\n",
       "      <td>2016-04-01 21:48:20</td>\n",
       "      <td>Mercedes_A_klasse_angemeldet_mit_Tuef_und_Auto...</td>\n",
       "      <td>800</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2017</td>\n",
       "      <td>automatik</td>\n",
       "      <td>101</td>\n",
       "      <td>a_klasse</td>\n",
       "      <td>150000</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>39108</td>\n",
       "      <td>2016-04-01 21:48:20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1972 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              date_crawled                                               name  \\\n",
       "10     2016-03-15 01:41:36                       VW_Golf_Tuning_in_siber/grau   \n",
       "55     2016-03-07 02:47:54                     Mercedes_E320_AMG_zu_Tauschen!   \n",
       "65     2016-04-04 19:30:39                      Ford_Fiesta_zum_ausschlachten   \n",
       "68     2016-04-03 17:36:59  Mini_cooper_s_clubman_/vollausstattung_/_Navi/...   \n",
       "84     2016-03-27 19:52:54                                     Renault_twingo   \n",
       "...                    ...                                                ...   \n",
       "49796  2016-03-09 09:38:38                        Opel_corsa_1.4_zu_verkaufen   \n",
       "49841  2016-03-11 15:37:02                                  Passat_abzugeben.   \n",
       "49880  2016-03-30 08:52:57                                E39_528i_an_Bastler   \n",
       "49910  2016-04-03 21:39:15  Schoener_fast_neuer_Opel_Mokka_in_Zell_Mosel_m...   \n",
       "49935  2016-04-01 21:48:20  Mercedes_A_klasse_angemeldet_mit_Tuef_und_Auto...   \n",
       "\n",
       "       price  ab_test vehicle_type  registration_year   gear_box  power_PS  \\\n",
       "10       999     test          NaN               2017    manuell        90   \n",
       "55         1     test          NaN               2017  automatik       224   \n",
       "65       250  control          NaN               2017    manuell        65   \n",
       "68     10990     test          NaN               2017    manuell       174   \n",
       "84       900  control          NaN               2018        NaN        60   \n",
       "...      ...      ...          ...                ...        ...       ...   \n",
       "49796   4500     test          NaN               2017    manuell        90   \n",
       "49841    600     test          NaN               2017    manuell       101   \n",
       "49880      0  control          NaN               2017    manuell       193   \n",
       "49910  22200     test          NaN               9000  automatik       140   \n",
       "49935    800     test          NaN               2017  automatik       101   \n",
       "\n",
       "          model  odometer_km  registration_month fuel_type          brand  \\\n",
       "10          NaN       150000                   4    benzin     volkswagen   \n",
       "55     e_klasse       125000                   7    benzin  mercedes_benz   \n",
       "65       fiesta       125000                   9    benzin           ford   \n",
       "68      clubman       100000                   0       NaN           mini   \n",
       "84       twingo       150000                   0       NaN        renault   \n",
       "...         ...          ...                 ...       ...            ...   \n",
       "49796     corsa        70000                   7    benzin           opel   \n",
       "49841    passat       150000                   7       NaN     volkswagen   \n",
       "49880       5er       150000                   4       NaN            bmw   \n",
       "49910    andere        10000                   3    benzin           opel   \n",
       "49935  a_klasse       150000                   9       NaN  mercedes_benz   \n",
       "\n",
       "      unrepaired_damage           ad_created  postal_code            last_seen  \n",
       "10                 nein  2016-03-14 00:00:00        86157  2016-04-07 03:16:21  \n",
       "55                 nein  2016-03-06 00:00:00        22111  2016-03-08 05:45:44  \n",
       "65                  NaN  2016-04-04 00:00:00        65606  2016-04-05 12:22:12  \n",
       "68                 nein  2016-04-03 00:00:00        83135  2016-04-05 17:26:26  \n",
       "84                  NaN  2016-03-27 00:00:00        40589  2016-04-05 18:46:49  \n",
       "...                 ...                  ...          ...                  ...  \n",
       "49796              nein  2016-03-09 00:00:00        88433  2016-03-17 20:45:08  \n",
       "49841               NaN  2016-03-11 00:00:00        53804  2016-03-11 16:41:14  \n",
       "49880                ja  2016-03-30 00:00:00        65468  2016-04-07 01:15:27  \n",
       "49910               NaN  2016-04-03 00:00:00        56856  2016-04-05 22:18:26  \n",
       "49935              nein  2016-04-01 00:00:00        39108  2016-04-01 21:48:20  \n",
       "\n",
       "[1972 rows x 17 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the number of records where 'registration_year' is before 1900 OR after 2016.\n",
    "\n",
    "autos[(autos[\"registration_year\"] < 1900) | (autos[\"registration_year\"] > 2016)]\n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 1972 records where 'registration_year' is before 1900 OR after 2016, which is about 4%.\n",
    "\n",
    "We will remove these outliers in the following section."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Remove Outliers\n",
    "\n",
    "### 9.1 Remove records with unrealistic 'registration_year'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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",
       "      <th>registration_year</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>odometer_km</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>4.802800e+04</td>\n",
       "      <td>48028.00000</td>\n",
       "      <td>48028.000000</td>\n",
       "      <td>48028.000000</td>\n",
       "      <td>48028.000000</td>\n",
       "      <td>48028.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>9.585252e+03</td>\n",
       "      <td>2002.80351</td>\n",
       "      <td>117.070417</td>\n",
       "      <td>125544.161739</td>\n",
       "      <td>5.767760</td>\n",
       "      <td>50935.867327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>4.843817e+05</td>\n",
       "      <td>7.31085</td>\n",
       "      <td>195.151278</td>\n",
       "      <td>40106.751417</td>\n",
       "      <td>3.696802</td>\n",
       "      <td>25792.079828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1910.00000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1067.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1.150000e+03</td>\n",
       "      <td>1999.00000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>30459.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>2.990000e+03</td>\n",
       "      <td>2003.00000</td>\n",
       "      <td>107.000000</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>49696.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>7.400000e+03</td>\n",
       "      <td>2008.00000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>71665.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>2016.00000</td>\n",
       "      <td>17700.000000</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>99998.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              price  registration_year      power_PS    odometer_km  \\\n",
       "count  4.802800e+04        48028.00000  48028.000000   48028.000000   \n",
       "mean   9.585252e+03         2002.80351    117.070417  125544.161739   \n",
       "std    4.843817e+05            7.31085    195.151278   40106.751417   \n",
       "min    0.000000e+00         1910.00000      0.000000    5000.000000   \n",
       "25%    1.150000e+03         1999.00000     71.000000  100000.000000   \n",
       "50%    2.990000e+03         2003.00000    107.000000  150000.000000   \n",
       "75%    7.400000e+03         2008.00000    150.000000  150000.000000   \n",
       "max    1.000000e+08         2016.00000  17700.000000  150000.000000   \n",
       "\n",
       "       registration_month   postal_code  \n",
       "count        48028.000000  48028.000000  \n",
       "mean             5.767760  50935.867327  \n",
       "std              3.696802  25792.079828  \n",
       "min              0.000000   1067.000000  \n",
       "25%              3.000000  30459.000000  \n",
       "50%              6.000000  49696.000000  \n",
       "75%              9.000000  71665.000000  \n",
       "max             12.000000  99998.000000  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Filter records where 'registration_year' is between 1900 and 2016.\n",
    "\n",
    "boo = autos[\"registration_year\"].between(1900, 2016)\n",
    "autos2 = autos.loc[boo]\n",
    "autos2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000    0.069834\n",
       "2005    0.062776\n",
       "1999    0.062464\n",
       "2004    0.056988\n",
       "2003    0.056779\n",
       "          ...   \n",
       "1939    0.000021\n",
       "1927    0.000021\n",
       "1929    0.000021\n",
       "1948    0.000021\n",
       "1952    0.000021\n",
       "Name: registration_year, Length: 78, dtype: float64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos2[\"registration_year\"].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 9.2 Remove records with unrealistic 'price'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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",
       "      <th>registration_year</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>odometer_km</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>46681.000000</td>\n",
       "      <td>46681.000000</td>\n",
       "      <td>46681.000000</td>\n",
       "      <td>46681.000000</td>\n",
       "      <td>46681.000000</td>\n",
       "      <td>46681.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mean</td>\n",
       "      <td>5977.716801</td>\n",
       "      <td>2002.910756</td>\n",
       "      <td>117.892933</td>\n",
       "      <td>125586.855466</td>\n",
       "      <td>5.827125</td>\n",
       "      <td>51097.434181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>std</td>\n",
       "      <td>9177.909479</td>\n",
       "      <td>7.185103</td>\n",
       "      <td>184.922911</td>\n",
       "      <td>39852.528628</td>\n",
       "      <td>3.670300</td>\n",
       "      <td>25755.387192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>min</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1910.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1067.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>25%</td>\n",
       "      <td>1250.000000</td>\n",
       "      <td>1999.000000</td>\n",
       "      <td>75.000000</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>30827.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>50%</td>\n",
       "      <td>3100.000000</td>\n",
       "      <td>2003.000000</td>\n",
       "      <td>109.000000</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>49828.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>75%</td>\n",
       "      <td>7500.000000</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>71732.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>max</td>\n",
       "      <td>350000.000000</td>\n",
       "      <td>2016.000000</td>\n",
       "      <td>17700.000000</td>\n",
       "      <td>150000.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>99998.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               price  registration_year      power_PS    odometer_km  \\\n",
       "count   46681.000000       46681.000000  46681.000000   46681.000000   \n",
       "mean     5977.716801        2002.910756    117.892933  125586.855466   \n",
       "std      9177.909479           7.185103    184.922911   39852.528628   \n",
       "min         1.000000        1910.000000      0.000000    5000.000000   \n",
       "25%      1250.000000        1999.000000     75.000000  100000.000000   \n",
       "50%      3100.000000        2003.000000    109.000000  150000.000000   \n",
       "75%      7500.000000        2008.000000    150.000000  150000.000000   \n",
       "max    350000.000000        2016.000000  17700.000000  150000.000000   \n",
       "\n",
       "       registration_month   postal_code  \n",
       "count        46681.000000  46681.000000  \n",
       "mean             5.827125  51097.434181  \n",
       "std              3.670300  25755.387192  \n",
       "min              0.000000   1067.000000  \n",
       "25%              3.000000  30827.000000  \n",
       "50%              6.000000  49828.000000  \n",
       "75%              9.000000  71732.000000  \n",
       "max             12.000000  99998.000000  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Filter records where 'price' is not 0, and not greater than 350,000.\n",
    "\n",
    "boo = autos2[\"price\"].between(1, 350000)\n",
    "autos2 = autos2.loc[boo]\n",
    "autos2.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Find Popular Brands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['peugeot' 'bmw' 'volkswagen' 'smart' 'ford' 'chrysler' 'seat' 'renault'\n",
      " 'mercedes_benz' 'audi' 'sonstige_autos' 'opel' 'mazda' 'porsche' 'mini'\n",
      " 'toyota' 'dacia' 'nissan' 'jeep' 'saab' 'volvo' 'mitsubishi' 'jaguar'\n",
      " 'fiat' 'skoda' 'subaru' 'kia' 'citroen' 'chevrolet' 'hyundai' 'honda'\n",
      " 'daewoo' 'suzuki' 'trabant' 'land_rover' 'alfa_romeo' 'lada' 'rover'\n",
      " 'daihatsu' 'lancia']\n"
     ]
    }
   ],
   "source": [
    "### Create an array of unique brands\n",
    "\n",
    "brands = autos2[\"brand\"].unique()\n",
    "print(brands)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen        9862\n",
       "bmw               5137\n",
       "opel              5022\n",
       "mercedes_benz     4503\n",
       "audi              4041\n",
       "ford              3263\n",
       "renault           2201\n",
       "peugeot           1393\n",
       "fiat              1197\n",
       "seat               853\n",
       "skoda              766\n",
       "nissan             713\n",
       "mazda              709\n",
       "smart              661\n",
       "citroen            654\n",
       "toyota             593\n",
       "hyundai            468\n",
       "sonstige_autos     458\n",
       "volvo              427\n",
       "mini               409\n",
       "mitsubishi         384\n",
       "honda              366\n",
       "kia                330\n",
       "alfa_romeo         310\n",
       "porsche            286\n",
       "suzuki             277\n",
       "chevrolet          266\n",
       "chrysler           164\n",
       "dacia              123\n",
       "daihatsu           117\n",
       "jeep               106\n",
       "subaru             100\n",
       "land_rover          98\n",
       "saab                77\n",
       "jaguar              73\n",
       "daewoo              70\n",
       "trabant             65\n",
       "rover               62\n",
       "lancia              50\n",
       "lada                27\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Getting the count for each unique brand.\n",
    "\n",
    "autos2[\"brand\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "volkswagen       9862\n",
      "bmw              5137\n",
      "opel             5022\n",
      "mercedes_benz    4503\n",
      "audi             4041\n",
      "ford             3263\n",
      "renault          2201\n",
      "peugeot          1393\n",
      "fiat             1197\n",
      "seat              853\n",
      "Name: brand, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "### Selecting the top 10 brands to be used for further analysis.\n",
    "\n",
    "top10_brands = autos2[\"brand\"].value_counts().head(10)\n",
    "print(top10_brands)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['volkswagen', 'bmw', 'opel', 'mercedes_benz', 'audi', 'ford', 'renault',\n",
      "       'peugeot', 'fiat', 'seat'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "### Getting the index label of the top 10 brands.\n",
    "\n",
    "top10_brands = top10_brands.index\n",
    "print(top10_brands)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "Volkswagen is the most popular brand, having 9862 records, which is almost double of it's first rival, BMW (5137 records)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Analyze the Top 10 Brands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Create a general function to sort a given dictionary in descending order.\n",
    "### This function will return a sorted list of tuple.\n",
    "\n",
    "### Note that sorted() built-in function doesn't work too well with dictionaries because  \n",
    "###      it only considers and returns the dictionary keys (instead of the key+value pair).\n",
    "### We need to transform the dictionary into a list of tuples in order to do sorting.\n",
    "\n",
    "\n",
    "def sort_dict(dictionary):\n",
    "    ### Initialise a blank list to store tuples.\n",
    "    list_of_tuple = []\n",
    "    \n",
    "    for key in dictionary:                      \n",
    "        val_key_tuple = (dictionary[key], key)\n",
    "        list_of_tuple.append(val_key_tuple)\n",
    "        \n",
    "    list_of_tuple_sorted = sorted(list_of_tuple, reverse = True)\n",
    "    \n",
    "    # Either print the sorted records, or return the sorted list of tuple.\n",
    "    #for item in list_of_tuple_sorted:\n",
    "    #    print(item[1], ':', item[0])\n",
    "        \n",
    "    return list_of_tuple_sorted\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.1 Exploring Price by Brand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "### Average Price by Brand ###\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'volkswagen': 5402,\n",
       " 'bmw': 8332,\n",
       " 'opel': 2975,\n",
       " 'mercedes_benz': 8628,\n",
       " 'audi': 9336,\n",
       " 'ford': 3749,\n",
       " 'renault': 2474,\n",
       " 'peugeot': 3094,\n",
       " 'fiat': 2813,\n",
       " 'seat': 4397}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Getting the average price for each of the top 10 brands.\n",
    "\n",
    "avg_price_by_brand = {}\n",
    "\n",
    "for b in top10_brands:\n",
    "    selected_rows = autos2[autos2[\"brand\"] == b]\n",
    "    mean = selected_rows[\"price\"].mean()\n",
    "    avg_price_by_brand[b] = int(mean)\n",
    "    \n",
    "print(\"### Average Price by Brand ###\")\n",
    "avg_price_by_brand\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "### Average Price by Brand (Sorted) ###\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(9336, 'audi'),\n",
       " (8628, 'mercedes_benz'),\n",
       " (8332, 'bmw'),\n",
       " (5402, 'volkswagen'),\n",
       " (4397, 'seat'),\n",
       " (3749, 'ford'),\n",
       " (3094, 'peugeot'),\n",
       " (2975, 'opel'),\n",
       " (2813, 'fiat'),\n",
       " (2474, 'renault')]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Call the sort_dict() function to sort the records.\n",
    "\n",
    "print(\"### Average Price by Brand (Sorted) ###\")\n",
    "sort_dict(avg_price_by_brand)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "In the top 10 brands, there's a distinct price gap.\n",
    "\n",
    "- Audi, Mercedes Benz, and BMW are more expensive.\n",
    "- Opel, Fiat, and Renault are less expensive.\n",
    "- Volkswagen, Seat, Ford and Peugeot are in between.\n",
    "\n",
    "This may explain why Volkswagen is the most popular brand, as it's price is in between."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.2 Exploring Mileage by Brand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "### Average Mileage by Brand (Sorted) ###\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[(132572, 'bmw'),\n",
       " (130788, 'mercedes_benz'),\n",
       " (129310, 'opel'),\n",
       " (129157, 'audi'),\n",
       " (128707, 'volkswagen'),\n",
       " (128071, 'renault'),\n",
       " (127153, 'peugeot'),\n",
       " (124266, 'ford'),\n",
       " (121131, 'seat'),\n",
       " (117121, 'fiat')]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Getting the average mileage for each of the top 10 brands.\n",
    "\n",
    "avg_mileage_by_brand = {}\n",
    "\n",
    "for b in top10_brands:\n",
    "    selected_rows = autos2[autos2[\"brand\"] == b]\n",
    "    mean = selected_rows[\"odometer_km\"].mean()\n",
    "    avg_mileage_by_brand[b] = int(mean)\n",
    "    \n",
    "#print(\"### Average Mileage by Brand ###\")\n",
    "#avg_mileage_by_brand\n",
    "\n",
    "### Call the sort_dict() function to sort the records.\n",
    "\n",
    "print(\"### Average Mileage by Brand (Sorted) ###\")\n",
    "sort_dict(avg_mileage_by_brand)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "All the top 10 brands have high mileage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.3 Analyzing Price & Mileage by Brand (Part 1)\n",
    "\n",
    "Let's use aggregation to understand the average mileage for those cars and if there's any visible link with mean price.\n",
    " \n",
    "The following will combine the data from both series objects into a single dataframe (with a shared index) and display the dataframe directly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "volkswagen       5402\n",
      "bmw              8332\n",
      "opel             2975\n",
      "mercedes_benz    8628\n",
      "audi             9336\n",
      "ford             3749\n",
      "renault          2474\n",
      "peugeot          3094\n",
      "fiat             2813\n",
      "seat             4397\n",
      "dtype: int64\n"
     ]
    },
    {
     "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>mean_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>volkswagen</td>\n",
       "      <td>5402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>bmw</td>\n",
       "      <td>8332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>opel</td>\n",
       "      <td>2975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>8628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>audi</td>\n",
       "      <td>9336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ford</td>\n",
       "      <td>3749</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>renault</td>\n",
       "      <td>2474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>peugeot</td>\n",
       "      <td>3094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>fiat</td>\n",
       "      <td>2813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>seat</td>\n",
       "      <td>4397</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price\n",
       "volkswagen           5402\n",
       "bmw                  8332\n",
       "opel                 2975\n",
       "mercedes_benz        8628\n",
       "audi                 9336\n",
       "ford                 3749\n",
       "renault              2474\n",
       "peugeot              3094\n",
       "fiat                 2813\n",
       "seat                 4397"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Convert the 'avg_price_by_brand' dictionary to series objects, using the series constructor.\n",
    "### The keys in the dictionary became the index in the series object.\n",
    "\n",
    "bmp_series = pd.Series(avg_price_by_brand)\n",
    "print(bmp_series)\n",
    "\n",
    "### We can then create a single-column dataframe from this series object.\n",
    "### We need to use the columns parameter when calling the dataframe constructor (which accepts a array-like object) \n",
    "###     to specify the column name (or the column name will be set to 0 by default).\n",
    "\n",
    "df = pd.DataFrame(bmp_series, columns=['mean_price'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>mean_price</th>\n",
       "      <th>mean_mileage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>volkswagen</td>\n",
       "      <td>5402</td>\n",
       "      <td>128707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>bmw</td>\n",
       "      <td>8332</td>\n",
       "      <td>132572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>opel</td>\n",
       "      <td>2975</td>\n",
       "      <td>129310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>8628</td>\n",
       "      <td>130788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>audi</td>\n",
       "      <td>9336</td>\n",
       "      <td>129157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ford</td>\n",
       "      <td>3749</td>\n",
       "      <td>124266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>renault</td>\n",
       "      <td>2474</td>\n",
       "      <td>128071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>peugeot</td>\n",
       "      <td>3094</td>\n",
       "      <td>127153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>fiat</td>\n",
       "      <td>2813</td>\n",
       "      <td>117121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>seat</td>\n",
       "      <td>4397</td>\n",
       "      <td>121131</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price  mean_mileage\n",
       "volkswagen           5402        128707\n",
       "bmw                  8332        132572\n",
       "opel                 2975        129310\n",
       "mercedes_benz        8628        130788\n",
       "audi                 9336        129157\n",
       "ford                 3749        124266\n",
       "renault              2474        128071\n",
       "peugeot              3094        127153\n",
       "fiat                 2813        117121\n",
       "seat                 4397        121131"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Adding 'mean_mileage' new column to the dataframe.\n",
    "\n",
    "df[\"mean_mileage\"] = pd.Series(avg_mileage_by_brand)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "The average mileage seems to have no visible link with mean price.\n",
    "\n",
    "There is no significant difference on the average mileage for cars with expensive or cheaper price.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.4 Analyzing Price & Mileage by Brand (Part 2)\n",
    "\n",
    "Split the mileage (odometer_km) into groups, and use aggregation to see if average prices follows any patterns based on the mileage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5000        785\n",
       "10000       241\n",
       "20000       742\n",
       "30000       760\n",
       "40000       797\n",
       "50000       993\n",
       "60000      1128\n",
       "70000      1187\n",
       "80000      1375\n",
       "90000      1673\n",
       "100000     2058\n",
       "125000     4857\n",
       "150000    30085\n",
       "Name: odometer_km, dtype: int64"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the count and unique values of 'odometer_km'.\n",
    "\n",
    "autos2[\"odometer_km\"].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>mean_price_odo_up_to_50km</th>\n",
       "      <th>mean_price_odo_50_to_100km</th>\n",
       "      <th>mean_price_odo_above_100km</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>volkswagen</td>\n",
       "      <td>13427</td>\n",
       "      <td>9938</td>\n",
       "      <td>3762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>bmw</td>\n",
       "      <td>21404</td>\n",
       "      <td>15799</td>\n",
       "      <td>6282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>opel</td>\n",
       "      <td>7980</td>\n",
       "      <td>5389</td>\n",
       "      <td>2099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>22213</td>\n",
       "      <td>16487</td>\n",
       "      <td>6164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>audi</td>\n",
       "      <td>25087</td>\n",
       "      <td>17476</td>\n",
       "      <td>6303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ford</td>\n",
       "      <td>10878</td>\n",
       "      <td>5561</td>\n",
       "      <td>2440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>renault</td>\n",
       "      <td>7522</td>\n",
       "      <td>4182</td>\n",
       "      <td>1659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>peugeot</td>\n",
       "      <td>7963</td>\n",
       "      <td>5272</td>\n",
       "      <td>2139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>fiat</td>\n",
       "      <td>6626</td>\n",
       "      <td>4060</td>\n",
       "      <td>1758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>seat</td>\n",
       "      <td>11137</td>\n",
       "      <td>6849</td>\n",
       "      <td>2656</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price_odo_up_to_50km  mean_price_odo_50_to_100km  \\\n",
       "volkswagen                         13427                        9938   \n",
       "bmw                                21404                       15799   \n",
       "opel                                7980                        5389   \n",
       "mercedes_benz                      22213                       16487   \n",
       "audi                               25087                       17476   \n",
       "ford                               10878                        5561   \n",
       "renault                             7522                        4182   \n",
       "peugeot                             7963                        5272   \n",
       "fiat                                6626                        4060   \n",
       "seat                               11137                        6849   \n",
       "\n",
       "               mean_price_odo_above_100km  \n",
       "volkswagen                           3762  \n",
       "bmw                                  6282  \n",
       "opel                                 2099  \n",
       "mercedes_benz                        6164  \n",
       "audi                                 6303  \n",
       "ford                                 2440  \n",
       "renault                              1659  \n",
       "peugeot                              2139  \n",
       "fiat                                 1758  \n",
       "seat                                 2656  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Split the odometer_km into 3 groups.\n",
    "### Getting the average price for each of the top 10 brands, for each of the 'odometer_km' groups.\n",
    "\n",
    "avg_price_group1 = {}\n",
    "avg_price_group2 = {}\n",
    "avg_price_group3 = {}\n",
    "\n",
    "for b in top10_brands:\n",
    "    selected_rows_group1 = autos2[(autos2[\"brand\"] == b) & (autos2[\"odometer_km\"] <= 50000)]\n",
    "    selected_rows_group2 = autos2[(autos2[\"brand\"] == b) & (autos2[\"odometer_km\"].between(50001, 100000))]\n",
    "    selected_rows_group3 = autos2[(autos2[\"brand\"] == b) & (autos2[\"odometer_km\"] > 100000)]\n",
    "    \n",
    "    mean_group1 = selected_rows_group1[\"price\"].mean()\n",
    "    mean_group2 = selected_rows_group2[\"price\"].mean()\n",
    "    mean_group3 = selected_rows_group3[\"price\"].mean()\n",
    "    \n",
    "    avg_price_group1[b] = int(mean_group1)\n",
    "    avg_price_group2[b] = int(mean_group2)\n",
    "    avg_price_group3[b] = int(mean_group3)\n",
    "    \n",
    "\n",
    "### Convert the 'avg_price_with_damage' dictionary to series objects, using the series constructor.\n",
    "### The keys in the dictionary became the index in the series object.\n",
    "\n",
    "mp_series = pd.Series(avg_price_group1)\n",
    "\n",
    "### We can then create a single-column dataframe from this series object.\n",
    "### We need to use the columns parameter when calling the dataframe constructor (which accepts a array-like object) \n",
    "###     to specify the column name (or the column name will be set to 0 by default).\n",
    "\n",
    "df = pd.DataFrame(mp_series, columns=['mean_price_odo_up_to_50km'])\n",
    "\n",
    "### Adding 'mean_price_non_damage' new column to the dataframe.\n",
    "\n",
    "df[\"mean_price_odo_50_to_100km\"] = pd.Series(avg_price_group2)\n",
    "df[\"mean_price_odo_above_100km\"] = pd.Series(avg_price_group3)\n",
    "df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "For the same brand, average price is reduced when the mileage is increased."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 11.5 Analyzing Price For Cars With Damaged and Non-Damage\n",
    "\n",
    "Here we would want to find out how much cheaper are cars with damage than their non-damaged counterparts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "nein    33834\n",
       "ja       4540\n",
       "Name: unrepaired_damage, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Checking the unique values for the 'unrepaired_damage' column.\n",
    "\n",
    "autos2[\"unrepaired_damage\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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>mean_price_with_damage</th>\n",
       "      <th>mean_price_non_damage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>volkswagen</td>\n",
       "      <td>2179</td>\n",
       "      <td>6469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>bmw</td>\n",
       "      <td>3512</td>\n",
       "      <td>9437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>opel</td>\n",
       "      <td>1367</td>\n",
       "      <td>3660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>3921</td>\n",
       "      <td>9798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>audi</td>\n",
       "      <td>3324</td>\n",
       "      <td>10914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ford</td>\n",
       "      <td>1375</td>\n",
       "      <td>4660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>renault</td>\n",
       "      <td>1145</td>\n",
       "      <td>3103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>peugeot</td>\n",
       "      <td>1372</td>\n",
       "      <td>3674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>fiat</td>\n",
       "      <td>1146</td>\n",
       "      <td>3446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>seat</td>\n",
       "      <td>1741</td>\n",
       "      <td>5183</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price_with_damage  mean_price_non_damage\n",
       "volkswagen                       2179                   6469\n",
       "bmw                              3512                   9437\n",
       "opel                             1367                   3660\n",
       "mercedes_benz                    3921                   9798\n",
       "audi                             3324                  10914\n",
       "ford                             1375                   4660\n",
       "renault                          1145                   3103\n",
       "peugeot                          1372                   3674\n",
       "fiat                             1146                   3446\n",
       "seat                             1741                   5183"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Getting the average price for each of the top 10 brands, for cars with damage and without damage.\n",
    "\n",
    "avg_price_with_damage = {}\n",
    "avg_price_non_damaged = {}\n",
    "\n",
    "for b in top10_brands:\n",
    "    selected_rows_with_damage = autos2[(autos2[\"brand\"] == b) & (autos2[\"unrepaired_damage\"] == \"ja\")]\n",
    "    selected_rows_non_damage = autos2[(autos2[\"brand\"] == b) & (autos2[\"unrepaired_damage\"] == \"nein\")]\n",
    "    \n",
    "    mean_with_damage = selected_rows_with_damage[\"price\"].mean()\n",
    "    mean_non_damage = selected_rows_non_damage[\"price\"].mean()\n",
    "    \n",
    "    avg_price_with_damage[b] = int(mean_with_damage)\n",
    "    avg_price_non_damaged[b] = int(mean_non_damage)\n",
    "    \n",
    "\n",
    "### Convert the 'avg_price_with_damage' dictionary to series objects, using the series constructor.\n",
    "### The keys in the dictionary became the index in the series object.\n",
    "\n",
    "mp_series = pd.Series(avg_price_with_damage)\n",
    "\n",
    "### We can then create a single-column dataframe from this series object.\n",
    "### We need to use the columns parameter when calling the dataframe constructor (which accepts a array-like object) \n",
    "###     to specify the column name (or the column name will be set to 0 by default).\n",
    "\n",
    "df = pd.DataFrame(mp_series, columns=['mean_price_with_damage'])\n",
    "\n",
    "### Adding 'mean_price_non_damage' new column to the dataframe.\n",
    "\n",
    "df[\"mean_price_non_damage\"] = pd.Series(avg_price_non_damaged)\n",
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "scrolled": true
   },
   "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>mean_price_with_damage</th>\n",
       "      <th>mean_price_non_damage</th>\n",
       "      <th>percent_cheaper</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>volkswagen</td>\n",
       "      <td>2179</td>\n",
       "      <td>6469</td>\n",
       "      <td>33.683722</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>bmw</td>\n",
       "      <td>3512</td>\n",
       "      <td>9437</td>\n",
       "      <td>37.215217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>opel</td>\n",
       "      <td>1367</td>\n",
       "      <td>3660</td>\n",
       "      <td>37.349727</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>mercedes_benz</td>\n",
       "      <td>3921</td>\n",
       "      <td>9798</td>\n",
       "      <td>40.018371</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>audi</td>\n",
       "      <td>3324</td>\n",
       "      <td>10914</td>\n",
       "      <td>30.456295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ford</td>\n",
       "      <td>1375</td>\n",
       "      <td>4660</td>\n",
       "      <td>29.506438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>renault</td>\n",
       "      <td>1145</td>\n",
       "      <td>3103</td>\n",
       "      <td>36.899774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>peugeot</td>\n",
       "      <td>1372</td>\n",
       "      <td>3674</td>\n",
       "      <td>37.343495</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>fiat</td>\n",
       "      <td>1146</td>\n",
       "      <td>3446</td>\n",
       "      <td>33.255949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>seat</td>\n",
       "      <td>1741</td>\n",
       "      <td>5183</td>\n",
       "      <td>33.590585</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price_with_damage  mean_price_non_damage  percent_cheaper\n",
       "volkswagen                       2179                   6469        33.683722\n",
       "bmw                              3512                   9437        37.215217\n",
       "opel                             1367                   3660        37.349727\n",
       "mercedes_benz                    3921                   9798        40.018371\n",
       "audi                             3324                  10914        30.456295\n",
       "ford                             1375                   4660        29.506438\n",
       "renault                          1145                   3103        36.899774\n",
       "peugeot                          1372                   3674        37.343495\n",
       "fiat                             1146                   3446        33.255949\n",
       "seat                             1741                   5183        33.590585"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Calculate the percentage and adding it as column to the dataframe.\n",
    "\n",
    "df[\"percent_cheaper\"] = (df[\"mean_price_with_damage\"] / df[\"mean_price_non_damage\"]) * 100\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "For the top 10 brands, cars with damage are around 30-40% cheaper than their non-damaged counterparts."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Find the most common brand/model combinations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>ab_test</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gear_box</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer_km</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "      <th>brand_and_model</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>5000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manuell</td>\n",
       "      <td>158</td>\n",
       "      <td>andere</td>\n",
       "      <td>150000</td>\n",
       "      <td>3</td>\n",
       "      <td>lpg</td>\n",
       "      <td>peugeot</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>79588</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "      <td>peugeot/andere</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>8500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150000</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "      <td>bmw/7er</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>8990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70000</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "      <td>volkswagen/golf</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>4350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70000</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "      <td>smart/fortwo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>1350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150000</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>ford/focus</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49995</td>\n",
       "      <td>2016-03-27 14:38:19</td>\n",
       "      <td>Audi_Q5_3.0_TDI_qu._S_tr.__Navi__Panorama__Xenon</td>\n",
       "      <td>24900</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2011</td>\n",
       "      <td>automatik</td>\n",
       "      <td>239</td>\n",
       "      <td>q5</td>\n",
       "      <td>100000</td>\n",
       "      <td>1</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-27 00:00:00</td>\n",
       "      <td>82131</td>\n",
       "      <td>2016-04-01 13:47:40</td>\n",
       "      <td>audi/q5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49996</td>\n",
       "      <td>2016-03-28 10:50:25</td>\n",
       "      <td>Opel_Astra_F_Cabrio_Bertone_Edition___TÜV_neu+...</td>\n",
       "      <td>1980</td>\n",
       "      <td>control</td>\n",
       "      <td>cabrio</td>\n",
       "      <td>1996</td>\n",
       "      <td>manuell</td>\n",
       "      <td>75</td>\n",
       "      <td>astra</td>\n",
       "      <td>150000</td>\n",
       "      <td>5</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-28 00:00:00</td>\n",
       "      <td>44807</td>\n",
       "      <td>2016-04-02 14:18:02</td>\n",
       "      <td>opel/astra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49997</td>\n",
       "      <td>2016-04-02 14:44:48</td>\n",
       "      <td>Fiat_500_C_1.2_Dualogic_Lounge</td>\n",
       "      <td>13200</td>\n",
       "      <td>test</td>\n",
       "      <td>cabrio</td>\n",
       "      <td>2014</td>\n",
       "      <td>automatik</td>\n",
       "      <td>69</td>\n",
       "      <td>500</td>\n",
       "      <td>5000</td>\n",
       "      <td>11</td>\n",
       "      <td>benzin</td>\n",
       "      <td>fiat</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-02 00:00:00</td>\n",
       "      <td>73430</td>\n",
       "      <td>2016-04-04 11:47:27</td>\n",
       "      <td>fiat/500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49998</td>\n",
       "      <td>2016-03-08 19:25:42</td>\n",
       "      <td>Audi_A3_2.0_TDI_Sportback_Ambition</td>\n",
       "      <td>22900</td>\n",
       "      <td>control</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2013</td>\n",
       "      <td>manuell</td>\n",
       "      <td>150</td>\n",
       "      <td>a3</td>\n",
       "      <td>40000</td>\n",
       "      <td>11</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-08 00:00:00</td>\n",
       "      <td>35683</td>\n",
       "      <td>2016-04-05 16:45:07</td>\n",
       "      <td>audi/a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>49999</td>\n",
       "      <td>2016-03-14 00:42:12</td>\n",
       "      <td>Opel_Vectra_1.6_16V</td>\n",
       "      <td>1250</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1996</td>\n",
       "      <td>manuell</td>\n",
       "      <td>101</td>\n",
       "      <td>vectra</td>\n",
       "      <td>150000</td>\n",
       "      <td>1</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-13 00:00:00</td>\n",
       "      <td>45897</td>\n",
       "      <td>2016-04-06 21:18:48</td>\n",
       "      <td>opel/vectra</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>46681 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              date_crawled                                               name  \\\n",
       "0      2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1      2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2      2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3      2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4      2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "...                    ...                                                ...   \n",
       "49995  2016-03-27 14:38:19   Audi_Q5_3.0_TDI_qu._S_tr.__Navi__Panorama__Xenon   \n",
       "49996  2016-03-28 10:50:25  Opel_Astra_F_Cabrio_Bertone_Edition___TÜV_neu+...   \n",
       "49997  2016-04-02 14:44:48                     Fiat_500_C_1.2_Dualogic_Lounge   \n",
       "49998  2016-03-08 19:25:42                 Audi_A3_2.0_TDI_Sportback_Ambition   \n",
       "49999  2016-03-14 00:42:12                                Opel_Vectra_1.6_16V   \n",
       "\n",
       "       price  ab_test vehicle_type  registration_year   gear_box  power_PS  \\\n",
       "0       5000  control          bus               2004    manuell       158   \n",
       "1       8500  control    limousine               1997  automatik       286   \n",
       "2       8990     test    limousine               2009    manuell       102   \n",
       "3       4350  control   kleinwagen               2007  automatik        71   \n",
       "4       1350     test        kombi               2003    manuell         0   \n",
       "...      ...      ...          ...                ...        ...       ...   \n",
       "49995  24900  control    limousine               2011  automatik       239   \n",
       "49996   1980  control       cabrio               1996    manuell        75   \n",
       "49997  13200     test       cabrio               2014  automatik        69   \n",
       "49998  22900  control        kombi               2013    manuell       150   \n",
       "49999   1250  control    limousine               1996    manuell       101   \n",
       "\n",
       "        model  odometer_km  registration_month fuel_type       brand  \\\n",
       "0      andere       150000                   3       lpg     peugeot   \n",
       "1         7er       150000                   6    benzin         bmw   \n",
       "2        golf        70000                   7    benzin  volkswagen   \n",
       "3      fortwo        70000                   6    benzin       smart   \n",
       "4       focus       150000                   7    benzin        ford   \n",
       "...       ...          ...                 ...       ...         ...   \n",
       "49995      q5       100000                   1    diesel        audi   \n",
       "49996   astra       150000                   5    benzin        opel   \n",
       "49997     500         5000                  11    benzin        fiat   \n",
       "49998      a3        40000                  11    diesel        audi   \n",
       "49999  vectra       150000                   1    benzin        opel   \n",
       "\n",
       "      unrepaired_damage           ad_created  postal_code  \\\n",
       "0                  nein  2016-03-26 00:00:00        79588   \n",
       "1                  nein  2016-04-04 00:00:00        71034   \n",
       "2                  nein  2016-03-26 00:00:00        35394   \n",
       "3                  nein  2016-03-12 00:00:00        33729   \n",
       "4                  nein  2016-04-01 00:00:00        39218   \n",
       "...                 ...                  ...          ...   \n",
       "49995              nein  2016-03-27 00:00:00        82131   \n",
       "49996              nein  2016-03-28 00:00:00        44807   \n",
       "49997              nein  2016-04-02 00:00:00        73430   \n",
       "49998              nein  2016-03-08 00:00:00        35683   \n",
       "49999              nein  2016-03-13 00:00:00        45897   \n",
       "\n",
       "                 last_seen  brand_and_model  \n",
       "0      2016-04-06 06:45:54   peugeot/andere  \n",
       "1      2016-04-06 14:45:08          bmw/7er  \n",
       "2      2016-04-06 20:15:37  volkswagen/golf  \n",
       "3      2016-03-15 03:16:28     smart/fortwo  \n",
       "4      2016-04-01 14:38:50       ford/focus  \n",
       "...                    ...              ...  \n",
       "49995  2016-04-01 13:47:40          audi/q5  \n",
       "49996  2016-04-02 14:18:02       opel/astra  \n",
       "49997  2016-04-04 11:47:27         fiat/500  \n",
       "49998  2016-04-05 16:45:07          audi/a3  \n",
       "49999  2016-04-06 21:18:48      opel/vectra  \n",
       "\n",
       "[46681 rows x 18 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Create a new column 'brand_and_model', which combines the brand and the model columns.\n",
    "\n",
    "autos2[\"brand_and_model\"] = autos2[\"brand\"] + \"/\" + autos2[\"model\"]\n",
    "autos2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen/golf      3707\n",
       "bmw/3er              2615\n",
       "volkswagen/polo      1609\n",
       "opel/corsa           1592\n",
       "volkswagen/passat    1349\n",
       "                     ... \n",
       "ford/b_max              1\n",
       "rover/discovery         1\n",
       "rover/rangerover        1\n",
       "bmw/i3                  1\n",
       "audi/200                1\n",
       "Name: brand_and_model, Length: 290, dtype: int64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### Getting the count for each unique brand/model combination.\n",
    "\n",
    "autos2[\"brand_and_model\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Observation:**\n",
    "\n",
    "The most common brand/model combination is Volkswagen/Golf (3707 records), followed by BMW/3er (2615 records)."
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}