{ "cells": [ { "cell_type": "markdown", "metadata": { "collapsed": true }, "source": [ "# Exploring Ebay Car Sales Data\n", "\n", "In this project, we'll work with a dataset of used cars from eBay Kleinanzeigen, a classifieds section of the German eBay website.\n", "\n", "The dataset was originally scraped and uploaded to [Kaggle](https://www.kaggle.com/orgesleka/used-cars-database/data). The version of the dataset we are working with is a sample of 50,000 data points that was prepared by Dataquest including simulating a less-cleaned version of the data.\n", "\n", "The data dictionary provided with data is as follows:\n", "\n", "- dateCrawled - When this ad was first crawled. All field-values are taken from this date.\n", "- name - Name of the car.\n", "- seller - Whether the seller is private or a dealer.\n", "- offerType - The type of listing\n", "- price - The price on the ad to sell the car.\n", "- abtest - Whether the listing is included in an A/B test.\n", "- vehicleType - The vehicle Type.\n", "- yearOfRegistration - The year in which the car was first registered.\n", "- gearbox - The transmission type.\n", "- powerPS - The power of the car in PS.\n", "- model - The car model name.\n", "- kilometer - How many kilometers the car has driven.\n", "- monthOfRegistration - The month in which the car was first registered.\n", "- fuelType - What type of fuel the car uses.\n", "- brand - The brand of the car.\n", "- notRepairedDamage - If the car has a damage which is not yet repaired.\n", "- dateCreated - The date on which the eBay listing was created.\n", "- nrOfPictures - The number of pictures in the ad.\n", "- postalCode - The postal code for the location of the vehicle.\n", "- lastSeenOnline - When the crawler saw this ad last online.\n", "\n", "The aim of this project is to clean the data and analyze the included used car listings." ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "collapsed": false }, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "\n", "autos = pd.read_csv('autos.csv', encoding = 'Latin-1')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Dataset Description" ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "RangeIndex: 50000 entries, 0 to 49999\n", "Data columns (total 20 columns):\n", "dateCrawled 50000 non-null object\n", "name 50000 non-null object\n", "seller 50000 non-null object\n", "offerType 50000 non-null object\n", "price 50000 non-null object\n", "abtest 50000 non-null object\n", "vehicleType 44905 non-null object\n", "yearOfRegistration 50000 non-null int64\n", "gearbox 47320 non-null object\n", "powerPS 50000 non-null int64\n", "model 47242 non-null object\n", "odometer 50000 non-null object\n", "monthOfRegistration 50000 non-null int64\n", "fuelType 45518 non-null object\n", "brand 50000 non-null object\n", "notRepairedDamage 40171 non-null object\n", "dateCreated 50000 non-null object\n", "nrOfPictures 50000 non-null int64\n", "postalCode 50000 non-null int64\n", "lastSeen 50000 non-null object\n", "dtypes: int64(5), object(15)\n", "memory usage: 7.6+ MB\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
dateCrawlednamesellerofferTypepriceabtestvehicleTypeyearOfRegistrationgearboxpowerPSmodelodometermonthOfRegistrationfuelTypebrandnotRepairedDamagedateCreatednrOfPicturespostalCodelastSeen
02016-03-26 17:47:46Peugeot_807_160_NAVTECH_ON_BOARDprivatAngebot$5,000controlbus2004manuell158andere150,000km3lpgpeugeotnein2016-03-26 00:00:000795882016-04-06 06:45:54
12016-04-04 13:38:56BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_OptikprivatAngebot$8,500controllimousine1997automatik2867er150,000km6benzinbmwnein2016-04-04 00:00:000710342016-04-06 14:45:08
22016-03-26 18:57:24Volkswagen_Golf_1.6_UnitedprivatAngebot$8,990testlimousine2009manuell102golf70,000km7benzinvolkswagennein2016-03-26 00:00:000353942016-04-06 20:15:37
32016-03-12 16:58:10Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...privatAngebot$4,350controlkleinwagen2007automatik71fortwo70,000km6benzinsmartnein2016-03-12 00:00:000337292016-03-15 03:16:28
42016-04-01 14:38:50Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...privatAngebot$1,350testkombi2003manuell0focus150,000km7benzinfordnein2016-04-01 00:00:000392182016-04-01 14:38:50
\n", "
" ], "text/plain": [ " dateCrawled name \\\n", "0 2016-03-26 17:47:46 Peugeot_807_160_NAVTECH_ON_BOARD \n", "1 2016-04-04 13:38:56 BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik \n", "2 2016-03-26 18:57:24 Volkswagen_Golf_1.6_United \n", "3 2016-03-12 16:58:10 Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan... \n", "4 2016-04-01 14:38:50 Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg... \n", "\n", " seller offerType price abtest vehicleType yearOfRegistration \\\n", "0 privat Angebot $5,000 control bus 2004 \n", "1 privat Angebot $8,500 control limousine 1997 \n", "2 privat Angebot $8,990 test limousine 2009 \n", "3 privat Angebot $4,350 control kleinwagen 2007 \n", "4 privat Angebot $1,350 test kombi 2003 \n", "\n", " gearbox powerPS model odometer monthOfRegistration fuelType \\\n", "0 manuell 158 andere 150,000km 3 lpg \n", "1 automatik 286 7er 150,000km 6 benzin \n", "2 manuell 102 golf 70,000km 7 benzin \n", "3 automatik 71 fortwo 70,000km 6 benzin \n", "4 manuell 0 focus 150,000km 7 benzin \n", "\n", " brand notRepairedDamage dateCreated nrOfPictures \\\n", "0 peugeot nein 2016-03-26 00:00:00 0 \n", "1 bmw nein 2016-04-04 00:00:00 0 \n", "2 volkswagen nein 2016-03-26 00:00:00 0 \n", "3 smart nein 2016-03-12 00:00:00 0 \n", "4 ford nein 2016-04-01 00:00:00 0 \n", "\n", " postalCode lastSeen \n", "0 79588 2016-04-06 06:45:54 \n", "1 71034 2016-04-06 14:45:08 \n", "2 35394 2016-04-06 20:15:37 \n", "3 33729 2016-03-15 03:16:28 \n", "4 39218 2016-04-01 14:38:50 " ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos.info()\n", "autos.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This information shows how the dataset contains 20 columns, most of which are strings. Some columns have null values, but none have more than ~20% null values.\n", "\n", "### Fixing the columns name" ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "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')\n" ] } ], "source": [ "print(autos.columns)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
date_crawlednameselleroffer_typepriceabtestvehicle_typeregistration_yeargearboxpower_psmodelodometerregistration_monthfuel_typebrandunrepaired_damagead_creatednr_of_picturespostal_codelast_seen
02016-03-26 17:47:46Peugeot_807_160_NAVTECH_ON_BOARDprivatAngebot$5,000controlbus2004manuell158andere150,000km3lpgpeugeotnein2016-03-26 00:00:000795882016-04-06 06:45:54
12016-04-04 13:38:56BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_OptikprivatAngebot$8,500controllimousine1997automatik2867er150,000km6benzinbmwnein2016-04-04 00:00:000710342016-04-06 14:45:08
22016-03-26 18:57:24Volkswagen_Golf_1.6_UnitedprivatAngebot$8,990testlimousine2009manuell102golf70,000km7benzinvolkswagennein2016-03-26 00:00:000353942016-04-06 20:15:37
\n", "
" ], "text/plain": [ " date_crawled name seller \\\n", "0 2016-03-26 17:47:46 Peugeot_807_160_NAVTECH_ON_BOARD privat \n", "1 2016-04-04 13:38:56 BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik privat \n", "2 2016-03-26 18:57:24 Volkswagen_Golf_1.6_United privat \n", "\n", " offer_type price abtest vehicle_type registration_year gearbox \\\n", "0 Angebot $5,000 control bus 2004 manuell \n", "1 Angebot $8,500 control limousine 1997 automatik \n", "2 Angebot $8,990 test limousine 2009 manuell \n", "\n", " power_ps model odometer registration_month fuel_type brand \\\n", "0 158 andere 150,000km 3 lpg peugeot \n", "1 286 7er 150,000km 6 benzin bmw \n", "2 102 golf 70,000km 7 benzin volkswagen \n", "\n", " unrepaired_damage ad_created nr_of_pictures postal_code \\\n", "0 nein 2016-03-26 00:00:00 0 79588 \n", "1 nein 2016-04-04 00:00:00 0 71034 \n", "2 nein 2016-03-26 00:00:00 0 35394 \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 " ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "new_col = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'abtest',\n", " 'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model',\n", " 'odometer', 'registration_month', 'fuel_type', 'brand',\n", " 'unrepaired_damage', 'ad_created', 'nr_of_pictures', 'postal_code',\n", " 'last_seen']\n", "autos.columns = new_col\n", "autos.head(3)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We have converted the column names from camelcase to snakecase and reword some of the column names based on the data dictionary to be more descriptive.\n", "\n", "## Data Exploration and Cleaning\n", "Now, we present some basic data exploration to determine what other cleaning tasks need to be done." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
date_crawlednameselleroffer_typepriceabtestvehicle_typeregistration_yeargearboxpower_psmodelodometerregistration_monthfuel_typebrandunrepaired_damagead_creatednr_of_picturespostal_codelast_seen
count5000050000500005000050000500004490550000.0000004732050000.000000472425000050000.0000004551850000401715000050000.050000.00000050000
unique482133875422235728NaN2NaN24513NaN740276NaNNaN39481
top2016-03-23 18:39:34Ford_FiestaprivatAngebot$0testlimousineNaNmanuellNaNgolf150,000kmNaNbenzinvolkswagennein2016-04-03 00:00:00NaNNaN2016-04-07 06:17:27
freq378499994999914212575612859NaN36993NaN402432424NaN3010710687352321946NaNNaN8
meanNaNNaNNaNNaNNaNNaNNaN2005.073280NaN116.355920NaNNaN5.723360NaNNaNNaNNaN0.050813.627300NaN
stdNaNNaNNaNNaNNaNNaNNaN105.712813NaN209.216627NaNNaN3.711984NaNNaNNaNNaN0.025779.747957NaN
minNaNNaNNaNNaNNaNNaNNaN1000.000000NaN0.000000NaNNaN0.000000NaNNaNNaNNaN0.01067.000000NaN
25%NaNNaNNaNNaNNaNNaNNaN1999.000000NaN70.000000NaNNaN3.000000NaNNaNNaNNaN0.030451.000000NaN
50%NaNNaNNaNNaNNaNNaNNaN2003.000000NaN105.000000NaNNaN6.000000NaNNaNNaNNaN0.049577.000000NaN
75%NaNNaNNaNNaNNaNNaNNaN2008.000000NaN150.000000NaNNaN9.000000NaNNaNNaNNaN0.071540.000000NaN
maxNaNNaNNaNNaNNaNNaNNaN9999.000000NaN17700.000000NaNNaN12.000000NaNNaNNaNNaN0.099998.000000NaN
\n", "
" ], "text/plain": [ " date_crawled name seller offer_type price abtest \\\n", "count 50000 50000 50000 50000 50000 50000 \n", "unique 48213 38754 2 2 2357 2 \n", "top 2016-03-23 18:39:34 Ford_Fiesta privat Angebot $0 test \n", "freq 3 78 49999 49999 1421 25756 \n", "mean NaN NaN NaN NaN NaN NaN \n", "std NaN NaN NaN NaN NaN NaN \n", "min NaN NaN NaN NaN NaN NaN \n", "25% NaN NaN NaN NaN NaN NaN \n", "50% NaN NaN NaN NaN NaN NaN \n", "75% NaN NaN NaN NaN NaN NaN \n", "max NaN NaN NaN NaN NaN NaN \n", "\n", " vehicle_type registration_year gearbox power_ps model \\\n", "count 44905 50000.000000 47320 50000.000000 47242 \n", "unique 8 NaN 2 NaN 245 \n", "top limousine NaN manuell NaN golf \n", "freq 12859 NaN 36993 NaN 4024 \n", "mean NaN 2005.073280 NaN 116.355920 NaN \n", "std NaN 105.712813 NaN 209.216627 NaN \n", "min NaN 1000.000000 NaN 0.000000 NaN \n", "25% NaN 1999.000000 NaN 70.000000 NaN \n", "50% NaN 2003.000000 NaN 105.000000 NaN \n", "75% NaN 2008.000000 NaN 150.000000 NaN \n", "max NaN 9999.000000 NaN 17700.000000 NaN \n", "\n", " odometer registration_month fuel_type brand unrepaired_damage \\\n", "count 50000 50000.000000 45518 50000 40171 \n", "unique 13 NaN 7 40 2 \n", "top 150,000km NaN benzin volkswagen nein \n", "freq 32424 NaN 30107 10687 35232 \n", "mean NaN 5.723360 NaN NaN NaN \n", "std NaN 3.711984 NaN NaN NaN \n", "min NaN 0.000000 NaN NaN NaN \n", "25% NaN 3.000000 NaN NaN NaN \n", "50% NaN 6.000000 NaN NaN NaN \n", "75% NaN 9.000000 NaN NaN NaN \n", "max NaN 12.000000 NaN NaN NaN \n", "\n", " ad_created nr_of_pictures postal_code last_seen \n", "count 50000 50000.0 50000.000000 50000 \n", "unique 76 NaN NaN 39481 \n", "top 2016-04-03 00:00:00 NaN NaN 2016-04-07 06:17:27 \n", "freq 1946 NaN NaN 8 \n", "mean NaN 0.0 50813.627300 NaN \n", "std NaN 0.0 25779.747957 NaN \n", "min NaN 0.0 1067.000000 NaN \n", "25% NaN 0.0 30451.000000 NaN \n", "50% NaN 0.0 49577.000000 NaN \n", "75% NaN 0.0 71540.000000 NaN \n", "max NaN 0.0 99998.000000 NaN " ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos.describe(include = 'all')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "These results show how seller, offer_type and nr_of_pictures columns have mostly one value. Then, they will be dropped.\n", "\n", "The columns gearbox and unrepaired_damage need more investigation since they present only two different values, with one of them is presented in most of the dataset. Moreover, these columns present missing values.\n", "\n", "On the other hand, the columns price and odometer are numeric values stored as text. We will convert these values to the numeric type.\n", "\n", "#### Droping columns with mostly one value\n", "Let's drop seller, offer_type and nr_of_pictures columns because they have mostly one value." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "collapsed": false, "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
date_crawlednamepriceabtestvehicle_typeregistration_yeargearboxpower_psmodelodometerregistration_monthfuel_typebrandunrepaired_damagead_createdpostal_codelast_seen
02016-03-26 17:47:46Peugeot_807_160_NAVTECH_ON_BOARD$5,000controlbus2004manuell158andere150,000km3lpgpeugeotnein2016-03-26 00:00:00795882016-04-06 06:45:54
12016-04-04 13:38:56BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik$8,500controllimousine1997automatik2867er150,000km6benzinbmwnein2016-04-04 00:00:00710342016-04-06 14:45:08
22016-03-26 18:57:24Volkswagen_Golf_1.6_United$8,990testlimousine2009manuell102golf70,000km7benzinvolkswagennein2016-03-26 00:00:00353942016-04-06 20:15:37
32016-03-12 16:58:10Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan...$4,350controlkleinwagen2007automatik71fortwo70,000km6benzinsmartnein2016-03-12 00:00:00337292016-03-15 03:16:28
42016-04-01 14:38:50Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg...$1,350testkombi2003manuell0focus150,000km7benzinfordnein2016-04-01 00:00:00392182016-04-01 14:38:50
\n", "
" ], "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", " price abtest vehicle_type registration_year gearbox power_ps \\\n", "0 $5,000 control bus 2004 manuell 158 \n", "1 $8,500 control limousine 1997 automatik 286 \n", "2 $8,990 test limousine 2009 manuell 102 \n", "3 $4,350 control kleinwagen 2007 automatik 71 \n", "4 $1,350 test kombi 2003 manuell 0 \n", "\n", " model odometer registration_month fuel_type brand \\\n", "0 andere 150,000km 3 lpg peugeot \n", "1 7er 150,000km 6 benzin bmw \n", "2 golf 70,000km 7 benzin volkswagen \n", "3 fortwo 70,000km 6 benzin smart \n", "4 focus 150,000km 7 benzin ford \n", "\n", " unrepaired_damage ad_created postal_code last_seen \n", "0 nein 2016-03-26 00:00:00 79588 2016-04-06 06:45:54 \n", "1 nein 2016-04-04 00:00:00 71034 2016-04-06 14:45:08 \n", "2 nein 2016-03-26 00:00:00 35394 2016-04-06 20:15:37 \n", "3 nein 2016-03-12 00:00:00 33729 2016-03-15 03:16:28 \n", "4 nein 2016-04-01 00:00:00 39218 2016-04-01 14:38:50 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos.drop([\"offer_type\",\"seller\",\"nr_of_pictures\"],axis=1).head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Cleaning columns price and odometer\n", "Let's clean and convert price and odometer" ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "collapsed": false }, "outputs": [], "source": [ "autos[\"price\"] = autos[\"price\"].str.replace('$','')\n", "autos[\"price\"] = autos[\"price\"].str.replace(',','')\n", "autos[\"price\"] = autos[\"price\"].astype(int)" ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "collapsed": false }, "outputs": [], "source": [ "autos[\"odometer\"] = autos[\"odometer\"].str.replace('km','')\n", "autos[\"odometer\"] = autos[\"odometer\"].str.replace(',','')\n", "autos[\"odometer\"] = autos[\"odometer\"].astype(int)\n", "autos.rename({\"odometer\": \"odometer_km\"}, axis=1, inplace=True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We continue exploring the data, specifically looking for data that doesn't look right. We'll start by analyzing the odometer_km and price columns using minimum and maximum values and look for any values that look unrealistically high or low (outliers) that we might want to remove." ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Shape odometer_km (13,)\n", "Describe the odometer_km:\n", "count 50000.000000\n", "mean 125732.700000\n", "std 40042.211706\n", "min 5000.000000\n", "25% 125000.000000\n", "50% 150000.000000\n", "75% 150000.000000\n", "max 150000.000000\n", "Name: odometer_km, dtype: float64\n" ] }, { "data": { "text/plain": [ "150000 32424\n", "125000 5170\n", "100000 2169\n", "90000 1757\n", "80000 1436\n", "70000 1230\n", "60000 1164\n", "50000 1027\n", "5000 967\n", "40000 819\n", "30000 789\n", "20000 784\n", "10000 264\n", "Name: odometer_km, dtype: int64" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "print(\"Shape odometer_km\", autos[\"odometer_km\"].unique().shape)\n", "print(\"Describe the odometer_km:\")\n", "print(autos[\"odometer_km\"].describe())\n", "autos[\"odometer_km\"].value_counts()" ] }, { "cell_type": "code", "execution_count": 10, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Shape price (2357,)\n", "Describe the price:\n", "count 5.000000e+04\n", "mean 9.840044e+03\n", "std 4.811044e+05\n", "min 0.000000e+00\n", "25% 1.100000e+03\n", "50% 2.950000e+03\n", "75% 7.200000e+03\n", "max 1.000000e+08\n", "Name: price, dtype: float64\n", "0 1421\n", "1 156\n", "2 3\n", "3 1\n", "5 2\n", "8 1\n", "9 1\n", "10 7\n", "11 2\n", "12 3\n", "13 2\n", "14 1\n", "15 2\n", "17 3\n", "18 1\n", "20 4\n", "25 5\n", "29 1\n", "30 7\n", "35 1\n", "Name: price, dtype: int64\n", "197000 1\n", "198000 1\n", "220000 1\n", "250000 1\n", "259000 1\n", "265000 1\n", "295000 1\n", "299000 1\n", "345000 1\n", "350000 1\n", "999990 1\n", "999999 2\n", "1234566 1\n", "1300000 1\n", "3890000 1\n", "10000000 1\n", "11111111 2\n", "12345678 3\n", "27322222 1\n", "99999999 1\n", "Name: price, dtype: int64\n" ] } ], "source": [ "print(\"Shape price\", autos[\"price\"].unique().shape)\n", "print(\"Describe the price:\")\n", "print(autos[\"price\"].describe())\n", "autos_price_sorted = autos[\"price\"].value_counts().sort_index(ascending = True)\n", "print(autos_price_sorted.head(20))\n", "print(autos_price_sorted.tail(20))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The values from the odometer_km seem to be logical. However, we will remove the rows with a price equal to 0 and the ones with values higher than 350000, since the values in the price column show a jump from 350000 to 999999." ] }, { "cell_type": "code", "execution_count": 11, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
priceregistration_yearpower_psodometer_kmregistration_monthnr_of_picturespostal_code
count48565.00000048565.00000048565.00000048565.00000048565.00000048565.048565.000000
mean5888.9355912004.755421117.197158125770.1019255.7822510.050975.745207
std9059.85475488.643887200.64961839788.6368043.6855950.025746.968398
min1.0000001000.0000000.0000005000.0000000.0000000.01067.000000
25%1200.0000001999.00000071.000000125000.0000003.0000000.030657.000000
50%3000.0000002004.000000107.000000150000.0000006.0000000.049716.000000
75%7490.0000002008.000000150.000000150000.0000009.0000000.071665.000000
max350000.0000009999.00000017700.000000150000.00000012.0000000.099998.000000
\n", "
" ], "text/plain": [ " price registration_year power_ps odometer_km \\\n", "count 48565.000000 48565.000000 48565.000000 48565.000000 \n", "mean 5888.935591 2004.755421 117.197158 125770.101925 \n", "std 9059.854754 88.643887 200.649618 39788.636804 \n", "min 1.000000 1000.000000 0.000000 5000.000000 \n", "25% 1200.000000 1999.000000 71.000000 125000.000000 \n", "50% 3000.000000 2004.000000 107.000000 150000.000000 \n", "75% 7490.000000 2008.000000 150.000000 150000.000000 \n", "max 350000.000000 9999.000000 17700.000000 150000.000000 \n", "\n", " registration_month nr_of_pictures postal_code \n", "count 48565.000000 48565.0 48565.000000 \n", "mean 5.782251 0.0 50975.745207 \n", "std 3.685595 0.0 25746.968398 \n", "min 0.000000 0.0 1067.000000 \n", "25% 3.000000 0.0 30657.000000 \n", "50% 6.000000 0.0 49716.000000 \n", "75% 9.000000 0.0 71665.000000 \n", "max 12.000000 0.0 99998.000000 " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos = autos[autos['price'].between(1,350000)]\n", "autos.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The new statistical description of this column show how the average price is \talmost 5900, where most of the values are between 1200 and 7490.\n", "\n", "### Exploring date columns \n", "First, we will understand how the values in the three string columns are formatted. These columns all represent full timestamp values:" ] }, { "cell_type": "code", "execution_count": 12, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
date_crawledad_createdlast_seen
02016-03-26 17:47:462016-03-26 00:00:002016-04-06 06:45:54
12016-04-04 13:38:562016-04-04 00:00:002016-04-06 14:45:08
22016-03-26 18:57:242016-03-26 00:00:002016-04-06 20:15:37
32016-03-12 16:58:102016-03-12 00:00:002016-03-15 03:16:28
42016-04-01 14:38:502016-04-01 00:00:002016-04-01 14:38:50
\n", "
" ], "text/plain": [ " date_crawled ad_created last_seen\n", "0 2016-03-26 17:47:46 2016-03-26 00:00:00 2016-04-06 06:45:54\n", "1 2016-04-04 13:38:56 2016-04-04 00:00:00 2016-04-06 14:45:08\n", "2 2016-03-26 18:57:24 2016-03-26 00:00:00 2016-04-06 20:15:37\n", "3 2016-03-12 16:58:10 2016-03-12 00:00:00 2016-03-15 03:16:28\n", "4 2016-04-01 14:38:50 2016-04-01 00:00:00 2016-04-01 14:38:50" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos[['date_crawled','ad_created','last_seen']][0:5]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To understand the date range, we can extract just the first 10 characters that represent date values. Then, we calculate the values's distribution in these columns.\n", "#### Exploring date_crawled column" ] }, { "cell_type": "code", "execution_count": 13, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/plain": [ "2016-03-05 0.025327\n", "2016-03-06 0.014043\n", "2016-03-07 0.036014\n", "2016-03-08 0.033296\n", "2016-03-09 0.033090\n", "2016-03-10 0.032184\n", "2016-03-11 0.032575\n", "2016-03-12 0.036920\n", "2016-03-13 0.015670\n", "2016-03-14 0.036549\n", "2016-03-15 0.034284\n", "2016-03-16 0.029610\n", "2016-03-17 0.031628\n", "2016-03-18 0.012911\n", "2016-03-19 0.034778\n", "2016-03-20 0.037887\n", "2016-03-21 0.037373\n", "2016-03-22 0.032987\n", "2016-03-23 0.032225\n", "2016-03-24 0.029342\n", "2016-03-25 0.031607\n", "2016-03-26 0.032204\n", "2016-03-27 0.031092\n", "2016-03-28 0.034860\n", "2016-03-29 0.034099\n", "2016-03-30 0.033687\n", "2016-03-31 0.031834\n", "2016-04-01 0.033687\n", "2016-04-02 0.035478\n", "2016-04-03 0.038608\n", "2016-04-04 0.036487\n", "2016-04-05 0.013096\n", "2016-04-06 0.003171\n", "2016-04-07 0.001400\n", "Name: date_crawled, dtype: float64" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "date_crawled = autos['date_crawled'].str[:10]\n", "date_crawled.value_counts(normalize=True, dropna=False).sort_index(ascending = True)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The results show how the distribution of the values in the date_crawled column are balanced in most of the dates. However, the last three dates the number of ad crawled decrease respect to the rest of the dates, since the percentage in these days is less than 1.5%. Notice that, in march there are three exceptional dates, where the percentege is also less than 1.5.\n", "\n", "#### Exploring ad_created column" ] }, { "cell_type": "code", "execution_count": 14, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2015-06-11 0.000021\n", "2015-08-10 0.000021\n", "2015-09-09 0.000021\n", "2015-11-10 0.000021\n", "2015-12-05 0.000021\n", "2015-12-30 0.000021\n", "2016-01-03 0.000021\n", "2016-01-07 0.000021\n", "2016-01-10 0.000041\n", "2016-01-13 0.000021\n", "Name: ad_created, dtype: float64\n", "2016-03-04 0.001483\n", "2016-03-05 0.022897\n", "2016-03-06 0.015320\n", "2016-03-07 0.034737\n", "2016-03-08 0.033316\n", "2016-03-09 0.033151\n", "2016-03-10 0.031895\n", "2016-03-11 0.032904\n", "2016-03-12 0.036755\n", "2016-03-13 0.017008\n", "2016-03-14 0.035190\n", "2016-03-15 0.034016\n", "2016-03-16 0.030125\n", "2016-03-17 0.031278\n", "2016-03-18 0.013590\n", "2016-03-19 0.033687\n", "2016-03-20 0.037949\n", "2016-03-21 0.037579\n", "2016-03-22 0.032801\n", "2016-03-23 0.032060\n", "2016-03-24 0.029280\n", "2016-03-25 0.031751\n", "2016-03-26 0.032266\n", "2016-03-27 0.030989\n", "2016-03-28 0.034984\n", "2016-03-29 0.034037\n", "2016-03-30 0.033501\n", "2016-03-31 0.031875\n", "2016-04-01 0.033687\n", "2016-04-02 0.035149\n", "2016-04-03 0.038855\n", "2016-04-04 0.036858\n", "2016-04-05 0.011819\n", "2016-04-06 0.003253\n", "2016-04-07 0.001256\n", "Name: ad_created, dtype: float64\n" ] }, { "data": { "text/plain": [ "count 76.000000\n", "mean 0.013158\n", "std 0.015956\n", "min 0.000021\n", "25% 0.000021\n", "50% 0.000144\n", "75% 0.032112\n", "max 0.038855\n", "Name: ad_created, dtype: float64" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "ad_created = autos['ad_created'].str[:10]\n", "value_counts_ad_created = ad_created.value_counts(normalize=True, dropna=False).sort_index(ascending = True)\n", "print(value_counts_ad_created.head(10))\n", "print(value_counts_ad_created.tail(35))\n", "value_counts_ad_created.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The first dates stored in the dataset are at the end of 2015, where the number of ad created was less than 1%, almost one ad created most of the dates. However, at the begining of March, 2016, this number was increased, reaching 3% until almost the begining of april.\n", "#### Exploring last_seen column" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "2016-03-05 0.001071\n", "2016-03-06 0.004324\n", "2016-03-07 0.005395\n", "2016-03-08 0.007413\n", "2016-03-09 0.009595\n", "2016-03-10 0.010666\n", "2016-03-11 0.012375\n", "2016-03-12 0.023783\n", "2016-03-13 0.008895\n", "2016-03-14 0.012602\n", "2016-03-15 0.015876\n", "2016-03-16 0.016452\n", "2016-03-17 0.028086\n", "2016-03-18 0.007351\n", "2016-03-19 0.015834\n", "2016-03-20 0.020653\n", "2016-03-21 0.020632\n", "2016-03-22 0.021373\n", "2016-03-23 0.018532\n", "2016-03-24 0.019767\n", "2016-03-25 0.019211\n", "2016-03-26 0.016802\n", "2016-03-27 0.015649\n", "2016-03-28 0.020859\n", "2016-03-29 0.022341\n", "2016-03-30 0.024771\n", "2016-03-31 0.023783\n", "2016-04-01 0.022794\n", "2016-04-02 0.024915\n", "2016-04-03 0.025203\n", "2016-04-04 0.024483\n", "2016-04-05 0.124761\n", "2016-04-06 0.221806\n", "2016-04-07 0.131947\n", "Name: last_seen, dtype: float64\n" ] }, { "data": { "text/plain": [ "count 34.000000\n", "mean 0.029412\n", "std 0.043718\n", "min 0.001071\n", "25% 0.012432\n", "50% 0.019489\n", "75% 0.023783\n", "max 0.221806\n", "Name: last_seen, dtype: float64" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "last_seen = autos['last_seen'].str[:10]\n", "value_counts_last_seen = last_seen.value_counts(normalize=True, dropna=False).sort_index(ascending = True)\n", "print(value_counts_last_seen)\n", "value_counts_last_seen.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The results show how the distribution of the values in the last_seen column are balanced in most of the dates, with values around one and two percentage. However, these values increased in the last three dates, since the percentage in these days was higher than 12%.\n", "\n", "#### Exploring registration_year column" ] }, { "cell_type": "code", "execution_count": 16, "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": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos[\"registration_year\"].describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "This description shows how the registration_year column present odd values, since the minimum value is 1000 and the maximum value is 9999.\n", "\n", "Let's count the number of listings with cars that fall outside the 1900 - 2016 interval and see if it's safe to remove those rows entirely, or if we need more custom logic." ] }, { "cell_type": "code", "execution_count": 17, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Number of autos with a year registration less than 1900: 5\n", "Number of autos with a year registration higuer than 2016: 1879\n", "\n", "Values count of the registration year less than 1940:\n", "1000 1\n", "1001 1\n", "1111 1\n", "1800 2\n", "1910 5\n", "1927 1\n", "1929 1\n", "1931 1\n", "1934 2\n", "1937 4\n", "Name: registration_year, dtype: int64\n" ] } ], "source": [ "autos_year_1900 = autos[autos['registration_year'] < 1900]\n", "print(\"Number of autos with a year registration less than 1900:\",autos_year_1900.shape[0])\n", "autos_year_2016 = autos[autos['registration_year'] > 2016]\n", "print(\"Number of autos with a year registration higuer than 2016:\",autos_year_2016.shape[0])\n", "print(\"\\nValues count of the registration year less than 1940:\")\n", "print(autos.loc[(autos[\"registration_year\"] < 1940),\"registration_year\"].value_counts().sort_index(ascending = True).head(10))\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The number of autos with a registration year less than 1900 is 5. These rows will be removed since they present an invalid registration year. \n", "\n", "Now, let's analyse the autos with a registration year 1910, which is the next year stored after 1900." ] }, { "cell_type": "code", "execution_count": 18, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
date_crawlednameselleroffer_typepriceabtestvehicle_typeregistration_yeargearboxpower_psmodelodometer_kmregistration_monthfuel_typebrandunrepaired_damagead_creatednr_of_picturespostal_codelast_seen
36792016-04-04 00:36:17Suche_AutoprivatAngebot1testNaN1910NaN0NaN50000NaNsonstige_autosNaN2016-04-04 00:00:000402392016-04-04 07:49:15
226592016-03-14 08:51:18Opel_Corsa_BprivatAngebot500testNaN1910NaN0corsa1500000NaNopelNaN2016-03-14 00:00:000523932016-04-03 07:53:55
286932016-03-22 17:48:41Renault_TwingoprivatAngebot599controlkleinwagen1910manuell0NaN50000benzinrenaultNaN2016-03-22 00:00:000703762016-04-06 09:16:59
307812016-03-25 13:47:46Opel_Calibra_V6_DTM_Bausatz_1:24privatAngebot30testNaN1910NaN0calibra1000000NaNopelNaN2016-03-25 00:00:000476382016-03-26 23:46:29
451572016-03-11 22:37:01MotorhaubeprivatAngebot15controlNaN1910NaN0NaN50000NaNtrabantNaN2016-03-11 00:00:000904912016-03-25 11:18:57
\n", "
" ], "text/plain": [ " date_crawled name seller \\\n", "3679 2016-04-04 00:36:17 Suche_Auto privat \n", "22659 2016-03-14 08:51:18 Opel_Corsa_B privat \n", "28693 2016-03-22 17:48:41 Renault_Twingo privat \n", "30781 2016-03-25 13:47:46 Opel_Calibra_V6_DTM_Bausatz_1:24 privat \n", "45157 2016-03-11 22:37:01 Motorhaube privat \n", "\n", " offer_type price abtest vehicle_type registration_year gearbox \\\n", "3679 Angebot 1 test NaN 1910 NaN \n", "22659 Angebot 500 test NaN 1910 NaN \n", "28693 Angebot 599 control kleinwagen 1910 manuell \n", "30781 Angebot 30 test NaN 1910 NaN \n", "45157 Angebot 15 control NaN 1910 NaN \n", "\n", " power_ps model odometer_km registration_month fuel_type \\\n", "3679 0 NaN 5000 0 NaN \n", "22659 0 corsa 150000 0 NaN \n", "28693 0 NaN 5000 0 benzin \n", "30781 0 calibra 100000 0 NaN \n", "45157 0 NaN 5000 0 NaN \n", "\n", " brand unrepaired_damage ad_created nr_of_pictures \\\n", "3679 sonstige_autos NaN 2016-04-04 00:00:00 0 \n", "22659 opel NaN 2016-03-14 00:00:00 0 \n", "28693 renault NaN 2016-03-22 00:00:00 0 \n", "30781 opel NaN 2016-03-25 00:00:00 0 \n", "45157 trabant NaN 2016-03-11 00:00:00 0 \n", "\n", " postal_code last_seen \n", "3679 40239 2016-04-04 07:49:15 \n", "22659 52393 2016-04-03 07:53:55 \n", "28693 70376 2016-04-06 09:16:59 \n", "30781 47638 2016-03-26 23:46:29 \n", "45157 90491 2016-03-25 11:18:57 " ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos[autos[\"registration_year\"] == 1910]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "There are five rows that contain 1910 in the registration_year column. Most of them present missing values for the vehicle_type, gearbox, fuel_type, unrepaired_damage and model column. Moreover, the registration_month column contain 0 value for all the rows, which represent a wrong value. The price column also present odd values, range between 1 and 599. Finally, with a very low probability, ebay could sell a car registered in 1910. For this reason, these rows will be removed.\n", "\n", "Let's analyse the autos with a registration year between 1927 and 1940, which with some probabiltiy may be ebay sell a car with these registration years." ] }, { "cell_type": "code", "execution_count": 19, "metadata": { "collapsed": false, "scrolled": true }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Percent missing for the autos with registration year between 1927-1940: \n", " date_crawled 0.000000\n", "name 0.000000\n", "seller 0.000000\n", "offer_type 0.000000\n", "price 0.000000\n", "abtest 0.000000\n", "vehicle_type 0.000000\n", "registration_year 0.000000\n", "gearbox 27.272727\n", "power_ps 0.000000\n", "model 45.454545\n", "odometer_km 0.000000\n", "registration_month 0.000000\n", "fuel_type 18.181818\n", "brand 0.000000\n", "unrepaired_damage 27.272727\n", "ad_created 0.000000\n", "nr_of_pictures 0.000000\n", "postal_code 0.000000\n", "last_seen 0.000000\n", "dtype: float64\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
priceregistration_yearpower_psodometer_kmregistration_monthnr_of_picturespostal_code
count11.00000011.00000011.00000011.00000011.00000011.011.000000
mean13021.8181821934.54545524.27272730454.5454554.2727270.045506.000000
std9479.7012803.95888028.34463248138.0590313.1333980.029825.147376
min3000.0000001927.0000000.0000005000.0000000.0000000.03042.000000
25%5900.0000001932.5000000.0000005000.0000001.0000000.016629.000000
50%8990.0000001937.00000015.0000005000.0000005.0000000.053804.000000
75%21000.0000001937.00000039.50000030000.0000007.0000000.065843.500000
max27500.0000001939.00000085.000000150000.0000008.0000000.090489.000000
\n", "
" ], "text/plain": [ " price registration_year power_ps odometer_km \\\n", "count 11.000000 11.000000 11.000000 11.000000 \n", "mean 13021.818182 1934.545455 24.272727 30454.545455 \n", "std 9479.701280 3.958880 28.344632 48138.059031 \n", "min 3000.000000 1927.000000 0.000000 5000.000000 \n", "25% 5900.000000 1932.500000 0.000000 5000.000000 \n", "50% 8990.000000 1937.000000 15.000000 5000.000000 \n", "75% 21000.000000 1937.000000 39.500000 30000.000000 \n", "max 27500.000000 1939.000000 85.000000 150000.000000 \n", "\n", " registration_month nr_of_pictures postal_code \n", "count 11.000000 11.0 11.000000 \n", "mean 4.272727 0.0 45506.000000 \n", "std 3.133398 0.0 29825.147376 \n", "min 0.000000 0.0 3042.000000 \n", "25% 1.000000 0.0 16629.000000 \n", "50% 5.000000 0.0 53804.000000 \n", "75% 7.000000 0.0 65843.500000 \n", "max 8.000000 0.0 90489.000000 " ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos_1927_1940 = autos[autos[\"registration_year\"].between(1927,1940)]\n", "percent_missing = autos_1927_1940.isnull().sum() * 100 / len(autos_1927_1940)\n", "print(\"Percent missing for the autos with registration year between 1927-1940: \\n\", percent_missing)\n", "autos_1927_1940.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The results show how the prices in these rows are quite logic and how these rows present few missing values for almost all the columns. \n", "\n", "Then, the lowest acceptable value will be 1927 for the registration year column and the highest will be 2016 because a car can't be first registered after the listing was seen, then any vehicle with a registration year above 2016 is definitely inaccurate." ] }, { "cell_type": "code", "execution_count": 20, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1927 0.000021\n", "1929 0.000021\n", "1931 0.000021\n", "1934 0.000043\n", "1937 0.000086\n", "1938 0.000021\n", "1939 0.000021\n", "1941 0.000043\n", "1943 0.000021\n", "1948 0.000021\n", "1950 0.000064\n", "1951 0.000043\n", "1952 0.000021\n", "1953 0.000021\n", "1954 0.000043\n", "1955 0.000043\n", "1956 0.000086\n", "1957 0.000043\n", "1958 0.000086\n", "1959 0.000129\n", "1960 0.000493\n", "1961 0.000129\n", "1962 0.000086\n", "1963 0.000171\n", "1964 0.000257\n", "1965 0.000364\n", "1966 0.000471\n", "1967 0.000557\n", "1968 0.000557\n", "1969 0.000407\n", "Name: registration_year, dtype: float64\n", "1997 0.041799\n", "1998 0.050626\n", "1999 0.062066\n", "2000 0.067615\n", "2001 0.056474\n", "2002 0.053261\n", "2003 0.057824\n", "2004 0.057910\n", "2005 0.062902\n", "2006 0.057203\n", "2007 0.048783\n", "2008 0.047455\n", "2009 0.044670\n", "2010 0.034043\n", "2011 0.034772\n", "2012 0.028066\n", "2013 0.017204\n", "2014 0.014204\n", "2015 0.008398\n", "2016 0.026138\n", "Name: registration_year, dtype: float64\n" ] }, { "data": { "text/plain": [ "count 77.000000\n", "mean 0.012987\n", "std 0.020765\n", "min 0.000021\n", "25% 0.000086\n", "50% 0.000728\n", "75% 0.017204\n", "max 0.067615\n", "Name: registration_year, dtype: float64" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "autos = autos[autos['registration_year'].between(1927,2016)]\n", "value_counts_autos_reg_year = autos['registration_year'].value_counts(normalize=True).sort_index(ascending = True)\n", "print(value_counts_autos_reg_year.head(30))\n", "print(value_counts_autos_reg_year.tail(20))\n", "value_counts_autos_reg_year.describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We can see how in the early decades of the 1900s, a few number of autos with these registration years where stored, and after the 60s the percentage increase. Moreover, the highest percentage (3-6%) are represented at the end of the 90s until 2011.\n", "\n", "### Exploring variations across different car brands\n", "\n", "When working with data on cars, it's natural to explore variations across different car brands. We will explore the unique values in the brand column, and decide on which brands we want to aggregate by. " ] }, { "cell_type": "code", "execution_count": 21, "metadata": { "collapsed": false }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "volkswagen 0.211286\n", "bmw 0.110057\n", "opel 0.107550\n", "mercedes_benz 0.096474\n", "audi 0.086576\n", "ford 0.069907\n", "renault 0.047133\n", "peugeot 0.029844\n", "fiat 0.025645\n", "seat 0.018275\n", "Name: brand, dtype: float64\n" ] }, { "data": { "text/plain": [ "count 46676\n", "unique 40\n", "top volkswagen\n", "freq 9862\n", "Name: brand, dtype: object" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "value_counts_brand = autos['brand'].value_counts(normalize=True)\n", "print(value_counts_brand.head(10))\n", "autos['brand'].describe()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "We select for a futher analysis the brands that have a percentage of the total values higher than 5%. The description of the brand column show that it present 40 unique values, where the top one is volkswagen." ] }, { "cell_type": "code", "execution_count": 22, "metadata": { "collapsed": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
mean_pricemean_mileage_km
audi9336.69129157.39
bmw8332.82132572.51
ford3749.47124266.01
mercedes_benz8628.45130788.36
opel2976.32129311.75
volkswagen5402.41128707.16
\n", "
" ], "text/plain": [ " mean_price mean_mileage_km\n", "audi 9336.69 129157.39\n", "bmw 8332.82 132572.51\n", "ford 3749.47 124266.01\n", "mercedes_benz 8628.45 130788.36\n", "opel 2976.32 129311.75\n", "volkswagen 5402.41 128707.16" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "brands_selected = value_counts_brand[value_counts_brand > 0.05]\n", "brand_mean_prices = {}\n", "brands_mean_mileage = {}\n", "for b in brands_selected.index.values:\n", " prices_brand = autos.loc[autos[\"brand\"] == b, \"price\"]\n", " mileage_brand = autos.loc[autos[\"brand\"] == b, \"odometer_km\"]\n", " brand_mean_prices[b] = round(prices_brand.sum() / len(prices_brand), 2)\n", " brands_mean_mileage[b] = round(mileage_brand.sum() / len(mileage_brand), 2)\n", "\n", "bm_price_series = pd.Series(brand_mean_prices)\n", "bm_mileage_series = pd.Series(brands_mean_mileage)\n", "df_brands = pd.DataFrame(bm_price_series, columns=['mean_price'])\n", "df_brands['mean_mileage_km'] = pd.Series(brands_mean_mileage)\n", "df_brands" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The above results show that the most expensive brands are Audi, BMW and Mercedes Benz and the less one Ford and Opel. Finally, Volkswagen is between them. The average mileage is quite similar for all the brands. However, note that the BMW and Mercedes Benz present the highest values for this column. " ] }, { "cell_type": "code", "execution_count": null, "metadata": { "collapsed": false }, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.4.3" } }, "nbformat": 4, "nbformat_minor": 2 }