Project 2 - EDA on Vehicle Insurance Customer Data

#1 1. Add the column names to both datasets:

i. Column Name for customer details table:
import pandas as pd

# read the sales.csv file and create the DataFrame3
DataFrame3 = pd.read_csv('customer_details.csv', header=None, names=['customer_id', 'Gender', 'age', 'driving licence present',
                                         'region code', 'previously insured', 'vehicle age', 'vehicle damage'])

# Customer details dataset
customer_details = pd.DataFrame(DataFrame3)
print(customer_details)


#ii. Column Name for customer_policy table:
# Read the customer_policy table
DataFrame4 = pd.read_csv('customer_policy_details.csv',header = None,names=['customer_id', 'annual_premium', 'sales_channel_code', 'vintage', 'response'])

# Assign column names
customer_policy = pd.DataFrame(DataFrame4)
print(customer_policy)

#2. Checking and Cleaning Data Quality:

#i. Null values
# Check for null values in the customer details table
null_counts1 = customer_details.isnull().sum()
print(null_counts1)


# Check for null values in the customer_policy table
null_counts2 = customer_policy.isnull().sum()
print(null_counts2)


# Drop null values for the customer_id column in the customer details table
print(customer_details.dropna(subset=['customer_id']))



# Replace null values for numeric columns in the customer details table with mean
customer_details.fillna(customer_details.mean())



# Replace null values for categorical columns in the customer details table with mode
print(customer_details.fillna(customer_details.mode().iloc[0]))


# Read the customer_policy table
DataFrame4 = pd.read_csv('customer_policy_details.csv',header = None,names=['customer_id', 'annual_premium', 'sales_channel_code', 'vintage', 'response'])

# Assign column names
customer_policy = pd.DataFrame(DataFrame4)
print(customer_policy)


# Replace null values for numeric columns in the customer_policy table with mean
print(customer_policy.fillna(customer_policy.mean()))


# Replace null values for categorical columns in the customer_policy table with mode
print(customer_policy.fillna(customer_policy.mode().iloc[0]))


# Calculate the IQR for numeric columns in the customer details table
q1 = customer_details.quantile(0.25)
q3 = customer_details.quantile(0.75)
iqr = q3 - q1
print(iqr)


# Find outliers using IQR method
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr
outliers = ((customer_details < lower_bound) | (customer_details > upper_bound)).sum()
print(outliers)


# Replace outliers in numeric columns with mean
customer_details = customer_details.mask((customer_details < lower_bound) | (customer_details > upper_bound), customer_details.mean(),axis = 0)
print(customer_details)


# Calculate the IQR for numeric columns in the customer_policy table
q1_policy = customer_policy.quantile(0.25)
q3_policy = customer_policy.quantile(0.75)
iqr_policy = q3_policy - q1_policy
print(iqr_policy)


#ii. Outliers#
# Find outliers using IQR method
lower_bound_policy = q1_policy - 1.5 * iqr_policy
upper_bound_policy = q3_policy + 1.5 * iqr_policy
outliers_policy = ((customer_policy < lower_bound_policy) | (customer_policy > upper_bound_policy)).sum()
print(outliers_policy)


# Replace outliers in numeric columns with mean
customer_policy = customer_policy.mask((customer_policy < lower_bound_policy) | (customer_policy > upper_bound_policy), customer_policy.mean(),axis = 0)
print(customer_policy)


# Remove white spaces in customer_details table
customer_details = customer_details.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
# Remove white spaces in customer_policy table
customer_policy = customer_policy.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
print(customer_details)
print(customer_policy)


# Convert all columns to lowercase in customer_details table
lower_cd = customer_details.apply(lambda x: x.str.lower() if x.dtype == "object" else x)
# Convert all columns to lowercase in customer_policy table
lower_cp = customer_policy.apply(lambda x: x.str.lower() if x.dtype == "object" else x)
print(lower_cd)
print(lower_cp)


# Convert nominal columns into dummy variables in customer_details table
dummy_cd = pd.get_dummies(customer_details, columns=['Gender'])
# Convert nominal columns into dummy variables in customer_policy table
dummy_cp = pd.get_dummies(customer_policy, columns=['response'])
print(dummy_cd)
print(dummy_cp)