Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd

import numpy as np
customer_details=pd.read_csv('customer_details.csv')
customer_policy_details=pd.read_csv('customer_policy_details.csv')

Q-1

1 Column Name for customer details table:

customer_details.rename({'0':'Customer_Id','1':'Gender','2':'Age','3':'Driving Licence Present','4':'Region Code','5':'Previously Insured','6':'Vehicle Age','7':'Vehicle Damage'},axis=1,inplace=True)
customer_details.head()

2 Column Name for customer_policy table:

customer_policy_details.rename({'0':'Customer_Id','1':'Annual Premium(Rs)','2':'Sales Channel Code','3':'Vintage','4':'Response'},axis=1,inplace=True)
customer_policy_details.head()

Q-2

1 Null values

customer_details.isna().sum()

customer_policy_details.isna().sum()

Replace all null values for numeric columns by mean.

customer_policy_details.fillna(customer_policy_details.mean().mean(),inplace=True)
customer_policy_details.isna().sum()
customer_details.isna().sum()

Replace all null values for Categorical value by mode.

customer_details['Gender']=customer_details['Gender'].fillna(customer_details['Gender'].mode()[0])
customer_details['Age']=customer_details['Age'].fillna(customer_details['Age'].mode()[0])
customer_details['Driving Licence Present']=customer_details['Driving Licence Present'].fillna(customer_details['Driving Licence Present'].mode()[0])
customer_details['Region Code']=customer_details['Region Code'].fillna(customer_details['Region Code'].mode()[0])
customer_details['Previously Insured']=customer_details['Previously Insured'].fillna(customer_details['Previously Insured'].mode()[0])
customer_details['Vehicle Age']=customer_details['Vehicle Age'].fillna(customer_details['Vehicle Age'].mode()[0])
customer_details['Vehicle Damage']=customer_details['Vehicle Damage'].fillna(customer_details['Vehicle Damage'].mode()[0])


customer_details.isnull().sum()

2 Outliers

q1=customer_policy_details.quantile(0.25)
q3=customer_policy_details.quantile(0.75)
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((customer_policy_details<l)|(customer_policy_details>u)).sum()

Replace all outlier values for numeric columns by mean.

customer_policy_details['Annual Premium(Rs)']=customer_policy_details['Annual Premium(Rs)'].astype('float')

q1=customer_policy_details[['Annual Premium(Rs)']].quantile(0.25)
q3=customer_policy_details[['Annual Premium(Rs)']].quantile(0.75)

iqr=q3-q1

l=q1-(1.5*iqr)
u=q3+(1.5*iqr)

((customer_policy_details[['Annual Premium(Rs)']]<l)|(customer_policy_details[['Annual Premium(Rs)']]>u)).sum()

3 White spaces

customer_details['Gender'].str.strip()

4 case correction(lower or upper, any one)

customer_details.apply(lambda x: x.astype(str).str.upper())

5 Convert nominal data (categorical) into dummies

customerdetails=pd.get_dummies(customer_details)

customerdetails


customerpolicydetails=pd.get_dummies(customer_policy_details)

customerpolicydetails

6 Drop Duplicates (duplicated rows)

customerdetails.drop_duplicates()

Q-3

master_table=pd.merge(customer_details,customer_policy_details)

master_table['Age']=master_table['Age'].astype('int')
master_table

Q-4

1 Gender wise average annual premium

master_table.groupby(['Gender'])['Annual Premium(Rs)'].mean()

2 Age wise

master_table.groupby(['Age'])['Annual Premium(Rs)'].mean()

3 Is your data balanced between the genders?

master_table['Gender'].value_counts()

4 Vehicle age wise

master_table.groupby(['Vehicle Age'])['Annual Premium(Rs)'].mean()

Q-5

np.corrcoef(master_table['Age'],master_table['Annual Premium(Rs)'])# There is no relationship