Project 2 - EDA on Vehicle Insurance Customer Data

A company has customer data that contains 8 columns of customer details and another table having the 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, the following tasks DS team decided to perform:

1Q Add the column names to both datasets:

i. Column Name for customer details table:

customer_id, 

Gender,

age, 

driving license present,

region code, 

previously insured, 

vehicle age and vehicle damage, in respective order.

# Libraries

import pandas as pd

import numpy as np

customer_details=pd.read_csv('customer_details.CSV)

table2=pd.read_csv("C:/Users/Sridevi/Downloads/customer_policy.csv")

table2.head(5)

table1=pd.read_csv("C:/Users/Sridevi/Downloads/customer_details.csv")

table1.head(7)

table1.rename({'0':'Customer_Id','1':'Gender','2':'Age','3':'Driving_LicencePresent','4':'Region_Code','5':'Previously_Insured','6':'Vehicle_Age','7':'Vehicle_Damage'},axis=1,inplace=True)

table1.head(8)

ii Column Name for customer_policy table:

   customer_id, 

   annual premium (in Rs), 

   sales channel code, 

   vintage and response.


table2.rename({'0':'Customer_Id','1':'Annual_premium','2':'sales_channel_code',
'3':'vintage','4':'response'},axis=1,inplace=True)

table2

2Q Checking and Cleaning Data Quality:

i. Null values

Generate a summary of the count of all the null values column wise Drop Null values for customer_id because central tendencies for ids are not feasible. Replace all null values for numeric columns by mean. Replace all null values for Categorical value by mode.

table1.isnull().sum()

table2.isnull().sum()

Drop Null values for customer_id

table1.drop a(subset=['Customer_Id'],inplace=True)

table1.isnull().sum()

table2.dropna(subset=['Customer_Id'],inplace=True)

table2.isnull().sum()

table1.info()

Replace all null values for numeric columns by mean.

table1['Age'].fillna(table1['Age'].mean(),inplace=True)

table1.isna().sum()

Replace all null values for Categorical value by mode.

table1['Gender']

table1

table1['Gender'].fillna(table1['Gender'].mode()[0],inplace=True)

table1['Region_Code']

table1.describe()
table_1=table1[['Customer_Id','Age','Driving_LicencePresent','Region_Code','Previously_Insured']]

ii. Outliers

Generate a summary of the count of all the outliers column wise

Replace all outlier values for numeric columns by mean.

Hint1: for outlier treatment use the 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)

# outliers mean
def finding_outlier(pdf):
q1=df.quantile(0.25)
q3=df.quantile(0.75)
iqr=q3-q1
outlier=df[((df<1.5*iqr)|(df>(q3+1.5*iqr)))]
return outlier

iii. White spaces

Remove white spaces

table1.apply(lambda x: x.str.strip() if x.dtype=='object' else x)

iv. case correction(lower or upper, anyone)

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

table1.apply(lambda x: x.astype(str).str.lower())

v. Convert nominal data (categorical) into dummies

for future modeling use if required

table1['Gender']

vi. Drop Duplicates (duplicated rows)

table1.drop_duplicates()
table1.head()

3Q 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(table1,table2,on='Customer_Id')

master_table

4Q Company needs some important information from the master table to make decisions for future growth. They need the following information:

i. Gender wise average annual premium

master_table.group by('Gender')['Annual_premium'].mean()

master_table.group by('Gender')['Annual_premium'].mean()

master_table.groupby(['Age'])['Annual_premium'].mean()