Project 2 - EDA on Vehicle Insurance Customer Data

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 pandas as pd
import numpy as np

Table1 ( customer_details )

#importing customer_details.csv
df1=pd.read_csv('customer_details.csv')
df1

# Column Name for customer details table:
df1.columns=['customer_id', 'Gender','age', 'driving_licence_present','region_code','previously_insured','vehicle_age','vehicle_damage']
df1

# checking and cleaning data from customer_details.csv

df1.info()

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

print('Null Values in Cusmoter_id:',df1['customer_id'].isna().sum())
print('Null Values in Gender:',df1['Gender'].isna().sum())
print('Null Values in age:',df1['age'].isna().sum())
print('Null Values in driving_licence_present:',df1['driving_licence_present'].isna().sum())
print('Null Values in region_code:',df1['region_code'].isna().sum())
print('Null Values in previously_insured:',df1['previously_insured'].isna().sum())
print('Null Values in vehicle_age:',df1['vehicle_age'].isna().sum())
print('Null Values in vehicle_damage:',df1['vehicle_damage'].isna().sum())

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

df1.dropna(subset=['customer_id'])
df1.dropna(inplace=True)
print('Null Values in Cusmoter_id after droping Null values:',df1['customer_id'].isna().sum())

#Replace all null values for numeric columns by mean.

df1['age'].fillna(df1['age'].mean(),inplace=True)
print('Null Values in age after droping Null values:',df1['age'].isna().sum())

# Replace all null values for Categorical value by mode.

df1['driving_licence_present'].fillna(df1['driving_licence_present'].mode()[0],inplace=True)
print('Null Values in driving_licence_present after droping Null values:',df1['driving_licence_present'].isna().sum())

df1['region_code'].fillna(df1['region_code'].mode()[0],inplace=True)
print('Null Values in region_code after droping Null values:',df1['region_code'].isna().sum())

df1['previously_insured'].fillna(df1['previously_insured'].mode()[0],inplace=True)
print('Null Values in previously_insured after droping Null values:',df1['previously_insured'].isna().sum())

df1['customer_id'].fillna(df1['customer_id'].mode()[0],inplace=True)
print('Null Values in Cusmoter_id after droping Null values:',df1['customer_id'].isna().sum())

df1['Gender'].fillna(df1['Gender'].mode()[0],inplace=True)
print('Null Values in Gender after droping Null values:',df1['Gender'].isna().sum())

df1['vehicle_age'].fillna(df1['vehicle_age'].mode()[0],inplace=True)
print('Null Values in vehicle_age after droping Null values:',df1['vehicle_age'].isna().sum())

df1['vehicle_damage'].fillna(df1['vehicle_damage'].mode()[0],inplace=True)
print('Null Values in vehicle_damage after droping Null values:',df1['vehicle_damage'].isna().sum())

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

df1.describe()

# Generate a summary of count of all the outliers column wise
# Replace all outlier values for numeric columns by mean.

Q1=df1.describe().loc["25%","age"]
Q3=df1.describe().loc["75%","age"]

IQR=Q3-Q1
print("IQR value of age",IQR)

print('Outliners in age:',(Q1-IQR*1.5))
print('Outliners in age:',(Q3+IQR*1.5))

print('Outliners in age:',df1.loc[df1["age"]<(Q1-IQR*1.5),"age"].count())
print('Outliners in age:',df1.loc[df1["age"]>(Q3+IQR*1.5),"age"].count())

Q1=df1.describe().loc["25%","driving_licence_present"]
Q3=df1.describe().loc["75%","driving_licence_present"]

IQR=Q3-Q1
print("IQR value of driving_licence_present",IQR)

print('Outliners in driving_licence_present:',(Q1-IQR*1.5))
print('Outliners in driving_licence_present:',(Q3+IQR*1.5))

print('Outliners in driving_licence_present:',df1.loc[df1["driving_licence_present"]<(Q1-IQR*1.5),"driving_licence_present"].count())
print('Outliners in driving_licence_present:',df1.loc[df1["driving_licence_present"]>(Q3+IQR*1.5),"driving_licence_present"].count())

Q1=df1.describe().loc["25%","region_code"]
Q3=df1.describe().loc["75%","region_code"]

IQR=Q3-Q1
print("IQR value of region_code",IQR)

print('Outliners in region_code:',(Q1-IQR*1.5))
print('Outliners in region_code:',(Q3+IQR*1.5))
print('Outliners in region_code:',df1.loc[df1["region_code"]<(Q1-IQR*1.5),"region_code"].count())
print('Outliners in region_code:',df1.loc[df1["region_code"]>(Q3+IQR*1.5),"region_code"].count())

Q1=df1.describe().loc["25%","previously_insured"]
Q3=df1.describe().loc["75%","previously_insured"]

IQR=Q3-Q1
print("IQR value of previously_insured",IQR)

print('Outliners in previously_insured:',(Q1-IQR*1.5))
print('Outliners in previously_insured:',(Q3+IQR*1.5))
print('Outliners in previously_insured:',df1.loc[df1["previously_insured"]<(Q1-IQR*1.5),"previously_insured"].count())
print('Outliners in previously_insured:',df1.loc[df1["previously_insured"]>(Q3+IQR*1.5),"previously_insured"].count())

# Remove white spaces

df1["Gender"]=df1["Gender"].str.strip()
df1["vehicle_age"]=df1["vehicle_age"].str.strip()
df1["vehicle_damage"]=df1["vehicle_damage"].str.strip()
df1

# case correction(lower or upper, any one)

df1["Gender"]=df1["Gender"].str.upper()
df1["vehicle_age"]=df1["vehicle_age"].str.upper()
df1["vehicle_damage"]=df1["vehicle_damage"].str.upper()
df1

# Convert nominal data (categorical) into dummies
# for future modeling use if required
dummies_gender=pd.get_dummies(df1["Gender"])
dummies_gender

dummies_vehicle_age=pd.get_dummies(df1["vehicle_age"])
dummies_vehicle_age

dummies_vehicle_damage=pd.get_dummies(df1["vehicle_damage"])
dummies_vehicle_damage

dummies_dlp=pd.get_dummies(df1["driving_licence_present"])
dummies_dlp

dummies_pi=pd.get_dummies(df1["previously_insured"])
dummies_pi

#Drop duplicates (duplicated rows)

print(df1.duplicated().sum())

Table 2 ( customer_policy_details )

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

# Column Name for customer_policy table:
df2.columns=['customer_id','annual_premium(Rs)','sales_channel_code','vintage','response']

df2

#checking and cleaning data from customer_details.csv
df2.info()

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

print('Null Values in Cusmoter_id:',df2['customer_id'].isna().sum())
print('Null Values in annual_premium(Rs):',df2['annual_premium(Rs)'].isna().sum())
print('Null Values in sales_channel_code:',df2['sales_channel_code'].isna().sum())
print('Null Values in vintage:',df2['vintage'].isna().sum())
print('Null Values in response:',df2['response'].isna().sum())

#droping and filling null values

df2.dropna(subset=['customer_id'])
df2.dropna(inplace=True)
print('Null Values in Cusmoter_id after droping Null values:',df2['customer_id'].isna().sum())

#Replace all null values for numeric columns by mean.

df2['annual_premium(Rs)'].fillna(df2['annual_premium(Rs)'].mean(),inplace=True)
print('Null Values in annual_premium(Rs) after droping Null values:',df2['annual_premium(Rs)'].isna().sum())

df2['sales_channel_code'].fillna(df2['sales_channel_code'].mean,inplace=True)
print('Null Values in sales_channel_code after droping Null values:',df2['sales_channel_code'].isna().sum())

df2['vintage'].fillna(df2['vintage'].mean,inplace=True)
print('Null Values in vintage after droping Null values:',df2['vintage'].isna().sum())

df2['response'].fillna(df2['response'].mean,inplace=True)
print('Null Values in response after droping Null values:',df2['response'].isna().sum())

df2.describe()

# Generate a summary of count of all the outliers column wise
# Replace all outlier values for numeric columns by mean.

Q1=df2.describe().loc["25%","annual_premium(Rs)"]
Q3=df2.describe().loc["75%","annual_premium(Rs)"]

IQR=Q3-Q1
print("IQR value of annual_premium(Rs)",IQR)

print('Outliners in annual_premium(Rs):',(Q1-IQR*1.5))
print('Outliners in annual_premium(Rs):',(Q3+IQR*1.5))

print('Outliners in annual_premium(Rs):',df2.loc[df2["annual_premium(Rs)"]<(Q1-IQR*1.5),"annual_premium(Rs)"].count())
print('Outliners in annual_premium(Rs):',df2.loc[df2["annual_premium(Rs)"]>(Q3+IQR*1.5),"annual_premium(Rs)"].count())

Q1=df2.describe().loc["25%","sales_channel_code"]
Q3=df2.describe().loc["75%","sales_channel_code"]

IQR=Q3-Q1
print("IQR value of sales_channel_code",IQR)

print('Outliners in sales_channel_code:',(Q1-IQR*1.5))
print('Outliners in sales_channel_code:',(Q3+IQR*1.5))

print('Outliners in sales_channel_code:',df2.loc[df2["sales_channel_code"]<(Q1-IQR*1.5),"sales_channel_code"].count())
print('Outliners in sales_channel_code:',df2.loc[df2["sales_channel_code"]>(Q3+IQR*1.5),"sales_channel_code"].count())

Q1=df2.describe().loc["25%","vintage"]
Q3=df2.describe().loc["75%","vintage"]

IQR=Q3-Q1
print("IQR value of vintage",IQR)

print('Outliners in vintage:',(Q1-IQR*1.5))
print('Outliners in vintage:',(Q3+IQR*1.5))

print('Outliners in vintage:',df2.loc[df2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print('Outliners in vintage:',df2.loc[df2["vintage"]>(Q3+IQR*1.5),"vintage"].count())

Q1=df2.describe().loc["25%","response"]
Q3=df2.describe().loc["75%","response"]

IQR=Q3-Q1
print("IQR value of response",IQR)

print('Outliners in response:',(Q1-IQR*1.5))
print('Outliners in response:',(Q3+IQR*1.5))

print('Outliners in response:',df2.loc[df2["response"]<(Q1-IQR*1.5),"response"].count())
print('Outliners in response:',df2.loc[df2["response"]>(Q3+IQR*1.5),"response"].count())

df2['annual_premium(Rs)']

# white space,case correction,creating dummies not because there is no nominal data
print(df2.duplicated().sum())

Table 1 ( customer_details ) and Table 2 ( customer_policy_details ) merge data

#Merging
master_data=pd.merge(df1,df2,on='customer_id')
master_data

result_ftomratio = f"male to female ration is {round(master_data['Gender'].value_counts()[0]/master_data['Gender'].value_counts()[1],2)}"
print(result_ftomratio)

print(f"generally, the standard is: n balanced data ratio: {50/50}n slightly balanced data ratio: {round(55/45,2)}-{60/40} n imbalanced data ratio: {80/20}-{90/10}")

#Gender wise average annual premium

Gender_wise_average_annual_premium = master_data.groupby(["Gender"])["annual_premium(Rs)"].mean()
Gender_wise_average_annual_premium

import matplotlib.pyplot as pyplot
Gender_wise_average_annual_premium.plot()
pyplot.show()

#Age wise average annual premium

Age_wise_average_annual_premium = master_data.groupby(["age"])["annual_premium(Rs)"].mean()
Age_wise_average_annual_premium

import matplotlib.pyplot as pyplot
Age_wise_average_annual_premium.plot(color='r')
pyplot.show()

#Is your data balanced between the genders?

master_data.groupby('Gender').count()

#Vehicle age wise average annual premium. (Hint: Data is balanced if number of counts in each group is approximately same)

Vehicle_age_wise_average_annual_premium = master_data.groupby(["vehicle_age"])["annual_premium(Rs)"].mean()
Vehicle_age_wise_average_annual_premium

import matplotlib.pyplot as pyplot
Vehicle_age_wise_average_annual_premium.plot(color='g')
pyplot.show()

#relation between Person Age and annual premium

master_data['age'].corr(master_data["annual_premium(Rs)"])

master_data[['age','annual_premium(Rs)']].corr()

# 5. Is there any relation between Person Age and annual premium?
# (Hint: use correlation function (Correlation describes the relationship between two variables))
n = master_data['age'].corr(master_data['annual_premium(Rs)'])
if n<-0.5:
print("Strong negative relationship")
if n>0.5:
print("Strong positive relationship")
if n>-0.5 and n<0.5:
print("There is no relationship!")