Project 2 - EDA on Vehicle Insurance Customer Data
import pandas as pd
import numpy as np
customer_details=pd.read_csv('customer_details.csv')
customer_details
df=pd.read_csv('customer_policy_details.csv')
df
# 1. Add the column names to both datasets:
# i. Column Name for customer details table:
customer_details.rename({'0':'Customer_Id','1':'Gender','2':'Age','3':'Driving Licence Present','4':'Region Code','5':'Previously Insured','6':'Vehicle Age','7':'Vehicle Damage'},axis=1,inplace=True)
customer_details
# ii. Column Name for customer_policy table:
df.rename({'0':'Customer_Id','1':'Annual Premium(Rs)','2':'Sales Channel Code','3':'Vintage','4':'Response'},axis=1,inplace=True)
df
2. Checking and Cleaning Data Quality:
i. Null values
Generate a summary of count of all the null values column wise
customer_details.isna().sum()
df.isna().sum()
# Drop Null values for customer_id because central tendencies for id’s is not feasible
customer_details=customer_details.dropna(subset=['Customer_Id'])
customer_details.isna().sum()
df=df.dropna(subset=['Customer_Id'])
df.isna().sum()
# Replace all null values for numeric columns by mean.
df.fillna(df.mean(),inplace=True)
df.isna().sum()
customer_details.fillna(customer_details.mean(),inplace=True)
customer_details.isna().sum()
customer_details['Age'].fillna(int(customer_details['Age'].mean()), inplace=True)
customer_details.isna().sum()
df.loc['Customer_Id']=df['Customer_Id'].fillna(df['Customer_Id'].mode()[0])
df.loc['Annual Premium(Rs)']=df['Annual Premium(Rs)'].fillna(df['Annual Premium(Rs)'].mode()[0])
df.loc['Sales Channel Code']=df['Sales Channel Code'].fillna(df['Sales Channel Code'].mode()[0])
df.loc['Vintage']=df['Vintage'].fillna(df['Vintage'].mode()[0])
df.loc['Response']=df['Response'].fillna(df['Response'].mode()[0])
df.isnull().sum()
# ii. Outliers
# Generate a summary of count of all the outliers column wise
q1=df.quantile(0.25)
q3=df.quantile(0.75)
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((df<l)|(df>u)).sum()
customer_details.describe()
Q1=customer_details.describe().loc["25%","Age"]
Q3=customer_details.describe().loc["75%","Age"]
IQR=Q3-Q1
print('Customer details table')
print('Outliers present in age column are :',customer_details.loc[customer_details["Age"]<(Q1-IQR*1.5),"Age"].count())
print('Outliers present in age column are :',customer_details.loc[customer_details["Age"]>(Q3+IQR*1.5),"Age"].count())
Q1=customer_details.describe().loc["25%","Region Code"]
Q3=customer_details.describe().loc["75%","Region Code"]
IQR=Q3-Q1
print('Customer details table')
print('Outliers present in region code column are :',customer_details.loc[customer_details["Region Code"]<(Q1-IQR*1.5),"Region Code"].count())
print('Outliers present in region code column are :',customer_details.loc[customer_details["Region Code"]>(Q3+IQR*1.5),"Region Code"].count())
df.describe()
Q1=df.describe().loc["25%","Annual Premium(Rs)"]
Q3=df.describe().loc["75%","Annual Premium(Rs)"]
IQR=Q3-Q1
print('Customer-policy-details table')
print('Outliers present in annual premium (in Rs) column are :',df.loc[df["Annual Premium(Rs)"]<(Q1-IQR*1.5),"Annual Premium(Rs)"].count())
print('Outliers present in annual premium (in Rs) column are :',df.loc[df["Annual Premium(Rs)"]>(Q3+IQR*1.5),"Annual Premium(Rs)"].count())
Q1=df.describe().loc["25%","Vintage"]
Q3=df.describe().loc["75%","Vintage"]
IQR=Q3-Q1
print('Customer-policy-details table')
print('Outliers present in vintage column are :',df.loc[df["Vintage"]<(Q1-IQR*1.5),"Vintage"].count())
print('Outliers present in vintage column are :',df.loc[df["Vintage"]>(Q3+IQR*1.5),"Vintage"].count())
# Replace all outlier values for numeric columns by mean.
# We are replacing the outliers of annual premium (in Rs) column with mean values in customer-policy-details table
mean=df["Annual Premium(Rs)"].mean()
print('Mean of annual premium (in Rs) column values: ',mean)
def replace_outlier(df1):
q1=df1.quantile(0.25)
q3=df1.quantile(0.75)
iqr=q3-q1
upper=df1[~(df1>(q3+1.5*iqr))].max()
lower=df1[~(df1<(q1-1.5*iqr))].min()
df1=np.where(df1>upper,df1.mean(),np.where(df1<lower,df1.mean(),df1))
return df1
df["Annual Premium(Rs)"] = replace_outlier(df["Annual Premium(Rs)"])
# iii. White spaces
# Remove white spaces
customer_details["Vehicle Age"]=customer_details["Vehicle Age"].str.strip()
# iv. case correction(lower or upper, any one)
customer_details["Gender"]=customer_details["Gender"].str.lower()
customer_details["Vehicle Age"]=customer_details["Vehicle Age"].str.lower()
customer_details["Vehicle Damage"]=customer_details["Vehicle Damage"].str.lower()
# v. Convert nominal data (categorical) into dummies
# for future modeling use if required
dummies_gender=pd.get_dummies(customer_details["Gender"])
dummies_vehicle_age=pd.get_dummies(customer_details["Vehicle Age"])
dummies_vehicle_damage=pd.get_dummies(customer_details["Vehicle Damage"])
dummies_dlp=pd.get_dummies(customer_details["Driving Licence Present"])
dummies_pre=pd.get_dummies(customer_details["Previously Insured"])
# vi. Drop Duplicates (duplicated rows)
print(customer_details.duplicated().sum())
print(df.duplicated().sum())
customer_details.drop_duplicates(inplace=True) #dropping duplicates
df.drop_duplicates(inplace=True) #dropping duplicate
# 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.
# merging
mas_data=pd.merge(customer_details,df,on='Customer_Id')
mas_data
# 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
gwiseanpl=mas_data.groupby(["Gender"])["Annual Premium(Rs)"].mean()
gwiseanpl
# ii. Age wise average annual premium
agwise=mas_data.groupby(["Age"])["Annual Premium(Rs)"].mean()
agwise
# iii. Is your data balanced between the genders?
mas_data.groupby('Gender').count()
mas_data
# iv. Vehicle age wise average annual premium.
vwiseavp=mas_data.groupby(["Vehicle Age"])["Annual Premium(Rs)"].mean()
vwiseavp
vwiseavp.plot() # line graph
# 5. Is there any relation between Person Age and annual premium?
n=mas_data['Age'].corr(mas_data["Annual Premium(Rs)"])
n
mas_data[['Age','Annual Premium(Rs)']].corr()