{"cells": [{"cell_type": "markdown", "metadata": {"_uuid": "2bad73469a628dfd01c4edbede6b20ab606bf600", "_cell_guid": "d64a2848-8f28-4986-8361-9444fc5ef962"}, "source": ["Here is my first kernel on KKBox.\n", "\n", "I will be playing with only three of the csv files (train.csv, members.csv and transactions.csv). the file user_logs_1.csv is too big for these kernels.\n", "\n", "What are we going to see in this kernel?\n", "* How to reduce memory consumption of dataframes?\n", "* Some insights into the data at hand.\n", "\n", "##Memory Reduction\n", "\n", "The memory consumed by each of the above mentioned files are too high. I will first reduce it."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "7bfc4aaa12cde37773c2f9a72d8a641b39548e71", "collapsed": true, "_cell_guid": "ee1f67d8-863c-41d0-aa78-47439243693a"}, "source": ["# This Python 3 environment comes with many helpful analytics libraries installed\n", "# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n", "# For example, here's several helpful packages to load in \n", "\n", "import numpy as np # linear algebra\n", "import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n", "import seaborn as sns\n", "from matplotlib import pyplot\n", "\n", "# Input data files are available in the \"../input/\" directory.\n", "# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\n", "\n", "from subprocess import check_output\n", "print(check_output([\"ls\", \"../input\"]).decode(\"utf8\"))\n", "\n", "df_train = pd.read_csv('../input/train.csv')\n", "df_members = pd.read_csv('../input/members.csv')\n", "df_transactions = pd.read_csv('../input/transactions.csv')\n", "df_sample = pd.read_csv('../input/sample_submission_zero.csv')\n", "\n", "#df_user_logs_1 = pd.read_csv('../input/user_logs.csv', chunksize = 500)\n", "#df = pd.concat(df_user_logs_1, ignore_index=True)\n", "\n", "# Any results you write to the current directory are saved as output."], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "620ed69fa723d97048a88924a840a34a71ed6f2a", "_cell_guid": "2ff26f5c-1c76-44a5-854e-4189560d42fd"}, "source": ["# Memory Reduction\n", "\n", "**members.csv**\n", "\n", "First we will see how to reduce this file to a managable size. \n", "\n", "Let us see the memory consumption of this dataframe"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "09c6106c5b5804d460baaf094e85c5e9a5dd7172", "collapsed": true, "_cell_guid": "5ffa041a-83d4-4692-b869-808b9b711d67"}, "source": ["#--- Displays memory consumed by each column ---\n", "print(df_members.memory_usage())\n", "\n", "#--- Displays memory consumed by entire dataframe ---\n", "mem = df_members.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "b3cb521753ff42b8c4ec7dea533a079ab948d9b4", "_cell_guid": "5cb803b1-ea5e-4bf4-8a0a-73c130705ae3"}, "source": ["The dataframe consumes ~273MB. Our aim is to REDUCE this to a minimum possible."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "86e348c21f3b6fc12e13b197d0d92e0a4e636e43", "collapsed": true, "_cell_guid": "a959999c-29f5-49af-a62e-2baebb9da79e"}, "source": ["#--- Check whether it has any missing values ----\n", "print(df_members.isnull().values.any())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "9df755766635f2288bcc562487ae042c05c360b3", "collapsed": true, "_cell_guid": "35280668-2066-4801-aaeb-9ce9a78f1710"}, "source": ["#--- check which columns have Nan values ---\n", "columns_with_Nan = df_members.columns[df_members.isnull().any()].tolist()\n", "print(columns_with_Nan)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "022ed09bd7a07a3ee41f5c301dc961fb26200be0", "collapsed": true, "_cell_guid": "245158b4-2d9a-45ac-8a3b-7b412569035d"}, "source": ["#--- Check the datatypes of each of the columns in the dataframe ---\n", "print(df_members.dtypes)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "dc411f790033d6b802746c53e52c3c5fb512c427", "collapsed": true, "_cell_guid": "fc9ec570-bfa2-4bbb-9460-840aaf3e87e7"}, "source": ["print (df_members.head())"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "a80ff215397b1df828bb1e75ae23f59c6aac1ddc", "_cell_guid": "2b5de61b-185f-49f3-be79-e62d034b8122"}, "source": ["Memory consumption can be reduced for columns having values of type *integer* or *float*.\n", "\n", "First we have go through each column and find the **maximum** and **minimum** value and choose the appropriate datatype. [See this page for more info](https://docs.scipy.org/doc/numpy-1.13.0/user/basics.types.html).\n"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "8ef9647074c69e0e3e4a0e97c054ce41565a7251", "collapsed": true, "_cell_guid": "9e8461a1-2da3-44b9-adcb-e781660696bd"}, "source": ["print(np.max(df_members['city']))\n", "print(np.min(df_members['city']))"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b2714a469e76cf5a1cf7731384faae2b6cbfe41d", "collapsed": true, "_cell_guid": "18838158-0ecc-49ce-83a9-356aa38dd75a"}, "source": ["df_members['city'] = df_members['city'].astype(np.int8)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "e6550dcfedbe28df9efe9357d083ba0f7236c953", "collapsed": true, "_cell_guid": "9e156190-c4a7-432d-b4a8-d80b8804f1b6"}, "source": ["mem = df_members.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "55ad790221965e138419bc2185e7b67a750117c5", "_cell_guid": "ce79dad7-43ba-4d85-9b3e-bf7645c751e6"}, "source": ["We have already reduced the dataframe size from 273MB to ~240MB. \n", "\n", "Hold tight we have many more columns to go!!!!"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "5a489c710b14df44cf708a709f637694e5b4fe48", "collapsed": true, "_cell_guid": "f9130fe0-dae4-41a7-997e-2f4e41f2c589"}, "source": ["print(np.max(df_members['bd']))\n", "print(np.min(df_members['bd']))"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "1244b09d62e59eb5350183d5f0cff0f587df5890", "collapsed": true, "_cell_guid": "40dd7567-13e4-4b39-982a-8cfdf088de6b"}, "source": ["df_members['bd'] = df_members['bd'].astype(np.int16)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "55d7812d25dab5e999529888156ad90b623071d8", "collapsed": true, "_cell_guid": "111699a8-7fd8-4d27-b7a4-4bd62ac49531"}, "source": ["mem = df_members.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "0dd3c8e847f6557696a52cf2210f67393a471fb0", "_cell_guid": "db5288c8-ad93-49d6-914a-7f83a863f887"}, "source": ["Now the memory has reduced from 240MB to ~210MB."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "7009f1347d206d2061f7cde2f5a65daadd89e6cc", "collapsed": true, "_cell_guid": "a314ab57-0952-4cb1-bce2-06cc095815b4"}, "source": ["print(np.max(df_members['registered_via']))\n", "print(np.min(df_members['registered_via']))"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "6e0141a48113e0383c71c1e5bee9f08b8cec9f42", "collapsed": true, "_cell_guid": "d9de3a96-307a-4e83-98c8-80e5a0bc5572"}, "source": ["df_members['registered_via'] = df_members['registered_via'].astype(np.int8)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "f87fd23fc24ce2030b7c915b5806d705136108cb", "collapsed": true, "_cell_guid": "dd1cdc91-8ced-4e79-a0c7-2add00c50add"}, "source": ["mem = df_members.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "fc90a511226138a357367719eaebcb12c4a513f0", "_cell_guid": "c5dbd720-1e66-48ae-b403-af93cf768559"}, "source": ["We have further reduced the consumption to 175MB from 210MB!!!\n", "\n", "Now we have two date columns which are **NOT** of type datetime but normal integers. We will have to split them based on *year*, *month*, and *date*."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b7618a3bd1e22c6d0d20897ff363546f25622174", "collapsed": true, "_cell_guid": "c29f5721-c347-49d4-862b-32826c79ddcb"}, "source": ["df_members['registration_init_year'] = df_members['registration_init_time'].apply(lambda x: int(str(x)[:4]))\n", "df_members['registration_init_month'] = df_members['registration_init_time'].apply(lambda x: int(str(x)[4:6]))\n", "df_members['registration_init_date'] = df_members['registration_init_time'].apply(lambda x: int(str(x)[-2:]))"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "767cc7ad19b92ef9296d11c2ea62409337045cde", "collapsed": true, "_cell_guid": "a580f101-b86d-4dce-97c7-b90cbf2f5554"}, "source": ["df_members['expiration_date_year'] = df_members['expiration_date'].apply(lambda x: int(str(x)[:4]))\n", "df_members['expiration_date_month'] = df_members['expiration_date'].apply(lambda x: int(str(x)[4:6]))\n", "df_members['expiration_date_date'] = df_members['expiration_date'].apply(lambda x: int(str(x)[-2:]))"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "3606e4061ff6ea17afd1489a7c1b2bf5bb496f55", "collapsed": true, "_cell_guid": "5b4d4cda-aec6-4d55-a284-7d9ea8292983"}, "source": ["print(df_members.head())"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "2301a2903c0bf3e800065cb8238262ef93b2925e", "_cell_guid": "964c65d1-9791-4420-b67c-2b286ca25bb2"}, "source": ["The newly created columns are of type **int64** by default."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "da2e55b3d2bbcb10f3ee1e0cd5040915ad9344f1", "collapsed": true, "_cell_guid": "803f213d-a671-46e3-9be0-252cc6bf9b44"}, "source": ["mem = df_members.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "d5b2d285924898d7828b060fb151259fd7c83898", "_cell_guid": "6af8b03d-d67c-42cf-a317-198a25b00eaa"}, "source": ["You can see a surge in memory consumption to ~410MB!!!\n", "\n", "In a similar manner we have to check every **maximum** and **minimum** value in each of the newly created columns and assign the appropriate datatype."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "966cc965d554bbea31ffc2c028f67d79bf0a8370", "collapsed": true, "_cell_guid": "bfbe5879-f147-41de-9163-9a68b64dbfe0"}, "source": ["df_members['registration_init_year'] = df_members['registration_init_year'].astype(np.int16)\n", "df_members['registration_init_month'] = df_members['registration_init_month'].astype(np.int8)\n", "df_members['registration_init_date'] = df_members['registration_init_date'].astype(np.int8)\n", "\n", "df_members['expiration_date_year'] = df_members['expiration_date_year'].astype(np.int16)\n", "df_members['expiration_date_month'] = df_members['expiration_date_month'].astype(np.int8)\n", "df_members['expiration_date_date'] = df_members['expiration_date_date'].astype(np.int8)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "a2a085978f663c8c9e455ca0d22167d2e4a6ec63", "collapsed": true, "_cell_guid": "47c415ea-2ffb-47a0-bd80-19d6693e172f"}, "source": ["#--- Now drop the unwanted date columns ---\n", "df_members = df_members.drop('registration_init_time', 1)\n", "df_members = df_members.drop('expiration_date', 1)\n"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "38a4ab8bab020798d16fb27cf3f396055ddbb606", "collapsed": true, "_cell_guid": "fa1a968a-e300-491f-ad45-88429a122457"}, "source": ["mem = df_members.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "f71d34954124c932a30d58dddc9a17ea521795a4", "_cell_guid": "251f1b1a-e409-4d7a-8a38-7f6f0487edbe"}, "source": ["VOILA!!! we have reduced the dataframe to 137MB from ~273MB; which is 50% decrease in memory usage!!!!\n", "\n", "Now we can follow suit for the remaining two columns."]}, {"cell_type": "markdown", "metadata": {"_uuid": "9b550ce9429bfb22b640f93c1db9cc04958a7c83", "_cell_guid": "d20796d9-1137-499f-90ef-2774ce148313"}, "source": ["**train.csv**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "a3a7518a0d9c68398d70cb2f1cfa417548aa7480", "collapsed": true, "_cell_guid": "30cd3741-2e81-43d3-8acf-ea1a1606b17b"}, "source": ["print(df_train.head())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "47482a35e07f465c4d920de70c2f6a9a621a0cb7", "collapsed": true, "_cell_guid": "c7e5f816-6b90-4588-9f10-3fefef27c4b6"}, "source": ["print(df_train.isnull().values.any())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "350e37b771559d38708ef00631504ed65eddeb6a", "collapsed": true, "_cell_guid": "0cec7035-10f8-43cf-9759-62674efd3666"}, "source": ["print(df_train.dtypes)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "60422460342b66c7a500b5aee2151526ed7e89ed", "collapsed": true, "_cell_guid": "335dc2ba-9328-4b9a-90f8-81836578055c"}, "source": ["mem = df_train.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "a154da494aa883000e30c079436ec2984a88d629", "collapsed": true, "_cell_guid": "823881bc-2e08-4628-b82c-0ae74d495911"}, "source": ["df_train['is_churn'] = df_train['is_churn'].astype(np.int8)\n", "\n", "mem = df_train.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "2207b664f5d1f1c7b8fe3e784d4cf49120d0bed3", "_cell_guid": "b55a62e7-5f69-4934-b6ae-9d0216e54edb"}, "source": ["The train.csv file has been reduced from 15MB to 8MB.\n", "\n", "Let us now check the transactions.csv file\n", "\n", "**transactions.csv**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "217c6a1865d931318d424207d8983b78b7252bf1", "collapsed": true, "_cell_guid": "15d60293-86ec-4579-befe-a4d4b9d25e90"}, "source": ["print(df_transactions.head())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "2e4c2a218b054a2eb5a14abe45cad2308b40e54d", "collapsed": true, "_cell_guid": "42e0497c-e30c-4cc8-8fc1-f01f97a736a7"}, "source": ["mem = df_transactions.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "b087f7ee14b53bc672f6cead5822305aff23e132", "_cell_guid": "8c2352ff-8ae8-4ba4-9689-43ec8e8f89f0"}, "source": ["This is consuming a whooping 1.4GB!!!!"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "e5b8440921797db63cf5a50aebb2a43dfb1f2405", "collapsed": true, "_cell_guid": "8264499e-8ad1-4689-9ba7-0c1bcf33f4cc"}, "source": ["print(df_transactions.isnull().values.any())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "83a1f38d8ad14235afdf6901edc537de1935a8d7", "collapsed": true, "_cell_guid": "1b647064-25ba-4443-8772-ce29506f2fe2"}, "source": ["print(df_transactions.dtypes)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "388e243b04b337aa8089a025da00d7c7e4c2cfa6", "collapsed": true, "_cell_guid": "f34ae68c-db03-452c-ab14-e1cd35e621d3"}, "source": [" \n", "df_transactions['payment_method_id'] = df_transactions['payment_method_id'].astype(np.int8)\n", "df_transactions['payment_plan_days'] = df_transactions['payment_plan_days'].astype(np.int16)\n", "df_transactions['plan_list_price'] = df_transactions['plan_list_price'].astype(np.int16)\n", "df_transactions['actual_amount_paid'] = df_transactions['actual_amount_paid'].astype(np.int16)\n", "df_transactions['is_auto_renew'] = df_transactions['is_auto_renew'].astype(np.int8)\n", "df_transactions['is_cancel'] = df_transactions['is_cancel'].astype(np.int8)\n"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "9b30023648b6ae86b303181c2ed10b8a9b8b1bf6", "_cell_guid": "905ecefd-6279-4bfd-b274-0480566976ab"}, "source": ["Here we have two date columns as well of type integer. We proceed as before."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "4b744e90cc1b31d9b55538e1f6858f49babe1b77", "collapsed": true, "_cell_guid": "12f97d5a-a9b6-441e-ac02-3160edf376ee"}, "source": ["\n", "df_transactions['transaction_date_year'] = df_transactions['transaction_date'].apply(lambda x: int(str(x)[:4]))\n", "df_transactions['transaction_date_month'] = df_transactions['transaction_date'].apply(lambda x: int(str(x)[4:6]))\n", "df_transactions['transaction_date_date'] = df_transactions['transaction_date'].apply(lambda x: int(str(x)[-2:]))\n", "\n", "df_transactions['membership_expire_date_year'] = df_transactions['membership_expire_date'].apply(lambda x: int(str(x)[:4]))\n", "df_transactions['membership_expire_date_month'] = df_transactions['membership_expire_date'].apply(lambda x: int(str(x)[4:6]))\n", "df_transactions['membership_expire_date_date'] = df_transactions['membership_expire_date'].apply(lambda x: int(str(x)[-2:]))\n"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "030d312ef85d6971d065f8c9ef1baa8c2d098f3b", "_cell_guid": "fb984110-a86c-4731-a792-bd6c8559c706"}, "source": ["Now we assign different datatypes as appropriate to the newly created columns."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "149b23ebb64519ddb2842ae94195e59ff97e4123", "collapsed": true, "_cell_guid": "88fe6058-6811-4fd4-aad6-12bc9e202e6e"}, "source": ["\n", "df_transactions['transaction_date_year'] = df_transactions['transaction_date_year'].astype(np.int16)\n", "df_transactions['transaction_date_month'] = df_transactions['transaction_date_month'].astype(np.int8)\n", "df_transactions['transaction_date_date'] = df_transactions['transaction_date_date'].astype(np.int8)\n", "\n", "df_transactions['membership_expire_date_year'] = df_transactions['membership_expire_date_year'].astype(np.int16)\n", "df_transactions['membership_expire_date_month'] = df_transactions['membership_expire_date_month'].astype(np.int8)\n", "df_transactions['membership_expire_date_date'] = df_transactions['membership_expire_date_date'].astype(np.int8)\n"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "9b5ed5afa81c6f6053065925f6ab239a80feea23", "collapsed": true, "_cell_guid": "844df928-7263-4fc5-82f9-1be4846256d8"}, "source": ["#--- Now drop the unwanted date columns ---\n", "df_transactions = df_transactions.drop('transaction_date', 1)\n", "df_transactions = df_transactions.drop('membership_expire_date', 1)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "bb1ca3b0bccc63658148e3e53074424946c96cfb", "collapsed": true, "_cell_guid": "8bd12d5f-c1a6-40ac-9e35-a37d55c70072"}, "source": ["print(df_transactions.head())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b016a55951e06098204194bd4ab45d420135e2dc", "collapsed": true, "_cell_guid": "1b0ae6be-f91b-42bd-b6be-da02ec0d19c4"}, "source": ["mem = df_transactions.memory_usage(index=True).sum()\n", "print(mem/ 1024**2,\" MB\")"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "55860e40cac98fef5ff95ab464ab4dcfc78dfae3", "_cell_guid": "5e9b72ef-5366-463d-ba24-c034d0f7f95c"}, "source": ["We have reduced the transcations.csv file from 1.4GB to ~513 MB!!!!!!!"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "584a9823bfdaa0b88ba9b8740102717cf37721fc", "collapsed": true, "_cell_guid": "5e17e426-1136-42cc-8db3-6c1291c94a55"}, "source": ["print('DONE!!')"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "2e2fa7a6f1d609761ee69ca9d3650a4c1ef30dd2", "_cell_guid": "68e0e8de-2f29-4184-80fb-d02b94f00747"}, "source": ["Now we can perform feature engineering and then work out various models!!!!\n", "\n", "I am figuring out a way to use the *user_logs.csv* file though. If anyone has a way please do share it!!"]}, {"cell_type": "markdown", "metadata": {"_uuid": "d0a403d8ecee35933b3e8fafc248e64951eb1e49", "_cell_guid": "2be1e488-74dc-4d1a-9f0c-970e6f8a236f"}, "source": ["# Data Analysis\n", "\n", "Let us look into the data now. Below I am merging the three dataframes baesd on column **msno**."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "6c30fc1ffe35557ad59417aa95d36919ec7f500d", "collapsed": true, "_cell_guid": "298df1fb-13f7-4447-ad91-8efb8c2810c0"}, "source": ["#print(df_members.head())\n", "#print(df_train.head())\n", "\n", "df_train_members = pd.merge(df_train, df_members, on='msno', how='inner')\n", "df_merged = pd.merge(df_train_members, df_transactions, on='msno', how='inner')\n", "print(df_merged.head())\n"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "bfb04951d97a0d33b76a65f3486615f8d8621d45", "_cell_guid": "1004586e-05fa-4b4e-821f-9f597d42bd15"}, "source": ["## is_churn\n", "First and foremost let us analyze the output variable **is_churn**."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b08958970a550d455c525111fe9fdf3777d58b40", "collapsed": true, "_cell_guid": "9fe843e9-24f4-4ce2-8fff-1111571a6e23"}, "source": ["df_train_members.hist(column='is_churn')"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "4daf11e003aca9ced2d7df0c1d1a62bd2379b700", "_cell_guid": "c34b4e62-a2e3-47e8-9ea7-cb9c5f173201"}, "source": ["Roughly around 5000 people have churned the website over time. Our aim is to predict when the remaining people will churn."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b4b14c8376848e805059eebc7846b1bc133b215f", "collapsed": true, "_cell_guid": "68b645a3-1d4a-4e48-9bc9-ec8d9c17a6d2"}, "source": ["#--- Check whether new dataframe has any missing values ----\n", "print(df_train_members.isnull().values.any())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "3d30f8642d69980d5c7124fa1abd21d0025abb6a", "collapsed": true, "_cell_guid": "3e5f2679-0fa3-487f-9604-5e62097c9216"}, "source": ["#--- check which columns have Nan values ---\n", "columns_with_Nan = df_train_members.columns[df_train_members.isnull().any()].tolist()\n", "print(columns_with_Nan)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "d3c80f5a6c517f98100fdc86b31b06219d188f84", "collapsed": true, "_cell_guid": "bff7a0a8-a9d5-412e-9398-cec9f1340dc3"}, "source": ["df_train_members['gender'].isnull().sum()"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "5b510ab0b96f1039d6e9d056676566a84025163f", "_cell_guid": "257e4710-bdd0-4d4d-b041-06ed6ecb6a47"}, "source": ["We will see how to fill them later. Let us see the other variables.\n", "\n", "** is_churn vs gender**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "33ab1051c14f76b1fabba17432ba27e357ec0bbb", "collapsed": true, "_cell_guid": "f121055d-4c34-4b06-9389-f3a0a6f53b8e"}, "source": ["churn_vs_gender = pd.crosstab(df_train_members['gender'], df_train_members['is_churn'])\n", "\n", "churn_vs_gender_rate = churn_vs_gender.div(churn_vs_gender.sum(1).astype(float), axis=0) # normalize the value\n", "churn_vs_gender_rate.plot(kind='barh', , stacked=True)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "5a06d844c2f6fdf8900d7c3617bb5476999751aa", "_cell_guid": "67e12b0a-9c14-4d70-8fd4-7dfa10968d97"}, "source": ["** is_churn vs registered_via **"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "9a9bb6e4a5d7b24aa9be4be5c04ac243f7d5bf78", "collapsed": true, "_cell_guid": "1b5f0933-04c5-44ef-901a-2b1d07642e79"}, "source": ["churn_registered_via = pd.crosstab(df_train_members['registered_via'], df_train_members['is_churn'])\n", "\n", "churn_vs_registered_via_rate = churn_registered_via.div(churn_registered_via.sum(1).astype(float), axis=0) # normalize the value\n", "churn_vs_registered_via_rate.plot(kind='barh', stacked=True)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "ad8a7903c8d6ceb9b3319557494f0056eb0ca2a1", "_cell_guid": "e1347381-52d2-4c11-8c96-3d08794bde40"}, "source": ["** is_churn vs city **"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "4955f3c432484c6c2965a818a8fe3839cc2567cd", "collapsed": true, "_cell_guid": "da29953e-fa85-4f5e-9a2d-53bb16ed9903"}, "source": ["churn_vs_city = pd.crosstab(df_train_members['city'], df_train_members['is_churn'])\n", "\n", "churn_vs_city_rate = churn_vs_city.div(churn_vs_city.sum(1).astype(float), axis=0) # normalize the value\n", "churn_vs_city_rate.plot(kind='bar', stacked=True)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "e7e52ab94d6ebbf6b342f46bc1437970ee113307", "_cell_guid": "847b533d-f17d-4d54-aa00-21a9867db91d"}, "source": ["**is_churn vs bd (age)**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "0c1a104fa624ed0e10788bd29e4c518f3192a4f2", "collapsed": true, "_cell_guid": "79c5ffb8-9099-4ba9-89a1-ff128bf24d0c"}, "source": ["#eliminating extreme outliers\n", "df_train_members = df_train_members[df_train_members['bd'] >= 1]\n", "df_train_members = df_train_members[df_train_members['bd'] <= 80]\n", "\n", "import seaborn as sns\n", "sns.violinplot(x=df_train_members[\"is_churn\"], y=df_train_members[\"bd\"], data=df_train_members)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "b3fee2db2f25a8b54cc26da97b81c27c3b7e30b1", "_cell_guid": "dcfd0a72-48ea-48cd-96dc-2d6b98e224ed"}, "source": ["## **Variable: 'city'**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b5a763b8a4b5755d7c4a10ee59791063d4f9705b", "collapsed": true, "_cell_guid": "bce9696c-1204-49c1-8509-a801cb1712a1"}, "source": ["print (df_train_members['city'].unique())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "3a47c04f30275c46c4b9c42fc283364459a6d38e", "collapsed": true, "_cell_guid": "2852f102-8bfc-429e-9bd1-4fa32d85e58a"}, "source": ["data = df_train_members.groupby('city').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='city', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "b0c662af068eace241ad3206311ea17d98451f00", "_cell_guid": "1f29e17d-cd6c-4bf6-b851-0b68aa900550"}, "source": ["We see a high number of viewers/subscribers from city 1.\n", "\n", "## **Variable: 'bd'**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "905aebae77b2dd86039c9d30c6b0fc2d51c14cb0", "collapsed": true, "_cell_guid": "218c2354-6055-4824-9612-cc2097d41a6d"}, "source": ["print (df_train_members['bd'].nunique())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "13753561eb3065d77f1a80a8b7ccb7bc98a0ce23", "collapsed": true, "_cell_guid": "10593fd4-9f02-429f-be31-41047a2b6514"}, "source": ["df_train_members.plot(x=df_train_members.index, y='bd')"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "485e9f339d39aed89dce3e44f64037c0026e0819", "_cell_guid": "4d8b29d5-008e-4bd0-94ad-709f0bf473c9"}, "source": ["As stated in the data file, we can clearly see some outliers:\n", "* There is an occurrence of -3000 on the negatve side.\n", "* And several occurrences on the positive side.\n", "\n", "Since this column represents **age** such occurences must be removed\n", "\n", "Based on these variations there can be some change in the way a model predicts for the test data. Moreover, we do not know whether such cases are present in the test set.\n", "\n", "## **Variable: 'registered_via' **"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "b84c0ab019d7dd7f0ad372b780ee2e80b6f3b9f3", "collapsed": true, "_cell_guid": "2dc5a78b-c779-46ad-a061-43d64477867c"}, "source": ["print (df_train_members['registered_via'].unique())"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "7042aaa402e2e7ad28b91b74715af5a07b0072a4", "_cell_guid": "2a850e34-ae15-4e9a-bbce-fe808de533ad"}, "source": ["There are only 5 ways of registration. Let us plot them against the count."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "5525eadbd8d001283ccdb736178de25081f2eee2", "collapsed": true, "_cell_guid": "7bd392f0-cbba-49af-9ede-3838169bd011"}, "source": ["data = df_train_members.groupby('registered_via').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='registered_via', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "7e69e68d5b9428a4a902fc7a7153889843aca11f", "_cell_guid": "6f72b6e5-76d5-428f-a0f4-f15e37825c6f"}, "source": ["Most of the users registered through methods **7** and **8**.\n", "\n", "## **Variable 'registration_init_year' **\n", "\n", "When did the users register to this site?"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "90c94c4f9a709dadd40bf41a07e14c83d55094cb", "collapsed": true, "_cell_guid": "e17bd294-923d-41c2-b716-4d1256af6487"}, "source": ["print (df_train_members['registration_init_year'].unique())"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "39f73ecb2ecace6ef69577daca0d21c8b40f07fd", "collapsed": true, "_cell_guid": "fac297af-9386-41d9-84a8-feb765e1d79d"}, "source": ["data = df_train_members.groupby('registration_init_year').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='registration_init_year', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "5e741313b9210b963b295b430373b25a9bd892de", "_cell_guid": "fd1dc744-f16e-4b8b-8132-e3bf72a3ada0"}, "source": ["We can see a spike in registrations since the year 2010!\n", "\n", "We can perform the same for **registration_init_month** and **registration_init_date**."]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "3d7ac1557f36ca0ca8dc573bde8602c32155c458", "collapsed": true, "_cell_guid": "03218fe8-f72d-4dff-9a4d-b73be14cdad7"}, "source": ["data = df_train_members.groupby('registration_init_month').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='registration_init_month', y='msno', data=data)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "a5ea153a7da3cb025f88045589b0a83b00f355ce", "collapsed": true, "_cell_guid": "0fc8cd6b-0163-4192-bdd1-5315f45e28aa"}, "source": ["data = df_train_members.groupby('registration_init_date').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='registration_init_date', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "013dbb4899972155a96846618dee6d36834b021f", "_cell_guid": "348a3366-fcbe-420d-a4b8-13ad67b03177"}, "source": ["We are unable to visualize any form of trend in the above two plots.\n", "\n", "## **Variable: 'payment_method_id'**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "3e63f2c12ccd51771afe2295435b325ea6fb4bf7", "collapsed": true, "_cell_guid": "20fc6322-5148-4165-9c92-c57badae6971"}, "source": ["data = df_merged.groupby('payment_method_id').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='payment_method_id', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "6e5ede9ce339b7b7f50581c12302b5a0614ca64a", "_cell_guid": "886c64bb-8c4f-4f23-a18a-86b2d2a0c333"}, "source": ["A majorityof the people have opted payment method (41)\n", "\n", "## **Variable: 'payment_plan_days'**\n", "\n", "To see distribution across different payment plans"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "79b5bbe722e1b118e0e754ffb303741ebba45823", "collapsed": true, "_cell_guid": "cac4b229-645d-4fb4-a282-46881468f973"}, "source": ["from matplotlib import pyplot\n", "data = df_merged.groupby('payment_plan_days').aggregate({'msno':'count'}).reset_index()\n", "a4_dims = (11, 8)\n", "fig, ax = pyplot.subplots(figsize=a4_dims)\n", "ax = sns.barplot(x='payment_plan_days', y='msno', data=data)\n"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "5c64881ebada4ef917732d1bd377a1a62a1ae92e", "_cell_guid": "6c065423-efb7-4256-82c9-65372067e91b"}, "source": ["Most subscribers have planned for a month\n", "\n", "## **Variable: 'plan_list_price'**\n"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "1c739c1a1453e715f2aebc49ce9d4e715e34bd7e", "collapsed": true, "_cell_guid": "edcd6976-3635-4985-8f20-3e5be8391275"}, "source": ["data = df_merged.groupby('plan_list_price').aggregate({'msno':'count'}).reset_index()\n", "a4_dims = (20, 8)\n", "fig, ax = pyplot.subplots(figsize=a4_dims)\n", "ax = sns.barplot(x='plan_list_price', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "5d527cafee4b9822777a6bb157087bfbc4310c0d", "_cell_guid": "03e7f3c6-8af0-4781-b13c-4ef43b49fd5c"}, "source": ["## **Variable: 'actual_amount_paid'**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "4fe14761c3bbc234d9aec39adecd145ee1a20957", "collapsed": true, "_cell_guid": "c325fa6f-0803-4b78-9be5-c53892a03b6e"}, "source": ["data = df_merged.groupby('actual_amount_paid').aggregate({'msno':'count'}).reset_index()\n", "a4_dims = (20, 8)\n", "fig, ax = pyplot.subplots(figsize=a4_dims)\n", "ax = sns.barplot(x='actual_amount_paid', y='msno', data=data)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "62c30f51d8d1a954d002b74d4af9baeab9d31143", "_cell_guid": "122e3ef3-6faa-41f1-a16b-5aa916eed2d6"}, "source": ["Both the above plotted graphs have a strong resemblence. Maybe we can retain one of these features for modelling and training.\n", "\n", "## **Variable: 'is_cancel'**"]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "6dcb52e271abd2afb3eeabd1796ecf8c98119556", "collapsed": true, "_cell_guid": "59df90da-e853-4d39-a524-9cb8f683e3ef"}, "source": ["data = df_merged.groupby('is_cancel').aggregate({'msno':'count'}).reset_index()\n", "ax = sns.barplot(x='is_cancel', y='msno', data=data)"], "outputs": []}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "685c1aa3f466a4f583b4bbbdd7d149f1b5f2b9e9", "collapsed": true, "_cell_guid": "4f00c6a6-52ce-434e-905e-26f04c0d2859"}, "source": ["#print(df_merged.columns)"], "outputs": []}, {"cell_type": "markdown", "metadata": {"_uuid": "19f3290462301c28e5985d82b487a221a7eafe26", "_cell_guid": "63798b77-d317-484b-94ea-1f368ce2a4dd"}, "source": ["# Correlations\n", "\n", "Finding high correlations among columns "]}, {"cell_type": "code", "execution_count": null, "metadata": {"_uuid": "34366b638656f5d8e07747f1d3f9b84cb900b355", "collapsed": true, "_cell_guid": "aa1f55e0-42bf-4b3b-9eae-1f0c3c3dd330"}, "source": ["corr_matrix = df_merged.corr()\n", "f, ax = plt.subplots(figsize=(20, 25))\n", "cmap = sns.diverging_palette(220, 10, as_cmap=True)\n", "sns.heatmap(corr_matrix, cmap=cmap, vmax=.3, center=0,\n", " square=True, linewidths=.5, cbar_kws={\"shrink\": .5})\n", "plt.show()\n", "\n", "#--- For positive high correlation ---\n", "high_corr_var = np.where(corr_matrix > 0.8)\n", "high_corr_var = [(corr_matrix.index[x],corr_matrix.columns[y]) for x,y in zip(*high_corr_var) if x!=y and x