BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

Project 2 - EDA on Vehicle Insurance Customer Data

AAkhil Thomas24 Aug 2022

Project 2 - 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:

  1. Add the column names to both datasets:

i. Column Name for customer details table:

       customer_id, 

       Gender,

       age, 

       driving licence present,

       region code, 

       previously insured, 

       vehicle age 

       and vehicle damage, in respective order.
In [1]:
import pandas as pd
​
import numpy as np
In [24]:
df=pd.read_csv('customer_details.csv',header=None)
In [25]:
df
Out[25]:
0 1 2 3 4 5 6 7
0 121.0 MALE NaN 1 345.0 0.0 3 YEARS NO
1 122.0 FEMALE 28.0 1 456.0 0.0 2 YEARS NO
2 123.0 MALE 43.0 1 765.0 1.0 4 YEARS NO
3 124.0 MALE 38.0 1 457.0 3.0 3 YEARS YES
4 125.0 MALE 34.0 2 234.0 0.0 3 YEARS NO
5 126.0 MALE 50.0 1 875.0 0.0 5 YEARS NO
6 127.0 MALE 0.0 0 232.0 2.0 4 YEARS NaN
7 NaN NaN 0.0 0 NaN 0.0 0 NaN
8 129.0 FEMALE 19.0 1 432.0 NaN NaN 0
In [26]:
df.columns=['customer_id','gender','age','driving_licence present','region_code','previously_insured','vehicle_age','vehicle_damage']
In [27]:
df
Out[27]:
customer_id gender age driving_licence present region_code previously_insured vehicle_age vehicle_damage
0 121.0 MALE NaN 1 345.0 0.0 3 YEARS NO
1 122.0 FEMALE 28.0 1 456.0 0.0 2 YEARS NO
2 123.0 MALE 43.0 1 765.0 1.0 4 YEARS NO
3 124.0 MALE 38.0 1 457.0 3.0 3 YEARS YES
4 125.0 MALE 34.0 2 234.0 0.0 3 YEARS NO
5 126.0 MALE 50.0 1 875.0 0.0 5 YEARS NO
6 127.0 MALE 0.0 0 232.0 2.0 4 YEARS NaN
7 NaN NaN 0.0 0 NaN 0.0 0 NaN
8 129.0 FEMALE 19.0 1 432.0 NaN NaN 0

ii. Column Name for customer_policy table:

       customer_id, 

       annual premium (in Rs), 

       sales channel code, 

       vintage and response.
In [28]:
df1=pd.read_csv('policy_details.csv',header=None)
In [29]:
df1
Out[29]:
0 1 2 3 4
0 121 3454.0 12.0 321.0 1.0
1 122 45000.0 NaN 32.0 1.0
2 123 32000.0 12.0 56.0 1.0
3 124 21000.0 12.0 75.0 1.0
4 125 24000.0 13.0 67.0 0.0
5 126 39000.0 12.0 87.0 0.0
6 0 0.0 0.0 NaN 0.0
7 128 23000.0 13.0 54.0 1.0
8 0 0.0 0.0 0.0 0.0
9 129 32100.0 0.0 32.0 2.0
10 130 0.0 14.0 423.0 3.0
11 131 NaN 15.0 0.0 NaN
In [30]:
 
df1.columns=['customer_id','annual premium (in Rs)','sales channel code','vintage','response']
In [31]:
df1
Out[31]:
customer_id annual premium (in Rs) sales channel code vintage response
0 121 3454.0 12.0 321.0 1.0
1 122 45000.0 NaN 32.0 1.0
2 123 32000.0 12.0 56.0 1.0
3 124 21000.0 12.0 75.0 1.0
4 125 24000.0 13.0 67.0 0.0
5 126 39000.0 12.0 87.0 0.0
6 0 0.0 0.0 NaN 0.0
7 128 23000.0 13.0 54.0 1.0
8 0 0.0 0.0 0.0 0.0
9 129 32100.0 0.0 32.0 2.0
10 130 0.0 14.0 423.0 3.0
11 131 NaN 15.0 0.0 NaN

2. Checking and Cleaning Data Quality:

i. Null values

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

In [32]:
 
df.isna().sum()
Out[32]:
customer_id                1
gender                     1
age                        1
driving_licence present    0
region_code                1
previously_insured         1
vehicle_age                1
vehicle_damage             2
dtype: int64
In [33]:
 
df1.isna().sum()
Out[33]:
customer_id               0
annual premium (in Rs)    1
sales channel code        1
vintage                   1
response                  1
dtype: int64

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

In [37]:
df=df.dropna(subset=['customer_id'])
In [38]:
df.isna().sum()
Out[38]:
customer_id                0
gender                     0
age                        1
driving_licence present    0
region_code                0
previously_insured         1
vehicle_age                1
vehicle_damage             1
dtype: int64
In [39]:
df1=df1.dropna(subset=['customer_id'])
In [40]:
 
df1.isna().sum()
Out[40]:
customer_id               0
annual premium (in Rs)    1
sales channel code        1
vintage                   1
response                  1
dtype: int64

Replace all null values for numeric columns by mean.

In [45]:
 
df.fillna(df.mean().mean(),inplace=True)
C:\Users\AKHIL THOMAS\AppData\Local\Temp\ipykernel_17496\1564813062.py:1: FutureWarning: Dropping of nuisance columns in DataFrame reductions (with 'numeric_only=None') is deprecated; in a future version this will raise TypeError.  Select only valid columns before calling the reduction.
  df.fillna(df.mean().mean(),inplace=True)
In [46]:
df.isna().sum()
Out[46]:
customer_id                0
gender                     0
age                        0
driving_licence present    0
region_code                0
previously_insured         0
vehicle_age                0
vehicle_damage             0
dtype: int64
In [49]:
 
df
Out[49]:
customer_id gender age driving_licence present region_code previously_insured vehicle_age vehicle_damage
0 121.0 MALE 30.285714 1 345.0 0.000000 3 YEARS NO
1 122.0 FEMALE 28.000000 1 456.0 0.000000 2 YEARS NO
2 123.0 MALE 43.000000 1 765.0 1.000000 4 YEARS NO
3 124.0 MALE 38.000000 1 457.0 3.000000 3 YEARS YES
4 125.0 MALE 34.000000 2 234.0 0.000000 3 YEARS NO
5 126.0 MALE 50.000000 1 875.0 0.000000 5 YEARS NO
6 127.0 MALE 0.000000 0 232.0 2.000000 4 YEARS 126.253571
8 129.0 FEMALE 19.000000 1 432.0 0.857143 126.253571 0
In [47]:
df1.fillna(df1.mean().mean(),inplace=True)
In [48]:
df1.isna().sum()
Out[48]:
customer_id               0
annual premium (in Rs)    0
sales channel code        0
vintage                   0
response                  0
dtype: int64
In [50]:
 
df1
Out[50]:
customer_id annual premium (in Rs) sales channel code vintage response
0 121 3454.000000 12.000000 321.000000 1.000000
1 122 45000.000000 4035.783333 32.000000 1.000000
2 123 32000.000000 12.000000 56.000000 1.000000
3 124 21000.000000 12.000000 75.000000 1.000000
4 125 24000.000000 13.000000 67.000000 0.000000
5 126 39000.000000 12.000000 87.000000 0.000000
6 0 0.000000 0.000000 4035.783333 0.000000
7 128 23000.000000 13.000000 54.000000 1.000000
8 0 0.000000 0.000000 0.000000 0.000000
9 129 32100.000000 0.000000 32.000000 2.000000
10 130 0.000000 14.000000 423.000000 3.000000
11 131 4035.783333 15.000000 0.000000 4035.783333

Replace all null values for Categorical value by mode.

In [51]:
df['gender']=df['gender'].fillna(df['gender'].mode()[0])
df['age']=df['age'].fillna(df['age'].mode()[0])
df['driving_licence present']=df['driving_licence present'].fillna(df['driving_licence present'].mode()[0])
df['region_code']=df['region_code'].fillna(df['region_code'].mode()[0])
df['previously_insured']=df['previously_insured'].fillna(df['previously_insured'].mode()[0])
df['vehicle_age']=df['vehicle_age'].fillna(df['vehicle_age'].mode()[0])
df['vehicle_damage']=df['vehicle_damage'].fillna(df['vehicle_damage'].mode()[0])
​
In [52]:
df
Out[52]:
customer_id gender age driving_licence present region_code previously_insured vehicle_age vehicle_damage
0 121.0 MALE 30.285714 1 345.0 0.000000 3 YEARS NO
1 122.0 FEMALE 28.000000 1 456.0 0.000000 2 YEARS NO
2 123.0 MALE 43.000000 1 765.0 1.000000 4 YEARS NO
3 124.0 MALE 38.000000 1 457.0 3.000000 3 YEARS YES
4 125.0 MALE 34.000000 2 234.0 0.000000 3 YEARS NO
5 126.0 MALE 50.000000 1 875.0 0.000000 5 YEARS NO
6 127.0 MALE 0.000000 0 232.0 2.000000 4 YEARS 126.253571
8 129.0 FEMALE 19.000000 1 432.0 0.857143 126.253571 0
In [53]:
df.isnull().sum()
Out[53]:
customer_id                0
gender                     0
age                        0
driving_licence present    0
region_code                0
previously_insured         0
vehicle_age                0
vehicle_damage             0
dtype: int64
In [55]:
 
df1['annual premium (in Rs)']=df1['annual premium (in Rs)'].fillna(df1['annual premium (in Rs)'].mode()[0])
df1['sales channel code']=df1['sales channel code'].fillna(df1['sales channel code'].mode()[0])
df1['vintage']=df1['vintage'].fillna(df1['vintage'].mode()[0])
df1['response']=df1['response'].fillna(df1['response'].mode()[0])
​
In [57]:
 
df1.isnull().sum()
Out[57]:
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 (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)

In [60]:
q1=df.quantile(0.25)
q3=df.quantile(0.75)
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((df<l)|(df>u)).sum()
C:\Users\AKHIL THOMAS\AppData\Local\Temp\ipykernel_17496\3819093075.py:6: FutureWarning: Automatic reindexing on DataFrame vs Series comparisons is deprecated and will raise ValueError in a future version. Do `left, right = left.align(right, axis=1, copy=False)` before e.g. `left == right`
  ((df<l)|(df>u)).sum()
Out[60]:
age                        1
customer_id                0
driving_licence present    2
gender                     0
previously_insured         0
region_code                1
vehicle_age                0
vehicle_damage             0
dtype: int64
In [59]:
q1=df1.quantile(0.25)
q3=df1.quantile(0.75)
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((df1<l)|(df1>u)).sum()
Out[59]:
customer_id               2
annual premium (in Rs)    0
sales channel code        4
vintage                   3
response                  1
dtype: int64

Replace all outlier values for numeric columns by mean.

In [63]:
df1['annual premium (in Rs)']=df1['annual premium (in Rs)'].astype('float')
q1=df1[['annual premium (in Rs)']].quantile(0.25)
q3=df1[['annual premium (in Rs)']].quantile(0.75)
​
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((df1[['annual premium (in Rs)']]<l)|(df1[['annual premium (in Rs)']]>u)).sum()
Out[63]:
annual premium (in Rs)    0
dtype: int64

iii. White spaces

Remove white spaces

In [66]:
df['gender'].str.strip()
​
Out[66]:
0      MALE
1    FEMALE
2      MALE
3      MALE
4      MALE
5      MALE
6      MALE
8    FEMALE
Name: gender, dtype: object

iv. case correction(lower or upper, any one)

In [67]:
df.apply(lambda x: x.astype(str).str.upper())
Out[67]:
customer_id gender age driving_licence present region_code previously_insured vehicle_age vehicle_damage
0 121.0 MALE 30.285714285714285 1 345.0 0.0 3 YEARS NO
1 122.0 FEMALE 28.0 1 456.0 0.0 2 YEARS NO
2 123.0 MALE 43.0 1 765.0 1.0 4 YEARS NO
3 124.0 MALE 38.0 1 457.0 3.0 3 YEARS YES
4 125.0 MALE 34.0 2 234.0 0.0 3 YEARS NO
5 126.0 MALE 50.0 1 875.0 0.0 5 YEARS NO
6 127.0 MALE 0.0 0 232.0 2.0 4 YEARS 126.25357142857142
8 129.0 FEMALE 19.0 1 432.0 0.8571428571428571 126.25357142857142 0
In [68]:
 
df1.apply(lambda x: x.astype(str).str.upper())
Out[68]:
customer_id annual premium (in Rs) sales channel code vintage response
0 121 3454.0 12.0 321.0 1.0
1 122 45000.0 4035.7833333333338 32.0 1.0
2 123 32000.0 12.0 56.0 1.0
3 124 21000.0 12.0 75.0 1.0
4 125 24000.0 13.0 67.0 0.0
5 126 39000.0 12.0 87.0 0.0
6 0 0.0 0.0 4035.7833333333338 0.0
7 128 23000.0 13.0 54.0 1.0
8 0 0.0 0.0 0.0 0.0
9 129 32100.0 0.0 32.0 2.0
10 130 0.0 14.0 423.0 3.0
11 131 4035.7833333333338 15.0 0.0 4035.7833333333338

v. Convert nominal data (categorical) into dummies

for future modeling use if required

In [70]:
df3=pd.get_dummies(df)
In [71]:
 
df3
Out[71]:
customer_id age driving_licence present region_code previously_insured gender_FEMALE gender_MALE vehicle_age_126.25357142857142 vehicle_age_2 YEARS vehicle_age_3 YEARS vehicle_age_3 YEARS vehicle_age_4 YEARS vehicle_age_5 YEARS vehicle_damage_126.25357142857142 vehicle_damage_0 vehicle_damage_NO vehicle_damage_YES
0 121.0 30.285714 1 345.0 0.000000 0 1 0 0 0 1 0 0 0 0 1 0
1 122.0 28.000000 1 456.0 0.000000 1 0 0 1 0 0 0 0 0 0 1 0
2 123.0 43.000000 1 765.0 1.000000 0 1 0 0 0 0 1 0 0 0 1 0
3 124.0 38.000000 1 457.0 3.000000 0 1 0 0 1 0 0 0 0 0 0 1
4 125.0 34.000000 2 234.0 0.000000 0 1 0 0 1 0 0 0 0 0 1 0
5 126.0 50.000000 1 875.0 0.000000 0 1 0 0 0 0 0 1 0 0 1 0
6 127.0 0.000000 0 232.0 2.000000 0 1 0 0 0 0 1 0 1 0 0 0
8 129.0 19.000000 1 432.0 0.857143 1 0 1 0 0 0 0 0 0 1 0 0
In [72]:
df4=pd.get_dummies(df1)
In [73]:
 
df4
Out[73]:
customer_id annual premium (in Rs) sales channel code vintage response
0 121 3454.000000 12.000000 321.000000 1.000000
1 122 45000.000000 4035.783333 32.000000 1.000000
2 123 32000.000000 12.000000 56.000000 1.000000
3 124 21000.000000 12.000000 75.000000 1.000000
4 125 24000.000000 13.000000 67.000000 0.000000
5 126 39000.000000 12.000000 87.000000 0.000000
6 0 0.000000 0.000000 4035.783333 0.000000
7 128 23000.000000 13.000000 54.000000 1.000000
8 0 0.000000 0.000000 0.000000 0.000000
9 129 32100.000000 0.000000 32.000000 2.000000
10 130 0.000000 14.000000 423.000000 3.000000
11 131 4035.783333 15.000000 0.000000 4035.783333

vi. Drop Duplicates (duplicated rows)

In [74]:
df3.drop_duplicates()
Out[74]:
customer_id age driving_licence present region_code previously_insured gender_FEMALE gender_MALE vehicle_age_126.25357142857142 vehicle_age_2 YEARS vehicle_age_3 YEARS vehicle_age_3 YEARS vehicle_age_4 YEARS vehicle_age_5 YEARS vehicle_damage_126.25357142857142 vehicle_damage_0 vehicle_damage_NO vehicle_damage_YES
0 121.0 30.285714 1 345.0 0.000000 0 1 0 0 0 1 0 0 0 0 1 0
1 122.0 28.000000 1 456.0 0.000000 1 0 0 1 0 0 0 0 0 0 1 0
2 123.0 43.000000 1 765.0 1.000000 0 1 0 0 0 0 1 0 0 0 1 0
3 124.0 38.000000 1 457.0 3.000000 0 1 0 0 1 0 0 0 0 0 0 1
4 125.0 34.000000 2 234.0 0.000000 0 1 0 0 1 0 0 0 0 0 1 0
5 126.0 50.000000 1 875.0 0.000000 0 1 0 0 0 0 0 1 0 0 1 0
6 127.0 0.000000 0 232.0 2.000000 0 1 0 0 0 0 1 0 1 0 0 0
8 129.0 19.000000 1 432.0 0.857143 1 0 1 0 0 0 0 0 0 1 0 0
In [75]:
df4.drop_duplicates()
Out[75]:
customer_id annual premium (in Rs) sales channel code vintage response
0 121 3454.000000 12.000000 321.000000 1.000000
1 122 45000.000000 4035.783333 32.000000 1.000000
2 123 32000.000000 12.000000 56.000000 1.000000
3 124 21000.000000 12.000000 75.000000 1.000000
4 125 24000.000000 13.000000 67.000000 0.000000
5 126 39000.000000 12.000000 87.000000 0.000000
6 0 0.000000 0.000000 4035.783333 0.000000
7 128 23000.000000 13.000000 54.000000 1.000000
8 0 0.000000 0.000000 0.000000 0.000000
9 129 32100.000000 0.000000 32.000000 2.000000
10 130 0.000000 14.000000 423.000000 3.000000
11 131 4035.783333 15.000000 0.000000 4035.783333

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)

In [76]:
master_table=pd.merge(df,df1)
​
master_table['age']=master_table['age'].astype('int')
master_table
Out[76]:
customer_id gender age driving_licence present region_code previously_insured vehicle_age vehicle_damage annual premium (in Rs) sales channel code vintage response
0 121.0 MALE 30 1 345.0 0.000000 3 YEARS NO 3454.0 12.000000 321.0 1.0
1 122.0 FEMALE 28 1 456.0 0.000000 2 YEARS NO 45000.0 4035.783333 32.0 1.0
2 123.0 MALE 43 1 765.0 1.000000 4 YEARS NO 32000.0 12.000000 56.0 1.0
3 124.0 MALE 38 1 457.0 3.000000 3 YEARS YES 21000.0 12.000000 75.0 1.0
4 125.0 MALE 34 2 234.0 0.000000 3 YEARS NO 24000.0 13.000000 67.0 0.0
5 126.0 MALE 50 1 875.0 0.000000 5 YEARS NO 39000.0 12.000000 87.0 0.0
6 129.0 FEMALE 19 1 432.0 0.857143 126.253571 0 32100.0 0.000000 32.0 2.0

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

In [77]:
 
master_table.groupby(['gender'])['annual premium (in Rs)'].mean()
Out[77]:
gender
FEMALE    38550.0
MALE      23890.8
Name: annual premium (in Rs), dtype: float64

ii. Age wise average annual premium

In [78]:
 
master_table.groupby(['age'])['annual premium (in Rs)'].mean()
Out[78]:
age
19    32100.0
28    45000.0
30     3454.0
34    24000.0
38    21000.0
43    32000.0
50    39000.0
Name: annual premium (in Rs), dtype: float64

iii. Is your data balanced between the genders?

      (Hint: Data is balanced if number of counts in each group is approximately same)
In [79]:
master_table['gender'].value_counts()
Out[79]:
MALE      5
FEMALE    2
Name: gender, dtype: int64

iv. Vehicle age wise average annual premium.

In [80]:
 
master_table.groupby(['vehicle_age'])['annual premium (in Rs)'].mean()
Out[80]:
vehicle_age
126.25357142857142    32100.0
2 YEARS               45000.0
3 YEARS               22500.0
3 YEARS                3454.0
4 YEARS               32000.0
5 YEARS               39000.0
Name: annual premium (in Rs), dtype: float64

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.

Click here for the files

In [81]:
np.corrcoef(master_table['age'],master_table['annual premium (in Rs)'])
Out[81]:
array([[1.        , 0.14178702],
       [0.14178702, 1.        ]])
Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.