Project 2 - EDA on Vehicle Insurance Customer Data

Project 2:

import numpy as np
import pandas as pd
import os
from numpy import *
from pandas import *
import warnings
warnings.filterwarnings('ignore')
import string

reading the files using pandas

DataFrame1 = pd.read_csv(r"C:Userssambasambacustomer details.csv")

DataFrame2 = pd.read_csv(r"C:Userssambasambacustomer_policy table.csv")

DataFrame1.columns=["customer_id","Gender","age","driving licence present","region code","previouslyinsured","vehicle age","vehicle damage"]

DataFrame2.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
DataFrame2

print("Customer details Data Shape: ",DataFrame1.shape)
print("Customer policy details Data Shape: ",DataFrame2.shape)
DataFrame1

DataFrame2

setting columns by lower casing and replacing spaces by underscoring

DataFrame1.columns = DataFrame1.columns.str.lower().str.strip().str.replace(' ','_').str.replace('(','').str.replace(')','')
DataFrame2.columns = DataFrame2.columns.str.lower().str.strip().str.replace(' ','_').str.replace('(','').str.replace(')','')

DataFrame1

DataFrame2

counting null values in total two dataframes

DataFrame1.isnull().sum()

DataFrame2.isnull().sum()

DataFrame2.nunique()

DataFrame1.nunique()

counting null values and replacing individually

print("Null value in 'customer_id' : ",DataFrame1["customer_id"].isnull().sum())
DataFrame1=DataFrame1.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",DataFrame1["customer_id"].isnull().sum())

print("Null value in 'gender' : ",DataFrame1["gender"].isnull().sum())
DataFrame1["gender"]=DataFrame1["gender"].fillna(DataFrame1["gender"].mode()[0])
print("Null value in 'gender' after replacing : ",DataFrame1["gender"].isnull().sum())

print("Null value in 'driving_licence_present' : ",DataFrame1["driving_licence_present"].isnull().sum())
DataFrame1["driving_licence_present"]=DataFrame1["driving_licence_present"].fillna(DataFrame1["driving_licence_present"].mean())
print("Null value in 'driving_licence_present' after replacing : ",DataFrame1["driving_licence_present"].isnull().sum())

print("Null value in 'age' : ",DataFrame1["age"].isnull().sum())
DataFrame1["age"]=DataFrame1["age"].fillna(DataFrame1["age"].mean())
print("Null value in 'age' after replacing : ",DataFrame1["age"].isnull().sum())

print("Null value in 'region_code' : ",DataFrame1["region_code"].isnull().sum())
DataFrame1["region_code"]=DataFrame1["region_code"].fillna(DataFrame1["region_code"].mean())
print("Null value in 'region_code' after replacing : ",DataFrame1["region_code"].isnull().sum())

print("Null value in 'previouslyinsured' : ",DataFrame1["previouslyinsured"].isnull().sum())
DataFrame1["previouslyinsured"]=DataFrame1["previouslyinsured"].fillna(DataFrame1["previouslyinsured"].mean())
print("Null value in 'previouslyinsured' after replacing : ",DataFrame1["previouslyinsured"].isnull().sum())

print("Null value in 'vehicle_age' : ",DataFrame1["vehicle_age"].isnull().sum())
DataFrame1["vehicle_age"]=DataFrame1["vehicle_age"].fillna(DataFrame1["vehicle_age"].mode()[0])
print("Null value in 'vehicle_age' after replacing : ",DataFrame1["vehicle_age"].isnull().sum())

print("Null value in 'vehicle_damage' : ",DataFrame1["vehicle_damage"].isnull().sum())
DataFrame1["vehicle_damage"]=DataFrame1["vehicle_damage"].fillna(DataFrame1["vehicle_damage"].mode()[0])
print("Null value in 'vehicle_damage' after replacing : ",DataFrame1["vehicle_damage"].isnull().sum())

print("Null value in 'customer_id' : ",DataFrame2["customer_id"].isnull().sum())
DataFrame2=DataFrame2.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",DataFrame2["customer_id"].isnull().sum())

print("Null value in 'annual_premium_in_rs' : ",DataFrame2["annual_premium_in_rs"].isnull().sum())
DataFrame2["annual_premium_in_rs"]=DataFrame2["annual_premium_in_rs"].fillna(DataFrame2["annual_premium_in_rs"].mean())
print("Null value in 'annual_premium_in_rs' after replacing : ",DataFrame2["annual_premium_in_rs"].isnull().sum())

print("Null value in 'sales_channel_code' : ",DataFrame2["sales_channel_code"].isnull().sum())
DataFrame2["sales_channel_code"]=DataFrame2["sales_channel_code"].fillna(DataFrame2["sales_channel_code"].mean())
print("Null value in 'sales_channel_code' after replacing : ",DataFrame2["sales_channel_code"].isnull().sum())

print("Null value in 'vintage' : ",DataFrame2["vintage"].isnull().sum())
DataFrame2["vintage"]=DataFrame2["vintage"].fillna(DataFrame2["vintage"].mean())
print("Null value in 'vintage' after replacing : ",DataFrame2["vintage"].isnull().sum())

print("Null value in 'response' : ",DataFrame2["response"].isnull().sum())
DataFrame2["response"]=DataFrame2["response"].fillna(DataFrame2["response"].mean())
print("Null value in 'response' after replacing : ",DataFrame2["response"].isnull().sum())

is there any other null values then drop it

# drop rows and colums containing NaN values
DataFrame1.dropna(axis = 0)
DataFrame1.dropna(axis = 1)

# drop rows and colums containing NaN values
DataFrame2.dropna(axis = 0)
DataFrame2.dropna(axis = 1)

check the null values again if 0 then proceed

DataFrame1.isnull().sum()

DataFrame2.isnull().sum()

getting numerical value columns only by describe function

DataFrame1.describe()

DataFrame2.describe()

setting outliers

Q1=DataFrame1.describe().loc["25%","customer_id"]
Q3=DataFrame1.describe().loc["75%","customer_id"]
IQR=Q3-Q1
print(DataFrame1.loc[DataFrame1["customer_id"]<(Q1-IQR*1.5),"customer_id"].count())
print(DataFrame1.loc[DataFrame1["customer_id"]>(Q3+IQR*1.5),"customer_id"].count())

Q1=DataFrame1.describe().loc["25%","driving_licence_present"]
Q3=DataFrame1.describe().loc["75%","driving_licence_present"]
IQR=Q3-Q1
print(DataFrame1.loc[DataFrame1["driving_licence_present"]<(Q1-IQR*1.5),"driving_licence_present"].count())
print(DataFrame1.loc[DataFrame1["driving_licence_present"]>(Q3+IQR*1.5),"driving_licence_present"].count())

Q1=DataFrame1.describe().loc["25%","previouslyinsured"]
Q3=DataFrame1.describe().loc["75%","previouslyinsured"]
IQR=Q3-Q1
print(DataFrame1.loc[DataFrame1["previouslyinsured"]<(Q1-IQR*1.5),"previouslyinsured"].count())
print(DataFrame1.loc[DataFrame1["previouslyinsured"]>(Q3+IQR*1.5),"previouslyinsured"].count())

Q1=DataFrame1.describe().loc["25%","region_code"]
Q3=DataFrame1.describe().loc["75%","region_code"]
IQR=Q3-Q1
print(DataFrame1.loc[DataFrame1["region_code"]<(Q1-IQR*1.5),"region_code"].count())
print(DataFrame1.loc[DataFrame1["region_code"]>(Q3+IQR*1.5),"region_code"].count())

Q1=DataFrame1.describe().loc["25%","age"]
Q3=DataFrame1.describe().loc["75%","age"]
IQR=Q3-Q1
print(DataFrame1.loc[DataFrame1["age"]<(Q1-IQR*1.5),"age"].count())
print(DataFrame1.loc[DataFrame1["age"]>(Q3+IQR*1.5),"age"].count())

Q1=DataFrame2.describe().loc["25%","annual_premium_in_rs"]
Q3=DataFrame2.describe().loc["75%","annual_premium_in_rs"]
IQR=Q3-Q1
print(DataFrame2.loc[DataFrame2["annual_premium_in_rs"]<(Q1-IQR*1.5),"annual_premium_in_rs"].count())
print(DataFrame2.loc[DataFrame2["annual_premium_in_rs"]>(Q3+IQR*1.5),"annual_premium_in_rs"].count())

Q1=DataFrame2.describe().loc["25%","sales_channel_code"]
Q3=DataFrame2.describe().loc["75%","sales_channel_code"]
IQR=Q3-Q1
print(DataFrame2.loc[DataFrame2["sales_channel_code"]<(Q1-IQR*1.5),"sales_channel_code"].count())
print(DataFrame2.loc[DataFrame2["sales_channel_code"]>(Q3+IQR*1.5),"sales_channel_code"].count())

Q1=DataFrame2.describe().loc["25%","vintage"]
Q3=DataFrame2.describe().loc["75%","vintage"]
IQR=Q3-Q1
print(DataFrame2.loc[DataFrame2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print(DataFrame2.loc[DataFrame2["vintage"]>(Q3+IQR*1.5),"vintage"].count())

Q1=DataFrame2.describe().loc["25%","response"]
Q3=DataFrame2.describe().loc["75%","response"]
IQR=Q3-Q1
print(DataFrame2.loc[DataFrame2["response"]<(Q1-IQR*1.5),"response"].count())
print(DataFrame2.loc[DataFrame2["response"]>(Q3+IQR*1.5),"response"].count())

remove white spaces columnwise if there

DataFrame1["gender"]=DataFrame1["gender"].str.strip()
DataFrame1["vehicle_age"]=DataFrame1["vehicle_age"].str.strip()
DataFrame1["vehicle_damage"]=DataFrame1["vehicle_damage"].str.strip()

converting uppercase to lowercase columnwise if there

DataFrame1["gender"]=DataFrame1["gender"].str.lower()
DataFrame1["vehicle_age"]=DataFrame1["vehicle_age"].str.lower()
DataFrame1["vehicle_damage"]=DataFrame1["vehicle_damage"].str.lower()

creating dummies

dummies_gender=pd.get_dummies(DataFrame1["gender"])
dummies_vehicle_age=pd.get_dummies(DataFrame1["vehicle_age"])
dummies_vehicle_damage=pd.get_dummies(DataFrame1["vehicle_damage"])
dummies_d_l_p=pd.get_dummies(DataFrame1["driving_licence_present"])
dummies_p_i=pd.get_dummies(DataFrame1["previouslyinsured"])
dummies_response=pd.get_dummies(DataFrame2["response"])

counting duplicated values

print(DataFrame1.duplicated().sum())
print(DataFrame2.duplicated().sum())

dropping duplicates if threre

DataFrame1.drop_duplicates(inplace=True)
DataFrame2.drop_duplicates(inplace=True)

merging dataframe by column of customer id

MasterDataFrame = pandas.merge(DataFrame1, DataFrame2, on='customer_id')

MasterDataFrame

1.genderwise annual premium

res1 = MasterDataFrame.groupby('gender')['annual_premium_in_rs'].mean()
import matplotlib.pyplot as pyplot
res1.plot()
pyplot.show()

res1 = MasterDataFrame.groupby('gender')['annual_premium_in_rs'].mean()
import matplotlib.pyplot as pyplot
res1.plot.bar()
pyplot.show()

2.agewise annual premium

res2 = MasterDataFrame.groupby('age')['annual_premium_in_rs'].mean()
import matplotlib.pyplot as pyplot
res2.plot()
pyplot.show()

res2 = MasterDataFrame.groupby('age')['annual_premium_in_rs'].mean()
import matplotlib.pyplot as pyplot
res2.plot.bar()
pyplot.show()

4.vehicle age wise annual premium

res3 = MasterDataFrame.groupby('vehicle_age')['annual_premium_in_rs'].mean()
import matplotlib.pyplot as pyplot
res3.plot()
pyplot.show()

res3 = MasterDataFrame.groupby('vehicle_age')['annual_premium_in_rs'].mean()
import matplotlib.pyplot as pyplot
res3.plot.bar()
pyplot.show()

5.relation between age and annual premium

rf = MasterDataFrame['age'].corr(MasterDataFrame['annual_premium_in_rs'])
if rf<-0.5:
    print("Strong negative relationship")
elif rf>0.5:
    print("Strong positive relationship")
elif rf>-0.5 and rf<0.5:
    print("There is no relationship!")
else:
    print(rf)

3.is data balanced according to genders

res1

res4 = MasterDataFrame.groupby('gender')['sales_channel_code'].mean()
import matplotlib.pyplot as pyplot
res4.plot.bar()
pyplot.show()

res4

res5 = MasterDataFrame.groupby('gender')['vintage'].mean()
import matplotlib.pyplot as pyplot
res5.plot.bar()
pyplot.show()

res5

res6 = MasterDataFrame.groupby('gender')['age'].mean()
import matplotlib.pyplot as pyplot
res6.plot.bar()
pyplot.show()

res6

res4,res6 shows the mean values of different aspects according to genderwise

they are difference between other

so data are not balanced according to gender .

data is balanced accordong to gender by annual premium and vintage.

res1,res5 are same in mean