EDA on Vehicle Insurance Customer Data using PYTHON
# Project 2 - EDA on Vehicle Insurance Customer Data
# 1. Add the column names to both datasets: 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. Adding columns to customer details import pandas as pd df1=pd.read_csv(".\customer_details.csv") df1.columns = ["customer_id","Gender", "age", "driving licence present","region code","previously insured","vehicle age","vehicle damage"] print(df1)
customer_id Gender age driving licence present region code \ 0 1.0 Male 44.0 1.0 28.0 1 2.0 Male 76.0 1.0 3.0 2 3.0 Male 47.0 1.0 28.0 3 4.0 Male 21.0 1.0 11.0 4 5.0 Female 29.0 1.0 41.0 ... ... ... ... ... ... 381104 381105.0 Male 74.0 1.0 26.0 381105 381106.0 Male 30.0 1.0 37.0 381106 381107.0 Male 21.0 1.0 30.0 381107 381108.0 Female 68.0 1.0 14.0 381108 381109.0 Male 46.0 1.0 29.0 previously insured vehicle age vehicle damage 0 0.0 > 2 Years Yes 1 0.0 1-2 Year No 2 0.0 > 2 Years Yes 3 1.0 < 1> 2 Years Yes 381108 0.0 1-2 Year No [381109 rows x 8 columns]
# Adding columns to customer_policy details import pandas as pd df2=pd.read_csv(".\customer_policy_details.csv") df2.columns = ["customer_id", "annual premium (in Rs)", "sales channel code", "vintage", "response"] print(df2)
customer_id annual premium (in Rs) sales channel code vintage \
0 1.0 40454.0 26.0 217.0
1 2.0 33536.0 26.0 183.0
2 3.0 38294.0 26.0 27.0
3 4.0 28619.0 152.0 203.0
4 5.0 27496.0 152.0 39.0
... ... ... ... ...
381104 381105.0 30170.0 26.0 88.0
381105 381106.0 40016.0 152.0 131.0
381106 381107.0 35118.0 160.0 161.0
381107 381108.0 44617.0 124.0 74.0
381108 381109.0 41777.0 26.0 237.0
response
0 1.0
1 0.0
2 1.0
3 0.0
4 0.0
... ...
381104 0.0
381105 0.0
381106 0.0
381107 0.0
381108 0.0
[381109 rows x 5 columns]
# 2.Checking and Cleaning Data Quality:
i.Null values
Generate a summary of count of all the null values column wise
Drop Null values for customer_id because central tendencies for id’s is not feasible.
Replace all null values for numeric columns by mean.
Replace all null values for Categorical value by mode.
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)
#i).
Null values count for df1 -customer_details
df1.isna().sum()
customer_id 386 Gender 368 age 368 driving licence present 393 region code 392 previously insured 381 vehicle age 381 vehicle damage 407 dtype: int64
#Null values count for df1 - customer_policy_details
df2.isna().sum()
customer_id 387 annual premium (in Rs) 346 sales channel code 400 vintage 388 response 361 dtype: int64
#Drop Null values for customer_id because central tendencies for id’s is not feasible df1.dropna(subset=['customer_id'],inplace=True) df2.dropna(subset=['customer_id'],inplace=True)
#Replacing null values by mean value for numeric columns # customer_details database df1['age'].fillna(df1['age'].mean(),inplace=True) df1['region code'].fillna(df1['region code'].mean(),inplace=True) #customer_policy_details database df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean(),inplace=True) df2['sales channel code'].fillna(df2['sales channel code'].mean(),inplace=True) df2['vintage'].fillna(df2['vintage'].mean(),inplace=True) df1.isna().sum() #To check that null values has been replaced by mean
customer_id 0 Gender 368 age 0 driving licence present 392 region code 0 previously insured 381 vehicle age 381 vehicle damage 406 dtype: int64
df2.isna().sum() #To check that null values has been replaced by mean
customer_id 0 annual premium (in Rs) 0 sales channel code 0 vintage 0 response 361 dtype: int64
#Replace all null values for Categorical value by mode. df1['Gender'].fillna(df1['Gender'].mode()[0],inplace=True) df1['driving licence present'].fillna(df1['driving licence present'].mode()[0],inplace=True) df1['previously insured'].fillna(df1['previously insured'].mode()[0],inplace=True) df1['vehicle damage'].fillna(df1['vehicle damage'].mode()[0],inplace=True) df1['vehicle age'].fillna(df1['vehicle age'].mode()[0],inplace=True) df2['response'].fillna(df2['response'].mode()[0],inplace=True)
df1.isna().sum() #To check all null values for Categorical value by mode
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
df2.isna().sum() #To check all null values for Categorical value by mode
customer_id 0 annual premium (in Rs) 0 sales channel code 0 vintage 0 response 0 dtype: int64
#Generate a summary of count of all the outliers column wise Replace all outlier values for numeric columns by mean. #Describing database 1 : customer_details df1.describe()
| customer_id | age | driving licence present | region code | previously insured | |
|---|---|---|---|---|---|
| count | 380723.000000 | 380723.000000 | 380723.000000 | 380723.000000 | 380723.000000 |
| mean | 190548.776244 | 38.822788 | 0.997870 | 26.389436 | 0.457800 |
| std | 110016.805160 | 15.504826 | 0.046105 | 13.223671 | 0.498217 |
| min | 1.000000 | 20.000000 | 0.000000 | 0.000000 | 0.000000 |
| 25% | 95269.500000 | 25.000000 | 1.000000 | 15.000000 | 0.000000 |
| 50% | 190543.000000 | 36.000000 | 1.000000 | 28.000000 | 0.000000 |
| 75% | 285822.500000 | 49.000000 | 1.000000 | 35.000000 | 1.000000 |
| max | 381109.000000 | 85.000000 | 1.000000 | 52.000000 | 1.000000 |
# Method for finding Inter quartile range for database 1 column - age Q1=df1.describe().loc["25%","age"] Q3=df1.describe().loc["75%","age"] IQR=Q3-Q1 print('Outlier : Customer_details ') print('Outliers present in age column :',df1.loc[df1["age"]<(Q1-IQR*1.5),"age"].count()) print('Outliers present in age column :',df1.loc[df1["age"]>(Q3+IQR*1.5),"age"].count())
Outlier : Customer_details Outliers present in age column : 0 Outliers present in age column : 0
# Method for finding Inter quartile range for database 1 column - region code Q1=df1.describe().loc["25%","region code"] Q3=df1.describe().loc["75%","region code"] IQR=Q3-Q1 print('Outlier : Customer_details') print('Outliers present in region code column :',df1.loc[df1["region code"]<(Q1-IQR*1.5),"region code"].count()) print('Outliers present in region code column :',df1.loc[df1["region code"]>(Q3+IQR*1.5),"region code"].count())
Outlier : Customer_details Outliers present in region code column : 0 Outliers present in region code column : 0
#Describing database 2 : customer_policy_details df2.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.122410 |
| std | 110013.824148 | 17190.147550 | 54.177046 | 83.628096 | 0.327759 |
| 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 |
# Method for finding Inter quartile range for database 2 column -
Q1=df2.describe().loc["25%","vintage"]
Q3=df2.describe().loc["75%","vintage"]
IQR=Q3-Q1
print('Outiler :Customer_policy_details')
print('Outliers present in vintage column are :',df2.loc[df2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print('Outliers present in vintage column are :',df2.loc[df2["vintage"]>(Q3+IQR*1.5),"vintage"].count())
Outiler :Customer_policy_details
Outliers present in vintage column are : 0
Outliers present in vintage column are : 0
# Method for finding Inter quartile range for database 2 column - annual premium (in Rs)
Q1=df2.describe().loc["25%","annual premium (in Rs)"]
Q3=df2.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print('Outliers :Customer_policy_details')
print('Outliers present in annual premium (in Rs) column :',df2.loc[df2["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print('Outliers present in annual premium (in Rs) column :',df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Outliers :Customer_policy_details Outliers present in annual premium (in Rs) column : 0 Outliers present in annual premium (in Rs) column : 10332
#ii). Replacing outliers by mean value df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"]=df2["annual premium (in Rs)"].mean()
#Checking whether Outilers are replaced by mean value print('Outliers present in annual premium (in Rs) column :',df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())
Outliers present in annual premium (in Rs) column : 0
#iii). Whitesapces # Removing whitespaces df1["Gender"]=df1["Gender"].str.strip() df1["vehicle age"]=df1["vehicle age"].str.strip() df1["vehicle damage"]=df1["vehicle damage"].str.strip()
#iv. case correction(lower or upper, any one) df1["Gender"]=df1["Gender"].str.upper() df1["vehicle age"]=df1["vehicle age"].str.upper() df1["vehicle damage"]=df1["vehicle damage"].str.upper()
df1 # Checking case correction # Converted to upper case
OUTPUT:
| 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> | 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 |
380723 rows × 8 columns
#v. Convert nominal data (categorical) into dummies #For database 1 d_gender=pd.get_dummies(df1["Gender"]) d_driving_licence_present=pd.get_dummies(df1["driving licence present"]) d_previously_insured=pd.get_dummies(df1["previously insured"]) d_vehicle_age=pd.get_dummies(df1["vehicle age"]) d_vehicle_damage=pd.get_dummies(df1["vehicle damage"]) #For database 2 d_response=pd.get_dummies(df2["response"])
#vi. Drop Duplicates (duplicated rows)
df1.duplicated().sum()
# zero duplicates
0
df2.duplicated().sum() # zero duplicates
0
# 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)
# MERGING DATABASES #df is master_table df = pd.merge(df1,df2,on='customer_id')
# Printing master_table df
OUTPUT:
| 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> | NO | 28619.0 | 152.0 | 203.0 | 0.0 |
| 4 | 5.0 | FEMALE | 29.0 | 1.0 | 41.0 | 1.0 | < 1> | 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> | NO | 40016.0 | 152.0 | 131.0 | 0.0 |
| 380333 | 381107.0 | MALE | 21.0 | 1.0 | 30.0 | 1.0 | < 1> | 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:
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
df.groupby(["Gender"])["annual premium (in Rs)"].mean()
OUTPUT:
Gender FEMALE 29273.474247 MALE 29323.099677 Name: annual premium (in Rs), dtype: float64
# ii). Age wise average annual premium
df.groupby(["age"])["annual premium (in Rs)"].mean()
OUTPUT:
age
20.0 26342.073517
21.0 29751.791916
22.0 29946.848634
23.0 29838.344763
24.0 30125.557096
...
81.0 29287.910702
82.0 36480.586199
83.0 28995.818172
84.0 35440.818182
85.0 26637.454525
Name: annual premium (in Rs), Length: 67, dtype: float64
# iii). Is your data balanced between the genders? df.groupby(['Gender']).count()
OUTPUT:
| customer_id | age | driving licence present | region code | previously insured | vehicle age | vehicle damage | annual premium (in Rs) | sales channel code | vintage | response | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| Gender | |||||||||||
| FEMALE | 174485 | 174485 | 174485 | 174485 | 174485 | 174485 | 174485 | 174485 | 174485 | 174485 | 174485 |
| MALE | 205851 | 205851 | 205851 | 205851 | 205851 | 205851 | 205851 | 205851 | 205851 | 205851 | 205851 |
# Result : Data is balanced between the genders since the count is approixmately near and same
# iv). Vehicle age wise average annual premium. df.groupby(["vehicle age"])["annual premium (in Rs)"].mean()
OUTPUT:
vehicle age 1-2 YEAR 29102.009839 < 1> 2 YEARS 32943.540830 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>
#Correlation coefficient calculation df[["age","annual premium (in Rs)"]].corr()
OUTPUT:
| age | annual premium (in Rs) | |
|---|---|---|
| age | 1.000000 | 0.050658 |
| annual premium (in Rs) | 0.050658 | 1.000000 |
#The correlation coefficient between person age and annual premium is 0.050658 #Conclusion : 0.5 < Correlation>