Project 2 - 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:

import numpy as np

import pandas as pd

from matplotlib import pyplot as plt

import seaborn as sns

customerdetail = pd.read_csv('.\Files\customer_details.csv')

customerpoliceydetail = pd.read_csv('.\Files\customer_policy_details.csv')

customerdetail

output:

customerpoliceydetail

output:

1. Add the column names to both datasets:

i. Column Name for customer details table:

ii. Column Name for customer_policy table:

col1 = ["Customer_ID","Gender","Age","Driving Licence Present","Region Code","Previously Insured","Vehicle

Age","Vehicle Damage"]

customerdetail.columns = col1

customerdetail.head()

output:

col2 = ["Customer_ID","Annual Premium","Sales channel Code","Vintage","Response"]

customerpoliceydetail.columns = col2

customerpoliceydetail.head()

output:

2. Checking and Cleaning Data Quality:

i. Null values

ii. Outliers

iii. White spaces

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

v. Convert nominal data (categorical) into dummies

vi. Drop Duplicates (duplicated rows)

# null values

customerdetail.isnull()

output:

customerpoliceydetail.isnull()

output:

# Drop Null values for CoustomerDeatil

customerdetail.dropna(subset=['Customer_ID'],axis = 0,inplace = True)

customerdetail.reset_index(drop = True,inplace=True)

# Drop Null values for customerPoliceyDetail

customerpoliceydetail.dropna(subset=['Customer_ID'],axis = 0,inplace = True)

customerpoliceydetail.reset_index(drop = True,inplace=True)

# Replace all null values for numeric columns by mean for CoustomerDeatil

customerdetail['Age'].fillna(customerdetail['Age'].mean())

output:

# Replace all null values for numeric columns by mean for CoustomerDeatil

customerdetail['Age'].fillna(customerdetail['Age'].mean())

output:

customerdetail['Region Code'].fillna(customerdetail['Region Code'].mean())

output:

# Replace all null values for numeric columns by mean for customerPoliceyDetail

customerpoliceydetail['Annual Premium'].fillna(customerpoliceydetail['Annual Premium'].mean())

output:

customerpoliceydetail['Vintage'].fillna(customerpoliceydetail['Vintage'].mean())

output:

# Replace all null values for Categorical value by mode for CoustomerDeatil

customerdetail.fillna(0)

output:

# Replace all null values for Categorical value by mode for customerPoliceyDetail

customerpoliceydetail.fillna(0)

output:

customerdetail.describe()

output:

customerpoliceydetail.describe()

output:

Q1 = customerpoliceydetail.quantile(0.25)

Q3 = customerpoliceydetail.quantile(0.75)

IQR = Q3 - Q1

print(IQR)

output:

# Outliers for CoustomerDeatil

def plot_boxplot(df,ft):

df.boxplot(column=[ft])

plt.grid(False)

plt.show

plot_boxplot(customerdetail,'Age')

output:

def plot_boxplot(df,ft):

df.boxplot(column=[ft])

plt.grid(False)

plt.show

plot_boxplot(customerdetail,'Region Code')

output:

# Outliers for customerPoliceyDetail

def plot_boxplot(df,ft):

df.boxplot(column=[ft])

plt.grid(False)

plt.show

plot_boxplot(customerpoliceydetail,'Annual Premium')

output:

def plot_boxplot(df,ft):

df.boxplot(column=[ft])

plt.grid(False)

plt.show

plot_boxplot(customerpoliceydetail,'Vintage')

output:

damagevehicle = pd.get_dummies(customerdetail['Vehicle Damage'])

damagevehicle.rename(columns={'No':'Vehicale Damage No','Yes':'Vehicle Damage Yes'},inplace = True)

damagevehicle.head()

output:

# CustomerDetail

percentile25 = customerdetail['Age'].quantile(0.25)

percentile75 = customerdetail['Age'].quantile(0.75)

iqr = np.subtract(*np.percentile(1, [75, 25]))

upper_limit = percentile75 + 1.5 * iqr

lower_limit = percentile25 - 1.5 * iqr

customerdetail[customerdetail['Age'] > upper_limit]

customerdetail[customerdetail['Age'] < lower_limit]

output:

# CustomerPoliceyDetail

percentile25 = customerpoliceydetail['Annual Premium'].quantile(0.25)

percentile75 = customerpoliceydetail['Annual Premium'].quantile(0.75)

iqr = np.subtract(*np.percentile(1, [75, 25]))

upper_limit = percentile75 + 1.5 * iqr

lower_limit = percentile25 - 1.5 * iqr

customerpoliceydetail[customerpoliceydetail['Annual Premium'] > upper_limit]

customerpoliceydetail[customerpoliceydetail['Annual Premium'] < lower_limit]

output:

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.

mastertable = pd.merge(customerdetail,customerpoliceydetail,on='Customer_ID')

mastertable

output:

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

average = mastertable.groupby('Gender')['Annual Premium'].mean()

average

output:

average.plot()

output:

average.plot.bar(title='Gender vs Annual Premium')

output:

# Age wise average annual premium

average = mastertable.groupby('Age')['Annual Premium'].mean()

average

output:

average.plot()

output:

average.plot.bar()

output:

mastertable.groupby('Gender').count()

output:

vehicalageaverage = mastertable.groupby('Vehicle Age')['Annual Premium'].mean()

vehicalageaverage

output:

vehicalageaverage.plot()

output:

vehicalageaverage.plot.bar()

output:

5. Is there any relation between Person Age and annual premium?

mastertable['Age'].corr(mastertable['Annual Premium'])

output:

mastertable[['Age','Annual Premium']].corr()

output: