{"cells": [{"execution_count": null, "source": ["!ls ../input"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "5d46827a51154c0b392e793480527b9cca75b239", "_cell_guid": "7c854f9a-77e8-473a-a22f-f1a0f2556776", "collapsed": false, "scrolled": true}}, {"execution_count": null, "source": ["from multiprocessing import Pool, cpu_count\n", "import gc; gc.enable()\n", "import xgboost as xgb\n", "import pandas as pd\n", "import numpy as np\n", "from sklearn import *\n", "import sklearn"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "e483b0cd19fcd1d1d0f913b3586877b32e45d917", "_cell_guid": "2b881b2f-b5d3-4638-8e69-f05a6b92f32c", "collapsed": false}}, {"execution_count": null, "source": ["train = pd.read_csv('../input/train.csv')\n", "train = pd.concat((train, pd.read_csv('../input/train_v2.csv')), axis=0, ignore_index=True).reset_index(drop=True)\n", "test = pd.read_csv('../input/sample_submission_v2.csv')\n", "\n", "transactions = pd.read_csv('../input/transactions.csv', usecols=['msno'])\n", "transactions = pd.concat((transactions, pd.read_csv('../input/transactions_v2.csv', usecols=['msno'])), axis=0, ignore_index=True).reset_index(drop=True)\n", "transactions = pd.DataFrame(transactions['msno'].value_counts().reset_index())\n", "transactions.columns = ['msno','trans_count']\n", "train = pd.merge(train, transactions, how='left', on='msno')\n", "test = pd.merge(test, transactions, how='left', on='msno')\n", "transactions = []; print('transaction merge...')\n", "\n", "user_logs = pd.read_csv('../input/user_logs_v2.csv', usecols=['msno'])\n", "#user_logs = pd.read_csv('../input/user_logs.csv', usecols=['msno'])\n", "#user_logs = pd.concat((user_logs, pd.read_csv('../input/user_logs_v2.csv', usecols=['msno'])), axis=0, ignore_index=True).reset_index(drop=True)\n", "user_logs = pd.DataFrame(user_logs['msno'].value_counts().reset_index())\n", "user_logs.columns = ['msno','logs_count']\n", "train = pd.merge(train, user_logs, how='left', on='msno')\n", "test = pd.merge(test, user_logs, how='left', on='msno')\n", "user_logs = []; print('user logs merge...')\n", "\n", "members = pd.read_csv('../input/members_v3.csv')\n", "train = pd.merge(train, members, how='left', on='msno')\n", "test = pd.merge(test, members, how='left', on='msno')\n", "members = []; print('members merge...') "], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "0797f9105fc62785850af750fd949002f8b9a322", "_cell_guid": "f229ddc8-6924-4abf-8719-e0ec3c652863", "collapsed": false}}, {"execution_count": null, "source": ["gender = {'male':1, 'female':2}\n", "train['gender'] = train['gender'].map(gender)\n", "test['gender'] = test['gender'].map(gender)\n", "\n", "train = train.fillna(0)\n", "test = test.fillna(0)"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "b24e7f1fb0c01887208a020356d783e46c7b4172", "_cell_guid": "48c17f47-87b0-4bc5-99f2-f4ba90faa921", "collapsed": true}}, {"execution_count": null, "source": ["transactions = pd.read_csv('../input/transactions.csv')\n", "transactions = pd.concat((transactions, pd.read_csv('../input/transactions_v2.csv')), axis=0, ignore_index=True).reset_index(drop=True)\n", "transactions = transactions.sort_values(by=['transaction_date'], ascending=[False]).reset_index(drop=True)\n", "transactions = transactions.drop_duplicates(subset=['msno'], keep='first')\n", "\n", "train = pd.merge(train, transactions, how='left', on='msno')\n", "test = pd.merge(test, transactions, how='left', on='msno')\n", "transactions=[]"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "2d1849e5b6c07933039b3d025b3390a7db320c01", "_cell_guid": "4a52098a-2eee-401f-8b5d-eddaa5e28fbd", "collapsed": true}}, {"execution_count": null, "source": [], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "a4ea393e7b7d6055a3600a934403fbc1a116261f", "_cell_guid": "3b95b6d4-067e-49f7-a475-3944f426904d", "collapsed": true}}, {"execution_count": null, "source": ["def transform_df(df):\n", "    df = pd.DataFrame(df)\n", "    df = df.sort_values(by=['date'], ascending=[False])\n", "    df = df.reset_index(drop=True)\n", "    df = df.drop_duplicates(subset=['msno'], keep='first')\n", "    return df\n", "\n", "def transform_df2(df):\n", "    df = df.sort_values(by=['date'], ascending=[False])\n", "    df = df.reset_index(drop=True)\n", "    df = df.drop_duplicates(subset=['msno'], keep='first')\n", "    return df\n", "\n", "df_iter = pd.read_csv('../input/user_logs.csv', low_memory=False, iterator=True, chunksize=10000000)\n", "last_user_logs = []\n", "i = 0 #~400 Million Records - starting at the end but remove locally if needed\n", "for df in df_iter:\n", "    if i>35:\n", "        if len(df)>0:\n", "            print(df.shape)\n", "            p = Pool(cpu_count())\n", "            df = p.map(transform_df, np.array_split(df, cpu_count()))   \n", "            df = pd.concat(df, axis=0, ignore_index=True).reset_index(drop=True)\n", "            df = transform_df2(df)\n", "            p.close(); p.join()\n", "            last_user_logs.append(df)\n", "            print('...', df.shape)\n", "            df = []\n", "    i+=1\n", "last_user_logs.append(transform_df(pd.read_csv('../input/user_logs_v2.csv')))\n", "last_user_logs = pd.concat(last_user_logs, axis=0, ignore_index=True).reset_index(drop=True)\n", "last_user_logs = transform_df2(last_user_logs)\n", "\n", "train = pd.merge(train, last_user_logs, how='left', on='msno')\n", "test = pd.merge(test, last_user_logs, how='left', on='msno')\n", "last_user_logs=[]"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "0de7839769997cade8bb31b96ec40df398edee90", "_cell_guid": "ef0ff7bd-cc4a-4603-b8a4-72591b6ba6c7", "collapsed": false}}, {"execution_count": null, "source": ["print(train.shape)\n", "train.head()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "3b806098a222571e931e3bebcc78f3f69a03d2d0", "_cell_guid": "7140b459-5504-40fe-9c93-7d201bcd327c", "collapsed": false, "scrolled": true}}, {"execution_count": null, "source": ["pd.to_datetime(train.tail(9).transaction_date,format=\"%Y%m%d\",errors=\"coerce\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "8b5ac23f909c78e205c3f58edc6471dcd7828bdb", "_cell_guid": "7ac4b2b3-8efc-43f6-8550-01bcedd69974", "collapsed": false}}, {"execution_count": null, "source": ["# train.registration_init_time = pd.to_datetime(train.registration_init_time,format=\"%Y%m%d\",errors=\"coerce\")\n", "# test.registration_init_time = pd.to_datetime(test.registration_init_time,format=\"%Y%m%d\",errors=\"coerce\")\n", "\n", "# train.registration_init_time.tail()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "96d8e5be66d0c32b71af46a1c99e8766e5d08e7d", "_cell_guid": "d85a4288-b395-467e-8e4c-871dfcfe36da", "collapsed": false}}, {"execution_count": null, "source": ["date_cols = [\"transaction_date\",\"membership_expire_date\",\"date\",\"registration_init_time\"]"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "e2450be6c5eba1ea51415eb7223ef5343c3c8c8c", "_cell_guid": "cb0d441e-4fc7-40ea-8965-06a25f3fbba9", "collapsed": true}}, {"execution_count": null, "source": ["test[date_cols].tail()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "8218bbfeb87ec58fe76c4b0f8b74a58112d371c4", "_cell_guid": "b7395b9a-4815-4579-8a6b-51edeb5af460", "collapsed": false}}, {"execution_count": null, "source": ["# pd.to_datetime(train.transaction_date,format=\"%Y%m%d\")\n", "# pd.to_datetime(test.transaction_date,format=\"%Y%m%d\")\n", "\n", "# pd.to_datetime(train.membership_expire_date,format=\"%Y%m%d\")\n", "# pd.to_datetime(test.membership_expire_date,format=\"%Y%m%d\")\n", "\n", "# pd.to_datetime(test.date,format=\"%Y%m%d.0\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "b73a975cdc2d45fa2fa487c9d388b2e2707d7652", "_cell_guid": "78a96559-c651-46fe-912e-743544216460", "collapsed": true}}, {"execution_count": null, "source": ["# pd.to_datetime(train.tail().registration_init_time.astype(int),format=\"%Y%m%d\",errors=\"coerce\")\n", "# pd.to_datetime(train.tail().date.fillna(0).astype(int),format=\"%Y%m%d\",errors=\"coerce\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "3be0477f3de63fe1f04c77c445d863ee0daf2f78", "_cell_guid": "469336ea-5e73-4086-9046-9569eedf6de7", "collapsed": false, "scrolled": true}}, {"execution_count": null, "source": ["### try to coerce? \n", "\n", "train.transaction_date = pd.to_datetime(train.transaction_date,format=\"%Y%m%d\")\n", "test.transaction_date = pd.to_datetime(test.transaction_date,format=\"%Y%m%d\")\n", "\n", "train.membership_expire_date = pd.to_datetime(train.membership_expire_date,format=\"%Y%m%d\")\n", "test.membership_expire_date = pd.to_datetime(test.membership_expire_date,format=\"%Y%m%d\")\n", "\n", "train.registration_init_time = pd.to_datetime(train.registration_init_time.astype(int),format=\"%Y%m%d\",errors=\"coerce\")\n", "test.registration_init_time = pd.to_datetime(test.registration_init_time.astype(int),format=\"%Y%m%d\",errors=\"coerce\")\n", "\n", "train.date = pd.to_datetime(train.date.fillna(0).astype(int),format=\"%Y%m%d\",errors=\"coerce\")\n", "test.date = pd.to_datetime(test.date.fillna(0).astype(int),format=\"%Y%m%d\",errors=\"coerce\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "dd514e4651e7260560692a3b5048c1dea4e206a0", "_cell_guid": "7c950ece-7dfd-4572-a362-8a87bea467e4", "collapsed": false}}, {"execution_count": null, "source": ["train.head()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "b2547285ec3562006524f5b08eb15d95db4bc473", "_cell_guid": "935eed3e-7ef3-489a-8871-c3475e46c28d", "collapsed": false}}, {"source": ["# add some features (in primary data, not context, for now)\n", "\n", "* https://www.kaggle.com/danofer/kkbox-churn-getting-started"], "cell_type": "markdown", "metadata": {"_uuid": "fac8bcf9c4aebc1699422c7cdaf7a8337c45b44b", "_cell_guid": "f0027700-fb3f-452f-9cc0-e340d40f90e5"}}, {"execution_count": null, "source": ["# train[\"sum_nan\"] = train.isnull().sum(axis=1)\n", "# train[\"sum_nan\"].describe()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "f0c782c6a875517363fbcc87adb4b5e204e898cc", "_cell_guid": "021eafb9-e96e-4e99-a696-10777837b444", "collapsed": true}}, {"execution_count": null, "source": ["train[\"played_songs_nonUnique_ratio\"] = (train['num_100'] + train['num_25'] + train['num_50'] + train['num_75'] + train['num_985'])/train[\"num_unq\"]\n", "test[\"played_songs_nonUnique_ratio\"] = (test['num_100'] + test['num_25'] + test['num_50'] + test['num_75'] + test['num_985'])/test[\"num_unq\"]\n", "\n", "train[\"played_songs_nonUnique_ratio\"].describe()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "61df4c12907db439b3b5be5a41066943050972ce", "_cell_guid": "7c06db85-b2f4-48ee-a3ed-2f84530ddf1c", "collapsed": false}}, {"execution_count": null, "source": ["train[\"price_paid_diff\"]  = train.plan_list_price -  train.actual_amount_paid\n", "test[\"price_paid_diff\"]  = test.plan_list_price -  test.actual_amount_paid"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "eba68180adb88ad9ac998036924ea7920dad96c2", "_cell_guid": "8f26022f-ce1c-4166-ab14-67b31c4ff4cd", "collapsed": true}}, {"execution_count": null, "source": ["# train = train.fillna(0)\n", "# test = test.fillna(0)\n", "\n", "# cols = [c for c in train.columns if c not in ['is_churn','msno']]"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "10a1d624dcb3c7b4a4209aa642f5a837d92a2f09", "_cell_guid": "df0fd7c7-dad2-41b9-b144-0ea2d4b9fae4", "collapsed": true}}, {"execution_count": null, "source": ["# def xgb_score(preds, dtrain):\n", "#     labels = dtrain.get_label()\n", "#     return 'log_loss', metrics.log_loss(labels, preds)\n", "\n", "# fold = 1\n", "# for i in range(fold):\n", "#     params = {\n", "#         'eta': 0.02, #use 0.002\n", "#         'max_depth': 7,\n", "#         'objective': 'binary:logistic',\n", "#         'eval_metric': 'logloss',\n", "#         'seed': i,\n", "#         'silent': True\n", "#     }\n", "#     x1, x2, y1, y2 = model_selection.train_test_split(train[cols], train['is_churn'], test_size=0.3, random_state=i)\n", "#     watchlist = [(xgb.DMatrix(x1, y1), 'train'), (xgb.DMatrix(x2, y2), 'valid')]\n", "#     model = xgb.train(params, xgb.DMatrix(x1, y1), 150,  watchlist, feval=xgb_score, maximize=False, verbose_eval=50, early_stopping_rounds=50) #use 1500\n", "#     if i != 0:\n", "#         pred += model.predict(xgb.DMatrix(test[cols]), ntree_limit=model.best_ntree_limit)\n", "#     else:\n", "#         pred = model.predict(xgb.DMatrix(test[cols]), ntree_limit=model.best_ntree_limit)\n", "# pred /= fold\n", "# test['is_churn'] = pred.clip(0.+1e-15, 1-1e-15)\n", "# test[['msno','is_churn']].to_csv('submission.csv', index=False)\n", "# #test[['msno','is_churn']].to_csv('submission.csv.gz', index=False, compression='gzip')"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "cb258ccda2afac4d0e8ed0285da798c3808a5920", "_cell_guid": "00d437d9-df05-4f0c-a947-e2ca6cf0bac2", "collapsed": true}}, {"execution_count": null, "source": ["# import matplotlib.pyplot as plt\n", "# import seaborn as sns\n", "# %matplotlib inline\n", "\n", "# plt.rcParams['figure.figsize'] = (7.0, 7.0)\n", "# xgb.plot_importance(booster=model); plt.show()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "04f54b577acd293fd6fb73eff71f7548577817c1", "_cell_guid": "5f0517ba-8774-49e0-9cbe-4de4704a8e15", "collapsed": true}}, {"execution_count": null, "source": ["train.to_csv(\"kkbox_churn_train_v3.csv.gz\",index=False,compression=\"gzip\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "d11e9737e569f2892d9b5092912739b2eaec3cdb", "_cell_guid": "44d483ad-894e-4516-907e-4f7f3a5d1149", "collapsed": true}}, {"execution_count": null, "source": ["test.to_csv(\"kkbox_churn_test_v3.csv.gz\",index=False,compression=\"gzip\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "36dd2e88765ab02991519cfa05ecf9b9cb1b2c12", "_cell_guid": "a8b5aec8-535a-42d3-a6d0-4b27976de65a", "collapsed": true}}, {"source": ["# merge the rawer logs data\n", "\n", "* seperate kernel due to disk space:\n", "* https://www.kaggle.com/danofer/get-extra-data-kkbox-churn-v2/"], "cell_type": "markdown", "metadata": {"_uuid": "a0de9b3f56778e96e4a248f6d4b06a8783fc2433", "_cell_guid": "d6f64459-b0c6-4d01-b416-27c7634e9992"}}, {"execution_count": null, "source": ["# transactions = pd.read_csv('../input/transactions.csv',nrows=1000)\n", "# transactions = pd.concat((transactions, pd.read_csv('../input/transactions_v2.csv')), axis=0, ignore_index=True).reset_index(drop=True)\n", "# transactions_msno_counts = pd.DataFrame(transactions['msno'].value_counts().reset_index()).rename(columns={\"msno\":\"msno_counts\"})\n", "# print(transactions_msno_counts.shape)\n", "\n", "# transactions.membership_expire_date = pd.to_datetime(transactions.membership_expire_date,format=\"%Y%m%d\")\n", "# transactions.transaction_date = pd.to_datetime(transactions.transaction_date,format=\"%Y%m%d\")\n", "\n", "# print(transactions.shape)\n", "# transactions.head()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "ee0cf12f42df711340d2cf096e69b02fcaad915a", "_cell_guid": "3c4485e1-4886-4a04-b693-271f70a2edcd", "collapsed": false}}, {"execution_count": null, "source": ["# transactions[\"price_paid_diff\"]  = transactions.plan_list_price -  transactions.actual_amount_paid"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "cefa10a39de3520edb3e550b31341027b2d3c29d", "_cell_guid": "d7147f35-1b91-4291-8998-acfe84f0a2f0", "collapsed": false, "scrolled": false}}, {"execution_count": null, "source": ["# transactions[\"transactions_expiry_transaction_days_diff\"] = ((transactions.membership_expire_date - transactions.transaction_date).dt.days) "], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "e443ab20fc9bcc6b843edbc4fc04cfe4016ceff4", "_cell_guid": "d09068eb-d1d2-4f10-932d-2f70245cd5c9", "collapsed": false}}, {"execution_count": null, "source": ["# transactions[\"transactions_expiry_transaction_days_diff_div_plan\"] = transactions.payment_plan_days/transactions.transactions_expiry_transaction_days_diff"], "cell_type": "code", "outputs": [], "metadata": {"collapsed": true}}, {"execution_count": null, "source": ["# transactions[\"transactions_expiry_transaction_days_diff\"].describe()\n", "# transactions[\"transactions_expiry_transaction_days_diff_div_plan\"].describe()"], "cell_type": "code", "outputs": [], "metadata": {"collapsed": false}}, {"execution_count": null, "source": ["# (transactions[\"transactions_expiry_transaction_days_diff\"] != transactions.payment_plan_days).sum()"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "0633b822561040f29c7789bae1d15ab671e77439", "_cell_guid": "de33cb09-f32f-4e94-8d03-afc3bf6f1681", "collapsed": true}}, {"execution_count": null, "source": ["# transactions.to_csv(\"kkbox_churn_transactions_v3.csv.gz\",index=False,compression=\"gzip\")"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "008834a5aa9e30b737de6bcad1338ec64137933a", "_cell_guid": "c744b84d-78c9-4a42-ac07-53402e2e9b72", "collapsed": true}}, {"execution_count": null, "source": ["def get_date_diffs(df):\n", "    \"\"\"\n", "    Get time between the expiry date and other columns in days + add day of week, month features.\n", "     - Could add more, e.g. time between other dates, is weekend, etc' .\n", "     membership_expire_date is the deciding date for churn determination.\n", "     # https://www.kaggle.com/danofer/kkbox-churn-getting-started\n", "    \"\"\"\n", "    df[\"exp-registration-diff\"] = (df.membership_expire_date - df.registration_init_time ).dt.days\n", "    df[\"exp-transaction-diff\"] = (df.membership_expire_date - df.transaction_date ).dt.days\n", "    df[\"exp-expiration-diff\"] = (df.membership_expire_date - df.expiration_date ).dt.days\n", "    df[\"exp-logdate-diff\"] = (df.membership_expire_date - df[\"date\"] ).dt.days\n", "    \n", "    for col in dateCols:\n", "        df[\"dayOfWeek_%s\" %(col)] = df[col].dt.dayofweek\n", "        df[\"dayOfMonth_%s\" %(col)] = df[col].dt.day\n", "    \n", "    df[\"payment_plan_days_div-exp-expiration-diff\"] = df.payment_plan_days / df[\"exp-expiration-diff\"]\n", "    df[\"payment_plan_days_div-exp-transaction-diff\"] = df.payment_plan_days / df[\"exp-transaction-diff\"]\n", "    df[\"payment_plan_days_div-eexp-logdate-diff\"] = df.payment_plan_days / df[\"exp-logdate-diff\"]"], "cell_type": "code", "outputs": [], "metadata": {"_uuid": "74e8837e8866d39e01c6792c5d2b11c853a5cbc2", "_cell_guid": "1ce7146e-812b-431a-b879-ab4a2dbc9f66", "collapsed": true}}], "nbformat_minor": 0, "nbformat": 4, "metadata": {"kernelspec": {"language": "python", "display_name": "Python 3", "name": "python3"}, "language_info": {"codemirror_mode": {"version": 3, "name": "ipython"}, "pygments_lexer": "ipython3", "name": "python", "nbconvert_exporter": "python", "mimetype": "text/x-python", "version": "3.6.3", "file_extension": ".py"}}}