Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd

import numpy as np

# Load customer details and customer policy data

customer_details_path = "/mnt/data/customer_details.csv"

customer_policy_path = "/mnt/data/customer_policy_details.csv"

customer_details = pd.read_csv(customer_details_path, header=None)

customer_policy = pd.read_csv(customer_policy_path, header=None)

# 1. Adding 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"

]

# 2. Data Cleaning

## i. Handling Null Values

# Drop rows where customer_id is null

customer_details.dropna(subset=["customer_id"], inplace=True)

customer_policy.dropna(subset=["customer_id"], inplace=True)

# Replace null values in numeric columns with mean

numeric_cols_customer = ["age", "region_code"]

numeric_cols_policy = ["annual_premium", "sales_channel_code", "vintage"]

for col in numeric_cols_customer:

customer_details[col].fillna(customer_details[col].mean(), inplace=True)

for col in numeric_cols_policy:

customer_policy[col].fillna(customer_policy[col].mean(), inplace=True)

# Replace null values in categorical columns with mode

categorical_cols_customer = ["Gender", "vehicle_age", "vehicle_damage"]

categorical_cols_policy = ["response"]

for col in categorical_cols_customer:

customer_details[col].fillna(customer_details[col].mode()[0], inplace=True)

for col in categorical_cols_policy:

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

## ii. Handling Outliers using IQR Method

def replace_outliers_with_mean(df, column):

Q1 = df[column].quantile(0.25)

Q3 = df[column].quantile(0.75)

IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR

upper_bound = Q3 + 1.5 * IQR

mean_value = df[column].mean()

df[column] = np.where((df[column] < lower_bound) | (df[column] > upper_bound), mean_value, df[column])

for col in numeric_cols_customer:

replace_outliers_with_mean(customer_details, col)

for col in numeric_cols_policy:

replace_outliers_with_mean(customer_policy, col)

## iii. Removing White Spaces

customer_details = customer_details.applymap(lambda x: x.strip() if isinstance(x, str) else x)

customer_policy = customer_policy.applymap(lambda x: x.strip() if isinstance(x, str) else x)

## iv. Case Correction (Converting to Lowercase)

customer_details = customer_details.applymap(lambda x: x.lower() if isinstance(x, str) else x)

customer_policy = customer_policy.applymap(lambda x: x.lower() if isinstance(x, str) else x)

## v. Convert Categorical Columns to Dummies

customer_details = pd.get_dummies(customer_details, columns=categorical_cols_customer, drop_first=True)

customer_policy = pd.get_dummies(customer_policy, columns=categorical_cols_policy, drop_first=True)

## vi. Dropping Duplicates

customer_details.drop_duplicates(inplace=True)

customer_policy.drop_duplicates(inplace=True)

# 3. Create Master Table by merging datasets

master_table = pd.merge(customer_details, customer_policy, on="customer_id", how="inner")

# Restoring Gender column

master_table["Gender"] = master_table.apply(lambda row: "male" if row["Gender_male"] == 1 else "female", axis=1)

# 4. Company Insights

## i. Gender wise average annual premium

gender_wise_premium = master_table.groupby("Gender")["annual_premium"].mean()

## ii. Age wise average annual premium

age_wise_premium = master_table.groupby("age")["annual_premium"].mean()

## iii. Checking data balance between genders

gender_balance = master_table["Gender"].value_counts(normalize=True)

## iv. Vehicle age wise average annual premium

vehicle_age_wise_premium = master_table.groupby("vehicle_age_< 1 year")["annual_premium"].mean()

# 5. Correlation between Age and Annual Premium

correlation = master_table["age"].corr(master_table["annual_premium"])

# Save results

gender_wise_premium.to_csv("/mnt/data/Gender_Wise_Premium.csv")

age_wise_premium.to_csv("/mnt/data/Age_Wise_Premium.csv")

gender_balance.to_csv("/mnt/data/Gender_Balance.csv")

vehicle_age_wise_premium.to_csv("/mnt/data/Vehicle_Age_Wise_Premium.csv")

master_table.to_csv("/mnt/data/Master_Table.csv", index=False)

# Print correlation result

print("Correlation between Age and Annual Premium:", correlation)