{"metadata": {"language_info": {"codemirror_mode": {"version": 3, "name": "ipython"}, "file_extension": ".py", "version": "3.6.3", "nbconvert_exporter": "python", "name": "python", "pygments_lexer": "ipython3", "mimetype": "text/x-python"}, "kernelspec": {"display_name": "Python 3", "name": "python3", "language": "python"}}, "cells": [{"metadata": {"_cell_guid": "3372c467-888a-4fa0-8f83-b2cdc5813d51", "_uuid": "4bf3890dd93439fb28c0acd187bb5c0478fa0739"}, "cell_type": "markdown", "source": ["### 1. Read train, member, transactions and sample submission files"]}, {"outputs": [], "metadata": {"_cell_guid": "5c0b9b76-9f90-4e78-ab23-a3c4b4ac22b2", "_uuid": "ecf54cc5b60ac6ede0345945195bdfd5563cb804"}, "cell_type": "code", "execution_count": null, "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", "\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", "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", "# Any results you write to the current directory are saved as output."]}, {"metadata": {"_cell_guid": "9210e2fb-60ef-4c0d-ae42-a62c36b5ef35", "_uuid": "3e399081f90e5858dc48204da135066cfe0ea0c6"}, "cell_type": "markdown", "source": ["### 2. Read Log file\n", "Slice out 30,000 rows from 3 differnt parts from log file for analysis"]}, {"outputs": [], "metadata": {"_cell_guid": "ea6686ef-8158-4d2e-bc18-46afeb62a47b", "collapsed": true, "_uuid": "da66e5b23cfc20c66ab1369ae114ee3a16521400"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs_1 = pd.read_csv('../input/user_logs.csv', nrows = 1e5)\n", "df_user_logs_2 = pd.read_csv('../input/user_logs.csv', skiprows = int(1e7), nrows = 1e5)\n", "df_user_logs_3 = pd.read_csv('../input/user_logs.csv', skiprows = int(5e7), nrows = 1e5)"]}, {"metadata": {"_cell_guid": "aeb5c2d3-378e-4ead-9baa-4d24a10a6ca1", "_uuid": "7863d194014688d0738588749fee53cde2fb714b"}, "cell_type": "markdown", "source": ["### 3. Train File\n"]}, {"outputs": [], "metadata": {"_cell_guid": "14411969-4f3a-45f3-937a-cf0f02a8f25d", "collapsed": true, "_uuid": "a703b419685b8425aac0a0b88bad2c0b457f07a1"}, "cell_type": "code", "execution_count": null, "source": ["df_train.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "d562364b-d931-446e-854e-7637ab40e8b8", "collapsed": true, "_uuid": "9862fe9b86e3114ad0ea3ee25c803f85c6fc210f"}, "cell_type": "code", "execution_count": null, "source": ["df_train.info()"]}, {"outputs": [], "metadata": {"_cell_guid": "5bc46d53-b2f7-46e7-b211-37d448e075bd", "collapsed": true, "_uuid": "0b5ceb21e84cedda5a2b0752b2368645bf30811d"}, "cell_type": "code", "execution_count": null, "source": ["# examine if there are duplicates for msno\n", "df_train.msno.unique().shape, df_train.shape"]}, {"metadata": {"_cell_guid": "acd308d7-88f8-4be0-85b1-41620cbeb11c", "_uuid": "706afdced8edf7a1addaaf04964dc995da226a99"}, "cell_type": "markdown", "source": ["#### 3.1 Merge train and test files"]}, {"outputs": [], "metadata": {"_cell_guid": "81564600-ce77-4ab1-94ff-9e1ade57d471", "collapsed": true, "_uuid": "ae4f9ef3757a6a7bed5be189b5708e00d862bc2d"}, "cell_type": "code", "execution_count": null, "source": ["df_train_test_merge = df_train.merge(df_sample, on = 'msno', how = 'outer')\n", "df_train_test_merge.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "d270aacc-259a-43a2-b8d8-14edcf182de3", "collapsed": true, "_uuid": "70b7531ac5f734f73783d3a0ad9c2fdce692b99c"}, "cell_type": "code", "execution_count": null, "source": ["df_train_test_merge.info()"]}, {"outputs": [], "metadata": {"_cell_guid": "be7dd87c-136d-46ff-a9da-fd9dcdcfef1d", "collapsed": true, "_uuid": "c6d90d4f3a5f0e1e920057bbddefb566daac576d"}, "cell_type": "code", "execution_count": null, "source": ["df_com = df_train_test_merge[(~pd.isnull(df_train_test_merge['is_churn_y']))&(~pd.isnull(df_train_test_merge['is_churn_x']))]\n", "df_com.info()"]}, {"outputs": [], "metadata": {"_cell_guid": "97b8168d-76ca-4f3d-ace8-a180bbbaf33d", "collapsed": true, "_uuid": "7ccf1d43e7a14883824717b0938d919d79838ef9"}, "cell_type": "code", "execution_count": null, "source": ["print (df_com.shape)\n", "df_com"]}, {"outputs": [], "metadata": {"_cell_guid": "579b79f3-2844-4db2-b16c-1b611c87323c", "collapsed": true, "_uuid": "db7fa322d64da271b660f812ad29d5b53fd305b5"}, "cell_type": "code", "execution_count": null, "source": ["print ('Percentage of is_churn in test dataset also labeled in train dataset: {:.2f}'.format(df_com.shape[0]*100.0/df_sample.shape[0]))"]}, {"metadata": {"_cell_guid": "dea6b60a-c5ad-4e4e-b30f-10503e347f48", "_uuid": "f67f1f26d16e581521816c8503745b7603b2aef1"}, "cell_type": "markdown", "source": ["#### 90.81% test data are already labeled? Big Leaky? or label issue? \n", "This is potentially label issue because when I submit a file with labels in train data file, the PL is only 2.89. So why we are train with wrong labels???"]}, {"metadata": {"_cell_guid": "a97b8f12-5f85-4582-bdd9-6ef8c7f2b1bc", "_uuid": "00081519a15e44b723ee6ef9dbb9d42ae18d636e"}, "cell_type": "markdown", "source": ["#### 3.2 Classes in test as labeled in train[](http://)"]}, {"outputs": [], "metadata": {"_cell_guid": "b6421e6e-bbb8-4f68-8e05-8f71e264f508", "collapsed": true, "_uuid": "dac9553e593626a74b32f36403c23998e1cfb31e"}, "cell_type": "code", "execution_count": null, "source": ["df_com['is_churn_x'].plot(kind='hist')"]}, {"metadata": {"_cell_guid": "0a5caa68-b7c5-40e0-b4e7-058ebe97ffc3", "_uuid": "41d004bd652e843d5c85f9642785ed31ac920f32"}, "cell_type": "markdown", "source": ["#### 3.3 Classes in train file"]}, {"outputs": [], "metadata": {"_cell_guid": "4fd99679-53a8-486d-a2cf-954a23197e48", "collapsed": true, "_uuid": "b2ea8dfadcf8c7287665cd1f07a01ffb800b4921"}, "cell_type": "code", "execution_count": null, "source": ["df_train['is_churn'].plot(kind='hist')"]}, {"outputs": [], "metadata": {"_cell_guid": "6e3da7b0-b26d-4b74-852c-4d3b77d97a7f", "collapsed": true, "_uuid": "61803fc9f61d8d2a14d4161c1d2de73483f14dab"}, "cell_type": "code", "execution_count": null, "source": ["sum(df_train['is_churn'] == 0), sum(df_train['is_churn'] == 1)"]}, {"metadata": {"_cell_guid": "02a69ff7-eda7-4021-85c1-1837ef5b447d", "collapsed": true, "_uuid": "6eec4efa90cbca7e71113335493bbfa528ef7538"}, "cell_type": "markdown", "source": ["#### Really imbalanced"]}, {"metadata": {"_cell_guid": "caf5dd87-3837-46b6-b2e6-b5246cbad9c0", "_uuid": "c960a1e06137a148c243d45cf1594e578be91fad"}, "cell_type": "markdown", "source": ["### 4. Members"]}, {"outputs": [], "metadata": {"_cell_guid": "aef1d4c6-6009-487c-81cb-2b7fd22c3e79", "collapsed": true, "_uuid": "fc1febfe56f44f7f17129cc99b1cc840d67e5810"}, "cell_type": "code", "execution_count": null, "source": ["df_members.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "04544508-90bf-4560-b984-15030e21d6b5", "collapsed": true, "_uuid": "3a68acd9fc980b5abd3c84b9c3ff38a768012d20"}, "cell_type": "code", "execution_count": null, "source": ["df_members.info()"]}, {"outputs": [], "metadata": {"_cell_guid": "a234cc8f-0156-49c2-947f-f53303280998", "collapsed": true, "_uuid": "6653999dcabe4966ef6a554bb93173bb5ec07391"}, "cell_type": "code", "execution_count": null, "source": ["df_members.describe()"]}, {"metadata": {"_cell_guid": "2b876f33-4e14-45fa-b648-eeda64262201", "_uuid": "808410e60a774ef48365da4abaff18a089a0e1b3"}, "cell_type": "markdown", "source": ["#### 4.1 Duration from expiration date to registration date"]}, {"outputs": [], "metadata": {"_cell_guid": "4b3dd226-7183-4c97-bc96-8248a9530443", "collapsed": true, "_uuid": "0c27e1c01b702e19397fcf4709a42acf307e813b"}, "cell_type": "code", "execution_count": null, "source": ["df_members['reg_year'] = df_members['registration_init_time'].astype(str).apply(lambda x : int(x[:4]))\n", "df_members['reg_month'] = df_members['registration_init_time'].astype(str).apply(lambda x : int(x[4:6]))\n", "df_members['reg_day'] = df_members['registration_init_time'].astype(str).apply(lambda x : int(x[6:]))\n", "df_members['exp_year'] = df_members['expiration_date'].astype(str).apply(lambda x : int(x[:4]))\n", "df_members['exp_month'] = df_members['expiration_date'].astype(str).apply(lambda x : int(x[4:6]))\n", "df_members['exp_day'] = df_members['expiration_date'].astype(str).apply(lambda x : int(x[6:]))"]}, {"outputs": [], "metadata": {"_cell_guid": "7a5c1f68-288b-49d2-8b4c-1efa34f3338a", "collapsed": true, "_uuid": "bca68ba88e2b1226765ecc1f43ab69b8469c766f"}, "cell_type": "code", "execution_count": null, "source": ["df_members['exp_reg'] = (df_members['exp_year'] - df_members['reg_year'])*365 \\\n", "                            + (df_members['exp_month'] - df_members['reg_month'])*30 + \\\n", "                                (df_members['exp_day'] - df_members['reg_day'])"]}, {"metadata": {"_cell_guid": "1031106e-325c-4c14-8fab-1a1c36b74778", "_uuid": "f79354f22add8044f552c4e9e16a0d37759b2ab5"}, "cell_type": "markdown", "source": ["#### 4.2 Split train and test data"]}, {"outputs": [], "metadata": {"_cell_guid": "b9b9a693-2251-4e44-86c4-535986d0a97f", "collapsed": true, "_uuid": "954e76440cba6bf723848d071349629ec5ca9f8a"}, "cell_type": "code", "execution_count": null, "source": ["# separate out 10% data only present in train file but not in sample submission\n", "df_train_only = df_train_test_merge[pd.isnull(df_train_test_merge['is_churn_y'])]"]}, {"outputs": [], "metadata": {"_cell_guid": "bc312344-3412-4c8d-b1de-22b56fe64820", "collapsed": true, "_uuid": "9f68d354adaf12937c1c12fb1d11937cc40cb2db"}, "cell_type": "code", "execution_count": null, "source": ["df_train_only = df_train_only.iloc[:, [0,1]]\n", "df_train_only.columns = ['msno', 'is_churn']\n", "df_train_only.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "fa85d85a-fb8a-4ace-b963-2184abc4a688", "collapsed": true, "_uuid": "d0c11899f4f22bd2e2a0b5fbabb071b888d2d026"}, "cell_type": "code", "execution_count": null, "source": ["gender_dict = {np.nan:0, 'female': 1, 'male':2}\n", "df_members['gender'] = df_members['gender'].apply(lambda x : gender_dict[x])"]}, {"outputs": [], "metadata": {"_cell_guid": "44ed9422-8109-41d3-b1ee-6c6e4953d016", "collapsed": true, "_uuid": "1df80a65b8eae4a0727c7eb6a1d46d50b3faf30c"}, "cell_type": "code", "execution_count": null, "source": ["df_members_train = df_train_only.merge(df_members, on = 'msno', how = 'left')"]}, {"outputs": [], "metadata": {"_cell_guid": "e6620437-9f36-4cb4-b213-251c259f9900", "collapsed": true, "_uuid": "566b43fba5c43ea2282dbe466dc48a34c1b3d339"}, "cell_type": "code", "execution_count": null, "source": ["df_members_test = df_sample.merge(df_members, on = 'msno', how = 'left')\n", "del df_members"]}, {"outputs": [], "metadata": {"_cell_guid": "7f467222-a0b8-42f6-92aa-a25ebd1598f6", "collapsed": true, "_uuid": "187b8378f8577d323356c390345d09c1da8c4dcd"}, "cell_type": "code", "execution_count": null, "source": ["df_members_merge = df_members_train.merge(df_members_test, on = 'msno', how ='outer')"]}, {"outputs": [], "metadata": {"_cell_guid": "8252816d-7b0b-48ad-b595-109a7a403db4", "collapsed": true, "_uuid": "313840432eb0d9e4118d064412f77b00f0258fd1"}, "cell_type": "code", "execution_count": null, "source": ["df_members_train_not_churn = df_members_train[df_members_train['is_churn'] == 0]\n", "df_members_train_churn = df_members_train[df_members_train['is_churn'] == 1]"]}, {"metadata": {"_cell_guid": "c1d9884a-6c3e-43b9-ac24-1d7b66edfa5f", "_uuid": "ba24c640cc1f8ffd0a54829e15d66dfec5fa7a86"}, "cell_type": "markdown", "source": ["#### 4.3 Distribution of churn and not churn in train "]}, {"outputs": [], "metadata": {"_cell_guid": "d445c7bb-8fab-478a-a473-b66dc560978c", "collapsed": true, "_uuid": "3cb95ad6bac8c53f8f038892815546850c8284cf"}, "cell_type": "code", "execution_count": null, "source": ["import matplotlib.pyplot as plt\n", "def dis_1(df1, df2, cols = None):\n", "    '''\n", "    generate distribution plots for churn and not churn in train\n", "    '''\n", "    if cols:\n", "        for col in cols:\n", "            plt.figure()\n", "            df1[col].plot(kind='hist', bins = 200, logy=True, legend = True, label = 'Not Churn', figsize=(10, 4))\n", "            df2[col].plot(kind='hist', bins = 200, logy=True, legend = True, label = 'Churn', figsize=(10, 4))\n", "            plt.title('Distribubtion of {} in train'.format(col))"]}, {"outputs": [], "metadata": {"_cell_guid": "88306294-1f48-41a1-82be-da272d7f488f", "collapsed": true, "_uuid": "6ca2d015ce106e9de38b86c91202a842d13a39ed"}, "cell_type": "code", "execution_count": null, "source": ["members_cols = ['city', 'bd', 'gender', 'registered_via', 'reg_year', 'reg_month', 'reg_day', \n", "               'exp_year', 'exp_month', 'exp_day', 'exp_reg']\n", "dis_1(df_members_train_not_churn, df_members_train_churn, members_cols)"]}, {"metadata": {"_cell_guid": "49849669-7388-47de-bd33-d20c9afb2ea9", "_uuid": "5be4db03c469156f226661e74187740ee3e48d3b"}, "cell_type": "markdown", "source": ["#### 4.4 Distribution in train and test"]}, {"outputs": [], "metadata": {"_cell_guid": "80fa4e0a-647c-40f4-bc7a-a06826a6b6ca", "collapsed": true, "_uuid": "f3ec2c6e8fa977b5a54f89eae98524453cce74b9"}, "cell_type": "code", "execution_count": null, "source": ["def dis_2(df, cols = None):\n", "    '''\n", "    generate distribution plots in train and test\n", "    '''\n", "    if cols:\n", "        for col in cols:\n", "            plt.figure()\n", "            df[[col + '_x']].plot(kind='hist', bins=100, logy=True, legend = True, label = 'Train', figsize=(10, 4))\n", "            df[[col + '_y']].plot(kind='hist', bins=100, logy=True, legend = True, label = 'Test', figsize=(10, 4))\n", "            plt.title('Distribubtion of {} in train and test'.format(col))"]}, {"outputs": [], "metadata": {"_cell_guid": "18c078b4-4447-4b9b-aa3e-2314642893f2", "collapsed": true, "_uuid": "0e3c64336c5145f77f4b3ee3c370dccdbb25be31"}, "cell_type": "code", "execution_count": null, "source": ["dis_2(df_members_merge, members_cols)"]}, {"metadata": {"_cell_guid": "72daa0a3-c7e5-48f1-8e5f-07440f9d0fe7", "_uuid": "23c03a5b43abc1e70a5857e8e3cee9c0a9f44c0a"}, "cell_type": "markdown", "source": ["### 5. Transactions"]}, {"outputs": [], "metadata": {"_cell_guid": "16404222-f330-4c8c-9ebe-fece17669134", "collapsed": true, "_uuid": "0edc80e5d67678cc8b9fb4f162204406d4224a12"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions.info()"]}, {"metadata": {"_cell_guid": "e2ac8d0c-0e45-49bb-b224-a378b10e7378", "_uuid": "d27a27b5035361b788853963ee1da8d959611dcb"}, "cell_type": "markdown", "source": ["#### 5.1 Transactions date and membership expire date"]}, {"outputs": [], "metadata": {"_cell_guid": "7b35932a-5b2e-460f-8935-89a3b24bc66b", "collapsed": true, "_uuid": "da4227875029b3a6f5fc84089d94b28063c8d8c1"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions['trans_year'] = df_transactions['transaction_date'].astype(str).apply(lambda x : int(x[:4]))\n", "df_transactions['trans_month'] = df_transactions['transaction_date'].astype(str).apply(lambda x : int(x[4:6]))\n", "df_transactions['trans_day'] = df_transactions['transaction_date'].astype(str).apply(lambda x : int(x[6:]))\n", "df_transactions['mem_exp_year'] = df_transactions['membership_expire_date'].astype(str).apply(lambda x : int(x[:4]))\n", "df_transactions['mem_exp_month'] = df_transactions['membership_expire_date'].astype(str).apply(lambda x : int(x[4:6]))\n", "df_transactions['mem_exp_day'] = df_transactions['membership_expire_date'].astype(str).apply(lambda x : int(x[6:]))"]}, {"metadata": {"_cell_guid": "ec2eb1d1-4076-4f9c-8b64-d3ad8bb0f61f", "_uuid": "725ca2b8d729caefb21c86be04ea356c3f52a9c2"}, "cell_type": "markdown", "source": ["#### 5.2 Generate features"]}, {"outputs": [], "metadata": {"_cell_guid": "826a3a18-8bd6-42ba-8719-4ad875f680d6", "collapsed": true, "_uuid": "507f60b08193308fc2f491c3d86ebbe4e926c521"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions['exp_trans'] = (df_transactions['mem_exp_year'] - df_transactions['trans_year'])*365 \\\n", "                            + (df_transactions['mem_exp_month'] - df_transactions['trans_month'])*30 + \\\n", "                                (df_transactions['mem_exp_day'] - df_transactions['trans_day'])"]}, {"outputs": [], "metadata": {"_cell_guid": "e0f7c6b2-c301-4b67-9a18-58e4b4132125", "collapsed": true, "_uuid": "f29a547541614ba04f854c17cdb7eaecfa517068"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions['discount'] = df_transactions['actual_amount_paid'] - df_transactions['plan_list_price']"]}, {"outputs": [], "metadata": {"_cell_guid": "cf0c6f06-da7b-4908-b0e6-51c2a2a1d694", "collapsed": true, "_uuid": "acdca6b8d279b2cb6c8bf05d547e515cfc1e90cf"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions['price_per_day'] = df_transactions['actual_amount_paid'] / df_transactions['payment_plan_days']\n", "df_transactions.replace(np.inf, 0, inplace = True)\n", "df_transactions.fillna(0, inplace=True)"]}, {"outputs": [], "metadata": {"_cell_guid": "d10e8dee-c146-44f5-8467-e115a08a0c0b", "collapsed": true, "_uuid": "1c65a5c366d95452710803583b95fcb2addd2a64"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions.head()"]}, {"metadata": {"_cell_guid": "7797a565-ebe9-428c-b319-465afa567df4", "_uuid": "8ff6d300ecaf60bc873422b4a28283ef20b44b0e"}, "cell_type": "markdown", "source": ["#### 5.3  Data aggregation"]}, {"outputs": [], "metadata": {"_cell_guid": "0acf0862-da93-4372-b005-5d6487c3630f", "collapsed": true, "_uuid": "2fbabbfb85c35c7eb3c2cad28166cdb39ba34d4d"}, "cell_type": "code", "execution_count": null, "source": ["# get mean for payment_plan_days, exp_trans, discount plan_list_price, \n", "# actual_amount_paid and price_per_day\n", "trans_mean_cols = ['msno', 'payment_plan_days', 'exp_trans', 'discount', \n", "             'plan_list_price', 'actual_amount_paid', 'price_per_day']\n", "df_transactions_mean = df_transactions[trans_mean_cols].groupby(['msno']).mean().reset_index()\n", "print(df_transactions_mean.shape)\n", "df_transactions_mean.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "e7e6f968-f03c-49d5-9faa-ab9be9ee4d02", "collapsed": true, "_uuid": "31ad2fea2602c2c878cb4a56376f5f72bc80f4fc"}, "cell_type": "code", "execution_count": null, "source": ["# Counts for is_auto_renew and is_cancel\n", "trans_count_cols = ['msno', 'is_auto_renew', 'is_cancel']\n", "df_transactions_count = df_transactions[trans_count_cols].groupby(['msno']).sum().reset_index()\n", "print(df_transactions_count.shape)\n", "df_transactions_count.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "121e07cb-2371-496f-a8ca-a5e214565902", "collapsed": true, "_uuid": "6f6ec92d3936a9e7f556426ed7bafa55e8da2433"}, "cell_type": "code", "execution_count": null, "source": ["# frequency for payment_method_id, trans_year, trans_month, trans_day, mem_exp_year, \n", "# mem_exp_month, mem_exp_day\n", "trans_count_freq = ['msno', 'trans_year', 'trans_month', 'trans_day', 'mem_exp_year', \n", "                    'mem_exp_month', 'mem_exp_day']\n", "df_transactions_freq = df_transactions[trans_count_freq].groupby(['msno']).count().reset_index()\n", "del df_transactions\n", "df_transactions_freq.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "7f37962c-df55-45bf-b287-91e9f58e2dbe", "collapsed": true, "_uuid": "c2362c036c22170e3de080f0473bcefb07122da9"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions_proc = df_transactions_mean.merge(df_transactions_count, on = 'msno', \n", "                                                  how = 'outer').merge(df_transactions_freq, \n", "                                                                       on = 'msno', how = 'outer')\n", "df_transactions_proc.replace(np.inf, 0, inplace = True)\n", "df_transactions_proc.fillna(0, inplace=True)"]}, {"outputs": [], "metadata": {"_cell_guid": "f56f0a3c-2c30-4edb-935a-fcac52a320c4", "collapsed": true, "_uuid": "fc9b05179fa8ce83102db6a7d3c6fbd23980c441"}, "cell_type": "code", "execution_count": null, "source": ["df_transactions_train = df_train_only.merge(df_transactions_proc, on = 'msno', how = 'left')\n", "df_transactions_test = df_sample.merge(df_transactions_proc, on = 'msno', how = 'left')\n", "df_transactions_merge = df_transactions_train.merge(df_transactions_test, on = 'msno', how = 'outer')\n", "df_trans_train_not_churn = df_transactions_train[df_transactions_train['is_churn'] == 0]\n", "df_trans_train_churn = df_transactions_train[df_transactions_train['is_churn'] == 1]\n", "del df_transactions_proc"]}, {"metadata": {"_cell_guid": "fda41c1d-0a3e-4730-a7db-92d1ee71b398", "_uuid": "2bff8ac42b6543c307ca60f9b9d709392c29f957"}, "cell_type": "markdown", "source": ["#### 5.4 Distribution of churn and not churn in train "]}, {"outputs": [], "metadata": {"_cell_guid": "973571c3-fbd1-4726-953b-39e06b025328", "collapsed": true, "_uuid": "9ab661b7ef2020d0f07132146fec8a04bc9e752a"}, "cell_type": "code", "execution_count": null, "source": ["trans_cols = ['payment_plan_days', 'exp_trans', 'discount', \n", "             'plan_list_price', 'actual_amount_paid', 'price_per_day',\n", "              'is_auto_renew', 'is_cancel', 'trans_year', 'trans_month', 'trans_day', \n", "              'mem_exp_year', 'mem_exp_month', 'mem_exp_day']"]}, {"outputs": [], "metadata": {"_cell_guid": "2cd0e38e-2120-4fe3-8c8c-af54cd11ac6c", "collapsed": true, "_uuid": "268fdfd3cd2bab773bc7f5597baa2d71ba267490"}, "cell_type": "code", "execution_count": null, "source": ["dis_1(df_trans_train_not_churn, df_trans_train_churn, trans_cols)"]}, {"metadata": {"_cell_guid": "1ab2bf0f-2bd4-465b-9039-c3ed8e9473b5", "_uuid": "08e89b5f8ce0c446461a756ea3de9cc5b18571d6"}, "cell_type": "markdown", "source": ["#### 5.5 Distribution in train and test"]}, {"outputs": [], "metadata": {"_cell_guid": "e26ca063-6bd2-4370-b812-57db9253142d", "collapsed": true, "_uuid": "963fcbacd0bc8ac6b14df4558e048113a296d22c"}, "cell_type": "code", "execution_count": null, "source": ["dis_2(df_transactions_merge, trans_cols)"]}, {"metadata": {"_cell_guid": "67b364b8-75b0-490f-be92-183d52b09b5c", "_uuid": "7583bd31732b260db5f52067e8d569a670350dee"}, "cell_type": "markdown", "source": ["### 6. User Logs"]}, {"outputs": [], "metadata": {"_cell_guid": "10b91d0d-7a29-41d0-a2d0-1bf244d38d18", "collapsed": true, "_uuid": "deb22353ca1df38b5dc8becf499f3b21cc4eac4f"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs_2.columns = df_user_logs_1.columns\n", "df_user_logs_3.columns = df_user_logs_1.columns\n", "df_user_logs = pd.concat([df_user_logs_1, df_user_logs_2, df_user_logs_3], axis=0)"]}, {"outputs": [], "metadata": {"_cell_guid": "e3230152-a9d0-4b1b-8820-6a24d79aceac", "collapsed": true, "_uuid": "bcc4369c8042876408c347ed299605ab7a9f3026"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "f94572ee-e96d-4a66-943f-7a6e954dcd06", "collapsed": true, "_uuid": "2090fac7216e08bd38fb9bf821a089a60fbd6d52"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs.shape"]}, {"outputs": [], "metadata": {"_cell_guid": "309d0668-e5fb-4d26-bd0e-e757ca1a90d0", "collapsed": true, "_uuid": "9e107313265c821ab6921c9c8c7aa958171e7f17"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs.msno.unique().shape"]}, {"metadata": {"_cell_guid": "fa040e0b-c466-4983-a5c9-bc431b579a28", "_uuid": "d33a0b7ea9ce9a8a72fe2ffd7a4ddcd6c5b56c65"}, "cell_type": "markdown", "source": ["#### 6.1 Log features"]}, {"outputs": [], "metadata": {"_cell_guid": "49afd400-ff95-4d5c-a4ae-ada07e3a04d6", "collapsed": true, "_uuid": "adf6027350597f45f6349cde5cbd6c64d7e8d50f"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs['log_year'] = df_user_logs['date'].astype(str).apply(lambda x : int(x[:4]))\n", "df_user_logs['log_month'] = df_user_logs['date'].astype(str).apply(lambda x : int(x[4:6]))\n", "df_user_logs['log_day'] = df_user_logs['date'].astype(str).apply(lambda x : int(x[6:]))"]}, {"outputs": [], "metadata": {"_cell_guid": "4cede4c4-b09f-42b3-a162-d14d970ab9f0", "collapsed": true, "_uuid": "d573b1a61e2933537b5b63a917b1cc802bdbca5c"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs['avg_secs'] = df_user_logs['total_secs']/df_user_logs[['num_25', 'num_50', 'num_75', 'num_985', 'num_100']].sum(axis=1)"]}, {"outputs": [], "metadata": {"_cell_guid": "d5e1d189-6a6e-453d-8946-11765e785d15", "collapsed": true, "_uuid": "dab7b37002376f73d91b9355d4a40f81ee051b57"}, "cell_type": "code", "execution_count": null, "source": ["df_user_logs['num_dup'] = df_user_logs[['num_25', 'num_50', 'num_75', 'num_985', 'num_100']].sum(axis=1) - df_user_logs['num_unq']"]}, {"metadata": {"_cell_guid": "c040b66b-5628-450c-8518-e45aaa36b598", "_uuid": "a9677c196ed4dfde61fda214f61746589bb0fea8"}, "cell_type": "markdown", "source": ["#### 6.2 Aggregate data"]}, {"outputs": [], "metadata": {"_cell_guid": "26ff775e-fa2a-4c1e-8b47-3892388e5d22", "collapsed": true, "_uuid": "c0e399a2d1a8e992744a5331de54c426c992bf9a"}, "cell_type": "code", "execution_count": null, "source": ["# mean for 'num_25', 'num_50', 'num_75', 'num_985', 'num_100', 'num_unq', 'num_dup'\n", "user_logs_mean_cols = ['msno', 'num_25', 'num_50', 'num_75', 'num_985', 'num_100', 'num_unq', 'num_dup']\n", "df_user_logs_mean = df_user_logs[user_logs_mean_cols].groupby(['msno']).mean().reset_index()\n", "df_user_logs_mean.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "5545380c-facd-4307-979b-edd5219f419a", "collapsed": true, "_uuid": "fed2d251bf318d5c6a7b9f629bdbbcff1fc7e992"}, "cell_type": "code", "execution_count": null, "source": ["# frequency for log_year, log_month and log_day\n", "user_logs_count_cols = ['msno', 'log_year', 'log_month', 'log_day']\n", "df_user_logs_count = df_user_logs[user_logs_count_cols].groupby(['msno']).count().reset_index()\n", "df_user_logs_count.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "d5805732-de24-4f9c-bfb9-5d5efc6091fc", "collapsed": true, "_uuid": "d71c352f5940d25a9fc515b79e878f02d3c9c16e"}, "cell_type": "code", "execution_count": null, "source": ["df_logs_proc = df_user_logs_mean.merge(df_user_logs_count, on = 'msno', \n", "                                                  how = 'outer')\n", "df_logs_proc.replace(np.inf, 0, inplace = True)\n", "df_logs_proc.fillna(0, inplace=True)"]}, {"outputs": [], "metadata": {"_cell_guid": "4ccf1067-08c0-45cc-a8d5-0eed8e11e95e", "collapsed": true, "_uuid": "7d8fc1d3112a381ca7e331b662779a6fbbeab802"}, "cell_type": "code", "execution_count": null, "source": ["df_logs_train = df_train_only.merge(df_logs_proc, on = 'msno', how = 'left')\n", "df_logs_test = df_sample.merge(df_logs_proc, on = 'msno', how = 'left')\n", "df_logs_merge = df_logs_train.merge(df_logs_test, on = 'msno', how = 'outer')\n", "df_logs_train_not_churn = df_logs_train[df_logs_train['is_churn'] == 0]\n", "df_logs_train_churn = df_logs_train[df_logs_train['is_churn'] == 1]\n", "del df_logs_proc"]}, {"metadata": {"_cell_guid": "5b4614eb-e96e-4624-a4a5-f45fadd9458c", "_uuid": "5df6fe952884a6b21710d99b6103c4fde1594011"}, "cell_type": "markdown", "source": ["#### 6.3 Distribution of churn and not churn in train "]}, {"outputs": [], "metadata": {"_cell_guid": "82e32955-6bf4-4a55-9464-cc1811e1a977", "collapsed": true, "_uuid": "82c8ec53bd2f613d2a17e7066532d9876b91df7e"}, "cell_type": "code", "execution_count": null, "source": ["log_cols = ['num_25', 'num_50', 'num_75', 'num_985', 'num_100', 'num_unq', 'num_dup',\n", "           'log_year', 'log_month', 'log_day']\n", "dis_1(df_logs_train_not_churn, df_logs_train_churn, log_cols)"]}, {"metadata": {"_cell_guid": "fbd4459c-9584-4618-948e-314b8c9c2935", "_uuid": "b8b36d4559872655419422257a3ea8da94560178"}, "cell_type": "markdown", "source": ["#### 6.4 Distribution in train  and test"]}, {"outputs": [], "metadata": {"_cell_guid": "d8a48153-37ac-4040-b7c5-21b9642e24fa", "collapsed": true, "_uuid": "388aa9c00c83715f9aa8218ffadbd699a8d9af07"}, "cell_type": "code", "execution_count": null, "source": ["dis_2(df_logs_merge, log_cols)"]}, {"metadata": {"_cell_guid": "cc5d296b-e872-496c-87b5-37e0b6c24b77", "_uuid": "ecb505d202fb5c9c1fbab0d4a40c46a75be63c65"}, "cell_type": "markdown", "source": ["### 7. Feature Correlation"]}, {"outputs": [], "metadata": {"_cell_guid": "ffb5e823-47c8-45b8-9e84-19ea820339d9", "collapsed": true, "_uuid": "c0614dc1fc6e48670006c3fbf83bd93fe7b08fcb"}, "cell_type": "code", "execution_count": null, "source": ["import seaborn as sns\n", "def corr_analysis(df = None, corr_method = 'pearson', show_graph = False):\n", "    '''\n", "    function to analyze the correlation between features\n", "    '''\n", "    assert(not df.empty)\n", "    # compute correlation\n", "    df_corr = df.corr(method = corr_method)\n", "    print(df_corr['is_churn'])\n", "    # show heatmap graph\n", "    if show_graph:\n", "        plt.figure(figsize = (16,14))\n", "        mask = np.zeros_like(df_corr)\n", "        mask[np.triu_indices_from(mask)] = True\n", "        with sns.axes_style(\"white\"):\n", "            #matplotlib.rcParams.update({\"font.size\": 8})\n", "            sns.set(font_scale=1.2)\n", "            sns.heatmap(df_corr, cmap = \"coolwarm\", annot = False, mask = mask)\n", "            plt.yticks(rotation=0) \n", "            plt.xticks(rotation=90) \n", "            plt.title('Correlation Analysis Between Features')"]}, {"outputs": [], "metadata": {"_cell_guid": "d0a0a843-d432-4e43-9df3-33e37a97d87f", "collapsed": true, "_uuid": "b6d4f9c3c21ab71244ab8e857d4c15b6151dbacb"}, "cell_type": "code", "execution_count": null, "source": ["df = df_members_train.merge(df_transactions_train, on = ['msno', 'is_churn'], how = 'outer')\n", "df.head()"]}, {"outputs": [], "metadata": {"_cell_guid": "289ef382-0bcd-401b-936b-31f90890211c", "collapsed": true, "_uuid": "48383e046eecfb7b253ac0602debb63b8eefc774"}, "cell_type": "code", "execution_count": null, "source": ["corr_analysis(df = df, corr_method = 'pearson', show_graph = True)"]}, {"outputs": [], "metadata": {"_cell_guid": "4d825fe0-0b20-4f47-a7d7-f63f675e0965", "collapsed": true, "_uuid": "daf4e25d7f0f6e42dae2dcd4e94c7dd6adc25827"}, "cell_type": "code", "execution_count": null, "source": []}], "nbformat_minor": 1, "nbformat": 4}