Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd

customer_detail=pd.read_csv("customer_details.csv")
customer_policy=pd.read_csv("customer_policy_details.csv")
customer_detail.columns=["customer_id","Gender","Age","Driving License Present","Region Code","Previously Insured","Vehicle Age",
"Vehicle Damage"]
customer_policy.columns=["customer_id","Annual Premium(in Rs)","Sales Channel Code","Vintage","Response"]
customer_detail

customer_policy

#Null Values
null_det=customer_detail.isnull().sum()
print(null_det)

null_pol=customer_policy.isnull().sum()
print(null_pol)

#Drop Null Values

#Customer Detail
customer_detail.dropna(subset = ['customer_id'],axis=0,inplace = True)
#reseting indexing some rows deleted
customer_detail.reset_index(drop = True,inplace = True)

#Customer Policy
customer_policy.dropna(subset = ['customer_id'],axis=0,inplace = True)
#reseting indexing some rows deleted
customer_policy.reset_index(drop = True,inplace = True)

#Replacing all Null Values by Mean

customer_detail.head()
customer_detail['Age'].fillna(customer_detail['Age'].mean(),inplace = True)
customer_detail['Region Code'].fillna(customer_detail['Region Code'].mean(),inplace = True)

customer_policy.head()
customer_policy['Annual Premium(in Rs)'].fillna(customer_policy['Annual Premium(in Rs)'].mean(),inplace = True)
customer_policy['Sales Channel Code'].fillna(customer_policy['Sales Channel Code'].mean(),inplace = True)
customer_policy['Vintage'].fillna(customer_policy['Vintage'].mean(),inplace = True)

#Replacing Null values by mode

customer_detail['Driving License Present'].fillna (customer_detail['Driving License Present'].mode()[0], inplace=True)
customer_detail['Previously Insured'].fillna (customer_detail['Previously Insured'].mode()[0], inplace=True)
customer_policy['Response'].fillna(customer_policy['Response'].mode()[0],inplace = True)

#Outliers

customer_detail.describe()[['Age','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_detail["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_detail["Region 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["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)']

# Remove white spaces
customer_detail.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_detail.apply(lambda x : x.str.upper() if x.dtype == 'object' else x)

# Convert nominal data (categorical) into dummies

dummy_variable = pd.get_dummies(customer_detail['Vehicle Damage'])
dummy_variable.rename(columns={'No':'Vehicle Damage - No','Yes':'Vehicle Damage - Yes'}, inplace = True)
dummy_variable.head(5)

customer_detail = pd.concat([customer_detail, dummy_variable], axis = 1)
customer_detail.head(5)

customer_detail.drop("Vehicle Damage", axis = 1, inplace =True)
customer_detail.head(5)

# Drop Duplicates (duplicated rows)

customer_detail.drop_duplicates(inplace = True)
customer_detail

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.

master_table = pd.merge(customer_detail, customer_policy, on = 'customer_id')
master_table.head(10)

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