Project 2 - EDA on Vehicle Insurance Customer Data

Q1

import pandas as pd

df1=pd.read_csv('customer_details.csv',header=None)

df1

df1.columns=['customer_id','Gender','Age','Driving licence','Region_Code','Previously_Insured','Vehicale_Age','Vehicale_Damage']

df1

df2=pd.read_csv('customer_policy_details.csv',header=None)

df2.columns=['customer_id','Annual_Premium','Sales_Channel_code','Vintage','Response']

df2

Q2

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

df1_null=df1.isnull().sum()

df2_null=df2.isnull().sum()

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

df1.dropna(subset='customer_id',inplace=True)

df2.dropna(subset='customer_id',inplace=True)

## Replace all null values for numeric columns by mean. 

df1['Age'].fillna(df1['Age'].mean(),inplace=True)

df2['Annual_Premium'].fillna(df2['Annual_Premium'].mean(),inplace=True)

df2['Vintage'].fillna(df2['Vintage'].mean(),inplace=True)

# Replace all null values for Categorical value by mode.

df1['Driving licence'].fillna(df1['Driving licence'].mode()[0],inplace=True)

df1['Region_Code'].fillna(df1['Region_Code'].mode()[0],inplace=True)

df1['Previously_Insured'].fillna(df1['Previously_Insured'].mode()[0],inplace=True)

df1['Vehicale_Age'].fillna(df1['Vehicale_Age'].mode()[0],inplace=True)

df1['Vehicale_Damage'].fillna(df1['Vehicale_Damage'].mode()[0],inplace=True)

df1['Gender'].fillna(df1['Gender'].mode()[0],inplace=True)

df2['Sales_Channel_code'].fillna(df2['Sales_Channel_code'].mode()[0],inplace=True)

df2['Response'].fillna(df2['Response'].mode()[0],inplace=True)

# summary of outliers in column wise

Q1 = df1.quantile(0.25)

Q3 = df1.quantile(0.75)

IQR = Q3 - Q1

((df1 < (Q1 - 1.5 * IQR)) | (df1 > (Q3 + 1.5 * IQR))).sum()

col= ((df2[['Annual_Premium']] < (Q1 - 1.5 * IQR)) | (df2[['Annual_Premium']]> (Q3 + 1.5 * IQR)))

col

 # Replace outliers 

ind = col[col['Annual_Premium']].index

ind

df2.loc[ind,'Annual_Premium'] = df2['Annual_Premium'].mean()

((df2 < (Q1 - 1.5 * IQR)) | (df2 > (Q3 + 1.5 * IQR))).sum()

df2.head()

df1.head()

# White spaces

df1['Vehicale_Age'] = df1['Vehicale_Age'].str.replace(' ','')

df1.head()

#iv. case correction(lower or upper, any one) 

df1['Gender']=df1['Gender'].str.lower()

df1

# v. Convert nominal data (categorical) into dummies 

dummie_df1=pd.get_dummies(df1,columns=['Gender','Vehicale_Age'])

dummie_df1

# vi. Drop Duplicates (duplicated rows)

df1.drop_duplicates(subset=None,keep='first',inplace=True)

df2.drop_duplicates(subset=None,keep='first',inplace=True)

Q3

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

master_df

Q4

master_df[['Gender','Annual_Premium']].groupby(['Gender']).mean()

master_df[['Age','Annual_Premium']].groupby(['Age']).mean()

master_df['Gender'].value_counts() # For text oriented things we have to use the value_cont function

master_df[['Vehicale_Age','Annual_Premium']].groupby(['Vehicale_Age']).mean()

Q5

# corelation:

import numpy as np

np.corrcoef(master_df['Age'], master_df['Annual_Premium'])

#There is no relation between person age and annual premium as 0.0506541 almost equals to 0