Challenge

Project 2 - EDA on Vehicle Insurance Customer Data

SSrujana Arli3 Jun 2023

1.a Add thecolumn names to the both datasets

In[] import pandas as pd

import numpy as np

In[] customer_details=pd.read_csv('customer_details.csv')

In[] customer_policy_details=pd.read_csv('customer_policy_details.csv')

i. Column Name for customer details table:

In[] 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)

In[] customer_details.head()

ii. Column Name for customer_policy table:

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

In[] 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

In[] customer_details.isna().sum()

In[] customer_policy_details.isna().sum()

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

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

In[] customer_details.isna().sum()

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

In[] customer_policy_details.isna().sum()

Replace all null values for numeric columns by mean.

In[] customer_policy_details.fillna(customer_policy_details.mean().mean(),inplace=True)

In[] customer_policy_details.isna().sum()

In[] customer_details.isna().sum()

Replace all null values for Categorical value by mode.

In[] 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])

In[] customer_details.isnull().sum()

ii. Outliers

Generate a summary of count of all the outliers column wise

In[] 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.

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

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

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

In[] iqr=q3-q1

In[] l=q1-(1.5*iqr)

u=q3+(1.5*iqr)

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

iii. White spaces

Remove white spaces

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

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

In[] customer_details.apply(lambda x: x.astype(str).str.upper())

v. Convert nominal data (categorical) into dummies

for future modeling use if required

In[] customerdetails=pd.get_dummies(customer_details)

In[] customerdetails

vi. Drop Duplicates (duplicated rows)

In[] 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)

In[] master_table=pd.merge(customer_details,customer_policy_details)

In[] 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

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

ii. Age wise average annual premium

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

iii. Is your data balanced between the genders?

In[] master_table['Gender'].value_counts()

iv. Vehicle age wise average annual premium.

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

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

In[] 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[] np.corrcoef(master_table['Age'],master_table['Annual Premium(Rs)'])

Project 2 - EDA on Vehicle Insurance Customer Data

Ship work like this
in your own course.

Talk to a career counsellor

Ship work like thisin your own course.

Talk to a career counsellor

Ship work like this
in your own course.