Project 2 - EDA on Vehicle Insurance Customer Data

import pandas
table1 = pandas.read_csv("customer_details.csv")
table2 = pandas.read_csv("customer_policy_details.csv")
print(table1.head())
print(table2.head())
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'}

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()

table1.dropna(subset=['0'],inplace=True)
print(f"number of cells of {table1_labels[str(0)]} with null values = {table1[str(0)].isnull().sum()}")

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()}")

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()}")

table2_labels={'0':'customer_id', '1':'annual_premium_INR', '2':'sales_channel_code', '3':'vintage',
'4':'response'}

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()

table2.dropna(subset=['0'],inplace=True)
print(f"number of cells of {table1_labels[str(0)]} with null values = {table2[str(0)].isnull().sum()}")
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()}")

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
hl = Q3 + 1.5*IQR
table1_limits[str(i)] = (ll,hl)
table1_limits

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

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()

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
hl = Q3 + 1.5*IQR
table2_limits[str(i)] = (ll,hl)
table2_limits

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
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()
table1.apply(lambda x: x.str.strip() if x.dtype == "object" else x)

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

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

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

table1.drop_duplicates(inplace=True)

table2.drop_duplicates(inplace=True)

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

data.groupby('gender')['annual_premium_INR'].mean()

result4_2 = data.groupby('age')['annual_premium_INR'].mean()
import matplotlib.pyplot as pyplot
result4_2.plot()
pyplot.show()

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}")

result4_4 = data.groupby('vehicle_age')['annual_premium_INR'].mean()
import matplotlib.pyplot as pyplot
result4_4.plot()
pyplot.show()

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!")