Project 2 - EDA on Vehicle Insurance Customer Data

[{"metadata":{"trusted":false},"id":"f96a9e97","cell_type":"code","source":"import pandas as pd\nimport numpy as np","execution_count":2,"outputs":[]},{"metadata":{"trusted":false},"id":"51e6646a","cell_type":"code","source":"customer_details=pd.read_csv('customer_details.csv')","execution_count":3,"outputs":[]},{"metadata":{"trusted":false},"id":"3da2312c","cell_type":"code","source":"customer_policy_details=pd.read_csv('customer_policy_details.csv')","execution_count":4,"outputs":[]},{"metadata":{},"id":"75afe3f0","cell_type":"markdown","source":"# Q1.i"},{"metadata":{"trusted":false},"id":"e07903fc","cell_type":"code","source":"customer_details.rename({'0':'Customer_Id','1':'Gender','2':'Age','3':'Driving Licence Present','4':'Region Code','5':'Previously Insured','6':'Vehicle Age','7':'Vehicle Damage'},axis=1,inplace=True)","execution_count":5,"outputs":[]},{"metadata":{"trusted":false},"id":"a541baf9","cell_type":"code","source":"customer_details.head()","execution_count":6,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Gender Age Driving Licence Present Region Code \\\n0 1.0 Male 44.0 1.0 28.0 \n1 2.0 Male 76.0 1.0 3.0 \n2 3.0 Male 47.0 1.0 28.0 \n3 4.0 Male 21.0 1.0 11.0 \n4 5.0 Female 29.0 1.0 41.0 \n\n Previously Insured Vehicle Age Vehicle Damage \n0 0.0 > 2 Years Yes \n1 0.0 1-2 Year No \n2 0.0 > 2 Years Yes \n3 1.0 < 1 Year No \n4 1.0 < 1 Year No "},"execution_count":6,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"0d8558c3","cell_type":"markdown","source":"# Q1.ii"},{"metadata":{"trusted":false},"id":"03d126f7","cell_type":"code","source":"customer_policy_details.rename({'0':'Customer_Id','1':'Annual Premium(Rs)','2':'Sales Channel Code','3':'Vintage','4':'Response'},axis=1,inplace=True)","execution_count":7,"outputs":[]},{"metadata":{"trusted":false},"id":"956bf9d3","cell_type":"code","source":"customer_policy_details.head()","execution_count":8,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Annual Premium(Rs) Sales Channel Code Vintage Response\n0 1.0 40454.0 26.0 217.0 1.0\n1 2.0 33536.0 26.0 183.0 0.0\n2 3.0 38294.0 26.0 27.0 1.0\n3 4.0 28619.0 152.0 203.0 0.0\n4 5.0 27496.0 152.0 39.0 0.0"},"execution_count":8,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"188fbd1d","cell_type":"markdown","source":"# Q2.i"},{"metadata":{"trusted":false},"id":"f68289ea","cell_type":"code","source":"customer_details.isna().sum()","execution_count":9,"outputs":[{"data":{"text/plain":"Customer_Id 386\nGender 368\nAge 368\nDriving Licence Present 393\nRegion Code 392\nPreviously Insured 381\nVehicle Age 381\nVehicle Damage 407\ndtype: int64"},"execution_count":9,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"6ddaca41","cell_type":"code","source":"customer_policy_details.isna().sum()","execution_count":10,"outputs":[{"data":{"text/plain":"Customer_Id 387\nAnnual Premium(Rs) 346\nSales Channel Code 400\nVintage 388\nResponse 361\ndtype: int64"},"execution_count":10,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"fdae7f8a","cell_type":"code","source":"customer_details=customer_details.dropna(subset=['Customer_Id'])","execution_count":11,"outputs":[]},{"metadata":{"trusted":false},"id":"02c7ef46","cell_type":"code","source":"customer_details.isna().sum()","execution_count":12,"outputs":[{"data":{"text/plain":"Customer_Id 0\nGender 368\nAge 366\nDriving Licence Present 392\nRegion Code 391\nPreviously Insured 381\nVehicle Age 381\nVehicle Damage 406\ndtype: int64"},"execution_count":12,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"24701de2","cell_type":"code","source":"customer_policy_details=customer_policy_details.dropna(subset=['Customer_Id'])","execution_count":13,"outputs":[]},{"metadata":{"trusted":false},"id":"7cc5f434","cell_type":"code","source":"customer_policy_details.isna().sum()","execution_count":14,"outputs":[{"data":{"text/plain":"Customer_Id 0\nAnnual Premium(Rs) 344\nSales Channel Code 400\nVintage 388\nResponse 361\ndtype: int64"},"execution_count":14,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"d43f891a","cell_type":"code","source":"customer_policy_details.fillna(customer_policy_details.mean().mean(),inplace=True)","execution_count":15,"outputs":[]},{"metadata":{"trusted":false},"id":"55e06f01","cell_type":"code","source":"customer_policy_details.isna().sum()","execution_count":16,"outputs":[{"data":{"text/plain":"Customer_Id 0\nAnnual Premium(Rs) 0\nSales Channel Code 0\nVintage 0\nResponse 0\ndtype: int64"},"execution_count":16,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"2abef048","cell_type":"code","source":"customer_details.isna().sum()","execution_count":17,"outputs":[{"data":{"text/plain":"Customer_Id 0\nGender 368\nAge 366\nDriving Licence Present 392\nRegion Code 391\nPreviously Insured 381\nVehicle Age 381\nVehicle Damage 406\ndtype: int64"},"execution_count":17,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"3e8862fe","cell_type":"code","source":"customer_details['Gender']=customer_details['Gender'].fillna(customer_details['Gender'].mode()[0])\ncustomer_details['Age']=customer_details['Age'].fillna(customer_details['Age'].mode()[0])\ncustomer_details['Driving Licence Present']=customer_details['Driving Licence Present'].fillna(customer_details['Driving Licence Present'].mode()[0])\ncustomer_details['Region Code']=customer_details['Region Code'].fillna(customer_details['Region Code'].mode()[0])\ncustomer_details['Previously Insured']=customer_details['Previously Insured'].fillna(customer_details['Previously Insured'].mode()[0])\ncustomer_details['Vehicle Age']=customer_details['Vehicle Age'].fillna(customer_details['Vehicle Age'].mode()[0])\ncustomer_details['Vehicle Damage']=customer_details['Vehicle Damage'].fillna(customer_details['Vehicle Damage'].mode()[0])\n","execution_count":18,"outputs":[]},{"metadata":{"trusted":false},"id":"bc5b26a7","cell_type":"code","source":"customer_details.isnull().sum()","execution_count":19,"outputs":[{"data":{"text/plain":"Customer_Id 0\nGender 0\nAge 0\nDriving Licence Present 0\nRegion Code 0\nPreviously Insured 0\nVehicle Age 0\nVehicle Damage 0\ndtype: int64"},"execution_count":19,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"08c666ce","cell_type":"markdown","source":"# Q2.ii"},{"metadata":{"trusted":false},"id":"b66a134c","cell_type":"code","source":"q1=customer_policy_details.quantile(0.25)\nq3=customer_policy_details.quantile(0.75)\niqr=q3-q1\nl=q1-(1.5*iqr)\nu=q3+(1.5*iqr)\n((customer_policy_details<l)|(customer_policy_details>u)).sum()","execution_count":20,"outputs":[{"data":{"text/plain":"Customer_Id 0\nAnnual Premium(Rs) 10239\nSales Channel Code 400\nVintage 388\nResponse 46965\ndtype: int64"},"execution_count":20,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"f1a06f37","cell_type":"code","source":"customer_policy_details['Annual Premium(Rs)']=customer_policy_details['Annual Premium(Rs)'].astype('float')","execution_count":21,"outputs":[]},{"metadata":{"trusted":false},"id":"fe9f6ef8","cell_type":"code","source":"q1=customer_policy_details[['Annual Premium(Rs)']].quantile(0.25)\nq3=customer_policy_details[['Annual Premium(Rs)']].quantile(0.75)","execution_count":22,"outputs":[]},{"metadata":{"trusted":false},"id":"f225e53e","cell_type":"code","source":"iqr=q3-q1","execution_count":23,"outputs":[]},{"metadata":{"trusted":false},"id":"03a6e1f4","cell_type":"code","source":"l=q1-(1.5*iqr)\nu=q3+(1.5*iqr)","execution_count":24,"outputs":[]},{"metadata":{"trusted":false},"id":"345d7ed6","cell_type":"code","source":"((customer_policy_details[['Annual Premium(Rs)']]<l)|(customer_policy_details[['Annual Premium(Rs)']]>u)).sum()","execution_count":25,"outputs":[{"data":{"text/plain":"Annual Premium(Rs) 10239\ndtype: int64"},"execution_count":25,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"087fd621","cell_type":"markdown","source":"# Q2.iii"},{"metadata":{"trusted":false},"id":"c58546dd","cell_type":"code","source":"customer_details['Gender'].str.strip()","execution_count":26,"outputs":[{"data":{"text/plain":"0 Male\n1 Male\n2 Male\n3 Male\n4 Female\n ... \n381104 Male\n381105 Male\n381106 Male\n381107 Female\n381108 Male\nName: Gender, Length: 380723, dtype: object"},"execution_count":26,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"56150bb1","cell_type":"markdown","source":"# Q2.iv"},{"metadata":{"trusted":false},"id":"cbcee38b","cell_type":"code","source":"customer_details.apply(lambda x: x.astype(str).str.upper())","execution_count":27,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Gender Age Driving Licence Present Region Code \\\n0 1.0 MALE 44.0 1.0 28.0 \n1 2.0 MALE 76.0 1.0 3.0 \n2 3.0 MALE 47.0 1.0 28.0 \n3 4.0 MALE 21.0 1.0 11.0 \n4 5.0 FEMALE 29.0 1.0 41.0 \n... ... ... ... ... ... \n381104 381105.0 MALE 74.0 1.0 26.0 \n381105 381106.0 MALE 30.0 1.0 37.0 \n381106 381107.0 MALE 21.0 1.0 30.0 \n381107 381108.0 FEMALE 68.0 1.0 14.0 \n381108 381109.0 MALE 46.0 1.0 29.0 \n\n Previously Insured Vehicle Age Vehicle Damage \n0 0.0 > 2 YEARS YES \n1 0.0 1-2 YEAR NO \n2 0.0 > 2 YEARS YES \n3 1.0 < 1 YEAR NO \n4 1.0 < 1 YEAR NO \n... ... ... ... \n381104 1.0 1-2 YEAR NO \n381105 1.0 < 1 YEAR NO \n381106 1.0 < 1 YEAR NO \n381107 0.0 > 2 YEARS YES \n381108 0.0 1-2 YEAR NO \n\n[380723 rows x 8 columns]"},"execution_count":27,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"0c2f7e7e","cell_type":"markdown","source":"# Q2.v"},{"metadata":{"trusted":false},"id":"cbb2a9ae","cell_type":"code","source":"customerdetails=pd.get_dummies(customer_details)","execution_count":28,"outputs":[]},{"metadata":{"trusted":false},"id":"dd1317ba","cell_type":"code","source":"customerdetails","execution_count":29,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Age Driving Licence Present Region Code \\\n0 1.0 44.0 1.0 28.0 \n1 2.0 76.0 1.0 3.0 \n2 3.0 47.0 1.0 28.0 \n3 4.0 21.0 1.0 11.0 \n4 5.0 29.0 1.0 41.0 \n... ... ... ... ... \n381104 381105.0 74.0 1.0 26.0 \n381105 381106.0 30.0 1.0 37.0 \n381106 381107.0 21.0 1.0 30.0 \n381107 381108.0 68.0 1.0 14.0 \n381108 381109.0 46.0 1.0 29.0 \n\n Previously Insured Gender_Female Gender_Male Vehicle Age_1-2 Year \\\n0 0.0 0 1 0 \n1 0.0 0 1 1 \n2 0.0 0 1 0 \n3 1.0 0 1 0 \n4 1.0 1 0 0 \n... ... ... ... ... \n381104 1.0 0 1 1 \n381105 1.0 0 1 0 \n381106 1.0 0 1 0 \n381107 0.0 1 0 0 \n381108 0.0 0 1 1 \n\n Vehicle Age_< 1 Year Vehicle Age_> 2 Years Vehicle Damage_No \\\n0 0 1 0 \n1 0 0 1 \n2 0 1 0 \n3 1 0 1 \n4 1 0 1 \n... ... ... ... \n381104 0 0 1 \n381105 1 0 1 \n381106 1 0 1 \n381107 0 1 0 \n381108 0 0 1 \n\n Vehicle Damage_Yes \n0 1 \n1 0 \n2 1 \n3 0 \n4 0 \n... ... \n381104 0 \n381105 0 \n381106 0 \n381107 1 \n381108 0 \n\n[380723 rows x 12 columns]"},"execution_count":29,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"b749b590","cell_type":"code","source":"customerpolicydetails=pd.get_dummies(customer_policy_details)","execution_count":30,"outputs":[]},{"metadata":{"trusted":false},"id":"32f81b6a","cell_type":"code","source":"customerpolicydetails","execution_count":31,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Annual Premium(Rs) Sales Channel Code Vintage Response\n0 1.0 40454.0 26.0 217.0 1.0\n1 2.0 33536.0 26.0 183.0 0.0\n2 3.0 38294.0 26.0 27.0 1.0\n3 4.0 28619.0 152.0 203.0 0.0\n4 5.0 27496.0 152.0 39.0 0.0\n... ... ... ... ... ...\n381104 381105.0 30170.0 26.0 88.0 0.0\n381105 381106.0 40016.0 152.0 131.0 0.0\n381106 381107.0 35118.0 160.0 161.0 0.0\n381107 381108.0 44617.0 124.0 74.0 0.0\n381108 381109.0 41777.0 26.0 237.0 0.0\n\n[380722 rows x 5 columns]"},"execution_count":31,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"ba5bec60","cell_type":"markdown","source":"# Q2.vi"},{"metadata":{"trusted":false},"id":"e97c99b0","cell_type":"code","source":"customerdetails.drop_duplicates()","execution_count":32,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Age Driving Licence Present Region Code \\\n0 1.0 44.0 1.0 28.0 \n1 2.0 76.0 1.0 3.0 \n2 3.0 47.0 1.0 28.0 \n3 4.0 21.0 1.0 11.0 \n4 5.0 29.0 1.0 41.0 \n... ... ... ... ... \n381104 381105.0 74.0 1.0 26.0 \n381105 381106.0 30.0 1.0 37.0 \n381106 381107.0 21.0 1.0 30.0 \n381107 381108.0 68.0 1.0 14.0 \n381108 381109.0 46.0 1.0 29.0 \n\n Previously Insured Gender_Female Gender_Male Vehicle Age_1-2 Year \\\n0 0.0 0 1 0 \n1 0.0 0 1 1 \n2 0.0 0 1 0 \n3 1.0 0 1 0 \n4 1.0 1 0 0 \n... ... ... ... ... \n381104 1.0 0 1 1 \n381105 1.0 0 1 0 \n381106 1.0 0 1 0 \n381107 0.0 1 0 0 \n381108 0.0 0 1 1 \n\n Vehicle Age_< 1 Year Vehicle Age_> 2 Years Vehicle Damage_No \\\n0 0 1 0 \n1 0 0 1 \n2 0 1 0 \n3 1 0 1 \n4 1 0 1 \n... ... ... ... \n381104 0 0 1 \n381105 1 0 1 \n381106 1 0 1 \n381107 0 1 0 \n381108 0 0 1 \n\n Vehicle Damage_Yes \n0 1 \n1 0 \n2 1 \n3 0 \n4 0 \n... ... \n381104 0 \n381105 0 \n381106 0 \n381107 1 \n381108 0 \n\n[380723 rows x 12 columns]"},"execution_count":32,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"ebcaf2ea","cell_type":"markdown","source":"# Q3"},{"metadata":{"trusted":false},"id":"64dc40f8","cell_type":"code","source":"master_table=pd.merge(customer_details,customer_policy_details)","execution_count":33,"outputs":[]},{"metadata":{"trusted":false},"id":"b6853444","cell_type":"code","source":"master_table['Age']=master_table['Age'].astype('int')\nmaster_table","execution_count":34,"outputs":[{"data":{"text/html":"

","text/plain":" Customer_Id Gender Age Driving Licence Present Region Code \\\n0 1.0 Male 44 1.0 28.0 \n1 2.0 Male 76 1.0 3.0 \n2 3.0 Male 47 1.0 28.0 \n3 4.0 Male 21 1.0 11.0 \n4 5.0 Female 29 1.0 41.0 \n... ... ... ... ... ... \n380331 381105.0 Male 74 1.0 26.0 \n380332 381106.0 Male 30 1.0 37.0 \n380333 381107.0 Male 21 1.0 30.0 \n380334 381108.0 Female 68 1.0 14.0 \n380335 381109.0 Male 46 1.0 29.0 \n\n Previously Insured Vehicle Age Vehicle Damage Annual Premium(Rs) \\\n0 0.0 > 2 Years Yes 40454.0 \n1 0.0 1-2 Year No 33536.0 \n2 0.0 > 2 Years Yes 38294.0 \n3 1.0 < 1 Year No 28619.0 \n4 1.0 < 1 Year No 27496.0 \n... ... ... ... ... \n380331 1.0 1-2 Year No 30170.0 \n380332 1.0 < 1 Year No 40016.0 \n380333 1.0 < 1 Year No 35118.0 \n380334 0.0 > 2 Years Yes 44617.0 \n380335 0.0 1-2 Year No 41777.0 \n\n Sales Channel Code Vintage Response \n0 26.0 217.0 1.0 \n1 26.0 183.0 0.0 \n2 26.0 27.0 1.0 \n3 152.0 203.0 0.0 \n4 152.0 39.0 0.0 \n... ... ... ... \n380331 26.0 88.0 0.0 \n380332 152.0 131.0 0.0 \n380333 160.0 161.0 0.0 \n380334 124.0 74.0 0.0 \n380335 26.0 237.0 0.0 \n\n[380336 rows x 12 columns]"},"execution_count":34,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"29d89d3c","cell_type":"markdown","source":"# Q4.i"},{"metadata":{"trusted":false},"id":"3b3470a1","cell_type":"code","source":"master_table.groupby(['Gender'])['Annual Premium(Rs)'].mean()","execution_count":35,"outputs":[{"data":{"text/plain":"Gender\nFemale 30505.151882\nMale 30635.409127\nName: Annual Premium(Rs), dtype: float64"},"execution_count":35,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"8d839e00","cell_type":"markdown","source":"# Q4.ii"},{"metadata":{"trusted":false},"id":"1fe1cedc","cell_type":"code","source":"master_table.groupby(['Age'])['Annual Premium(Rs)'].mean()","execution_count":36,"outputs":[{"data":{"text/plain":"Age\n20 26940.061164\n21 30573.665891\n22 30837.557285\n23 30698.806695\n24 31200.619093\n ... \n81 31201.571429\n82 37705.379310\n83 31012.727273\n84 35440.818182\n85 29792.363636\nName: Annual Premium(Rs), Length: 66, dtype: float64"},"execution_count":36,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"fc91639d","cell_type":"markdown","source":"# Q4.iii"},{"metadata":{"trusted":false},"id":"9da4aa11","cell_type":"code","source":"master_table['Gender'].value_counts()","execution_count":37,"outputs":[{"data":{"text/plain":"Male 205851\nFemale 174485\nName: Gender, dtype: int64"},"execution_count":37,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"e50c9b0c","cell_type":"markdown","source":"# Q4.iv"},{"metadata":{"trusted":false},"id":"4b6f6fcb","cell_type":"code","source":"master_table.groupby(['Vehicle Age'])['Annual Premium(Rs)'].mean()","execution_count":38,"outputs":[{"data":{"text/plain":"Vehicle Age\n1-2 Year 30538.472949\n< 1 Year 30126.565208\n> 2 Years 35667.836135\nName: Annual Premium(Rs), dtype: float64"},"execution_count":38,"metadata":{},"output_type":"execute_result"}]},{"metadata":{},"id":"a6cfb0f2","cell_type":"markdown","source":"# Q5"},{"metadata":{"trusted":false},"id":"6b1d2da4","cell_type":"code","source":"np.corrcoef(master_table['Age'],master_table['Annual Premium(Rs)'])# There is no relationship","execution_count":39,"outputs":[{"data":{"text/plain":"array([[1. , 0.06769523],\n [0.06769523, 1. ]])"},"execution_count":39,"metadata":{},"output_type":"execute_result"}]},{"metadata":{"trusted":false},"id":"8a27df88","cell_type":"code","source":"","execution_count":null,"outputs":[]}]