used-car-ads.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Analysis of used car ads on eBay Kleinanzeigen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this project, I will clean and analyze a dataset containing metrics on used car ads posted to eBay Kleinanzeigen, a classifieds section of the German eBay website. The dataset was originally posted to Kaggle but can now be found [here](https://data.world/data-society/used-cars-data). For this project, I'm using a version of the dataset that contains only 50,000 data points and which is dirtied a bit to get some practice cleaning data.\n",
    "\n",
    "After cleaning the data, I tackle the following questions:\n",
    "\n",
    "- What is the price distribution of the most common used car brands?\n",
    "- What are the most common brand/model combinations?\n",
    "- How much cheaper are used cars with versus without damage?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "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",
       "  </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": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos = pd.read_csv('autos.csv', encoding='Latin-1')\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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"
     ]
    }
   ],
   "source": [
    "autos.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading the data, we can see that we have 50000 rows and 20 columns, with columns including the seller for the ad, offer type, price, vehicle metrics, number of pictures, and more. We can see that we have a handful of columns with some null values: `vehicleType`, `gearbox`, `model`, `fuelType`, and `notRepairedDamage`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Rename columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, I'll rename some columns so that all columns are in snakecase:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['date_crawled', 'name', 'seller', 'offer_type', 'price', 'abtest',\n",
      "       'vehicle_type', 'registration_year', 'gearbox', 'power_PS', 'model',\n",
      "       'odometer', 'registration_month', 'fuel_type', 'brand',\n",
      "       'unrepaired_damage', 'ad_created', 'nr_of_pictures', 'postal_code',\n",
      "       'last_seen'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "autos.rename({'dateCrawled':'date_crawled',\n",
    "              'offerType':'offer_type',\n",
    "              'vehicleType':'vehicle_type',\n",
    "              'yearOfRegistration':'registration_year',\n",
    "              'powerPS':'power_PS',\n",
    "              'monthOfRegistration':'registration_month',\n",
    "              'fuelType':'fuel_type',\n",
    "              'notRepairedDamage':'unrepaired_damage',\n",
    "              'dateCreated':'ad_created',\n",
    "              'nrOfPictures':'nr_of_pictures',\n",
    "              'postalCode':'postal_code',\n",
    "              'lastSeen':'last_seen'},\n",
    "            axis=1, inplace=True)\n",
    "\n",
    "print(autos.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First, I inspect all the columns in the dataset to see if there are any numeric columns stored as text:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>abtest</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>power_PS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>nr_of_pictures</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000</td>\n",
       "      <td>44905</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>47320</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>47242</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>45518</td>\n",
       "      <td>50000</td>\n",
       "      <td>40171</td>\n",
       "      <td>50000</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>48213</td>\n",
       "      <td>38754</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2357</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>245</td>\n",
       "      <td>13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>40</td>\n",
       "      <td>2</td>\n",
       "      <td>76</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>39481</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>2016-03-23 19:38:20</td>\n",
       "      <td>Ford_Fiesta</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$0</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>NaN</td>\n",
       "      <td>manuell</td>\n",
       "      <td>NaN</td>\n",
       "      <td>golf</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>NaN</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-03 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-04-07 06:17:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>3</td>\n",
       "      <td>78</td>\n",
       "      <td>49999</td>\n",
       "      <td>49999</td>\n",
       "      <td>1421</td>\n",
       "      <td>25756</td>\n",
       "      <td>12859</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36993</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4024</td>\n",
       "      <td>32424</td>\n",
       "      <td>NaN</td>\n",
       "      <td>30107</td>\n",
       "      <td>10687</td>\n",
       "      <td>35232</td>\n",
       "      <td>1946</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2005.073280</td>\n",
       "      <td>NaN</td>\n",
       "      <td>116.355920</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.723360</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50813.627300</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.712813</td>\n",
       "      <td>NaN</td>\n",
       "      <td>209.216627</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.711984</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25779.747957</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1067.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1999.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30451.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2003.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49577.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2008.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>71540.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9999.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17700.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99998.000000</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               date_crawled         name  seller offer_type  price abtest  \\\n",
       "count                 50000        50000   50000      50000  50000  50000   \n",
       "unique                48213        38754       2          2   2357      2   \n",
       "top     2016-03-23 19:38:20  Ford_Fiesta  privat    Angebot     $0   test   \n",
       "freq                      3           78   49999      49999   1421  25756   \n",
       "mean                    NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "std                     NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "min                     NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "25%                     NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "50%                     NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "75%                     NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "max                     NaN          NaN     NaN        NaN    NaN    NaN   \n",
       "\n",
       "       vehicle_type  registration_year  gearbox      power_PS  model  \\\n",
       "count         44905       50000.000000    47320  50000.000000  47242   \n",
       "unique            8                NaN        2           NaN    245   \n",
       "top       limousine                NaN  manuell           NaN   golf   \n",
       "freq          12859                NaN    36993           NaN   4024   \n",
       "mean            NaN        2005.073280      NaN    116.355920    NaN   \n",
       "std             NaN         105.712813      NaN    209.216627    NaN   \n",
       "min             NaN        1000.000000      NaN      0.000000    NaN   \n",
       "25%             NaN        1999.000000      NaN     70.000000    NaN   \n",
       "50%             NaN        2003.000000      NaN    105.000000    NaN   \n",
       "75%             NaN        2008.000000      NaN    150.000000    NaN   \n",
       "max             NaN        9999.000000      NaN  17700.000000    NaN   \n",
       "\n",
       "         odometer  registration_month fuel_type       brand unrepaired_damage  \\\n",
       "count       50000        50000.000000     45518       50000             40171   \n",
       "unique         13                 NaN         7          40                 2   \n",
       "top     150,000km                 NaN    benzin  volkswagen              nein   \n",
       "freq        32424                 NaN     30107       10687             35232   \n",
       "mean          NaN            5.723360       NaN         NaN               NaN   \n",
       "std           NaN            3.711984       NaN         NaN               NaN   \n",
       "min           NaN            0.000000       NaN         NaN               NaN   \n",
       "25%           NaN            3.000000       NaN         NaN               NaN   \n",
       "50%           NaN            6.000000       NaN         NaN               NaN   \n",
       "75%           NaN            9.000000       NaN         NaN               NaN   \n",
       "max           NaN           12.000000       NaN         NaN               NaN   \n",
       "\n",
       "                 ad_created  nr_of_pictures   postal_code            last_seen  \n",
       "count                 50000         50000.0  50000.000000                50000  \n",
       "unique                   76             NaN           NaN                39481  \n",
       "top     2016-04-03 00:00:00             NaN           NaN  2016-04-07 06:17:27  \n",
       "freq                   1946             NaN           NaN                    8  \n",
       "mean                    NaN             0.0  50813.627300                  NaN  \n",
       "std                     NaN             0.0  25779.747957                  NaN  \n",
       "min                     NaN             0.0   1067.000000                  NaN  \n",
       "25%                     NaN             0.0  30451.000000                  NaN  \n",
       "50%                     NaN             0.0  49577.000000                  NaN  \n",
       "75%                     NaN             0.0  71540.000000                  NaN  \n",
       "max                     NaN             0.0  99998.000000                  NaN  "
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It looks like the `price` and `odometer` columns have numeric values stored as text, so I will convert and clean those values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        1421\n",
       "500       781\n",
       "1500      734\n",
       "2500      643\n",
       "1000      639\n",
       "         ... \n",
       "20790       1\n",
       "8970        1\n",
       "846         1\n",
       "2895        1\n",
       "33980       1\n",
       "Name: price, Length: 2357, dtype: int64"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert price column to integers\n",
    "autos['price'] = autos['price'].str.replace('$', '')\n",
    "autos['price'] = autos['price'].str.replace(',', '')\n",
    "autos['price'] = autos['price'].astype(int)\n",
    "autos['price'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150000    32424\n",
       "125000     5170\n",
       "100000     2169\n",
       "90000      1757\n",
       "80000      1436\n",
       "70000      1230\n",
       "60000      1164\n",
       "50000      1027\n",
       "5000        967\n",
       "40000       819\n",
       "30000       789\n",
       "20000       784\n",
       "10000       264\n",
       "Name: odometer_km, dtype: int64"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert odometer column and rename to `odometer_km`\n",
    "autos['odometer'] = autos['odometer'].str.replace('km', '')\n",
    "autos['odometer'] = autos['odometer'].str.replace(',', '')\n",
    "autos['odometer'] = autos['odometer'].astype(int)\n",
    "autos.rename({'odometer':'odometer_km'}, axis=1, inplace=True)\n",
    "autos['odometer_km'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, I look at the `top` and `freq` entries to see if there are columns where all or almost all values are the same, as they might not be useful for analysis. It looks like the `seller` and `offer_type` columns have nearly all identical values, so we remove those:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "autos.drop(['seller', 'offer_type'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing outliers in the price column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I noticed when cleaning the `price` columns above that there were some unlikely values that might not be correct:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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\n"
     ]
    }
   ],
   "source": [
    "# view min/max/median etc\n",
    "print(autos['price'].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "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\n"
     ]
    }
   ],
   "source": [
    "# view very high prices\n",
    "print(autos['price'].value_counts().sort_index(ascending=False).head(20))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0     1421\n",
      "1      156\n",
      "2        3\n",
      "3        1\n",
      "5        2\n",
      "8        1\n",
      "9        1\n",
      "10       7\n",
      "11       2\n",
      "12       3\n",
      "13       2\n",
      "14       1\n",
      "15       2\n",
      "17       3\n",
      "18       1\n",
      "20       4\n",
      "25       5\n",
      "29       1\n",
      "30       7\n",
      "35       1\n",
      "Name: price, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# view very low prices\n",
    "print(autos['price'].value_counts().sort_index(ascending=True).head(20))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We are going to assume that prices below 200 and over 350000 are mistakes, so we'll remove the corresponding rows in the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     47645.000000\n",
       "mean       6000.707273\n",
       "std        9110.783444\n",
       "min         200.000000\n",
       "25%        1300.000000\n",
       "50%        3190.000000\n",
       "75%        7500.000000\n",
       "max      350000.000000\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos = autos[autos['price'].between(200, 350000)]\n",
    "autos['price'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Removing outliers in the odometer column"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I also noticed some potentially erroneous values in teh `odometer_km` column. Below, I check for any potential outliers: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     47645.000000\n",
       "mean     125887.501312\n",
       "std       39482.911790\n",
       "min        5000.000000\n",
       "25%      125000.000000\n",
       "50%      150000.000000\n",
       "75%      150000.000000\n",
       "max      150000.000000\n",
       "Name: odometer_km, dtype: float64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['odometer_km'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "150000    30781\n",
      "125000     4998\n",
      "100000     2083\n",
      "90000      1720\n",
      "80000      1407\n",
      "70000      1209\n",
      "60000      1145\n",
      "50000      1007\n",
      "40000       813\n",
      "30000       773\n",
      "Name: odometer_km, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# view very high odometer values\n",
    "print(autos['odometer_km'].value_counts().sort_index(ascending=False).head(10))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5000      720\n",
      "10000     242\n",
      "20000     747\n",
      "30000     773\n",
      "40000     813\n",
      "50000    1007\n",
      "60000    1145\n",
      "70000    1209\n",
      "80000    1407\n",
      "90000    1720\n",
      "Name: odometer_km, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# view very low odometer values\n",
    "print(autos['odometer_km'].value_counts().sort_index(ascending=True).head(10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I don't see any apparent outliers for this column, so we leave all entries for now."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning date columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are three columns where dates are currently formatted as strings: `date_crawled`, `last_seen`, `ad_created`. I am going to extract the date from each column to explore the distributions of dates:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-05    0.025354\n",
       "2016-03-06    0.014062\n",
       "2016-03-07    0.035995\n",
       "2016-03-08    0.033120\n",
       "2016-03-09    0.033036\n",
       "2016-03-10    0.032322\n",
       "2016-03-11    0.032700\n",
       "2016-03-12    0.036877\n",
       "2016-03-13    0.015699\n",
       "2016-03-14    0.036562\n",
       "2016-03-15    0.034232\n",
       "2016-03-16    0.029447\n",
       "2016-03-17    0.031546\n",
       "2016-03-18    0.012824\n",
       "2016-03-19    0.034610\n",
       "2016-03-20    0.037800\n",
       "2016-03-21    0.037360\n",
       "2016-03-22    0.032700\n",
       "2016-03-23    0.032385\n",
       "2016-03-24    0.029321\n",
       "2016-03-25    0.031420\n",
       "2016-03-26    0.032217\n",
       "2016-03-27    0.031210\n",
       "2016-03-28    0.035030\n",
       "2016-03-29    0.033980\n",
       "2016-03-30    0.033897\n",
       "2016-03-31    0.031840\n",
       "2016-04-01    0.033813\n",
       "2016-04-02    0.035681\n",
       "2016-04-03    0.038724\n",
       "2016-04-04    0.036562\n",
       "2016-04-05    0.013139\n",
       "2016-04-06    0.003169\n",
       "2016-04-07    0.001364\n",
       "Name: date_crawled, dtype: float64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['date_crawled'].str[:10].value_counts(normalize=True, dropna=False).sort_index(ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2015-06-11    0.000021\n",
       "2015-08-10    0.000021\n",
       "2015-09-09    0.000021\n",
       "2015-11-10    0.000021\n",
       "2015-12-05    0.000021\n",
       "                ...   \n",
       "2016-04-03    0.038976\n",
       "2016-04-04    0.036919\n",
       "2016-04-05    0.011859\n",
       "2016-04-06    0.003253\n",
       "2016-04-07    0.001217\n",
       "Name: ad_created, Length: 76, dtype: float64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['ad_created'].str[:10].value_counts(normalize=True, dropna=False).sort_index(ascending=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-05    0.001091\n",
       "2016-03-06    0.004303\n",
       "2016-03-07    0.005373\n",
       "2016-03-08    0.007178\n",
       "2016-03-09    0.009613\n",
       "2016-03-10    0.010473\n",
       "2016-03-11    0.012299\n",
       "2016-03-12    0.023906\n",
       "2016-03-13    0.008899\n",
       "2016-03-14    0.012530\n",
       "2016-03-15    0.015720\n",
       "2016-03-16    0.016287\n",
       "2016-03-17    0.028083\n",
       "2016-03-18    0.007283\n",
       "2016-03-19    0.015615\n",
       "2016-03-20    0.020653\n",
       "2016-03-21    0.020506\n",
       "2016-03-22    0.021408\n",
       "2016-03-23    0.018491\n",
       "2016-03-24    0.019624\n",
       "2016-03-25    0.019079\n",
       "2016-03-26    0.016686\n",
       "2016-03-27    0.015511\n",
       "2016-03-28    0.020758\n",
       "2016-03-29    0.022185\n",
       "2016-03-30    0.024578\n",
       "2016-03-31    0.023864\n",
       "2016-04-01    0.022899\n",
       "2016-04-02    0.024808\n",
       "2016-04-03    0.025102\n",
       "2016-04-04    0.024620\n",
       "2016-04-05    0.125470\n",
       "2016-04-06    0.222437\n",
       "2016-04-07    0.132669\n",
       "Name: last_seen, dtype: float64"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['last_seen'].str[:10].value_counts(normalize=True, dropna=False).sort_index(ascending=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the above, we can see that the ads included in the dataset were created in 2015 and 2016."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll also explore the `registration_year` column to explore the years of registration for the used cars:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    47645.000000\n",
       "mean      2004.800084\n",
       "std         88.423872\n",
       "min       1000.000000\n",
       "25%       1999.000000\n",
       "50%       2004.000000\n",
       "75%       2008.000000\n",
       "max       9999.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Noticing that there is at least one outlier (the max value, 9999), I'll look for outliers and remove values accordingly:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000    1\n",
       "1001    1\n",
       "1111    1\n",
       "1800    2\n",
       "1910    2\n",
       "1927    1\n",
       "1929    1\n",
       "1931    1\n",
       "1934    2\n",
       "1937    4\n",
       "Name: registration_year, dtype: int64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view very low registration years\n",
    "autos['registration_year'].value_counts().sort_index(ascending=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9999       3\n",
       "9000       1\n",
       "8888       1\n",
       "6200       1\n",
       "5911       1\n",
       "5000       3\n",
       "4800       1\n",
       "4500       1\n",
       "4100       1\n",
       "2800       1\n",
       "2019       1\n",
       "2018     468\n",
       "2017    1371\n",
       "2016    1161\n",
       "2015     376\n",
       "Name: registration_year, dtype: int64"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# view very high registration years\n",
    "autos['registration_year'].value_counts().sort_index(ascending=False).head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I'll treat years after 2016 and before 1910 as outliers or errors, since the ads were all created in 2015 or 2016, and it seems unlikely that cars built before 1910 would be for sale on eBay. I remove the corresponding rows from the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    45786.000000\n",
       "mean      2002.993098\n",
       "std          7.113188\n",
       "min       1910.000000\n",
       "25%       1999.000000\n",
       "50%       2003.000000\n",
       "75%       2008.000000\n",
       "max       2016.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos = autos[autos['registration_year'].between(1910, 2016)]\n",
    "autos['registration_year'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What is the price distribution of the most common used car brands?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, I'll explore the prices of used cars according to brand. I'll start by exploring what brands of cars are in the dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen        0.211244\n",
       "bmw               0.111257\n",
       "opel              0.105906\n",
       "mercedes_benz     0.097628\n",
       "audi              0.087603\n",
       "ford              0.068733\n",
       "renault           0.046543\n",
       "peugeot           0.029944\n",
       "fiat              0.025095\n",
       "seat              0.018171\n",
       "skoda             0.016512\n",
       "nissan            0.015289\n",
       "mazda             0.015136\n",
       "smart             0.014371\n",
       "citroen           0.014131\n",
       "toyota            0.012930\n",
       "hyundai           0.010112\n",
       "sonstige_autos    0.009544\n",
       "volvo             0.009195\n",
       "mini              0.008889\n",
       "mitsubishi        0.008168\n",
       "honda             0.007928\n",
       "kia               0.007142\n",
       "alfa_romeo        0.006683\n",
       "porsche           0.006094\n",
       "suzuki            0.005810\n",
       "chevrolet         0.005722\n",
       "chrysler          0.003560\n",
       "dacia             0.002686\n",
       "daihatsu          0.002490\n",
       "jeep              0.002315\n",
       "land_rover        0.002140\n",
       "subaru            0.002097\n",
       "saab              0.001660\n",
       "jaguar            0.001529\n",
       "daewoo            0.001485\n",
       "rover             0.001332\n",
       "trabant           0.001289\n",
       "lancia            0.001048\n",
       "lada              0.000590\n",
       "Name: brand, dtype: float64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For simplicity, I'm going to examine prices for used cars from only brands present for at least 5% of ads:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['volkswagen' 'bmw' 'opel' 'mercedes_benz' 'audi' 'ford']\n"
     ]
    }
   ],
   "source": [
    "brands_sorted = autos['brand'].value_counts(normalize=True)\n",
    "brands = brands_sorted[brands_sorted >= .05].index.values\n",
    "print(brands)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, I loop over this subset of 6 brands and find the mean used car price for each brand:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'volkswagen': 5506.4409636062865, 'bmw': 8402.666077738515, 'opel': 3077.5772324190557, 'mercedes_benz': 8691.71744966443, 'audi': 9406.093243580155, 'ford': 3883.292024149984}\n"
     ]
    }
   ],
   "source": [
    "prices_by_brand = {}\n",
    "for b in brands:\n",
    "    prices_by_brand[b] = autos.loc[autos['brand'] == b, 'price'].mean()\n",
    "    \n",
    "print(prices_by_brand)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result indicates that Audi, Mercedes Benz, and BMW cars were the most expensive, whereas Ford and Opel cars were the least expensive. Volkswagen fell between these two groupings."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Is there a link between price and mileage for the top used car brands?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, I'd like to see if there is a link between price and mileage, and whether that varies by brand. I will calculate the mean mileage and price for each of the 6 brands identified above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [],
   "source": [
    "mileage_by_brand = {}\n",
    "# calculate mean mileage for each of the top brands\n",
    "for b in brands:\n",
    "    mileage_by_brand[b] = autos.loc[autos['brand'] == b, 'odometer_km'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                mean_price   mean_mileage\n",
      "volkswagen     5506.440964  128774.813896\n",
      "bmw            8402.666078  132792.500982\n",
      "opel           3077.577232  129231.800371\n",
      "mercedes_benz  8691.717450  131091.722595\n",
      "audi           9406.093244  129260.782847\n",
      "ford           3883.292024  124095.964411\n"
     ]
    }
   ],
   "source": [
    "# join mean mileage and mean price data in a single dataframe\n",
    "price_series = pd.Series(prices_by_brand)\n",
    "mileage_series = pd.Series(mileage_by_brand)\n",
    "\n",
    "data_brands = pd.DataFrame(price_series, columns = ['mean_price'])\n",
    "data_brands = data_brands.assign(mean_mileage = mileage_series)\n",
    "print(data_brands)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The mean mileage is very similar across brands. So, it doesn't seem like there is a clear link between mileage and mean price. Brand seems to be more of a driver of price."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What are the most common brand/model combinations?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next, I want to figure out what the most common brand/model combinations in the dataset. I'm only examining combinations for the top 6 brands identified above:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "volkswagen:\n",
      "['golf' 'polo' 'passat']\n",
      "\n",
      "bmw:\n",
      "['3er' '5er' '1er']\n",
      "\n",
      "opel:\n",
      "['corsa' 'astra' 'vectra']\n",
      "\n",
      "mercedes_benz:\n",
      "['c_klasse' 'e_klasse' 'a_klasse']\n",
      "\n",
      "audi:\n",
      "['a4' 'a3' 'a6']\n",
      "\n",
      "ford:\n",
      "['focus' 'fiesta' 'mondeo']\n",
      "\n"
     ]
    }
   ],
   "source": [
    "models_by_brand = {}\n",
    "for b in brands:\n",
    "    # choose the top 3 most common models\n",
    "    top_models = np.array(autos.loc[autos['brand'] == b, 'model'].value_counts(normalize=True).head(3).index.values)\n",
    "    print(b + ':')\n",
    "    print(top_models)\n",
    "    print('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How much cheaper are cars with damage vs. without damage?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally, I compare the distribution of prices for cars with and without damage:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['nein', nan, 'ja'], dtype=object)"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['unrepaired_damage'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Unrepaired damage -- ja:\n",
      "[ 2361  1100   200 44200]\n",
      "Unrepaired damage -- nein:\n",
      "[  7198   4200    200 350000]\n"
     ]
    }
   ],
   "source": [
    "price_by_repairstatus = {}\n",
    "for value in ['ja', 'nein']:\n",
    "    price_by_repairstatus[value] = np.array([\n",
    "        autos.loc[autos['unrepaired_damage'] == value, 'price'].mean(),\n",
    "        autos.loc[autos['unrepaired_damage'] == value, 'price'].median(),\n",
    "        autos.loc[autos['unrepaired_damage'] == value, 'price'].min(),\n",
    "        autos.loc[autos['unrepaired_damage'] == value, 'price'].max()\n",
    "    ]\n",
    "    )\n",
    "    print('Unrepaired damage -- ' + value + ':')\n",
    "    print(price_by_repairstatus[value].astype(int))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not surprisingly, the mean, median and maximum price for cars without unrepaired damage is higher than that for cars with repaired damage. The minimum price for cars with and without damage is the same."
   ]
  }
 ],
 "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
}