#!/usr/bin/env python
# coding: utf-8

# # <center><h1>Exploring Ebay Car Sales Data</center><h1>
# "There are few phrases, in the modern world, that can create the image of the highest quality: American customer service, Italian food, African safari, Caribbean beaches, British sense of humour. None of these quite drum up the image of perfection like **"German Engineering”**. The connotations of the phrase itself are almost certainly born from the famed quality, reliability and longevity of *German cars*." Taken from [Top 5 German Car Brands](https://carleasespecialoffers.co.uk/blog/top-5-german-car-brands).
#     
# ---
#     
# In this project, we will analyze a data of used cars from *eBay Kleinanzeigen*, a classified section of the German eBay website.
# 
# The dataset was originally scraped and uploaded to Kaggle. Few modifications from the original dataset were done and uploaded to Kaggle:
# 
# The dataset was dirtied 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 aim of this project is to get ourselves more familiar with data cleaning and analysis of the included used car listings. 
#     
# Below are some points to desribe the column in the data:    
# * `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.
#     
#     ---

# ## Cleaning, cleaning..
# So, of course, the first thing we'll need to do is cleaning. To do that, let's read the datas first to find out what needs to be cleaned.

# In[1]:


import numpy as np
import pandas as pd

#reading the csv file
autos = pd.read_csv("autos.csv", encoding="Latin-1")
autos


# So, there are 50,000 cars listed in the data with 20 columns to describe each. Let's continue to find out some more information.

# In[2]:


print(autos.info())
print('\n')
print(autos.head())


# And here are some thing that we could notice from the findings above:
# - Most columns in the dataset are expressed in strings. Few are numerical values.
# - Some columns have null values, but none have more than ~20% null values.
# - The column names use camelcase instead of Python's preferred snakecase, which means we can't just replace spaces with underscores.
# 
# Let's find column(s) which are numbers but expressed in strings and convert them to numerical values. And columns which are irrelevant to our work. Or some wrong datas ruining our analysis!
# But before that, we should convert the column names from camelcase to snakecase to improve readability. 
#  
#  --- 
# 
# ## <del>camelCase</del> snake_case 

# In[3]:


print("Column names:", autos.columns)


# In[4]:


autos.columns = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'abtest', 'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model', 'odometer', 'registration_month', 'fuel_type', 'brand', 'unrepaired_damage', 'ad_created', 'pictures_nr', 'postal_code', 'last_seen']


# In[5]:


autos.head()


# Yep, that's better.
# 
# ---
# 
# ## Now, to the real cleaning..

# In[6]:


autos.describe(include='all')


# In the description above, we can see there are some columns which seemed irrelevant to our analysis, those that need more investigation and columns in which numeric data are stored as text. 
# 
# * Columns that need to be dropped: `seller`, `offert_type`, `pictures_nr`
# * Numeric data stored as text: `price`, `odometer`
# * Columns need more investigation: `registration year`, `power_ps`
# 
# Let's go in order.
# ### Dropping columns

# In[7]:


autos["seller"].value_counts()


# In the `seller` column, all car sales were sold by "privat" except one. And this data isn't really helpful for the analysis because of its uniformity. The same goes for the `offer_type` columns below.

# In[8]:


autos["offer_type"].value_counts()


# And here's another column. All values in the `pictures_nr` column refers to 0 which is meaningless to be analyzed if all are the same.

# In[9]:


autos["pictures_nr"].value_counts()


# So, these columns can just be thrown away to the bin!

# In[10]:


autos.drop(["seller", "offer_type", "pictures_nr"], axis=1, inplace=True)
autos.head(2)


# Done! Aaand there's 17 columns left! Yay less work for me~
# 
# --- 
# 
# ### Converting to numbers
# And then, there are two columns which contains numerical data but stored as text, like price and odometer column. These columns need to be converted to numerical values to help better analysis.
# 
# For `price`, the /$ symbol needs to be removed along with the , then converted to an integer. While `odometer` needs to remove the km and , before converting to a numerical value.

# In[11]:


#converting price column to numeric values
autos["price"] = (autos["price"]
                  .str.replace('$', '')
                  .str.replace(",", "")
                  .astype(int)
                 )

#converting odometer column to numeric values
autos["odometer"] = (autos["odometer"]
                     .str.replace("km", "")
                     .str.replace(",", "")
                     .astype(int)
                    )
autos.rename({"odometer": "odometer_km"}, axis=1, inplace=True)

#the result
print(autos["price"].head())
print('\n')
print(autos["odometer_km"].head())


# Done! Now that it's numbers, let's investigate the data more deeply. Let's start from the `price` and `odometer_km` column first because the numerical values didn't appear in the previous description.

# In[12]:


autos["price"].describe()


# Whoops. It turns out that there is a mistake. The max data of price is 100 larger than the 75% price of the whole column. This needs more investigation which data is wrong.

# In[13]:


autos["price"].value_counts().sort_index(ascending=False).head(15)


# In[14]:


autos["price"].value_counts().sort_index(ascending=True).head(10)


# According to [eBay](https://www.ebay.com/motors/blog/most-expensive-cars-sold-ebay/) the most expensive vehicles sold on eBay cost around $3 million. This clearly means prices above 3 milion needs to be removed. However, if we're to see datas even below that, there seems to be a weird jump of prices between 350k and 1 million dollar. So, in this project, we'll remove prices above 350k.
# 
# And since auctions at eBay that start with $1 is not too uncommon, we'll start the price from there and exclude those zeroes.

# In[15]:


autos = autos[autos["price"].between(1,351000)]
autos["price"].describe()


# Nice. This looks much better. 
# 
# Continue on, let's check the `odometer_km` column. As a reminder, this column describes how many kilometer the car has driven.

# In[16]:


autos["odometer_km"].value_counts()


# Great! This doesn't look like there's a problem at all!
# 
# ---
# 
# ### Need more investigation hmm..
# On the other hand, some column which needs to be investigated are `registration_year` and `power_ps`. The max data in `registration_year` column indicates the year 9999, which doesn't exist. Meanwhile, the max data of `power_ps` column far exceeds the 75% mark of the whole data column. We need to check if there's something wrong with the data in this column. And remove it if it does. Let's start with `registration_year`.

# In[17]:


autos["registration_year"].value_counts().sort_index()


# See, there's so many wrong-inputted registration year. This column should describe the year the car was first registrated, so it shouldn't be possible for it to be above the year 2016, when the data was listed at the time. But it's also not possible that the registration year happened as long ago as the year 1800 or even 1000. 
# 
# Since the data we're analyzing originates from Germany, let's limit the registration year of cars in Germany, which starts from the year 1896. Germany was the second country to introduce registration plate with the police in 1896, based on [this source](https://en.wikipedia.org/wiki/Vehicle_registration_plate#History). So, only years between 1896 and 2016 will be accounted in this data.

# In[18]:


autos = autos[autos["registration_year"].between(1896, 2016)]
autos["registration_year"].value_counts(normalize=True).sort_index(ascending=False).head(20)


# In[19]:


autos["registration_year"].value_counts(normalize=True).sort_index(ascending=False).tail(10)


# And here's the result! This looks much more rational and realistic. Furthermore, there's a logical reason as to why recent years cars are registered more than the old years. Of course, it's thanks to the advancement of technology that in the 2000s more and more cars are registered than the early years it was founded.
# 
# Let's go on to the `power_ps` column. This column describes the power of the car in PS(Pferdestrke) which literally means 'horse strength'. It is also alternatively known as 'hp' and 'bhp'. 

# In[20]:


autos["power_ps"].value_counts().sort_index()


# Whaat?? 0 PS? Is there even a car with an engine that doesn't give off any power? I did not expect this. Or 0 means that they don't know the power of the engine. I should remove these 0s. 
# 
# Wait, what's that 17000 PS in the bottom? That's also weird.. As far as I searched, 8000 PS are for F1 cars.. And the most common for daily use cars are 120-150 hp(almost equivalent to 120-150 PS).

# In[21]:


autos["power_ps"].value_counts().sort_index(ascending=False).head(20)


# Hmm.. this is not possible. Let's check how expensive a car with 17,700 PS power engine would be sold at..

# In[22]:


autos[autos["power_ps"] == 17700][["name","price","power_ps","brand"]]


# \$6000 for a super power engine car?! No way. 
# 
# Then, what about the 8000 PS car? Since the gap between 8000 and 14000 is a bit large, I will decide if I should throw the 8000 too or not after seeing the price.

# In[23]:


autos[autos["power_ps"] == 8404][["name","price","power_ps","brand"]]


# Yup. \$950 for a wonderful 8000 PS engine car. Throw this away. 
# 
# I need to find a reasonable amount to limit the engine. Let's look at the median and percentages.

# In[24]:


autos["power_ps"].describe()


# As expected, the 75% mark is in the normal power range for daily to use cars. According to this [guide](https://www.autolist.com/guides/average-car-horsepower), 300 PS in a normal car is still realistic. So, I will set the limit between 1 PS and 300 PS if the highest data price matches the power.

# In[25]:


#if you recall the cut above, 
#the current highest price of a car is $350000
autos[autos["price"] == 350000][["name","price","power_ps","brand"]]


# Wow, so the highest price car has a power of 500 PS! Not bad, this is still acceptable since it's not far-fetched like the 17700 or 8000 PS. So I'll set the range between 1 and 500.

# In[26]:


autos = autos[autos["power_ps"].between(1,500)]
autos["power_ps"].describe()


# Now that the irregularity have been removed, only 42 thousand datas are left. But, it's alright! It's still a lot of data, plenty to be analyzed.
# 
# ---
# 
# ### Date, data?
# It seems there are 3 columns which represent date values expressed in string, `date_crawled`, `last_seen`, and `ad_created`. We should convert them into numerical values to be able to analyze it quantitatively. First, let's see how they are represented.

# In[27]:


autos[["date_crawled", "ad_created", "last_seen"]][:5]


# The first 10 characters refers to the date and the last 8 are the time. Since we just need the date values, we can extract only the date, distribute and sort them by index. Phew, sounds complicated.

# In[28]:


(autos["date_crawled"]
 # to select only the first 10 characters
                     .str[:10]
 # normalize to use percentages, 
 # unable dropna to include missing values in the distribution
                     .value_counts(normalize=True, dropna=False)
 # to rank by date from earliest to latest
                     .sort_index(ascending=True)
                    )


# It seems there are varying frequencies of crawled date, so let's sort them by values.

# In[29]:


(autos["date_crawled"]
                     .str[:10]
                     .value_counts(normalize=True, dropna=False)
                     .sort_values(ascending=False)
                    )


# Hmmm.. I think that the crawler mostly done the scraping between March and April, where the most crawls happened during late-March and early April.
# 
# Let's find out about the rest 2 columns.

# In[30]:


(autos["ad_created"]
                     .str[:10]
                     .value_counts(normalize=True, dropna=False)
                     .sort_values(ascending=False)
                    )


# `ad_created` describes the date on which the eBay listing was created. The listing was created over 72 different dates and it seems like most listings was created during March and April of 2016, nearly the same as the crawled date. While there are some created months before the latest listing date.

# In[31]:


(autos["last_seen"]
                     .str[:10]
                     .value_counts(normalize=True, dropna=False)
                     .sort_index(ascending=True)
                    )


# The `last_seen` column describes the date the crawler last saw the ads of the car online, which could also informs us the date it was removed, possibly because it was sold. 
# 
# The ads last seen continue to increase as time goes on, where the most ads seen last was on 6th of April and the least was a month before that. The crawler roughly saw these ads online in a span of a month between March and April. Thus, the large amount of ads seen in the last 3 days does not indicate car sales.
# 
# ---
# 
# ## Analysis: What brand?
# When speaking about cars, I mean car brands. Is it the notorious Porsche? Or my rich neighbor's Lexus? Or instead some Toyota my dad likes to use? Let's find out what brands are the most reliable in eBay.

# In[32]:


brand = autos["brand"].value_counts(normalize=True)
brand


# We can see here the relative frequency of car brands sold in the German eBay website. Overall, German car brands like Volkswagen, Mercedes-Benz, BMW, Opel and Audi prevail in their own country. Volkwagen account almost twice the cars of BMW. While cars originating from other countries like Lada, Trabant and Lancia which gives off old, retro styles are not too popular.
# 
# As this is a German eBay website, I decided to select the top 5 car brands to aggregate by. Of course, seeing that the top 5 brands are all from Germany was one of the reason, but the fact that they all account over than 5% of the total values proves how popular they are. Let's find out the average price of these 5 favorite brands.

# In[33]:


top_5_mean_price = {}

top_5_brands = brand.index[:5]

for b in top_5_brands:
    selected_rows = autos[autos["brand"] == b]
    mean_price = selected_rows["price"].mean()
    top_5_mean_price[b] = mean_price
    
top_5_mean_price


# To state the obvious, the Volkswagen are cheaper and Opel are much cheaper compared to other famous brands like the BMW, Merc and Audi. 
# 
# While Opel are not that much familiar-sounding to the rest of the world like Mercedes Benz and Audi are, it is still a big brand name like the rest are. Though, seeing that there are a lot less publicity and fame, it might explain why the price are much cheaper than the others.
# 
# The same might be goes to Volkwagen. Thanks to its cheaper price in addition to its novel technology and eye-catching design with a built to give comfort and safety, it has secured its place as the number one car brand. To give you a bit of its background, it was originally made to provide the people with a car, hence its name literally means "People's Car" that the multitude could realistically afford.
# 
# This affordability for the masses would make it the top of any other cars, whereas other brands continue to strive higher and advance more with costly prices.
# 
# But, let's continue to dig deeper using the mileage to discover whether there's a connection to the price.

# In[34]:


top_5_mean_mileage = {}

for b in top_5_brands:
    sel_rows = autos[autos["brand"] == b]
    mean_mileage = sel_rows["odometer_km"].mean()
    top_5_mean_mileage[b] = mean_mileage
    
top_5_mean_mileage


# I found the average mileage (though it's expressed in km) for each car brand, but like this it would be too hard to compare it to the price. Therefore, I will make these datas into a dataframe first to analyze it more efficiently.

# In[35]:


# using series constructor to turn the dictionaries into series
bmp_series = pd.Series(top_5_mean_price)
bmm_series = pd.Series(top_5_mean_mileage)

# turn both series into a dataframe
df = pd.DataFrame(bmp_series, columns=["mean_price"])
df["mean_mileage"] = bmm_series

# result
df


# Generally speaking, higher mileage should lead to lower prices. The fact that cars covering a distance less than 130,000 km mostly cost less, while above that point cars are more expensive come to tell us that probable mileage doesn't really have a connection to its price. That said, Audi has the least mileage among the five and is the most pricey. 
# 
# Though, strictly speaking, there is not a large difference in the mileage in all five types of cars, so mileage plays a little role in the car sales.
# 
# --- 

# ## Conclusion
# All in all, the most popular car brands sold in the German eBay website are brands from their own country - Germany. Mercedez Benz, BMW, Opel, Volkswagen and Audi are the most popular ones, and among them Volkswagen - the "People's Car" - prevails. The sales of Volkswagen alone accounts over 20% of the whole sales. 
# 
# Between the average price and mileage of the top five highest-selling brands, there are little to no connection. Although the affordability and relatively cheap price of the "People's Car" Volkswagen is outstanding, ensuring its fame. 
# 
# However, more analysis need to be done to further understand the connection between mileage and price, to find the most common brand/model combinations and correlation of sales and damaged parts.