Project 2 - EDA on Vehicle Insurance Customer Data
Project 2 - EDA on Vehicle Insurance Customer Data
# STEP 1: Reading Data
import pandas
table1 = pandas.read_csv(r'C:\Users\hp\Downloads\customer_details.csv')
table2 = pandas.read_csv(r'C:\Users\hp\Downloads\customer_policy_details.csv')
print(table1.head())
print(" ")
print(table2.head())
# STEP 2: Checking and Cleaning the data
# 2.1 Null values
# 2.1.1 Table1
table1_labels={'0':'customer_id', '1':'gender', '2':'age', '3':'driving_licence_presence',
'4':'region_code', '5':'previously_insured', '6':'vehicle_age', '7':'vehicle_damage'}
# 2.1.1.1 counting the null values of each column of table1
for i in range(table1.shape[1]):
print(f"number of cells of {table1_labels[str(i)]} with null values = {table1[str(i)].isnull().sum()}")
table1.info()
# 2.1.1.2 dropping rows with null customer_id of table1
table1.dropna(subset=['0'],inplace=True)
print(f"number of cells of {table1_labels[str(0)]} with null values = {table1[str(0)].isnull().sum()}")
# 2.1.1.3 replacing all null values for numeric columns by mean of table1
for i in range(2,6):
table1[str(i)].fillna(table1[str(i)].mean(),inplace=True)
print(f"number of cells of {table1_labels[str(i)]} with null values = {table1[str(i)].isnull().sum()}")
# 2.1.1.4 replacing all null values for Categorical value by mode of table1
for i in [1,6,7]:
table1[str(i)].fillna(table1[str(i)].mode()[0],inplace=True)
print(f"number of cells of {table1_labels[str(i)]} with null values = {table1[str(i)].isnull().sum()}")
# 2.1.2 Table2
table2_labels={'0':'customer_id', '1':'annual_premium_INR', '2':'sales_channel_code', '3':'vintage',
'4':'response'}
# 2.1.2.1 counting the null values of each column of table2
for i in range(table2.shape[1]):
print(f"number of cells of {table2_labels[str(i)]} with null values is {table2[str(i)].isnull().sum()}")
table2.info()
# 2.1.2.2 dropping rows with null customer_id of table2
table2.dropna(subset=['0'],inplace=True)
print(f"number of cells of {table1_labels[str(0)]} with null values = {table2[str(0)].isnull().sum()}")
# 2.1.2.3 replacing all null values for numeric columns by mean of table2
for i in range(1,5):
table2[str(i)].fillna(table2[str(i)].mean(),inplace=True)
print(f"number of cells of {table2_labels[str(i)]} with null values = {table2[str(i)].isnull().sum()}")
# 2.2 Outliers
# 2.2.1 Table1
table1_limits = {}
for i in range(2,6):
computations = table1[str(i)].describe(percentiles=[.25, .75])
mean = computations.values[1]
Q1 = computations.values[4] # 25%
Q3 = computations.values[6] # 75%
IQR = Q3-Q1
ll = Q1 - 1.5*IQR # lower limit
hl = Q3 + 1.5*IQR # higher limit
table1_limits[str(i)] = (ll,hl)
table1_limits
# 2.2.1.1 generating a summary of count of all the outliers column wise of table1 (only for numeric columns)
table1_outliers = {'2':0, '3':0, '4':0, '5':0}
for j in table1.index:
for i in range(2,6):
if (table1_limits[str(i)][0]!=table1_limits[str(i)][1]) and (table1.loc[j, str(i)]>table1_limits[str(i)][1] or table1.loc[j, str(i)]<table1_limits[str(i)][0]):
table1_outliers[str(i)]+=1
table1_outliers
# 2.2.1.2 replacing all outlier values for numeric columns by mean for table 1
# though as can be seen above, there are no outliers at all!, anyways the code is following
for j in table1.index:
for i in range(2,6):
if table1.loc[j, str(i)]<table1_limits[str(i)][0]:
table1.loc[j, str(i)]=table1[str(i)].mean()
if table1.loc[j, str(i)]>table1_limits[str(i)][1]:
table1.loc[j, str(i)]=table1[str(i)].mean()
# 2.2.2 Table2
table2_limits = {}
for i in range(1,5):
computations = table2[str(i)].describe(percentiles=[.25, .75])
mean = computations.values[1]
Q1 = computations.values[4] # 25%
Q3 = computations.values[6] # 75%
IQR = Q3-Q1
ll = Q1 - 1.5*IQR # lower limit
hl = Q3 + 1.5*IQR # higher limit
table2_limits[str(i)] = (ll,hl)
table2_limits
# 2.2.2.1 generating a summary of count of all the outliers column wise of table2 (only for numeric columns)
table2_outliers = {'1':0, '2':0, '3':0, '4':0, '5':0}
for j in table2.index:
for i in range(1,5):
if (table2_limits[str(i)][0]!=table2_limits[str(i)][1]) and (table2.loc[j, str(i)]>table2_limits[str(i)][1] or table2.loc[j, str(i)]<table2_limits[str(i)][0]):
table2_outliers[str(i)]+=1
table2_outliers
# 2.2.2.2 replacing all outlier values for numeric columns by mean for table 2
for j in table2.index:
for i in range(1,5):
if table2.loc[j, str(i)]<table2_limits[str(i)][0]:
table2.loc[j, str(i)]=table2[str(i)].mean()
if table2.loc[j, str(i)]>table2_limits[str(i)][1]:
table2.loc[j, str(i)]=table2[str(i)].mean()
# 2.3.1 Remove Whitespaces for table1
table1.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
# 2.3.2 Remove Whitespaces for table2
table2.apply(lambda x: x.str.strip() if x.dtype == "object" else x)
# 2.4.1 case correction for table1
table1.apply(lambda x: x.str.lower() if x.dtype == "object" else x)
# 2.4.2 case correction for table2
table2.apply(lambda x: x.str.lower() if x.dtype == "object" else x)
# 2.5.1 converting categorical data into dummy variables for table1
# 2.5.2 converting categorical data into dummy variables for table2
# 2.6.1 dropping duplicate rows for table1
table1.drop_duplicates(inplace=True)
# 2.6.2 dropping duplicate rows for table2
table2.drop_duplicates(inplace=True)
# STEP3: Creating a master table
data = pandas.merge(table1, table2, on='0',)
labels={'0':'customer_id', '1_x':'gender', '2_x':'age', '3_x':'driving_licence_presence',
'4_x':'region_code', '5':'previously_insured', '6':'vehicle_age', '7':'vehicle_damage',
'1_y':'annual_premium_INR', '2_y':'sales_channel_code', '3_y':'vintage', '4_y':'response'}
data.rename(columns=labels, inplace=True)
data
# STEP4: getting information
# 4.1 Gender wise average annual premium
data.groupby('gender')['annual_premium_INR'].mean()
# 4.2 Age wise average annual premium
result4_2 = data.groupby('age')['annual_premium_INR'].mean()
import matplotlib.pyplot as pyplot
result4_2.plot()
pyplot.show()
# 4.3 Is your data balanced between the genders?
print(f"male to female ration is {round(data['gender'].value_counts()[0]/data['gender'].value_counts()[1],2)}")
print(f"generally, the standard is: \n balanced data ratio: {50/50}\n slightly balanced data ratio: {round(55/45,2)}-{60/40} \n imbalanced data ratio: {80/20}-{90/10}")
# 4.4 Vehicle age wise average annual premium.
result4_4 = data.groupby('vehicle_age')['annual_premium_INR'].mean()
import matplotlib.pyplot as pyplot
result4_4.plot()
pyplot.show()
# STEP5: Is there any relation between Person Age and annual premium?
n = data['age'].corr(data['annual_premium_INR'])
if n<-0.5:
print("Strong negative relationship")
if n>0.5:
print("Strong positive relationship")
if n>-0.5 and n<0.5:
print("There is no relationship!")