08. Song Challenge.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.preprocessing import normalize\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>song_played</th>\n",
       "      <th>time_played</th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_sign_up_date</th>\n",
       "      <th>user_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOQMMKSQQH</td>\n",
       "      <td>Hey Jude</td>\n",
       "      <td>2015-06-11 21:51:35</td>\n",
       "      <td>122</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Louisiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HWKKBQKNWI</td>\n",
       "      <td>We Can Work It Out</td>\n",
       "      <td>2015-06-06 16:49:19</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DKQSXVNJDH</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "      <td>2015-06-14 02:11:29</td>\n",
       "      <td>35</td>\n",
       "      <td>2015-05-04</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HLHRIDQTUW</td>\n",
       "      <td>P.s. I Love You</td>\n",
       "      <td>2015-06-08 12:26:10</td>\n",
       "      <td>126</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Illinois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SUKJCSBCYW</td>\n",
       "      <td>Sgt. Pepper's Lonely Hearts Club Band</td>\n",
       "      <td>2015-06-28 14:57:00</td>\n",
       "      <td>6</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                            song_played         time_played  \\\n",
       "0  GOQMMKSQQH                               Hey Jude 2015-06-11 21:51:35   \n",
       "1  HWKKBQKNWI                     We Can Work It Out 2015-06-06 16:49:19   \n",
       "2  DKQSXVNJDH                   Back In the U.S.S.R. 2015-06-14 02:11:29   \n",
       "3  HLHRIDQTUW                        P.s. I Love You 2015-06-08 12:26:10   \n",
       "4  SUKJCSBCYW  Sgt. Pepper's Lonely Hearts Club Band 2015-06-28 14:57:00   \n",
       "\n",
       "   user_id user_sign_up_date  user_state  \n",
       "0      122        2015-05-16   Louisiana  \n",
       "1        3        2015-05-01        Ohio  \n",
       "2       35        2015-05-04  New Jersey  \n",
       "3      126        2015-05-16    Illinois  \n",
       "4        6        2015-05-01  New Jersey  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_json('./data/song.json')\n",
    "data['time_played'] = pd.to_datetime(data['time_played'])\n",
    "data['user_sign_up_date'] = pd.to_datetime(data['user_sign_up_date'])\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4000 entries, 0 to 3999\n",
      "Data columns (total 6 columns):\n",
      "id                   4000 non-null object\n",
      "song_played          4000 non-null object\n",
      "time_played          4000 non-null datetime64[ns]\n",
      "user_id              4000 non-null int64\n",
      "user_sign_up_date    4000 non-null datetime64[ns]\n",
      "user_state           4000 non-null object\n",
      "dtypes: datetime64[ns](2), int64(1), object(3)\n",
      "memory usage: 187.6+ KB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   0\n",
       "song_played          0\n",
       "time_played          0\n",
       "user_id              0\n",
       "user_sign_up_date    0\n",
       "user_state           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check missing values\n",
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id              \t  4000\n",
      "song_played     \t   100\n",
      "time_played     \t  3997\n",
      "user_id         \t   196\n",
      "user_sign_up_date \t    20\n",
      "user_state      \t    41\n"
     ]
    }
   ],
   "source": [
    "# check unique values\n",
    "for name in data.columns:\n",
    "    print('{0:15s} \\t {1:5d}'.format(name, len(data[name].unique())))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def unique_count(x):\n",
    "    \"\"\" function to count the unique user_id \"\"\"\n",
    "    return len(np.unique(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create temporary dataframe for unique user count per state\n",
    "state_user_count = data.groupby('user_state')['user_id'].apply(unique_count).reset_index()\n",
    "state_user_count = state_user_count.rename(columns={'user_id': 'user_count'})\n",
    "state_user_count = state_user_count.sort_values(by='user_count', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>New York</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Texas</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_state  user_count\n",
       "25    New York          23\n",
       "4   California          21\n",
       "35       Texas          15"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the top 3 states\n",
    "state_user_count.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>New Mexico</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Idaho</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>North Dakota</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Connecticut</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Iowa</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_state  user_count\n",
       "24    New Mexico           1\n",
       "9          Idaho           1\n",
       "27  North Dakota           1\n",
       "6    Connecticut           1\n",
       "12          Iowa           1\n",
       "32  Rhode Island           1\n",
       "22      Nebraska           1\n",
       "2        Arizona           1\n",
       "13        Kansas           1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the bottom 3 states\n",
    "state_user_count.tail(9)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here, we define the user engagement is measured throught the average plays per user in the given state.\n",
    "\n",
    "Of course, there are other definitions, such as \"average play event per hour\" (see [this link](https://github.com/stasi009/TakeHomeDataChallenges/blob/master/08.SongChallenge/song_challenge.ipynb) for details)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create temporary dataframe for play count per state\n",
    "state_play_count = data.groupby('user_state')['id'].count().reset_index()\n",
    "state_play_count = state_play_count.rename(columns={'id': 'play_count'})\n",
    "\n",
    "# merge user count and play count\n",
    "state_user_play = pd.merge(left=state_user_count, right=state_play_count, on='user_state')\n",
    "\n",
    "# calculate average play\n",
    "state_user_play['average_play'] = state_user_play['play_count'] / state_user_play['user_count']\n",
    "\n",
    "# sort according to average_play\n",
    "state_user_play = state_user_play.sort_values(by='average_play', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_count</th>\n",
       "      <th>play_count</th>\n",
       "      <th>average_play</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Nebraska</td>\n",
       "      <td>1</td>\n",
       "      <td>36</td>\n",
       "      <td>36.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>2</td>\n",
       "      <td>58</td>\n",
       "      <td>29.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>South Carolina</td>\n",
       "      <td>3</td>\n",
       "      <td>85</td>\n",
       "      <td>28.333333</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        user_state  user_count  play_count  average_play\n",
       "38        Nebraska           1          36     36.000000\n",
       "27          Alaska           2          58     29.000000\n",
       "22  South Carolina           3          85     28.333333"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the top 3 states\n",
    "state_user_play.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_count</th>\n",
       "      <th>play_count</th>\n",
       "      <th>average_play</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>4</td>\n",
       "      <td>42</td>\n",
       "      <td>10.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_state  user_count  play_count  average_play\n",
       "40     Kansas           1           8           8.0\n",
       "30   Virginia           2          17           8.5\n",
       "17  Minnesota           4          42          10.5"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the bottom 3 states\n",
    "state_user_play.tail(3)[::-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_first_user(df):\n",
    "    \"\"\" function to find the first user \"\"\"\n",
    "    idx = df['user_sign_up_date'].argmin()\n",
    "    return df.loc[idx, ['user_id', 'user_sign_up_date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_sign_up_date</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>user_state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alabama</th>\n",
       "      <td>5</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>7</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>3</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>4</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>6</td>\n",
       "      <td>2015-05-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>11</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>19</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>8</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>13</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>15</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>18</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>23</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>20</td>\n",
       "      <td>2015-05-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>29</td>\n",
       "      <td>2015-05-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>34</td>\n",
       "      <td>2015-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>39</td>\n",
       "      <td>2015-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>41</td>\n",
       "      <td>2015-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>32</td>\n",
       "      <td>2015-05-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>45</td>\n",
       "      <td>2015-05-05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>50</td>\n",
       "      <td>2015-05-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>60</td>\n",
       "      <td>2015-05-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>70</td>\n",
       "      <td>2015-05-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>64</td>\n",
       "      <td>2015-05-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>78</td>\n",
       "      <td>2015-05-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>85</td>\n",
       "      <td>2015-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>105</td>\n",
       "      <td>2015-05-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>102</td>\n",
       "      <td>2015-05-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>106</td>\n",
       "      <td>2015-05-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>119</td>\n",
       "      <td>2015-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>134</td>\n",
       "      <td>2015-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>125</td>\n",
       "      <td>2015-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>127</td>\n",
       "      <td>2015-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>142</td>\n",
       "      <td>2015-05-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Dakota</th>\n",
       "      <td>135</td>\n",
       "      <td>2015-05-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>173</td>\n",
       "      <td>2015-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>177</td>\n",
       "      <td>2015-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>178</td>\n",
       "      <td>2015-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idaho</th>\n",
       "      <td>165</td>\n",
       "      <td>2015-05-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rhode Island</th>\n",
       "      <td>174</td>\n",
       "      <td>2015-05-19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                user_id user_sign_up_date\n",
       "user_state                               \n",
       "Alabama               5        2015-05-01\n",
       "Texas                 7        2015-05-01\n",
       "Oregon                1        2015-05-01\n",
       "Ohio                  3        2015-05-01\n",
       "North Carolina        2        2015-05-01\n",
       "New Mexico            4        2015-05-01\n",
       "New Jersey            6        2015-05-01\n",
       "Pennsylvania         11        2015-05-02\n",
       "New York             19        2015-05-02\n",
       "Minnesota             8        2015-05-02\n",
       "Michigan             13        2015-05-02\n",
       "Massachusetts        15        2015-05-02\n",
       "Maryland             18        2015-05-02\n",
       "Mississippi          23        2015-05-02\n",
       "Georgia              20        2015-05-02\n",
       "Utah                 29        2015-05-03\n",
       "Kentucky             34        2015-05-04\n",
       "California           39        2015-05-04\n",
       "Florida              41        2015-05-04\n",
       "Wisconsin            32        2015-05-04\n",
       "Illinois             45        2015-05-05\n",
       "Louisiana            50        2015-05-06\n",
       "West Virginia        60        2015-05-07\n",
       "Tennessee            70        2015-05-08\n",
       "South Carolina       64        2015-05-08\n",
       "Arkansas             78        2015-05-08\n",
       "Missouri             85        2015-05-09\n",
       "Arizona             105        2015-05-12\n",
       "Indiana             102        2015-05-12\n",
       "Alaska              106        2015-05-12\n",
       "Oklahoma            119        2015-05-16\n",
       "Nebraska            134        2015-05-16\n",
       "Washington          125        2015-05-16\n",
       "Connecticut         127        2015-05-16\n",
       "Virginia            142        2015-05-17\n",
       "North Dakota        135        2015-05-17\n",
       "Colorado            173        2015-05-19\n",
       "Kansas              177        2015-05-19\n",
       "Iowa                178        2015-05-19\n",
       "Idaho               165        2015-05-19\n",
       "Rhode Island        174        2015-05-19"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby('user_state').apply(find_first_user).sort_values(by='user_sign_up_date')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>song_played</th>\n",
       "      <th>time_played</th>\n",
       "      <th>user_id</th>\n",
       "      <th>user_sign_up_date</th>\n",
       "      <th>user_state</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOQMMKSQQH</td>\n",
       "      <td>Hey Jude</td>\n",
       "      <td>2015-06-11 21:51:35</td>\n",
       "      <td>122</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Louisiana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HWKKBQKNWI</td>\n",
       "      <td>We Can Work It Out</td>\n",
       "      <td>2015-06-06 16:49:19</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>Ohio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DKQSXVNJDH</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "      <td>2015-06-14 02:11:29</td>\n",
       "      <td>35</td>\n",
       "      <td>2015-05-04</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HLHRIDQTUW</td>\n",
       "      <td>P.s. I Love You</td>\n",
       "      <td>2015-06-08 12:26:10</td>\n",
       "      <td>126</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Illinois</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SUKJCSBCYW</td>\n",
       "      <td>Sgt. Pepper's Lonely Hearts Club Band</td>\n",
       "      <td>2015-06-28 14:57:00</td>\n",
       "      <td>6</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>New Jersey</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                            song_played         time_played  \\\n",
       "0  GOQMMKSQQH                               Hey Jude 2015-06-11 21:51:35   \n",
       "1  HWKKBQKNWI                     We Can Work It Out 2015-06-06 16:49:19   \n",
       "2  DKQSXVNJDH                   Back In the U.S.S.R. 2015-06-14 02:11:29   \n",
       "3  HLHRIDQTUW                        P.s. I Love You 2015-06-08 12:26:10   \n",
       "4  SUKJCSBCYW  Sgt. Pepper's Lonely Hearts Club Band 2015-06-28 14:57:00   \n",
       "\n",
       "   user_id user_sign_up_date  user_state  \n",
       "0      122        2015-05-16   Louisiana  \n",
       "1        3        2015-05-01        Ohio  \n",
       "2       35        2015-05-04  New Jersey  \n",
       "3      126        2015-05-16    Illinois  \n",
       "4        6        2015-05-01  New Jersey  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There should be a lot of different methods for this question. The simplest method is to choose the most popular songs. In addition, k-nearest neighbor (KNN) method can also be used. More advancely, collaborative filtering method can be implemented.\n",
    "\n",
    "Here, I implement a simple version of collaborative filtering algorithm for song recommendation. More specifically, the similarity of two songs is calculate using the number of users whole listen them together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>user_id</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>191</th>\n",
       "      <th>192</th>\n",
       "      <th>193</th>\n",
       "      <th>194</th>\n",
       "      <th>195</th>\n",
       "      <th>196</th>\n",
       "      <th>197</th>\n",
       "      <th>198</th>\n",
       "      <th>199</th>\n",
       "      <th>200</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>song_played</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A Day In The Life</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Hard Day's Night</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Saturday Club Xmas/Crimble Medley</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANYTIME AT ALL</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Across The Universe</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 196 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "user_id                              1    2    3    4    5    6    7    8    \\\n",
       "song_played                                                                   \n",
       "A Day In The Life                      0    0    1    1    0    1    0    0   \n",
       "A Hard Day's Night                     0    0    0    0    0    1    0    0   \n",
       "A Saturday Club Xmas/Crimble Medley    0    0    0    0    0    0    0    0   \n",
       "ANYTIME AT ALL                         0    0    0    0    0    0    0    0   \n",
       "Across The Universe                    0    0    0    1    0    0    0    0   \n",
       "\n",
       "user_id                              9    10  ...   191  192  193  194  195  \\\n",
       "song_played                                   ...                             \n",
       "A Day In The Life                      0    0 ...     0    0    1    1    0   \n",
       "A Hard Day's Night                     1    0 ...     0    0    0    0    1   \n",
       "A Saturday Club Xmas/Crimble Medley    0    0 ...     0    0    0    0    0   \n",
       "ANYTIME AT ALL                         0    0 ...     0    0    0    0    0   \n",
       "Across The Universe                    0    0 ...     0    0    0    0    0   \n",
       "\n",
       "user_id                              196  197  198  199  200  \n",
       "song_played                                                   \n",
       "A Day In The Life                      1    0    0    1    0  \n",
       "A Hard Day's Night                     0    0    0    0    0  \n",
       "A Saturday Club Xmas/Crimble Medley    0    0    0    0    0  \n",
       "ANYTIME AT ALL                         0    0    0    0    0  \n",
       "Across The Universe                    0    0    0    0    0  \n",
       "\n",
       "[5 rows x 196 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 1: build the Song-User matrix\n",
    "song_user = data.groupby(['song_played', 'user_id'])['id'].count().unstack(fill_value=0)\n",
    "song_user = (song_user > 0).astype(int)\n",
    "\n",
    "song_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>song_played</th>\n",
       "      <th>A Day In The Life</th>\n",
       "      <th>A Hard Day's Night</th>\n",
       "      <th>A Saturday Club Xmas/Crimble Medley</th>\n",
       "      <th>ANYTIME AT ALL</th>\n",
       "      <th>Across The Universe</th>\n",
       "      <th>All My Loving</th>\n",
       "      <th>All You Need Is Love</th>\n",
       "      <th>And Your Bird Can Sing</th>\n",
       "      <th>BAD BOY</th>\n",
       "      <th>BALLAD OF JOHN AND YOKO</th>\n",
       "      <th>...</th>\n",
       "      <th>We Can Work It Out</th>\n",
       "      <th>When I'm 64</th>\n",
       "      <th>While My Guitar Gently Weeps</th>\n",
       "      <th>Wild Honey Pie</th>\n",
       "      <th>With a Little Help From My Friends</th>\n",
       "      <th>YOUR MOTHER SHOULD KNOW</th>\n",
       "      <th>Yellow Submarine</th>\n",
       "      <th>Yesterday</th>\n",
       "      <th>You Never Give Me Your Money</th>\n",
       "      <th>You're Going To Lose That Girl</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>song_played</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>A Day In The Life</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.264392</td>\n",
       "      <td>0.139347</td>\n",
       "      <td>0.148968</td>\n",
       "      <td>0.132196</td>\n",
       "      <td>0.301023</td>\n",
       "      <td>0.295599</td>\n",
       "      <td>0.098533</td>\n",
       "      <td>0.197066</td>\n",
       "      <td>0.201129</td>\n",
       "      <td>...</td>\n",
       "      <td>0.516528</td>\n",
       "      <td>0.056888</td>\n",
       "      <td>0.578459</td>\n",
       "      <td>0.279852</td>\n",
       "      <td>0.399723</td>\n",
       "      <td>0.088131</td>\n",
       "      <td>0.330489</td>\n",
       "      <td>0.365433</td>\n",
       "      <td>0.164222</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Hard Day's Night</th>\n",
       "      <td>0.264392</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.146385</td>\n",
       "      <td>0.111803</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.091287</td>\n",
       "      <td>...</td>\n",
       "      <td>0.305788</td>\n",
       "      <td>0.129099</td>\n",
       "      <td>0.266996</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.215003</td>\n",
       "      <td>0.074536</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A Saturday Club Xmas/Crimble Medley</th>\n",
       "      <td>0.139347</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.161165</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.182574</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ANYTIME AT ALL</th>\n",
       "      <td>0.148968</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.164957</td>\n",
       "      <td>0.094491</td>\n",
       "      <td>0.125988</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.172292</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.188044</td>\n",
       "      <td>0.097590</td>\n",
       "      <td>0.191663</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.103835</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Across The Universe</th>\n",
       "      <td>0.132196</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.097590</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.101929</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.133498</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.061430</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "song_played                          A Day In The Life  A Hard Day's Night  \\\n",
       "song_played                                                                  \n",
       "A Day In The Life                             1.000000            0.264392   \n",
       "A Hard Day's Night                            0.264392            1.000000   \n",
       "A Saturday Club Xmas/Crimble Medley           0.139347            0.000000   \n",
       "ANYTIME AT ALL                                0.148968            0.000000   \n",
       "Across The Universe                           0.132196            0.100000   \n",
       "\n",
       "song_played                          A Saturday Club Xmas/Crimble Medley  \\\n",
       "song_played                                                                \n",
       "A Day In The Life                                               0.139347   \n",
       "A Hard Day's Night                                              0.000000   \n",
       "A Saturday Club Xmas/Crimble Medley                             1.000000   \n",
       "ANYTIME AT ALL                                                  0.000000   \n",
       "Across The Universe                                             0.000000   \n",
       "\n",
       "song_played                          ANYTIME AT ALL  Across The Universe  \\\n",
       "song_played                                                                \n",
       "A Day In The Life                          0.148968             0.132196   \n",
       "A Hard Day's Night                         0.000000             0.100000   \n",
       "A Saturday Club Xmas/Crimble Medley        0.000000             0.000000   \n",
       "ANYTIME AT ALL                             1.000000             0.000000   \n",
       "Across The Universe                        0.000000             1.000000   \n",
       "\n",
       "song_played                          All My Loving  All You Need Is Love  \\\n",
       "song_played                                                                \n",
       "A Day In The Life                         0.301023              0.295599   \n",
       "A Hard Day's Night                        0.146385              0.111803   \n",
       "A Saturday Club Xmas/Crimble Medley       0.000000              0.000000   \n",
       "ANYTIME AT ALL                            0.164957              0.094491   \n",
       "Across The Universe                       0.097590              0.000000   \n",
       "\n",
       "song_played                          And Your Bird Can Sing   BAD BOY  \\\n",
       "song_played                                                             \n",
       "A Day In The Life                                  0.098533  0.197066   \n",
       "A Hard Day's Night                                 0.000000  0.000000   \n",
       "A Saturday Club Xmas/Crimble Medley                0.000000  0.000000   \n",
       "ANYTIME AT ALL                                     0.125988  0.000000   \n",
       "Across The Universe                                0.000000  0.000000   \n",
       "\n",
       "song_played                          BALLAD OF JOHN AND YOKO  \\\n",
       "song_played                                                    \n",
       "A Day In The Life                                   0.201129   \n",
       "A Hard Day's Night                                  0.091287   \n",
       "A Saturday Club Xmas/Crimble Medley                 0.000000   \n",
       "ANYTIME AT ALL                                      0.000000   \n",
       "Across The Universe                                 0.000000   \n",
       "\n",
       "song_played                                       ...                \\\n",
       "song_played                                       ...                 \n",
       "A Day In The Life                                 ...                 \n",
       "A Hard Day's Night                                ...                 \n",
       "A Saturday Club Xmas/Crimble Medley               ...                 \n",
       "ANYTIME AT ALL                                    ...                 \n",
       "Across The Universe                               ...                 \n",
       "\n",
       "song_played                          We Can Work It Out  When I'm 64  \\\n",
       "song_played                                                            \n",
       "A Day In The Life                              0.516528     0.056888   \n",
       "A Hard Day's Night                             0.305788     0.129099   \n",
       "A Saturday Club Xmas/Crimble Medley            0.161165     0.000000   \n",
       "ANYTIME AT ALL                                 0.172292     0.000000   \n",
       "Across The Universe                            0.101929     0.000000   \n",
       "\n",
       "song_played                          While My Guitar Gently Weeps  \\\n",
       "song_played                                                         \n",
       "A Day In The Life                                        0.578459   \n",
       "A Hard Day's Night                                       0.266996   \n",
       "A Saturday Club Xmas/Crimble Medley                      0.000000   \n",
       "ANYTIME AT ALL                                           0.188044   \n",
       "Across The Universe                                      0.133498   \n",
       "\n",
       "song_played                          Wild Honey Pie  \\\n",
       "song_played                                           \n",
       "A Day In The Life                          0.279852   \n",
       "A Hard Day's Night                         0.000000   \n",
       "A Saturday Club Xmas/Crimble Medley        0.182574   \n",
       "ANYTIME AT ALL                             0.097590   \n",
       "Across The Universe                        0.000000   \n",
       "\n",
       "song_played                          With a Little Help From My Friends  \\\n",
       "song_played                                                               \n",
       "A Day In The Life                                              0.399723   \n",
       "A Hard Day's Night                                             0.000000   \n",
       "A Saturday Club Xmas/Crimble Medley                            0.000000   \n",
       "ANYTIME AT ALL                                                 0.191663   \n",
       "Across The Universe                                            0.000000   \n",
       "\n",
       "song_played                          YOUR MOTHER SHOULD KNOW  \\\n",
       "song_played                                                    \n",
       "A Day In The Life                                   0.088131   \n",
       "A Hard Day's Night                                  0.000000   \n",
       "A Saturday Club Xmas/Crimble Medley                 0.000000   \n",
       "ANYTIME AT ALL                                      0.000000   \n",
       "Across The Universe                                 0.000000   \n",
       "\n",
       "song_played                          Yellow Submarine  Yesterday  \\\n",
       "song_played                                                        \n",
       "A Day In The Life                            0.330489   0.365433   \n",
       "A Hard Day's Night                           0.050000   0.215003   \n",
       "A Saturday Club Xmas/Crimble Medley          0.000000   0.000000   \n",
       "ANYTIME AT ALL                               0.000000   0.103835   \n",
       "Across The Universe                          0.000000   0.061430   \n",
       "\n",
       "song_played                          You Never Give Me Your Money  \\\n",
       "song_played                                                         \n",
       "A Day In The Life                                        0.164222   \n",
       "A Hard Day's Night                                       0.074536   \n",
       "A Saturday Club Xmas/Crimble Medley                      0.000000   \n",
       "ANYTIME AT ALL                                           0.000000   \n",
       "Across The Universe                                      0.000000   \n",
       "\n",
       "song_played                          You're Going To Lose That Girl  \n",
       "song_played                                                          \n",
       "A Day In The Life                                               0.0  \n",
       "A Hard Day's Night                                              0.0  \n",
       "A Saturday Club Xmas/Crimble Medley                             0.0  \n",
       "ANYTIME AT ALL                                                  0.0  \n",
       "Across The Universe                                             0.0  \n",
       "\n",
       "[5 rows x 100 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Step 2: build song-song similarity matrix\n",
    "song_user_norm = normalize(song_user, axis=1)  # normalize the song-user matrix\n",
    "similarity = np.dot(song_user_norm, song_user_norm.T)  # calculate the similarity matrix\n",
    "similarity_df = pd.DataFrame(similarity, index=song_user.index, columns=song_user.index)\n",
    "\n",
    "similarity_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Step 3: find the top-k most similar songs\n",
    "def find_topk(song, similarity, k=1):\n",
    "    df = similarity.loc[song].sort_values(ascending=False)[1:k + 1].reset_index()\n",
    "    df = df.rename(columns={'song_played': 'Song', song: 'Similarity'})\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Song</th>\n",
       "      <th>Similarity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Revolution</td>\n",
       "      <td>0.705327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Come Together</td>\n",
       "      <td>0.691885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Get Back</td>\n",
       "      <td>0.671014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Hello Goodbye</td>\n",
       "      <td>0.610658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "      <td>0.607872</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Let It Be</td>\n",
       "      <td>0.594578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Hey Jude</td>\n",
       "      <td>0.591295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Lucy In The Sky With Diamonds</td>\n",
       "      <td>0.580249</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>While My Guitar Gently Weeps</td>\n",
       "      <td>0.578459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Here Comes The Sun</td>\n",
       "      <td>0.553557</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Song  Similarity\n",
       "0                     Revolution    0.705327\n",
       "1                  Come Together    0.691885\n",
       "2                       Get Back    0.671014\n",
       "3                  Hello Goodbye    0.610658\n",
       "4           Back In the U.S.S.R.    0.607872\n",
       "5                      Let It Be    0.594578\n",
       "6                       Hey Jude    0.591295\n",
       "7  Lucy In The Sky With Diamonds    0.580249\n",
       "8   While My Guitar Gently Weeps    0.578459\n",
       "9             Here Comes The Sun    0.553557"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Example: find the top 10 similar song for 'A Day In The Life'\n",
    "df = find_topk(song='A Day In The Life', similarity=similarity_df, k=10)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need to perform a A/B test ([reference](https://github.com/stasi009/TakeHomeDataChallenges/blob/master/08.SongChallenge/song_challenge.ipynb)):\n",
    "\n",
    "* randomly split users into two groups, one Control group and one Experiment group\n",
    "* Control group has no recommendation strategy\n",
    "* Experiment group recommend the next song\n",
    "* after running some time, perform a one-tailed t-test on 'average #play per hour'\n",
    "    - $H_0$: population 'average #play per hour' is same in two groups\n",
    "    - $H_a$: experiment group's population 'average #play per hour' is higher than control group's"
   ]
  },
  {
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}