German_Ebay_Car_Sales_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "<h1>Exploring and Cleaning EBAY Car Sales Data </h1>\n",
    "<p> The aim of this project is to use Python 3's Pandas library to clean EBAY car sales data.  The dataset is originally found here: https://www.kaggle.com/orgesleka/used-cars-database/data</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "autos= pd.read_csv(\"autos.csv\", encoding= \"Latin-1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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/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": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.info()\n",
    "autos.head()\n",
    "autos.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Changing the column names:</h3>\n",
    "Some of the column names need to be modified, I created a function to do this and loop through the column names to change them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>ab_test</th>\n",
       "      <th>vehicle_type</th>\n",
       "      <th>registration_year</th>\n",
       "      <th>gearbox</th>\n",
       "      <th>power_ps</th>\n",
       "      <th>model</th>\n",
       "      <th>odometer</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>fuel_type</th>\n",
       "      <th>brand</th>\n",
       "      <th>unrepaired_damage</th>\n",
       "      <th>ad_created</th>\n",
       "      <th>num_photos</th>\n",
       "      <th>postal_code</th>\n",
       "      <th>last_seen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-03-26 17:47:46</td>\n",
       "      <td>Peugeot_807_160_NAVTECH_ON_BOARD</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$5,000</td>\n",
       "      <td>control</td>\n",
       "      <td>bus</td>\n",
       "      <td>2004</td>\n",
       "      <td>manuell</td>\n",
       "      <td>158</td>\n",
       "      <td>andere</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>3</td>\n",
       "      <td>lpg</td>\n",
       "      <td>peugeot</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>79588</td>\n",
       "      <td>2016-04-06 06:45:54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-04-04 13:38:56</td>\n",
       "      <td>BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,500</td>\n",
       "      <td>control</td>\n",
       "      <td>limousine</td>\n",
       "      <td>1997</td>\n",
       "      <td>automatik</td>\n",
       "      <td>286</td>\n",
       "      <td>7er</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>bmw</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-04 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>71034</td>\n",
       "      <td>2016-04-06 14:45:08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-03-26 18:57:24</td>\n",
       "      <td>Volkswagen_Golf_1.6_United</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$8,990</td>\n",
       "      <td>test</td>\n",
       "      <td>limousine</td>\n",
       "      <td>2009</td>\n",
       "      <td>manuell</td>\n",
       "      <td>102</td>\n",
       "      <td>golf</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>volkswagen</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-26 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>35394</td>\n",
       "      <td>2016-04-06 20:15:37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03-12 16:58:10</td>\n",
       "      <td>Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$4,350</td>\n",
       "      <td>control</td>\n",
       "      <td>kleinwagen</td>\n",
       "      <td>2007</td>\n",
       "      <td>automatik</td>\n",
       "      <td>71</td>\n",
       "      <td>fortwo</td>\n",
       "      <td>70,000km</td>\n",
       "      <td>6</td>\n",
       "      <td>benzin</td>\n",
       "      <td>smart</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-03-12 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>33729</td>\n",
       "      <td>2016-03-15 03:16:28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "      <td>Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...</td>\n",
       "      <td>privat</td>\n",
       "      <td>Angebot</td>\n",
       "      <td>$1,350</td>\n",
       "      <td>test</td>\n",
       "      <td>kombi</td>\n",
       "      <td>2003</td>\n",
       "      <td>manuell</td>\n",
       "      <td>0</td>\n",
       "      <td>focus</td>\n",
       "      <td>150,000km</td>\n",
       "      <td>7</td>\n",
       "      <td>benzin</td>\n",
       "      <td>ford</td>\n",
       "      <td>nein</td>\n",
       "      <td>2016-04-01 00:00:00</td>\n",
       "      <td>0</td>\n",
       "      <td>39218</td>\n",
       "      <td>2016-04-01 14:38:50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          date_crawled                                               name  \\\n",
       "0  2016-03-26 17:47:46                   Peugeot_807_160_NAVTECH_ON_BOARD   \n",
       "1  2016-04-04 13:38:56         BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik   \n",
       "2  2016-03-26 18:57:24                         Volkswagen_Golf_1.6_United   \n",
       "3  2016-03-12 16:58:10  Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...   \n",
       "4  2016-04-01 14:38:50  Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...   \n",
       "\n",
       "   seller offer_type   price  ab_test vehicle_type  registration_year  \\\n",
       "0  privat    Angebot  $5,000  control          bus               2004   \n",
       "1  privat    Angebot  $8,500  control    limousine               1997   \n",
       "2  privat    Angebot  $8,990     test    limousine               2009   \n",
       "3  privat    Angebot  $4,350  control   kleinwagen               2007   \n",
       "4  privat    Angebot  $1,350     test        kombi               2003   \n",
       "\n",
       "     gearbox  power_ps   model   odometer  registration_month fuel_type  \\\n",
       "0    manuell       158  andere  150,000km                   3       lpg   \n",
       "1  automatik       286     7er  150,000km                   6    benzin   \n",
       "2    manuell       102    golf   70,000km                   7    benzin   \n",
       "3  automatik        71  fortwo   70,000km                   6    benzin   \n",
       "4    manuell         0   focus  150,000km                   7    benzin   \n",
       "\n",
       "        brand unrepaired_damage           ad_created  num_photos  postal_code  \\\n",
       "0     peugeot              nein  2016-03-26 00:00:00           0        79588   \n",
       "1         bmw              nein  2016-04-04 00:00:00           0        71034   \n",
       "2  volkswagen              nein  2016-03-26 00:00:00           0        35394   \n",
       "3       smart              nein  2016-03-12 00:00:00           0        33729   \n",
       "4        ford              nein  2016-04-01 00:00:00           0        39218   \n",
       "\n",
       "             last_seen  \n",
       "0  2016-04-06 06:45:54  \n",
       "1  2016-04-06 14:45:08  \n",
       "2  2016-04-06 20:15:37  \n",
       "3  2016-03-15 03:16:28  \n",
       "4  2016-04-01 14:38:50  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.columns = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',\n",
    "       'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model',\n",
    "       'odometer', 'registration_month', 'fuel_type', 'brand',\n",
    "       'unrepaired_damage', 'ad_created', 'num_photos', 'postal_code',\n",
    "       'last_seen']\n",
    "\n",
    "autos.head()\n",
    "autos.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I will look for text columns where almost all of the values are the same. I will also look for columns where numeric values are stored as text.  price and odometer_km has text stored with it's numeric value, so I will remove the text and convert it to a int."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "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>registration_year</th>\n",
       "      <th>power_ps</th>\n",
       "      <th>registration_month</th>\n",
       "      <th>num_photos</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  num_photos  \\\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": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The \"odometer_km\" and \"price\" columns are not being counted as numeric data.  I will clean these columns below to ensure they are being counted as numeric data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos[\"odometer\"]= (autos[\"odometer\"]\n",
    "                        .str.replace(\"km\", \"\")\n",
    "                        .str.replace(\",\", \"\")\n",
    "                        .astype(int)\n",
    "                      )\n",
    "\n",
    "autos[\"price\"] = (autos[\"price\"]\n",
    "                          .str.replace(\"$\",\"\")\n",
    "                          .str.replace(\",\",\"\")\n",
    "                          .astype(int)\n",
    "                          )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The \"seller\", \"offer_type\", and \"nofpictures\" columns do not have useful data to analyze.  I will remove these columns. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos = autos.drop([\"num_photos\", \"seller\", \"offer_type\"], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Removing incorrect data</h3>\n",
    "\n",
    "I will examine data in the odometer_km and price columns and will remove data that doesn't fit with the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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, dtype: float64"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"odometer\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "150000    32424\n",
       "125000     5170\n",
       "100000     2169\n",
       "90000      1757\n",
       "80000      1436\n",
       "70000      1230\n",
       "60000      1164\n",
       "50000      1027\n",
       "5000        967\n",
       "40000       819\n",
       "30000       789\n",
       "20000       784\n",
       "10000       264\n",
       "Name: odometer, dtype: int64"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"odometer\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There look to be more high mileage vehicles than low mileage vehiles/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     1421\n",
       "1      156\n",
       "2        3\n",
       "3        1\n",
       "5        2\n",
       "8        1\n",
       "9        1\n",
       "10       7\n",
       "11       2\n",
       "12       3\n",
       "13       2\n",
       "14       1\n",
       "15       2\n",
       "17       3\n",
       "18       1\n",
       "20       4\n",
       "25       5\n",
       "29       1\n",
       "30       7\n",
       "35       1\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"price\"].value_counts().sort_index(ascending=True).head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "99999999    1\n",
       "27322222    1\n",
       "12345678    3\n",
       "11111111    2\n",
       "10000000    1\n",
       "3890000     1\n",
       "1300000     1\n",
       "1234566     1\n",
       "999999      2\n",
       "999990      1\n",
       "350000      1\n",
       "345000      1\n",
       "299000      1\n",
       "295000      1\n",
       "265000      1\n",
       "259000      1\n",
       "250000      1\n",
       "220000      1\n",
       "198000      1\n",
       "197000      1\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"price\"].value_counts().sort_index(ascending=False).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are very few data points above 350K, and they could be skewing the mean upward. I will remove these points and keep the distribution at the bottom."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "autos = autos[autos[\"price\"].between(1,351000)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Working with dates in the dataset </h3>\n",
    "The date_crawled, ad_created, and last_seen columns all have dates.  I will examine these dates to see their distributions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "autos['date_crawled']= autos['date_crawled'].str[:10]\n",
    "autos['ad_created']= autos['ad_created'].str[:10]\n",
    "autos['last_seen']= autos['last_seen'].str[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-04-03    0.038608\n",
       "2016-03-20    0.037887\n",
       "2016-03-21    0.037373\n",
       "2016-03-12    0.036920\n",
       "2016-03-14    0.036549\n",
       "2016-04-04    0.036487\n",
       "2016-03-07    0.036014\n",
       "2016-04-02    0.035478\n",
       "2016-03-28    0.034860\n",
       "2016-03-19    0.034778\n",
       "2016-03-15    0.034284\n",
       "2016-03-29    0.034099\n",
       "2016-04-01    0.033687\n",
       "2016-03-30    0.033687\n",
       "2016-03-08    0.033296\n",
       "2016-03-09    0.033090\n",
       "2016-03-22    0.032987\n",
       "2016-03-11    0.032575\n",
       "2016-03-23    0.032225\n",
       "2016-03-26    0.032204\n",
       "2016-03-10    0.032184\n",
       "2016-03-31    0.031834\n",
       "2016-03-17    0.031628\n",
       "2016-03-25    0.031607\n",
       "2016-03-27    0.031092\n",
       "2016-03-16    0.029610\n",
       "2016-03-24    0.029342\n",
       "2016-03-05    0.025327\n",
       "2016-03-13    0.015670\n",
       "2016-03-06    0.014043\n",
       "2016-04-05    0.013096\n",
       "2016-03-18    0.012911\n",
       "2016-04-06    0.003171\n",
       "2016-04-07    0.001400\n",
       "Name: date_crawled, dtype: float64"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['date_crawled'].sort_index().value_counts(normalize=True, dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The site was mostly crawled in March of 2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-04-03    0.038855\n",
       "2016-03-20    0.037949\n",
       "2016-03-21    0.037579\n",
       "2016-04-04    0.036858\n",
       "2016-03-12    0.036755\n",
       "2016-03-14    0.035190\n",
       "2016-04-02    0.035149\n",
       "2016-03-28    0.034984\n",
       "2016-03-07    0.034737\n",
       "2016-03-29    0.034037\n",
       "2016-03-15    0.034016\n",
       "2016-03-19    0.033687\n",
       "2016-04-01    0.033687\n",
       "2016-03-30    0.033501\n",
       "2016-03-08    0.033316\n",
       "2016-03-09    0.033151\n",
       "2016-03-11    0.032904\n",
       "2016-03-22    0.032801\n",
       "2016-03-26    0.032266\n",
       "2016-03-23    0.032060\n",
       "2016-03-10    0.031895\n",
       "2016-03-31    0.031875\n",
       "2016-03-25    0.031751\n",
       "2016-03-17    0.031278\n",
       "2016-03-27    0.030989\n",
       "2016-03-16    0.030125\n",
       "2016-03-24    0.029280\n",
       "2016-03-05    0.022897\n",
       "2016-03-13    0.017008\n",
       "2016-03-06    0.015320\n",
       "                ...   \n",
       "2016-01-10    0.000041\n",
       "2016-02-24    0.000041\n",
       "2016-02-26    0.000041\n",
       "2016-02-05    0.000041\n",
       "2016-02-02    0.000041\n",
       "2016-02-12    0.000041\n",
       "2016-02-18    0.000041\n",
       "2016-02-14    0.000041\n",
       "2016-02-20    0.000041\n",
       "2016-02-17    0.000021\n",
       "2015-11-10    0.000021\n",
       "2016-01-03    0.000021\n",
       "2016-02-09    0.000021\n",
       "2016-02-16    0.000021\n",
       "2015-12-05    0.000021\n",
       "2016-02-07    0.000021\n",
       "2016-01-22    0.000021\n",
       "2016-02-01    0.000021\n",
       "2016-02-22    0.000021\n",
       "2016-01-29    0.000021\n",
       "2015-09-09    0.000021\n",
       "2016-02-11    0.000021\n",
       "2016-01-14    0.000021\n",
       "2016-02-08    0.000021\n",
       "2016-01-07    0.000021\n",
       "2015-06-11    0.000021\n",
       "2015-12-30    0.000021\n",
       "2016-01-13    0.000021\n",
       "2015-08-10    0.000021\n",
       "2016-01-16    0.000021\n",
       "Name: ad_created, Length: 76, dtype: float64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['ad_created'].sort_index().value_counts(normalize=True, dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are no ad_created cells that have a large proportion of the data.  Many of the ads were created a month before the data was scraped."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2016-04-06    0.221806\n",
       "2016-04-07    0.131947\n",
       "2016-04-05    0.124761\n",
       "2016-03-17    0.028086\n",
       "2016-04-03    0.025203\n",
       "2016-04-02    0.024915\n",
       "2016-03-30    0.024771\n",
       "2016-04-04    0.024483\n",
       "2016-03-12    0.023783\n",
       "2016-03-31    0.023783\n",
       "2016-04-01    0.022794\n",
       "2016-03-29    0.022341\n",
       "2016-03-22    0.021373\n",
       "2016-03-28    0.020859\n",
       "2016-03-20    0.020653\n",
       "2016-03-21    0.020632\n",
       "2016-03-24    0.019767\n",
       "2016-03-25    0.019211\n",
       "2016-03-23    0.018532\n",
       "2016-03-26    0.016802\n",
       "2016-03-16    0.016452\n",
       "2016-03-15    0.015876\n",
       "2016-03-19    0.015834\n",
       "2016-03-27    0.015649\n",
       "2016-03-14    0.012602\n",
       "2016-03-11    0.012375\n",
       "2016-03-10    0.010666\n",
       "2016-03-09    0.009595\n",
       "2016-03-13    0.008895\n",
       "2016-03-08    0.007413\n",
       "2016-03-18    0.007351\n",
       "2016-03-07    0.005395\n",
       "2016-03-06    0.004324\n",
       "2016-03-05    0.001071\n",
       "Name: last_seen, dtype: float64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['last_seen'].sort_index().value_counts(normalize=True, dropna=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Most of the sites were seen in early April (4/6/2016 has about 30%). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    48565.000000\n",
       "mean      2004.755421\n",
       "std         88.643887\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": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos[\"registration_year\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The min and max for the registration_year columns do not make sense.  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Removing Incorrect Registration Year Data </h3>\n",
    "I will look into how many values have a registration_year below 1920 and after 2017 and will remove these data points."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.010130752599608772"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(~autos[\"registration_year\"].between(1910,2017)).sum() / autos.shape[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Only about 1% of the data is before 1910 and after 2017.  I will remove these points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2000    0.065650\n",
       "2005    0.061074\n",
       "1999    0.060263\n",
       "2004    0.056227\n",
       "2003    0.056144\n",
       "2006    0.055541\n",
       "2001    0.054833\n",
       "2002    0.051713\n",
       "1998    0.049154\n",
       "2007    0.047365\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos = autos[autos[\"registration_year\"].between(1910,2017)]\n",
    "autos[\"registration_year\"].value_counts(normalize=True).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data are distributed with about 5% per year between 1998 and 2007."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Exploring Auto Brand Data </h3>\n",
    "I will explore the auto brand data to see which brands I may wish to aggregate by."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "volkswagen        0.212593\n",
       "bmw               0.108814\n",
       "opel              0.108377\n",
       "mercedes_benz     0.095896\n",
       "audi              0.086098\n",
       "ford              0.069831\n",
       "renault           0.047677\n",
       "peugeot           0.029601\n",
       "fiat              0.025836\n",
       "seat              0.018618\n",
       "skoda             0.016163\n",
       "nissan            0.015310\n",
       "mazda             0.015248\n",
       "smart             0.014312\n",
       "citroen           0.014104\n",
       "toyota            0.012668\n",
       "hyundai           0.009985\n",
       "sonstige_autos    0.009631\n",
       "volvo             0.009090\n",
       "mini              0.008695\n",
       "Name: brand, dtype: float64"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "autos['brand'].value_counts(normalize=True).head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There were several car brands that represented less than 5% of the data. To aggregate the data, I will only aggregate the top car companies by price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['volkswagen', 'bmw', 'opel', 'mercedes_benz', 'audi', 'ford'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "brand_counts = autos[\"brand\"].value_counts(normalize=True)\n",
    "common_brands = brand_counts[brand_counts > .05].index\n",
    "print(common_brands)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'audi': 9239,\n",
       " 'bmw': 8284,\n",
       " 'ford': 3732,\n",
       " 'mercedes_benz': 8528,\n",
       " 'opel': 2952,\n",
       " 'volkswagen': 5351}"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_mean_prices = {}\n",
    "\n",
    "for brand in common_brands:\n",
    "    brand_only = autos[autos[\"brand\"] == brand]\n",
    "    mean_price = brand_only[\"price\"].mean()\n",
    "    brand_mean_prices[brand] = int(mean_price)\n",
    "\n",
    "brand_mean_prices"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The top brands look to be German brands.  Audis are the more expensive vehicles on German ebay."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Exploring Milage </h3>\n",
    "Now I will compare the mileage of the top brands to see if there is a correlation between mileage and price."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>9239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>8284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>3732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>8528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>2952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>5351</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_price\n",
       "audi                 9239\n",
       "bmw                  8284\n",
       "ford                 3732\n",
       "mercedes_benz        8528\n",
       "opel                 2952\n",
       "volkswagen           5351"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bmp_series = pd.Series(brand_mean_prices)\n",
    "pd.DataFrame(bmp_series, columns=[\"mean_price\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "brand_mean_mileage = {}\n",
    "\n",
    "for brand in common_brands:\n",
    "    brand_only = autos[autos[\"brand\"] == brand]\n",
    "    mean_mileage = brand_only[\"odometer\"].mean()\n",
    "    brand_mean_mileage[brand] = int(mean_mileage)\n",
    "\n",
    "mean_mileage = pd.Series(brand_mean_mileage).sort_values(ascending=False)\n",
    "mean_prices = pd.Series(brand_mean_prices).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "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_mileage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>132666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>130962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>129415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>129406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>128928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>124255</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_mileage\n",
       "bmw                  132666\n",
       "mercedes_benz        130962\n",
       "opel                 129415\n",
       "audi                 129406\n",
       "volkswagen           128928\n",
       "ford                 124255"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_info = pd.DataFrame(mean_mileage,columns=['mean_mileage'])\n",
    "brand_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "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_mileage</th>\n",
       "      <th>mean_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bmw</th>\n",
       "      <td>132666</td>\n",
       "      <td>8284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mercedes_benz</th>\n",
       "      <td>130962</td>\n",
       "      <td>8528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>opel</th>\n",
       "      <td>129415</td>\n",
       "      <td>2952</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>audi</th>\n",
       "      <td>129406</td>\n",
       "      <td>9239</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>volkswagen</th>\n",
       "      <td>128928</td>\n",
       "      <td>5351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ford</th>\n",
       "      <td>124255</td>\n",
       "      <td>3732</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               mean_mileage  mean_price\n",
       "bmw                  132666        8284\n",
       "mercedes_benz        130962        8528\n",
       "opel                 129415        2952\n",
       "audi                 129406        9239\n",
       "volkswagen           128928        5351\n",
       "ford                 124255        3732"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brand_info[\"mean_price\"] = mean_prices\n",
    "brand_info"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "The range of car mileages does not vary as much as the prices do by brand, instead all falling within 10% for the top brands. There is a slight trend to the more expensive vehicles having higher mileage, with the less expensive vehicles having lower mileage."
   ]
  }
 ],
 "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
}