Project 2 - EDA on Vehicle Insurance Customer Data

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

# In[1]:


import pandas as pd
import numpy as np


# ###  import data and adding column names 

# In[2]:


customer_details = pd.read_csv('customer_details.csv')
customer_details.columns = ['customer_id', 
           'Gender',
           'age', 
           'driving licence present',
           'region code', 
           'previously insured', 
           'vehicle age' ,
           'vehicle damage']
#customer_details.index.astype({0:np.int64})
#customer_details.drop(columns=['index'],inplace=True)
customer_details.info()

#pd.DataFrame()


# In[3]:


customer_details.head()


# In[4]:


customer_policy_details = pd.read_csv('customer_policy_details.csv')
customer_policy_details.columns = ['customer_id', 
           'annual premium (in Rs)', 
           'sales channel code', 
           'vintage','response']
customer_policy_details.info()


# In[5]:


customer_policy_details.head()


# ### checking and cleaning data quality

# In[6]:


# droping all records with customer_id is null
customer_details.drop(axis=0,index=customer_details[customer_details.customer_id.isnull()==True].index,inplace=True)

#validating again 
customer_details[customer_details.customer_id.isnull()==True]


# In[7]:


# droping all records with customer_id is null
customer_policy_details.drop(axis=0,index=customer_policy_details[customer_policy_details.customer_id.isnull()==True].index,inplace=True)

#validating again 
customer_policy_details[customer_policy_details.customer_id.isnull()==True]


# ### customer_details

# In[8]:


def nullcount(df,colname):
    return df[df[colname].isnull()==True].shape[0]
for col in customer_details.columns:
    print('{} null count : {:}'.format(col,nullcount(customer_details,col)))


# In[9]:


# gender
print(customer_details.Gender.mode()[0])
customer_details['Gender'].fillna(customer_details['Gender'].mode()[0],inplace=True)

# age 
print(customer_details.age.mean())
customer_details['age'].fillna(customer_details['age'].mean(),inplace=True)

# driving licence present
print(customer_details['driving licence present'].mode()[0])
customer_details['driving licence present'].fillna(customer_details['driving licence present'].mode()[0],inplace=True)

# region code
print(customer_details['region code'].mode()[0])
customer_details['region code'].fillna(customer_details['region code'].mode()[0],inplace=True)

# previously insured
print(customer_details['previously insured'].mode()[0])
customer_details['previously insured'].fillna(customer_details['previously insured'].mode()[0],inplace=True)

#vehicle agevehicle damage
print(customer_details['vehicle age'].mode()[0])
customer_details['vehicle age'].fillna(customer_details['vehicle age'].mode()[0],inplace=True)

#vehicle damage
print(customer_details['vehicle damage'].mode()[0])
customer_details['vehicle damage'].fillna(customer_details['vehicle damage'].mode()[0],inplace=True)


# In[10]:


for col in customer_details.columns:
    print('{} null count : {:}'.format(col,nullcount(customer_details,col)))


# ### customer_policy_details

# In[11]:


for col in customer_policy_details.columns:
    print('{} null count : {:}'.format(col,nullcount(customer_policy_details,col)))


# In[12]:


# annual permium
print(customer_policy_details['annual premium (in Rs)'].mean())
customer_policy_details['annual premium (in Rs)'].fillna(customer_policy_details['annual premium (in Rs)'].mean(),inplace=True)

# sales channel code
print(customer_policy_details['sales channel code'].mode()[0])
customer_policy_details['sales channel code'].fillna(customer_policy_details['sales channel code'].mode()[0],inplace=True)

# vintage
print(customer_policy_details['vintage'].mean())
customer_policy_details['vintage'].fillna(customer_policy_details['vintage'].mean(),inplace=True)

#response
print(customer_policy_details['response'].mode()[0])
customer_policy_details['response'].fillna(customer_policy_details['response'].mode()[0],inplace=True)


# In[13]:


for col in customer_policy_details.columns:
    print('{} null count : {:}'.format(col,nullcount(customer_policy_details,col)))


# ##### outliers will be present only for numeric values 

# In[14]:


#customer_details has age is the contineous value
def fillOutlierMean(df,col):
    q1 = np.percentile(df[col], 25)
    q3 = np.percentile(df[col], 75)
    iqr = (q3-q1)*1.5
    mean = df[col].mean()
    lbound =  mean-iqr
    rbound = mean+iqr
    ind = df[(df[col]<lbound) | (df[col]>rbound)].index
    df.loc[ind,col]=mean
    print(df[(df[col]<lbound) | (df[col]>rbound)].size)
    
#replacing outliers with mean 
fillOutlierMean(customer_details,'age')
fillOutlierMean(customer_policy_details,'vintage')


# In[15]:


customer_details.info()


# In[16]:


#checing for spaces only for vehicle age we have spaces , still we cand od much on that 
print(customer_details.Gender.value_counts())
print(customer_details['vehicle age'].value_counts())
print(customer_details['vehicle damage'].value_counts())


# In[17]:


customer_details.head()


# In[18]:


#changes some categorical datatype to int from float 
customer_details = customer_details.astype({'customer_id':np.int64,
                        'age':np.int64,
                         'driving licence present':np.int64,
                         'region code':np.int64,
                         'previously insured':np.int64},
                        True)
customer_details.head()


# In[19]:


customer_policy_details.head()


# In[20]:


customer_policy_details = customer_policy_details.astype({'customer_id':np.int64,'sales channel code':np.int64,'response':np.int64},True)
customer_policy_details.head()


# In[21]:


# creating dummy dataframe with extra col from categorical columns 
dummy_cd = pd.get_dummies(customer_details)
dummy_cd.head()


# In[22]:


dummy_cpd = pd.get_dummies(customer_policy_details)
dummy_cpd.head()


# In[23]:


dummy_cd.drop_duplicates(inplace = True)
dummy_cd.shape


# In[24]:


dummy_cpd.drop_duplicates(inplace = True)
dummy_cpd.shape


# In[25]:


# merging (creating master tabel)
Master_df = pd.merge(customer_details,customer_policy_details,how='inner',on='customer_id')
Master_df.drop_duplicates()
Master_df.head()


# In[26]:


Master_df.info()


# In[27]:


Master_df.describe(include='all')


# In[28]:


# Gender wise average annual premium
Master_df.groupby('Gender')['annual premium (in Rs)'].mean()


# In[29]:


# age wise average anual premium
Master_df.groupby('age')['annual premium (in Rs)'].mean()


# In[30]:


# Is your data balanced between the genders
res = Master_df.Gender.value_counts()
print('There is difference of ',res['Male']-res['Female'])
res


# In[31]:


#Vehicle age wise average annual premium.
Master_df.groupby('vehicle age')['annual premium (in Rs)'].mean()


# In[32]:


Master_df[['age','annual premium (in Rs)']].corr()


# In[33]:


# as we see corr is -0.5<0.06<0.5
# so there is no strong releations between age and annual premium