<jupyter_start><jupyter_text>Cleaning the dataIn this project, we will be cleaning the data about used cars scraped from eBay Kleinanzeigen.<jupyter_code>import pandas as pd
import numpy as np
autos = pd.read_csv("autos.csv", encoding="Latin-1")
autos
autos.info()
autos.head()<jupyter_output><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 obj[...]<jupyter_text>Notes:1. The colmn names follow the same camel-case structure. These has to be renamed following the underscored standard.2. Some columns that contain numbers are considered as an Object. We have to fix this.3. Some columns have null values  Convert the columns to underscored case* "yearOfRegistration" to "registration_year"* "monthOfRegistration" to "registration_month"* "notRepairedDamage" to "unrepaired_damage"* "dateCreated" to "ad_created"The rest of the columns is converted from camel case to snakecase.<jupyter_code>print(autos.columns)
cols = ['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', 'nr_of_pictures', 'postal_code',
       'last_seen']
autos.columns = cols
autos.head()
autos.describe(include='all')<jupyter_output><empty_output><jupyter_text>NotesThe values of "nr_of_pictures" are all 0. Therefore, it is not useful for us. We should remove it.The "seller" and "seller" columns have two unique values and one of them is repeated 49999 out of 50000 times, which make these columns of not so much use for us.The most frequent value in the "price" column is 0. This is probably means that the real price is not known and is represented as 0. We can use NaN to fix this. Some columns' data is stored as a strings, we have to clean it.<jupyter_code>autos["price"].head()<jupyter_output><empty_output><jupyter_text>Cleaning Price<jupyter_code>autos["price"] = (autos["price"]
                  .str.replace("$","")
                  .str.replace(",",".")
                  .str.replace(".","")
                  .astype(float)
                 )
autos["price"].head()
autos = autos[autos["price"].between(100,100000)]<jupyter_output><empty_output><jupyter_text>The reason for choosing the values 100 and 100000 is that some sellers might put lowest prices (even 0!) to attract more buyers and negociators toward their car. And any value that is more than 100000 seems to be for a new car and doesn't fit the context of a used car.  Dropping "nr_of_pictures"<jupyter_code>autos = autos.drop("nr_of_pictures", axis=1)<jupyter_output><empty_output><jupyter_text>Cleaning Odometer<jupyter_code>autos = autos.rename(columns={"odometer":"odometer_km"})
autos["odometer_km"].head()
autos["odometer_km"] = (autos["odometer_km"]
                        .str.replace("km","")
                        .str.replace(",",".")
                        .astype(float)
                       )
autos["odometer_km"].head()
autos["odometer_km"].unique().shape
autos["odometer_km"].unique()<jupyter_output><empty_output><jupyter_text>Dealing with the dates columns<jupyter_code>autos["registration_month"].describe()
(autos['date_crawled']
 .str[:10]
 .value_counts(normalize=True, dropna=False)
 .sort_index()
)<jupyter_output><empty_output><jupyter_text>It seems like the crawling process was mainly conducted between March and April 2016 with an almost equal distribution.<jupyter_code>(autos['ad_created']
 .str[:10]
 .value_counts(normalize=True, dropna=False)
 .sort_index()
)<jupyter_output><empty_output><jupyter_text>The number of ads created from Marh 2016 onwards is larger than whatever was created before.<jupyter_code>(autos['last_seen']
 .str[:10]
 .value_counts(normalize=True, dropna=False)
 .sort_index()
)<jupyter_output><empty_output><jupyter_text>Most of the ads has been seen recently during April 2016.<jupyter_code>autos["registration_year"].describe()<jupyter_output><empty_output><jupyter_text>We notice the minimun year to be 1000 which is far before cars were invented. Similarly, the maximum value (9999) is far in the future. These values have to be cleaned.<jupyter_code>(autos['registration_year']
 .value_counts(dropna=False)
 .sort_index()
 .head()
)<jupyter_output><empty_output><jupyter_text>We can see that the number of cars registered after 2016 is pretty significant. However, these values are without a doubt innacurate so we can safely remove them. As for the lower bound, we notice that there are very few cars registered before 1900. So, it is safe to remove those too.<jupyter_code>autos = autos[autos["registration_year"].between(1900, 2016)]
(autos['registration_year']
 .value_counts(normalize=True, dropna=False)
 .sort_index()
 .head()
)<jupyter_output><empty_output><jupyter_text>Aggregate the Brand columnWe will collect the unique values from "Brand" then select the top 10. Afterwards, we will aggregate the mean price for each of these top 10 brands.<jupyter_code>mean_by_brand = {}
top_10 = autos["brand"].value_counts().index[:10]
for brand in top_10:
    brand_row = autos[autos["brand"] == brand]
    brand_mean = brand_row["price"].mean()
    mean_by_brand[brand] = brand_mean
import operator
sorted(mean_by_brand.items(), key=operator.itemgetter(1), reverse=True)<jupyter_output><empty_output><jupyter_text>From the above data, we can conclude that the Audi brand is the most expensive in the second hand market followed closely Mercedes Benz and BMW. Also, it is apparent that the German brands are on the top with a remarkable price gap between them and the other foreign competitors.  Comparing Mean Price and MileageWe have already calculated the mean price for the top 10 brands. Let's calculate the mean mileage for these brands as well.<jupyter_code>mileage_by_brand = {}
for brand in top_10:
    brand_row = autos[autos["brand"] == brand]
    mileage_mean = brand_row["odometer_km"].mean()
    mileage_by_brand[brand] = mileage_mean

import operator
sorted(mileage_by_brand.items(), key=operator.itemgetter(1), reverse=True)<jupyter_output><empty_output><jupyter_text>Now, let's convert these dictionaries to Series, then create a Dataframe from these Series for easier comparison.<jupyter_code>pbb_series = pd.Series(mean_by_brand)
mbb_series = pd.Series(mileage_by_brand)

compare_brands = pd.DataFrame(pbb_series, columns=['mean_price'])
compare_brands
compare_brands["mean_mileage"] = mbb_series
compare_brands<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09687415790891943, 'fraction_numerical': 0.03018054432767448, 'mean_word_length': 4.44208211143695, 'pattern_counts': {'":': 1, '<': 61, '<?xml version=': 0, '>': 61, 'https://': 0, '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': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '519918', 'license': 'Apache-2.0', 'n_tokens_mistral': 2445, 'n_tokens_neox': 2220, 'n_words': 701, 'path': 'Exploring Ebay Car Sales Data/Exploring_Ebay_Car_Sales_Data.ipynb', 'repo_name': 'ktobah/data_science', 'size': '7422'}