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. A few modifications from the original dataset that was uploaded to Kaggle have been made:
The aim of this project is to clean the data and analyze the included used car listings.
import numpy as np
import pandas as pd
#Let's read in the dataset
autos = pd.read_csv('autos.csv', encoding='Latin-1')
autos.info()
autos.head()
In the above few cells we read in the autos dataset into a dataframe:
Let's convert the column names from camelcase to snakecase and reword some of the column names based on the data dictionary to be more descriptive.
#Let's get the names of the columns as they are:
autos.columns
#We edit the columns'names to snakecase:
autos.columns = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',
'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model',
'odometer', 'registration_month', 'fuel_type', 'brand',
'unrepaired_damage', 'ad_created', 'n_pictures', 'postal_code',
'last_seen']
autos.head()
In the above few cells we cleaned the names of the columns in the dataframe so that they are in snakecase which would be easier to work with.
Now let's do some basic data exploration to determine what other cleaning tasks need to be done. Initially we will look for:
autos.describe(include='all')
autos['seller'].value_counts()
#We see that there is only one entry which isn't from a private person.
autos['offer_type'].value_counts()
#Only one entry differs.
autos['price'].value_counts()
#This column needs additional clean up.
autos['odometer'].value_counts()
#This column needs additional clean up.
autos['n_pictures'].value_counts()
#We see that all ads are without pictures. Maybe we can drop that column.
From the above it looks like the 'seller', 'offer_type' and 'n_pictures' columns can be dropped. It also seems like the 'odometer' and 'price' columns need additional clean up - they hold numeric values stored as text. Let's clean them up:
#First we will clean the price column:
autos['price'] = autos['price'].str.replace('$', '')
autos['price'] = autos['price'].str.replace(',', '')
autos['price'] = autos['price'].astype(int)
autos['price'].head()
#Next we will clean the odometer column:
autos['odometer'] = autos['odometer'].str.replace('km', '')
autos['odometer'] = autos['odometer'].str.replace(',', '')
autos['odometer'] = autos['odometer'].astype(float)
autos['odometer'].head()
#Let's rename the odometer column, so that it's clear that it is in km:
autos = autos.rename({'odometer':'odometer_km'}, axis=1)
Now that we've cleaned up the price and odometer columns, let's explore them further.
#Let's see how many unique values there are:
autos['price'].unique().shape
#Let's view min/max/median/mean etc
autos['price'].describe()
autos['price'].value_counts().sort_index(ascending=True).head(40)
#We see the 40 lowest prices and how many cars are priced at them.
autos['price'].value_counts().sort_index(ascending=False).head(20)
#We see the 20 highest prices and how many cars are priced at them.
Looking at the highest prices - it seems there's a huge jump from 350,000 to 999,990. In fact, all prices above 350,000 look as if they are incorrect. Let's drop those rows. Additionally, we see that there are 1421 cars priced at 0 USD. Let's drop these as well.
autos = autos[(autos['price'] >= 1) & (autos['price'] < 999990)]
autos['price'].describe()
#Let's see how many unique values
autos['odometer_km'].shape
#Let's view min/max/median/mean etc
autos['odometer_km'].describe()
autos['odometer_km'].value_counts(ascending=True).head(10)
autos['odometer_km'].value_counts(ascending=False).head()
The odometer values seem rounded which might mean that the sellers had to choose from a list and not enter the actual numbers of the cars.
There are 5 columns that should represent date values. Some of these columns were created by the crawler, some came from the website itself. Right now, the date_crawled, last_seen, and ad_created columns are all identified as string values by pandas. Because these three columns are represented as strings, we need to convert the data into a numerical representation so we can understand it quantitatively. The other two columns are represented as numeric values.
Let's first understand how the values in the three string columns are formatted. These columns all represent full timestamp values, like so:
autos[['date_crawled','ad_created','last_seen']][0:5]
autos['date_crawled'].str[:10].value_counts(normalize=True, dropna=False).sort_index()
We see that the ads were crawled between March 5th, 2016 and April 7th, 2016. It seems that the ads were crawled almost daily.
autos['ad_created'].str[:10].value_counts(normalize=True, dropna=False).sort_index()
We see that the oldest ads are from June 11th, 2015 and the newest ones - from April 7th, 2016.
autos['last_seen'].str[:10].value_counts(normalize=True, dropna=False).sort_index()
The crawler records when it last saw a listing. This allows us to determine when the car was sold.
autos['registration_year'].describe()
autos['registration_year'].value_counts(normalize=True).sort_index()
There are values in the registration_year column that seem incorrect:
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.
autos['registration_year'].shape[0]-autos['registration_year'].between(1900,2016).sum()
We see that 1884 out of the 48565 listings fall outside of the 1900-2016 registration year interval. Given that this account for less than 4%, it is safe to remove them.
autos=autos[autos['registration_year'].between(1900,2016)]
autos['registration_year'].value_counts(normalize=True).head()
It looks like the majority of the cars were registered between 1999 and 2005.
(autos['brand'].value_counts(normalize=True)*100)
Unsurprisingly, German brands make up for about 60% of all listings. Volkswagen is the top brand with the number of cars listed from that brand being the same as the total of the next two brands - BMW and Opel. There are a lot of brands that aren't very represented. For the sake of the analysis, we will focus on those that make up more than 5% of the listings.
#Let's isolate the top brands
brand_counts = (autos['brand'].value_counts(normalize=True)*100)
top_brands = brand_counts[brand_counts > 5].index
top_brands
brand_mean_prices = {}
for brand in top_brands:
by_brand = autos[autos['brand']==brand]
mean_price = by_brand['price'].mean()
brand_mean_prices[brand] = int(mean_price)
print(brand_mean_prices)
Based on the average prices we see that:
km_per_brand = {}
for brand in top_brands:
by_brand = autos[autos['brand'] == brand]
mean_km = by_brand['odometer_km'].mean()
km_per_brand[brand] = int(mean_km)
km_per_brand
Above we created a dictionary in which we see the average mileage per brand. Now, let's build a dataframe so that we can easily compare the average price and average mileage per brand and see if we can find any corelation.
mean_prices = pd.Series(brand_mean_prices)
mean_mileage = pd.Series(km_per_brand)
brand_info = pd.DataFrame(mean_prices, columns=['mean_prices'])
brand_info['mean_mileage'] = mean_mileage
brand_info
It is interesting to see that even though the average mileage of BMW and Mercedes cars is higher, their prices are still higher than the prices of other brands.
In this guided project we explored data collected from the German eBay. The data contained information on car sales listings. We cleaned up the data and looked for any corelation between the average price and mileage of the top brands.
Data cleaning next steps:
Analysis next steps:
Let's identify columns which have data entered in German and translate it in English. We already know that the 'seller' and 'offer_type' columns have words in German so let's start with them.
autos['seller'] = autos['seller'].str.replace('privat', 'private').str.replace('gewerblich', 'commercial')
autos['seller'].value_counts()
autos['offer_type'] = autos['offer_type'].str.replace('Angebot', 'offer')
autos['offer_type'].value_counts()
autos['vehicle_type'].value_counts()
autos['vehicle_type'] = autos['vehicle_type'].str.replace(
'kleinwagen', 'small car').str.replace('kombi', 'combi').str.replace('cabrio', 'convertible').str.replace('andere', 'other')
autos['vehicle_type'].value_counts()
autos['gearbox'].value_counts()
autos['gearbox'] = autos['gearbox'].str.replace('manuell', 'manual').str.replace('automatik', 'automatic')
autos['gearbox'].value_counts()
autos['fuel_type'].value_counts()
autos['fuel_type'] = autos['fuel_type'].str.replace('benzin', 'petrol').str.replace('elektro', 'electric').str.replace('andere', 'other')
autos['fuel_type'].value_counts()
autos['unrepaired_damage'].value_counts()
autos['unrepaired_damage'] = autos['unrepaired_damage'].str.replace('ja', 'yes').str.replace('nein', 'no')
autos['unrepaired_damage'].value_counts()
Now, let's convert the dates to be uniform numeric data, so "2016-03-21" becomes the integer 20160321. The columns containing dates are: 'date_crawled', 'ad_created' and 'last_seen'
autos['date_crawled'] = autos['date_crawled'].str[:10]
autos['date_crawled'] = autos['date_crawled'].str.replace('-', '')
autos['date_crawled'] = autos['date_crawled'].astype(float)
autos['date_crawled'].value_counts().head()
autos['ad_created'] = autos['ad_created'].str[:10]
autos['ad_created'] = autos['ad_created'].str.replace('-', '')
autos['ad_created'] = autos['ad_created'].astype(int)
autos['ad_created'].value_counts().head()
autos['last_seen'] = autos['last_seen'].str[:10]
autos['last_seen'] = autos['last_seen'].str.replace('-', '')
autos['last_seen'] = autos['last_seen'].astype(int)
autos['last_seen'].value_counts().head()
Let's try to find the most common brand/model combination for the top brands:
brand_model = {}
for brand in top_brands:
by_brand = autos[autos['brand'] == brand]
top_model = by_brand['model'].describe()['top']
brand_model[brand] = top_model
brand_model
#Below we see the most common model per brand
Now let's see if the average prices follow any pattern based on mileage.
ranges = autos['odometer_km'].value_counts(bins=5).index
ranges
ranges_tuple = ranges.to_tuples()
km_price = {}
for r in ranges_tuple:
by_km = autos[autos['odometer_km'].between(r[0], r[1])]
price = by_km['price'].mean()
km_price[r] = int(price)
km_price
In the above two cells we did the following:
We can conclude that the lower the milage of the car, the higher the price.
How much cheaper are cars with damage than their non-damaged counterparts?
damage = autos['unrepaired_damage'].unique()
damaged_price = {}
for d in damage:
by_d = autos[autos['unrepaired_damage'] == d]
price = by_d['price'].mean()
damaged_price[d] = price
price_per_damaged
On average, cars with unrepaired damage are 30% cheaper than undamaged cars.