Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd 

customer_details=pd.read_csv('customer_details.csv')

customer_policy_details=pd.read_csv('customer_policy_details.csv')

customer_details.columns=['customer_id','Gender','age','driving licence present','region code','previously insured','vehicle age','vehicle damage']

customer_policy_details.columns=['customer_id','annual premium (in Rs)','sales channel code','vintage','response']

customer_details_null_values=customer_details.isnull()

customer_details_null_values_summary=customer_details_null_values.sum()

customer_details_null_values_summary

customer_policy_details_null_values=customer_policy_details.isnull()

customer_policy_details_null_values_summary=customer_policy_details_null_values.sum()

customer_policy_details_null_values_summary

customer_details.dropna(subset='customer_id',axis=0,inplace=True)
customer_details

380723 rows × 8 columns

customer_policy_details.dropna(subset='customer_id',axis=0,inplace=True)
customer_policy_details

380722 rows × 5 columns

# in custome_details age contains the only numerical values 
mean_age=int(customer_details['age'].mean())
print(mean_age)

customer_details['age'].fillna(mean_age,inplace=True)

# in custome_policy_details annual premium (in Rs) and vintage contains the numerical values 
customer_policy_details['annual premium (in Rs)'].fillna(customer_policy_details['annual premium (in Rs)'].mean(),inplace=True)

mean_vintage=int(customer_policy_details['vintage'].mean())
print(mean_vintage)

customer_policy_details['vintage'].fillna(mean_vintage,inplace=True)

# in custome_details other than age all are categorical columns
mode_Gender=customer_details['Gender'].mode()[0]

mode_licence_present=customer_details['driving licence present'].mode()[0]

mode_region_code=customer_details['region code'].mode()[0]

mode_previously_insured=customer_details['previously insured'].mode()[0]

mode_vehicle_age=customer_details['vehicle age'].mode()[0]

mode_vehicle_damage=customer_details['vehicle damage'].mode()[0]

customer_details['Gender'].fillna(mode_Gender,inplace=True)

customer_details['driving licence present'].fillna(mode_licence_present,inplace=True)

customer_details['region code'].fillna(mode_region_code,inplace=True)

customer_details['previously insured'].fillna(mode_previously_insured,inplace=True)

customer_details['vehicle age'].fillna(mode_vehicle_age,inplace=True)

customer_details['vehicle damage'].fillna(mode_vehicle_damage,inplace=True)

# in custome_policy_details sales channel code and response are categorical columns
customer_policy_details['sales channel code'].fillna(customer_policy_details['sales channel code'].mode()[0],inplace=True)
customer_policy_details['response'].fillna(customer_policy_details['response'].mode()[0],inplace=True)

# finding outliers range for customer details

Q=customer_details.describe()

Q1=Q.loc['25%']
Q1

Q3=Q.loc['75%']
Q3

IQR=Q3-Q1

IQR

Lower_band=Q1-(1.5*(IQR))
Upper_band=Q3+(1.5*(IQR))
Lower_band

Upper_band

# count the no. of out liers

outliers=customer_details[(customer_details<Lower_band) | (customer_details>Upper_band)]

outlier_count=outliers.count()

for i in outlier_count.index:
    print(f"The outlier for {i} is {outlier_count[i]}")

# it shows the outlier for driving licence present is 811 but as a categorical column it is not consided as a outlier
# We can conclude that , 811 customer don,t have a DL.

import matplotlib.pyplot as plt

plt.boxplot(customer_details['age'])
plt.ylabel('age')
plt.show()

# finding outliers range customer policy details

Qp=customer_policy_details.describe()

Qp1=Qp.loc['25%']
Qp1

Qp3=Qp.loc['75%']
Qp3

IQRp=Qp3-Qp1

IQRp

Lower_bandp=Qp1-(1.5*(IQRp))
Upper_bandp=Qp3+(1.5*(IQRp))
Lower_bandp

Upper_bandp

# count the no. of out liers

outliers_p=customer_policy_details[(customer_policy_details<Lower_bandp) | (customer_policy_details>Upper_bandp)]

outlier_count_p=outliers_p.count()

for j in outlier_count_p.index:
    print(f"The outlier for {j} is {outlier_count_p[j]}")

# In 'response' values = 1.0 is counted as a outlier.
# but response' is a categorical column so that there is no outlier practically.

import matplotlib.pyplot as plt
# for numerical column
plt.boxplot(customer_policy_details['annual premium (in Rs)'])
plt.ylabel('annual premium (in Rs)')
plt.show()

plt.boxplot(customer_policy_details['vintage'])
plt.ylabel('vintage')
plt.show()

# though we have outliers only on the 'annual premium (in Rs)' in customer policy table so we change it with the mean

outlliers_amount=(customer_policy_details[((customer_policy_details['annual premium (in Rs)'])<(Lower_bandp['annual premium (in Rs)'])) | ((customer_policy_details['annual premium (in Rs)'])>(Upper_bandp['annual premium (in Rs)']))])['annual premium (in Rs)']

customer_policy_details.loc[outlliers_amount.index,'annual premium (in Rs)']=customer_policy_details['annual premium (in Rs)'].mean()

customer_details['vehicle age']=customer_details['vehicle age'].str.strip()

customer_details['vehicle age']=customer_details['vehicle age'].str.replace(" ","")

customer_details['vehicle age']

customer_details.columns=['customer_id','gender','age','driving licence present','region code','previously insured','vehicle age','vehicle damage']

def type_of(series):
    for value in series:
        if isinstance(value, str):
            return str


def chang_lower(df):
    for column in df.columns:
        col_type = type_of(df[column])
        if col_type == str:
            df[column] = df[column].str.lower()
    return df

chang_lower(customer_details)

380723 rows × 8 columns

chang_lower(customer_policy_details)

380722 rows × 5 columns

pd.get_dummies(customer_details,columns=['gender','vehicle age','vehicle damage'])

380723 rows × 12 columns

customer_details.drop_duplicates(inplace=True)

customer_policy_details.drop_duplicates(inplace=True)

master_table=pd.merge(customer_details,customer_policy_details,on='customer_id')
master_table

380336 rows × 12 columns

df1=master_table.groupby('gender')
df1['annual premium (in Rs)'].mean()

df2=master_table.groupby('age')
df2['annual premium (in Rs)'].mean()

df3=master_table.groupby('gender')['customer_id'].count()
df3

# No. of male customer higer than female customer but ratio is approximately 1 so we can data is balanced as per gender.

df4=master_table.groupby('vehicle age')['annual premium (in Rs)'].mean()
df4

correlation=master_table['age'].corr(master_table['annual premium (in Rs)'])

print('The correlation between age and annual premium is ',correlation)

# so the correlation between age and annual premium is between 0.5 and -0.5 , so there is no relation between these column

master_table.info()

customer_details.info()

customer_policy_details.info()