<jupyter_start><jupyter_text>We will be working on a dataset of used cars from eBay Kleinanzeigen, a classifieds section of the German eBay website.The data dictionary provided with data is as follows:   - `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.   - `offerTyp`e - 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 which year 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 which year 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.The aim of this project is to clean the data and analyze the included used car listings.<jupyter_code>import numpy as np
import pandas as pd
import warnings
warnings.filterwarnings('ignore')
autos = pd.read_csv("D:/Developer/Data Science/Projects/CSV files/autos.csv", encoding = 'Latin-1')
autos.head(3)<jupyter_output><empty_output><jupyter_text>Cleaning Column Names<jupyter_code>autos.columns
autos.columns = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'ab_test', 'vehicle_type', 
                 'registration_year', 'gearbox', 'power', 'model', 'odometer', 'registration_month', 'fuel_type', 
                 'brand', 'unrepaired_damage', 'date_created', 'number_pictures', 'postal_code','last_seen']
len(autos) #371528 rows imported
len(autos.columns) #20 columns imported<jupyter_output><empty_output><jupyter_text>Renaming column<jupyter_code>autos.rename({'odometer':'odometer_km'}, axis = 1, inplace = True)<jupyter_output><empty_output><jupyter_text>Changing Type<jupyter_code>for col in ['date_crawled', 'date_created', 'last_seen']:
    autos[col] = pd.to_datetime(autos[col])
for col in ['name', 'seller', 'offer_type', 'ab_test', 'vehicle_type', 'gearbox', 'model', 'fuel_type', 'power']:
    autos[col] = autos[col].astype('category')<jupyter_output><empty_output><jupyter_text>zfill consider whole postal code equal to zero rather than considering it as a some number<jupyter_code>autos['postal_code'] = autos['postal_code'].astype('str').str.zfill(5)
autos['odometer_km'] = autos['odometer_km'].astype('float')
autos['price'] = autos['price'].astype('float')<jupyter_output><empty_output><jupyter_text>Initial Exploration and Cleaning<jupyter_code>autos['unrepaired_damage'].unique()
autos['number_pictures'].unique()
autos['number_pictures'].nunique()
autos.drop('number_pictures', axis = 1, inplace = True)
autos.drop('unrepaired_damage', axis = 1, inplace = True)
autos.drop('offer_type', axis = 1, inplace = True)
autos.drop('seller', axis = 1, inplace = True)
autos.head(3)
autos.info()
autos.describe(include='all')<jupyter_output><empty_output><jupyter_text>Finding Outliers ***Find outliers with the Interquartile Range where q1 is 25 percentile and q3 75 percentile value of .describe()***<jupyter_code>autos['price'].describe().apply(lambda x: format(x, 'f')) #scientific notation suppressed.
autos['odometer_km'].describe().apply(lambda x: format(x, 'f'))
cols = ['price', 'odometer_km']
q1 = autos[cols].quantile(0.25)
q3 = autos[cols].quantile(0.75)
iqr = q3 - q1

# Return a boolean array of the rows with (any) non-outlier column values
condition = ~((autos[cols] < (q1 - 1.5 * iqr)) | (autos[cols] > (q3 +1.5 * iqr))).any(axis = 1)

# Filter our dataframe based on condition
filtered_autos = autos[condition]
len(filtered_autos)<jupyter_output><empty_output><jupyter_text>**Original Rows: 371528**\**After outliers**\**Adjusted Rows: 295620**  Exploring the Odometer and Price Columns<jupyter_code>filtered_autos['odometer_km'].value_counts()<jupyter_output><empty_output><jupyter_text>It can be seen that the values in this field are rounded, which might indicate that sellers had to choose from pre-set options for this field. Additionally, there are more high mileage than low mileage vehicles.<jupyter_code>filtered_autos['price'].describe()
print('Total Number of Unique Values:',filtered_autos['price'].nunique())
print('Lowest Price:',filtered_autos['price'].min())
print('Highest Price:',filtered_autos['price'].max())<jupyter_output>Total Number of Unique Values: 2990
Lowest Price: 0.0
Highest Price: 16270.0<jupyter_text>Removing values which display no price.<jupyter_code>filtered_price = filtered_autos[filtered_autos['price'].between(1, 16270)]<jupyter_output><empty_output><jupyter_text>Exploring the Date Columns<jupyter_code>#showing distribution of values for dates
#to include missing values in the distribution and to use percentile instead of counts(dropna).
filtered_autos[['date_crawled','date_created','last_seen',]].value_counts(normalize = True).sort_index().head(10)
autos['registration_year'].describe()
filtered_autos['registration_year'].describe()<jupyter_output><empty_output><jupyter_text>Dealing with Incorrect Registration Year DataA car can't be first registered before the listing was seen, any vehicle with a registration year above 2016 is definitely inaccurate. Determining the earliest valid year is more difficult. Realistically, it could be somewhere in the first few decades of the 1900s.The above mentioned problem can be solved by remove the listings with these values. Let's determine what percentage of our data has invalid values in this column:<jupyter_code>filtered_autos_1900_2016 = filtered_autos[filtered_autos['registration_year'].between(1900, 2016)]
filtered_autos_1900_2016['registration_year'].value_counts(normalize = True)<jupyter_output><empty_output><jupyter_text>It appears that most of the vehicles were first registered in the past 20 years. Exploring Price & Mileage by Brand<jupyter_code>filtered_autos['brand'].value_counts()<jupyter_output><empty_output><jupyter_text>Selecting top 20 brands.<jupyter_code>first20_brands = filtered_autos['brand'].value_counts().index[0:20]
first20_brands<jupyter_output><empty_output><jupyter_text>Below we will construct a dictionary with unique brand name as key and mean value of price and mileage as value.<jupyter_code>mean_price = {}
mean_mileage = {}
for key in first20_brands:
    selected_top20_brands = filtered_price[filtered_price['brand'] == key]
    mean_price[key] = round(selected_top20_brands['price'].agg(np.mean), 4)
    mean_mileage[key] = round(selected_top20_brands['odometer_km'].mean(), 4)
    
mean_price
mean_mileage<jupyter_output><empty_output><jupyter_text>Converting from dictionary to dataframe.<jupyter_code>def top20_brands(x, y):
    
    mean_price_df = pd.DataFrame.from_dict(x, orient = 'index', columns = ['mean_price'])
    mean_mileage_df = pd.DataFrame.from_dict(y, orient = 'index', columns = ['mean_mileage'])
    
    mean_price_mileage = pd.concat([mean_price_df, mean_mileage_df], axis = 1)
    
    return mean_price_mileage
    
top20_brands(mean_price, mean_mileage)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11908234982097865, 'fraction_numerical': 0.017239092958493567, 'mean_word_length': 6.142045454545454, 'pattern_counts': {'":': 0, '<': 65, '<?xml version=': 0, '>': 65, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '592333', 'license': 'Unlicense', 'n_tokens_mistral': 2589, 'n_tokens_neox': 2400, 'n_words': 669, 'path': 'Exploring eBay Cars Sales Data_Project/Exploring eBay Cars Sales Data.ipynb', 'repo_name': 'samadkhanyz/Data-Analyst-Portfolio', 'size': '7541'}