Project 2 - EDA on Vehicle Insurance Customer Data

import numpy as np
import pandas as pd

customer_details = pd.read_csv('customer_details.csv')
customer_policy_details = pd.read_csv('customer_policy_details.csv')

# 1. Adding Column names to both data sets

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

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

""" ii. Column Name for customer_policy table:
customer_id, annual premium (in Rs), sales channel code, vintage and response. """

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

# 2. Checking and Cleaning Data Quality:

# i. Null values

# Generate a summary of count of all the null values column wise

customer_details.isna().sum()

customer_policy_details.isna().sum()

# Drop Null values for customer_id because central tendencies for id’s is not feasible.

customer_details=customer_details.dropna(subset=['Customer_Id'])

customer_details.isna().sum()

customer_policy_details=customer_policy_details.dropna(subset=['Customer_Id'])

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

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

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

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

# iii. White spaces

# Remove white spaces

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

# iv. case correction(lower or upper, any one)

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

# v. Convert nominal data (categorical) into dummies

# for future modeling use if required

customerdetails=pd.get_dummies(customer_details)

customerdetails

customerpolicydetails=pd.get_dummies(customer_policy_details)

customerpolicydetails

# vi. Drop Duplicates (duplicated rows)

customerdetails.drop_duplicates()

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

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

master_table=pd.merge(customer_details,customer_policy_details)

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

# 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

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

# ii. Age wise average annual premium

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

# iii. Is your data balanced between the genders?

# (Hint: Data is balanced if number of counts in each group is approximately same)

master_table['Gender'].value_counts()

# iv. Vehicle age wise average annual premium

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

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

np.corrcoef(master_table['Age'],master_table['Annual Premium(Rs)'])