Project 2 - EDA on Vehicle Insurance Customer Data
import warnings
warnings.filterwarnings('ignore')
customer_id,
Gender,
age,
drivinglicencepresent,
regioncode,
previouslyinsured,
vehicleage
and vehicledamage, in respective order.
annual premium (in Rs),
sales channel code,
vintage and response.
import pandas as pd
customer_detail_df=pd.read_csv(".\customer_details.csv")
customer_detail_df
customer_detail_df.columns=["customer_id","Gender","age","driving licence present","region code","previously insured","vehicle age","vehicle damage"]
customer_detail_df
customer_policy_detail_df=pd.read_csv(".\customer_policy_details.csv")
customer_policy_detail_df
customer_policy_detail_df.columns=["customer_id","annual premium (in Rs)","sales channel code","vintage","response"]
customer_policy_detail_df
Generate a summary of count of all the null values column wise
Drop Null values for customer_id because central tendencies for id is not feasible.
Replace all null values for numeric columns by mean.
Replace all null values for Categorical value by mode.
Generate a summary of count of all the outliers column wise
Replace all outlier values for numeric columns by mean.
Remove white spaces
for future modeling use if required
customer_detail_df.shape
print("Null value in 'customer_id' : ",customer_detail_df["customer_id"].isnull().sum())
customer_detail_df=customer_detail_df.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",customer_detail_df["customer_id"].isnull().sum())
print("Null value in 'Gender' : ",customer_detail_df["Gender"].isnull().sum())
customer_detail_df["Gender"]=customer_detail_df["Gender"].fillna(customer_detail_df["Gender"].mode()[0])
print("Null value in 'Gender' after replacing : ",customer_detail_df["Gender"].isnull().sum())
print("Null value in 'age' : ",customer_detail_df["age"].isnull().sum())
customer_detail_df["age"]=customer_detail_df["age"].fillna(customer_detail_df["age"].mean())
print("Null value in 'age' after replacing : ",customer_detail_df["age"].isnull().sum())
print("Null value in 'driving licence present' : ",customer_detail_df["driving licence present"].isnull().sum())
customer_detail_df["driving licence present"]=customer_detail_df["driving licence present"].fillna(customer_detail_df["driving licence present"].mode()[0])
print("Null value in 'driving licence present' after replacing : ",customer_detail_df["driving licence present"].isnull().sum())
print("Null value in 'region code' : ",customer_detail_df["region code"].isnull().sum())
customer_detail_df["region code"]=customer_detail_df["region code"].fillna(customer_detail_df["region code"].mode()[0])
print("Null value in 'region code' after replacing : ",customer_detail_df["region code"].isnull().sum())
print("Null value in 'previously insured' : ",customer_detail_df["previously insured"].isnull().sum())
customer_detail_df["previously insured"]=customer_detail_df["previously insured"].fillna(customer_detail_df["previously insured"].mode()[0])
print("Null value in 'previously insured' after replacing : ",customer_detail_df["previously insured"].isnull().sum())
print("Null value in 'vehicle age' : ",customer_detail_df["vehicle age"].isnull().sum())
customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].fillna(customer_detail_df["vehicle age"].mode()[0])
print("Null value in 'vehicle age' after replacing : ",customer_detail_df["vehicle age"].isnull().sum())
print("Null value in 'vehicle damage' : ",customer_detail_df["vehicle damage"].isnull().sum())
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].fillna(customer_detail_df["vehicle damage"].mode()[0])
print("Null value in 'vehicle damage' after replacing : ",customer_detail_df["vehicle damage"].isnull().sum())
print("Null value in 'customer_id' : ",customer_policy_detail_df["customer_id"].isnull().sum())
customer_policy_detail_df=customer_policy_detail_df.dropna(subset=["customer_id"])
print("Null value in 'customer_id' after droping : ",customer_policy_detail_df["customer_id"].isnull().sum())
print("Null value in 'annual premium (in Rs)' : ",customer_policy_detail_df["annual premium (in Rs)"].isnull().sum())
customer_policy_detail_df["annual premium (in Rs)"]=customer_policy_detail_df["annual premium (in Rs)"].fillna(customer_policy_detail_df["annual premium (in Rs)"].mean())
print("Null value in 'annual premium (in Rs)' after replacing : ",customer_policy_detail_df["annual premium (in Rs)"].isnull().sum())
print("Null value in 'sales channel code' : ",customer_policy_detail_df["sales channel code"].isnull().sum())
customer_policy_detail_df["sales channel code"]=customer_policy_detail_df["sales channel code"].fillna(customer_policy_detail_df["sales channel code"].mode()[0])
print("Null value in 'sales channel code' after replacing : ",customer_policy_detail_df["sales channel code"].isnull().sum())
print("Null value in 'vintage' : ",customer_policy_detail_df["vintage"].isnull().sum())
customer_policy_detail_df["vintage"]=customer_policy_detail_df["vintage"].fillna(customer_policy_detail_df["vintage"].mean())
print("Null value in 'vintage' after replacing : ",customer_policy_detail_df["vintage"].isnull().sum())
print("Null value in 'response' : ",customer_policy_detail_df["response"].isnull().sum())
customer_policy_detail_df["response"]=customer_policy_detail_df["response"].fillna(customer_policy_detail_df["response"].mode()[0])
print("Null value in 'response' after replacing : ",customer_policy_detail_df["response"].isnull().sum())
customer_detail_df.describe()
customer_policy_detail_df.describe()
Q1=customer_detail_df.describe().loc["25%","age"]
Q3=customer_detail_df.describe().loc["75%","age"]
IQR=Q3-Q1
print(customer_detail_df.loc[customer_detail_df["age"]<(Q1-IQR*1.5),"age"].count())
print(customer_detail_df.loc[customer_detail_df["age"]>(Q3+IQR*1.5),"age"].count())
Q1=customer_policy_detail_df.describe().loc["25%","annual premium (in Rs)"]
Q3=customer_policy_detail_df.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=customer_policy_detail_df["annual premium (in Rs)"].mean()
print(customer_policy_detail_df.loc[customer_policy_detail_df["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Q1=customer_policy_detail_df.describe().loc["25%","vintage"]
Q3=customer_policy_detail_df.describe().loc["75%","vintage"]
IQR=Q3-Q1
print(customer_policy_detail_df.loc[customer_policy_detail_df["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print(customer_policy_detail_df.loc[customer_policy_detail_df["vintage"]>(Q3+IQR*1.5),"vintage"].count())
customer_detail_df["Gender"]=customer_detail_df["Gender"].str.strip()
customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.strip()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.strip()
customer_detail_df["Gender"]=customer_detail_df["Gender"].str.lower()
customer_detail_df["vehicle age"]=customer_detail_df["vehicle age"].str.lower()
customer_detail_df["vehicle damage"]=customer_detail_df["vehicle damage"].str.lower()
dummies_gender=pd.get_dummies(customer_detail_df["Gender"])
dummies_vehicle_age=pd.get_dummies(customer_detail_df["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(customer_detail_df["vehicle damage"])
dummies_d_l_p=pd.get_dummies(customer_detail_df["driving licence present"])
dummies_p_i=pd.get_dummies(customer_detail_df["previously insured"])
dummies_response=pd.get_dummies(customer_policy_detail_df["response"])
print(customer_detail_df.duplicated().sum())
print(customer_policy_detail_df.duplicated().sum())
(Hint: use pd.merge() function)
master_df=pd.merge(customer_detail_df,customer_policy_detail_df,on='customer_id')
master_df
(Hint: Data is balanced if number of counts in each group is approximately same)
aap_gender_df=master_df.groupby(["Gender"])["annual premium (in Rs)"].mean()
aap_gender_df
aap_gender_df
aap_age_df=master_df.groupby(["age"])["annual premium (in Rs)"].mean()
aap_age_df
aap_v_age_df=master_df.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
aap_v_age_df
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.
Correlation_coefficient=master_df["age"].corr(master_df["annual premium (in Rs)"])
print(Correlation_coefficient)