Project 2 - EDA on Vehicle Insurance Customer Data

#!/usr/bin/env python
# coding: utf-8

# In[1]:

import pandas as pd

# # Q1

# In[62]:

cust_details = pd.read_csv('customer_details.csv',header=0)
cust_details.columns = ['customer_id','gender','age','driving licence present','region code','previously insured','vehicle age','vehicle damage']
cust_details.head()

# In[63]:

cust_pt = pd.read_csv('customer_policy_details.csv',header=0)
cust_pt.columns = ['customer_id','annual premium','sales channel code','vintage','response']
cust_pt.head()

# # Q2

# In[64]:

cust_details.isna().sum()

# In[65]:

cust_pt.isna().sum()

# In[66]:

cust_details.dropna(subset=['customer_id'],inplace=True)
cust_pt.dropna(subset=['customer_id'],inplace=True)

# In[67]:

for c in cust_details.columns:
if cust_details[c].dtype == 'float64':
cust_details[c].fillna(cust_details[c].mean(),inplace=True)
else:
cust_details[c].fillna(cust_details[c].mode()[0],inplace=True)
for c in cust_pt.columns:
if cust_pt[c].dtype == 'float64':
cust_pt[c].fillna(cust_pt[c].mean(),inplace=True)
else:
cust_pt[c].fillna(cust_pt[c].mode()[0],inplace=True)

# In[68]:

cust_pt.describe()

# In[69]:

def outlier_repair(x):
p25 = x.describe()['25%']
p75 = x.describe()['75%']
iqr = p75 - p25
return p25 - 1.5*iqr, p75 + 1.5*iqr

for c in cust_details.columns:
if cust_details[c].dtype == 'float64':
m = cust_details[c].mean()
a, b = outlier_repair(cust_details[c])
cust_details[c]= cust_details[c].apply(lambda x: m if ((x<a) or (x>b)) else x)
for c in cust_pt.columns:
if cust_pt[c].dtype == 'float64':
m = cust_pt[c].mean()
a, b = outlier_repair(cust_pt[c])
cust_pt[c] = cust_pt[c].apply(lambda x: m if ((x<a) or (x>b)) else x)
cust_pt.describe()

# In[70]:

import re
for c in cust_details.columns:
if cust_details[c].dtype == 'object':
cust_details[c] = cust_details[c].apply(lambda x: re.sub(' ','',x.lower()))
for c in cust_pt.columns:
if cust_pt[c].dtype == 'object':
cust_pt[c] = cust_pt[c].apply(lambda x: re.sub(' ','',x.lower()))
cust_details.head()

# In[71]:

for c in cust_details.columns:
if cust_details[c].dtype == 'object':
cust_details = pd.concat([cust_details,pd.get_dummies(cust_details[c])],axis=1)
# cust_details.drop(columns=[c],inplace=True)
for c in cust_pt.columns:
if cust_pt[c].dtype == 'object':
cust_pt = pd.concat([cust_pt,pd.get_dummies(cust_pt[c])],axis=1)
# cust_pt.drop(columns=[c],inplace=True)
cust_pt.head()

# In[72]:

cust_details.drop_duplicates(inplace=True)
cust_pt.drop_duplicates(inplace=True)

# # Q3

# In[73]:

master_table = pd.merge(cust_details,cust_pt,on='customer_id')
master_table.head()

# # Q4

# In[75]:

gender_wise_avg_premium = master_table.groupby(['gender'])['annual premium'].mean()
gender_wise_avg_premium

# In[79]:

age_wise_avg_premium = master_table.groupby(['age'])['annual premium'].mean()
age_wise_avg_premium

# In[80]:

vehicle_age_wise_avg_premium = master_table.groupby(['vehicle age'])['annual premium'].mean()
vehicle_age_wise_avg_premium

# # Q5

# In[83]:

master_table[['age','annual premium']].corr()

# In[84]:

correl = master_table[['age','annual premium']].corr()['age']['annual premium']
if correl < -0.5:
print("Strong negative correlation")
elif correl > 0.5:
print("Strong positive correlation")
else:
print("There is no relationship")

# In[ ]: