Project 2 - EDA on Vehicle Insurance Customer Data

Q1:- Add the column names to both datasets:

i. Column Name for customer details table:

customer_id,

Gender,

age,

driving licence present,

region code,

previously insured,

vehicle age

and vehicle damage, in respective order.

ii. Column Name for customer_policy table:

customer_id,

annual premium (in Rs),

sales channel code,

vintage and response.

Answer:-

import pandas as pd
import numpy as np

# Load customer details table
customer_details = pd.read_csv('customer_details.csv') # Replace with the actual file path

# Load customer policy table
customer_policy = pd.read_csv('customer_policy.csv') # Replace with the actual file path

# Task 1: Add column names
customer_details.columns = ['customer_id', 'Gender', 'age', 'driving_licence_present', 'region_code',
'previously_insured', 'vehicle_age', 'vehicle_damage']

customer_policy.columns = ['customer_id', 'annual_premium', 'sales_channel_code', 'vintage', 'response']

Q2:-Checking and Cleaning Data Quality:

i. Null values

Generate a summary of count of all the null values column wise
Drop Null values for customer_id because central tendencies for id’s is not feasible.
Replace all null values for numeric columns by mean.
Replace all null values for Categorical value by mode.

ii. Outliers

Generate a summary of count of all the outliers column wise
Replace all outlier values for numeric columns by mean.

(Hint1: for outlier treatment use IQR method as follows:

For example: for a column X calculate Q1 = 25th percentile and Q3 = 75th percentile then IQR = Q3 – Q1 ) then to check outlier, anything lower than a Q1 – 1.5IQR or greater than Q3 + 1.5 IQR would be an outlier

Hint2: For getting percentile value, explore pd.describe() function)

iii. White spaces

Remove white spaces

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

v. Convert nominal data (categorical) into dummies

for future modeling use if required

vi. Drop Duplicates (duplicated rows)

Answer:-

# Checking and Cleaning Data Quality
# i. Null values
null_summary = pd.concat([customer_details.isnull().sum(), customer_policy.isnull().sum()], axis=1, keys=['Customer Details', 'Customer Policy'])
customer_details.dropna(subset=['customer_id'], inplace=True)
customer_details.fillna(customer_details.mean(), inplace=True)
customer_details.fillna(customer_details.mode().iloc[0], inplace=True)

customer_policy.fillna(customer_policy.mean(), inplace=True)
customer_policy.fillna(customer_policy.mode().iloc[0], inplace=True)

# ii. Outliers
def treat_outliers(column):
Q1 = np.percentile(column, 25)
Q3 = np.percentile(column, 75)
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR
column[column < lower_bound] = column.mean()
column[column > upper_bound] = column.mean()

for col in customer_details.select_dtypes(include=np.number).columns:
treat_outliers(customer_details[col])

# iii. White spaces
customer_details = customer_details.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

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

# v. Convert nominal data into dummies
customer_details = pd.get_dummies(customer_details, columns=['Gender'])

# vi. Drop Duplicates
customer_details.drop_duplicates(inplace=True)

Q3:-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.

(Hint: use pd.merge() function)

Answer:-

#Create a Master table

master_table = pd.merge(customer_details, customer_policy, on='customer_id')

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

ii. Age wise average annual premium

iii. Is your data balanced between the genders?

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

iv. Vehicle age wise average annual premium.

Answer:-

#Company needs information
# i. Gender wise average annual premium
gender_avg_premium = master_table.groupby('Gender')['annual_premium'].mean()

# ii. Age wise average annual premium
age_avg_premium = master_table.groupby('age')['annual_premium'].mean()

# iii. Check if data is balanced between genders
gender_balance = master_table['Gender'].value_counts()

# iv. Vehicle age wise average annual premium
vehicle_age_avg_premium = master_table.groupby('vehicle_age')['annual_premium'].mean()

Q5:-Is there any relation between Person Age and annual premium?

Hint: use correlation function (Correlation describes the relationship between two variables).

Correlation coefficient < -0.5 - Strong negative relationship

Correlation coefficient > 0.5 - Strong positive relationship

0.5 < Correlation coefficient < 0.5 - There is no relationship.

Answer:-

# Answer 5: Is there any relation between Person Age and annual premium
correlation_coefficient = master_table['age'].corr(master_table['annual_premium'])

# Display results
print("Task 1:")
print("Customer Details Columns:", customer_details.columns)
print("Customer Policy Columns:", customer_policy.columns)

print("\nTask 2:")
print("Null Values Summary:\n", null_summary)
print("\nOutliers Treated")

print("\nTask 3:")
print("Master Table:\n", master_table)

print("\nTask 4:")
print("i. Gender Wise Average Annual Premium:\n", gender_avg_premium)
print("\nii. Age Wise Average Annual Premium:\n", age_avg_premium)
print("\niii. Gender Balance:\n", gender_balance)
print("\niv. Vehicle Age Wise Average Annual Premium:\n", vehicle_age_avg_premium)

print("\nTask 5:")
print("Correlation Coefficient between Age and Annual Premium:", correlation_coefficient)