Spotify_data_in_Python.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyOTHF6FIhPAxiyEMotyp5ua",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/bmill42/streaming-data/blob/main/Spotify_data_in_Python.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Introduction to manipulating data in Python\n",
        "\n",
        "To work with data in Python, we'll need to bring in some extra functions that aren't available by default. External functions are stored in a *library* that we make available in our current environment using the `import` keyword.\n",
        "\n",
        "The library that's typically used for data science in Python is called `pandas`. When we import it, it's customary to give it a shorter name by using `as`. This is just a convention that most programmers use - it doesn't affect how the code works.\n",
        "\n",
        "Our Spotify data is stored in Google Drive, and this Colab notebook can't access your Drive files without special permission. Google provides a lot of custom libraries for working with their various products, and we'll import the one called `drive`, which lets us load and save files in code just like we normally do through the website.\n",
        "\n",
        "The code for this looks slightly different from the code for importing pandas: `from x import y`, but it's doing the same thing; we're just specifying that we don't need to import the code for all the other Google Colab libraries, only the one for interacting with Drive."
      ],
      "metadata": {
        "id": "FeJPyl3eBo7I"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "VnqU6WeMtzbo"
      },
      "outputs": [],
      "source": [
        "import pandas as pd\n",
        "from google.colab import drive"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Connect to Google Drive\n",
        "\n",
        "This line of code calls a function that comes from the Drive library. We don't need to worry too much about how it works, just know that running this cell will give you a sign-in prompt from Google Drive, and you need to confirm that you're allowing the notebook to connect to your files."
      ],
      "metadata": {
        "id": "3r7WSyqG6CHq"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "drive.mount('/content/drive')"
      ],
      "metadata": {
        "id": "R_olPZEo6EnY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Loading data using pandas\n",
        "\n",
        "The main feature of pandas is a data structure called a **dataframe**, which is a table with rows and columns (think of an Excel file) that can be manipulated in many useful ways.\n",
        "\n",
        "To create a dataframe, we need to load some data, and pandas can take in data in many different file formats. The most common formats are CSV or Excel files, but our Spotify data comes in the form of a JSON file. JSON stands for JavaScript Object Notation, and it can store data in a variety of flexible ways. The Spotify data contains a bunch of blocks of text that look like this:\n",
        "\n",
        "```\n",
        "{\n",
        "    \"ts\": \"2013-02-02T04:56:53Z\",\n",
        "    \"username\": \"1265420670\",\n",
        "    \"platform\": \"OS X 10.8.2 [x86 4]\",\n",
        "    \"ms_played\": 383493,\n",
        "    \"conn_country\": \"US\",\n",
        "    \"ip_addr_decrypted\": \"99.191.69.97\",\n",
        "    \"user_agent_decrypted\": \"unknown\",\n",
        "    \"master_metadata_track_name\": \"Paranoid Android\",\n",
        "    \"master_metadata_album_artist_name\": \"Radiohead\",\n",
        "    \"master_metadata_album_album_name\": \"OK Computer\",\n",
        "    \"spotify_track_uri\": \"spotify:track:1U8ZGtWBxcPeWcwDTCO64X\",\n",
        "    \"episode_name\": null,\n",
        "    \"episode_show_name\": null,\n",
        "    \"spotify_episode_uri\": null,\n",
        "    \"reason_start\": \"trackdone\",\n",
        "    \"reason_end\": \"trackdone\",\n",
        "    \"shuffle\": false,\n",
        "    \"skipped\": false,\n",
        "    \"offline\": false,\n",
        "    \"offline_timestamp\": 0,\n",
        "    \"incognito_mode\": false\n",
        "  }\n",
        "```\n",
        "\n",
        "In between the curly braces `{}`, we have a list of **keys** in quotation marks paired with **values**, separated by colons.\n",
        "\n",
        "While it doesn't quite look like it now, this data is already formatted like a table. To load it into pandas, we create a new variable (it's common to call the main dataset we're working with `df`, for \"data frame\", though it's often a good idea to use a more descriptive name); and we assign that variable using a function called `pd.read_json()`, which tells Python to look inside the pandas library for a function called `read_json`.\n",
        "\n",
        "Functions usually take in some kind of arguments within the parentheses, and `read_json()` typically takes in the **filepath** to the JSON file we want to turn into a dataframe. This filepath is normally for a location on the computer that is running Python, and since we've loaded up the Google Drive library, our notebook can see a special folder called `/content/drive/MyDrive` that contains all of our Google Drive files. The rest of the path points to wherever you stored your Spotify data, including the filename.\n",
        "\n",
        "**Modify this path to point to the data stored in YOUR Google Drive.** Even though you're using my sample data for the exercises, your filepath may be different depending on how your Drive is organized."
      ],
      "metadata": {
        "id": "ERbazvVdtWzj"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df = pd.read_json('/content/drive/MyDrive/COMPFOR 304/Data/BAM - Streaming_History_Audio_2013-2024.json')\n",
        "#df = pd.read_json('/content/drive/MyDrive/PATH TO YOUR DATA')"
      ],
      "metadata": {
        "id": "46XTk8BDuOc4"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Examining the data\n",
        "\n",
        "We can see the contents of the variable that we just created by running a notebook cell with only the variable's name in it."
      ],
      "metadata": {
        "id": "TEpbsAAY2Ir5"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df"
      ],
      "metadata": {
        "id": "3lj9NeoGuvAV"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now we can see the correspondence between the JSON format's key-value pairs and the column-row format of the dataframe. Every *key* has now become a *column*, and each *value* becomes an entry in a *row*.\n",
        "\n",
        "In fact, everything that fell between a pair of curly braces in the JSON file is now part of the same row in the dataframe - which in turn represents a single listening event."
      ],
      "metadata": {
        "id": "GsmZYvjU6mtJ"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Getting a better look at the data\n",
        "\n",
        "If you've listened to more than 20 or so tracks in your time on Spotify, you'll probably find that only the first five rows appear, followed by an ellipsis (...) and then the last five rows.\n",
        "\n",
        "More frustratingly, our Spotify data has a lot of columns, and not all of them can fit on the screen. If you scroll to the right, you'll see an ellipsis in the list of column names at the top, indicating that some columns have been left out of the display.\n",
        "\n",
        "There are many ways to get a better look at the whole table. One is to simply export it as a file that we can open in Excel or Google Sheets. Just as we can load data into pandas using functions with the name `read_<format>`, we can save files using `to_<format>`.\n",
        "\n",
        "Since `df` is the object that we want to save, we call the function as `df.to_csv('filename')`."
      ],
      "metadata": {
        "id": "LpI6qBB5623W"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.to_csv('output.csv')"
      ],
      "metadata": {
        "id": "7KAF3LOsu_QX"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The file that we saved can be found in the file browser on the left - click the icon that looks like a folder, then click the dots to the right of the file and choose 'Download'. You can then open this file in any spreadsheet editor. Feel free to try it out.\n",
        "\n",
        "## Examining a dataset using code\n",
        "\n",
        "Using a separate spreadsheet program is generally the best way to look at a *whole* table, but there are many useful ways to look at *subsets* of the table using code. We will look at a few of the most useful methods, and [this page in the pandas documentation](https://pandas.pydata.org/docs/getting_started/intro_tutorials/03_subset_data.html) is a good place to find more.\n",
        "\n",
        "Pandas includes functions called `head()` and `tail()` that will show us the first or last few rows of the table. You'll get five rows by default, or you can provide a number as an argument to get that number of rows."
      ],
      "metadata": {
        "id": "ZnE3Fh85BsUa"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.head()"
      ],
      "metadata": {
        "id": "K-yJ_-zd9Nsb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "df.tail(2)"
      ],
      "metadata": {
        "id": "Hvswwx9AG3OO"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "We can also select a set of rows from anywhere in the table (not just the beginning or end). One way to do this is to just use the normal approach for **indexing** a Python list, using square brackets and a range specified with a start and end (keeping in mind that Python calls the first item `0`, not `1`).\n",
        "\n",
        "For example, to choose (or *slice*) the first 5 items from a list, we'd say `list_name[0:5]`, and likewise to get rows 10-15 of a dataframe we'd say `df[9:15]`. See the numbers going from 0 to 4 down the lefthand side of the `df.head()` results above? Those numbers give the **index** for each row, and it's those indices that we're asking for when we say something like `df[0:5]`.\n",
        "\n",
        "We can do the same thing in a way that plays slightly better with other pandas features using a method called `iloc`, which basically means \"*index* at a certain *location*\". We can give `iloc` a list or a range of numbers, and we'll get back the rows corresponding to those index numbers."
      ],
      "metadata": {
        "id": "032oYleLbpv9"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.iloc[0:15]"
      ],
      "metadata": {
        "id": "Zn1m1WOFHBib"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Dealing with columns\n",
        "\n",
        "We'll get to more in-depth approaches to selecting rows later (like selecting based on the *values* in the data rather than just row numbers). But we also need to get a handle on all those columns, which don't even fit on the screen in the default view.\n",
        "\n",
        "This is particularly important because this dataset, like many that we might encounter in real-world settings, contains a lot of columns that just aren't useful to us. Look at that second column, `username` - that seems to be some kind of number corresponding to your Spotify account, but since all of the data in this file is for the same user, that column doesn't tell us anything new and it's taking up space on screen and in the computer's memory.\n",
        "\n",
        "Now look at the third column, `platform`. This one tells you something about the kind of system you were listening on, with values like `OS X` (a Mac desktop/laptop computer) or `ios` (an iPhone or iPad). This may be very helpful to us if we're trying to understand what our musical preferences are like while walking to school or exercising, because we could drop all the rows that aren't associated with a mobile device. On the other hand, if you've only ever listened to Spotify on your phone, or if you just want to study your listening history regardless of what platform you used, then this column is also just taking up space.\n",
        "\n",
        "The first thing we might want to do in order to get a handle on our columns is just to see what they are, since not all of them fit on the screen (hence the ellipsis).\n",
        "\n",
        "Luckily, each dataframe has an attribute called `columns` that we can ask for like this:"
      ],
      "metadata": {
        "id": "5Xl-WIike1Vo"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.columns"
      ],
      "metadata": {
        "id": "G2P2ZFV1bwOq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "The result isn't the easiest thing to read, but it's just a list of all the column names (you can ignore the extra information, like `Index` and `dtype`). If we want a slightly nicer version, we can use a `for` loop:"
      ],
      "metadata": {
        "id": "C39olvoYjidw"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "for col in df.columns:\n",
        "  print(col)"
      ],
      "metadata": {
        "id": "6A78l6-CjhUv"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "*Here's a hint:* instead of typing out those long column names later on, you can just copy and paste them from this list.\n",
        "\n",
        "Some of these names are a bit cryptic, and we can learn what all of these column names refer to using the documentation that came with our Spotify data, but for now we'll play around with some of the most obviously useful ones.\n",
        "\n",
        "While much of the value of a dataframe is in the way it associates different pieces of information (e.g. at X time I listened to Y artist for Z seconds), we can get a surprising amount of information from just a single column.\n",
        "\n",
        "To get a column by itself, we use square brackets with the name of the column in quotes, like this:"
      ],
      "metadata": {
        "id": "s7afemESj2Nl"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['master_metadata_album_artist_name']"
      ],
      "metadata": {
        "id": "6VdDwGBjjvZM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "As an alternative, we can get the same thing with less typing by using a dot (`.`) to specify the column name: `df.<column>`. Note that this *only* works when the column name has no spaces in it! If there are spaces, we have to use the square brackets and quotations marks as above."
      ],
      "metadata": {
        "id": "T445pFniZj3z"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.master_metadata_album_artist_name"
      ],
      "metadata": {
        "id": "grilAJUBZ8pp"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "If we want more than one column - say, the date and time the track was played (`ts` for 'timestamp', more on this later) along with the track name - we can provide a list of column names. *Note the double square brackets*, since we're putting a list (which is always surrounded by square brackets) inside the regular square brackets that go after the dataframe's name when we want to index into it:"
      ],
      "metadata": {
        "id": "qOT3Xw4KHDvI"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df[['ts', 'master_metadata_track_name']]"
      ],
      "metadata": {
        "id": "SYlzyls9lur0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# More advanced data manipulation in pandas"
      ],
      "metadata": {
        "id": "Ywc3pdI4rp8q"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Group By: a key data analysis technique"
      ],
      "metadata": {
        "id": "-2wc7F4huwsn"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "To start digging into the data further, we'll need to transform it in various ways, typically with the goal of summarizing some aspect of it. For example, how many times have I listened to each artist over all the years I've used Spotify?"
      ],
      "metadata": {
        "id": "Agpfaj-70Hmf"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "One of the most useful transformations we can perform is called  **group by** - we'll divide the data into groups that all have the same value for one column. Once the data has been grouped, we can easily do things like count how many items are in each group."
      ],
      "metadata": {
        "id": "hqV5jA7a0n2x"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "I want to know how many times I've listened to each artist, so I'll start by making a new column called `num_plays` and set it to `1`. After all, each row in the dataset is one play of one track. To create a column, we provide the name of the new column in quotes, then provide the value."
      ],
      "metadata": {
        "id": "Mxgyo9z41c_d"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['num_plays'] = 1"
      ],
      "metadata": {
        "id": "GWTbLN-I11HI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now we apply the **group by** operation. This involves using the `groupby` method with one argument: the name of the column to serve as the basis for the groups, in this case `master_metadata_album_artist_name`. It's helpful here to create a new variable that will store this transformed table so that we don't lose the original version.\n",
        "\n",
        "It's most common to go ahead and apply an *aggregation method* at this point. In other words, what do we want to do with the groups? Here, we want to count how many items are in each one - how many lines in the dataset are there for each artist? The aggregation method we want is called `sum()`."
      ],
      "metadata": {
        "id": "nfZpUjEB12qU"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_artists = df.groupby('master_metadata_album_artist_name').sum()\n",
        "df_artists"
      ],
      "metadata": {
        "id": "6UrOZ96BINQq"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now, we see all the artist names on the left side of the table - these are the groups. Some of the columns looks a little strange now, but if we look all the way to the right, we can see that `num_plays` is no longer just `1`; instead, it's the count of how many times that artist appeared."
      ],
      "metadata": {
        "id": "NRlyG36922c-"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's get rid of all those extra columns since we only care about the number of plays per artist. We'll just set the new dataframe, `df_artists`, to itself, but with only one column selected, just like in the earlier examples."
      ],
      "metadata": {
        "id": "MJTGxPE5tvZR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_artists = df_artists[['num_plays']]"
      ],
      "metadata": {
        "id": "f8KYUAletv6n"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "This looks a lot cleaner, but the order isn't very meaningful. Since we care about which artist I've listened to the most, we need to sort by the `num_plays`. The method `sort_values()` will do precisely this, taking a column name as an argument."
      ],
      "metadata": {
        "id": "kqdmQrEv31AU"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_artists.sort_values('num_plays')"
      ],
      "metadata": {
        "id": "kSBxV45ka1Mk"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "`sort_values()` can also take an argument called `ascending`, which can either be `True` (sort from lowest to highest) or `False` (sort from highest to lowest). To see what extra arguments a method can take, just look at the [documentation](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sort_values.html)."
      ],
      "metadata": {
        "id": "zK-1-tHFuVov"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df_artists.sort_values('num_plays', ascending=False)"
      ],
      "metadata": {
        "id": "6NjZD30htIcZ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Finally, let's clean this up even further and look at only my top ten artists. For this, we can combine our sort method with an indexing method like `iloc[]`. This process of chaining together multiple methods is very common - the methods will execute from left to right, so `iloc` will slice the *result* of `sort_values`.\n",
        "\n",
        "To enshrine thist list a bit more permanently, we'll also make a new variable for it called `top_artists`."
      ],
      "metadata": {
        "id": "-Q9mFGlV48xO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "top_artists = df_artists.sort_values('num_plays', ascending=False).iloc[0:10]\n",
        "top_artists"
      ],
      "metadata": {
        "id": "gBy8meXuuMeU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Filtering data"
      ],
      "metadata": {
        "id": "xCr-pM5_rH2T"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Looking back at the full dataset (`df`), what's the deal with that column called `reason_end`? In the preview tables up above, we can basically see one value: `trackdone`, which seems to suggest that this is telling us how or why the track in that row stopped playing.\n",
        "\n",
        "The documentation that came with the data should be the definitive source for definitions of terms used in the data, but we can also find out quite a bit just by exploring the data itself. The method `unique()` is particularly helpful because it will give us a list of all the distinct values that appear in a particular column. What other reasons are there for a track to end?"
      ],
      "metadata": {
        "id": "5F1pFcaOtDkH"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.reason_end.unique()"
      ],
      "metadata": {
        "id": "yvfaBTRlwVQo"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "It appears that `fwdbtn` describes tracks that were skipped using the forward button in the app. Let's **filter** the data so that we can see a list of only these tracks to get an idea of what kind of music I chose to skip.\n",
        "\n",
        "We previously used `iloc[]` to select rows based on their numerical index; to select based on particular **conditions**, we'll use a method called `loc[]`.\n",
        "\n",
        "Inside the square brackets, we craft a statement that is either `True` or `False` for each row, using operators like `==`, `<`, `>`, `<=`, `>=`, etc. For example, to get all the rows for tracks that ended because I pressed the forward button, we'd write:"
      ],
      "metadata": {
        "id": "jSEJH5pitgEB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.loc[df.reason_end == 'fwdbtn']"
      ],
      "metadata": {
        "id": "-qG8UHA9sl_K"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Notice how we refer to column names in the conditional statement using `dataframe_name.column_name`. Spelling and capitalization are crucial here - pandas will only return *exact* matches, so if your query isn't returning what you expect, double check the spelling of column names and unusual data values like `fwdbtn`."
      ],
      "metadata": {
        "id": "FBLG3M6Kk4tT"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "We got 211 tracks from our query. If we look closely, it seems like the column `skipped` is always `1` for these tracks, which makes sense, though it might be worth verifying that these two values *always* appear together."
      ],
      "metadata": {
        "id": "StgVVZ_uvfxh"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Getting all plays for a single artist\n",
        "\n",
        "One very useful things we can do with conditional indexing is to select all the rows for tracks by a single artist. Let's look at all the times I listened to Mitski. We already know from the group by exercise how many rows should appear in the resulting table:"
      ],
      "metadata": {
        "id": "l0-z5YX4vuC2"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.loc[df.master_metadata_album_artist_name == 'Mitski']"
      ],
      "metadata": {
        "id": "Wu3UTu96tS9a"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Working with dates and times\n",
        "\n",
        "That `ts`, or **timestamp**, column is the key to answering a lot of interesting questions about the data. What artists do I tend to listen to in the morning? After 9pm? How did my listening change from year to year?\n",
        "\n",
        "To get there, we need to break the timestamp down into a more usable format. Right now it's just a string of characters:"
      ],
      "metadata": {
        "id": "9wRmrNtLwC1p"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.ts"
      ],
      "metadata": {
        "id": "fia4yrOgvsFP"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "While we could use string manipulation techniques to pull out individual elements (e.g. the first four characters always contain the year), pandas comes with powerful tools for parsing (separating into individual components) and manipulating dates and times. The first step is to convert the timestamp into a special pandas type called a **datetime**, which uses the function `pd.to_datetime()`.\n",
        "\n",
        "We'll create a new column called `datetime` to contain the resulting information:"
      ],
      "metadata": {
        "id": "kdlcnsDmxQgB"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['datetime'] = pd.to_datetime(df.ts)"
      ],
      "metadata": {
        "id": "hzP9bn2swLPA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "A datetime column contains all of the information about the time and date stored there in a way that allows us to access the individual components. For example, we can get the hour using `.dt.hour`:"
      ],
      "metadata": {
        "id": "NrBdSE9ExToM"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.datetime.dt.hour"
      ],
      "metadata": {
        "id": "FJFM1nsqwik1"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now we can easily get all of the tracks played after 9pm:"
      ],
      "metadata": {
        "id": "NkHBheRhuQXp"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.loc[df.datetime.dt.hour >= 21] # 9pm is 21 on a 24-hour clock"
      ],
      "metadata": {
        "id": "uIcq1AkXuWwM"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "See the [documentation](https://pandas.pydata.org/docs/reference/api/pandas.Series.dt.year.html) for more examples of basic datetime attributes (year, month, day, hour, etc.)\n",
        "\n",
        "Pandas also has special methods that make it easy to do things like select particular days of the week, which would be great for studying the difference between weekend and weekday listening:"
      ],
      "metadata": {
        "id": "U2ZidOC_ulxc"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.datetime.dt.day_name()"
      ],
      "metadata": {
        "id": "OJjSABmEvEhd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "It's easy to combine multiple conditions when filtering with `loc[]`. Just put individual conditions in parentheses `()` and combine conditions with `&` (and) or `|` (or).\n",
        "\n",
        "For example, let's find all the tracks I listened to on Sundays after 9pm:"
      ],
      "metadata": {
        "id": "RCSDNm_Yxkbi"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df.loc[(df.datetime.dt.day_name() == 'Sunday') & (df.datetime.dt.hour >= 21)]"
      ],
      "metadata": {
        "id": "w5K9MdVBwsnU"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Finally, it's sometimes helpful to add new columns that are transformed versions of existing ones. This can help us resolve an issue with our current data: *Spotify provides all of its timestamps in UTC, or coordinated universal time,* which means that our attempts to look at songs played after 9pm were not really accurate. Let's make a new column that converts the original timestamp to US Eastern time using the method `dt.tz_convert()`.\n",
        "\n",
        "As when we previously added the column `num_plays`, we just use square brackets and quotes to declare the name of the new column, then describe the new value:"
      ],
      "metadata": {
        "id": "-5dZRJksXH8o"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['datetime_est'] = df.datetime.dt.tz_convert('US/Eastern')\n",
        "df"
      ],
      "metadata": {
        "id": "kqWmyNfvX8hY"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Just for practice manipulating data to make new columns, let's also create a column containing the minute of the day represented by our new Eastern timestamp (e.g. 12am is minute 1 of the day, 11:59pm is minute 1,440). Note how we can do simple arithmetic on column names (or derivations like the `dt` methods) and pandas will calculate these values for each row separately:"
      ],
      "metadata": {
        "id": "1NlP_2Fpf8wP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "df['minute_of_day'] = df.datetime_est.dt.minute + (60 * df.datetime_est.dt.hour)\n",
        "df"
      ],
      "metadata": {
        "id": "fznlyDOwgYye"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Exercises"
      ],
      "metadata": {
        "id": "_pDINLi_1LcM"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 1"
      ],
      "metadata": {
        "id": "EEmBciCu7e-x"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "The dataset has a column called `shuffle` that specifies whether a playlist or album was playing in random order or not. Its values are either `True` or `False` (as a Python boolean type, there are no quotation marks around these)."
      ],
      "metadata": {
        "id": "-31e66uI4zLl"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "To explore this further, let's zero in on the columns that will be most useful.\n",
        "\n",
        "**In this cell, create a new variable** called `df_simplified` that contains only the columns `master_metadata_album_artist_name`, `shuffle`, and `num_plays` (which we created ourselves earlier):"
      ],
      "metadata": {
        "id": "Nj5e7V8053Uf"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# code here"
      ],
      "metadata": {
        "id": "WN29h7S3zStS"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Now, create another new variable** called `df_shuffle` that contains only the tracks from `df_simplified` where shuffle is `True`:"
      ],
      "metadata": {
        "id": "EXk57aSo6vzu"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# code here"
      ],
      "metadata": {
        "id": "lXIVWRIm3_OJ"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "**Now, group the data** by artist name and sum the `num_plays` for each artist. Sort the resulting list and display only the top 10 shuffled artists."
      ],
      "metadata": {
        "id": "C2BIUYr-7IbG"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# code here"
      ],
      "metadata": {
        "id": "3M6fWgRl6-C0"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Exercise 2"
      ],
      "metadata": {
        "id": "rBg3866R7jLh"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Now let's find out which artists have the longest songs on average. **Write code to carry out the following steps:**\n",
        "\n",
        "1. Create a new column, `m_played`, in `df` that converts `ms_played` into the duration of the tracked played in seconds.\n",
        "2. Keep only the rows for tracks that played to the end (look at the `reason_end` column). Now we know that the duration of the playback is equal to the full length of the track\n",
        "3. Keep only the columns for the artist name, track name and number of seconds played\n",
        "4. Drop duplicates so that we don't count any tracks more than once. You can use the method `drop_duplicates()`\n",
        "5. Group by artist name, and use the `mean()` method to calculate the average track duration per artist\n",
        "6. Look at the [list of aggregation functions](https://pandas.pydata.org/docs/reference/groupby.html#dataframegroupby-computations-descriptive-stats) that can go with `groupby()`. Do you see anything that might be more interesting or useful than `mean()` for thinking about how track length varies from one artist to the next? There are a lot of options - you might focus on the part of the list beginning with `max()`. Write your answer in a new text cell and include any relevant code in a code cell.\n",
        "\n",
        "It's possible to do most of this in one or two lines of code, but please use a separate line for each numbered step. The code can all be in one cell or split across multiple."
      ],
      "metadata": {
        "id": "KJU_0Te47lzL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "# write your code here (feel free to use multiple cells)"
      ],
      "metadata": {
        "id": "Ix17leXPWa7F"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}