Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd

import numpy as np
customer_details=pd.read_csv('customer_details.csv')
customer_policy_details=pd.read_csv('customer_policy_details.csv')
# 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.

customer_details.rename({'0':'Customer_Id','1':'Gender','2':'Age','3':'Driving Licence Present','4':'Region Code','5':'Previously Insured','6':'Vehicle Age','7':'Vehicle Damage'},axis=1,inplace=True)
customer_details.head()
## ii. Column Name for customer_policy table:
### customer_id,
### annual premium (in Rs),
### sales channel code,
### vintage and response.
customer_policy_details.rename({'0':'Customer_Id','1':'Annual Premium(Rs)','2':'Sales Channel Code','3':'Vintage','4':'Response'},axis=1,inplace=True)
customer_policy_details.head()
# 2. Checking and Cleaning Data Quality:

## i. Null values

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

customer_details.isna().sum()
customer_policy_details.isna().sum()
### Drop Null values for customer_id because central tendencies for id’s is not feasible.

customer_details=customer_details.dropna(subset=['Customer_Id'])
customer_details.isna().sum()
customer_policy_details=customer_policy_details.dropna(subset=['Customer_Id'])
customer_policy_details.isna().sum()
### Replace all null values for numeric columns by mean.

customer_policy_details.fillna(customer_policy_details.mean().mean(),inplace=True)
customer_policy_details.isna().sum()
customer_details.isna().sum()
### Replace all null values for Categorical value by mode.
customer_details['Gender']=customer_details['Gender'].fillna(customer_details['Gender'].mode()[0])
customer_details['Age']=customer_details['Age'].fillna(customer_details['Age'].mode()[0])
customer_details['Driving Licence Present']=customer_details['Driving Licence Present'].fillna(customer_details['Driving Licence Present'].mode()[0])
customer_details['Region Code']=customer_details['Region Code'].fillna(customer_details['Region Code'].mode()[0])
customer_details['Previously Insured']=customer_details['Previously Insured'].fillna(customer_details['Previously Insured'].mode()[0])
customer_details['Vehicle Age']=customer_details['Vehicle Age'].fillna(customer_details['Vehicle Age'].mode()[0])
customer_details['Vehicle Damage']=customer_details['Vehicle Damage'].fillna(customer_details['Vehicle Damage'].mode()[0])

customer_details.isnull().sum()
## 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)
q1=customer_policy_details.quantile(0.25)
q3=customer_policy_details.quantile(0.75)
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((customer_policy_details<l)|(customer_policy_details>u)).sum()
### Replace all outlier values for numeric columns by mean.
customer_policy_details['Annual Premium(Rs)']=customer_policy_details['Annual Premium(Rs)'].astype('float')
q1=customer_policy_details[['Annual Premium(Rs)']].quantile(0.25)
q3=customer_policy_details[['Annual Premium(Rs)']].quantile(0.75)
iqr=q3-q1
l=q1-(1.5*iqr)
u=q3+(1.5*iqr)
((customer_policy_details[['Annual Premium(Rs)']]<l)|(customer_policy_details[['Annual Premium(Rs)']]>u)).sum()
## iii. White spaces

### Remove white spaces
customer_details['Gender'].str.strip()
## iv. case correction(lower or upper, any one)

customer_details.apply(lambda x: x.astype(str).str.upper())

## v. Convert nominal data (categorical) into dummies

### for future modeling use if required
customerdetails=pd.get_dummies(customer_details)
customerdetails
customerpolicydetails=pd.get_dummies(customer_policy_details)
customerpolicydetails
## vi. Drop Duplicates (duplicated rows)

customerdetails.drop_duplicates()
# 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)
master_table=pd.merge(customer_details,customer_policy_details)
master_table['Age']=master_table['Age'].astype('int')
master_table
# 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

master_table.groupby(['Gender'])['Annual Premium(Rs)'].mean()
## ii. Age wise average annual premium

master_table.groupby(['Age'])['Annual Premium(Rs)'].mean()
## iii. Is your data balanced between the genders?

### (Hint: Data is balanced if number of counts in each group is approximately same)
master_table['Gender'].value_counts()
## iv. Vehicle age wise average annual premium.
master_table.groupby(['Vehicle Age'])['Annual Premium(Rs)'].mean()
# 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.

np.corrcoef(master_table['Age'],master_table['Annual Premium(Rs)'])# There is no relationship