data_cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dependecy\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>url</th>\n",
       "      <th>WeekID</th>\n",
       "      <th>Week Position</th>\n",
       "      <th>Song</th>\n",
       "      <th>Performer</th>\n",
       "      <th>SongID</th>\n",
       "      <th>Instance</th>\n",
       "      <th>Previous Week Position</th>\n",
       "      <th>Peak Position</th>\n",
       "      <th>Weeks on Chart</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1958-0...</td>\n",
       "      <td>8/2/1958</td>\n",
       "      <td>1</td>\n",
       "      <td>Poor Little Fool</td>\n",
       "      <td>Ricky Nelson</td>\n",
       "      <td>Poor Little FoolRicky Nelson</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1995-1...</td>\n",
       "      <td>12/2/1995</td>\n",
       "      <td>1</td>\n",
       "      <td>One Sweet Day</td>\n",
       "      <td>Mariah Carey &amp; Boyz II Men</td>\n",
       "      <td>One Sweet DayMariah Carey &amp; Boyz II Men</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1997-1...</td>\n",
       "      <td>10/11/1997</td>\n",
       "      <td>1</td>\n",
       "      <td>Candle In The Wind 1997/Something About The Wa...</td>\n",
       "      <td>Elton John</td>\n",
       "      <td>Candle In The Wind 1997/Something About The Wa...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/2006-0...</td>\n",
       "      <td>7/1/2006</td>\n",
       "      <td>1</td>\n",
       "      <td>Do I Make You Proud</td>\n",
       "      <td>Taylor Hicks</td>\n",
       "      <td>Do I Make You ProudTaylor Hicks</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/2009-1...</td>\n",
       "      <td>10/24/2009</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Britney Spears</td>\n",
       "      <td>3Britney Spears</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320490</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>1/5/2019</td>\n",
       "      <td>8</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>7</td>\n",
       "      <td>13.0</td>\n",
       "      <td>8</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320491</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>12/7/2019</td>\n",
       "      <td>47</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320492</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>12/14/2019</td>\n",
       "      <td>23</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>47.0</td>\n",
       "      <td>23</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320493</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>12/21/2019</td>\n",
       "      <td>15</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>23.0</td>\n",
       "      <td>15</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320494</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>12/28/2019</td>\n",
       "      <td>9</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>15.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>320495 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      url      WeekID  \\\n",
       "0       http://www.billboard.com/charts/hot-100/1958-0...    8/2/1958   \n",
       "1       http://www.billboard.com/charts/hot-100/1995-1...   12/2/1995   \n",
       "2       http://www.billboard.com/charts/hot-100/1997-1...  10/11/1997   \n",
       "3       http://www.billboard.com/charts/hot-100/2006-0...    7/1/2006   \n",
       "4       http://www.billboard.com/charts/hot-100/2009-1...  10/24/2009   \n",
       "...                                                   ...         ...   \n",
       "320490  https://www.billboard.com/charts/hot-100/2019-...    1/5/2019   \n",
       "320491  https://www.billboard.com/charts/hot-100/2019-...   12/7/2019   \n",
       "320492  https://www.billboard.com/charts/hot-100/2019-...  12/14/2019   \n",
       "320493  https://www.billboard.com/charts/hot-100/2019-...  12/21/2019   \n",
       "320494  https://www.billboard.com/charts/hot-100/2019-...  12/28/2019   \n",
       "\n",
       "        Week Position                                               Song  \\\n",
       "0                   1                                   Poor Little Fool   \n",
       "1                   1                                      One Sweet Day   \n",
       "2                   1  Candle In The Wind 1997/Something About The Wa...   \n",
       "3                   1                                Do I Make You Proud   \n",
       "4                   1                                                  3   \n",
       "...               ...                                                ...   \n",
       "320490              8                                   Jingle Bell Rock   \n",
       "320491             47                                   Jingle Bell Rock   \n",
       "320492             23                                   Jingle Bell Rock   \n",
       "320493             15                                   Jingle Bell Rock   \n",
       "320494              9                                   Jingle Bell Rock   \n",
       "\n",
       "                         Performer  \\\n",
       "0                     Ricky Nelson   \n",
       "1       Mariah Carey & Boyz II Men   \n",
       "2                       Elton John   \n",
       "3                     Taylor Hicks   \n",
       "4                   Britney Spears   \n",
       "...                            ...   \n",
       "320490                 Bobby Helms   \n",
       "320491                 Bobby Helms   \n",
       "320492                 Bobby Helms   \n",
       "320493                 Bobby Helms   \n",
       "320494                 Bobby Helms   \n",
       "\n",
       "                                                   SongID  Instance  \\\n",
       "0                            Poor Little FoolRicky Nelson         1   \n",
       "1                 One Sweet DayMariah Carey & Boyz II Men         1   \n",
       "2       Candle In The Wind 1997/Something About The Wa...         1   \n",
       "3                         Do I Make You ProudTaylor Hicks         1   \n",
       "4                                         3Britney Spears         1   \n",
       "...                                                   ...       ...   \n",
       "320490                        Jingle Bell RockBobby Helms         7   \n",
       "320491                        Jingle Bell RockBobby Helms         8   \n",
       "320492                        Jingle Bell RockBobby Helms         8   \n",
       "320493                        Jingle Bell RockBobby Helms         8   \n",
       "320494                        Jingle Bell RockBobby Helms         8   \n",
       "\n",
       "        Previous Week Position  Peak Position  Weeks on Chart  \n",
       "0                          NaN              1               1  \n",
       "1                          NaN              1               1  \n",
       "2                          NaN              1               1  \n",
       "3                          NaN              1               1  \n",
       "4                          NaN              1               1  \n",
       "...                        ...            ...             ...  \n",
       "320490                    13.0              8              25  \n",
       "320491                     NaN             47              26  \n",
       "320492                    47.0             23              27  \n",
       "320493                    23.0             15              28  \n",
       "320494                    15.0              9              29  \n",
       "\n",
       "[320495 rows x 10 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read the billboard data into pandas\n",
    "hot_100 = pd.read_csv('data/hot_100_1958_2019.csv')\n",
    "\n",
    "# Display dataframe \n",
    "hot_100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "url                        object\n",
       "WeekID                     object\n",
       "Week Position               int64\n",
       "Song                       object\n",
       "Performer                  object\n",
       "SongID                     object\n",
       "Instance                    int64\n",
       "Previous Week Position    float64\n",
       "Peak Position               int64\n",
       "Weeks on Chart              int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show column data types\n",
    "hot_100.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert 'WeekID' from a string to a datetime object\n",
    "hot_100['WeekID'] = pd.to_datetime(hot_100['WeekID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "url                               object\n",
       "WeekID                    datetime64[ns]\n",
       "Week Position                      int64\n",
       "Song                              object\n",
       "Performer                         object\n",
       "SongID                            object\n",
       "Instance                           int64\n",
       "Previous Week Position           float64\n",
       "Peak Position                      int64\n",
       "Weeks on Chart                     int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show column data types\n",
    "hot_100.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>url</th>\n",
       "      <th>WeekID</th>\n",
       "      <th>Week Position</th>\n",
       "      <th>Song</th>\n",
       "      <th>Performer</th>\n",
       "      <th>SongID</th>\n",
       "      <th>Instance</th>\n",
       "      <th>Previous Week Position</th>\n",
       "      <th>Peak Position</th>\n",
       "      <th>Weeks on Chart</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>320494</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>9</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>15.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254238</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>74</td>\n",
       "      <td>Death</td>\n",
       "      <td>Trippie Redd Featuring DaBaby</td>\n",
       "      <td>DeathTrippie Redd Featuring DaBaby</td>\n",
       "      <td>1</td>\n",
       "      <td>64.0</td>\n",
       "      <td>59</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72380</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>14</td>\n",
       "      <td>Ballin'</td>\n",
       "      <td>Mustard Featuring Roddy Ricch</td>\n",
       "      <td>Ballin'Mustard Featuring Roddy Ricch</td>\n",
       "      <td>1</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>264782</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>77</td>\n",
       "      <td>Leave Em Alone</td>\n",
       "      <td>Layton Greene, Lil Baby, City Girls &amp; PnB Rock</td>\n",
       "      <td>Leave Em AloneLayton Greene, Lil Baby, City Gi...</td>\n",
       "      <td>1</td>\n",
       "      <td>69.0</td>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38074</th>\n",
       "      <td>https://www.billboard.com/charts/hot-100/2019-...</td>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>93</td>\n",
       "      <td>Baby Sitter</td>\n",
       "      <td>DaBaby Featuring Offset</td>\n",
       "      <td>Baby SitterDaBaby Featuring Offset</td>\n",
       "      <td>1</td>\n",
       "      <td>84.0</td>\n",
       "      <td>59</td>\n",
       "      <td>19</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140290</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1958-0...</td>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>57</td>\n",
       "      <td>You Need Hands</td>\n",
       "      <td>Eydie Gorme</td>\n",
       "      <td>You Need HandsEydie Gorme</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13370</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1958-0...</td>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>14</td>\n",
       "      <td>If Dreams Came True</td>\n",
       "      <td>Pat Boone</td>\n",
       "      <td>If Dreams Came TruePat Boone</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13353</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1958-0...</td>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>13</td>\n",
       "      <td>Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...</td>\n",
       "      <td>Patti Page</td>\n",
       "      <td>Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28499</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1958-0...</td>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>71</td>\n",
       "      <td>Got A Match?</td>\n",
       "      <td>Frank Gallup</td>\n",
       "      <td>Got A Match?Frank Gallup</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://www.billboard.com/charts/hot-100/1958-0...</td>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>1</td>\n",
       "      <td>Poor Little Fool</td>\n",
       "      <td>Ricky Nelson</td>\n",
       "      <td>Poor Little FoolRicky Nelson</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>320495 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                      url     WeekID  \\\n",
       "320494  https://www.billboard.com/charts/hot-100/2019-... 2019-12-28   \n",
       "254238  https://www.billboard.com/charts/hot-100/2019-... 2019-12-28   \n",
       "72380   https://www.billboard.com/charts/hot-100/2019-... 2019-12-28   \n",
       "264782  https://www.billboard.com/charts/hot-100/2019-... 2019-12-28   \n",
       "38074   https://www.billboard.com/charts/hot-100/2019-... 2019-12-28   \n",
       "...                                                   ...        ...   \n",
       "140290  http://www.billboard.com/charts/hot-100/1958-0... 1958-08-02   \n",
       "13370   http://www.billboard.com/charts/hot-100/1958-0... 1958-08-02   \n",
       "13353   http://www.billboard.com/charts/hot-100/1958-0... 1958-08-02   \n",
       "28499   http://www.billboard.com/charts/hot-100/1958-0... 1958-08-02   \n",
       "0       http://www.billboard.com/charts/hot-100/1958-0... 1958-08-02   \n",
       "\n",
       "        Week Position                                               Song  \\\n",
       "320494              9                                   Jingle Bell Rock   \n",
       "254238             74                                              Death   \n",
       "72380              14                                            Ballin'   \n",
       "264782             77                                     Leave Em Alone   \n",
       "38074              93                                        Baby Sitter   \n",
       "...               ...                                                ...   \n",
       "140290             57                                     You Need Hands   \n",
       "13370              14                                If Dreams Came True   \n",
       "13353              13  Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...   \n",
       "28499              71                                       Got A Match?   \n",
       "0                   1                                   Poor Little Fool   \n",
       "\n",
       "                                             Performer  \\\n",
       "320494                                     Bobby Helms   \n",
       "254238                   Trippie Redd Featuring DaBaby   \n",
       "72380                    Mustard Featuring Roddy Ricch   \n",
       "264782  Layton Greene, Lil Baby, City Girls & PnB Rock   \n",
       "38074                          DaBaby Featuring Offset   \n",
       "...                                                ...   \n",
       "140290                                     Eydie Gorme   \n",
       "13370                                        Pat Boone   \n",
       "13353                                       Patti Page   \n",
       "28499                                     Frank Gallup   \n",
       "0                                         Ricky Nelson   \n",
       "\n",
       "                                                   SongID  Instance  \\\n",
       "320494                        Jingle Bell RockBobby Helms         8   \n",
       "254238                 DeathTrippie Redd Featuring DaBaby         1   \n",
       "72380                Ballin'Mustard Featuring Roddy Ricch         1   \n",
       "264782  Leave Em AloneLayton Greene, Lil Baby, City Gi...         1   \n",
       "38074                  Baby SitterDaBaby Featuring Offset         1   \n",
       "...                                                   ...       ...   \n",
       "140290                          You Need HandsEydie Gorme         1   \n",
       "13370                        If Dreams Came TruePat Boone         1   \n",
       "13353   Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...         1   \n",
       "28499                            Got A Match?Frank Gallup         1   \n",
       "0                            Poor Little FoolRicky Nelson         1   \n",
       "\n",
       "        Previous Week Position  Peak Position  Weeks on Chart  \n",
       "320494                    15.0              9              29  \n",
       "254238                    64.0             59               5  \n",
       "72380                     18.0             14              22  \n",
       "264782                    69.0             60              11  \n",
       "38074                     84.0             59              19  \n",
       "...                        ...            ...             ...  \n",
       "140290                     NaN             57               1  \n",
       "13370                      NaN             14               1  \n",
       "13353                      NaN             13               1  \n",
       "28499                      NaN             71               1  \n",
       "0                          NaN              1               1  \n",
       "\n",
       "[320495 rows x 10 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sort dataframe by 'WeekID'\n",
    "sorted_hot_100 = hot_100.sort_values(['WeekID'], ascending=[False])\n",
    "sorted_hot_100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete 'url' column\n",
    "del sorted_hot_100['url']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>WeekID</th>\n",
       "      <th>Week Position</th>\n",
       "      <th>Song</th>\n",
       "      <th>Performer</th>\n",
       "      <th>SongID</th>\n",
       "      <th>Instance</th>\n",
       "      <th>Previous Week Position</th>\n",
       "      <th>Peak Position</th>\n",
       "      <th>Weeks on Chart</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>9</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>15.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>74</td>\n",
       "      <td>Death</td>\n",
       "      <td>Trippie Redd Featuring DaBaby</td>\n",
       "      <td>DeathTrippie Redd Featuring DaBaby</td>\n",
       "      <td>1</td>\n",
       "      <td>64.0</td>\n",
       "      <td>59</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>14</td>\n",
       "      <td>Ballin'</td>\n",
       "      <td>Mustard Featuring Roddy Ricch</td>\n",
       "      <td>Ballin'Mustard Featuring Roddy Ricch</td>\n",
       "      <td>1</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>77</td>\n",
       "      <td>Leave Em Alone</td>\n",
       "      <td>Layton Greene, Lil Baby, City Girls &amp; PnB Rock</td>\n",
       "      <td>Leave Em AloneLayton Greene, Lil Baby, City Gi...</td>\n",
       "      <td>1</td>\n",
       "      <td>69.0</td>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>93</td>\n",
       "      <td>Baby Sitter</td>\n",
       "      <td>DaBaby Featuring Offset</td>\n",
       "      <td>Baby SitterDaBaby Featuring Offset</td>\n",
       "      <td>1</td>\n",
       "      <td>84.0</td>\n",
       "      <td>59</td>\n",
       "      <td>19</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320490</th>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>57</td>\n",
       "      <td>You Need Hands</td>\n",
       "      <td>Eydie Gorme</td>\n",
       "      <td>You Need HandsEydie Gorme</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320491</th>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>14</td>\n",
       "      <td>If Dreams Came True</td>\n",
       "      <td>Pat Boone</td>\n",
       "      <td>If Dreams Came TruePat Boone</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320492</th>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>13</td>\n",
       "      <td>Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...</td>\n",
       "      <td>Patti Page</td>\n",
       "      <td>Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320493</th>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>71</td>\n",
       "      <td>Got A Match?</td>\n",
       "      <td>Frank Gallup</td>\n",
       "      <td>Got A Match?Frank Gallup</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>71</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>320494</th>\n",
       "      <td>1958-08-02</td>\n",
       "      <td>1</td>\n",
       "      <td>Poor Little Fool</td>\n",
       "      <td>Ricky Nelson</td>\n",
       "      <td>Poor Little FoolRicky Nelson</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>320495 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           WeekID  Week Position  \\\n",
       "0      2019-12-28              9   \n",
       "1      2019-12-28             74   \n",
       "2      2019-12-28             14   \n",
       "3      2019-12-28             77   \n",
       "4      2019-12-28             93   \n",
       "...           ...            ...   \n",
       "320490 1958-08-02             57   \n",
       "320491 1958-08-02             14   \n",
       "320492 1958-08-02             13   \n",
       "320493 1958-08-02             71   \n",
       "320494 1958-08-02              1   \n",
       "\n",
       "                                                     Song  \\\n",
       "0                                        Jingle Bell Rock   \n",
       "1                                                   Death   \n",
       "2                                                 Ballin'   \n",
       "3                                          Leave Em Alone   \n",
       "4                                             Baby Sitter   \n",
       "...                                                   ...   \n",
       "320490                                     You Need Hands   \n",
       "320491                                If Dreams Came True   \n",
       "320492  Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...   \n",
       "320493                                       Got A Match?   \n",
       "320494                                   Poor Little Fool   \n",
       "\n",
       "                                             Performer  \\\n",
       "0                                          Bobby Helms   \n",
       "1                        Trippie Redd Featuring DaBaby   \n",
       "2                        Mustard Featuring Roddy Ricch   \n",
       "3       Layton Greene, Lil Baby, City Girls & PnB Rock   \n",
       "4                              DaBaby Featuring Offset   \n",
       "...                                                ...   \n",
       "320490                                     Eydie Gorme   \n",
       "320491                                       Pat Boone   \n",
       "320492                                      Patti Page   \n",
       "320493                                    Frank Gallup   \n",
       "320494                                    Ricky Nelson   \n",
       "\n",
       "                                                   SongID  Instance  \\\n",
       "0                             Jingle Bell RockBobby Helms         8   \n",
       "1                      DeathTrippie Redd Featuring DaBaby         1   \n",
       "2                    Ballin'Mustard Featuring Roddy Ricch         1   \n",
       "3       Leave Em AloneLayton Greene, Lil Baby, City Gi...         1   \n",
       "4                      Baby SitterDaBaby Featuring Offset         1   \n",
       "...                                                   ...       ...   \n",
       "320490                          You Need HandsEydie Gorme         1   \n",
       "320491                       If Dreams Came TruePat Boone         1   \n",
       "320492  Left Right Out Of Your Heart (Hi Lee Hi Lo Hi ...         1   \n",
       "320493                           Got A Match?Frank Gallup         1   \n",
       "320494                       Poor Little FoolRicky Nelson         1   \n",
       "\n",
       "        Previous Week Position  Peak Position  Weeks on Chart  \n",
       "0                         15.0              9              29  \n",
       "1                         64.0             59               5  \n",
       "2                         18.0             14              22  \n",
       "3                         69.0             60              11  \n",
       "4                         84.0             59              19  \n",
       "...                        ...            ...             ...  \n",
       "320490                     NaN             57               1  \n",
       "320491                     NaN             14               1  \n",
       "320492                     NaN             13               1  \n",
       "320493                     NaN             71               1  \n",
       "320494                     NaN              1               1  \n",
       "\n",
       "[320495 rows x 9 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Re-index the dataframe\n",
    "clean_hot_100 = sorted_hot_100.reset_index(drop=True)\n",
    "\n",
    "# Display dataframe\n",
    "clean_hot_100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>week_id</th>\n",
       "      <th>week_pos</th>\n",
       "      <th>track</th>\n",
       "      <th>artist</th>\n",
       "      <th>track_id</th>\n",
       "      <th>instance</th>\n",
       "      <th>prev_week_pos</th>\n",
       "      <th>peak_pos</th>\n",
       "      <th>weeks_on_chart</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>9</td>\n",
       "      <td>Jingle Bell Rock</td>\n",
       "      <td>Bobby Helms</td>\n",
       "      <td>Jingle Bell RockBobby Helms</td>\n",
       "      <td>8</td>\n",
       "      <td>15.0</td>\n",
       "      <td>9</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>74</td>\n",
       "      <td>Death</td>\n",
       "      <td>Trippie Redd Featuring DaBaby</td>\n",
       "      <td>DeathTrippie Redd Featuring DaBaby</td>\n",
       "      <td>1</td>\n",
       "      <td>64.0</td>\n",
       "      <td>59</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>14</td>\n",
       "      <td>Ballin'</td>\n",
       "      <td>Mustard Featuring Roddy Ricch</td>\n",
       "      <td>Ballin'Mustard Featuring Roddy Ricch</td>\n",
       "      <td>1</td>\n",
       "      <td>18.0</td>\n",
       "      <td>14</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>77</td>\n",
       "      <td>Leave Em Alone</td>\n",
       "      <td>Layton Greene, Lil Baby, City Girls &amp; PnB Rock</td>\n",
       "      <td>Leave Em AloneLayton Greene, Lil Baby, City Gi...</td>\n",
       "      <td>1</td>\n",
       "      <td>69.0</td>\n",
       "      <td>60</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-12-28</td>\n",
       "      <td>93</td>\n",
       "      <td>Baby Sitter</td>\n",
       "      <td>DaBaby Featuring Offset</td>\n",
       "      <td>Baby SitterDaBaby Featuring Offset</td>\n",
       "      <td>1</td>\n",
       "      <td>84.0</td>\n",
       "      <td>59</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     week_id  week_pos             track  \\\n",
       "0 2019-12-28         9  Jingle Bell Rock   \n",
       "1 2019-12-28        74             Death   \n",
       "2 2019-12-28        14           Ballin'   \n",
       "3 2019-12-28        77    Leave Em Alone   \n",
       "4 2019-12-28        93       Baby Sitter   \n",
       "\n",
       "                                           artist  \\\n",
       "0                                     Bobby Helms   \n",
       "1                   Trippie Redd Featuring DaBaby   \n",
       "2                   Mustard Featuring Roddy Ricch   \n",
       "3  Layton Greene, Lil Baby, City Girls & PnB Rock   \n",
       "4                         DaBaby Featuring Offset   \n",
       "\n",
       "                                            track_id  instance  prev_week_pos  \\\n",
       "0                        Jingle Bell RockBobby Helms         8           15.0   \n",
       "1                 DeathTrippie Redd Featuring DaBaby         1           64.0   \n",
       "2               Ballin'Mustard Featuring Roddy Ricch         1           18.0   \n",
       "3  Leave Em AloneLayton Greene, Lil Baby, City Gi...         1           69.0   \n",
       "4                 Baby SitterDaBaby Featuring Offset         1           84.0   \n",
       "\n",
       "   peak_pos  weeks_on_chart  \n",
       "0         9              29  \n",
       "1        59               5  \n",
       "2        14              22  \n",
       "3        60              11  \n",
       "4        59              19  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rename columns\n",
    "cleaned_billboard_data = clean_hot_100.rename(columns={'WeekID': 'week_id', 'Week Position': 'week_pos', 'Song': 'track', 'Performer': 'artist', 'SongID': 'track_id', 'Instance': 'instance', 'Previous Week Position': 'prev_week_pos', 'Peak Position': 'peak_pos', 'Weeks on Chart': 'weeks_on_chart'})\n",
    "cleaned_billboard_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_billboard_data = cleaned_billboard_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Read and store content of the excel file  \n",
    "hot_100_audio = pd.read_excel ('data/hot_100_audio_features.xlsx') \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Write the dataframe object into csv file \n",
    "hot_100_audio.to_csv ('data/hot_100_audio_features.csv', index = None, header=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>SongID</th>\n",
       "      <th>Performer</th>\n",
       "      <th>Song</th>\n",
       "      <th>spotify_genre</th>\n",
       "      <th>spotify_track_id</th>\n",
       "      <th>spotify_track_preview_url</th>\n",
       "      <th>spotify_track_album</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>...</th>\n",
       "      <th>key</th>\n",
       "      <th>loudness</th>\n",
       "      <th>mode</th>\n",
       "      <th>speechiness</th>\n",
       "      <th>acousticness</th>\n",
       "      <th>instrumentalness</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AdictoTainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>Tainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>Adicto</td>\n",
       "      <td>['pop reggaeton']</td>\n",
       "      <td>3jbT1Y5MoPwEIpZndDDwVq</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Adicto (with Anuel AA &amp; Ozuna)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>270740.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>-4.803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0735</td>\n",
       "      <td>0.017000</td>\n",
       "      <td>0.000016</td>\n",
       "      <td>0.1790</td>\n",
       "      <td>0.623</td>\n",
       "      <td>80.002</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Ones That Didn't Make It Back HomeJustin M...</td>\n",
       "      <td>Justin Moore</td>\n",
       "      <td>The Ones That Didn't Make It Back Home</td>\n",
       "      <td>['arkansas country', 'contemporary country', '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ShallowLady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>Lady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>Shallow</td>\n",
       "      <td>['dance pop', 'pop']</td>\n",
       "      <td>2VxeLyX666F8uXCJ0dZF8B</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A Star Is Born Soundtrack</td>\n",
       "      <td>0.0</td>\n",
       "      <td>215733.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-6.362</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0308</td>\n",
       "      <td>0.371000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2310</td>\n",
       "      <td>0.323</td>\n",
       "      <td>95.799</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EnemiesPost Malone Featuring DaBaby</td>\n",
       "      <td>Post Malone Featuring DaBaby</td>\n",
       "      <td>Enemies</td>\n",
       "      <td>['dfw rap', 'melodic rap', 'rap']</td>\n",
       "      <td>0Xek5rqai2jcOWCYWJfVCF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Hollywood's Bleeding</td>\n",
       "      <td>1.0</td>\n",
       "      <td>196760.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-4.169</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.2100</td>\n",
       "      <td>0.058800</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0955</td>\n",
       "      <td>0.667</td>\n",
       "      <td>76.388</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bacc At It AgainYella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>Yella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>Bacc At It Again</td>\n",
       "      <td>['dfw rap', 'rap', 'southern hip hop', 'trap']</td>\n",
       "      <td>2biNa12dMbHJrHVFRt8JyO</td>\n",
       "      <td>https://p.scdn.co/mp3-preview/fa6fa6f6f363be29...</td>\n",
       "      <td>Bacc At It Again</td>\n",
       "      <td>1.0</td>\n",
       "      <td>228185.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-5.725</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1680</td>\n",
       "      <td>0.001240</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>0.0716</td>\n",
       "      <td>0.856</td>\n",
       "      <td>135.979</td>\n",
       "      <td>4.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>28487</th>\n",
       "      <td>Tonight, TonightThe Smashing Pumpkins</td>\n",
       "      <td>The Smashing Pumpkins</td>\n",
       "      <td>Tonight, Tonight</td>\n",
       "      <td>['alternative metal', 'alternative rock', 'gru...</td>\n",
       "      <td>7bu0znpSbTks0O6I98ij0W</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Mellon Collie And The Infinite Sadness (Deluxe...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>254626.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-9.381</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0368</td>\n",
       "      <td>0.000063</td>\n",
       "      <td>0.748000</td>\n",
       "      <td>0.2370</td>\n",
       "      <td>0.222</td>\n",
       "      <td>148.928</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28488</th>\n",
       "      <td>Tonight's The Night (Gonna Be Alright)Rod Stewart</td>\n",
       "      <td>Rod Stewart</td>\n",
       "      <td>Tonight's The Night (Gonna Be Alright)</td>\n",
       "      <td>['adult standards', 'mellow gold', 'soft rock']</td>\n",
       "      <td>10LRRKPmx8uHcSxLVWK0ct</td>\n",
       "      <td>https://p.scdn.co/mp3-preview/de4772519fbe8134...</td>\n",
       "      <td>A Night on the Town (Deluxe Edition)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>237120.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>...</td>\n",
       "      <td>11.0</td>\n",
       "      <td>-12.617</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0523</td>\n",
       "      <td>0.336000</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.3820</td>\n",
       "      <td>0.724</td>\n",
       "      <td>136.542</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28489</th>\n",
       "      <td>Tonight's The NightBLACKstreet</td>\n",
       "      <td>BLACKstreet</td>\n",
       "      <td>Tonight's The Night</td>\n",
       "      <td>['boy band', 'dance pop', 'hip hop', 'hip pop'...</td>\n",
       "      <td>3scEwKiAkfdPUdZ6k1AEoW</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Blackstreet</td>\n",
       "      <td>0.0</td>\n",
       "      <td>258026.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-5.561</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0759</td>\n",
       "      <td>0.133000</td>\n",
       "      <td>0.000011</td>\n",
       "      <td>0.1730</td>\n",
       "      <td>0.591</td>\n",
       "      <td>76.060</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28490</th>\n",
       "      <td>Tonight's The NightS.S.O.</td>\n",
       "      <td>S.S.O.</td>\n",
       "      <td>Tonight's The Night</td>\n",
       "      <td>[]</td>\n",
       "      <td>5Gxa3zKb3xoS9G0y4fJnP1</td>\n",
       "      <td>https://p.scdn.co/mp3-preview/fb63477c965b182b...</td>\n",
       "      <td>Funky Chicken: Belgian Grooves From The 70's</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170506.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28491</th>\n",
       "      <td>Tonight's The NightSolomon Burke</td>\n",
       "      <td>Solomon Burke</td>\n",
       "      <td>Tonight's The Night</td>\n",
       "      <td>['brill building pop', 'classic soul', 'funk',...</td>\n",
       "      <td>18TM70njJRFH4Fm4ZuwLd8</td>\n",
       "      <td>https://p.scdn.co/mp3-preview/3bc545be6705bc2e...</td>\n",
       "      <td>The Very Best Of Solomon Burke</td>\n",
       "      <td>0.0</td>\n",
       "      <td>161186.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-9.534</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0290</td>\n",
       "      <td>0.401000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.6000</td>\n",
       "      <td>0.633</td>\n",
       "      <td>173.305</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28492 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  SongID  \\\n",
       "0                          AdictoTainy, Anuel AA & Ozuna   \n",
       "1      The Ones That Didn't Make It Back HomeJustin M...   \n",
       "2                      ShallowLady Gaga & Bradley Cooper   \n",
       "3                    EnemiesPost Malone Featuring DaBaby   \n",
       "4        Bacc At It AgainYella Beezy, Gucci Mane & Quavo   \n",
       "...                                                  ...   \n",
       "28487              Tonight, TonightThe Smashing Pumpkins   \n",
       "28488  Tonight's The Night (Gonna Be Alright)Rod Stewart   \n",
       "28489                     Tonight's The NightBLACKstreet   \n",
       "28490                          Tonight's The NightS.S.O.   \n",
       "28491                   Tonight's The NightSolomon Burke   \n",
       "\n",
       "                             Performer  \\\n",
       "0              Tainy, Anuel AA & Ozuna   \n",
       "1                         Justin Moore   \n",
       "2           Lady Gaga & Bradley Cooper   \n",
       "3         Post Malone Featuring DaBaby   \n",
       "4      Yella Beezy, Gucci Mane & Quavo   \n",
       "...                                ...   \n",
       "28487            The Smashing Pumpkins   \n",
       "28488                      Rod Stewart   \n",
       "28489                      BLACKstreet   \n",
       "28490                           S.S.O.   \n",
       "28491                    Solomon Burke   \n",
       "\n",
       "                                         Song  \\\n",
       "0                                      Adicto   \n",
       "1      The Ones That Didn't Make It Back Home   \n",
       "2                                     Shallow   \n",
       "3                                     Enemies   \n",
       "4                            Bacc At It Again   \n",
       "...                                       ...   \n",
       "28487                        Tonight, Tonight   \n",
       "28488  Tonight's The Night (Gonna Be Alright)   \n",
       "28489                     Tonight's The Night   \n",
       "28490                     Tonight's The Night   \n",
       "28491                     Tonight's The Night   \n",
       "\n",
       "                                           spotify_genre  \\\n",
       "0                                      ['pop reggaeton']   \n",
       "1      ['arkansas country', 'contemporary country', '...   \n",
       "2                                   ['dance pop', 'pop']   \n",
       "3                      ['dfw rap', 'melodic rap', 'rap']   \n",
       "4         ['dfw rap', 'rap', 'southern hip hop', 'trap']   \n",
       "...                                                  ...   \n",
       "28487  ['alternative metal', 'alternative rock', 'gru...   \n",
       "28488    ['adult standards', 'mellow gold', 'soft rock']   \n",
       "28489  ['boy band', 'dance pop', 'hip hop', 'hip pop'...   \n",
       "28490                                                 []   \n",
       "28491  ['brill building pop', 'classic soul', 'funk',...   \n",
       "\n",
       "             spotify_track_id  \\\n",
       "0      3jbT1Y5MoPwEIpZndDDwVq   \n",
       "1                         NaN   \n",
       "2      2VxeLyX666F8uXCJ0dZF8B   \n",
       "3      0Xek5rqai2jcOWCYWJfVCF   \n",
       "4      2biNa12dMbHJrHVFRt8JyO   \n",
       "...                       ...   \n",
       "28487  7bu0znpSbTks0O6I98ij0W   \n",
       "28488  10LRRKPmx8uHcSxLVWK0ct   \n",
       "28489  3scEwKiAkfdPUdZ6k1AEoW   \n",
       "28490  5Gxa3zKb3xoS9G0y4fJnP1   \n",
       "28491  18TM70njJRFH4Fm4ZuwLd8   \n",
       "\n",
       "                               spotify_track_preview_url  \\\n",
       "0                                                    NaN   \n",
       "1                                                    NaN   \n",
       "2                                                    NaN   \n",
       "3                                                    NaN   \n",
       "4      https://p.scdn.co/mp3-preview/fa6fa6f6f363be29...   \n",
       "...                                                  ...   \n",
       "28487                                                NaN   \n",
       "28488  https://p.scdn.co/mp3-preview/de4772519fbe8134...   \n",
       "28489                                                NaN   \n",
       "28490  https://p.scdn.co/mp3-preview/fb63477c965b182b...   \n",
       "28491  https://p.scdn.co/mp3-preview/3bc545be6705bc2e...   \n",
       "\n",
       "                                     spotify_track_album  \\\n",
       "0                         Adicto (with Anuel AA & Ozuna)   \n",
       "1                                                    NaN   \n",
       "2                              A Star Is Born Soundtrack   \n",
       "3                                   Hollywood's Bleeding   \n",
       "4                                       Bacc At It Again   \n",
       "...                                                  ...   \n",
       "28487  Mellon Collie And The Infinite Sadness (Deluxe...   \n",
       "28488               A Night on the Town (Deluxe Edition)   \n",
       "28489                                        Blackstreet   \n",
       "28490       Funky Chicken: Belgian Grooves From The 70's   \n",
       "28491                     The Very Best Of Solomon Burke   \n",
       "\n",
       "       spotify_track_explicit  spotify_track_duration_ms  \\\n",
       "0                         0.0                   270740.0   \n",
       "1                         NaN                        NaN   \n",
       "2                         0.0                   215733.0   \n",
       "3                         1.0                   196760.0   \n",
       "4                         1.0                   228185.0   \n",
       "...                       ...                        ...   \n",
       "28487                     0.0                   254626.0   \n",
       "28488                     0.0                   237120.0   \n",
       "28489                     0.0                   258026.0   \n",
       "28490                     0.0                   170506.0   \n",
       "28491                     0.0                   161186.0   \n",
       "\n",
       "       spotify_track_popularity  ...   key  loudness  mode  speechiness  \\\n",
       "0                          91.0  ...  10.0    -4.803   0.0       0.0735   \n",
       "1                           NaN  ...   NaN       NaN   NaN          NaN   \n",
       "2                          88.0  ...   7.0    -6.362   1.0       0.0308   \n",
       "3                          86.0  ...   6.0    -4.169   1.0       0.2100   \n",
       "4                          61.0  ...   8.0    -5.725   0.0       0.1680   \n",
       "...                         ...  ...   ...       ...   ...          ...   \n",
       "28487                      69.0  ...   6.0    -9.381   1.0       0.0368   \n",
       "28488                      55.0  ...  11.0   -12.617   1.0       0.0523   \n",
       "28489                      32.0  ...   6.0    -5.561   0.0       0.0759   \n",
       "28490                      13.0  ...   NaN       NaN   NaN          NaN   \n",
       "28491                      28.0  ...   7.0    -9.534   1.0       0.0290   \n",
       "\n",
       "       acousticness  instrumentalness  liveness  valence    tempo  \\\n",
       "0          0.017000          0.000016    0.1790    0.623   80.002   \n",
       "1               NaN               NaN       NaN      NaN      NaN   \n",
       "2          0.371000          0.000000    0.2310    0.323   95.799   \n",
       "3          0.058800          0.000000    0.0955    0.667   76.388   \n",
       "4          0.001240          0.000001    0.0716    0.856  135.979   \n",
       "...             ...               ...       ...      ...      ...   \n",
       "28487      0.000063          0.748000    0.2370    0.222  148.928   \n",
       "28488      0.336000          0.125000    0.3820    0.724  136.542   \n",
       "28489      0.133000          0.000011    0.1730    0.591   76.060   \n",
       "28490           NaN               NaN       NaN      NaN      NaN   \n",
       "28491      0.401000          0.000000    0.6000    0.633  173.305   \n",
       "\n",
       "       time_signature  \n",
       "0                 4.0  \n",
       "1                 NaN  \n",
       "2                 4.0  \n",
       "3                 4.0  \n",
       "4                 4.0  \n",
       "...               ...  \n",
       "28487             4.0  \n",
       "28488             3.0  \n",
       "28489             4.0  \n",
       "28490             NaN  \n",
       "28491             3.0  \n",
       "\n",
       "[28492 rows x 22 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Read csv file and convert into a dataframe object \n",
    "audio_features = pd.DataFrame(pd.read_csv('data/hot_100_audio_features.csv')) \n",
    "  \n",
    "# Display dataframe \n",
    "audio_features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SongID\n",
      "Performer\n",
      "Song\n",
      "spotify_genre\n",
      "spotify_track_id\n",
      "spotify_track_preview_url\n",
      "spotify_track_album\n",
      "spotify_track_explicit\n",
      "spotify_track_duration_ms\n",
      "spotify_track_popularity\n",
      "danceability\n",
      "energy\n",
      "key\n",
      "loudness\n",
      "mode\n",
      "speechiness\n",
      "acousticness\n",
      "instrumentalness\n",
      "liveness\n",
      "valence\n",
      "tempo\n",
      "time_signature\n"
     ]
    }
   ],
   "source": [
    "# Loop to print all column names for easier reading\n",
    "for col in audio_features.columns: \n",
    "    print(col)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Delete 'spotify_track_preview_url' and 'spotify_track_album' columns\n",
    "del audio_features['spotify_track_preview_url']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>SongID</th>\n",
       "      <th>Performer</th>\n",
       "      <th>Song</th>\n",
       "      <th>spotify_genre</th>\n",
       "      <th>spotify_track_id</th>\n",
       "      <th>spotify_track_album</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>danceability</th>\n",
       "      <th>...</th>\n",
       "      <th>key</th>\n",
       "      <th>loudness</th>\n",
       "      <th>mode</th>\n",
       "      <th>speechiness</th>\n",
       "      <th>acousticness</th>\n",
       "      <th>instrumentalness</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AdictoTainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>Tainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>Adicto</td>\n",
       "      <td>['pop reggaeton']</td>\n",
       "      <td>3jbT1Y5MoPwEIpZndDDwVq</td>\n",
       "      <td>Adicto (with Anuel AA &amp; Ozuna)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>270740.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>0.734</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>-4.803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0735</td>\n",
       "      <td>0.01700</td>\n",
       "      <td>0.000016</td>\n",
       "      <td>0.1790</td>\n",
       "      <td>0.623</td>\n",
       "      <td>80.002</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Ones That Didn't Make It Back HomeJustin M...</td>\n",
       "      <td>Justin Moore</td>\n",
       "      <td>The Ones That Didn't Make It Back Home</td>\n",
       "      <td>['arkansas country', 'contemporary country', '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ShallowLady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>Lady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>Shallow</td>\n",
       "      <td>['dance pop', 'pop']</td>\n",
       "      <td>2VxeLyX666F8uXCJ0dZF8B</td>\n",
       "      <td>A Star Is Born Soundtrack</td>\n",
       "      <td>0.0</td>\n",
       "      <td>215733.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>0.572</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-6.362</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0308</td>\n",
       "      <td>0.37100</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2310</td>\n",
       "      <td>0.323</td>\n",
       "      <td>95.799</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EnemiesPost Malone Featuring DaBaby</td>\n",
       "      <td>Post Malone Featuring DaBaby</td>\n",
       "      <td>Enemies</td>\n",
       "      <td>['dfw rap', 'melodic rap', 'rap']</td>\n",
       "      <td>0Xek5rqai2jcOWCYWJfVCF</td>\n",
       "      <td>Hollywood's Bleeding</td>\n",
       "      <td>1.0</td>\n",
       "      <td>196760.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>0.542</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-4.169</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.2100</td>\n",
       "      <td>0.05880</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0955</td>\n",
       "      <td>0.667</td>\n",
       "      <td>76.388</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bacc At It AgainYella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>Yella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>Bacc At It Again</td>\n",
       "      <td>['dfw rap', 'rap', 'southern hip hop', 'trap']</td>\n",
       "      <td>2biNa12dMbHJrHVFRt8JyO</td>\n",
       "      <td>Bacc At It Again</td>\n",
       "      <td>1.0</td>\n",
       "      <td>228185.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.948</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-5.725</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1680</td>\n",
       "      <td>0.00124</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>0.0716</td>\n",
       "      <td>0.856</td>\n",
       "      <td>135.979</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              SongID  \\\n",
       "0                      AdictoTainy, Anuel AA & Ozuna   \n",
       "1  The Ones That Didn't Make It Back HomeJustin M...   \n",
       "2                  ShallowLady Gaga & Bradley Cooper   \n",
       "3                EnemiesPost Malone Featuring DaBaby   \n",
       "4    Bacc At It AgainYella Beezy, Gucci Mane & Quavo   \n",
       "\n",
       "                         Performer                                    Song  \\\n",
       "0          Tainy, Anuel AA & Ozuna                                  Adicto   \n",
       "1                     Justin Moore  The Ones That Didn't Make It Back Home   \n",
       "2       Lady Gaga & Bradley Cooper                                 Shallow   \n",
       "3     Post Malone Featuring DaBaby                                 Enemies   \n",
       "4  Yella Beezy, Gucci Mane & Quavo                        Bacc At It Again   \n",
       "\n",
       "                                       spotify_genre        spotify_track_id  \\\n",
       "0                                  ['pop reggaeton']  3jbT1Y5MoPwEIpZndDDwVq   \n",
       "1  ['arkansas country', 'contemporary country', '...                     NaN   \n",
       "2                               ['dance pop', 'pop']  2VxeLyX666F8uXCJ0dZF8B   \n",
       "3                  ['dfw rap', 'melodic rap', 'rap']  0Xek5rqai2jcOWCYWJfVCF   \n",
       "4     ['dfw rap', 'rap', 'southern hip hop', 'trap']  2biNa12dMbHJrHVFRt8JyO   \n",
       "\n",
       "              spotify_track_album  spotify_track_explicit  \\\n",
       "0  Adicto (with Anuel AA & Ozuna)                     0.0   \n",
       "1                             NaN                     NaN   \n",
       "2       A Star Is Born Soundtrack                     0.0   \n",
       "3            Hollywood's Bleeding                     1.0   \n",
       "4                Bacc At It Again                     1.0   \n",
       "\n",
       "   spotify_track_duration_ms  spotify_track_popularity  danceability  ...  \\\n",
       "0                   270740.0                      91.0         0.734  ...   \n",
       "1                        NaN                       NaN           NaN  ...   \n",
       "2                   215733.0                      88.0         0.572  ...   \n",
       "3                   196760.0                      86.0         0.542  ...   \n",
       "4                   228185.0                      61.0         0.948  ...   \n",
       "\n",
       "    key  loudness  mode  speechiness  acousticness  instrumentalness  \\\n",
       "0  10.0    -4.803   0.0       0.0735       0.01700          0.000016   \n",
       "1   NaN       NaN   NaN          NaN           NaN               NaN   \n",
       "2   7.0    -6.362   1.0       0.0308       0.37100          0.000000   \n",
       "3   6.0    -4.169   1.0       0.2100       0.05880          0.000000   \n",
       "4   8.0    -5.725   0.0       0.1680       0.00124          0.000001   \n",
       "\n",
       "   liveness  valence    tempo  time_signature  \n",
       "0    0.1790    0.623   80.002             4.0  \n",
       "1       NaN      NaN      NaN             NaN  \n",
       "2    0.2310    0.323   95.799             4.0  \n",
       "3    0.0955    0.667   76.388             4.0  \n",
       "4    0.0716    0.856  135.979             4.0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "audio_features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>track_id</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>spotify_genre</th>\n",
       "      <th>spotify_track_id</th>\n",
       "      <th>spotify_track_album</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>danceability</th>\n",
       "      <th>...</th>\n",
       "      <th>key</th>\n",
       "      <th>loudness</th>\n",
       "      <th>mode</th>\n",
       "      <th>speechiness</th>\n",
       "      <th>acousticness</th>\n",
       "      <th>instrumentalness</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AdictoTainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>Tainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>Adicto</td>\n",
       "      <td>['pop reggaeton']</td>\n",
       "      <td>3jbT1Y5MoPwEIpZndDDwVq</td>\n",
       "      <td>Adicto (with Anuel AA &amp; Ozuna)</td>\n",
       "      <td>0.0</td>\n",
       "      <td>270740.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>0.734</td>\n",
       "      <td>...</td>\n",
       "      <td>10.0</td>\n",
       "      <td>-4.803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0735</td>\n",
       "      <td>0.01700</td>\n",
       "      <td>0.000016</td>\n",
       "      <td>0.1790</td>\n",
       "      <td>0.623</td>\n",
       "      <td>80.002</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Ones That Didn't Make It Back HomeJustin M...</td>\n",
       "      <td>Justin Moore</td>\n",
       "      <td>The Ones That Didn't Make It Back Home</td>\n",
       "      <td>['arkansas country', 'contemporary country', '...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ShallowLady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>Lady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>Shallow</td>\n",
       "      <td>['dance pop', 'pop']</td>\n",
       "      <td>2VxeLyX666F8uXCJ0dZF8B</td>\n",
       "      <td>A Star Is Born Soundtrack</td>\n",
       "      <td>0.0</td>\n",
       "      <td>215733.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>0.572</td>\n",
       "      <td>...</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-6.362</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0308</td>\n",
       "      <td>0.37100</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2310</td>\n",
       "      <td>0.323</td>\n",
       "      <td>95.799</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EnemiesPost Malone Featuring DaBaby</td>\n",
       "      <td>Post Malone Featuring DaBaby</td>\n",
       "      <td>Enemies</td>\n",
       "      <td>['dfw rap', 'melodic rap', 'rap']</td>\n",
       "      <td>0Xek5rqai2jcOWCYWJfVCF</td>\n",
       "      <td>Hollywood's Bleeding</td>\n",
       "      <td>1.0</td>\n",
       "      <td>196760.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>0.542</td>\n",
       "      <td>...</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-4.169</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.2100</td>\n",
       "      <td>0.05880</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0955</td>\n",
       "      <td>0.667</td>\n",
       "      <td>76.388</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bacc At It AgainYella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>Yella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>Bacc At It Again</td>\n",
       "      <td>['dfw rap', 'rap', 'southern hip hop', 'trap']</td>\n",
       "      <td>2biNa12dMbHJrHVFRt8JyO</td>\n",
       "      <td>Bacc At It Again</td>\n",
       "      <td>1.0</td>\n",
       "      <td>228185.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.948</td>\n",
       "      <td>...</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-5.725</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1680</td>\n",
       "      <td>0.00124</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>0.0716</td>\n",
       "      <td>0.856</td>\n",
       "      <td>135.979</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            track_id  \\\n",
       "0                      AdictoTainy, Anuel AA & Ozuna   \n",
       "1  The Ones That Didn't Make It Back HomeJustin M...   \n",
       "2                  ShallowLady Gaga & Bradley Cooper   \n",
       "3                EnemiesPost Malone Featuring DaBaby   \n",
       "4    Bacc At It AgainYella Beezy, Gucci Mane & Quavo   \n",
       "\n",
       "                            artist                                   track  \\\n",
       "0          Tainy, Anuel AA & Ozuna                                  Adicto   \n",
       "1                     Justin Moore  The Ones That Didn't Make It Back Home   \n",
       "2       Lady Gaga & Bradley Cooper                                 Shallow   \n",
       "3     Post Malone Featuring DaBaby                                 Enemies   \n",
       "4  Yella Beezy, Gucci Mane & Quavo                        Bacc At It Again   \n",
       "\n",
       "                                       spotify_genre        spotify_track_id  \\\n",
       "0                                  ['pop reggaeton']  3jbT1Y5MoPwEIpZndDDwVq   \n",
       "1  ['arkansas country', 'contemporary country', '...                     NaN   \n",
       "2                               ['dance pop', 'pop']  2VxeLyX666F8uXCJ0dZF8B   \n",
       "3                  ['dfw rap', 'melodic rap', 'rap']  0Xek5rqai2jcOWCYWJfVCF   \n",
       "4     ['dfw rap', 'rap', 'southern hip hop', 'trap']  2biNa12dMbHJrHVFRt8JyO   \n",
       "\n",
       "              spotify_track_album  spotify_track_explicit  \\\n",
       "0  Adicto (with Anuel AA & Ozuna)                     0.0   \n",
       "1                             NaN                     NaN   \n",
       "2       A Star Is Born Soundtrack                     0.0   \n",
       "3            Hollywood's Bleeding                     1.0   \n",
       "4                Bacc At It Again                     1.0   \n",
       "\n",
       "   spotify_track_duration_ms  spotify_track_popularity  danceability  ...  \\\n",
       "0                   270740.0                      91.0         0.734  ...   \n",
       "1                        NaN                       NaN           NaN  ...   \n",
       "2                   215733.0                      88.0         0.572  ...   \n",
       "3                   196760.0                      86.0         0.542  ...   \n",
       "4                   228185.0                      61.0         0.948  ...   \n",
       "\n",
       "    key  loudness  mode  speechiness  acousticness  instrumentalness  \\\n",
       "0  10.0    -4.803   0.0       0.0735       0.01700          0.000016   \n",
       "1   NaN       NaN   NaN          NaN           NaN               NaN   \n",
       "2   7.0    -6.362   1.0       0.0308       0.37100          0.000000   \n",
       "3   6.0    -4.169   1.0       0.2100       0.05880          0.000000   \n",
       "4   8.0    -5.725   0.0       0.1680       0.00124          0.000001   \n",
       "\n",
       "   liveness  valence    tempo  time_signature  \n",
       "0    0.1790    0.623   80.002             4.0  \n",
       "1       NaN      NaN      NaN             NaN  \n",
       "2    0.2310    0.323   95.799             4.0  \n",
       "3    0.0955    0.667   76.388             4.0  \n",
       "4    0.0716    0.856  135.979             4.0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_audio_features_data = audio_features.rename(columns={'Song': 'track', 'Performer': 'artist', 'SongID': 'track_id'})\n",
    "cleaned_audio_features_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_audio_features_data = cleaned_audio_features_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export cleaned dataframes as csvs\n",
    "cleaned_billboard_data.to_csv('data/billboard_hot_100_1958_2019.csv', index = False)\n",
    "cleaned_audio_features_data.to_csv('data/audio_features_hot_100_1958_2019.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "cleaned_data = pd.merge(cleaned_audio_features_data, cleaned_billboard_data, how='inner', on = 'track_id')\n",
    "\n",
    "del cleaned_data['track_y']\n",
    "del cleaned_data['artist_y']\n",
    "\n",
    "cleaned_data = cleaned_data.rename(columns={'track_x': 'track', 'artist_x':'artist'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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></th>\n",
       "      <th>track_id</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>spotify_genre</th>\n",
       "      <th>spotify_track_id</th>\n",
       "      <th>spotify_track_album</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>danceability</th>\n",
       "      <th>...</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "      <th>week_id</th>\n",
       "      <th>week_pos</th>\n",
       "      <th>instance</th>\n",
       "      <th>prev_week_pos</th>\n",
       "      <th>peak_pos</th>\n",
       "      <th>weeks_on_chart</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>track_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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">\"B\" GirlsYoung And Restless</th>\n",
       "      <th>140963</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>Young And Restless</td>\n",
       "      <td>\"B\" Girls</td>\n",
       "      <td>[]</td>\n",
       "      <td>0rWUHulFlScxQsCq7kWKde</td>\n",
       "      <td>Something To Get You Hyped</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>...</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1990-05-05</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140962</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>Young And Restless</td>\n",
       "      <td>\"B\" Girls</td>\n",
       "      <td>[]</td>\n",
       "      <td>0rWUHulFlScxQsCq7kWKde</td>\n",
       "      <td>Something To Get You Hyped</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>...</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1990-05-12</td>\n",
       "      <td>78</td>\n",
       "      <td>1</td>\n",
       "      <td>84.0</td>\n",
       "      <td>78</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140961</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>Young And Restless</td>\n",
       "      <td>\"B\" Girls</td>\n",
       "      <td>[]</td>\n",
       "      <td>0rWUHulFlScxQsCq7kWKde</td>\n",
       "      <td>Something To Get You Hyped</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>...</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1990-05-19</td>\n",
       "      <td>68</td>\n",
       "      <td>1</td>\n",
       "      <td>78.0</td>\n",
       "      <td>68</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140960</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>Young And Restless</td>\n",
       "      <td>\"B\" Girls</td>\n",
       "      <td>[]</td>\n",
       "      <td>0rWUHulFlScxQsCq7kWKde</td>\n",
       "      <td>Something To Get You Hyped</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>...</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1990-05-26</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>68.0</td>\n",
       "      <td>60</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140959</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>Young And Restless</td>\n",
       "      <td>\"B\" Girls</td>\n",
       "      <td>[]</td>\n",
       "      <td>0rWUHulFlScxQsCq7kWKde</td>\n",
       "      <td>Something To Get You Hyped</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>...</td>\n",
       "      <td>0.312</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1990-06-02</td>\n",
       "      <td>58</td>\n",
       "      <td>1</td>\n",
       "      <td>60.0</td>\n",
       "      <td>58</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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>www.memoryAlan Jackson</th>\n",
       "      <th>291320</th>\n",
       "      <td>www.memoryAlan Jackson</td>\n",
       "      <td>Alan Jackson</td>\n",
       "      <td>www.memory</td>\n",
       "      <td>['contemporary country', 'country', 'country r...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-12-23</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>47.0</td>\n",
       "      <td>45</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">¿Dònde Està Santa Claus? (Where Is Santa Claus?)Augie Rios</th>\n",
       "      <th>152117</th>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus...</td>\n",
       "      <td>Augie Rios</td>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus?)</td>\n",
       "      <td>['novelty']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1958-12-13</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152116</th>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus...</td>\n",
       "      <td>Augie Rios</td>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus?)</td>\n",
       "      <td>['novelty']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1958-12-20</td>\n",
       "      <td>53</td>\n",
       "      <td>1</td>\n",
       "      <td>66.0</td>\n",
       "      <td>53</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152114</th>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus...</td>\n",
       "      <td>Augie Rios</td>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus?)</td>\n",
       "      <td>['novelty']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1959-01-03</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "      <td>47.0</td>\n",
       "      <td>47</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152115</th>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus...</td>\n",
       "      <td>Augie Rios</td>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus?)</td>\n",
       "      <td>['novelty']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1958-12-27</td>\n",
       "      <td>47</td>\n",
       "      <td>1</td>\n",
       "      <td>53.0</td>\n",
       "      <td>47</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>321018 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                    track_id  \\\n",
       "track_id                                                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963                        \"B\" GirlsYoung And Restless   \n",
       "                                                   140962                        \"B\" GirlsYoung And Restless   \n",
       "                                                   140961                        \"B\" GirlsYoung And Restless   \n",
       "                                                   140960                        \"B\" GirlsYoung And Restless   \n",
       "                                                   140959                        \"B\" GirlsYoung And Restless   \n",
       "...                                                                                                      ...   \n",
       "www.memoryAlan Jackson                             291320                             www.memoryAlan Jackson   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117  ¿Dònde Està Santa Claus? (Where Is Santa Claus...   \n",
       "                                                   152116  ¿Dònde Està Santa Claus? (Where Is Santa Claus...   \n",
       "                                                   152114  ¿Dònde Està Santa Claus? (Where Is Santa Claus...   \n",
       "                                                   152115  ¿Dònde Està Santa Claus? (Where Is Santa Claus...   \n",
       "\n",
       "                                                                       artist  \\\n",
       "track_id                                                                        \n",
       "\"B\" GirlsYoung And Restless                        140963  Young And Restless   \n",
       "                                                   140962  Young And Restless   \n",
       "                                                   140961  Young And Restless   \n",
       "                                                   140960  Young And Restless   \n",
       "                                                   140959  Young And Restless   \n",
       "...                                                                       ...   \n",
       "www.memoryAlan Jackson                             291320        Alan Jackson   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117          Augie Rios   \n",
       "                                                   152116          Augie Rios   \n",
       "                                                   152114          Augie Rios   \n",
       "                                                   152115          Augie Rios   \n",
       "\n",
       "                                                                                                      track  \\\n",
       "track_id                                                                                                      \n",
       "\"B\" GirlsYoung And Restless                        140963                                         \"B\" Girls   \n",
       "                                                   140962                                         \"B\" Girls   \n",
       "                                                   140961                                         \"B\" Girls   \n",
       "                                                   140960                                         \"B\" Girls   \n",
       "                                                   140959                                         \"B\" Girls   \n",
       "...                                                                                                     ...   \n",
       "www.memoryAlan Jackson                             291320                                        www.memory   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117  ¿Dònde Està Santa Claus? (Where Is Santa Claus?)   \n",
       "                                                   152116  ¿Dònde Està Santa Claus? (Where Is Santa Claus?)   \n",
       "                                                   152114  ¿Dònde Està Santa Claus? (Where Is Santa Claus?)   \n",
       "                                                   152115  ¿Dònde Està Santa Claus? (Where Is Santa Claus?)   \n",
       "\n",
       "                                                                                               spotify_genre  \\\n",
       "track_id                                                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963                                                 []   \n",
       "                                                   140962                                                 []   \n",
       "                                                   140961                                                 []   \n",
       "                                                   140960                                                 []   \n",
       "                                                   140959                                                 []   \n",
       "...                                                                                                      ...   \n",
       "www.memoryAlan Jackson                             291320  ['contemporary country', 'country', 'country r...   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                                        ['novelty']   \n",
       "                                                   152116                                        ['novelty']   \n",
       "                                                   152114                                        ['novelty']   \n",
       "                                                   152115                                        ['novelty']   \n",
       "\n",
       "                                                                 spotify_track_id  \\\n",
       "track_id                                                                            \n",
       "\"B\" GirlsYoung And Restless                        140963  0rWUHulFlScxQsCq7kWKde   \n",
       "                                                   140962  0rWUHulFlScxQsCq7kWKde   \n",
       "                                                   140961  0rWUHulFlScxQsCq7kWKde   \n",
       "                                                   140960  0rWUHulFlScxQsCq7kWKde   \n",
       "                                                   140959  0rWUHulFlScxQsCq7kWKde   \n",
       "...                                                                           ...   \n",
       "www.memoryAlan Jackson                             291320                     NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                     NaN   \n",
       "                                                   152116                     NaN   \n",
       "                                                   152114                     NaN   \n",
       "                                                   152115                     NaN   \n",
       "\n",
       "                                                                  spotify_track_album  \\\n",
       "track_id                                                                                \n",
       "\"B\" GirlsYoung And Restless                        140963  Something To Get You Hyped   \n",
       "                                                   140962  Something To Get You Hyped   \n",
       "                                                   140961  Something To Get You Hyped   \n",
       "                                                   140960  Something To Get You Hyped   \n",
       "                                                   140959  Something To Get You Hyped   \n",
       "...                                                                               ...   \n",
       "www.memoryAlan Jackson                             291320                         NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                         NaN   \n",
       "                                                   152116                         NaN   \n",
       "                                                   152114                         NaN   \n",
       "                                                   152115                         NaN   \n",
       "\n",
       "                                                           spotify_track_explicit  \\\n",
       "track_id                                                                            \n",
       "\"B\" GirlsYoung And Restless                        140963                     0.0   \n",
       "                                                   140962                     0.0   \n",
       "                                                   140961                     0.0   \n",
       "                                                   140960                     0.0   \n",
       "                                                   140959                     0.0   \n",
       "...                                                                           ...   \n",
       "www.memoryAlan Jackson                             291320                     NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                     NaN   \n",
       "                                                   152116                     NaN   \n",
       "                                                   152114                     NaN   \n",
       "                                                   152115                     NaN   \n",
       "\n",
       "                                                           spotify_track_duration_ms  \\\n",
       "track_id                                                                               \n",
       "\"B\" GirlsYoung And Restless                        140963                   242106.0   \n",
       "                                                   140962                   242106.0   \n",
       "                                                   140961                   242106.0   \n",
       "                                                   140960                   242106.0   \n",
       "                                                   140959                   242106.0   \n",
       "...                                                                              ...   \n",
       "www.memoryAlan Jackson                             291320                        NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                        NaN   \n",
       "                                                   152116                        NaN   \n",
       "                                                   152114                        NaN   \n",
       "                                                   152115                        NaN   \n",
       "\n",
       "                                                           spotify_track_popularity  \\\n",
       "track_id                                                                              \n",
       "\"B\" GirlsYoung And Restless                        140963                      20.0   \n",
       "                                                   140962                      20.0   \n",
       "                                                   140961                      20.0   \n",
       "                                                   140960                      20.0   \n",
       "                                                   140959                      20.0   \n",
       "...                                                                             ...   \n",
       "www.memoryAlan Jackson                             291320                       NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                       NaN   \n",
       "                                                   152116                       NaN   \n",
       "                                                   152114                       NaN   \n",
       "                                                   152115                       NaN   \n",
       "\n",
       "                                                           danceability  ...  \\\n",
       "track_id                                                                 ...   \n",
       "\"B\" GirlsYoung And Restless                        140963         0.615  ...   \n",
       "                                                   140962         0.615  ...   \n",
       "                                                   140961         0.615  ...   \n",
       "                                                   140960         0.615  ...   \n",
       "                                                   140959         0.615  ...   \n",
       "...                                                                 ...  ...   \n",
       "www.memoryAlan Jackson                             291320           NaN  ...   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117           NaN  ...   \n",
       "                                                   152116           NaN  ...   \n",
       "                                                   152114           NaN  ...   \n",
       "                                                   152115           NaN  ...   \n",
       "\n",
       "                                                           liveness  valence  \\\n",
       "track_id                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963     0.312    0.769   \n",
       "                                                   140962     0.312    0.769   \n",
       "                                                   140961     0.312    0.769   \n",
       "                                                   140960     0.312    0.769   \n",
       "                                                   140959     0.312    0.769   \n",
       "...                                                             ...      ...   \n",
       "www.memoryAlan Jackson                             291320       NaN      NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117       NaN      NaN   \n",
       "                                                   152116       NaN      NaN   \n",
       "                                                   152114       NaN      NaN   \n",
       "                                                   152115       NaN      NaN   \n",
       "\n",
       "                                                             tempo  \\\n",
       "track_id                                                             \n",
       "\"B\" GirlsYoung And Restless                        140963  193.762   \n",
       "                                                   140962  193.762   \n",
       "                                                   140961  193.762   \n",
       "                                                   140960  193.762   \n",
       "                                                   140959  193.762   \n",
       "...                                                            ...   \n",
       "www.memoryAlan Jackson                             291320      NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117      NaN   \n",
       "                                                   152116      NaN   \n",
       "                                                   152114      NaN   \n",
       "                                                   152115      NaN   \n",
       "\n",
       "                                                           time_signature  \\\n",
       "track_id                                                                    \n",
       "\"B\" GirlsYoung And Restless                        140963             3.0   \n",
       "                                                   140962             3.0   \n",
       "                                                   140961             3.0   \n",
       "                                                   140960             3.0   \n",
       "                                                   140959             3.0   \n",
       "...                                                                   ...   \n",
       "www.memoryAlan Jackson                             291320             NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117             NaN   \n",
       "                                                   152116             NaN   \n",
       "                                                   152114             NaN   \n",
       "                                                   152115             NaN   \n",
       "\n",
       "                                                             week_id  \\\n",
       "track_id                                                               \n",
       "\"B\" GirlsYoung And Restless                        140963 1990-05-05   \n",
       "                                                   140962 1990-05-12   \n",
       "                                                   140961 1990-05-19   \n",
       "                                                   140960 1990-05-26   \n",
       "                                                   140959 1990-06-02   \n",
       "...                                                              ...   \n",
       "www.memoryAlan Jackson                             291320 2000-12-23   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117 1958-12-13   \n",
       "                                                   152116 1958-12-20   \n",
       "                                                   152114 1959-01-03   \n",
       "                                                   152115 1958-12-27   \n",
       "\n",
       "                                                           week_pos  instance  \\\n",
       "track_id                                                                        \n",
       "\"B\" GirlsYoung And Restless                        140963        84         1   \n",
       "                                                   140962        78         1   \n",
       "                                                   140961        68         1   \n",
       "                                                   140960        60         1   \n",
       "                                                   140959        58         1   \n",
       "...                                                             ...       ...   \n",
       "www.memoryAlan Jackson                             291320        45         1   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117        66         1   \n",
       "                                                   152116        53         1   \n",
       "                                                   152114        47         1   \n",
       "                                                   152115        47         1   \n",
       "\n",
       "                                                           prev_week_pos  \\\n",
       "track_id                                                                   \n",
       "\"B\" GirlsYoung And Restless                        140963            NaN   \n",
       "                                                   140962           84.0   \n",
       "                                                   140961           78.0   \n",
       "                                                   140960           68.0   \n",
       "                                                   140959           60.0   \n",
       "...                                                                  ...   \n",
       "www.memoryAlan Jackson                             291320           47.0   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117            NaN   \n",
       "                                                   152116           66.0   \n",
       "                                                   152114           47.0   \n",
       "                                                   152115           53.0   \n",
       "\n",
       "                                                           peak_pos  \\\n",
       "track_id                                                              \n",
       "\"B\" GirlsYoung And Restless                        140963        84   \n",
       "                                                   140962        78   \n",
       "                                                   140961        68   \n",
       "                                                   140960        60   \n",
       "                                                   140959        58   \n",
       "...                                                             ...   \n",
       "www.memoryAlan Jackson                             291320        45   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117        66   \n",
       "                                                   152116        53   \n",
       "                                                   152114        47   \n",
       "                                                   152115        47   \n",
       "\n",
       "                                                           weeks_on_chart  \n",
       "track_id                                                                   \n",
       "\"B\" GirlsYoung And Restless                        140963               1  \n",
       "                                                   140962               2  \n",
       "                                                   140961               3  \n",
       "                                                   140960               4  \n",
       "                                                   140959               5  \n",
       "...                                                                   ...  \n",
       "www.memoryAlan Jackson                             291320               8  \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117               1  \n",
       "                                                   152116               2  \n",
       "                                                   152114               4  \n",
       "                                                   152115               3  \n",
       "\n",
       "[321018 rows x 27 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data = cleaned_data.groupby(['track_id']).apply(lambda x: x.sort_values(['peak_pos'], ascending=False))\n",
    "\n",
    "grouped_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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></th>\n",
       "      <th>track_id</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>spotify_genre</th>\n",
       "      <th>spotify_track_id</th>\n",
       "      <th>spotify_track_album</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>danceability</th>\n",
       "      <th>...</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "      <th>week_id</th>\n",
       "      <th>week_pos</th>\n",
       "      <th>instance</th>\n",
       "      <th>prev_week_pos</th>\n",
       "      <th>peak_pos</th>\n",
       "      <th>weeks_on_chart</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>track_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",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>\"B\" GirlsYoung And Restless</th>\n",
       "      <th>140963</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>Young And Restless</td>\n",
       "      <td>\"B\" Girls</td>\n",
       "      <td>[]</td>\n",
       "      <td>0rWUHulFlScxQsCq7kWKde</td>\n",
       "      <td>Something To Get You Hyped</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3120</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1990-05-05</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"Cherry Cherry\" from Hot August NightNeil Diamond</th>\n",
       "      <th>179792</th>\n",
       "      <td>\"Cherry Cherry\" from Hot August NightNeil Diamond</td>\n",
       "      <td>Neil Diamond</td>\n",
       "      <td>\"Cherry Cherry\" from Hot August Night</td>\n",
       "      <td>['adult standards', 'brill building pop', 'fol...</td>\n",
       "      <td>18ocy9Be9SxWEEnOLGDfkS</td>\n",
       "      <td>Hot August Night / NYC (Live From Madison Squa...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>443320.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.340</td>\n",
       "      <td>...</td>\n",
       "      <td>0.8670</td>\n",
       "      <td>0.604</td>\n",
       "      <td>172.349</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1973-03-17</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"Having A Party\" MedleyThe Ovations (Featuring Louis Williams)</th>\n",
       "      <th>243211</th>\n",
       "      <td>\"Having A Party\" MedleyThe Ovations (Featuring...</td>\n",
       "      <td>The Ovations (Featuring Louis Williams)</td>\n",
       "      <td>\"Having A Party\" Medley</td>\n",
       "      <td>['memphis soul', 'southern soul']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1973-10-13</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"Joy\" Pt. IIsaac Hayes</th>\n",
       "      <th>156811</th>\n",
       "      <td>\"Joy\" Pt. IIsaac Hayes</td>\n",
       "      <td>Isaac Hayes</td>\n",
       "      <td>\"Joy\" Pt. I</td>\n",
       "      <td>['classic soul', 'disco', 'funk', 'jazz funk',...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1973-12-22</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"Roots\" MedleyQuincy Jones</th>\n",
       "      <th>247068</th>\n",
       "      <td>\"Roots\" MedleyQuincy Jones</td>\n",
       "      <td>Quincy Jones</td>\n",
       "      <td>\"Roots\" Medley</td>\n",
       "      <td>['adult standards', 'disco', 'funk', 'jazz fun...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1977-03-05</td>\n",
       "      <td>87</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>87</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\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>teachmeMusiq Soulchild</th>\n",
       "      <th>85179</th>\n",
       "      <td>teachmeMusiq Soulchild</td>\n",
       "      <td>Musiq Soulchild</td>\n",
       "      <td>teachme</td>\n",
       "      <td>['alternative r&amp;b', 'dance pop', 'neo soul', '...</td>\n",
       "      <td>0ODFPypnJLE6hKgahiuSzn</td>\n",
       "      <td>Luvanmusiq</td>\n",
       "      <td>0.0</td>\n",
       "      <td>260017.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>0.499</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0722</td>\n",
       "      <td>0.436</td>\n",
       "      <td>172.969</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2007-05-26</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>whoa (mind in awe)XXXTENTACION</th>\n",
       "      <th>285628</th>\n",
       "      <td>whoa (mind in awe)XXXTENTACION</td>\n",
       "      <td>XXXTENTACION</td>\n",
       "      <td>whoa (mind in awe)</td>\n",
       "      <td>['emo rap', 'miami hip hop']</td>\n",
       "      <td>7pdF27mSDuPWhppnHAmWHa</td>\n",
       "      <td>SKINS</td>\n",
       "      <td>0.0</td>\n",
       "      <td>157776.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>0.735</td>\n",
       "      <td>...</td>\n",
       "      <td>0.1010</td>\n",
       "      <td>0.369</td>\n",
       "      <td>160.147</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>96</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>whoknowsMusiq</th>\n",
       "      <th>285685</th>\n",
       "      <td>whoknowsMusiq</td>\n",
       "      <td>Musiq</td>\n",
       "      <td>whoknows</td>\n",
       "      <td>['alternative r&amp;b', 'dance pop', 'neo soul', '...</td>\n",
       "      <td>69rOfKSz0ys8UdgTkGd6qV</td>\n",
       "      <td>soulstar</td>\n",
       "      <td>0.0</td>\n",
       "      <td>295320.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.419</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3390</td>\n",
       "      <td>0.316</td>\n",
       "      <td>118.862</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2004-04-24</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>www.memoryAlan Jackson</th>\n",
       "      <th>291327</th>\n",
       "      <td>www.memoryAlan Jackson</td>\n",
       "      <td>Alan Jackson</td>\n",
       "      <td>www.memory</td>\n",
       "      <td>['contemporary country', 'country', 'country r...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000-11-04</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>¿Dònde Està Santa Claus? (Where Is Santa Claus?)Augie Rios</th>\n",
       "      <th>152117</th>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus...</td>\n",
       "      <td>Augie Rios</td>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus?)</td>\n",
       "      <td>['novelty']</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1958-12-13</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28374 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                    track_id  \\\n",
       "track_id                                                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963                        \"B\" GirlsYoung And Restless   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792  \"Cherry Cherry\" from Hot August NightNeil Diamond   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211  \"Having A Party\" MedleyThe Ovations (Featuring...   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                             \"Joy\" Pt. IIsaac Hayes   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                         \"Roots\" MedleyQuincy Jones   \n",
       "...                                                                                                      ...   \n",
       "teachmeMusiq Soulchild                             85179                              teachmeMusiq Soulchild   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                     whoa (mind in awe)XXXTENTACION   \n",
       "whoknowsMusiq                                      285685                                      whoknowsMusiq   \n",
       "www.memoryAlan Jackson                             291327                             www.memoryAlan Jackson   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117  ¿Dònde Està Santa Claus? (Where Is Santa Claus...   \n",
       "\n",
       "                                                                                            artist  \\\n",
       "track_id                                                                                             \n",
       "\"B\" GirlsYoung And Restless                        140963                       Young And Restless   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792                             Neil Diamond   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211  The Ovations (Featuring Louis Williams)   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                              Isaac Hayes   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                             Quincy Jones   \n",
       "...                                                                                            ...   \n",
       "teachmeMusiq Soulchild                             85179                           Musiq Soulchild   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                             XXXTENTACION   \n",
       "whoknowsMusiq                                      285685                                    Musiq   \n",
       "www.memoryAlan Jackson                             291327                             Alan Jackson   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                               Augie Rios   \n",
       "\n",
       "                                                                                                      track  \\\n",
       "track_id                                                                                                      \n",
       "\"B\" GirlsYoung And Restless                        140963                                         \"B\" Girls   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792             \"Cherry Cherry\" from Hot August Night   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                           \"Having A Party\" Medley   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                                       \"Joy\" Pt. I   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                                    \"Roots\" Medley   \n",
       "...                                                                                                     ...   \n",
       "teachmeMusiq Soulchild                             85179                                            teachme   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                                whoa (mind in awe)   \n",
       "whoknowsMusiq                                      285685                                          whoknows   \n",
       "www.memoryAlan Jackson                             291327                                        www.memory   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117  ¿Dònde Està Santa Claus? (Where Is Santa Claus?)   \n",
       "\n",
       "                                                                                               spotify_genre  \\\n",
       "track_id                                                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963                                                 []   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792  ['adult standards', 'brill building pop', 'fol...   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                  ['memphis soul', 'southern soul']   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811  ['classic soul', 'disco', 'funk', 'jazz funk',...   \n",
       "\"Roots\" MedleyQuincy Jones                         247068  ['adult standards', 'disco', 'funk', 'jazz fun...   \n",
       "...                                                                                                      ...   \n",
       "teachmeMusiq Soulchild                             85179   ['alternative r&b', 'dance pop', 'neo soul', '...   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                       ['emo rap', 'miami hip hop']   \n",
       "whoknowsMusiq                                      285685  ['alternative r&b', 'dance pop', 'neo soul', '...   \n",
       "www.memoryAlan Jackson                             291327  ['contemporary country', 'country', 'country r...   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                                        ['novelty']   \n",
       "\n",
       "                                                                 spotify_track_id  \\\n",
       "track_id                                                                            \n",
       "\"B\" GirlsYoung And Restless                        140963  0rWUHulFlScxQsCq7kWKde   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792  18ocy9Be9SxWEEnOLGDfkS   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                     NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                     NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                     NaN   \n",
       "...                                                                           ...   \n",
       "teachmeMusiq Soulchild                             85179   0ODFPypnJLE6hKgahiuSzn   \n",
       "whoa (mind in awe)XXXTENTACION                     285628  7pdF27mSDuPWhppnHAmWHa   \n",
       "whoknowsMusiq                                      285685  69rOfKSz0ys8UdgTkGd6qV   \n",
       "www.memoryAlan Jackson                             291327                     NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                     NaN   \n",
       "\n",
       "                                                                                         spotify_track_album  \\\n",
       "track_id                                                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963                         Something To Get You Hyped   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792  Hot August Night / NYC (Live From Madison Squa...   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                                                NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                                                NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                                                NaN   \n",
       "...                                                                                                      ...   \n",
       "teachmeMusiq Soulchild                             85179                                          Luvanmusiq   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                                              SKINS   \n",
       "whoknowsMusiq                                      285685                                           soulstar   \n",
       "www.memoryAlan Jackson                             291327                                                NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                                                NaN   \n",
       "\n",
       "                                                           spotify_track_explicit  \\\n",
       "track_id                                                                            \n",
       "\"B\" GirlsYoung And Restless                        140963                     0.0   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792                     0.0   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                     NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                     NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                     NaN   \n",
       "...                                                                           ...   \n",
       "teachmeMusiq Soulchild                             85179                      0.0   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                     0.0   \n",
       "whoknowsMusiq                                      285685                     0.0   \n",
       "www.memoryAlan Jackson                             291327                     NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                     NaN   \n",
       "\n",
       "                                                           spotify_track_duration_ms  \\\n",
       "track_id                                                                               \n",
       "\"B\" GirlsYoung And Restless                        140963                   242106.0   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792                   443320.0   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                        NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                        NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                        NaN   \n",
       "...                                                                              ...   \n",
       "teachmeMusiq Soulchild                             85179                    260017.0   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                   157776.0   \n",
       "whoknowsMusiq                                      285685                   295320.0   \n",
       "www.memoryAlan Jackson                             291327                        NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                        NaN   \n",
       "\n",
       "                                                           spotify_track_popularity  \\\n",
       "track_id                                                                              \n",
       "\"B\" GirlsYoung And Restless                        140963                      20.0   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792                      16.0   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211                       NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811                       NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068                       NaN   \n",
       "...                                                                             ...   \n",
       "teachmeMusiq Soulchild                             85179                       57.0   \n",
       "whoa (mind in awe)XXXTENTACION                     285628                      77.0   \n",
       "whoknowsMusiq                                      285685                      39.0   \n",
       "www.memoryAlan Jackson                             291327                       NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117                       NaN   \n",
       "\n",
       "                                                           danceability  ...  \\\n",
       "track_id                                                                 ...   \n",
       "\"B\" GirlsYoung And Restless                        140963         0.615  ...   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792         0.340  ...   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211           NaN  ...   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811           NaN  ...   \n",
       "\"Roots\" MedleyQuincy Jones                         247068           NaN  ...   \n",
       "...                                                                 ...  ...   \n",
       "teachmeMusiq Soulchild                             85179          0.499  ...   \n",
       "whoa (mind in awe)XXXTENTACION                     285628         0.735  ...   \n",
       "whoknowsMusiq                                      285685         0.419  ...   \n",
       "www.memoryAlan Jackson                             291327           NaN  ...   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117           NaN  ...   \n",
       "\n",
       "                                                           liveness  valence  \\\n",
       "track_id                                                                       \n",
       "\"B\" GirlsYoung And Restless                        140963    0.3120    0.769   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792    0.8670    0.604   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211       NaN      NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811       NaN      NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068       NaN      NaN   \n",
       "...                                                             ...      ...   \n",
       "teachmeMusiq Soulchild                             85179     0.0722    0.436   \n",
       "whoa (mind in awe)XXXTENTACION                     285628    0.1010    0.369   \n",
       "whoknowsMusiq                                      285685    0.3390    0.316   \n",
       "www.memoryAlan Jackson                             291327       NaN      NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117       NaN      NaN   \n",
       "\n",
       "                                                             tempo  \\\n",
       "track_id                                                             \n",
       "\"B\" GirlsYoung And Restless                        140963  193.762   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792  172.349   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211      NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811      NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068      NaN   \n",
       "...                                                            ...   \n",
       "teachmeMusiq Soulchild                             85179   172.969   \n",
       "whoa (mind in awe)XXXTENTACION                     285628  160.147   \n",
       "whoknowsMusiq                                      285685  118.862   \n",
       "www.memoryAlan Jackson                             291327      NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117      NaN   \n",
       "\n",
       "                                                           time_signature  \\\n",
       "track_id                                                                    \n",
       "\"B\" GirlsYoung And Restless                        140963             3.0   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792             4.0   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211             NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811             NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068             NaN   \n",
       "...                                                                   ...   \n",
       "teachmeMusiq Soulchild                             85179              3.0   \n",
       "whoa (mind in awe)XXXTENTACION                     285628             4.0   \n",
       "whoknowsMusiq                                      285685             4.0   \n",
       "www.memoryAlan Jackson                             291327             NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117             NaN   \n",
       "\n",
       "                                                             week_id  \\\n",
       "track_id                                                               \n",
       "\"B\" GirlsYoung And Restless                        140963 1990-05-05   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792 1973-03-17   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211 1973-10-13   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811 1973-12-22   \n",
       "\"Roots\" MedleyQuincy Jones                         247068 1977-03-05   \n",
       "...                                                              ...   \n",
       "teachmeMusiq Soulchild                             85179  2007-05-26   \n",
       "whoa (mind in awe)XXXTENTACION                     285628 2019-01-12   \n",
       "whoknowsMusiq                                      285685 2004-04-24   \n",
       "www.memoryAlan Jackson                             291327 2000-11-04   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117 1958-12-13   \n",
       "\n",
       "                                                           week_pos  instance  \\\n",
       "track_id                                                                        \n",
       "\"B\" GirlsYoung And Restless                        140963        84         1   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792        84         1   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211        99         1   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811        72         1   \n",
       "\"Roots\" MedleyQuincy Jones                         247068        87         1   \n",
       "...                                                             ...       ...   \n",
       "teachmeMusiq Soulchild                             85179         92         1   \n",
       "whoa (mind in awe)XXXTENTACION                     285628        96         2   \n",
       "whoknowsMusiq                                      285685        75         1   \n",
       "www.memoryAlan Jackson                             291327        75         1   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117        66         1   \n",
       "\n",
       "                                                           prev_week_pos  \\\n",
       "track_id                                                                   \n",
       "\"B\" GirlsYoung And Restless                        140963            NaN   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792            NaN   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211            NaN   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811            NaN   \n",
       "\"Roots\" MedleyQuincy Jones                         247068            NaN   \n",
       "...                                                                  ...   \n",
       "teachmeMusiq Soulchild                             85179             NaN   \n",
       "whoa (mind in awe)XXXTENTACION                     285628            NaN   \n",
       "whoknowsMusiq                                      285685            NaN   \n",
       "www.memoryAlan Jackson                             291327            NaN   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117            NaN   \n",
       "\n",
       "                                                           peak_pos  \\\n",
       "track_id                                                              \n",
       "\"B\" GirlsYoung And Restless                        140963        84   \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792        84   \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211        99   \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811        72   \n",
       "\"Roots\" MedleyQuincy Jones                         247068        87   \n",
       "...                                                             ...   \n",
       "teachmeMusiq Soulchild                             85179         92   \n",
       "whoa (mind in awe)XXXTENTACION                     285628        96   \n",
       "whoknowsMusiq                                      285685        75   \n",
       "www.memoryAlan Jackson                             291327        75   \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117        66   \n",
       "\n",
       "                                                           weeks_on_chart  \n",
       "track_id                                                                   \n",
       "\"B\" GirlsYoung And Restless                        140963               1  \n",
       "\"Cherry Cherry\" from Hot August NightNeil Diamond  179792               1  \n",
       "\"Having A Party\" MedleyThe Ovations (Featuring ... 243211               1  \n",
       "\"Joy\" Pt. IIsaac Hayes                             156811               1  \n",
       "\"Roots\" MedleyQuincy Jones                         247068               1  \n",
       "...                                                                   ...  \n",
       "teachmeMusiq Soulchild                             85179                1  \n",
       "whoa (mind in awe)XXXTENTACION                     285628               3  \n",
       "whoknowsMusiq                                      285685               1  \n",
       "www.memoryAlan Jackson                             291327               1  \n",
       "¿Dònde Està Santa Claus? (Where Is Santa Claus?... 152117               1  \n",
       "\n",
       "[28374 rows x 27 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data.drop_duplicates(subset=['track_id'], keep='first', inplace=True)\n",
    "grouped_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data.drop(['track_id','artist','track','spotify_genre','spotify_track_id','spotify_track_album','week_id', 'prev_week_pos'], axis=1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['spotify_track_explicit', 'spotify_track_duration_ms',\n",
       "       'spotify_track_popularity', 'danceability', 'energy', 'key', 'loudness',\n",
       "       'mode', 'speechiness', 'acousticness', 'instrumentalness', 'liveness',\n",
       "       'valence', 'tempo', 'time_signature', 'week_pos', 'instance',\n",
       "       'peak_pos', 'weeks_on_chart'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data = grouped_data.fillna(0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "grouped_data.drop(['level_1'], axis=1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>track_id</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>danceability</th>\n",
       "      <th>energy</th>\n",
       "      <th>key</th>\n",
       "      <th>loudness</th>\n",
       "      <th>mode</th>\n",
       "      <th>speechiness</th>\n",
       "      <th>acousticness</th>\n",
       "      <th>instrumentalness</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "      <th>week_pos</th>\n",
       "      <th>instance</th>\n",
       "      <th>peak_pos</th>\n",
       "      <th>weeks_on_chart</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>\"B\" GirlsYoung And Restless</td>\n",
       "      <td>0.0</td>\n",
       "      <td>242106.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.615</td>\n",
       "      <td>0.497</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-11.913</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.4390</td>\n",
       "      <td>0.0160</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.3120</td>\n",
       "      <td>0.769</td>\n",
       "      <td>193.762</td>\n",
       "      <td>3.0</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>\"Cherry Cherry\" from Hot August NightNeil Diamond</td>\n",
       "      <td>0.0</td>\n",
       "      <td>443320.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.340</td>\n",
       "      <td>0.948</td>\n",
       "      <td>9.0</td>\n",
       "      <td>-5.061</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.1370</td>\n",
       "      <td>0.0941</td>\n",
       "      <td>0.000907</td>\n",
       "      <td>0.8670</td>\n",
       "      <td>0.604</td>\n",
       "      <td>172.349</td>\n",
       "      <td>4.0</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "      <td>84</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>\"Having A Party\" MedleyThe Ovations (Featuring...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>\"Joy\" Pt. IIsaac Hayes</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "      <td>72</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>\"Roots\" MedleyQuincy Jones</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>87</td>\n",
       "      <td>1</td>\n",
       "      <td>87</td>\n",
       "      <td>1</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28369</th>\n",
       "      <td>teachmeMusiq Soulchild</td>\n",
       "      <td>0.0</td>\n",
       "      <td>260017.0</td>\n",
       "      <td>57.0</td>\n",
       "      <td>0.499</td>\n",
       "      <td>0.610</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-5.110</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1350</td>\n",
       "      <td>0.4730</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0722</td>\n",
       "      <td>0.436</td>\n",
       "      <td>172.969</td>\n",
       "      <td>3.0</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "      <td>92</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28370</th>\n",
       "      <td>whoa (mind in awe)XXXTENTACION</td>\n",
       "      <td>0.0</td>\n",
       "      <td>157776.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>0.735</td>\n",
       "      <td>0.525</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-2.939</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0441</td>\n",
       "      <td>0.6530</td>\n",
       "      <td>0.918000</td>\n",
       "      <td>0.1010</td>\n",
       "      <td>0.369</td>\n",
       "      <td>160.147</td>\n",
       "      <td>4.0</td>\n",
       "      <td>96</td>\n",
       "      <td>2</td>\n",
       "      <td>96</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28371</th>\n",
       "      <td>whoknowsMusiq</td>\n",
       "      <td>0.0</td>\n",
       "      <td>295320.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>0.419</td>\n",
       "      <td>0.694</td>\n",
       "      <td>2.0</td>\n",
       "      <td>-6.144</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.1220</td>\n",
       "      <td>0.2300</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.3390</td>\n",
       "      <td>0.316</td>\n",
       "      <td>118.862</td>\n",
       "      <td>4.0</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28372</th>\n",
       "      <td>www.memoryAlan Jackson</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "      <td>75</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28373</th>\n",
       "      <td>¿Dònde Està Santa Claus? (Where Is Santa Claus...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "      <td>66</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28374 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                track_id  \\\n",
       "0                            \"B\" GirlsYoung And Restless   \n",
       "1      \"Cherry Cherry\" from Hot August NightNeil Diamond   \n",
       "2      \"Having A Party\" MedleyThe Ovations (Featuring...   \n",
       "3                                 \"Joy\" Pt. IIsaac Hayes   \n",
       "4                             \"Roots\" MedleyQuincy Jones   \n",
       "...                                                  ...   \n",
       "28369                             teachmeMusiq Soulchild   \n",
       "28370                     whoa (mind in awe)XXXTENTACION   \n",
       "28371                                      whoknowsMusiq   \n",
       "28372                             www.memoryAlan Jackson   \n",
       "28373  ¿Dònde Està Santa Claus? (Where Is Santa Claus...   \n",
       "\n",
       "       spotify_track_explicit  spotify_track_duration_ms  \\\n",
       "0                         0.0                   242106.0   \n",
       "1                         0.0                   443320.0   \n",
       "2                         0.0                        0.0   \n",
       "3                         0.0                        0.0   \n",
       "4                         0.0                        0.0   \n",
       "...                       ...                        ...   \n",
       "28369                     0.0                   260017.0   \n",
       "28370                     0.0                   157776.0   \n",
       "28371                     0.0                   295320.0   \n",
       "28372                     0.0                        0.0   \n",
       "28373                     0.0                        0.0   \n",
       "\n",
       "       spotify_track_popularity  danceability  energy  key  loudness  mode  \\\n",
       "0                          20.0         0.615   0.497  7.0   -11.913   1.0   \n",
       "1                          16.0         0.340   0.948  9.0    -5.061   1.0   \n",
       "2                           0.0         0.000   0.000  0.0     0.000   0.0   \n",
       "3                           0.0         0.000   0.000  0.0     0.000   0.0   \n",
       "4                           0.0         0.000   0.000  0.0     0.000   0.0   \n",
       "...                         ...           ...     ...  ...       ...   ...   \n",
       "28369                      57.0         0.499   0.610  8.0    -5.110   0.0   \n",
       "28370                      77.0         0.735   0.525  6.0    -2.939   0.0   \n",
       "28371                      39.0         0.419   0.694  2.0    -6.144   1.0   \n",
       "28372                       0.0         0.000   0.000  0.0     0.000   0.0   \n",
       "28373                       0.0         0.000   0.000  0.0     0.000   0.0   \n",
       "\n",
       "       speechiness  acousticness  instrumentalness  liveness  valence  \\\n",
       "0           0.4390        0.0160          0.000000    0.3120    0.769   \n",
       "1           0.1370        0.0941          0.000907    0.8670    0.604   \n",
       "2           0.0000        0.0000          0.000000    0.0000    0.000   \n",
       "3           0.0000        0.0000          0.000000    0.0000    0.000   \n",
       "4           0.0000        0.0000          0.000000    0.0000    0.000   \n",
       "...            ...           ...               ...       ...      ...   \n",
       "28369       0.1350        0.4730          0.000000    0.0722    0.436   \n",
       "28370       0.0441        0.6530          0.918000    0.1010    0.369   \n",
       "28371       0.1220        0.2300          0.000000    0.3390    0.316   \n",
       "28372       0.0000        0.0000          0.000000    0.0000    0.000   \n",
       "28373       0.0000        0.0000          0.000000    0.0000    0.000   \n",
       "\n",
       "         tempo  time_signature  week_pos  instance  peak_pos  weeks_on_chart  \n",
       "0      193.762             3.0        84         1        84               1  \n",
       "1      172.349             4.0        84         1        84               1  \n",
       "2        0.000             0.0        99         1        99               1  \n",
       "3        0.000             0.0        72         1        72               1  \n",
       "4        0.000             0.0        87         1        87               1  \n",
       "...        ...             ...       ...       ...       ...             ...  \n",
       "28369  172.969             3.0        92         1        92               1  \n",
       "28370  160.147             4.0        96         2        96               3  \n",
       "28371  118.862             4.0        75         1        75               1  \n",
       "28372    0.000             0.0        75         1        75               1  \n",
       "28373    0.000             0.0        66         1        66               1  \n",
       "\n",
       "[28374 rows x 20 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>track_id</th>\n",
       "      <th>genre</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AdictoTainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Ones That Didn't Make It Back HomeJustin M...</td>\n",
       "      <td>country</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>ShallowLady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>EnemiesPost Malone Featuring DaBaby</td>\n",
       "      <td>rap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Bacc At It AgainYella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>rap</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130809</th>\n",
       "      <td>Tonight, TonightThe Smashing Pumpkins</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130817</th>\n",
       "      <td>Tonight's The Night (Gonna Be Alright)Rod Stewart</td>\n",
       "      <td>rock</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130820</th>\n",
       "      <td>Tonight's The NightBLACKstreet</td>\n",
       "      <td>pop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130831</th>\n",
       "      <td>Tonight's The NightS.S.O.</td>\n",
       "      <td>other</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130832</th>\n",
       "      <td>Tonight's The NightSolomon Burke</td>\n",
       "      <td>soul</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28378 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 track_id    genre\n",
       "0                           AdictoTainy, Anuel AA & Ozuna      pop\n",
       "1       The Ones That Didn't Make It Back HomeJustin M...  country\n",
       "7                       ShallowLady Gaga & Bradley Cooper      pop\n",
       "9                     EnemiesPost Malone Featuring DaBaby      rap\n",
       "12        Bacc At It AgainYella Beezy, Gucci Mane & Quavo      rap\n",
       "...                                                   ...      ...\n",
       "130809              Tonight, TonightThe Smashing Pumpkins     rock\n",
       "130817  Tonight's The Night (Gonna Be Alright)Rod Stewart     rock\n",
       "130820                     Tonight's The NightBLACKstreet      pop\n",
       "130831                          Tonight's The NightS.S.O.    other\n",
       "130832                   Tonight's The NightSolomon Burke     soul\n",
       "\n",
       "[28378 rows x 2 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre = pd.read_csv('data/songGenre.csv')[['songid','supergenre']].rename(columns = {'songid':'track_id','supergenre':'genre'})\n",
    "genre = genre.drop_duplicates()\n",
    "genre"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>track_id</th>\n",
       "      <th>genre</th>\n",
       "      <th>spotify_track_explicit</th>\n",
       "      <th>spotify_track_duration_ms</th>\n",
       "      <th>spotify_track_popularity</th>\n",
       "      <th>danceability</th>\n",
       "      <th>energy</th>\n",
       "      <th>key</th>\n",
       "      <th>loudness</th>\n",
       "      <th>mode</th>\n",
       "      <th>...</th>\n",
       "      <th>acousticness</th>\n",
       "      <th>instrumentalness</th>\n",
       "      <th>liveness</th>\n",
       "      <th>valence</th>\n",
       "      <th>tempo</th>\n",
       "      <th>time_signature</th>\n",
       "      <th>week_pos</th>\n",
       "      <th>instance</th>\n",
       "      <th>peak_pos</th>\n",
       "      <th>weeks_on_chart</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AdictoTainy, Anuel AA &amp; Ozuna</td>\n",
       "      <td>pop</td>\n",
       "      <td>0.0</td>\n",
       "      <td>270740.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>0.734</td>\n",
       "      <td>0.836</td>\n",
       "      <td>10.0</td>\n",
       "      <td>-4.803</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.017000</td>\n",
       "      <td>0.000016</td>\n",
       "      <td>0.1790</td>\n",
       "      <td>0.623</td>\n",
       "      <td>80.002</td>\n",
       "      <td>4.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>The Ones That Didn't Make It Back HomeJustin M...</td>\n",
       "      <td>country</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ShallowLady Gaga &amp; Bradley Cooper</td>\n",
       "      <td>pop</td>\n",
       "      <td>0.0</td>\n",
       "      <td>215733.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>0.572</td>\n",
       "      <td>0.385</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-6.362</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.371000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.2310</td>\n",
       "      <td>0.323</td>\n",
       "      <td>95.799</td>\n",
       "      <td>4.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EnemiesPost Malone Featuring DaBaby</td>\n",
       "      <td>rap</td>\n",
       "      <td>1.0</td>\n",
       "      <td>196760.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>0.542</td>\n",
       "      <td>0.674</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-4.169</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.058800</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0955</td>\n",
       "      <td>0.667</td>\n",
       "      <td>76.388</td>\n",
       "      <td>4.0</td>\n",
       "      <td>98.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bacc At It AgainYella Beezy, Gucci Mane &amp; Quavo</td>\n",
       "      <td>rap</td>\n",
       "      <td>1.0</td>\n",
       "      <td>228185.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>0.948</td>\n",
       "      <td>0.623</td>\n",
       "      <td>8.0</td>\n",
       "      <td>-5.725</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.001240</td>\n",
       "      <td>0.000001</td>\n",
       "      <td>0.0716</td>\n",
       "      <td>0.856</td>\n",
       "      <td>135.979</td>\n",
       "      <td>4.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>94.0</td>\n",
       "      <td>3.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>28373</th>\n",
       "      <td>Tonight, TonightThe Smashing Pumpkins</td>\n",
       "      <td>rock</td>\n",
       "      <td>0.0</td>\n",
       "      <td>254626.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>0.287</td>\n",
       "      <td>0.670</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-9.381</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000063</td>\n",
       "      <td>0.748000</td>\n",
       "      <td>0.2370</td>\n",
       "      <td>0.222</td>\n",
       "      <td>148.928</td>\n",
       "      <td>4.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28374</th>\n",
       "      <td>Tonight's The Night (Gonna Be Alright)Rod Stewart</td>\n",
       "      <td>rock</td>\n",
       "      <td>0.0</td>\n",
       "      <td>237120.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>0.550</td>\n",
       "      <td>0.514</td>\n",
       "      <td>11.0</td>\n",
       "      <td>-12.617</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.336000</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.3820</td>\n",
       "      <td>0.724</td>\n",
       "      <td>136.542</td>\n",
       "      <td>3.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28375</th>\n",
       "      <td>Tonight's The NightBLACKstreet</td>\n",
       "      <td>pop</td>\n",
       "      <td>0.0</td>\n",
       "      <td>258026.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0.585</td>\n",
       "      <td>0.573</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-5.561</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.133000</td>\n",
       "      <td>0.000011</td>\n",
       "      <td>0.1730</td>\n",
       "      <td>0.591</td>\n",
       "      <td>76.060</td>\n",
       "      <td>4.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28376</th>\n",
       "      <td>Tonight's The NightS.S.O.</td>\n",
       "      <td>other</td>\n",
       "      <td>0.0</td>\n",
       "      <td>170506.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28377</th>\n",
       "      <td>Tonight's The NightSolomon Burke</td>\n",
       "      <td>soul</td>\n",
       "      <td>0.0</td>\n",
       "      <td>161186.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.318</td>\n",
       "      <td>0.476</td>\n",
       "      <td>7.0</td>\n",
       "      <td>-9.534</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.401000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.6000</td>\n",
       "      <td>0.633</td>\n",
       "      <td>173.305</td>\n",
       "      <td>3.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>28375 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                track_id    genre  \\\n",
       "0                          AdictoTainy, Anuel AA & Ozuna      pop   \n",
       "1      The Ones That Didn't Make It Back HomeJustin M...  country   \n",
       "2                      ShallowLady Gaga & Bradley Cooper      pop   \n",
       "3                    EnemiesPost Malone Featuring DaBaby      rap   \n",
       "4        Bacc At It AgainYella Beezy, Gucci Mane & Quavo      rap   \n",
       "...                                                  ...      ...   \n",
       "28373              Tonight, TonightThe Smashing Pumpkins     rock   \n",
       "28374  Tonight's The Night (Gonna Be Alright)Rod Stewart     rock   \n",
       "28375                     Tonight's The NightBLACKstreet      pop   \n",
       "28376                          Tonight's The NightS.S.O.    other   \n",
       "28377                   Tonight's The NightSolomon Burke     soul   \n",
       "\n",
       "       spotify_track_explicit  spotify_track_duration_ms  \\\n",
       "0                         0.0                   270740.0   \n",
       "1                         0.0                        0.0   \n",
       "2                         0.0                   215733.0   \n",
       "3                         1.0                   196760.0   \n",
       "4                         1.0                   228185.0   \n",
       "...                       ...                        ...   \n",
       "28373                     0.0                   254626.0   \n",
       "28374                     0.0                   237120.0   \n",
       "28375                     0.0                   258026.0   \n",
       "28376                     0.0                   170506.0   \n",
       "28377                     0.0                   161186.0   \n",
       "\n",
       "       spotify_track_popularity  danceability  energy   key  loudness  mode  \\\n",
       "0                          91.0         0.734   0.836  10.0    -4.803   0.0   \n",
       "1                           0.0         0.000   0.000   0.0     0.000   0.0   \n",
       "2                          89.0         0.572   0.385   7.0    -6.362   1.0   \n",
       "3                          86.0         0.542   0.674   6.0    -4.169   1.0   \n",
       "4                          61.0         0.948   0.623   8.0    -5.725   0.0   \n",
       "...                         ...           ...     ...   ...       ...   ...   \n",
       "28373                      69.0         0.287   0.670   6.0    -9.381   1.0   \n",
       "28374                      55.0         0.550   0.514  11.0   -12.617   1.0   \n",
       "28375                      32.0         0.585   0.573   6.0    -5.561   0.0   \n",
       "28376                      13.0         0.000   0.000   0.0     0.000   0.0   \n",
       "28377                      28.0         0.318   0.476   7.0    -9.534   1.0   \n",
       "\n",
       "       ...  acousticness  instrumentalness  liveness  valence    tempo  \\\n",
       "0      ...      0.017000          0.000016    0.1790    0.623   80.002   \n",
       "1      ...      0.000000          0.000000    0.0000    0.000    0.000   \n",
       "2      ...      0.371000          0.000000    0.2310    0.323   95.799   \n",
       "3      ...      0.058800          0.000000    0.0955    0.667   76.388   \n",
       "4      ...      0.001240          0.000001    0.0716    0.856  135.979   \n",
       "...    ...           ...               ...       ...      ...      ...   \n",
       "28373  ...      0.000063          0.748000    0.2370    0.222  148.928   \n",
       "28374  ...      0.336000          0.125000    0.3820    0.724  136.542   \n",
       "28375  ...      0.133000          0.000011    0.1730    0.591   76.060   \n",
       "28376  ...      0.000000          0.000000    0.0000    0.000    0.000   \n",
       "28377  ...      0.401000          0.000000    0.6000    0.633  173.305   \n",
       "\n",
       "       time_signature  week_pos  instance  peak_pos  weeks_on_chart  \n",
       "0                 4.0      98.0       1.0      86.0             2.0  \n",
       "1                 0.0     100.0       1.0     100.0             1.0  \n",
       "2                 4.0      28.0       1.0      28.0             1.0  \n",
       "3                 4.0      98.0       1.0      16.0            15.0  \n",
       "4                 4.0      94.0       1.0      94.0             3.0  \n",
       "...               ...       ...       ...       ...             ...  \n",
       "28373             4.0      51.0       1.0      51.0             1.0  \n",
       "28374             3.0      81.0       1.0      81.0             1.0  \n",
       "28375             4.0      95.0       2.0      95.0             2.0  \n",
       "28376             0.0     100.0       1.0     100.0             2.0  \n",
       "28377             3.0      80.0       1.0      80.0             1.0  \n",
       "\n",
       "[28375 rows x 21 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "join_df = genre.merge(grouped_data, on = 'track_id', how = 'left').dropna(how='any')\n",
    "join_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export cleaned dataframes as csv\n",
    "join_df.to_csv('data/grouped_audio_billboard_genre_data.csv', index = False)"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}