Exploring Ebay Car Sales Data

The dataset was scraped from eBay Kleinanzeigen, a classifieds section of the German eBay website.

Data dictionary:

  • dateCrawled - When this ad was first crawled. All field-values are taken from this date.
  • name - Name of the car.
  • seller - Whether the seller is private or a dealer.
  • offerType - The type of listing
  • price - The price on the ad to sell the car.
  • abtest - Whether the listing is included in an A/B test.
  • vehicleType - The vehicle Type.
  • yearOfRegistration - The year in which the car was first registered.
  • gearbox - The transmission type.
  • powerPS - The power of the car in PS.
  • model - The car model name.
  • kilometer - How many kilometers the car has driven.
  • monthOfRegistration - The month in which the car was first registered.
  • fuelType - What type of fuel the car uses.
  • brand - The brand of the car.
  • notRepairedDamage - If the car has a damage which is not yet repaired.
  • dateCreated - The date on which the eBay listing was created.
  • nrOfPictures - The number of pictures in the ad.
  • postalCode - The postal code for the location of the vehicle.
  • lastSeenOnline - When the crawler saw this ad last online.

Goal: Clean the data and analyze the included used car listings.

In [82]:
# import pandas and numpy and read in our autos dataset
import numpy as np
import pandas as pd

autos = pd.read_csv('autos.csv', encoding = 'Latin-1')
In [83]:
autos
Out[83]:
dateCrawled name seller offerType price abtest vehicleType yearOfRegistration gearbox powerPS model odometer monthOfRegistration fuelType brand notRepairedDamage dateCreated nrOfPictures postalCode lastSeen
0 2016-03-26 17:47:46 Peugeot_807_160_NAVTECH_ON_BOARD privat Angebot $5,000 control bus 2004 manuell 158 andere 150,000km 3 lpg peugeot nein 2016-03-26 00:00:00 0 79588 2016-04-06 06:45:54
1 2016-04-04 13:38:56 BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik privat Angebot $8,500 control limousine 1997 automatik 286 7er 150,000km 6 benzin bmw nein 2016-04-04 00:00:00 0 71034 2016-04-06 14:45:08
2 2016-03-26 18:57:24 Volkswagen_Golf_1.6_United privat Angebot $8,990 test limousine 2009 manuell 102 golf 70,000km 7 benzin volkswagen nein 2016-03-26 00:00:00 0 35394 2016-04-06 20:15:37
3 2016-03-12 16:58:10 Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan... privat Angebot $4,350 control kleinwagen 2007 automatik 71 fortwo 70,000km 6 benzin smart nein 2016-03-12 00:00:00 0 33729 2016-03-15 03:16:28
4 2016-04-01 14:38:50 Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg... privat Angebot $1,350 test kombi 2003 manuell 0 focus 150,000km 7 benzin ford nein 2016-04-01 00:00:00 0 39218 2016-04-01 14:38:50
5 2016-03-21 13:47:45 Chrysler_Grand_Voyager_2.8_CRD_Aut.Limited_Sto... privat Angebot $7,900 test bus 2006 automatik 150 voyager 150,000km 4 diesel chrysler NaN 2016-03-21 00:00:00 0 22962 2016-04-06 09:45:21
6 2016-03-20 17:55:21 VW_Golf_III_GT_Special_Electronic_Green_Metall... privat Angebot $300 test limousine 1995 manuell 90 golf 150,000km 8 benzin volkswagen NaN 2016-03-20 00:00:00 0 31535 2016-03-23 02:48:59
7 2016-03-16 18:55:19 Golf_IV_1.9_TDI_90PS privat Angebot $1,990 control limousine 1998 manuell 90 golf 150,000km 12 diesel volkswagen nein 2016-03-16 00:00:00 0 53474 2016-04-07 03:17:32
8 2016-03-22 16:51:34 Seat_Arosa privat Angebot $250 test NaN 2000 manuell 0 arosa 150,000km 10 NaN seat nein 2016-03-22 00:00:00 0 7426 2016-03-26 18:18:10
9 2016-03-16 13:47:02 Renault_Megane_Scenic_1.6e_RT_Klimaanlage privat Angebot $590 control bus 1997 manuell 90 megane 150,000km 7 benzin renault nein 2016-03-16 00:00:00 0 15749 2016-04-06 10:46:35
10 2016-03-15 01:41:36 VW_Golf_Tuning_in_siber/grau privat Angebot $999 test NaN 2017 manuell 90 NaN 150,000km 4 benzin volkswagen nein 2016-03-14 00:00:00 0 86157 2016-04-07 03:16:21
11 2016-03-16 18:45:34 Mercedes_A140_Motorschaden privat Angebot $350 control NaN 2000 NaN 0 NaN 150,000km 0 benzin mercedes_benz NaN 2016-03-16 00:00:00 0 17498 2016-03-16 18:45:34
12 2016-03-31 19:48:22 Smart_smart_fortwo_coupe_softouch_pure_MHD_Pan... privat Angebot $5,299 control kleinwagen 2010 automatik 71 fortwo 50,000km 9 benzin smart nein 2016-03-31 00:00:00 0 34590 2016-04-06 14:17:52
13 2016-03-23 10:48:32 Audi_A3_1.6_tuning privat Angebot $1,350 control limousine 1999 manuell 101 a3 150,000km 11 benzin audi nein 2016-03-23 00:00:00 0 12043 2016-04-01 14:17:13
14 2016-03-23 11:50:46 Renault_Clio_3__Dynamique_1.2__16_V;_viele_Ver... privat Angebot $3,999 test kleinwagen 2007 manuell 75 clio 150,000km 9 benzin renault NaN 2016-03-23 00:00:00 0 81737 2016-04-01 15:46:47
15 2016-04-01 12:06:20 Corvette_C3_Coupe_T_Top_Crossfire_Injection privat Angebot $18,900 test coupe 1982 automatik 203 NaN 80,000km 6 benzin sonstige_autos nein 2016-04-01 00:00:00 0 61276 2016-04-02 21:10:48
16 2016-03-16 14:59:02 Opel_Vectra_B_Kombi privat Angebot $350 test kombi 1999 manuell 101 vectra 150,000km 5 benzin opel nein 2016-03-16 00:00:00 0 57299 2016-03-18 05:29:37
17 2016-03-29 11:46:22 Volkswagen_Scirocco_2_G60 privat Angebot $5,500 test coupe 1990 manuell 205 scirocco 150,000km 6 benzin volkswagen nein 2016-03-29 00:00:00 0 74821 2016-04-05 20:46:26
18 2016-03-26 19:57:44 Verkaufen_mein_bmw_e36_320_i_touring privat Angebot $300 control bus 1995 manuell 150 3er 150,000km 0 benzin bmw NaN 2016-03-26 00:00:00 0 54329 2016-04-02 12:16:41
19 2016-03-17 13:36:21 mazda_tribute_2.0_mit_gas_und_tuev_neu_2018 privat Angebot $4,150 control suv 2004 manuell 124 andere 150,000km 2 lpg mazda nein 2016-03-17 00:00:00 0 40878 2016-03-17 14:45:58
20 2016-03-05 19:57:31 Audi_A4_Avant_1.9_TDI_*6_Gang*AHK*Klimatronik*... privat Angebot $3,500 test kombi 2003 manuell 131 a4 150,000km 5 diesel audi NaN 2016-03-05 00:00:00 0 53913 2016-03-07 05:46:46
21 2016-03-06 19:07:10 Porsche_911_Carrera_4S_Cabrio privat Angebot $41,500 test cabrio 2004 manuell 320 911 150,000km 4 benzin porsche nein 2016-03-06 00:00:00 0 65428 2016-04-05 23:46:19
22 2016-03-28 20:50:54 MINI_Cooper_S_Cabrio privat Angebot $25,450 control cabrio 2015 manuell 184 cooper 10,000km 1 benzin mini nein 2016-03-28 00:00:00 0 44789 2016-04-01 06:45:30
23 2016-03-10 19:55:34 Peugeot_Boxer_2_2_HDi_120_Ps_9_Sitzer_inkl_Klima privat Angebot $7,999 control bus 2010 manuell 120 NaN 150,000km 2 diesel peugeot nein 2016-03-10 00:00:00 0 30900 2016-03-17 08:45:17
24 2016-04-03 11:57:02 BMW_535i_xDrive_Sport_Aut. privat Angebot $48,500 control limousine 2014 automatik 306 5er 30,000km 12 benzin bmw nein 2016-04-03 00:00:00 0 22547 2016-04-07 13:16:50
25 2016-03-21 21:56:18 Ford_escort_kombi_an_bastler_mit_ghia_ausstattung privat Angebot $90 control kombi 1996 manuell 116 NaN 150,000km 4 benzin ford ja 2016-03-21 00:00:00 0 27574 2016-04-01 05:16:49
26 2016-04-03 22:46:28 Volkswagen_Polo_Fox privat Angebot $777 control kleinwagen 1992 manuell 54 polo 125,000km 2 benzin volkswagen nein 2016-04-03 00:00:00 0 38110 2016-04-05 23:46:48
27 2016-03-27 18:45:01 Hat_einer_Ahnung_mit_Ford_Galaxy_HILFE privat Angebot $0 control NaN 2005 NaN 0 NaN 150,000km 0 NaN ford NaN 2016-03-27 00:00:00 0 66701 2016-03-27 18:45:01
28 2016-03-19 21:56:19 MINI_Cooper_D privat Angebot $5,250 control kleinwagen 2007 manuell 110 cooper 150,000km 7 diesel mini ja 2016-03-19 00:00:00 0 15745 2016-04-07 14:58:48
29 2016-04-02 12:45:44 Mercedes_Benz_E_320_T_CDI_Avantgarde_DPF7_Sitz... privat Angebot $4,999 test kombi 2004 automatik 204 e_klasse 150,000km 10 diesel mercedes_benz nein 2016-04-02 00:00:00 0 47638 2016-04-02 12:45:44
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
49970 2016-03-21 22:47:37 c4_Grand_Picasso_mit_Automatik_Leder_Navi_Temp... privat Angebot $15,800 control bus 2010 automatik 136 c4 60,000km 4 diesel citroen nein 2016-03-21 00:00:00 0 14947 2016-04-07 04:17:34
49971 2016-03-29 14:54:12 W.Lupo_1.0 privat Angebot $950 test kleinwagen 2001 manuell 50 lupo 150,000km 4 benzin volkswagen nein 2016-03-29 00:00:00 0 65197 2016-03-29 20:41:51
49972 2016-03-26 22:25:23 Mercedes_Benz_Vito_115_CDI_Extralang_Aut. privat Angebot $3,300 control bus 2004 automatik 150 vito 150,000km 10 diesel mercedes_benz ja 2016-03-26 00:00:00 0 65326 2016-03-28 11:28:18
49973 2016-03-27 05:32:39 Mercedes_Benz_SLK_200_Kompressor privat Angebot $6,000 control cabrio 2004 manuell 163 slk 150,000km 11 benzin mercedes_benz nein 2016-03-27 00:00:00 0 53567 2016-03-27 08:25:24
49974 2016-03-20 10:52:31 Golf_1_Cabrio_Tuev_Neu_viele_Extras_alles_eing... privat Angebot $0 control cabrio 1983 manuell 70 golf 150,000km 2 benzin volkswagen nein 2016-03-20 00:00:00 0 8209 2016-03-27 19:48:16
49975 2016-03-27 20:51:39 Honda_Jazz_1.3_DSi_i_VTEC_IMA_CVT_Comfort privat Angebot $9,700 control kleinwagen 2012 automatik 88 jazz 100,000km 11 hybrid honda nein 2016-03-27 00:00:00 0 84385 2016-04-05 19:45:34
49976 2016-03-19 18:56:05 Audi_80_Avant_2.6_E__Vollausstattung!!_Einziga... privat Angebot $5,900 test kombi 1992 automatik 150 80 150,000km 12 benzin audi nein 2016-03-19 00:00:00 0 36100 2016-04-07 06:16:44
49977 2016-03-31 18:37:18 Mercedes_Benz_C200_Cdi_W203 privat Angebot $5,500 control limousine 2003 manuell 116 c_klasse 150,000km 2 diesel mercedes_benz nein 2016-03-31 00:00:00 0 33739 2016-04-06 12:16:11
49978 2016-04-04 10:37:14 Mercedes_Benz_E_200_Classic privat Angebot $900 control limousine 1996 automatik 136 e_klasse 150,000km 9 benzin mercedes_benz ja 2016-04-04 00:00:00 0 24405 2016-04-06 12:44:20
49979 2016-03-20 18:38:40 Volkswagen_Polo_1.6_TDI_Style privat Angebot $11,000 test kleinwagen 2011 manuell 90 polo 70,000km 11 diesel volkswagen nein 2016-03-20 00:00:00 0 48455 2016-04-07 01:45:12
49980 2016-03-12 10:55:54 Ford_Escort_Turnier_16V privat Angebot $400 control kombi 1995 manuell 105 escort 125,000km 3 benzin ford NaN 2016-03-12 00:00:00 0 56218 2016-04-06 17:16:49
49981 2016-03-15 09:38:21 Opel_Astra_Kombi_mit_Anhaengerkupplung privat Angebot $2,000 control kombi 1998 manuell 115 astra 150,000km 12 benzin opel nein 2016-03-15 00:00:00 0 86859 2016-04-05 17:21:46
49982 2016-03-29 18:51:08 Skoda_Fabia_4_Tuerer_Bj:2004__85.000Tkm privat Angebot $1,950 control kleinwagen 2004 manuell 0 fabia 90,000km 7 benzin skoda NaN 2016-03-29 00:00:00 0 45884 2016-03-29 18:51:08
49983 2016-03-06 12:43:04 Ford_focus_99 privat Angebot $600 test kleinwagen 1999 manuell 101 focus 150,000km 4 benzin ford NaN 2016-03-06 00:00:00 0 52477 2016-03-09 06:16:08
49984 2016-03-31 22:48:48 Student_sucht_ein__Anfaengerauto___ab_2000_BJ_... privat Angebot $0 test NaN 2000 NaN 0 NaN 150,000km 0 NaN sonstige_autos NaN 2016-03-31 00:00:00 0 12103 2016-04-02 19:44:53
49985 2016-04-02 16:38:23 Verkaufe_meinen_vw_vento! privat Angebot $1,000 control NaN 1995 automatik 0 NaN 150,000km 0 benzin volkswagen NaN 2016-04-02 00:00:00 0 30900 2016-04-06 15:17:52
49986 2016-04-04 20:46:02 Chrysler_300C_3.0_CRD_DPF_Automatik_Voll_Ausst... privat Angebot $15,900 control limousine 2010 automatik 218 300c 125,000km 11 diesel chrysler nein 2016-04-04 00:00:00 0 73527 2016-04-06 23:16:00
49987 2016-03-22 20:47:27 Audi_A3_Limousine_2.0_TDI_DPF_Ambition__NAVI__... privat Angebot $21,990 control limousine 2013 manuell 150 a3 50,000km 11 diesel audi nein 2016-03-22 00:00:00 0 94362 2016-03-26 22:46:06
49988 2016-03-28 19:49:51 BMW_330_Ci privat Angebot $9,550 control coupe 2001 manuell 231 3er 150,000km 10 benzin bmw nein 2016-03-28 00:00:00 0 83646 2016-04-07 02:17:40
49989 2016-03-11 19:50:37 VW_Polo_zum_Ausschlachten_oder_Wiederaufbau privat Angebot $150 test kleinwagen 1997 manuell 0 polo 150,000km 5 benzin volkswagen ja 2016-03-11 00:00:00 0 21244 2016-03-12 10:17:55
49990 2016-03-21 19:54:19 Mercedes_Benz_A_200__BlueEFFICIENCY__Urban privat Angebot $17,500 test limousine 2012 manuell 156 a_klasse 30,000km 12 benzin mercedes_benz nein 2016-03-21 00:00:00 0 58239 2016-04-06 22:46:57
49991 2016-03-06 15:25:19 Kleinwagen privat Angebot $500 control NaN 2016 manuell 0 twingo 150,000km 0 benzin renault NaN 2016-03-06 00:00:00 0 61350 2016-03-06 18:24:19
49992 2016-03-10 19:37:38 Fiat_Grande_Punto_1.4_T_Jet_16V_Sport privat Angebot $4,800 control kleinwagen 2009 manuell 120 andere 125,000km 9 lpg fiat nein 2016-03-10 00:00:00 0 68642 2016-03-13 01:44:51
49993 2016-03-15 18:47:35 Audi_A3__1_8l__Silber;_schoenes_Fahrzeug privat Angebot $1,650 control kleinwagen 1997 manuell 0 NaN 150,000km 7 benzin audi NaN 2016-03-15 00:00:00 0 65203 2016-04-06 19:46:53
49994 2016-03-22 17:36:42 Audi_A6__S6__Avant_4.2_quattro_eventuell_Tausc... privat Angebot $5,000 control kombi 2001 automatik 299 a6 150,000km 1 benzin audi nein 2016-03-22 00:00:00 0 46537 2016-04-06 08:16:39
49995 2016-03-27 14:38:19 Audi_Q5_3.0_TDI_qu._S_tr.__Navi__Panorama__Xenon privat Angebot $24,900 control limousine 2011 automatik 239 q5 100,000km 1 diesel audi nein 2016-03-27 00:00:00 0 82131 2016-04-01 13:47:40
49996 2016-03-28 10:50:25 Opel_Astra_F_Cabrio_Bertone_Edition___TÜV_neu+... privat Angebot $1,980 control cabrio 1996 manuell 75 astra 150,000km 5 benzin opel nein 2016-03-28 00:00:00 0 44807 2016-04-02 14:18:02
49997 2016-04-02 14:44:48 Fiat_500_C_1.2_Dualogic_Lounge privat Angebot $13,200 test cabrio 2014 automatik 69 500 5,000km 11 benzin fiat nein 2016-04-02 00:00:00 0 73430 2016-04-04 11:47:27
49998 2016-03-08 19:25:42 Audi_A3_2.0_TDI_Sportback_Ambition privat Angebot $22,900 control kombi 2013 manuell 150 a3 40,000km 11 diesel audi nein 2016-03-08 00:00:00 0 35683 2016-04-05 16:45:07
49999 2016-03-14 00:42:12 Opel_Vectra_1.6_16V privat Angebot $1,250 control limousine 1996 manuell 101 vectra 150,000km 1 benzin opel nein 2016-03-13 00:00:00 0 45897 2016-04-06 21:18:48

50000 rows × 20 columns

In [84]:
autos.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 50000 entries, 0 to 49999
Data columns (total 20 columns):
dateCrawled            50000 non-null object
name                   50000 non-null object
seller                 50000 non-null object
offerType              50000 non-null object
price                  50000 non-null object
abtest                 50000 non-null object
vehicleType            44905 non-null object
yearOfRegistration     50000 non-null int64
gearbox                47320 non-null object
powerPS                50000 non-null int64
model                  47242 non-null object
odometer               50000 non-null object
monthOfRegistration    50000 non-null int64
fuelType               45518 non-null object
brand                  50000 non-null object
notRepairedDamage      40171 non-null object
dateCreated            50000 non-null object
nrOfPictures           50000 non-null int64
postalCode             50000 non-null int64
lastSeen               50000 non-null object
dtypes: int64(5), object(15)
memory usage: 7.6+ MB
In [85]:
autos.isnull().sum()
Out[85]:
dateCrawled               0
name                      0
seller                    0
offerType                 0
price                     0
abtest                    0
vehicleType            5095
yearOfRegistration        0
gearbox                2680
powerPS                   0
model                  2758
odometer                  0
monthOfRegistration       0
fuelType               4482
brand                     0
notRepairedDamage      9829
dateCreated               0
nrOfPictures              0
postalCode                0
lastSeen                  0
dtype: int64

After reading in the dataset we can see that there are some null (NaN) values present in our dataset that we might have to decide on deleting or keeping depending on our goal.

In [86]:
# change the column names from camelCase to snake_case 
autos.columns 

new_columns = ['ad_created', 'name', 'seller', 'offer_type', 'price', 
               'ab_test', 'vehicle_type', 'registration_year', 'gearbox', 
               'power_in_ps', 'model', 'odometer_km', 'registration_month',
               'fuel_type', 'brand', 'unrepaired_damage', 'date_created',
               'num_pictures', 'postal_code', 'last_seen']

autos.columns = new_columns

Above I copied the names of the columns of the autos dataset and manually changed names from camelCase to snake_case and then reassigned the column names to the dataset

In [87]:
autos.describe(include = 'all').head()
Out[87]:
ad_created name seller offer_type price ab_test vehicle_type registration_year gearbox power_in_ps model odometer_km registration_month fuel_type brand unrepaired_damage date_created num_pictures postal_code last_seen
count 50000 50000 50000 50000 50000 50000 44905 50000.00000 47320 50000.00000 47242 50000 50000.00000 45518 50000 40171 50000 50000.0 50000.0000 50000
unique 48213 38754 2 2 2357 2 8 NaN 2 NaN 245 13 NaN 7 40 2 76 NaN NaN 39481
top 2016-03-21 20:37:19 Ford_Fiesta privat Angebot $0 test limousine NaN manuell NaN golf 150,000km NaN benzin volkswagen nein 2016-04-03 00:00:00 NaN NaN 2016-04-07 06:17:27
freq 3 78 49999 49999 1421 25756 12859 NaN 36993 NaN 4024 32424 NaN 30107 10687 35232 1946 NaN NaN 8
mean NaN NaN NaN NaN NaN NaN NaN 2005.07328 NaN 116.35592 NaN NaN 5.72336 NaN NaN NaN NaN 0.0 50813.6273 NaN

From exploring the data closer, we can see that:

  1. There is only one (1) commercial seller, the rest are private
  2. There is only one (1) Gesuch offer type, the rest are Angebot
  3. Price column is present as string data, needs to be converted to numeric data
  4. Odometer column is present as a string data, needs to be converted to numeric data
  5. Number of pictures column is zero all around so it's best to remove it
In [88]:
# coverting price and odometer columns to numeric 
autos['price'] = (autos['price']
                  .str.replace('$', '')
                  .str.replace(',', '')
                  .astype(float)
                 )

autos['odometer_km'] = (autos['odometer_km']
                  .str.replace('km', '')
                  .str.replace(',', '')
                  .astype(float)
                 )
autos.head()
Out[88]:
ad_created name seller offer_type price ab_test vehicle_type registration_year gearbox power_in_ps model odometer_km registration_month fuel_type brand unrepaired_damage date_created num_pictures postal_code last_seen
0 2016-03-26 17:47:46 Peugeot_807_160_NAVTECH_ON_BOARD privat Angebot 5000.0 control bus 2004 manuell 158 andere 150000.0 3 lpg peugeot nein 2016-03-26 00:00:00 0 79588 2016-04-06 06:45:54
1 2016-04-04 13:38:56 BMW_740i_4_4_Liter_HAMANN_UMBAU_Mega_Optik privat Angebot 8500.0 control limousine 1997 automatik 286 7er 150000.0 6 benzin bmw nein 2016-04-04 00:00:00 0 71034 2016-04-06 14:45:08
2 2016-03-26 18:57:24 Volkswagen_Golf_1.6_United privat Angebot 8990.0 test limousine 2009 manuell 102 golf 70000.0 7 benzin volkswagen nein 2016-03-26 00:00:00 0 35394 2016-04-06 20:15:37
3 2016-03-12 16:58:10 Smart_smart_fortwo_coupe_softouch/F1/Klima/Pan... privat Angebot 4350.0 control kleinwagen 2007 automatik 71 fortwo 70000.0 6 benzin smart nein 2016-03-12 00:00:00 0 33729 2016-03-15 03:16:28
4 2016-04-01 14:38:50 Ford_Focus_1_6_Benzin_TÜV_neu_ist_sehr_gepfleg... privat Angebot 1350.0 test kombi 2003 manuell 0 focus 150000.0 7 benzin ford nein 2016-04-01 00:00:00 0 39218 2016-04-01 14:38:50
In [89]:
# start analyzing the odometer column to identify special cases
autos['odometer_km'].unique().shape
Out[89]:
(13,)
In [90]:
# we have 13 unique odometer readings 
autos['odometer_km'].describe()
Out[90]:
count     50000.000000
mean     125732.700000
std       40042.211706
min        5000.000000
25%      125000.000000
50%      150000.000000
75%      150000.000000
max      150000.000000
Name: odometer_km, dtype: float64
In [91]:
# describing how many cars are sold per km reading
print(autos['odometer_km']
      .value_counts()
      .sort_index(ascending = False)
     )
150000.0    32424
125000.0     5170
100000.0     2169
90000.0      1757
80000.0      1436
70000.0      1230
60000.0      1164
50000.0      1027
40000.0       819
30000.0       789
20000.0       784
10000.0       264
5000.0        967
Name: odometer_km, dtype: int64

By analyzing the odometer column, we can see that all the values seem reasonable, the minimum km is 5000 vs the max is at 150,000 km.

In [92]:
# now we analyze the price column 
autos['price'].unique().shape
Out[92]:
(2357,)
In [93]:
# we have 2,357 unique prices 
autos['price'].describe()
Out[93]:
count    5.000000e+04
mean     9.840044e+03
std      4.811044e+05
min      0.000000e+00
25%      1.100000e+03
50%      2.950000e+03
75%      7.200000e+03
max      1.000000e+08
Name: price, dtype: float64
In [94]:
print(autos['price']
      .value_counts()
      .sort_index(ascending = False)
      .head(10)
     )
99999999.0    1
27322222.0    1
12345678.0    3
11111111.0    2
10000000.0    1
3890000.0     1
1300000.0     1
1234566.0     1
999999.0      2
999990.0      1
Name: price, dtype: int64
In [95]:
print(autos['price']
      .value_counts()
      .sort_index(ascending = False)
      .tail(10)
     )
12.0       3
11.0       2
10.0       7
9.0        1
8.0        1
5.0        2
3.0        1
2.0        3
1.0      156
0.0     1421
Name: price, dtype: int64

While I am tempted to count the price zero as an outlier, I'm not sure if I should, there were 1421 cars sold at that value, not sure if this is actually free or just bad data. Same with the max value, I would count the top two as outliers because who spends upwards of 100 million on ebay. But, if we want to analyze data for 'reasonably priced non-free cars' we can elimiate the millions values and the free cars.

In [96]:
autos_non_free = (autos[autos['price']
                        .between(0.0, 1000000, 
                                 inclusive = False)]
                 )

print(autos_non_free['price']
      .value_counts()
      .sort_index(ascending = False)
      .head()
     )

print(autos_non_free['price']
      .value_counts()
      .sort_index(ascending = False)
      .tail()
     )
999999.0    2
999990.0    1
350000.0    1
345000.0    1
299000.0    1
Name: price, dtype: int64
8.0      1
5.0      2
3.0      1
2.0      3
1.0    156
Name: price, dtype: int64

Right now, the date_created, last_seen and ad_created columns are all identified as string (object) values by pandas. We need to conver them to numerical data so we can analyze it just like the registration_year and registration_month.

In [97]:
print(autos['date_created'].describe())
print('\n')
print(autos['last_seen'].describe())
print('\n')
print(autos['ad_created'].describe())
count                   50000
unique                     76
top       2016-04-03 00:00:00
freq                     1946
Name: date_created, dtype: object


count                   50000
unique                  39481
top       2016-04-07 06:17:27
freq                        8
Name: last_seen, dtype: object


count                   50000
unique                  48213
top       2016-03-21 20:37:19
freq                        3
Name: ad_created, dtype: object

Notice how the data is presented in full time stamp. The first 10 characters are the year, month and day. We should extract the date alone and generate the percentage of the frequency of each date sorted from earilest to latest date.

In [98]:
(autos['ad_created']
 .str[:10]
 .value_counts(normalize = True, dropna = False)
 .sort_index()
)
Out[98]:
2016-03-05    0.02538
2016-03-06    0.01394
2016-03-07    0.03596
2016-03-08    0.03330
2016-03-09    0.03322
2016-03-10    0.03212
2016-03-11    0.03248
2016-03-12    0.03678
2016-03-13    0.01556
2016-03-14    0.03662
2016-03-15    0.03398
2016-03-16    0.02950
2016-03-17    0.03152
2016-03-18    0.01306
2016-03-19    0.03490
2016-03-20    0.03782
2016-03-21    0.03752
2016-03-22    0.03294
2016-03-23    0.03238
2016-03-24    0.02910
2016-03-25    0.03174
2016-03-26    0.03248
2016-03-27    0.03104
2016-03-28    0.03484
2016-03-29    0.03418
2016-03-30    0.03362
2016-03-31    0.03192
2016-04-01    0.03380
2016-04-02    0.03540
2016-04-03    0.03868
2016-04-04    0.03652
2016-04-05    0.01310
2016-04-06    0.00318
2016-04-07    0.00142
Name: ad_created, dtype: float64
In [99]:
(autos['date_created']
 .str[:10]
 .value_counts(normalize = True, dropna = False)
 .sort_index()
)
Out[99]:
2015-06-11    0.00002
2015-08-10    0.00002
2015-09-09    0.00002
2015-11-10    0.00002
2015-12-05    0.00002
2015-12-30    0.00002
2016-01-03    0.00002
2016-01-07    0.00002
2016-01-10    0.00004
2016-01-13    0.00002
2016-01-14    0.00002
2016-01-16    0.00002
2016-01-22    0.00002
2016-01-27    0.00006
2016-01-29    0.00002
2016-02-01    0.00002
2016-02-02    0.00004
2016-02-05    0.00004
2016-02-07    0.00002
2016-02-08    0.00002
2016-02-09    0.00004
2016-02-11    0.00002
2016-02-12    0.00006
2016-02-14    0.00004
2016-02-16    0.00002
2016-02-17    0.00002
2016-02-18    0.00004
2016-02-19    0.00006
2016-02-20    0.00004
2016-02-21    0.00006
               ...   
2016-03-09    0.03324
2016-03-10    0.03186
2016-03-11    0.03278
2016-03-12    0.03662
2016-03-13    0.01692
2016-03-14    0.03522
2016-03-15    0.03374
2016-03-16    0.03000
2016-03-17    0.03120
2016-03-18    0.01372
2016-03-19    0.03384
2016-03-20    0.03786
2016-03-21    0.03772
2016-03-22    0.03280
2016-03-23    0.03218
2016-03-24    0.02908
2016-03-25    0.03188
2016-03-26    0.03256
2016-03-27    0.03090
2016-03-28    0.03496
2016-03-29    0.03414
2016-03-30    0.03344
2016-03-31    0.03192
2016-04-01    0.03380
2016-04-02    0.03508
2016-04-03    0.03892
2016-04-04    0.03688
2016-04-05    0.01184
2016-04-06    0.00326
2016-04-07    0.00128
Name: date_created, Length: 76, dtype: float64
In [100]:
(autos['last_seen']
 .str[:10]
 .value_counts(normalize = True, dropna = False)
 .sort_index()
)
Out[100]:
2016-03-05    0.00108
2016-03-06    0.00442
2016-03-07    0.00536
2016-03-08    0.00760
2016-03-09    0.00986
2016-03-10    0.01076
2016-03-11    0.01252
2016-03-12    0.02382
2016-03-13    0.00898
2016-03-14    0.01280
2016-03-15    0.01588
2016-03-16    0.01644
2016-03-17    0.02792
2016-03-18    0.00742
2016-03-19    0.01574
2016-03-20    0.02070
2016-03-21    0.02074
2016-03-22    0.02158
2016-03-23    0.01858
2016-03-24    0.01956
2016-03-25    0.01920
2016-03-26    0.01696
2016-03-27    0.01602
2016-03-28    0.02086
2016-03-29    0.02234
2016-03-30    0.02484
2016-03-31    0.02384
2016-04-01    0.02310
2016-04-02    0.02490
2016-04-03    0.02536
2016-04-04    0.02462
2016-04-05    0.12428
2016-04-06    0.22100
2016-04-07    0.13092
Name: last_seen, dtype: float64

After analyzing the columns, we can see that ad_created and last_seen columns only contain data for 2016 while the date_created column contains data for 2015 and 2016 with more unique values.

In [101]:
autos['registration_year'].describe()
Out[101]:
count    50000.000000
mean      2005.073280
std        105.712813
min       1000.000000
25%       1999.000000
50%       2003.000000
75%       2008.000000
max       9999.000000
Name: registration_year, dtype: float64

From exploring the registration_year column, we can see that the minumum and maximum years of registration are impossible, so we have to clean those

In [102]:
print(autos['registration_year']
 .value_counts(dropna = False)
 .sort_index()
 .head(8)
)

print(autos['registration_year']
 .value_counts(dropna = False)
 .sort_index()
 .tail(15)
)
1000    1
1001    1
1111    1
1500    1
1800    2
1910    9
1927    1
1929    1
Name: registration_year, dtype: int64
2016    1316
2017    1453
2018     492
2019       3
2800       1
4100       1
4500       1
4800       1
5000       4
5911       1
6200       1
8888       1
9000       2
9996       1
9999       4
Name: registration_year, dtype: int64

Comparing what we see above vs the earliest possible registration date of 1901, we can elimiate some values. As well as since the date of last_seen is 2016, it's impossible for a car to be first registered after 2016, so we remove the data after that as well.

In [103]:
autos = (autos[autos['registration_year']
                        .between(1901, 2016)]
                 )

print(autos['registration_year']
 .value_counts(normalize = True, dropna = False)
 .sort_index()
 .tail(8)
)
2009    0.043683
2010    0.033251
2011    0.034022
2012    0.027546
2013    0.016782
2014    0.013867
2015    0.008308
2016    0.027401
Name: registration_year, dtype: float64
In [104]:
top_6_brands = (autos['brand']
                 .value_counts()
                 .head(6)
                 .index
                )

print(top_6_brands)
Index(['volkswagen', 'bmw', 'opel', 'mercedes_benz', 'audi', 'ford'], dtype='object')

Right now wer're going to aggregate the data around the brand and price column. For this, I will choose the top 10 selling brands to compare the average price of each car sold. Before, we cleaned the price column but I didn't save it back into the DataFrame because I was unsure of which values actually matter so I'm just going to use that non_free_autos and compare it to the regular autos DataFrame

In [105]:
print(autos_non_free['price']
      .value_counts()
      .sort_index()
      .head()
     )
print('\n')
print(autos['price']
     .value_counts()
      .sort_index()
      .head()
     )
1.0    156
2.0      3
3.0      1
5.0      2
8.0      1
Name: price, dtype: int64


0.0    1335
1.0     150
2.0       2
3.0       1
5.0       2
Name: price, dtype: int64
In [106]:
price_per_brand = {}
# key is the brand name and price is the value 

for brand in top_6_brands:
    mean = autos.loc[autos['brand'] == brand, 'price'].mean()
    price_per_brand[brand] = mean
    
price_per_brand
Out[106]:
{'audi': 9093.65003615329,
 'bmw': 8334.645155185466,
 'ford': 7263.015811455847,
 'mercedes_benz': 30317.447816593885,
 'opel': 5252.61655437921,
 'volkswagen': 6516.457597173145}

On average, Mercedes-Benz, Audi and BMW have the highest average price.

In [109]:
km_per_brand = {}
# key is brand name, value is the mileage (kmage?)
for brand in top_6_brands:
    kmage = (autos.loc[
        autos['brand'] == brand, 'odometer_km']
             .mean()
            )
    km_per_brand[brand] = kmage
km_per_brand
Out[109]:
{'audi': 129287.78018799711,
 'bmw': 132434.70855412565,
 'ford': 124046.83770883054,
 'mercedes_benz': 130860.26200873363,
 'opel': 129227.14148219442,
 'volkswagen': 128730.36906164115}
In [119]:
sr = pd.Series(price_per_brand)
In [121]:
df = pd.DataFrame(sr, columns = ['mean_price'])
In [123]:
sr2 = pd.Series(km_per_brand)
df['mean_km'] = sr2
df
Out[123]:
mean_price mean_km
audi 9093.650036 129287.780188
bmw 8334.645155 132434.708554
ford 7263.015811 124046.837709
mercedes_benz 30317.447817 130860.262009
opel 5252.616554 129227.141482
volkswagen 6516.457597 128730.369062

So above we created a new Series from the price_per_brand dict, that allowed pandas to assign the keys as the index values, then we converted it to a DataFrame which allowed us to add another column and since pandas aligns the indecies, the values were aligned allowing our data to be easily explored.

From looking at the data, we can see that most of the brands had similar mileage values per price which shows no correlation between price and mileage, that means the price was most likely due to other factors such as model.