<jupyter_start><jupyter_text>Handling missing values<jupyter_code>data1.isnull().sum()
data1[data1['fuel'].isnull()]['vehicleClass'].value_counts()
sop=data1[data1['vehicleClass']=='Trailer For Commercial Use']['fuel'].notnull()
sop=list(sop[sop].index)
data1.loc[sop, 'fuel'].value_counts()
### Gathering all notnull value of  the different catogery of model name i.e 'modelDesc' depending on its fuel type
### Gathering of fuel type value from 'vehicleClass' of 'Trailer For Commercial Use'

TC = data1[data1['vehicleClass'] == 'Trailer For Commercial Use']['fuel'].notnull()
TC = list(TC[TC].index)

dieselName = data1.loc[TC, 'fuel'] == 'DIESEL'
dieselName = list(dieselName[dieselName].index)
dieselName = list(data1.iloc[dieselName,1].unique())

oneName = data1.loc[TC, 'fuel'] == '-1'
oneName = list(oneName[oneName].index)
oneName = list(data1.loc[oneName, 'modelDesc'].unique())
TC = data1[data1['vehicleClass'] == 'Trailer For Commercial Use']['fuel'].isnull()
TC = list(TC[TC].index)
data1.loc[TC, 'modelDesc'].isin(dieselName).value_counts()
TC = data1[data1['vehicleClass'] == 'Trailer For Commercial Use']['fuel'].isnull()
TC = list(TC[TC].index)
dcc=0
occ=0
for i in TC:
    
    if data1.loc[i, 'modelDesc'] in dieselName:
        dcc=dcc+1
        
    elif data1.loc[i, 'modelDesc'] in oneName:
        occ=occ+1
        
print("diesel name of notnull fuel value match with nan fuel value's model name",dcc)

print("-1 name of notnull fuel value match with nan fuel value's model name",occ)
### filling missing value based on same model name 
### also noticed that some name are common among diesel and non fuel varient name
### so as per data without fuel varient is large in number and diesel varient is less in number
### so we try to fill diesel value first

dieN = data1.loc[TC,'modelDesc'].isin(dieselName)

oneN = data1.loc[TC,'modelDesc'].isin(oneName)

data1.loc[dieN[dieN].index, 'fuel'] = 'DIESEL'

data1.loc[oneN[oneN].index, 'fuel'] = '-1'
data1[data1['fuel'].isnull()]['vehicleClass'].value_counts()
### fuel type in "Trailer For Commercial Use"

dddd=data1[data1['fuel'].notnull()]['vehicleClass']=='Trailer For Commercial Use'
data1.loc[dddd[dddd].index,'fuel'].value_counts().plot.bar()
### filling rest missing value with most occured value

catt=data1[data1['fuel'].isnull()]['vehicleClass'] == 'Trailer For Commercial Use'
data1.loc[catt[catt].index, 'fuel'] = '-1'
data1[data1['fuel'].isnull()]['vehicleClass'].value_counts()
### Handling missing 'fuel' value of 'vehicleClass' sub category "Trailer for Agriculture Purpose"

dddd=data1[data1['fuel'].notnull()]['vehicleClass'] == 'Trailer for Agriculture Purpose'
data1.loc[dddd[dddd].index,'fuel'].value_counts().plot.bar()
### Gathering all notnull value of  the different catogery of model name i.e 'modelDesc' depending on its fuel type
### Gathering of fuel type value from 'vehicleClass' of 'Trailer for Agriculture Purpose'

TC = data1[data1['vehicleClass'] == 'Trailer for Agriculture Purpose']['fuel'].notnull()
TC = list(TC[TC].index)

dieselName = data1.loc[TC, 'fuel'] == 'DIESEL'
dieselName = list(dieselName[dieselName].index)
dieselName = list(data1.loc[dieselName, 'modelDesc'].unique())

petrolName = data1.loc[TC, 'fuel'] == 'PETROL'
petrolName = list(petrolName[petrolName].index)
petrolName = list(data1.loc[petrolName,'modelDesc'].unique())

oneName = data1.loc[TC, 'fuel'] == '-1'
oneName = list(oneName[oneName].index)

oneName = list(data1.loc[oneName,'modelDesc'].unique())
TC = data1[data1['vehicleClass'] == 'Trailer for Agriculture Purpose']['fuel'].isnull()
TC = list(TC[TC].index)
data1.loc[TC, 'modelDesc'].isin(oneName).value_counts()
### notnull fuel type model name match with nan fuel value model name

TC = data1[data1['vehicleClass'] == 'Trailer for Agriculture Purpose']['fuel'].isnull()
TC = list(TC[TC].index)
dcc=0
occ=0
pcc=0
for i in TC:
    
    if data1.loc[i, 'modelDesc'] in dieselName:
        dcc=dcc+1
        
    elif data1.loc[i, 'modelDesc'] in oneName:
        occ=occ+1
        
    elif data1.loc[i, 'modelDesc'] in petrolName:
        pcc=pcc+1
        
print("Diesel name of notnull fuel value match with nan fuel value's model name = ",dcc)
print("")
print("Petrol name of notnull fuel value match with nan fuel value's model name = ",pcc)
print("")
print("-1 name of notnull fuel value match with nan fuel value's model name = ",occ)
dieN = data1.loc[TC, 'modelDesc'].isin(dieselName)

oneN = data1.loc[TC, 'modelDesc'].isin(oneName)

data1.loc[dieN[dieN].index, 'fuel'] = 'DIESEL'

data1.loc[oneN[oneN].index, 'fuel'] = '-1'
data1[data1['fuel'].isnull()]['vehicleClass'].value_counts()
### fuel type in "Trailer for Agriculture Purpose"

dddd=data1[data1['fuel'].notnull()]['vehicleClass']=='Trailer for Agriculture Purpose'
data1.loc[dddd[dddd].index,'fuel'].value_counts().plot.bar()
### filling rest missing value with most occured value

catt=data1[data1['fuel'].isnull()]['vehicleClass'] == 'Trailer for Agriculture Purpose'
data1.loc[catt[catt].index, 'fuel'] = '-1'
#### handling missing values of 'MOTOR CYCLE' of vehicleClass
### fuel type in "MOTOR CYCLE"

dddd=data1[data1['fuel'].notnull()]['vehicleClass']=='MOTOR CYCLE'
data1.loc[dddd[dddd].index,'fuel'].value_counts()
### filling rest missing value with most occured value of 'MOTOR CYCLE'

catt=data1[data1['fuel'].isnull()]['vehicleClass'] == 'MOTOR CYCLE'
data1.loc[catt[catt].index, 'fuel'] = 'PETROL'
#### handling missing values of 'MOTOR CAR' of vehicleClass
### fuel type in "MOTOR CAR"

dddd=data1[data1['fuel'].notnull()]['vehicleClass']=='MOTOR CAR'
data1.loc[dddd[dddd].index,'fuel'].value_counts().plot.bar()
### Motor car missing value filled by random value i.e (Petrol,Diesel) because occurrence of both is maximum
car=data1[data1['vehicleClass'] == 'MOTOR CAR']['fuel'].isnull()

count_car = len(car[car])

np.random.seed(0)

car_varient = np.random.choice(['DIESEL','PETROL'], size=count_car)

data1.loc[car[car].index, 'fuel'] = car_varient
data1[data1['fuel'].isnull()]['vehicleClass'].value_counts().sort_values( ascending=False)
### maximum missing value of top 3 vehicleClass fuel varient 
val=data1[data1['fuel'].isnull()]['vehicleClass'].unique()[:3]
fig, axes = plt.subplots(nrows=1,ncols=3, figsize=(15,4),constrained_layout=True)

data1.loc[data1['vehicleClass']=='Tractor for Agricultural Purpose','fuel'].value_counts().plot(ax=axes[0], kind='bar', title='Tractor for Agricultural Purpose')
data1.loc[data1['vehicleClass']=='Luxory Tourist Cab','fuel'].value_counts().plot(ax=axes[1], kind='bar', title='Luxory Tourist Cab')
data1.loc[data1['vehicleClass']=='Motor Cab','fuel'].value_counts().plot(ax=axes[2], kind='bar', title='Motor Cab')

data1.loc[data1['fuel'].isnull(),'fuel']='DIESEL'<jupyter_output><empty_output><jupyter_text>Handling OfficeCd missing value<jupyter_code>d=data1.isnull().sum().sort_values(ascending=False)
d[d.values>0]
### vehicle first 4 digit number 

data1["rto"]=data1['tempRegistrationNumber'].astype(str).str[0:4]

### vehicle state 

data1['state'] = data1['rto'].astype(str).str[:2]
data1.apply(lambda x : pd.factorize(x)[0]).corr(method='pearson', min_periods=1)
### for OfficeCd missing values 
### 1.) group the rto and category 
### 2.) take the top 3 most frequent OfficeCd of peticular group
### 3.) randomly fill that value in perticular group

c_test=data1.groupby(['rto', 'category']).groups
for cate_rto in c_test.keys():
    
    missing_office=data1.loc[c_test[cate_rto], 'OfficeCd'].isnull().sum()
    
    if missing_office:
            
            dat = data1.loc[c_test[cate_rto], 'OfficeCd'].value_counts().sort_values(ascending=False).keys()[:3]

            count_office = data1.loc[c_test[cate_rto],'OfficeCd'].isnull().sum()
            
            np.random.seed(0)
            choice_cate = np.random.choice(dat, size=count_office)
            
            d=data1.loc[c_test[cate_rto], 'OfficeCd'].isnull()
            
            data1.loc[d[d].index, 'OfficeCd'] = choice_cate
d=data1.isnull().sum()
d[d>0]
### Replacing Colour NIL value with most frequent colour of perticular vehicle class

d=data1['colour']=='NIL'
vehi_class = data1.loc[d[d].index, 'vehicleClass'].unique()

for clas in vehi_class:
    val = data1[data1['vehicleClass'] == clas]['colour'].value_counts().sort_values(ascending=False).keys()

    if val[0].upper() == 'NIL':
        val = val[1]
    
    else:
        val=val[0]
        
        
    d = data1[(data1['vehicleClass'] == clas)&(data1['colour']=='NIL')]
    
    data1.loc[d.index, 'colour'] = val

### handling colour missing value 
### filling nan value with most frequent colour of perticular vehicle class

d=data1['colour'].isnull()
vehi_class = data1.loc[d[d].index, 'vehicleClass'].unique()
data1['colour'] = data1['colour'].str.upper()

for clas in vehi_class:
    val = data1[data1['vehicleClass'] == clas]['colour'].value_counts().sort_values(ascending=False).keys()

    if val[0].upper() == 'NIL':

        print(val)
    else:
        val=val[0]

        
    d = data1[data1['vehicleClass'] == clas]['colour'].isnull()
    
    data1.loc[d[d].index, 'colour'] = val
    
    
data1.isnull().sum()<jupyter_output><empty_output><jupyter_text>Features play<jupyter_code>### manufacturing month and manufacturing year

d=data1['makeYear'].notnull()

data1['manufacturing_year']=data1.loc[d[d].index,'makeYear'].apply(lambda x: str('20') + x.split('/')[2] if len(x.split('/')[2])==2 else x.split('/')[2])
data1['manufacturing_year'] = data1['manufacturing_year'].astype(int)

dict_month={'02':'February', '01':'January', '04':'April ', '03':'March', '05':'May', '11':'November', 
            '07':'July', '12':'December', '09':'September', '10':'October', '08':'August',
       '06':'Jun'}
data1['manufacturing_month']=data1.loc[d[d].index,'makeYear'].apply(lambda x: x.split('/')[1]).map(dict_month)
c=data1['manufacturing_year'].astype(int)
c[c>2020].index
data1.loc[3577031,'manufacturing_year']=1985
data1['howOld']=2020-data1['manufacturing_year'].astype(int)

data1.columns
### maximum missing value of top 3 vehicleClass fuel varient 
#fig, axes = plt.subplots(nrows=2,ncols=2, figsize=(15,6),constrained_layout=True)
fig = plt.figure(figsize=(15,6))
ax1 = fig.add_subplot(221)
ax2 = fig.add_subplot(222)
ax3 = fig.add_subplot(212)

data1.boxplot(column = 'manufacturing_year', ax=ax1, vert=False)
data1.boxplot(column='howOld', ax=ax2, vert=False)
data1.boxplot(column='seatCapacity', ax=ax3, vert=False)
import matplotlib

fig, ax1 = plt.subplots(figsize=(18, 15))
c=[c for c in data1.groupby(['howOld']).groups.keys()]
cy = [len(i) for i in list(data1.groupby(['howOld']).groups.values())]
ax1.plot(c,cy, '*g--')
ax1.get_yaxis().set_major_formatter(
    matplotlib.ticker.FuncFormatter(lambda x, p: format(int(x), ',')))
ax1.grid()
plt.xlabel("How old vehicle", axes=ax1, size=15)
plt.ylabel("Total registerd vehicle", axes=ax1, size=15)
plt.title("Total vehicle distribution as per its age", axes=ax1, size=20)
plt.show()

fig, ax1 = plt.subplots(figsize=(18, 15))
c=[c for c in data1.groupby(['seatCapacity']).groups.keys()]
cy = [len(i) for i in list(data1.groupby(['seatCapacity']).groups.values())]

ax1.get_yaxis().set_major_formatter(
    matplotlib.ticker.FuncFormatter(lambda x, p: format(int(x), ',')))

ax1.plot(c,cy, '*r--')

#plt.xticks(keys, rotation='vertical', size=15, axes=ax1)
ax1.grid()

plt.xlabel("Vehicle count", axes=ax1, size=15)
plt.ylabel("Vehicle Seat capacity", axes=ax1, size=15)
plt.title("Total vehicle distribution as per its Seat Capacity", axes=ax1, size=20)
plt.show()
### year sold vehicle class

keys = [pair for pair in data1.groupby(['manufacturing_year']).groups]

fig, ax = plt.subplots(figsize=(25, 20))

ax.get_yaxis().set_major_formatter(
    matplotlib.ticker.FuncFormatter(lambda x, p: format(int(x), ',')))

c=[c for c in data1.groupby(['manufacturing_year']).groups.keys()]
cy = [len(i) for i in list(data1.groupby(['manufacturing_year']).groups.values())]
ax.plot(c,cy, '*r--')

plt.xticks(keys, rotation='vertical', size=15, axes=ax)
plt.yticks(np.arange(0, max(cy), 100000), axes=ax, size=20)
ax.grid(color='lightgreen')

plt.xlabel("Vehicle count", axes=ax1, size=20)
plt.ylabel("Vehicle Manufacturing Year", axes=ax1, size=20)
plt.title("Total vehicle distribution as per its Manufacturing Year", size=25)

plt.show()


data1['vehicleClass'].unique()
from random import randrange


c=[i for i in data1.groupby(['vehicleClass']).groups.keys()]
cy=[len(i) for i in data1.groupby('vehicleClass').groups.values()]
fig, ax =plt.subplots(figsize=(25, 20))

c_data = [randrange(50,250) for i in range(len(c))]
my_cmap = matplotlib.cm.get_cmap('jet')

plt.bar(c, cy, axes=ax, color=my_cmap(c_data))

plt.xticks(c, rotation='vertical', axes=ax, size=20)
plt.yticks(np.arange(0, max(cy), 100000), axes=ax, size=20)
ax.get_yaxis().set_major_formatter(
    matplotlib.ticker.FuncFormatter(lambda x, p: format(int(x), ',')))

plt.xlabel("Vehicle count", axes=ax, size=30)
plt.ylabel("Vehicle class", axes=ax, size=30)
plt.title("Total vehicle distribution as per Vehicle class", axes=ax, size=30)
plt.grid(color='lightblue')

plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18264632202231837, 'fraction_numerical': 0.02679723677218553, 'mean_word_length': 5.76978417266187, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 16, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '13480', 'license': 'MIT', 'n_tokens_mistral': 5058, 'n_tokens_neox': 4688, 'n_words': 987, 'path': 'EDA - Transport Vehicle Online Sales.ipynb', 'repo_name': 'tripathivenkteshwar/Vehicle-Sales-Data-Analysis', 'size': '13173'}