Introduction
Rectangling is the art and craft of taking a deeply nested list (often sourced from wild caught JSON or XML) and taming it into a tidy data set of rows and columns. There are three functions from tidyr that are particularly useful for rectangling:
-
unnest_longer()takes each element of a list-column and makes a new row. -
unnest_wider()takes each element of a list-column and makes a new column. -
unnest_auto()guesses whether you wantunnest_longer()orunnest_wider(). -
hoist()is similar tounnest_wider()but only plucks out selected components, and can reach down multiple levels.
A very large number of data rectangling problems can be solved by combining these functions with a splash of dplyr (largely eliminating prior approaches that combined mutate() with multiple purrr::map()s).
To illustrate these techniques, we’ll use the repurrrsive package, which provides a number deeply nested lists originally mostly captured from web APIs.
GitHub users
We’ll start with gh_users, a list which contains information about six GitHub users. To begin, we put the gh_users list into a data frame:
users <- tibble(user = gh_users)This seems a bit counter-intuitive: why is the first step in making a list simpler to make it more complicated? But a data frame has a big advantage: it bundles together multiple vectors so that everything is tracked together in a single object.
Each user is a named list, where each element represents a column.
names(users$user[[1]])
#> [1] "login" "id" "avatar_url"
#> [4] "gravatar_id" "url" "html_url"
#> [7] "followers_url" "following_url" "gists_url"
#> [10] "starred_url" "subscriptions_url" "organizations_url"
#> [13] "repos_url" "events_url" "received_events_url"
#> [16] "type" "site_admin" "name"
#> [19] "company" "blog" "location"
#> [22] "email" "hireable" "bio"
#> [25] "public_repos" "public_gists" "followers"
#> [28] "following" "created_at" "updated_at"There are two ways to turn the list components into columns. unnest_wider() takes every component and makes a new column:
users %>% unnest_wider(user)
#> # A tibble: 6 × 30
#> login id avata…¹ grava…² url html_…³ follo…⁴ follo…⁵ gists…⁶
#> <chr> <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 gaborcsardi 6.60e5 https:… "" http… https:… https:… https:… https:…
#> 2 jennybc 5.99e5 https:… "" http… https:… https:… https:… https:…
#> 3 jtleek 1.57e6 https:… "" http… https:… https:… https:… https:…
#> 4 juliasilge 1.25e7 https:… "" http… https:… https:… https:… https:…
#> 5 leeper 3.51e6 https:… "" http… https:… https:… https:… https:…
#> 6 masalmon 8.36e6 https:… "" http… https:… https:… https:… https:…
#> # … with 21 more variables: starred_url <chr>, subscriptions_url <chr>,
#> # organizations_url <chr>, repos_url <chr>, events_url <chr>,
#> # received_events_url <chr>, type <chr>, site_admin <lgl>, name <chr>,
#> # company <chr>, blog <chr>, location <chr>, email <chr>,
#> # hireable <lgl>, bio <chr>, public_repos <int>, public_gists <int>,
#> # followers <int>, following <int>, created_at <chr>, updated_at <chr>,
#> # and abbreviated variable names ¹avatar_url, ²gravatar_id, …But in this case, there are many components and we don’t need most of them so we can instead use hoist(). hoist() allows us to pull out selected components using the same syntax as purrr::pluck():
users %>% hoist(user,
followers = "followers",
login = "login",
url = "html_url"
)
#> # A tibble: 6 × 4
#> followers login url user
#> <int> <chr> <chr> <list>
#> 1 303 gaborcsardi https://github.com/gaborcsardi <named list [27]>
#> 2 780 jennybc https://github.com/jennybc <named list [27]>
#> 3 3958 jtleek https://github.com/jtleek <named list [27]>
#> 4 115 juliasilge https://github.com/juliasilge <named list [27]>
#> 5 213 leeper https://github.com/leeper <named list [27]>
#> 6 34 masalmon https://github.com/masalmon <named list [27]>hoist() removes the named components from the user list-column, so you can think of it as moving components out of the inner list into the top-level data frame.
GitHub repos
We start off gh_repos similarly, by putting it in a tibble:
repos <- tibble(repo = gh_repos)
repos
#> # A tibble: 6 × 1
#> repo
#> <list>
#> 1 <list [30]>
#> 2 <list [30]>
#> 3 <list [30]>
#> 4 <list [26]>
#> 5 <list [30]>
#> 6 <list [30]>This time the elements of user are a list of repositories that belong to that user. These are observations, so should become new rows, so we use unnest_longer() rather than unnest_wider():
repos <- repos %>% unnest_longer(repo)
repos
#> # A tibble: 176 × 1
#> repo
#> <list>
#> 1 <named list [68]>
#> 2 <named list [68]>
#> 3 <named list [68]>
#> 4 <named list [68]>
#> 5 <named list [68]>
#> 6 <named list [68]>
#> 7 <named list [68]>
#> 8 <named list [68]>
#> 9 <named list [68]>
#> 10 <named list [68]>
#> # … with 166 more rowsThen we can use unnest_wider() or hoist():
repos %>% hoist(repo,
login = c("owner", "login"),
name = "name",
homepage = "homepage",
watchers = "watchers_count"
)
#> # A tibble: 176 × 5
#> login name homepage watchers repo
#> <chr> <chr> <chr> <int> <list>
#> 1 gaborcsardi after NA 5 <named list [65]>
#> 2 gaborcsardi argufy NA 19 <named list [65]>
#> 3 gaborcsardi ask NA 5 <named list [65]>
#> 4 gaborcsardi baseimports NA 0 <named list [65]>
#> 5 gaborcsardi citest NA 0 <named list [65]>
#> 6 gaborcsardi clisymbols "" 18 <named list [65]>
#> 7 gaborcsardi cmaker NA 0 <named list [65]>
#> 8 gaborcsardi cmark NA 0 <named list [65]>
#> 9 gaborcsardi conditions NA 0 <named list [65]>
#> 10 gaborcsardi crayon NA 52 <named list [65]>
#> # … with 166 more rowsNote the use of c("owner", "login"): this allows us to reach two levels deep inside of a list. An alternative approach would be to pull out just owner and then put each element of it in a column:
repos %>%
hoist(repo, owner = "owner") %>%
unnest_wider(owner)
#> # A tibble: 176 × 18
#> login id avata…¹ grava…² url html_…³ follo…⁴ follo…⁵ gists…⁶
#> <chr> <int> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 2 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 3 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 4 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 5 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 6 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 7 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 8 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 9 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> 10 gaborcsar… 660288 https:… "" http… https:… https:… https:… https:…
#> # … with 166 more rows, 9 more variables: starred_url <chr>,
#> # subscriptions_url <chr>, organizations_url <chr>, repos_url <chr>,
#> # events_url <chr>, received_events_url <chr>, type <chr>,
#> # site_admin <lgl>, repo <list>, and abbreviated variable names
#> # ¹avatar_url, ²gravatar_id, ³html_url, ⁴followers_url, ⁵following_url,
#> # ⁶gists_urlInstead of looking at the list and carefully thinking about whether it needs to become rows or columns, you can use unnest_auto(). It uses a handful of heuristics to figure out whether unnest_longer() or unnest_wider() is appropriate, and tells you about its reasoning.
tibble(repo = gh_repos) %>%
unnest_auto(repo) %>%
unnest_auto(repo)
#> Using `unnest_longer(repo, indices_include = FALSE)`; no element has names
#> Using `unnest_wider(repo)`; elements have 68 names in common
#> # A tibble: 176 × 68
#> id name full_…¹ owner private html_…² descr…³ fork url
#> <int> <chr> <chr> <list> <lgl> <chr> <chr> <lgl> <chr>
#> 1 61160198 after gaborc… <named list> FALSE https:… Run Co… FALSE http…
#> 2 40500181 argu… gaborc… <named list> FALSE https:… Declar… FALSE http…
#> 3 36442442 ask gaborc… <named list> FALSE https:… Friend… FALSE http…
#> 4 34924886 base… gaborc… <named list> FALSE https:… Do we … FALSE http…
#> 5 61620661 cite… gaborc… <named list> FALSE https:… Test R… TRUE http…
#> 6 33907457 clis… gaborc… <named list> FALSE https:… Unicod… FALSE http…
#> 7 37236467 cmak… gaborc… <named list> FALSE https:… port o… TRUE http…
#> 8 67959624 cmark gaborc… <named list> FALSE https:… Common… TRUE http…
#> 9 63152619 cond… gaborc… <named list> FALSE https:… NA TRUE http…
#> 10 24343686 cray… gaborc… <named list> FALSE https:… R pack… FALSE http…
#> # … with 166 more rows, 59 more variables: forks_url <chr>,
#> # keys_url <chr>, collaborators_url <chr>, teams_url <chr>,
#> # hooks_url <chr>, issue_events_url <chr>, events_url <chr>,
#> # assignees_url <chr>, branches_url <chr>, tags_url <chr>,
#> # blobs_url <chr>, git_tags_url <chr>, git_refs_url <chr>,
#> # trees_url <chr>, statuses_url <chr>, languages_url <chr>,
#> # stargazers_url <chr>, contributors_url <chr>, …Game of Thrones characters
got_chars has a similar structure to gh_users: it’s a list of named lists, where each element of the inner list describes some attribute of a GoT character. We start in the same way, first by creating a data frame and then by unnesting each component into a column:
chars <- tibble(char = got_chars)
chars
#> # A tibble: 30 × 1
#> char
#> <list>
#> 1 <named list [18]>
#> 2 <named list [18]>
#> 3 <named list [18]>
#> 4 <named list [18]>
#> 5 <named list [18]>
#> 6 <named list [18]>
#> 7 <named list [18]>
#> 8 <named list [18]>
#> 9 <named list [18]>
#> 10 <named list [18]>
#> # … with 20 more rows
chars2 <- chars %>% unnest_wider(char)
chars2
#> # A tibble: 30 × 18
#> url id name gender culture born died alive titles aliases
#> <chr> <int> <chr> <chr> <chr> <chr> <chr> <lgl> <list> <list>
#> 1 https://ww… 1022 Theo… Male "Ironb… "In … "" TRUE <chr> <chr>
#> 2 https://ww… 1052 Tyri… Male "" "In … "" TRUE <chr> <chr>
#> 3 https://ww… 1074 Vict… Male "Ironb… "In … "" TRUE <chr> <chr>
#> 4 https://ww… 1109 Will Male "" "" "In … FALSE <chr> <chr>
#> 5 https://ww… 1166 Areo… Male "Norvo… "In … "" TRUE <chr> <chr>
#> 6 https://ww… 1267 Chett Male "" "At … "In … FALSE <chr> <chr>
#> 7 https://ww… 1295 Cres… Male "" "In … "In … FALSE <chr> <chr>
#> 8 https://ww… 130 Aria… Female "Dorni… "In … "" TRUE <chr> <chr>
#> 9 https://ww… 1303 Daen… Female "Valyr… "In … "" TRUE <chr> <chr>
#> 10 https://ww… 1319 Davo… Male "Weste… "In … "" TRUE <chr> <chr>
#> # … with 20 more rows, and 8 more variables: father <chr>, mother <chr>,
#> # spouse <chr>, allegiances <list>, books <list>, povBooks <list>,
#> # tvSeries <list>, playedBy <list>This is more complex than gh_users because some component of char are themselves a list, giving us a collection of list-columns:
chars2 %>% select_if(is.list)
#> # A tibble: 30 × 7
#> titles aliases allegiances books povBooks tvSeries playedBy
#> <list> <list> <list> <list> <list> <list> <list>
#> 1 <chr [3]> <chr [4]> <chr [1]> <chr [3]> <chr [2]> <chr [6]> <chr>
#> 2 <chr [2]> <chr [11]> <chr [1]> <chr [2]> <chr [4]> <chr [6]> <chr>
#> 3 <chr [2]> <chr [1]> <chr [1]> <chr [3]> <chr [2]> <chr [1]> <chr>
#> 4 <chr [1]> <chr [1]> <NULL> <chr [1]> <chr [1]> <chr [1]> <chr>
#> 5 <chr [1]> <chr [1]> <chr [1]> <chr [3]> <chr [2]> <chr [2]> <chr>
#> 6 <chr [1]> <chr [1]> <NULL> <chr [2]> <chr [1]> <chr [1]> <chr>
#> 7 <chr [1]> <chr [1]> <NULL> <chr [2]> <chr [1]> <chr [1]> <chr>
#> 8 <chr [1]> <chr [1]> <chr [1]> <chr [4]> <chr [1]> <chr [1]> <chr>
#> 9 <chr [5]> <chr [11]> <chr [1]> <chr [1]> <chr [4]> <chr [6]> <chr>
#> 10 <chr [4]> <chr [5]> <chr [2]> <chr [1]> <chr [3]> <chr [5]> <chr>
#> # … with 20 more rowsWhat you do next will depend on the purposes of the analysis. Maybe you want a row for every book and TV series that the character appears in:
chars2 %>%
select(name, books, tvSeries) %>%
pivot_longer(c(books, tvSeries), names_to = "media", values_to = "value") %>%
unnest_longer(value)
#> # A tibble: 180 × 3
#> name media value
#> <chr> <chr> <chr>
#> 1 Theon Greyjoy books A Game of Thrones
#> 2 Theon Greyjoy books A Storm of Swords
#> 3 Theon Greyjoy books A Feast for Crows
#> 4 Theon Greyjoy tvSeries Season 1
#> 5 Theon Greyjoy tvSeries Season 2
#> 6 Theon Greyjoy tvSeries Season 3
#> 7 Theon Greyjoy tvSeries Season 4
#> 8 Theon Greyjoy tvSeries Season 5
#> 9 Theon Greyjoy tvSeries Season 6
#> 10 Tyrion Lannister books A Feast for Crows
#> # … with 170 more rowsOr maybe you want to build a table that lets you match title to name:
chars2 %>%
select(name, title = titles) %>%
unnest_longer(title)
#> # A tibble: 60 × 2
#> name title
#> <chr> <chr>
#> 1 Theon Greyjoy "Prince of Winterfell"
#> 2 Theon Greyjoy "Captain of Sea Bitch"
#> 3 Theon Greyjoy "Lord of the Iron Islands (by law of the green lands…
#> 4 Tyrion Lannister "Acting Hand of the King (former)"
#> 5 Tyrion Lannister "Master of Coin (former)"
#> 6 Victarion Greyjoy "Lord Captain of the Iron Fleet"
#> 7 Victarion Greyjoy "Master of the Iron Victory"
#> 8 Will ""
#> 9 Areo Hotah "Captain of the Guard at Sunspear"
#> 10 Chett ""
#> # … with 50 more rows(Note that the empty titles ("") are due to an infelicity in the input got_chars: ideally people without titles would have a title vector of length 0, not a title vector of length 1 containing an empty string.)
Again, we could rewrite using unnest_auto(). This is convenient for exploration, but I wouldn’t rely on it in the long term - unnest_auto() has the undesirable property that it will always succeed. That means if your data structure changes, unnest_auto() will continue to work, but might give very different output that causes cryptic failures from downstream functions.
tibble(char = got_chars) %>%
unnest_auto(char) %>%
select(name, title = titles) %>%
unnest_auto(title)
#> Using `unnest_wider(char)`; elements have 18 names in common
#> Using `unnest_longer(title, indices_include = FALSE)`; no element has names
#> # A tibble: 60 × 2
#> name title
#> <chr> <chr>
#> 1 Theon Greyjoy "Prince of Winterfell"
#> 2 Theon Greyjoy "Captain of Sea Bitch"
#> 3 Theon Greyjoy "Lord of the Iron Islands (by law of the green lands…
#> 4 Tyrion Lannister "Acting Hand of the King (former)"
#> 5 Tyrion Lannister "Master of Coin (former)"
#> 6 Victarion Greyjoy "Lord Captain of the Iron Fleet"
#> 7 Victarion Greyjoy "Master of the Iron Victory"
#> 8 Will ""
#> 9 Areo Hotah "Captain of the Guard at Sunspear"
#> 10 Chett ""
#> # … with 50 more rowsGeocoding with google
Next we’ll tackle a more complex form of data that comes from Google’s geocoding service. It’s against the terms of service to cache this data, so I first write a very simple wrapper around the API. This relies on having an Google maps API key stored in an environment; if that’s not available these code chunks won’t be run.
has_key <- !identical(Sys.getenv("GOOGLE_MAPS_API_KEY"), "")
if (!has_key) {
message("No Google Maps API key found; code chunks will not be run")
}
#> No Google Maps API key found; code chunks will not be run
# https://developers.google.com/maps/documentation/geocoding
geocode <- function(address, api_key = Sys.getenv("GOOGLE_MAPS_API_KEY")) {
url <- "https://maps.googleapis.com/maps/api/geocode/json"
url <- paste0(url, "?address=", URLencode(address), "&key=", api_key)
jsonlite::read_json(url)
}The list that this function returns is quite complex:
houston <- geocode("Houston TX")
str(houston)Fortunately, we can attack the problem step by step with tidyr functions. To make the problem a bit harder (!) and more realistic, I’ll start by geocoding a few cities:
city <- c("Houston", "LA", "New York", "Chicago", "Springfield")
city_geo <- purrr::map(city, geocode)I’ll put these results in a tibble, next to the original city name:
loc <- tibble(city = city, json = city_geo)
locThe first level contains components status and result, which we can reveal with unnest_wider():
loc %>%
unnest_wider(json)Notice that results is a list of lists. Most of the cities have 1 element (representing a unique match from the geocoding API), but Springfield has two. We can pull these out into separate rows with unnest_longer():
loc %>%
unnest_wider(json) %>%
unnest_longer(results)Now these all have the same components, as revealed by unnest_wider():
loc %>%
unnest_wider(json) %>%
unnest_longer(results) %>%
unnest_wider(results)We can find the lat and lon coordinates by unnesting geometry:
loc %>%
unnest_wider(json) %>%
unnest_longer(results) %>%
unnest_wider(results) %>%
unnest_wider(geometry)And then location:
loc %>%
unnest_wider(json) %>%
unnest_longer(results) %>%
unnest_wider(results) %>%
unnest_wider(geometry) %>%
unnest_wider(location)Again, unnest_auto() makes this simpler with the small risk of failing in unexpected ways if the input structure changes:
loc %>%
unnest_auto(json) %>%
unnest_auto(results) %>%
unnest_auto(results) %>%
unnest_auto(geometry) %>%
unnest_auto(location)We could also just look at the first address for each city:
loc %>%
unnest_wider(json) %>%
hoist(results, first_result = 1) %>%
unnest_wider(first_result) %>%
unnest_wider(geometry) %>%
unnest_wider(location)Or use hoist() to dive deeply to get directly to lat and lng:
Sharla Gelfand’s discography
We’ll finish off with the most complex list, from Sharla Gelfand’s discography. We’ll start the usual way: putting the list into a single column data frame, and then widening so each component is a column. I also parse the date_added column into a real date-time1.
discs <- tibble(disc = discog) %>%
unnest_wider(disc) %>%
mutate(date_added = as.POSIXct(strptime(date_added, "%Y-%m-%dT%H:%M:%S")))
discs
#> # A tibble: 155 × 5
#> instance_id date_added basic_information id rating
#> <int> <dttm> <list> <int> <int>
#> 1 354823933 2019-02-16 17:48:59 <named list [11]> 7496378 0
#> 2 354092601 2019-02-13 14:13:11 <named list [11]> 4490852 0
#> 3 354091476 2019-02-13 14:07:23 <named list [11]> 9827276 0
#> 4 351244906 2019-02-02 11:39:58 <named list [11]> 9769203 0
#> 5 351244801 2019-02-02 11:39:37 <named list [11]> 7237138 0
#> 6 351052065 2019-02-01 20:40:53 <named list [11]> 13117042 0
#> 7 350315345 2019-01-29 15:48:37 <named list [11]> 7113575 0
#> 8 350315103 2019-01-29 15:47:22 <named list [11]> 10540713 0
#> 9 350314507 2019-01-29 15:44:08 <named list [11]> 11260950 0
#> 10 350314047 2019-01-29 15:41:35 <named list [11]> 11726853 0
#> # … with 145 more rowsAt this level, we see information about when each disc was added to Sharla’s discography, not any information about the disc itself. To do that we need to widen the basic_information column:
discs %>% unnest_wider(basic_information)
#> Error in `unpack()`:
#> ! Names must be unique.
#> ✖ These names are duplicated:
#> * "id" at locations 7 and 14.
#> ℹ Use argument `names_repair` to specify repair strategy.Unfortunately that fails because there’s an id column inside basic_information. We can quickly see what’s going on by setting names_repair = "unique":
discs %>% unnest_wider(basic_information, names_repair = "unique")
#> New names:
#> • `id` -> `id...7`
#> • `id` -> `id...14`
#> # A tibble: 155 × 15
#> instanc…¹ date_added labels year maste…² artists id...7 thumb
#> <int> <dttm> <list> <int> <chr> <list> <int> <chr>
#> 1 354823933 2019-02-16 17:48:59 <list> 2015 NA <list> 7.50e6 "htt…
#> 2 354092601 2019-02-13 14:13:11 <list> 2013 https:… <list> 4.49e6 "htt…
#> 3 354091476 2019-02-13 14:07:23 <list> 2017 https:… <list> 9.83e6 "htt…
#> 4 351244906 2019-02-02 11:39:58 <list> 2017 https:… <list> 9.77e6 "htt…
#> 5 351244801 2019-02-02 11:39:37 <list> 2015 https:… <list> 7.24e6 "htt…
#> 6 351052065 2019-02-01 20:40:53 <list> 2019 https:… <list> 1.31e7 "htt…
#> 7 350315345 2019-01-29 15:48:37 <list> 2014 https:… <list> 7.11e6 "htt…
#> 8 350315103 2019-01-29 15:47:22 <list> 2015 https:… <list> 1.05e7 "htt…
#> 9 350314507 2019-01-29 15:44:08 <list> 2017 https:… <list> 1.13e7 ""
#> 10 350314047 2019-01-29 15:41:35 <list> 2017 NA <list> 1.17e7 "htt…
#> # … with 145 more rows, 7 more variables: title <chr>, formats <list>,
#> # cover_image <chr>, resource_url <chr>, master_id <int>,
#> # id...14 <int>, rating <int>, and abbreviated variable names
#> # ¹instance_id, ²master_urlThe problem is that basic_information repeats the id column that’s also stored at the top-level, so we can just drop that:
discs %>%
select(!id) %>%
unnest_wider(basic_information)
#> # A tibble: 155 × 14
#> instanc…¹ date_added labels year maste…² artists id thumb
#> <int> <dttm> <list> <int> <chr> <list> <int> <chr>
#> 1 354823933 2019-02-16 17:48:59 <list> 2015 NA <list> 7.50e6 "htt…
#> 2 354092601 2019-02-13 14:13:11 <list> 2013 https:… <list> 4.49e6 "htt…
#> 3 354091476 2019-02-13 14:07:23 <list> 2017 https:… <list> 9.83e6 "htt…
#> 4 351244906 2019-02-02 11:39:58 <list> 2017 https:… <list> 9.77e6 "htt…
#> 5 351244801 2019-02-02 11:39:37 <list> 2015 https:… <list> 7.24e6 "htt…
#> 6 351052065 2019-02-01 20:40:53 <list> 2019 https:… <list> 1.31e7 "htt…
#> 7 350315345 2019-01-29 15:48:37 <list> 2014 https:… <list> 7.11e6 "htt…
#> 8 350315103 2019-01-29 15:47:22 <list> 2015 https:… <list> 1.05e7 "htt…
#> 9 350314507 2019-01-29 15:44:08 <list> 2017 https:… <list> 1.13e7 ""
#> 10 350314047 2019-01-29 15:41:35 <list> 2017 NA <list> 1.17e7 "htt…
#> # … with 145 more rows, 6 more variables: title <chr>, formats <list>,
#> # cover_image <chr>, resource_url <chr>, master_id <int>, rating <int>,
#> # and abbreviated variable names ¹instance_id, ²master_urlAlternatively, we could use hoist():
discs %>%
hoist(basic_information,
title = "title",
year = "year",
label = list("labels", 1, "name"),
artist = list("artists", 1, "name")
)
#> # A tibble: 155 × 9
#> instance_id date_added title year label artist basic_info…¹
#> <int> <dttm> <chr> <int> <chr> <chr> <list>
#> 1 354823933 2019-02-16 17:48:59 Demo 2015 Tobi… Mollot <named list>
#> 2 354092601 2019-02-13 14:13:11 Observ… 2013 La V… Una B… <named list>
#> 3 354091476 2019-02-13 14:07:23 I 2017 La V… S.H.I… <named list>
#> 4 351244906 2019-02-02 11:39:58 Oído A… 2017 La V… Rata … <named list>
#> 5 351244801 2019-02-02 11:39:37 A Cat'… 2015 Kato… Ivy (… <named list>
#> 6 351052065 2019-02-01 20:40:53 Tashme 2019 High… Tashme <named list>
#> 7 350315345 2019-01-29 15:48:37 Demo 2014 Mind… Desgr… <named list>
#> 8 350315103 2019-01-29 15:47:22 Let Th… 2015 Not … Phant… <named list>
#> 9 350314507 2019-01-29 15:44:08 Sub Sp… 2017 Not … Sub S… <named list>
#> 10 350314047 2019-01-29 15:41:35 Demo 2017 Pres… Small… <named list>
#> # … with 145 more rows, 2 more variables: id <int>, rating <int>, and
#> # abbreviated variable name ¹basic_informationHere I quickly extract the name of the first label and artist by indexing deeply into the nested list.
A more systematic approach would be to create separate tables for artist and label:
discs %>%
hoist(basic_information, artist = "artists") %>%
select(disc_id = id, artist) %>%
unnest_longer(artist) %>%
unnest_wider(artist)
#> # A tibble: 167 × 8
#> disc_id join name anv tracks role resou…¹ id
#> <int> <chr> <chr> <chr> <chr> <chr> <chr> <int>
#> 1 7496378 "" Mollot "" "" "" https:… 4.62e6
#> 2 4490852 "" Una Bèstia Incontrola… "" "" "" https:… 3.19e6
#> 3 9827276 "" S.H.I.T. (3) "" "" "" https:… 2.77e6
#> 4 9769203 "" Rata Negra "" "" "" https:… 4.28e6
#> 5 7237138 "" Ivy (18) "" "" "" https:… 3.60e6
#> 6 13117042 "" Tashme "" "" "" https:… 5.21e6
#> 7 7113575 "" Desgraciados "" "" "" https:… 4.45e6
#> 8 10540713 "" Phantom Head "" "" "" https:… 4.27e6
#> 9 11260950 "" Sub Space (2) "" "" "" https:… 5.69e6
#> 10 11726853 "" Small Man (2) "" "" "" https:… 6.37e6
#> # … with 157 more rows, and abbreviated variable name ¹resource_url
discs %>%
hoist(basic_information, format = "formats") %>%
select(disc_id = id, format) %>%
unnest_longer(format) %>%
unnest_wider(format) %>%
unnest_longer(descriptions)
#> # A tibble: 281 × 5
#> disc_id descriptions text name qty
#> <int> <chr> <chr> <chr> <chr>
#> 1 7496378 "Numbered" Black Cassette 1
#> 2 4490852 "LP" NA Vinyl 1
#> 3 9827276 "7\"" NA Vinyl 1
#> 4 9827276 "45 RPM" NA Vinyl 1
#> 5 9827276 "EP" NA Vinyl 1
#> 6 9769203 "LP" NA Vinyl 1
#> 7 9769203 "Album" NA Vinyl 1
#> 8 7237138 "7\"" NA Vinyl 1
#> 9 7237138 "45 RPM" NA Vinyl 1
#> 10 13117042 "7\"" NA Vinyl 1
#> # … with 271 more rowsThen you could join these back on to the original dataset as needed.