EDA on Vehicle Insurance Customer Data
A company has customer data that contains 8 columns of customer details and another table having name customer_policy data contains the policy details of the customer.
The company intends to offer some discount in premium for certain customers. To do that they ask their Data scientist team to get some information. Hence, following tasks DS team decided to perform:
import numpy as np
import pandas as pd
from matplotlib import pyplot as plt
import seaborn as sns
customerDetail = pd.read_csv('customer_details.csv')
customerPolDetail = pd.read_csv('customer_policy_details.csv')
1. Add the column names to both datasets:
i. Column Name for customer details table:
customerDetail.columns
Index(['0', '1', '2', '3', '4', '5', '6', '7'], dtype='object')
customerDetail.rename(columns={'0':'customer_id','1':'Gender','2':'age','3':'driving licence present','4':'region code','5':'previously insured','6':'vechile age','7':'vechile damage'})
customer_id Gender age driving licence present region code previously insured vechile age vechile 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 Year No
4 5.0 Female 29.0 1.0 41.0 1.0 < 1 Year 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 Year No
381106 381107.0 Male 21.0 1.0 30.0 1.0 < 1 Year 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
customerDetail = customerDetail.rename(columns={'0':'customer_id','1':'Gender','2':'age','3':'driving licence present','4':'region code','5':'previously insured','6':'vechile age','7':'vechile damage'})
customerDetail.columns
Index(['customer_id', 'Gender', 'age', 'driving licence present',
'region code', 'previously insured', 'vechile age', 'vechile damage'],
dtype='object')
ii. Column Name for customer_policy table:
customerPolDetail.columns
Index(['0', '1', '2', '3', '4'], dtype='object')
customerPolDetail.rename(columns={'0':'customer_id','1':'annual premium (in Rs)','2':'sales channel code','3':'vintage','4':'response'})
customer_id annual premium (in Rs) sales 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
... ... ... ... ... ...
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
customerPolDetail = customerPolDetail.rename(columns={'0':'customer_id','1':'annual premium (in Rs)','2':'sales channel code','3':'vintage','4':'response'})
customerPolDetail.columns
Index(['customer_id', 'annual premium (in Rs)', 'sales channel code',
'vintage', 'response'],
dtype='object')
2. Checking and Cleaning Data Quality:
i. Null values
customerDetail.isnull().sum()
customer_id 386
Gender 368
age 368
driving licence present 393
region code 392
previously insured 381
vechile age 381
vechile damage 407
dtype: int64
customerPolDetail.isnull().sum()
customer_id 387
annual premium (in Rs) 346
sales channel code 400
vintage 388
response 361
dtype: int64
customerDetail['customer_id'].isna().sum()
386
customerDetail['customer_id'].count()
380723
customerPolDetail['customer_id'].isna().sum()
387
customerDetail['customer_id'].count()
380723
customerPolDetail = customerPolDetail.dropna(subset=['customer_id'])
customerPolDetail['customer_id'].mean()
190547.49166320832
customerPolDetail['annual premium (in Rs)'].fillna((customerPolDetail['annual premium (in Rs)'].mean()),inplace= True)
customerPolDetail['annual premium (in Rs)'].mean()
30563.99977390893
customerPolDetail['sales channel code'].fillna((customerPolDetail['sales channel code'].mean()),inplace= True)
customerPolDetail['sales channel code'].mean()
112.03668733336546
customerPolDetail['vintage'].fillna((customerPolDetail['vintage'].mean()),inplace= True)
customerPolDetail['vintage'].mean()
154.34719220474696
customerPolDetail['response'].fillna((customerPolDetail['response'].mean()),inplace= True)
customerPolDetail['response'].mean()
0.12252570584260679
customerPolDetail.isna().sum()
customer_id 0
annual premium (in Rs) 0
sales channel code 0
vintage 0
response 0
dtype: int64
customerDetail = customerDetail.dropna(subset=['customer_id'])
customerDetail = customerDetail.dropna(subset=['Gender'])
customerDetail = customerDetail.dropna(subset=['age'])
customerDetail = customerDetail.dropna(subset=['driving licence present'])
customerDetail = customerDetail.dropna(subset=['region code'])
customerDetail = customerDetail.dropna(subset=['previously insured'])
customerDetail = customerDetail.dropna(subset=['vechile age'])
customerDetail = customerDetail.dropna(subset=['vechile damage'])
## Replace all null values for Categorical value by mode.
customerDetail['customer_id'].fillna((customerDetail['customer_id'].mode()),inplace=True)
customerDetail['Gender'].fillna((customerDetail['Gender'].mode().mode().iloc[0]),inplace=True)
customerDetail['age'].fillna((customerDetail['age'].mode().iloc[0]), inplace=True)
customerDetail['driving licence present'].fillna((customerDetail['driving licence present'].mode().iloc[0]), inplace=True)
customerDetail['region code'].fillna((customerDetail['region code'].mode().iloc[0]),inplace=True)
customerDetail['previously insured'].fillna((customerDetail['previously insured'].mode().iloc[0]),inplace=True)
customerDetail['vechile age'].fillna((customerDetail['vechile age'].mode().iloc[0]),inplace=True)
customerDetail['vechile damage'].fillna((customerDetail['vechile damage'].mode().iloc[0]),inplace=True)
customerDetail.isnull().sum()
customer_id 0
Gender 0
age 0
driving licence present 0
region code 0
previously insured 0
vechile age 0
vechile damage 0
dtype: int64
customerPolDetail['customer_id'].fillna((customerPolDetail['customer_id'].mode()),inplace=True)
customerPolDetail['annual premium (in Rs)'].fillna((customerPolDetail['annual premium (in Rs)'].mode().iloc[0]),inplace=True)
customerPolDetail['sales channel code'].fillna((customerPolDetail['sales channel code'].mode().iloc[0]),inplace=True)
customerPolDetail['vintage'].fillna((customerPolDetail['vintage'].mode().iloc[0]),inplace=True)
customerPolDetail['response'].fillna((customerPolDetail['response'].mode().iloc[0]),inplace=True)
customerPolDetail.isnull().sum()
customer_id 0
annual premium (in Rs) 0
sales channel code 0
vintage 0
response 0
dtype: int64
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)
customerDetail.describe()
customer_id age driving licence present region code previously insured
count 378049.000000 378049.000000 378049.000000 378049.000000 378049.000000
mean 190573.212919 38.821208 0.997873 26.392764 0.458195
std 110017.560502 15.510030 0.046067 13.230727 0.498250
min 1.000000 20.000000 0.000000 0.000000 0.000000
25% 95304.000000 25.000000 1.000000 15.000000 0.000000
50% 190574.000000 36.000000 1.000000 28.000000 0.000000
75% 285866.000000 49.000000 1.000000 35.000000 1.000000
max 381109.000000 85.000000 1.000000 52.000000 1.000000
customerPolDetail.describe()
customer_id annual premium (in Rs) sales channel code vintage response
count 380722.000000 380722.000000 380722.000000 380722.000000 380722.000000
mean 190547.491663 30563.999774 112.036687 154.347192 0.122526
std 110013.824148 17190.147550 54.177046 83.628096 0.327737
min 1.000000 2630.000000 1.000000 10.000000 0.000000
25% 95276.250000 24416.000000 29.000000 82.000000 0.000000
50% 190536.500000 31656.000000 131.000000 154.000000 0.000000
75% 285818.750000 39391.750000 152.000000 227.000000 0.000000
max 381109.000000 540165.000000 163.000000 299.000000 1.000000
3. 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)
customerDetail
customer_id Gender age driving licence present region code previously insured vechile age vechile 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 Year No
4 5.0 Female 29.0 1.0 41.0 1.0 < 1 Year 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 Year No
381106 381107.0 Male 21.0 1.0 30.0 1.0 < 1 Year 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
378049 rows × 8 columns
customerPolDetail
customer_id annual premium (in Rs) sales 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
... ... ... ... ... ...
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
380722 rows × 5 columns
master_table = pd.merge(customerDetail,customerPolDetail)
master_table
customer_id Gender age driving licence present region code previously insured vechile age vechile damage annual premium (in Rs) sales channel code vintage response
0 1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes 40454.0 26.0 217.0 1.0
1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 33536.0 26.0 183.0 0.0
2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 38294.0 26.0 27.0 1.0
3 4.0 Male 21.0 1.0 11.0 1.0 < 1 Year No 28619.0 152.0 203.0 0.0
4 5.0 Female 29.0 1.0 41.0 1.0 < 1 Year No 27496.0 152.0 39.0 0.0
... ... ... ... ... ... ... ... ... ... ... ... ...
377658 381105.0 Male 74.0 1.0 26.0 1.0 1-2 Year No 30170.0 26.0 88.0 0.0
377659 381106.0 Male 30.0 1.0 37.0 1.0 < 1 Year No 40016.0 152.0 131.0 0.0
377660 381107.0 Male 21.0 1.0 30.0 1.0 < 1 Year No 35118.0 160.0 161.0 0.0
377661 381108.0 Female 68.0 1.0 14.0 0.0 > 2 Years Yes 44617.0 124.0 74.0 0.0
377662 381109.0 Male 46.0 1.0 29.0 0.0 1-2 Year No 41777.0 26.0 237.0 0.0
377663 rows × 12 columns
master_table.set_index('customer_id')
Gender age driving licence present region code previously insured vechile age vechile damage annual premium (in Rs) sales channel code vintage response
customer_id
1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes 40454.0 26.0 217.0 1.0
2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No 33536.0 26.0 183.0 0.0
3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes 38294.0 26.0 27.0 1.0
4.0 Male 21.0 1.0 11.0 1.0 < 1 Year No 28619.0 152.0 203.0 0.0
5.0 Female 29.0 1.0 41.0 1.0 < 1 Year No 27496.0 152.0 39.0 0.0
... ... ... ... ... ... ... ... ... ... ... ...
381105.0 Male 74.0 1.0 26.0 1.0 1-2 Year No 30170.0 26.0 88.0 0.0
381106.0 Male 30.0 1.0 37.0 1.0 < 1 Year No 40016.0 152.0 131.0 0.0
381107.0 Male 21.0 1.0 30.0 1.0 < 1 Year No 35118.0 160.0 161.0 0.0
381108.0 Female 68.0 1.0 14.0 0.0 > 2 Years Yes 44617.0 124.0 74.0 0.0
381109.0 Male 46.0 1.0 29.0 0.0 1-2 Year No 41777.0 26.0 237.0 0.0
377663 rows × 11 columns
4. Company needs some important information from the master table to make decisions for future growth.They needs following information:
i. Gender wise average annual premium
ii. Age wise average annual premium
iii. Is your data balanced between the genders?
(Hint: Data is balanced if number of counts in each group is approximately same)
iv. Vehicle age wise average annual premium.
# i. Gender wise average annual premium
plt.figure(figsize = (14, 5))
sns.distplot(master_table.loc[(master_table['Gender'] == 1), 'annual premium (in Rs)'])
sns.distplot(master_table.loc[(master_table['Gender'] == 0), 'annual premium (in Rs)'])
plt.title('Gender wise average annual premium', fontsize = 14)
plt.show()
C:\Users\kajit\anaconda3\lib\site-packages\seaborn\distributions.py:2619: FutureWarning: `distplot` is a deprecated function and will be removed in a future version. Please adapt your code to use either `displot` (a figure-level function with similar flexibility) or `histplot` (an axes-level function for histograms).
warnings.warn(msg, FutureWarning)
C:\Users\kajit\anaconda3\lib\site-packages\seaborn\distributions.py:2657: RuntimeWarning: Mean of empty slice.
line, = ax.plot(a.mean(), 0)
C:\Users\kajit\anaconda3\lib\site-packages\numpy\core\_methods.py:189: RuntimeWarning: invalid value encountered in double_scalars
ret = ret.dtype.type(ret / rcount)
C:\Users\kajit\anaconda3\lib\site-packages\numpy\lib\histograms.py:906: RuntimeWarning: invalid value encountered in true_divide
return n/db/n.sum(), bin_edges
C:\Users\kajit\anaconda3\lib\site-packages\seaborn\distributions.py:2619: FutureWarning: `distplot` is a deprecated function and will be removed in a future version. Please adapt your code to use either `displot` (a figure-level function with similar flexibility) or `histplot` (an axes-level function for histograms).
warnings.warn(msg, FutureWarning)
C:\Users\kajit\anaconda3\lib\site-packages\seaborn\distributions.py:2657: RuntimeWarning: Mean of empty slice.
line, = ax.plot(a.mean(), 0)
C:\Users\kajit\anaconda3\lib\site-packages\numpy\core\_methods.py:189: RuntimeWarning: invalid value encountered in double_scalars
ret = ret.dtype.type(ret / rcount)
C:\Users\kajit\anaconda3\lib\site-packages\numpy\lib\histograms.py:906: RuntimeWarning: invalid value encountered in true_divide
return n/db/n.sum(), bin_edges
5. 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.
corrMatrix = master_table.corr()
sns.heatmap(corrMatrix, annot = True)
<AxesSubplot:>
plt.show()