Exploring eBay Car Sales Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Exploring eBay Car Sales Data\n",
    "\n",
    "In this project, we'll work with a dataset of used cars from [*eBay Kleinanzeigen*](https://www.ebay-kleinanzeigen.de/), a classifieds section of the German eBay website. \n",
    "\n",
    "The dataset was originally scraped and uploaded to [Kaggle](https://www.kaggle.com/), and we will be working with 50,000 data points from the full dataset. The original dataset is no longer available on Kaggle, bit it can be found [here](https://data.world/data-society/used-cars-data).\n",
    "\n",
    "The data dictionary has columns labeled as follows:\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 listings by brand, price, and mileage."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Data\n",
    "\n",
    "First, we will import the data for cleaning. As `UTF-8` encoding did not work, we have encoded with `Latin-1`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime as dt\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"
     ]
    },
    {
     "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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dateCrawled                                               name  \\\n",
       "0  2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1  2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2  2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3  2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4  2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "\n",
       "   seller offerType   price   abtest vehicleType  yearOfRegistration  \\\n",
       "0  privat   Angebot  $5,000  control         bus                2004   \n",
       "1  privat   Angebot  $8,500  control   limousine                1997   \n",
       "2  privat   Angebot  $8,990     test   limousine                2009   \n",
       "3  privat   Angebot  $4,350  control  kleinwagen                2007   \n",
       "4  privat   Angebot  $1,350     test       kombi                2003   \n",
       "\n",
       "     gearbox  powerPS   model   odometer  monthOfRegistration fuelType  \\\n",
       "0    manuell      158  andere  150,000km                    3      lpg   \n",
       "1  automatik      286     7er  150,000km                    6   benzin   \n",
       "2    manuell      102    golf   70,000km                    7   benzin   \n",
       "3  automatik       71  fortwo   70,000km                    6   benzin   \n",
       "4    manuell        0   focus  150,000km                    7   benzin   \n",
       "\n",
       "        brand notRepairedDamage          dateCreated  nrOfPictures  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00             0   \n",
       "1         bmw              nein  2016-04-04 00:00:00             0   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00             0   \n",
       "3       smart              nein  2016-03-12 00:00:00             0   \n",
       "4        ford              nein  2016-04-01 00:00:00             0   \n",
       "\n",
       "   postalCode             lastSeen  \n",
       "0       79588  2016-04-06 06:45:54  \n",
       "1       71034  2016-04-06 14:45:08  \n",
       "2       35394  2016-04-06 20:15:37  \n",
       "3       33729  2016-03-15 03:16:28  \n",
       "4       39218  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.info()\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A first look at the dataset shows that of the 20 columns, there are 15 columns with values stored as strings and five columns with values stored as integers. There are only five columns with any null values, and every column has greater than 80% non-null values. Finally, we see that the columns are listed in [camel case](https://en.wikipedia.org/wiki/Camel_case).\n",
    "\n",
    "In order to make the data a bit easier to use, we'll change the name of some of the columns and convert the names to [snake case](https://en.wikipedia.org/wiki/Snake_case), the general convention when using Python."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['dateCrawled', 'name', 'seller', 'offerType', 'price', 'abtest',\n",
       "       'vehicleType', 'yearOfRegistration', 'gearbox', 'powerPS', 'model',\n",
       "       'odometer', 'monthOfRegistration', 'fuelType', 'brand',\n",
       "       'notRepairedDamage', 'dateCreated', 'nrOfPictures', 'postalCode',\n",
       "       'lastSeen'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerps</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fueltype</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           datecrawled                                               name  \\\n",
       "0  2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1  2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2  2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3  2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4  2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "\n",
       "   seller offertype   price   abtest vehicletype  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  powerps   model   odometer  registration_month 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 unrepaired_damage           ad_created  nrofpictures  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00             0   \n",
       "1         bmw              nein  2016-04-04 00:00:00             0   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00             0   \n",
       "3       smart              nein  2016-03-12 00:00:00             0   \n",
       "4        ford              nein  2016-04-01 00:00:00             0   \n",
       "\n",
       "   postalcode             lastseen  \n",
       "0       79588  2016-04-06 06:45:54  \n",
       "1       71034  2016-04-06 14:45:08  \n",
       "2       35394  2016-04-06 20:15:37  \n",
       "3       33729  2016-03-15 03:16:28  \n",
       "4       39218  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_columns = []\n",
    "\n",
    "for c in autos.columns:\n",
    "    if c == 'yearOfRegistration':\n",
    "        c = 'registration_year'\n",
    "    elif c =='monthOfRegistration':\n",
    "        c = 'registration_month'\n",
    "    elif c == 'notRepairedDamage':\n",
    "        c = 'unrepaired_damage'\n",
    "    elif c =='dateCreated':\n",
    "        c = 'ad_created'\n",
    "    else:\n",
    "        c = c.lower()\n",
    "    new_columns.append(c)\n",
    "\n",
    "autos.columns = new_columns\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a look at the dataset with the column name changes that we made."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>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>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerps</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fueltype</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>nrofpictures</th>\n",
       "      <th>postalcode</th>\n",
       "      <th>lastseen</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-29 23:42:13</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": [
       "                datecrawled         name  seller offertype  price abtest  \\\n",
       "count                 50000        50000   50000     50000  50000  50000   \n",
       "unique                48213        38754       2         2   2357      2   \n",
       "top     2016-03-29 23:42:13  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",
       "       vehicletype  registration_year  gearbox       powerps  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 fueltype       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  nrofpictures    postalcode             lastseen  \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": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Cleaning\n",
    "\n",
    "There is quite a bit of interesting information that we can decipher from this look at `autos`. We can make quite a few changes to the data in order to simplify it and make our analysis easier.\n",
    "\n",
    "1. `nrofpictures` has a value of `0` (zero) for every row and can be removed.\n",
    "2. `datecrawled`, `adcreated`, and `lastseen` can be convered from strings to datetime objects.\n",
    "3. `price` and `odometer` are text values and should be converted to numbers before further examination.\n",
    "4. `registration_year` and `registration_month` have bad data as there are values that fall outside of the reasonable ranges for these columns.\n",
    "5. `seller` and `offertype` have only one instance where the value is different.\n",
    "6. Where possible, translate information from German into English.\n",
    "\n",
    "Let's take each of these one-by-one."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Remove the `nrofpictures` Column\n",
    "\n",
    "This column has a value of `0` (zero) for every row and therefore provides no valuable information. Let's remove it to speed up our data processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "autos = autos.drop('nrofpictures', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Convert the `datecrawled`, `adcreated`, and `lastseen` Columns to Datetime Objects\n",
    "\n",
    "The `datecrawled`, `adcreated`, and `lastseen` columns are all string-type objects but contain dates and times. While we can work with these columns as strings without issue, conversion will make our code more intuitive. Best practice should always be to keep our data in the most logical format. This helps us better understand what we were doing when we return to the code, and it also makes it easier for others to continue our work and/or make changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "<class 'pandas._libs.tslibs.timestamps.Timestamp'>\n",
      "\n",
      "\n",
      "count                   50000\n",
      "unique                     76\n",
      "top       2016-04-03 00:00:00\n",
      "freq                     1946\n",
      "first     2015-06-11 00:00:00\n",
      "last      2016-04-07 00:00:00\n",
      "Name: ad_created, dtype: object\n"
     ]
    }
   ],
   "source": [
    "autos['datecrawled'] = pd.to_datetime(autos['datecrawled'])\n",
    "autos['ad_created'] = pd.to_datetime(autos['ad_created'])\n",
    "autos['lastseen'] = pd.to_datetime(autos['lastseen'])\n",
    "\n",
    "print(type(autos['datecrawled'][3]))\n",
    "print(type(autos['ad_created'][27]))\n",
    "print(type(autos['lastseen'][45]))\n",
    "\n",
    "print('\\n')\n",
    "print(autos['ad_created'].describe())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a quick and simple change, and we can see that we have done this properly. Let's continue with the next item on our list."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Convert the `price` and `odometer` Columns to Numbers\n",
    "\n",
    "The `price` and `odometer` columns contain numeric values, but we can see that these two columns are strings instead of numbers because of the addition of different characters. Let's look at the unique values of these columns to see what characters need to be removed before we convert them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['$5,000', '$8,500', '$8,990', ..., '$385', '$22,200', '$16,995'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['150,000km', '70,000km', '50,000km', '80,000km', '10,000km',\n",
       "       '30,000km', '125,000km', '90,000km', '20,000km', '60,000km',\n",
       "       '5,000km', '100,000km', '40,000km'], dtype=object)"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['odometer'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to remove `\"$\"` and `\",\"` from `autos` and `\",\"` and `\"km\"` from `odometer`. Since the odometer readings are in kilometers (km), let's rename the `odometer` column as `odometer_km` for clarity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos['price'] = autos['price'].str.replace('$','')\n",
    "autos['price'] = autos['price'].str.replace(',','')\n",
    "autos['price'] = autos['price'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos['odometer'] = autos['odometer'].str.replace(',','')\n",
    "autos['odometer'] = autos['odometer'].str.replace('km','')\n",
    "autos['odometer'] = autos['odometer'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerps</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer_km</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fueltype</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</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>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</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>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</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>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</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>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</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>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</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",
       "    </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>24900</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2011</td>\n",
       "      <td>automatik</td>\n",
       "      <td>239</td>\n",
       "      <td>q5</td>\n",
       "      <td>100000</td>\n",
       "      <td>1</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-27</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>1980</td>\n",
       "      <td>control</td>\n",
       "      <td>cabrio</td>\n",
       "      <td>1996</td>\n",
       "      <td>manuell</td>\n",
       "      <td>75</td>\n",
       "      <td>astra</td>\n",
       "      <td>150000</td>\n",
       "      <td>5</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-28</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>13200</td>\n",
       "      <td>test</td>\n",
       "      <td>cabrio</td>\n",
       "      <td>2014</td>\n",
       "      <td>automatik</td>\n",
       "      <td>69</td>\n",
       "      <td>500</td>\n",
       "      <td>5000</td>\n",
       "      <td>11</td>\n",
       "      <td>benzin</td>\n",
       "      <td>fiat</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-02</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>22900</td>\n",
       "      <td>control</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2013</td>\n",
       "      <td>manuell</td>\n",
       "      <td>150</td>\n",
       "      <td>a3</td>\n",
       "      <td>40000</td>\n",
       "      <td>11</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-08</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>1250</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1996</td>\n",
       "      <td>manuell</td>\n",
       "      <td>101</td>\n",
       "      <td>vectra</td>\n",
       "      <td>150000</td>\n",
       "      <td>1</td>\n",
       "      <td>benzin</td>\n",
       "      <td>opel</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-13</td>\n",
       "      <td>45897</td>\n",
       "      <td>2016-04-06 21:18:48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>50000 rows × 19 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  registration_year  \\\n",
       "0      privat   Angebot   5000  control         bus               2004   \n",
       "1      privat   Angebot   8500  control   limousine               1997   \n",
       "2      privat   Angebot   8990     test   limousine               2009   \n",
       "3      privat   Angebot   4350  control  kleinwagen               2007   \n",
       "4      privat   Angebot   1350     test       kombi               2003   \n",
       "...       ...       ...    ...      ...         ...                ...   \n",
       "49995  privat   Angebot  24900  control   limousine               2011   \n",
       "49996  privat   Angebot   1980  control      cabrio               1996   \n",
       "49997  privat   Angebot  13200     test      cabrio               2014   \n",
       "49998  privat   Angebot  22900  control       kombi               2013   \n",
       "49999  privat   Angebot   1250  control   limousine               1996   \n",
       "\n",
       "         gearbox  powerps   model  odometer_km  registration_month fueltype  \\\n",
       "0        manuell      158  andere       150000                   3      lpg   \n",
       "1      automatik      286     7er       150000                   6   benzin   \n",
       "2        manuell      102    golf        70000                   7   benzin   \n",
       "3      automatik       71  fortwo        70000                   6   benzin   \n",
       "4        manuell        0   focus       150000                   7   benzin   \n",
       "...          ...      ...     ...          ...                 ...      ...   \n",
       "49995  automatik      239      q5       100000                   1   diesel   \n",
       "49996    manuell       75   astra       150000                   5   benzin   \n",
       "49997  automatik       69     500         5000                  11   benzin   \n",
       "49998    manuell      150      a3        40000                  11   diesel   \n",
       "49999    manuell      101  vectra       150000                   1   benzin   \n",
       "\n",
       "            brand unrepaired_damage ad_created  postalcode            lastseen  \n",
       "0         peugeot              nein 2016-03-26       79588 2016-04-06 06:45:54  \n",
       "1             bmw              nein 2016-04-04       71034 2016-04-06 14:45:08  \n",
       "2      volkswagen              nein 2016-03-26       35394 2016-04-06 20:15:37  \n",
       "3           smart              nein 2016-03-12       33729 2016-03-15 03:16:28  \n",
       "4            ford              nein 2016-04-01       39218 2016-04-01 14:38:50  \n",
       "...           ...               ...        ...         ...                 ...  \n",
       "49995        audi              nein 2016-03-27       82131 2016-04-01 13:47:40  \n",
       "49996        opel              nein 2016-03-28       44807 2016-04-02 14:18:02  \n",
       "49997        fiat              nein 2016-04-02       73430 2016-04-04 11:47:27  \n",
       "49998        audi              nein 2016-03-08       35683 2016-04-05 16:45:07  \n",
       "49999        opel              nein 2016-03-13       45897 2016-04-06 21:18:48  \n",
       "\n",
       "[50000 rows x 19 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.rename({'odometer': 'odometer_km'}, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see above that there are only 13 different values in the `odometer_km` column, so they were likely input as a range. Regardless, that data looks sufficient, so let's look at the `price` column to see if there is data that doesn't look right and needs to be changed or removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2357,)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    5.000000e+04\n",
       "mean     9.840044e+03\n",
       "std      4.811044e+05\n",
       "min      0.000000e+00\n",
       "25%      1.100000e+03\n",
       "50%      2.950000e+03\n",
       "75%      7.200000e+03\n",
       "max      1.000000e+08\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99999999       1\n",
       "27322222       1\n",
       "12345678       3\n",
       "11111111       2\n",
       "10000000       1\n",
       "            ... \n",
       "5              2\n",
       "3              1\n",
       "2              3\n",
       "1            156\n",
       "0           1421\n",
       "Name: price, Length: 2357, dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].value_counts().sort_index(ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the `price` column, we can see that there are 2,357 unique prices, but there are a number of prices that are very high with eight prices listed as greater than or equal to 10,000,000. There are also 1,421 listings where the price is 0 (zero). Since this data is scraped from eBay, it is possible that there are listings where the starting bid is below one dollar, resulting in data that round to zero. Let's look in more detail at the most expensive listings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99999999    1\n",
       "27322222    1\n",
       "12345678    3\n",
       "11111111    2\n",
       "10000000    1\n",
       "3890000     1\n",
       "1300000     1\n",
       "1234566     1\n",
       "999999      2\n",
       "999990      1\n",
       "350000      1\n",
       "345000      1\n",
       "299000      1\n",
       "295000      1\n",
       "265000      1\n",
       "259000      1\n",
       "250000      1\n",
       "220000      1\n",
       "198000      1\n",
       "197000      1\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].value_counts().sort_index(ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are some extremely high prices here and while all of these prices look very high for a used car, there is a relatively steady increase in price up to `$350,000` before the price jumps up to one million dollars and beyond. Let's remove every listing where the price is above `$350,000`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "autos = autos[autos['price'] <= 350000]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Fix Bad Data in `registration_year` and `registration_month` Columns\n",
    "\n",
    "When we looked at these columns earlier, we could see that there was incorrect data in both of these columns. The `registration_year` column has a minimum value of 1000, long before cars were invented, and a maximum value of 9999, a date which obviously we have not yet reached. The `registration_month` column contains no values greater than 12, but does contain at least one row with a value of 0 (zero).\n",
    "\n",
    "Both of these columns are already of type integer, so we do not need to make any changes there. Every value that falls outside of a reasonable range, we will convert to `NaN` (not a number) so that we can easily keep those rows separate from the ones that contain data with values we can more readily accept as accurate.\n",
    "\n",
    "Let's begin by exploring the `registration_year` column in a bit more detail."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([2004, 1997, 2009, 2007, 2003, 2006, 1995, 1998, 2000, 2017, 2010,\n",
       "       1999, 1982, 1990, 2015, 2014, 1996, 1992, 2005, 2002, 2012, 2011,\n",
       "       2008, 1985, 2016, 1994, 1986, 2001, 2018, 2013, 1972, 1993, 1988,\n",
       "       1989, 1967, 1973, 1956, 1976, 4500, 1987, 1991, 1983, 1960, 1969,\n",
       "       1950, 1978, 1980, 1984, 1963, 1977, 1961, 1968, 1934, 1965, 1971,\n",
       "       1966, 1979, 1981, 1970, 1974, 1910, 1975, 5000, 4100, 2019, 1959,\n",
       "       9996, 9999, 6200, 1964, 1958, 1800, 1948, 1931, 1943, 9000, 1941,\n",
       "       1962, 1927, 1937, 1929, 1000, 1957, 1952, 1111, 1955, 1939, 8888,\n",
       "       1954, 1938, 2800, 5911, 1500, 1953, 1951, 4800, 1001])"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9999       4\n",
       "9996       1\n",
       "9000       2\n",
       "8888       1\n",
       "6200       1\n",
       "5911       1\n",
       "5000       4\n",
       "4800       1\n",
       "4500       1\n",
       "4100       1\n",
       "2800       1\n",
       "2019       3\n",
       "2018     491\n",
       "2017    1452\n",
       "2016    1316\n",
       "2015     399\n",
       "2014     665\n",
       "2013     806\n",
       "2012    1323\n",
       "2011    1634\n",
       "Name: registration_year, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].value_counts().sort_index(ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1943    1\n",
       "1941    2\n",
       "1939    1\n",
       "1938    1\n",
       "1937    4\n",
       "1934    2\n",
       "1931    1\n",
       "1929    1\n",
       "1927    1\n",
       "1910    9\n",
       "1800    2\n",
       "1500    1\n",
       "1111    1\n",
       "1001    1\n",
       "1000    1\n",
       "Name: registration_year, dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].value_counts().sort_index(ascending=False).tail(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1mNumber of vehicles registered between 1910 and 2016: \u001b[0m96.06%\n"
     ]
    }
   ],
   "source": [
    "x = (autos['registration_year'].between(1910, 2016)).sum() / autos.shape[0] * 100\n",
    "print('\\033[1m' + 'Number of vehicles registered between 1910 and 2016: '\n",
    "      + '\\033[0m' + str(x.round(2)) + \"%\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can see that on both ends of the scale there are only a few listings with dates that are obviously inaccurate. However, as this data is from 2016, it is theoretically impossible for a car to have a registration year greater than 2016. \n",
    "\n",
    "However, given the distribution of vehicles according to the `registration_year` column, it is likely that these dates do not correspond to the date of most recent registration but the original registration date or model year of the vehicle. Since the model years for cars release early and most 2017 vehicles are actually released in 2016, we should include all of the listings for 2017 as well as these could be vehicle pre-orders or models that were released very early in the year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos = autos.loc[autos['registration_year'].between(1910, 2017)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, let's look at the `registration_month` column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3     5029\n",
       "0     4951\n",
       "6     4320\n",
       "5     4066\n",
       "4     4060\n",
       "7     3921\n",
       "10    3625\n",
       "12    3411\n",
       "9     3363\n",
       "11    3331\n",
       "1     3242\n",
       "8     3169\n",
       "2     2980\n",
       "Name: registration_month, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_month'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While `0` (zero) is not a valid registration month, we can see that it is the second-most common value for our dataset. Since the registration year values suggest that many of the registration dates may be the model year instead of an actual date, `0` (zero) would be a logical value where the month of release is unknown. As such, we'll make no further revisions to the data based on the `registration_month` column."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Remove the `seller` and `offertype` column\n",
    "\n",
    "These columns have only one row where the value is different and can likely be removed, but let's look at each one before doing so."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "privat        49467\n",
       "gewerblich        1\n",
       "Name: seller, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['seller'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are 49,467 instances of `privat` (German: private) sales and one row listed as `gewerblich` (German: commercial). While we could leave the data as it is, excluding non-private sales will possibly allow us to draw better conclusions while only necessitating the removal of one row. Let's remove this row and the `seller` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos = autos.loc[autos['seller'] == 'privat']\n",
    "\n",
    "autos = autos.drop('seller', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Angebot    49466\n",
       "Gesuch         1\n",
       "Name: offertype, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['offertype'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Again, there is only one value which is different. There are 49,466 instances of `Angebot` (German: offer) and one that is 'Gesuch' (German: wanted). We are looking at car sales data, so a listing where someone is looking to buy a specific car is not relevant to our analysis. Let's remove this row and the `offertype` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos = autos.loc[autos['offertype'] == 'Angebot']\n",
    "\n",
    "autos = autos.drop('offertype', axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Translate Information from German into English\n",
    "\n",
    "Since this data was taken from a German website, there is a fair bit of information that is in German. Where feasible, let's translate this information into English."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['manuell', 'automatik', nan], dtype=object)"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['gearbox'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These values correspond to manual (German: \"manuell\") and automatic (German: \"automatik\") transmissions as well as some listings where the type is not listed. Let's translate this column into English."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos.loc[autos['gearbox'] == 'manuell', 'gearbox'] = 'manual'\n",
    "autos.loc[autos['gearbox'] == 'automatik', 'gearbox'] = 'automatic'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['nein', nan, 'ja'], dtype=object)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['unrepaired_damage'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a simple yes (German: \"ja\") or no (German: \"nein\") question. We'll translate these values accordingly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos.loc[autos['unrepaired_damage'] == 'ja', 'unrepaired_damage'] = 'yes'\n",
    "autos.loc[autos['unrepaired_damage'] == 'nein', 'unrepaired_damage'] = 'no'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['lpg', 'benzin', 'diesel', nan, 'cng', 'hybrid', 'elektro',\n",
       "       'andere'], dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['fueltype'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are a number of fuel types, but the only ones that we need to change are \"benzin\" to gasoline, \"elektro\" to electric, and \"andere\" to other."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos.loc[autos['fueltype'] == 'benzin', 'fueltype'] = 'gasoline'\n",
    "autos.loc[autos['fueltype'] == 'elektro', 'fueltype'] = 'electric'\n",
    "autos.loc[autos['fueltype'] == 'andere', 'fueltype'] = 'other'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "limousine     12854\n",
       "kleinwagen    10822\n",
       "kombi          9124\n",
       "bus            4091\n",
       "cabrio         3061\n",
       "coupe          2533\n",
       "suv            1986\n",
       "andere          420\n",
       "Name: vehicletype, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['vehicletype'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The final column we'll translate is the `vehicletype` column.  While, the fact that the most common type of vehicle listed is limousine, a quick look at some of the vehicles listed as this type are certainly not limousines. While a further examination of the datset in this area could yield some interesting results, that is beyond the scope of this analysis. We'll simply translate the words to English for coherency and leave the rest of the analysis to a different study."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos.loc[autos['vehicletype'] == 'kleinwagen', 'vehicletype'] = 'compact'\n",
    "autos.loc[autos['vehicletype'] == 'kombi', 'vehicletype'] = 'station_wagon'\n",
    "autos.loc[autos['vehicletype'] == 'cabrio', 'vehicletype'] = 'convertible'\n",
    "autos.loc[autos['vehicletype'] == 'andere', 'vehicletype'] = 'other'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the majority of the cleaning is complete, we'll look at the dataset one last time before moving on to analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datecrawled</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>abtest</th>\n",
       "      <th>vehicletype</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerps</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fueltype</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</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>5000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manual</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>no</td>\n",
       "      <td>2016-03-26</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>automatic</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150000</td>\n",
       "      <td>6</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>bmw</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-04-04</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>manual</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70000</td>\n",
       "      <td>7</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-26</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>compact</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatic</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70000</td>\n",
       "      <td>6</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>smart</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-12</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>station_wagon</td>\n",
       "      <td>2003</td>\n",
       "      <td>manual</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150000</td>\n",
       "      <td>7</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>ford</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-04-01</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",
       "    </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>24900</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2011</td>\n",
       "      <td>automatic</td>\n",
       "      <td>239</td>\n",
       "      <td>q5</td>\n",
       "      <td>100000</td>\n",
       "      <td>1</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-27</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>1980</td>\n",
       "      <td>control</td>\n",
       "      <td>convertible</td>\n",
       "      <td>1996</td>\n",
       "      <td>manual</td>\n",
       "      <td>75</td>\n",
       "      <td>astra</td>\n",
       "      <td>150000</td>\n",
       "      <td>5</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>opel</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-28</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>13200</td>\n",
       "      <td>test</td>\n",
       "      <td>convertible</td>\n",
       "      <td>2014</td>\n",
       "      <td>automatic</td>\n",
       "      <td>69</td>\n",
       "      <td>500</td>\n",
       "      <td>5000</td>\n",
       "      <td>11</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>fiat</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-04-02</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>22900</td>\n",
       "      <td>control</td>\n",
       "      <td>station_wagon</td>\n",
       "      <td>2013</td>\n",
       "      <td>manual</td>\n",
       "      <td>150</td>\n",
       "      <td>a3</td>\n",
       "      <td>40000</td>\n",
       "      <td>11</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-08</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>1250</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1996</td>\n",
       "      <td>manual</td>\n",
       "      <td>101</td>\n",
       "      <td>vectra</td>\n",
       "      <td>150000</td>\n",
       "      <td>1</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>opel</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-13</td>\n",
       "      <td>45897</td>\n",
       "      <td>2016-04-06 21:18:48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49466 rows × 17 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",
       "       price   abtest    vehicletype  registration_year    gearbox  powerps  \\\n",
       "0       5000  control            bus               2004     manual      158   \n",
       "1       8500  control      limousine               1997  automatic      286   \n",
       "2       8990     test      limousine               2009     manual      102   \n",
       "3       4350  control        compact               2007  automatic       71   \n",
       "4       1350     test  station_wagon               2003     manual        0   \n",
       "...      ...      ...            ...                ...        ...      ...   \n",
       "49995  24900  control      limousine               2011  automatic      239   \n",
       "49996   1980  control    convertible               1996     manual       75   \n",
       "49997  13200     test    convertible               2014  automatic       69   \n",
       "49998  22900  control  station_wagon               2013     manual      150   \n",
       "49999   1250  control      limousine               1996     manual      101   \n",
       "\n",
       "        model  odometer  registration_month  fueltype       brand  \\\n",
       "0      andere    150000                   3       lpg     peugeot   \n",
       "1         7er    150000                   6  gasoline         bmw   \n",
       "2        golf     70000                   7  gasoline  volkswagen   \n",
       "3      fortwo     70000                   6  gasoline       smart   \n",
       "4       focus    150000                   7  gasoline        ford   \n",
       "...       ...       ...                 ...       ...         ...   \n",
       "49995      q5    100000                   1    diesel        audi   \n",
       "49996   astra    150000                   5  gasoline        opel   \n",
       "49997     500      5000                  11  gasoline        fiat   \n",
       "49998      a3     40000                  11    diesel        audi   \n",
       "49999  vectra    150000                   1  gasoline        opel   \n",
       "\n",
       "      unrepaired_damage ad_created  postalcode            lastseen  \n",
       "0                    no 2016-03-26       79588 2016-04-06 06:45:54  \n",
       "1                    no 2016-04-04       71034 2016-04-06 14:45:08  \n",
       "2                    no 2016-03-26       35394 2016-04-06 20:15:37  \n",
       "3                    no 2016-03-12       33729 2016-03-15 03:16:28  \n",
       "4                    no 2016-04-01       39218 2016-04-01 14:38:50  \n",
       "...                 ...        ...         ...                 ...  \n",
       "49995                no 2016-03-27       82131 2016-04-01 13:47:40  \n",
       "49996                no 2016-03-28       44807 2016-04-02 14:18:02  \n",
       "49997                no 2016-04-02       73430 2016-04-04 11:47:27  \n",
       "49998                no 2016-03-08       35683 2016-04-05 16:45:07  \n",
       "49999                no 2016-03-13       45897 2016-04-06 21:18:48  \n",
       "\n",
       "[49466 rows x 17 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One last correction that can be made is to remove the `abtest` column. [A/B testing](https://en.wikipedia.org/wiki/A/B_testing) is a form of data analysis for websites. While this column might be useful to someone looking at metrics for eBay Germany, it does not provide any valuable information to us, so we will remove the column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos = autos.drop('abtest', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>datecrawled</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>vehicletype</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerps</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fueltype</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</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>5000</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manual</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>no</td>\n",
       "      <td>2016-03-26</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>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatic</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150000</td>\n",
       "      <td>6</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>bmw</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-04-04</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>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manual</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70000</td>\n",
       "      <td>7</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-26</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>compact</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatic</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70000</td>\n",
       "      <td>6</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>smart</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-12</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>station_wagon</td>\n",
       "      <td>2003</td>\n",
       "      <td>manual</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150000</td>\n",
       "      <td>7</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>ford</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-04-01</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",
       "    </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>24900</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2011</td>\n",
       "      <td>automatic</td>\n",
       "      <td>239</td>\n",
       "      <td>q5</td>\n",
       "      <td>100000</td>\n",
       "      <td>1</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-27</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>1980</td>\n",
       "      <td>convertible</td>\n",
       "      <td>1996</td>\n",
       "      <td>manual</td>\n",
       "      <td>75</td>\n",
       "      <td>astra</td>\n",
       "      <td>150000</td>\n",
       "      <td>5</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>opel</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-28</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>13200</td>\n",
       "      <td>convertible</td>\n",
       "      <td>2014</td>\n",
       "      <td>automatic</td>\n",
       "      <td>69</td>\n",
       "      <td>500</td>\n",
       "      <td>5000</td>\n",
       "      <td>11</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>fiat</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-04-02</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>22900</td>\n",
       "      <td>station_wagon</td>\n",
       "      <td>2013</td>\n",
       "      <td>manual</td>\n",
       "      <td>150</td>\n",
       "      <td>a3</td>\n",
       "      <td>40000</td>\n",
       "      <td>11</td>\n",
       "      <td>diesel</td>\n",
       "      <td>audi</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-08</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>1250</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1996</td>\n",
       "      <td>manual</td>\n",
       "      <td>101</td>\n",
       "      <td>vectra</td>\n",
       "      <td>150000</td>\n",
       "      <td>1</td>\n",
       "      <td>gasoline</td>\n",
       "      <td>opel</td>\n",
       "      <td>no</td>\n",
       "      <td>2016-03-13</td>\n",
       "      <td>45897</td>\n",
       "      <td>2016-04-06 21:18:48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>49466 rows × 16 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",
       "       price    vehicletype  registration_year    gearbox  powerps   model  \\\n",
       "0       5000            bus               2004     manual      158  andere   \n",
       "1       8500      limousine               1997  automatic      286     7er   \n",
       "2       8990      limousine               2009     manual      102    golf   \n",
       "3       4350        compact               2007  automatic       71  fortwo   \n",
       "4       1350  station_wagon               2003     manual        0   focus   \n",
       "...      ...            ...                ...        ...      ...     ...   \n",
       "49995  24900      limousine               2011  automatic      239      q5   \n",
       "49996   1980    convertible               1996     manual       75   astra   \n",
       "49997  13200    convertible               2014  automatic       69     500   \n",
       "49998  22900  station_wagon               2013     manual      150      a3   \n",
       "49999   1250      limousine               1996     manual      101  vectra   \n",
       "\n",
       "       odometer  registration_month  fueltype       brand unrepaired_damage  \\\n",
       "0        150000                   3       lpg     peugeot                no   \n",
       "1        150000                   6  gasoline         bmw                no   \n",
       "2         70000                   7  gasoline  volkswagen                no   \n",
       "3         70000                   6  gasoline       smart                no   \n",
       "4        150000                   7  gasoline        ford                no   \n",
       "...         ...                 ...       ...         ...               ...   \n",
       "49995    100000                   1    diesel        audi                no   \n",
       "49996    150000                   5  gasoline        opel                no   \n",
       "49997      5000                  11  gasoline        fiat                no   \n",
       "49998     40000                  11    diesel        audi                no   \n",
       "49999    150000                   1  gasoline        opel                no   \n",
       "\n",
       "      ad_created  postalcode            lastseen  \n",
       "0     2016-03-26       79588 2016-04-06 06:45:54  \n",
       "1     2016-04-04       71034 2016-04-06 14:45:08  \n",
       "2     2016-03-26       35394 2016-04-06 20:15:37  \n",
       "3     2016-03-12       33729 2016-03-15 03:16:28  \n",
       "4     2016-04-01       39218 2016-04-01 14:38:50  \n",
       "...          ...         ...                 ...  \n",
       "49995 2016-03-27       82131 2016-04-01 13:47:40  \n",
       "49996 2016-03-28       44807 2016-04-02 14:18:02  \n",
       "49997 2016-04-02       73430 2016-04-04 11:47:27  \n",
       "49998 2016-03-08       35683 2016-04-05 16:45:07  \n",
       "49999 2016-03-13       45897 2016-04-06 21:18:48  \n",
       "\n",
       "[49466 rows x 16 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset Analysis\n",
    "\n",
    "There is a great deal of information in this dataset, but we will limit our analysis to brand, price, and mileage.\n",
    "\n",
    "### Listing by Brand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen        10562\n",
       "opel               5390\n",
       "bmw                5382\n",
       "mercedes_benz      4689\n",
       "audi               4254\n",
       "ford               3449\n",
       "renault            2367\n",
       "peugeot            1449\n",
       "fiat               1287\n",
       "seat                915\n",
       "skoda               782\n",
       "mazda               751\n",
       "nissan              749\n",
       "smart               695\n",
       "citroen             692\n",
       "toyota              615\n",
       "sonstige_autos      531\n",
       "hyundai             485\n",
       "volvo               454\n",
       "mini                424\n",
       "mitsubishi          399\n",
       "honda               391\n",
       "kia                 353\n",
       "alfa_romeo          327\n",
       "porsche             293\n",
       "suzuki              289\n",
       "chevrolet           279\n",
       "chrysler            181\n",
       "dacia               128\n",
       "daihatsu            126\n",
       "jeep                109\n",
       "subaru              107\n",
       "land_rover           99\n",
       "daewoo               78\n",
       "saab                 78\n",
       "trabant              77\n",
       "jaguar               76\n",
       "rover                69\n",
       "lancia               56\n",
       "lada                 29\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(40,)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen    0.21352\n",
       "Name: brand, dtype: float64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts().head(1) / autos.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8034609630857559"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts().head(10).sum() / autos.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analyzing Top Brands by Price\n",
    "\n",
    "Examining the listings by brand, there are 40 different brands of vehicle listed for sale, but the top handful of brands make up a large percentage of the total listings. On its own, Volkswagen has nearly twice as many listings as any other brand and makes up more than 21% of all offers. We'll explore pricing for the top ten brands as these ten make up more than 80% of all listings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "volkswagen       10562\n",
      "opel              5390\n",
      "bmw               5382\n",
      "mercedes_benz     4689\n",
      "audi              4254\n",
      "ford              3449\n",
      "renault           2367\n",
      "peugeot           1449\n",
      "fiat              1287\n",
      "seat               915\n",
      "Name: brand, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "brand_labels = autos['brand'].value_counts().head(10)\n",
    "print(brand_labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1mAverage Price of Vehicle by Brand, Top Ten Brands\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1mvolkswagen: \u001b[0m5178.13\n",
      "\u001b[1mopel: \u001b[0m2853.82\n",
      "\u001b[1mbmw: \u001b[0m8051.63\n",
      "\u001b[1mmercedes_benz: \u001b[0m8384.58\n",
      "\u001b[1maudi: \u001b[0m8989.55\n",
      "\u001b[1mford: \u001b[0m3632.72\n",
      "\u001b[1mrenault: \u001b[0m2365.32\n",
      "\u001b[1mpeugeot: \u001b[0m3014.34\n",
      "\u001b[1mfiat: \u001b[0m2693.5\n",
      "\u001b[1mseat: \u001b[0m4259.55\n"
     ]
    }
   ],
   "source": [
    "avg_price_by_brand = {}\n",
    "\n",
    "\n",
    "for b in brand_labels.index:\n",
    "    selected_rows = autos[autos['brand'] == b]\n",
    "    mean = round(selected_rows['price'].mean(), 2)\n",
    "    avg_price_by_brand[b] = mean\n",
    "    \n",
    "print('\\033[1m' + \"Average Price of Vehicle by Brand, Top Ten Brands\" + '\\033[0m')\n",
    "print('\\n')  \n",
    "for p in avg_price_by_brand:\n",
    "    print('\\033[1m' + p + \": \" +'\\033[0m' + str(avg_price_by_brand[p]))\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Of the top 10 brands:\n",
    "\n",
    "* Audi, Mercedes Benz, and BMW cost the most by a significant margin, nearly double all other brands in the top ten.\n",
    "* Renault, Fiat, Opel are the cheapest brands with all three having an average cost less than one-third the average cost of the most expensive brand, Audi.\n",
    "* Volkswagen, the most popular listing is very much in between these ranges, costing significantly less than the top three brands but significantly more than the rest of the top ten, more than twice the cost of the cheapest brand, Renault."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analyzing Top Brands by Mileage\n",
    "\n",
    "With the large discrepancy in average price by brand among the top ten brands, it is important to look at the average mileage for each vehicle to see if this plays a factor in price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[1mAverage Odometer of Vehicle by Brand, Top Ten Brands\u001b[0m\n",
      "\n",
      "\n",
      "\u001b[1mvolkswagen: \u001b[0m128968.47\n",
      "\u001b[1mopel: \u001b[0m129322.82\n",
      "\u001b[1mbmw: \u001b[0m132517.65\n",
      "\u001b[1mmercedes_benz: \u001b[0m131037.53\n",
      "\u001b[1maudi: \u001b[0m129561.59\n",
      "\u001b[1mford: \u001b[0m124079.44\n",
      "\u001b[1mrenault: \u001b[0m128221.38\n",
      "\u001b[1mpeugeot: \u001b[0m127311.94\n",
      "\u001b[1mfiat: \u001b[0m116810.41\n",
      "\u001b[1mseat: \u001b[0m122027.32\n"
     ]
    }
   ],
   "source": [
    "avg_odometer_by_brand = {}\n",
    "\n",
    "\n",
    "for b in brand_labels.index:\n",
    "    selected_rows = autos[autos['brand'] == b]\n",
    "    mean = round(selected_rows['odometer'].mean(), 2)\n",
    "    avg_odometer_by_brand[b] = mean\n",
    "    \n",
    "print('\\033[1m' + \"Average Odometer of Vehicle by Brand, Top Ten Brands\" + '\\033[0m')\n",
    "print('\\n')  \n",
    "for o in avg_odometer_by_brand:\n",
    "    print('\\033[1m' + o + \": \" +'\\033[0m' + str(avg_odometer_by_brand[o]))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "126986.0\n"
     ]
    }
   ],
   "source": [
    "total = 0\n",
    "\n",
    "for o in avg_odometer_by_brand:\n",
    "    total += avg_odometer_by_brand[o]\n",
    "    \n",
    "print(round(total / 10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mean_price</th>\n",
       "      <th>mileage_in_km</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>5178.13</td>\n",
       "      <td>128968.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>2853.82</td>\n",
       "      <td>129322.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>8051.63</td>\n",
       "      <td>132517.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>8384.58</td>\n",
       "      <td>131037.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>8989.55</td>\n",
       "      <td>129561.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>3632.72</td>\n",
       "      <td>124079.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>renault</th>\n",
       "      <td>2365.32</td>\n",
       "      <td>128221.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peugeot</th>\n",
       "      <td>3014.34</td>\n",
       "      <td>127311.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiat</th>\n",
       "      <td>2693.50</td>\n",
       "      <td>116810.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>seat</th>\n",
       "      <td>4259.55</td>\n",
       "      <td>122027.32</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price  mileage_in_km\n",
       "volkswagen        5178.13      128968.47\n",
       "opel              2853.82      129322.82\n",
       "bmw               8051.63      132517.65\n",
       "mercedes_benz     8384.58      131037.53\n",
       "audi              8989.55      129561.59\n",
       "ford              3632.72      124079.44\n",
       "renault           2365.32      128221.38\n",
       "peugeot           3014.34      127311.94\n",
       "fiat              2693.50      116810.41\n",
       "seat              4259.55      122027.32"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "apb_series = pd.Series(avg_price_by_brand)\n",
    "aob_series = pd.Series(avg_odometer_by_brand)\n",
    "\n",
    "df = pd.DataFrame(apb_series, columns=['mean_price'])\n",
    "new_df = df.assign(mileage_in_km = aob_series)\n",
    "\n",
    "new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Looking at the mileage, this does not seem to be a factor in pricing. The only brand with an average mileage more than 5% from the mean mileage is Fiat, one of the cheapest brands. In fact, the top four brands in price also have the top four values for average mileage although not in the same order. This means that we can return to our original observations on prices of the top brands.\n",
    "\n",
    "## Conclusion\n",
    "\n",
    "Of the top 10 brands:\n",
    "\n",
    "* Audi, Mercedes Benz, and BMW cost the most by a significant margin, nearly double all other brands in the top ten.\n",
    "* Renault, Fiat, Opel are the cheapest brands with all three having an average cost less than one-third the average cost of the most expensive brand, Audi.\n",
    "* Volkswagen, the most popular listing is very much in between these ranges, costing significantly less than the top three brands but significantly more than the rest of the top ten, more than twice the cost of the cheapest brand, Renault.\n",
    "* Mileage is not a factor in vehicle cost by brand.\n",
    "\n"
   ]
  }
 ],
 "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
}