6.4 Rectangling

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 want unnest_longer() or unnest_wider().
  • hoist() is similar to unnest_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.

6.4.1 Github users

We’ll start with gh_users, a list which contains information about six GitHub users.

Each user is a named list, where each element represents a column:

To begin, we put the gh_users list into a data frame:

Each element of column user is yet another list, where each element represents a column.

Obviously we could use unnest_wider() to turn the list components into columns:

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():

hoist() 从列表列中提取出指明的元素作为新变量,保留余下的元素

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

6.4.2 Github repos

We start off gh_repos similarly, by putting it in a tibble:

By comparison, gh_repos is more nested than gh_users, with elements in the 2nd hierarchy being repositorys that gh_users own, and thus requires one more level of information to record each repo.

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():

Now each rwo representes a repository, then we can use unnest_wider() or hoist():

Note the use of list("owner", "login"): this allows us to reach two levels deep inside of a list using the same syntax as purrr::pluck(). An alternative approach would be to pull out just owner and then put each element of it in a column:

Instead 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)`; no element has names
#> Using `unnest_wider(repo)`; elements have 68 names in common
#> # A tibble: 176 x 67
#>       id name  full_name owner private html_url description fork  url  
#>    <int> <chr> <chr>     <lis> <lgl>   <chr>    <chr>       <lgl> <chr>
#> 1 6.12e7 after gaborcsa~ <nam~ FALSE   https:/~ Run Code i~ FALSE http~
#> 2 4.05e7 argu~ gaborcsa~ <nam~ FALSE   https:/~ Declarativ~ FALSE http~
#> 3 3.64e7 ask   gaborcsa~ <nam~ FALSE   https:/~ Friendly C~ FALSE http~
#> 4 3.49e7 base~ gaborcsa~ <nam~ FALSE   https:/~ Do we get ~ FALSE http~
#> 5 6.16e7 cite~ gaborcsa~ <nam~ FALSE   https:/~ Test R pac~ TRUE  http~
#> 6 3.39e7 clis~ gaborcsa~ <nam~ FALSE   https:/~ Unicode sy~ FALSE http~
#> # ... with 170 more rows, and 58 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>,
#> #   subscribers_url <chr>, subscription_url <chr>, commits_url <chr>,
#> #   git_commits_url <chr>, comments_url <chr>, issue_comment_url <chr>,
#> #   contents_url <chr>, compare_url <chr>, merges_url <chr>, archive_url <chr>,
#> #   downloads_url <chr>, issues_url <chr>, pulls_url <chr>,
#> #   milestones_url <chr>, notifications_url <chr>, labels_url <chr>,
#> #   releases_url <chr>, deployments_url <chr>, created_at <chr>,
#> #   updated_at <chr>, pushed_at <chr>, git_url <chr>, ssh_url <chr>,
#> #   clone_url <chr>, svn_url <chr>, size <int>, stargazers_count <int>,
#> #   watchers_count <int>, language <chr>, has_issues <lgl>,
#> #   has_downloads <lgl>, has_wiki <lgl>, has_pages <lgl>, forks_count <int>,
#> #   open_issues_count <int>, forks <int>, open_issues <int>, watchers <int>,
#> #   default_branch <chr>, homepage <chr>

6.4.3 Game of Throne 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:

This is more complex than gh_users because some component of char are themselves a list, giving us a collection of list-columns:

What 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:

Or maybe you want to build a table that lets you match title to name:

6.4.4 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-time:

At 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:

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"(default to "check_unique" which makes no name repair, but check they are unique):

The problem is that basic_information repeats the id column that’s also stored at the top-level, so we can just drop that:

Alternatively, we could use hoist()

A more systematic approach would be to create separate tables for artist and label: