<jupyter_start><jupyter_text>Research on car sales adsYou're an analyst at Crankshaft List. Hundreds of free advertisements for vehicles are published on your site every day.You need to study data collected over the last few years and determine which factors influence the price of a vehicle.   Step 1. Open the data file and study the general information.<jupyter_code>import pandas as pd
df = pd.read_csv('/datasets/vehicles_us.csv')
print(df.head())
print(df.tail())
print(df.info())
print(df.describe()) 
print(len(df))
print(df.sample(10))

# checking for missing values at random:

model_year_by_type = df.groupby('type').agg({'model_year': lambda x: x.isnull().sum()})

df_type = df.groupby('type').count()

model_year_missing_v = model_year_by_type / df_type

print(round(model_year_missing_v.dropna(axis=1)*100, 2).astype(str)+'%')

model_odometer_v = df.groupby('condition').agg({'odometer': lambda x: x.isnull().sum()})

df_condition = df.groupby('condition').count()

odometer_missing_v = model_odometer_v / df_condition 
                    
print(round(odometer_missing_v.dropna(axis=1) * 100, 2).astype(str)+'%')

df_paint_color = df.groupby('type').agg({'paint_color': lambda x: x.isnull().sum()})<jupyter_output>model_year
type                  
SUV               8.0%
bus               0.0%
convertible      6.44%
coupe            6.72%
hatchback        7.49%
mini-van          8.0%
offroad           4.9%
other             9.4%
pickup           7.77%
sedan             7.5%
truck            7.26%
van              6.57%
wagon            7.69%
          odometer
condition         
excellent   18.12%
fair         17.3%
good        18.13%
like new    18.14%
new          14.4%
salvage     17.35%<jupyter_text>In order to check whether the data is missing at random or not I did calculate the percentage of missing value for model year from total model years values grouped by type and odometer missing values from the total odometer values grouped by condition, based on that calculation I can conclude that that the data is missing at random at this columns.  Conclusion After checking the data I found out that there is missing values at the 'model_year', 'cylinders', 'odometer', 'paint_color' and 'is_4wd' columns that need to be treated but first of all I wanna get rid of all duplicates and work with up to date data.I also assumed that there may be same ads that were placed more than once but with different dates and left only the latest ads that were published.  Step 2. Data preprocessing<jupyter_code>dup = df.duplicated().sum()

print(dup) # there is no duplicates

#I assumed that there may be same ads that were placed more than once but with different dates:

duplicate = df[df.duplicated(['price', 'odometer', 'model', 'model_year', 'paint_color', 'is_4wd', 'type', 'fuel', 'cylinders'])]

print((duplicate).sort_values(['price', 'odometer', 'model', 'model_year', 'paint_color', 'is_4wd', 'type', 'fuel', 'cylinders']))

#I left only the latest ads that were published:

df = df.sort_values('date_posted', ascending = False).drop_duplicates(['price', 'odometer', 'model', 'model_year', 'paint_color', 'is_4wd', 'type', 'fuel', 'cylinders'],keep = 'first')
                                                     
#dealing with missing values:

print(df[df['model_year'].isnull()].count())

df['model_year'] = df['model_year'].fillna(df.groupby('model')['model_year'].transform('median'))

print(df[df['cylinders'].isnull()].count())

df['cylinders'] = df.groupby(['model', 'model_year'])['cylinders'].fillna(method='ffill')

print(df[df['odometer'].isnull()].count())

df['odometer'] = df['odometer'].fillna(df.groupby('model_year')['odometer'].transform('median'))

print(df[df['paint_color'].isna()].count())

df['paint_color'] = df['paint_color'].fillna(value = 'Not reported')

print(df[df['is_4wd'].isnull()].count()) # There is only values = 1, so it's reasonable to assume that the missing values = 0

df['is_4wd'] = df['is_4wd'].fillna(value = 0) # Fill the missing values with 0

df['is_4wd'] = df['is_4wd'].astype(int) # Integer type more appropriate

df.info()

#there is still 256 'cylinders' missing values, it's less than 1% so I decided to get rid of them:

df = df.dropna()

df<jupyter_output>0
        price  model_year            model  condition  cylinders    fuel  \
23602       1      2007.0  chevrolet tahoe   like new        8.0     gas   
23804       1      2007.0  chevrolet tahoe   like new        8.0     gas   
23811       1      2007.0  chevrolet tahoe   like new        8.0     gas   
23988       1      2007.0  chevrolet tahoe   like new        8.0     gas   
24989       1      2007.0  chevrolet tahoe   like new        8.0     gas   
...       ...         ...              ...        ...        ...     ...   
15788   59995      2019.0         ram 2500   like new        6.0  diesel   
1891    60774      2018.0  chevrolet tahoe  excellent        8.0     gas   
27375  189000      2014.0       ford f-150       good        6.0     gas   
33434  189000      2014.0       ford f-150       good        6.0     gas   
34389  189000      2014.0       ford f-150       good        6.0     gas   

       odometer transmission   type paint_color  is_4wd date_posted  \
23602       1.[...]<jupyter_text>After checking missing values at 'is_4wd' column Under the 'is_4wd' column there is only values = 1, so it's reasonable to assume that the missing values = 0, I think just because the intention of empty cell was that w4d is equal to zero.Same logic I can assume for odometer missing values of the new cars. I will let know those who are responsible for data collection to fill the missing values with zeros in order to avoid this in future.I filled missing values with median value calculation by each missing value type, based on that median value is the more common value on the market.   Step 3. Make calculations and add them to the table<jupyter_code>df['date_posted'] = pd.to_datetime(df['date_posted'])
df['day_of_the_week'] = df['date_posted'].dt.weekday
df['month'] = df['date_posted'].dt.month
df['year'] = df['date_posted'].dt.year
df["vehicle's_age"] = df['year'] - df['model_year']

for i in df['odometer']:
    if i > 0 :
        df['average_mileage'] = df['odometer'] / df["vehicle's_age"]
    else:
        df['average_mileage'] = 0
          
df['average_mileage'] = df['average_mileage'].round(decimals = 3)

print(df[(df['condition'] == 'new')&(df['odometer'] > 1000)]) #there is vehicles that uder new condition but they drived more then 1000 km 

df.loc[(df['condition'] == 'new')&(df['odometer'] > 1000),'condition']='like new'    

def numeric_scale(condition):
    if condition == 'new' :
        return 5
    elif condition == 'like new':
        return 4
    elif condition == 'excellent':
        return 3
    elif condition == 'good':
        return 2
    elif condition == 'fair':
        return 1  
    elif condition == 'salvage':
        return 0 
df['condition_numeric_scale'] = df['condition'].apply(numeric_scale)

df['condition_numeric_scale'] = df['condition_numeric_scale'].astype(int) # Integer type more appropriate


print(df.info())
print(df)<jupyter_output>price  model_year                       model condition  cylinders  \
25357  54000      2018.0                  ford f-250       new        8.0   
2170    8300      2013.0                toyota camry       new        4.0   
32544   4999      2011.0                chrysler 200       new        4.0   
47905  13900      2008.0               jeep wrangler       new        6.0   
27567  38000      2018.0                  ford f-150       new        6.0   
...      ...         ...                         ...       ...        ...   
26906  14995      2002.0          chevrolet corvette       new        8.0   
47747  14600      2013.0             ford expedition       new        8.0   
17672  58500      2018.0  chevrolet silverado 3500hd       new        8.0   
20539    250      2018.0                toyota camry       new        4.0   
13208      1      2018.0                 toyota rav4       new        4.0   

         fuel  odometer transmission   type paint_color  is_4wd date_posted[...]<jupyter_text>Step 4. Carry out exploratory data analysis<jupyter_code># Let's study the outliers:

import matplotlib.pyplot as plt

columns_name = ['price', "vehicle's_age", 'odometer', 'cylinders', 'condition_numeric_scale']

for name in columns_name:
    Q1 = df[name].quantile(0.25)
    Q3 = df[name].quantile(0.75)
    df.boxplot(column = name)
    plt.hlines(y=[Q1, Q3], xmin=0.9, xmax=1.1, color='red') 
    plt.show()

# In order determine which factors influence the price of a vehicle I need to ges rid of outliers:

for name in columns_name:
    Q1 = df[name].quantile(0.25)
    Q3 = df[name].quantile(0.75)
    IQR = Q3 - Q1
    if name == 'price':
        df_out = df[(df[name] > Q1) & (df[name] < Q3 + 1.5 * IQR)] # there is a sharp spike around 0 price which make no sense
        if name == "vehicle's_age":
            df_out = df[df[name] < Q3 + 1.5 * IQR] # it's legitimate that there are also new cars for sale  
            if name == 'odometer':
                df_out = df[df[name] < Q3 + 1.5 * IQR] # new cars usually has 0 mileage
                if name == 'cylinders':
                    df_out = df[(df[name] > Q1 - IQR * 1.5) & (df['cylinders'] < Q3 + 1.5 * IQR)]
                    if name == 'condition_numeric_scale':
                        df_out = df[(df[name] > Q1 - IQR * 1.5) & (df['condition_numeric_scale'] < Q3 + 1.5 * IQR)]     

# Comparing original data with without outliers data:

import matplotlib.pyplot as plt
bins = 30
for name in columns_name:
    df.hist(column = name, bins = bins);
    plt.xlabel('original data')
    plt.ylabel('amount of ads');
    df_out.hist(column = name, bins = bins);
    plt.xlabel('without outliners')
    plt.ylabel('amount of ads');
    plt.show()

df_out.hist(column = 'days_listed', bins = 30)
plt.show()
    
median_ad = df_out['days_listed'].median() 

mean_ad = df_out['days_listed'].mean()

print('The median time advertisements were displayed for {:.0f}'.format(median_ad), 'days')
print()
print('The mean time advertisements were displayed for {:.0f}'.format(mean_ad), 'days')
print()

Q1 = df['days_listed'].quantile(0.25)
Q3 = df['days_listed'].quantile(0.75)

print('Typical lifetime of an ad between {:.0f}'.format(Q1), 'and {:.0f}'.format(Q3), 'days') # I took a IQR as typical life time of an ad

quickly_ads = df_out[df_out['days_listed'] < Q1] # all the ads that removed quickly 

long_ads = df_out[df_out['days_listed'] > Q3] # all the ads that listed for abnormally long time
print()



for name in columns_name:
    print('the correlation of "quick ads"',name,'on days_listed ads :{:.3f}'.format(quickly_ads[name].corr(quickly_ads['days_listed'])))          
    print('the correlation of "long ads"',name,'on days_listed ads :{:.3f}'.format(long_ads[name].corr(long_ads['days_listed'])))          
# There is no dependency between the quantitative columns in columns_name and days_listed.<jupyter_output><empty_output><jupyter_text>After checking the variables distributions I pay attention to that there is a sharp spike around 0 on the graf price which make no sense, in order to know wich factor influence the price of vehiclesI determined the typical price based on IQR method, based on that method I got rid from other outliers as well, for example vehicle's age which is really old and much above the typical market age does not reflect the influence of the age on average market price and needs to be excluded. The outliers affect the average and don't let to see the real common of things, after I got rid of them you can see the effect on the graphs which is show more realistic influence of price factors.<jupyter_code># I will plot scatter for the categorical columns and lets see:

cat_columns =['fuel', 'transmission', 'type', 'paint_color']

for name in cat_columns:
    plt.scatter(df[name].values, df['days_listed'], color="blue", alpha=0.5)
    plt.gcf().set_size_inches((12, 9))  
    plt.show()   
      
# everything becomes very clear as we look at the graphs for example the shortest ads have bus type

# and as more rare the color of a car then shorter the time of an ad (orange and purple for example)

#In order to nalyze the number of ads and the average price for each type of vehicle:

ads_by_type = df_out.pivot_table(index ='type', values = 'price', aggfunc = 'count').sort_values('price')

ads_by_type.plot(kind = 'bar', figsize=(12, 9))

print(ads_by_type)

num_of_ads_by_type = df_out.pivot_table(index ='type', values = 'model', aggfunc = 'count').sort_values('model')

num_of_ads_by_type.plot(kind = 'bar', figsize=(12, 9))

# two types with the greatest number of ads are truck and SUV and apparently are most expensive.


print('SUV price depends:')

df_SUV = df_out[(df_out['type'] == 'SUV')] 

price_SUV_corr = df_SUV[['price', "vehicle's_age", 'odometer', 'condition_numeric_scale']]

pd.plotting.scatter_matrix(price_SUV_corr, figsize=(9, 9))

print(price_SUV_corr.corr())

print()

print('truck price depends:')

print()

df_truck = df_out[(df_out['type'] == 'truck')] 

price_truck_corr = df_truck[['price', "vehicle's_age", 'odometer', 'condition_numeric_scale']]

pd.plotting.scatter_matrix(price_truck_corr, figsize=(9, 9))

print(price_truck_corr.corr())

import seaborn as sns

# In order to filter categorical variables that have at least 50 ads I sorted them in ascending order 

print(df_SUV.groupby('transmission').count().sort_values('paint_color')) 

print(df_SUV.groupby('paint_color').count().sort_values('transmission')) # In need to exclude Not reported, purple and yellow colors

Not = pd.Series(['purple', 'yellow','Not reported'])
                
df_SUV = df_SUV.loc[~df_SUV['paint_color'].isin(Not)]
print()
print(df_SUV['paint_color'].value_counts())

sns.boxplot( x = df_SUV['paint_color'], y = df_SUV['price'])

sns.boxplot(x = df_SUV['transmission'], y = df_SUV['price'])

# In order to filter categorical variables that have at least 50 ads I sorted them in ascending order 

print(df_truck.groupby('paint_color').count().sort_values('transmission')) # In need to exclude Not reported, purple, orange, yellow colors

Not = pd.Series(['purple', 'yellow', 'orange','Not reported'])
                
df_track = df_truck.loc[~df_truck['paint_color'].isin(Not)]
print()
print(df_truck['paint_color'].value_counts())

sns.boxplot( x = df_truck['paint_color'], y = df_truck['price'])

print(df_truck.groupby('transmission').count().sort_values('paint_color')) 

# There is less then 50 ads for 'other' under 'transmission' column for track category so I need to ged rid of them:

df_truck = df_truck.loc[df_truck['transmission'] != 'other']

sns.boxplot( x = df_truck['transmission'], y = df_truck['price'])<jupyter_output>price  model_year  model  condition  cylinders  fuel  odometer  \
transmission                                                                   
other            51          51     51         51         51    51        51   
manual          266         266    266        266        266   266       266   
automatic      7539        7539   7539       7539       7539  7539      7539   

              type  paint_color  is_4wd  date_posted  days_listed  \
transmission                                                        
other           51           51      51           51           51   
manual         266          266     266          266          266   
automatic     7539         7539    7539         7539         7539   

              day_of_the_week  month  year  vehicle's_age  average_mileage  \
transmission                                                                 
other                      51     51    51             51               51   
manual             [...]
{'fraction_non_alphanumeric': 0.11308245990655386, 'fraction_numerical': 0.04255587826745801, 'mean_word_length': 2.7215695488721803, 'pattern_counts': {'":': 1, '<': 23, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '63000', 'license': 'MIT', 'n_tokens_mistral': 5439, 'n_tokens_neox': 4891, 'n_words': 1564, 'path': 'Car_sales_ads_research/car_sales_ads_research.ipynb', 'repo_name': 'BorisAks/Portfolio', 'size': '15838'}