The data created in the previous step needs a little bit of cleaning up before we can get into the model building. It is a common step that needs to be undertaken to ensure that the data can be loaded into models without any issues.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Steps for Preparing the Data<\/p>\n\n\n\n
The first step is to clean up the rankings files that I created in the previous step. <\/p>\n\n\n\n
<\/p>\n\n\n\n
Reference the necessary packages. Proto, gsubfn, magrittr, dplyr, and lubridate.<\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# clean up the ranking files and merge with the lyrics files\n# ----------------------------------------------------------------------------\nlibrary(proto ,quietly = TRUE, warn.conflicts = FALSE) #used by gsubfn\nlibrary(gsubfn ,quietly = TRUE, warn.conflicts = FALSE)\n\nlibrary(magrittr ,quietly = TRUE, warn.conflicts = FALSE)\nlibrary(dplyr ,quietly = TRUE, warn.conflicts = FALSE)\n\nlibrary(lubridate ,quietly = TRUE, warn.conflicts = FALSE)<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 1.02 <\/strong>Load the Ranking Data into Data Frames<\/h3>\n\n\n\nWill use the standard library to read the flat files, and then store the data into a data frame. All of the flat files have the same number of columns and the column order is identical for each file, no need to reorganize or make adjustments afterwards.<\/p>\n\n\n\n
Additionally because I created the ranking files in the prior step, I know that there are no null values.<\/p>\n\n\n\n
# load the ranking data\ndf_rankings_drake <- read.delim(paste0(getwd(), \"\/ranks\/Drake.txt\")\n , header = TRUE\n , stringsAsFactors = FALSE\n )\n\ndf_rankings_onedirection <- read.delim(paste0(getwd(), \"\/ranks\/One-Direction.txt\")\n , header = TRUE\n , stringsAsFactors = FALSE\n )\n\ndf_rankings_pink <- read.delim(paste0(getwd(), \"\/ranks\/Pink.txt\")\n , header = TRUE\n , stringsAsFactors = FALSE\n )\n\ndf_rankings_rihanna <- read.delim(paste0(getwd(), \"\/ranks\/Rihanna.txt\")\n , header = TRUE\n , stringsAsFactors = FALSE\n )\n\ndf_rankings_taylorswift <- read.delim(paste0(getwd(), \"\/ranks\/Taylor-Swift.txt\")\n , header = TRUE\n , stringsAsFactors = FALSE\n )\n\ndf_rankings_u2 <- read.delim(paste0(getwd(), \"\/ranks\/U2.txt\")\n , header = TRUE\n , stringsAsFactors = FALSE\n )\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 1.03<\/strong> Function to Replace White Space<\/h3>\n\n\n\nThe function will replace the line feeds, tabs or any white space in the peek_rank column. <\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# update the data frames\n# ----------------------------------------------------------------------------\nreplace_empty <- function(textcontent){\n# remove line feeds, tabs, and white space\n if(\"factor\" %in% class(textcontent)){\n textcontent <- as.character(textcontent)\n }\n replacement_list <- list(\"\\n\" = \" \", \"\\t\" = \"\t\")\n textcontent <- ifelse(textcontent==\"\", \"#1 on\" , textcontent)\n textcontent <- gsubfn(\".\" , replacement_list,textcontent)\n \n return(textcontent)\n}<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 1.04 Replace the Data Frame<\/h3>\n\n\n\n
For each artist, take the data frame will have four columns.<\/p>\n\n\n\n
- artist<\/strong>: is the name of the artist<\/li>
- song_title:<\/strong> name of the song<\/li>
- peek_rank<\/strong>: the highest position the song reached on the Billboard Hot 100 rankings<\/li>
- peek_date<\/strong>: the date the song reached its highest rank on the Billboard Hot 100 rankings<\/li><\/ol>\n\n\n\n
# -- Drake\ndf_rankings_drake <- df_rankings_drake %>%\n transmute(artist = \"Drake\"\n ,song_title = song_title\n ,peek_rank = replace_empty(peek_rank)\n ,peek_date = mdy(peek_date)\n )\n\n# -- one direction\ndf_rankings_onedirection <- df_rankings_onedirection %>%\n transmute(artist = \"OneDirection\"\n ,song_title = song_title\n ,peek_rank = replace_empty(peek_rank)\n ,peek_date = mdy(peek_date)\n )\n\n# -- Pink\ndf_rankings_pink <- df_rankings_pink %>%\n transmute(artist = \"Pink\"\n ,song_title = song_title\n ,peek_rank = replace_empty(peek_rank)\n ,peek_date = mdy(peek_date)\n )\n\n# -- Rihanna\ndf_rankings_rihanna <- df_rankings_rihanna %>%\n transmute(artist = \"Rihanna\"\n ,song_title = song_title\n ,peek_rank = replace_empty(peek_rank)\n ,peek_date = mdy(peek_date)\n )\n\n# -- Taylor Swift\ndf_rankings_taylorswift <- df_rankings_taylorswift %>%\n transmute(artist = \"TaylorSwift\"\n ,song_title = song_title\n ,peek_rank = replace_empty(peek_rank)\n ,peek_date = mdy(peek_date)\n )\n\n\n# -- U2\ndf_rankings_u2 <- df_rankings_u2 %>%\n transmute(artist = \"U2\"\n ,song_title = song_title\n ,peek_rank = replace_empty(peek_rank)\n ,peek_date = mdy(peek_date)\n )\n\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2 \u2013 Match the Song Ranks<\/strong><\/h2>\n\n\n\nNow that the rankings files have been loaded and stored into data frames, the next step is to join the ranking files with the lyric files.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Step 2.01 Load the necessary packages<\/h3>\n\n\n\n
Load the necessary packages magrittr, dplyr, and readr.<\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# take the ranking files and merge with the song lists\n# ----------------------------------------------------------------------------\nlibrary(magrittr ,quietly = TRUE, warn.conflicts = FALSE)\nlibrary(dplyr ,quietly = TRUE, warn.conflicts = FALSE)\nlibrary(readr ,quietly = TRUE, warn.conflicts = FALSE)<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.02 Load the Lyrics into Data Frames<\/h4>\n\n\n\n
Load each of the lyric .csv files that I created from the previous step into data frames.<\/p>\n\n\n\n
# load the songs lists\ndf_songs_drake <- read.csv(paste0(getwd(), \"\/lyrics\/album_song\/Drake_lyrics.csv\")\n , header = TRUE\n , stringsAsFactors = FALSE\n)\n\ndf_songs_onedirection <- read.csv(paste0(getwd(), \"\/lyrics\/album_song\/One-Direction_lyrics.csv\")\n , header = TRUE\n , stringsAsFactors = FALSE\n)\n\ndf_songs_pink <- read.csv(paste0(getwd(), \"\/lyrics\/album_song\/pink_lyrics.csv\")\n , header = TRUE\n , stringsAsFactors = FALSE\n)\n\ndf_songs_rihanna <- read.csv(paste0(getwd(), \"\/lyrics\/album_song\/Rihanna_lyrics.csv\")\n , header = TRUE\n , stringsAsFactors = FALSE\n)\n\ndf_songs_taylorswift <- read.csv(paste0(getwd(), \"\/lyrics\/album_song\/Taylor-Swift_lyrics.csv\")\n , header = TRUE\n , stringsAsFactors = FALSE\n)\n\ndf_songs_u2 <- read.csv(paste0(getwd(), \"\/lyrics\/album_song\/U2_lyrics.csv\")\n , header = TRUE\n , stringsAsFactors = FALSE\n)<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.03 Update the Data Frames Dropping the song_url Column<\/h4>\n\n\n\n
I will convert the data frame into a tibble, selecting all columns except the song_url column<\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# update the data frames\n# ----------------------------------------------------------------------------\ndf_songs_drake <- as_tibble(df_songs_drake %>% select(-c(song_url)))\ndf_songs_onedirection <- as_tibble(df_songs_onedirection %>% select(-c(song_url)))\ndf_songs_pink <- as_tibble(df_songs_pink %>% select(-c(song_url)))\ndf_songs_rihanna <- as_tibble(df_songs_rihanna %>% select(-c(song_url)))\ndf_songs_taylorswift <- as_tibble(df_songs_taylorswift %>% select(-c(song_url)))\ndf_songs_u2 <- as_tibble(df_songs_u2 %>% select(-c(song_url)))\n\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.04 Merge Data Frames<\/h4>\n\n\n\n# ----------------------------------------------------------------------------\n# merge the data frames\n# ----------------------------------------------------------------------------\ndf_songs_drake <- df_songs_drake %>% left_join(df_rankings_drake , by = \"song_title\")\ndf_songs_onedirection <- df_songs_onedirection %>% left_join(df_rankings_onedirection, by = \"song_title\")\ndf_songs_pink <- df_songs_pink %>% left_join(df_rankings_pink , by = \"song_title\")\ndf_songs_rihanna <- df_songs_rihanna %>% left_join(df_rankings_rihanna , by = \"song_title\")\ndf_songs_taylorswift <- df_songs_taylorswift %>% left_join(df_rankings_taylorswift , by = \"song_title\")\ndf_songs_u2 <- df_songs_u2 %>% left_join(df_rankings_u2 , by = \"song_title\")\n\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.05 Drop Unnecessary Data Frames<\/h4>\n\n\n\n
Since I no longer need the ranking data frames I will drop them from the global environment.<\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# drop the ranking data frames\n# ----------------------------------------------------------------------------\nrm(list=ls(pattern = \"df_rank\"))\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.06 Fix the Artist Column<\/h4>\n\n\n\n# ----------------------------------------------------------------------------\n# populate the missing values\n# ----------------------------------------------------------------------------\ndf_songs_drake <- df_songs_drake %>% mutate(artist = \"Drake\")\ndf_songs_onedirection <- df_songs_onedirection %>% mutate(artist = \"One-Direction\")\ndf_songs_pink <- df_songs_pink %>% mutate(artist = \"Pink\")\ndf_songs_rihanna <- df_songs_rihanna %>% mutate(artist = \"Rihanna\")\ndf_songs_taylorswift <- df_songs_taylorswift %>% mutate(artist = \"Taylor-Swift\")\ndf_songs_u2 <- df_songs_u2 %>% mutate(artist = \"U2\")\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.07 Merge the Data Frames<\/h4>\n\n\n\n
Since all of the columns in all of the data frames are same I can use the <\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# merge the data frames\n# ----------------------------------------------------------------------------\ndf_songs <- bind_rows(df_songs_drake \n ,df_songs_onedirection\n ,df_songs_pink\n ,df_songs_rihanna\n ,df_songs_taylorswift\n ,df_songs_u2)\n\ndf_backup_songs <- df_songs\n\nrm(list=ls(pattern = \"df_songs_\"))<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.08 Clean Up and Load Lyrics into Data Frame<\/h4>\n\n\n\n
Need to clean up the peek_rank column in the data frame. need to remove the hash symbol (#), and phrase ” on”. Updating the column values will allow the column to be converted to an integer.<\/p>\n\n\n\n
Next I need to clean up some of the specific song titles. In the next step while creating the models, it won’t like ellipses (“…”) or double quotes for labeling in the model. The song title isn’t part of the model, just used as part of the unique identifier. <\/p>\n\n\n\n
Next I created a custom function to get the lyrics from the flat files that I created in the previous step. I then insert the lyrics in to the data frame for each song. <\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# convert peek_rank to an integer\n# ----------------------------------------------------------------------------\ndf_songs <- df_songs %>% mutate(peek_rank = stringr::str_replace(peek_rank,\"#\",\"\"))\ndf_songs <- df_songs %>% mutate(peek_rank = stringr::str_replace(peek_rank,\" on\",\"\"))\ndf_songs <- df_songs %>% mutate(peek_rank = as.integer(peek_rank))\n\n# ----------------------------------------------------------------------------\n# clean up an invalid song value\n# ----------------------------------------------------------------------------\ndf_songs <- df_songs %>% mutate(song_title = if_else(song_title==\"...Ready For It?\"\n , \"Ready For It?\" \n , song_title))\ndf_songs <- df_songs %>% mutate(song_title = if_else(song_title=='The Crystal Ballroom (12\" Mix)'\n , \"The Crystal Ballroom (12 Mix)\" \n , song_title))\n\n# ----------------------------------------------------------------------------\n# get the lyric text from the files\n# ----------------------------------------------------------------------------\nget_lyric_text <- function(base_dir, song_name){\n lyric_text = read_file(paste0(getwd()\n , \"\/lyrics\/\" ,base_dir ,\"_Lyrics\/\" \n , song_name \n ,\".txt\"))\n return(lyric_text)\n}\n# vectorize the function\nget_lyric_text_v <- Vectorize(get_lyric_text)\n\n# load the lyrics into the dataframe\ndf_songs_lyrics <- df_songs %>% mutate(\n lyrics = get_lyric_text_v(artist, song_title)\n )\n<\/pre><\/div>\n\n\n\n<\/p>\n\n\n\n
Step 2.08 Add Feature Columns<\/h4>\n\n\n\n
Add four additional columns to the data frame<\/p>\n\n\n\n
- album_decade: The decade the album was released <\/li>
- charted: did the song appear on Billboard’s Hot 100 charts<\/li>
- NumberOne: was the song have a peek ranking of number one on the Billboard Hot 100 chart<\/li>
- chart_group: was the peek ranking of the song in the top 10, or top 100 on the Billboard Hot 100 chart<\/li><\/ol>\n\n\n\n
# ----------------------------------------------------------------------------\n# add additional columns to the data\n# album_decade: decade album was released\n# charted: did the album chart\n# numberone: was it a number one song\n# chart_group: was it a top ten, 11-100, or not-charted\n# ----------------------------------------------------------------------------\ndf_songs_lyrics <- df_songs_lyrics %>% mutate(album_decade = as.integer(substr(album_year,1,3)) * 10)\ndf_songs_lyrics <- df_songs_lyrics %>% mutate(charted = ifelse(is.na(peek_rank), \"Not Charted\" , \"Charted\"))\ndf_songs_lyrics <- df_songs_lyrics %>% mutate(NumberOne = ifelse(is.na(peek_rank), FALSE, ifelse(peek_rank == 1, TRUE , FALSE)))\ndf_songs_lyrics <- df_songs_lyrics %>% mutate(chart_group = ifelse(is.na(peek_rank), \"Not Charted\"\n , ifelse(peek_rank <= 10, \"Top 10\" , \"Top 100\")))\n<\/pre><\/div>\n\n\n\nStep 2.09 Write the Results to a Tab Separated File<\/h4>\n\n\n\n
Lastly I will save the updated results into a tab separated file to be used in the next step, Model Prediction. <\/p>\n\n\n\n
# ----------------------------------------------------------------------------\n# save the data frame for future use\n# ----------------------------------------------------------------------------\n\nwrite_tsv(df_songs_lyrics\n ,file.path(paste0(getwd(), \"\/df_song_lyrics.txt\"))\n , na = \"NA\"\n , append = FALSE\n #, col_names = !append\n , quote_escape = \"double\"\n )\n\n\n\n\n<\/pre><\/div>\n\n\n\n<\/p>\n","protected":false},"excerpt":{"rendered":"
The data created in the previous step needs a little bit of cleaning up before we can get into the model building. It is a common step that needs to be undertaken to ensure that the data can be loaded into models without any issues. Part 3 of 4 Steps for Preparing the Data Clean […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_crdt_document":"","footnotes":""},"categories":[58,4,5,59],"tags":[],"series":[],"class_list":["post-372","post","type-post","status-publish","format-standard","hentry","category-datascience","category-code","category-r","category-songlyrics"],"_links":{"self":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/372","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/comments?post=372"}],"version-history":[{"count":8,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/372\/revisions"}],"predecessor-version":[{"id":415,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/posts\/372\/revisions\/415"}],"wp:attachment":[{"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/media?parent=372"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/categories?post=372"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/tags?post=372"},{"taxonomy":"series","embeddable":true,"href":"https:\/\/eipsoftware.com\/musings\/wp-json\/wp\/v2\/series?post=372"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}