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

Project 2 - EDA on Vehicle Insurance Customer Data

SShaik Maliha6 Sept 2022

import pandas as pd
customer_detail_df=pd.read_csv("D:\\customer_details.txt")
# ' .\ ' represent the current directory
customer_detail_df
customer_detail_df.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"]
customer_detail_df

customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage
0 100012 M 45 yes AP39 yes 5 Headlight
1 100025 F 25 yes AP04 yes 2 brakes
2 100041 F 35 NaN AP03 No 8 NaN
3 100045 M 20 yes AP02 No 2 NaN

customer_policy_detail_df=pd.read_csv("D:\\customer_policy_details.txt")
customer_policy_detail_df
customer_policy_detail_df.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
customer_policy_detail_df

customer_id annual premium (in Rs) sales channel code vintage response
0 100012 25000 201 1 yes
1 100025 20000 202 0 no
2 100035 15000 203 1 no
3 100042 30000 204 0 yes

# EDA on vehicle insurance

customer_detail_df.shape
print("Null value in 'customer_id' : ",customer_detail_df["customer_id"].isnull().sum())
customer_detail_df=customer_detail_df.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",customer_detail_df["customer_id"].isnull().sum())

print("Null value in 'Gender' : ",customer_detail_df["Gender"].isnull().sum())
customer_detail_df["Gender"]=customer_detail_df["Gender"].fillna(customer_detail_df["Gender"].mode()[0])
print("Null value in 'Gender' after replacing : ",customer_detail_df["Gender"].isnull().sum())

print("Null value in 'age' : ",customer_detail_df["age"].isnull().sum())
customer_detail_df["age"]=customer_detail_df["age"].fillna(customer_detail_df["age"].mean())
print("Null value in 'age' after replacing : ",customer_detail_df["age"].isnull().sum())

print("Null value in 'driving licence present' : ",customer_detail_df["driving licence present"].isnull().sum())
customer_detail_df["driving licence present"]=customer_detail_df["driving licence present"].fillna(customer_detail_df["driving licence present"].mode()[0])
print("Null value in 'driving licence present' after replacing : ",customer_detail_df["driving licence present"].isnull().sum())

print("Null value in 'region code' : ",customer_detail_df["region code"].isnull().sum())
customer_detail_df["region code"]=customer_detail_df["region code"].fillna(customer_detail_df["region code"].mode()[0])
print("Null value in 'region code' after replacing : ",customer_detail_df["region code"].isnull().sum())

print("Null value in 'previously insured' : ",customer_detail_df["previously insured"].isnull().sum())
customer_detail_df["previously insured"]=customer_detail_df["previously insured"].fillna(customer_detail_df["previously insured"].mode()[0])
print("Null value in 'previously insured' after replacing : ",customer_detail_df["previously insured"].isnull().sum())

print("Null value in 'vehicle age' : ",customer_detail_df["vehicle age"].isnull().sum())
customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].fillna(customer_detail_df["vehicle age"].mode()[0])
print("Null value in 'vehicle age' after replacing : ",customer_detail_df["vehicle age"].isnull().sum())

print("Null value in 'vehicle damage' : ",customer_detail_df["vehicle damage"].isnull().sum())
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].fillna(customer_detail_df["vehicle damage"].mode()[0])
print("Null value in 'vehicle damage' after replacing : ",customer_detail_df["vehicle damage"].isnull().sum())
## Null values for customer_policy_detail

print("Null value in 'customer_id' : ",customer_policy_detail_df["customer_id"].isnull().sum())
customer_policy_detail_df=customer_policy_detail_df.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",customer_policy_detail_df["customer_id"].isnull().sum())

print("Null value in 'annual premium (in Rs)' : ",customer_policy_detail_df["annual premium (in Rs)"].isnull().sum())
customer_policy_detail_df["annual premium (in Rs)"]=customer_policy_detail_df["annual premium (in Rs)"].fillna(customer_policy_detail_df["annual premium (in Rs)"].mean())
print("Null value in 'annual premium (in Rs)' after replacing : ",customer_policy_detail_df["annual premium (in Rs)"].isnull().sum())

print("Null value in 'sales channel code' : ",customer_policy_detail_df["sales channel code"].isnull().sum())
customer_policy_detail_df["sales channel code"]=customer_policy_detail_df["sales channel code"].fillna(customer_policy_detail_df["sales channel code"].mode()[0])
print("Null value in 'sales channel code' after replacing : ",customer_policy_detail_df["sales channel code"].isnull().sum())

print("Null value in 'vintage' : ",customer_policy_detail_df["vintage"].isnull().sum())
customer_policy_detail_df["vintage"]=customer_policy_detail_df["vintage"].fillna(customer_policy_detail_df["vintage"].mean())
print("Null value in 'vintage' after replacing : ",customer_policy_detail_df["vintage"].isnull().sum())

print("Null value in 'response' : ",customer_policy_detail_df["response"].isnull().sum())
customer_policy_detail_df["response"]=customer_policy_detail_df["response"].fillna(customer_policy_detail_df["response"].mode()[0])
print("Null value in 'response' after replacing : ",customer_policy_detail_df["response"].isnull().sum())

Null value in 'customer_id' :  0
Null value in 'customer_id' after droping :  0
Null value in 'Gender' :  0
Null value in 'Gender' after replacing :  0
Null value in 'age' :  0
Null value in 'age' after replacing :  0
Null value in 'driving licence present' :  0
Null value in 'driving licence present' after replacing :  0
Null value in 'region code' :  0
Null value in 'region code' after replacing :  0
Null value in 'previously insured' :  0
Null value in 'previously insured' after replacing :  0
Null value in 'vehicle age' :  0
Null value in 'vehicle age' after replacing :  0
Null value in 'vehicle damage' :  0
Null value in 'vehicle damage' after replacing :  0
Null value in 'customer_id' :  0
Null value in 'customer_id' after droping :  0
Null value in 'annual premium (in Rs)' :  0
Null value in 'annual premium (in Rs)' after replacing :  0
Null value in 'sales channel code' :  0
Null value in 'sales channel code' after replacing :  0
Null value in 'vintage' :  0
Null value in 'vintage' after replacing :  0
Null value in 'response' :  0
Null value in 'response' after replacing :  0

## Outliers

customer_detail_df.describe()
customer_policy_detail_df.describe()
Q1=customer_detail_df.describe().loc["25%","age"]
Q3=customer_detail_df.describe().loc["75%","age"]
IQR=Q3-Q1
print(customer_detail_df.loc[customer_detail_df["age"]<(Q1-IQR*1.5),"age"].count())
print(customer_detail_df.loc[customer_detail_df["age"]>(Q3+IQR*1.5),"age"].count())

##### there are no outliers in 'age' column

Q1=customer_policy_detail_df.describe().loc["25%","annual premium (in Rs)"]
Q3=customer_policy_detail_df.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=customer_policy_detail_df["annual premium (in Rs)"].mean()
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Q1=customer_policy_detail_df.describe().loc["25%","vintage"]
Q3=customer_policy_detail_df.describe().loc["75%","vintage"]
IQR=Q3-Q1
print(customer_policy_detail_df.loc[customer_policy_detail_df["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print(customer_policy_detail_df.loc[customer_policy_detail_df["vintage"]>(Q3+IQR*1.5),"vintage"].count())
##### there are no outliers in 'vintage' column

0
0
0
0
0
0
0

##### there are no outliers in 'vintage' column

### White spaces

customer_detail_df["Gender"]=customer_detail_df["Gender"].str.strip()
#customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.strip()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.strip()

### case change

customer_detail_df["Gender"]=customer_detail_df["Gender"].str.lower()
#customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.lower()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.lower()

### creating dummies

dummies_gender=pd.get_dummies(customer_detail_df["Gender"])
dummies_vehicle_age=pd.get_dummies(customer_detail_df["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(customer_detail_df["vehicle damage"])
dummies_d_l_p=pd.get_dummies(customer_detail_df["driving licence present"])
dummies_p_i=pd.get_dummies(customer_detail_df["previously insured"])
dummies_response=pd.get_dummies(customer_policy_detail_df["response"])

### checking for any duplicates

print(customer_detail_df.duplicated().sum())
print(customer_policy_detail_df.duplicated().sum())

0
0

### 3. Create a Master table for future use. Join the customer table and customer_policy table to get a master table using customer_id in both tables.


master_df=pd.merge(customer_detail_df,customer_policy_detail_df,on='customer_id')
master_df=pd.merge(customer_detail_df,customer_policy_detail_df,on='customer_id')
master_df

customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage annual premium (in Rs) sales channel code vintage response
0 100012 m 45 yes AP39 yes 5 headlight 25000 201 1 yes
1 100025 f 25 yes AP04 yes 2 brakes 20000 202 0 no

### 4. Company needs some important information from the master table to make decisions for future growth.They needs following information:

##### i. Gender wise average annual premium

##### ii. Age wise average annual premium

##### iii. Is your data balanced between the genders?
##### iv. Vehicle age wise average annual premium.

#Gender wise average annual premium
aap_gender_df=master_df.groupby(["Gender"])["annual premium (in Rs)"].mean()
aap_gender_df

In [1]:
import pandas as pd
customer_detail_df=pd.read_csv("D:\\customer_details.txt")
 # ' .\ ' represent the current directory
customer_detail_df
customer_detail_df.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"]
customer_detail_df
Out[1]:
customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage
0 100012 M 45 yes AP39 yes 5 Headlight
1 100025 F 25 yes AP04 yes 2 brakes
2 100041 F 35 NaN AP03 No 8 NaN
3 100045 M 20 yes AP02 No 2 NaN
In [2]:
customer_policy_detail_df=pd.read_csv("D:\\customer_policy_details.txt")
customer_policy_detail_df
customer_policy_detail_df.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
customer_policy_detail_df
Out[2]:
customer_id annual premium (in Rs) sales channel code vintage response
0 100012 25000 201 1 yes
1 100025 20000 202 0 no
2 100035 15000 203 1 no
3 100042 30000 204 0 yes
In [3]:
customer_detail_df.shape
print("Null value in 'customer_id' : ",customer_detail_df["customer_id"].isnull().sum())
customer_detail_df=customer_detail_df.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",customer_detail_df["customer_id"].isnull().sum())
​
print("Null value in 'Gender' : ",customer_detail_df["Gender"].isnull().sum())
customer_detail_df["Gender"]=customer_detail_df["Gender"].fillna(customer_detail_df["Gender"].mode()[0])
print("Null value in 'Gender' after replacing : ",customer_detail_df["Gender"].isnull().sum())
​
print("Null value in 'age' : ",customer_detail_df["age"].isnull().sum())
customer_detail_df["age"]=customer_detail_df["age"].fillna(customer_detail_df["age"].mean())
print("Null value in 'age' after replacing : ",customer_detail_df["age"].isnull().sum())
​
print("Null value in 'driving licence present' : ",customer_detail_df["driving licence present"].isnull().sum())
customer_detail_df["driving licence present"]=customer_detail_df["driving licence present"].fillna(customer_detail_df["driving licence present"].mode()[0])
print("Null value in 'driving licence present' after replacing : ",customer_detail_df["driving licence present"].isnull().sum())
​
print("Null value in 'region code' : ",customer_detail_df["region code"].isnull().sum())
customer_detail_df["region code"]=customer_detail_df["region code"].fillna(customer_detail_df["region code"].mode()[0])
print("Null value in 'region code' after replacing : ",customer_detail_df["region code"].isnull().sum())
​
print("Null value in 'previously insured' : ",customer_detail_df["previously insured"].isnull().sum())
customer_detail_df["previously insured"]=customer_detail_df["previously insured"].fillna(customer_detail_df["previously insured"].mode()[0])
print("Null value in 'previously insured' after replacing : ",customer_detail_df["previously insured"].isnull().sum())
​
print("Null value in 'vehicle age' : ",customer_detail_df["vehicle age"].isnull().sum())
customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].fillna(customer_detail_df["vehicle age"].mode()[0])
print("Null value in 'vehicle age' after replacing : ",customer_detail_df["vehicle age"].isnull().sum())
​
print("Null value in 'vehicle damage' : ",customer_detail_df["vehicle damage"].isnull().sum())
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].fillna(customer_detail_df["vehicle damage"].mode()[0])
print("Null value in 'vehicle damage' after replacing : ",customer_detail_df["vehicle damage"].isnull().sum())
## Null values for customer_policy_detail
​
print("Null value in 'customer_id' : ",customer_policy_detail_df["customer_id"].isnull().sum())
customer_policy_detail_df=customer_policy_detail_df.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",customer_policy_detail_df["customer_id"].isnull().sum())
​
print("Null value in 'annual premium (in Rs)' : ",customer_policy_detail_df["annual premium (in Rs)"].isnull().sum())
customer_policy_detail_df["annual premium (in Rs)"]=customer_policy_detail_df["annual premium (in Rs)"].fillna(customer_policy_detail_df["annual premium (in Rs)"].mean())
print("Null value in 'annual premium (in Rs)' after replacing : ",customer_policy_detail_df["annual premium (in Rs)"].isnull().sum())
​
print("Null value in 'sales channel code' : ",customer_policy_detail_df["sales channel code"].isnull().sum())
customer_policy_detail_df["sales channel code"]=customer_policy_detail_df["sales channel code"].fillna(customer_policy_detail_df["sales channel code"].mode()[0])
print("Null value in 'sales channel code' after replacing : ",customer_policy_detail_df["sales channel code"].isnull().sum())
​
print("Null value in 'vintage' : ",customer_policy_detail_df["vintage"].isnull().sum())
customer_policy_detail_df["vintage"]=customer_policy_detail_df["vintage"].fillna(customer_policy_detail_df["vintage"].mean())
print("Null value in 'vintage' after replacing : ",customer_policy_detail_df["vintage"].isnull().sum())
​
print("Null value in 'response' : ",customer_policy_detail_df["response"].isnull().sum())
customer_policy_detail_df["response"]=customer_policy_detail_df["response"].fillna(customer_policy_detail_df["response"].mode()[0])
print("Null value in 'response' after replacing : ",customer_policy_detail_df["response"].isnull().sum())
Null value in 'customer_id' :  0
Null value in 'customer_id' after droping :  0
Null value in 'Gender' :  0
Null value in 'Gender' after replacing :  0
Null value in 'age' :  0
Null value in 'age' after replacing :  0
Null value in 'driving licence present' :  1
Null value in 'driving licence present' after replacing :  0
Null value in 'region code' :  0
Null value in 'region code' after replacing :  0
Null value in 'previously insured' :  0
Null value in 'previously insured' after replacing :  0
Null value in 'vehicle age' :  0
Null value in 'vehicle age' after replacing :  0
Null value in 'vehicle damage' :  2
Null value in 'vehicle damage' after replacing :  0
Null value in 'customer_id' :  0
Null value in 'customer_id' after droping :  0
Null value in 'annual premium (in Rs)' :  0
Null value in 'annual premium (in Rs)' after replacing :  0
Null value in 'sales channel code' :  0
Null value in 'sales channel code' after replacing :  0
Null value in 'vintage' :  0
Null value in 'vintage' after replacing :  0
Null value in 'response' :  0
Null value in 'response' after replacing :  0
In [4]:
## Outliers
​
customer_detail_df.describe()
customer_policy_detail_df.describe()
Q1=customer_detail_df.describe().loc["25%","age"]
Q3=customer_detail_df.describe().loc["75%","age"]
IQR=Q3-Q1
print(customer_detail_df.loc[customer_detail_df["age"]<(Q1-IQR*1.5),"age"].count())
print(customer_detail_df.loc[customer_detail_df["age"]>(Q3+IQR*1.5),"age"].count())
​
##### there are no outliers in 'age' column
​
Q1=customer_policy_detail_df.describe().loc["25%","annual premium (in Rs)"]
Q3=customer_policy_detail_df.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=customer_policy_detail_df["annual premium (in Rs)"].mean()
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Q1=customer_policy_detail_df.describe().loc["25%","vintage"]
Q3=customer_policy_detail_df.describe().loc["75%","vintage"]
IQR=Q3-Q1
print(customer_policy_detail_df.loc[customer_policy_detail_df["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print(customer_policy_detail_df.loc[customer_policy_detail_df["vintage"]>(Q3+IQR*1.5),"vintage"].count())
##### there are no outliers in 'vintage' column
      
0
0
0
0
0
0
0
In [5]:
##### there are no outliers in 'vintage' column
​
### White spaces
​
customer_detail_df["Gender"]=customer_detail_df["Gender"].str.strip()
#customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.strip()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.strip()
In [6]:
 
### case change
​
customer_detail_df["Gender"]=customer_detail_df["Gender"].str.lower()
#customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.lower()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.lower()
In [7]:
### creating dummies
​
dummies_gender=pd.get_dummies(customer_detail_df["Gender"])
dummies_vehicle_age=pd.get_dummies(customer_detail_df["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(customer_detail_df["vehicle damage"])
dummies_d_l_p=pd.get_dummies(customer_detail_df["driving licence present"])
dummies_p_i=pd.get_dummies(customer_detail_df["previously insured"])
dummies_response=pd.get_dummies(customer_policy_detail_df["response"])
In [8]:
### checking for any duplicates
​
print(customer_detail_df.duplicated().sum())
print(customer_policy_detail_df.duplicated().sum())
0
0
In [9]:
### 3. Create a Master table for future use. Join the customer table and customer_policy table to get a master table using customer_id in both tables.
​
​
master_df=pd.merge(customer_detail_df,customer_policy_detail_df,on='customer_id')
master_df
Out[9]:
customer_id Gender age driving licence present region code previously insured vehicle age vehicle damage annual premium (in Rs) sales channel code vintage response
0 100012 m 45 yes AP39 yes 5 headlight 25000 201 1 yes
1 100025 f 25 yes AP04 yes 2 brakes 20000 202 0 no
In [10]:
### 4. Company needs some important information from the master table to make decisions for future growth.They needs following information:
​
##### i. Gender wise average annual premium
​
##### ii. Age wise average annual premium
​
##### iii. Is your data balanced between the genders?
##### iv. Vehicle age wise average annual premium.
​
#Gender wise average annual premium 
aap_gender_df=master_df.groupby(["Gender"])["annual premium (in Rs)"].mean()
aap_gender_df
Gender
f    20000.0
m    25000.0
Name: annual premium (in Rs), dtype: float64

## Data is balanced between the two genders
#### data is balanced between the two genders because the avereages are approximately equal

# age wise average annual premium
aap_age_df=master_df.groupby(["age"])["annual premium (in Rs)"].mean()
aap_age_df
#vehicle age wise average annual premium
aap_v_age_df=master_df.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
aap_v_age_df

vehicle age
2    20000.0
5    25000.0
Name: annual premium (in Rs), dtype: float64

#### 5. Is there any relation between Person Age and annual premium?

#Correlation coefficient < -0.5 - Strong negative relationship

#Correlation coefficient > 0.5 - Strong positive relationship

#0.5 < Correlation coefficient < 0.5 - There is no relationship.

Correlation_coefficient=master_df["age"].corr(master_df["annual premium (in Rs)"])
print(Correlation_coefficient)
# there is no relationship between Person age and annual premium because Corrrelation coefficient<0.5

1.0
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.