<jupyter_start><jupyter_text>Exploring eBay Car Sales Data We'll use data from [eBay Kleinanzeigen](https://en.wikipedia.org/wiki/Classified_advertising), a classifieds section of the German eBay website. A few modifications have been made to the original dataset:- 50,000 points have been sampled from the full dataset- The dataset has been dirtied to test and improve data cleaning skillsThe data dictionary is:- ```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. GoalOur goal is to create a clean data set and perform some basic analysis of the data.  Import libraries and load data<jupyter_code>import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

plt.style.use('seaborn-whitegrid')
plt.rc('figure', autolayout=True)
plt.rc(
    'axes',
    labelweight='bold',
    labelsize='large',
    titleweight='bold',
    titlesize=14,
    titlepad=10)


%matplotlib inline
autos = pd.read_csv('autos.csv', encoding = 'Latin-1')
autos.head()
autos.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 50000 entries, 0 to 49999
Data columns (total 20 columns):
 #   Column               Non-Null Count  Dtype 
---  ------               --------------  ----- 
 0   dateCrawled          50000 non-null  object
 1   name                 50000 non-null  object
 2   seller               50000 non-null  object
 3   offerType            50000 non-null  object
 4   price                50000 non-null  object
 5   abtest               50000 non-null  object
 6   vehicleType          44905 non-null  object
 7   yearOfRegistration   50000 non-null  int64 
 8   gearbox              47320 non-null  object
 9   powerPS              50000 non-null  int64 
 10  model                47242 non-null  object
 11  odometer             50000 non-null  object
 12  monthOfRegistration  50000 non-null  int64 
 13  fuelType             45518 non-null  object
 14  brand                50000 non-null  object
 15  notRepairedDamage    40171 non-null  object
 16  date[...]<jupyter_text>The majority of columns are stored as strings. There are two columns that are stored as *objects* that we will want to convert to numerics: price and odometer. The price is an object because is begins with $. The odometer reading ends with kmThere are five columns that have missing data. Sorted from least to most missing data: gearbox, model, fuelType, vehicleType, and notRepairedDamageThe dates are stored in YYYY-MM-DD format and the times are 24 hours. The column names are stored in camelcase.  Cleaning Column Names Let's change the column name case to snake case. This will make them easier to read<jupyter_code>autos.columns
new_column_names = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test',
       'vehicle_type', 'registration_year', 'gearbox', 'powerPS', 'model',
       'odometer', 'registration_month', 'fuel_type', 'brand',
       'unrepaired_damage', 'ad_created', 'nr_of_pictures', 'postal_code',
       'last_seen']
autos.columns = new_column_names
autos.head()<jupyter_output><empty_output><jupyter_text>Identify cleaning tasks<jupyter_code>autos.describe(include='all')<jupyter_output><empty_output><jupyter_text>seller and offer_type both are nearly uniform columns, with all values being the same with one exception. As mentioned previously, price and odometer columns will need to be cleaned and converted to numerical values. The price is an object because is begins with $. The odometer reading ends with km<jupyter_code>autos['gearbox'].value_counts()<jupyter_output><empty_output><jupyter_text>Clean price and odometer columns<jupyter_code># remove initial $ and any , from price columns 
# and convert to a numeric dtype

autos['price'] = autos['price'].str.replace('$','')
autos['price'] = autos['price'].str.replace(',','')
autos['price'] = pd.to_numeric(autos['price'])
#autos['price'].dtype

# remove km from price columns 
# and convert to a numeric dtype
autos['odometer'] = autos['odometer'].str.replace('km','')
autos['odometer'] = autos['odometer'].str.replace(',','')

autos['odometer'] = pd.to_numeric(autos['odometer'])
autos['odometer'].dtype
autos.rename(columns={'odometer':'odometer_km'}, inplace=True)<jupyter_output><empty_output><jupyter_text>Explore odometer and price columns<jupyter_code># start with price

autos['price'].unique().shape
autos['price'].describe()
autos['price'].value_counts().sort_index(ascending=True).head(5)
autos[(autos['price'] < 200) & (autos['price'] > 100)].count()<jupyter_output><empty_output><jupyter_text>The upper quartile is at $7200. We could consider values over 1,000,000 an extreme outlier. This is only 11 vehicles, so would not significantly change our dataset.On the other end, we have 1421 cars listed at a price of $0 and 1762 at \< $100. These seem like very low prices for cars, so we'll (somewhat arbitrarily) use this as a cutoff to remove extreme price outliers. There are only 445 values between 100 and 200, so we see that this number drops relatively quickly.<jupyter_code># drop these rows now

autos = autos[(autos['price'] > 100) & (autos['price'] < 1000000)]
autos.shape
# now look at odometer
autos['odometer_km'].unique().shape
autos['odometer_km'].describe()
autos['odometer_km'].value_counts()<jupyter_output><empty_output><jupyter_text>The odometer column does not seem to have any unusual outliers. All values are one of 13 unique values, between 5000 and 150000. This seems reasonable since we are dealing with used cars. Reported values must be rounded to the nearest 10- or 25-thousand miles.   Exploring the date columns Let's explore the date columns to understand the date ranges this data covers. There are 5 columns related to dates. Three of those are currently stored as strings, so we need to convert into numerical representation so that we can analyze it.<jupyter_code>autos[['date_crawled','ad_created','last_seen']][0:5]<jupyter_output><empty_output><jupyter_text>Let's create a distribution of dates by extracting them first.<jupyter_code>autos['date_crawled'].str[:10].value_counts(normalize=True,dropna=False)
autos['ad_created'].str[:10].value_counts(normalize=True,dropna=False).sort_index()
autos['last_seen'].str[:10].value_counts(normalize=True,dropna=False)<jupyter_output><empty_output><jupyter_text>The majority of these dates come from 2016. The crawler was only run in March and April of 2016. Thus, the date_crawled and last_seen columns have similar ranges of dates. The ads were created between June of 2015 and April of 2016.Now, let's take a look at the registration years of the cars.<jupyter_code>autos['registration_year'].value_counts().sort_index().tail(15)
#describe()
#value_counts()<jupyter_output><empty_output><jupyter_text>The mean registration year is 2004, with a wider range of standard deviations of 88 years. This indicates that there are a decent number of older cars. However, we also see that the minimum year is 1000 and the max year is 9999. So there is some incorrect data for the registration years that we'll need to deal with. The first production of automobiles in Germany was late 1800s, so we can use 1900 as a cut-off. At the other end, the crawler was run in 2016, so we should not find cars with registration years later than that. There are about 2500 autos with 2017 and 2018 registrations listed, which could be typos. Values larger than that should definitely not be used for analysis. We will use 1900-2016 as the range of acceptable values. The number of autos we lose from 2017 and 2018 are relatively small.<jupyter_code>autos = autos[(autos['registration_year'] > 1900) 
              & (autos['registration_year'] < 2017)]
autos['registration_year'].value_counts(normalize=True,ascending=True)
autos.hist(column='registration_year')<jupyter_output><empty_output><jupyter_text>There are very few cars that were first registered before 1960.  Exploring Price by Brand Let's now explore price based on brand. We have quite a few different brands included in our data, so for simplicity we'll need to break this down. When we look at the normalized value counts by brand, we decide to focus on only those brands that account for at least 1% of the total values.<jupyter_code>autos['brand'].value_counts(normalize=True)
autos['brand'].value_counts().index
brand_list = ['volkswagen', 'bmw', 'opel', 'mercedes_benz', 'audi', 'ford', 'renault',
       'peugeot', 'fiat', 'seat', 'skoda', 'nissan', 'mazda', 'smart',
       'citroen', 'toyota', 'hyundai']

brand_dict = {}

for x in brand_list:
    short_auto = autos[autos['brand'] == x]
    mean_price = short_auto['price'].mean()
    brand_dict[x] = mean_price
    
print(brand_dict)
# plot averages price by brand

keys = list(brand_dict.keys())
vals = list(brand_dict.values())

plt.figure(figsize=(16,9))
sns.barplot(x = keys, y = vals, color = 'lightcoral')
plt.xticks(rotation=90,fontsize=26)
plt.yticks(fontsize=26)
plt.show()<jupyter_output><empty_output><jupyter_text>We see that BMW, Mercedes Benz, and Audi have the highest mean price by quite a lot. Now, let's look at only the top 6 brands: Audi, BMW, Mercedes Benz, Ford, Opel, and Volkswagen. Let's use aggregation to understand the average mileage for those cars and see if there's any link with mean price.<jupyter_code>#shorten our above dictionary to only the six brands we care about
short_brand_list = ['volkswagen', 'bmw', 'opel', 'mercedes_benz', 'audi', 'ford']

short_brand_dict = {}

for x in short_brand_list:
    short_auto = autos[autos['brand'] == x]
    mean_price = short_auto['price'].mean()
    short_brand_dict[x] = mean_price
# calculate the mean mileage per vehicle

short_mpv = {}

for x in short_brand_list:
    short_auto = autos[autos['brand'] == x]
    mean_km = short_auto['odometer_km'].mean()
    short_mpv[x] = mean_km
price_series = pd.Series(short_brand_dict)
mileage_series = pd.Series(short_mpv)

df = pd.DataFrame(price_series, columns=['mean_price'])
df['mean_mileage'] = mileage_series
df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1065884476534296, 'fraction_numerical': 0.027617328519855596, 'mean_word_length': 4.562751004016064, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 56, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '392522', 'license': 'MIT', 'n_tokens_mistral': 3599, 'n_tokens_neox': 3265, 'n_words': 1242, 'path': 'Exploring_eBay_car_sales.ipynb', 'repo_name': 'joshfuchs/DataScience_projects', 'size': '11080'}