Project 2 - EDA on Vehicle Insurance Customer Data
14 - Project 2 - EDA on Vehicle Insurance Customer Data
1. Add the column names to both datasets:
import pandas as pd
df1 = pd.read_csv('.\pandas1\customer_details.csv')
df1.columns = ['customer_id','gender','age','driving licence present','region code',
'previously insured','vehicle age','vehicle damage']
In [8]:
df1
Out[8]:
| customer_id | gender | age | driving licence present | region code | previously insured | vehicle age | vehicle 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
In [42]:
df2 = pd.read_csv('.\pandas1\customer_policy_details.csv')
df2.columns = ['customer_id','annual premium (in Rs)','sales channel code','vintage','response']
In [43]:
df2
Out[43]:
| 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
2. Checking and Cleaning Data Quality:
In [16]:
# 1. null values
# customer details
print('null values of customer_id :',df1['customer_id'].isnull().sum())
df1 = df1.dropna(subset=['customer_id'])
print('null values of customer_id after droping :',df1['customer_id'].isnull().sum())
null values of customer_id : 0 null values of customer_id after droping : 0
In [19]:
print('null values of gender :',df1['gender'].isnull().sum())
df1['gender'] = df1['gender'].fillna(df1['gender'].mode()[0])
print('null values of gender after replacing :',df1['gender'].isnull().sum())
null values of gender : 0 null values of gender after replacing : 0
In [20]:
print('null values of age :',df1['age'].isnull().sum())
df1['age'] = df1['age'].fillna(df1['age'].mean())
print('null values of age after replacing :',df1['age'].isnull().sum())
null values of age : 366 null values of age after replacing : 0
In [21]:
print('null values of driving licence present :',df1['driving licence present'].isnull().sum())
df1['driving licence present'] = df1['driving licence present'].fillna(df1['driving licence present'].mode()[0])
print('null values of driving licence present after replacing :',df1['driving licence present'].isnull().sum())
null values of driving licence present : 392 null values of driving licence present after replacing : 0
In [22]:
print('null values of region code :',df1['region code'].isnull().sum())
df1['region code'] = df1['region code'].fillna(df1['region code'].mode()[0])
print('null values of region code after replacing :',df1['region code'].isnull().sum())
null values of region code : 391 null values of region code after replacing : 0
In [23]:
print('null values of previously insured :',df1['previously insured'].isnull().sum())
df1['previously insured'] = df1['previously insured'].fillna(df1['previously insured'].mode()[0])
print('null values of previously insured after replacing :',df1['previously insured'].isnull().sum())
null values of previously insured : 381 null values of previously insured after replacing : 0
In [24]:
print('null values of vehicle age :',df1['vehicle age'].isnull().sum())
df1['vehicle age'] = df1['vehicle age'].fillna(df1['vehicle age'].mode()[0])
print('null values of vehicle age after replacing :',df1['vehicle age'].isnull().sum())
null values of vehicle age : 381 null values of vehicle age after replacing : 0
In [25]:
print('null values of vehicle damage :',df1['vehicle damage'].isnull().sum())
df1['vehicle damage'] = df1['vehicle damage'].fillna(df1['vehicle damage'].mode()[0])
print('null values of vehicle damage after replacing :',df1['vehicle damage'].isnull().sum())
null values of vehicle damage : 406 null values of vehicle damage after replacing : 0
In [44]:
# customer_policy_details
print('null values of customer_id :',df2["customer_id"].isnull().sum())
df2 = df2.dropna(subset=["customer_id"])
print('null values of customer_id after droping :',df2["customer_id"].isnull().sum())
null values of customer_id : 387 null values of customer_id after droping : 0
In [45]:
print('null values of annual premium (in Rs) :',df2['annual premium (in Rs)'].isnull().sum())
df2['annual premium (in Rs)'] = df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean())
print('null values of annual premium (in Rs) after replacing :',df2['annual premium (in Rs)'].isnull().sum())
null values of annual premium (in Rs) : 344 null values of annual premium (in Rs) after replacing : 0
C:\Users\ASUS\AppData\Local\Temp\ipykernel_9360\1293267657.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df2['annual premium (in Rs)'] = df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean())
In [46]:
print('null values of sales channel code :',df2['sales channel code'].isnull().sum())
df2['sales channel code'] = df2['sales channel code'].fillna(df2['sales channel code'].mode()[0])
print('null values of sales channel code after replacing :',df2['sales channel code'].isnull().sum())
null values of sales channel code : 400 null values of sales channel code after replacing : 0
C:\Users\ASUS\AppData\Local\Temp\ipykernel_9360\4230632360.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df2['sales channel code'] = df2['sales channel code'].fillna(df2['sales channel code'].mode()[0])
In [47]:
print('null values of vintage :',df2['vintage'].isnull().sum())
df2['vintage'] = df2['vintage'].fillna(df2['vintage'].mean())
print('null values of vintage after replacing :',df2['vintage'].isnull().sum())
null values of vintage : 388 null values of vintage after replacing : 0
C:\Users\ASUS\AppData\Local\Temp\ipykernel_9360\903307503.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df2['vintage'] = df2['vintage'].fillna(df2['vintage'].mean())
In [48]:
print('null values of response :',df2['response'].isnull().sum())
df2['response'] = df2['response'].fillna(df2['response'].mode()[0])
print('null values of response after replacing :',df2['response'].isnull().sum())
null values of response : 361 null values of response after replacing : 0
C:\Users\ASUS\AppData\Local\Temp\ipykernel_9360\238978895.py:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy df2['response'] = df2['response'].fillna(df2['response'].mode()[0])
In [60]:
# 2. Outliers
df1.describe()
df2.describe()
Q1 = df1.describe().loc['25%','age']
Q3 = df1.describe().loc['75%','age']
IQR = Q3 - Q1
print('check outlier for age lower :',df1.loc[df1['age']<(Q1-IQR*1.5),'age'].count())
print('check outlier for age greater :',df1.loc[df1['age']>(Q3+IQR*1.5),'age'].count())
Q1 = df2.describe().loc['25%','annual premium (in Rs)']
Q3 = df2.describe().loc['75%','annual premium (in Rs)']
IQR = Q3 - Q1
print('check outlier for annual premium (in Rs) lower :',df2.loc[df2['annual premium (in Rs)']<(Q1-IQR*1.5),'annual premium (in Rs)'].count())
print('check outlier for annual premium (in Rs) greater :',df2.loc[df2['annual premium (in Rs)']>(Q3+IQR*1.5),'annual premium (in Rs)'].count())
df2.loc[df2['annual premium (in Rs)']>(Q3+IQR*1.5),'annual premium (in Rs)'] = df2['annual premium (in Rs)'].mean()
print('check outlier for annual premium (in Rs) greater :',df2.loc[df2['annual premium (in Rs)']>(Q3+IQR*1.5),'annual premium (in Rs)'].count())
Q1 = df2.describe().loc['25%','vintage']
Q3 = df2.describe().loc['75%','vintage']
IQR = Q3 - Q1
print('check outlier for vintage lower :',df2.loc[df2['vintage']<(Q1-IQR*1.5),'vintage'].count())
print('check outlier for vintage greater :',df2.loc[df2['vintage']>(Q3+IQR*1.5),'vintage'].count())
check outlier for age lower : 0 check outlier for age greater : 0 check outlier for annual premium (in Rs) lower : 64735 check outlier for annual premium (in Rs) greater : 786 check outlier for annual premium (in Rs) greater : 0 check outlier for vintage lower : 0 check outlier for vintage greater : 0
In [63]:
# 3. White spaces
df1['gender'] = df1['gender'].str.strip()
df1['vehicle age'] = df1['vehicle age'].str.strip()
df1['vehicle damage'] = df1['vehicle damage'].str.strip()
In [64]:
# 4. case correction
df1['gender'] = df1['gender'].str.lower()
df1['vehicle age'] = df1['vehicle age'].str.lower()
df1['vehicle damage'] = df1['vehicle damage'].str.lower()
In [74]:
# 5. Convert nominal data (categorical) into dummies
dummies_gender = pd.get_dummies(df1['gender'])
dummies_driving_licence_present = pd.get_dummies(df1['driving licence present'])
dummies_previously_insured = pd.get_dummies(df1['previously insured'])
dummies_vehicle_damage = pd.get_dummies(df1['vehicle damage'])
dummies_response = pd.get_dummies(df2['response'])
In [76]:
# 6. Drop Duplicates (duplicated rows)
print('duplicated rows in df1 :',df1.duplicated().sum())
print('duplicated rows in df2 :',df2.duplicated().sum())
df1.drop_duplicates()
df2.drop_duplicates()
duplicated rows in df1 : 42 duplicated rows in df2 : 0
Out[76]:
| 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
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.
In [77]:
master_df = pd.merge(df1,df2,on='customer_id')
master_df
Out[77]:
| 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 | 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 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 380331 | 381105.0 | Male | 74.0 | 1.0 | 26.0 | 1.0 | 1-2 Year | No | 30170.0 | 26.0 | 88.0 | 0.0 |
| 380332 | 381106.0 | Male | 30.0 | 1.0 | 37.0 | 1.0 | < 1 Year | No | 40016.0 | 152.0 | 131.0 | 0.0 |
| 380333 | 381107.0 | Male | 21.0 | 1.0 | 30.0 | 1.0 | < 1 Year | No | 35118.0 | 160.0 | 161.0 | 0.0 |
| 380334 | 381108.0 | Female | 68.0 | 1.0 | 14.0 | 0.0 | > 2 Years | Yes | 44617.0 | 124.0 | 74.0 | 0.0 |
| 380335 | 381109.0 | Male | 46.0 | 1.0 | 29.0 | 0.0 | 1-2 Year | No | 41777.0 | 26.0 | 237.0 | 0.0 |
380336 rows × 12 columns
4. Company needs some important information from the master table to make decisions for future growth.They needs following information:
In [79]:
# i. Gender wise average annual premium
annp_gender_df = master_df.groupby(['gender']).mean()['annual premium (in Rs)']
annp_gender_df
Out[79]:
gender Female 28996.00389 Male 29034.43524 Name: annual premium (in Rs), dtype: float64
In [80]:
# ii. Age wise average annual premium
annp_age_df = master_df.groupby(['age']).mean()['annual premium (in Rs)']
annp_age_df
Out[80]:
age
20.0 26212.348592
21.0 29546.692625
22.0 29745.540752
23.0 29651.404361
24.0 29899.078839
...
81.0 28136.035453
82.0 33312.304425
83.0 28995.818172
84.0 35440.818182
85.0 26637.454525
Name: annual premium (in Rs), Length: 66, dtype: float64
In [83]:
# iii. Is your data balanced between the genders?
print('data is balanced between the genders because avg is approx.equal... ')
data is balanced between the genders because avg is approx.equal...
In [84]:
# iv. Vehicle age wise average annual premium.
annp_vage_df = master_df.groupby(['vehicle age']).mean()['annual premium (in Rs)']
annp_vage_df
Out[84]:
vehicle age 1-2 Year 28768.152221 < 1 Year 28994.301862 > 2 Years 32340.054868 Name: annual premium (in Rs), dtype: float64
5. Is there any relation between Person Age and annual premium?
In [86]:
# 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_df['age'].corr(master_df['annual premium (in Rs)'])
print('Correlation coefficient :',Correlation_coefficient)
if Correlation_coefficient < -0.5:
print('Strong negative relationship')
elif Correlation_coefficient > 0.5:
print('Strong positive relationship')
else:
print('There is no relationship.')
Correlation coefficient : 0.04359466124565957 There is no relationship.