Project 2 - EDA on Vehicle Insurance Customer Data

'''A company has customer data that contains 8 columns of customer details and another table having name customer_policy data contains the policy details of the customer. 

​

The company intends to offer some discount in premium for certain customers. To do that they ask their Data scientist team to get some information. Hence, following tasks DS team decided to perform:

​

1. Add the column names to both datasets:

​

i. Column Name for customer details table:

​

           customer_id, 

​

           Gender,

​

           age, 

​

           driving licence present,

​

           region code, 

​

           previously insured, 

​

           vehicle age 

​

           and vehicle damage, in respective order. 

​

ii. Column Name for customer_policy table:

​

           customer_id, 

​

           annual premium (in Rs), 

​

           sales channel code, 

​

           vintage and response. '''

import pandas as pd

customer_policy = pd.read_csv(".customer_policy_details.csv", header = None)

customer_policy

# Add the column names to both datasets:

# i. Column Name for customer details table:

# customer_id, Gender,age,driving licence present,region code, previously insured, vehicle age and vehicle damage, in respective order. 

​

customer_details.columns = ["Customer_id","Gender","Age","Driving Licence Present","Region Code","Previously Insured","Vehicle Age","Vehicle Damage"]

customer_details

# ii. Column Name for customer_policy table:

# customer_id, annual premium (in Rs), sales channel code, vintage and response. 

​

customer_policy.columns = ["Customer_id","Annual Premium (in Rs)","Sales Channel Code","Vintage","Response"]

customer_policy

# 2. Checking and Cleaning Data Quality:

​

# i. Null values

​

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

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

# Replace all null values for numeric columns by mean. 

# Replace all null values for Categorical value by mode.

​

# customer details

customer_details.isna().sum()

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

customer_details.dropna(subset=['Customer_id'], axis=0, inplace = True)

customer_details.reset_index(drop = True, inplace = True)

customer_details

customer_details.isna().sum()

customer_policy.isna().sum()

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

customer_policy.dropna(subset=['Customer_id'], axis=0, inplace = True)

customer_policy.reset_index(drop = True, inplace = True)

customer_policy

# Replace all null values for numeric columns by mean.

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

customer_details['Region Code'].fillna(customer_details['Region Code'].mean(), inplace=True)

customer_policy['Annual Premium (in Rs)'].fillna(customer_policy['Annual Premium (in Rs)'].mean(), inplace=True)

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

# Replace all null values for Categorical value by mode.

customer_details['Driving Licence Present'].fillna(customer_details['Driving Licence Present'].mode()[0], inplace=True)

customer_details['Previously Insured'].fillna(customer_details['Previously Insured'].mode()[0], inplace=True)

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

# customer_policy 

​

customer_policy

customer_policy.isna().sum()

# ii. Outliers

​

# Generate a summary of count of all the outliers column wise

# Replace all outlier values for numeric columns by mean. 

# (Hint1: for outlier treatment use IQR method as follows:

# For example: for a column X calculate Q1 = 25th percentile and Q3 = 75th percentile then IQR = Q3 – Q1 ) 

# then to check outlier, anything lower than a Q1 – 1.5IQR or greater than Q3 + 1.5 IQR would be an outlier

​

# Hint2: For getting percentile value, explore pd.describe() function)

​

customer_details.describe()[['Age','Region Code']]

# vizualize using box plot

import matplotlib.pyplot as plt

​

def boxPlot(df, col):

    df.boxplot(column=[col])

    plt.grid(False)

    plt.show()

boxPlot(customer_details, 'Age')

boxPlot(customer_details, 'Region Code')

def find_outliers_IQR(df):

    q1 = df.quantile(0.25)

    q3 = df.quantile(0.75)

    IQR = q3 - q1

    outliers = df[((df<(q1-1.5*IQR)) | (df>(q3+1.5*IQR)))]

    return outliers

outliers = find_outliers_IQR(customer_details["Age"])

print("Number of outliers:",str(len(outliers)))

print("Maximum of outliers:",str(outliers.max()))

print("Minimum of outliers:",str(outliers.min()))

outliers

outliers = find_outliers_IQR(customer_details["Region Code"])

print("Number of outliers:",str(len(outliers)))

print("Maximum of outliers:",str(outliers.max()))

print("Minimum of outliers:",str(outliers.min()))

outliers

# customer_policy

​

boxPlot(customer_policy, 'Annual Premium (in Rs)')

boxPlot(customer_policy, 'Sales Channel Code')

boxPlot(customer_policy, 'Vintage')

outliers = find_outliers_IQR(customer_policy["Annual Premium (in Rs)"])

print("Number of outliers:",str(len(outliers)))

print("Maximum of outliers:",str(outliers.max()))

print("Minimum of outliers:",str(outliers.min()))

outliers

outliers = find_outliers_IQR(customer_policy["Sales Channel Code"])

print("Number of outliers:",str(len(outliers)))

print("Maximum of outliers:",str(outliers.max()))

print("Minimum of outliers:",str(outliers.min()))

outliers

outliers = find_outliers_IQR(customer_policy["Vintage"])

print("Number of outliers:",str(len(outliers)))

print("Maximum of outliers:",str(outliers.max()))

print("Minimum of outliers:",str(outliers.min()))

outliers

# Replace all outlier values for numeric columns by mean. 

import numpy as np

def replace_outliers_IQR(df):

    q1=df.quantile(0.25)

    q3=df.quantile(0.75)

    IQR=q3-q1

    upper = df[~(df>(q3+1.5*IQR))].max()

    lower = df[~(df<(q1-1.5*IQR))].min()

    df = np.where(df > upper,df.mean(),np.where(df < lower,df.mean(),df))

    return df

customer_policy['Annual Premium (in Rs)'] = replace_outliers_IQR(customer_policy['Annual Premium (in Rs)'])

customer_policy.describe()['Annual Premium (in Rs)']

customer_policy

# iii. White spaces

# Remove white spaces

​

customer_details.apply(lambda x : x.str.strip() if x.dtype == 'object' else x)

customer_policy.apply(lambda x : x.str.strip() if x.dtype == 'object' else x)

# case correction(lower or upper, any one) 

customer_details.apply(lambda x : x.str.upper() if x.dtype == 'object' else x)

# Convert nominal data (categorical) into dummies 

# for future modeling use if required

​

dummy_variable = pd.get_dummies(customer_details['Vehicle Damage'])

dummy_variable.rename(columns={'No':'Vehicle Damage - No','Yes':'Vehicle Damage - Yes'}, inplace = True)

dummy_variable.head(5)

customer_details = pd.concat([customer_details, dummy_variable], axis = 1)

customer_details.head(5)

customer_details.drop("Vehicle Damage", axis = 1, inplace =True)

customer_details.head(5)

# Drop Duplicates (duplicated rows)

​

customer_details.drop_duplicates(inplace = True)

customer_details

customer_policy.drop_duplicates(inplace = True)

customer_policy

# Create a Master table for future use. Join the customer table and customer_policy table to get a master table using customer_id in both tables.

​

# (Hint: use pd.merge() function)

​

master_table = pd.merge(customer_details, customer_policy, on = 'Customer_id')

# 4. Company needs some important information from the master table to make decisions for future growth.

# They needs following information:

​

#i. Gender wise average annual premium

#ii. Age wise average annual premium

#iii. Is your data balanced between the genders?

#(Hint: Data is balanced if number of counts in each group is approximately same)

#iv. Vehicle age wise average annual premium.

​

#i. Gender wise average annual premium

master_table.groupby('Gender')['Annual Premium (in Rs)'].mean()

#ii. Age wise average annual premium

master_table.groupby('Age')['Annual Premium (in Rs)'].mean()

#iii. Is your data balanced between the genders?

#(Hint: Data is balanced if number of counts in each group is approximately same)

master_table.groupby('Gender').count()

#iv. Vehicle age wise average annual premium.

​

master_table.groupby('Vehicle Age')['Annual Premium (in Rs)'].mean()

# 5. Is there any relation between Person Age and annual premium?

# Hint: use correlation function (Correlation describes the relationship between two variables). 

# Correlation coefficient < -0.5           - Strong negative relationship

# Correlation coefficient > 0.5            -  Strong positive relationship

# -0.5 < Correlation coefficient < 0.5   - There is no relationship. 

master_table[['Age','Annual Premium (in Rs)']].corr()