Home > other >  How to find and return recurring elements in an R list, using a column Id from a nested dataframe as
How to find and return recurring elements in an R list, using a column Id from a nested dataframe as

Time:04-19

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:

  1. sapply(genres, [[, ...) and look for Country, as in

    us100[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=1376707066
    

    or

    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/~
    
  2. tidyr::unnest, as TomHoel just suggested. The power of this is that you get to deal with genreId, name, and url (components of each value within genres list-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.

  3. pivot_wider and then unnest so that you keep the same number of rows, and add a column for each genres$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")
  •  Tags:  
  • r
  • Related