<jupyter_start><jupyter_text>About the projectThe aim of this priject is to clean the data and analyze the included used car listings.The used data set from Ebay Kleinzeigen, a classifyied section of German Website. The data set was originally scraped and uploaded to Kaggle.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.* 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 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.<jupyter_code>import pandas as pd
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>Observations:    - The data set has 20 columns and mostly are strings.    - all columns have null-value    - only five colums are int and the reast are objects. e.g. price, odometr, date and last seen are numbers but the type is object.    - column names are not of lower register    - some columns don't have entries e.g. vehicleType, gearbox, model, fuel type, not repaird damage.<jupyter_code>autos.columns<jupyter_output><empty_output><jupyter_text>The code below converts clumn names to snakecase.The column names uses caelcase instead of Python's preferred snakecase.<jupyter_code>cols_edit = ['date_crawled', 'name', 'seller', 'offer_type', 'price', 'abtest',
       'vehicle_type', 'registration_year', 'gearbox', 'power_ps', 'model',
       'odometer', 'registration_month', 'fuel_type', 'brand',
       'urepaired_damage', 'ad_created', 'nr_of_pictures', 'postal_code',
       'last_seen']
autos.columns = cols_edit
#chek edited columns
autos.head()<jupyter_output><empty_output><jupyter_text>Basic data exploration and cleaning<jupyter_code>autos.describe(include="all")<jupyter_output><empty_output><jupyter_text>Columns that have mostly one value and candidated to be dropped:    - seller, offer type, abtest, gearbox, unrepaired damage, nr_of_picturesColumns to investigate more:    - data_crawled, ad_created, nameNumeric columns to be cleaned:    - odometr, price,   Columns investigationColumns date_crawled, ad_created and last_seen are dates. These columns can be converted to date format<jupyter_code>autos.info()
print(autos["date_crawled"].unique())
print(autos["ad_created"].unique())
autos["name"].unique()<jupyter_output><empty_output><jupyter_text>Columns date_crawled and ad_created are dates. These columns can be converted to date format  Data claning<jupyter_code>autos["price"].unique()
autos["price"] = (autos["price"].str.replace("$", "")
                      .str.replace(",", "")
                      .astype(int)
                 )
autos["odometer"].unique()
autos["odometer"] = (autos["odometer"].str.replace("km", "")
                         .str.replace(",", "")
                         .astype(int)
                    )
autos["odometer_km"] = autos["odometer"]<jupyter_output><empty_output><jupyter_text>Data analysis (odometer_km, price) Odometer_km - Investigation of outliers<jupyter_code>print(autos["odometer_km"].unique())
print(autos["odometer_km"].unique().shape)
print(autos["odometer_km"].value_counts())
autos["odometer_km"].describe()<jupyter_output>[150000  70000  50000  80000  10000  30000 125000  90000  20000  60000
   5000 100000  40000]
(13,)
150000    32424
125000     5170
100000     2169
90000      1757
80000      1436
70000      1230
60000      1164
50000      1027
5000        967
40000       819
30000       789
20000       784
10000       264
Name: odometer_km, dtype: int64<jupyter_text>No outliers have been observed in the column odometer_km. The column has been kept withput any changes. It seems people temd to round the odoemeter values since there are only 13 unique values out of 50K. Price - Investigation of outliers.<jupyter_code>print(autos["price"].unique())
print(autos["price"].unique().shape)
print(autos["price"].describe())
autos["price"].value_counts().head(10)<jupyter_output>[ 5000  8500  8990 ...   385 22200 16995]
(2357,)
count    5.000000e+04
mean     9.840044e+03
std      4.811044e+05
min      0.000000e+00
25%      1.100000e+03
50%      2.950000e+03
75%      7.200000e+03
max      1.000000e+08
Name: price, dtype: float64<jupyter_text>There are 1421 ads with price 0$. This is 2% of all data. So, these rows can be removed.The highest price is one hundread million dollars. It seems very high price. Let's look at the highest prices.<jupyter_code>autos["price"].value_counts().sort_index(ascending=False).head(20)
autos["price"].value_counts().sort_index(ascending=True).head(20)<jupyter_output><empty_output><jupyter_text>There are 15 listings with a price over \$1M what is less than \0.001% of all listings. And 1500 listings with price lower by \$30 including 1421 listings at price 0$.Apparently the price of \$1 used to make a bit on eBay. Therefore, considering to keep listing at \$1 and higer and removing listing above $350K.<jupyter_code>autos = autos[autos["price"].between(1,351000)]
autos["price"].describe()<jupyter_output><empty_output><jupyter_text>Exploring Data Columns There are 5 columns that should represent date values. Some of these columns were created by the crawler, some came from the website itself. We can differentiate by referring to the data dictionary:- `date_crawled`: added by the crawler- `last_seen`: added by the crawler- `ad_created`: from the website- `registration_month`: from the website- `registration_year`: from the website<jupyter_code>autos[['date_crawled','ad_created','last_seen']][0:5]
(autos['date_crawled']
     .str[:10]
     .value_counts(normalize=True, dropna=False)
     .sort_index(ascending=True)
    )
(autos['date_crawled']
     .str[:10]
     .value_counts(normalize=True, dropna=False)
     .sort_values(ascending=True)
    )<jupyter_output><empty_output><jupyter_text>It looks like the sate was crawled every day over a month in the period March-April 2016. The listing  distribution looks roughly uniform.<jupyter_code>(autos['ad_created']
     .str[:10]
     .value_counts(normalize=True, dropna=False)
     .sort_index(ascending=True)
    )
(autos['ad_created']
     .str[:10]
     .value_counts(normalize=True, dropna=False)
     .sort_values(ascending=True)
    )<jupyter_output><empty_output><jupyter_text>The more ads were created in March-April 2016. In the period November 2015 - February 2016 the ads activity is quite low. Not many ads were published.<jupyter_code>(autos['last_seen']
     .str[:10]
     .value_counts(normalize=True, dropna=False)
     .sort_index(ascending=True)
    )
(autos['last_seen']
     .str[:10]
     .value_counts(normalize=True, dropna=False)
     .sort_values(ascending=True)
    )
autos["registration_year"].describe()<jupyter_output><empty_output><jupyter_text>Min and max are odd values. Min year is far from the your of first invented car in 1895. The max year is from far future.  Investigation of registration year  The outliers of registration column should be removed. Before it will br done let's seew what's the percentage of the data has odd values.The first car was invented in 1895. Therefore, the year range should be 1895 - 2016.<jupyter_code>(~autos["registration_year"].between(1900,2016)).sum() / autos.shape[0]<jupyter_output><empty_output><jupyter_text>There are 4% listings out of the year range. Therefore, these listings will be removed.<jupyter_code>autos = autos[autos["registration_year"].between(1895,2016)]
print(autos["registration_year"].describe())
autos["registration_year"].value_counts(normalize=True)<jupyter_output>count    46681.000000
mean      2002.910756
std          7.185103
min       1910.000000
25%       1999.000000
50%       2003.000000
75%       2008.000000
max       2016.000000
Name: registration_year, dtype: float64<jupyter_text>The more cars of this distribution have been registered in the period 1994-2016.  Exploring Price by Brand<jupyter_code>print(autos["brand"].unique())
autos["brand"].value_counts(normalize=True)<jupyter_output>['peugeot' 'bmw' 'volkswagen' 'smart' 'ford' 'chrysler' 'seat' 'renault'
 'mercedes_benz' 'audi' 'sonstige_autos' 'opel' 'mazda' 'porsche' 'mini'
 'toyota' 'dacia' 'nissan' 'jeep' 'saab' 'volvo' 'mitsubishi' 'jaguar'
 'fiat' 'skoda' 'subaru' 'kia' 'citroen' 'chevrolet' 'hyundai' 'honda'
 'daewoo' 'suzuki' 'trabant' 'land_rover' 'alfa_romeo' 'lada' 'rover'
 'daihatsu' 'lancia']<jupyter_text>Despite the fact that half of listings presented by German brends. Let's look at the prices of top-10 brands. - volkswagen- bmw- opel- mercedes_benz- audi- ford- renault- peugeot- fiat- seat<jupyter_code>brand = autos["brand"].value_counts().head(10).index
brand_mean_price = {}

for b in brand:
    selected_rows = autos[autos['brand'] == b]
    mean_price = selected_rows['price'].mean()
    brand_mean_price[b] = int(mean_price)
    
brand_mean_price<jupyter_output><empty_output><jupyter_text>There is a price gap between popular brend:- The last top-5 brends, Seat, Feat, Peugeot, Renault, Ford are in price rnge of \$2474 - 4397. - The most expensive brands are Audi, BMW, Mercedes-Benz with average price of \$8300-9300. - The most popular brens, Volkswagen, has average price of \$5400. That is between the cheap brands and expensive ones. It may be the best option of cheap and expensive worlds.  Exploring Mileage For the top 10 brands, let's use aggregation to understand the average mileage for those cars and if there's any visible link with mean price.<jupyter_code>bmp_series = pd.Series(brand_mean_price)
bmp_series
pd.DataFrame(bmp_series, columns=['mean_mileage'])
brand_mean_mileage = {}

for b in brand:
    selected_rows = autos[autos['brand'] == b]
    mean_mileage = selected_rows['odometer_km'].mean()
    brand_mean_mileage[b] = int(mean_mileage)
    
brand_mean_mileage
mean_price = pd.Series(brand_mean_price).sort_values(ascending=False)
mean_mileage = pd.Series(brand_mean_mileage).sort_values(ascending=False)
brand_mean_price_mileage = pd.DataFrame(mean_mileage, columns=['mean_mileage'])
brand_mean_price_mileage
brand_mean_price_mileage['mean_price'] = mean_price
brand_mean_price_mileage<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09969055783223217, 'fraction_numerical': 0.05461236096010705, 'mean_word_length': 4.233260393873086, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, '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': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '18434', 'license': 'MIT', 'n_tokens_mistral': 4247, 'n_tokens_neox': 3690, 'n_words': 1172, 'path': 'Guded Project_ Exploring Ebay Car Sales Data.ipynb', 'repo_name': 'kamkanna/DataQuest', 'size': '11957'}