Project 2 - EDA on Vehicle Insurance Customer Data
# Project 2 - EDA on Vehicle Insurance Customer Data
#import libraries
import pandas as pd
import numpy as np
#Reading .csv file to dataframe and Adding column names
customer_details = pd.read_csv('.\customer_details.csv')
#Reading .csv file to dataframe and Adding column names
customer_policy_details = pd.read_csv('.\customer_policy_details.csv')
#Adding column names for customer_details table
customer_details.columns = ('customer_id','Gender','age', 'driving licence present','region code', 'previously insured','vehicle age','vehicle damage')
customer_details
#Adding column names for customer_policy table
customer_policy_details.columns = ('customer_id','annual premium (in rs)','sales channel code','vintage','response')
customer_policy_details
#Generate a summary of count of all the null values column wise
#for customer_details DataFrame
summary_of_null_values_for_customer_details = customer_details.isna().sum()
summary_of_null_values_for_customer_details
summary_of_null_values_for_customer_details.plot.bar()
#for customer_policy_details DataFrame
summary_of_null_values_for_customer_policy_details = customer_policy_details.isna().sum()
summary_of_null_values_for_customer_policy_details.plot.bar()
summary_of_null_values_for_customer_policy_details.plot.bar()
#in customer_details DataFrame
customer_details = customer_details.dropna(subset = ['customer_id'])
#in customer_policy_details DataFrame
customer_policy_details = customer_policy_details.dropna(subset = ['customer_id'])
#For customer_details DataFrame
customer_details = customer_details.fillna(customer_details.mean(numeric_only = True))
#For customer_policy_details
customer_policy_details = customer_policy_details.fillna(customer_policy_details.mean(numeric_only = True))
#In customer_details DataFrame
customer_details['Gender'] = customer_details['Gender'].fillna(customer_details.Gender.value_counts().index[0])
customer_details['vehicle age'] = customer_details['vehicle age'].fillna(customer_details['vehicle age'].value_counts().index[0])
customer_details['vehicle damage'] = customer_details['vehicle damage'].fillna(customer_details['vehicle damage'].value_counts().index[0])
#check if any null values present in customer_details DataFrame
customer_details.isna().sum()
customer_details.isna().sum().plot.bar()
#check if any null values present in customer_policy_details DataFrame
customer_policy_details.isna().sum()
customer_policy_details.isna().sum().plot.bar()
customer_details.describe()
def find_outliers_IQR(df):
"""This Method Finds the Outliers present in a DataFrame"""
q1=df.quantile(0.25)
q3=df.quantile(0.75)
IQR=q3-q1
outliers = df[((df<(q1-1.5*IQR)) | (df>(q3+1.5*IQR)))]
return outliers
#find outliers for coulmns in customer_details DataFrame
outliers = find_outliers_IQR(customer_details['customer_id'])
a = outliers.value_counts().sum()
outliers1 = find_outliers_IQR(customer_details['age'])
b = outliers1.value_counts().sum()
outliers2 = find_outliers_IQR(customer_details['driving licence present'])
c = outliers2.value_counts().sum()
outliers3 = find_outliers_IQR(customer_details['region code'])
d = outliers3.value_counts().sum()
outliers4 = find_outliers_IQR(customer_details['previously insured'])
e = outliers4.value_counts().sum()
#creating a DataFrame to display the summary of count of outliers column wise
outliers_in_customer_details = pd.DataFrame([a,b,c,d,e],['customer_id','age','driving licence present','region code','previously insured'],columns = ['num of outliers'])
outliers_in_customer_details
outliers_in_customer_details.plot.bar()
#find outliers for coulmns in customer_policy_details DataFrame
outliers = find_outliers_IQR(customer_policy_details['customer_id'])
a = outliers.value_counts().sum()
outliers1 = find_outliers_IQR(customer_policy_details['annual premium (in rs)'])
b = outliers1.value_counts().sum()
outliers2 = find_outliers_IQR(customer_policy_details['sales channel code'])
c = outliers2.value_counts().sum()
outliers3 = find_outliers_IQR(customer_policy_details['vintage'])
d = outliers3.value_counts().sum()
outliers4 = find_outliers_IQR(customer_policy_details['response'])
e = outliers4.value_counts().sum()
#creating a DataFrame to display the summary of count of outliers column wise
outliers_in_customer_policy_details = pd.DataFrame([a,b,c,d,e],['customer_id','annual premium (in rs)','sales channel code','vintage','response'],columns = ['num of outliers'])
outliers_in_customer_policy_details
outliers_in_customer_policy_details.plot.bar()
def replace_outliers(df):
"""This Method replaces the Outliers with the mean in a DataFrame"""
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 < lower,df.mean(),df))
return df
#replacing outliers value with the mean value for customer_details DataFrame
#outliers value found only in 'driving licence present' column.
replace_outliers(customer_details['driving licence present'])
#replacing outliers value with the mean value for customer_policy_details DataFrame
#outliers found in 'annual premium (in rs)' and 'response' column
replace_outliers(customer_policy_details['annual premium (in rs)'])
replace_outliers(customer_policy_details['response'])
customer_details.describe()
customer_policy_details.describe()
#Remove whitespaces
customer_details['Gender'] = customer_details['Gender'].str.strip()
#Case correction to Upper case
customer_details['Gender'] = customer_details['Gender'].str.upper()
customer_details['Gender']
#for 'Gender' column
Gender_dummy =pd.get_dummies(customer_details['Gender'])
Gender_dummy
#for 'vehicle age' column
vehicle_age_dummy =pd.get_dummies(customer_details['vehicle age'])
vehicle_age_dummy
#for 'vehicle damage' column
vehicle_damage_dummy = pd.get_dummies(customer_details['vehicle damage'])
vehicle_damage_dummy
#drop duplicated rows
customer_details = customer_details.drop_duplicates()
customer_details
customer_policy_details = customer_policy_details.drop_duplicates()
customer_policy_details
master_table = pd.merge(customer_details,customer_policy_details,on = 'customer_id')
master_table
#Gender wise average annual premium
print('Gender wise average annual premium')
print(master_table.groupby('Gender')['annual premium (in rs)'].mean())
master_table.groupby('Gender')['annual premium (in rs)'].mean().plot.bar(ylabel = 'Annual Premium (in Rs)')
#Age wise average annual premium
print('Age wise average annual premium')
print(master_table.groupby('age')['annual premium (in rs)'].mean())
master_table.groupby('age')['annual premium (in rs)'].mean().plot()
if master_table.groupby('Gender')['annual premium (in rs)'].mean().value_counts().iloc[0] == master_table.groupby('Gender')['annual premium (in rs)'].mean().value_counts().iloc[1]:
print('Data is balanced gender wise')
else:
print('Data is not balance gender wise')
master_table.groupby('Gender')['annual premium (in rs)'].mean().plot.bar(ylabel = 'Annual Premium (in Rs)')
print('Vehicle age wise average annual premium')
print(master_table.groupby('vehicle age')['annual premium (in rs)'].mean())
master_table.groupby('vehicle age')['annual premium (in rs)'].mean().plot.bar()
correlation_coefficient = master_table['age'].corr(master_table['annual premium (in rs)'])
correlation_coefficient
if correlation_coefficient < -0.5:
print('There is Strong negative relationship between Person Age and Annual Premium')
elif correlation_coefficient > 0.5:
print('There is Strong postive relationship between Person Age and Annual Premium')
elif correlation_coefficient < 0.5 and 0.5 < correlation_coefficient:
print('There is no relation between Person Age and Annual Premium')
else:
print('There is a good relation between Person Age and Annual Premium')