Notebook

Lame Cars Are a Better Deal¶

Introduction¶

In 2019 the global used car market was estimated to be a whopping 1332 billion US dollars (1). This was prior to the ubiquitous supply chain disruptions that are now part of our daily lives and the expectations of a 13% to 32% decline in global trade (2)(3). The turnover in used car inventory for August and September was the highest in six years (4). One large sector of used car sales comes from the eBay Vehicle Market. They have over 7.4 million users per month. A car or truck is sold every 3 minutes (5). That's of lot of interactions!

The main conclusion of this study is unpopular cars might be a better deal when using mileage as a metric. For the money spent they have lower mileage.

Let's take a quick look at a selection of 50,000 rows from a dataset created in 2015 for the German eBay marketplace, eBay Kleinanzeigen. The original data set can be found here. https://data.world/data-society/used-cars-data

Citations

Grand View Research. Used Car Market Size, Share & Trends Analysis Report By Vehicle Type (Hybrid, Conventional, Electric), By Vendor Type, By Fuel Type, By Size, By Region, By Sales Channel, And Segment Forecasts, 2020 - 2027. Sept 2020.

https://www.grandviewresearch.com/industry-analysis/used-car-market 2. Li, X., Ghadami, A., Drake, J.M. et al. Mathematical model of the feedback between global supply chain disruption and COVID-19 dynamics. Sci Rep 11, 15450 (2021). https://doi.org/10.1038/s41598-021-94619-1 3. World Trade Organization. Trade set to plunge as COVID-19 pandemic upends global economy. April 2020. https://www.wto.org/english/news_e/pres20_e/pr855_e.htm 4. Eric Rosenbaum. The used car boom is one of the hottest, and trickiest, coronavirus markets for consumers. CNBC October 2020. https://www.cnbc.com/2020/10/15/used-car-boom-is-one-of-hottest-coronavirus-markets-for-consumers.html 5. Welcome to the eBay Vehicle Seller Center. eBay. Sept 2021. https://pages.ebay.com/sellerinformation/vehiclesellercenter.html

Open and Examine Data Set¶

An initial look at the data set shows 50,000 entries with 20 columns of information.
Five of the columns are missing entries and in some the data needs to be converted to the correct type.
Some of the entries are in German and need to be translated.

In [2]:

# import libraries and read csv
# import emoji
import pandas as pd
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import pprint
autos = pd.read_csv("autos.csv", encoding="Latin-1")
# get info about dataset
autos.info()

---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
/var/folders/bz/5cj1tzbj5xn319rpqrw6gpmh0000gn/T/ipykernel_2809/3296113383.py in <module>
      5 import pandas as pd
      6 import matplotlib.pyplot as plt
----> 7 import seaborn as sns
      8 import pprint
      9 autos = pd.read_csv("autos.csv", encoding="Latin-1")

ModuleNotFoundError: No module named 'seaborn'

In [ ]:

# initial stats
autos.describe(include="all")

In [ ]:

# first five rows
autos.head()

Begin Cleaning Data Set¶

The price and odometer columns need to be converted to integers.
The column titles could be more clear and use snake_case.
It would be nice if the name, brand, model, and vehicle_type columns were grouped together.

In [ ]:

# clean the price and odometer columns
autos["price"] = autos["price"].str.replace("$","").str.replace(",","")
autos["price"] = autos["price"].astype(int)
autos["odometer"] = autos["odometer"].str.replace(",","").str.replace("km","")
autos["odometer"] = autos["odometer"].astype(int)
autos.rename(columns={"odometer" : "odometer_km"}, inplace=True)
print("The data type for the price column is : ", autos["price"].dtypes)
print("The data type for the odometer_km column is : ", autos["odometer_km"].dtypes)
# change the column names
new_cols = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',
       'vehicle_type', 'year_of_registration', 'gearbox', 'powerPS', 'model',
       'odometer_km', 'month_of_registration', 'fuel_type', 'brand',
       'unrepaired_damage', 'ad_created', 'num_of_pictures', 'postal_code',
       'last_seen']
autos.columns = new_cols
# reorder columns
autos = autos.reindex(columns=["date_crawled", "price", "name", "brand", "model", "vehicle_type", "ab_test",
                   "odometer_km", "month_of_registration", "year_of_registration", "gearbox","powerPS",
                   "fuel_type", "unrepaired_damage", "ad_created", "postal_code", "last_seen",
                              "seller", "offer_type", "num_of_pictures"])

Removing outliers and unnecessary columns¶

Part 1 of 4¶

Looking at the price and odometer columns.

The majority of the values in the price column are less than 500,000 but a small number are close to or well over 1,000,000! The rows for these prices do not appear to be unique in other ways nor are they related to the three columns being considered for removal. Two of the entries are for an expensive brand, but they are not representative of either group. Most of the expensive cars are priced a suspicious \$12,345,678🤔. The high number of vehicles (1421) with a price of 0 will skew the results as well. Keeping all rows with prices between \$1 and \$500,00 seems to make sense.
The odometer column doesn't contain any outlier values. It is broken into a group of 13 buckets. Notably, 64% of the entries are in the 150,000km or more category. There doesn't seem to be any reason to remove any of the rows based on the odometer entries.

In [ ]:

# isolate the price column and reveal outlier values
price_series = autos["price"]
print("Here's the index and price for the 15 most expensive vehicles:\n", price_series.sort_values(ascending=False).head(n=15))
print("Here's the index and price for the 10 least expensive vehicles:\n", price_series.sort_values(ascending=False).tail(n=10))

In [ ]:

# examine rows with outlier prices
cheap_cars = autos[autos["price"].between(1,100)]
print("The number of cars between $1 and $100: ", len(cheap_cars))
expensive_cars = autos[autos["price"]>500000]
print("The number of cars more than $500,000: ", len(expensive_cars))
free_cars = autos[autos["price"] == 0]
print("The number of cars that are free: ", len(free_cars))
outlier_bool = (autos["price"] < 1) | (autos["price"] > 500000)
outlier_rows = autos[outlier_bool]
outlier_rows.sort_values(by="price", axis=0, ascending=False)
print("This chart shows the most and least expensive cars.")

In [ ]:

# remove rows with outlier prices
print("Length: ", len(autos))
autos = autos[autos["price"].between(1, 500000)]
print("Length: ", len(autos))

In [ ]:

# isolate the odometer column and reveal outlier values
odometer_series = autos["odometer_km"]
print(odometer_series.unique().shape)
print(odometer_series.describe)
odometer_series.value_counts().sort_index(ascending=False).head(n=15)

Removing outliers and unnecessary columns¶

Part 2 of 4¶

We can probably remove these three columns and make a note in the conclusion.

The seller and offer_type columns have only two values in each column. In each, one value represents all of the entries except for one entry.
The num_of_pictures column only has one entry, "0".

In [ ]:

# close look at value counts in three columns
print(autos["seller"].value_counts())
print(autos["offer_type"].value_counts())
print(autos["num_of_pictures"].value_counts())

In [ ]:

# drop the seller, offer_type, and num_of_pictures columns
autos = autos.drop(["seller", "offer_type", "num_of_pictures"], axis=1)

Removing outliers and unnecessary columns¶

Part 3 of 4¶

The date values are in string format and should be converted but an initial look shows some interesting things.

The date_crawled column shows a normal distribution for when the ads were put into the data set. There are three low points that could be due to problems with the bot or network, or some other factor.
The date_created column is heavily skewed to the left. However, the tail is very shallow and the vast majority of entries occurs under a normal distribution. Creating a frequency table shows some ads with much older date_created values than the rest of the set. This is the only one of the three with dates prior to March 2016. Again, there are three low points that interestingly appear to correspond to the low points in the date_crawled series. Removing any dates prior to the earliest date for the date_crawled column removes 203 entries. Looking at a line graph for date_crawled and date_created confirms that the dips occur at the same times.
The last_seen column aligns more closely with date_crawled than the date_created column but there is a huge increase on April 4-6. These three days represent over 50% of the values in this column.

In [ ]:

# isolate the date portion of the date_crawled column and count the number of entries per day
date_crawled_dist = autos["date_crawled"].str[:10].value_counts().sort_index()
# set width and height for plots
plt.rcParams['figure.figsize'] = [10,7]
# create a line plot showing crawl volume for each day
date_crawled_dist.plot.line()
plt.ylabel("Number of entries")
plt.xlabel("Date Crawled")
plt.xticks(rotation=30)
plt.show()

In [ ]:

# isolate the date portion of the ad_created column and count the number of entries per day
ad_created_dist = autos["ad_created"].str[:10].value_counts().sort_index()
# create a line plot showing day the ad was created
ad_created_dist.plot.line()
plt.ylabel("Number of entries")
plt.xlabel("Date Ad Created")
plt.xticks(rotation=30)
plt.show()

In [ ]:

# remove early entries so ad_created start date matches dates for date_crawled column
autos = autos[autos["ad_created"].str[:4] != "2015"]
autos = autos[autos["ad_created"].str[:7] != "2016-01"]
autos = autos[autos["ad_created"].str[:7] != "2016-02"]
autos = autos[autos["ad_created"].str[:10] != "2016-03-01"]
autos = autos[autos["ad_created"].str[:10] != "2016-03-02"]
autos = autos[autos["ad_created"].str[:10] != "2016-03-03"]
autos = autos[autos["ad_created"].str[:10] != "2016-03-04"]
print(len(autos))
# create a line plot showing day the ad was created
print(len(autos))
ad_created_dist = autos["ad_created"].str[:10].value_counts().sort_index()
ad_created_dist.plot.line()
plt.ylabel("Number of entries")
plt.xlabel("Date Ad Created")
plt.xticks(rotation=30)
plt.show()

In [ ]:

# create a line plot that shows date_crawled and ad_created
distributions = [date_crawled_dist, ad_created_dist]
for distribution in distributions:
    distribution.plot.line()
plt.legend()
plt.xticks(rotation=30)
plt.show()

In [ ]:

# isolate the date portion of the last_seen column and count the number of entries per day
last_seen_dist = autos["last_seen"].str[:10].value_counts().sort_index()
# create a line plot showing day the ad was removed
last_seen_dist.plot.line()
plt.ylabel("Number of entries")
plt.xlabel("Date Ad Removed")
plt.xticks(rotation=30)
plt.show()

In [ ]:

# create a frequency table for the last_seen column
# this allows for a more precise measure for the volume in the last three days
last_seen_freq = autos["last_seen"].str[:10].value_counts(normalize=True, dropna=False)*100
last_seen_freq = last_seen_freq.sort_index()
last_seen_freq

Removing outliers and unnecessary columns¶

Part 4 of 4¶

The year_of_registration column has 5 entries prior to 1900 and 1879 after 2016. It's unlikely that any of these dates are correct. It would take far to long to look up the true values for these entries and using a mean value would not be appropriate for what this column represents. Removing them might allow a more accurate view of the distribution. Interestingly, while this does affect the minimum, maximum, and standard deviation values, it results in little change to the upper ends of the first, second, and third quartiles.

In [ ]:

# isolate the year_of_registration column and get initial stats
registration_series = autos["year_of_registration"]
print(registration_series.describe())

In [ ]:

# remove year_of_registration outliers
print(len(autos[autos["year_of_registration"] < 1900]))
print(len(autos[autos["year_of_registration"] > 2016]))
print("The length of the data set before removing outlier registration year values is: ", len(autos))
autos = autos[autos["year_of_registration"].between(1900, 2016)]
print("The length of the data set after removing outlier registration year values is: ", len(autos))

In [ ]:

# isolate the year_of_registration column and get final stats
registration_series = autos["year_of_registration"]
print(registration_series.describe())
print("The earliest year for a vehicle to be registered is:", registration_series.min())
print("The latest year for a vehicle to be registered is:", registration_series.max())

Focusing on the Brand¶

At this point the dataset is prepared for an initial analysis. Many consumer choices are influenced by brand. Using that as a reference for looking at mean price and mean mileage is a good way to begin grouping the entries.

In [ ]:

# isolate the brand column and look at totals and relative frequencies.
brand_series = autos["brand"]
# brand_value_counts = brand_series.value_counts()
brand_value_counts = brand_series.value_counts(normalize=True, dropna=False)*100
# print(brand_value_counts)
# create a line plot showing volume of sales by brand
# how do i get volkswagen on the last tick?
brand_value_counts.plot.line()
plt.ylabel("Number of entries")
plt.xlabel("Brand")
plt.xticks(rotation=45)
plt.show()

In [ ]:

# a function to create a dictionary and mean column value for part of a series
def autos_converter(c_series, mean_column, start_slice=0, stop_slice=5):
    """
    Takes a series index and returns that index and the mean of a column in the autos dataframe.
    Can be sliced. Default = 0:5
    Example:
    >>> autos_converter(c_series=brand_value_counts, mean_column="price", stop_slice=2)
    {'volkswagen': 5404.27828123409, 'bmw': 8337.549843627834}
    """
    series_slice = c_series.iloc[start_slice:stop_slice]
    series_dict = {}
    for index in series_slice.index:
        index_mean = autos.loc[autos["brand"] == index, mean_column].mean()
        series_dict[index] = index_mean
    return series_dict

In [ ]:

# create dictionary with brand and mean price for top five selling brands
t5_mean_price = autos_converter(c_series=brand_value_counts, mean_column="price")
# create dictionary with brand and mean odometer_km for top five selling brands
t5_mean_odomkm = autos_converter(c_series=brand_value_counts, mean_column="odometer_km")

In [ ]:

# i could have made another fundtion but i have to get onto the next project!
# transform t5_mean_price dictionary to dataframe
t5_mean_price_series = pd.Series(t5_mean_price)
t5_df = pd.DataFrame(t5_mean_price_series, columns=["mean_price"])
# add columns for mean odometer_km and relative frequency in original df
t5_mean_odomkm_series = pd.Series(t5_mean_odomkm)
t5_df["mean_odomkm"] = t5_mean_odomkm_series
t5_df["%_in_autos"] = brand_value_counts[:5]
t5_df = t5_df.astype(int)
t5_df = t5_df.reset_index()
t5_df = t5_df.rename(columns={'index': 'brand'})

In [ ]:

# create dictionary with brand and mean price for five least selling brands
l5_mean_price = autos_converter(c_series=brand_value_counts, mean_column="price", start_slice=-5, stop_slice=None)
# create dictionary with brand and mean odometer_km for five least selling brands
l5_mean_odomkm = autos_converter(c_series=brand_value_counts, mean_column="odometer_km", start_slice=-5, stop_slice=None)

In [ ]:

# transform l5_mean_price dictionary to dataframe
l5_mean_price_series = pd.Series(l5_mean_price)
l5_df = pd.DataFrame(l5_mean_price_series, columns=["mean_price"])
# add columns for mean odometer_km and relative frequency in original df
l5_mean_odomkm_series = pd.Series(l5_mean_odomkm)
l5_df["mean_odomkm"] = l5_mean_odomkm_series
l5_df["%_in_autos"] = brand_value_counts[-5:]
l5_df = l5_df.astype(int)
l5_df = l5_df.reset_index()
l5_df = l5_df.rename(columns={'index': 'brand'})

In [ ]:

t5_df["price_mileage_ratio"] = 0
t5_df["price_mileage_ratio"] = t5_df["mean_price"] / t5_df["mean_odomkm"]
l5_df["price_mileage_ratio"] = 0
l5_df["price_mileage_ratio"] = l5_df["mean_price"] / l5_df["mean_odomkm"]

In [ ]:

# pretty print dataframes for five most sold brands and five least sold brands
pd.set_option('display.max_rows', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.width', 1000)
pd.set_option('display.colheader_justify', 'center')
pd.set_option('display.precision', 3)
display(t5_df)
display(l5_df)

Conclusion¶

Vehicle brand is an easy way for consumers to evaluate a purchase. Using this as a starting point leads to a few coclusions.

The three most popular brands (Volkswagen, BMW, and Opel respectively) represent slightly more than 50% of the vehicles in the data set. Un-commenting "# print(brand_value_counts)" displays the frequency table.
Of the five least most popular vehicles, Ladas and Trabants have very low mileage. Their unpopularity is probably due to quality perceptions.
The price_mileage_ratio column shows the relationship between mean_price and mean_odomkm. The closer the number to zero, the lower the vehicle mileage per dollar spent. So, amongst the five most popular brands, audis had the lowest mileage per dollar in the mean price.
Additionally, this allows a comparison of value compared to popularity. The mean price for the five most popular cars is \$6935 and for the five least popular it is \$2101. The price to mileage ratio for the popular cars is 0.05 and for the unpopular cars it is 0.114. So not only is an unpopular car less expensive it is also a better deal if mileage is the only criteria.
Coding a dashboard to combine different columns and row slices to create new ratios would possibly show more unexpected results.
Lastly, it would be interesting to examine the rows with outlier prices. If the price is controled for in some way would that group reveal similar paterns?