Project 2 - EDA on Vehicle Insurance Customer Data

381109 rows × 5 columns

# Checking and Cleaning Data Quality:

# i. Null values

# Generate a summary of count of all the null values column wise

# Checking NULL value for customer_details Dataframe(df1)

print('Null values in customer_id : ',df1['customer_id'].isna().sum())
print('Null values in Gender : ',df1['Gender'].isna().sum())
print('Null values in Age : ',df1['age'].isna().sum())
print('Null values in Driving license present : ',df1['driving licence present'].isna().sum())
print('Null values in region code : ',df1['region code'].isna().sum())
print('Null values in previously insured : ',df1['previously insured'].isna().sum())
print('Null values in vehicle age : ',df1['vehicle age'].isna().sum())
print('Null values in vehicle damage : ',df1['vehicle damage'].isna().sum())

Null values in customer_id  :  386
Null values in Gender  :  368
Null values in Age  :  368
Null values in Driving license present  :  393
Null values in region code  :  392
Null values in previously insured  :  381
Null values in vehicle age  :  381
Null values in vehicle damage  :  407 

# Checking NULL value for customer_Policy_details Dataframe(df2)

print('Null values in customer_id : ',df2['customer_id'].isna().sum())
print('Null values in annual premium (in Rs) : ',df2['annual premium (in Rs)'].isna().sum())
print('Null values in sales channel code : ',df2['sales channel code'].isna().sum())
print('Null values in vintage : ',df2['vintage'].isna().sum())
print('Null values in response : ',df2['response'].isna().sum())

Null values in customer_id  :  387
Null values in annual premium (in Rs)  :  346
Null values in sales channel code  :  400
Null values in vintage :  388
Null values in response  :  361

 

# Drop Null values for customer_id because central tendencies for id’s is not feasible.

df1=df1.dropna(subset=['customer_id'])
df2=df2.dropna(subset=['customer_id'])

print('Null values in customer_id after droping : ',df1['customer_id'].isna().sum())
print('Null values in customer_id after droping : ',df2['customer_id'].isna().sum())

Null values in customer_id after droping :  0
Null values in customer_id after droping :  0

# Replace all null values for numeric columns by mean

df1['age']=df1['age'].fillna(df1['age'].mean)
print('Null values in Age after Replacing : ',df1['age'].isna().sum())

df2['annual premium (in Rs)']=df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean)
print('Null values in annual premium (in Rs) after Replacing : ',df2['annual premium (in Rs)'].isna().sum())

df2['vintage']=df2['vintage'].fillna(df2['vintage'].mean)
print('Null values in vintage after Replacing : ',df2['vintage'].isna().sum())

Null values in Age after Replacing :  0
Null values in annual premium (in Rs) after Replacing :  0
Null values in vintage after Replacing : 0

# Replace all null values for Categorical value by mode

df1['Gender']=df1['Gender'].fillna(df1['Gender'].mode()[0])
print('Null values in Gender after Replacing : ',df1['Gender'].isna().sum())

df1['driving licence present']=df1['driving licence present'].fillna(df1['driving licence present'].mode()[0])
print('Null values in driving licence present after Replacing : ',df1['driving licence present'].isna().sum())

df1['region code']=df1['region code'].fillna(df1['region code'].mode()[0])
print('Null values in region code after Replacing : ',df1['region code'].isna().sum())

df1['previously insured']=df1['previously insured'].fillna(df1['previously insured'].mode()[0])
print('Null values in previously insured after Replacing : ',df1['previously insured'].isna().sum())

df1['vehicle age']=df1['vehicle age'].fillna(df1['vehicle age'].mode()[0])
print('Null values in vehicle age after Replacing : ',df1['vehicle age'].isna().sum())

df1['vehicle damage']=df1['vehicle damage'].fillna(df1['vehicle damage'].mode()[0])
print('Null values in vehicle damage after Replacing : ',df1['vehicle damage'].isna().sum())

df2['sales channel code']=df2['sales channel code'].fillna(df2['sales channel code'].mode()[0])
print('Null values in sales channel code after Replacing : ',df2['sales channel code'].isna().sum())

df2['response']=df2['response'].fillna(df2['response'].mode()[0])
print('Null values in response after Replacing : ',df2['response'].isna().sum())

Null values in Gender after Replacing :  0
Null values in driving licence present after Replacing :  0
Null values in region code after Replacing :  0
Null values in previously insured after Replacing :  0
Null values in vehicle age after Replacing :  0
Null values in vehicle damage after Replacing :  0
Null values in sales channel code after Replacing :  0
Null values in response after Replacing :  0 


# ii. Outliers
# Generate a summary of count of all the outliers column wise
# Replace all outlier values for numeric columns by mean. 
# (Hint1: for outlier treatment use IQR method as follows:
# For example: for a column X calculate Q1 = 25th percentile and Q3 = 75th percentile then IQR = Q3 – Q1 ) then to check outlier, anything lower than a Q1 – 1.5IQR or greater than Q3 + 1.5 IQR would be an outlier
# Hint2: For getting percentile value, explore pd.describe() function)

df1.describe()

customer_id                95269.5
age                           25.0
driving licence present        1.0
region code                   15.0
previously insured             0.0
Name: 25%, dtype: float64

df1.describe().loc['75%']

customer_id                285822.5
age                            49.0
driving licence present         1.0
region code                    35.0
previously insured              1.0
Name: 75%, dtype: float64

IQR for age is :  24.0
Checking anything is Lower : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: age, Length: 380723, dtype: bool
Number of Outliers for age :  0
Checking anything is Greater : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: age, Length: 380723, dtype: bool
Number of Outliers for age :  0

IQR for region code is :  20.0
Checking anything is Lower : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: region code, Length: 380723, dtype: bool
Number of Outliers for region code :  0
Checking anything is Greater : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: region code, Length: 380723, dtype: bool
Number of Outliers for region code :  0

df2['annual premium (in Rs)'].describe()

count    380378.000000
mean      30563.999774
std       17197.918886
min        2630.000000
25%       24407.000000
50%       31667.000000
75%       39401.750000
max      540165.000000
Name: annual premium (in Rs), dtype: float64

df2['vintage'].describe()

count    380722.000000
mean        154.450788
std          83.690971
min          10.000000
25%          82.000000
50%         154.000000
75%         227.000000
max         299.000000
Name: vintage, dtype: float64

IQR for annual premium (in Rs) is :  14994.75
Checking anything is Lower : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: annual premium (in Rs), Length: 380722, dtype: bool
Number of Outliers for annual premium (in Rs) :  0
Checking anything is Greater : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: annual premium (in Rs), Length: 380722, dtype: bool
Number of Outliers for annual premium (in Rs) :  10291

IQR for vintage is :  145.0
Checking anything is Lower : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: vintage, Length: 380722, dtype: bool
Number of Outliers for vintage :  0
Checking anything is Greater : 
 0         False
1         False
2         False
3         False
4         False
          ...  
381104    False
381105    False
381106    False
381107    False
381108    False
Name: vintage, Length: 380722, dtype: bool
Number of Outliers for vintage :  0

df2['annual premium (in Rs)']=df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean)

# iii. White spaces

# Remove white spaces

df1["Gender"]=df1["Gender"].str.strip()
df1["vehicle age"]=df1["vehicle age"].str.strip()
df1["vehicle damage"]=df1["vehicle damage"].str.strip()

# or using Lambda

df1.apply(lambda x : x.str.strip() if x.dtype == 'object'else x)

# case correction(lower or upper, any one)

df1.apply(lambda x : x.str.upper() if x.dtype == 'object' else x)

380723 rows × 8 columns

# v. Convert nominal data (categorical) into dummies

# for future modeling use if required

dummies_gender=pd.get_dummies(df1["Gender"])
dummies_vehicle_age=pd.get_dummies(df1["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(df1["vehicle damage"])
dummies_driving_licence_present=pd.get_dummies(df1["driving licence present"])
dummies_previously_insured=pd.get_dummies(df1["previously insured"])
dummies_response=pd.get_dummies(df2["response"])

# Drop Duplicates (duplicated rows)

df1.drop_duplicates(inplace=True)
df2.drop_duplicates(inplace=True)

# checking for any duplicates

print(df1.duplicated().sum())
print(df2.duplicated().sum())

# Create a Master table for future use.
# Join the customer table and customer_policy table to get a master table using customer_id in both tables
# (Hint: use pd.merge() function)

master_table=pd.merge(df1,df2,on='customer_id')

master_table

380336 rows × 12 columns

# Company needs some important information from the master table to make decisions for future growth.
# They needs following information:

# Gender wise average annual premium

gender_wise_avg = master_table.groupby(['Gender'])['annual premium (in Rs)'].mean()

gender_wise_avg

Gender
Female    30491.959528
Male      30624.674414
Name: annual premium (in Rs), dtype: float64

# Age wise average annual premium

age_wise_avg=master_table.groupby(['age'])['annual premium (in Rs)'].mean()

age_wise_avg

age
20.0    26920.517153
21.0    30564.476130
22.0    30824.039423
23.0    30688.699065
24.0    31184.306152
            ...     
81.0    31201.571429
82.0    37705.379310
83.0    31012.727273
84.0    35440.818182
85.0    29792.363636
Name: annual premium (in Rs), Length: 66, dtype: float64

# Is your data balanced between the genders?
# (Hint: Data is balanced if number of counts in each group is approximately same)

count=master_table.groupby(['Gender']).count()
count

# Vehicle age wise average annual premium

vehical_age_wise_avg=master_table.groupby(['vehicle age'])['annual premium (in Rs)'].mean()

vehical_age_wise_avg

vehicle age
1-2 Year     30522.422917
< 1 Year     30115.361397
> 2 Years    35661.355606
Name: annual premium (in Rs), dtype: float64

# Is there any relation between Person Age and annual premium?

# 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_table["age"].corr(master_table["annual premium (in Rs)"])

print(Correlation_coefficient)

0.06777762525952817

-0.5 < Correlation coefficient < 0.5 - There is no relationship