Exploring eBay Car Sales Data.ipynb

{
 "metadata": {
  "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.9.2-final"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "python3",
   "display_name": "Python 3.9.2 64-bit",
   "metadata": {
    "interpreter": {
     "hash": "42ffdba7f0de544957ef69efdb450b60517fb3f2f7c6065358c1f9d2622fefe6"
    }
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "source": [
    "# Exploring eBay Car Sales Data\n",
    "\n",
    "This project is mainly for practicing cleaning data.\n",
    "\n",
    "The dataset used in the project is a classifieds section of eBay Germany. The dataset is available to download <a href=\"https://data.world/data-society/used-cars-data\">here</a>. As this is from eBay Germany, the data is in German with English header. The data dictionary are as follows:\n",
    "\n",
    "| column name | description |\n",
    "| --- | --- |\n",
    "| dateCrawled | When this ads was first crawled. All field-values are taken from this date. |\n",
    "| name | Name of the car/ads |\n",
    "| seller | Type of seller: private or commercial (a dealer) |\n",
    "| offerType | The type of listing: offer or request |\n",
    "| price | The price on the ads to sell the car |\n",
    "| abtest | Whether the listing is a test or control group in the A/B test |\n",
    "| vehicleType | Type of vehicle |\n",
    "| yearOfRegistration | The year in which the car was first registered |\n",
    "| gearbox | Type of transmission: manual or automatic|\n",
    "| powerPS | The power of the car in PS |\n",
    "| model | The car model name |\n",
    "| kilometer | How many kilometers the car has driven |\n",
    "| monthOfRegistration | The month in which the car was first registered |\n",
    "| fuelType | Type of fuel the car uses |\n",
    "| brand | The brand of the car |\n",
    "| notRepairedDamage | If the car has a damage which is not yet repaired |\n",
    "| dateCreated | The date on which the eBay listing was created |\n",
    "| nrOfPictures | The number of pictures in the ad |\n",
    "| postalCode | The postal code for the location of the vehicle |\n",
    "| lastSeenOnline | When the crawler saw this ad last online |\n",
    "\n"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "autos = pd.read_csv(\"autos.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 371528 entries, 0 to 371527\nData columns (total 20 columns):\n #   Column               Non-Null Count   Dtype \n---  ------               --------------   ----- \n 0   dateCrawled          371528 non-null  object\n 1   name                 371528 non-null  object\n 2   seller               371528 non-null  object\n 3   offerType            371528 non-null  object\n 4   price                371528 non-null  int64 \n 5   abtest               371528 non-null  object\n 6   vehicleType          333659 non-null  object\n 7   yearOfRegistration   371528 non-null  int64 \n 8   gearbox              351319 non-null  object\n 9   powerPS              371528 non-null  int64 \n 10  model                351044 non-null  object\n 11  kilometer            371528 non-null  int64 \n 12  monthOfRegistration  371528 non-null  int64 \n 13  fuelType             338142 non-null  object\n 14  brand                371528 non-null  object\n 15  notRepairedDamage    299468 non-null  object\n 16  dateCreated          371528 non-null  object\n 17  nrOfPictures         371528 non-null  int64 \n 18  postalCode           371528 non-null  int64 \n 19  lastSeen             371528 non-null  object\ndtypes: int64(7), object(13)\nmemory usage: 56.7+ MB\n"
     ]
    }
   ],
   "source": [
    "autos.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "           dateCrawled                            name  seller offerType  \\\n0  2016-03-24 11:52:17                      Golf_3_1.6  privat   Angebot   \n1  2016-03-24 10:58:45            A5_Sportback_2.7_Tdi  privat   Angebot   \n2  2016-03-14 12:52:21  Jeep_Grand_Cherokee_\"Overland\"  privat   Angebot   \n3  2016-03-17 16:54:04              GOLF_4_1_4__3T�RER  privat   Angebot   \n4  2016-03-31 17:25:20  Skoda_Fabia_1.4_TDI_PD_Classic  privat   Angebot   \n\n   price abtest vehicleType  yearOfRegistration    gearbox  powerPS  model  \\\n0    480   test         NaN                1993    manuell        0   golf   \n1  18300   test       coupe                2011    manuell      190    NaN   \n2   9800   test         suv                2004  automatik      163  grand   \n3   1500   test  kleinwagen                2001    manuell       75   golf   \n4   3600   test  kleinwagen                2008    manuell       69  fabia   \n\n   kilometer  monthOfRegistration fuelType       brand notRepairedDamage  \\\n0     150000                    0   benzin  volkswagen               NaN   \n1     125000                    5   diesel        audi                ja   \n2     125000                    8   diesel        jeep               NaN   \n3     150000                    6   benzin  volkswagen              nein   \n4      90000                    7   diesel       skoda              nein   \n\n           dateCreated  nrOfPictures  postalCode             lastSeen  \n0  2016-03-24 00:00:00             0       70435  2016-04-07 03:16:57  \n1  2016-03-24 00:00:00             0       66954  2016-04-07 01:46:50  \n2  2016-03-14 00:00:00             0       90480  2016-04-05 12:47:46  \n3  2016-03-17 00:00:00             0       91074  2016-03-17 17:40:17  \n4  2016-03-31 00:00:00             0       60437  2016-04-06 10:17:21  \n"
     ]
    }
   ],
   "source": [
    "print(autos.head(5))"
   ]
  },
  {
   "source": [
    "There are 371,528 rows and 20 columns in our dataset. The types of each column seem correct as it matches with the column names. The column names used camelcase which could be hard to read. Note that there are columns with null value, which are `vehicleType`, `gearbox`, `model`, `fuelType`, and `notRepairedDamaged`."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## Clean data\n",
    "### Clean columns"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "Index(['dateCrawled', 'name', 'seller', 'offerType', 'price', 'abtest',\n",
       "       'vehicleType', 'yearOfRegistration', 'gearbox', 'powerPS', 'model',\n",
       "       'kilometer', 'monthOfRegistration', 'fuelType', 'brand',\n",
       "       'notRepairedDamage', 'dateCreated', 'nrOfPictures', 'postalCode',\n",
       "       'lastSeen'],\n",
       "      dtype='object')"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "source": [
    "autos.columns"
   ]
  },
  {
   "source": [
    "As mentioned previously that camelcase is used, we change all column names to snakecase. All letters in the original column names are changed to lowercases. An underscore (_) is added to separate words in the name. Additionally, we change names of some columns to be more descriptive and easier to understand. "
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "          date_crawled                            name  seller offer_type  \\\n",
       "0  2016-03-24 11:52:17                      Golf_3_1.6  privat    Angebot   \n",
       "1  2016-03-24 10:58:45            A5_Sportback_2.7_Tdi  privat    Angebot   \n",
       "2  2016-03-14 12:52:21  Jeep_Grand_Cherokee_\"Overland\"  privat    Angebot   \n",
       "3  2016-03-17 16:54:04              GOLF_4_1_4__3T�RER  privat    Angebot   \n",
       "4  2016-03-31 17:25:20  Skoda_Fabia_1.4_TDI_PD_Classic  privat    Angebot   \n",
       "\n",
       "   price abtest vehicle_type  registration_year    gearbox  power_ps  model  \\\n",
       "0    480   test          NaN               1993    manuell         0   golf   \n",
       "1  18300   test        coupe               2011    manuell       190    NaN   \n",
       "2   9800   test          suv               2004  automatik       163  grand   \n",
       "3   1500   test   kleinwagen               2001    manuell        75   golf   \n",
       "4   3600   test   kleinwagen               2008    manuell        69  fabia   \n",
       "\n",
       "   kilometer  registration_month fuel_type       brand unrepaired_damage  \\\n",
       "0     150000                   0    benzin  volkswagen               NaN   \n",
       "1     125000                   5    diesel        audi                ja   \n",
       "2     125000                   8    diesel        jeep               NaN   \n",
       "3     150000                   6    benzin  volkswagen              nein   \n",
       "4      90000                   7    diesel       skoda              nein   \n",
       "\n",
       "            ad_created  num_pictures  postal_code            last_seen  \n",
       "0  2016-03-24 00:00:00             0        70435  2016-04-07 03:16:57  \n",
       "1  2016-03-24 00:00:00             0        66954  2016-04-07 01:46:50  \n",
       "2  2016-03-14 00:00:00             0        90480  2016-04-05 12:47:46  \n",
       "3  2016-03-17 00:00:00             0        91074  2016-03-17 17:40:17  \n",
       "4  2016-03-31 00:00:00             0        60437  2016-04-06 10:17:21  "
      ],
      "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>kilometer</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_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-24 11:52:17</td>\n      <td>Golf_3_1.6</td>\n      <td>privat</td>\n      <td>Angebot</td>\n      <td>480</td>\n      <td>test</td>\n      <td>NaN</td>\n      <td>1993</td>\n      <td>manuell</td>\n      <td>0</td>\n      <td>golf</td>\n      <td>150000</td>\n      <td>0</td>\n      <td>benzin</td>\n      <td>volkswagen</td>\n      <td>NaN</td>\n      <td>2016-03-24 00:00:00</td>\n      <td>0</td>\n      <td>70435</td>\n      <td>2016-04-07 03:16:57</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2016-03-24 10:58:45</td>\n      <td>A5_Sportback_2.7_Tdi</td>\n      <td>privat</td>\n      <td>Angebot</td>\n      <td>18300</td>\n      <td>test</td>\n      <td>coupe</td>\n      <td>2011</td>\n      <td>manuell</td>\n      <td>190</td>\n      <td>NaN</td>\n      <td>125000</td>\n      <td>5</td>\n      <td>diesel</td>\n      <td>audi</td>\n      <td>ja</td>\n      <td>2016-03-24 00:00:00</td>\n      <td>0</td>\n      <td>66954</td>\n      <td>2016-04-07 01:46:50</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2016-03-14 12:52:21</td>\n      <td>Jeep_Grand_Cherokee_\"Overland\"</td>\n      <td>privat</td>\n      <td>Angebot</td>\n      <td>9800</td>\n      <td>test</td>\n      <td>suv</td>\n      <td>2004</td>\n      <td>automatik</td>\n      <td>163</td>\n      <td>grand</td>\n      <td>125000</td>\n      <td>8</td>\n      <td>diesel</td>\n      <td>jeep</td>\n      <td>NaN</td>\n      <td>2016-03-14 00:00:00</td>\n      <td>0</td>\n      <td>90480</td>\n      <td>2016-04-05 12:47:46</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2016-03-17 16:54:04</td>\n      <td>GOLF_4_1_4__3T�RER</td>\n      <td>privat</td>\n      <td>Angebot</td>\n      <td>1500</td>\n      <td>test</td>\n      <td>kleinwagen</td>\n      <td>2001</td>\n      <td>manuell</td>\n      <td>75</td>\n      <td>golf</td>\n      <td>150000</td>\n      <td>6</td>\n      <td>benzin</td>\n      <td>volkswagen</td>\n      <td>nein</td>\n      <td>2016-03-17 00:00:00</td>\n      <td>0</td>\n      <td>91074</td>\n      <td>2016-03-17 17:40:17</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2016-03-31 17:25:20</td>\n      <td>Skoda_Fabia_1.4_TDI_PD_Classic</td>\n      <td>privat</td>\n      <td>Angebot</td>\n      <td>3600</td>\n      <td>test</td>\n      <td>kleinwagen</td>\n      <td>2008</td>\n      <td>manuell</td>\n      <td>69</td>\n      <td>fabia</td>\n      <td>90000</td>\n      <td>7</td>\n      <td>diesel</td>\n      <td>skoda</td>\n      <td>nein</td>\n      <td>2016-03-31 00:00:00</td>\n      <td>0</td>\n      <td>60437</td>\n      <td>2016-04-06 10:17:21</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "source": [
    "new_col_name = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'abtest', 'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model', 'kilometer', 'registration_month', 'fuel_type', 'brand', 'unrepaired_damage', 'ad_created', 'num_pictures', 'postal_code', 'last_seen']\n",
    "\n",
    "autos.columns = new_col_name\n",
    "autos.head()"
   ]
  },
  {
   "source": [
    "## Explore data\n",
    "\n",
    "Now that the column headers are cleaned, we will start exploring the data.\n",
    "\n",
    "### Remove unnecessary columns"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               date_crawled         name  seller offer_type         price  \\\n",
       "count                371528       371528  371528     371528  3.715280e+05   \n",
       "unique               280500       233528       2          2           NaN   \n",
       "top     2016-03-24 14:49:47  Ford_Fiesta  privat    Angebot           NaN   \n",
       "freq                      7          657  371525     371516           NaN   \n",
       "mean                    NaN          NaN     NaN        NaN  1.729514e+04   \n",
       "std                     NaN          NaN     NaN        NaN  3.587954e+06   \n",
       "min                     NaN          NaN     NaN        NaN  0.000000e+00   \n",
       "25%                     NaN          NaN     NaN        NaN  1.150000e+03   \n",
       "50%                     NaN          NaN     NaN        NaN  2.950000e+03   \n",
       "75%                     NaN          NaN     NaN        NaN  7.200000e+03   \n",
       "max                     NaN          NaN     NaN        NaN  2.147484e+09   \n",
       "\n",
       "        abtest vehicle_type  registration_year  gearbox       power_ps  \\\n",
       "count   371528       333659      371528.000000   351319  371528.000000   \n",
       "unique       2            8                NaN        2            NaN   \n",
       "top       test    limousine                NaN  manuell            NaN   \n",
       "freq    192585        95894                NaN   274214            NaN   \n",
       "mean       NaN          NaN        2004.577997      NaN     115.549477   \n",
       "std        NaN          NaN          92.866598      NaN     192.139578   \n",
       "min        NaN          NaN        1000.000000      NaN       0.000000   \n",
       "25%        NaN          NaN        1999.000000      NaN      70.000000   \n",
       "50%        NaN          NaN        2003.000000      NaN     105.000000   \n",
       "75%        NaN          NaN        2008.000000      NaN     150.000000   \n",
       "max        NaN          NaN        9999.000000      NaN   20000.000000   \n",
       "\n",
       "         model      kilometer  registration_month fuel_type       brand  \\\n",
       "count   351044  371528.000000       371528.000000    338142      371528   \n",
       "unique     251            NaN                 NaN         7          40   \n",
       "top       golf            NaN                 NaN    benzin  volkswagen   \n",
       "freq     30070            NaN                 NaN    223857       79640   \n",
       "mean       NaN  125618.688228            5.734445       NaN         NaN   \n",
       "std        NaN   40112.337051            3.712412       NaN         NaN   \n",
       "min        NaN    5000.000000            0.000000       NaN         NaN   \n",
       "25%        NaN  125000.000000            3.000000       NaN         NaN   \n",
       "50%        NaN  150000.000000            6.000000       NaN         NaN   \n",
       "75%        NaN  150000.000000            9.000000       NaN         NaN   \n",
       "max        NaN  150000.000000           12.000000       NaN         NaN   \n",
       "\n",
       "       unrepaired_damage           ad_created  num_pictures   postal_code  \\\n",
       "count             299468               371528      371528.0  371528.00000   \n",
       "unique                 2                  114           NaN           NaN   \n",
       "top                 nein  2016-04-03 00:00:00           NaN           NaN   \n",
       "freq              263182                14450           NaN           NaN   \n",
       "mean                 NaN                  NaN           0.0   50820.66764   \n",
       "std                  NaN                  NaN           0.0   25799.08247   \n",
       "min                  NaN                  NaN           0.0    1067.00000   \n",
       "25%                  NaN                  NaN           0.0   30459.00000   \n",
       "50%                  NaN                  NaN           0.0   49610.00000   \n",
       "75%                  NaN                  NaN           0.0   71546.00000   \n",
       "max                  NaN                  NaN           0.0   99998.00000   \n",
       "\n",
       "                  last_seen  \n",
       "count                371528  \n",
       "unique               182806  \n",
       "top     2016-04-06 13:45:54  \n",
       "freq                     17  \n",
       "mean                    NaN  \n",
       "std                     NaN  \n",
       "min                     NaN  \n",
       "25%                     NaN  \n",
       "50%                     NaN  \n",
       "75%                     NaN  \n",
       "max                     NaN  "
      ],
      "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>kilometer</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_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>371528</td>\n      <td>371528</td>\n      <td>371528</td>\n      <td>371528</td>\n      <td>3.715280e+05</td>\n      <td>371528</td>\n      <td>333659</td>\n      <td>371528.000000</td>\n      <td>351319</td>\n      <td>371528.000000</td>\n      <td>351044</td>\n      <td>371528.000000</td>\n      <td>371528.000000</td>\n      <td>338142</td>\n      <td>371528</td>\n      <td>299468</td>\n      <td>371528</td>\n      <td>371528.0</td>\n      <td>371528.00000</td>\n      <td>371528</td>\n    </tr>\n    <tr>\n      <th>unique</th>\n      <td>280500</td>\n      <td>233528</td>\n      <td>2</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>8</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>251</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>7</td>\n      <td>40</td>\n      <td>2</td>\n      <td>114</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>182806</td>\n    </tr>\n    <tr>\n      <th>top</th>\n      <td>2016-03-24 14:49:47</td>\n      <td>Ford_Fiesta</td>\n      <td>privat</td>\n      <td>Angebot</td>\n      <td>NaN</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>NaN</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-06 13:45:54</td>\n    </tr>\n    <tr>\n      <th>freq</th>\n      <td>7</td>\n      <td>657</td>\n      <td>371525</td>\n      <td>371516</td>\n      <td>NaN</td>\n      <td>192585</td>\n      <td>95894</td>\n      <td>NaN</td>\n      <td>274214</td>\n      <td>NaN</td>\n      <td>30070</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>223857</td>\n      <td>79640</td>\n      <td>263182</td>\n      <td>14450</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>17</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>1.729514e+04</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>2004.577997</td>\n      <td>NaN</td>\n      <td>115.549477</td>\n      <td>NaN</td>\n      <td>125618.688228</td>\n      <td>5.734445</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>0.0</td>\n      <td>50820.66764</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>3.587954e+06</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>92.866598</td>\n      <td>NaN</td>\n      <td>192.139578</td>\n      <td>NaN</td>\n      <td>40112.337051</td>\n      <td>3.712412</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>0.0</td>\n      <td>25799.08247</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>0.000000e+00</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>5000.000000</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.00000</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>1.150000e+03</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>125000.000000</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>30459.00000</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>2.950000e+03</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>150000.000000</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>49610.00000</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>7.200000e+03</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>150000.000000</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>71546.00000</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>2.147484e+09</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>9999.000000</td>\n      <td>NaN</td>\n      <td>20000.000000</td>\n      <td>NaN</td>\n      <td>150000.000000</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.00000</td>\n      <td>NaN</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "source": [
    "autos.describe(include=\"all\")"
   ]
  },
  {
   "source": [
    "There are columns which contain less than 10 unique values. Those columns are `seller`, `offer_type`, `abtest`, `vehicle_type`, `gearbox` `fuel_type`, and `unrepaired_damage`. We will explore each column to determine whether to keep or to drop them."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "privat        371525\ngewerblich         3\nName: seller, dtype: int64\n\nAngebot    371516\nGesuch         12\nName: offer_type, dtype: int64\n\ntest       192585\ncontrol    178943\nName: abtest, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"seller\"].value_counts())\n",
    "print()\n",
    "print(autos[\"offer_type\"].value_counts())\n",
    "print()\n",
    "print(autos[\"abtest\"].value_counts())"
   ]
  },
  {
   "source": [
    "`seller` column shows the types of seller: \"privat\" (private) and \"gewerblich\" (commercial). Since 99.99% of our dataset belong to privat type, this column will be removed as it would not be useful much for the analysis.\n",
    "\n",
    "`offer_type` shows the types of ads: \"Angebot\" (offer) and \"Gesuch\" (request). Similar to `seller` column, 99.99% of our dataset belong to one type. This column will also be removed.\n",
    "\n",
    "`abtest` shows better distribution between each type. It is a part of eBay's new feature testing process. \"test\" is assigned to an ads to receive a new feature, and \"control\" is an ads with normal features. We don't know yet if this feature testing could affect the car ads, hence this column will be kept for now."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "limousine     95894\n",
      "kleinwagen    80023\n",
      "kombi         67564\n",
      "bus           30201\n",
      "cabrio        22898\n",
      "coupe         19015\n",
      "suv           14707\n",
      "andere         3357\n",
      "Name: vehicle_type, dtype: int64\n",
      "\n",
      "manuell      274214\n",
      "automatik     77105\n",
      "Name: gearbox, dtype: int64\n",
      "\n",
      "benzin     223857\n",
      "diesel     107746\n",
      "lpg          5378\n",
      "cng           571\n",
      "hybrid        278\n",
      "andere        208\n",
      "elektro       104\n",
      "Name: fuel_type, dtype: int64\n",
      "\n",
      "nein    263182\n",
      "ja       36286\n",
      "Name: unrepaired_damage, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"vehicle_type\"].value_counts())\n",
    "print()\n",
    "print(autos[\"gearbox\"].value_counts())\n",
    "print()\n",
    "print(autos[\"fuel_type\"].value_counts())\n",
    "print()\n",
    "print(autos[\"unrepaired_damage\"].value_counts())"
   ]
  },
  {
   "source": [
    "These columns provide information about the cars being advertised despite small unique values. Hence we will keep these columns for further analysis."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "0    371528\nName: num_pictures, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"num_pictures\"].value_counts())"
   ]
  },
  {
   "source": [
    "We spot that all statistical values for `num_pictures` column are 0. We check all data in the column and conclude to remove this column as well.\n",
    "\n",
    "In conclusion, we will drop 3 columns (`seller`, `offer_type`, and `num_pictures`) from our dataset."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nRangeIndex: 371528 entries, 0 to 371527\nData columns (total 17 columns):\n #   Column              Non-Null Count   Dtype \n---  ------              --------------   ----- \n 0   date_crawled        371528 non-null  object\n 1   name                371528 non-null  object\n 2   price               371528 non-null  int64 \n 3   abtest              371528 non-null  object\n 4   vehicle_type        333659 non-null  object\n 5   registration_year   371528 non-null  int64 \n 6   gearbox             351319 non-null  object\n 7   power_ps            371528 non-null  int64 \n 8   model               351044 non-null  object\n 9   kilometer           371528 non-null  int64 \n 10  registration_month  371528 non-null  int64 \n 11  fuel_type           338142 non-null  object\n 12  brand               371528 non-null  object\n 13  unrepaired_damage   299468 non-null  object\n 14  ad_created          371528 non-null  object\n 15  postal_code         371528 non-null  int64 \n 16  last_seen           371528 non-null  object\ndtypes: int64(6), object(11)\nmemory usage: 48.2+ MB\n"
     ]
    }
   ],
   "source": [
    "autos = autos.drop([\"seller\", \"offer_type\", \"num_pictures\"], axis=1)\n",
    "autos.info()"
   ]
  },
  {
   "source": [
    "### Remove outliers\n",
    "Next we will check if our dataset contains any outliers, or any unrealistic data that could mislead the analysis. We will focus on `price` and `kilometer` columns.\n",
    "\n",
    "*Remove outliers for* `price`"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "5597\n\ncount    3.715280e+05\nmean     1.729514e+04\nstd      3.587954e+06\nmin      0.000000e+00\n25%      1.150000e+03\n50%      2.950000e+03\n75%      7.200000e+03\nmax      2.147484e+09\nName: price, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"price\"].unique().shape[0])\n",
    "print()\n",
    "print(autos[\"price\"].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0     10778\n",
       "1      1189\n",
       "2        12\n",
       "3         8\n",
       "4         1\n",
       "5        26\n",
       "7         3\n",
       "8         9\n",
       "9         8\n",
       "10       84\n",
       "Name: price, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "autos[\"price\"].value_counts().sort_index(ascending=True).head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "            date_crawled                                               name  \\\n7    2016-03-21 18:54:38                       VW_Derby_Bj_80__Scheunenfund   \n40   2016-03-26 22:06:17                  Suche_Opel_corsa_a_zu_verschenken   \n60   2016-03-29 15:48:15  TAUSCHE_BMW_E38_740i_g._SUV_/_GEL�NDEWAGEN_LES...   \n91   2016-03-28 09:37:01  MERCEDES_BENZ_W124_250D_83KW_/_113PS___SCHLACH...   \n115  2016-03-19 18:40:12                                    Golf_IV_1.4_16V   \n\n     price   abtest vehicle_type  registration_year  gearbox  power_ps  \\\n7        0     test    limousine               1980  manuell        50   \n40       0     test          NaN               1990      NaN         0   \n60       1  control          suv               1994  manuell       286   \n91       1  control    limousine               1995  manuell       113   \n115      0     test          NaN               2017  manuell         0   \n\n        model  kilometer  registration_month fuel_type           brand  \\\n7      andere      40000                   7    benzin      volkswagen   \n40      corsa     150000                   1    benzin            opel   \n60        NaN     150000                  11       NaN  sonstige_autos   \n91   e_klasse     150000                   4    diesel   mercedes_benz   \n115      golf       5000                  12    benzin      volkswagen   \n\n    unrepaired_damage           ad_created  postal_code            last_seen  \n7                nein  2016-03-21 00:00:00        19348  2016-03-25 16:47:58  \n40                NaN  2016-03-26 00:00:00        56412  2016-03-27 17:43:34  \n60                NaN  2016-03-29 00:00:00        53721  2016-04-06 01:44:38  \n91               nein  2016-03-28 00:00:00        40589  2016-04-06 12:15:54  \n115               NaN  2016-03-19 00:00:00        21698  2016-04-01 08:47:05  \n"
     ]
    }
   ],
   "source": [
    "autos_zero = autos[autos[\"price\"] < 100].head()\n",
    "print(autos_zero)"
   ]
  },
  {
   "source": [
    "Although these prices seem too low to be a car price, it is possible that these are the starting price. Hence we will keep these data.\n",
    "\n",
    "Next we check the price at the higher end."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2147483647     1\n",
       "99999999      15\n",
       "99000000       1\n",
       "74185296       1\n",
       "32545461       1\n",
       "27322222       1\n",
       "14000500       1\n",
       "12345678       9\n",
       "11111111      10\n",
       "10010011       1\n",
       "10000000       8\n",
       "9999999        3\n",
       "3895000        1\n",
       "3890000        1\n",
       "2995000        1\n",
       "2795000        1\n",
       "1600000        2\n",
       "1300000        1\n",
       "1250000        2\n",
       "1234566        1\n",
       "Name: price, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 14
    }
   ],
   "source": [
    "autos[\"price\"].value_counts().sort_index(ascending=False).head(20)"
   ]
  },
  {
   "source": [
    "There are many unrealistic prices. For example, the maximum price is over 2billion, which is too high. We also notice a price jump from 3,895,000 to 9,999,999. However, the frequency of each price in the top 20 range is very low. We will therefore check the price in each range to better determine our cut-off line."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "371528\n"
     ]
    },
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "0 - 1,000             87984\n",
       "1,001 - 10,000       223142\n",
       "10,001 - 100,000      59999\n",
       "100,001 - 200,000       233\n",
       "200,001 - 300,000        48\n",
       "> 300,000               122\n",
       "Name: price_range, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 15
    }
   ],
   "source": [
    "# Create new column for price range\n",
    "autos[\"price_range\"] = autos[\"price\"] <= 1000\n",
    "autos.loc[(autos[\"price\"]) <= 1000, \"price_range\"] = \"0 - 1,000\"\n",
    "autos.loc[(autos[\"price\"] > 1000) & (autos[\"price\"] <= 10000), \"price_range\"] = \"1,001 - 10,000\"\n",
    "autos.loc[(autos[\"price\"] > 10000) & (autos[\"price\"] <= 100000), \"price_range\"] = \"10,001 - 100,000\"\n",
    "autos.loc[(autos[\"price\"] > 100000) & (autos[\"price\"] <= 200000), \"price_range\"] = \"100,001 - 200,000\"\n",
    "autos.loc[(autos[\"price\"] > 200000) & (autos[\"price\"] <= 300000), \"price_range\"] = \"200,001 - 300,000\"\n",
    "autos.loc[autos[\"price\"] > 300000, \"price_range\"] = \"> 300,000\"\n",
    "\n",
    "print(autos[\"price_range\"].shape[0])\n",
    "autos[\"price_range\"].value_counts().sort_index(ascending=True)"
   ]
  },
  {
   "source": [
    "We see that there are 403 car ads at the price above 100,000 euro. These 403 car ads are accounted for 0.11% of our data set. Therefore we will drop any rows above 100,000 euro."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "count    371125.000000\n",
       "mean       5607.804734\n",
       "std        7503.923817\n",
       "min           0.000000\n",
       "25%        1150.000000\n",
       "50%        2950.000000\n",
       "75%        7199.000000\n",
       "max      100000.000000\n",
       "Name: price, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 16
    }
   ],
   "source": [
    "autos = autos[autos[\"price\"] <= 100000]\n",
    "autos[\"price\"].describe()"
   ]
  },
  {
   "source": [
    "*Remove outliers for* `kilometer`"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "13\n\ncount    371125.000000\nmean     125692.206130\nstd       40031.064165\nmin        5000.000000\n25%      125000.000000\n50%      150000.000000\n75%      150000.000000\nmax      150000.000000\nName: kilometer, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(autos[\"kilometer\"].unique().shape[0])\n",
    "print()\n",
    "print(autos[\"kilometer\"].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "150000    240720\n",
       "125000     38054\n",
       "100000     15896\n",
       "90000      12516\n",
       "80000      11038\n",
       "70000       9762\n",
       "60000       8650\n",
       "50000       7598\n",
       "40000       6358\n",
       "30000       6015\n",
       "20000       5632\n",
       "10000       1922\n",
       "5000        6964\n",
       "Name: kilometer, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "autos[\"kilometer\"].value_counts().sort_index(ascending=False)"
   ]
  },
  {
   "source": [
    "These kilometer data seem to be a preset information from eBay as they are all rounded and not many unique values. Since there is no outlier in this column, no rows are removed."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\nInt64Index: 371125 entries, 0 to 371527\nData columns (total 18 columns):\n #   Column              Non-Null Count   Dtype \n---  ------              --------------   ----- \n 0   date_crawled        371125 non-null  object\n 1   name                371125 non-null  object\n 2   price               371125 non-null  int64 \n 3   abtest              371125 non-null  object\n 4   vehicle_type        333298 non-null  object\n 5   registration_year   371125 non-null  int64 \n 6   gearbox             350983 non-null  object\n 7   power_ps            371125 non-null  int64 \n 8   model               350750 non-null  object\n 9   kilometer           371125 non-null  int64 \n 10  registration_month  371125 non-null  int64 \n 11  fuel_type           337804 non-null  object\n 12  brand               371125 non-null  object\n 13  unrepaired_damage   299129 non-null  object\n 14  ad_created          371125 non-null  object\n 15  postal_code         371125 non-null  int64 \n 16  last_seen           371125 non-null  object\n 17  price_range         371125 non-null  object\ndtypes: int64(6), object(12)\nmemory usage: 53.8+ MB\n"
     ]
    }
   ],
   "source": [
    "autos.info()"
   ]
  },
  {
   "source": [
    "We will continue working with 371,125 rows and 18 columns.\n",
    "\n",
    "### Date columns\n",
    "There are 5 date-related columns in our data set. Some are added by the crawler, some are data from the website. We will convert the date in string format into numerical values so we can work with them quantitatively.\n",
    "\n",
    "|Column|Type|Remark|\n",
    "|------|----|------|\n",
    "|`date_crawled` | String | Added by the crawler|\n",
    "|`registration_year` | Integer | From the website|\n",
    "|`registration_month` | Integer | From the website|\n",
    "|`ad_created` | Integer | From the website|\n",
    "|`last_seen` | String | Added by the crawler|"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "          date_crawled           ad_created            last_seen\n",
       "0  2016-03-24 11:52:17  2016-03-24 00:00:00  2016-04-07 03:16:57\n",
       "1  2016-03-24 10:58:45  2016-03-24 00:00:00  2016-04-07 01:46:50\n",
       "2  2016-03-14 12:52:21  2016-03-14 00:00:00  2016-04-05 12:47:46\n",
       "3  2016-03-17 16:54:04  2016-03-17 00:00:00  2016-03-17 17:40:17\n",
       "4  2016-03-31 17:25:20  2016-03-31 00:00:00  2016-04-06 10:17:21"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>date_crawled</th>\n      <th>ad_created</th>\n      <th>last_seen</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2016-03-24 11:52:17</td>\n      <td>2016-03-24 00:00:00</td>\n      <td>2016-04-07 03:16:57</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2016-03-24 10:58:45</td>\n      <td>2016-03-24 00:00:00</td>\n      <td>2016-04-07 01:46:50</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2016-03-14 12:52:21</td>\n      <td>2016-03-14 00:00:00</td>\n      <td>2016-04-05 12:47:46</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>2016-03-17 16:54:04</td>\n      <td>2016-03-17 00:00:00</td>\n      <td>2016-03-17 17:40:17</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2016-03-31 17:25:20</td>\n      <td>2016-03-31 00:00:00</td>\n      <td>2016-04-06 10:17:21</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 20
    }
   ],
   "source": [
    "autos[['date_crawled','ad_created','last_seen']][0:5]"
   ]
  },
  {
   "source": [
    "The date format of all 3 columns is \"YYYY-MM-DD hh:mm:ss\". We will extract the date section from these columns."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2016-03-05    0.025557\n",
       "2016-03-06    0.014464\n",
       "2016-03-07    0.035697\n",
       "2016-03-08    0.033458\n",
       "2016-03-09    0.034220\n",
       "2016-03-10    0.032574\n",
       "2016-03-11    0.032728\n",
       "2016-03-12    0.036209\n",
       "2016-03-13    0.015749\n",
       "2016-03-14    0.036257\n",
       "2016-03-15    0.033450\n",
       "2016-03-16    0.030154\n",
       "2016-03-17    0.031652\n",
       "2016-03-18    0.013122\n",
       "2016-03-19    0.035295\n",
       "2016-03-20    0.036357\n",
       "2016-03-21    0.035721\n",
       "2016-03-22    0.032463\n",
       "2016-03-23    0.031973\n",
       "2016-03-24    0.029917\n",
       "2016-03-25    0.032946\n",
       "2016-03-26    0.031976\n",
       "2016-03-27    0.030267\n",
       "2016-03-28    0.035118\n",
       "2016-03-29    0.034164\n",
       "2016-03-30    0.033525\n",
       "2016-03-31    0.031873\n",
       "2016-04-01    0.034115\n",
       "2016-04-02    0.035091\n",
       "2016-04-03    0.038720\n",
       "2016-04-04    0.037596\n",
       "2016-04-05    0.012812\n",
       "2016-04-06    0.003161\n",
       "2016-04-07    0.001617\n",
       "Name: date_crawled, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 21
    }
   ],
   "source": [
    "(autos['date_crawled']\n",
    "                    .str[:10]\n",
    "                    .value_counts(normalize=True, dropna=False)\n",
    "                    .sort_index(ascending=True)\n",
    ")"
   ]
  },
  {
   "source": [
    "The crawler scraped data in a period of roughly 1 month, from 5 March to 7 April 2016. The amount of data each day is about the same, except on 6 and 7 April where we can see significant drop."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2014-03-10    0.000003\n",
       "2015-03-20    0.000003\n",
       "2015-06-11    0.000003\n",
       "2015-06-18    0.000003\n",
       "2015-08-07    0.000003\n",
       "                ...   \n",
       "2016-04-03    0.038876\n",
       "2016-04-04    0.037729\n",
       "2016-04-05    0.011643\n",
       "2016-04-06    0.003153\n",
       "2016-04-07    0.001555\n",
       "Name: ad_created, Length: 114, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 22
    }
   ],
   "source": [
    "(autos['ad_created']\n",
    "                    .str[:10]\n",
    "                    .value_counts(normalize=True, dropna=False)\n",
    "                    .sort_index(ascending=True)\n",
    ")"
   ]
  },
  {
   "source": [
    "There are 114 unique dates in `ad_created` column. The oldest ads in our dataset was created since 2014."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2016-03-05    0.001291\n",
       "2016-03-06    0.004139\n",
       "2016-03-07    0.005265\n",
       "2016-03-08    0.008057\n",
       "2016-03-09    0.009997\n",
       "2016-03-10    0.011573\n",
       "2016-03-11    0.013047\n",
       "2016-03-12    0.023413\n",
       "2016-03-13    0.008496\n",
       "2016-03-14    0.012292\n",
       "2016-03-15    0.016407\n",
       "2016-03-16    0.016426\n",
       "2016-03-17    0.028764\n",
       "2016-03-18    0.006928\n",
       "2016-03-19    0.016321\n",
       "2016-03-20    0.019915\n",
       "2016-03-21    0.020123\n",
       "2016-03-22    0.020616\n",
       "2016-03-23    0.018150\n",
       "2016-03-24    0.019244\n",
       "2016-03-25    0.019112\n",
       "2016-03-26    0.016164\n",
       "2016-03-27    0.016916\n",
       "2016-03-28    0.022281\n",
       "2016-03-29    0.023313\n",
       "2016-03-30    0.023865\n",
       "2016-03-31    0.024234\n",
       "2016-04-01    0.024024\n",
       "2016-04-02    0.025008\n",
       "2016-04-03    0.025355\n",
       "2016-04-04    0.025665\n",
       "2016-04-05    0.126146\n",
       "2016-04-06    0.217816\n",
       "2016-04-07    0.129638\n",
       "Name: last_seen, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 23
    }
   ],
   "source": [
    "(autos['last_seen']\n",
    "                    .str[:10]\n",
    "                    .value_counts(normalize=True, dropna=False)\n",
    "                    .sort_index(ascending=True)\n",
    ")"
   ]
  },
  {
   "source": [
    "This is the date that the crawler last seen the car ads. It could be that the ads was removed because they were sold. However, there is an unusual spike during 5 - 7 April. The highest is on 6 April, where the value is 10 times higher preivous days. Hence, the `last_seen` data during these 3 days might not be an indication of car sales."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "count    371125.000000\n",
       "mean       2004.548785\n",
       "std          91.260186\n",
       "min        1000.000000\n",
       "25%        1999.000000\n",
       "50%        2003.000000\n",
       "75%        2008.000000\n",
       "max        9999.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 24
    }
   ],
   "source": [
    "autos[\"registration_year\"].describe()"
   ]
  },
  {
   "source": [
    "The data in `registration_year` shows a minimum at *1000* and a maximum at *9999*. These numbers are definitely not an actual information of a car. We will need to explore this column further in order to correct these incorrect data.\n",
    "\n",
    "### Clean `registration_year` column"
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "np.sort(autos[\"registration_year\"].unique())"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 25,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "array([1000, 1001, 1039, 1111, 1200, 1234, 1253, 1255, 1300, 1400, 1500,\n",
       "       1600, 1602, 1688, 1800, 1910, 1911, 1915, 1919, 1920, 1923, 1925,\n",
       "       1927, 1928, 1929, 1930, 1931, 1932, 1933, 1934, 1935, 1936, 1937,\n",
       "       1938, 1939, 1940, 1941, 1942, 1943, 1944, 1945, 1946, 1947, 1948,\n",
       "       1949, 1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959,\n",
       "       1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969, 1970,\n",
       "       1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979, 1980, 1981,\n",
       "       1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989, 1990, 1991, 1992,\n",
       "       1993, 1994, 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002, 2003,\n",
       "       2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014,\n",
       "       2015, 2016, 2017, 2018, 2019, 2066, 2200, 2222, 2290, 2500, 2800,\n",
       "       2900, 3000, 3200, 3500, 3700, 3800, 4000, 4100, 4500, 4800, 5000,\n",
       "       5300, 5555, 5600, 5900, 5911, 6000, 6200, 6500, 7000, 7100, 7500,\n",
       "       7777, 7800, 8000, 8200, 8455, 8500, 8888, 9000, 9229, 9450, 9996,\n",
       "       9999], dtype=int64)"
      ]
     },
     "metadata": {},
     "execution_count": 25
    }
   ]
  },
  {
   "source": [
    "The first modern car was invented in 1886. Consequently, we will drop rows before 1886. We will also drop any rows after 2016. Since our data set is in April 2016, any years after 2016 would be a future date."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "count    356388.000000\n",
       "mean       2002.784084\n",
       "std           7.373305\n",
       "min        1910.000000\n",
       "25%        1999.000000\n",
       "50%        2003.000000\n",
       "75%        2008.000000\n",
       "max        2016.000000\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 26
    }
   ],
   "source": [
    "autos = autos[autos[\"registration_year\"].between(1886,2016)]\n",
    "autos[\"registration_year\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "2000    0.068844\n",
       "1999    0.063871\n",
       "2005    0.062600\n",
       "2006    0.056739\n",
       "2001    0.056702\n",
       "2003    0.055748\n",
       "2004    0.055386\n",
       "2002    0.053837\n",
       "1998    0.050338\n",
       "2007    0.049570\n",
       "Name: registration_year, dtype: float64"
      ]
     },
     "metadata": {},
     "execution_count": 27
    }
   ],
   "source": [
    "autos[\"registration_year\"].value_counts(normalize=True).sort_values(ascending=False).head(10)"
   ]
  },
  {
   "source": [
    "Most cars in our data set were first registered during 1998-2007. This percentage matches the percentile ranks shown above."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## Analyze data\n",
    "### Price-Brand analysis\n",
    "Here we will analyze which brands are more expensive and which are cheaper on average."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "Total number of brands:  40\n\nvolkswagen        0.212552\nbmw               0.109746\nopel              0.107178\nmercedes_benz     0.095932\naudi              0.089403\nford              0.068905\nrenault           0.047608\npeugeot           0.029892\nfiat              0.025784\nseat              0.018648\nskoda             0.015421\nmazda             0.015362\nsmart             0.014119\ncitroen           0.013887\nnissan            0.013584\ntoyota            0.012759\nsonstige_autos    0.010418\nhyundai           0.009840\nmini              0.009220\nvolvo             0.009139\nmitsubishi        0.008272\nhonda             0.007596\nkia               0.006886\nalfa_romeo        0.006353\nsuzuki            0.006319\nporsche           0.005716\nchevrolet         0.005017\nchrysler          0.003945\ndacia             0.002452\ndaihatsu          0.002189\njeep              0.002186\nland_rover        0.002130\nsubaru            0.002127\njaguar            0.001700\ntrabant           0.001619\nsaab              0.001453\ndaewoo            0.001439\nrover             0.001299\nlancia            0.001294\nlada              0.000612\nName: brand, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print(\"Total number of brands: \",autos[\"brand\"].unique().shape[0])\n",
    "print()\n",
    "print(autos[\"brand\"].value_counts(normalize=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               mean_price\n",
       "audi                 8810\n",
       "mercedes_benz        8215\n",
       "bmw                  8165\n",
       "volkswagen           5222\n",
       "seat                 4397\n",
       "ford                 3585\n",
       "peugeot              3206\n",
       "opel                 2870\n",
       "fiat                 2803\n",
       "renault              2360"
      ],
      "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>8810</td>\n    </tr>\n    <tr>\n      <th>mercedes_benz</th>\n      <td>8215</td>\n    </tr>\n    <tr>\n      <th>bmw</th>\n      <td>8165</td>\n    </tr>\n    <tr>\n      <th>volkswagen</th>\n      <td>5222</td>\n    </tr>\n    <tr>\n      <th>seat</th>\n      <td>4397</td>\n    </tr>\n    <tr>\n      <th>ford</th>\n      <td>3585</td>\n    </tr>\n    <tr>\n      <th>peugeot</th>\n      <td>3206</td>\n    </tr>\n    <tr>\n      <th>opel</th>\n      <td>2870</td>\n    </tr>\n    <tr>\n      <th>fiat</th>\n      <td>2803</td>\n    </tr>\n    <tr>\n      <th>renault</th>\n      <td>2360</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 29
    }
   ],
   "source": [
    "# Select top 10 common brands\n",
    "top10 = autos[\"brand\"].value_counts(normalize=True).head(10).index\n",
    "\n",
    "# Find average price of the top 10 brands\n",
    "brand_mean_price = {}\n",
    "\n",
    "for brand in top10:\n",
    "    car_brand = autos[autos[\"brand\"] == brand]\n",
    "    mean_price = car_brand[\"price\"].mean()\n",
    "    brand_mean_price[brand] = int(mean_price)\n",
    "\n",
    "bmp_series = pd.Series(brand_mean_price)\n",
    "brand_analysis = pd.DataFrame(bmp_series, columns=[\"mean_price\"])\n",
    "brand_analysis.sort_values(\"mean_price\", ascending=False)"
   ]
  },
  {
   "source": [
    "There are 40 unique values in `brand` columns, consisting of 39 brands and 1 \"other brands\". As the top 10 brands already cover approximately 75% of the data, we therefore focus on these brands.\n",
    "\n",
    "From the information above, there is a big price gap between top-tier and other brands. The prices of *Audi*, *Mercedes Benz*, and *BMW* are more than 8,000 euro on average. The less expensive brands are *Fiat*,*Opel*, and *Renault*, at less than 3,000 euro. The brands in the middle range are *Volkswagen*, *Seat*, *Ford*, and *Peugeot*.\n",
    "\n",
    "### Mileage-Brand analysis\n",
    "Next we will analyze the mileage of each top 10 brands."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "brand_mean_mileage = {}\n",
    "\n",
    "for brand in top10:\n",
    "    car_brand = autos[autos[\"brand\"] == brand]\n",
    "    mean_km = car_brand[\"kilometer\"].mean()\n",
    "    brand_mean_mileage[brand] = int(mean_km)\n",
    "\n",
    "brand_analysis[\"mean_mileage\"] = pd.Series(brand_mean_mileage)\n",
    "brand_analysis.sort_values(\"mean_mileage\", ascending=False)"
   ],
   "cell_type": "code",
   "metadata": {},
   "execution_count": 30,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               mean_price  mean_mileage\n",
       "bmw                  8165        132688\n",
       "mercedes_benz        8215        130671\n",
       "audi                 8810        129529\n",
       "opel                 2870        128756\n",
       "volkswagen           5222        128341\n",
       "renault              2360        127877\n",
       "peugeot              3206        124599\n",
       "ford                 3585        123621\n",
       "seat                 4397        120911\n",
       "fiat                 2803        116523"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>mean_price</th>\n      <th>mean_mileage</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>bmw</th>\n      <td>8165</td>\n      <td>132688</td>\n    </tr>\n    <tr>\n      <th>mercedes_benz</th>\n      <td>8215</td>\n      <td>130671</td>\n    </tr>\n    <tr>\n      <th>audi</th>\n      <td>8810</td>\n      <td>129529</td>\n    </tr>\n    <tr>\n      <th>opel</th>\n      <td>2870</td>\n      <td>128756</td>\n    </tr>\n    <tr>\n      <th>volkswagen</th>\n      <td>5222</td>\n      <td>128341</td>\n    </tr>\n    <tr>\n      <th>renault</th>\n      <td>2360</td>\n      <td>127877</td>\n    </tr>\n    <tr>\n      <th>peugeot</th>\n      <td>3206</td>\n      <td>124599</td>\n    </tr>\n    <tr>\n      <th>ford</th>\n      <td>3585</td>\n      <td>123621</td>\n    </tr>\n    <tr>\n      <th>seat</th>\n      <td>4397</td>\n      <td>120911</td>\n    </tr>\n    <tr>\n      <th>fiat</th>\n      <td>2803</td>\n      <td>116523</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 30
    }
   ]
  },
  {
   "source": [
    "*BMW*, *Mercendes Benz*, and *Audi* are still at the top for mileage. However, the mileage ranges for each brand are at about the same level with less than 10% variance between the 1st (BMW) and the last (Fiat) ranks."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## Conclusion\n",
    "The price of each brand tends to follow the brand positioning. The top-tier brands (Audi, Mercedes Benz, and BMW) are listed at more than 8,000 euro on average, while the less expensive brands (Fiat, Opel, and Renault) are listed at less than 3,000 euro.\n",
    "\n",
    "The more-expensive brands tend to have higher mileage on average. However, there are less than 10% variance between the 1st (BMW) and the last (Fiat) ranks."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "source": [
    "## Next step\n",
    "### Transmission-Price analysis\n",
    "We will analyze whether a type of transmission would affect the price of the car. We will focus only on the common brands from above."
   ],
   "cell_type": "markdown",
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "manuell      263282\n",
       "automatik     74901\n",
       "Name: gearbox, dtype: int64"
      ]
     },
     "metadata": {},
     "execution_count": 31
    }
   ],
   "source": [
    "autos[\"gearbox\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "               mean_price  mean_mileage  manual_mean_price  manual_auto_price\n",
       "audi                 8810        129529               7052              12832\n",
       "bmw                  8165        132688               6248              12202\n",
       "seat                 4397        120911               4140              11517\n",
       "mercedes_benz        8215        130671               5223              10695\n",
       "volkswagen           5222        128341               4605              10157\n",
       "ford                 3585        123621               3350               7433\n",
       "fiat                 2803        116523               2810               5443\n",
       "peugeot              3206        124599               3139               5133\n",
       "opel                 2870        128756               2863               3649\n",
       "renault              2360        127877               2346               3493"
      ],
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>mean_price</th>\n      <th>mean_mileage</th>\n      <th>manual_mean_price</th>\n      <th>manual_auto_price</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>audi</th>\n      <td>8810</td>\n      <td>129529</td>\n      <td>7052</td>\n      <td>12832</td>\n    </tr>\n    <tr>\n      <th>bmw</th>\n      <td>8165</td>\n      <td>132688</td>\n      <td>6248</td>\n      <td>12202</td>\n    </tr>\n    <tr>\n      <th>seat</th>\n      <td>4397</td>\n      <td>120911</td>\n      <td>4140</td>\n      <td>11517</td>\n    </tr>\n    <tr>\n      <th>mercedes_benz</th>\n      <td>8215</td>\n      <td>130671</td>\n      <td>5223</td>\n      <td>10695</td>\n    </tr>\n    <tr>\n      <th>volkswagen</th>\n      <td>5222</td>\n      <td>128341</td>\n      <td>4605</td>\n      <td>10157</td>\n    </tr>\n    <tr>\n      <th>ford</th>\n      <td>3585</td>\n      <td>123621</td>\n      <td>3350</td>\n      <td>7433</td>\n    </tr>\n    <tr>\n      <th>fiat</th>\n      <td>2803</td>\n      <td>116523</td>\n      <td>2810</td>\n      <td>5443</td>\n    </tr>\n    <tr>\n      <th>peugeot</th>\n      <td>3206</td>\n      <td>124599</td>\n      <td>3139</td>\n      <td>5133</td>\n    </tr>\n    <tr>\n      <th>opel</th>\n      <td>2870</td>\n      <td>128756</td>\n      <td>2863</td>\n      <td>3649</td>\n    </tr>\n    <tr>\n      <th>renault</th>\n      <td>2360</td>\n      <td>127877</td>\n      <td>2346</td>\n      <td>3493</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 32
    }
   ],
   "source": [
    "# Find average price for manual transmission\n",
    "brand_manual_price = {}\n",
    "\n",
    "for brand in top10:\n",
    "    car_brand = autos[(autos[\"brand\"] == brand) & (autos[\"gearbox\"] == \"manuell\")]\n",
    "    mean_price = car_brand[\"price\"].mean()\n",
    "    brand_manual_price[brand] = int(mean_price)\n",
    "\n",
    "brand_analysis[\"manual_mean_price\"] = pd.Series(brand_manual_price)\n",
    "brand_analysis.sort_values(\"manual_mean_price\")\n",
    "\n",
    "\n",
    "# Find average price for automatic transmission\n",
    "brand_auto_price = {}\n",
    "\n",
    "for brand in top10:\n",
    "    car_brand = autos[(autos[\"brand\"] == brand) & (autos[\"gearbox\"] == \"automatik\")]\n",
    "    mean_price = car_brand[\"price\"].mean()\n",
    "    brand_auto_price[brand] = int(mean_price)\n",
    "\n",
    "brand_analysis[\"manual_auto_price\"] = pd.Series(brand_auto_price)\n",
    "brand_analysis.sort_values(\"manual_auto_price\", ascending=False)"
   ]
  },
  {
   "source": [
    "The table shows that the transmission type of the car affects the price. On average, cars with automatic transmission are more expensive. For most brands, the price for manual transmission cars is lower than their average price, except for *Fiat*. *Renault* has minimal price difference between the two transmissions, while *Seat* has the highest price difference between manual and automatic transmission cars."
   ],
   "cell_type": "markdown",
   "metadata": {}
  }
 ]
}