Hi there

What is this data wrangling?

Image Credits goes to stats illustrations from Allison Horst

• Data wrangling is where we take a raw dataset and transform it into a format that fits for different tasks

• Simply it's like meal prep where we get the data ready to make the main dish (in this case it could be a model or visualization)

• There are several packages that can help with data wrangling but for today we would be scratching the surface of the dplyr package in the tidyverse ecosystem.

What are we going to do today

Here's the scene,

Glimpse at the data

Rows: 32,833
Columns: 23
$ track_id                 <chr> "6f807x0ima9a1j3VPbc7VN", "0r7CVbZ…
$ track_name               <chr> "I Don't Care (with Justin Bieber)…
$ track_artist             <chr> "Ed Sheeran", "Maroon 5", "Zara La…
$ track_popularity         <dbl> 66, 67, 70, 60, 69, 67, 62, 69, 68…
$ track_album_id           <chr> "2oCs0DGTsRO98Gh5ZSl2Cx", "63rPSO2…
$ track_album_name         <chr> "I Don't Care (with Justin Bieber)…
$ track_album_release_date <chr> "2019-06-14", "2019-12-13", "2019-…
$ playlist_name            <chr> "Pop Remix", "Pop Remix", "Pop Rem…
$ playlist_id              <chr> "37i9dQZF1DXcZDD7cfEKhW", "37i9dQZ…
$ playlist_genre           <chr> "pop", "pop", "pop", "pop", "pop",…
$ playlist_subgenre        <chr> "dance pop", "dance pop", "dance p…
$ danceability             <dbl> 0.748, 0.726, 0.675, 0.718, 0.650,…
$ energy                   <dbl> 0.916, 0.815, 0.931, 0.930, 0.833,…
$ key                      <dbl> 6, 11, 1, 7, 1, 8, 5, 4, 8, 2, 6, …
$ loudness                 <dbl> -2.634, -4.969, -3.432, -3.778, -4…
$ mode                     <dbl> 1, 1, 0, 1, 1, 1, 0, 0, 1, 1, 1, 1…
$ speechiness              <dbl> 0.0583, 0.0373, 0.0742, 0.1020, 0.…
$ acousticness             <dbl> 0.10200, 0.07240, 0.07940, 0.02870…
$ instrumentalness         <dbl> 0.00e+00, 4.21e-03, 2.33e-05, 9.43…
$ liveness                 <dbl> 0.0653, 0.3570, 0.1100, 0.2040, 0.…
$ valence                  <dbl> 0.518, 0.693, 0.613, 0.277, 0.725,…
$ tempo                    <dbl> 122.036, 99.972, 124.008, 121.956,…
$ duration_ms              <dbl> 194754, 162600, 176616, 169093, 18…

What are all these numbers?

• The entire data dictionary was found here and you manage to jot it down as follows.

• We have the id,genre and subgenre of each playlist.

• The id,name and release date of each album.

• Finally id,name,artist and popularity of each track.

• In addition we have the following features of songs.

  • danceability : is it suitable for dancing (range: [0,1])
  • energy : how intense and active is the song (range: [0,1])
  • loudness : the overall loudness of track in dB (range: [-60,0])
  • speechiness : is there a lot of speech in it? (range: [0,1])
  • accousticness : is the song an acoustic song (range: [0,1])
  • instrumentalness : does the song have no vocal content (range: [0,1])
  • liveness : is there an audience in the song (range: [0,1])
  • valence : how positive is the song (range: [0,1])
  • key : the key of the song (CAUTION NA's means that there is no key)
  • mode : 1 if its a major or 0 if its a minor
  • tempo : BPM of the song

Task No. 1 (Spot the Cool Kids)

• First let's select the tracks and their artist names and how popular they are.
• Then we can think of a way to get the top five from that table someway.
• This should work right? right????

How do we select column names?

spotify %>% 
  select(track_name,track_artist,track_popularity)

• The select command helps us select columns that we want, and we can give a range of columns or even the columns that we don't want to have.

• The select function works as follows.

  • The first argument is the tibble or data frame, in this case we have piped the spotify dataset using the pipe operator.
  • The rest of the arguments are the different ways of selecting the variables.
  • For example if we want to select columnA from tibble t we would say select(t,A). Likewise if we want to select everything except column B from tibble t we would say select(t,-B).
  • you can even use a set of functions called tidy selectors to select columns based on how the name starts or ends, (For example select(t,starts_with("LKR_")))

Right now you know how to select the top rows of a tibble with the head function, so if you can get the tracks arranged by their popularity in ascending order then everything's all set!

Order! in the dataset

We need a function that can sort the rows based on one or more columns.

This is where the arrange function comes to play. The arrange function takes up a tibble as the first input and a set of columns to order by increasing or decreasing order.

spotify %>% 
  select(track_name,track_artist,track_popularity) %>% 
  arrange(track_popularity) %>%
  head(5)

Wait something's not right, this isn't in descending order! We have to tell the arrange function to sort them in descending order.

You can tell the arrange function to order in descending order by using a minus operator in front, somewhat similar to the select function.

spotify %>% 
  select(track_name,track_artist,track_popularity) %>% 
  arrange(-track_popularity) %>%
  head(5)

Hold up, why is it repeating like that?
One track can be in multiple playlists! That's why! Now what do we do? Let's select only the distinct or unique rows.

For that we can use the distinct function. The distinct function takes in a tibble as the first argument and then a set of columns to find distinctive rows or uses all columns if no arguments are given. You can see the pattern here right?

Task No. 1 is completed!

Finally by using the distinct function which returns only the unique rows in the dataframe we were able to get the top 5 track names and artists.

spotify %>% 
  select(track_name,track_artist,track_popularity) %>% 
  arrange(-track_popularity) %>% 
  distinct() %>%
  head(5)

Let's send this out to your supervisor now!

Task No. 1 last final changes

Using the rename function we were able to change the names of the column names as well

spotify %>% 
  select(track_name,track_artist,track_popularity) %>% 
  arrange(-track_popularity) %>% 
  distinct() %>% 
  head(5) %>% 
  select(-track_popularity) %>% 
  rename(Song = track_name, Artist = track_artist)

Recap of Task No. 1

What did we learn from trying out Task No. 1?

• select function helps us select the columns that we want
• arrange function helps us arrange the table by columns
• rename function helps us rename columns
• distinct functions helps us select only the unique rows in the table

Congratulations! We finished one task at our internship at Spotify!

Task No. 2 (Missing the Good old days)

spotify %>% 
  select(track_album_release_date) %>% 
  head(2)

# A tibble: 2 x 1
  track_album_release_date
  <chr>                   
1 2019-06-14              
2 2019-12-13

lubricate can fix your string of horrible dates?

Ding! Ding!
Your supervisor replied back telling you to use the function as.Date to make the column into a date and then the year function from the lubridate package.

What is this lubridate?

lubridate is a package that helps in analysing and extracting useful information from dates, like the year, month, day or even difference between dates in different units!

Image Credits goes to stats illustrations from Allison Horst

You go through the documentation and Google online and land on a StackOverflow page to find out that this code works out to give you the years that you need.

as.Date(spotify$track_album_release_date) %>% lubridate::year()

Before you send this out to your supervisor you want to make sure that the code that you wrote is correct, so you start matching with the documentation to get a rough idea about what happens.

1. You create a vector of Dates from a vector of characters
2. You get the year as a number from the year function in lubridate

Sounds good!

How do we modify the existing columns?

Image Credits goes to stats illustrations from Allison Horst

Let's mutate the dataset!

We will first mutate the original dataset to check whether this works. Remember you just have to give the name of the column you want the operation to happen on, and there's no need to have the dataset before that followed with a $

spotify %>% 
  mutate(release_year = lubridate::year(as.Date(track_album_release_date))) %>%
  select(release_year) %>% 
  head(2)

# A tibble: 2 x 1
  release_year
         <dbl>
1         2019
2         2019

Success! We managed to get the years out. Now we need to get the maximum of these years.

spotify_with_year <- spotify %>% 
  mutate(release_year = lubridate::year(as.Date(track_album_release_date)))
latest_year <- max(spotify_with_year$release_year,na.rm = TRUE)

## [1] 2020

How can we filter the songs that we want?

The latest year is with us now! All we need to do now is to filter and get only the tracks that was released on a year that is equal to the latest year.

The filter function can be used to get a portion of a dataset that ticks the boxes of a set of one or more logical conditions. Basically any logical condition that gives out a logical vector matching the length of the dataset can be used to filter out the rows that you need.

It's kind of like an actual filter where the barriers are the rows with FALSE.

Image Credits goes to stats illustrations from Allison Horst

Let's use the filter function to filter out the rows that we need. We have to a logical condition as an argument to filter and you can combine different conditions using logical operators like &,|, ~ etc.

spotify_with_year %>% 
  filter(release_year == (latest_year-1)) %>%
  select(track_name,release_year) %>% 
  head(5)

# A tibble: 5 x 2
  track_name                                            release_year
  <chr>                                                        <dbl>
1 I Don't Care (with Justin Bieber) - Loud Luxury Remix         2019
2 Memories - Dillon Francis Remix                               2019
3 All the Time - Don Diablo Remix                               2019
4 Call You Mine - Keanu Silva Remix                             2019
5 Someone You Loved - Future Humans Remix                       2019

Great! Now we need to plug these into our previous code and we are good to go!

Task 2 is done and dusted

spotify_with_year %>%
  filter(release_year == 2019) %>% 
  select(track_name,track_artist,track_popularity) %>% 
  arrange(-track_popularity) %>% 
  distinct() %>% 
  head(5) %>% 
  select(-track_popularity) %>% 
  rename(Song = track_name, Artist = track_artist)

Recap of Task No. 2

What did we learn from trying out Task No.2 ?

• as.Date function converts character columns to Date columns.
• year function from lubridate package gets the year from a Date column.
• mutate function mutates existing columns to create new ones.
• filter function helps to filter the rows based on a condition.

That was a huge leap from what we learned in Task No. 1!

Task No. 3 (Playlists for your moods)

The supervisor has one final task for you.

You need to give the average scores for energy, danceability, instrumentalness, valence for each playlist to so that the supervisor can use that dataset in her Plotly Radar plot function.

This is going to be a tough one! You sit down and sketch out how the final dataset should look like and this is what you got,

So now we know what sort of outcome we expect at the end. Now the question remains how do you group these playlists together.

Understanding group_by

We can use the group_by function in dplyr for this. But the group_by function can be tricky sometimes so you dig a bit deeper.

• group_by function literally groups rows with the same combination of values in the given columns together
• After that any mutate function that is applied will apply within the group.
• But remember to ungroup the dataset afterwards, otherwise they would remain in the grouped state and all the actions that you perform on it will be performed group wise.

Testing out the waters

Let's try seeing how this group_by works by trying to get the average energy of a genre like pop. You type out the values by hand and get the mean value as 0.7010278.

spotify %>% 
  group_by(playlist_genre) %>% 
  mutate(mean_energy = mean(energy)) %>% 
  select(playlist_genre,mean_energy) %>% 
  head(3)

## # A tibble: 3 x 2
## # Groups:   playlist_genre [1]
##   playlist_genre mean_energy
##   <chr>                <dbl>
## 1 pop                  0.701
## 2 pop                  0.701
## 3 pop                  0.701

So we have to call distinct for this as well it seems. And the tibble output shows that the dataset is now a grouped dataset by playlist_genre. We should add an ungroup at the end to remove that effect, otherwise we might forget and issues may come up.

With that in your head you first write out the group_by command and decide to be adventurous and mutate a new column for each of the averages.

spotify %>% 
  group_by(playlist_genre) %>%
  mutate(avg_energy = mean(energy,na.rm=TRUE),avg_dance = mean(danceability,na.rm=TRUE),
            avg_instu = mean(instrumentalness,na.rm=TRUE),avg_valen = mean(valence,na.rm=TRUE)) %>%
  rename(Genre = playlist_genre,Energetic = avg_energy, Dance = avg_dance,
         Instrumentals = avg_instu, Upbeat = avg_valen) %>% 
  select(Genre,Energetic,Dance,Instrumentals, Upbeat) %>% 
  distinct() %>% 
  ungroup() %>% 
  head(4)

Do we really have to call distinct again like this? Or is there an easier way than having to group_by -> mutate -> select -> distinct?

Task No. 3 is over!

Your supervisor noticed what you were typing on your laptop and gave a tip to Google up on the function summarize to make the code more readable and safer.

And now after digging on the summarize function, you realize that by simply replacing mutate with summarize your code now looks like this. And it automatically ungroups the outermost group after giving the summary statistics.

spotify %>% 
  group_by(playlist_genre) %>% 
  summarize(avg_energy = mean(energy,na.rm=TRUE),avg_dance = mean(danceability,na.rm=TRUE),
            avg_instu = mean(instrumentalness,na.rm=TRUE),avg_valen = mean(valence,na.rm=TRUE)) %>%
  rename(Genre = playlist_genre,Energetic = avg_energy, Dance = avg_dance,
         Instrumentals = avg_instu, Upbeat = avg_valen) %>% 
  head(5)

Recap of Task No. 3

What did we learn from trying out Task No. 3?

• group_by helps to group the dataset into groups based on the values of a column
• summarize helps to make a summary of a grouped / ungrouped dataset to get summary statistics

Finally! We finished all three tasks! We are now well on our way to start analysing data that come our way.