eecsd.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Exploring Ebay Car Sales Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Dataquest Guided Project**\n",
    "\n",
    "We'll work with a dataset of used cars from eBay Kleinanzeigen, a classifieds section of the German eBay website.  \n",
    "The dataset was originally scraped and uploaded to Kaggle(https://www.kaggle.com/orgesleka/used-cars-database/data).  \n",
    "(the version uploaded to Kaggle was cleaned to be easier to work with)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The aim of this project is to clean the data and analyze the included used car listings.   \n",
    "The data dictionary provided with data is as follows:\n",
    "- **dateCrawled** - When this ad was first crawled. All field-values are taken from this date.\n",
    "- **name** - Name of the car.\n",
    "- **seller** - Whether the seller is private or a dealer.\n",
    "- **offerType** - The type of listing\n",
    "- **price** - The price on the ad to sell the car.\n",
    "- **abtest** - Whether the listing is included in an A/B test.\n",
    "- **vehicleType** - The vehicle Type.\n",
    "- **yearOfRegistration** - The year in which the car was first registered.\n",
    "- **gearbox** - The transmission type.\n",
    "- **powerPS** - The power of the car in PS.\n",
    "- **model** - The car model name.\n",
    "- **kilometer** - How many kilometers the car has driven.\n",
    "- **monthOfRegistration** - The month in which the car was first registered.\n",
    "- **fuelType** - What type of fuel the car uses.\n",
    "- **brand** - The brand of the car.\n",
    "- **notRepairedDamage** - If the car has a damage which is not yet repaired.\n",
    "- **dateCreated** - The date on which the eBay listing was created.\n",
    "- **nrOfPictures** - The number of pictures in the ad.\n",
    "- **postalCode** - The postal code for the location of the vehicle.\n",
    "- **lastSeenOnline** - When the crawler saw this ad last online."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos = pd.read_csv('autos.csv', encoding='latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": 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>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.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 50000 entries, 0 to 49999\n",
      "Data columns (total 20 columns):\n",
      "dateCrawled            50000 non-null object\n",
      "name                   50000 non-null object\n",
      "seller                 50000 non-null object\n",
      "offerType              50000 non-null object\n",
      "price                  50000 non-null object\n",
      "abtest                 50000 non-null object\n",
      "vehicleType            44905 non-null object\n",
      "yearOfRegistration     50000 non-null int64\n",
      "gearbox                47320 non-null object\n",
      "powerPS                50000 non-null int64\n",
      "model                  47242 non-null object\n",
      "odometer               50000 non-null object\n",
      "monthOfRegistration    50000 non-null int64\n",
      "fuelType               45518 non-null object\n",
      "brand                  50000 non-null object\n",
      "notRepairedDamage      40171 non-null object\n",
      "dateCreated            50000 non-null object\n",
      "nrOfPictures           50000 non-null int64\n",
      "postalCode             50000 non-null int64\n",
      "lastSeen               50000 non-null object\n",
      "dtypes: int64(5), object(15)\n",
      "memory usage: 7.6+ MB\n"
     ]
    }
   ],
   "source": [
    "autos.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Cleaning Column Names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From the work we did, we can make the following observations:\n",
    "- The dataset contains 20 columns, most of which are strings.\n",
    "- Some columns have null values, but none have more than ~20% null values.\n",
    "- The column names use camelcase instead of Python's preferred snakecase, which means we can't just replace spaces with underscores."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "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": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "autos.columns = [\n",
    "       '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",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>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>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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled                              name  seller offer_type  \\\n",
       "0  2016-03-26 17:47:46  Peugeot_807_160_NAVTECH_ON_BOARD  privat    Angebot   \n",
       "\n",
       "    price   abtest vehicle_type  registration_year  gearbox  power_ps   model  \\\n",
       "0  $5,000  control          bus               2004  manuell       158  andere   \n",
       "\n",
       "    odometer  registration_month fuel_type    brand unrepaired_damage  \\\n",
       "0  150,000km                   3       lpg  peugeot              nein   \n",
       "\n",
       "            ad_created  nr_of_pictures  postal_code            last_seen  \n",
       "0  2016-03-26 00:00:00               0        79588  2016-04-06 06:45:54  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Initial Exploration and Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's do some basic data exploration to determine what other cleaning tasks need to be done.   \n",
    "Initially we will look for: \n",
    "- Text columns where all or almost all values are the same. These can often be dropped as they don't have useful information for analysis. \n",
    "\n",
    "- Examples of numeric data stored as text which can be cleaned and converted."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "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>registration_year</th>\n",
       "      <th>power_ps</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>nr_of_pictures</th>\n",
       "      <th>postal_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.000000</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>50000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2005.073280</td>\n",
       "      <td>116.355920</td>\n",
       "      <td>5.723360</td>\n",
       "      <td>0.0</td>\n",
       "      <td>50813.627300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>105.712813</td>\n",
       "      <td>209.216627</td>\n",
       "      <td>3.711984</td>\n",
       "      <td>0.0</td>\n",
       "      <td>25779.747957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1067.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>1999.000000</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>30451.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>2003.000000</td>\n",
       "      <td>105.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>49577.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>2008.000000</td>\n",
       "      <td>150.000000</td>\n",
       "      <td>9.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>71540.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>9999.000000</td>\n",
       "      <td>17700.000000</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99998.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       registration_year      power_ps  registration_month  nr_of_pictures  \\\n",
       "count       50000.000000  50000.000000        50000.000000         50000.0   \n",
       "mean         2005.073280    116.355920            5.723360             0.0   \n",
       "std           105.712813    209.216627            3.711984             0.0   \n",
       "min          1000.000000      0.000000            0.000000             0.0   \n",
       "25%          1999.000000     70.000000            3.000000             0.0   \n",
       "50%          2003.000000    105.000000            6.000000             0.0   \n",
       "75%          2008.000000    150.000000            9.000000             0.0   \n",
       "max          9999.000000  17700.000000           12.000000             0.0   \n",
       "\n",
       "        postal_code  \n",
       "count  50000.000000  \n",
       "mean   50813.627300  \n",
       "std    25779.747957  \n",
       "min     1067.000000  \n",
       "25%    30451.000000  \n",
       "50%    49577.000000  \n",
       "75%    71540.000000  \n",
       "max    99998.000000  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "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": {
    "collapsed": false
   },
   "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": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos['price'] = autos['price'].str.replace('$','')\n",
    "autos['price'] = autos['price'].str.replace(',','')\n",
    "autos['price'] = autos['price'].astype(float)\n",
    "autos['odometer'] = autos['odometer'].str.replace('km','')\n",
    "autos['odometer'] = autos['odometer'].str.replace(',','')\n",
    "autos['odometer'] = autos['odometer'].astype(float)\n",
    "autos.rename(columns={'odometer':'odometer_km'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_crawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offer_type</th>\n",
       "      <th>price</th>\n",
       "      <th>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_km</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>nr_of_pictures</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>5000.0</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.0</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled                              name  seller offer_type  \\\n",
       "0  2016-03-26 17:47:46  Peugeot_807_160_NAVTECH_ON_BOARD  privat    Angebot   \n",
       "\n",
       "    price   abtest vehicle_type  registration_year  gearbox  power_ps   model  \\\n",
       "0  5000.0  control          bus               2004  manuell       158  andere   \n",
       "\n",
       "   odometer_km  registration_month fuel_type    brand unrepaired_damage  \\\n",
       "0     150000.0                   3       lpg  peugeot              nein   \n",
       "\n",
       "            ad_created  nr_of_pictures  postal_code            last_seen  \n",
       "0  2016-03-26 00:00:00               0        79588  2016-04-06 06:45:54  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## Exploring the Odometer and Price Columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We'll start by analyzing the odometer_km and price columns.Here's the steps we'll take:  \n",
    "- Analyze the columns using minimum and maximum values and look for any values that look unrealistically high or low (outliers) that we might want to remove.\n",
    "- We'll use:\n",
    " - **Series.unique().shape** to see how many unique values\n",
    " - **Series.describe()** to view min/max/median/mean etc\n",
    " - **Series.value_counts()**, with some variations:\n",
    "   - chained to **.head()** if there are lots of values.\n",
    "   - Because **Series.value_counts()** returns a series, we can use **Series.sort_index()** with **ascending=** **True** or **False** to view the highest and lowest values with their counts (can also chain to **head()** here).\n",
    " - When removing outliers, we can do **df[(df[\"col\"] > x ) & (df[\"col\"] < y )]**, but it's more readable to use **df[df[\"col\"].between(x,y)]**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13,)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['odometer_km'].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     50000.000000\n",
       "mean     125732.700000\n",
       "std       40042.211706\n",
       "min        5000.000000\n",
       "25%      125000.000000\n",
       "50%      150000.000000\n",
       "75%      150000.000000\n",
       "max      150000.000000\n",
       "Name: odometer_km, dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['odometer_km'].describe() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150000.0    32424\n",
       "125000.0     5170\n",
       "100000.0     2169\n",
       "90000.0      1757\n",
       "80000.0      1436\n",
       "70000.0      1230\n",
       "60000.0      1164\n",
       "50000.0      1027\n",
       "5000.0        967\n",
       "40000.0       819\n",
       "30000.0       789\n",
       "20000.0       784\n",
       "10000.0       264\n",
       "Name: odometer_km, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['odometer_km'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2357,)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].unique().shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "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": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].describe() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0       1421\n",
       "500.0      781\n",
       "1500.0     734\n",
       "2500.0     643\n",
       "1200.0     639\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['price'].value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring the date columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's now move on to the date columns and understand the date range the data covers.\n",
    "\n",
    "There are 5 columns that should represent date values. Some of these columns were created by the crawler, some came from the website itself. We can differentiate by referring to the data dictionary:\n",
    "- **date_crawled**: added by the crawler\n",
    "- **last_seen**: added by the crawler\n",
    "- **ad_created**: from the website\n",
    "- **registration_month**: from the website\n",
    "- **registration_year**: from the website"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Right now, the **date_crawled**, **last_seen**, and **ad_created** columns are all identified as string values by pandas. Because these three columns are represented as strings, we need to convert the data into a numerical representation so we can understand it quantitatively. The other two columns are represented as numeric values, so we can use methods like **Series.describe()** to understand the distribution without any extra data processing."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's first understand how the values in the three string columns are formatted. These columns all represent full timestamp values, like so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date_crawled</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>2016-03-26 00:00:00</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>2016-04-04 00:00:00</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>2016-03-26 00:00:00</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>2016-03-12 00:00:00</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>2016-04-01 00:00:00</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled           ad_created            last_seen\n",
       "0  2016-03-26 17:47:46  2016-03-26 00:00:00  2016-04-06 06:45:54\n",
       "1  2016-04-04 13:38:56  2016-04-04 00:00:00  2016-04-06 14:45:08\n",
       "2  2016-03-26 18:57:24  2016-03-26 00:00:00  2016-04-06 20:15:37\n",
       "3  2016-03-12 16:58:10  2016-03-12 00:00:00  2016-03-15 03:16:28\n",
       "4  2016-04-01 14:38:50  2016-04-01 00:00:00  2016-04-01 14:38:50"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[['date_crawled','ad_created','last_seen']][0:5]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You'll notice that the first 10 characters represent the day (e.g. **2016-03-12**). To understand the date range, we can extract just the date values, use **Series.value_counts()** to generate a distribution, and then sort by the index.\n",
    "\n",
    "To select the first 10 characters in each column, we can use **Series.str[:10]**:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016-03-26\n",
       "1        2016-04-04\n",
       "2        2016-03-26\n",
       "3        2016-03-12\n",
       "4        2016-04-01\n",
       "5        2016-03-21\n",
       "6        2016-03-20\n",
       "7        2016-03-16\n",
       "8        2016-03-22\n",
       "9        2016-03-16\n",
       "10       2016-03-15\n",
       "11       2016-03-16\n",
       "12       2016-03-31\n",
       "13       2016-03-23\n",
       "14       2016-03-23\n",
       "15       2016-04-01\n",
       "16       2016-03-16\n",
       "17       2016-03-29\n",
       "18       2016-03-26\n",
       "19       2016-03-17\n",
       "20       2016-03-05\n",
       "21       2016-03-06\n",
       "22       2016-03-28\n",
       "23       2016-03-10\n",
       "24       2016-04-03\n",
       "25       2016-03-21\n",
       "26       2016-04-03\n",
       "27       2016-03-27\n",
       "28       2016-03-19\n",
       "29       2016-04-02\n",
       "            ...    \n",
       "49970    2016-03-21\n",
       "49971    2016-03-29\n",
       "49972    2016-03-26\n",
       "49973    2016-03-27\n",
       "49974    2016-03-20\n",
       "49975    2016-03-27\n",
       "49976    2016-03-19\n",
       "49977    2016-03-31\n",
       "49978    2016-04-04\n",
       "49979    2016-03-20\n",
       "49980    2016-03-12\n",
       "49981    2016-03-15\n",
       "49982    2016-03-29\n",
       "49983    2016-03-06\n",
       "49984    2016-03-31\n",
       "49985    2016-04-02\n",
       "49986    2016-04-04\n",
       "49987    2016-03-22\n",
       "49988    2016-03-28\n",
       "49989    2016-03-11\n",
       "49990    2016-03-21\n",
       "49991    2016-03-06\n",
       "49992    2016-03-10\n",
       "49993    2016-03-15\n",
       "49994    2016-03-22\n",
       "49995    2016-03-27\n",
       "49996    2016-03-28\n",
       "49997    2016-04-02\n",
       "49998    2016-03-08\n",
       "49999    2016-03-14\n",
       "Name: date_crawled, Length: 50000, dtype: object"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['date_crawled'].str[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-03-27 22:55:05    0.00006\n",
       "2016-03-21 16:37:21    0.00006\n",
       "2016-03-14 20:50:02    0.00006\n",
       "2016-03-25 19:57:10    0.00006\n",
       "2016-03-23 18:39:34    0.00006\n",
       "2016-03-29 23:42:13    0.00006\n",
       "2016-03-23 19:38:20    0.00006\n",
       "2016-03-21 20:37:19    0.00006\n",
       "2016-03-11 22:38:16    0.00006\n",
       "2016-03-22 09:51:06    0.00006\n",
       "2016-03-10 15:36:24    0.00006\n",
       "2016-03-05 16:57:05    0.00006\n",
       "2016-04-02 11:37:04    0.00006\n",
       "2016-03-08 10:40:35    0.00006\n",
       "2016-04-02 15:49:30    0.00006\n",
       "2016-03-19 17:36:18    0.00006\n",
       "2016-04-04 16:40:33    0.00006\n",
       "2016-03-09 11:54:38    0.00006\n",
       "2016-03-12 16:06:22    0.00006\n",
       "2016-03-30 17:37:35    0.00006\n",
       "2016-03-30 19:48:02    0.00006\n",
       "2016-03-16 21:50:53    0.00006\n",
       "2016-03-09 23:25:18    0.00004\n",
       "2016-03-23 20:25:18    0.00004\n",
       "2016-04-04 14:48:38    0.00004\n",
       "2016-03-30 02:57:54    0.00004\n",
       "2016-04-02 14:58:27    0.00004\n",
       "2016-03-11 10:54:11    0.00004\n",
       "2016-03-20 15:45:40    0.00004\n",
       "2016-03-20 12:25:21    0.00004\n",
       "                        ...   \n",
       "2016-03-25 20:49:27    0.00002\n",
       "2016-03-16 00:39:13    0.00002\n",
       "2016-03-29 10:37:02    0.00002\n",
       "2016-04-02 07:58:40    0.00002\n",
       "2016-03-19 04:43:51    0.00002\n",
       "2016-03-13 17:46:42    0.00002\n",
       "2016-03-09 20:52:35    0.00002\n",
       "2016-03-08 23:57:22    0.00002\n",
       "2016-04-02 09:37:34    0.00002\n",
       "2016-03-23 09:44:40    0.00002\n",
       "2016-03-05 22:43:57    0.00002\n",
       "2016-04-02 01:37:12    0.00002\n",
       "2016-03-08 14:46:42    0.00002\n",
       "2016-03-14 23:51:54    0.00002\n",
       "2016-03-16 20:45:27    0.00002\n",
       "2016-03-12 21:51:51    0.00002\n",
       "2016-03-12 23:50:35    0.00002\n",
       "2016-03-26 21:55:44    0.00002\n",
       "2016-03-13 19:37:41    0.00002\n",
       "2016-03-27 16:36:44    0.00002\n",
       "2016-03-09 09:56:59    0.00002\n",
       "2016-04-04 10:49:00    0.00002\n",
       "2016-03-10 21:54:00    0.00002\n",
       "2016-03-31 14:47:46    0.00002\n",
       "2016-03-23 15:53:39    0.00002\n",
       "2016-03-14 12:53:19    0.00002\n",
       "2016-03-26 22:45:41    0.00002\n",
       "2016-03-05 14:56:07    0.00002\n",
       "2016-03-26 11:57:48    0.00002\n",
       "2016-03-16 21:48:59    0.00002\n",
       "Name: date_crawled, Length: 48213, dtype: float64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['date_crawled'].value_counts(normalize=True, dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016-03-26 17:47:46\n",
       "1        2016-04-04 13:38:56\n",
       "2        2016-03-26 18:57:24\n",
       "3        2016-03-12 16:58:10\n",
       "4        2016-04-01 14:38:50\n",
       "5        2016-03-21 13:47:45\n",
       "6        2016-03-20 17:55:21\n",
       "7        2016-03-16 18:55:19\n",
       "8        2016-03-22 16:51:34\n",
       "9        2016-03-16 13:47:02\n",
       "10       2016-03-15 01:41:36\n",
       "11       2016-03-16 18:45:34\n",
       "12       2016-03-31 19:48:22\n",
       "13       2016-03-23 10:48:32\n",
       "14       2016-03-23 11:50:46\n",
       "15       2016-04-01 12:06:20\n",
       "16       2016-03-16 14:59:02\n",
       "17       2016-03-29 11:46:22\n",
       "18       2016-03-26 19:57:44\n",
       "19       2016-03-17 13:36:21\n",
       "20       2016-03-05 19:57:31\n",
       "21       2016-03-06 19:07:10\n",
       "22       2016-03-28 20:50:54\n",
       "23       2016-03-10 19:55:34\n",
       "24       2016-04-03 11:57:02\n",
       "25       2016-03-21 21:56:18\n",
       "26       2016-04-03 22:46:28\n",
       "27       2016-03-27 18:45:01\n",
       "28       2016-03-19 21:56:19\n",
       "29       2016-04-02 12:45:44\n",
       "                ...         \n",
       "49970    2016-03-21 22:47:37\n",
       "49971    2016-03-29 14:54:12\n",
       "49972    2016-03-26 22:25:23\n",
       "49973    2016-03-27 05:32:39\n",
       "49974    2016-03-20 10:52:31\n",
       "49975    2016-03-27 20:51:39\n",
       "49976    2016-03-19 18:56:05\n",
       "49977    2016-03-31 18:37:18\n",
       "49978    2016-04-04 10:37:14\n",
       "49979    2016-03-20 18:38:40\n",
       "49980    2016-03-12 10:55:54\n",
       "49981    2016-03-15 09:38:21\n",
       "49982    2016-03-29 18:51:08\n",
       "49983    2016-03-06 12:43:04\n",
       "49984    2016-03-31 22:48:48\n",
       "49985    2016-04-02 16:38:23\n",
       "49986    2016-04-04 20:46:02\n",
       "49987    2016-03-22 20:47:27\n",
       "49988    2016-03-28 19:49:51\n",
       "49989    2016-03-11 19:50:37\n",
       "49990    2016-03-21 19:54:19\n",
       "49991    2016-03-06 15:25:19\n",
       "49992    2016-03-10 19:37:38\n",
       "49993    2016-03-15 18:47:35\n",
       "49994    2016-03-22 17:36:42\n",
       "49995    2016-03-27 14:38:19\n",
       "49996    2016-03-28 10:50:25\n",
       "49997    2016-04-02 14:44:48\n",
       "49998    2016-03-08 19:25:42\n",
       "49999    2016-03-14 00:42:12\n",
       "Name: date_crawled, Length: 50000, dtype: object"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['date_crawled'].sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-04-03 00:00:00    0.03892\n",
       "2016-03-20 00:00:00    0.03786\n",
       "2016-03-21 00:00:00    0.03772\n",
       "2016-04-04 00:00:00    0.03688\n",
       "2016-03-12 00:00:00    0.03662\n",
       "2016-03-14 00:00:00    0.03522\n",
       "2016-04-02 00:00:00    0.03508\n",
       "2016-03-28 00:00:00    0.03496\n",
       "2016-03-07 00:00:00    0.03474\n",
       "2016-03-29 00:00:00    0.03414\n",
       "2016-03-19 00:00:00    0.03384\n",
       "2016-04-01 00:00:00    0.03380\n",
       "2016-03-15 00:00:00    0.03374\n",
       "2016-03-30 00:00:00    0.03344\n",
       "2016-03-08 00:00:00    0.03334\n",
       "2016-03-09 00:00:00    0.03324\n",
       "2016-03-22 00:00:00    0.03280\n",
       "2016-03-11 00:00:00    0.03278\n",
       "2016-03-26 00:00:00    0.03256\n",
       "2016-03-23 00:00:00    0.03218\n",
       "2016-03-31 00:00:00    0.03192\n",
       "2016-03-25 00:00:00    0.03188\n",
       "2016-03-10 00:00:00    0.03186\n",
       "2016-03-17 00:00:00    0.03120\n",
       "2016-03-27 00:00:00    0.03090\n",
       "2016-03-16 00:00:00    0.03000\n",
       "2016-03-24 00:00:00    0.02908\n",
       "2016-03-05 00:00:00    0.02304\n",
       "2016-03-13 00:00:00    0.01692\n",
       "2016-03-06 00:00:00    0.01512\n",
       "                        ...   \n",
       "2016-02-12 00:00:00    0.00006\n",
       "2016-02-24 00:00:00    0.00004\n",
       "2016-02-02 00:00:00    0.00004\n",
       "2016-02-09 00:00:00    0.00004\n",
       "2016-02-14 00:00:00    0.00004\n",
       "2016-01-10 00:00:00    0.00004\n",
       "2016-02-20 00:00:00    0.00004\n",
       "2016-02-05 00:00:00    0.00004\n",
       "2016-02-18 00:00:00    0.00004\n",
       "2016-02-26 00:00:00    0.00004\n",
       "2016-02-22 00:00:00    0.00002\n",
       "2016-02-01 00:00:00    0.00002\n",
       "2016-02-16 00:00:00    0.00002\n",
       "2016-01-16 00:00:00    0.00002\n",
       "2016-01-29 00:00:00    0.00002\n",
       "2015-11-10 00:00:00    0.00002\n",
       "2016-02-08 00:00:00    0.00002\n",
       "2015-08-10 00:00:00    0.00002\n",
       "2016-01-14 00:00:00    0.00002\n",
       "2015-12-05 00:00:00    0.00002\n",
       "2016-01-03 00:00:00    0.00002\n",
       "2016-01-07 00:00:00    0.00002\n",
       "2015-09-09 00:00:00    0.00002\n",
       "2016-01-22 00:00:00    0.00002\n",
       "2016-02-17 00:00:00    0.00002\n",
       "2016-01-13 00:00:00    0.00002\n",
       "2015-12-30 00:00:00    0.00002\n",
       "2016-02-07 00:00:00    0.00002\n",
       "2015-06-11 00:00:00    0.00002\n",
       "2016-02-11 00:00:00    0.00002\n",
       "Name: ad_created, Length: 76, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['ad_created'].value_counts(normalize=True, dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016-03-26 00:00:00\n",
       "1        2016-04-04 00:00:00\n",
       "2        2016-03-26 00:00:00\n",
       "3        2016-03-12 00:00:00\n",
       "4        2016-04-01 00:00:00\n",
       "5        2016-03-21 00:00:00\n",
       "6        2016-03-20 00:00:00\n",
       "7        2016-03-16 00:00:00\n",
       "8        2016-03-22 00:00:00\n",
       "9        2016-03-16 00:00:00\n",
       "10       2016-03-14 00:00:00\n",
       "11       2016-03-16 00:00:00\n",
       "12       2016-03-31 00:00:00\n",
       "13       2016-03-23 00:00:00\n",
       "14       2016-03-23 00:00:00\n",
       "15       2016-04-01 00:00:00\n",
       "16       2016-03-16 00:00:00\n",
       "17       2016-03-29 00:00:00\n",
       "18       2016-03-26 00:00:00\n",
       "19       2016-03-17 00:00:00\n",
       "20       2016-03-05 00:00:00\n",
       "21       2016-03-06 00:00:00\n",
       "22       2016-03-28 00:00:00\n",
       "23       2016-03-10 00:00:00\n",
       "24       2016-04-03 00:00:00\n",
       "25       2016-03-21 00:00:00\n",
       "26       2016-04-03 00:00:00\n",
       "27       2016-03-27 00:00:00\n",
       "28       2016-03-19 00:00:00\n",
       "29       2016-04-02 00:00:00\n",
       "                ...         \n",
       "49970    2016-03-21 00:00:00\n",
       "49971    2016-03-29 00:00:00\n",
       "49972    2016-03-26 00:00:00\n",
       "49973    2016-03-27 00:00:00\n",
       "49974    2016-03-20 00:00:00\n",
       "49975    2016-03-27 00:00:00\n",
       "49976    2016-03-19 00:00:00\n",
       "49977    2016-03-31 00:00:00\n",
       "49978    2016-04-04 00:00:00\n",
       "49979    2016-03-20 00:00:00\n",
       "49980    2016-03-12 00:00:00\n",
       "49981    2016-03-15 00:00:00\n",
       "49982    2016-03-29 00:00:00\n",
       "49983    2016-03-06 00:00:00\n",
       "49984    2016-03-31 00:00:00\n",
       "49985    2016-04-02 00:00:00\n",
       "49986    2016-04-04 00:00:00\n",
       "49987    2016-03-22 00:00:00\n",
       "49988    2016-03-28 00:00:00\n",
       "49989    2016-03-11 00:00:00\n",
       "49990    2016-03-21 00:00:00\n",
       "49991    2016-03-06 00:00:00\n",
       "49992    2016-03-10 00:00:00\n",
       "49993    2016-03-15 00:00:00\n",
       "49994    2016-03-22 00:00:00\n",
       "49995    2016-03-27 00:00:00\n",
       "49996    2016-03-28 00:00:00\n",
       "49997    2016-04-02 00:00:00\n",
       "49998    2016-03-08 00:00:00\n",
       "49999    2016-03-13 00:00:00\n",
       "Name: ad_created, Length: 50000, dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['ad_created'].sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-04-07 06:17:27    0.00016\n",
       "2016-04-06 06:17:24    0.00014\n",
       "2016-04-06 21:17:51    0.00014\n",
       "2016-04-07 03:16:17    0.00014\n",
       "2016-04-06 01:16:01    0.00012\n",
       "2016-04-06 02:17:26    0.00012\n",
       "2016-04-06 05:16:14    0.00012\n",
       "2016-04-06 14:17:04    0.00012\n",
       "2016-04-06 22:17:26    0.00012\n",
       "2016-04-05 16:44:47    0.00012\n",
       "2016-04-06 07:46:11    0.00012\n",
       "2016-04-06 07:46:03    0.00012\n",
       "2016-04-07 05:16:17    0.00012\n",
       "2016-04-07 04:46:51    0.00012\n",
       "2016-04-06 19:16:38    0.00012\n",
       "2016-04-06 15:17:56    0.00012\n",
       "2016-04-06 02:16:12    0.00012\n",
       "2016-04-06 10:17:12    0.00012\n",
       "2016-04-07 03:45:23    0.00012\n",
       "2016-04-06 19:15:21    0.00012\n",
       "2016-04-06 15:45:50    0.00012\n",
       "2016-04-06 08:44:19    0.00012\n",
       "2016-04-06 14:44:55    0.00012\n",
       "2016-04-06 15:16:45    0.00012\n",
       "2016-04-06 13:17:03    0.00012\n",
       "2016-04-07 06:46:12    0.00012\n",
       "2016-04-06 20:48:27    0.00012\n",
       "2016-04-07 04:46:40    0.00010\n",
       "2016-04-07 07:44:37    0.00010\n",
       "2016-04-06 08:44:40    0.00010\n",
       "                        ...   \n",
       "2016-03-12 02:16:44    0.00002\n",
       "2016-04-05 13:18:54    0.00002\n",
       "2016-03-31 17:47:26    0.00002\n",
       "2016-03-27 04:17:31    0.00002\n",
       "2016-04-07 02:17:43    0.00002\n",
       "2016-03-22 06:51:18    0.00002\n",
       "2016-04-03 21:47:23    0.00002\n",
       "2016-03-28 12:44:58    0.00002\n",
       "2016-04-07 01:17:01    0.00002\n",
       "2016-04-04 17:47:30    0.00002\n",
       "2016-04-02 08:46:03    0.00002\n",
       "2016-03-09 13:17:34    0.00002\n",
       "2016-04-06 11:15:57    0.00002\n",
       "2016-03-16 12:37:16    0.00002\n",
       "2016-03-19 07:47:33    0.00002\n",
       "2016-03-12 14:46:55    0.00002\n",
       "2016-03-23 16:48:56    0.00002\n",
       "2016-03-27 17:47:28    0.00002\n",
       "2016-03-17 16:17:47    0.00002\n",
       "2016-03-08 16:15:22    0.00002\n",
       "2016-03-24 10:45:38    0.00002\n",
       "2016-04-04 18:56:57    0.00002\n",
       "2016-03-12 23:46:51    0.00002\n",
       "2016-03-09 14:16:57    0.00002\n",
       "2016-03-26 14:16:41    0.00002\n",
       "2016-04-05 12:46:11    0.00002\n",
       "2016-03-23 11:48:08    0.00002\n",
       "2016-03-17 17:44:41    0.00002\n",
       "2016-03-13 01:46:39    0.00002\n",
       "2016-04-05 11:44:57    0.00002\n",
       "Name: last_seen, Length: 39481, dtype: float64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['last_seen'].value_counts(normalize=True, dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        2016-04-06 06:45:54\n",
       "1        2016-04-06 14:45:08\n",
       "2        2016-04-06 20:15:37\n",
       "3        2016-03-15 03:16:28\n",
       "4        2016-04-01 14:38:50\n",
       "5        2016-04-06 09:45:21\n",
       "6        2016-03-23 02:48:59\n",
       "7        2016-04-07 03:17:32\n",
       "8        2016-03-26 18:18:10\n",
       "9        2016-04-06 10:46:35\n",
       "10       2016-04-07 03:16:21\n",
       "11       2016-03-16 18:45:34\n",
       "12       2016-04-06 14:17:52\n",
       "13       2016-04-01 14:17:13\n",
       "14       2016-04-01 15:46:47\n",
       "15       2016-04-02 21:10:48\n",
       "16       2016-03-18 05:29:37\n",
       "17       2016-04-05 20:46:26\n",
       "18       2016-04-02 12:16:41\n",
       "19       2016-03-17 14:45:58\n",
       "20       2016-03-07 05:46:46\n",
       "21       2016-04-05 23:46:19\n",
       "22       2016-04-01 06:45:30\n",
       "23       2016-03-17 08:45:17\n",
       "24       2016-04-07 13:16:50\n",
       "25       2016-04-01 05:16:49\n",
       "26       2016-04-05 23:46:48\n",
       "27       2016-03-27 18:45:01\n",
       "28       2016-04-07 14:58:48\n",
       "29       2016-04-02 12:45:44\n",
       "                ...         \n",
       "49970    2016-04-07 04:17:34\n",
       "49971    2016-03-29 20:41:51\n",
       "49972    2016-03-28 11:28:18\n",
       "49973    2016-03-27 08:25:24\n",
       "49974    2016-03-27 19:48:16\n",
       "49975    2016-04-05 19:45:34\n",
       "49976    2016-04-07 06:16:44\n",
       "49977    2016-04-06 12:16:11\n",
       "49978    2016-04-06 12:44:20\n",
       "49979    2016-04-07 01:45:12\n",
       "49980    2016-04-06 17:16:49\n",
       "49981    2016-04-05 17:21:46\n",
       "49982    2016-03-29 18:51:08\n",
       "49983    2016-03-09 06:16:08\n",
       "49984    2016-04-02 19:44:53\n",
       "49985    2016-04-06 15:17:52\n",
       "49986    2016-04-06 23:16:00\n",
       "49987    2016-03-26 22:46:06\n",
       "49988    2016-04-07 02:17:40\n",
       "49989    2016-03-12 10:17:55\n",
       "49990    2016-04-06 22:46:57\n",
       "49991    2016-03-06 18:24:19\n",
       "49992    2016-03-13 01:44:51\n",
       "49993    2016-04-06 19:46:53\n",
       "49994    2016-04-06 08:16:39\n",
       "49995    2016-04-01 13:47:40\n",
       "49996    2016-04-02 14:18:02\n",
       "49997    2016-04-04 11:47:27\n",
       "49998    2016-04-05 16:45:07\n",
       "49999    2016-04-06 21:18:48\n",
       "Name: last_seen, Length: 50000, dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['last_seen'].sort_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    50000.000000\n",
       "mean      2005.073280\n",
       "std        105.712813\n",
       "min       1000.000000\n",
       "25%       1999.000000\n",
       "50%       2003.000000\n",
       "75%       2008.000000\n",
       "max       9999.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dealing with Incorrect Registration Year Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One thing that stands out from the exploration we did in the last screen is that the registration_year column contains some odd values:\n",
    "- The minimum value is **1000**, before cars were invented\n",
    "- The maximum value is **9999**, many years into the future\n",
    "\n",
    "Because a car can't be first registered after the listing was seen, any vehicle with a registration year above 2016 is definitely inaccurate. Determining the earliest valid year is more difficult. Realistically, it could be somewhere in the first few decades of the 1900s.\n",
    "\n",
    "Let's count the number of listings with cars that fall outside the 1900 - 2016 interval and see if it's safe to remove those rows entirely, or if we need more custom logic."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(519,)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.loc[~ autos['registration_year'].between(1899, 2017), 'registration_year'].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos.loc[~ autos['registration_year'].between(1899, 2017), 'registration_year'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos.dropna(subset=['registration_year'], axis=0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000.0    0.067784\n",
       "2005.0    0.060932\n",
       "1999.0    0.060629\n",
       "2004.0    0.055314\n",
       "2003.0    0.055112\n",
       "2006.0    0.054728\n",
       "2001.0    0.054627\n",
       "2002.0    0.051191\n",
       "1998.0    0.049575\n",
       "2007.0    0.046563\n",
       "2008.0    0.045088\n",
       "2009.0    0.042400\n",
       "1997.0    0.040985\n",
       "2011.0    0.033023\n",
       "2010.0    0.032275\n",
       "2017.0    0.029365\n",
       "1996.0    0.029183\n",
       "2012.0    0.026738\n",
       "2016.0    0.026596\n",
       "1995.0    0.026535\n",
       "2013.0    0.016289\n",
       "2014.0    0.013460\n",
       "1994.0    0.013338\n",
       "1993.0    0.008993\n",
       "2015.0    0.008064\n",
       "1990.0    0.007983\n",
       "1992.0    0.007902\n",
       "1991.0    0.007195\n",
       "1989.0    0.003658\n",
       "1988.0    0.002870\n",
       "            ...   \n",
       "1977.0    0.000445\n",
       "1966.0    0.000445\n",
       "1975.0    0.000384\n",
       "1969.0    0.000384\n",
       "1965.0    0.000344\n",
       "1964.0    0.000243\n",
       "1910.0    0.000182\n",
       "1963.0    0.000182\n",
       "1959.0    0.000141\n",
       "1961.0    0.000121\n",
       "1956.0    0.000101\n",
       "1962.0    0.000081\n",
       "1958.0    0.000081\n",
       "1937.0    0.000081\n",
       "1950.0    0.000061\n",
       "1934.0    0.000040\n",
       "1941.0    0.000040\n",
       "1951.0    0.000040\n",
       "1954.0    0.000040\n",
       "1955.0    0.000040\n",
       "1957.0    0.000040\n",
       "1952.0    0.000020\n",
       "1953.0    0.000020\n",
       "1948.0    0.000020\n",
       "1943.0    0.000020\n",
       "1939.0    0.000020\n",
       "1938.0    0.000020\n",
       "1931.0    0.000020\n",
       "1929.0    0.000020\n",
       "1927.0    0.000020\n",
       "Name: registration_year, Length: 79, dtype: float64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['registration_year'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(49481, 20)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exploring Price by Brand"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When working with data on cars, it's natural to explore variations across different car brands. We can use aggregation to understand the **brand** column. Here's what the process looks like:  \n",
    "- Identify the unique values we want to aggregate by\n",
    "- Create an empty dictionary to store our aggregate data\n",
    "- Loop over the unique values, and for each:\n",
    "    - Subset the dataframe by the unique values\n",
    "    - Calculate the mean of whichever column we're interested in\n",
    "    - Assign the val/mean to the dict as k/v."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['peugeot', 'bmw', 'volkswagen', 'smart', 'ford', 'chrysler',\n",
       "       'seat', 'renault', 'mercedes_benz', 'audi', 'sonstige_autos',\n",
       "       'opel', 'mazda', 'porsche', 'mini', 'toyota', 'dacia', 'nissan',\n",
       "       'jeep', 'saab', 'volvo', 'mitsubishi', 'jaguar', 'fiat', 'skoda',\n",
       "       'subaru', 'kia', 'citroen', 'chevrolet', 'hyundai', 'honda',\n",
       "       'daewoo', 'suzuki', 'trabant', 'land_rover', 'alfa_romeo', 'lada',\n",
       "       'rover', 'daihatsu', 'lancia'], dtype=object)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen        10566\n",
       "opel               5391\n",
       "bmw                5383\n",
       "mercedes_benz      4690\n",
       "audi               4254\n",
       "ford               3451\n",
       "renault            2368\n",
       "peugeot            1449\n",
       "fiat               1288\n",
       "seat                915\n",
       "skoda               782\n",
       "mazda               751\n",
       "nissan              749\n",
       "smart               695\n",
       "citroen             693\n",
       "toyota              615\n",
       "sonstige_autos      534\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",
       "saab                 78\n",
       "daewoo               78\n",
       "trabant              77\n",
       "jaguar               76\n",
       "rover                69\n",
       "lancia               56\n",
       "lada                 29\n",
       "Name: brand, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brand_mean_prices = {}\n",
    "brands = autos['brand'].unique()\n",
    "\n",
    "for brand in brands:\n",
    "    brand_mean_prices[brand] = autos.loc[autos['brand'] == brand, 'price'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'alfa_romeo': 3955.4495412844035,\n",
       " 'audi': 8989.552891396334,\n",
       " 'bmw': 8279.482258963404,\n",
       " 'chevrolet': 6496.311827956989,\n",
       " 'chrysler': 3286.0552486187844,\n",
       " 'citroen': 43097.88888888889,\n",
       " 'dacia': 5920.3828125,\n",
       " 'daewoo': 1049.1025641025642,\n",
       " 'daihatsu': 1535.4603174603174,\n",
       " 'fiat': 12276.558229813665,\n",
       " 'ford': 7140.0625905534625,\n",
       " 'honda': 3928.2762148337597,\n",
       " 'hyundai': 5303.065979381443,\n",
       " 'jaguar': 11176.197368421053,\n",
       " 'jeep': 11377.550458715596,\n",
       " 'kia': 5724.385269121813,\n",
       " 'lada': 2502.896551724138,\n",
       " 'lancia': 3057.339285714286,\n",
       " 'land_rover': 18934.272727272728,\n",
       " 'mazda': 3968.7709720372836,\n",
       " 'mercedes_benz': 29704.757995735607,\n",
       " 'mini': 10392.393867924528,\n",
       " 'mitsubishi': 3320.187969924812,\n",
       " 'nissan': 4589.3564753004,\n",
       " 'opel': 5143.339454646633,\n",
       " 'peugeot': 3014.3381642512077,\n",
       " 'porsche': 44553.46757679181,\n",
       " 'renault': 2364.367820945946,\n",
       " 'rover': 1494.5217391304348,\n",
       " 'saab': 3192.269230769231,\n",
       " 'seat': 4259.546448087432,\n",
       " 'skoda': 6300.735294117647,\n",
       " 'smart': 3494.264748201439,\n",
       " 'sonstige_autos': 39098.70599250936,\n",
       " 'subaru': 3702.1401869158876,\n",
       " 'suzuki': 4028.59169550173,\n",
       " 'toyota': 5101.678048780488,\n",
       " 'trabant': 1557.2077922077922,\n",
       " 'volkswagen': 6417.041169789892,\n",
       " 'volvo': 4690.112334801762}"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_mean_prices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Storing Aggregate Data in a DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "we aggregated across brands to understand mean price. We observed that in the top 6 brands, there's a distinct price gap.\n",
    "- Audi, BMW and Mercedes Benz are more expensive\n",
    "- Ford and Opel are less expensive\n",
    "- Volkswagen is in between"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the top 6 brands, let's use aggregation to understand the average mileage for those cars and if there's any visible link with mean price. While our natural instinct may be to display both aggregated series objects and visually compare them, this has a few limitations:\n",
    "\n",
    "- it's difficult to compare more than two aggregate series objects if we want to extend to more columns\n",
    "- we can't compare more than a few rows from each series object\n",
    "- we can only sort by the index (brand name) of both series objects so we can easily make visual comparisons"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead, we can combine the data from both series objects into a single dataframe (with a shared index) and display the dataframe directly. To do this, we'll need to learn two pandas methods:\n",
    "- pandas series constructor\n",
    "- pandas dataframe constructor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brand_mean_mileage = {}\n",
    "for brand in brands:\n",
    "    brand_mean_mileage[brand] = autos.loc[autos['brand'] == brand, 'odometer_km'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'alfa_romeo': 131437.30886850154,\n",
       " 'audi': 129561.58909261871,\n",
       " 'bmw': 132520.89912688092,\n",
       " 'chevrolet': 99014.33691756273,\n",
       " 'chrysler': 133149.17127071825,\n",
       " 'citroen': 119834.05483405484,\n",
       " 'dacia': 84218.75,\n",
       " 'daewoo': 121346.15384615384,\n",
       " 'daihatsu': 115714.28571428571,\n",
       " 'fiat': 116836.1801242236,\n",
       " 'ford': 124058.24398725007,\n",
       " 'honda': 123631.71355498722,\n",
       " 'hyundai': 106690.72164948453,\n",
       " 'jaguar': 120921.05263157895,\n",
       " 'jeep': 127522.93577981651,\n",
       " 'kia': 112719.54674220963,\n",
       " 'lada': 85517.24137931035,\n",
       " 'lancia': 123125.0,\n",
       " 'land_rover': 118333.33333333333,\n",
       " 'mazda': 124933.42210386152,\n",
       " 'mercedes_benz': 131041.57782515991,\n",
       " 'mini': 89375.0,\n",
       " 'mitsubishi': 126904.76190476191,\n",
       " 'nissan': 118951.93591455274,\n",
       " 'opel': 129326.65553700613,\n",
       " 'peugeot': 127311.939268461,\n",
       " 'porsche': 97457.33788395904,\n",
       " 'renault': 128230.57432432432,\n",
       " 'rover': 136449.27536231885,\n",
       " 'saab': 144487.1794871795,\n",
       " 'seat': 122027.32240437159,\n",
       " 'skoda': 111074.16879795397,\n",
       " 'smart': 100489.20863309353,\n",
       " 'sonstige_autos': 87303.37078651685,\n",
       " 'subaru': 125327.10280373832,\n",
       " 'suzuki': 109290.65743944637,\n",
       " 'toyota': 116105.69105691057,\n",
       " 'trabant': 60064.93506493507,\n",
       " 'volkswagen': 128976.4338444066,\n",
       " 'volvo': 138557.26872246695}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_mean_mileage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "bmp = pd.Series(brand_mean_prices)\n",
    "bmm = pd.Series(brand_mean_mileage)\n",
    "bm = pd.DataFrame(bmp, columns=['mean_prices'])\n",
    "bm['mean_mileage'] = bmm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": 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>mean_prices</th>\n",
       "      <th>mean_mileage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>alfa_romeo</th>\n",
       "      <td>3955.449541</td>\n",
       "      <td>131437.308869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>8989.552891</td>\n",
       "      <td>129561.589093</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>8279.482259</td>\n",
       "      <td>132520.899127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chevrolet</th>\n",
       "      <td>6496.311828</td>\n",
       "      <td>99014.336918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chrysler</th>\n",
       "      <td>3286.055249</td>\n",
       "      <td>133149.171271</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>citroen</th>\n",
       "      <td>43097.888889</td>\n",
       "      <td>119834.054834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dacia</th>\n",
       "      <td>5920.382812</td>\n",
       "      <td>84218.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>daewoo</th>\n",
       "      <td>1049.102564</td>\n",
       "      <td>121346.153846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>daihatsu</th>\n",
       "      <td>1535.460317</td>\n",
       "      <td>115714.285714</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiat</th>\n",
       "      <td>12276.558230</td>\n",
       "      <td>116836.180124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>7140.062591</td>\n",
       "      <td>124058.243987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>honda</th>\n",
       "      <td>3928.276215</td>\n",
       "      <td>123631.713555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hyundai</th>\n",
       "      <td>5303.065979</td>\n",
       "      <td>106690.721649</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>jaguar</th>\n",
       "      <td>11176.197368</td>\n",
       "      <td>120921.052632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>jeep</th>\n",
       "      <td>11377.550459</td>\n",
       "      <td>127522.935780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kia</th>\n",
       "      <td>5724.385269</td>\n",
       "      <td>112719.546742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lada</th>\n",
       "      <td>2502.896552</td>\n",
       "      <td>85517.241379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lancia</th>\n",
       "      <td>3057.339286</td>\n",
       "      <td>123125.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>land_rover</th>\n",
       "      <td>18934.272727</td>\n",
       "      <td>118333.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mazda</th>\n",
       "      <td>3968.770972</td>\n",
       "      <td>124933.422104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>29704.757996</td>\n",
       "      <td>131041.577825</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mini</th>\n",
       "      <td>10392.393868</td>\n",
       "      <td>89375.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mitsubishi</th>\n",
       "      <td>3320.187970</td>\n",
       "      <td>126904.761905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nissan</th>\n",
       "      <td>4589.356475</td>\n",
       "      <td>118951.935915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>5143.339455</td>\n",
       "      <td>129326.655537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peugeot</th>\n",
       "      <td>3014.338164</td>\n",
       "      <td>127311.939268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>porsche</th>\n",
       "      <td>44553.467577</td>\n",
       "      <td>97457.337884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>renault</th>\n",
       "      <td>2364.367821</td>\n",
       "      <td>128230.574324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rover</th>\n",
       "      <td>1494.521739</td>\n",
       "      <td>136449.275362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>saab</th>\n",
       "      <td>3192.269231</td>\n",
       "      <td>144487.179487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>seat</th>\n",
       "      <td>4259.546448</td>\n",
       "      <td>122027.322404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>skoda</th>\n",
       "      <td>6300.735294</td>\n",
       "      <td>111074.168798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>smart</th>\n",
       "      <td>3494.264748</td>\n",
       "      <td>100489.208633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sonstige_autos</th>\n",
       "      <td>39098.705993</td>\n",
       "      <td>87303.370787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>subaru</th>\n",
       "      <td>3702.140187</td>\n",
       "      <td>125327.102804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>suzuki</th>\n",
       "      <td>4028.591696</td>\n",
       "      <td>109290.657439</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>toyota</th>\n",
       "      <td>5101.678049</td>\n",
       "      <td>116105.691057</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trabant</th>\n",
       "      <td>1557.207792</td>\n",
       "      <td>60064.935065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>6417.041170</td>\n",
       "      <td>128976.433844</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volvo</th>\n",
       "      <td>4690.112335</td>\n",
       "      <td>138557.268722</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 mean_prices   mean_mileage\n",
       "alfa_romeo       3955.449541  131437.308869\n",
       "audi             8989.552891  129561.589093\n",
       "bmw              8279.482259  132520.899127\n",
       "chevrolet        6496.311828   99014.336918\n",
       "chrysler         3286.055249  133149.171271\n",
       "citroen         43097.888889  119834.054834\n",
       "dacia            5920.382812   84218.750000\n",
       "daewoo           1049.102564  121346.153846\n",
       "daihatsu         1535.460317  115714.285714\n",
       "fiat            12276.558230  116836.180124\n",
       "ford             7140.062591  124058.243987\n",
       "honda            3928.276215  123631.713555\n",
       "hyundai          5303.065979  106690.721649\n",
       "jaguar          11176.197368  120921.052632\n",
       "jeep            11377.550459  127522.935780\n",
       "kia              5724.385269  112719.546742\n",
       "lada             2502.896552   85517.241379\n",
       "lancia           3057.339286  123125.000000\n",
       "land_rover      18934.272727  118333.333333\n",
       "mazda            3968.770972  124933.422104\n",
       "mercedes_benz   29704.757996  131041.577825\n",
       "mini            10392.393868   89375.000000\n",
       "mitsubishi       3320.187970  126904.761905\n",
       "nissan           4589.356475  118951.935915\n",
       "opel             5143.339455  129326.655537\n",
       "peugeot          3014.338164  127311.939268\n",
       "porsche         44553.467577   97457.337884\n",
       "renault          2364.367821  128230.574324\n",
       "rover            1494.521739  136449.275362\n",
       "saab             3192.269231  144487.179487\n",
       "seat             4259.546448  122027.322404\n",
       "skoda            6300.735294  111074.168798\n",
       "smart            3494.264748  100489.208633\n",
       "sonstige_autos  39098.705993   87303.370787\n",
       "subaru           3702.140187  125327.102804\n",
       "suzuki           4028.591696  109290.657439\n",
       "toyota           5101.678049  116105.691057\n",
       "trabant          1557.207792   60064.935065\n",
       "volkswagen       6417.041170  128976.433844\n",
       "volvo            4690.112335  138557.268722"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next Steps"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this guided project, we practiced applying a variety of pandas methods to explore and understand a data set on car listings. Here are some next steps for you to consider:\n",
    "- Data cleaning next steps:\n",
    "  - Identify categorical data that uses german words, translate them and map the values to their english counterparts\n",
    "  - Convert the dates to be uniform numeric data, so **\"2016-03-21\"** becomes the integer **20160321**.\n",
    "  - See if there are particular keywords in the name column that you can extract as new columns\n",
    "- Analysis next steps:\n",
    "  - Find the most common brand/model combinations\n",
    "  - Split the **odometer_km** into groups, and use aggregation to see if average prices follows any patterns based on the milage.\n",
    "  - How much cheaper are cars with damage than their non-damaged counterparts?"
   ]
  }
 ],
 "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}