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_lecence_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(i)]}with null values={table1[str(i)].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={table2[str(i)].isnull().sum()}")
table2.info()
table2.dropna(subset=['0'],inplace=True)
print(f"number of cells of {table1_labels[str(i)]}with null values={table1[str(i)].isnull().sum()}")
for i in range(1,5):
table2[str(i)].fillna(table2[str(i)].mean(),inplace=True)
print(f"number of cells of {table1_labels[str(i)]}with null values={table1[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]
Q3=computations.values[6]
lQR=Q3-Q1
ll=Q1-1.5*lQR
hl=Q3+1.5*lQR
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]
Q3=computations.values[6]
lQR=Q3-Q1
ll=Q1-1.5*lQR
hl=Q3+1.5*lQR
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':'gender','2':'age','3':'driving_lecence_presence','4':'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