8_ Song Challenge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e1ed4db1",
   "metadata": {},
   "source": [
    "## Goal of Analysis:\n",
    "\n",
    "### 1. answer the specific questions from managers\n",
    "\n",
    "### 2. build a song recommendation model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abdb50c2",
   "metadata": {},
   "source": [
    "### Data Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "b17926dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1d5c8583",
   "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>user_id</th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_sign_up_date</th>\n",
       "      <th>song_played</th>\n",
       "      <th>time_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOQMMKSQQH</td>\n",
       "      <td>122</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Hey Jude</td>\n",
       "      <td>2015-06-11 21:51:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HWKKBQKNWI</td>\n",
       "      <td>3</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>We Can Work It Out</td>\n",
       "      <td>2015-06-06 16:49:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DKQSXVNJDH</td>\n",
       "      <td>35</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2015-05-04</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "      <td>2015-06-14 02:11:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HLHRIDQTUW</td>\n",
       "      <td>126</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>P.s. I Love You</td>\n",
       "      <td>2015-06-08 12:26:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SUKJCSBCYW</td>\n",
       "      <td>6</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>Sgt. Pepper's Lonely Hearts Club Band</td>\n",
       "      <td>2015-06-28 14:57:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3995</th>\n",
       "      <td>MKVEXMRJGF</td>\n",
       "      <td>122</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Paperback Writer</td>\n",
       "      <td>2015-06-03 08:26:26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3996</th>\n",
       "      <td>HMBEUWPBQI</td>\n",
       "      <td>76</td>\n",
       "      <td>New York</td>\n",
       "      <td>2015-05-08</td>\n",
       "      <td>Reprise / Day in the Life</td>\n",
       "      <td>2015-06-14 15:46:04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3997</th>\n",
       "      <td>KIYDZUFUJN</td>\n",
       "      <td>68</td>\n",
       "      <td>Florida</td>\n",
       "      <td>2015-05-08</td>\n",
       "      <td>Get Back</td>\n",
       "      <td>2015-06-13 13:13:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3998</th>\n",
       "      <td>BZNJXQSTTL</td>\n",
       "      <td>138</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>2015-05-17</td>\n",
       "      <td>Help!</td>\n",
       "      <td>2015-06-01 17:01:40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3999</th>\n",
       "      <td>LXJTCLWRUV</td>\n",
       "      <td>188</td>\n",
       "      <td>New York</td>\n",
       "      <td>2015-05-20</td>\n",
       "      <td>Birthday</td>\n",
       "      <td>2015-06-25 18:13:41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4000 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  user_id  user_state user_sign_up_date  \\\n",
       "0     GOQMMKSQQH      122   Louisiana        2015-05-16   \n",
       "1     HWKKBQKNWI        3        Ohio        2015-05-01   \n",
       "2     DKQSXVNJDH       35  New Jersey        2015-05-04   \n",
       "3     HLHRIDQTUW      126    Illinois        2015-05-16   \n",
       "4     SUKJCSBCYW        6  New Jersey        2015-05-01   \n",
       "...          ...      ...         ...               ...   \n",
       "3995  MKVEXMRJGF      122   Louisiana        2015-05-16   \n",
       "3996  HMBEUWPBQI       76    New York        2015-05-08   \n",
       "3997  KIYDZUFUJN       68     Florida        2015-05-08   \n",
       "3998  BZNJXQSTTL      138     Alabama        2015-05-17   \n",
       "3999  LXJTCLWRUV      188    New York        2015-05-20   \n",
       "\n",
       "                                song_played         time_played  \n",
       "0                                  Hey Jude 2015-06-11 21:51:35  \n",
       "1                        We Can Work It Out 2015-06-06 16:49:19  \n",
       "2                      Back In the U.S.S.R. 2015-06-14 02:11:29  \n",
       "3                           P.s. I Love You 2015-06-08 12:26:10  \n",
       "4     Sgt. Pepper's Lonely Hearts Club Band 2015-06-28 14:57:00  \n",
       "...                                     ...                 ...  \n",
       "3995                       Paperback Writer 2015-06-03 08:26:26  \n",
       "3996              Reprise / Day in the Life 2015-06-14 15:46:04  \n",
       "3997                               Get Back 2015-06-13 13:13:11  \n",
       "3998                                  Help! 2015-06-01 17:01:40  \n",
       "3999                               Birthday 2015-06-25 18:13:41  \n",
       "\n",
       "[4000 rows x 6 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert_dates is similar to parse_date in pd.read_csv\n",
    "\n",
    "data = pd.read_json('song.json', convert_dates=['user_sign_up_date', 'time_played'])\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "f6788f9a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                           object\n",
       "user_id                       int64\n",
       "user_state                   object\n",
       "user_sign_up_date    datetime64[ns]\n",
       "song_played                  object\n",
       "time_played          datetime64[ns]\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9aa5e809",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   0\n",
       "user_id              0\n",
       "user_state           0\n",
       "user_sign_up_date    0\n",
       "song_played          0\n",
       "time_played          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cd44282",
   "metadata": {},
   "source": [
    "### Question 1： What are the top 3 and the bottom 3 states in terms of number of users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "6cb176d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# We will need to find the unique ID instead of just summing up the IDs\n",
    "\n",
    "user_group = data.groupby('user_state')['user_id'].apply(lambda x: len(np.unique(x))).sort_values(ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "0479c645",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_state\n",
       "New York      23\n",
       "California    21\n",
       "Texas         15\n",
       "Name: user_id, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 3:\n",
    "\n",
    "user_group.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "663c12ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user_state\n",
       "Nebraska    1\n",
       "Arizona     1\n",
       "Kansas      1\n",
       "Name: user_id, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 3:\n",
    "\n",
    "user_group.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19e7e1c5",
   "metadata": {},
   "source": [
    "The top three states are New York, California, and Texas. The last three states are Nebraska, Arizona, and Kansas."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f4bb3972",
   "metadata": {},
   "source": [
    "### Question 2: What are the top 3 and the bottom 3 states in terms of user engagement? (which states users are using the product a lot/very little)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bd0fdcb",
   "metadata": {},
   "source": [
    "#### Here, I define the user engagement as the average time a user play the song in each state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "eaa36118",
   "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>0</th>\n",
       "      <td>New York</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>California</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Texas</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ohio</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     user_state  user_count\n",
       "0      New York          23\n",
       "1    California          21\n",
       "2         Texas          15\n",
       "3  Pennsylvania           9\n",
       "4          Ohio           9"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first define the user_group as a dataframe and rename the column\n",
    "\n",
    "user_group = user_group.reset_index().rename(columns={'user_id': 'user_count'})\n",
    "user_group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ff408352",
   "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>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alabama</td>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alaska</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arizona</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arkansas</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>California</td>\n",
       "      <td>425</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_state  play_count\n",
       "0     Alabama         104\n",
       "1      Alaska          58\n",
       "2     Arizona          22\n",
       "3    Arkansas          34\n",
       "4  California         425"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a dataframe for play count per state\n",
    "\n",
    "state_id_group = data.groupby('user_state')['id'].count().reset_index().rename(columns={'id': 'play_count'})\n",
    "state_id_group.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e3e5e995",
   "metadata": {},
   "outputs": [],
   "source": [
    "# merge user count and play count\n",
    "\n",
    "state_user_play = pd.merge(left=user_group, right=state_id_group, on='user_state')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f8c128df",
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate average play\n",
    "state_user_play['average_play'] = state_user_play['play_count'] / state_user_play['user_count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "29085f12",
   "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",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Mississippi</td>\n",
       "      <td>3</td>\n",
       "      <td>85</td>\n",
       "      <td>28.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>1</td>\n",
       "      <td>27</td>\n",
       "      <td>27.000000</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\n",
       "25     Mississippi           3          85     28.333333\n",
       "37    Rhode Island           1          27     27.000000"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sort according to average_play\n",
    "state_user_play = state_user_play.sort_values(by='average_play', ascending=False)\n",
    "state_user_play.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "6d9f44f8",
   "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": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top 3\n",
    "\n",
    "state_user_play.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bbc85ff7",
   "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>17</th>\n",
       "      <td>Minnesota</td>\n",
       "      <td>4</td>\n",
       "      <td>42</td>\n",
       "      <td>10.5</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>40</th>\n",
       "      <td>Kansas</td>\n",
       "      <td>1</td>\n",
       "      <td>8</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_state  user_count  play_count  average_play\n",
       "17  Minnesota           4          42          10.5\n",
       "30   Virginia           2          17           8.5\n",
       "40     Kansas           1           8           8.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Last 3\n",
    "\n",
    "state_user_play.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f16ac2b0",
   "metadata": {},
   "source": [
    "So the top three states are Nebraska, Alaska, and South Carolina. The last three are Minnesota, Virginia, and Kansad."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e5f7b54",
   "metadata": {},
   "source": [
    "### Question 3: list the first user who signed-up from each state"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "48d9487b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "f3acbfd4",
   "metadata": {
    "scrolled": true
   },
   "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>user_id</th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_sign_up_date</th>\n",
       "      <th>song_played</th>\n",
       "      <th>time_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HWKKBQKNWI</td>\n",
       "      <td>3</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>We Can Work It Out</td>\n",
       "      <td>2015-06-06 16:49:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SUKJCSBCYW</td>\n",
       "      <td>6</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>Sgt. Pepper's Lonely Hearts Club Band</td>\n",
       "      <td>2015-06-28 14:57:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>HZICITYVPP</td>\n",
       "      <td>174</td>\n",
       "      <td>Rhode Island</td>\n",
       "      <td>2015-05-19</td>\n",
       "      <td>Revolution</td>\n",
       "      <td>2015-06-14 04:55:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>FDYRTNRNLB</td>\n",
       "      <td>11</td>\n",
       "      <td>Pennsylvania</td>\n",
       "      <td>2015-05-02</td>\n",
       "      <td>She Loves You</td>\n",
       "      <td>2015-06-05 18:46:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>RJAXELQHQP</td>\n",
       "      <td>85</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>2015-05-09</td>\n",
       "      <td>Come Together</td>\n",
       "      <td>2015-06-25 12:23:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3981</th>\n",
       "      <td>GZWDHMNTML</td>\n",
       "      <td>105</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>2015-05-12</td>\n",
       "      <td>Birthday</td>\n",
       "      <td>2015-06-01 13:05:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3988</th>\n",
       "      <td>QHLNQQZUVK</td>\n",
       "      <td>165</td>\n",
       "      <td>Idaho</td>\n",
       "      <td>2015-05-19</td>\n",
       "      <td>Revolution</td>\n",
       "      <td>2015-06-04 22:59:02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3989</th>\n",
       "      <td>DQXBQLVUSJ</td>\n",
       "      <td>44</td>\n",
       "      <td>California</td>\n",
       "      <td>2015-05-04</td>\n",
       "      <td>Yellow Submarine</td>\n",
       "      <td>2015-06-26 10:36:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3990</th>\n",
       "      <td>WJPGBHEPUX</td>\n",
       "      <td>178</td>\n",
       "      <td>Iowa</td>\n",
       "      <td>2015-05-19</td>\n",
       "      <td>Yellow Submarine</td>\n",
       "      <td>2015-06-05 15:02:01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3994</th>\n",
       "      <td>POKTQSPDTL</td>\n",
       "      <td>4</td>\n",
       "      <td>New Mexico</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "      <td>2015-06-18 15:27:55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1005 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              id  user_id    user_state user_sign_up_date  \\\n",
       "1     HWKKBQKNWI        3          Ohio        2015-05-01   \n",
       "4     SUKJCSBCYW        6    New Jersey        2015-05-01   \n",
       "8     HZICITYVPP      174  Rhode Island        2015-05-19   \n",
       "18    FDYRTNRNLB       11  Pennsylvania        2015-05-02   \n",
       "24    RJAXELQHQP       85      Missouri        2015-05-09   \n",
       "...          ...      ...           ...               ...   \n",
       "3981  GZWDHMNTML      105       Arizona        2015-05-12   \n",
       "3988  QHLNQQZUVK      165         Idaho        2015-05-19   \n",
       "3989  DQXBQLVUSJ       44    California        2015-05-04   \n",
       "3990  WJPGBHEPUX      178          Iowa        2015-05-19   \n",
       "3994  POKTQSPDTL        4    New Mexico        2015-05-01   \n",
       "\n",
       "                                song_played         time_played  \n",
       "1                        We Can Work It Out 2015-06-06 16:49:19  \n",
       "4     Sgt. Pepper's Lonely Hearts Club Band 2015-06-28 14:57:00  \n",
       "8                                Revolution 2015-06-14 04:55:17  \n",
       "18                            She Loves You 2015-06-05 18:46:03  \n",
       "24                            Come Together 2015-06-25 12:23:05  \n",
       "...                                     ...                 ...  \n",
       "3981                               Birthday 2015-06-01 13:05:17  \n",
       "3988                             Revolution 2015-06-04 22:59:02  \n",
       "3989                       Yellow Submarine 2015-06-26 10:36:35  \n",
       "3990                       Yellow Submarine 2015-06-05 15:02:01  \n",
       "3994                   Back In the U.S.S.R. 2015-06-18 15:27:55  \n",
       "\n",
       "[1005 rows x 6 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter out the first signup date for easy manipulation later on\n",
    "\n",
    "first_signup = data[data.groupby('user_state')['user_sign_up_date'].transform('min').eq(data['user_sign_up_date'])]\n",
    "\n",
    "first_signup\n",
    "# first_signup.loc[:,['user_id', 'user_state', 'user_sign_up_date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "cbda8bc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# first find out the first signup id for each state\n",
    "\n",
    "first_signup_id = first_signup.groupby('user_state')['user_id'].apply(lambda x: np.unique(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "c6c9795d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# then find out the first signup time in each state\n",
    "\n",
    "first_signup_time = first_signup.groupby('user_state')['user_sign_up_date'].apply(lambda x: np.unique(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "607d9e32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((41,), (41,))"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_signup_id.shape, first_signup_time.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "d963e91d",
   "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-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alaska</th>\n",
       "      <td>[106]</td>\n",
       "      <td>[2015-05-12T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arizona</th>\n",
       "      <td>[105]</td>\n",
       "      <td>[2015-05-12T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arkansas</th>\n",
       "      <td>[78]</td>\n",
       "      <td>[2015-05-08T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>[39, 44]</td>\n",
       "      <td>[2015-05-04T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Colorado</th>\n",
       "      <td>[166, 173]</td>\n",
       "      <td>[2015-05-19T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Connecticut</th>\n",
       "      <td>[127]</td>\n",
       "      <td>[2015-05-16T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Florida</th>\n",
       "      <td>[41, 43]</td>\n",
       "      <td>[2015-05-04T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Georgia</th>\n",
       "      <td>[16, 20]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Idaho</th>\n",
       "      <td>[165]</td>\n",
       "      <td>[2015-05-19T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Illinois</th>\n",
       "      <td>[45]</td>\n",
       "      <td>[2015-05-05T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Indiana</th>\n",
       "      <td>[102]</td>\n",
       "      <td>[2015-05-12T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Iowa</th>\n",
       "      <td>[178]</td>\n",
       "      <td>[2015-05-19T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kansas</th>\n",
       "      <td>[177]</td>\n",
       "      <td>[2015-05-19T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kentucky</th>\n",
       "      <td>[34]</td>\n",
       "      <td>[2015-05-04T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Louisiana</th>\n",
       "      <td>[50]</td>\n",
       "      <td>[2015-05-06T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Maryland</th>\n",
       "      <td>[18]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Massachusetts</th>\n",
       "      <td>[15]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michigan</th>\n",
       "      <td>[13]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Minnesota</th>\n",
       "      <td>[8, 21]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mississippi</th>\n",
       "      <td>[23, 26]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Missouri</th>\n",
       "      <td>[85]</td>\n",
       "      <td>[2015-05-09T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nebraska</th>\n",
       "      <td>[134]</td>\n",
       "      <td>[2015-05-16T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Jersey</th>\n",
       "      <td>[6]</td>\n",
       "      <td>[2015-05-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New Mexico</th>\n",
       "      <td>[4]</td>\n",
       "      <td>[2015-05-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>New York</th>\n",
       "      <td>[10, 12, 19, 22, 27]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Carolina</th>\n",
       "      <td>[2]</td>\n",
       "      <td>[2015-05-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>North Dakota</th>\n",
       "      <td>[135]</td>\n",
       "      <td>[2015-05-17T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>[3]</td>\n",
       "      <td>[2015-05-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oklahoma</th>\n",
       "      <td>[119]</td>\n",
       "      <td>[2015-05-16T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Oregon</th>\n",
       "      <td>[1]</td>\n",
       "      <td>[2015-05-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Pennsylvania</th>\n",
       "      <td>[11]</td>\n",
       "      <td>[2015-05-02T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rhode Island</th>\n",
       "      <td>[174]</td>\n",
       "      <td>[2015-05-19T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>South Carolina</th>\n",
       "      <td>[64]</td>\n",
       "      <td>[2015-05-08T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tennessee</th>\n",
       "      <td>[70]</td>\n",
       "      <td>[2015-05-08T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>[7]</td>\n",
       "      <td>[2015-05-01T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>[29]</td>\n",
       "      <td>[2015-05-03T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virginia</th>\n",
       "      <td>[142]</td>\n",
       "      <td>[2015-05-17T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Washington</th>\n",
       "      <td>[125]</td>\n",
       "      <td>[2015-05-16T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Virginia</th>\n",
       "      <td>[60]</td>\n",
       "      <td>[2015-05-07T00:00:00.000000000]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wisconsin</th>\n",
       "      <td>[32]</td>\n",
       "      <td>[2015-05-04T00:00:00.000000000]</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-01T00:00:00.000000000]\n",
       "Alaska                         [106]  [2015-05-12T00:00:00.000000000]\n",
       "Arizona                        [105]  [2015-05-12T00:00:00.000000000]\n",
       "Arkansas                        [78]  [2015-05-08T00:00:00.000000000]\n",
       "California                  [39, 44]  [2015-05-04T00:00:00.000000000]\n",
       "Colorado                  [166, 173]  [2015-05-19T00:00:00.000000000]\n",
       "Connecticut                    [127]  [2015-05-16T00:00:00.000000000]\n",
       "Florida                     [41, 43]  [2015-05-04T00:00:00.000000000]\n",
       "Georgia                     [16, 20]  [2015-05-02T00:00:00.000000000]\n",
       "Idaho                          [165]  [2015-05-19T00:00:00.000000000]\n",
       "Illinois                        [45]  [2015-05-05T00:00:00.000000000]\n",
       "Indiana                        [102]  [2015-05-12T00:00:00.000000000]\n",
       "Iowa                           [178]  [2015-05-19T00:00:00.000000000]\n",
       "Kansas                         [177]  [2015-05-19T00:00:00.000000000]\n",
       "Kentucky                        [34]  [2015-05-04T00:00:00.000000000]\n",
       "Louisiana                       [50]  [2015-05-06T00:00:00.000000000]\n",
       "Maryland                        [18]  [2015-05-02T00:00:00.000000000]\n",
       "Massachusetts                   [15]  [2015-05-02T00:00:00.000000000]\n",
       "Michigan                        [13]  [2015-05-02T00:00:00.000000000]\n",
       "Minnesota                    [8, 21]  [2015-05-02T00:00:00.000000000]\n",
       "Mississippi                 [23, 26]  [2015-05-02T00:00:00.000000000]\n",
       "Missouri                        [85]  [2015-05-09T00:00:00.000000000]\n",
       "Nebraska                       [134]  [2015-05-16T00:00:00.000000000]\n",
       "New Jersey                       [6]  [2015-05-01T00:00:00.000000000]\n",
       "New Mexico                       [4]  [2015-05-01T00:00:00.000000000]\n",
       "New York        [10, 12, 19, 22, 27]  [2015-05-02T00:00:00.000000000]\n",
       "North Carolina                   [2]  [2015-05-01T00:00:00.000000000]\n",
       "North Dakota                   [135]  [2015-05-17T00:00:00.000000000]\n",
       "Ohio                             [3]  [2015-05-01T00:00:00.000000000]\n",
       "Oklahoma                       [119]  [2015-05-16T00:00:00.000000000]\n",
       "Oregon                           [1]  [2015-05-01T00:00:00.000000000]\n",
       "Pennsylvania                    [11]  [2015-05-02T00:00:00.000000000]\n",
       "Rhode Island                   [174]  [2015-05-19T00:00:00.000000000]\n",
       "South Carolina                  [64]  [2015-05-08T00:00:00.000000000]\n",
       "Tennessee                       [70]  [2015-05-08T00:00:00.000000000]\n",
       "Texas                            [7]  [2015-05-01T00:00:00.000000000]\n",
       "Utah                            [29]  [2015-05-03T00:00:00.000000000]\n",
       "Virginia                       [142]  [2015-05-17T00:00:00.000000000]\n",
       "Washington                     [125]  [2015-05-16T00:00:00.000000000]\n",
       "West Virginia                   [60]  [2015-05-07T00:00:00.000000000]\n",
       "Wisconsin                       [32]  [2015-05-04T00:00:00.000000000]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# combine the two lists to get our user id and user signup date list\n",
    "\n",
    "first_signup_list = pd.merge(left = first_signup_id, right = first_signup_time, on='user_state', how='left')\n",
    "first_signup_list"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "397840eb",
   "metadata": {},
   "source": [
    "The manager can use this list to give the rewards for people in each state"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e7f17d7b",
   "metadata": {},
   "source": [
    "### Question 4: build a song recommendation model based on the previous songs the user listened"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "375973d7",
   "metadata": {},
   "source": [
    "#### There are two common methods for this recommendation problem, one is collaborative recommendation, the other is KNN model, \n",
    "\n",
    "\n",
    "#### The idea behind collaborative recommendation is to recommend songs that are similar to the user's past playlist\n",
    "\n",
    "\n",
    "#### The idea of KNN is to find out the most popular songs among all the users and recommend top 10 popular songs.\n",
    "\n",
    "#### I will use KNN model in this question.\n",
    "\n",
    "reference: https://github.com/SwathyMM/Top-10-song-recommendation-using-collaborative-filtering-and-KNN/blob/master/Song%20recommender.ipynb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "0dc6364f",
   "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>user_id</th>\n",
       "      <th>user_state</th>\n",
       "      <th>user_sign_up_date</th>\n",
       "      <th>song_played</th>\n",
       "      <th>time_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOQMMKSQQH</td>\n",
       "      <td>122</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>Hey Jude</td>\n",
       "      <td>2015-06-11 21:51:35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HWKKBQKNWI</td>\n",
       "      <td>3</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>We Can Work It Out</td>\n",
       "      <td>2015-06-06 16:49:19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DKQSXVNJDH</td>\n",
       "      <td>35</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2015-05-04</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "      <td>2015-06-14 02:11:29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HLHRIDQTUW</td>\n",
       "      <td>126</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>2015-05-16</td>\n",
       "      <td>P.s. I Love You</td>\n",
       "      <td>2015-06-08 12:26:10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SUKJCSBCYW</td>\n",
       "      <td>6</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>2015-05-01</td>\n",
       "      <td>Sgt. Pepper's Lonely Hearts Club Band</td>\n",
       "      <td>2015-06-28 14:57:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id  user_id  user_state user_sign_up_date  \\\n",
       "0  GOQMMKSQQH      122   Louisiana        2015-05-16   \n",
       "1  HWKKBQKNWI        3        Ohio        2015-05-01   \n",
       "2  DKQSXVNJDH       35  New Jersey        2015-05-04   \n",
       "3  HLHRIDQTUW      126    Illinois        2015-05-16   \n",
       "4  SUKJCSBCYW        6  New Jersey        2015-05-01   \n",
       "\n",
       "                             song_played         time_played  \n",
       "0                               Hey Jude 2015-06-11 21:51:35  \n",
       "1                     We Can Work It Out 2015-06-06 16:49:19  \n",
       "2                   Back In the U.S.S.R. 2015-06-14 02:11:29  \n",
       "3                        P.s. I Love You 2015-06-08 12:26:10  \n",
       "4  Sgt. Pepper's Lonely Hearts Club Band 2015-06-28 14:57:00  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "f8ac05a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                   4000\n",
       "user_id               196\n",
       "user_state             41\n",
       "user_sign_up_date      20\n",
       "song_played           100\n",
       "time_played          3997\n",
       "dtype: int64"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e72b6889",
   "metadata": {},
   "source": [
    "There are 100 unique songs, and 196 unique users."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "36af51d9",
   "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_played</th>\n",
       "      <th>user_id</th>\n",
       "      <th>play_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A Day In The Life</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A Day In The Life</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A Day In The Life</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A Day In The Life</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A Day In The Life</td>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2767</th>\n",
       "      <td>You Never Give Me Your Money</td>\n",
       "      <td>162</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2768</th>\n",
       "      <td>You Never Give Me Your Money</td>\n",
       "      <td>179</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2769</th>\n",
       "      <td>You Never Give Me Your Money</td>\n",
       "      <td>184</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2770</th>\n",
       "      <td>You're Going To Lose That Girl</td>\n",
       "      <td>69</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2771</th>\n",
       "      <td>You're Going To Lose That Girl</td>\n",
       "      <td>70</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2772 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         song_played  user_id  play_count\n",
       "0                  A Day In The Life        3           1\n",
       "1                  A Day In The Life        4           3\n",
       "2                  A Day In The Life        6           2\n",
       "3                  A Day In The Life       12           2\n",
       "4                  A Day In The Life       13           3\n",
       "...                              ...      ...         ...\n",
       "2767    You Never Give Me Your Money      162           1\n",
       "2768    You Never Give Me Your Money      179           1\n",
       "2769    You Never Give Me Your Money      184           1\n",
       "2770  You're Going To Lose That Girl       69           1\n",
       "2771  You're Going To Lose That Girl       70           1\n",
       "\n",
       "[2772 rows x 3 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first calculate how many times each song played by each user\n",
    "\n",
    "time_played = data.groupby(['song_played', 'user_id']).count().reset_index().loc[:,('song_played', 'user_id', 'id')]\n",
    "time_played.rename(columns = {'id':'play_count'}, inplace = True)\n",
    "time_played"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "60dd4b12",
   "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>user_id</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>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>198</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>199</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>196 rows × 100 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "song_played  A Day In The Life  A Hard Day's Night  \\\n",
       "user_id                                              \n",
       "1                          0.0                 0.0   \n",
       "2                          0.0                 0.0   \n",
       "3                          1.0                 0.0   \n",
       "4                          3.0                 0.0   \n",
       "5                          0.0                 0.0   \n",
       "...                        ...                 ...   \n",
       "196                        2.0                 0.0   \n",
       "197                        0.0                 0.0   \n",
       "198                        0.0                 0.0   \n",
       "199                        2.0                 0.0   \n",
       "200                        0.0                 0.0   \n",
       "\n",
       "song_played  A Saturday Club Xmas/Crimble Medley  ANYTIME AT ALL  \\\n",
       "user_id                                                            \n",
       "1                                            0.0             0.0   \n",
       "2                                            0.0             0.0   \n",
       "3                                            0.0             0.0   \n",
       "4                                            0.0             0.0   \n",
       "5                                            0.0             0.0   \n",
       "...                                          ...             ...   \n",
       "196                                          0.0             0.0   \n",
       "197                                          0.0             0.0   \n",
       "198                                          0.0             0.0   \n",
       "199                                          0.0             0.0   \n",
       "200                                          0.0             0.0   \n",
       "\n",
       "song_played  Across The Universe  All My Loving  All You Need Is Love  \\\n",
       "user_id                                                                 \n",
       "1                            0.0            0.0                   0.0   \n",
       "2                            0.0            0.0                   0.0   \n",
       "3                            0.0            0.0                   0.0   \n",
       "4                            1.0            0.0                   0.0   \n",
       "5                            0.0            0.0                   0.0   \n",
       "...                          ...            ...                   ...   \n",
       "196                          0.0            0.0                   0.0   \n",
       "197                          0.0            0.0                   0.0   \n",
       "198                          0.0            0.0                   0.0   \n",
       "199                          0.0            0.0                   0.0   \n",
       "200                          0.0            0.0                   0.0   \n",
       "\n",
       "song_played  And Your Bird Can Sing  BAD BOY  BALLAD OF JOHN AND YOKO  ...  \\\n",
       "user_id                                                                ...   \n",
       "1                               0.0      0.0                      0.0  ...   \n",
       "2                               0.0      0.0                      0.0  ...   \n",
       "3                               0.0      0.0                      0.0  ...   \n",
       "4                               0.0      0.0                      0.0  ...   \n",
       "5                               0.0      0.0                      0.0  ...   \n",
       "...                             ...      ...                      ...  ...   \n",
       "196                             0.0      0.0                      0.0  ...   \n",
       "197                             0.0      0.0                      0.0  ...   \n",
       "198                             0.0      0.0                      0.0  ...   \n",
       "199                             0.0      0.0                      0.0  ...   \n",
       "200                             1.0      1.0                      0.0  ...   \n",
       "\n",
       "song_played  We Can Work It Out  When I'm 64  While My Guitar Gently Weeps  \\\n",
       "user_id                                                                      \n",
       "1                           0.0          0.0                           1.0   \n",
       "2                           0.0          0.0                           1.0   \n",
       "3                           1.0          0.0                           0.0   \n",
       "4                           0.0          0.0                           0.0   \n",
       "5                           1.0          0.0                           0.0   \n",
       "...                         ...          ...                           ...   \n",
       "196                         1.0          0.0                           0.0   \n",
       "197                         0.0          0.0                           0.0   \n",
       "198                         1.0          0.0                           1.0   \n",
       "199                         1.0          0.0                           1.0   \n",
       "200                         0.0          0.0                           0.0   \n",
       "\n",
       "song_played  Wild Honey Pie  With a Little Help From My Friends  \\\n",
       "user_id                                                           \n",
       "1                       0.0                                 0.0   \n",
       "2                       0.0                                 0.0   \n",
       "3                       0.0                                 0.0   \n",
       "4                       0.0                                 0.0   \n",
       "5                       0.0                                 1.0   \n",
       "...                     ...                                 ...   \n",
       "196                     1.0                                 0.0   \n",
       "197                     0.0                                 0.0   \n",
       "198                     0.0                                 0.0   \n",
       "199                     0.0                                 0.0   \n",
       "200                     1.0                                 0.0   \n",
       "\n",
       "song_played  YOUR MOTHER SHOULD KNOW  Yellow Submarine  Yesterday  \\\n",
       "user_id                                                             \n",
       "1                                0.0               0.0        1.0   \n",
       "2                                0.0               0.0        1.0   \n",
       "3                                0.0               0.0        0.0   \n",
       "4                                0.0               0.0        0.0   \n",
       "5                                0.0               0.0        0.0   \n",
       "...                              ...               ...        ...   \n",
       "196                              0.0               0.0        2.0   \n",
       "197                              0.0               0.0        0.0   \n",
       "198                              0.0               0.0        0.0   \n",
       "199                              0.0               0.0        0.0   \n",
       "200                              0.0               0.0        1.0   \n",
       "\n",
       "song_played  You Never Give Me Your Money  You're Going To Lose That Girl  \n",
       "user_id                                                                    \n",
       "1                                     0.0                             0.0  \n",
       "2                                     1.0                             0.0  \n",
       "3                                     0.0                             0.0  \n",
       "4                                     0.0                             0.0  \n",
       "5                                     0.0                             0.0  \n",
       "...                                   ...                             ...  \n",
       "196                                   0.0                             0.0  \n",
       "197                                   0.0                             0.0  \n",
       "198                                   0.0                             0.0  \n",
       "199                                   0.0                             0.0  \n",
       "200                                   0.0                             0.0  \n",
       "\n",
       "[196 rows x 100 columns]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# let me put the songs and user id into a pivot table for easy manipulation\n",
    "\n",
    "song_pivot=time_played.pivot(index='user_id',columns='song_played',values='play_count').fillna(0)\n",
    "song_pivot"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ee6d6e2",
   "metadata": {},
   "source": [
    "We put 196 unique users and 100 unique songs into a matrix, the values are the times each user play each songs"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3b5a6146",
   "metadata": {},
   "source": [
    "#### fit the KNN model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "467179ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.neighbors import NearestNeighbors\n",
    "\n",
    "knn=NearestNeighbors(n_neighbors=10,metric='cosine')\n",
    "Model=knn.fit(song_pivot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "a6558add",
   "metadata": {},
   "outputs": [],
   "source": [
    "user = song_pivot.iloc[1,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "2fa4ae8f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(array([[1.11022302e-16, 2.40565667e-01, 2.81250000e-01, 2.88487526e-01,\n",
       "         3.20975287e-01, 3.31650408e-01, 3.37410844e-01, 3.62112046e-01,\n",
       "         3.65208281e-01, 3.75000000e-01]]),\n",
       " array([[  1,  93, 187,  79,  73,  42, 175, 167,  19, 138]], dtype=int64))"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "distances, indices = Model.kneighbors([user])\n",
    "distances, indices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "aa3dd2f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[181, 32, 150, 136, 76, 18, 106, 38, 14]"
      ]
     },
     "execution_count": 148,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neighbors=[]\n",
    "for item in indices[0][1:]:\n",
    "    neighbors.append(time_played[time_played.index == item].user_id.values[0])\n",
    "neighbors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "b6402747",
   "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>play_count</th>\n",
       "      <th>song_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>181</td>\n",
       "      <td>2</td>\n",
       "      <td>A Day In The Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>181</td>\n",
       "      <td>1</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>181</td>\n",
       "      <td>1</td>\n",
       "      <td>Can't Buy Me Love</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>181</td>\n",
       "      <td>2</td>\n",
       "      <td>Come Together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>181</td>\n",
       "      <td>2</td>\n",
       "      <td>Get Back</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>Sgt. Pepper/with A Little Help From My Friends</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>The Fool On The Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>The Long And Winding Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "      <td>Twist and Shout</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>14</td>\n",
       "      <td>3</td>\n",
       "      <td>While My Guitar Gently Weeps</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>161 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id play_count                                     song_played\n",
       "0       181          2                               A Day In The Life\n",
       "1       181          1                            Back In the U.S.S.R.\n",
       "2       181          1                               Can't Buy Me Love\n",
       "3       181          2                                   Come Together\n",
       "4       181          2                                        Get Back\n",
       "..      ...        ...                                             ...\n",
       "156      14          1  Sgt. Pepper/with A Little Help From My Friends\n",
       "157      14          1                            The Fool On The Hill\n",
       "158      14          1                       The Long And Winding Road\n",
       "159      14          1                                 Twist and Shout\n",
       "160      14          3                    While My Guitar Gently Weeps\n",
       "\n",
       "[161 rows x 3 columns]"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neighbor_songs=pd.DataFrame(columns=['user_id','play_count','song_played'])\n",
    "\n",
    "for item in neighbors:\n",
    "    neighbor_songs=neighbor_songs.append(time_played[time_played.user_id == item],ignore_index = True)\n",
    "neighbor_songs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "c84eb331",
   "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>Count</th>\n",
       "      <th>song_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>A Day In The Life</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Back In the U.S.S.R.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Can't Buy Me Love</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Come Together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Get Back</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>1</td>\n",
       "      <td>Sgt. Pepper/with A Little Help From My Friends</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>1</td>\n",
       "      <td>The Fool On The Hill</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>1</td>\n",
       "      <td>The Long And Winding Road</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>1</td>\n",
       "      <td>Twist and Shout</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>3</td>\n",
       "      <td>While My Guitar Gently Weeps</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>161 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Count                                     song_played\n",
       "0       2                               A Day In The Life\n",
       "1       1                            Back In the U.S.S.R.\n",
       "2       1                               Can't Buy Me Love\n",
       "3       2                                   Come Together\n",
       "4       2                                        Get Back\n",
       "..    ...                                             ...\n",
       "156     1  Sgt. Pepper/with A Little Help From My Friends\n",
       "157     1                            The Fool On The Hill\n",
       "158     1                       The Long And Winding Road\n",
       "159     1                                 Twist and Shout\n",
       "160     3                    While My Guitar Gently Weeps\n",
       "\n",
       "[161 rows x 2 columns]"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neighbor_songs=pd.DataFrame({'Count':neighbor_songs['play_count'],'song_played':neighbor_songs['song_played'].tolist()})\n",
    "neighbor_songs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "ff14ae06",
   "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>Count</th>\n",
       "      <th>song_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>Come Together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Hello Goodbye</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>Come Together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Come Together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>Come Together</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>1</td>\n",
       "      <td>Sgt. Pepper/with A Little Help From My Friends</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>1</td>\n",
       "      <td>Can't Buy Me Love</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>1</td>\n",
       "      <td>Birthday</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>1</td>\n",
       "      <td>She Loves You</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>1</td>\n",
       "      <td>Get Back</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>161 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Count                                     song_played\n",
       "0       6                                   Come Together\n",
       "1       5                                   Hello Goodbye\n",
       "2       5                                   Come Together\n",
       "3       4                                   Come Together\n",
       "4       3                                   Come Together\n",
       "..    ...                                             ...\n",
       "156     1  Sgt. Pepper/with A Little Help From My Friends\n",
       "157     1                               Can't Buy Me Love\n",
       "158     1                                        Birthday\n",
       "159     1                                   She Loves You\n",
       "160     1                                        Get Back\n",
       "\n",
       "[161 rows x 2 columns]"
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "neighbor_songs=neighbor_songs.sort_values('Count',ascending=False).reset_index(drop=True)\n",
    "neighbor_songs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "36597927",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                                                0\n",
      "0                               A Day In The Life\n",
      "1                          And Your Bird Can Sing\n",
      "2                                   Come Together\n",
      "3                                        Get Back\n",
      "4                                   Hello Goodbye\n",
      "5                              Here Comes The Sun\n",
      "6                                       Let It Be\n",
      "7                                      Revolution\n",
      "8  Sgt. Pepper/with A Little Help From My Friends\n",
      "9                    While My Guitar Gently Weeps\n"
     ]
    }
   ],
   "source": [
    "Recommended_Song=neighbor_songs['song_played'][0:20]\n",
    "print(pd.DataFrame(np.unique(Recommended_Song)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ada474b2",
   "metadata": {},
   "source": [
    "We can recommend those top 10 most popular songs to the users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "07b8b0c2",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}