<jupyter_start><jupyter_text>eBay Car SalesThis is an analysis of car sales data from eBay Kleinanzeigen The 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.<jupyter_code>import numpy as np
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>Data cleaning is needed here, for several reasons:* null values in several columns* some columns should be integer: price, odometer* some columns should be date (so can be used for calculations): dateCrawled, dateCreated, lastSeen* date are all in the format YYYY-MM-DD HH:MM:SS where HH is the 24 hour method* name column could have underscore replaced with space and potentially other parsing First the column names need to be cleaned up to make them more descriptive and also convert camelcase to snakecase<jupyter_code>cols = autos.columns.copy()
print(cols)
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')
autos["nr_of_pictures"].value_counts()<jupyter_output><empty_output><jupyter_text>Observations on columns* The "nr_of_pictures" column can be dropped as all values are 0 (not useful)* The "seller" column are all "privat" (not useful)* The "offer_type" column are all "Angebot" (not useful)* The "registration_year" column has invalid years (1000 and 9999)* The "power_ps" (care horsepower) has some invalid data like 17700 or 0 (should be NaN?)* The "price" and "odometer" columns should be numeric, after cleaning* Null values need to be investigated in:  - vehicleType            44905 non-null object  - gearbox                47320 non-null object  - model                  47242 non-null object  - fuelType               45518 non-null object  - notRepairedDamage      40171 non-null object* Some "registration_month" columns have 0 value, maybe these should be NaN<jupyter_code>autos["price"] = autos["price"].str.replace("$","")
autos["price"] = autos["price"].str.replace(",","")
autos["price"] = autos["price"].astype(int)

autos["odometer"] = autos["odometer"].str.replace("km","").str.replace(",","")
autos["odometer"] = autos["odometer"].astype(int)
autos.rename(columns={"odometer": "odometer_km"}, inplace=True)

autos.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 50000 entries, 0 to 49999
Data columns (total 20 columns):
date_crawled          50000 non-null object
name                  50000 non-null object
seller                50000 non-null object
offer_type            50000 non-null object
price                 50000 non-null int64
abtest                50000 non-null object
vehicle_type          44905 non-null object
registration_year     50000 non-null int64
gearbox               47320 non-null object
power_ps              50000 non-null int64
model                 47242 non-null object
odometer_km           50000 non-null int64
registration_month    50000 non-null int64
fuel_type             45518 non-null object
brand                 50000 non-null object
unrepaired_damage     40171 non-null object
ad_created            50000 non-null object
nr_of_pictures        50000 non-null int64
postal_code           50000 non-null int64
last_seen             50000 non-null object
dtypes: int64(7), [...]<jupyter_text>Analyze price and odometer_km for outliers* "odometer_km" column looks reasonable for typical car ranges* "price" column has 1,421 values of 0, but this could just mean the seller wants to set no price (either give away car or no reserve price)* "price" column high values are relative (what is a reasonable price for a car), but it seems that prices \$10 million or above would be unreasonable so I set those to NaN<jupyter_code>print(autos["price"].max())
print(autos["price"].min())
print(autos["price"].unique())
print(autos["price"].describe())
print(autos["price"].value_counts().sort_index(ascending=True).head(10))
print(autos["price"].value_counts().sort_index(ascending=False).head(20))
print(autos["odometer_km"].max())
print(autos["odometer_km"].min())
print(autos["odometer_km"].unique())
print(autos["odometer_km"].describe())
print(autos["odometer_km"].value_counts().sort_index(ascending=True).head())
print(autos["odometer_km"].value_counts().sort_index(ascending=False).head())
autos.loc[autos["price"] >= 10000000, "price"] = np.nan
print(autos["price"].value_counts().sort_index(ascending=False).head(20))
print(autos["price"].)<jupyter_output><empty_output><jupyter_text>Now investigate date related columns: date_crawled, last_seen, ad_created, registration_month, and registration_year<jupyter_code>print(autos["date_crawled"].value_counts(normalize=True, dropna=False).sort_index())<jupyter_output>2016-03-05 14:06:30    0.00002
2016-03-05 14:06:40    0.00002
2016-03-05 14:07:04    0.00002
2016-03-05 14:07:08    0.00002
2016-03-05 14:07:21    0.00002
2016-03-05 14:07:26    0.00002
2016-03-05 14:07:40    0.00002
2016-03-05 14:07:45    0.00002
2016-03-05 14:08:00    0.00004
2016-03-05 14:08:05    0.00004
2016-03-05 14:08:27    0.00002
2016-03-05 14:08:42    0.00002
2016-03-05 14:09:02    0.00004
2016-03-05 14:09:05    0.00002
2016-03-05 14:09:20    0.00002
2016-03-05 14:09:22    0.00002
2016-03-05 14:09:38    0.00002
2016-03-05 14:09:46    0.00002
2016-03-05 14:09:56    0.00002
2016-03-05 14:09:57    0.00002
2016-03-05 14:09:58    0.00004
2016-03-05 14:10:18    0.00002
2016-03-05 14:10:20    0.00002
2016-03-05 14:10:46    0.00002
2016-03-05 14:11:03    0.00002
2016-03-05 14:11:05    0.00002
2016-03-05 14:11:14    0.00002
2016-03-05 14:11:15    0.00002
2016-03-05 14:11:25    0.00002
2016-03-05 14:11:40    0.00002
                        ...   
2016-04-07 10:36:19    0.00002
2016-04-[...]<jupyter_text>The dates are evenly distributed and range from 2016-03-05 14:06:30 to 2016-04-07 14:36:56<jupyter_code>print(autos["ad_created"].value_counts(normalize=True, dropna=False).sort_index())<jupyter_output>2015-06-11 00:00:00    0.00002
2015-08-10 00:00:00    0.00002
2015-09-09 00:00:00    0.00002
2015-11-10 00:00:00    0.00002
2015-12-05 00:00:00    0.00002
2015-12-30 00:00:00    0.00002
2016-01-03 00:00:00    0.00002
2016-01-07 00:00:00    0.00002
2016-01-10 00:00:00    0.00004
2016-01-13 00:00:00    0.00002
2016-01-14 00:00:00    0.00002
2016-01-16 00:00:00    0.00002
2016-01-22 00:00:00    0.00002
2016-01-27 00:00:00    0.00006
2016-01-29 00:00:00    0.00002
2016-02-01 00:00:00    0.00002
2016-02-02 00:00:00    0.00004
2016-02-05 00:00:00    0.00004
2016-02-07 00:00:00    0.00002
2016-02-08 00:00:00    0.00002
2016-02-09 00:00:00    0.00004
2016-02-11 00:00:00    0.00002
2016-02-12 00:00:00    0.00006
2016-02-14 00:00:00    0.00004
2016-02-16 00:00:00    0.00002
2016-02-17 00:00:00    0.00002
2016-02-18 00:00:00    0.00004
2016-02-19 00:00:00    0.00006
2016-02-20 00:00:00    0.00004
2016-02-21 00:00:00    0.00006
                        ...   
2016-03-09 00:00:00    0.03324
2016-03-[...]<jupyter_text>The dates look evenly distributed with a reasonable range (assuming this is the original create date and if an ad is reposted, the original date is used)<jupyter_code>print(autos["last_seen"].value_counts(normalize=True, dropna=False).sort_index())<jupyter_output>2016-03-05 14:45:46    0.00002
2016-03-05 14:46:02    0.00002
2016-03-05 14:49:34    0.00002
2016-03-05 15:16:11    0.00002
2016-03-05 15:16:47    0.00002
2016-03-05 15:28:10    0.00002
2016-03-05 15:41:30    0.00002
2016-03-05 15:45:43    0.00002
2016-03-05 15:47:38    0.00002
2016-03-05 15:47:44    0.00002
2016-03-05 16:45:57    0.00002
2016-03-05 16:47:28    0.00002
2016-03-05 17:15:45    0.00002
2016-03-05 17:16:12    0.00002
2016-03-05 17:16:14    0.00002
2016-03-05 17:16:23    0.00002
2016-03-05 17:17:02    0.00002
2016-03-05 17:39:19    0.00002
2016-03-05 17:40:14    0.00002
2016-03-05 17:44:50    0.00002
2016-03-05 17:44:54    0.00002
2016-03-05 17:46:01    0.00002
2016-03-05 18:17:58    0.00002
2016-03-05 18:47:14    0.00002
2016-03-05 18:50:38    0.00002
2016-03-05 19:15:08    0.00002
2016-03-05 19:15:20    0.00002
2016-03-05 19:15:42    0.00002
2016-03-05 19:16:36    0.00002
2016-03-05 19:17:17    0.00002
                        ...   
2016-04-07 14:58:09    0.00004
2016-04-[...]<jupyter_text>These dates look evenly distributed and are closer to the "date_crawled" column<jupyter_code>print(autos["registration_month"].describe())
print(autos["registration_year"].describe())<jupyter_output>count    50000.000000
mean         5.723360
std          3.711984
min          0.000000
25%          3.000000
50%          6.000000
75%          9.000000
max         12.000000
Name: registration_month, dtype: float64
count    50000.000000
mean      2005.073280
std        105.712813
min       1000.000000
25%       1999.000000
50%       2003.000000
75%       2008.000000
max       9999.000000
Name: registration_year, dtype: float64<jupyter_text>As mentioned before, the registration_month contains 0 values when it should be 1-12.Also the registration year has invalid min (1000) and max (9999) values. For this column, a reasonable range is 1900 to 2016. 1900 because that would be likely the earliest cars could be registered and 2016 because the listings end in 2016 (can't register car after a listing).<jupyter_code>autos.loc[~(autos["registration_year"].between(1900,2016)), "registration_year"] = np.nan
print(autos["registration_year"].value_counts(normalize=True).sort_index())<jupyter_output>1910.0    0.000187
1927.0    0.000021
1929.0    0.000021
1931.0    0.000021
1934.0    0.000042
1937.0    0.000083
1938.0    0.000021
1939.0    0.000021
1941.0    0.000042
1943.0    0.000021
1948.0    0.000021
1950.0    0.000062
1951.0    0.000042
1952.0    0.000021
1953.0    0.000021
1954.0    0.000042
1955.0    0.000042
1956.0    0.000104
1957.0    0.000042
1958.0    0.000083
1959.0    0.000146
1960.0    0.000708
1961.0    0.000125
1962.0    0.000083
1963.0    0.000187
1964.0    0.000250
1965.0    0.000354
1966.0    0.000458
1967.0    0.000562
1968.0    0.000541
            ...   
1987.0    0.001562
1988.0    0.002957
1989.0    0.003769
1990.0    0.008224
1991.0    0.007412
1992.0    0.008141
1993.0    0.009265
1994.0    0.013742
1995.0    0.027338
1996.0    0.030066
1997.0    0.042225
1998.0    0.051074
1999.0    0.062464
2000.0    0.069834
2001.0    0.056280
2002.0    0.052740
2003.0    0.056779
2004.0    0.056988
2005.0    0.062776
2006.0    0.056384
2007.0    0.047972
2008.0    0.[...]<jupyter_text>The "registration_year" column looks reasonable now  Aggregate across brands to analyze car brand trends<jupyter_code>print(autos["brand"].value_counts().head(20))<jupyter_output>volkswagen        10687
opel               5461
bmw                5429
mercedes_benz      4734
audi               4283
ford               3479
renault            2404
peugeot            1456
fiat               1308
seat                941
skoda               786
mazda               757
nissan              754
citroen             701
smart               701
toyota              617
sonstige_autos      546
hyundai             488
volvo               457
mini                424
Name: brand, dtype: int64<jupyter_text>The above are the top 20 car brands in eBay Germany auto sales, therefore this will be the focus of the aggregation<jupyter_code>brands_price = {}
top_20 = autos["brand"].value_counts().head(20).index
print(top_20)

for b in top_20:
    brands_row = autos[autos["brand"] == b]
    brands_price[b] = brands_row["price"].mean()

print(brands_price)<jupyter_output>Index(['volkswagen', 'opel', 'bmw', 'mercedes_benz', 'audi', 'ford', 'renault',
       'peugeot', 'fiat', 'seat', 'skoda', 'mazda', 'nissan', 'citroen',
       'smart', 'toyota', 'sonstige_autos', 'hyundai', 'volvo', 'mini'],
      dtype='object')
{'mercedes_benz': 29511.955428812842, 'nissan': 4588.879310344828, 'fiat': 12134.20642201835, 'opel': 5106.092657022524, 'renault': 2351.301996672213, 'toyota': 5097.941653160454, 'smart': 3482.971469329529, 'seat': 4219.431455897981, 'citroen': 42657.46362339515, 'sonstige_autos': 38300.84065934066, 'ford': 7105.662546708824, 'volvo': 31689.908096280087, 'audi': 8965.560354891431, 'volkswagen': 6384.167399644428, 'hyundai': 5316.754098360656, 'mazda': 3962.542932628798, 'peugeot': 3010.8688186813188, 'skoda': 6305.044529262086, 'bmw': 8252.918953766808, 'mini': 10392.393867924528}<jupyter_text>Now check the mean mileage to see how it compares with brand prices<jupyter_code>mileage = {}

for b in top_20:
    mileage_row = autos[autos["brand"] == b]
    mileage[b] = mileage_row["odometer_km"].mean()

print(mileage)
bmp_series = pd.Series(brands_price)
bmp = pd.DataFrame(bmp_series, columns=['mean_price'])
mm_series = pd.Series(mileage)

bmp['mean_mileage'] = mm_series

print(bmp.sort_values("mean_mileage", ascending=False))<jupyter_output>mean_price   mean_mileage
volvo           31689.908096  138632.385120
bmw              8252.918954  132521.643028
mercedes_benz   29511.955429  130886.142797
audi             8965.560355  129643.941163
opel             5106.092657  129298.663248
volkswagen       6384.167400  128955.272761
renault          2351.301997  128223.793677
peugeot          3010.868819  127352.335165
mazda            3962.542933  125132.100396
ford             7105.662547  124131.934464
seat             4219.431456  122061.636557
citroen         42657.463623  119764.621969
nissan           4588.879310  118978.779841
fiat            12134.206422  117037.461774
toyota           5097.941653  115988.654781
skoda            6305.044529  110947.837150
hyundai          5316.754098  106782.786885
smart            3482.971469  100756.062767
mini            10392.393868   89375.000000
sonstige_autos  38300.840659   87188.644689
{'fraction_non_alphanumeric': 0.11132690139080316, 'fraction_numerical': 0.24389174288936225, 'mean_word_length': 3.0546101092202185, 'pattern_counts': {'":': 1, '<': 41, '<?xml version=': 0, '>': 42, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '288415', 'license': 'MIT', 'n_tokens_mistral': 8164, 'n_tokens_neox': 5721, 'n_words': 1485, 'path': 'dataquest/ebay_car_sales/EBay Car Sales.ipynb', 'repo_name': 'arturbacu/data-science', 'size': '15962'}