Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd
import numpy as np

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

381109 rows × 8 columns

df1.columns=['customer_id', 'Gender','age',  'driving_licence_present','region_code','previously_insured','vehicle_age','vehicle_damage']

df1

381109 rows × 8 columns

#checking and cleaning data from customer_details.csv

#Null values of columns

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

df1.dropna(subset='customer_id',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['age'].fillna(df1['age'].mean(),inplace=True)

print('Null Values in age after droping Null values:',df1['age'].isna().sum())

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

df1.describe()

#outliners

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

IQR=Q3-Q1

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%","region_code"]
Q3=df1.describe().loc["75%","region_code"]

IQR=Q3-Q1

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

#Remove spaces

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

#case correction

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

#Convert nominal into dummies

dummies_gender=pd.get_dummies(df1["Gender"])
dummies_vehicle_age=pd.get_dummies(df1["vehicle_age"])
dummies_vehicle_damage=pd.get_dummies(df1["vehicle_damage"])
dummies_dlp=pd.get_dummies(df1["driving_licence_present"])
dummies_pi=pd.get_dummies(df1["previously_insured"])

#Drop duplicates

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

#importing customer_policy_details.csv

df2=pd.read_csv('Week6/customer_policy_details.csv')
df2

381109 rows × 5 columns

df2.columns=['customer_id','annual_premium(Rs)','sales_channel_code','vintage','response']

df2

381109 rows × 5 columns

#checking and cleaning data from customer_details.csv

#Null values of columns

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',inplace=True)

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

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

#outliners

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

IQR=Q3-Q1

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

mean=df2["annual_premium(Rs)"].mean()

mean

df2.loc[df2["annual_premium(Rs)"]>(Q3+IQR*1.5),"annual_premium(Rs)"]=np.nan

df2.fillna(mean,inplace=True)

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

df2['annual_premium(Rs)']

# white space,case correction,creating dummies not because there is no nominal data

#drop dupicates

print(df2.duplicated().sum())

#Merging

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

master_data

380336 rows × 12 columns

#Gender wise average annual premium

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

gwap

#Age wise average annual premium

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

awap

#Is your data balanced between the genders?

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

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

gwap

#Vehicle age wise average annual premium

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

vwap

#relation between Person Age and annual premium

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

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