MyDataETL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "55ebb6ed-dd88-4d17-9978-611afd08227d",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Data Extraction"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac573c52-e6c5-4f7b-8a3c-04b987f708ec",
   "metadata": {},
   "source": [
    "What files were provided to me:\n",
    "- Follows\n",
    "- Identifiers\n",
    "- Identity\n",
    "- Inferences\n",
    "- Payments\n",
    "- Playlist1\n",
    "- Search queries\n",
    "- Streaming hstory\n",
    "- My user data\n",
    "- My library\n",
    "\n",
    "\n",
    " These records refers to my whole activity on the during one year - which means that I've data from October 2020 here."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e3db7bb-e498-49e9-9728-163088429667",
   "metadata": {},
   "source": [
    "What I would like to know about myself - musically speaking:\n",
    "\n",
    "- The top 100 most played tracks from the past year (October 2020 - 2021)\n",
    "- The top 5 genres most played from the past year (October 2020 - 2021)\n",
    "- How many hours I spend listening the tracks\n",
    "- The top 5 most played artits from the past year\n",
    "\n",
    "\n",
    "Basically trying to do a part of the spotify retrospective some weeks before they will"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4edc821f-10cb-44e0-a12a-2df5257f0847",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "# Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "95707faf-7a18-47e4-847e-99954910c7fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import spotipy\n",
    "import json\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4ca63a95-ba65-4fb0-b795-ec23b3cbce19",
   "metadata": {},
   "outputs": [],
   "source": [
    "base_path = r'C:\\Users\\Ale\\Desktop\\Data\\MyData\\\\'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e8ada52-a216-498f-a087-6066265eeb07",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "# Inferences"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "45d8e982-9811-43de-a783-73d3d14d767b",
   "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>inferences</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1P_Custom_At_Home_Hub_Listeners</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1P_Custom_Cultural_Affinity_WHM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1P_Custom_Discovery_Streamers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1P_Custom_Disney_Marvel_Playlist_Streamers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1P_Custom_High_Intent_Streamers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1P_Custom_Meditation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1P_Custom_PopCulture_Hub_Streamers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1P_Custom_Relaxation</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1P_Custom_SquareSpace_showListeners_Package1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1P_Custom_SquareSpace_showListeners_Package3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     inferences\n",
       "0               1P_Custom_At_Home_Hub_Listeners\n",
       "1               1P_Custom_Cultural_Affinity_WHM\n",
       "2                 1P_Custom_Discovery_Streamers\n",
       "3    1P_Custom_Disney_Marvel_Playlist_Streamers\n",
       "4               1P_Custom_High_Intent_Streamers\n",
       "5                          1P_Custom_Meditation\n",
       "6            1P_Custom_PopCulture_Hub_Streamers\n",
       "7                          1P_Custom_Relaxation\n",
       "8  1P_Custom_SquareSpace_showListeners_Package1\n",
       "9  1P_Custom_SquareSpace_showListeners_Package3"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inferences = pd.read_json(base_path+'Inferences.json')\n",
    "inferences.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d119e9ee-094a-4a5e-b0bc-995b5fcc9a38",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 33 entries, 0 to 32\n",
      "Data columns (total 1 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   inferences  33 non-null     object\n",
      "dtypes: object(1)\n",
      "memory usage: 392.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "inferences.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7302398f-113e-431a-b691-24743d909e6c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "34e3f0d3-437b-45e0-9bf2-eb497416e348",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "# Playlists"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5adc5178-cf14-4df0-9ec6-14b5e3f1268f",
   "metadata": {},
   "source": [
    "The playlist file is as clean as a whistle "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c129326a-96fc-4d45-894a-df2110328f66",
   "metadata": {},
   "outputs": [],
   "source": [
    "playlist = pd.read_json(base_path+'Playlist1.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "2e162e42-6a5a-4a43-854e-79fe43b3164b",
   "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>playlists</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>{'name': 'playlist oficial da corridinha das 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>{'name': 'rnb', 'lastModifiedDate': '2021-09-3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>{'name': 'motorolav3rosa.mp3', 'lastModifiedDa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>{'name': 'mariah's hardflip 🛹', 'lastModifiedD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>{'name': 'Paul Kalkbrenner @ EXIT mts Dance Ar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>{'name': 'this is fritasseira --hard', 'lastMo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>{'name': 'Paul Kalkbrenner @ UNTOLD 2019', 'la...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>{'name': 'Adriatique @ Core Stage, Tomorrowlan...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>{'name': 'ELF 2019 Opening Ceremony', 'lastMod...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>{'name': 'lowkey techno | melodic house', 'las...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>{'name': 'techno | house ♀️', 'lastModifiedDat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>{'name': 'rnb | soul ♀️', 'lastModifiedDate': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>{'name': 'sad songs 2 me', 'lastModifiedDate':...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>{'name': 'this is alevibes -- fritasseira low'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>{'name': 'rap | hip hop', 'lastModifiedDate': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>{'name': 'fall in love w/ your solitude', 'las...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>{'name': 'lost frequencies [current listening]...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>{'name': 'rosalía y reggaeton', 'lastModifiedD...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>{'name': 'pop EDM', 'lastModifiedDate': '2021-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>{'name': 'Tomorrowland Vibes', 'lastModifiedDa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>{'name': 'EDM vibes', 'lastModifiedDate': '202...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>{'name': 'popes', 'lastModifiedDate': '2021-04...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>{'name': 'brasilidades', 'lastModifiedDate': '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>{'name': 'mes mots ne sont pas très beaux', 'l...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>{'name': 'baile da terceira idade', 'lastModif...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            playlists\n",
       "0   {'name': 'playlist oficial da corridinha das 1...\n",
       "1   {'name': 'rnb', 'lastModifiedDate': '2021-09-3...\n",
       "2   {'name': 'motorolav3rosa.mp3', 'lastModifiedDa...\n",
       "3   {'name': 'mariah's hardflip 🛹', 'lastModifiedD...\n",
       "4   {'name': 'Paul Kalkbrenner @ EXIT mts Dance Ar...\n",
       "5   {'name': 'this is fritasseira --hard', 'lastMo...\n",
       "6   {'name': 'Paul Kalkbrenner @ UNTOLD 2019', 'la...\n",
       "7   {'name': 'Adriatique @ Core Stage, Tomorrowlan...\n",
       "8   {'name': 'ELF 2019 Opening Ceremony', 'lastMod...\n",
       "9   {'name': 'lowkey techno | melodic house', 'las...\n",
       "10  {'name': 'techno | house ♀️', 'lastModifiedDat...\n",
       "11  {'name': 'rnb | soul ♀️', 'lastModifiedDate': ...\n",
       "12  {'name': 'sad songs 2 me', 'lastModifiedDate':...\n",
       "13  {'name': 'this is alevibes -- fritasseira low'...\n",
       "14  {'name': 'rap | hip hop', 'lastModifiedDate': ...\n",
       "15  {'name': 'fall in love w/ your solitude', 'las...\n",
       "16  {'name': 'lost frequencies [current listening]...\n",
       "17  {'name': 'rosalía y reggaeton', 'lastModifiedD...\n",
       "18  {'name': 'pop EDM', 'lastModifiedDate': '2021-...\n",
       "19  {'name': 'Tomorrowland Vibes', 'lastModifiedDa...\n",
       "20  {'name': 'EDM vibes', 'lastModifiedDate': '202...\n",
       "21  {'name': 'popes', 'lastModifiedDate': '2021-04...\n",
       "22  {'name': 'brasilidades', 'lastModifiedDate': '...\n",
       "23  {'name': 'mes mots ne sont pas très beaux', 'l...\n",
       "24  {'name': 'baile da terceira idade', 'lastModif..."
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playlist.head(25)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1b20e167-5ca1-480e-a1ad-e3e0118ee990",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "# Streaming History "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "1453ce58-b150-46d2-a9c8-78f273479d70",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-17 20:55</td>\n",
       "      <td>Don Diablo</td>\n",
       "      <td>Mr. Brightside</td>\n",
       "      <td>193339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-18 16:47</td>\n",
       "      <td>Don Diablo</td>\n",
       "      <td>Mr. Brightside</td>\n",
       "      <td>17800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-10-18 16:51</td>\n",
       "      <td>Unknown Artist</td>\n",
       "      <td>Unknown Track</td>\n",
       "      <td>220148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-10-18 16:53</td>\n",
       "      <td>3 Are Legend</td>\n",
       "      <td>Khaleesi</td>\n",
       "      <td>165000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-10-18 16:57</td>\n",
       "      <td>Afrojack</td>\n",
       "      <td>Ten Feet Tall</td>\n",
       "      <td>229149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2020-10-18 17:03</td>\n",
       "      <td>Afrojack</td>\n",
       "      <td>Turn Up The Speakers - Original Mix</td>\n",
       "      <td>343533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-10-18 17:06</td>\n",
       "      <td>Alan Walker</td>\n",
       "      <td>Alone, Pt. II</td>\n",
       "      <td>179052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2020-10-18 17:07</td>\n",
       "      <td>Alan Walker</td>\n",
       "      <td>All Falls Down (feat. Juliander)</td>\n",
       "      <td>34356</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2020-10-18 17:07</td>\n",
       "      <td>Alan Walker</td>\n",
       "      <td>Faded</td>\n",
       "      <td>1190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2020-10-18 17:07</td>\n",
       "      <td>Alan Walker</td>\n",
       "      <td>Darkside</td>\n",
       "      <td>1470</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            endTime      artistName                            trackName  \\\n",
       "0  2020-10-17 20:55      Don Diablo                       Mr. Brightside   \n",
       "1  2020-10-18 16:47      Don Diablo                       Mr. Brightside   \n",
       "2  2020-10-18 16:51  Unknown Artist                        Unknown Track   \n",
       "3  2020-10-18 16:53    3 Are Legend                             Khaleesi   \n",
       "4  2020-10-18 16:57        Afrojack                        Ten Feet Tall   \n",
       "5  2020-10-18 17:03        Afrojack  Turn Up The Speakers - Original Mix   \n",
       "6  2020-10-18 17:06     Alan Walker                        Alone, Pt. II   \n",
       "7  2020-10-18 17:07     Alan Walker     All Falls Down (feat. Juliander)   \n",
       "8  2020-10-18 17:07     Alan Walker                                Faded   \n",
       "9  2020-10-18 17:07     Alan Walker                             Darkside   \n",
       "\n",
       "   msPlayed  \n",
       "0    193339  \n",
       "1     17800  \n",
       "2    220148  \n",
       "3    165000  \n",
       "4    229149  \n",
       "5    343533  \n",
       "6    179052  \n",
       "7     34356  \n",
       "8      1190  \n",
       "9      1470  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatenate my streaming files - it varies from person to person\n",
    "streaming0 = pd.read_json(base_path+'StreamingHistory0.json')\n",
    "streaming1 = pd.read_json(base_path+'StreamingHistory1.json')\n",
    "streaming2 = pd.read_json(base_path+'StreamingHistory2.json')\n",
    "streaming = pd.concat([streaming0, streaming1, streaming2])\n",
    "streaming.head(10) # Showing the most recent songs played"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "207a9b1f-4e7f-4078-8a28-842c97234152",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 27574 entries, 0 to 7573\n",
      "Data columns (total 4 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   endTime     27574 non-null  object\n",
      " 1   artistName  27574 non-null  object\n",
      " 2   trackName   27574 non-null  object\n",
      " 3   msPlayed    27574 non-null  int64 \n",
      "dtypes: int64(1), object(3)\n",
      "memory usage: 1.1+ MB\n"
     ]
    }
   ],
   "source": [
    "streaming.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8abd9378-eaa3-43eb-ad01-14506624dba0",
   "metadata": {
    "jp-MarkdownHeadingCollapsed": true,
    "tags": []
   },
   "source": [
    "# Saved Tracks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c0b94a9-8ec9-4aec-892f-ff12051e2fab",
   "metadata": {},
   "source": [
    "On my firsts attempts the code below didn't work. It seems to have a structure that pandas doesnt recognize - may be the case of the brackets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "018837fb-e568-4df7-896e-43532aac1858",
   "metadata": {},
   "outputs": [],
   "source": [
    "library = pd.read_json(base_path+'YourLibrary.json')\n",
    "library.head(10) \n",
    "# This didnt work for me"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e26759-9d58-489b-a819-ab89c393f61d",
   "metadata": {},
   "source": [
    " So I need to use this code "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "a45b3eca-339c-4f34-b3e1-6e89e650c47b",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(base_path+'YourLibrary.json', 'r', encoding = 'cp850') as read_file:\n",
    "    data = json.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cc3c5dbc-8ec8-4bfd-80d7-342c3c2c69f1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['tracks', 'albums', 'shows', 'episodes', 'bannedTracks', 'artists', 'bannedArtists', 'other'])"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.keys() # Checking the internal dictionaries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c293dcc5-e3dd-4f79-9669-734ba2291d71",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['artist', 'album', 'track', 'uri'])"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = [track.keys() for track in data['tracks']]\n",
    "a[5] # getting the tracks fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "d88a147d-af4c-4c95-bbca-8f0162800ab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here I get the tracks' keys dictionaries as list, in order to create a dataframe for it later\n",
    "tracks_keys = [list(track.keys()) for track in data['tracks']]\n",
    "tracks_keys = tracks_keys[0] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bbcadd84-3565-4a0a-b328-ed5a169aed67",
   "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>artist</th>\n",
       "      <th>album</th>\n",
       "      <th>track</th>\n",
       "      <th>uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DVBBS</td>\n",
       "      <td>Somebody Like You</td>\n",
       "      <td>Somebody Like You</td>\n",
       "      <td>spotify:track:1wojSLvDvnie1dVaRiMxjS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alan Fitzpatrick</td>\n",
       "      <td>We Do What We Want</td>\n",
       "      <td>We Do What We Want</td>\n",
       "      <td>spotify:track:4gve8Gz2iNDnOIfqQK2dAI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>French Montana</td>\n",
       "      <td>Unforgettable (feat. Swae Lee)</td>\n",
       "      <td>Unforgettable (feat. Swae Lee) - Major Lazer R...</td>\n",
       "      <td>spotify:track:5Xl8hwINbHKEYBHIU0WL1i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Maxim Lany</td>\n",
       "      <td>Renaissance</td>\n",
       "      <td>Renaissance</td>\n",
       "      <td>spotify:track:5DprKz35pYir1JQN75V28n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K├Âlsch</td>\n",
       "      <td>Now Here No Where</td>\n",
       "      <td>Shoulder Of Giants</td>\n",
       "      <td>spotify:track:0D9WPuMYmnsN7Z1kweZBy1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>R├£F├£S DU SOL</td>\n",
       "      <td>Live from Joshua Tree</td>\n",
       "      <td>Innerbloom - Live from Joshua Tree</td>\n",
       "      <td>spotify:track:4tShXbgB6x443S4c28Esjz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Shakira</td>\n",
       "      <td>Waka Waka (This Time for Africa) [The Official...</td>\n",
       "      <td>Waka Waka (This Time for Africa) [The Official...</td>\n",
       "      <td>spotify:track:2Cd9iWfcOpGDHLz6tVA3G4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Lost Frequencies</td>\n",
       "      <td>Cup Of Beats</td>\n",
       "      <td>Don't Leave Me Now</td>\n",
       "      <td>spotify:track:59VDjcpB2I8psQasslQpdK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NSG</td>\n",
       "      <td>Options</td>\n",
       "      <td>Options</td>\n",
       "      <td>spotify:track:2cytBOLpwFRX7J9URCrFIe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Klanglos</td>\n",
       "      <td>Vergessen</td>\n",
       "      <td>Vergessen</td>\n",
       "      <td>spotify:track:1wCjlErPp61MwhSvm09lsF</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             artist                                              album  \\\n",
       "0             DVBBS                                  Somebody Like You   \n",
       "1  Alan Fitzpatrick                                 We Do What We Want   \n",
       "2    French Montana                     Unforgettable (feat. Swae Lee)   \n",
       "3        Maxim Lany                                        Renaissance   \n",
       "4           K├Âlsch                                  Now Here No Where   \n",
       "5    R├£F├£S DU SOL                              Live from Joshua Tree   \n",
       "6           Shakira  Waka Waka (This Time for Africa) [The Official...   \n",
       "7  Lost Frequencies                                       Cup Of Beats   \n",
       "8               NSG                                            Options   \n",
       "9          Klanglos                                          Vergessen   \n",
       "\n",
       "                                               track  \\\n",
       "0                                  Somebody Like You   \n",
       "1                                 We Do What We Want   \n",
       "2  Unforgettable (feat. Swae Lee) - Major Lazer R...   \n",
       "3                                        Renaissance   \n",
       "4                                 Shoulder Of Giants   \n",
       "5                 Innerbloom - Live from Joshua Tree   \n",
       "6  Waka Waka (This Time for Africa) [The Official...   \n",
       "7                                 Don't Leave Me Now   \n",
       "8                                            Options   \n",
       "9                                          Vergessen   \n",
       "\n",
       "                                    uri  \n",
       "0  spotify:track:1wojSLvDvnie1dVaRiMxjS  \n",
       "1  spotify:track:4gve8Gz2iNDnOIfqQK2dAI  \n",
       "2  spotify:track:5Xl8hwINbHKEYBHIU0WL1i  \n",
       "3  spotify:track:5DprKz35pYir1JQN75V28n  \n",
       "4  spotify:track:0D9WPuMYmnsN7Z1kweZBy1  \n",
       "5  spotify:track:4tShXbgB6x443S4c28Esjz  \n",
       "6  spotify:track:2Cd9iWfcOpGDHLz6tVA3G4  \n",
       "7  spotify:track:59VDjcpB2I8psQasslQpdK  \n",
       "8  spotify:track:2cytBOLpwFRX7J9URCrFIe  \n",
       "9  spotify:track:1wCjlErPp61MwhSvm09lsF  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# And here it is the library dataframe with all my saved tracks so far\n",
    "library = pd.DataFrame(data['tracks'], columns=tracks_keys) \n",
    "library.head(10)\n",
    "# The codification is really weird and I hope I dont have problems with it in the future - specially the artist"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd3d5232-4378-4773-b629-5e16ebe0558a",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Lets clean this mess "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81da60d2-8b3b-4762-91dc-0bf67c7df792",
   "metadata": {},
   "source": [
    "Ok. So now, I will compile the saved tracks and the ones on my streaming history. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "1dcf60e3-4632-4553-be22-5e88d930a2a3",
   "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>artist</th>\n",
       "      <th>album</th>\n",
       "      <th>trackName</th>\n",
       "      <th>uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DVBBS</td>\n",
       "      <td>Somebody Like You</td>\n",
       "      <td>Somebody Like You</td>\n",
       "      <td>spotify:track:1wojSLvDvnie1dVaRiMxjS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alan Fitzpatrick</td>\n",
       "      <td>We Do What We Want</td>\n",
       "      <td>We Do What We Want</td>\n",
       "      <td>spotify:track:4gve8Gz2iNDnOIfqQK2dAI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>French Montana</td>\n",
       "      <td>Unforgettable (feat. Swae Lee)</td>\n",
       "      <td>Unforgettable (feat. Swae Lee) - Major Lazer R...</td>\n",
       "      <td>spotify:track:5Xl8hwINbHKEYBHIU0WL1i</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Maxim Lany</td>\n",
       "      <td>Renaissance</td>\n",
       "      <td>Renaissance</td>\n",
       "      <td>spotify:track:5DprKz35pYir1JQN75V28n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K├Âlsch</td>\n",
       "      <td>Now Here No Where</td>\n",
       "      <td>Shoulder Of Giants</td>\n",
       "      <td>spotify:track:0D9WPuMYmnsN7Z1kweZBy1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             artist                           album  \\\n",
       "0             DVBBS               Somebody Like You   \n",
       "1  Alan Fitzpatrick              We Do What We Want   \n",
       "2    French Montana  Unforgettable (feat. Swae Lee)   \n",
       "3        Maxim Lany                     Renaissance   \n",
       "4           K├Âlsch               Now Here No Where   \n",
       "\n",
       "                                           trackName  \\\n",
       "0                                  Somebody Like You   \n",
       "1                                 We Do What We Want   \n",
       "2  Unforgettable (feat. Swae Lee) - Major Lazer R...   \n",
       "3                                        Renaissance   \n",
       "4                                 Shoulder Of Giants   \n",
       "\n",
       "                                    uri  \n",
       "0  spotify:track:1wojSLvDvnie1dVaRiMxjS  \n",
       "1  spotify:track:4gve8Gz2iNDnOIfqQK2dAI  \n",
       "2  spotify:track:5Xl8hwINbHKEYBHIU0WL1i  \n",
       "3  spotify:track:5DprKz35pYir1JQN75V28n  \n",
       "4  spotify:track:0D9WPuMYmnsN7Z1kweZBy1  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# I will rename the track column in order to make easier the dataframe operations (join) later\n",
    "library = library.rename(columns={'track': 'trackName'})\n",
    "library.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16f0dedd-8d77-4360-a551-3a7da54a8883",
   "metadata": {},
   "source": [
    "I'm gonna to save the data on csvs in order to explore them in other notebook. \n",
    "For that, I have to keep in mind the analysis that I want to do on the data, in order to save them correctly.\n",
    "I will need the following features:\n",
    "- artist name\n",
    "- track name\n",
    "- track uri\n",
    "- msPlayed\n",
    "- album\n",
    "- endTime\n",
    "\n",
    "and might be all features that I've explored so far. But I can also transform or create new features from the ones that I have, specially considering the analysis that I wanna do. Base on that, I'll create a feature for year, month and hoursPlayed, just to make my life easier when I will be analysing it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "2249f0fb-c50e-43a7-897a-1de779ae9b6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_datetime(datetime_obj: int, value: str) -> datetime:\n",
    "    ''' Just made a function to get the year and month based on the endTime ''' \n",
    "    \n",
    "    if value == 'year':\n",
    "        return datetime.strptime(str(datetime_obj), \"%Y-%m-%d %H:%M\").year\n",
    "    \n",
    "    return datetime.strptime(str(datetime_obj), \"%Y-%m-%d %H:%M\").month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "f2466f12-454e-44b4-86ad-31786fc4c648",
   "metadata": {},
   "outputs": [],
   "source": [
    "streaming['year'] = streaming['endTime'].apply(lambda x: get_datetime(x, 'year'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "3a6a4acb-11cf-462e-9467-6dc414ca03f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "streaming['month'] = streaming['endTime'].apply(lambda x: get_datetime(x, 'month'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "14650ba0-adb7-4b1e-b4ee-a6fc36782f1f",
   "metadata": {},
   "outputs": [],
   "source": [
    "streaming['hoursPlayed'] = streaming['msPlayed'].transform(lambda x: x*1e-3/3600)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "d58f8650-5f0f-4692-9d7e-379cbace3aa1",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>hoursPlayed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-17 20:55</td>\n",
       "      <td>Don Diablo</td>\n",
       "      <td>Mr. Brightside</td>\n",
       "      <td>193339</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>0.053705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-18 16:47</td>\n",
       "      <td>Don Diablo</td>\n",
       "      <td>Mr. Brightside</td>\n",
       "      <td>17800</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>0.004944</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-10-18 16:51</td>\n",
       "      <td>Unknown Artist</td>\n",
       "      <td>Unknown Track</td>\n",
       "      <td>220148</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>0.061152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-10-18 16:53</td>\n",
       "      <td>3 Are Legend</td>\n",
       "      <td>Khaleesi</td>\n",
       "      <td>165000</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>0.045833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-10-18 16:57</td>\n",
       "      <td>Afrojack</td>\n",
       "      <td>Ten Feet Tall</td>\n",
       "      <td>229149</td>\n",
       "      <td>2020</td>\n",
       "      <td>10</td>\n",
       "      <td>0.063653</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            endTime      artistName       trackName  msPlayed  year  month  \\\n",
       "0  2020-10-17 20:55      Don Diablo  Mr. Brightside    193339  2020     10   \n",
       "1  2020-10-18 16:47      Don Diablo  Mr. Brightside     17800  2020     10   \n",
       "2  2020-10-18 16:51  Unknown Artist   Unknown Track    220148  2020     10   \n",
       "3  2020-10-18 16:53    3 Are Legend        Khaleesi    165000  2020     10   \n",
       "4  2020-10-18 16:57        Afrojack   Ten Feet Tall    229149  2020     10   \n",
       "\n",
       "   hoursPlayed  \n",
       "0     0.053705  \n",
       "1     0.004944  \n",
       "2     0.061152  \n",
       "3     0.045833  \n",
       "4     0.063653  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "streaming.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "fc27f4a6-979f-4ac9-a87c-a60a2db9e896",
   "metadata": {},
   "outputs": [],
   "source": [
    "streaming.to_csv(base_path+'streaming.csv', index=False) # index = False to remove the Unnamed when importing the csv\n",
    "library.to_csv(base_path+'library.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d68574b3-6787-45c6-ae7f-22b0f8f65a66",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Now let's antecipate the most listened song so far "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0e7bf10-2bb0-45a2-9e99-6f8fe44e6760",
   "metadata": {},
   "source": [
    "For that, we need to see some things:\n",
    "* The year played, which is embedded on the date\n",
    "* The frequency tracks' in the history streaming\n",
    "* Certify that only tracks will be counted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "96e5ca7b-b065-4e82-afd7-a30f7d44cf1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data = pd.merge(my_data, library[['album', 'artist', 'uri', 'trackName']], how='left', on=['trackName'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "ddcd4c9a-f8a7-4703-a0b8-02a0d4f01cbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 28054 entries, 0 to 28053\n",
      "Data columns (total 8 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   endTime     28054 non-null  object\n",
      " 1   artistName  28054 non-null  object\n",
      " 2   trackName   28054 non-null  object\n",
      " 3   msPlayed    28054 non-null  int64 \n",
      " 4   Common      28054 non-null  int32 \n",
      " 5   album       10750 non-null  object\n",
      " 6   artist      10750 non-null  object\n",
      " 7   uri         10750 non-null  object\n",
      "dtypes: int32(1), int64(1), object(6)\n",
      "memory usage: 1.8+ MB\n"
     ]
    }
   ],
   "source": [
    "my_data.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "81180229-ac46-4ccc-ab1a-70a28f317378",
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data = streaming.copy()\n",
    "# This code I grab from () and it's pretty useful. It attributes 1 to the common, 0 for the other\n",
    "my_data['Common'] = np.where(my_data['trackName'].isin(library['trackName'].tolist()), 1, 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "6d5a23ec-f127-4c93-a038-6fa7d0b9d907",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "      <th>Common</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-10-17 20:55</td>\n",
       "      <td>Don Diablo</td>\n",
       "      <td>Mr. Brightside</td>\n",
       "      <td>193339</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-10-18 16:47</td>\n",
       "      <td>Don Diablo</td>\n",
       "      <td>Mr. Brightside</td>\n",
       "      <td>17800</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-10-18 16:51</td>\n",
       "      <td>Unknown Artist</td>\n",
       "      <td>Unknown Track</td>\n",
       "      <td>220148</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-10-18 16:53</td>\n",
       "      <td>3 Are Legend</td>\n",
       "      <td>Khaleesi</td>\n",
       "      <td>165000</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-10-18 16:57</td>\n",
       "      <td>Afrojack</td>\n",
       "      <td>Ten Feet Tall</td>\n",
       "      <td>229149</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            endTime      artistName       trackName  msPlayed  Common\n",
       "0  2020-10-17 20:55      Don Diablo  Mr. Brightside    193339       0\n",
       "1  2020-10-18 16:47      Don Diablo  Mr. Brightside     17800       0\n",
       "2  2020-10-18 16:51  Unknown Artist   Unknown Track    220148       0\n",
       "3  2020-10-18 16:53    3 Are Legend        Khaleesi    165000       0\n",
       "4  2020-10-18 16:57        Afrojack   Ten Feet Tall    229149       0"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d91a641e-7968-4f2b-9834-17b0afd70f2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "my_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "4b9d9190-4935-4060-9e97-db907b37ed8c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['endTime', 'artistName', 'trackName', 'msPlayed', 'Common', 'album',\n",
       "       'artist', 'uri'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "24431222-4d22-4822-8c25-e0273598a47b",
   "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>trackName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LA NOCHE DE ANOCHE</th>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Money Trees</th>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Invisible - Paul Kalkbrenner Remix</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>drivers license</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Last Night</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>In The Summer (feat. Jaymes Young)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WANT IT</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naked - (THAT KIND Remix)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gotta Go</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dozens missing in Deadly Indian Floods</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6769 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        trackName\n",
       "LA NOCHE DE ANOCHE                             81\n",
       "Money Trees                                    79\n",
       "Invisible - Paul Kalkbrenner Remix             74\n",
       "drivers license                                73\n",
       "Last Night                                     73\n",
       "...                                           ...\n",
       "In The Summer (feat. Jaymes Young)              1\n",
       "WANT IT                                         1\n",
       "Naked - (THAT KIND Remix)                       1\n",
       "Gotta Go                                        1\n",
       "Dozens missing in Deadly Indian Floods          1\n",
       "\n",
       "[6769 rows x 1 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_played_all = streaming.trackName.value_counts().to_frame()\n",
    "top_played_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "18e5a167-92cc-45c9-8965-3e73c4a17ed2",
   "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>trackName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LA NOCHE DE ANOCHE</th>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Money Trees</th>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Invisible - Paul Kalkbrenner Remix</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>drivers license</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Last Night</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Here with You - Mastrovita X Mordkey Remix</th>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Epiphany</th>\n",
       "      <td>64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Redbone</th>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home</th>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lost</th>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            trackName\n",
       "LA NOCHE DE ANOCHE                                 81\n",
       "Money Trees                                        79\n",
       "Invisible - Paul Kalkbrenner Remix                 74\n",
       "drivers license                                    73\n",
       "Last Night                                         73\n",
       "Here with You - Mastrovita X Mordkey Remix         67\n",
       "Epiphany                                           64\n",
       "Redbone                                            63\n",
       "Home                                               61\n",
       "Lost                                               59"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_played_all[:100].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c79d24eb-ebb9-4807-bcf2-25ea554f7580",
   "metadata": {},
   "source": [
    "Ok, so far we have LA NOCHE DE ANOCHE as most played music by me. But it's about all the time."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "326fd20d-34b0-44aa-86bf-85dd38ddf8c9",
   "metadata": {},
   "source": [
    "Lets get only for this year. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "c244bbd5-6c50-4be2-8ac2-f340c9d574ff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2020-10-17 20:55\n",
       "0    2021-03-28 21:54\n",
       "0    2021-07-13 17:13\n",
       "Name: endTime, dtype: object"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "streaming['endTime'][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "id": "456b8470-2321-4e46-a81e-88344c4eebf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "top_2021 = streaming.query(\"year==2021\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "5ce4199c-ab5d-4e46-90bf-e46b1f99ee96",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6454</th>\n",
       "      <td>2021-01-01 16:28</td>\n",
       "      <td>Mähr</td>\n",
       "      <td>Dream of You</td>\n",
       "      <td>36416</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6455</th>\n",
       "      <td>2021-01-01 18:19</td>\n",
       "      <td>Maroon 5</td>\n",
       "      <td>Payphone</td>\n",
       "      <td>2900</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6456</th>\n",
       "      <td>2021-01-01 18:22</td>\n",
       "      <td>Mähr</td>\n",
       "      <td>Dream of You</td>\n",
       "      <td>192305</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6457</th>\n",
       "      <td>2021-01-01 18:22</td>\n",
       "      <td>Nora En Pure</td>\n",
       "      <td>Epiphany</td>\n",
       "      <td>3914</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6458</th>\n",
       "      <td>2021-01-01 18:27</td>\n",
       "      <td>Mert Oksuz</td>\n",
       "      <td>Everything We Had</td>\n",
       "      <td>278079</td>\n",
       "      <td>2021</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               endTime    artistName          trackName  msPlayed  year\n",
       "6454  2021-01-01 16:28          Mähr       Dream of You     36416  2021\n",
       "6455  2021-01-01 18:19      Maroon 5           Payphone      2900  2021\n",
       "6456  2021-01-01 18:22          Mähr       Dream of You    192305  2021\n",
       "6457  2021-01-01 18:22  Nora En Pure           Epiphany      3914  2021\n",
       "6458  2021-01-01 18:27    Mert Oksuz  Everything We Had    278079  2021"
      ]
     },
     "execution_count": 135,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_2021.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "id": "5c072f90-6ea4-43aa-a0c9-a5c91fab7c59",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Invisible - Paul Kalkbrenner Remix</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Last Night</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>drivers license</th>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Redbone</th>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Home</th>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Way down We Go</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>I Found You (Neptune)</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Balearic Mornings</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Silence - Tiësto's Big Room Remix</th>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Break This Habit</th>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    count\n",
       "Invisible - Paul Kalkbrenner Remix     74\n",
       "Last Night                             73\n",
       "drivers license                        73\n",
       "Redbone                                63\n",
       "Home                                   60\n",
       "...                                   ...\n",
       "Way down We Go                         26\n",
       "I Found You (Neptune)                  26\n",
       "Balearic Mornings                      26\n",
       "Silence - Tiësto's Big Room Remix      26\n",
       "Break This Habit                       25\n",
       "\n",
       "[100 rows x 1 columns]"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_2021.trackName.value_counts().to_frame(name='count')[:100]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac30dcc9-7c90-4806-a724-b0ab82930df7",
   "metadata": {},
   "source": [
    "Now we have!!!!!!"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7304f7d-ccf7-4a41-8db0-de79a0f98de3",
   "metadata": {},
   "source": [
    "I need to be careful analysing the msPlayed, 'cause I usually like to listen long tracks (up to 5 mintues). So maybe there's a chance that when we sort usign the msPlayed, even if I haven't listen to much, they could count. But the ideia now is to get the tracks that I've most played or listened by frequency, not by duration."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "9645f20c-e56d-4e93-831e-27d06dc94492",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9457</th>\n",
       "      <td>2021-03-22 17:27</td>\n",
       "      <td>Towards Data Science</td>\n",
       "      <td>75. Georg Northoff - Consciousness and AI</td>\n",
       "      <td>3552049</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2773</th>\n",
       "      <td>2020-11-11 17:29</td>\n",
       "      <td>Mulheres da Engenharia</td>\n",
       "      <td>68 - Transmissão de Energia em Corrente Contín...</td>\n",
       "      <td>3255044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7639</th>\n",
       "      <td>2021-01-30 03:15</td>\n",
       "      <td>An Actor Despairs</td>\n",
       "      <td>Elisha Applebaum</td>\n",
       "      <td>2818358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9437</th>\n",
       "      <td>2021-03-21 15:15</td>\n",
       "      <td>The Economist Podcasts</td>\n",
       "      <td>Checks and Balance: No vacancy</td>\n",
       "      <td>2657880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5098</th>\n",
       "      <td>2020-12-09 19:36</td>\n",
       "      <td>Boletos Pagos com Nath Finanças</td>\n",
       "      <td>Como Emicida lida com suas finanças pessoais</td>\n",
       "      <td>2599530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>2021-07-15 12:05</td>\n",
       "      <td>Ben Böhmer</td>\n",
       "      <td>Beyond Beliefs</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4535</th>\n",
       "      <td>2020-12-03 17:49</td>\n",
       "      <td>ROSALÍA</td>\n",
       "      <td>A NINGÚN HOMBRE - Cap.11: Poder</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6358</th>\n",
       "      <td>2020-12-21 22:49</td>\n",
       "      <td>Mike Perry</td>\n",
       "      <td>Hands</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7193</th>\n",
       "      <td>2021-01-13 12:45</td>\n",
       "      <td>illusionize</td>\n",
       "      <td>Down</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>2021-07-15 22:47</td>\n",
       "      <td>Lous and The Yakuza</td>\n",
       "      <td>Messes basses</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>27574 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               endTime                       artistName  \\\n",
       "9457  2021-03-22 17:27             Towards Data Science   \n",
       "2773  2020-11-11 17:29           Mulheres da Engenharia   \n",
       "7639  2021-01-30 03:15                An Actor Despairs   \n",
       "9437  2021-03-21 15:15           The Economist Podcasts   \n",
       "5098  2020-12-09 19:36  Boletos Pagos com Nath Finanças   \n",
       "...                ...                              ...   \n",
       "74    2021-07-15 12:05                       Ben Böhmer   \n",
       "4535  2020-12-03 17:49                          ROSALÍA   \n",
       "6358  2020-12-21 22:49                       Mike Perry   \n",
       "7193  2021-01-13 12:45                      illusionize   \n",
       "121   2021-07-15 22:47              Lous and The Yakuza   \n",
       "\n",
       "                                              trackName  msPlayed  \n",
       "9457          75. Georg Northoff - Consciousness and AI   3552049  \n",
       "2773  68 - Transmissão de Energia em Corrente Contín...   3255044  \n",
       "7639                                   Elisha Applebaum   2818358  \n",
       "9437                     Checks and Balance: No vacancy   2657880  \n",
       "5098       Como Emicida lida com suas finanças pessoais   2599530  \n",
       "...                                                 ...       ...  \n",
       "74                                       Beyond Beliefs         0  \n",
       "4535                    A NINGÚN HOMBRE - Cap.11: Poder         0  \n",
       "6358                                              Hands         0  \n",
       "7193                                               Down         0  \n",
       "121                                       Messes basses         0  \n",
       "\n",
       "[27574 rows x 4 columns]"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "streaming.sort_values('msPlayed', ascending=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2813d3aa-42d4-44e5-a2dc-ddcaa6d49478",
   "metadata": {},
   "source": [
    "And, surprise, the history listening has also podcasts, which counts a lot viewing by msPlayed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "8542562a-ce51-4de4-96af-77d95eefddee",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "      <th>Common</th>\n",
       "      <th>album</th>\n",
       "      <th>artist</th>\n",
       "      <th>uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1225</th>\n",
       "      <td>2020-10-28 14:02</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>386906</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:0UtnpKaReKUg2GquaSxCyD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1226</th>\n",
       "      <td>2020-10-28 14:02</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>386906</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:2HbKqm4o0w5wEeEFXm2sD4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1315</th>\n",
       "      <td>2020-10-29 16:48</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>386906</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:0UtnpKaReKUg2GquaSxCyD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1316</th>\n",
       "      <td>2020-10-29 16:48</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>386906</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:2HbKqm4o0w5wEeEFXm2sD4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2080</th>\n",
       "      <td>2020-11-06 12:55</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>386906</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:0UtnpKaReKUg2GquaSxCyD</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24546</th>\n",
       "      <td>2021-09-01 20:10</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>218290</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:0UtnpKaReKUg2GquaSxCyD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24547</th>\n",
       "      <td>2021-09-01 20:10</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>218290</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:2HbKqm4o0w5wEeEFXm2sD4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24879</th>\n",
       "      <td>2021-09-04 19:35</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>LOYALTY. FEAT. RIHANNA.</td>\n",
       "      <td>227360</td>\n",
       "      <td>1</td>\n",
       "      <td>DAMN.</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:6SwRhMLwNqEi6alNPVG00n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24906</th>\n",
       "      <td>2021-09-04 21:41</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>LOYALTY. FEAT. RIHANNA.</td>\n",
       "      <td>227360</td>\n",
       "      <td>1</td>\n",
       "      <td>DAMN.</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:6SwRhMLwNqEi6alNPVG00n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27380</th>\n",
       "      <td>2021-10-05 14:35</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>LOYALTY. FEAT. RIHANNA.</td>\n",
       "      <td>227360</td>\n",
       "      <td>1</td>\n",
       "      <td>DAMN.</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:6SwRhMLwNqEi6alNPVG00n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>170 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                endTime      artistName                trackName  msPlayed  \\\n",
       "1225   2020-10-28 14:02  Kendrick Lamar              Money Trees    386906   \n",
       "1226   2020-10-28 14:02  Kendrick Lamar              Money Trees    386906   \n",
       "1315   2020-10-29 16:48  Kendrick Lamar              Money Trees    386906   \n",
       "1316   2020-10-29 16:48  Kendrick Lamar              Money Trees    386906   \n",
       "2080   2020-11-06 12:55  Kendrick Lamar              Money Trees    386906   \n",
       "...                 ...             ...                      ...       ...   \n",
       "24546  2021-09-01 20:10  Kendrick Lamar              Money Trees    218290   \n",
       "24547  2021-09-01 20:10  Kendrick Lamar              Money Trees    218290   \n",
       "24879  2021-09-04 19:35  Kendrick Lamar  LOYALTY. FEAT. RIHANNA.    227360   \n",
       "24906  2021-09-04 21:41  Kendrick Lamar  LOYALTY. FEAT. RIHANNA.    227360   \n",
       "27380  2021-10-05 14:35  Kendrick Lamar  LOYALTY. FEAT. RIHANNA.    227360   \n",
       "\n",
       "       Common                   album          artist  \\\n",
       "1225        1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "1226        1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "1315        1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "1316        1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "2080        1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "...       ...                     ...             ...   \n",
       "24546       1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "24547       1  good kid, m.A.A.d city  Kendrick Lamar   \n",
       "24879       1                   DAMN.  Kendrick Lamar   \n",
       "24906       1                   DAMN.  Kendrick Lamar   \n",
       "27380       1                   DAMN.  Kendrick Lamar   \n",
       "\n",
       "                                        uri  \n",
       "1225   spotify:track:0UtnpKaReKUg2GquaSxCyD  \n",
       "1226   spotify:track:2HbKqm4o0w5wEeEFXm2sD4  \n",
       "1315   spotify:track:0UtnpKaReKUg2GquaSxCyD  \n",
       "1316   spotify:track:2HbKqm4o0w5wEeEFXm2sD4  \n",
       "2080   spotify:track:0UtnpKaReKUg2GquaSxCyD  \n",
       "...                                     ...  \n",
       "24546  spotify:track:0UtnpKaReKUg2GquaSxCyD  \n",
       "24547  spotify:track:2HbKqm4o0w5wEeEFXm2sD4  \n",
       "24879  spotify:track:6SwRhMLwNqEi6alNPVG00n  \n",
       "24906  spotify:track:6SwRhMLwNqEi6alNPVG00n  \n",
       "27380  spotify:track:6SwRhMLwNqEi6alNPVG00n  \n",
       "\n",
       "[170 rows x 8 columns]"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_data[my_data.artist == 'Kendrick Lamar']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47530b02-97c1-4cae-a461-193f5acc3d05",
   "metadata": {},
   "source": [
    "Here I'm dropping episodes and podcasts because they're much longer in duration than tracks"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dc859f0-62cc-47d3-85ce-5b9a495497b5",
   "metadata": {},
   "source": [
    "I'm also removing the same track that appears twice, 'cause you may have listened in two albums - which is pretty common when the artist has deluxe and the \"normal\" albuns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "96be3be0-8b62-461a-8268-b08386d54530",
   "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>trackName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Money Trees</th>\n",
       "      <td>158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Left Hand Free</th>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Spotless</th>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Silence</th>\n",
       "      <td>104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gone</th>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>In The Summer (feat. Jaymes Young)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WANT IT</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Naked - (THAT KIND Remix)</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Gotta Go</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dozens missing in Deadly Indian Floods</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6769 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        trackName\n",
       "Money Trees                                   158\n",
       "Left Hand Free                                111\n",
       "Spotless                                      106\n",
       "Silence                                       104\n",
       "Gone                                           90\n",
       "...                                           ...\n",
       "In The Summer (feat. Jaymes Young)              1\n",
       "WANT IT                                         1\n",
       "Naked - (THAT KIND Remix)                       1\n",
       "Gotta Go                                        1\n",
       "Dozens missing in Deadly Indian Floods          1\n",
       "\n",
       "[6769 rows x 1 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_data.trackName.value_counts().to_frame()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "4202d61c-3754-45d0-801d-8d9dbc380dd2",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "      <th>Common</th>\n",
       "      <th>album</th>\n",
       "      <th>artist</th>\n",
       "      <th>uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2250</th>\n",
       "      <td>2020-11-07 21:23</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>Money Trees</td>\n",
       "      <td>1346720</td>\n",
       "      <td>1</td>\n",
       "      <td>good kid, m.A.A.d city</td>\n",
       "      <td>Kendrick Lamar</td>\n",
       "      <td>spotify:track:2HbKqm4o0w5wEeEFXm2sD4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15690</th>\n",
       "      <td>2021-05-16 21:31</td>\n",
       "      <td>Klanglos</td>\n",
       "      <td>Vergessen</td>\n",
       "      <td>1076603</td>\n",
       "      <td>1</td>\n",
       "      <td>Vergessen</td>\n",
       "      <td>Klanglos</td>\n",
       "      <td>spotify:track:1wCjlErPp61MwhSvm09lsF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17425</th>\n",
       "      <td>2021-06-08 01:20</td>\n",
       "      <td>RÜFÜS DU SOL</td>\n",
       "      <td>Imaginary Air</td>\n",
       "      <td>897563</td>\n",
       "      <td>1</td>\n",
       "      <td>Atlas (Light / Dark Deluxe Edition)</td>\n",
       "      <td>R├£F├£S DU SOL</td>\n",
       "      <td>spotify:track:7lHMyZUwMtEjfwdym714dr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24468</th>\n",
       "      <td>2021-09-01 14:11</td>\n",
       "      <td>Zigan Aldi</td>\n",
       "      <td>Fidale</td>\n",
       "      <td>799375</td>\n",
       "      <td>1</td>\n",
       "      <td>Fidale</td>\n",
       "      <td>Zigan Aldi</td>\n",
       "      <td>spotify:track:7J0DIlnGW8C0IW1Gso1BDV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22476</th>\n",
       "      <td>2021-08-13 01:27</td>\n",
       "      <td>Childish Gambino</td>\n",
       "      <td>Redbone</td>\n",
       "      <td>736940</td>\n",
       "      <td>1</td>\n",
       "      <td>\"Awaken, My Love!\"</td>\n",
       "      <td>Childish Gambino</td>\n",
       "      <td>spotify:track:0wXuerDYiBnERgIpbb3JBR</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8401</th>\n",
       "      <td>2021-02-08 22:24</td>\n",
       "      <td>Labrinth</td>\n",
       "      <td>Something's Got To Give</td>\n",
       "      <td>1369</td>\n",
       "      <td>1</td>\n",
       "      <td>Something's Got To Give</td>\n",
       "      <td>Labrinth</td>\n",
       "      <td>spotify:track:56khZQKfmdTikYbbZLgxAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>552</th>\n",
       "      <td>2020-10-22 12:55</td>\n",
       "      <td>Catching Flies</td>\n",
       "      <td>Mt. Wolf - Life Size Ghosts - Catching Flies R...</td>\n",
       "      <td>1330</td>\n",
       "      <td>1</td>\n",
       "      <td>The Stars</td>\n",
       "      <td>Catching Flies</td>\n",
       "      <td>spotify:track:3gE9U5mNrJewDXN1wU7d8x</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6657</th>\n",
       "      <td>2021-01-03 23:04</td>\n",
       "      <td>Billie Eilish</td>\n",
       "      <td>bury a friend</td>\n",
       "      <td>1068</td>\n",
       "      <td>1</td>\n",
       "      <td>WHEN WE ALL FALL ASLEEP, WHERE DO WE GO?</td>\n",
       "      <td>Billie Eilish</td>\n",
       "      <td>spotify:track:4SSnFejRGlZikf02HLewEF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>563</th>\n",
       "      <td>2020-10-22 12:55</td>\n",
       "      <td>Snakehips</td>\n",
       "      <td>All My Friends (feat. Tinashe &amp; Chance the Rap...</td>\n",
       "      <td>890</td>\n",
       "      <td>1</td>\n",
       "      <td>All My Friends (feat. Tinashe &amp; Chance the Rap...</td>\n",
       "      <td>Snakehips</td>\n",
       "      <td>spotify:track:6TaqooOXAEcijL6G1AWS2K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6672</th>\n",
       "      <td>2021-01-03 23:07</td>\n",
       "      <td>Kygo</td>\n",
       "      <td>Raging (feat. Kodaline)</td>\n",
       "      <td>859</td>\n",
       "      <td>1</td>\n",
       "      <td>Raging (feat. Kodaline)</td>\n",
       "      <td>Kygo</td>\n",
       "      <td>spotify:track:6DsFZITJMPnh8z5XewfVmL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>694 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                endTime        artistName  \\\n",
       "2250   2020-11-07 21:23    Kendrick Lamar   \n",
       "15690  2021-05-16 21:31          Klanglos   \n",
       "17425  2021-06-08 01:20      RÜFÜS DU SOL   \n",
       "24468  2021-09-01 14:11        Zigan Aldi   \n",
       "22476  2021-08-13 01:27  Childish Gambino   \n",
       "...                 ...               ...   \n",
       "8401   2021-02-08 22:24          Labrinth   \n",
       "552    2020-10-22 12:55    Catching Flies   \n",
       "6657   2021-01-03 23:04     Billie Eilish   \n",
       "563    2020-10-22 12:55         Snakehips   \n",
       "6672   2021-01-03 23:07              Kygo   \n",
       "\n",
       "                                               trackName  msPlayed  Common  \\\n",
       "2250                                         Money Trees   1346720       1   \n",
       "15690                                          Vergessen   1076603       1   \n",
       "17425                                      Imaginary Air    897563       1   \n",
       "24468                                             Fidale    799375       1   \n",
       "22476                                            Redbone    736940       1   \n",
       "...                                                  ...       ...     ...   \n",
       "8401                             Something's Got To Give      1369       1   \n",
       "552    Mt. Wolf - Life Size Ghosts - Catching Flies R...      1330       1   \n",
       "6657                                       bury a friend      1068       1   \n",
       "563    All My Friends (feat. Tinashe & Chance the Rap...       890       1   \n",
       "6672                             Raging (feat. Kodaline)       859       1   \n",
       "\n",
       "                                                   album            artist  \\\n",
       "2250                              good kid, m.A.A.d city    Kendrick Lamar   \n",
       "15690                                          Vergessen          Klanglos   \n",
       "17425                Atlas (Light / Dark Deluxe Edition)    R├£F├£S DU SOL   \n",
       "24468                                             Fidale        Zigan Aldi   \n",
       "22476                                 \"Awaken, My Love!\"  Childish Gambino   \n",
       "...                                                  ...               ...   \n",
       "8401                             Something's Got To Give          Labrinth   \n",
       "552                                            The Stars    Catching Flies   \n",
       "6657            WHEN WE ALL FALL ASLEEP, WHERE DO WE GO?     Billie Eilish   \n",
       "563    All My Friends (feat. Tinashe & Chance the Rap...         Snakehips   \n",
       "6672                             Raging (feat. Kodaline)              Kygo   \n",
       "\n",
       "                                        uri  \n",
       "2250   spotify:track:2HbKqm4o0w5wEeEFXm2sD4  \n",
       "15690  spotify:track:1wCjlErPp61MwhSvm09lsF  \n",
       "17425  spotify:track:7lHMyZUwMtEjfwdym714dr  \n",
       "24468  spotify:track:7J0DIlnGW8C0IW1Gso1BDV  \n",
       "22476  spotify:track:0wXuerDYiBnERgIpbb3JBR  \n",
       "...                                     ...  \n",
       "8401   spotify:track:56khZQKfmdTikYbbZLgxAY  \n",
       "552    spotify:track:3gE9U5mNrJewDXN1wU7d8x  \n",
       "6657   spotify:track:4SSnFejRGlZikf02HLewEF  \n",
       "563    spotify:track:6TaqooOXAEcijL6G1AWS2K  \n",
       "6672   spotify:track:6DsFZITJMPnh8z5XewfVmL  \n",
       "\n",
       "[694 rows x 8 columns]"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_new_data = my_data.sort_values('msPlayed', ascending=False).drop_duplicates('trackName')\n",
    "my_new_data.dropna(inplace=True)\n",
    "my_new_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57eff0e1-bb3c-4726-baf3-85dfed08c1c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# From that pointed, my most listened track so far is money Trees. But is not counting for this year. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "4c433664-8498-46e4-93b5-fb6a0fff5a24",
   "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>endTime</th>\n",
       "      <th>artistName</th>\n",
       "      <th>trackName</th>\n",
       "      <th>msPlayed</th>\n",
       "      <th>Common</th>\n",
       "      <th>album</th>\n",
       "      <th>artist</th>\n",
       "      <th>uri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20722</th>\n",
       "      <td>2021-07-21 19:55</td>\n",
       "      <td>Jan Blomqvist</td>\n",
       "      <td>Elephant Shunned</td>\n",
       "      <td>321707</td>\n",
       "      <td>1</td>\n",
       "      <td>Disconnected</td>\n",
       "      <td>Jan Blomqvist</td>\n",
       "      <td>spotify:track:6pYtOQlMif5e0jHCIbhdK3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>402</th>\n",
       "      <td>2020-10-22 00:41</td>\n",
       "      <td>VIZE</td>\n",
       "      <td>Brother Louie</td>\n",
       "      <td>321417</td>\n",
       "      <td>1</td>\n",
       "      <td>Brother Louie</td>\n",
       "      <td>VIZE</td>\n",
       "      <td>spotify:track:0fQfopzcRtmYxdK9xFA3Qr</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5157</th>\n",
       "      <td>2020-12-09 17:07</td>\n",
       "      <td>Emicida</td>\n",
       "      <td>AmarElo (Sample: Sujeito de Sorte - Belchior)</td>\n",
       "      <td>320800</td>\n",
       "      <td>1</td>\n",
       "      <td>AmarElo</td>\n",
       "      <td>Emicida</td>\n",
       "      <td>spotify:track:5p3LIyy38s0QQNoSTwbZXX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12913</th>\n",
       "      <td>2021-04-22 10:48</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>One More Time</td>\n",
       "      <td>320357</td>\n",
       "      <td>1</td>\n",
       "      <td>Discovery</td>\n",
       "      <td>Daft Punk</td>\n",
       "      <td>spotify:track:0DiWol3AO6WpXZgp0goxAV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6985</th>\n",
       "      <td>2021-01-08 14:37</td>\n",
       "      <td>Giggs</td>\n",
       "      <td>Im Workin (feat. Jorja Smith)</td>\n",
       "      <td>319758</td>\n",
       "      <td>1</td>\n",
       "      <td>Now Or Never</td>\n",
       "      <td>Giggs</td>\n",
       "      <td>spotify:track:4cqjB1MFbHYmCzN6ENAbOV</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                endTime     artistName  \\\n",
       "20722  2021-07-21 19:55  Jan Blomqvist   \n",
       "402    2020-10-22 00:41           VIZE   \n",
       "5157   2020-12-09 17:07        Emicida   \n",
       "12913  2021-04-22 10:48      Daft Punk   \n",
       "6985   2021-01-08 14:37          Giggs   \n",
       "\n",
       "                                           trackName  msPlayed  Common  \\\n",
       "20722                               Elephant Shunned    321707       1   \n",
       "402                                    Brother Louie    321417       1   \n",
       "5157   AmarElo (Sample: Sujeito de Sorte - Belchior)    320800       1   \n",
       "12913                                  One More Time    320357       1   \n",
       "6985                   Im Workin (feat. Jorja Smith)    319758       1   \n",
       "\n",
       "               album         artist                                   uri  \n",
       "20722   Disconnected  Jan Blomqvist  spotify:track:6pYtOQlMif5e0jHCIbhdK3  \n",
       "402    Brother Louie           VIZE  spotify:track:0fQfopzcRtmYxdK9xFA3Qr  \n",
       "5157         AmarElo        Emicida  spotify:track:5p3LIyy38s0QQNoSTwbZXX  \n",
       "12913      Discovery      Daft Punk  spotify:track:0DiWol3AO6WpXZgp0goxAV  \n",
       "6985    Now Or Never          Giggs  spotify:track:4cqjB1MFbHYmCzN6ENAbOV  "
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_100 = my_new_data[:100]\n",
    "top_100.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "2d729198-422a-446d-a7ef-786a47a194a0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Money Trees                               158\n",
       "Left Hand Free                            111\n",
       "Spotless                                  106\n",
       "Silence                                   104\n",
       "Gone                                       90\n",
       "                                         ... \n",
       "In The Summer (feat. Jaymes Young)          1\n",
       "WANT IT                                     1\n",
       "Naked - (THAT KIND Remix)                   1\n",
       "Gotta Go                                    1\n",
       "Dozens missing in Deadly Indian Floods      1\n",
       "Name: trackName, Length: 6769, dtype: int64"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_data.trackName.value_counts()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}