Project 2 - EDA on Vehicle Insurance Customer Data

project 2: EDA on Vehicle Insurance Customer Data

# Step1 Reading data
import pandas as pd
first_table=pd.read_csv('.pandascustomer_details.csv')
import pandas as pd
second_table=pd.read_csv('.pandascustomer_policy_details.csv')
print(first_table.head())
print(second_table.head())

# giving column names to first_table
first_table_labels={'0':'customer_id','1':'Gender','2':'age','3':'driving licence present','4':'region code','5':'previously insured','6':'vehicle age' ,'7':'vehicle damage'}

#Step 2: Checking and Cleaning Data Quality:
#2.1 Null values
# 1. Generate a summary of count of all the null values column wise of first_table

for i in range(first_table.shape[1]):
print(f"number of cells of {first_table[str(i)]} with null values = {first_table[str(i)].isnull().sum()}")

first_table.info()

#2.1.1.2. droping rows with null customer_id of first_table
first_table.dropna(subset=['0'],inplace=True)
print(f"number of cells of {first_table_labels[str(0)]} with null values = {first_table[str(0)].isnull().sum()}")

#2.1.1.3. replace all null values for numeric columns by mean of first_table
for i in range(2,6):
first_table[str(i)].fillna(first_table[str(i)].mean(),inplace=True)
print(f"number of cells of {first_table[str(i)]} with null values = {first_table[str(i)].isnull().sum()}")

#2.1.1.4 replace all null values for categorical value by mode of first_table
for i in [1,6,7]:
first_table[str(i)].fillna(first_table[str(i)].mode(),inplace=True)
print(f" number of cells of {first_table_labels[str(i)]} with null values = {first_table[str(i)].isnull().sum()}")

#2.1.2 second table
second_table_labels={'0':'customer_id','1':'annual_premium_INR','2':'sales_channel_code','3':'vintage' , '4':'response' }
#2.1.2.i counting the null values of each column of second_table

for i in range(second_table.shape[1]):
print(f"number of cells of {second_table[str(i)]} with null values = {second_table[str(i)].isnull().sum()}")

second_table.info()

#2.1.2.2 dropping rows with null customer id of second table
second_table.dropna(subset=['0'],inplace=True)
print(f"number of cells of {second_table_labels[str(0)]} with null values ={second_table[str(0)].isnull().sum()}")

#2.1.2.3 replacing all null values for numeric by mean of second_table
for i in range(1,5):
second_table[str(i)].fillna(second_table[str(i)].mean(),inplace=True)
print(f"number of cells of {second_table[str(i)]} with null values ={second_table[str(i)].isnull().sum()}")

#Outliers

#2.2.1 first_table
first_table_limits={}
for i in range(2,6):
computations=first_table[str(i)].describe(percentiles=[.25,.75])
mean=computations.values[1] #will get mean of column
Q1=computations.values[4] #25%
Q3=computations.values[6] #75%
IQR=Q3-Q1
lower_limit=Q1- 1.5*IQR
higher_limit=Q3+1.5 * IQR
first_table_limits[str(i)]=(lower_limit,higher_limit)
first_table_limits

#2.2.1.1 generating a summary of count of all outliers column wise of first_table(only for numeric columns)
first_table_outliers={'2':0,'3':0,'4':0,'5':0}
for j in first_table.index:
for i in range(2,6):
if(first_table_limits[str(i)][0]!=first_table_limits[str(i)][1]) and (first_table.loc[j,str(i)] > first_table_limits[str(i)][1] or first_table.loc[j,str(i)]< first_table_limits[str(i)][0]):
first_table_outliers[str(i)]+=1
first_table_outliers

#2.2.1.2 replace all outlier for numeric columns by mean for first_table
for j in first_table.index:
for i in range(2,6):
if first_table.loc[j,str(i)]<first_table_limits[str(i)][0]:
first_table.loc[j,str(i)]=first_table[str(i)].mean()
if first_table.loc[j,str(i)]> first_table_limits[str(i)][1]:
first_table.loc[j,str(i)]=first_table[str(i)].mean()
first_table_outliers

#2.2.2 second table
second_table_limits={}
for i in range(1,5):
computations=second_table[str(i)].describe(percentiles=[.25,.75])
mean=computations.values[1] #will get mean of column
Q1=computations.values[4] #25%
Q3=computations.values[6] #75%
IQR=Q3-Q1
lower_limit=Q1- 1.5*IQR
higher_limit=Q3+1.5 * IQR
second_table_limits[str(i)]=(lower_limit,higher_limit)
second_table_limits

#2.2.1.1 generating a summary of count of all outliers column wise of second_table(only for numeric columns)
second_table_outliers={'1':0,'2':0,'3':0,'4':0}
for j in second_table.index:
for i in range(1,5):
if(second_table_limits[str(i)][0]!=second_table_limits[str(i)][1]) and (second_table.loc[j,str(i)] > second_table_limits[str(i)][1] or second_table.loc[j,str(i)]< second_table_limits[str(i)][0]):
second_table_outliers[str(i)]+=1
second_table_outliers

#2.2.1.2 replace all outlier for numeric columns by mean for second_table
for j in second_table.index:
for i in range(1,5):
if second_table.loc[j,str(i)]<second_table_limits[str(i)][0]:
second_table.loc[j,str(i)]=second_table[str(i)].mean()
if second_table.loc[j,str(i)]> second_table_limits[str(i)][1]:
second_table.loc[j,str(i)]=second_table[str(i)].mean()
second_table_outliers

# Remove whitespace for first_table
first_table.apply(lambda x:x.str.strip() if x.dtype=="object" else x)

#2.3.2 remove white spaces for second_table
second_table.apply(lambda x:x.str.strip() if x.dtype=="object" else x)

#2.4.1 case correctoin for first_table
first_table.apply(lambda x:x.str.upper() if x.dtype=="object" else x)

#2.4.2 case correctoin for second_table
second_table.apply(lambda x:x.str.upper() if x.dtype=="object" else x)

#2.5 Convert nominal data (categorical) into dummies
# for future modeling use if required

#2.6 dropping duplicate rows for first_table and second_tabe
first_table.drop_duplicates(inplace=True)
second_table.drop_duplicates(inplace=True)

# Step3: Creating a master table
Master=pd.merge(first_table,second_table,on='0')
labels={'0':'customer_id','1_x':'Gender','2_x':'age','3_x':'driving licence present','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' }
Master.rename(columns=labels,inplace=True)
Master

#Step4 : getting information
#4.1 Gender wise Average anual premium
result1=Master.groupby('Gender')['annual_premium_INR'].mean()
result1

#4.2 Age wise Average annualpremium
result2=Master.groupby('age')['annual_premium_INR'].mean()
result2

#4.3 is your data balanced between the genders
print(f"male to female ratio is {round(Master['Gender'].value_counts()[0] / Master['Gender'].value_counts()[1],2)}")
print(f"generally the standard ratio is{50/50} and slightly balanced ratio to {round(55/45,2)} _ {60/40} but the imbalanced ratio is {80/40} _ {90/10}")

#4.4 vehicle age wise average annual premium
result3=Master.groupby('vehicle age')['annual_premium_INR'].mean()
result3

# Step5: is there any relation between Person Age and annual premium?
n=Master['age'].corr(Master['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!")