# Reading Data

import pandas as pd

​

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

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

print(DF1.head())

print(DF2.head())

# 1. Add the column names to both datasets:

# i. Column Name for customer details table:

​

#File1(customer_details)

DF1.columns=['customer_id','gender','age','driving_liscence_present','region_code','previously_insured','vehicle_age','vehicle_damage']

​

# ii. Column Name for customer_policy table:

#File2(customer_policy_details)

DF2.columns=['customer_id','annual_premium','sales_channel_code','vintage','response']

​

# 2. Checking and Cleaning Data Quality:

# 2.i(a)Generate a summary of count of all the null values column wise

​

# File1(customer_details)

print(DF1.isnull().sum())

​

print('*'*50)

​

#File2(customer_policy_details)

print(DF2.isnull().sum())

# 2.i(b)Drop Null values for customer_id because central tendencies for id’s is not feasible.

​

#File1(customer_details)

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

print(DF1.isnull().sum())

​

print('*'*50)

​

#File2(customer_policy_details)

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

print(DF2.isnull().sum())

# 2.i(c)Replace all null values for numeric columns by mean. 

​

#File1(customer_details)

for column in DF1[['age', 'driving_liscence_present', 'region_code','previously_insured']]:

    mean = DF1[column].mean()

    DF1[column] = DF1[column].fillna(mean)

​

print('_'*100)    

​

#File2(customer_policy_details)

for column in DF2[['annual_premium','sales_channel_code','vintage','response']]:

    mean = DF2[column].mean()

    DF2[column] = DF2[column].fillna(mean)

# 2.i(d)Replace all null values for Categorical value by mode.

​

#File1(customer_details)

for column in DF1[['gender', 'vehicle_age', 'vehicle_damage']]:

    mode = DF1[column].mode()

    DF1[column] = DF1[column].fillna(mode)

#2.ii Outliers

# 2.ii(a) Generate a summary of count of all the outliers column wise

​

#File1(customer_details)

DF1['age'].describe()

DF1['driving_liscence_present'].describe()

DF1['previously_insured'].describe()

DF1['region_code'].describe()

​

#File2(customer_policy_details)

DF2['annual_premium'].describe()

DF2['sales_channel_code'].describe()

DF2['vintage'].describe()

DF2['response'].describe()

# 2.ii (b) Replace all outlier values for numeric columns by mean.

# 2.iii. White spaces

# Remove white spaces

​

#File1(customer_details)

DF1.apply(lambda x: x.str.strip() if x.dtype=="object" else x)

​

print('*'*50)

​

#File2(customer_policy_details)

DF2.apply(lambda x: x.str.strip() if x.dtype=="object" else x)

# 2.iv. case correction(lower or upper, any one)  

​

#File1(customer_details)

DF1.apply(lambda x: x.str.lower() if x.dtype == "object" else x)

​

print('*'*100)

#File2(customer_policy_details)

DF2.apply(lambda x: x.str.lower() if x.dtype == "object" else x)

# 2.v. Convert nominal data (categorical) into dummies 

# for future modeling use if required

​

#File1(customer_details)

DF1_dummies = pd.get_dummies(DF1, columns=['gender'])

DF1_dummies.head()

​

#File2(customer_policy_details)

DF2_dummies = pd.get_dummies(DF2, columns=['response'])

DF2_dummies.head()

# 2.vi. Drop Duplicates (duplicated rows)

​

#File1(customer_details)

DF1.drop_duplicates(inplace=True)

​

#File2(customer_policy_details)

DF2.drop_duplicates(inplace=True)

# 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.

​

# (Hint: use pd.merge() function)

​

data=pd.merge(DF1,DF2,on='customer_id')

data

​

# 4. Company needs some important information from the master table to make decisions for future growth.

# They needs following information:

​

# 4.i Gender wise average annual premium

data.groupby('gender')['annual_premium'].mean()

​

# 4.ii. Age wise average annual premium

result = data.groupby('age')['annual_premium'].mean()

import matplotlib.pyplot as pyplot

result.plot()

pyplot.show()

​

# 4.iii. Is your data balanced between the genders?

#           (Hint: Data is balanced if number of counts in each group is approximately same)

print(f"male to female ration is {round(data['gender'].value_counts()[0]/data['gender'].value_counts()[1],2)}")

print(f"generally, the standard is: \n balanced data ratio: {50/50}\n slightly balanced data ratio: {round(55/45,2)}-{60/40} \n imbalanced data ratio: {80/20}-{90/10}")

​

# 4.iv. Vehicle age wise average annual premium.

result1 = data.groupby('vehicle_age')['annual_premium'].mean()

import matplotlib.pyplot as pyplot

result1.plot()

pyplot.show()

# 5.Is there any relation between Person Age and annual premium?

# Hint: use correlation function (Correlation describes the relationship between two variables). 

# Correlation coefficient < -0.5           - Strong negative relationship

# Correlation coefficient > 0.5            -  Strong positive relationship

# 0.5 < Correlation coefficient < 0.5   - There is no relationship. 

​

n = data['age'].corr(data['annual_premium'])

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!")