Project 2 - EDA on Vehicle Insurance Customer Data

#1
#i
import pandas as pd
columns = ['customer_id', 'Gender','age','driving licence present','region code','previously insured',
'vehicle age', 'vehicle damage']

customer_details = pd.read_csv(r'C:\Users\HARSHA\customer_details.csv', names=columns)
#ii

policy_columns = ['customer_id',

'annual premium (in Rs)',

'sales channel code',

'vintage', 'response']

customer_policy_details = pd.read_csv(r'C:\Users\HARSHA\customer_policy_details.csv',names=policy_columns)
#2
#i
for customer in customer_details.columns:
customer_details[customer].isna().sum()
print('null values in the column '+customer + ' - ',customer_details[customer].isna().sum())

for policy in customer_policy_details.columns:
customer_policy_details[policy].isna().sum()
print('null values in the column '+policy + ' - ',customer_policy_details[policy].isna().sum())

customer_details = customer_details[customer_details['customer_id'].notna()]
customer_policy_details = customer_policy_details[customer_policy_details['customer_id'].notna()]

import numpy as np
columns = customer_details.select_dtypes(include=np.number).columns.tolist()
for column in columns:
customer_details[column].fillna(value=customer_details[column].mean(),inplace=True)

policy_columns = customer_policy_details.select_dtypes(include=np.number).columns.tolist()
for column in policy_columns:
customer_policy_details[column].fillna(value=customer_policy_details[column].mean(),inplace=True)

for customer in customer_details.columns:
customer_details[customer].isna().sum()
print('null values in the column '+customer + ' - ',customer_details[customer].isna().sum())

for policy in customer_policy_details.columns:
customer_policy_details[policy].isna().sum()
print('null values in the column '+policy + ' - ',customer_policy_details[policy].isna().sum())

rem_columns=[]
rem_policy_columns=[]
for col in customer_details.columns.tolist() :
if col not in columns:
rem_columns.append(col)
for pol_col in customer_policy_details.columns.tolist():
if pol_col not in policy_columns:
rem_policy_columns.append(pol_col)

for column in rem_columns:
customer_details[column].fillna(value=customer_details[column].mode()[0],inplace=True)
for column in rem_policy_columns:
customer_details[column].fillna(value=customer_details[column].mode()[0],inplace=True)

#ii
Q1 = customer_details.quantile(0.25)
Q3 = customer_details.quantile(0.75)
IQR = Q3 - Q1

(customer_details[customer_details.columns.tolist()] > (Q3 + 1.5 * IQR)).sum()

(customer_details[customer_details.columns.tolist()] < (Q1 - 1.5 * IQR)).sum()

mean = customer_details['driving licence present'].mean()
customer_details['driving licence present'].fillna(mean, inplace=True)

#iii
customer_details['Gender'].str.strip()

#iv
customer_details['Gender'].str.lower()

#v
pd.get_dummies(customer_details['Gender']).head()

pd.get_dummies(customer_details['age']).head()

pd.get_dummies(customer_details['driving licence present']).head()

pd.get_dummies(customer_details['previously insured']).head()

pd.get_dummies(customer_details['vehicle age']).head()

pd.get_dummies(customer_details['vehicle damage']).head()

pd.get_dummies(customer_details['region code']).head()

pd.get_dummies(customer_policy_details['sales channel code']).head()

pd.get_dummies(customer_policy_details['vintage']).head()

pd.get_dummies(customer_policy_details['response']).head()

#vi
customer_details.drop_duplicates()

customer_policy_details.drop_duplicates()

#4

master_table= pd.concat([customer_details,customer_policy_details],axis=1)

#i
master_table[master_table['Gender'] == 'Female']['annual premium (in Rs)'].mean()

master_table[master_table['Gender'] == 'Male']['annual premium (in Rs)'].mean()

#ii
master_table.groupby('age')['annual premium (in Rs)'].mean()

master_table[master_table['Gender'] == 'Female'].count()

master_table[master_table['Gender'] == 'Male'].count()

#iii
No, beacuse the count of female records is 174674 and male records is 206049

#iv
master_table.groupby('vehicle age')['annual premium (in Rs)'].mean()

#5
master_table['age'].corr(master_table['annual premium (in Rs)'])

So therefore, this signifies a strong correlation between age and annual Premium