Project 2 - EDA on Vehicle Insurance Customer Data
1.)
import pandas as pd
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
2.)
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())
Customer policy
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()
# case change
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()
# createdummi
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"])
Check duplicates
print(customer_detail_df.duplicated().sum())
print(customer_policy_detail_df.duplicated().sum())
3.)
master_df=pd.merge(customer_detail_df,customer_policy_detail_df,on='customer_id')
master_df
4.)
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
5.)
Correlation_coefficient=master_df["age"].corr(master_df["annual premium (in Rs)"])
print(Correlation_coefficient)