Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd

import numpy as np

customer_details=pd.read_csv('C://Users//abila//Downloads//customer_details.csv')
print(customer_details)

customer_policy_details=pd.read_csv('C://Users//abila//Downloads//customer_policy_details.csv')
print(customer_policy_details)

# i. Column Name for customer details table:

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_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:

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_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

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

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.

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()

# master_table.groupby(['Age'])['Annual Premium(Rs)'].mean()

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?

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

import numpy_financial as npf

out = npf.fv(rate = 100,nper= 20, pmt =0, pv= -1)

print(out)

cashflow = (20,50,100,-10,200,150)

x= npf.npv(0.1,cashflow)

print(x)

payment=npf.pmt(0.015/12, 15*12,2500000)
print(payment)

print(payment)

a = npf.pv(rate =0.10, nper=8, pmt=0, fv=1000)
print(a)

lumpsum = npf.fv(rate= 0.20, nper=10 ,pv=-251500,pmt=0)
print(lumpsum)

# initial investment PV
# expected return rate of intrest % = RATE
# time period in years = NPER

futv = npf.pv(rate = 0.45 , nper = 20, pmt = 0,fv=-1500000000)
print(futv)

#find per year payment for future value