Adding the Spotify Songs dataset to a database

This week, I’ll be using the Tidy Tuesday Spotify Songs dataset to practice working with SQL a bit. If you’re curious, the link to the dataset has more information on how this was gathered too.

Let’s start by getting the data and setting up.

0. Data and setup

# Packages needed 
import pandas as pd
import sqlite3
from sqlalchemy import create_engine

# Read in spotify dataset from github url
spotify_df = pd.read_csv("https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2020/2020-01-21/spotify_songs.csv")

Let’s also create a SQLite3 database on disk to use to store our data later. Note: if you don’t have sqlmagic, you may need to install it first as follows:

! python3 -m pip install --quiet ipython-sql

%load_ext sql

Then, you can make a new database (or connect to it, if it already exists):

%sql sqlite:///data/spotify.db

1. Normalize data to Third Normal Form

Before we populate our database, it’s good to normalize it; this enables us to remove redundancies and prevent inconsistencies later.

1.1 Normalize data into First Normal Form

First, let’s get the data into First Normal Form (1NF). This means we want the data table (at least for now, let’s consider this to be spotify_df) to have:

1. A primary key (a unique, non-null column identifying each row).
2. No repeating groups of columns
3. Each cell contains a single value

1.1.1 Identify or create primary key

Let’s start by looking at the columns available:

spotify_df.columns

Index([‘track_id’, ‘track_name’, ‘track_artist’, ‘track_popularity’, ‘track_album_id’, ‘track_album_name’, ‘track_album_release_date’, ‘playlist_name’, ‘playlist_id’, ‘playlist_genre’, ‘playlist_subgenre’, ‘danceability’, ‘energy’, ‘key’, ‘loudness’, ‘mode’, ‘speechiness’, ‘acousticness’, ‘instrumentalness’, ‘liveness’, ‘valence’, ‘tempo’, ‘duration_ms’], dtype=’object’)

At first glance, it seems like track_id might be a good contender for a primary key, but let’s confirm its values are unique and non-null for each row:

# Check if length of unique track_id values is equal to number of rows 
print("Is there a unique id per row?")
spotify_df.shape[0] == len(spotify_df["track_id"].unique())

Is there a unique id per row?

False

Hm, that doesn’t seem to be the case. Let’s look at one duplicated track_id to get a better sense for what a reasonable primary key could be:

# Get first track_id that's duplicated
first_duplicated_track_id = (spotify_df.
                             loc[spotify_df.duplicated(subset=['track_id'])]["track_id"].
                             iloc[0])

# Inspect rows of duplicated track_id
spotify_df.loc[spotify_df["track_id"] == first_duplicated_track_id]

Ok– it seems like the track_id is redundant here because it’s on multiple playlists. We could combine the track_id with playlist_id to create a unique ID per row, but considering that they have to do with different things it probably makes more sense to just split the data frame into two tables (track and playlist) and allocate relevant columns accordingly. So, let’s do that:

spotify_df.columns

Index([‘track_id’, ‘track_name’, ‘track_artist’, ‘track_popularity’, ‘track_album_id’, ‘track_album_name’, ‘track_album_release_date’, ‘playlist_name’, ‘playlist_id’, ‘playlist_genre’, ‘playlist_subgenre’, ‘danceability’, ‘energy’, ‘key’, ‘loudness’, ‘mode’, ‘speechiness’, ‘acousticness’, ‘instrumentalness’, ‘liveness’, ‘valence’, ‘tempo’, ‘duration_ms’], dtype=’object’)

# columns relevant to track
track_columns = ['track_id', 
                 'track_name', 
                 'track_artist', 
                 'track_popularity',
                 'track_album_id', 
                 'track_album_name', 
                 'track_album_release_date',
                 'danceability', 
                 'energy', 
                 'key', 
                 'loudness', 
                 'mode', 
                 'speechiness',
                 'acousticness', 
                 'instrumentalness', 
                 'liveness', 
                 'valence', 
                 'tempo',
                 'duration_ms'] 

# columns relevant to playlist 
playlist_columns = ['playlist_id',
                    'playlist_name', 
                    'playlist_genre', 
                    'playlist_subgenre']

# Create a dictionary of 2 dataframes (track_df and playlist_df)
spotify_df_dict = {'track' : spotify_df.loc[:,track_columns].drop_duplicates(),
                  'playlist' : spotify_df.loc[:,playlist_columns].drop_duplicates()}

Now let’s confirm that track_id and playlist_id are unique and non-null within each DataFrame:

# Check track_id is unique per row in track df
print("TRACK:")
print("Is there a unique id per row?")
print(spotify_df_dict["track"].shape[0] == len(spotify_df_dict["track"]["track_id"].unique()))

# Check track_id has no NA values
print("Are there NA values?")
print(spotify_df_dict["track"]["track_id"].isnull().any())

# Check playlist_id is unique per row in playlist df
print("\nPLAYLIST:")
print("Is there a unique id per row?")
print(spotify_df_dict["playlist"].shape[0] == len(spotify_df_dict["playlist"]["playlist_id"].unique()))

# Check playlist_id has no NA values
print("Are there NA values?")
print(spotify_df_dict["playlist"]["playlist_id"].isnull().any())

TRACK: Is there a unique id per row? True Are there NA values? False

PLAYLIST: Is there a unique id per row? False Are there NA values? False

Seems like playlist_id still isn’t unique. Let’s look more closely at the rows where playlist_id is duplicated:

duplicated_playlist_ids = (spotify_df_dict["playlist"].
 loc[spotify_df_dict["playlist"].duplicated(subset=['playlist_id'])]["playlist_id"])

spotify_df_dict["playlist"][spotify_df_dict["playlist"]["playlist_id"].
                            isin(duplicated_playlist_ids)].sort_values(by=["playlist_id"])

The issue seems to be that some playlists are listed under two subgenres. We want to keep a single value per column so combining the redundant subgenres isn’t a good idea. So, let’s instead just create a new unique id for the playlist dataframe:

spotify_df_dict["playlist"]["playlist_uid"] = list(range(0, spotify_df_dict["playlist"].shape[0]))

# Check playlist_uid is unique per row in playlist df
print("Is there a unique id per row?")
print(spotify_df_dict["playlist"].shape[0] == len(spotify_df_dict["playlist"]["playlist_uid"].unique()))

# Check playlist_uid has no NA values
print("Are there NA values?")
print(spotify_df_dict["playlist"]["playlist_uid"].isnull().any())

Is there a unique id per row? True Are there NA values? False

Great! Moving on.

1.1.2 Check that there are no repeating columns

For the track DataFrame, none of the columns seem to repeat:

spotify_df_dict["track"].head()

For the playlist DataFrame (since we added a unique identifying column), there are no repeating groups of columns:

spotify_df_dict["playlist"].head()

1.1.3 Each cell contains a single value

From our inspection of the DataFrames in 1.2, we see that each cell contains a single value; so, this requirement is satisfied.

2. Normalize data into Second Normal Form

Now we’re ready to get our data into Second Normal form (“2NF”). This means we want each table to have all columns in each row to depend fully on candidate keys. Basically, we’ll ask if each column in the two tables we have so far serve to describe what the primary key identifies.

2.1 Playlist table

The columns of the playlist table are as follows:

spotify_df_dict["playlist"].columns

Index([‘playlist_id’, ‘playlist_name’, ‘playlist_genre’, ‘playlist_subgenre’, ‘playlist_uid’], dtype=’object’)

Each column describes what the primary key identifies (a playlist), so 2NF is satisfied for this table.

2.2. Track table

The columns of the track table are as follows:

spotify_df_dict["track"].columns

Index([‘track_id’, ‘track_name’, ‘track_artist’, ‘track_popularity’, ‘track_album_id’, ‘track_album_name’, ‘track_album_release_date’, ‘danceability’, ‘energy’, ‘key’, ‘loudness’, ‘mode’, ‘speechiness’, ‘acousticness’, ‘instrumentalness’, ‘liveness’, ‘valence’, ‘tempo’, ‘duration_ms’], dtype=’object’)

Here, we notice that some of the columns (specifically, track_album_id, track_album_name, track_album_release_date) relate to the album of the track, not the track itself (as the other columns do). So, let’s move the album-related columns to their own table:

album_columns = ["track_album_id",
                "track_album_name",
                "track_album_release_date"]

# Create new DataFrame of album information, dropping duplicates
spotify_df_dict["album"] = spotify_df_dict["track"][album_columns].drop_duplicates()

# Check that track_album_id is an appropriate primary key
print("Is there a unique id per row?")
print(spotify_df_dict["album"].shape[0] == len(spotify_df_dict["album"]["track_album_id"].unique()))

# Check playlist_uid has no NA values
print("Are there NA values?")
print(spotify_df_dict["album"]["track_album_id"].isnull().any())

Is there a unique id per row? True Are there NA values? False

Great! Now let’s remove those columns from the track DataFrame and we should be all set with 2NF:

spotify_df_dict["track"].drop(album_columns, axis=1, inplace=True)

# Confirm that album columns were removed
spotify_df_dict["track"].columns

Index([‘track_id’, ‘track_name’, ‘track_artist’, ‘track_popularity’, ‘danceability’, ‘energy’, ‘key’, ‘loudness’, ‘mode’, ‘speechiness’, ‘acousticness’, ‘instrumentalness’, ‘liveness’, ‘valence’, ‘tempo’, ‘duration_ms’], dtype=’object’)

3. Normalize data into Third Normal Form (3NF)

Lastly, we want to confirm that there are no transitive dependencies between non-candidate columns.

3.1 Album table

Let’s look at the columns again:

spotify_df_dict["album"].head()

Each column depends on the album_id (or row number), so there are no 3NF violations.

3.2 Playlist table

spotify_df_dict["playlist"].head()

Here, playlist_subgenre violates 3NF, because it only depends on the playlist_uid via the playlist_genre. So, let’s split that out (with playlist_genre) into a separate genre table:

genre_columns = ["playlist_genre",
                "playlist_subgenre"]

# Create new DataFrame of genre information, dropping duplicates
spotify_df_dict["genre"] = spotify_df_dict["playlist"][genre_columns].drop_duplicates()

# Check that playlist_subgenre is an appropriate primary key
print("Is there a unique id per row?")
print(spotify_df_dict["genre"].shape[0] == len(spotify_df_dict["genre"]["playlist_subgenre"].unique()))

# Check playlist_uid has no NA values
print("Are there NA values?")
print(spotify_df_dict["genre"]["playlist_subgenre"].isnull().any())

Is there a unique id per row? True Are there NA values? False

Great. Lastly, let’s remove playlist_genre and playlist_subgenre from the playlist table:

spotify_df_dict["playlist"].drop(["playlist_genre","playlist_subgenre"], axis=1, inplace=True)

# Confirm that album columns were removed
spotify_df_dict["playlist"].columns

Index([‘playlist_id’, ‘playlist_name’, ‘playlist_uid’], dtype=’object’)

3.3 Track table

spotify_df_dict["track"].head()

Each column depends on the track_id (or row number), so there are no 3NF violations.

3. Populate tables of SQLite3 database

Now we’re (almost) ready to populate our database! But first, we need to resolve an issue– our tables are no longer linked! We need to add foreign keys (or, a field/column linking a given table to the primary key of a second table) because this enable us to maintain the relationships between the tables.

3.1 Add foreign keys to DataFrames

We start by using the original DataFrame to make a map between each of the primary keys: track_id, playlist_id (* we will map to playlist_uid in a second), playlist_subgenre, and track_album_id):

original_id_columns = ["track_id",
                      "playlist_id",
                      "playlist_subgenre",
                      "track_album_id"]
id_map_df = spotify_df[original_id_columns].drop_duplicates()
id_map_df.head()

Now we add playlist_uid to make this a complete map:

id_map_df = id_map_df.merge(spotify_df_dict["playlist"].loc[:,["playlist_id", "playlist_uid"]],
                           how="left",
                           on="playlist_id")
id_map_df.head()

And now we add the relevant foreign keys to preserve the DataFrame’s original links via joins:

# Add participant_uid and album_id foreign keys to track df 
spotify_df_dict["track"] = (spotify_df_dict["track"].
                            merge(id_map_df.loc[:,["track_id","playlist_uid", "track_album_id"]],
                                 how="left",
                                 on="track_id"))

# Add playlist_subgenre foreign keys to playlist df
spotify_df_dict["playlist"] = (spotify_df_dict["playlist"].
                            merge(id_map_df.loc[:,["playlist_uid", "playlist_subgenre"]],
                                 how="left",
                                 on="playlist_uid"))

Ok, now we can finally…

3.2 Populate tables of database

First, let’s create an engine to the database we previously defined:

engine = create_engine('sqlite:///data/spotify.db')

And then we write each value of our dictionary of DataFrames to a table (pandas makes this really easy):

for key in spotify_df_dict.keys():
    spotify_df_dict[key].to_sql(key, con=engine)

4. Sample SQL query: Find the names of all playlists that contain instrumentals

Lastly, let’s perform a sample query on our database. We see from the dataset documentation that the instrumentalness column (which is in the track table) assigns a value between 0 and 1 to each track, where values above 0.5 indicate the track has likely instrumental content.

So, we want to:

1. Identify which distinct playlist_uids have instrumentalness > 0.5 in the track table.
2. See which playlist_names these playlist_uids belong to.

We’ll do this with a sub-query, as follows:

%%sql 

SELECT DISTINCT playlist_name FROM playlist
WHERE playlist_uid IN (
    SELECT playlist_uid FROM track
    WHERE instrumentalness > 0.5
)

* sqlite:///data/spotify.db    Done.

Ta-da!