Project 2 - EDA on Vehicle Insurance Customer Data
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
1. Add the column names to both datasets:
DataFrame3=pd.read_csv('.\pandas\customer_policy_details.csv')
ataFrame3
output:
| 0 | 1 | 2 | 3 | 4 | |
|---|---|---|---|---|---|
| 0 | 1.0 | 40454.0 | 26.0 | 217.0 | 1.0 |
| 1 | 2.0 | 33536.0 | 26.0 | 183.0 | 0.0 |
| 2 | 3.0 | 38294.0 | 26.0 | 27.0 | 1.0 |
| 3 | 4.0 | 28619.0 | 152.0 | 203.0 | 0.0 |
| 4 | 5.0 | 27496.0 | 152.0 | 39.0 | 0.0 |
| ... | ... | ... | ... | ... | ... |
| 381104 | 381105.0 | 30170.0 | 26.0 | 88.0 | 0.0 |
| 381105 | 381106.0 | 40016.0 | 152.0 | 131.0 | 0.0 |
| 381106 | 381107.0 | 35118.0 | 160.0 | 161.0 | 0.0 |
| 381107 | 381108.0 | 44617.0 | 124.0 | 74.0 | 0.0 |
| 381108 | 381109.0 | 41777.0 | 26.0 | 237.0 | 0.0 |
381109 rows × 5 columns
DataFrame4=pd.read_csv('.\pandas\customer_details.csv')
DataFrame4
output:
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1.0 | Male | 44.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 1 | 2.0 | Male | 76.0 | 1.0 | 3.0 | 0.0 | 1-2 Year | No |
| 2 | 3.0 | Male | 47.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 3 | 4.0 | Male | 21.0 | 1.0 | 11.0 | 1.0 | < 1> | No |
| 4 | 5.0 | Female | 29.0 | 1.0 | 41.0 | 1.0 | < 1> | No |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 381104 | 381105.0 | Male | 74.0 | 1.0 | 26.0 | 1.0 | 1-2 Year | No |
| 381105 | 381106.0 | Male | 30.0 | 1.0 | 37.0 | 1.0 | < 1> | No |
| 381106 | 381107.0 | Male | 21.0 | 1.0 | 30.0 | 1.0 | < 1> | No |
| 381107 | 381108.0 | Female | 68.0 | 1.0 | 14.0 | 0.0 | > 2 Years | Yes |
| 381108 | 381109.0 | Male | 46.0 | 1.0 | 29.0 | 0.0 | 1-2 Year | No |
381109 rows × 8 columns
# 1,i. Column Name for customer details table:
col1=["customer_Id","Gender","Age","Driving licence Active","regin code","Previously Insured","Vehical Age","Vehical Damage"]
DataFrame4.columns = col1
DataFrame4.head()
output:
| customer_Id | Gender | Age | Driving licence Active | regin code | Previously Insured | Vehical Age | Vehical Damage | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1.0 | Male | 44.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 1 | 2.0 | Male | 76.0 | 1.0 | 3.0 | 0.0 | 1-2 Year | No |
# 1,ii. Column Name for customer_policy table:
col2 = ["customer_Id","Anaual Salary","Sale channel code","Vintage","Response"]
DataFrame3.columns=col2
DataFrame3.head()
output:
| customer_Id | Anaual Salary | Sale channel code | Vintage | Response | |
|---|---|---|---|---|---|
| 0 | 1.0 | 40454.0 | 26.0 | 217.0 | 1.0 |
| 1 | 2.0 | 33536.0 | 26.0 | 183.0 | 0.0 |
2. Checking and Cleaning Data Quality:
# 2,i. Null values
#Generate a summary of count of all the null values column wise
df1_n = DataFrame4.isnull()
for i in df1_n.columns.values.tolist():
print(i)
print(df1_n[i].value_counts())
print("")
output:
customer_Id False 380723 Name: customer_Id, dtype: int64 Gender False 380355 True 368 Name: Gender, dtype: int64 Age False 380723 Name: Age, dtype: int64 Driving licence Active False 380723 Name: Driving licence Active, dtype: int64 regin code False 380723 Name: regin code, dtype: int64 Previously Insured False 380723 Name: Previously Insured, dtype: int64 Vehical Age False 380342 True 381 Name: Vehical Age, dtype: int64 Vehical Damage False 380317 True 406 Name: Vehical Damage, dtype: int64
df1_n = DataFrame3.isnull()
for i in df1_n.columns.values.tolist():
print(i)
print(df1_n[i].value_counts())
print("")
output:
customer_Id
False 380722 True 387 Name: customer_Id, dtype: int64 Anaual Salary False 381109 Name: Anaual Salary, dtype: int64 Sale channel code False 381109 Name: Sale channel code, dtype: int64 Vintage False 381109 Name: Vintage, dtype: int64 Response False 381109 Name: Response, dtype: int64
# Generate a summary of count of all the null values column wise DataFrame4.dropna(subset=['customer_Id'],axis = 0,inplace = True) DataFrame4.reset_index(drop = True,inplace=True)
DataFrame4['Age'].fillna(DataFrame4['Age'].mean(),inplace=True) ##Replacing the NaN value of region code by its mean value DataFrame4['regin code'].fillna(DataFrame4['regin code'].mean(),inplace=True) DataFrame4.head()
output:
| customer_Id | Gender | Age | Driving licence Active | regin code | Previously Insured | Vehical Age | Vehical Damage | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1.0 | Male | 44.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 1 | 2.0 | Male | 76.0 | 1.0 | 3.0 | 0.0 | 1-2 Year |
No |
#--------------------------Replacing all Null Values--------------- #Replacing the NaN value of age by its mean value DataFrame3['Anaual Salary'].fillna(DataFrame3['Anaual Salary'].mean(),inplace=True) ##Replacing the NaN value of sale channel code by its mean value DataFrame3['Sale channel code'].fillna(DataFrame3['Sale channel code'].mean(),inplace=True) ##Replacing the NaN value of vintage code by its mean value DataFrame3['Vintage'].fillna(DataFrame3['Vintage'].mean(),inplace=True) DataFrame3.head()
output:
| customer_Id | Anaual Salary | Sale channel code | Vintage | Response | |
|---|---|---|---|---|---|
| 0 | 1.0 | 40454.0 | 26.0 | 217.0 | 1.0 |
| 1 | 2.0 | 33536.0 | 26.0 | 183.0 | 0.0 |
| 2 | 3.0 | 38294.0 | 26.0 | 27.0 | 1.0 |
| 3 | 4.0 | 28619.0 | 152.0 | 203.0 | 0.0 |
#--------------------------Replacing Categorical Null Values by Mode--------------- #Replacing the NaN value of Driving licence Active by its mode value DataFrame4['Driving licence Active'].fillna(DataFrame4['Driving licence Active'].mode()[0],inplace=True) ##Replacing the NaN value of Previously Insured by its mode value DataFrame4['Previously Insured'].fillna(DataFrame4['Previously Insured'].mode()[0],inplace=True) DataFrame4.head()
output:
| customer_Id | Gender | Age | Driving licence Active | regin code | Previously Insured | Vehical Age | Vehical Damage | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1.0 | Male | 44.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 1 | 2.0 | Male | 76.0 | 1.0 | 3.0 | 0.0 | 1-2 Year | No |
| 2 | 3.0 | Male | 47.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
#2]Outliers #----------------------------Visualisation of Outliers------------- def plot_boxplot(DataFrame,ft): DataFrame.boxplot(column=[ft]) plt.grid(False) plt.show plot_boxplot(DataFrame4,'Age')
#---------------------------------------------------IQR Method--------------------------------------------- #this function finds outliers if present by IQR method def finding_out(df): q1 = df.quantile(0.25) q3=df.quantile(0.75) iqr=q3-q1 outlier = df[((df<(q1-1.5*iqr)))|(df>(q3+1.5*iqr))] return outlier outliers = finding_out(DataFrame4['regin code']) print("Number of outliers:",len(outliers)) print("Max Number of outliers:",outliers.max()) print("Minimum outlier value:",outliers.min())
output:
Number of outliers: 0 Max Number of outliers: nan Minimum outlier value: nan
#-----------------------The summary of the Outliers------------------------------------------- print("For Annual Salary") outliers = finding_out(DataFrame3['Anaual Salary']) print("Number of outliers:",len(outliers)) print("Max Number of outliers:",outliers.max()) print("Minimum outlier value:",outliers.min()) print('-**-'*16) print("For sales channel code") outliers = finding_out(DataFrame3['Sale channel code']) print("Number of outliers:",len(outliers)) print("Max Number of outliers:",outliers.max()) print("Minimum outlier value:",outliers.min()) print('-**-'*16) print("For Vintage") outliers = finding_out(DataFrame3['Vintage']) print("Number of outliers:",len(outliers)) print("Max Number of outliers:",outliers.max()) print("Minimum outlier value:",outliers.min()) print('-**-'*16)
output:
For Annual Salary Number of outliers: 10341 Max Number of outliers: 540165.0 Minimum outlier value: 61858.0 -**--**--**--**--**--**--**--**--**--**--**--**--**--**--**--**- For sales channel code Number of outliers: 0 Max Number of outliers: nan Minimum outlier value: nan -**--**--**--**--**--**--**--**--**--**--**--**--**--**--**--**- For Vintage Number of outliers: 0 Max Number of outliers: nan Minimum outlier value: nan -**--**--**--**--**--**--**--**--**--**--**--**--**--**--**--**-
#---------------------------------------Replacing the outliers with mean values_________________________________ def replace(df): q1 = df.quantile(0.25) q3=df.quantile(0.75) iqr=q3-q1 upper = df[~(df>(q3+1.5*iqr))].max() lower = df[~(df>(q3+1.5*iqr))].min() return df replace(DataFrame3['Anaual Salary'])
output:
0 40454.0
1 33536.0
2 38294.0
3 28619.0
4 27496.0
...
381104 30170.0
381105 40016.0
381106 35118.0
381107 44617.0
381108 41777.0
Name: Anaual Salary, Length: 381109, dtype: float64
#--------------------------------------Remove White Spaces---------------------------------------------
DataFrame4.apply(lambda x:x.str.strip() if x.dtype == 'object'else x)
output:
| customer_Id | Gender | Age | Driving licence Active | regin code | Previously Insured | Vehical Age | Vehical Damage | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1.0 | Male | 44.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 1 | 2.0 | Male | 76.0 | 1.0 | 3.0 | 0.0 | 1-2 Year | No |
| 2 | 3.0 | Male | 47.0 | 1.0 | 28.0 | 0.0 | > 2 Years | Yes |
| 3 | 4.0 | Male | 21.0 | 1.0 | 11.0 | 1.0 | < 1> | No |
| 4 | 5.0 | Female | 29.0 | 1.0 | 41.0 | 1.0 | < 1> | No |
DataFrame3.apply(lambda x:x.str.strip() if x.dtype == 'object'else x)
output:
| customer_Id | Anaual Salary | Sale channel code | Vintage | Response | |
|---|---|---|---|---|---|
| 0 | 1.0 | 40454.0 | 26.0 | 217.0 | 1.0 |
| 1 | 2.0 | 33536.0 | 26.0 | 183.0 | 0.0 |
| 2 | 3.0 | 38294.0 | 26.0 | 27.0 | 1.0 |
| 3 | 4.0 | 28619.0 | 152.0 | 203.0 | 0.0 |
| 4 | 5.0 | 27496.0 | 152.0 | 39.0 | 0.0 |
#--------------------------------------Case Correctio------------------------------------------ DataFrame4.apply(lambda x:x.str.upper() if x.dtype == 'object'else x)
output:
| customer_Id | Gender | Age | Driving licence Active | regin code | Previously Insured | Vehical Age | Vehical Damage | |
|---|---|---|---|---|---|---|---|---|
| 0 | 1.0 | MALE | 44.0 | 1.0 | 28.0 | 0.0 | > 2 YEARS | YES |
| 1 | 2.0 | MALE | 76.0 | 1.0 | 3.0 | 0.0 | 1-2 YEAR | NO |
| 2 | 3.0 | MALE | 47.0 | 1.0 | 28.0 | 0.0 | > 2 YEARS | YES |
| 3 | 4.0 | MALE | 21.0 | 1.0 | 11.0 | 1.0 | < 1> | NO |
| 4 | 5.0 | FEMALE | 29.0 | 1.0 | 41.0 | 1.0 | < 1> | NO |
#--------------------------------------Case Correctio------------------------------------------ DataFrame3.apply(lambda x:x.str.upper() if x.dtype == 'object'else x)
output:
| customer_Id | Anaual Salary | Sale channel code | Vintage | Response | |
|---|---|---|---|---|---|
| 0 | 1.0 | 40454.0 | 26.0 | 217.0 | 1.0 |
| 1 | 2.0 | 33536.0 | 26.0 | 183.0 | 0.0 |
| 2 | 3.0 | 38294.0 | 26.0 | 27.0 | 1.0 |
| 3 | 4.0 | 28619.0 | 152.0 | 203.0 | 0.0 |
| 4 | 5.0 | 27496.0 | 152.0 | 39.0 | 0.0 |
#------------------------------------convert nominal data into dummy variables--------------------------------------- dummy_variable_1 = pd.get_dummies(DataFrame4['Vehical Damage']) dummy_variable_1.rename(columns={'No':'vehicale-damage-No','Yes':'vehicle-damage-Yes'},inplace = True) dummy_variable_1.head()
output:
| vehicale-damage-No | vehicle-damage-Yes | |
|---|---|---|
| 0 | 0 | 1 |
| 1 | 1 | 0 |
| 2 | 0 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 0 |
dummies_gender=pd.get_dummies(DataFrame4["Gender"]) dummies_vehicle_age=pd.get_dummies(DataFrame4["Vehical Age"]) dummies_vehicle_damage=pd.get_dummies(DataFrame4["Vehical Damage"]) dummies_dlp=pd.get_dummies(DataFrame4["Driving licence Active"]) dummies_pre=pd.get_dummies(DataFrame4["Previously Insured"])
print(DataFrame4.duplicated().sum())
output:
0
print(DataFrame3.duplicated().sum())
output:
0