Cars_Sales_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Exploring Ebay Car Sales Data\n",
    "\n",
    "Data set contains information about used cars scrapped from German eBay website. It is available under https://www.kaggle.com/orgesleka/used-cars-database/data.\n",
    "\n",
    "Goal of the project is to clean the data and make analysis from used car listings.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "autos = pd.read_csv('autos.csv',encoding='Latin-1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dateCrawled</th>\n",
       "      <th>name</th>\n",
       "      <th>seller</th>\n",
       "      <th>offerType</th>\n",
       "      <th>price</th>\n",
       "      <th>abtest</th>\n",
       "      <th>vehicleType</th>\n",
       "      <th>yearOfRegistration</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>powerPS</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>monthOfRegistration</th>\n",
       "      <th>fuelType</th>\n",
       "      <th>brand</th>\n",
       "      <th>notRepairedDamage</th>\n",
       "      <th>dateCreated</th>\n",
       "      <th>nrOfPictures</th>\n",
       "      <th>postalCode</th>\n",
       "      <th>lastSeen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$5,000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manuell</td>\n",
       "      <td>158</td>\n",
       "      <td>andere</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>3</td>\n",
       "      <td>lpg</td>\n",
       "      <td>peugeot</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>79588</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$4,350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-03-21 13:47:45</td>\n",
       "      <td>Chrysler_Grand_Voyager_2.8_CRD_Aut.Limited_Sto...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$7,900</td>\n",
       "      <td>test</td>\n",
       "      <td>bus</td>\n",
       "      <td>2006</td>\n",
       "      <td>automatik</td>\n",
       "      <td>150</td>\n",
       "      <td>voyager</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>4</td>\n",
       "      <td>diesel</td>\n",
       "      <td>chrysler</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-03-21 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>22962</td>\n",
       "      <td>2016-04-06 09:45:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-03-20 17:55:21</td>\n",
       "      <td>VW_Golf_III_GT_Special_Electronic_Green_Metall...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$300</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1995</td>\n",
       "      <td>manuell</td>\n",
       "      <td>90</td>\n",
       "      <td>golf</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>8</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-03-20 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>31535</td>\n",
       "      <td>2016-03-23 02:48:59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016-03-16 18:55:19</td>\n",
       "      <td>Golf_IV_1.9_TDI_90PS</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,990</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1998</td>\n",
       "      <td>manuell</td>\n",
       "      <td>90</td>\n",
       "      <td>golf</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>12</td>\n",
       "      <td>diesel</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-16 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>53474</td>\n",
       "      <td>2016-04-07 03:17:32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-03-22 16:51:34</td>\n",
       "      <td>Seat_Arosa</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$250</td>\n",
       "      <td>test</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>arosa</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>seat</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-22 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>7426</td>\n",
       "      <td>2016-03-26 18:18:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-03-16 13:47:02</td>\n",
       "      <td>Renault_Megane_Scenic_1.6e_RT_Klimaanlage</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$590</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>1997</td>\n",
       "      <td>manuell</td>\n",
       "      <td>90</td>\n",
       "      <td>megane</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>renault</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-16 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>15749</td>\n",
       "      <td>2016-04-06 10:46:35</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",
       "5  2016-03-21 13:47:45  Chrysler_Grand_Voyager_2.8_CRD_Aut.Limited_Sto...   \n",
       "6  2016-03-20 17:55:21  VW_Golf_III_GT_Special_Electronic_Green_Metall...   \n",
       "7  2016-03-16 18:55:19                               Golf_IV_1.9_TDI_90PS   \n",
       "8  2016-03-22 16:51:34                                         Seat_Arosa   \n",
       "9  2016-03-16 13:47:02          Renault_Megane_Scenic_1.6e_RT_Klimaanlage   \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",
       "5  privat   Angebot  $7,900     test         bus                2006   \n",
       "6  privat   Angebot    $300     test   limousine                1995   \n",
       "7  privat   Angebot  $1,990  control   limousine                1998   \n",
       "8  privat   Angebot    $250     test         NaN                2000   \n",
       "9  privat   Angebot    $590  control         bus                1997   \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",
       "5  automatik      150  voyager  150,000km                    4   diesel   \n",
       "6    manuell       90     golf  150,000km                    8   benzin   \n",
       "7    manuell       90     golf  150,000km                   12   diesel   \n",
       "8    manuell        0    arosa  150,000km                   10      NaN   \n",
       "9    manuell       90   megane  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",
       "5    chrysler               NaN  2016-03-21 00:00:00             0   \n",
       "6  volkswagen               NaN  2016-03-20 00:00:00             0   \n",
       "7  volkswagen              nein  2016-03-16 00:00:00             0   \n",
       "8        seat              nein  2016-03-22 00:00:00             0   \n",
       "9     renault              nein  2016-03-16 00:00:00             0   \n",
       "\n",
       "   postalCode             lastSeen  \n",
       "0       79588  2016-04-06 06:45:54  \n",
       "1       71034  2016-04-06 14:45:08  \n",
       "2       35394  2016-04-06 20:15:37  \n",
       "3       33729  2016-03-15 03:16:28  \n",
       "4       39218  2016-04-01 14:38:50  \n",
       "5       22962  2016-04-06 09:45:21  \n",
       "6       31535  2016-03-23 02:48:59  \n",
       "7       53474  2016-04-07 03:17:32  \n",
       "8        7426  2016-03-26 18:18:10  \n",
       "9       15749  2016-04-06 10:46:35  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.info()\n",
    "autos.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From above information we can notice that:\n",
    "- columns names use camelcase\n",
    "- car name column does not contain white spaces (it has been separated by underscore sign)\n",
    "- dataset contain German names\n",
    "- 5 columns are numerics, other are strings\n",
    "- 5 columns contains null values (vehicleType, gearbox, model, fuelType, notRepairedDamage)\n",
    "- price value is in string format (it contains $ sign)\n",
    "- there are three column with date\n",
    "- date format is in YYYY-MM-DD HH:mm:ss"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$4,350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled                                               name  \\\n",
       "0  2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1  2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2  2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3  2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4  2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "\n",
       "   seller offer_type   price   abtest vehicle_type  registration_year  \\\n",
       "0  privat    Angebot  $5,000  control          bus               2004   \n",
       "1  privat    Angebot  $8,500  control    limousine               1997   \n",
       "2  privat    Angebot  $8,990     test    limousine               2009   \n",
       "3  privat    Angebot  $4,350  control   kleinwagen               2007   \n",
       "4  privat    Angebot  $1,350     test        kombi               2003   \n",
       "\n",
       "     gearbox  power_ps   model   odometer  registration_month fuel_type  \\\n",
       "0    manuell       158  andere  150,000km                   3       lpg   \n",
       "1  automatik       286     7er  150,000km                   6    benzin   \n",
       "2    manuell       102    golf   70,000km                   7    benzin   \n",
       "3  automatik        71  fortwo   70,000km                   6    benzin   \n",
       "4    manuell         0   focus  150,000km                   7    benzin   \n",
       "\n",
       "        brand unrepaired_damage           ad_created  nr_of_pictures  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00               0   \n",
       "1         bmw              nein  2016-04-04 00:00:00               0   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00               0   \n",
       "3       smart              nein  2016-03-12 00:00:00               0   \n",
       "4        ford              nein  2016-04-01 00:00:00               0   \n",
       "\n",
       "   postal_code            last_seen  \n",
       "0        79588  2016-04-06 06:45:54  \n",
       "1        71034  2016-04-06 14:45:08  \n",
       "2        35394  2016-04-06 20:15:37  \n",
       "3        33729  2016-03-15 03:16:28  \n",
       "4        39218  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "def convert_to_snake(name):\n",
    "    s1 = re.sub('(.)([A-Z][a-z]+)', r'\\1_\\2', name)\n",
    "    return re.sub('([a-z0-9])([A-Z])', r'\\1_\\2', s1).lower()\n",
    "def clean_col(column_name):\n",
    "    column_name = column_name.replace(\"yearOfRegistration\",\"registration_year\")\n",
    "    column_name = column_name.replace(\"monthOfRegistration\",\"registration_month\")\n",
    "    column_name = column_name.replace(\"notRepairedDamage\",\"unrepaired_damage\")\n",
    "    column_name = column_name.replace(\"dateCreated\",\"ad_created\")\n",
    "    column_name = convert_to_snake(column_name)\n",
    "    return column_name\n",
    "\n",
    "cleaned_cols = [] \n",
    "for name in autos.columns:\n",
    "    cleaned_column = clean_col(name)\n",
    "    cleaned_cols.append(cleaned_column)\n",
    "\n",
    "autos.columns = cleaned_cols\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Due to Python prefer snakecase above code convert all the columns name from camelcase to snakecase. Also, some of the columns have to be more decriptive (ex. name dateCreate is unclear)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>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-30 17:37:35</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-30 17:37:35  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": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.describe(include='all')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Above statistics shows that:\n",
    "- Nr of pictures does not contain any useful information (all the values are zeros) so it can be dropped.\n",
    "- column registration_year contains some values which are not correct (like 1000.0 and 9999.0) so it is need to investigate some deeper.\n",
    "- column power_ps contains some values which are not correct (like 0.0) which need deeper investigation\n",
    "- price needs to be converted to float type. Some of the price values contains $ sign what needs to be cleaned\n",
    "- odometer rows needs to be converted to float type. Some of the price values contains km what needs to be cleaned."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['$5,000' '$8,500' '$8,990' ... '$385' '$22,200' '$16,995']\n",
      "['150,000km' '70,000km' '50,000km' '80,000km' '10,000km' '30,000km'\n",
      " '125,000km' '90,000km' '20,000km' '60,000km' '5,000km' '100,000km'\n",
      " '40,000km']\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"price\"].unique())\n",
    "print(autos[\"odometer\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos[\"price\"] = (autos[\"price\"]\n",
    "                  .str.replace(\"$\",\"\")\n",
    "                  .str.replace(\",\",\"\")\n",
    "                  .astype(int)\n",
    "                 )\n",
    "autos[\"odometer\"] = (autos[\"odometer\"]\n",
    "                  .str.replace(\"km\",\"\")\n",
    "                  .str.replace(\",\",\"\")\n",
    "                  .astype(int)\n",
    "                 )\n",
    "autos.rename({\"odometer\":\"odometer_km\"}, axis='columns',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "(2357,)\n",
      "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",
      "Name: price, dtype: int64 \n",
      "\n",
      "*ODOMETER all value with counts*\n",
      "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\n"
     ]
    }
   ],
   "source": [
    "top_prices_counts = autos[\"price\"].value_counts().sort_index().head(10)\n",
    "tail_prices_counts = autos[\"price\"].value_counts().sort_index(ascending=False).head(10)\n",
    "unique_prices = autos[\"price\"].unique().shape\n",
    "print('''\n",
    "{}\n",
    "{} \n",
    "'''.format(unique_prices,top_prices_counts,tail_prices_counts))\n",
    "print('''*ODOMETER all value with counts*\n",
    "{}'''\n",
    "      .format(autos[\"odometer_km\"].value_counts()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "According to statistics about price and odometer there is need to clean some prices which don't look not realistics. There are a lot of with price 0\\$. I left only cars which costs less than 7M\\$ and more than 100\\$ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count    4.823000e+04\n",
      "mean     6.125042e+03\n",
      "std      2.286169e+04\n",
      "min      1.000000e+02\n",
      "25%      1.250000e+03\n",
      "50%      3.000000e+03\n",
      "75%      7.499000e+03\n",
      "max      3.890000e+06\n",
      "Name: price, dtype: float64\n",
      "(2315,)\n"
     ]
    }
   ],
   "source": [
    "autos_price_clean = autos[autos[\"price\"].between(100,7000000)]\n",
    "print(autos_price_clean[\"price\"].describe())\n",
    "print(autos_price_clean[\"price\"].unique().shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Nearly 2000 rows have been deleted, there was 52 unique values which was between 100\\$ and 7M\\$ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2016-03-05    0.025358\n",
      "2016-03-06    0.014037\n",
      "2016-03-07    0.036056\n",
      "2016-03-08    0.033174\n",
      "2016-03-09    0.033009\n",
      "2016-03-10    0.032283\n",
      "2016-03-11    0.032594\n",
      "2016-03-12    0.036906\n",
      "2016-03-13    0.015675\n",
      "2016-03-14    0.036658\n",
      "2016-03-15    0.034315\n",
      "2016-03-16    0.029463\n",
      "2016-03-17    0.031516\n",
      "2016-03-18    0.012897\n",
      "2016-03-19    0.034729\n",
      "2016-03-20    0.037798\n",
      "2016-03-21    0.037217\n",
      "2016-03-22    0.032905\n",
      "2016-03-23    0.032283\n",
      "2016-03-24    0.029442\n",
      "2016-03-25    0.031495\n",
      "2016-03-26    0.032304\n",
      "2016-03-27    0.031122\n",
      "2016-03-28    0.034957\n",
      "2016-03-29    0.034128\n",
      "2016-03-30    0.033734\n",
      "2016-03-31    0.031847\n",
      "2016-04-01    0.033693\n",
      "2016-04-02    0.035600\n",
      "2016-04-03    0.038607\n",
      "2016-04-04    0.036575\n",
      "2016-04-05    0.013062\n",
      "2016-04-06    0.003172\n",
      "2016-04-07    0.001389\n",
      "Name: date_crawled, dtype: float64\n",
      "2015-06    0.000021\n",
      "2015-08    0.000021\n",
      "2015-09    0.000021\n",
      "2015-11    0.000021\n",
      "2015-12    0.000041\n",
      "2016-01    0.000249\n",
      "2016-02    0.001265\n",
      "2016-03    0.837321\n",
      "2016-04    0.161041\n",
      "Name: ad_created, dtype: float64\n",
      "2016-03-05    0.001078\n",
      "2016-03-06    0.004313\n",
      "2016-03-07    0.005432\n",
      "2016-03-08    0.007319\n",
      "2016-03-09    0.009579\n",
      "2016-03-10    0.010637\n",
      "2016-03-11    0.012399\n",
      "2016-03-12    0.023782\n",
      "2016-03-13    0.008874\n",
      "2016-03-14    0.012627\n",
      "2016-03-15    0.015861\n",
      "2016-03-16    0.016442\n",
      "2016-03-17    0.028095\n",
      "2016-03-18    0.007319\n",
      "2016-03-19    0.015758\n",
      "2016-03-20    0.020651\n",
      "2016-03-21    0.020547\n",
      "2016-03-22    0.021356\n",
      "2016-03-23    0.018578\n",
      "2016-03-24    0.019759\n",
      "2016-03-25    0.019096\n",
      "2016-03-26    0.016670\n",
      "2016-03-27    0.015550\n",
      "2016-03-28    0.020858\n",
      "2016-03-29    0.022310\n",
      "2016-03-30    0.024694\n",
      "2016-03-31    0.023823\n",
      "2016-04-01    0.022849\n",
      "2016-04-02    0.024902\n",
      "2016-04-03    0.025130\n",
      "2016-04-04    0.024528\n",
      "2016-04-05    0.125088\n",
      "2016-04-06    0.221957\n",
      "2016-04-07    0.132138\n",
      "Name: last_seen, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "date_crawled_part = autos_price_clean[\"date_crawled\"].str[:10]\n",
    "ad_created_part = autos_price_clean[\"ad_created\"].str[:7]\n",
    "last_seen_part = autos_price_clean[\"last_seen\"].str[:10]\n",
    "print(date_crawled_part.value_counts(normalize=True,dropna=False).sort_index())\n",
    "print(ad_created_part.value_counts(normalize=True,dropna=False).sort_index())\n",
    "print(last_seen_part.value_counts(normalize=True,dropna=False).sort_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The code above extract date part from column with date. Then it is printed all values with frequency of appearance from earliest date to latest.\n",
    "\n",
    "It is visible that last_seen date and date_crawled is in the same range (1 month period). Most ad_created is from march 2016 values. There is no null values.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    48230.000000\n",
       "mean      2004.729795\n",
       "std         87.892058\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": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos_price_clean[\"registration_year\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Description of registration_year shows that it contains values which are incorrect (1000 and 9999). It is needed to be replaced.\n",
    "\n",
    "The first car have been created in 1886. Data set have been created in 2016, so it is impossible that there is cars which have been registrated after 2016.So I assumed that registration year before 1900 and after 2016 is incorrect.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2000    0.066957\n",
      "2005    0.062794\n",
      "1999    0.062125\n",
      "2004    0.058221\n",
      "2003    0.058091\n",
      "2006    0.057574\n",
      "2001    0.056711\n",
      "2002    0.053432\n",
      "1998    0.050477\n",
      "2007    0.049031\n",
      "2008    0.047672\n",
      "2009    0.044890\n",
      "1997    0.041525\n",
      "2011    0.034902\n",
      "2010    0.034234\n",
      "1996    0.029229\n",
      "2012    0.028215\n",
      "2016    0.025929\n",
      "1995    0.025735\n",
      "2013    0.017279\n",
      "2014    0.014280\n",
      "1994    0.013504\n",
      "1993    0.009060\n",
      "2015    0.008197\n",
      "1992    0.007938\n",
      "1991    0.007291\n",
      "1990    0.007162\n",
      "1989    0.003689\n",
      "1988    0.002869\n",
      "1985    0.002028\n",
      "          ...   \n",
      "1966    0.000475\n",
      "1976    0.000453\n",
      "1969    0.000410\n",
      "1975    0.000388\n",
      "1965    0.000367\n",
      "1964    0.000259\n",
      "1963    0.000173\n",
      "1961    0.000129\n",
      "1959    0.000129\n",
      "1962    0.000086\n",
      "1956    0.000086\n",
      "1937    0.000086\n",
      "1958    0.000086\n",
      "1955    0.000043\n",
      "1954    0.000043\n",
      "1957    0.000043\n",
      "1951    0.000043\n",
      "1910    0.000043\n",
      "1934    0.000043\n",
      "1941    0.000043\n",
      "1927    0.000022\n",
      "1929    0.000022\n",
      "1950    0.000022\n",
      "1931    0.000022\n",
      "1948    0.000022\n",
      "1938    0.000022\n",
      "1939    0.000022\n",
      "1953    0.000022\n",
      "1943    0.000022\n",
      "1952    0.000022\n",
      "Name: registration_year, Length: 78, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "autos_cleaned_registration = (\n",
    "    autos_price_clean[autos_price_clean[\"registration_year\"]\n",
    "                     .between(1900,2016)])\n",
    "print(autos_cleaned_registration[\"registration_year\"]\n",
    "      .value_counts(normalize=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "10 most often registered years are from 1998 to 2007. It is about 50% of all the cars. Registration date before 1980  is very rare."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "volkswagen        0.211420\n",
      "bmw               0.110186\n",
      "opel              0.107231\n",
      "mercedes_benz     0.096639\n",
      "audi              0.086760\n",
      "ford              0.069848\n",
      "renault           0.047068\n",
      "peugeot           0.029855\n",
      "fiat              0.025605\n",
      "seat              0.018249\n",
      "skoda             0.016416\n",
      "nissan            0.015337\n",
      "mazda             0.015229\n",
      "smart             0.014194\n",
      "citroen           0.014043\n",
      "toyota            0.012792\n",
      "hyundai           0.010009\n",
      "sonstige_autos    0.009578\n",
      "volvo             0.009125\n",
      "mini              0.008801\n",
      "mitsubishi        0.008176\n",
      "honda             0.007874\n",
      "kia               0.007075\n",
      "alfa_romeo        0.006666\n",
      "porsche           0.006018\n",
      "suzuki            0.005932\n",
      "chevrolet         0.005673\n",
      "chrysler          0.003516\n",
      "dacia             0.002653\n",
      "daihatsu          0.002502\n",
      "jeep              0.002287\n",
      "subaru            0.002114\n",
      "land_rover        0.002114\n",
      "saab              0.001661\n",
      "jaguar            0.001532\n",
      "daewoo            0.001488\n",
      "trabant           0.001359\n",
      "rover             0.001337\n",
      "lancia            0.001057\n",
      "lada              0.000582\n",
      "Name: brand, dtype: float64 (40,)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "brand_distribution = autos_cleaned_registration[\"brand\"].value_counts(normalize=True)\n",
    "print(brand_distribution,brand_distribution.shape)\n",
    "brands_over_1_percent = brand_distribution[brand_distribution>0.02].index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is 40 different brands. Let's agregate some of the most popular ones, for example which have over 2% of the total values. It is good to see what are the brand average price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'fiat': 2836.8736310025274, 'ford': 4086.93421865349, 'audi': 9380.718547986076, 'peugeot': 3113.860549132948, 'opel': 3005.4960772480385, 'volkswagen': 5639.900316294255, 'bmw': 8621.729052466719, 'renault': 2496.070577451879, 'mercedes_benz': 8672.654241071428}\n"
     ]
    }
   ],
   "source": [
    "brand_mean_prices = {}\n",
    "for brand in brands_over_1_percent:\n",
    "    brand_mean_price = autos_cleaned_registration.loc[\n",
    "        autos_cleaned_registration[\"brand\"]==brand,\"price\"].mean(axis=0)\n",
    "    brand_mean_prices[brand] = brand_mean_price\n",
    "print(brand_mean_prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The most expensive car from listed above is audi, and the cheapest is fiat and renault. The top-3 most expensive cars are from Germany"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'fiat': 116950.29486099411, 'ford': 124277.33168622607, 'audi': 129245.40029835903, 'peugeot': 127127.8901734104, 'opel': 129384.42969221485, 'volkswagen': 128804.2036526885, 'bmw': 132698.7079091621, 'renault': 128281.3932172319, 'mercedes_benz': 131025.66964285714}\n",
      "{'fiat': 2003.267902274642, 'ford': 2002.7906114885732, 'audi': 2004.1718050721033, 'peugeot': 2003.7796242774566, 'opel': 2002.26554013277, 'volkswagen': 2002.4793388429753, 'bmw': 2003.0432654659357, 'renault': 2002.9106324472962, 'mercedes_benz': 2002.052232142857}\n"
     ]
    }
   ],
   "source": [
    "brand_mean_mileages = {}\n",
    "for brand in brands_over_1_percent:\n",
    "    brand_mean_mileage = autos_cleaned_registration.loc[\n",
    "        autos_cleaned_registration[\"brand\"]==brand,\"odometer_km\"].mean(axis=0)\n",
    "    brand_mean_mileages[brand] = brand_mean_mileage\n",
    "print(brand_mean_mileages)\n",
    "brand_mean_registrations = {}\n",
    "for brand in brands_over_1_percent:\n",
    "    brand_mean_registration = autos_cleaned_registration.loc[\n",
    "        autos_cleaned_registration[\"brand\"]==brand,\"registration_year\"].mean(axis=0)\n",
    "    brand_mean_registrations[brand] = brand_mean_registration\n",
    "print(brand_mean_registrations)"
   ]
  },
  {
   "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>brand_mean_price</th>\n",
       "      <th>brand_mean_mileages</th>\n",
       "      <th>brand_registration_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>9380.718548</td>\n",
       "      <td>129245.400298</td>\n",
       "      <td>2004.171805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>8621.729052</td>\n",
       "      <td>132698.707909</td>\n",
       "      <td>2003.043265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fiat</th>\n",
       "      <td>2836.873631</td>\n",
       "      <td>116950.294861</td>\n",
       "      <td>2003.267902</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>4086.934219</td>\n",
       "      <td>124277.331686</td>\n",
       "      <td>2002.790611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>8672.654241</td>\n",
       "      <td>131025.669643</td>\n",
       "      <td>2002.052232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>3005.496077</td>\n",
       "      <td>129384.429692</td>\n",
       "      <td>2002.265540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>peugeot</th>\n",
       "      <td>3113.860549</td>\n",
       "      <td>127127.890173</td>\n",
       "      <td>2003.779624</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>renault</th>\n",
       "      <td>2496.070577</td>\n",
       "      <td>128281.393217</td>\n",
       "      <td>2002.910632</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>5639.900316</td>\n",
       "      <td>128804.203653</td>\n",
       "      <td>2002.479339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               brand_mean_price  brand_mean_mileages  brand_registration_year\n",
       "audi                9380.718548        129245.400298              2004.171805\n",
       "bmw                 8621.729052        132698.707909              2003.043265\n",
       "fiat                2836.873631        116950.294861              2003.267902\n",
       "ford                4086.934219        124277.331686              2002.790611\n",
       "mercedes_benz       8672.654241        131025.669643              2002.052232\n",
       "opel                3005.496077        129384.429692              2002.265540\n",
       "peugeot             3113.860549        127127.890173              2003.779624\n",
       "renault             2496.070577        128281.393217              2002.910632\n",
       "volkswagen          5639.900316        128804.203653              2002.479339"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bmp_series = pd.Series(brand_mean_prices)\n",
    "bmm_series = pd.Series(brand_mean_mileages)\n",
    "bmr_series = pd.Series(brand_mean_registrations)\n",
    "df_agg_brand = pd.DataFrame(bmp_series,columns=[\"brand_mean_price\"])\n",
    "df_agg_brand[\"brand_mean_mileages\"] = bmm_series\n",
    "df_agg_brand[\"brand_registration_year\"] = bmr_series\n",
    "df_agg_brand"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There is not visible correlation between brand price and mlieages. Fiat has mean mileage the smallest and the price is the one of the cheapest. Renault is the cheapest one and the mileage is similar as volkswagen which is two times more expensive.\n",
    "\n",
    "It is visible that avearage registration year is latest for audi. Difference is about one year in comparision to others cars. However, mercedes_benz mean is the oldest and the price is one of the highest.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gasoline\n",
      "diesel\n",
      "lpg\n",
      "cng\n",
      "hybrid\n",
      "electric\n",
      "others\n",
      "{'hybrid': 14346.027027027027, 'diesel': 8682.989082346225, 'others': 3185.6428571428573, 'gasoline': 5188.389479988726, 'electric': 26089.444444444445, 'lpg': 4334.817901234568, 'cng': 4930.785714285715}\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/dataquest/system/env/python3/lib/python3.4/site-packages/pandas/core/generic.py:4619: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    }
   ],
   "source": [
    "ger_eng_fuel_type = {\n",
    "    \"benzin\":\"gasoline\",\"diesel\":\"diesel\",\"lpg\":\"lpg\",\n",
    "    \"cng\":\"cng\",\"hybrid\":\"hybrid\",\"elektro\":\"electric\",\n",
    "    \"andere\":\"others\"\n",
    "}\n",
    "autos_cleaned_registration[\"fuel_type\"].replace(ger_eng_fuel_type,inplace=True)\n",
    "\n",
    "fuel_types = autos_cleaned_registration[\"fuel_type\"].value_counts(normalize=True).index\n",
    "fuel_types_mean_prices = {}\n",
    "\n",
    "for fuel_type in fuel_types:\n",
    "    print(fuel_type)\n",
    "    fuel_types_mean_price = autos_cleaned_registration.loc[\n",
    "        autos_cleaned_registration[\"fuel_type\"]==fuel_type,\"price\"].mean(axis=0)\n",
    "    fuel_types_mean_prices[fuel_type] = fuel_types_mean_price\n",
    "print(fuel_types_mean_prices)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Cars with the lpg are the cheapest one. The most expensive are hybrid and electric cars. Gasoline cars are twice cheaper than diesel cars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'fiat': gasoline    864\n",
      "diesel      185\n",
      "cng          13\n",
      "lpg          12\n",
      "Name: fuel_type, dtype: int64, 'ford': gasoline    2037\n",
      "diesel       893\n",
      "lpg           28\n",
      "others         1\n",
      "Name: fuel_type, dtype: int64, 'audi': gasoline    1928\n",
      "diesel      1847\n",
      "lpg           49\n",
      "hybrid         2\n",
      "cng            1\n",
      "Name: fuel_type, dtype: int64, 'peugeot': gasoline    964\n",
      "diesel      286\n",
      "lpg          17\n",
      "electric      1\n",
      "Name: fuel_type, dtype: int64, 'opel': gasoline    3548\n",
      "diesel       897\n",
      "lpg           70\n",
      "cng           11\n",
      "others         1\n",
      "hybrid         1\n",
      "Name: fuel_type, dtype: int64, 'volkswagen': gasoline    5456\n",
      "diesel      3529\n",
      "lpg           80\n",
      "cng           33\n",
      "hybrid         1\n",
      "Name: fuel_type, dtype: int64, 'bmw': gasoline    2837\n",
      "diesel      1890\n",
      "lpg           92\n",
      "cng            3\n",
      "electric       2\n",
      "hybrid         1\n",
      "Name: fuel_type, dtype: int64, 'renault': gasoline    1562\n",
      "diesel       419\n",
      "lpg           16\n",
      "electric       5\n",
      "hybrid         1\n",
      "cng            1\n",
      "others         1\n",
      "Name: fuel_type, dtype: int64, 'mercedes_benz': gasoline    2320\n",
      "diesel      1861\n",
      "lpg           91\n",
      "others         2\n",
      "hybrid         1\n",
      "Name: fuel_type, dtype: int64}\n"
     ]
    }
   ],
   "source": [
    "fuel_types_in_brand = {}\n",
    "for brand in brands_over_1_percent:\n",
    "    idx_brand_fuel_type = ((autos_cleaned_registration[\"brand\"]==brand))\n",
    "    nr_ft_in_brand = autos_cleaned_registration.loc[idx_brand_fuel_type,\"fuel_type\"].value_counts()\n",
    "    fuel_types_in_brand[brand] = nr_ft_in_brand\n",
    "print(fuel_types_in_brand)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Regardless of brand most of the cars use gasoline. There is only few examples of cars which are hybrid or electric. It is noticable that there is higher ratio diesel to gasoline in audi and mercedes_benz brands. If we look at relation fuel type to  price the diesel cars are twice more expensive than gasoline."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'fiat': 'punto', 'ford': 'focus', 'audi': 'a4', 'peugeot': '2_reihe', 'opel': 'corsa', 'volkswagen': 'golf', 'bmw': '3er', 'renault': 'twingo', 'mercedes_benz': 'c_klasse'}\n"
     ]
    }
   ],
   "source": [
    "most_common_model = ()\n",
    "brand_most_common_model = {}\n",
    "for brand in brands_over_1_percent:\n",
    "    model_dist = (autos_cleaned_registration.loc[\n",
    "        autos_cleaned_registration[\"brand\"] == brand,\"model\"]\n",
    "                           .value_counts())\n",
    "    if model_dist.shape[0] > 0:    \n",
    "        brand_most_common_model[brand] = model_dist.index[0]\n",
    "    #if most_common_model_brand  > most_common_model_brand.values:\n",
    "print(brand_most_common_model)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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
}