Project 2 - EDA on Vehicle Insurance Customer Data
# EDA ON VEHICLE INSURANCE CUSTOMER DATA
import warnings
warnings.filterwarnings("ignore")
import pandas as pd
import matplotlib.pyplot as pyplot
table1=pd.read_csv("customer_details.csv")
table1
table2=pd.read_csv("customer_policy_details.csv")
table2
### STEP 1. Add the column names to both datasets:
#i. Column Name for customer details table:
table1.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"]
#ii. Column Name for customer_policy table:
table2.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
### STEP 2. Checking and Cleaning Data Quality:
#i. Null values
# Null values for customer_details
#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())
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())
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())
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())
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())
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())
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())
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())
# Null values for customer_policy_detail
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())
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())
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())
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())
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())
#ii. Outliers
#Generate a summary of count of all the outliers column wise
#Replace all outlier values for numeric columns by mean.
#table1
table1.describe()
table2.describe()
Q1=table1.describe().loc["25%","age"]
Q3=table1.describe().loc["75%","age"]
IQR=Q3-Q1
print(table1.loc[table1["age"]<(Q1-IQR*1.5),"age"].count())
print(table1.loc[table1["age"]>(Q3+IQR*1.5),"age"].count())
# there are no outliers in 'age' column
#table2
Q1=table2.describe().loc["25%","annual premium (in Rs)"]
Q3=table2.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print(table2.loc[table2["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print(table2.loc[table2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
table2.loc[table2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=table2["annual premium (in Rs)"].mean()
print(table2.loc[table2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Q1=table2.describe().loc["25%","vintage"]
Q3=table2.describe().loc["75%","vintage"]
IQR=Q3-Q1
print(table2.loc[table2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print(table2.loc[table2["vintage"]>(Q3+IQR*1.5),"vintage"].count())
#there are no outliers in 'vintage' column
#iii. White spaces
#Remove white spaces
#Remove Whitespaces for table1
table1.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
#Remove Whitespaces for table2
table2.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
#iv. case correction(lower or upper, any one)
#case correction for table1
table1.apply(lambda x: x.str.lower() if x.dtype == "object" else x)
#case correction for table2
table2.apply(lambda x: x.str.lower() if x.dtype == "object" else x)
#v. Convert nominal data (categorical) into dummies
#for future modeling use if required
#converting categorical data into dummy variables for table1
dummies_gender=pd.get_dummies(table1["Gender"])
dummies_d_l_p=pd.get_dummies(table1["driving licence present"])
dummies_rc=pd.get_dummies(table1["region code"])
dummies_p_i=pd.get_dummies(table1["previously insured"])
dummies_vehicle_age=pd.get_dummies(table1["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(table1["vehicle damage"])
# converting categorical data into dummy variables for table2
dummies_scc=pd.get_dummies(table2["sales channel code"])
dummies_vi=pd.get_dummies(table2["vintage"])
dummies_res=pd.get_dummies(table2["response"])
#vi. Drop Duplicates (duplicated rows)
### checking for any duplicates
# dropping duplicate rows for table1
table1.drop_duplicates(inplace=True)
table1
# dropping duplicate rows for table2
table2.drop_duplicates(inplace=True)
table2
print(table1.duplicated().sum())
print(table2.duplicated().sum())
### STEP 3. Create a Master table for future use.(customer table + customer_policy)
master_df=pd.merge(table1,table2,on='customer_id')
master_df
### STEP 4. getting information
# STEP4: getting information
#i Gender wise average annual premium
aap_gender_df=master_df.groupby(["Gender"])["annual premium (in Rs)"].mean()
print(aap_gender_df)
print("n")
print("nn")
print("Gender wise average annual premium:")
import matplotlib.pyplot as pyplot
aap_gender_df.plot()
pyplot.show()
#ii age wise average annual premium
aap_age_df=master_df.groupby(["age"])["annual premium (in Rs)"].mean()
print("Age wise average annual premium:")
#print(aap_age_df)
import matplotlib.pyplot as pyplot
aap_age_df.plot()
pyplot.show()
#iii Is your data balanced between the genders?
print(f"Male to Female ratio is {round(master_df['Gender'].value_counts()[0]/master_df['Gender'].value_counts()[1],2)}")
print(f"Generally, the standard is: nBalanced data ratio: {50/50}nSlightly balanced data ratio: {round(55/45,2)}-{60/40} nImbalanced data ratio: {80/20}-{90/10}")
print("Data is balanced between the two genders because the number of counts are approximately same")
print("n")
#data is balanced between the two genders because the averages are approximately equal
#iv vehicle_age wise average annual premium
aap_v_age_df=master_df.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
print("Vehicle_age wise average annual premium:")
#print(aap_v_age_df)
import matplotlib.pyplot as pyplot
aap_v_age_df.plot()
pyplot.show()
### STEP 5.Is there any relation between Person Age and annual premium?
# STEP5: Is there any relation between Person Age and annual premium?
print("Is there any relation between Person Age and annual premium?")
n =master_df['age'].corr(master_df['annual premium (in Rs)'])
if n<-0.5:
print("Strong negative relationship")
if n>0.5:
print("Strong positive relationship")
if n>-0.5 and n<0.5:
print("There is no relationship!")