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
import numpy as np
import matplotlib.pyplot as plt
# Loading the data files
df1 = pd.read_csv("customer_details.csv")
df2 = pd.read_csv("customer_policy_details.csv")
headers = ['customer_id','Gender','age','driving licence present','region code','previously insured','vehicle age','vehicle damage']
df1.columns=headers
df1.head()
df2.columns=['customer_id','annual premium (in Rs)','sales channel code','vintage','responce']
df2.head()
2. Checking and Cleaning Data Quality:
i. Null values
Generate a summary of count of all the null values column wise
df1_nullvalues = df1.isnull()
for i in df1_nullvalues.columns.values.tolist():
print(i)
print(df1_nullvalues[i].value_counts())
print("")
Droppinf NULL values for customer_id
df1.dropna(subset=['customer_id'], axis=0,inplace=True)
df1.reset_index(drop = True, inplace = True)
df1
df2.dropna(subset=['customer_id'], axis=0,inplace=True) # dropping the rows containing null values on customer id
df2.reset_index(drop = True, inplace = True)
#Replace all null values for numeric columns by mean.
df1['age'].fillna(df1['age'].mean(),inplace = True)
df1['region code'].fillna(df1['region code'].mean(),inplace = True)
df1
df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean(),inplace = True)
df2['sales channel code'].fillna(df2['sales channel code'].mean(),inplace = True)
df2['vintage'].fillna(df2['vintage'].mean(),inplace = True)
df2
# Replace all null values for Categorical value by mode.
df1['driving licence present'].fillna (df1['driving licence present'].mode()[0], inplace=True)
df1['previously insured'].fillna (df1['previously insured'].mode()[0], inplace=True)
df1
df2['responce'].fillna(df2['responce'].mode()[0], inplace=True)
df2
# Outliers
Generate a summary of count of all the outliers column wise
df1.describe()
df2.describe()
Q1=df1.describe().loc["25%","age"]
Q3=df1.describe().loc["75%","age"]
IQR=Q3-Q1
print(df1.loc[df1["age"]<(Q1-IQR*1.5),"age"].count())
print(df1.loc[df1["age"]>(Q3+IQR*1.5),"age"].count())
Q1=df2.describe().loc["25%","annual premium (in Rs)"]
Q3=df2.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print(df2.loc[df2["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print(df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=df2["annual premium (in Rs)"].mean()
print(df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Q1=df2.describe().loc["25%","vintage"]
Q3=df2.describe().loc["75%","vintage"]
IQR=Q3-Q1
print(df2.loc[df2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print(df2.loc[df2["vintage"]>(Q3+IQR*1.5),"vintage"].count())
# Replace all outlier values for numeric columns by mean.
def replace_outlier(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
df2['annual premium (in Rs)'] = replace_outlier(df2['annual premium (in Rs)'])
df2
White spaces
Remove white spaces
df1.apply(lambda x: x.str.strip() if x.dtype=='object' else x)
df2.apply(lambda x: x.str.strip() if x.dtype=='object' else x)
case correction(lower or upper, any one)
df2.apply(lambda x: x.str.upper() if x.dtype=='object' else x)
df1.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_val = pd.get_dummies(df1["vehicle damage"])
dummy_val.rename(columns={'No':'vehicle-damage-No', 'Yes':'vehicle-damage-Yes'}, inplace=True)
dummy_val.head()
df1 = pd.concat([df1, dummy_val], axis=1)
df1.drop("vehicle damage", axis = 1, inplace=True)
df1
Drop Duplicates (duplicated rows)
df1.drop_duplicates(inplace=True)
df2.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(df1,df2,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
aap_gender_df=master_table.groupby(["Gender"])["annual premium (in Rs)"].mean()
aap_gender_df.plot.bar()
Age wise average annual premium
aap_age_df=master_table.groupby(["age"])["annual premium (in Rs)"].mean()
aap_age_df.plot.bar()
Vehicle age wise average annual premium.
aap_vehicle_df=master_table.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
aap_vehicle_df.plot.bar()
# Is your data balanced between the genders?
master_table.groupby('Gender').count()
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.
value=master_table['age'].corr(master_table['annual premium (in Rs)'])
st=""
if value <-0.5:
st="Strong negative relationship"
elif value>0.5:
st=" Strong positive relationship"
else:
st="There is no relationship."
print("the relation ship between age and annual premium is",st)