3_etl_down_sample_by_user.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get user id list and count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# use shell script to count unique id (large file, slow)\n",
    "import os\n",
    "cmd=\"\"\"\n",
    "export LC_CTYPE=C \n",
    "export LANG=C\n",
    "# get uid field| sort | count unique ids | strip blank spaces | output to file\n",
    "cat ../data/play/all_play_log| cut -f1 -d$'\\t'| sort | uniq -c | sed -e 's/^ *//g;s/ *$//g'> ../data/uid_count.csv\n",
    "\"\"\"\n",
    "os.system(cmd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('../data/uid_count.csv',sep='\\s+', names=['count','uid'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 594735 entries, 0 to 594734\n",
      "Data columns (total 2 columns):\n",
      "count    594735 non-null int64\n",
      "uid      594734 non-null float64\n",
      "dtypes: float64(1), int64(1)\n",
      "memory usage: 9.1 MB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>uid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>5.947350e+05</td>\n",
       "      <td>5.947340e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>2.460525e+02</td>\n",
       "      <td>1.673628e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>1.526662e+04</td>\n",
       "      <td>1.047142e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>9.000000e+00</td>\n",
       "      <td>1.680262e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>4.000000e+01</td>\n",
       "      <td>1.684782e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.740000e+02</td>\n",
       "      <td>1.687685e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>7.501794e+06</td>\n",
       "      <td>1.692623e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              count           uid\n",
       "count  5.947350e+05  5.947340e+05\n",
       "mean   2.460525e+02  1.673628e+08\n",
       "std    1.526662e+04  1.047142e+07\n",
       "min    1.000000e+00  0.000000e+00\n",
       "25%    9.000000e+00  1.680262e+08\n",
       "50%    4.000000e+01  1.684782e+08\n",
       "75%    1.740000e+02  1.687685e+08\n",
       "max    7.501794e+06  1.692623e+08"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove bots and outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# We use matplotlib for plotting\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# This statement allow to display plot without asking to\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5,0,'user # plays (10^n)')"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAZUAAAEKCAYAAADaa8itAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMS4wLCBo\ndHRwOi8vbWF0cGxvdGxpYi5vcmcvpW3flQAAGLhJREFUeJzt3X20JVV95vHvQ4OCKILSMkhDGrV9\nQZcKtkAWGTWC0IIRnIiDY0KrJD3j4KjDzMTWOCHxJcHJjChrDEqEAdQEwZdABMQWQdGIdCPIO9KD\nPdLCEgwgIAhp+M0fZ189XG73Pbe7zj19uN/PWmfdql276uzi5T53V+3alapCkqQubDHqBkiSHj8M\nFUlSZwwVSVJnDBVJUmcMFUlSZwwVSVJnDBVJUmcMFUlSZwwVSVJnthx1A2bbjjvuWAsXLhx1MyRp\nbFx++eU/r6r5g9Sdc6GycOFCVq1aNepmSNLYSPL/Bq3r5S9JUmcMFUlSZwwVSVJnDBVJUmcMFUlS\nZwwVSVJnDBVJUmcMFUlSZwwVSVJn5twT9Zti4fJzZ1R/zXGHDKklkrR5sqciSeqMoSJJ6oyhIknq\nzFDvqSRZA9wLPAysq6rFSZ4GfAFYCKwB3lRVdyUJ8AngYOB+4K1V9YN2nKXAB9phP1xVp7XylwGn\nAtsA5wHvrqoa5jlpcDO9BwXeh5LG3Wz0VH63ql5aVYvb+nLgwqpaBFzY1gFeCyxqn2XAiQAthI4F\n9gH2Bo5NskPb58RWd2K/JcM/HUnS+ozi8tehwGlt+TTgsL7y06vnUmD7JDsDBwErqurOqroLWAEs\nadu2q6rvtd7J6X3HkiSNwLCHFBfw9SQFfLqqTgJ2qqrbAKrqtiTPaHV3AW7p23dtK9tQ+dopyjUE\nG3MpS9LcM+xQ2a+qbm3BsSLJDRuomynKaiPKH3vgZBm9y2TstttuG26xJGmjDfXyV1Xd2n7eDnyF\n3j2Rn7VLV7Sft7fqa4Fd+3ZfANw6TfmCKcqnasdJVbW4qhbPnz/Qa5YlSRthaKGSZNskT5lYBg4E\nrgHOAZa2akuBs9vyOcCR6dkX+EW7THYBcGCSHdoN+gOBC9q2e5Ps20aOHdl3LEnSCAzz8tdOwFd6\nv+/ZEvi7qvpakpXAmUmOAn4CHN7qn0dvOPFqekOK3wZQVXcm+RCwstX7YFXd2ZbfwW+GFJ/fPppD\nHLYsbV6GFipVdTPwkinK/xnYf4ryAo5ez7FOAU6ZonwV8KJNbqwkqRM+US9J6oyhIknqjKEiSeqM\noSJJ6oyhIknqjKEiSeqMrxOWBuCrpKXB2FORJHXGUJEkdcZQkSR1xlCRJHXGUJEkdcZQkSR1xlCR\nJHXG51Q052zMO1gkDcaeiiSpM4aKJKkzXv7SZuXxcmnK1xxrrrKnIknqjKEiSeqMoSJJ6oyhIknq\njKEiSeqMoSJJ6oyhIknqjKEiSeqMoSJJ6oyhIknqjKEiSeqMoSJJ6oyhIknqzNBDJcm8JFck+Wpb\n3z3J95PclOQLSZ7Qyp/Y1le37Qv7jvG+Vn5jkoP6ype0stVJlg/7XCRJGzYbPZV3A9f3rX8UOL6q\nFgF3AUe18qOAu6rqOcDxrR5J9gCOAF4ILAH+pgXVPOCTwGuBPYA3t7qSpBEZaqgkWQAcAnymrQd4\nNfDFVuU04LC2fGhbp23fv9U/FDijqh6sqh8Dq4G922d1Vd1cVQ8BZ7S6kqQRGXZP5ePAnwCPtPWn\nA3dX1bq2vhbYpS3vAtwC0Lb/otX/dfmkfdZX/hhJliVZlWTVHXfcsannJElaj6GFSpLXAbdX1eX9\nxVNUrWm2zbT8sYVVJ1XV4qpaPH/+/A20WpK0KYb5OuH9gNcnORjYGtiOXs9l+yRbtt7IAuDWVn8t\nsCuwNsmWwFOBO/vKJ/Tvs75ySdIIDK2nUlXvq6oFVbWQ3o32b1bVW4CLgDe2akuBs9vyOW2dtv2b\nVVWt/Ig2Omx3YBFwGbASWNRGkz2hfcc5wzofSdL0htlTWZ/3Amck+TBwBXByKz8Z+GyS1fR6KEcA\nVNW1Sc4ErgPWAUdX1cMASd4JXADMA06pqmtn9UwkSY8yK6FSVRcDF7flm+mN3Jpc51fA4evZ/yPA\nR6YoPw84r8OmSiOzcPm5M95nzXGHDKEl0sbziXpJUmcMFUlSZwwVSVJnDBVJUmcMFUlSZwwVSVJn\nDBVJUmcMFUlSZwwVSVJnDBVJUmdGMfeXOub0HpI2F/ZUJEmdMVQkSZ0xVCRJnTFUJEmdMVQkSZ0x\nVCRJnTFUJEmdMVQkSZ0xVCRJnTFUJEmdGShUkrxo2A2RJI2/QXsqn0pyWZL/mGT7obZIkjS2BgqV\nqvod4C3ArsCqJH+X5DVDbZkkaewMfE+lqm4CPgC8F3glcEKSG5L8m2E1TpI0Xga9p/LiJMcD1wOv\nBn6vql7Qlo8fYvskSWNk0Pep/G/gb4H3V9UDE4VVdWuSDwylZZKksTNoqBwMPFBVDwMk2QLYuqru\nr6rPDq11kqSxMug9lW8A2/StP6mVSZL0a4OGytZVdd/ESlt+0nCaJEkaV4OGyi+T7DWxkuRlwAMb\nqC9JmoMGDZX3AGcluSTJJcAXgHduaIckW7cHJn+Y5Nokf9HKd0/y/SQ3JflCkie08ie29dVt+8K+\nY72vld+Y5KC+8iWtbHWS5TM7dUlS1wa6UV9VK5M8H3geEOCGqvqXaXZ7EHh1Vd2XZCvgO0nOB44B\njq+qM5J8CjgKOLH9vKuqnpPkCOCjwL9NsgdwBPBC4JnAN5I8t33HJ4HXAGuBlUnOqarrBj99SVKX\nZjKh5MuBFwN7Am9OcuSGKlfPxH2Yrdqn6D3b8sVWfhpwWFs+tK3Ttu+fJK38jKp6sKp+DKwG9m6f\n1VV1c1U9BJzR6kqSRmSgnkqSzwLPBq4EHm7FBZw+zX7zgMuB59DrVfxf4O6qWteqrAV2acu7ALcA\nVNW6JL8Ant7KL+07bP8+t0wq32c97VgGLAPYbbfdNtRkSdImGPQ5lcXAHlVVMzl4e67lpW0Syq8A\nL5iqWvuZ9WxbX/lUvawp21dVJwEnASxevHhG5yBJGtygoXIN8K+A2zbmS6rq7iQXA/sC2yfZsvVW\nFgC3tmpr6U1YuTbJlsBTgTv7yif077O+cmlOWLj83BnVX3PcIUNqidQz6D2VHYHrklyQ5JyJz4Z2\nSDJ/Ypr8JNsAB9CbO+wi4I2t2lLg7LZ8Tlunbf9m6xmdAxzRRoftDiwCLgNWAovaaLIn0LuZv8E2\nSZKGa9Ceyp9vxLF3Bk5r91W2AM6sqq8muQ44I8mHgSuAk1v9k4HPJllNr4dyBEBVXZvkTOA6YB1w\ndN90Me8ELgDmAadU1bUb0U5JUkcGHVL8rSS/BSyqqm8keRK9X+Qb2ucqeiPFJpffTG/k1uTyXwGH\nr+dYHwE+MkX5ecB5g5yDJGn4Bp36/o/pDfP9dCvaBfiHYTVKkjSeBr2ncjSwH3AP/PqFXc8YVqMk\nSeNp0FB5sD1gCEAbneXQXEnSowwaKt9K8n5gm/Zu+rOAfxxesyRJ42jQUFkO3AFcDfx7ejfHfeOj\nJOlRBh399Qi91wn/7XCbI0kaZ4PO/fVjpriHUlXP6rxFkqSxNZO5vyZsTe95kqd13xxJ0jgb6J5K\nVf1z3+enVfVxelPYS5L0a4Ne/tqrb3ULej2XpwylRZKksTXo5a//1be8DlgDvKnz1kiSxtqgo79+\nd9gN0eya6ZTpkjSIQS9/HbOh7VX1sW6aI0kaZzMZ/fVyfvO+kt8Dvs2jX+crSZrjBg2VHYG9qupe\ngCR/DpxVVX80rIZJksbPoNO07AY81Lf+ELCw89ZIksbaoD2VzwKXJfkKvSfr3wCcPrRWSZLG0qCj\nvz6S5HzgX7eit1XVFcNrliRpHA16+QvgScA9VfUJYG2S3YfUJknSmBr0dcLHAu8F3teKtgI+N6xG\nSZLG06A9lTcArwd+CVBVt+I0LZKkSQYNlYeqqmjT3yfZdnhNkiSNq0FD5cwknwa2T/LHwDfwhV2S\npEkGHf31P9u76e8Bngf8WVWtGGrLJEljZ9pQSTIPuKCqDgAMEknSek17+auqHgbuT/LUWWiPJGmM\nDfpE/a+Aq5OsoI0AA6iqdw2lVZKksTRoqJzbPpIkrdcGQyXJblX1k6o6bbYaJGl4NublbGuOO2QI\nLdHj1XT3VP5hYiHJl4bcFknSmJsuVNK3/KyZHDjJrkkuSnJ9kmuTvLuVPy3JiiQ3tZ87tPIkOSHJ\n6iRXJdmr71hLW/2bkiztK39ZkqvbPickyWNbIkmaLdOFSq1neRDrgP9SVS8A9gWOTrIHsBy4sKoW\nARe2dYDXAovaZxlwIvRCCDgW2AfYGzh2IohanWV9+y2ZYRslSR2a7kb9S5LcQ6/Hsk1bpq1XVW23\nvh2r6jbgtrZ8b5LrgV2AQ4FXtWqnARfTm6zyUOD0Nh3MpUm2T7Jzq7uiqu4EaCPQliS5GNiuqr7X\nyk8HDgPOH/jsh8zr15Lmmg2GSlXN6+JLkiwE9gS+D+zUAoequi3JM1q1XXj0O+/XtrINla+dolyS\nNCIzeZ/KRknyZOBLwHuq6p4NVZ2irDaifKo2LEuyKsmqO+64Y7omS5I20lBDJclW9ALl81X15Vb8\ns3ZZi/bz9la+Fti1b/cFwK3TlC+YovwxquqkqlpcVYvnz5+/aSclSVqvoYVKG4l1MnB9VX2sb9M5\nwMQIrqXA2X3lR7ZRYPsCv2iXyS4ADkyyQ7tBfyC9uchuA+5Nsm/7riP7jiVJGoFBn6jfGPsBf0hv\nepcrW9n7gePoTaV/FPAT4PC27TzgYGA1cD/wNoCqujPJh4CVrd4HJ27aA+8ATgW2oXeDfrO5SS9J\nc9HQQqWqvsPU9z0A9p+ifgFHr+dYpwCnTFG+CnjRJjRTktShod+olyTNHYaKJKkzhookqTOGiiSp\nM4aKJKkzhookqTOGiiSpM4aKJKkzhookqTOGiiSpM4aKJKkzhookqTOGiiSpM8Oc+l4bYWPeay9J\nmwt7KpKkzhgqkqTOGCqSpM4YKpKkzhgqkqTOGCqSpM4YKpKkzhgqkqTOGCqSpM74RL2kDdqYWR7W\nHHfIEFqicWBPRZLUGUNFktQZQ0WS1BlDRZLUGUNFktQZQ0WS1BlDRZLUmaGFSpJTktye5Jq+sqcl\nWZHkpvZzh1aeJCckWZ3kqiR79e2ztNW/KcnSvvKXJbm67XNCkgzrXCRJgxlmT+VUYMmksuXAhVW1\nCLiwrQO8FljUPsuAE6EXQsCxwD7A3sCxE0HU6izr22/yd0mSZtnQQqWqvg3cOan4UOC0tnwacFhf\n+enVcymwfZKdgYOAFVV1Z1XdBawAlrRt21XV96qqgNP7jiVJGpHZvqeyU1XdBtB+PqOV7wLc0ldv\nbSvbUPnaKcolSSO0udyon+p+SG1E+dQHT5YlWZVk1R133LGRTZQkTWe2Q+Vn7dIV7eftrXwtsGtf\nvQXArdOUL5iifEpVdVJVLa6qxfPnz9/kk5AkTW22Q+UcYGIE11Lg7L7yI9sosH2BX7TLYxcABybZ\nod2gPxC4oG27N8m+bdTXkX3HkiSNyNCmvk/y98CrgB2TrKU3ius44MwkRwE/AQ5v1c8DDgZWA/cD\nbwOoqjuTfAhY2ep9sKombv6/g94Is22A89tHkjRCQwuVqnrzejbtP0XdAo5ez3FOAU6ZonwV8KJN\naaMkqVuby416SdLjgKEiSeqMoSJJ6oyhIknqjKEiSeqMoSJJ6oyhIknqzNCeU5E0dy1cfu6M6q85\n7pAhtUSzzZ6KJKkzhookqTOGiiSpM4aKJKkzhookqTOGiiSpM4aKJKkzhookqTOGiiSpM4aKJKkz\nhookqTOGiiSpM4aKJKkzhookqTOGiiSpM4aKJKkzvqRL0sjN9KVe4Iu9Nlf2VCRJnTFUJEmdMVQk\nSZ0xVCRJnTFUJEmdMVQkSZ0Z+yHFSZYAnwDmAZ+pquNG3CRJs8BhyJunse6pJJkHfBJ4LbAH8OYk\ne4y2VZI0d411qAB7A6ur6uaqegg4Azh0xG2SpDlr3C9/7QLc0re+FthnRG2RtJmb6SUzL5fN3LiH\nSqYoq8dUSpYBy9rqfUlu3Mjv2xH4+UbuO+7m8rnD3D7/OXvu+ejcPfdm4vx/a9Adxj1U1gK79q0v\nAG6dXKmqTgJO2tQvS7KqqhZv6nHG0Vw+d5jb5++5z81zh407/3G/p7ISWJRk9yRPAI4AzhlxmyRp\nzhrrnkpVrUvyTuACekOKT6mqa0fcLEmas8Y6VACq6jzgvFn6uk2+hDbG5vK5w9w+f8997prx+afq\nMfe1JUnaKON+T0WStBkxVAaQZEmSG5OsTrJ81O2ZTUlOSXJ7kmtG3ZbZlmTXJBcluT7JtUnePeo2\nzaYkWye5LMkP2/n/xajbNNuSzEtyRZKvjrotsynJmiRXJ7kyyaoZ7evlrw1rU8H8CHgNvSHMK4E3\nV9V1I23YLEnyCuA+4PSqetGo2zObkuwM7FxVP0jyFOBy4LA59O8+wLZVdV+SrYDvAO+uqktH3LRZ\nk+QYYDGwXVW9btTtmS1J1gCLq2rGz+jYU5nenJ4Kpqq+Ddw56naMQlXdVlU/aMv3AtfTm8VhTqie\n+9rqVu0zZ/4KTbIAOAT4zKjbMk4MlelNNRXMnPnFop4kC4E9ge+PtiWzq13+uRK4HVhRVXPp/D8O\n/AnwyKgbMgIFfD3J5W1GkoEZKtMbaCoYPX4leTLwJeA9VXXPqNszm6rq4ap6Kb3ZKvZOMicugSZ5\nHXB7VV0+6raMyH5VtRe9GeCPbpfBB2KoTG+gqWD0+NTuJXwJ+HxVfXnU7RmVqrobuBhYMuKmzJb9\ngNe3ewtnAK9O8rnRNmn2VNWt7eftwFfo3QYYiKEyPaeCmaPajeqTgeur6mOjbs9sSzI/yfZteRvg\nAOCG0bZqdlTV+6pqQVUtpPf//Der6g9G3KxZkWTbNjCFJNsCBwIDj/40VKZRVeuAialgrgfOnEtT\nwST5e+B7wPOSrE1y1KjbNIv2A/6Q3l+pV7bPwaNu1CzaGbgoyVX0/rhaUVVzamjtHLUT8J0kPwQu\nA86tqq8NurNDiiVJnbGnIknqjKEiSeqMoSJJ6oyhIknqjKEiSeqMoSLNQJK/SvKqJIfNdMbqtt/Q\nhuQm2SbJt9okqCT5WpK7J39ne+bq+0luSvKF9vzV5Hb+MsnAc14lOSPJom7OROPMUJEmSbKhN6Lu\nQ2/+r1cCl8xOiwb2duDLVfVwW/9res/ZTPZR4PiqWgTcBfz62aM2Dcvf0DvPpyQ5dsDvPpHePFma\n4wwVjbUkC/vf9ZLkvyb587b8riTXJbkqyRmtbNv2jpiV7T0Zh7bytyY5K8k/Al+f4nv+uj0E+HJ6\nD4P+EXBikj+bou6pST6V5JIkP2rzSE2us3eSf2pt+Kckz2vllyR5aV+97yZ5cZJX9j2AecXEE8+T\nvAU4e2Klqi4E7p30vQFeDXyxFZ0GHNa27UJvBoHDquoa4N/Re+j17W37q5JcnOSLSW5I8vl2POgF\n7AHTBLLmAP8D0OPZcmD3qnpwYroR4E/pTbnx9lZ2WZJvtG2/Dby4qh4z1X9V/bckZ9H7y/8Y4OKq\n2m8D372QXm/m2fSeSn/OpO03AK+oqnVJDgD+Evh9etOsvxV4T5LnAk+sqqta2B1dVd9tE1z+qv9g\n7RLWs6pqzTT/TJ4O3N1mioC+Wber6qf0eigT5/wwvWDptyfwQnrz332X3qwD36mqR5KsBl5C770z\nmqPsqejx7Crg80n+AJj4JXogsLxN534xsDWwW9u2YqpA6bMncCXwfGC6F3WdWVWPVNVNwM1tn35P\nBc5qvazj6f2iBjgLeF2byPLtwKmt/LvAx5K8C9i+LxQm7AjcPU2bYNNn3b6sqtZW1SP0/lks7Nt2\nO/DMGRxLj0P2VDTu1vHoP4627ls+BHgF8Hrgvyd5Ib1fqr9fVTf2HyTJPsAvp/qCdjnqVHozVP8c\neFKvOFcCv11VD0yx2+Rf1JPXPwRcVFVvSO9dLRcDVNX9SVbQexHcm+i9dZCqOi7JucDBwKVJDqiq\n/skdH5h07uvzc2D7JFu2YJrprNsP9i0/zKN/h2zd2qE5zJ6Kxt3PgGckeXqSJwKvA0iyBbBrVV1E\n7wby9sCT6U0M+p8m7gUk2XO6L6iqK9s7RX4E7AF8Ezioql66nkABODzJFkmeDTwLuHHS9qcCP23L\nb5207TPACcDKiZ5TkmdX1dVV9VFgFZN6PlV1FzAvyQaDpXqT/V0EvLEVLaXvPswmei4wZyZb1dQM\nFY21qvoX4IP0RmR9ld9MzT4P+FySq4Er6I12upteD2Er4Kp26elDg3xPkvnAXe2yz/MHeE/9jcC3\ngPOB/1BVv5q0/X8Af5Xku62t/ed0OXAP8H/6it+T5Jo2c+wD7biTfR34nb42X0Lvctr+bYbpg9qm\n9wLHtHsgT6d3c36TJNkJeKCqbtvUY2m8OUux1LEkpwJfraovTld3Pfs/k97lsOe3EBt0vz2BY6pq\nqmHEQ5XkPwP3VNUmB5TGmz0VaTOS5Eh6va4/nUmgAFTVFfRGms2btnL37qY3PFlznD0VSVJn7KlI\nkjpjqEiSOmOoSJI6Y6hIkjpjqEiSOmOoSJI68/8BbzBJ8R6bivMAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x108289eb8>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "np.log10(df['count']).plot.hist(bins=np.arange(0,5,0.2))\n",
    "plt.xlabel(\"user # plays (10^n)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5195.394\n"
     ]
    }
   ],
   "source": [
    "top_count_threshold = np.percentile(df['count'],99.9)\n",
    "print(top_count_threshold)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total number of users: 594735\n"
     ]
    }
   ],
   "source": [
    "print(\"total number of users:\",len(df['uid']))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove bots: get id with play counts<top_count_threshold\n",
    "id_list_bot_removed = np.array(df['uid'][df['count']<top_count_threshold].dropna())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total number of users after bot removed: 594139\n"
     ]
    }
   ],
   "source": [
    "print(\"total number of users after bot removed:\",len(id_list_bot_removed))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Apply downsample on uid level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# downsample ids\n",
    "np.random.seed = 1\n",
    "down_sample_ratio = 0.1\n",
    "\n",
    "\n",
    "# add random number that is in the selected subset. \n",
    "\n",
    "id_subset = set(id_list_bot_removed[np.random.random(id_list_bot_removed.shape)<down_sample_ratio])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total number of users after down sample: 59237\n"
     ]
    }
   ],
   "source": [
    "print(\"total number of users after down sample:\",len(id_subset))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "# define date conversion function\n",
    "import datetime\n",
    "def convert_date(s):\n",
    "    s = str(s).strip()\n",
    "    try:\n",
    "        year = int(s[:4])\n",
    "        month = int(s[4:6])\n",
    "        day = int(s[6:8])\n",
    "        return datetime.date(year,month,day)\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#row processed: 1000000\n",
      "#row processed: 2000000\n",
      "#row processed: 3000000\n",
      "#row processed: 4000000\n",
      "#row processed: 5000000\n",
      "#row processed: 6000000\n",
      "#row processed: 7000000\n",
      "#row processed: 8000000\n",
      "#row processed: 9000000\n",
      "#row processed: 10000000\n",
      "#row processed: 11000000\n",
      "#row processed: 12000000\n",
      "#row processed: 13000000\n",
      "#row processed: 14000000\n",
      "#row processed: 15000000\n",
      "#row processed: 16000000\n",
      "#row processed: 17000000\n",
      "#row processed: 18000000\n",
      "#row processed: 19000000\n",
      "#row processed: 20000000\n",
      "#row processed: 21000000\n",
      "#row processed: 22000000\n",
      "#row processed: 23000000\n",
      "#row processed: 24000000\n",
      "#row processed: 25000000\n",
      "#row processed: 26000000\n",
      "#row processed: 27000000\n",
      "#row processed: 28000000\n",
      "#row processed: 29000000\n",
      "#row processed: 30000000\n",
      "#row processed: 31000000\n",
      "#row processed: 32000000\n",
      "#row processed: 33000000\n",
      "#row processed: 34000000\n",
      "#row processed: 35000000\n",
      "#row processed: 36000000\n",
      "#row processed: 37000000\n",
      "#row processed: 38000000\n",
      "#row processed: 39000000\n",
      "#row processed: 40000000\n",
      "#row processed: 41000000\n",
      "#row processed: 42000000\n",
      "#row processed: 43000000\n",
      "#row processed: 44000000\n",
      "#row processed: 45000000\n",
      "#row processed: 46000000\n",
      "#row processed: 47000000\n",
      "#row processed: 48000000\n",
      "#row processed: 49000000\n",
      "#row processed: 50000000\n",
      "#row processed: 51000000\n",
      "#row processed: 52000000\n",
      "#row processed: 53000000\n",
      "#row processed: 54000000\n",
      "#row processed: 55000000\n",
      "#row processed: 56000000\n",
      "#row processed: 57000000\n",
      "#row processed: 58000000\n",
      "#row processed: 59000000\n",
      "#row processed: 60000000\n",
      "#row processed: 61000000\n",
      "#row processed: 62000000\n",
      "#row processed: 63000000\n",
      "#row processed: 64000000\n",
      "#row processed: 65000000\n",
      "#row processed: 66000000\n",
      "#row processed: 67000000\n",
      "#row processed: 68000000\n",
      "#row processed: 69000000\n",
      "#row processed: 70000000\n",
      "#row processed: 71000000\n",
      "#row processed: 72000000\n",
      "#row processed: 73000000\n",
      "#row processed: 74000000\n",
      "#row processed: 75000000\n",
      "#row processed: 76000000\n",
      "#row processed: 77000000\n",
      "#row processed: 78000000\n",
      "#row processed: 79000000\n",
      "#row processed: 80000000\n",
      "#row processed: 81000000\n",
      "#row processed: 82000000\n",
      "#row processed: 83000000\n",
      "#row processed: 84000000\n",
      "#row processed: 85000000\n",
      "#row processed: 86000000\n",
      "#row processed: 87000000\n",
      "#row processed: 88000000\n",
      "#row processed: 89000000\n",
      "#row processed: 90000000\n",
      "#row processed: 91000000\n",
      "#row processed: 92000000\n",
      "#row processed: 93000000\n",
      "#row processed: 94000000\n",
      "#row processed: 95000000\n",
      "#row processed: 96000000\n",
      "#row processed: 97000000\n",
      "#row processed: 98000000\n",
      "#row processed: 99000000\n",
      "#row processed: 100000000\n",
      "#row processed: 101000000\n",
      "#row processed: 102000000\n",
      "#row processed: 103000000\n",
      "#row processed: 104000000\n",
      "#row processed: 105000000\n",
      "#row processed: 106000000\n",
      "#row processed: 107000000\n",
      "#row processed: 108000000\n",
      "#row processed: 109000000\n",
      "#row processed: 110000000\n",
      "#row processed: 111000000\n",
      "#row processed: 112000000\n",
      "#row processed: 113000000\n",
      "#row processed: 114000000\n",
      "#row processed: 115000000\n",
      "#row processed: 116000000\n",
      "#row processed: 117000000\n",
      "#row processed: 118000000\n",
      "#row processed: 119000000\n",
      "#row processed: 120000000\n",
      "#row processed: 121000000\n",
      "#row processed: 122000000\n",
      "#row processed: 123000000\n",
      "#row processed: 124000000\n",
      "#row processed: 125000000\n",
      "#row processed: 126000000\n",
      "#row processed: 127000000\n",
      "#row processed: 128000000\n",
      "#row processed: 129000000\n",
      "#row processed: 130000000\n",
      "#row processed: 131000000\n",
      "#row processed: 132000000\n",
      "#row processed: 133000000\n",
      "#row processed: 134000000\n",
      "#row processed: 135000000\n",
      "#row processed: 136000000\n",
      "#row processed: 137000000\n",
      "#row processed: 138000000\n",
      "#row processed: 139000000\n",
      "#row processed: 140000000\n",
      "#row processed: 141000000\n",
      "#row processed: 142000000\n",
      "#row processed: 143000000\n",
      "#row processed: 144000000\n",
      "#row processed: 145000000\n",
      "#row processed: 146000000\n"
     ]
    }
   ],
   "source": [
    "# down sample play by uid\n",
    "import csv\n",
    "input_file = '../data/play/all_play_log'\n",
    "output_file = '../data/play_ds.csv'\n",
    "input_field_list = ['uid','device','song_id','song_type','song_name','singer','play_time','song_length','paid_flag','date']\n",
    "output_field_list = ['uid','device','song_id','date','play_time','song_length']\n",
    "i=0\n",
    "\n",
    "# use with function to combind functions. \n",
    "with open(input_file,'r',encoding='latin-1') as fin, open(output_file,'w') as fout:\n",
    "    # read source data \n",
    "    # each row is a dictionary . use key to access each field\n",
    "    csvin = csv.DictReader(fin,delimiter='\\t',fieldnames=input_field_list,quoting=csv.QUOTE_NONE)\n",
    "    # \n",
    "    csvout = csv.writer(fout,delimiter=',')\n",
    "    csvout.writerow(output_field_list) # write header\n",
    "    for row in csvin:\n",
    "        i+=1\n",
    "        if i%1000000==0:\n",
    "            print(\"#row processed:\",i)\n",
    "            \n",
    "        # use try and except to rull out the case where row isn't regular     \n",
    "        try:\n",
    "            int(row['uid'])\n",
    "        except:\n",
    "            continue\n",
    "        if int(row['uid']) in id_subset:\n",
    "            row['date'] = convert_date(row['date'])\n",
    "            if row['date'] != None: \n",
    "                csvout.writerow([str(row[key]).strip() for key in output_field_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#row processed: 1000000\n",
      "#row processed: 2000000\n",
      "#row processed: 3000000\n",
      "#row processed: 4000000\n",
      "#row processed: 5000000\n",
      "#row processed: 6000000\n",
      "#row processed: 7000000\n"
     ]
    }
   ],
   "source": [
    "# down sample download by uid\n",
    "import csv\n",
    "input_file = '../data/down/all_down_log'\n",
    "output_file = '../data/down_ds.csv'\n",
    "input_field_list = ['uid','device','song_id','song_name','singer','paid_flag','date']\n",
    "output_field_list = ['uid','device','song_id','date']\n",
    "i=0\n",
    "with open(input_file,'r',encoding='latin-1') as fin, open(output_file,'w') as fout:\n",
    "    csvin = csv.DictReader(fin,delimiter='\\t',fieldnames=input_field_list,quoting=csv.QUOTE_NONE)\n",
    "    csvout = csv.writer(fout,delimiter=',')\n",
    "    csvout.writerow(output_field_list) # write header\n",
    "    for row in csvin:\n",
    "        i+=1\n",
    "        if i%1000000==0:\n",
    "            print(\"#row processed:\",i)\n",
    "        try:\n",
    "            int(row['uid'])\n",
    "        except:\n",
    "            continue\n",
    "        if int(row['uid']) in id_subset:\n",
    "            row['date'] = convert_date(row['date'])\n",
    "            if row['date'] != None: \n",
    "                csvout.writerow([str(row[key]).strip() for key in output_field_list])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#row processed: 1000000\n",
      "#row processed: 2000000\n",
      "#row processed: 3000000\n",
      "#row processed: 4000000\n",
      "#row processed: 5000000\n",
      "#row processed: 6000000\n",
      "#row processed: 7000000\n",
      "#row processed: 8000000\n"
     ]
    }
   ],
   "source": [
    "# down sample search by uid  \n",
    "import csv\n",
    "input_file = '../data/search/all_search_log'\n",
    "output_file = '../data/search_ds.csv'\n",
    "input_field_list = ['uid','device','time_stamp','search_query','date']\n",
    "output_field_list = ['uid','device','date']\n",
    "i=0\n",
    "with open(input_file,'r',encoding='latin-1') as fin, open(output_file,'w') as fout:\n",
    "    csvin = csv.DictReader(fin,delimiter='\\t',fieldnames=input_field_list,quoting=csv.QUOTE_NONE)\n",
    "    csvout = csv.writer(fout,delimiter=',')\n",
    "    csvout.writerow(output_field_list) # write header\n",
    "    for row in csvin:\n",
    "        i+=1\n",
    "        if i%1000000==0:\n",
    "            print(\"#row processed:\",i)\n",
    "        try:\n",
    "            int(row['uid'])\n",
    "        except:\n",
    "            continue\n",
    "        if int(row['uid']) in id_subset:\n",
    "            row['date'] = convert_date(row['date'])\n",
    "            if row['date'] != None: \n",
    "                csvout.writerow([str(row[key]).strip() for key in output_field_list])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create event table for feature generation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing play ...\n"
     ]
    }
   ],
   "source": [
    "# combine three types to a big table\n",
    "# event: P for play, D for download, S for search \n",
    "\n",
    "play_file = '../data/play_ds.csv'\n",
    "output_file = '../data/event_play.csv'\n",
    "play_field_list = ['uid','device','song_id','date','play_time','song_length']\n",
    "output_field_list = ['uid','event','song_id','date']\n",
    "with open(play_file,'r') as f_play, \\\n",
    "open(output_file,'w') as f_out:\n",
    "    csvplay = csv.DictReader(f_play,delimiter=',')\n",
    "    csvout = csv.writer(f_out,delimiter=',')\n",
    "    csvout.writerow(output_field_list) # write header\n",
    "    print('Processing play ...')\n",
    "    for row in csvplay:\n",
    "        row['event'] = 'P'\n",
    "        row['date']\n",
    "        csvout.writerow([row[key] for key in output_field_list])\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing down ...\n",
      "Processing search ...\n"
     ]
    }
   ],
   "source": [
    "down_file = '../data/down_ds.csv'\n",
    "search_file = '../data/search_ds.csv'\n",
    "output_file = '../data/event_down_search.csv'\n",
    "down_field_list = ['uid','device','song_id','date']\n",
    "search_field_list = ['uid','device','date']\n",
    "output_field_list = ['uid','event','song_id','date']\n",
    "with open(down_file,'r') as f_down, \\\n",
    "open(search_file,'r') as f_search,open(output_file,'w') as f_out:\n",
    "    csvdown = csv.DictReader(f_down,delimiter=',')\n",
    "    csvsearch = csv.DictReader(f_search,delimiter=',')\n",
    "    csvout = csv.writer(f_out,delimiter=',')\n",
    "    csvout.writerow(output_field_list) # write header\n",
    "    print('Processing down ...')\n",
    "    for row in csvdown:\n",
    "        row['event'] = 'D'\n",
    "        csvout.writerow([row[key] for key in output_field_list])\n",
    "    print('Processing search ...')\n",
    "    for row in csvsearch:\n",
    "        row['event'] = 'S'\n",
    "        csvout.writerow([row.get(key,'') for key in output_field_list])\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}