Exploring_eBay_Car_Sales_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Exploring eBay Car Sales Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this project, we'll work with dataset of used cars from eBay Kleinanzeigen. We have the modified version of [this dataset](https://data.world/data-society/used-cars-data). It's dirtier than the original because we want to simulate a real data-cleaning task before data-analyze.\n",
    "\n",
    "The dataset consists of next columns:\n",
    "\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",
    "- `odometer` - 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",
    "The aim of this project is to clean the data and analyze the included used car listings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "autos = pd.read_csv('autos.csv', encoding=\"Latin-1\")"
   ]
  },
  {
   "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",
       "      <th>0</th>\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",
       "      <th>1</th>\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",
       "      <th>2</th>\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",
       "      <th>3</th>\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",
       "      <th>4</th>\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",
       "      <th>...</th>\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",
       "      <th>49995</th>\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",
       "      <th>49996</th>\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",
       "      <th>49997</th>\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",
       "      <th>49998</th>\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",
       "      <th>49999</th>\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": [
    "autos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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",
      " #   Column               Non-Null Count  Dtype \n",
      "---  ------               --------------  ----- \n",
      " 0   dateCrawled          50000 non-null  object\n",
      " 1   name                 50000 non-null  object\n",
      " 2   seller               50000 non-null  object\n",
      " 3   offerType            50000 non-null  object\n",
      " 4   price                50000 non-null  object\n",
      " 5   abtest               50000 non-null  object\n",
      " 6   vehicleType          44905 non-null  object\n",
      " 7   yearOfRegistration   50000 non-null  int64 \n",
      " 8   gearbox              47320 non-null  object\n",
      " 9   powerPS              50000 non-null  int64 \n",
      " 10  model                47242 non-null  object\n",
      " 11  odometer             50000 non-null  object\n",
      " 12  monthOfRegistration  50000 non-null  int64 \n",
      " 13  fuelType             45518 non-null  object\n",
      " 14  brand                50000 non-null  object\n",
      " 15  notRepairedDamage    40171 non-null  object\n",
      " 16  dateCreated          50000 non-null  object\n",
      " 17  nrOfPictures         50000 non-null  int64 \n",
      " 18  postalCode           50000 non-null  int64 \n",
      " 19  lastSeen             50000 non-null  object\n",
      "dtypes: int64(5), object(15)\n",
      "memory usage: 7.6+ MB\n",
      "None\n",
      "\n",
      "\n",
      "           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  \n"
     ]
    }
   ],
   "source": [
    "print(autos.info())\n",
    "print('\\n')\n",
    "print(autos.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see next details after first glance of our data:\n",
    "- The dataset includes 50k rows and 20 columns\n",
    "- Most of the data are strings even data that we want to see as numbers (e.g. columns `price`, `odometer`)\n",
    "- Some columns contain string data in German (`notRepairedDamage`, `fuelType`, `vehicleType`, `gearbox`, `model`).\n",
    "- The date of registration was separated into two columns: `monthOfRegistration` and `yearOfRegistration`\n",
    "- `name` column is not in human-readable format\n",
    "- Some columns contain null values, but not more than ~20% of the data per column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Column Names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The column names in the dataset are in camelCase format, but in Python, we prefer snake_case. So let's convert column names to snake_case. We will use [rename method](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.rename.html) to do it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>gearbox</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>num_photos</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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",
       "      <th>1</th>\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",
       "      <th>2</th>\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",
       "      <th>3</th>\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",
       "      <th>4</th>\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",
       "     gearbox  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  num_photos  postal_code  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00           0        79588   \n",
       "1         bmw              nein  2016-04-04 00:00:00           0        71034   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00           0        35394   \n",
       "3       smart              nein  2016-03-12 00:00:00           0        33729   \n",
       "4        ford              nein  2016-04-01 00:00:00           0        39218   \n",
       "\n",
       "             last_seen  \n",
       "0  2016-04-06 06:45:54  \n",
       "1  2016-04-06 14:45:08  \n",
       "2  2016-04-06 20:15:37  \n",
       "3  2016-03-15 03:16:28  \n",
       "4  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.rename(columns={ \"dateCrawled\": \"date_crawled\", \"offerType\": \"offer_type\", \"abtest\": \"ab_test\", \"vehicleType\": \"vehicle_type\", \"yearOfRegistration\": \"registration_year\", \"powerPS\": \"power_ps\", \"monthOfRegistration\": \"registration_month\", \"fuelType\": \"fuel_type\", \"notRepairedDamage\": \"unrepaired_damage\", \"dateCreated\": \"ad_created\", \"nrOfPictures\": \"num_photos\", \"postalCode\": \"postal_code\", \"lastSeen\": \"last_seen\" }, inplace=True)\n",
    "\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In addition to the simple conversion from camelCase to snake_case, we also renamed several columns for a better description (e.g. `nrOfPictures` to `num_photos` or `dateCreated` to `ad_created`)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initial Exploration and Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's do some basic data exploration to determine what other cleaning tasks need to be done. Initially we will look for:\n",
    "- Text columns where all or almost all values are the same. These can often be dropped as they don't have useful information for analysis.\n",
    "- Examples of numeric data stored as text which can be cleaned and converted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": false
   },
   "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>gearbox</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>num_photos</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\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",
       "      <th>unique</th>\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",
       "      <th>top</th>\n",
       "      <td>2016-03-14 20:50: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",
       "      <th>freq</th>\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",
       "      <th>mean</th>\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",
       "      <th>std</th>\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",
       "      <th>min</th>\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",
       "      <th>25%</th>\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",
       "      <th>50%</th>\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",
       "      <th>75%</th>\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",
       "      <th>max</th>\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-14 20:50: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  gearbox      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  num_photos   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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "description = autos.describe(include=\"all\")\n",
    "description"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's investigate columns with unique row values equal NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "registration_year     NaN\n",
       "power_ps              NaN\n",
       "registration_month    NaN\n",
       "num_photos            NaN\n",
       "postal_code           NaN\n",
       "Name: unique, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique = description.loc['unique']\n",
    "\n",
    "# use boolean indexing to select only the columns with NaN values\n",
    "nan_columns = unique.loc[unique.isna()]\n",
    "nan_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        5500\n",
       "75       3171\n",
       "60       2195\n",
       "150      2046\n",
       "140      1884\n",
       "         ... \n",
       "650         1\n",
       "490         1\n",
       "362         1\n",
       "153         1\n",
       "16312       1\n",
       "Name: power_ps, Length: 448, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['power_ps'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     5075\n",
       "3     5071\n",
       "6     4368\n",
       "5     4107\n",
       "4     4102\n",
       "7     3949\n",
       "10    3651\n",
       "12    3447\n",
       "9     3389\n",
       "11    3360\n",
       "1     3282\n",
       "8     3191\n",
       "2     3008\n",
       "Name: registration_month, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_month'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    50000\n",
       "Name: num_photos, dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['num_photos'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10115    109\n",
       "65428    104\n",
       "66333     54\n",
       "45888     50\n",
       "44145     48\n",
       "        ... \n",
       "23942      1\n",
       "83365      1\n",
       "95683      1\n",
       "97794      1\n",
       "67585      1\n",
       "Name: postal_code, Length: 7014, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['postal_code'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Conclusions after researching columns:\n",
    "- In `registration_month` column there are values of `0` and `12`. This is incorrect, because we can have months ranging from `0 to 11` or `1 to 12`.\n",
    "- In `power_ps` column there is abnormal value `16312`\n",
    "- `num_photos` column consists of zero values\n",
    "- There are a number of text columns where all (or nearly all) of the values are the same: `seller` and `offer_type`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's drop `num_photos`, `seller` and `offer_type` columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos.drop(['num_photos', 'seller', 'offer_type'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, we can see that `price` and `odometer` columns contain string values instead of numeric ones. Let's convert it to numeric values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    5000\n",
       "1    8500\n",
       "2    8990\n",
       "3    4350\n",
       "4    1350\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def convert_price(price):\n",
    "    return int(price.replace('$', '').replace(',', ''))\n",
    "\n",
    "autos['price'] = autos['price'].apply(convert_price)\n",
    "autos['price'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    150000\n",
       "1    150000\n",
       "2     70000\n",
       "3     70000\n",
       "4    150000\n",
       "Name: odometer_km, dtype: int64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def convert_odometer(km):\n",
    "    return int(km.replace(',', '').replace('km', ''))\n",
    "\n",
    "autos['odometer'] = autos['odometer'].apply(convert_odometer)\n",
    "autos.rename(columns={ 'odometer': 'odometer_km' }, inplace=True)\n",
    "autos['odometer_km'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring the Odometer and Price Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start with `price` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see some abnormal values: `0` and very big numbers like as `99999999`. I think that cars with zero prices are cars with unknown prices, but prices from $1 and up are normal prices because eBay is auction. Let's clean our data and leave only cars with prices from `$1` to `$100,000`. This price range looks most realistic for eBay."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>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>gearbox</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",
       "      <th>0</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "    </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",
       "   price  ab_test vehicle_type  registration_year    gearbox  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",
       "    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",
       "  unrepaired_damage           ad_created  postal_code            last_seen  \n",
       "0              nein  2016-03-26 00:00:00        79588  2016-04-06 06:45:54  \n",
       "1              nein  2016-04-04 00:00:00        71034  2016-04-06 14:45:08  \n",
       "2              nein  2016-03-26 00:00:00        35394  2016-04-06 20:15:37  \n",
       "3              nein  2016-03-12 00:00:00        33729  2016-03-15 03:16:28  \n",
       "4              nein  2016-04-01 00:00:00        39218  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos = autos[autos['price'].between(1,100000)]\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1        156\n",
       "2          3\n",
       "3          1\n",
       "5          2\n",
       "8          1\n",
       "        ... \n",
       "93911      1\n",
       "94999      1\n",
       "98500      1\n",
       "99000      2\n",
       "99900      2\n",
       "Name: price, Length: 2309, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's investigate `odometer_km` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5000        827\n",
       "10000       250\n",
       "20000       754\n",
       "30000       774\n",
       "40000       814\n",
       "50000      1010\n",
       "60000      1153\n",
       "70000      1217\n",
       "80000      1414\n",
       "90000      1734\n",
       "100000     2113\n",
       "125000     5055\n",
       "150000    31411\n",
       "Name: odometer_km, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['odometer_km'].value_counts().sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here all values look correct"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring the date columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the dataset we have five columns with dates:\n",
    "- `date_crawled`: added by the crawler\n",
    "- `last_seen`: added by the crawler\n",
    "- `ad_created`: from the website\n",
    "- `registration_month`: from the website\n",
    "- `registration_year`: from the website\n",
    "\n",
    "Let's see how these columns are presented in the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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",
       "      <th>registration_month</th>\n",
       "      <th>registration_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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",
       "      <td>3</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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",
       "      <td>6</td>\n",
       "      <td>1997</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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",
       "      <td>7</td>\n",
       "      <td>2009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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",
       "      <td>6</td>\n",
       "      <td>2007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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",
       "      <td>7</td>\n",
       "      <td>2003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49995</th>\n",
       "      <td>2016-03-27 14:38:19</td>\n",
       "      <td>2016-03-27 00:00:00</td>\n",
       "      <td>2016-04-01 13:47:40</td>\n",
       "      <td>1</td>\n",
       "      <td>2011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49996</th>\n",
       "      <td>2016-03-28 10:50:25</td>\n",
       "      <td>2016-03-28 00:00:00</td>\n",
       "      <td>2016-04-02 14:18:02</td>\n",
       "      <td>5</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49997</th>\n",
       "      <td>2016-04-02 14:44:48</td>\n",
       "      <td>2016-04-02 00:00:00</td>\n",
       "      <td>2016-04-04 11:47:27</td>\n",
       "      <td>11</td>\n",
       "      <td>2014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49998</th>\n",
       "      <td>2016-03-08 19:25:42</td>\n",
       "      <td>2016-03-08 00:00:00</td>\n",
       "      <td>2016-04-05 16:45:07</td>\n",
       "      <td>11</td>\n",
       "      <td>2013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49999</th>\n",
       "      <td>2016-03-14 00:42:12</td>\n",
       "      <td>2016-03-13 00:00:00</td>\n",
       "      <td>2016-04-06 21:18:48</td>\n",
       "      <td>1</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>48526 rows × 5 columns</p>\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   \n",
       "...                    ...                  ...                  ...   \n",
       "49995  2016-03-27 14:38:19  2016-03-27 00:00:00  2016-04-01 13:47:40   \n",
       "49996  2016-03-28 10:50:25  2016-03-28 00:00:00  2016-04-02 14:18:02   \n",
       "49997  2016-04-02 14:44:48  2016-04-02 00:00:00  2016-04-04 11:47:27   \n",
       "49998  2016-03-08 19:25:42  2016-03-08 00:00:00  2016-04-05 16:45:07   \n",
       "49999  2016-03-14 00:42:12  2016-03-13 00:00:00  2016-04-06 21:18:48   \n",
       "\n",
       "       registration_month  registration_year  \n",
       "0                       3               2004  \n",
       "1                       6               1997  \n",
       "2                       7               2009  \n",
       "3                       6               2007  \n",
       "4                       7               2003  \n",
       "...                   ...                ...  \n",
       "49995                   1               2011  \n",
       "49996                   5               1996  \n",
       "49997                  11               2014  \n",
       "49998                  11               2013  \n",
       "49999                   1               1996  \n",
       "\n",
       "[48526 rows x 5 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[['date_crawled','ad_created','last_seen','registration_month','registration_year']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`date_crawled`, `ad_created`, and `last_seen` are presented as string `YYYY-MM-DD HH:MM:SS`, `registration_month` and `registration_year` are presented as numbers."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's explore dates from crawler"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-05    0.025306\n",
       "2016-03-06    0.014054\n",
       "2016-03-07    0.036043\n",
       "2016-03-08    0.033281\n",
       "2016-03-09    0.033096\n",
       "2016-03-10    0.032210\n",
       "2016-03-11    0.032601\n",
       "2016-03-12    0.036929\n",
       "2016-03-13    0.015682\n",
       "2016-03-14    0.036578\n",
       "2016-03-15    0.034270\n",
       "2016-03-16    0.029613\n",
       "2016-03-17    0.031633\n",
       "2016-03-18    0.012880\n",
       "2016-03-19    0.034806\n",
       "2016-03-20    0.037897\n",
       "2016-03-21    0.037361\n",
       "2016-03-22    0.032910\n",
       "2016-03-23    0.032230\n",
       "2016-03-24    0.029345\n",
       "2016-03-25    0.031612\n",
       "2016-03-26    0.032189\n",
       "2016-03-27    0.031076\n",
       "2016-03-28    0.034847\n",
       "2016-03-29    0.034126\n",
       "2016-03-30    0.033652\n",
       "2016-03-31    0.031839\n",
       "2016-04-01    0.033652\n",
       "2016-04-02    0.035507\n",
       "2016-04-03    0.038598\n",
       "2016-04-04    0.036517\n",
       "2016-04-05    0.013086\n",
       "2016-04-06    0.003174\n",
       "2016-04-07    0.001401\n",
       "Name: date_crawled, dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['date_crawled'].str[:10].value_counts(normalize=True, dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see those ads were crawled during one month in 2016."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2015-06-11    0.000021\n",
       "2015-08-10    0.000021\n",
       "2015-09-09    0.000021\n",
       "2015-11-10    0.000021\n",
       "2015-12-05    0.000021\n",
       "                ...   \n",
       "2016-04-03    0.038845\n",
       "2016-04-04    0.036887\n",
       "2016-04-05    0.011808\n",
       "2016-04-06    0.003256\n",
       "2016-04-07    0.001257\n",
       "Name: ad_created, Length: 76, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['ad_created'].str[:10].value_counts(normalize=True, dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The dataset contains ads created from 2015 to 2016."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-05    0.001072\n",
       "2016-03-06    0.004328\n",
       "2016-03-07    0.005399\n",
       "2016-03-08    0.007419\n",
       "2016-03-09    0.009582\n",
       "2016-03-10    0.010675\n",
       "2016-03-11    0.012385\n",
       "2016-03-12    0.023802\n",
       "2016-03-13    0.008902\n",
       "2016-03-14    0.012612\n",
       "2016-03-15    0.015888\n",
       "2016-03-16    0.016445\n",
       "2016-03-17    0.028109\n",
       "2016-03-18    0.007336\n",
       "2016-03-19    0.015847\n",
       "2016-03-20    0.020649\n",
       "2016-03-21    0.020628\n",
       "2016-03-22    0.021391\n",
       "2016-03-23    0.018526\n",
       "2016-03-24    0.019742\n",
       "2016-03-25    0.019227\n",
       "2016-03-26    0.016754\n",
       "2016-03-27    0.015641\n",
       "2016-03-28    0.020855\n",
       "2016-03-29    0.022359\n",
       "2016-03-30    0.024791\n",
       "2016-03-31    0.023802\n",
       "2016-04-01    0.022813\n",
       "2016-04-02    0.024914\n",
       "2016-04-03    0.025203\n",
       "2016-04-04    0.024502\n",
       "2016-04-05    0.124634\n",
       "2016-04-06    0.221840\n",
       "2016-04-07    0.131929\n",
       "Name: last_seen, dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['last_seen'].str[:10].value_counts(normalize=True, dropna=False).sort_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The crawler recorded the date it last saw any listing, which allows us to determine on what day a listing was removed, presumably because the car was sold.\n",
    "\n",
    "The last three days contain a disproportionate amount of 'last seen' values. Given that these are 6-10x the values from the previous days, it's unlikely that there was a massive spike in sales, and more likely that these values are to do with the crawling period ending and don't indicate car sales."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    48526.000000\n",
       "mean      2004.754935\n",
       "std         88.678603\n",
       "min       1000.000000\n",
       "25%       1999.000000\n",
       "50%       2004.000000\n",
       "75%       2008.000000\n",
       "max       9999.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that in average cars were registered in 2006. Also, we can see two abnormal values: `min 1000` and `max 9999`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with Incorrect Registration Year Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "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",
    "We'll take ads from the date range between 1950 and 2016."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos = autos[autos['registration_year'].between(1950,2016)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000    0.067672\n",
       "2005    0.062975\n",
       "1999    0.062138\n",
       "2004    0.057977\n",
       "2003    0.057891\n",
       "2006    0.057269\n",
       "2001    0.056518\n",
       "2002    0.053322\n",
       "1998    0.050620\n",
       "2007    0.048818\n",
       "2008    0.047467\n",
       "2009    0.044721\n",
       "1997    0.041826\n",
       "2011    0.034790\n",
       "2010    0.033997\n",
       "1996    0.029450\n",
       "2012    0.028034\n",
       "1995    0.026297\n",
       "2016    0.026039\n",
       "2013    0.017138\n",
       "2014    0.014178\n",
       "1994    0.013491\n",
       "1993    0.009116\n",
       "2015    0.008322\n",
       "1992    0.007915\n",
       "1990    0.007443\n",
       "1991    0.007271\n",
       "1989    0.003732\n",
       "1988    0.002874\n",
       "1985    0.002038\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].value_counts(normalize=True).head(30)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that most of the cars were registered in the early 2000s."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Price by Brand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen        0.211510\n",
       "bmw               0.110098\n",
       "opel              0.107653\n",
       "mercedes_benz     0.096478\n",
       "audi              0.086654\n",
       "ford              0.069903\n",
       "renault           0.047188\n",
       "peugeot           0.029879\n",
       "fiat              0.025675\n",
       "seat              0.018296\n",
       "skoda             0.016430\n",
       "nissan            0.015293\n",
       "mazda             0.015207\n",
       "smart             0.014178\n",
       "citroen           0.014028\n",
       "toyota            0.012719\n",
       "hyundai           0.010038\n",
       "sonstige_autos    0.009545\n",
       "volvo             0.009159\n",
       "mini              0.008773\n",
       "mitsubishi        0.008236\n",
       "honda             0.007850\n",
       "kia               0.007078\n",
       "alfa_romeo        0.006649\n",
       "suzuki            0.005941\n",
       "chevrolet         0.005705\n",
       "porsche           0.005577\n",
       "chrysler          0.003518\n",
       "dacia             0.002638\n",
       "daihatsu          0.002510\n",
       "jeep              0.002274\n",
       "subaru            0.002145\n",
       "land_rover        0.002102\n",
       "saab              0.001652\n",
       "jaguar            0.001566\n",
       "daewoo            0.001501\n",
       "trabant           0.001373\n",
       "rover             0.001330\n",
       "lancia            0.001072\n",
       "lada              0.000579\n",
       "Name: brand, dtype: float64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands = autos['brand'].value_counts(normalize=True)\n",
    "brands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that `volkswagen`, `bmw`, and `opel` take up almost 50% of all ads. We'll explore brands with `>5%` of the total values."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['volkswagen', 'bmw', 'opel', 'mercedes_benz', 'audi', 'ford'], dtype='object')"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "most_popular_brands = brands[brands > .05].index\n",
    "most_popular_brands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find the average price for cars of these brands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'volkswagen': 5398,\n",
       " 'bmw': 8200,\n",
       " 'opel': 2976,\n",
       " 'mercedes_benz': 8526,\n",
       " 'audi': 9295,\n",
       " 'ford': 3698}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_price_by_brands = {}\n",
    "\n",
    "for brand in most_popular_brands:\n",
    "    mean_price_by_brands[brand] = int(autos[autos['brand'] == brand]['price'].mean())\n",
    "    \n",
    "mean_price_by_brands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the most expensive brand is `audi`. It has average price is **9295**. The cheapest brand is `opel` with an average price is **2976**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Storing Aggregate Data in a DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the top 6 brands, let's use aggregation to understand the average mileage for those cars and if there's any visible link with mean price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'volkswagen': 128710,\n",
       " 'bmw': 132658,\n",
       " 'opel': 129336,\n",
       " 'mercedes_benz': 130894,\n",
       " 'audi': 129188,\n",
       " 'ford': 124397}"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_mileage_by_brands = {}\n",
    "\n",
    "for brand in most_popular_brands:\n",
    "    mean_mileage_by_brands[brand] = int(autos[autos['brand'] == brand]['odometer_km'].mean())\n",
    "    \n",
    "mean_mileage_by_brands"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's convert both dictionaries to series objects, using the series constructor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen       5398\n",
       "bmw              8200\n",
       "opel             2976\n",
       "mercedes_benz    8526\n",
       "audi             9295\n",
       "ford             3698\n",
       "dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_price_by_brands_series = pd.Series(mean_price_by_brands)\n",
    "mean_price_by_brands_series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen       128710\n",
       "bmw              132658\n",
       "opel             129336\n",
       "mercedes_benz    130894\n",
       "audi             129188\n",
       "ford             124397\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mean_mileage_by_brands_series = pd.Series(mean_mileage_by_brands)\n",
    "mean_mileage_by_brands_series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we'll create dataframes from these series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>5398</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>8200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>2976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>8526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>9295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>3698</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price\n",
       "volkswagen           5398\n",
       "bmw                  8200\n",
       "opel                 2976\n",
       "mercedes_benz        8526\n",
       "audi                 9295\n",
       "ford                 3698"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(mean_price_by_brands_series, columns=['mean_price'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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",
       "      <th>volkswagen</th>\n",
       "      <td>5398</td>\n",
       "      <td>128710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>8200</td>\n",
       "      <td>132658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>2976</td>\n",
       "      <td>129336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>8526</td>\n",
       "      <td>130894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>9295</td>\n",
       "      <td>129188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>3698</td>\n",
       "      <td>124397</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price  mean_mileage\n",
       "volkswagen           5398        128710\n",
       "bmw                  8200        132658\n",
       "opel                 2976        129336\n",
       "mercedes_benz        8526        130894\n",
       "audi                 9295        129188\n",
       "ford                 3698        124397"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['mean_mileage'] = mean_mileage_by_brands_series\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is no correlation between mileage and price, since all cars on average have the same mileage (~128,000km)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Translation of German Words to English"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since English is the most popular language, let's translate the data in some columns into English.\n",
    "\n",
    "As we know columns `vehicle_type`, `gearbox`, `fuel_type`, and `unrepaired_damage` contain German words. Let's check unique values in each column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['bus' 'limousine' 'kleinwagen' 'kombi' nan 'coupe' 'suv' 'cabrio'\n",
      " 'andere']\n",
      "['manuell' 'automatik' nan]\n",
      "['lpg' 'benzin' 'diesel' nan 'cng' 'hybrid' 'elektro' 'andere']\n",
      "['nein' nan 'ja']\n"
     ]
    }
   ],
   "source": [
    "print(autos['vehicle_type'].unique())\n",
    "print(autos['gearbox'].unique())\n",
    "print(autos['fuel_type'].unique())\n",
    "print(autos['unrepaired_damage'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that we have nan values in these columns. So our next steps:\n",
    "1. Replace German words with English translation\n",
    "2. Replace `nan` with `unknown`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "vehicle_type_mapper = { 'bus': 'bus', 'limousine': 'limousine', 'kleinwagen': 'compact', 'kombi': 'combi', 'coupe': 'coupe', 'suv': 'suv', 'cabrio': 'cabrio', 'andere': 'other' }\n",
    "gearbox_mapper = { 'manuell': 'manual', 'automatik': 'automatic' }\n",
    "fuel_type_mapper = { 'lpg': 'lpg', 'benzin': 'gasoline', 'diesel': 'diesel', 'cng': 'cng', 'hybrid': 'hybrid', 'electro': 'electro', 'andere': 'other' }\n",
    "unrepaired_damage_mapper = { 'nein': 'no', 'ja': 'yes' }\n",
    "\n",
    "translation_map = { 'vehicle_type': vehicle_type_mapper, 'gearbox': gearbox_mapper, 'fuel_type': fuel_type_mapper, 'unrepaired_damage': unrepaired_damage_mapper }\n",
    "\n",
    "for col in translation_map:\n",
    "    autos[col] = autos[col].map(translation_map[col]).fillna('unknown')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "limousine    12596\n",
       "compact      10584\n",
       "combi         8929\n",
       "bus           4031\n",
       "cabrio        2998\n",
       "unknown       2699\n",
       "coupe         2436\n",
       "suv           1962\n",
       "other          387\n",
       "Name: vehicle_type, dtype: int64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['vehicle_type'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "no         33791\n",
       "unknown     8297\n",
       "yes         4534\n",
       "Name: unrepaired_damage, dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['unrepaired_damage'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Search for the Most Common Brand/Model Combinations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's find the most common make/model combinations to see which specific cars sell more often"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'peugeot': '2_reihe',\n",
       " 'bmw': '3er',\n",
       " 'volkswagen': 'golf',\n",
       " 'smart': 'fortwo',\n",
       " 'ford': 'focus',\n",
       " 'chrysler': 'andere',\n",
       " 'seat': 'ibiza',\n",
       " 'renault': 'twingo',\n",
       " 'mercedes_benz': 'c_klasse',\n",
       " 'audi': 'a4',\n",
       " 'sonstige_autos': nan,\n",
       " 'opel': 'corsa',\n",
       " 'mazda': '3_reihe',\n",
       " 'porsche': '911',\n",
       " 'mini': 'cooper',\n",
       " 'toyota': 'yaris',\n",
       " 'dacia': 'sandero',\n",
       " 'nissan': 'micra',\n",
       " 'jeep': 'grand',\n",
       " 'saab': 'andere',\n",
       " 'volvo': 'v70',\n",
       " 'mitsubishi': 'colt',\n",
       " 'jaguar': 'andere',\n",
       " 'fiat': 'punto',\n",
       " 'skoda': 'octavia',\n",
       " 'subaru': 'legacy',\n",
       " 'kia': 'andere',\n",
       " 'citroen': 'andere',\n",
       " 'chevrolet': 'andere',\n",
       " 'hyundai': 'i_reihe',\n",
       " 'honda': 'civic',\n",
       " 'daewoo': 'matiz',\n",
       " 'suzuki': 'andere',\n",
       " 'trabant': '601',\n",
       " 'land_rover': 'freelander',\n",
       " 'alfa_romeo': '156',\n",
       " 'lada': 'niva',\n",
       " 'rover': 'andere',\n",
       " 'daihatsu': 'cuore',\n",
       " 'lancia': 'ypsilon'}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unique_brands = autos['brand'].unique()\n",
    "brand_model_combination = {}\n",
    "\n",
    "for brand in unique_brands:\n",
    "    brand_model_combination[brand] = autos[autos['brand'] == brand]['model'].describe()['top']\n",
    "    \n",
    "brand_model_combination"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Since eBay has the most advertisements for Volkswagen cars for sale, we can conclude that the most popular car for sale is the **Volkswagen Golf**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Checking Correlation Between Damage and Price"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check how much cheaper are cars with damage than their non-damaged counterparts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2222.3098808998675"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[autos['unrepaired_damage'] == 'yes']['price'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6982.830635376284"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[autos['unrepaired_damage'] == 'no']['price'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that the average price of cars without damage is almost 4 times higher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this project we did the following work:\n",
    "- Cleaned and prepared input data to analyze\n",
    "- Analyzed the correlation between mileage and price\n",
    "- Analyzed the correlation between damage and price\n",
    "- Found the most popular brands and most popular model on eBay"
   ]
  }
 ],
 "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.8.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}