analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0511e9fa",
   "metadata": {},
   "source": [
    "## Background\n",
    "\n",
    "**TODO:** Write the background"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6da5f51",
   "metadata": {},
   "source": [
    "## Questions to Answer\n",
    "\n",
    "General analysis:\n",
    "- Listening patterns over time\n",
    "    - Long term analysis\n",
    "    - Time of day, day of week, season, and other short term analysis\n",
    "- What types of artists and genres did I listen to?\n",
    "- Can I obtain the lyrics of each song and analyze the emotion of a song?\n",
    "    - What emotions of songs did I listen to over time?\n",
    "- How often did I listen to extremely popular artists versus smaller/indie artists?\n",
    "- How often do I listen to music in any one sitting?\n",
    "- How does the frequency of how long I listen to music vary over different time frames?\n",
    "    - How long do I listen to specific artists and genres?\n",
    "- Which countries did I listen to and what type of music did I listen to in those countries?\n",
    "- How is my listening behavior affected by the device I use to listen?\n",
    "\n",
    "\n",
    "Some specific questions:\n",
    "- What type of songs did I either skip or only play for five seconds or less?\n",
    "- What type of songs did I listen to on incognito mode and why?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "07786a44",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import libraries\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from datetime import datetime\n",
    "import calendar\n",
    "import pytz\n",
    "\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "fede8996",
   "metadata": {},
   "outputs": [],
   "source": [
    "# read in datasets\n",
    "df0 = pd.read_json(\"data/Streaming_History_Audio_2018-2020_0.json\")\n",
    "df1 = pd.read_json(\"data/Streaming_History_Audio_2020-2021_1.json\")\n",
    "df2 = pd.read_json(\"data/Streaming_History_Audio_2021-2022_2.json\")\n",
    "df3 = pd.read_json(\"data/Streaming_History_Audio_2022_3.json\")\n",
    "df4 = pd.read_json(\"data/Streaming_History_Audio_2022-2023_4.json\")\n",
    "df5 = pd.read_json(\"data/Streaming_History_Audio_2023-2024_5.json\")\n",
    "df6 = pd.read_json(\"data/Streaming_History_Audio_2024_6.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "c050c26a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([df0, df1, df2, df3, df4, df5, df6], axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "id": "f7b6ce6a",
   "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>ts</th>\n",
       "      <th>username</th>\n",
       "      <th>platform</th>\n",
       "      <th>ms_played</th>\n",
       "      <th>conn_country</th>\n",
       "      <th>ip_addr_decrypted</th>\n",
       "      <th>user_agent_decrypted</th>\n",
       "      <th>master_metadata_track_name</th>\n",
       "      <th>master_metadata_album_artist_name</th>\n",
       "      <th>master_metadata_album_album_name</th>\n",
       "      <th>...</th>\n",
       "      <th>episode_name</th>\n",
       "      <th>episode_show_name</th>\n",
       "      <th>spotify_episode_uri</th>\n",
       "      <th>reason_start</th>\n",
       "      <th>reason_end</th>\n",
       "      <th>shuffle</th>\n",
       "      <th>skipped</th>\n",
       "      <th>offline</th>\n",
       "      <th>offline_timestamp</th>\n",
       "      <th>incognito_mode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-03-16T20:41:49Z</td>\n",
       "      <td>mmphieag</td>\n",
       "      <td>Windows 10 (10.0.16299; x64; AppX)</td>\n",
       "      <td>45010</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>No Role Modelz</td>\n",
       "      <td>J. Cole</td>\n",
       "      <td>2014 Forest Hills Drive</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>clickrow</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1521232854659</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-03-16T20:42:29Z</td>\n",
       "      <td>mmphieag</td>\n",
       "      <td>Windows 10 (10.0.16299; x64; AppX)</td>\n",
       "      <td>36270</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>No Role Modelz</td>\n",
       "      <td>J. Cole</td>\n",
       "      <td>2014 Forest Hills Drive</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>appload</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1521232907487</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-16T20:44:28Z</td>\n",
       "      <td>mmphieag</td>\n",
       "      <td>Windows 10 (10.0.16299; x64; AppX)</td>\n",
       "      <td>27800</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>No Role Modelz</td>\n",
       "      <td>J. Cole</td>\n",
       "      <td>2014 Forest Hills Drive</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>clickrow</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1521232947154</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-03-16T20:45:00Z</td>\n",
       "      <td>mmphieag</td>\n",
       "      <td>Windows 10 (10.0.16299; x64; AppX)</td>\n",
       "      <td>31060</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>Chandler Road (Instrumental Remix)</td>\n",
       "      <td>Sbvce</td>\n",
       "      <td>Sbvce Pvck: Bedroomtrap Vol. 1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>clickrow</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1521233066829</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-03-16T20:47:55Z</td>\n",
       "      <td>mmphieag</td>\n",
       "      <td>Windows 10 (10.0.16299; x64; AppX)</td>\n",
       "      <td>3870</td>\n",
       "      <td>US</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>I. The Worst Guys</td>\n",
       "      <td>Childish Gambino</td>\n",
       "      <td>Because The Internet</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>clickrow</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>False</td>\n",
       "      <td>1521233098379</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                     ts  username                            platform  \\\n",
       "0  2018-03-16T20:41:49Z  mmphieag  Windows 10 (10.0.16299; x64; AppX)   \n",
       "1  2018-03-16T20:42:29Z  mmphieag  Windows 10 (10.0.16299; x64; AppX)   \n",
       "2  2018-03-16T20:44:28Z  mmphieag  Windows 10 (10.0.16299; x64; AppX)   \n",
       "3  2018-03-16T20:45:00Z  mmphieag  Windows 10 (10.0.16299; x64; AppX)   \n",
       "4  2018-03-16T20:47:55Z  mmphieag  Windows 10 (10.0.16299; x64; AppX)   \n",
       "\n",
       "   ms_played conn_country ip_addr_decrypted user_agent_decrypted  \\\n",
       "0      45010           US              None                 None   \n",
       "1      36270           US              None                 None   \n",
       "2      27800           US              None                 None   \n",
       "3      31060           US              None                 None   \n",
       "4       3870           US              None                 None   \n",
       "\n",
       "           master_metadata_track_name master_metadata_album_artist_name  \\\n",
       "0                      No Role Modelz                           J. Cole   \n",
       "1                      No Role Modelz                           J. Cole   \n",
       "2                      No Role Modelz                           J. Cole   \n",
       "3  Chandler Road (Instrumental Remix)                             Sbvce   \n",
       "4                   I. The Worst Guys                  Childish Gambino   \n",
       "\n",
       "  master_metadata_album_album_name  ... episode_name episode_show_name  \\\n",
       "0          2014 Forest Hills Drive  ...         None              None   \n",
       "1          2014 Forest Hills Drive  ...         None              None   \n",
       "2          2014 Forest Hills Drive  ...         None              None   \n",
       "3   Sbvce Pvck: Bedroomtrap Vol. 1  ...         None              None   \n",
       "4             Because The Internet  ...         None              None   \n",
       "\n",
       "  spotify_episode_uri reason_start reason_end shuffle  skipped  offline  \\\n",
       "0                None     clickrow       None   False      NaN    False   \n",
       "1                None      appload       None   False      NaN    False   \n",
       "2                None     clickrow       None   False      NaN    False   \n",
       "3                None     clickrow       None   False      NaN    False   \n",
       "4                None     clickrow       None   False      NaN    False   \n",
       "\n",
       "   offline_timestamp  incognito_mode  \n",
       "0      1521232854659           False  \n",
       "1      1521232907487           False  \n",
       "2      1521232947154           False  \n",
       "3      1521233066829           False  \n",
       "4      1521233098379           False  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "0890a44b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ts', 'username', 'platform', 'ms_played', 'conn_country',\n",
       "       'ip_addr_decrypted', 'user_agent_decrypted',\n",
       "       'master_metadata_track_name', 'master_metadata_album_artist_name',\n",
       "       'master_metadata_album_album_name', 'spotify_track_uri', 'episode_name',\n",
       "       'episode_show_name', 'spotify_episode_uri', 'reason_start',\n",
       "       'reason_end', 'shuffle', 'skipped', 'offline', 'offline_timestamp',\n",
       "       'incognito_mode'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8995b0b8",
   "metadata": {},
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "832c9a36",
   "metadata": {},
   "source": [
    "### Connected Country\n",
    "conn_country: This field is the country code of the country where the stream was played (e.g. SE - Sweden)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "45550d4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'conn_country': 'country'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "a9d395df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    US\n",
       "1    US\n",
       "2    US\n",
       "3    US\n",
       "4    US\n",
       "Name: country, dtype: object"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "id": "59b65e2e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['US', 'IS', 'ZZ', 'CA', 'DE', 'IN', 'TZ', 'KE', 'MX', 'DO'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "ff550bbf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "US    107737\n",
       "DE       649\n",
       "ZZ       568\n",
       "IN       400\n",
       "TZ       303\n",
       "MX       113\n",
       "DO        77\n",
       "KE        57\n",
       "IS        50\n",
       "CA        17\n",
       "Name: country, dtype: int64"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "id": "1da06d70",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_country(code):\n",
    "    code_dict = {\n",
    "        'US': 'United States',\n",
    "        'IS': 'Iceland',\n",
    "        'ZZ': 'Unknown',\n",
    "        'CA': 'Canada',\n",
    "        'DE': 'Germany',\n",
    "        'IN': 'India',\n",
    "        'TZ': 'Tanzania',\n",
    "        'KE': 'Kenya',\n",
    "        'MX': 'Mexico',\n",
    "        'DO': 'Dominican Republic',\n",
    "    }\n",
    "    \n",
    "    return code_dict[code]\n",
    "\n",
    "df['country'] = df['country'].apply(lambda x: convert_to_country(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "496a3372",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['United States', 'Iceland', 'Unknown', 'Canada', 'Germany',\n",
       "       'India', 'Tanzania', 'Kenya', 'Mexico', 'Dominican Republic'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "10ccf5ee",
   "metadata": {},
   "source": [
    "### Timestamp\n",
    "ts: This field is a timestamp indicating when the track stopped playing in UTC (Coordinated Universal Time). The order is year, month and day followed by a timestamp in military time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "e8d13287",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'ts': 'timestamp'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "aa079a55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2018-03-16T20:41:49Z\n",
       "1    2018-03-16T20:42:29Z\n",
       "2    2018-03-16T20:44:28Z\n",
       "3    2018-03-16T20:45:00Z\n",
       "4    2018-03-16T20:47:55Z\n",
       "Name: timestamp, dtype: object"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['timestamp'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aab03cff",
   "metadata": {},
   "source": [
    "First, change the format to datetime."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "c446ea09",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['timestamp'] = df['timestamp'].apply(lambda x: datetime.strptime(x, '%Y-%m-%dT%H:%M:%SZ'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f445d886",
   "metadata": {},
   "source": [
    "Next, I need to convert it to the relevant timezone for accurate time calculation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "6b606847",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_time_zone(stamp, country):\n",
    "    country_to_time = {\n",
    "        'United States': 'US/Eastern',\n",
    "        'Iceland': 'Atlantic/Reykjavik',\n",
    "        'Canada': 'US/Eastern',\n",
    "        'Germany': 'Europe/Berlin',\n",
    "        'India': 'Asia/Kolkata',\n",
    "        'Tanzania': 'Africa/Dar_es_Salaam',\n",
    "        'Kenya': 'Africa/Nairobi',\n",
    "        'Mexico': 'US/Eastern',\n",
    "        'Dominican Republic': 'US/Eastern'\n",
    "    }\n",
    "    \n",
    "    return stamp.astimezone(pytz.timezone('US/Eastern'))\n",
    "\n",
    "df['timestamp'] = df.apply(lambda x: convert_time_zone(x['timestamp'].replace(tzinfo=pytz.utc), x['country']), axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aa9a252",
   "metadata": {},
   "source": [
    "Now, I need to extract certain information from the timestamp:\n",
    "\n",
    "- Date\n",
    "- Year\n",
    "- Month\n",
    "- Day of week\n",
    "- Season (determined based on me living in the Northern Hemisphere)\n",
    "- Time\n",
    "- Time of day (Morning, Afternoon, Night)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "b602fb2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = df['timestamp'].dt.date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "2b8b8487",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['year'] = df['timestamp'].dt.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "50bc897b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['month'] = df['timestamp'].dt.month.apply(lambda x: calendar.month_name[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "7a499570",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['day_of_week'] = df['timestamp'].dt.date.apply(lambda x: x.strftime('%A'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "076a3e7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "def determine_season(day, month, year):\n",
    "    seasons = {\n",
    "        'Spring': ((3,20), (6,20)),\n",
    "        'Summer': ((6,21), (9,21)),\n",
    "        'Fall': ((9,22), (12, 20)),\n",
    "        'Winter': ((12,21), (3,19))\n",
    "    }\n",
    "    \n",
    "    # check for winter\n",
    "    if (month == 12 and day >= 21) or (month in (1,2)) or (month == 3 and day <= 19):\n",
    "        return \"Winter\"\n",
    "    \n",
    "    for season, (s, e) in seasons.items():\n",
    "        start = datetime(year, s[0], s[1])\n",
    "        end = datetime(year, e[0], e[1])\n",
    "        date = datetime(year, month, day)\n",
    "        \n",
    "        if start <= date <= end:\n",
    "            return season\n",
    "\n",
    "df['season'] = df['timestamp'].dt.date.apply(lambda x: determine_season(x.day, x.month, x.year))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "e891610e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['time'] = df['timestamp'].dt.time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "d21983f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def determine_time_of_day(h, m, s):\n",
    "    times_of_day = {\n",
    "        'Early Morning': (5, 8),\n",
    "        'Late Morning': (9, 12),\n",
    "        'Early Afternoon': (13, 15),\n",
    "        'Late Afternoon': (16, 17),\n",
    "        'Evening': (18, 20),\n",
    "        'Night': (21, 23),\n",
    "        'Late Night': (0, 4)\n",
    "    }\n",
    "    \n",
    "    for tod, (start, end) in times_of_day.items():\n",
    "        if start <= h <= end:\n",
    "            return tod\n",
    "\n",
    "df['time_of_day'] = df['timestamp'].dt.time.apply(lambda x: determine_time_of_day(x.hour, x.minute, x.second))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "1ecbaa2b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Early Afternoon    24182\n",
       "Late Morning       23920\n",
       "Late Afternoon     18400\n",
       "Evening            18207\n",
       "Night              13805\n",
       "Late Night          5938\n",
       "Early Morning       5519\n",
       "Name: time_of_day, dtype: int64"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['time_of_day'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67d9ad43",
   "metadata": {},
   "source": [
    "### Username\n",
    "username: This field is your Spotify username."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "8bfe1f48",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mmphieag    109971\n",
       "Name: username, dtype: int64"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['username'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6612ee7",
   "metadata": {},
   "source": [
    "This column only has one unique value and is not relevant to the analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "88e90e2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['username'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28121c28",
   "metadata": {},
   "source": [
    "### Platform\n",
    "platform: This field is the platform used when streaming the track (e.g. Android OS, Google Chromecast)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "42f6b547",
   "metadata": {},
   "outputs": [],
   "source": [
    "def determine_platform(platform):\n",
    "    \n",
    "    platform = platform.lower()\n",
    "    \n",
    "    if \"tesla\" in platform:\n",
    "        return \"Car\"\n",
    "    \n",
    "    if \"windows\" in platform or \"web\" in platform or \"osx\" in platform or \"os x\" in platform:\n",
    "        return \"Laptop\"\n",
    "    \n",
    "    if \"tizen\" in platform or \"playstation\" in platform or \"cast_tv\" in platform or \"chromecast_assistant_touch\" in platform:\n",
    "        return \"TV\"\n",
    "    \n",
    "    if \"ios\" in platform or \"android\" in platform or \"not_applicable\" in platform:\n",
    "        return \"Phone\"\n",
    "    \n",
    "    if \"google_nest_hub\" in platform or \"google_home\" in platform:\n",
    "        return \"Smart Device\"\n",
    "    \n",
    "    return platform\n",
    "\n",
    "df['platform'] = df['platform'].apply(lambda x: determine_platform(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "f45d75d8",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Laptop', 'Phone', 'Smart Device', 'TV', 'Car'], dtype=object)"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['platform'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "c1ff210d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Phone           77177\n",
       "Laptop          31215\n",
       "TV                900\n",
       "Car               448\n",
       "Smart Device      231\n",
       "Name: platform, dtype: int64"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['platform'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "255bceea",
   "metadata": {},
   "source": [
    "### Milliseconds Played\n",
    "ms_played: This field is the number of milliseconds the stream was played."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "id": "7b5a84af",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['sec_played'] = df['ms_played'].apply(lambda x: x // 1000)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9fdd6e4",
   "metadata": {},
   "source": [
    "### IP Address\n",
    "Ip_addr_decrypted: This field contains the IP address logged when streaming the track."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "3a14fccb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['ip_addr_decrypted'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "353afcaa",
   "metadata": {},
   "source": [
    "### User Agent\n",
    "user_agent_decrypted: This field contains the user agent used when streaming the track (e.g. a browser, like Mozilla Firefox, or Safari)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "868d4583",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['user_agent_decrypted'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59ccec2e",
   "metadata": {},
   "source": [
    "### Track Name\n",
    "master_metadata_track_name: This field is the name of the track."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "a8616e5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'master_metadata_track_name': 'track_name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23778e9c",
   "metadata": {},
   "source": [
    "### Artist Name\n",
    "master_metadata_album_artist_name: This field is the name of the artist, band or podcast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "452d6913",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'master_metadata_album_artist_name': 'artist_name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "921baea0",
   "metadata": {},
   "source": [
    "### Album Name\n",
    "master_metadata_album_album_name: This field is the name of the album of the track."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "386d5892",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.rename(columns={'master_metadata_album_album_name': 'album_name'}, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42faa7bc",
   "metadata": {},
   "source": [
    "### Episode Name\n",
    "episode_name: This field contains the name of the episode of the podcast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "b918b029",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['episode_name'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "ae09e9bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['episode_name'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bbf0dfa5",
   "metadata": {},
   "source": [
    "### Episode Show Name\n",
    "episode_show_name: This field contains the name of the show of the podcast."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "1fce43aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['episode_show_name'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27877a1a",
   "metadata": {},
   "source": [
    "### Spotify Episode URI\n",
    "spotify_episode_uri: A Spotify Episode URI, uniquely identifying the podcast episode in the form of “spotify:episode:<base-62 string>”"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "924e1136",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['spotify_episode_uri'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a159f0ac",
   "metadata": {},
   "source": [
    "### Reason Start\n",
    "reason_start: This field is a value telling why the track started (e.g. “trackdone”)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "a1f7748f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fwdbtn        41707\n",
       "trackdone     32775\n",
       "clickrow      22979\n",
       "backbtn        5656\n",
       "playbtn        4236\n",
       "appload        1772\n",
       "remote          623\n",
       "trackerror      158\n",
       "unknown          26\n",
       "Name: reason_start, dtype: int64"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reason_start'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "9ab295f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['reason_start'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "81a37466",
   "metadata": {},
   "source": [
    "### Reason End\n",
    "reason_end: This field is a value telling why the track ended (e.g. “endplay”)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "0e430c13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "fwdbtn                          41322\n",
       "trackdone                       33001\n",
       "endplay                         23971\n",
       "backbtn                          5574\n",
       "logout                           3820\n",
       "unexpected-exit-while-paused     1078\n",
       "remote                            390\n",
       "unknown                           306\n",
       "unexpected-exit                   110\n",
       "trackerror                         40\n",
       "Name: reason_end, dtype: int64"
      ]
     },
     "execution_count": 182,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['reason_end'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "a2504e8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['reason_end'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff5b88a4",
   "metadata": {},
   "source": [
    "### Shuffle?\n",
    "shuffle: This field has the value True or False depending on if shuffle mode was used when playing the track."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "29afd6fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     82959\n",
       "False    26973\n",
       "Name: shuffle, dtype: int64"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['shuffle'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ac244fb",
   "metadata": {},
   "source": [
    "### Skipped?\n",
    "skipped: This field indicates if the user skipped to the next song"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59d3be42",
   "metadata": {},
   "source": [
    "There seems to be an issue in the way this data was collected, so I won't consider this feature for my analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "f08ae33e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['skipped'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77d99375",
   "metadata": {},
   "source": [
    "### Offline?\n",
    "offline: This field indicates whether the track was played in offline mode (“True”) or not (“False”)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "id": "a0e4e4a2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    105345\n",
       "True       4469\n",
       "Name: offline, dtype: int64"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['offline'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe4336c2",
   "metadata": {},
   "source": [
    "### Offline Timestamp\n",
    "offline_timestamp: This field is a timestamp of when offline mode was used, if used."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "e91c5e77",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.drop(['offline_timestamp'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc4399a2",
   "metadata": {},
   "source": [
    "### Incognito Mode?\n",
    "incognito_mode: This field indicates whether the track was played during a private session (“True”) or not (“False”)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "cb64f49b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    107472\n",
       "True       2460\n",
       "Name: incognito_mode, dtype: int64"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['incognito_mode'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a97c559f",
   "metadata": {},
   "source": [
    "### Spotify URI\n",
    "spotify_track_uri: A Spotify URI, uniquely identifying the track in the form of “spotify:track:<base-62 string>”\n",
    "A Spotify URI is a resource identifier that you can enter, for example, in the Spotify Desktop client’s search box to locate an artist, album, or track."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "8713c89e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['spotify_track_uri'].isna() == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "id": "5a4cd9c1",
   "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>timestamp</th>\n",
       "      <th>platform</th>\n",
       "      <th>ms_played</th>\n",
       "      <th>country</th>\n",
       "      <th>track_name</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>album_name</th>\n",
       "      <th>spotify_track_uri</th>\n",
       "      <th>shuffle</th>\n",
       "      <th>offline</th>\n",
       "      <th>incognito_mode</th>\n",
       "      <th>date</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>season</th>\n",
       "      <th>time</th>\n",
       "      <th>time_of_day</th>\n",
       "      <th>sec_played</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-03-16 16:41:49-04:00</td>\n",
       "      <td>Laptop</td>\n",
       "      <td>45010</td>\n",
       "      <td>United States</td>\n",
       "      <td>No Role Modelz</td>\n",
       "      <td>J. Cole</td>\n",
       "      <td>2014 Forest Hills Drive</td>\n",
       "      <td>spotify:track:62vpWI1CHwFy7tMIcSStl8</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>2018</td>\n",
       "      <td>March</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Winter</td>\n",
       "      <td>16:41:49</td>\n",
       "      <td>Late Afternoon</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-03-16 16:42:29-04:00</td>\n",
       "      <td>Laptop</td>\n",
       "      <td>36270</td>\n",
       "      <td>United States</td>\n",
       "      <td>No Role Modelz</td>\n",
       "      <td>J. Cole</td>\n",
       "      <td>2014 Forest Hills Drive</td>\n",
       "      <td>spotify:track:62vpWI1CHwFy7tMIcSStl8</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>2018</td>\n",
       "      <td>March</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Winter</td>\n",
       "      <td>16:42:29</td>\n",
       "      <td>Late Afternoon</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-03-16 16:44:28-04:00</td>\n",
       "      <td>Laptop</td>\n",
       "      <td>27800</td>\n",
       "      <td>United States</td>\n",
       "      <td>No Role Modelz</td>\n",
       "      <td>J. Cole</td>\n",
       "      <td>2014 Forest Hills Drive</td>\n",
       "      <td>spotify:track:62vpWI1CHwFy7tMIcSStl8</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>2018</td>\n",
       "      <td>March</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Winter</td>\n",
       "      <td>16:44:28</td>\n",
       "      <td>Late Afternoon</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-03-16 16:45:00-04:00</td>\n",
       "      <td>Laptop</td>\n",
       "      <td>31060</td>\n",
       "      <td>United States</td>\n",
       "      <td>Chandler Road (Instrumental Remix)</td>\n",
       "      <td>Sbvce</td>\n",
       "      <td>Sbvce Pvck: Bedroomtrap Vol. 1</td>\n",
       "      <td>spotify:track:0gzEuWfP1nlbw7Hhzdpy9g</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>2018</td>\n",
       "      <td>March</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Winter</td>\n",
       "      <td>16:45:00</td>\n",
       "      <td>Late Afternoon</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-03-16 16:47:55-04:00</td>\n",
       "      <td>Laptop</td>\n",
       "      <td>3870</td>\n",
       "      <td>United States</td>\n",
       "      <td>I. The Worst Guys</td>\n",
       "      <td>Childish Gambino</td>\n",
       "      <td>Because The Internet</td>\n",
       "      <td>spotify:track:464V1P4EY1xgbJn3bu1TOu</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>2018-03-16</td>\n",
       "      <td>2018</td>\n",
       "      <td>March</td>\n",
       "      <td>Friday</td>\n",
       "      <td>Winter</td>\n",
       "      <td>16:47:55</td>\n",
       "      <td>Late Afternoon</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  timestamp platform  ms_played        country  \\\n",
       "0 2018-03-16 16:41:49-04:00   Laptop      45010  United States   \n",
       "1 2018-03-16 16:42:29-04:00   Laptop      36270  United States   \n",
       "2 2018-03-16 16:44:28-04:00   Laptop      27800  United States   \n",
       "3 2018-03-16 16:45:00-04:00   Laptop      31060  United States   \n",
       "4 2018-03-16 16:47:55-04:00   Laptop       3870  United States   \n",
       "\n",
       "                           track_name       artist_name  \\\n",
       "0                      No Role Modelz           J. Cole   \n",
       "1                      No Role Modelz           J. Cole   \n",
       "2                      No Role Modelz           J. Cole   \n",
       "3  Chandler Road (Instrumental Remix)             Sbvce   \n",
       "4                   I. The Worst Guys  Childish Gambino   \n",
       "\n",
       "                       album_name                     spotify_track_uri  \\\n",
       "0         2014 Forest Hills Drive  spotify:track:62vpWI1CHwFy7tMIcSStl8   \n",
       "1         2014 Forest Hills Drive  spotify:track:62vpWI1CHwFy7tMIcSStl8   \n",
       "2         2014 Forest Hills Drive  spotify:track:62vpWI1CHwFy7tMIcSStl8   \n",
       "3  Sbvce Pvck: Bedroomtrap Vol. 1  spotify:track:0gzEuWfP1nlbw7Hhzdpy9g   \n",
       "4            Because The Internet  spotify:track:464V1P4EY1xgbJn3bu1TOu   \n",
       "\n",
       "   shuffle  offline  incognito_mode        date  year  month day_of_week  \\\n",
       "0    False    False           False  2018-03-16  2018  March      Friday   \n",
       "1    False    False           False  2018-03-16  2018  March      Friday   \n",
       "2    False    False           False  2018-03-16  2018  March      Friday   \n",
       "3    False    False           False  2018-03-16  2018  March      Friday   \n",
       "4    False    False           False  2018-03-16  2018  March      Friday   \n",
       "\n",
       "   season      time     time_of_day  sec_played  \n",
       "0  Winter  16:41:49  Late Afternoon          45  \n",
       "1  Winter  16:42:29  Late Afternoon          36  \n",
       "2  Winter  16:44:28  Late Afternoon          27  \n",
       "3  Winter  16:45:00  Late Afternoon          31  \n",
       "4  Winter  16:47:55  Late Afternoon           3  "
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  }
 ],
 "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.10.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}