BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2 - EDA on Vehicle Insurance Customer Data

YYeshwanth Kaipa29 Mar 2023

#Project 2 EDA on Vehicle Insurance Customer Data

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df1=pd.read_csv('D:\Pandas\customer_details.csv')

df2=pd.read_csv('D:\Pandas\customer_policy_details.csv')

df1.columns=['ID','GENDER','AGE','DRIVING LICENCE PRESENT','REGION CODE','PREVIOUSLY INSURED','VEHICLE AGE','VEHICLE DAMAGE']

df2.columns=["ID","ANNUAL PREMIUM","SALE CHANNEL CODE","VINTAGE","RESPONSE"]

df1​

  ID GENDER AGE DRIVING LICENCE PRESENT REGION CODE PREVIOUSLY INSURED VEHICLE AGE VEHICLE DAMAGE
0 1.0 male 44.0 1.0 28.0 0.0 > 2 years yes
1 2.0 male 76.0 1.0 3.0 0.0 1-2 year no
2 3.0 male 47.0 1.0 28.0 0.0 > 2 years yes
3 4.0 male 21.0 1.0 11.0 1.0 < 1 year no
4 5.0 female 29.0 1.0 41.0 1.0 < 1 year no
... ... ... ... ... ... ... ... ...
380718 381105.0 male 74.0 1.0 26.0 1.0 1-2 year no
380719 381106.0 male 30.0 1.0 37.0 1.0 < 1 year no
380720 381107.0 male 21.0 1.0 30.0 1.0 < 1 year no
380721 381108.0 female 68.0 1.0 14.0 0.0 > 2 years yes
380722 381109.0 male 46.0 1.0 29.0 0.0

1-2 year

df2

ID ANNUAL PREMIUM SALE CHANNEL CODE VINTAGE RESPONSE
0 1.0 40454.0 26.0 217.0 1.0
1 2.0 33536.0 26.0 183.0 0.0
2 3.0 38294.0 26.0 27.0 1.0
3 4.0 28619.0 152.0 203.0 0.0
4 5.0 27496.0 152.0 39.0 0.0
... ... ... ... ... ...
380717 381105.0 30170.0 26.0 88.0 0.0
380718 381106.0 40016.0 152.0 131.0 0.0
380719 381107.0 35118.0 160.0 161.0 0.0
380720 381108.0 44617.0 124.0 74.0 0.0
380721 381109.0 41777.0 26.0 237.0 0.0

380722 rows × 5 columns

2. Checking and Cleaning Data Quality:

df1.isnull()

  ID GENDER AGE DRIVING LICENCE PRESENT REGION CODE PREVIOUSLY INSURED VEHICLE AGE VEHICLE DAMAGE
0 False False False False False False False False
1 False False False False False False False False
2 False False False False False False False False
3 False False False False False False False False
4 False False False False False False False False
... ... ... ... ... ... ... ... ...
380718 False False False False False False False False
380719 False False False False False False False False
380720 False False False False False False False False
380721 False False False False False False False False
380722 False False False False False False False False

380723 rows × 8 columns

df1_null=df1.isnull()

for i in df1_null.columns.values.tolist():
print(i)
print(df1_null[i].value_counts())
print("")

ID
False    380722
Name: ID, dtype: int64

ANNUAL PREMIUM
False    380722
Name: ANNUAL PREMIUM, dtype: int64

SALE CHANNEL CODE
False    380722
Name: SALE CHANNEL CODE, dtype: int64

VINTAGE
False    380722
Name: VINTAGE, dtype: int64

RESPONSE
False    380722
Name: RESPONSE, dtype: int64

df2.dropna(subset=['ID'],axis=0,inplace=True)
df2.reset_index(drop=True,inplace=True)

#replacing null values of df2 customer_policy_details 
df2['ANNUAL PREMIUM'].fillna(df2['ANNUAL PREMIUM'].mean(),inplace=True)
df2['SALE CHANNEL CODE'].fillna(df2['SALE CHANNEL CODE'].mean(),inplace=True)
df2['VINTAGE'].fillna(df2['VINTAGE'].mean(),inplace=True)

#replacing categorial values by mode
df2['RESPONSE'].fillna(df2['VINTAGE'].mode()[0],inplace=True)



#2 ii finding ouliers 
def finding_out(df):
 q1 = df.quantile(0.25)
 q3=df.quantile(0.75)
 iqr=q3-q1
 outlier = df[((df<(q1-1.5*iqr)))|(df>(q3+1.5*iqr))]
 return outlier


outliers=finding_out(df1['REGION CODE'])
print("Number of outliers are:",len(outliers))
print("Maximum of outliers is:,",outliers.max())
print("minimum of outliers is:",outliers.min())


Number of outliers are: 0
Maximum  of outliers is:, nan
minimum of outliers is: nan


#finding outliers customer policy details:
print("for Annual premium")
outliers=finding_out(df2['ANNUAL PREMIUM'])
print("number of outliers are:",len(outliers))
print("maximum of outliers is:",outliers.max())
print("minimum of outliers is:",outliers.min())

print('^'*100)
print("for sales channel code")
outliers=finding_out(df2['SALE CHANNEL CODE'])
print("number of outliers are:",len(outliers))
print("maximum of outliers is:",outliers.max())
print("minimum of outliers is:",outliers.min())

print('^'*100)

print("for vintage")
outliers=finding_out(df2['VINTAGE'])
print("number of outliers are:",len(outliers))
print("maximum of outliers is:",outliers.max())
print("minimum of outliers is:",outliers.min())

for Annual premium
number of outliers are: 67443
maximum  of outliers is: 61854.0
minimum of outliers is: 2630.0
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
for sales channel code
number of outliers are: 0
maximum  of outliers is: nan
minimum of outliers is: nan
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
for vintage
number of outliers are: 0
maximum  of outliers is: nan
minimum of outliers is: nan


#replacing outliers with mean value
def replace_outlier(df):
 q1=df.quantile(0.25)
 q3=df.quantile(0.75)
 iqr=q3-q1
 upper=df[~(df>(q3+1.5*iqr))].max()
 lower=df[~(df<(q1-1.5*iqr))].min()
 df=np.where(df>upper,df.mean(),np.where(df return df

df2['ANNUAL PREMIUM']=replace_outlier(df2['ANNUAL PREMIUM'])

df2.head()
ID ANNUAL PREMIUM SALE CHANNEL CODE VINTAGE RESPONSE
0 1.0 40454.0 26.0 217.0 1.0
1 2.0 33536.0 26.0 183.0 0.0
2 3.0 38294.0 26.0 27.0 1.0
3 4.0 28619.0 152.0 203.0 0.0
4 5.0 27496.0 152.0 39.0

0





# REMOVING WHITE SPACES
df1['GENDER']=df1['GENDER'].str.strip()
df1['VEHICLE AGE']=df1['VEHICLE AGE'].str.strip()
df1['VEHICLE DAMAGE']=df1['VEHICLE DAMAGE'].str.strip()

# case correction for customer details
df1['GENDER']=df1['GENDER'].str.lower()
df1['VEHICLE AGE']=df1['VEHICLE AGE'].str.lower()
df1['VEHICLE DAMAGE']=df1['VEHICLE DAMAGE'].str.lower()

df1

ID GENDER AGE DRIVING LICENCE PRESENT REGION CODE PREVIOUSLY INSURED VEHICLE AGE VEHICLE DAMAGE
0 1.0 male 44.0 1.0 28.0 0.0 > 2 years yes
1 2.0 male 76.0 1.0 3.0 0.0 1-2 year no
2 3.0 male 47.0 1.0 28.0 0.0 > 2 years yes
3 4.0 male 21.0 1.0 11.0 1.0 < 1 year no
4 5.0 female 29.0 1.0 41.0 1.0 < 1 year no
... ... ... ... ... ... ... ... ...
380718 381105.0 male 74.0 1.0 26.0 1.0 1-2 year no
380719 381106.0 male 30.0 1.0 37.0 1.0 < 1 year no
380720 381107.0 male 21.0 1.0 30.0 1.0 < 1 year no
380721 381108.0 female 68.0 1.0 14.0 0.0 > 2 years yes
380722 381109.0 male 46.0 1.0 29.0 0.0 1-2 year no

380723 rows × 8 columns

#2 v Convert nominal data (categorical) into dummies for future modeling use if required
d_gender =pd.get_dummies(df1['GENDER'])
d_vehicle_age =pd.get_dummies(df1['VEHICLE AGE'])
d_vehicle_damage=pd.get_dummies(df1['VEHICLE DAMAGE'])
d_driving_license_present=pd.get_dummies(df1['DRIVING LICENCE PRESENT'])
d_previously=pd.get_dummies(df1['PREVIOUSLY INSURED'])

#2.vi. drop duplicates from the table
print(df2.duplicated().sum())

df1.drop_duplicates(inplace=True)
df2.drop_duplicates(inplace=True)

#3. Merging operation
master=pd.merge(df1,df2,on='ID')

master

ID GENDER AGE DRIVING LICENCE PRESENT REGION CODE PREVIOUSLY INSURED VEHICLE AGE VEHICLE DAMAGE ANNUAL SALARY SALE CHANNEL CODE VINTAGE RESPONSE
0 1.0 male 44.0 1.0 28.0 0.0 > 2 years yes 40454.0 26.0 217.0 1.0
1 2.0 male 76.0 1.0 3.0 0.0 1-2 year no 33536.0 26.0 183.0 0.0
2 3.0 male 47.0 1.0 28.0 0.0 > 2 years yes 38294.0 26.0 27.0 1.0
3 4.0 male 21.0 1.0 11.0 1.0 < 1 year no 28619.0 152.0 203.0 0.0
4 5.0 female 29.0 1.0 41.0 1.0 < 1 year no 27496.0 152.0 39.0 0.0
... ... ... ... ... ... ... ... ... ... ... ... ...
380331 381105.0 male 74.0 1.0 26.0 1.0 1-2 year no 30170.0 26.0 88.0 0.0
380332 381106.0 male 30.0 1.0 37.0 1.0 < 1 year no 40016.0 152.0 131.0 0.0
380333 381107.0 male 21.0 1.0 30.0 1.0 < 1 year no 35118.0 160.0 161.0 0.0
380334 381108.0 female 68.0 1.0 14.0 0.0 > 2 years yes 44617.0 124.0 74.0 0.0
380335 381109.0 male 46.0 1.0 29.0 0.0 1-2 year no 41777.0 26.0 237.0 0.0

380336 rows × 12 columns

# 4. Future predction
#1. Gender wise annual premium
gwap=master.groupby(["GENDER"])["ANNUAL PREMIUM"].mean()

gwap

GENDER
female    33312.128996
male      33867.885556
Name: ANNUAL PREMIUM, dtype: float64

awap=master.groupby(["AGE"])["ANNUAL PREMIUM"].mean()
awap
AGE
20.0    31945.978364
21.0    32553.921038
22.0    32459.379290
23.0    32426.589359
24.0    32655.099756
            ...     
81.0    33374.685866
82.0    38022.446823
83.0    35057.066584
84.0    40289.816912
85.0    33910.952620

master.groupby('GENDER').count()
ID AGE DRIVING LICENCE PRESENT REGION CODE PREVIOUSLY INSURED VEHICLE AGE VEHICLE DAMAGE ANNUAL PREMIUM SALE CHANNEL CODE VINTAGE RESPONSE
GENDER                      
female 174485 174485 174485 174485 174485 174309 174300 174485 174485 174485 174485
male 205484 205484 205484 205484 205484 205279 205266 205484 205484 205484 205484 

# iii 
yes, the data balanced between the genders

#iv vehicle age wise annual premium
vawap=master.groupby(['VEHICLE AGE'])['ANNUAL PREMIUM'].mean()

vawap

VEHICLE AGE
1-2 year     34353.839947
< 1 year     32371.344994
> 2 years    37099.358004
Name: ANNUAL PREMIUM, dtype: float64

#5. Is there any relation between Person Age and annual premium?
c=master['AGE'].corr(master['ANNUAL PREMIUM'])

c
0.15903248121760252

master[['AGE','ANNUAL PREMIUM']].corr()
AGEANNUAL PREMIUMAGE1.0000000.159032ANNUAL PREMIUM0.1590321.000000
Perason coefficient is not satisfying there is no corelation between age and annual premium
























Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.