Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd
import numpy as np

1. df1=pd.read_csv('.\customer_details.csv')

df1.columns=['Customer_ID','Gender','Age','Driving_License_Present','Region_Code','Previously_Insured','Vehicle_Age','Vehicle_Damage']

df1

df2=pd.read_csv('.\customer_policy_details.csv')

df2.columns=['Customer_ID','Annual_Premium_inRs','Sales_Channel_Code','Vintage','Response']

df2

2. i)

# Generate a summary of count of all the null values column wise
# customer_details
print('Count of null values in customer_details.csv: \n',df1.isnull().sum())

# Customer_policy_details
print('Count of null values in customer_policy_details.csv: \n',df2.isnull().sum())

# Drop Null values for customer_id because central tendencies for id’s is not feasible.
df1.dropna(axis=1) # No null values

df2.dropna(axis=1) # No null values

# Customer_details

df1['Gender'].fillna(df1['Gender'].mode())

df1['Age'].fillna(df1['Age'].mean())

df1['Driving_License_Present'].fillna(df1['Driving_License_Present'].mean())

df1['Region_Code'].fillna(df1['Region_Code'].mean())

df1['Previously_Insured'].fillna(df1['Previously_Insured'].mean())

df1['Vehicle_Age'].fillna(df1['Vehicle_Age'].mode())

df1['Vehicle_Damage'].fillna(df1['Vehicle_Damage'].mode())

df2['Annual_Premium_inRs'].fillna(df2['Annual_Premium_inRs'].mean())

df2['Sales_Channel_Code'].fillna(df2['Sales_Channel_Code'].mean())

df2['Vintage'].fillna(df2['Vintage'].mean())

df2['Response'].fillna(df2['Response'].mean())

ii)df3=df1.describe()

df3

q1,q3=np.percentile(df3,[25,75])

iqr=q3-q1

outliers_df1=df3[((df3<(q1-1.5*iqr)) | (df3>(q3+1.5*iqr)))]

outliers_df1

print('Count of outliers in df1: \n',outliers_df1.value_counts())

outliers_df1

df4=df2.describe()

q1,q3=np.percentile(df4,[25,75])

iqr=q3-q1

outliers_df2=df4[((df3<(q1-1.5*iqr)) | (df4>(q3+1.5*iqr)))]

outliers_df2

print('Count of outliers in df2: ',outliers_df2.value_counts())

iii) df1.columns=df1.columns.str.replace(' ','')
df2.columns=df2.columns.str.replace(' ','')

iv) df1['Gender']=df1['Gender'].str.upper()
df1['Vehicle_Age']=df1['Vehicle_Age'].str.lower()
df1['Vehicle_Damage']=df1['Vehicle_Damage'].str.upper()

v)df1_dummy=pd.get_dummies(df1,columns=['Gender'])

df1_dummy

vi)df1.drop_duplicates()

df2.drop_duplicates()

3. customer_details=pd.DataFrame(df1)

customer_policy_details=pd.DataFrame(df2)

master_table=pd.merge(customer_details,customer_policy_details,on='Customer_ID')

master_table

4. # i. Gender wise average annual premium
master_table.groupby('Gender').sum()['Annual_Premium_inRs']/master_table.groupby(['Gender']).count()['Annual_Premium_inRs']

# ii. Age wise average annual premium

master_table.groupby(['Age']).sum()['Annual_Premium_inRs']/master_table.groupby(['Age']).count()['Annual_Premium_inRs']

'''iii. Is your data balanced between the genders?

(Hint: Data is balanced if number of counts in each group is approximately same)'''

# NO, data is not balanced

master_table.groupby('Gender').count()

# iv. Vehicle age wise average annual premium.

master_table.groupby(['Vehicle_Age']).sum()['Annual_Premium_inRs']/master_table.groupby(['Vehicle_Age']).count()['Annual_Premium_inRs']

5. coefficient=master_table.corr()

coefficient

# The correlation between age and persons annual premium
if coefficient.iloc[1,5]<(-0.5):
print('Strong Negative Relationship')
elif coefficient.iloc[1,5]>(0.5):
print('Strong Positive Relationship')
else:
print('There is no Relationship')