<jupyter_start><jupyter_text>Adding the Spotify Songs dataset to a databaseThis week, I'll be using the [Tidy Tuesday Spotify Songs dataset](https://github.com/rfordatascience/tidytuesday/blob/master/data/2020/2020-01-21/readme.md) 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<jupyter_code># 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")<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>! python3 -m pip install --quiet ipython-sql
%load_ext sql<jupyter_output><empty_output><jupyter_text>Then, you can make a new database (or connect to it, if it already exists):<jupyter_code>%sql sqlite:///data/spotify.db<jupyter_output><empty_output><jupyter_text>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 columns3. Each cell contains a single value 1.1.1 Identify or create primary keyLet's start by looking at the columns available:<jupyter_code>spotify_df.columns<jupyter_output><empty_output><jupyter_text>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:<jupyter_code># 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())<jupyter_output>Is there a unique id per row?<jupyter_text>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:<jupyter_code># 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]<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>spotify_df.columns
# 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()}<jupyter_output><empty_output><jupyter_text>Now let's confirm that `track_id` and `playlist_id` are unique and non-null within each DataFrame:<jupyter_code># 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())<jupyter_output>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<jupyter_text>Seems like `playlist_id` still isn't unique. Let's look more closely at the rows where `playlist_id` is duplicated:<jupyter_code>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"])<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>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())<jupyter_output>Is there a unique id per row?
True
Are there NA values?
False<jupyter_text>Great! Moving on.  1.1.2 Check that there are no repeating columns For the track DataFrame, none of the columns seem to repeat:<jupyter_code>spotify_df_dict["track"].head()<jupyter_output><empty_output><jupyter_text>For the playlist DataFrame (since we added a unique identifying column), there are no repeating groups of columns:<jupyter_code>spotify_df_dict["playlist"].head()<jupyter_output><empty_output><jupyter_text>1.1.3 Each cell contains a single valueFrom 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:<jupyter_code>spotify_df_dict["playlist"].columns<jupyter_output><empty_output><jupyter_text>Each column describes what the primary key identifies (a playlist), so 2NF is satisfied for this table.  2.2. Track tableThe columns of the track table are as follows:<jupyter_code>spotify_df_dict["track"].columns<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>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())<jupyter_output>Is there a unique id per row?
True
Are there NA values?
False<jupyter_text>Great! Now let's remove those columns from the track DataFrame and we should be all set with 2NF:<jupyter_code>spotify_df_dict["track"].drop(album_columns, axis=1, inplace=True)

# Confirm that album columns were removed
spotify_df_dict["track"].columns<jupyter_output><empty_output><jupyter_text>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 tableLet's look at the columns again:<jupyter_code>spotify_df_dict["album"].head()<jupyter_output><empty_output><jupyter_text>Each column depends on the album_id (or row number), so there are no 3NF violations.  3.2 Playlist table<jupyter_code>spotify_df_dict["playlist"].head()<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>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())<jupyter_output>Is there a unique id per row?
True
Are there NA values?
False<jupyter_text>Great. Lastly, let's remove `playlist_genre` and `playlist_subgenre` from the playlist table:<jupyter_code>spotify_df_dict["playlist"].drop(["playlist_genre","playlist_subgenre"], axis=1, inplace=True)

# Confirm that album columns were removed
spotify_df_dict["playlist"].columns<jupyter_output><empty_output><jupyter_text>3.3 Track table<jupyter_code>spotify_df_dict["track"].head()<jupyter_output><empty_output><jupyter_text>Each column depends on the track_id (or row number), so there are no 3NF violations.   3. Populate tables of SQLite3 databaseNow 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`):<jupyter_code>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()<jupyter_output><empty_output><jupyter_text>Now we add `playlist_uid` to make this a complete map:<jupyter_code>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()<jupyter_output><empty_output><jupyter_text>And now we add the relevant foreign keys to preserve the DataFrame's original links via joins:<jupyter_code># 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"))<jupyter_output><empty_output><jupyter_text>Ok, now we can finally... 3.2 Populate tables of databaseFirst, let's create an engine to the database we previously defined:<jupyter_code>engine = create_engine('sqlite:///data/spotify.db')<jupyter_output><empty_output><jupyter_text>And then we write each value of our dictionary of DataFrames to a table (pandas makes this really easy):<jupyter_code>for key in spotify_df_dict.keys():
    spotify_df_dict[key].to_sql(key, con=engine)<jupyter_output><empty_output><jupyter_text>4. Sample SQL query: Find the names of all playlists that contain instrumentalsLastly, let's perform a sample query on our database. We see from [the dataset documentation](https://github.com/rfordatascience/tidytuesday/blob/master/data/2020/2020-01-21/readme.md) 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_uid`s have `instrumentalness` > 0.5 in the track table.  2. See which `playlist_name`s these `playlist_uids` belong to. We'll do this with a sub-query, as follows:<jupyter_code>%%sql 

SELECT DISTINCT playlist_name FROM playlist
WHERE playlist_uid IN (
    SELECT playlist_uid FROM track
    WHERE instrumentalness > 0.5
)<jupyter_output>* sqlite:///data/spotify.db
Done.
{'fraction_non_alphanumeric': 0.09782075537803939, 'fraction_numerical': 0.007427650479994394, 'mean_word_length': 4.64780371982588, 'pattern_counts': {'":': 0, '<': 103, '<?xml version=': 0, '>': 105, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '532196', 'license': 'MIT', 'n_tokens_mistral': 4468, 'n_tokens_neox': 4334, 'n_words': 1385, 'path': 'ipynbs/Spotify_Songs_DB.ipynb', 'repo_name': 'oena/oena.github.io', 'size': '14271'}