Project 2 - EDA on Vehicle Insurance Customer Data
all step complete
import pandas as pd
table1=pd.read_csv('customer_details.csv')
table1.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"]
table2=pd.read_csv('customer_policy_details.csv')
table2.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
print(table1)
print(table2)
table1.shape
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())
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())
## Outliers
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
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
### White spaces
table1["Gender"]=table1["Gender"].str.strip()
table1["vehicle age"]=table1["vehicle age"].str.strip()
table1["vehicle damage"]=table1["vehicle damage"].str.strip()
### case change
table1["Gender"]=table1["Gender"].str.lower()
table1["vehicle age"]=table1["vehicle age"].str.lower()
table1["vehicle damage"]=table1["vehicle damage"].str.lower()
### creating dummies
dummies_gender=pd.get_dummies(table1["Gender"])
dummies_vehicle_age=pd.get_dummies(table1["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(table1["vehicle damage"])
dummies_d_l_p=pd.get_dummies(table1["driving licence present"])
dummies_p_i=pd.get_dummies(table1["previously insured"])
dummies_response=pd.get_dummies(table2["response"])
### checking for any duplicates
print(table1.duplicated().sum())
print(table2.duplicated().sum())
# merge customer_id table1 table2
master_table=pd.merge(table1,table2,on='customer_id')
master_table
#Gender wise average annual premium
aap_gender_df=master_table.groupby(["Gender"])["annual premium (in Rs)"].mean()
aap_gender_df
# age wise average annual premium
aap_age_df=master_table.groupby(["age"])["annual premium (in Rs)"].mean()
aap_age_df
# Data is balanced between the two genders
fd=(master_table["Gender"].value_counts()[0]/master_table["Gender"].value_counts()[1],2)
fd
#vehicle age wise average annual premium
aap_v_age_df=master_table.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
aap_v_age_df
# STEP5: Is there any relation between Person Age and annual premium?
n = master_table["age"].corr(master_table["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!")