Project 2 - EDA on Vehicle Insurance Customer Data

import pandas as pd
#read file
df1 = pd.read_csv("customer_details.csv")
df2 = pd.read_csv("customer_policy_details.csv")
#Add column name
df1.columns=['customer_id','gender','age','driving license present','region code','previously insured','vehicle age','vehicle damage']
df2.columns=['customer_id','annual premium (in Rs)','sales channel code', 'vintage', 'response']
#count of null values
df1_null = df1.isnull()
for i in df1_null.columns.values.tolist():
    print (i)
    print (df1_null[i].value_counts())
df2_null = df2.isnull()
for i in df2_null.columns.values.tolist():
    print (i)
    print(df2_null[i].value_counts())
    
#drop details customer Id if null
df1.dropna(subset['customer_id'],inplace = True)
df2.dropna(subset['customer_id'],inplace = True)

#null fillna as mean
df1.['age'].fillna(df1.['age'].mean(), inplace = True)  
df1.['region'].fillna(df1.['region'].mean(), inplace = True)
df2.['annual premium (in Rs)'].fillna(df2.['annual premium (in Rs)'].mean(), inplace = True)
df2.['sales channel code'].fillna(df2.['sales channel code'].mean(), inplace = True)
df2.['vintage'].fillna(df2.['vintage'].mean(), inplace = True)
#null fill as mode
df1.['driving license present'].fillna(df1.['driving license present'].mode()[0], inplace = True)
df1.['previously insured'].fillna(df1.['previously insured'].mode()[0], inplace = True)
#outliners
Q1=df1.describe().loc["25%","Age"]
Q3=df1.describe().loc["75%","Age"]
IQR=Q3-Q1
print('Customer details table')
print('Outliers present in age column are :',df1.loc[df1["Age"]<(Q1-IQR*1.5),"Age"].count())
print('Outliers present in age column are :',df1.loc[df1["Age"]>(Q3+IQR*1.5),"Age"].count())
Q1=df1.describe().loc["25%","region code"]
Q3=df1.describe().loc["75%","region code"]
IQR=Q3-Q1
print('Customer details table')
print('Outliers present in region code column are :',df1.loc[df1["region code"]<(Q1-IQR*1.5),"region code"].count())
print('Outliers present in region code column are :',df1.loc[df1["region code"]>(Q3+IQR*1.5),"region code"].count())
Q1=df2.describe().loc["25%","annual premium (in Rs)"]
Q3=df2.describe().loc["75%","annual premium (in Rs)"]
IQR=Q3-Q1
print('Customer policy detail')
print('Outliers present in annual premium (in Rs) column are :',df1.loc[df1["Ageannual premium (in Rs) "]<(Q1-IQR*1.5),"annual premium (in Rs) "].count())
print('Outliers present in annual premium (in Rs)  column are :',df1.loc[df1["annual premium (in Rs) "]>(Q3+IQR*1.5),"annual premium (in Rs) "].count())
Q1=df2.describe().loc["25%","sales channel code"]
Q3=df2.describe().loc["75%","sales channel code"]
IQR=Q3-Q1
print('Customer policy detail ')
print('Outliers present in sales channel code column are :',df2.loc[df2["sales channel code"]<(Q1-IQR*1.5),"sales channel code"].count())
print('Outliers present in sales channel code column are :',df2.loc[df2["sales channel code"]>(Q3+IQR*1.5),"sales channel code"].count())
Q1=df2.describe().loc["25%","vintage"]
Q3=df2.describe().loc["75%","vintage"]
IQR=Q3-Q1
print('Customer policy detail ')
print('Outliers present in vintage column are :',df2.loc[df2["vintage"]<(Q1-IQR*1.5),"vintage"].count())
print('Outliers present in vintage column are :',df2.loc[df2["vintage"]>(Q3+IQR*1.5),"vintage"].count())

# Replacing the outliers with mean values
mean = df2["annual premium (in Rs)"].mean()
def replace_outlier(df):
    q1 = df.quantile(0.25)
    q3 = df.quantile(0.75)
    iqr = q3 - q1
    upper = q3 + 1.5 * iqr
    lower = q1 - 1.5 * iqr
  
    df = df.apply(lambda x: mean if x > upper or x < lower else x)
    return df

df2["annual premium (in Rs)"] = replace_outlier(df2["annual premium (in Rs)"])
#remove whitespaces
df1["gender"]=df1["gender"].str.strip()
df1["vehicle age"]=df1["vehicle age"].str.strip()
df1["vehicle damage"]=df1["vehicle damage"].str.strip()

#case correction
df1["Gender"]=df1["Gender"].str.lower()
df1["vehicle age"]=df1["vehicle age"].str.upper()
df1["vehicle damage"]=df1["vehicle damage"].str.lower()

#dummies
dummies_gender=pd.get_dummies(df1["Gender"])
dummies_vehicle_age=pd.get_dummies(df1["vehicle age"])
dummies_vehicle_damage=pd.get_dummies(df1["vehicle damage"])
dummies_dlp=pd.get_dummies(df1["Driving license present"])
dummies_pre=pd.get_dummies(df1["previously insured"])

# drop duplicates
df1.drop_duplicates(inplace=True)   
df2.drop_duplicates(inplace=True)

#create master table
master_table=pd.merge(df1,df2,on='customer_id')

#genderwise annual premium
gender_annual_premium=master_table.gropuby('gender')['annual premium (in Rs)'].mean()
print(gender_annual_premium)
#agewise annual premium
age_annual_premium=master_table.groupby('age')['annual premium (in Rs)'].mean()
print(age_annual_premium)

#data balanced
gender_counts=master_table['Gender'].value_counts()
if gender_counts['male'] == gender_counts['female']:
    print("The data is balanced between genders")
else:
    print("The data is imbalanced between genders")


#vechicaal age annual premium
gender_annual_premium=master_table.gropuby('vehicle age')['annual premium (in Rs)'].mean()
print(gender_annual_premium)

#corelation
n=master_data['Age'].corr(mas_data["annual premium (in Rs)"])
print(n)
if(n<-0.5)
    print("Strong Negative Relationship")
else if(n>0.5)
    print("Strong Positive Relationship")
else
    print("No Relationship")