import pandas as pd
import numpy as np

Reading .csv file

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

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

1) Add the column names to both datasets:

i. Column Names for customer details table

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

ii. Column Names for customer_policy table

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

2) Checking and Cleaning Data Quality

i. Null values

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

summary_of_null_values_for_customer_details = customer_details.isna().sum()
summary_of_null_values_for_customer_details()

summary_of_null_values_for_customer_details.plot.bar()

summary_of_null_values_for_customer_policy_details = customer_policy_details.isna().sum()
summary_of_null_values_for_customer_policy_details.plot.bar()

null_values_for_customer_policy_details.plot.bar()

Drop Null values for customer_id because central tendencies for id’s is not feasible:

customer_details = customer_details.dropna(subset = ['customer_id'])customer_policy_details = customer_policy_details.dropna(subset = ['customer_id'])

Replace all null values for numeric columns by mean:

customer_details = customer_details.fillna(customer_details.mean(numeric_only = True))

customer_policy_details = customer_policy_details.fillna(customer_policy_details.mean(numeric_only = True))

Replace all null values for Categorical value by mode:

customer_details['Gender'] = customer_details['Gender'].fillna(customer_details.Gender.value_counts().index[0])
customer_details['vehicle age'] = customer_details['vehicle age'].fillna(customer_details['vehicle age'].value_counts().index[0])
customer_details['vehicle damage'] = customer_details['vehicle damage'].fillna(customer_details['vehicle damage'].value_counts().index[0])

customer_details.isna().sum()

customer_details.isna().sum().plot.bar()

customer_policy_details.isna().sum()

customer_policy_details.isna().sum().plot.bar()

ii. Outliers

Generate a summary of count of all the outliers column wise

customer_details.describe()

def find_outliers_IQR(df):   
    """This Method Finds the Outliers present in a DataFrame"""
    q1=df.quantile(0.25)
    q3=df.quantile(0.75)
    IQR=q3-q1
    outliers = df[((df<(q1-1.5*IQR)) | (df>(q3+1.5*IQR)))]
    return outliers

#find outliers for columns in customer_details DataFrame

outliers = find_outliers_IQR(customer_details['customer_id'])
a = outliers.value_counts().sum()
outliers1 = find_outliers_IQR(customer_details['age'])
b = outliers1.value_counts().sum()
outliers2 = find_outliers_IQR(customer_details['driving licence present'])
c = outliers2.value_counts().sum()
outliers3 = find_outliers_IQR(customer_details['region code'])
d = outliers3.value_counts().sum()
outliers4 = find_outliers_IQR(customer_details['previously insured'])
e = outliers4.value_counts().sum()

outliers_in_customer_details = pd.DataFrame([a,b,c,d,e],['customer_id','age','driving licence present','region code','previously insured'])

outliers_in_customer_details()

outliers_in_customer_policy_details = pd.DataFrame([a,b,c,d,e],['customer_id','annual premium (in rs)','sales channel code','vintage','response'])

outliers_in_customer_details.plot.bar()

#find outliers for coulmns in customer_policy_details DataFrame

outliers = find_outliers_IQR(customer_policy_details['customer_id'])
a = outliers.value_counts().sum()
outliers1 = find_outliers_IQR(customer_policy_details['annual premium (in rs)'])
b = outliers1.value_counts().sum()
outliers2 = find_outliers_IQR(customer_policy_details['sales channel code'])
c = outliers2.value_counts().sum()
outliers3 = find_outliers_IQR(customer_policy_details['vintage'])
d = outliers3.value_counts().sum()
outliers4 = find_outliers_IQR(customer_policy_details['response'])
e = outliers4.value_counts().sum()

outliers_in_customer_policy_details = pd.DataFrame([a,b,c,d,e],['customer_id','annual premium (in rs)','sales channel code','vintage','response'])

outliers_in_customer_policy_details()

outliers_in_customer_policy_details.plot.bar()

Replace all outlier values for numeric columns by mean:

def replace_outliers(df): 
    """This Method replaces the Outliers with the mean in a DataFrame"""
    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>    return df

replace_outliers(customer_details['driving licence present'])

replace_outliers(customer_policy_details['annual premium (in rs)'])
replace_outliers(customer_policy_details['response'])

customer_details.describe()

customer_policy_details.describe()

iii. White spaces

customer_details['Gender'] = customer_details['Gender'].str.strip()

iv. case correction(lower or upper, any one

customer_details['Gender'] = customer_details['Gender'].str.upper()
customer_details['Gender']

v. Convert nominal data (categorical) into dummies

gender_dummy =pd.get_dummies(customer_details['Gender'])
gender_dummy()

vehicle_age_dummy =pd.get_dummies(customer_details['vehicle age'])
vehicle_age_dummy()

vi. Drop Duplicates (duplicated rows)

customer_details = customer_details.drop_duplicates()
customer_details()

customer_policy_details = customer_policy_details.drop_duplicates()
customer_policy_details()

3) Create a Master table for future use. Join the customer table and customer_policy table to get a master table using customer_id in both tables

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

master_table()