Project 2 - EDA on Vehicle Insurance Customer Data
EDA on Vehicle Insurance Customer Data
A company has customer data that contains 8 columns of customer details and another table having name customer_policy data contains the policy details of the customer.
The company intends to offer some discount in premium for certain customers. To do that they ask their Data scientist team to get some information. Hence, following tasks DS team decided to perform:
- Add the column names to both datasets:
i. Column Name for customer details table:
customer_id,
Gender,
age,
driving licence present,
region code,
previously insured,
vehicle age
and vehicle damage, in respective order. ii. Column Name for customer_policy table:
customer_id,
annual premium (in Rs),
sales channel code,
vintage and response. - Checking and Cleaning Data Quality:
i. Null values
Generate a summary of count of all the null values column wise Drop Null values for customer_id because central tendencies for id’s is not feasible. Replace all null values for numeric columns by mean. Replace all null values for Categorical value by mode. ii. Outliers
Generate a summary of count of all the outliers column wise Replace all outlier values for numeric columns by mean. (Hint1: for outlier treatment use IQR method as follows:
For example: for a column X calculate Q1 = 25th percentile and Q3 = 75th percentile then IQR = Q3 – Q1 ) then to check outlier, anything lower than a Q1 – 1.5IQR or greater than Q3 + 1.5 IQR would be an outlier
Hint2: For getting percentile value, explore pd.describe() function)
iii. White spaces
Remove white spaces iv. case correction(lower or upper, any one)
v. Convert nominal data (categorical) into dummies
for future modeling use if required vi. Drop Duplicates (duplicated rows)
- 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)
-
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.
- 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.
In [57]:
import pandas as pd
customer_detail_df=pd.read_csv(".\customer_details.csv")
customer_detail_df
# Column Name for customer details table:
customer_detail_df.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"]
customer_detail_df
customer_policy_detail_df=pd.read_csv(".\customer_policy_details.csv")
customer_policy_detail_df
# Column Name for customer_policy table:
customer_policy_detail_df.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
## Null values for customer_detail
# customer_id
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())
# gender
print("\nNull 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())
# age
print("\nNull 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())
# driving license present
print("\nNull 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())
# region code
print("\nNull 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())
# previously insured
print("\nNull 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())
# vehicle age
print("\nNull 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())
# vehicle damage
print("\nNull 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
# customer_id
print("\nNull 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())
# annual premium
print("\nNull 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())
# sales channel code
print("\nNull 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())
# vintage
print("\nNull 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())
# response
print("\nNull 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())
# summary of count of all the outliers
customer_detail_df.describe()
customer_policy_detail_df.describe()
print ('\nChecking outliers for all the mean values...')
# checking outliers for age
print ('Outliers in age are :')
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())
print ('There are no outliers in age column')
# checking outliers for annual premium (in Rs)
print ('\nOutliers in annual premium (in Rs) : ')
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())
print('Replacing the outlier value for numeric columns of annual premium by mean: ')
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())
print ('Now there are no outliers in annual premium (in Rs)')
# checking for outliers in vintage
print ('\nOutliers in vintage are :')
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())
print('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 correction
customer_detail_df["Gender"]=customer_detail_df["Gender"].str.upper()
customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.upper()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.upper()
### 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())
# merging using customer_id
master_df=pd.merge(customer_detail_df,customer_policy_detail_df,on='customer_id')
master_df
# Gender wise average annual premium
aap_gender_df=master_df.groupby(["Gender"])["annual premium (in Rs)"].mean()
aap_gender_df
# Data is balanced between two genders bcoz the averages 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
# relation between Person Age and annual premium?
print ('\nRelation between person age and annual premium is : ')
Correlation_coefficient=master_df["age"].corr(master_df["annual premium (in Rs)"])
print(Correlation_coefficient)
print ('There is no relationship between Person age and annual premium because Correlation coefficient < 0.5')
OUTPUT :
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 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 Checking outliers for all the mean values... Outliers in age are : 0 0 There are no outliers in age column Outliers in annual premium (in Rs) : 0 10332 Replacing the outlier value for numeric columns of annual premium by mean: 0 Now there are no outliers in annual premium (in Rs) Outliers in vintage are : 0 0 There are no outliers in vintage column 0 0 Relation between person age and annual premium is : 0.0506575892861754 There is no relationship between Person age and annual premium because Correlation coefficient < 0.5