Project 2 - EDA on Vehicle Insurance Customer Data
{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"id": "d1498c43",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
" 0 1 2 3 4 5 6 7\n",
"0 1.0 Male 44.0 1.0 28.0 0.0 > 2 Years Yes\n",
"1 2.0 Male 76.0 1.0 3.0 0.0 1-2 Year No\n",
"2 3.0 Male 47.0 1.0 28.0 0.0 > 2 Years Yes\n",
"3 4.0 Male 21.0 1.0 11.0 1.0 < 1 Year No\n",
"4 5.0 Female 29.0 1.0 41.0 1.0 < 1 Year No\n",
" 0 1 2 3 4\n",
"0 1.0 40454.0 26.0 217.0 1.0\n",
"1 2.0 33536.0 26.0 183.0 0.0\n",
"2 3.0 38294.0 26.0 27.0 1.0\n",
"3 4.0 28619.0 152.0 203.0 0.0\n",
"4 5.0 27496.0 152.0 39.0 0.0\n",
"number of cells of customer_id with null values = 386\n",
"number of cells of gender with null values = 368\n",
"number of cells of age with null values = 368\n",
"number of cells of driving_licence_presence with null values = 393\n",
"number of cells of region_code with null values = 392\n",
"number of cells of previously_insured with null values = 381\n",
"number of cells of vehicle_age with null values = 381\n",
"number of cells of vehicle_damage with null values = 407\n",
"<class 'pandas.core.frame.DataFrame'>\n",
"RangeIndex: 381109 entries, 0 to 381108\n",
"Data columns (total 8 columns):\n",
" # Column Non-Null Count Dtype \n",
"--- ------ -------------- ----- \n",
" 0 0 380723 non-null float64\n",
" 1 1 380741 non-null object \n",
" 2 2 380741 non-null float64\n",
" 3 3 380716 non-null float64\n",
" 4 4 380717 non-null float64\n",
" 5 5 380728 non-null float64\n",
" 6 6 380728 non-null object \n",
" 7 7 380702 non-null object \n",
"dtypes: float64(5), object(3)\n",
"memory usage: 23.3+ MB\n",
"number of cells of customer_id with null values = 0\n",
"number of cells of age with null values = 0\n",
"number of cells of driving_licence_presence with null values = 0\n",
"number of cells of region_code with null values = 0\n",
"number of cells of previously_insured with null values = 0\n",
"number of cells of gender with null values = 0\n",
"number of cells of vehicle_age with null values = 0\n",
"number of cells of vehicle_damage with null values = 0\n",
"number of cells of customer_id with null values is 387\n",
"number of cells of annual_premium_INR with null values is 346\n",
"number of cells of sales_channel_code with null values is 400\n",
"number of cells of vintage with null values is 388\n",
"number of cells of response with null values is 361\n",
"<class 'pandas.core.frame.DataFrame'>\n",
"RangeIndex: 381109 entries, 0 to 381108\n",
"Data columns (total 5 columns):\n",
" # Column Non-Null Count Dtype \n",
"--- ------ -------------- ----- \n",
" 0 0 380722 non-null float64\n",
" 1 1 380763 non-null float64\n",
" 2 2 380709 non-null float64\n",
" 3 3 380721 non-null float64\n",
" 4 4 380748 non-null float64\n",
"dtypes: float64(5)\n",
"memory usage: 14.5 MB\n",
"number of cells of customer_id with null values = 0\n",
"number of cells of annual_premium_INR with null values = 0\n",
"number of cells of sales_channel_code with null values = 0\n",
"number of cells of vintage with null values = 0\n",
"number of cells of response with null values = 0\n"
]
}
],
"source": [
"# STEP 1: Reading Data\n",
"import pandas\n",
"table1 = pandas.read_csv(\"customer_details.csv\")\n",
"table2 = pandas.read_csv(\"customer_policy_details.csv\")\n",
"print(table1.head())\n",
"print(table2.head())\n",
" \n",
"# STEP 2: Checking and Cleaning the data\n",
"# 2.1 Null values\n",
"\n",
"# 2.1.1 Table1\n",
"table1_labels={'0':'customer_id', '1':'gender', '2':'age', '3':'driving_licence_presence',\n",
"'4':'region_code', '5':'previously_insured', '6':'vehicle_age', '7':'vehicle_damage'}\n",
"\n",
"# 2.1.1.1 counting the null values of each column of table1\n",
"for i in range(table1.shape[1]):\n",
" print(f\"number of cells of {table1_labels[str(i)]} with null values = {table1[str(i)].isnull().sum()}\")\n",
"\n",
"table1.info()\n",
" \n",
"# 2.1.1.2 dropping rows with null customer_id of table1\n",
"table1.dropna(subset=['0'],inplace=True)\n",
"print(f\"number of cells of {table1_labels[str(0)]} with null values = {table1[str(0)].isnull().sum()}\")\n",
" \n",
"# 2.1.1.3 replacing all null values for numeric columns by mean of table1\n",
"for i in range(2,6):\n",
" table1[str(i)].fillna(table1[str(i)].mean(),inplace=True)\n",
" print(f\"number of cells of {table1_labels[str(i)]} with null values = {table1[str(i)].isnull().sum()}\")\n",
"\n",
"# 2.1.1.4 replacing all null values for Categorical value by mode of table1\n",
"for i in [1,6,7]:\n",
" table1[str(i)].fillna(table1[str(i)].mode()[0],inplace=True)\n",
" print(f\"number of cells of {table1_labels[str(i)]} with null values = {table1[str(i)].isnull().sum()}\")\n",
" \n",
"# 2.1.2 Table2\n",
"table2_labels={'0':'customer_id', '1':'annual_premium_INR', '2':'sales_channel_code', '3':'vintage',\n",
"'4':'response'}\n",
"\n",
"# 2.1.2.1 counting the null values of each column of table2\n",
"for i in range(table2.shape[1]):\n",
" print(f\"number of cells of {table2_labels[str(i)]} with null values is {table2[str(i)].isnull().sum()}\")\n",
"\n",
"table2.info()\n",
" \n",
"# 2.1.2.2 dropping rows with null customer_id of table2\n",
"table2.dropna(subset=['0'],inplace=True)\n",
"print(f\"number of cells of {table1_labels[str(0)]} with null values = {table2[str(0)].isnull().sum()}\")\n",
" \n",
"# 2.1.2.3 replacing all null values for numeric columns by mean of table2\n",
"for i in range(1,5):\n",
" table2[str(i)].fillna(table2[str(i)].mean(),inplace=True)\n",
" print(f\"number of cells of {table2_labels[str(i)]} with null values = {table2[str(i)].isnull().sum()}\")\n",
" \n",
"# 2.2 Outliers\n",
"\n",
"# 2.2.1 Table1\n",
"\n",
"table1_limits = {}\n",
"for i in range(2,6):\n",
" computations = table1[str(i)].describe(percentiles=[.25, .75])\n",
" mean = computations.values[1]\n",
" Q1 = computations.values[4] # 25%\n",
" Q3 = computations.values[6] # 75%\n",
" IQR = Q3-Q1\n",
" ll = Q1 - 1.5*IQR # lower limit\n",
" hl = Q3 + 1.5*IQR # higher limit\n",
" table1_limits[str(i)] = (ll,hl)\n",
"table1_limits\n",
" \n",
"# 2.2.1.1 generating a summary of count of all the outliers column wise of table1 (only for numeric columns)\n",
"table1_outliers = {'2':0, '3':0, '4':0, '5':0}\n",
"\n",
"for j in table1.index:\n",
" for i in range(2,6):\n",
" if (table1_limits[str(i)][0]!=table1_limits[str(i)][1]) and (table1.loc[j, str(i)]>table1_limits[str(i)][1] or table1.loc[j, str(i)]<table1_limits[str(i)][0]):\n",
" table1_outliers[str(i)]+=1\n",
"\n",
"table1_outliers\n",
" \n",
"# 2.2.1.2 replacing all outlier values for numeric columns by mean for table 1\n",
"# though as can be seen above, there are no outliers at all!, anyways the code is following\n",
"for j in table1.index:\n",
" for i in range(2,6):\n",
" if table1.loc[j, str(i)]<table1_limits[str(i)][0]:\n",
" table1.loc[j, str(i)]=table1[str(i)].mean()\n",
" if table1.loc[j, str(i)]>table1_limits[str(i)][1]:\n",
" table1.loc[j, str(i)]=table1[str(i)].mean()\n",
" \n",
"# 2.2.2 Table2\n",
"table2_limits = {}\n",
"\n",
"for i in range(1,5):\n",
" computations = table2[str(i)].describe(percentiles=[.25, .75])\n",
" mean = computations.values[1]\n",
" Q1 = computations.values[4] # 25%\n",
" Q3 = computations.values[6] # 75%\n",
" IQR = Q3-Q1\n",
" ll = Q1 - 1.5*IQR # lower limit\n",
" hl = Q3 + 1.5*IQR # higher limit\n",
" table2_limits[str(i)] = (ll,hl)\n",
"table2_limits\n",
" \n",
"# 2.2.2.1 generating a summary of count of all the outliers column wise of table2 (only for numeric columns)\n",
"table2_outliers = {'1':0, '2':0, '3':0, '4':0, '5':0}\n",
"\n",
"for j in table2.index:\n",
" for i in range(1,5):\n",
" if (table2_limits[str(i)][0]!=table2_limits[str(i)][1]) and (table2.loc[j, str(i)]>table2_limits[str(i)][1] or table2.loc[j, str(i)]<table2_limits[str(i)][0]):\n",
" table2_outliers[str(i)]+=1\n",
"\n",
"table2_outliers\n",
" \n",
"# 2.2.2.2 replacing all outlier values for numeric columns by mean for table 2\n",
"for j in table2.index:\n",
" for i in range(1,5):\n",
" if table2.loc[j, str(i)]<table2_limits[str(i)][0]:\n",
" table2.loc[j, str(i)]=table2[str(i)].mean()\n",
" if table2.loc[j, str(i)]>table2_limits[str(i)][1]:\n",
" table2.loc[j, str(i)]=table2[str(i)].mean()\n",
" \n",
"# 2.3.1 Remove Whitespaces for table1\n",
"table1.apply(lambda x: x.str.strip() if x.dtype == \"object\" else x)\n",
" \n",
"# 2.3.2 Remove Whitespaces for table2\n",
"table2.apply(lambda x: x.str.strip() if x.dtype == \"object\" else x)\n",
" \n",
"# 2.4.1 case correction for table1\n",
"table1.apply(lambda x: x.str.lower() if x.dtype == \"object\" else x)\n",
" \n",
"# 2.4.2 case correction for table2\n",
"table2.apply(lambda x: x.str.lower() if x.dtype == \"object\" else x)\n",
" \n",
"# 2.5.1 converting categorical data into dummy variables for table1\n",
"# 2.5.2 converting categorical data into dummy variables for table2\n",
" \n",
"# 2.6.1 dropping duplicate rows for table1\n",
"table1.drop_duplicates(inplace=True)\n",
"# 2.6.2 dropping duplicate rows for table2\n",
"table2.drop_duplicates(inplace=True)\n",
" \n",
"# STEP3: Creating a master table\n",
"data = pandas.merge(table1, table2, on='0',)\n",
"labels={'0':'customer_id', '1_x':'gender', '2_x':'age', '3_x':'driving_licence_presence',\n",
"'4_x':'region_code', '5':'previously_insured', '6':'vehicle_age', '7':'vehicle_damage',\n",
"'1_y':'annual_premium_INR', '2_y':'sales_channel_code', '3_y':'vintage', '4_y':'response'}\n",
"data.rename(columns=labels, inplace=True)\n",
"data\n",
" \n",
"# STEP4: getting information\n",
"# 4.1 Gender wise average annual premium\n",
"data.groupby('gender')['annual_premium_INR'].mean()\n",
" \n",
"# 4.2 Age wise average annual premium\n",
"result4_2 = data.groupby('age')['annual_premium_INR'].mean()\n",
"import matplotlib.pyplot as pyplot\n",
"result4_2.plot()\n",
"pyplot.show()\n",
" \n",
"# 4.3 Is your data balanced between the genders?\n",
"print(f\"male to female ration is {round(data['gender'].value_counts()[0]/data['gender'].value_counts()[1],2)}\")\n",
"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}\")\n",
" \n",
"# 4.4 Vehicle age wise average annual premium.\n",
"result4_4 = data.groupby('vehicle_age')['annual_premium_INR'].mean()\n",
"import matplotlib.pyplot as pyplot\n",
"result4_4.plot()\n",
"pyplot.show()\n",
" \n",
"# STEP5: Is there any relation between Person Age and annual premium?\n",
"n = data['age'].corr(data['annual_premium_INR'])\n",
"if n<-0.5:\n",
" print(\"Strong negative relationship\")\n",
"if n>0.5:\n",
" print(\"Strong positive relationship\")\n",
"if n>-0.5 and n<0.5:\n",
" print(\"There is no relationship!\")"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "57af889c",
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"id": "3aa6d72c",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.9.12"
}
},
"nbformat": 4,
"nbformat_minor": 5
}