**In this guided project, we'll work with a dataset of used cars from eBay Kleinanzeigen, a classifieds section of the German eBay website.

The dataset was originally scraped and uploaded to Kaggle. We've made a few modifications from the original dataset that was uploaded to Kaggle:

We sampled 50,000 data points from the full dataset, to ensure your code runs quickly in our hosted environment We dirtied the dataset a bit to more closely resemble what you would expect from a scraped dataset (the version uploaded to Kaggle was cleaned to be easier to work with) The data dictionary provided with data is as follows:

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. The aim of this project is to clean the data and analyze the included used car listings. You'll also become familiar with some of the unique benefits jupyter notebook provides for pandas.

Let's start by importing the libraries we need and reading the dataset into pandas.

In [1]:
import pandas as pd
import numpy as np
In [2]:
autos = pd.read_csv('autos.csv', encoding='Windows-1252')
autos.info()
autos.head()
<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
Out[2]:
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
In [3]:
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 [4]:
autos.columns = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',
       'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model',
       'odometer_km', 'registration_month', 'fuel_type', 'brand',
       'unrepaired_damage', 'ad_created', 'num_photos', 'postal_code',
       'last_seen']
autos.head()
Out[4]:
date_crawled name seller offer_type price ab_test vehicle_type registration_year gearbox power_ps model odometer_km registration_month fuel_type brand unrepaired_damage ad_created num_photos postal_code last_seen
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
In [5]:
autos.describe(include='all')
Out[5]:
date_crawled name seller offer_type price ab_test vehicle_type registration_year gearbox power_ps model odometer_km registration_month fuel_type brand unrepaired_damage ad_created num_photos postal_code last_seen
count 50000 50000 50000 50000 50000 50000 44905 50000.000000 47320 50000.000000 47242 50000 50000.000000 45518 50000 40171 50000 50000.0 50000.000000 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-30 19:48:02 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.073280 NaN 116.355920 NaN NaN 5.723360 NaN NaN NaN NaN 0.0 50813.627300 NaN
std NaN NaN NaN NaN NaN NaN NaN 105.712813 NaN 209.216627 NaN NaN 3.711984 NaN NaN NaN NaN 0.0 25779.747957 NaN
min NaN NaN NaN NaN NaN NaN NaN 1000.000000 NaN 0.000000 NaN NaN 0.000000 NaN NaN NaN NaN 0.0 1067.000000 NaN
25% NaN NaN NaN NaN NaN NaN NaN 1999.000000 NaN 70.000000 NaN NaN 3.000000 NaN NaN NaN NaN 0.0 30451.000000 NaN
50% NaN NaN NaN NaN NaN NaN NaN 2003.000000 NaN 105.000000 NaN NaN 6.000000 NaN NaN NaN NaN 0.0 49577.000000 NaN
75% NaN NaN NaN NaN NaN NaN NaN 2008.000000 NaN 150.000000 NaN NaN 9.000000 NaN NaN NaN NaN 0.0 71540.000000 NaN
max NaN NaN NaN NaN NaN NaN NaN 9999.000000 NaN 17700.000000 NaN NaN 12.000000 NaN NaN NaN NaN 0.0 99998.000000 NaN
In [6]:
autos["num_photos"].value_counts()
Out[6]:
0    50000
Name: num_photos, dtype: int64
In [7]:
autos = autos.drop(["num_photos", "seller" , "offer_type"], axis=1)
In [8]:
autos["price"] = (autos["price"]
                  .str.replace("$","")
                  .str.replace(",","")
                  .astype(int)
                 )
autos["price"].head()
Out[8]:
0    5000
1    8500
2    8990
3    4350
4    1350
Name: price, dtype: int64
In [9]:
autos["odometer_km"] = (autos["odometer_km"]
                     .str.replace("km","")
                     .str.replace(",","")
                     .astype(float)
                    )
In [10]:
autos["odometer_km"].value_counts()
Out[10]:
150000.0    32424
125000.0     5170
100000.0     2169
90000.0      1757
80000.0      1436
70000.0      1230
60000.0      1164
50000.0      1027
5000.0        967
40000.0       819
30000.0       789
20000.0       784
10000.0       264
Name: odometer_km, dtype: int64
In [11]:
print(autos["price"].unique().shape)
print(autos["price"].describe())
autos["price"].value_counts()
(2357,)
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
Out[11]:
0         1421
500        781
1500       734
2500       643
1000       639
1200       639
600        531
800        498
3500       498
2000       460
999        434
750        433
900        420
650        419
850        410
700        395
4500       394
300        384
2200       382
950        379
1100       376
1300       371
3000       365
550        356
1800       355
5500       340
1250       335
350        335
1600       327
1999       322
          ... 
46200        1
29600        1
13480        1
21700        1
7373         1
3279         1
4286         1
188          1
17830        1
9130         1
910          1
238          1
2671         1
69900        1
151990       1
2479         1
4510         1
86500        1
47499        1
16998        1
27299        1
41850        1
4780         1
686          1
6495         1
20790        1
8970         1
846          1
2895         1
33980        1
Name: price, Length: 2357, dtype: int64
In [12]:
autos["price"].value_counts().sort_index(ascending=False).head(20)
Out[12]:
99999999    1
27322222    1
12345678    3
11111111    2
10000000    1
3890000     1
1300000     1
1234566     1
999999      2
999990      1
350000      1
345000      1
299000      1
295000      1
265000      1
259000      1
250000      1
220000      1
198000      1
197000      1
Name: price, dtype: int64
In [13]:
autos["price"].value_counts().sort_index(ascending=True).head(20)
Out[13]:
0     1421
1      156
2        3
3        1
5        2
8        1
9        1
10       7
11       2
12       3
13       2
14       1
15       2
17       3
18       1
20       4
25       5
29       1
30       7
35       1
Name: price, dtype: int64
In [14]:
autos = autos[autos["price"].between(1,350001)]
autos["price"].describe()
Out[14]:
count     48565.000000
mean       5888.935591
std        9059.854754
min           1.000000
25%        1200.000000
50%        3000.000000
75%        7490.000000
max      350000.000000
Name: price, dtype: float64
In [15]:
autos.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 48565 entries, 0 to 49999
Data columns (total 17 columns):
date_crawled          48565 non-null object
name                  48565 non-null object
price                 48565 non-null int64
ab_test               48565 non-null object
vehicle_type          43979 non-null object
registration_year     48565 non-null int64
gearbox               46222 non-null object
power_ps              48565 non-null int64
model                 46107 non-null object
odometer_km           48565 non-null float64
registration_month    48565 non-null int64
fuel_type             44535 non-null object
brand                 48565 non-null object
unrepaired_damage     39464 non-null object
ad_created            48565 non-null object
postal_code           48565 non-null int64
last_seen             48565 non-null object
dtypes: float64(1), int64(5), object(11)
memory usage: 6.7+ MB
In [16]:
autos[['date_crawled','ad_created','last_seen']][0:5]
Out[16]:
date_crawled ad_created last_seen
0 2016-03-26 17:47:46 2016-03-26 00:00:00 2016-04-06 06:45:54
1 2016-04-04 13:38:56 2016-04-04 00:00:00 2016-04-06 14:45:08
2 2016-03-26 18:57:24 2016-03-26 00:00:00 2016-04-06 20:15:37
3 2016-03-12 16:58:10 2016-03-12 00:00:00 2016-03-15 03:16:28
4 2016-04-01 14:38:50 2016-04-01 00:00:00 2016-04-01 14:38:50
In [17]:
autos['date_crawled'].str[:10].value_counts(normalize=True, dropna=False).sort_index(ascending=True)
Out[17]:
2016-03-05    0.025327
2016-03-06    0.014043
2016-03-07    0.036014
2016-03-08    0.033296
2016-03-09    0.033090
2016-03-10    0.032184
2016-03-11    0.032575
2016-03-12    0.036920
2016-03-13    0.015670
2016-03-14    0.036549
2016-03-15    0.034284
2016-03-16    0.029610
2016-03-17    0.031628
2016-03-18    0.012911
2016-03-19    0.034778
2016-03-20    0.037887
2016-03-21    0.037373
2016-03-22    0.032987
2016-03-23    0.032225
2016-03-24    0.029342
2016-03-25    0.031607
2016-03-26    0.032204
2016-03-27    0.031092
2016-03-28    0.034860
2016-03-29    0.034099
2016-03-30    0.033687
2016-03-31    0.031834
2016-04-01    0.033687
2016-04-02    0.035478
2016-04-03    0.038608
2016-04-04    0.036487
2016-04-05    0.013096
2016-04-06    0.003171
2016-04-07    0.001400
Name: date_crawled, dtype: float64
In [18]:
autos['ad_created'].str[:10].value_counts(normalize=True, dropna=False).sort_index(ascending=True)
Out[18]:
2015-06-11    0.000021
2015-08-10    0.000021
2015-09-09    0.000021
2015-11-10    0.000021
2015-12-05    0.000021
2015-12-30    0.000021
2016-01-03    0.000021
2016-01-07    0.000021
2016-01-10    0.000041
2016-01-13    0.000021
2016-01-14    0.000021
2016-01-16    0.000021
2016-01-22    0.000021
2016-01-27    0.000062
2016-01-29    0.000021
2016-02-01    0.000021
2016-02-02    0.000041
2016-02-05    0.000041
2016-02-07    0.000021
2016-02-08    0.000021
2016-02-09    0.000021
2016-02-11    0.000021
2016-02-12    0.000041
2016-02-14    0.000041
2016-02-16    0.000021
2016-02-17    0.000021
2016-02-18    0.000041
2016-02-19    0.000062
2016-02-20    0.000041
2016-02-21    0.000062
                ...   
2016-03-09    0.033151
2016-03-10    0.031895
2016-03-11    0.032904
2016-03-12    0.036755
2016-03-13    0.017008
2016-03-14    0.035190
2016-03-15    0.034016
2016-03-16    0.030125
2016-03-17    0.031278
2016-03-18    0.013590
2016-03-19    0.033687
2016-03-20    0.037949
2016-03-21    0.037579
2016-03-22    0.032801
2016-03-23    0.032060
2016-03-24    0.029280
2016-03-25    0.031751
2016-03-26    0.032266
2016-03-27    0.030989
2016-03-28    0.034984
2016-03-29    0.034037
2016-03-30    0.033501
2016-03-31    0.031875
2016-04-01    0.033687
2016-04-02    0.035149
2016-04-03    0.038855
2016-04-04    0.036858
2016-04-05    0.011819
2016-04-06    0.003253
2016-04-07    0.001256
Name: ad_created, Length: 76, dtype: float64
In [19]:
autos['last_seen'].str[:10].value_counts(normalize=True, dropna=False).sort_index(ascending=True)
Out[19]:
2016-03-05    0.001071
2016-03-06    0.004324
2016-03-07    0.005395
2016-03-08    0.007413
2016-03-09    0.009595
2016-03-10    0.010666
2016-03-11    0.012375
2016-03-12    0.023783
2016-03-13    0.008895
2016-03-14    0.012602
2016-03-15    0.015876
2016-03-16    0.016452
2016-03-17    0.028086
2016-03-18    0.007351
2016-03-19    0.015834
2016-03-20    0.020653
2016-03-21    0.020632
2016-03-22    0.021373
2016-03-23    0.018532
2016-03-24    0.019767
2016-03-25    0.019211
2016-03-26    0.016802
2016-03-27    0.015649
2016-03-28    0.020859
2016-03-29    0.022341
2016-03-30    0.024771
2016-03-31    0.023783
2016-04-01    0.022794
2016-04-02    0.024915
2016-04-03    0.025203
2016-04-04    0.024483
2016-04-05    0.124761
2016-04-06    0.221806
2016-04-07    0.131947
Name: last_seen, dtype: float64
In [20]:
#Dealing with Incorrect Registration Year Data
In [21]:
autos = autos[autos['registration_year'].between(1901, 2020)]
autos.describe()
Out[21]:
price registration_year power_ps odometer_km registration_month postal_code
count 48545.000000 48545.000000 48545.000000 48545.000000 48545.000000 48545.000000
mean 5888.431764 2003.461510 117.156123 125819.136883 5.783603 50975.125121
std 9059.345265 7.566196 200.385362 39723.379171 3.684938 25748.944248
min 1.000000 1910.000000 0.000000 5000.000000 0.000000 1067.000000
25% 1200.000000 1999.000000 71.000000 125000.000000 3.000000 30657.000000
50% 3000.000000 2004.000000 107.000000 150000.000000 6.000000 49716.000000
75% 7490.000000 2008.000000 150.000000 150000.000000 9.000000 71665.000000
max 350000.000000 2019.000000 17700.000000 150000.000000 12.000000 99998.000000
In [22]:
autos["brand"].unique().shape
Out[22]:
(40,)
In [23]:
brands = autos['brand'].value_counts(normalize=True).sort_values(ascending=False)[0:20].index
brands
Out[23]:
Index(['volkswagen', 'opel', 'bmw', 'mercedes_benz', 'audi', 'ford', 'renault',
       'peugeot', 'fiat', 'seat', 'skoda', 'nissan', 'mazda', 'smart',
       'citroen', 'toyota', 'hyundai', 'sonstige_autos', 'volvo', 'mini'],
      dtype='object')
In [24]:
price_by_brand = {}

for brand in brands:
    price_mean = autos.loc[autos['brand'] == brand, 'price'].mean()
    price_by_brand[brand] = price_mean
price_by_brand    

# Audi, BMW and Mercedes Benz are more expensive
# Ford and Opel are less expensive
# Volkswagen is in between
Out[24]:
{'audi': 9212.9306621881,
 'bmw': 8261.382442169132,
 'citroen': 3756.07299270073,
 'fiat': 2793.8700475435817,
 'ford': 3728.4121821407452,
 'hyundai': 5371.792960662526,
 'mazda': 4059.059539918809,
 'mercedes_benz': 8526.623225806452,
 'mini': 10541.566985645934,
 'nissan': 4669.3859649122805,
 'opel': 2941.4664391353813,
 'peugeot': 3065.611888111888,
 'renault': 2431.195698924731,
 'seat': 4320.168661588684,
 'skoda': 6353.544871794872,
 'smart': 3518.102305475504,
 'sonstige_autos': 12208.673076923076,
 'toyota': 5148.0032733224225,
 'volkswagen': 5333.1962055948115,
 'volvo': 4866.993166287016}
In [25]:
bmp_series = pd.Series(price_by_brand)
print(bmp_series)
audi               9212.930662
bmw                8261.382442
citroen            3756.072993
fiat               2793.870048
ford               3728.412182
hyundai            5371.792961
mazda              4059.059540
mercedes_benz      8526.623226
mini              10541.566986
nissan             4669.385965
opel               2941.466439
peugeot            3065.611888
renault            2431.195699
seat               4320.168662
skoda              6353.544872
smart              3518.102305
sonstige_autos    12208.673077
toyota             5148.003273
volkswagen         5333.196206
volvo              4866.993166
dtype: float64
In [26]:
df = pd.DataFrame(bmp_series, columns=['mean_price'])
df
Out[26]:
mean_price
audi 9212.930662
bmw 8261.382442
citroen 3756.072993
fiat 2793.870048
ford 3728.412182
hyundai 5371.792961
mazda 4059.059540
mercedes_benz 8526.623226
mini 10541.566986
nissan 4669.385965
opel 2941.466439
peugeot 3065.611888
renault 2431.195699
seat 4320.168662
skoda 6353.544872
smart 3518.102305
sonstige_autos 12208.673077
toyota 5148.003273
volkswagen 5333.196206
volvo 4866.993166
In [28]:
#storing aggregate data in a dataframe
mileage_by_brand = {}

for brand in brands:
    mileage_mean = autos.loc[autos['brand'] == brand, 'odometer_km'].mean()
    mileage_by_brand[brand] = mileage_mean

mileage_by_brand
Out[28]:
{'audi': 129492.56238003839,
 'bmw': 132682.97307546454,
 'citroen': 120160.58394160584,
 'fiat': 117567.35340729002,
 'ford': 124349.49733885274,
 'hyundai': 106718.4265010352,
 'mazda': 124871.44790257105,
 'mercedes_benz': 130848.3870967742,
 'mini': 88899.52153110047,
 'nissan': 118711.20107962213,
 'opel': 129452.02882062951,
 'peugeot': 127356.64335664336,
 'renault': 128062.36559139784,
 'seat': 121768.22633297062,
 'skoda': 110993.58974358975,
 'smart': 100511.52737752162,
 'sonstige_autos': 90715.81196581197,
 'toyota': 116219.31260229132,
 'volkswagen': 128955.570612719,
 'volvo': 138337.1298405467}
In [29]:
mbb_series = pd.Series(mileage_by_brand)
d = {'mean_price' : bmp_series, 'mean_mileage' : mbb_series}
dff = pd.DataFrame(data=d)
dff
Out[29]:
mean_mileage mean_price
audi 129492.562380 9212.930662
bmw 132682.973075 8261.382442
citroen 120160.583942 3756.072993
fiat 117567.353407 2793.870048
ford 124349.497339 3728.412182
hyundai 106718.426501 5371.792961
mazda 124871.447903 4059.059540
mercedes_benz 130848.387097 8526.623226
mini 88899.521531 10541.566986
nissan 118711.201080 4669.385965
opel 129452.028821 2941.466439
peugeot 127356.643357 3065.611888
renault 128062.365591 2431.195699
seat 121768.226333 4320.168662
skoda 110993.589744 6353.544872
smart 100511.527378 3518.102305
sonstige_autos 90715.811966 12208.673077
toyota 116219.312602 5148.003273
volkswagen 128955.570613 5333.196206
volvo 138337.129841 4866.993166
In [ ]: