Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

df1 = pd.read_csv("customer_details.csv")
df2 = pd.read_csv("customer_policy_details.csv")

headers = ['customer_id','Gender','age','driving licence present','region code','previously insured','vehicle age','vehicle damage']

df1.columns=headers

df1.head()

df2.columns=['customer_id','annual premium (in Rs)','sales channel code','vintage','responce']

df2.head()

df1_null = df1.isnull()
for i in df1_null.columns.values.tolist():
print(i)
print(df1_null[i].value_counts())
print("")

df2_null = df2.isnull()

for i in df2_null.columns.values.tolist():
print(i)
print(df2_null[i].value_counts())
print("")

df1.dropna(subset=['customer_id'], axis=0,inplace=True)

df1.reset_index(drop = True, inplace = True)

df2.dropna(subset=['customer_id'], axis=0,inplace=True)

df2.reset_index(drop = True, inplace = True)

df1.head()

df2.head()

df1['age'].fillna(df1['age'].mean(),inplace = True)

df1['region code'].fillna(df1['region code'].mean(),inplace = True)

df2.head()

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['driving licence present'].fillna (df1['driving licence present'].mode()[0], inplace=True)

df1['previously insured'].fillna (df1['previously insured'].mode()[0], inplace=True)

df2['responce'].fillna(df2['responce'].mode()[0], inplace=True)

#2.(ii) OUTLIERS
#2.ii.(a). Summary of Count of Outliers
#In customer details table we need to calculate outliers for columns age and regional code.others are non-numerical columns so ignore them

# this is the function to boxplot for easy visualisation of outliers..

def plot_boxplot(df,ft):
df.boxplot(column=[ft])
plt.grid(False)
plt.show()

plot_boxplot(df1,'age')

plot_boxplot(df1,'region code')

def finding_outliers(df):
q1 = df.quantile(0.25)
q3 = df.quantile(0.75)
iqr = q3-q1
outlier = df[((df<(q1-1.5*iqr)) | (df>(q3+1.5*iqr)))]
return outlier
outliers = finding_outliers(df1['age'])
print('Number of outliers :',len(outliers))
print('Maximum outlier value :',outliers.max())
print('Minimum outlier value :',outliers.min())

outliers = finding_outliers(df1['region code'])
print('Number of outliers :',len(outliers))
print('Maximum outlier value :',outliers.max())
print('Minimum outlier value :',outliers.min())

plot_boxplot(df2,'annual premium (in Rs)')

plot_boxplot(df2,'vintage')

outliers = finding_outliers(df2['annual premium (in Rs)'])

print('Number of outliers :',len(outliers))
print('Maximum outlier value :',outliers.max())
print('Minimum outlier value :',outliers.min())

outliers = finding_outliers(df2['sales channel code'])

print('Number of outliers :',len(outliers))
print('Maximum outlier value :',outliers.max())
print('Minimum outlier value :',outliers.min())

outliers = finding_outliers(df2['vintage'])

print('Number of outliers :',len(outliers))
print('Maximum outlier value :',outliers.max())
print('Minimum outlier value :',outliers.min())

#2.ii.(b).Replacing the outliers with mean values
def replace_outlier(df):
q1 = df.quantile(0.25)
q3 = df.quantile(0.75)
iqr = q3-q1
upper = df[~(df>(q3+1.5*iqr))].max()
lower = df[~(df<(q1-1.5*iqr))].min()
df = np.where(df>upper,df.mean(),np.where(df<lower,df.mean(),df))
return df
df2['annual premium (in Rs)'] = replace_outlier(df2['annual premium (in Rs)'])

#2.(iii). Remove white spaces
df1.apply(lambda x: x.str.strip() if x.dtype=='object' else x)

df2.apply(lambda x: x.str.strip() if x.dtype=='object' else x)

df1.apply(lambda x: x.str.upper() if x.dtype=='object' else x)

df2.apply(lambda x: x.str.upper() if x.dtype=='object' else x)

#2.(v). Convert nominal data into dummy variables
#Indicator Variable (or Dummy Variable)
#What is an indicator variable?
#An indicator variable (or dummy variable) is a numerical variable used to label categories. They are called 'dummies' because the numbers themselves don't have inherent meaning.
# Creating dummy variable for vehicle damage

dummy_variable_1 = pd.get_dummies(df1["vehicle damage"])

dummy_variable_1.head()

# Changing the column names for dummy variable

dummy_variable_1.rename(columns={'No':'vehicle-damage-No', 'Yes':'vehicle-damage-Yes'}, inplace=True)

dummy_variable_1.head()

# merge data frame "df1" and "dummy_variable_1"
df1 = pd.concat([df1, dummy_variable_1], axis=1)

# drop original column "vehicle damage" from "df1"
df1.drop("vehicle damage", axis = 1, inplace=True)

#2.(vi). Drop Duplicates
# Dropping duplicates from customer details table

df1.drop_duplicates(inplace=True)

# Dropping duplicates from customer policy details table

df2.drop_duplicates(inplace=True)

#3. Merging the data sets.
master_data = pd.merge(df1,df2,on='customer_id')

master_data

#4. getting required data for decisions
#4.(i). Gender wise average annual premium
gender_data = master_data.groupby('Gender')['annual premium (in Rs)'].mean()
gender_data

# plots a linear graph
gender_data.plot()

gender_data.plot.bar(title='Gender vs average annual premium')

#4.(ii). age wise average annual premium
age_data = master_data.groupby('age')['annual premium (in Rs)'].mean()

age_data

# showing the results on a linear graph
age_data.plot(xlabel="age",ylabel="annual premium(in Rs)",title="Age vs Average annual premium")

age_data.plot.bar()

#4.(iii). Checking Balanced between gender or not..

master_data.groupby('Gender').count()

#4.(iv). Vehicle age wise average annual premium
vehicle_age = master_data.groupby('vehicle age')['annual premium (in Rs)'].mean()
vehicle_age

vehicle_age.plot() # linear graph

vehicle_age.plot.bar() # bar graph

5. Is there any relation between Person Age and annual premium?

# finding the correlation coefficient in pandas

master_data['age'].corr(master_data['annual premium (in Rs)'])
0.0506575892861754
# Matrix form of correlation

master_data[['age','annual premium (in Rs)']].corr()