Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd

'''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.
'''

table1=pd.read_csv(".\customer_details.csv")
table1.columns=["customer_id","Gender","age","driving licence present","region code","previously insured",
"vehicle age","vehicle damage"]
table1

'''ii. Column Name for customer_policy table:

customer_id,

annual premium (in Rs),

sales channel code,

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

# 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.'''

print("Null value in 'customer_id' : ",table1["customer_id"].isnull().sum())
table1=table1.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",table1["customer_id"].isnull().sum(),'\n')

print("Null value in 'Gender' : ",table1["Gender"].isnull().sum())
table1["Gender"]=table1["Gender"].fillna(table1["Gender"].mode()[0])
print("Null value in 'Gender' after replacing : ",table1["Gender"].isnull().sum(),'\n')

print("Null value in 'age' : ",table1["age"].isnull().sum())
table1["age"]=table1["age"].fillna(table1["age"].mean())
print("Null value in 'age' after replacing : ",table1["age"].isnull().sum(),'\n')

print("Null value in 'driving licence present' : ",table1["driving licence present"].isnull().sum())
table1["driving licence present"]=table1["driving licence present"].fillna(table1["driving licence present"].mode()[0])
print("Null value in 'driving licence present' after replacing : ",table1["driving licence present"].isnull().sum(),'\n')

print("Null value in 'region code' : ",table1["region code"].isnull().sum())
table1["region code"]=table1["region code"].fillna(table1["region code"].mode()[0])
print("Null value in 'region code' after replacing : ",table1["region code"].isnull().sum(),'\n')

print("Null value in 'previously insured' : ",table1["previously insured"].isnull().sum())
table1["previously insured"]=table1["previously insured"].fillna(table1["previously insured"].mode()[0])
print("Null value in 'previously insured' after replacing : ",table1["previously insured"].isnull().sum(),'\n')

print("Null value in 'vehicle age' : ",table1["vehicle age"].isnull().sum())
table1["vehicle age"]=table1["vehicle age"].fillna(table1["vehicle age"].mode()[0])
print("Null value in 'vehicle age' after replacing : ",table1["vehicle age"].isnull().sum(),'\n')

print("Null value in 'vehicle damage' : ",table1["vehicle damage"].isnull().sum())
table1["vehicle damage"]=table1["vehicle damage"].fillna(table1["vehicle damage"].mode()[0])
print("Null value in 'vehicle damage' after replacing : ",table1["vehicle damage"].isnull().sum(),'\n')

print("Null value in 'customer_id' : ",table2["customer_id"].isnull().sum())
table2=table2.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",table2["customer_id"].isnull().sum(),'\n')

print("Null value in 'annual premium (in Rs)' : ",table2["annual premium (in Rs)"].isnull().sum())
table2["annual premium (in Rs)"]=table2["annual premium (in Rs)"].fillna(table2["annual premium (in Rs)"].mean())
print("Null value in 'annual premium (in Rs)' after replacing : ",table2["annual premium (in Rs)"].isnull().sum(),'\n')

print("Null value in 'sales channel code' : ",table2["sales channel code"].isnull().sum())
table2["sales channel code"]=table2["sales channel code"].fillna(table2["sales channel code"].mode()[0])
print("Null value in 'sales channel code' after replacing : ",table2["sales channel code"].isnull().sum(),'\n')

print("Null value in 'vintage' : ",table2["vintage"].isnull().sum())
table2["vintage"]=table2["vintage"].fillna(table2["vintage"].mean())
print("Null value in 'vintage' after replacing : ",table2["vintage"].isnull().sum(),'\n')

print("Null value in 'response' : ",table2["response"].isnull().sum())
table2["response"]=table2["response"].fillna(table2["response"].mode()[0])
print("Null value in 'response' after replacing : ",table2["response"].isnull().sum(),'\n')

'''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)'''

table1.describe()

Q1=table1.describe().loc["25%","age"]
Q3=table1.describe().loc["75%","age"]
IQR=Q3-Q1
print('age outliers')
print('min :',table1.loc[table1["age"]<(Q1-IQR*1.5),"age"].count())
print('max :',table1.loc[table1["age"]>(Q3+IQR*1.5),"age"].count(),'\n')

table2.describe()

Q1=table2.describe().loc["25%","annual premium (in Rs)"]
Q3=table2.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print('annual premium ouyliers ')
print('min :',table2.loc[table2["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print('max :',table2.loc[table2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count(),)

# removing outliers

table2.loc[table2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=table2["annual premium (in Rs)"].mean()
print('after removing outliers :',table2.loc[table2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count(),'\n')

Q1=table2.describe().loc["25%","vintage"]
Q3=table2.describe().loc["75%","vintage"]
IQR=Q3-Q1
print('vintage outliers')
print('min :',table2.loc[table2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print('max :',table2.loc[table2["vintage"]>(Q3+IQR*1.5),"vintage"].count(),'\n')

'''iii. White spaces

Remove white spaces'''

table1["Gender"]=table1["Gender"].str.strip()
table1["vehicle age"]=table1["vehicle age"].str.strip()
table1["vehicle damage"]=table1["vehicle damage"].str.strip()

'''iv. case correction(lower or upper, any one)
'''
table1["Gender"]=table1["Gender"].str.lower()
table1["vehicle age"]=table1["vehicle age"].str.lower()
table1["vehicle damage"]=table1["vehicle damage"].str.lower()

'''v. Convert nominal data (categorical) into dummies

for future modeling use if required
'''

gender_d=pd.get_dummies(table1["Gender"])
vehicle_age_d=pd.get_dummies(table1["vehicle age"])
driving_licence_present_d=pd.get_dummies(table1["driving licence present"])
vehicle_damage_d=pd.get_dummies(table1["vehicle damage"])
previously_insured_d=pd.get_dummies(table1["previously insured"])
response_d=pd.get_dummies(table2["response"])

'''vi. Drop Duplicates (duplicated rows)'''

table1.drop_duplicates(inplace=True)
table2.drop_duplicates(inplace=True)

'''3. 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(table1,table2,on='customer_id')
master_table

'''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'''

gender_v_app=master_table.groupby(["Gender"])["annual premium (in Rs)"].mean()
print('Gender vs average annual premium :',gender_v_app,'\n')

'''
ii. Age wise average annual premium
'''
age_v_app=master_table.groupby(["age"])["annual premium (in Rs)"].mean()
print('Age vs average annual premium :',age_v_app,'\n')

'''iii. Is your data balanced between the genders?

(Hint: Data is balanced if number of counts in each group is approximately same)'''
a=master_table['Gender'].value_counts()[0]/master_table['Gender'].value_counts()[1]
print(round(a,2))
print('the data is almost balanced between the genders','\n')

'''iv. Vehicle age wise average annual premium.

'''
age_v_app=master_table.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
print('Vehicle age vs average annual premium :',age_v_app,'\n')

'''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.
'''

n=master_table["age"].corr(master_table["annual premium (in Rs)"])
if n< -0.5:
print('the correlation between person age and annual premium is Strong negative relationship')
elif n>0.5:
print('the correlation between person age and annual premium is Strong positive relationship')
else:
print('there is no relation between person age and annual premium')