# import

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# ================Loading data into files===============================

df1=pd.read_csv("customer_details.csv")

df1  # printing customer details table

381109 rows × 8 columns

df2=pd.read_csv("customer_policy_details.csv")

df2  #printing customer policy details table

381109 rows × 5 columns

# 1...(1)~ Adding coulumn names to customer details table-

df1.columns= ['customer_id','Gender','Age','Driving license present','region code','previously insured','vehicle age','vehicle damage']

df1     # Printing the customer policy table with column names

381109 rows × 8 columns

# 1...(2)~ Adding columns to the customer policy details table

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

df2    # Printing the customer policy table with column names

381109 rows × 5 columns

'''
2) Checking and cleaning data quality

 i) Null Values

    a) ~ Generating the summary of count all the null values column wise for customer details table
'''

df1_null = df1.isnull()
print(' n True = Null values in customer details table,n -------------------- n False = Not Null values in customer details table : n ------------------')
for i in df1_null.columns.values.tolist():
    print(i)
    print("-"*26)
    print(df1_null[i].value_counts())
    print("-"*26)

'''
2)-i) Null Values

      a) ~ Generating the summmary of couunt all the null values column wise for customer-policy-details table
'''
df2_null = df2.isnull()
print(' n True = Null values in customer-policy-details table,n -------------------- n False = Not Null values in customer-policy- details table : n ------------------')
for i in df2_null.columns.values.tolist():
    print(i)
    print("-"*26)
    print(df2_null[i].value_counts())
    print("-"*26)

# 2).i) Null Values
#       b) ~ Dropping Null Values for Customer _id for customer details table


df1.dropna(subset=['customer_id'],inplace=True)

print(' customer details table n Null values in customer_id column after dropping  null values :',df1['customer_id'].isna().sum())

# 2).i) Null Values
#       b) ~ Dropping Null Values for Customer _id in customer-policy-details table

df2.dropna(subset=['customer_id'],inplace=True)

print(' customer-policy-details table n Null values in customer_id column after dropping  null values :',df2['customer_id'].isna().sum())

'''
2).i)Null Values

c) Replace all values for numeric columns by mean 

'''
df1.head()

# Replacing the null values of age by its mean value

df1['Age'].fillna(df1['Age'].mean(),inplace=True)

print(' Null values in Age column after dropping  null values :',df1['Age'].isna().sum())

# Replacing the null values of region code by its mean value

df1['region code'].fillna(df1['region code'].mean(),inplace=True)

print(' Null values in region code column after dropping  null values :',df1['region code'].isna().sum())

df2.head()

# Replacing the null values of annual premium by its mean value

df2['annual premium (in Rs)'].fillna(df2['annual premium (in Rs)'].mean(),inplace=True)

print(' Null values in annual premium (in Rs) column after dropping  null values :',df2['annual premium (in Rs)'].isna().sum())

# Replacing the null values of channel code by its mean value

df2['sales channel code'].fillna(df2['sales channel code'].mean(),inplace=True)

print(' Null values in sales channel code column after dropping  null values :',df2['sales channel code'].isna().sum())

# Replacing the null values of vintage by its mean value

df2['vintage'].fillna(df2['vintage'].mean(),inplace=True)

print(' Null values in vintage column after dropping  null values :',df2['vintage'].isna().sum())

'''
2).i)Null Values

    d) Replacing categorical null values by its mode

'''
# Replacing the null values of driving license present by its mode value

df1['Driving license present'].fillna(df1['Driving license present'].mode()[0],inplace=True)

print(' Null values in Driving license present column after dropping  null values :',df1['Driving license present'].isna().sum())

# Replacing the null values of previously insured by its mode value

df1['previously insured'].fillna(df1['previously insured'].mode()[0],inplace=True)

print(' Null values in previously insured column after dropping  null values :',df1['previously insured'].isna().sum())

# Replacing the null values of vintage by its mode value

df2['vintage'].fillna(df2['vintage'].mode()[0],inplace=True)

print(' Null values in previously insured column after dropping  null values :',df2['vintage'].isna().sum())

'''
2.(ii). OUTLIERS

a) Generate the summary of count of all the null values column wise

'''
df1.describe()

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

df2.describe()

Q1=df2.describe().loc["25%","annual premium (in Rs)"]
Q3=df2.describe().loc["75%","annual premium (in Rs)"]

IQR=Q3-Q1

print('Customer-policy-details table')
print('Outliers present in annual premium (in Rs)  column are :',df2.loc[df2["annual premium (in Rs)"]<(Q1-IQR*1.5),"annual premium (in Rs)"].count())
print('Outliers present in annual premium (in Rs)  column are :',df2.loc[df2["annual premium (in Rs)"]>(Q3+IQR*1.5),"annual premium (in Rs)"].count())

Q1=df2.describe().loc["25%","vintage"]
Q3=df2.describe().loc["75%","vintage"]

IQR=Q3-Q1

print('Customer-policy-details table')
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())

'''
2.(ii).
 
 (b) Replacing the outliers with mean values

'''
# We are replacing the outliers of annual premium (in Rs) column with mean values in customer-policy-details table


mean=df2["annual premium (in Rs)"].mean()

print('Mean of annual premium (in Rs) column values: ',mean)

def replace_outlier(df):
    q1=df.quantile(0.25)
    q3=df.quantile(0.75)
    iqr=q3-q1
    upper=df[~(df>(q3+1.5*iqr))].max()
    lower=df[~(df<(q1-1.5*iqr))].min()
    df=np.where(df>upper,df.mean(),np.where(df<lower,df.mean(),df))
    return df

df2["annual premium (in Rs)"] = replace_outlier(df2["annual premium (in Rs)"])

df2['annual premium (in Rs)']  # Outliers are filled with mean values

'''
2).(iii)

  ~ Remove White spaces
    another way
'''

df1["Gender"]=df1["Gender"].str.strip()
df1["vehicle age"]=df1["vehicle age"].str.strip()
df1["vehicle damage"]=df1["vehicle damage"].str.strip()

'''
2).(iv).

    ~ Case Correction for customer details table

'''

df1["Gender"]=df1["Gender"].str.lower()
df1["vehicle age"]=df1["vehicle age"].str.lower()
df1["vehicle damage"]=df1["vehicle damage"].str.lower()

'''
2).(v).

    ~ Converting nominal data into 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"])

# 2).(vi) Drop duplictates from customer details table

print(df1.duplicated().sum())

# 2).(vi) Drop duplictates from customer-policy-details table

print(df2.duplicated().sum())

df1.drop_duplicates(inplace=True)   #dropping duplicates

df2.drop_duplicates(inplace=True)   #dropping duplicates

# merging

mas_data=pd.merge(df1,df2,on='customer_id')

mas_data

380336 rows × 12 columns

# 4. Getting required data for decisions

# 4....1) Gender wise average annual premium

gwiseanpl=mas_data.groupby(["Gender"])["annual premium (in Rs)"].mean()

gwiseanpl

# 4....2) Age wise average annual premium

agwise=mas_data.groupby(["Age"])["annual premium (in Rs)"].mean()

agwise

# 4....3) Is your data balanced between the genders

mas_data.groupby('Gender').count()

# 4....4) Vehicle age wise average plan

vwiseavp=mas_data.groupby(["vehicle age"])["annual premium (in Rs)"].mean()

vwiseavp

vwiseavp.plot()   # line graph

# 5. Is there any relation between person age and annual premium

n=mas_data['Age'].corr(mas_data["annual premium (in Rs)"])

n

mas_data[['Age','annual premium (in Rs)']].corr()