The following R code is a framework for a lab I have to complete. Given the list of the top 100 songs in the U.S, find and return every song with the genre "Country." The top 100 list is formatted into 11 total columns, but note that column 10 (the genre column) is actually a nested dataframe which has 3 values - genreId, name, and url - also note that the corresponding genreId for "Country" is 6. I cannot for the life of me figure out how to access the genreId element and use that number to check for every instance of a country song, or genreId 6. I hate blatantly asking for solutions online, but I can't even figure out how to start this and I have been trying for hours. Any help is appreciated.
install.packages(c("httr", "jsonlite"))
library(httr)
library(jsonlite)
res1<-GET("https://rss.applemarketingtools.com/api/v2/us/music/most-played/100/songs.json")
res1
rawToChar(res1$content)
data1 = fromJSON(rawToChar(res1$content))
us100<-data1$feed$results
value
res2 <- GET("https://rss.applemarketingtools.com/api/v2/gb/music/most-played/100/songs.json")
data2<-fromJSON(rawToChar(res2$content))
uk100<-data2$feed$results
CodePudding user response：
A few options:
sapply(genres,[[, ...)and look forCountry, as inus100[sapply(us100$genres, function(z) "Country" %in% z$name),] # artistName id name releaseDate kind artistId artistUrl contentAdvisoryRating artworkUrl100 genres url # 2 Morgan Wallen 1618841244 Don't Think Jesus 2022-04-15 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 <NA> https://is2-ssl.mzstatic.com/image/thumb/Music122/v4/cd/c6/67/cdc667b2-e0d9-dd6f-e2b6-45ebd037c821/22UMGIM38328.rgb.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/dont-think-jesus/1618841243?i=1618841244 # 33 Cody Johnson 1582024384 'Til You Can't 2021-06-11 songs 331459657 https://music.apple.com/us/artist/cody-johnson/331459657 <NA> https://is1-ssl.mzstatic.com/image/thumb/Music115/v4/8b/4d/54/8b4d545d-feee-c91c-992c-cecd16677f6e/093624879381.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/til-you-cant/1582024378?i=1582024384 # 42 Morgan Wallen 1540314624 Wasted On You 2021-01-08 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 <NA> https://is5-ssl.mzstatic.com/image/thumb/Music115/v4/10/a2/39/10a239bc-0f25-69d2-52df-b1fe755dcf19/20UM1IM03632.rgb.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/wasted-on-you/1540314609?i=1540314624 # 46 ERNEST 1608990811 Flower Shops (feat. Morgan Wallen) 2021-12-31 songs 1450042443 https://music.apple.com/us/artist/ernest/1450042443 <NA> https://is4-ssl.mzstatic.com/image/thumb/Music116/v4/8e/f3/aa/8ef3aa07-081c-edec-c037-c9a20da4d3e5/dj.cbtqyobo.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/flower-shops-feat-morgan-wallen/1608990805?i=1608990811 # 49 Morgan Wallen 1440111980 Whiskey Glasses 2016-01-01 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 <NA> https://is3-ssl.mzstatic.com/image/thumb/Music125/v4/ac/f5/19/acf51942-e001-2d6e-e0e6-49b3fd09cac4/842812106569_01_img001.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/whiskey-glasses/1440111976?i=1440111980 # 62 Morgan Wallen 1440111985 Chasin' You 2018-04-27 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 <NA> https://is3-ssl.mzstatic.com/image/thumb/Music125/v4/ac/f5/19/acf51942-e001-2d6e-e0e6-49b3fd09cac4/842812106569_01_img001.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/chasin-you/1440111976?i=1440111985 # 67 Morgan Wallen 1540314622 Sand In My Boots 2021-01-08 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 <NA> https://is5-ssl.mzstatic.com/image/thumb/Music115/v4/10/a2/39/10a239bc-0f25-69d2-52df-b1fe755dcf19/20UM1IM03632.rgb.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/sand-in-my-boots/1540314609?i=1540314622 # 77 Morgan Wallen 1530472716 Cover Me Up 2019-04-05 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 <NA> https://is3-ssl.mzstatic.com/image/thumb/Music115/v4/58/aa/21/58aa21b0-3f22-388c-d6ae-0198c8079b4f/20UMGIM77716.rgb.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/cover-me-up/1530472528?i=1530472716 # 81 Jordan Davis 1563946213 Buy Dirt (feat. Luke Bryan) 2021-05-21 songs 1240921740 https://music.apple.com/us/artist/jordan-davis/1240921740 <NA> https://is5-ssl.mzstatic.com/image/thumb/Music125/v4/12/19/5b/12195b9a-1ac0-4ded-03da-e034ce80ac8c/21UMGIM22433.rgb.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/buy-dirt-feat-luke-bryan/1563946209?i=1563946213 # 87 Chris Stapleton 1440827492 Tennessee Whiskey 2015-04-28 songs 1752134 https://music.apple.com/us/artist/chris-stapleton/1752134 <NA> https://is2-ssl.mzstatic.com/image/thumb/Music125/v4/e2/4b/60/e24b6016-8278-bb18-cf5d-d44bf68371da/00602547223838.rgb.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/tennessee-whiskey/1440827477?i=1440827492 # 97 Bailey Zimmerman 1607279335 Fall In Love 2022-02-11 songs 1551033783 https://music.apple.com/us/artist/bailey-zimmerman/1551033783 <NA> https://is1-ssl.mzstatic.com/image/thumb/Music116/v4/17/a1/89/17a189f0-9c40-a594-1052-9c4eda1f9838/054391906823.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/fall-in-love/1607279333?i=1607279335 # 98 Luke Combs 1376707066 Beautiful Crazy 2018-05-04 songs 815635315 https://music.apple.com/us/artist/luke-combs/815635315 <NA> https://is3-ssl.mzstatic.com/image/thumb/Music125/v4/f4/4b/59/f44b59a2-62c7-e9c7-df8b-4b8e7d772b41/886447048443.jpg/100x100bb.jpg 6, 34, Country, Music, https://itunes.apple.com/us/genre/id6, https://itunes.apple.com/us/genre/id34 https://music.apple.com/us/album/beautiful-crazy/1376705241?i=1376707066or
library(dplyr) tibble(us100) %>% filter(sapply(genres, function(z) "Country" %in% z$name)) # # A tibble: 12 x 11 # artistName id name releaseDate kind artistId artistUrl contentAdvisoryRating artworkUrl100 genres url # <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <list> <chr> # 1 Morgan Wallen 1618841244 Don't Think Jesus 2022-04-15 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 NA https://is2-ssl.mzstatic.com/image/thumb/Music12~ <df [2 ~ https://music.apple.com/us/~ # 2 Cody Johnson 1582024384 'Til You Can't 2021-06-11 songs 331459657 https://music.apple.com/us/artist/cody-johnson/331459657 NA https://is1-ssl.mzstatic.com/image/thumb/Music11~ <df [2 ~ https://music.apple.com/us/~ # 3 Morgan Wallen 1540314624 Wasted On You 2021-01-08 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 NA https://is5-ssl.mzstatic.com/image/thumb/Music11~ <df [2 ~ https://music.apple.com/us/~ # 4 ERNEST 1608990811 Flower Shops (feat. Morgan Wallen) 2021-12-31 songs 1450042443 https://music.apple.com/us/artist/ernest/1450042443 NA https://is4-ssl.mzstatic.com/image/thumb/Music11~ <df [2 ~ https://music.apple.com/us/~ # 5 Morgan Wallen 1440111980 Whiskey Glasses 2016-01-01 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 NA https://is3-ssl.mzstatic.com/image/thumb/Music12~ <df [2 ~ https://music.apple.com/us/~ # 6 Morgan Wallen 1440111985 Chasin' You 2018-04-27 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 NA https://is3-ssl.mzstatic.com/image/thumb/Music12~ <df [2 ~ https://music.apple.com/us/~ # 7 Morgan Wallen 1540314622 Sand In My Boots 2021-01-08 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 NA https://is5-ssl.mzstatic.com/image/thumb/Music11~ <df [2 ~ https://music.apple.com/us/~ # 8 Morgan Wallen 1530472716 Cover Me Up 2019-04-05 songs 829142092 https://music.apple.com/us/artist/morgan-wallen/829142092 NA https://is3-ssl.mzstatic.com/image/thumb/Music11~ <df [2 ~ https://music.apple.com/us/~ # 9 Jordan Davis 1563946213 Buy Dirt (feat. Luke Bryan) 2021-05-21 songs 1240921740 https://music.apple.com/us/artist/jordan-davis/1240921740 NA https://is5-ssl.mzstatic.com/image/thumb/Music12~ <df [2 ~ https://music.apple.com/us/~ # 10 Chris Stapleton 1440827492 Tennessee Whiskey 2015-04-28 songs 1752134 https://music.apple.com/us/artist/chris-stapleton/1752134 NA https://is2-ssl.mzstatic.com/image/thumb/Music12~ <df [2 ~ https://music.apple.com/us/~ # 11 Bailey Zimmerman 1607279335 Fall In Love 2022-02-11 songs 1551033783 https://music.apple.com/us/artist/bailey-zimmerman/1551033783 NA https://is1-ssl.mzstatic.com/image/thumb/Music11~ <df [2 ~ https://music.apple.com/us/~ # 12 Luke Combs 1376707066 Beautiful Crazy 2018-05-04 songs 815635315 https://music.apple.com/us/artist/luke-combs/815635315 NA https://is3-ssl.mzstatic.com/image/thumb/Music12~ <df [2 ~ https://music.apple.com/us/~tidyr::unnest, as TomHoel just suggested. The power of this is that you get to deal withgenreId,name, andurl(components of each value withingenreslist-column); the risk is that when it has more than one row (they all have 2 rows), you end up with 200 rows from 100 ... likely not a problem if the next thing you do is filter out the specific genre id or name that you need.pivot_widerand thenunnestso that you keep the same number of rows, and add a column for eachgenres$name. (Perhaps this is overkill, since you only need one genre at a time.)
CodePudding user response：
You can use unnest() to sort of unpack the dataframe within a dataframe.
library(tidyverse)
us100 %>%
tibble() %>%
unnest(genres, names_sep = "_") %>%
filter(genres_name != "Music")
You can find the new columns at the end of the dataset. Note that I had to rename name and URL.
# A tibble: 103 x 13
artistName id name releaseDate kind artistId artistUrl contentAdvisory~ artworkUrl100 genres_genreId genres_name genres_url url
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 Jack Harlow 1618136917 First Class 2022-04-08 songs 1047679~ https://~ Explict https://is4-~ 18 Hip-Hop/Rap https://i~ http~
2 Morgan Wallen 1618841244 Don't Think J~ 2022-04-15 songs 8291420~ https://~ NA https://is2-~ 6 Country https://i~ http~
3 Harry Styles 1615585008 As It Was 2022-03-31 songs 4712602~ https://~ NA https://is2-~ 14 Pop https://i~ http~
4 Lil Baby 1618285316 In A Minute 2022-04-08 songs 1276656~ https://~ Explict https://is4-~ 18 Hip-Hop/Rap https://i~ http~
5 Lil Durk 1611056166 What Happened~ 2022-03-11 songs 5412824~ https://~ Explict https://is1-~ 18 Hip-Hop/Rap https://i~ http~
6 Lil Baby 1618153473 Right On 2022-04-08 songs 1276656~ https://~ Explict https://is1-~ 18 Hip-Hop/Rap https://i~ http~
7 Kodak Black 1592774398 Super Gremlin 2022-02-10 songs 9539211~ https://~ Explict https://is2-~ 18 Hip-Hop/Rap https://i~ http~
8 Lil Durk 1611056020 AHHH HA 2022-02-22 songs 5412824~ https://~ Explict https://is1-~ 18 Hip-Hop/Rap https://i~ http~
9 Gunna & Future 1604944757 pushin P (fea~ 2022-01-07 songs 1236267~ https://~ Explict https://is5-~ 18 Hip-Hop/Rap https://i~ http~
10 Lauren Spencer-Smith 1618103630 Flowers 2022-04-14 songs 1462708~ https://~ Explict https://is2-~ 14 Pop https://i~ http~
# ... with 93 more rows
Since you wanted only Country music
us100 %>%
filter(genres_name == "Country")