{"metadata": {"language_info": {"codemirror_mode": {"version": 3, "name": "ipython"}, "name": "python", "file_extension": ".py", "version": "3.6.3", "mimetype": "text/x-python", "pygments_lexer": "ipython3", "nbconvert_exporter": "python"}, "kernelspec": {"display_name": "Python 3", "language": "python", "name": "python3"}}, "nbformat_minor": 1, "cells": [{"metadata": {"_uuid": "c44e06f92110a8042cd12480af4c06cf3722c1b2", "_cell_guid": "e479d15d-22fc-4c0c-b510-cc1a37b04d63"}, "cell_type": "markdown", "source": ["# <center> Relevent Member Data from Churn Competition </center>"]}, {"metadata": {"_uuid": "5787c749cf7f9beb3de682902fc9ed98d4c0a5e6", "_cell_guid": "0e08781d-04a4-48da-a335-5fe3964ccc5b"}, "cell_type": "markdown", "source": ["I feel this kernal was inevitable, so I decided to help everyone out by creating the dataset of relevant information from the Churn Competition. I have not read anywhere that we could not use this data for this competition, but use this data at your own risk. "]}, {"metadata": {"collapsed": true, "_uuid": "457049f9924879e2657c807a9d06220b60827dd2", "_kg_hide-output": false, "_cell_guid": "40e3332e-3f08-47a5-ae1f-16b6fc505026", "_kg_hide-input": false}, "execution_count": null, "cell_type": "code", "source": ["# Imports\n", "\n", "import pandas as pd\n", "import numpy as np\n", "import matplotlib.pyplot as plt\n", "import seaborn as sbn\n", "%matplotlib inline\n", "\n", "churn_data_path = '../input/kkbox-churn-prediction-challenge/'\n", "recommend_data_path = '../input/kkbox-music-recommendation-challenge/'"], "outputs": []}, {"metadata": {"_uuid": "1fa54f42a1c8db7868302f6c4028a54ac1186bda", "_cell_guid": "602785ec-6af4-4185-83d8-a8218f505633"}, "cell_type": "markdown", "source": ["Grabbing the list of members in the Music Recommendation challenge for creating the subset of relevent user_logs data."]}, {"metadata": {"_uuid": "2b18fdb6c370f0c863c1e9316967a9c2f0b8c7ef", "collapsed": true, "_cell_guid": "108471a1-e498-4bd3-a9b4-e267cc86ea7a"}, "execution_count": null, "cell_type": "code", "source": ["df_members = pd.read_csv(recommend_data_path + 'members.csv')\n", "members = pd.DataFrame(df_members['msno'])"], "outputs": []}, {"metadata": {"_uuid": "70c10f13b712b9691bd2ff3743ab930c0bda007d", "_cell_guid": "bbf086cb-734f-41aa-aec1-9b46a7af3721"}, "cell_type": "markdown", "source": ["Due to memory constraints, the user_log file must be read in by chunks and handled chunk by chunk. Created a dataframe of all the data related to the members in the Music Recommendation competition."]}, {"metadata": {"_uuid": "a2162e7d7214a477d593bcbc177524506d405686", "collapsed": true, "_cell_guid": "b96ca5da-5a19-4bb7-b276-6d2f6287d770"}, "execution_count": null, "cell_type": "code", "source": ["user_data = pd.DataFrame()\n", "for chunk in pd.read_csv(churn_data_path + 'user_logs.csv', chunksize=500000):\n", "    merged = members.merge(chunk, on='msno', how='inner')\n", "    user_data = pd.concat([user_data, merged])"], "outputs": []}, {"metadata": {"_uuid": "afa0b9e758e80f40e5e12344ccbb07ccbf4c68f2", "_cell_guid": "7f9ac1fe-2a33-4116-ae0c-9a9e4365c191"}, "cell_type": "markdown", "source": ["Almost all of the members in the Music Recommendation challenge now now have additional information:"]}, {"metadata": {"_uuid": "ada47898e3d4ae50e1a797dd75f7af1d1bb53843", "_cell_guid": "037569bc-9905-41fb-a98a-a76ed35fb3a4"}, "execution_count": null, "cell_type": "code", "source": ["# Almost all members have additional information now\n", "print (str(len(members['msno'].unique())) + \" unique members in Music Recommendation Challenge\")\n", "print (str(len(user_data['msno'].unique())) + \" users now have additional information\")"], "outputs": []}, {"metadata": {"_uuid": "8783accb64710009a0fb1647fb0722aa77395e2f", "_cell_guid": "8a5e8ce9-5968-496f-8fb2-fefe540f7d6e"}, "cell_type": "markdown", "source": ["If you are curious, this reduced the size of the user_logs file from ~ 30GB down to about 500MB! <br>\n", "I'll output the data here into a csv file in case you disagree with the upcoming pre-processing."]}, {"metadata": {"_uuid": "57cc29aae6f3f34f9448528a6d0e7366585e41c4", "collapsed": true, "_cell_guid": "5256c0ba-a6ce-4ff7-9a03-71009f872cbc"}, "execution_count": null, "cell_type": "code", "source": ["user_data.to_csv('user_logs2.csv', index=False)"], "outputs": []}, {"metadata": {"_uuid": "314c9e0163e43c7b8ab38a84f46b1d799b817ecf", "_cell_guid": "71eeaa85-4c14-43bf-af27-5a6db8f8faa8"}, "cell_type": "markdown", "source": ["A preview of the relevant members in the user_logs file:"]}, {"metadata": {"_uuid": "02989a5a313e2ee1d0f0a8d83f86ed9eaf802623", "_cell_guid": "a0edbdc1-b6cf-4446-9b1c-fb98f6e45437"}, "execution_count": null, "cell_type": "code", "source": ["print (user_data.head())"], "outputs": []}, {"metadata": {"_uuid": "8f3d7fc8377fb2ddba57c4db12c08f65125ad2ad", "_cell_guid": "82918c7a-34f8-49fb-9eb4-243ee9031548"}, "cell_type": "markdown", "source": ["There are some strange outliers in the total_secs column ( values < 0 ). <br>\n", "Since its only a fraction of the data, I'll just remove those rows."]}, {"metadata": {"_uuid": "8548e95b815db75a02ee77c4c81a6dcf454ca04c", "_cell_guid": "83351b37-7c3f-4a9c-8972-14c48758795d"}, "execution_count": null, "cell_type": "code", "source": ["for col in user_data.columns[1:]:\n", "    outlier_count = user_data['msno'][user_data[col] < 0].count()\n", "    print (str(outlier_count) + \" outliers in column \" + col)\n", "user_data = user_data[user_data['total_secs'] >= 0]\n", "print (user_data['msno'][user_data['total_secs'] < 0].count())"], "outputs": []}, {"metadata": {"_uuid": "abe3b3b725cce18b2659fa46bab14a91e6ec6c5e", "_cell_guid": "58777771-b5ba-4b27-bfb8-ae04a0b4400d"}, "cell_type": "markdown", "source": ["I think the most logical thing to do next is to group the data by member id and then sum the columns corresponding to each member. In addition, the number of days a user listened to songs might be useful (the frequency count of each member), so this was added as well. The date column becomes useless if we do this, so it will be removed first."]}, {"metadata": {"_uuid": "67efe8ae02e5f677703057c233e67c485598a18a", "_cell_guid": "8a557ae4-7427-4b85-af6d-3eb4321b185e"}, "execution_count": null, "cell_type": "code", "source": ["del user_data['date']\n", "\n", "print (str(np.shape(user_data)) + \" -- Size of data large due to repeated msno\")\n", "counts = user_data.groupby('msno')['total_secs'].count().reset_index()\n", "counts.columns = ['msno', 'days_listened']\n", "sums = user_data.groupby('msno').sum().reset_index()\n", "user_data = sums.merge(counts, how='inner', on='msno')\n", "\n", "print (str(np.shape(user_data)) + \" -- New size of data matches unique member count\")\n", "print (user_data.head())"], "outputs": []}, {"metadata": {"_uuid": "238055c47dc7bff9ff3673d5957853b5a3cc000f", "_cell_guid": "191f9637-3b04-4423-b4a4-a45d41da8829"}, "cell_type": "markdown", "source": ["To get an idea of the effect of each new feature on the target, I have plotted the probabilty of a user repeating a song vs the new features:"]}, {"metadata": {"_uuid": "af579d788a70ea12f7a456cede1e24785f652f83", "collapsed": true, "_cell_guid": "e76bcc87-cc5a-410a-9db1-0055a9a8f22e"}, "execution_count": null, "cell_type": "code", "source": ["df_train = pd.read_csv(recommend_data_path + 'train.csv')\n", "train = df_train.merge(user_data, how='left', on='msno')\n", "\n", "def repeat_chance_plot(groups, col, plot=False):\n", "    x_axis = [] # Sort by type\n", "    repeat = [] # % of time repeated\n", "    for name, group in groups:\n", "        count0 = float(group[group.target == 0][col].count())\n", "        count1 = float(group[group.target == 1][col].count())\n", "        percentage = count1/(count0 + count1)\n", "        x_axis = np.append(x_axis, name)\n", "        repeat = np.append(repeat, percentage)\n", "    plt.figure()\n", "    plt.title(col)\n", "    sbn.barplot(x_axis, repeat)\n", "\n", "for col in user_data.columns[1:]:\n", "    tmp = pd.DataFrame(pd.qcut(train[col], 15, labels=False))\n", "    tmp['target'] = train['target']\n", "    groups = tmp.groupby(col)\n", "    repeat_chance_plot(groups, col)"], "outputs": []}, {"metadata": {"_uuid": "def1d1a1df9f781749a4438eb341e2d7a91f1170", "_cell_guid": "f93701cd-359d-45cc-8097-62458c79c3ea"}, "cell_type": "markdown", "source": ["Logically, it would seem like these columns would be pretty heavily correlated since they all relate to how many songs a user has listened to over a set amount of time. To see if this is true, a correlation heatmap proves pretty useful:"]}, {"metadata": {"_uuid": "cd12d381f626e6faf18a86f0032d25c2cb0ce696", "collapsed": true, "_cell_guid": "5bbe901d-36c7-470e-b5f5-410cb9c05d28"}, "execution_count": null, "cell_type": "code", "source": ["corrmat = user_data[user_data.columns[1:]].corr()\n", "f, ax = plt.subplots(figsize=(12, 9))\n", "sbn.heatmap(corrmat, vmax=1, cbar=True, annot=True, square=True);\n", "plt.show()"], "outputs": []}, {"metadata": {"_uuid": "4d8cba4b0429fbf65df229c88da48f0718749077", "_cell_guid": "1edda359-500e-4536-8b1f-da630f4de77e"}, "cell_type": "markdown", "source": ["From this map, almost everything seems to be pretty correlated. However a couple column pairs jump out; (num_75, num_50) and (num_unq, num_100) are the most heavily correlated and so I will remove one from each pair."]}, {"metadata": {"_uuid": "fd1e941eb1a160c21037830e21fb684ac993e153", "collapsed": true, "_cell_guid": "ac32ad0f-10d4-4ac8-bf90-366bc9f47c07"}, "execution_count": null, "cell_type": "code", "source": ["del user_data['num_75']\n", "del user_data['num_unq']"], "outputs": []}, {"metadata": {"_uuid": "1337d408945c360f0dbff32bd9dce895cdd3645b", "_cell_guid": "fa97ba7a-4cf3-4560-b21e-adc4709d4cd0"}, "cell_type": "markdown", "source": ["Lastly, I will look at the distribution of data in each column. From having done so already, I know that the distributions are heavily skewed so I will log transform the data in attempt to create normally distributed data and plot them both for you to see. In addition, I have normalized the data (std of 1, mean of 0) using the sklearn StandardScaler."]}, {"metadata": {"_uuid": "bb68647c3bb6e7450b18314b8ae2302d5842634c", "collapsed": true, "_cell_guid": "d127590b-4a24-41d2-9342-607350910b35", "scrolled": false}, "execution_count": null, "cell_type": "code", "source": ["from sklearn.preprocessing import StandardScaler\n", "\n", "cols = user_data.columns[1:]\n", "log_user_data = user_data.copy()\n", "log_user_data[cols] = np.log1p(user_data[cols])\n", "ss = StandardScaler()\n", "log_user_data[cols] = ss.fit_transform(log_user_data[cols])\n", "\n", "for col in cols:\n", "    plt.figure(figsize=(15,7))\n", "    plt.subplot(1,2,1)\n", "    sbn.distplot(user_data[col].dropna())\n", "    plt.subplot(1,2,2)\n", "    sbn.distplot(log_user_data[col].dropna())\n", "    plt.figure()"], "outputs": []}, {"metadata": {"_uuid": "88601bc28f77dc3714f6ee96de8a77aa7fe20fa2", "collapsed": true, "_cell_guid": "19870584-9d24-4b76-aae2-f2b564493d79"}, "execution_count": null, "cell_type": "code", "source": ["log_user_data.to_csv('user_logs_final.csv', index=False)"], "outputs": []}, {"metadata": {"_uuid": "16bae6fefeba3dadbf14254c1757df39208798ca", "_cell_guid": "26e3234d-84c1-49a9-9e3c-854ad6ee03b8"}, "cell_type": "markdown", "source": ["I am new to the Machine Learning world and  would greatly appreciate any comments you may have."]}], "nbformat": 4}