Project 2 - EDA on Vehicle Insurance Customer Data

Aim : To write a program to check the  EDA on Vehicle Insurance Customer Data in python.

Objective :

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:

1. Add the column names to both datasets:

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. 

ii. Column Name for customer_policy table:

           customer_id, 

           annual premium (in Rs), 

           sales channel code, 

           vintage and response. 

2. Checking and Cleaning Data Quality:

i. Null values

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

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)

iii. White spaces

• Remove white spaces

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

v. Convert nominal data (categorical) into dummies 

• for future modeling use if required

vi. Drop Duplicates (duplicated rows)

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)

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.

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. 

#libraies importing 
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

#loading the data 

df1 = pd.read_csv("customer_details.csv")
df2 = pd.read_csv("customer_policy_details.csv")

#Adding column names to customer Details provided in data set

col1=["Id","Gender","Age","Driving licence Active","regin code","Previously Insured","Vehical Age","Vehical Damage"]
df1.columns = col1
df1.head()

#Adding columns to the Customer policy details provided in data set

col2 = ["Id","Anaual Salary","Sale channel code","Vintage","Response"]
df2.columns=col2
df2.head()

# checking for customer detials df1 Null Value present or not

df1_n = df1.isnull()

for i in df1_n.columns.values.tolist():
    print(i)
    print(df1_n[i].value_counts())
    print("")

# checking for customer detials df2 Null Value present or not

df2_n = df2.isnull()
for i in df2_n.columns.values.tolist():
    print(i)
    print(df2_n[i].value_counts())
    print("")

#Dropping all Null Values in customer Deatils

df1.dropna(subset=['Id'],axis = 0,inplace = True)
df1.reset_index(drop = True,inplace=True)

#Dropping all Null Values in customer Policy Deatils

df2.dropna(subset=['Id'],axis = 0,inplace = True)
df2.reset_index(drop = True,inplace=True)


#Replacing the all NaN value of age by its mean value in the data
df1['Age'].fillna(df1['Age'].mean(),inplace=True)

##Replacing the NaN value of region code by its mean value in the data
df1['regin code'].fillna(df1['regin code'].mean(),inplace=True)
df1.head()

#Replacing the NaN value of Driving licence Active by its mode value in the data set
df1['Driving licence Active'].fillna(df1['Driving licence Active'].mode()[0],inplace=True)

##Replacing the NaN value of Previously Insured by its mode value in the data set
df1['Previously Insured'].fillna(df1['Previously Insured'].mode()[0],inplace=True)
df1.head()

#df2
df2['Response'].fillna(df2['Response'].mode()[0],inplace=True)
df2.head()

#Visualisation of Outliers

def plot_boxplot(df,ft):
    df.boxplot(column=[ft])
    plt.grid(False)
    plt.show

plot_boxplot(df1,'Age')

plot_boxplot(df1,'regin code')

#IQR Method

def finding_out(df):
    q1 = df.quantile(0.25)
    q3=df.quantile(0.75)
    iqr=q3-q1
    outlier = df[((df<(q1-1.5*iqr)))|(df>(q3+1.5*iqr))]
    return outlier
outliers = finding_out(df1['regin code'])
print("Number of outliers:",len(outliers))
print("Max Number of outliers:",outliers.max())
print("Minimum outlier value:",outliers.min())

#Finding Outliers for customer policy details

plot_boxplot(df2,'Anaual Salary')

plot_boxplot(df2,'Sale channel code')

plot_boxplot(df2,'Vintage')

#The summary of all the Outliers

print("For Annual Salary")
outliers = finding_out(df2['Anaual Salary'])

print("Number of outliers:",len(outliers))

print("Max Number of outliers:",outliers.max())

print("Minimum outlier value:",outliers.min())

print('=&&='*25,'\n')

print("For sales channel code")

outliers = finding_out(df2['Sale channel code'])

print("Number of outliers:",len(outliers))

print("Max Number of outliers:",outliers.max())

print("Minimum outlier value:",outliers.min())

print('=&&='*25,'\n')

print("For Vintage")

outliers = finding_out(df2['Vintage'])

print("Number of outliers:",len(outliers))

print("Max Number of outliers:",outliers.max())

print("Minimum outlier value:",outliers.min())

print('=&&='*25,'\n')

#Replacing the all outliers with mean values in the data set
def replace(df):
    q1 = df.quantile(0.25)
    q3=df.quantile(0.75)
    iqr=q3-q1
    upper = df[~(df>(q3+1.5*iqr))].max()
    lower = df[~(df>(q3+1.5*iqr))].min()
    return df
replace(df2['Anaual Salary'])

#Remove all White Spaces in the data set

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

#The Case Correction in the data set

df1.apply(lambda x:x.str.upper() if x.dtype == 'object'else x)

#converting the nominal data into dummy variables in the data set

dummy_variable_1 = pd.get_dummies(df1['Vehical Damage'])

dummy_variable_1.rename(columns={'No':'vehicale-damage-No','Yes':'vehicle-damage-Yes'},inplace = True)

dummy_variable_1.head()

master_data = pd.merge(df1,df2,on='Id')
master_data

all_data = master_data.groupby('Gender')['Anaual Salary'].mean()
all_data

all_data.plot()

all_data.plot.bar(title='Gender vs Annual Salary')

age_data =master_data.groupby('Age')['Anaual Salary'].mean() 
age_data

age_data.plot()

age_data.plot.bar()

#Checking Balance
master_data.groupby('Gender').count()

#Vehical age-wise and average salary

vehical_age =master_data.groupby('Vehical Age')['Anaual Salary'].mean()
vehical_age

vehical_age.plot.bar()

master_data['Age'].corr(master_data['Anaual Salary'])

master_data[['Age','Anaual Salary']].corr()

Result : The desired result is achieved using the python