{"nbformat": 4, "nbformat_minor": 1, "metadata": {"language_info": {"codemirror_mode": {"version": 3, "name": "ipython"}, "pygments_lexer": "ipython3", "mimetype": "text/x-python", "nbconvert_exporter": "python", "name": "python", "file_extension": ".py", "version": "3.6.3"}, "kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}}, "cells": [{"cell_type": "code", "outputs": [], "metadata": {"_cell_guid": "7dd4f67d-9c2a-4142-ac9c-604253339c44", "collapsed": true, "_uuid": "6d30ff559a9d8410fccd72b29fb9c4626f55aec2"}, "source": ["#code review welcome.  I'm pretty sure this is totally not pythonic.  I'm new.\n", "#also any ideas for more pleasing graphical formatting are welcome.\n", "#\n", "#Feel free to use the code if its of use.\n", "#\n", "#Code for 10-fold cross validation set below, commented out.\n", "#I don't think it will work on kaggle b/c of incremental saves.."], "execution_count": null}, {"cell_type": "code", "outputs": [], "metadata": {"_cell_guid": "14d4d1a8-6d42-4713-a0ff-465d0ab07c4f", "collapsed": true, "_uuid": "414a20f03dfb786bc74c0f63bbcc2ad63a614f0b"}, "source": ["import pandas as pd # data processing, CSV file I/O (e.g. pd. read_csv)\n", "import numpy as np # linear algebra\n", "\n", "import datetime\n", "from datetime import timedelta\n", "from dateutil.relativedelta import relativedelta\n", "\n", "#imports for saving files\n", "#from pathlib import Path\n", "#import os.path\n", "\n", "#import for alternate, random msnos\n", "#from random import randint\n", "\n", "import matplotlib\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "\n", "%matplotlib inline\n", "sns.set_style(\"whitegrid\")\n", "\n", "pd.options.mode.chained_assignment = None # default='warn'"], "execution_count": null}, {"cell_type": "code", "outputs": [], "metadata": {"_cell_guid": "e89bc510-7fae-4ba1-b900-5d6584a0b134", "collapsed": true, "_uuid": "1ee68f10e28752f85531c6b8d7a18b9ed5b87662"}, "source": ["train = pd.read_csv('../input/kkbox-churn-prediction-challenge/train.csv', nrows=20000)\n", "#cross_validation_set = 0\n", "#train = pd.read_csv('../input/kkbox-churn-prediction-challenge/train.csv', nrows=99000, skiprows=range(1, (cross_validation_set * 99000 - 1)))\n", "\n", "members = pd.read_csv('../input/kkbox-churn-prediction-challenge/members.csv')\n", "members = members.loc[members['msno'].isin(train['msno'])]\n", "\n", "transactions = pd.read_csv('../input/kkbox-churn-prediction-challenge/transactions.csv')\n", "transactions = transactions.loc[transactions['msno'].isin(train['msno'])]\n", "\n", "userLog = pd.read_csv('../input/small-userlog-sample/UserLog_train0.csv')\n", "#if you have the cross validation sets locally, load with this...\n", "#userLog = pd.read_csv('F:kaggle/UserLog_train' + str(cross_validation_set) + '.csv')"], "execution_count": null}, {"cell_type": "code", "outputs": [], "metadata": {"_cell_guid": "7fd6bdd0-1533-421f-8564-b307601d97be", "collapsed": true, "_uuid": "986744d7380e29c6c1e95c0510fc7315cdadb121"}, "source": ["exUser = pd.merge(train, userLog, how='inner', on=['msno']).msno.unique()"], "execution_count": null}, {"cell_type": "code", "outputs": [], "metadata": {"_cell_guid": "31235b73-b099-469d-93cf-4bfb5f4b2e82", "scrolled": false, "collapsed": true, "_uuid": "173f3ca7653e6ef910a069612e0c72f7411a21b3"}, "source": ["## This code produces a 10-fold cross validation training set.\n", "## I'm not sure if it will work on kaggle, so this notebook uses\n", "## a smaller set (first 20,000 users in train)\n", "\n", "#user_log_length = pd.read_csv('E:kaggle csvs/first kaggle stay or go/user_logs.csv', usecols=['msno'])\n", "#number_of_records_to_go_through = max(user_log_length.shape)\n", "#del user_log_length\n", "##above code returns this          vvvvvvvvv\n", "#number_of_records_to_go_through = 392106543\n", "\n", "####WARNING: THIS WILL TAKE QUITE A LONG TIME TO DO ITS MAGIC.  MAKE SURE YOU CHANGE THE DIRECTORIES TO SUIT YOUR NEEDS.\n", "\n", "#num_records impacts speed substantially,- if anything crashes, try lowering this to 20000000 to 40000000\n", "#but the lower the number, the slower, it has to read from the first line of the file, even skipping rows.\n", "#num_records = 70000000\n", "#\n", "#for cv_fold in range (0, 10):\n", "#    num_records_left = number_of_records_to_go_through\n", "#    chunk = 0\n", "#\n", "#    train = pd.read_csv('E:kaggle csvs/first kaggle stay or go/train.csv', nrows=99000, skiprows=range(1, (cv_fold * 99000 - 1)))\n", "#\n", "#    while (num_records_left > num_records):\n", "#        print('starting on chunk number ' + str(chunk))\n", "#\n", "#        userLog = pd.read_csv('E:kaggle csvs/first kaggle stay or go/user_logs.csv', skiprows = (num_records*chunk), nrows=(num_records), header=None)\n", "#        userLog.rename(columns ={0: 'msno', 1:'date', 2:'num_25', 3:'num_50', 4:'num_75:', 5:'num_985', 6:'num_100', 7:'num_unq', 8:'total_secs'}, inplace=True)\n", "#        userLog_train = userLog.loc[userLog['msno'].isin(train['msno'])]\n", "#\n", "#        chunk += 1\n", "#        num_records_left = num_records_left - num_records\n", "#        \n", "#        persistent_save = Path(\"F:kaggle/userLog_train\" + str(cv_fold) + \".csv\")\n", "#        if persistent_save.is_file():\n", "#            with open('F:kaggle/userLog_train' + str(cv_fold) + '.csv', 'a') as f:\n", "#                userLog_train.to_csv(f, header=False)\n", "#                f.close()\n", "#                print('csv appended')\n", "#        else:\n", "#            userLog_train.to_csv('F:kaggle/UserLog_train' + str(cv_fold) + '.csv')\n", "#            print('csv created')\n", "#    \n", "#        del userLog\n", "#        del userLog_train\n", "#\n", "#    print('made it to the final group of records!')\n", "#\n", "#    userLog = pd.read_csv('E:kaggle csvs/first kaggle stay or go/user_logs.csv', skiprows = (num_records*(chunk)), nrows=(num_records_left - 1), header=None)\n", "#    userLog.rename(columns ={0: 'msno', 1:'date', 2:'num_25', 3:'num_50', 4:'num_75:', 5:'num_985', 6:'num_100', 7:'num_unq', 8:'total_secs'}, inplace=True)\n", "#    userLog_train = userLog.loc[userLog['msno'].isin(train['msno'])]\n", "#\n", "#    persistent_save = Path(\"F:kaggle/userLog_train\" + str(cv_fold) + \".csv\")\n", "#    if persistent_save.is_file():\n", "#        with open('F:kaggle/userLog_train' + str(cv_fold) + '.csv', 'a') as f:\n", "#            userLog_train.to_csv(f, header=False)\n", "#            f.close()\n", "#            print('csv appended')\n", "#    else:\n", "#        userLog_train.to_csv('F:kaggle/UserLog_train' + str(cv_fold) + '.csv')\n", "#        print('csv created')"], "execution_count": null}, {"cell_type": "code", "outputs": [], "metadata": {"_cell_guid": "d8a57a77-c3c4-41f5-ba98-eb96ac9ca499", "scrolled": false, "_uuid": "25f541307988c694a7bd09953af4d3a9c44bc579"}, "source": ["#different values for xyz give graphs of different users' timeseries\n", "\n", "# PLAY HERE\n", "#    |\n", "#   \\/\n", "xyz=1000\n", "numberOfUsers=100\n", "\n", "for user in range(0, numberOfUsers):\n", "    ###################################\n", "    ###  USER SUBSCRIPTION HISTORY  ###\n", "    ###################################\n", "    #xyz = randint(0,40000)\n", "    trans = transactions.loc[transactions['msno'] == exUser[xyz]]\n", "    trans['startDate'] = trans['transaction_date'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d %H:%M:%S'))\n", "    trans['endDate'] = trans['membership_expire_date'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d %H:%M:%S'))\n", "    trans['linewidth']= (trans['actual_amount_paid'] / trans['payment_plan_days'])\n", "    trans = trans[['startDate','endDate','linewidth']]\n", "    trans = trans.sort_values('startDate')\n", "    trans.index = pd.RangeIndex(len(trans.index))\n", "    trans['type']='transaction'\n", "    trans['color']='slateblue'\n", "    \n", "#some transactions are in reverse chronological order, this flips the two dates.\n", "    for x in range(0, (len(trans.index))):\n", "        if (trans.endDate[x] < trans.startDate[x]):\n", "            temp = trans.endDate[x]\n", "            trans.endDate[x] = trans.startDate[x]\n", "            trans.startDate[x] = temp\n", "            \n", "    trans = trans.sort_values('startDate')\n", "    trans.index = pd.RangeIndex(len(trans.index))\n", "    \n", "#merges single-day overlaps\n", "#    indexDeletions = []\n", "#    for x in range(0, (len(trans.index) - 1)):\n", "#        if (trans.endDate[x] == trans.startDate[x + 1]):\n", "#            trans.endDate[x] = trans.endDate[x+1]\n", "#            indexDeletions.append(x+1)\n", "#    for index in range(len(indexDeletions), 0):\n", "#        trans.drop(indexDeletions[(index)], inplace=True)\n", "#    trans.index = pd.RangeIndex(len(trans.index))    \n", "\n", "    #find lapses, churns, and redundant subscription in transactions\n", "    beginLapse = []\n", "    endLapse = []\n", "    colorLapse = []\n", "    \n", "    beginChurn = []\n", "    endChurn = []\n", "    \n", "    for x in range(0, (len(trans.index) - 1)):\n", "        #lapse\n", "        if (trans.endDate[x] < (trans.startDate[x+1] - timedelta(days=1))):\n", "            beginLapse.append((trans.endDate[x] + timedelta(days=1)))\n", "            endLapse.append(trans.startDate[x+1] - timedelta(days=1))\n", "            colorLapse.append('darkgrey')\n", "        #redundant subscription\n", "        if (trans.endDate[x] >= (trans.startDate[x+1])):\n", "            beginLapse.append(trans.endDate[x])\n", "            endLapse.append(trans.startDate[x+1])\n", "            colorLapse.append('greenyellow')\n", "        #churn\n", "        if ((trans.endDate[x] + relativedelta(months=1)) < trans.startDate.iloc[x+1]):\n", "            beginChurn.append(trans.endDate[x] + relativedelta(months=1))\n", "            endChurn.append(trans.startDate[x+1])\n", "\n", "    lapses = pd.DataFrame({'beginLapse': beginLapse, 'endLapse' : endLapse, 'color': colorLapse})\n", "    churn = pd.DataFrame({'beginChurn' : beginChurn, 'endChurn' : endChurn})  \n", "    \n", "    ################################\n", "    ###  USER LISTENING HISTORY  ###\n", "    ################################\n", "    cleanedUserLog = userLog.loc[userLog['msno'] == exUser[xyz]]\n", "    cleanedUserLog['endDate'] = cleanedUserLog['date'].apply(lambda x: pd.to_datetime(str(x), format='%Y%m%d %H:%M:%S'))\n", "    cleanedUserLog['startDate'] = cleanedUserLog['endDate'] - timedelta(days=1)\n", "    cleanedUserLog['linewidth'] = cleanedUserLog['total_secs'] / 1200\n", "    cleanedUserLog['color'] = 'darkgoldenrod'\n", "    cleanedUserLog['type'] = 'userLog'\n", "    \n", "    ##############\n", "    ###  plot  ###\n", "    ##############\n", "    matplotlib.rcParams['figure.figsize'] = (16, 1)\n", "    frames = [trans, cleanedUserLog]\n", "    ex = pd.concat(frames)\n", "    ex = ex.sort_values('startDate')\n", "    ex = ex.reset_index(drop=True)\n", "\n", "    for x in range (0, len(ex.index)):\n", "            plt.hlines(0, ex.startDate.iloc[x], ex.endDate.iloc[x], ex.color.iloc[x], linewidth=ex.linewidth.iloc[x], alpha = .70)\n", "\n", "    for y in range (0, (len(lapses))):\n", "        plt.axvspan(lapses.endLapse.iloc[y], lapses.beginLapse.iloc[y], ymin = .3, ymax = .7, facecolor = lapses.color.iloc[y], alpha=.4)\n", "        \n", "    for z in range (0, (len(churn))):\n", "        plt.axvspan(churn.endChurn.iloc[z], churn.beginChurn.iloc[z], facecolor = 'k', alpha=.25)\n", "\n", "    plt.ylabel('logged event')\n", "    plt.xlabel('date')\n", "    ischurn= train.iloc[xyz].is_churn\n", "    \n", "    plt.title('User History for: xyz = ' + str(xyz) + ',  ' + exUser[xyz] + ',  is_churn=' + str(ischurn))\n", "    plt.show()\n", "\n", "    xyz += 1\n", "    \n", "    #Logs are in brown, their height proportional to time the user listened to music.\n", "    #The purple line indicates an active subscription, height proportional to cost.\n", "    #grey boxes indicate lapses in subscription\n", "    #green indicates double subscription\n", "    #black indicates the user is in churn  "], "execution_count": null}]}