Project 2 - EDA on Vehicle Insurance Customer Data

2. Checking and Cleaning Data Quality:

i. Null values¶

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

import pandas as pd
customer_details_df = pd.read_csv(r'C:\Users\Dell\Desktop\customer_details.csv')
customer_policy_details_df = pd.read_csv(r'C:\Users\Dell\Desktop\customer_policy_details.csv')

customer_details_df.columns = ['customer_id','Gender','age','driving licence present','region code','previously insured','vehicle age','vehicle damage']
customer_policy_details_df.columns = ['customer_id','annual premium (in Rs)','sales channel code','vintage','response']

customer_details_df

customer_policy_details_df

print("Count of total NaN values in each columns of customer_details_dataframe: \n",customer_details_df.isnull().sum())

print("Count of total NaN values in each columns of customer_policy_details_dataframe: \n",customer_policy_details_df.isnull().sum())

Drop Null values for customer_id because central tendencies for id’s is not feasible.

customer_details_df.dropna(subset=['customer_id'],inplace=True)

customer_policy_details_df.dropna(subset=['customer_id'],inplace=True)

Replace all null values for numeric columns by mean.

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

customer_policy_details_df['annual premium (in Rs)'].fillna(customer_policy_details_df['annual premium (in Rs)'].mean(),inplace=True)
customer_policy_details_df['vintage'].fillna(customer_policy_details_df['vintage'].mean(),inplace=True)
customer_policy_details_df['sales channel code'].fillna(customer_policy_details_df['sales channel code'].mean(),inplace=True)

Replace all null values for Categorical value by mode.

customer_details_df['Gender'].fillna(customer_details_df['Gender'].mode()[0],inplace=True)
customer_details_df['driving licence present'].fillna(customer_details_df['driving licence present'].mode()[0],inplace=True)
customer_details_df['previously insured'].fillna(customer_details_df['previously insured'].mode()[0],inplace=True)
customer_details_df['vehicle age'].fillna(customer_details_df['vehicle age'].mode()[0],inplace=True)
customer_details_df['vehicle damage'].fillna(customer_details_df['vehicle damage'].mode()[0],inplace=True)

customer_policy_details_df['response'].fillna(customer_policy_details_df['response'].mode()[0],inplace=True)

ii. Outliers

Generate a summary of count of all the outliers column wise

df1_list_columns = ['age','region code']
for columns in df1_list_columns:
q1 = customer_details_df[columns].quantile(0.25)
q2 = customer_details_df[columns].quantile(0.75)
IQR = q2-q1
c = 0
for i in customer_details_df.index:
if customer_details_df.loc[i,columns]<(q1-(1.5*IQR)) or customer_details_df.loc[i,columns]>(q2+(1.5*IQR)):
c+=1
print('column: {} , Count of outlier: {}\n'.format(columns,c))

df2_list_columns = ['annual premium (in Rs)','vintage','sales channel code']
for columns in df2_list_columns:
q1 = customer_policy_details_df[columns].quantile(0.25)
q2 = customer_policy_details_df[columns].quantile(0.75)
IQR = q2-q1
c = 0
for i in customer_policy_details_df.index:
if customer_policy_details_df.loc[i,columns]<(q1-(1.5*IQR)) or customer_policy_details_df.loc[i,columns]>(q2+(1.5*IQR)):
c+=1
print('column: {} , Count of outlier: {}\n'.format(columns,c))

Replace all outlier values for numeric columns by mean.

q1 = customer_policy_details_df['annual premium (in Rs)'].quantile(0.25)
q2 = customer_policy_details_df['annual premium (in Rs)'].quantile(0.75)
IQR = q2-q1
for i in customer_policy_details_df.index:
if customer_policy_details_df.loc[i,'annual premium (in Rs)']<(q1-(1.5*IQR)) or customer_policy_details_df.loc[i,'annual premium (in Rs)']>(q2+(1.5*IQR)):
customer_policy_details_df.replace(customer_policy_details_df.loc[i,'annual premium (in Rs)'],customer_policy_details_df['annual premium (in Rs)'].mean(),inplace=True)

iii. White spaces

Remove white spaces

customer_details_df['Gender'] = customer_details_df['Gender'].str.strip()
customer_details_df['vehicle age'] = customer_details_df['vehicle age'].str.strip()
customer_details_df['vehicle damage'] = customer_details_df['vehicle damage'].str.strip()

iv. case correction(lower or upper, any one)

customer_details_df['Gender'] = customer_details_df['Gender'].str.upper()
customer_details_df['vehicle age'] = customer_details_df['vehicle age'].str.upper()
customer_details_df['vehicle damage'] = customer_details_df['vehicle damage'].str.upper()

v. Convert nominal data (categorical) into dummies

for future modeling use if required

customer_details_df_new = pd.get_dummies(customer_details_df,columns=['Gender','vehicle age','vehicle damage'])

vi. Drop Duplicates (duplicated rows)

customer_details_df_new.drop_duplicates(inplace=True)
customer_policy_details_df.drop_duplicates(inplace=True)

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_df,customer_policy_details_df,on='customer_id')

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']).mean()['annual premium (in Rs)']

iii. Is your data balanced between the genders?

if (customer_details_df_new['Gender_MALE'].sum()==customer_details_df_new['Gender_FEMALE'].sum()):
print('Data is balanced between the genders')
else:
print('Data is not balanced between the genders')

iv. Vehicle age wise average annual premium.

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

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

if(master_table.corr().loc['age','annual premium (in Rs)']>0.5):
print('Strong Positive Relationship')
elif(master_table.corr().loc['age','annual premium (in Rs)']<-0.5):
print('Strong Negative Relationship')
else:
print('No Relationship')