{"metadata": {"kernelspec": {"language": "python", "display_name": "Python 3", "name": "python3"}, "language_info": {"name": "python", "file_extension": ".py", "version": "3.6.3", "mimetype": "text/x-python", "codemirror_mode": {"name": "ipython", "version": 3}, "nbconvert_exporter": "python", "pygments_lexer": "ipython3"}}, "cells": [{"execution_count": null, "metadata": {"_uuid": "c41ed77d44f9c07f6f95a8fcfa1b6d2ace658b2c", "_cell_guid": "992dfd65-f2ec-486c-8ec5-7df93a068664"}, "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", "from scipy import stats\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/kkbox-churn-prediction-challenge\"]).decode(\"utf8\"))\n", "# 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", "\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", "import numpy as np # linear alegbra\n", "import pandas as pd # data processing\n", "import os # os commands\n", "from datetime import datetime as dt #work with date time format\n", "import dask.dataframe as dd\n", "\n", "%matplotlib inline \n", "# initiate matplotlib backend\n", "import seaborn as sns # work over matplotlib with improved and more graphs\n", "import matplotlib.pyplot as plt #some easy plotting\n", "\n"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "c53865c744f95bd7aaca04a2a5b0a6c9782d89d4", "collapsed": true, "_cell_guid": "c202a678-b1d4-4444-b313-3ce3594b5a51"}, "source": ["transactions = pd.read_csv('../input/kkbox-churn-prediction-challenge/transactions.csv', engine = 'c', sep=',')#reading the transaction file"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "5e88e2a22692b4beb3f8cf1605a24f6774587f01", "collapsed": true, "_cell_guid": "6dfae882-9843-4684-90d5-3dae3a9d9fba"}, "source": ["transactions =transactions.append(pd.read_csv('../input/kkbox-churn-prediction-challenge/transactions.csv', engine = 'c', sep=','))"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "c3034308f73022c7f0e10471bee30f00e1eb89ac", "_cell_guid": "f0c509de-543b-4b21-80c8-042e4851fedb"}, "source": ["transactions.info()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "24588ae07d30f249e3ef8bbd7e0a2cad354582b6", "_cell_guid": "80bdf630-74c9-45b4-9f75-f598207cc1f8"}, "source": ["transactions.describe()"], "outputs": [], "cell_type": "code"}, {"source": ["** to reduce the size of transactions dataframe**"], "metadata": {"_uuid": "89b02545f0df9688966eb48243c14b0e0a330deb", "_cell_guid": "57156994-d74a-405f-889d-0d3332c9bcb8"}, "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "e56d4992246465bb85c3bd4489948833ea20aa00", "_cell_guid": "db6dcf20-bbc9-41c6-b293-b06fb2396887"}, "source": ["\n", "print(\"payment_plan_days min: \",transactions['payment_plan_days'].min())\n", "print(\"payment_plan_days max: \",transactions['payment_plan_days'].max())\n", "\n", "print('payment_method_id min:', transactions['payment_method_id'].min())\n", "print('payment_method_id max:', transactions['payment_method_id'].max())\n"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "19e54896bdf6ed40c77dbb662c5d8143d7c606f9", "collapsed": true, "_cell_guid": "d67efbad-5db5-4048-b745-b2dc4cceb5dc"}, "source": ["# h=change the type of these series\n", "\n", "transactions['payment_method_id'] = transactions['payment_method_id'].astype('int8')\n", "transactions['payment_plan_days'] = transactions['payment_plan_days'].astype('int16')\n"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "28800f1e04569fa1cacbe041adb917dc96d91cfd", "_cell_guid": "73a8dfac-6d30-443a-8be6-1e4eb9e43d7f"}, "source": ["print('plan list price varies from ', transactions['plan_list_price'].min(), 'to ',transactions['plan_list_price'].max() )\n", "print('actual amount varies from ', transactions['actual_amount_paid'].min(),'to ', transactions['actual_amount_paid'].max() )"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "4112175a854ebf3660de3cbde8695cb410d94030", "collapsed": true, "_cell_guid": "4a8213ae-dfa3-4637-adaa-61b129a3d29e"}, "source": ["\n", "transactions['plan_list_price'] = transactions['plan_list_price'].astype('int16')\n", "transactions['actual_amount_paid'] = transactions['actual_amount_paid'].astype('int16')"], "outputs": [], "cell_type": "code"}, {"source": ["** size of file has decreased by almost 33% **"], "metadata": {"_uuid": "eb3635d153583c9c7bc445340b2a5e372523f121", "_cell_guid": "7e8be115-f1ac-4578-a88b-b0f7190c385b"}, "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "7b856482c5decef187f0e4f7bf991778c66778d4", "_cell_guid": "c638a90a-f422-4ef9-a596-2d7003bd4543"}, "source": ["transactions.info()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "3999b02e2840b612162848657caa5c59a078c0fe", "collapsed": true, "_cell_guid": "8d3384ca-1406-4366-9fe8-e588fa5e01bb"}, "source": ["\n", "transactions['is_auto_renew'] = transactions['is_auto_renew'].astype('int8') # chainging the type to boolean\n", "transactions['is_cancel'] = transactions['is_cancel'].astype('int8')#changing the type to boolean"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "7fe2e77bbacba9cedaba3a9f1e1e6d259b4f9e23", "_cell_guid": "8ca24ff3-a71c-432f-b20c-033b21b66911"}, "source": ["sum(transactions.memory_usage()/1024**2) # memory usage "], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a6e7ced0c0fb4d0efe065278ca18fd71c8518c2d", "_cell_guid": "66632670-5d34-4705-bc76-0e1ed0d86bf0"}, "source": ["\n", "transactions['membership_expire_date'] = pd.to_datetime(transactions['membership_expire_date'].astype(str), infer_datetime_format = True, exact=False)\n", "# converting the series to string and then to datetime format for easy manipulation of dates\n", "print(\"memory usage for transaction df is: \", np.round(sum(transactions.memory_usage()/1024**2),2), \"GB\") # this wouldn't change the size of df as memory occupied by object is similar to datetime"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a9b4c0dea3ade1e254bb22d300e59580fb648111", "_cell_guid": "377ebd34-3a5c-43ae-8d2b-22450cd7c17c"}, "source": ["transactions['transaction_date'] = pd.to_datetime(transactions['transaction_date'].astype(str), infer_datetime_format = True, exact=False)\n", "print(\"done!\")"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "c78c63fb3b0eb727e3adbdec4e67a5a425f27ce8", "_cell_guid": "ff96b543-8a61-4ead-8072-1da4a0eab74e"}, "source": ["agg = {'payment_plan_days':['mean','sum', 'count'] , 'payment_method_id':['max','min'],\n", "       'plan_list_price':['mean','sum'], 'actual_amount_paid':['mean','sum'], 'is_auto_renew':['mean','sum'],\n", "       'transaction_date':'min', 'membership_expire_date':'max', 'is_cancel':['mean','sum']}"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"collapsed": true}, "source": ["transactions_train = transactions[transactions['membership_expire_date']<='2017-02-28'];"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"collapsed": true}, "source": ["transactions_test = transactions[(transactions['membership_expire_date']<='2017-03-31')\n", "                                &(transactions['membership_expire_date']>'2017-02-28')]"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"collapsed": true}, "source": ["del transactions"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "7811f3f2949cbddb98960983868a4035e916b4ff", "collapsed": true, "_cell_guid": "fcaa6e92-8787-4b5a-95f2-e4beb7d7deac"}, "source": ["transactions_train = transactions_train.groupby('msno').agg(agg)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "7491951002be41f0a99c655ecc090c0c1217e80b", "collapsed": true, "_cell_guid": "945b7434-be01-40e4-9746-fc28e22c1ab2"}, "source": ["transactions_test = transactions_test.groupby('msno').agg(agg)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "21cb5b3302da394dc90c50255ab01013bd8c7cb5", "collapsed": true, "_cell_guid": "8b1813fe-e043-4021-9f27-7cac8f052334"}, "source": ["transactions_test.columns = transactions_test.columns.get_level_values(0)+'_'+transactions_test.columns.get_level_values(1)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "ed0d2642f8409a1503fc268c558ebc60de70c596", "collapsed": true, "_cell_guid": "be2a0611-507a-411e-a404-2cbdc582d7fe"}, "source": ["transactions_train.columns = transactions_train.columns.get_level_values(0)+'_'+transactions_train.columns.get_level_values(1)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "fd2f1e1eb5c8ec263cf72031547de4a0fa5b8258", "_cell_guid": "721e334b-b772-4906-9c3c-e74098892fc7"}, "source": ["transactions_test.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "2e7887d8ff5ef827214512965ba0f85ecd54d9e3", "_cell_guid": "0507c936-7afe-4124-8902-3d9067aee4e8"}, "source": ["transactions_train.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "306eb3d04367c206ed7d467ab9c5b210fd88ed5f", "collapsed": true, "_cell_guid": "eb114333-32ec-4541-903b-689b5416007e"}, "source": ["transactions_test = transactions_test.append(transactions_train)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "7d16e8ba0d611ce6d3b5d8254ffa0dd27335cb23", "collapsed": true, "_cell_guid": "f032b05f-5a15-4790-8a9c-79ba6a4e7cc7"}, "source": ["agg = {'payment_plan_days_mean':'mean', 'payment_plan_days_sum':'sum',\n", "       'payment_plan_days_count':'sum', 'payment_method_id_max':'max', 'payment_method_id_min':'min',\n", "       'plan_list_price_mean':'mean', 'plan_list_price_sum':'sum', \n", "       'actual_amount_paid_mean':'mean', 'actual_amount_paid_sum':'sum', \n", "       'is_auto_renew_mean':'mean', 'is_auto_renew_sum':'sum',\n", "       'transaction_date_min':'min', 'membership_expire_date_max':'max', 'is_cancel_mean':'mean',\n", "       'is_cancel_sum':'sum'}"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "c88ddeb73b07c5e882d4ef90c190aa334c8aa950", "_cell_guid": "dab70cf7-dc7b-4a0f-a3fb-a94ddde1de10"}, "source": ["transactions_test = transactions_test.groupby(level=0).agg(agg)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {}, "source": ["transactions_test.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "bbbc0ec9a66eca5d2e18ce2d5a1b5fec8b748092", "_cell_guid": "8c04af67-122e-4f09-b2ad-93b52d8683e8"}, "source": ["print(\"size of transactions_train is :\", sum(transactions_train.memory_usage()/1024**2)) # memory usage \n", "print(\"size of transactions_test is :\", sum(transactions_test.memory_usage()/1024**2))"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"collapsed": true}, "source": ["'''to make columns to mark if a particular user has chaned its payment method id'''\n", "def payment_method_id_change(df):\n", "    df['payment_method_id_change'] = df['payment_method_id_max'] - df['payment_method_id_min']\n", "    df['payment_method_id_change'] = df['payment_method_id_change'].map(lambda x: 1 if x>0 else 0)\n", "    df.drop(['payment_method_id_max','payment_method_id_min'], inplace=True, axis=1)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {}, "source": ["payment_method_id_change(transactions_train)\n", "payment_method_id_change(transactions_test)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {}, "source": ["transactions_test.columns"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {}, "source": ["transactions_train.columns"], "outputs": [], "cell_type": "code"}, {"source": ["** repeating the same process on members file/df**"], "metadata": {"_uuid": "b8e56edae280cad06f5c36e22f606a7f475b7e37", "_cell_guid": "6ca16009-a400-4bd1-aafd-06e4faec29fe"}, "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "6c0abd6580322cbecca38b3ad8d42faa1540aad8", "collapsed": true, "_cell_guid": "921ba6d3-d65d-4364-a6e3-0ab49669e134"}, "source": ["members = pd.read_csv('../input/kkbox-churn-prediction-challenge/members_v3.csv')"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "4676eb918e00ec0bf358e65dd0c01b22acc37783", "_cell_guid": "5decaaa9-777f-451f-9d53-682c6829bd08"}, "source": ["members.info()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "b0160c34048e62381b5217b113f22fc875ad55ed", "_cell_guid": "2dd34052-6cb3-4b81-87f1-86678c92af4e"}, "source": ["members.describe()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "c35ae39dc57167193b90754dabc14d54aa5d17b6", "collapsed": true, "_cell_guid": "2fa3d846-0a5b-4d49-a0eb-ff1844dd9f50"}, "source": ["members['city']=members['city'].astype('int8');\n", "members['bd'] = members['bd'].astype('int16');\n", "members['bd']=members['bd'].astype('int8');\n", "members['registration_init_time'] = pd.to_datetime(members['registration_init_time'].astype(str), infer_datetime_format = True, exact=False)\n", "#members['expiration_date'] = pd.to_datetime(members['expiration_date'].astype(str), infer_datetime_format = True, exact=False)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a174ed8274260369cd8554258622aea918c29631", "_cell_guid": "c7c52ce5-8ba3-42e0-8819-7a3d654370ad"}, "source": ["print(\"size of members is :\", sum(members.memory_usage()/1024**2))"], "outputs": [], "cell_type": "code"}, {"source": ["** doing the same with train data**"], "metadata": {"_uuid": "e031bbdecd32bc9719f627bf84eccbc15ae97c8b", "_cell_guid": "fdac1bc7-c92b-44df-a1d2-53adb070a017"}, "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "2a518c3a9450b3e3ce88e9a96209fc9cdfcaff53", "_cell_guid": "928b36ac-bdce-4281-a987-4f8b7b24cb3d"}, "source": ["train = pd.read_csv('../input/kkbox-churn-prediction-challenge/train.csv')\n", "train = train.append(pd.read_csv('../input/kkbox-churn-prediction-challenge/train_v2.csv'))\n", "train.head()"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "36ac987547503e8f53f8a702d635fa964a1e4dca", "collapsed": true, "_cell_guid": "366067f2-04b0-4154-9200-7aa7184fd282"}, "source": ["train['is_churn'] = train['is_churn'].astype('int8');"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a4c1321d1a72252f4fe75405453a98d86ca28b92", "collapsed": true, "_cell_guid": "561c7bef-76db-42d7-9264-d468e193832f"}, "source": ["train = train.groupby('msno').max()"], "outputs": [], "cell_type": "code"}, {"source": ["** now merging all the dataframe with inner joint as we would not want half information about users**"], "metadata": {"_uuid": "9fae3814ba776e2a5ee6357d74cc80d413dcb4bf", "_cell_guid": "9e414366-d42a-4827-9c5b-8bf44f199372"}, "cell_type": "markdown"}, {"execution_count": null, "metadata": {"_uuid": "3222327d79f367f02547e97d86b3c8eaae7b1b34", "collapsed": true, "_cell_guid": "d42f3f15-a655-47fc-9783-7a325bbad8ef"}, "source": ["train = train.reset_index().merge(transactions_train.reset_index(), how='left', on='msno')"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "3de865b24deb4a31dd1a680f4fdca58edbc1a8ab", "collapsed": true, "_cell_guid": "9b32f587-7feb-4cbc-a9f3-f1f4a7901b45"}, "source": ["#train = train.merge(transactions, on='msno',how='left', sort= False)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "0e594929ecb07a1cb8bca33314e585431c04b48f", "collapsed": true, "_cell_guid": "6b56f6de-64e1-4cb9-af4a-97ecf443833e"}, "source": ["train = train.reset_index().merge(members, how='left',on='msno')"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "a4a977a5ee9c2e03080ead6dd7121364a607d160", "collapsed": true, "_cell_guid": "2225a141-f6c5-46b9-aef5-ecfc2b306e29"}, "source": ["test = pd.read_csv('../input/kkbox-churn-prediction-challenge/sample_submission_v2.csv')"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "4ff33a035f5a5fe08d28ac1075fd649e2af3ec7e", "collapsed": true, "_cell_guid": "876ac2f6-2b7b-4991-b5c8-811023d68921"}, "source": ["test = test.merge(transactions_test.reset_index(), how='left',on='msno')"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "033b90732aa534c53992b732e6f1917cfd97abdb", "collapsed": true, "_cell_guid": "814260c9-3991-4349-9553-5667a0e8ab00"}, "source": ["test = test.merge(members, how='left',on='msno')"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "8cff295252629b67677b66c428faa76c0b8f7c44", "_cell_guid": "db2be28d-8bc3-465f-95d1-ca9f216bf833"}, "source": ["print(\"Shape of train data is :\", train.shape)\n", "print(\"Shape of test data is :\", test.shape)"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "7379c9290e9325f82d69ad8e5761e02c3b80bf26", "collapsed": true, "_cell_guid": "ffd4a0fd-e915-4377-876e-d825b037d144"}, "source": ["# deleting the previously imported df as they occupy space in memory\n", "del transactions_test\n", "del transactions_train\n", "del members"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "fd053aff9672050a8e21bc295a54db1f7316e1d4", "_cell_guid": "d610f3e8-2855-47bc-b977-ce988334934e"}, "source": ["#total memory consumptions by all these data frame\n", "print('size of train df is :', np.sum(train.memory_usage()/1024**2))\n", "print('size of test df is :', np.sum(test.memory_usage()/1024**2))"], "outputs": [], "cell_type": "code"}, {"execution_count": null, "metadata": {"_uuid": "58f1098e4e5ba6aaf1d933192c900fff0cba2b88", "collapsed": true, "_cell_guid": "c9b5d8b8-ff01-4fa9-b5e6-50dbe9866852"}, "source": ["test.to_csv('test_unprocessed')\n", "train.to_csv('train_unprocessed')"], "outputs": [], "cell_type": "code"}], "nbformat_minor": 1, "nbformat": 4}