EDA on Vehicle Insurance Customer Data

Objective:

A company has customer data that contains 8 columns of customer details and another table having name customer_policy data contains the policy details of the customer.

The company intends to offer some discount in premium for certain customers. To do that they ask their Data scientist team to get some information regarding tasks to be performed

LINK TO PROJECT:

http://localhost:8888/notebooks/Documents/python/project2_1676334928.ipynb

import pandas as pd

# loading data from csv file

ds1=pd.read_csv("C:/Users/DELL/OneDrive/Desktop/python\customer_details.csv")

ds2=pd.read_csv("C:/Users/DELL/OneDrive/Desktop/python/customer_policy_details.csv")

# adding column name for tables

#i

ds1.columns=["customer_id","Gender","age","driving licence present","regioncode","previously insured","vehical age","vehical damage, in respective order"]

ds1

#ii

ds2.columns=["customer_id","annual premium","sales channel code","vintage"," response"]

ds2

#checking and cleaning data quality:

# null values

#i.Generate a summary of count of all the null values column wise

ds1.isnull().sum()

ds2.isnull().sum()

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

dsd=ds1.dropna(subset=['customer_id'])
dsd

dsd1=ds2.dropna(subset=['customer_id'])
dsd1

# iii.Replace all null values for numeric columns by mean

dsm= ds1.fillna(ds1.mean(numeric_only=True))

dsm

dsm1= ds2.fillna(ds2.mean(numeric_only=True))

dsm1

#iv. Replace all null values for Categorical value by mode.

dsmode=ds1.fillna(ds1.mode())
dsmode

ds1mode=ds2.fillna(ds2.mode())
ds1mode

# Outliers:

#i.Generate a summary of count of all the outliers column wise

#ii.Replace all outlier values for numeric columns by mean.

ds1.describe()
Q1=ds1.describe().loc["25%","age"]
Q3=ds1.describe().loc["75%","age"]
IQR=Q3-Q1
print(ds1.loc[ds1["age"]<(Q1-IQR*1.5),"age"].count())
print(ds1.loc[ds1["age"]>(Q1+IQR*1.5),"age"].count())

ds2.describe()
Q1=ds2.describe().loc["25%","annual premium"]
Q3=ds2.describe().loc["75%","annual premium"]
IQR=Q3-Q1
print(ds2.loc[ds2["annual premium"]<(Q1-IQR*1.5),"annual premium"].count())
print(ds2.loc[ds2["annual premium"]>(Q1+IQR*1.5),"annual premium"].count())
ds2.loc[ds2["annual premium"]>(Q3+IQR*1.5),"annual premium"]=ds2["annual premium"].mean()
print(ds2.loc[ds2["annual premium"]>(Q3+IQR*1.5),"annual premium"].count())

#iii.White spaces

ds1.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
ds2.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

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

ds1.apply(lambda x: x.str.lower() if x.dtype == "object" else x)

ds2.apply(lambda x: x.str.lower() if x.dtype == "object" else x)

ds1.apply(lambda x: x.str.upper() if x.dtype == "object" else x)

ds2.apply(lambda x: x.str.upper() if x.dtype == "object" else x)

#v.Convert nominal data (categorical) into dummies

dummies_gender = pd.get_dummies(ds1["Gender"])
dummies_dlp = pd.get_dummies(ds1["driving licence present"])
dummies_rc = pd.get_dummies(ds1["regioncode"])
dummies_pi = pd.get_dummies(ds1["previously insured"])
dummies_va = pd.get_dummies(ds1["vehical age"])
dummies_vd = pd.get_dummies(ds1["vehical damage, in respective order"])

dummies_scc = pd.get_dummies(ds2["sales channel code"])
dummies_vintage = pd.get_dummies(ds2["vintage"])

#vi. Drop Duplicates (duplicated rows)

ds1.drop_duplicates(inplace=True)
ds1

ds2.drop_duplicates(inplace=True)
ds2

#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.

print(ds1.duplicated().sum())
print(ds2.duplicated().sum())

master_df = pd.merge(ds1,ds2,on='customer_id')
master_df

#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

gap_gender_df = master_df.groupby(["Gender"])["annual premium"].mean()
print(gap_gender_df)

#ii. Age wise average annual premium

gap_age_df = master_df.groupby(["age"])["annual premium"].mean()
print(gap_age_df)

#iii. Is your data balanced between the genders?

print(f"Male to Female ratio is{round(master_df['Gender'].value_counts()[0]/master_df['Gender'].value_counts()[1],2)}")
print(f"The standard is:\nBalanced data ratio:{50/50}\nSlightly balanced ratio:{round(55/45,2)}-{60/40}\nImbalanced data ratio:{80/20}")

#iv. Vehicle age wise average annual premium.

gap_v_age_df= master_df.groupby(["vehical age"])["annual premium"].mean()
print(gap_v_age_df)

#Is there any relation between Person Age and annual premium

n= master_df['age'].corr(master_df['annual premium'])
if n<-0.5:
print("negitive relationship")
if n>0.5:
print("positive relationship")
if n>-0.5 and n<0.5:
print("No Relationship")