Project 2 - EDA on Vehicle Insurance Customer Data

1.ANS

# to avoid warnings from python
import warnings
warnings.filterwarnings('ignore')

import pandas as pd
customer_detail_df=pd.read_csv(r'C:\Users\Chinna\Downloads\customer_details.csv')
# ' .\ ' 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

381109 rows × 8 columns

customer_policy_detail_df=pd.read_csv(r"C:\Users\Chinna\Downloads\customer_policy_details.csv")
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_detail_df.shape
## Null values for customer_detail

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())

381109 rows × 5 columns

Null value in 'customer_id' :  386
Null value in 'customer_id' after droping :  0
Null value in 'Gender' :  368
Null value in 'Gender' after replacing :  0
Null value in 'age' :  366
Null value in 'age' after replacing :  0
Null value in 'driving licence present' :  392
Null value in 'driving licence present' after replacing :  0
Null value in 'region code' :  391
Null value in 'region code' after replacing :  0
Null value in 'previously insured' :  381
Null value in 'previously insured' after replacing :  0
Null value in 'vehicle age' :  381
Null value in 'vehicle age' after replacing :  0
Null value in 'vehicle damage' :  406
Null value in 'vehicle damage' after replacing :  0

## 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' :  387
Null value in 'customer_id' after droping :  0
Null value in 'annual premium (in Rs)' :  344
Null value in 'annual premium (in Rs)' after replacing :  0
Null value in 'sales channel code' :  400
Null value in 'sales channel code' after replacing :  0
Null value in 'vintage' :  388
Null value in 'vintage' after replacing :  0
Null value in 'response' :  361
Null value in 'response' after replacing :  0

## 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 '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())

0
0

##### 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())

0
10332
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())
0
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())
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())

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

#(Hint: use pd.merge() function)

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

0
0

#### 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?
#(Hint: Data is balanced if number of counts in each group is approximately same)

##### 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
female    29273.474247
male      29323.099677
Name: annual premium (in Rs), dtype: float64

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

Gender
female    29273.474247
male      29323.099677
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
1-2 year     29102.009839
< 1 year     29188.150594
> 2 years    32943.540830
Name: annual premium (in Rs), dtype: float64

#### 5. Is there any relation between Person Age and annual premium?
#Hint: use correlation function (Correlation describes the relationship between two variables).

#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 Correlation coefficient<0.5

0.0506575892861717

#There is no relationship between Person age and annual premium
#because Correlation coefficient < 0.5