25  Data rectangling

You are reading the work-in-progress second edition of R for Data Science. This chapter is currently a dumping ground for ideas, and we don’t recommend reading it. You can find the complete first edition at https://r4ds.had.co.nz.

25.1 Introduction

In this chapter, you’ll learn the art of data rectangling, taking data that is fundamentally tree-like and converting it into a rectangular data frames made up of rows and columns. This is important because hierarchical data is surprisingly common, especially when working with data that comes from a web API.

To learn about rectangling, you’ll first learn about lists, the data structure that makes hierarchical data possible in R. Then you’ll learn about two crucial tidyr functions: tidyr::unnest_longer(), which converts children in rows, and tidyr::unnest_wider(), which converts children into columns. We’ll then show you a few case studies, applying these simple function multiple times to solve real complex problems. We’ll finish off by talking about JSON, the most frequent source of hierarchical datasets and common format for data exchange on the web.

25.1.1 Prerequisites

In this chapter we’ll continue using tidyr. We’ll also use repurrrsive to supply some interesting datasets to practice your rectangling skills, and we’ll finish up with a little jsonlite, which we’ll use to read JSON files into R lists.

25.2 Lists

So far we’ve used simple vectors like integers, numbers, characters, date-times, and factors. These vectors are simple because they’re homogeneous: every element is same type. If you want to store element of different types, you need a list, which you create with list():

x1 <- list(1:4, "a", TRUE)
x1
#> [[1]]
#> [1] 1 2 3 4
#> 
#> [[2]]
#> [1] "a"
#> 
#> [[3]]
#> [1] TRUE

It’s often convenient to name the components, or children, of a list, which you can do in the same way as naming the columns of a tibble:

x2 <- list(a = 1:2, b = 1:3, c = 1:4)
x2
#> $a
#> [1] 1 2
#> 
#> $b
#> [1] 1 2 3
#> 
#> $c
#> [1] 1 2 3 4

Even for these very simple lists, printing takes up quite a lot of space. A useful alternative is str(), which generates a compact display of the structure, de-emphasizing the contents:

str(x1)
#> List of 3
#>  $ : int [1:4] 1 2 3 4
#>  $ : chr "a"
#>  $ : logi TRUE
str(x2)
#> List of 3
#>  $ a: int [1:2] 1 2
#>  $ b: int [1:3] 1 2 3
#>  $ c: int [1:4] 1 2 3 4

As you can see, str() displays each child on its own line. It displays the name, if present, then an abbreviation of the type, then the first few values.

25.2.1 Hierarchy

Lists can contain any type of object, including other lists. This makes them suitable for representing hierarchical or tree-like structures:

x3 <- list(list(1, 2), list(3, 4))
str(x3)
#> List of 2
#>  $ :List of 2
#>   ..$ : num 1
#>   ..$ : num 2
#>  $ :List of 2
#>   ..$ : num 3
#>   ..$ : num 4

This is notably different to c(), which generates a flat vector:

c(c(1, 2), c(3, 4))
#> [1] 1 2 3 4

x4 <- c(list(1, 2), list(3, 4))
str(x4)
#> List of 4
#>  $ : num 1
#>  $ : num 2
#>  $ : num 3
#>  $ : num 4

As lists get more complex, str() gets more useful, as it lets you see the hierarchy at a glance:

x5 <- list(1, list(2, list(3, list(4, list(5)))))
str(x5)
#> List of 2
#>  $ : num 1
#>  $ :List of 2
#>   ..$ : num 2
#>   ..$ :List of 2
#>   .. ..$ : num 3
#>   .. ..$ :List of 2
#>   .. .. ..$ : num 4
#>   .. .. ..$ :List of 1
#>   .. .. .. ..$ : num 5

As lists get even large and more complex, even str() starts to fail, you’ll need to switch to View()1. Figure 25.1 shows the result of calling View(x4). The viewer starts by showing just the top level of the list, but you can interactively expand any of the components to see more, as in Figure 25.2. RStudio will also show you the code you need to access that element, as in Figure 25.3. We’ll come back to how this code works in Section 28.4.5.

25.2.2 List-columns

Lists can also live inside a tibble, where we call them list-columns. List-columns are useful because they allow you to shoehorn in objects that wouldn’t wouldn’t usually belong in a data frame. List-columns are are used a lot in the tidymodels ecosystem, because it allows you to store things like models or resamples in a data frame.

Here’s a simple example of a list-column:

df <- tibble(
  x = 1:2, 
  y = c("a", "b"),
  z = list(list(1, 2), list(3, 4, 5))
)
df
#> # A tibble: 2 × 3
#>       x y     z         
#>   <int> <chr> <list>    
#> 1     1 a     <list [2]>
#> 2     2 b     <list [3]>

There’s nothing special about lists in a tibble; they behave like any other column:

df |> 
  filter(x == 1)
#> # A tibble: 1 × 3
#>       x y     z         
#>   <int> <chr> <list>    
#> 1     1 a     <list [2]>

Computing with them is harder, but that’s because computing with lists is a harder; we’ll come back to that in Chapter 29.

The default print method just displays a rough summary of the contents. The list column could be arbitrarily complex, so there’s no good way to print it. If you want to see it, you’ll need to pull the list-column out and apply of the techniques that you learned above:

df |> 
  filter(x == 1) |> 
  pull(z) |> 
  str()
#> List of 1
#>  $ :List of 2
#>   ..$ : num 1
#>   ..$ : num 2

Similarly, if you View() a data frame in RStudio, you’ll get the standard tabular view, which doesn’t allow you to selectively expand list columns. To explore those fields you’ll need to pull() and view, e.g. View(pull(df, z)).

Base R

It’s possible to put a list in a column of a data.frame, but it’s a lot fiddlier. However, base R doesn’t make it easy to create list-columns because data.frame() treats a list as a list of columns:

data.frame(x = list(1:3, 3:5))
#>   x.1.3 x.3.5
#> 1     1     3
#> 2     2     4
#> 3     3     5

You can prevent data.frame() from doing this with I(), but the result doesn’t print particularly informatively:

data.frame(
  x = I(list(1:3, 3:5)), 
  y = c("1, 2", "3, 4, 5")
)
#>         x       y
#> 1 1, 2, 3    1, 2
#> 2 3, 4, 5 3, 4, 5

Tibbles make it easier to work with list-columns because tibble() doesn’t modify its inputs and the print method is designed with lists in mind.

25.3 Unnesting

Now that you’ve learned the basics of lists and list-columns, lets explore how you can turn them back into regular rows and columns. We’ll start with very simple sample data so you can get the basic idea, and then in the next section switch to more realistic examples.

List-columns tend to come in two basic forms: named and unnamed. When the children are named, they tend to have the same names in every row. When the children are unnamed, the number of elements tends to vary from row-to-row. The following code creates an example of each. In df1, every element of list-column y has two elements named a and b. If df2, the elements of list-column y are unnamed and vary in length.

df1 <- tribble(
  ~x, ~y,
  1, list(a = 11, b = 12),
  2, list(a = 21, b = 22),
  3, list(a = 31, b = 32),
)

df2 <- tribble(
  ~x, ~y,
  1, list(11, 12, 13),
  2, list(21),
  3, list(31, 32),
)

Named list-columns naturally unnest into columns: each named element becomes a new named column. Unnamed list-columns naturally unnested in to rows: you’ll get one row for each child. tidyr provides two functions for these two case: unnest_wider() and unnest_longer(). The following sections explain how they work.

25.3.1 unnest_wider()

When each row has the same number of elements with the same names, like df1, it’s natural to put each component into its own column with unnest_wider():

df1 |> 
  unnest_wider(y)
#> # A tibble: 3 × 3
#>       x     a     b
#>   <dbl> <dbl> <dbl>
#> 1     1    11    12
#> 2     2    21    22
#> 3     3    31    32

By default, the names of the new columns come exclusively from the names of the list, but you can use the names_sep argument to request that they combine the column name and the list names. This is useful for disambiguating repeated names.

df1 |> 
  unnest_wider(y, names_sep = "_")
#> # A tibble: 3 × 3
#>       x   y_a   y_b
#>   <dbl> <dbl> <dbl>
#> 1     1    11    12
#> 2     2    21    22
#> 3     3    31    32

We can also use unnest_wider() with unnamed list-columns, as in df2. Since columns require names but the list lacks them, unnest_wider() will label them with consecutive integers:

df2 |> 
  unnest_wider(y, names_sep = "_")
#> # A tibble: 3 × 4
#>       x   y_1   y_2   y_3
#>   <dbl> <dbl> <dbl> <dbl>
#> 1     1    11    12    13
#> 2     2    21    NA    NA
#> 3     3    31    32    NA

You’ll notice that unnested_wider(), much like pivot_wider(), turns implicit missing values in to explicit missing values.

25.3.2 unnest_longer()

When each row contains an unnamed list, it’s most natural to put each element into its own row with unnest_longer():

df2 |> 
  unnest_longer(y)
#> # A tibble: 6 × 2
#>       x     y
#>   <dbl> <dbl>
#> 1     1    11
#> 2     1    12
#> 3     1    13
#> 4     2    21
#> 5     3    31
#> 6     3    32

Note how x is duplicated for each element inside of y: we get one row of output for each element inside the list-column. But what happens if the list-column is empty, as in the following example?

df6 <- tribble(
  ~x, ~y,
  "a", list(1, 2),
  "b", list(3),
  "c", list()
)
df6 |> unnest_longer(y)
#> # A tibble: 3 × 2
#>   x         y
#>   <chr> <dbl>
#> 1 a         1
#> 2 a         2
#> 3 b         3

We get zero rows in the output, so the row effectively disappears. Once https://github.com/tidyverse/tidyr/issues/1339 is fixed, you’ll be able to keep this row, replacing y with NA by setting keep_empty = TRUE.

You can also unnest named list-columns, like df1$y into the rows. Because the elements are named, and those names might be useful data, puts them in a new column with the suffix_id:

df1 |> 
  unnest_longer(y)
#> # A tibble: 6 × 3
#>       x     y y_id 
#>   <dbl> <dbl> <chr>
#> 1     1    11 a    
#> 2     1    12 b    
#> 3     2    21 a    
#> 4     2    22 b    
#> 5     3    31 a    
#> 6     3    32 b

If you don’t want these ids, you can suppress this with indices_include = FALSE. On the other hand, it’s sometimes useful to retain the position of unnamed elements in unnamed list-columns. You can do this with indices_include = TRUE:

df2 |> 
  unnest_longer(y, indices_include = TRUE)
#> # A tibble: 6 × 3
#>       x     y  y_id
#>   <dbl> <dbl> <int>
#> 1     1    11     1
#> 2     1    12     2
#> 3     1    13     3
#> 4     2    21     1
#> 5     3    31     1
#> 6     3    32     2

25.3.3 Inconsistent types

What happens if you unnest a list-column contains different types of vector? For example, take the following dataset where the list-column y contains two numbers, a factor, and a logical, which can’t normally be mixed in a single column.

df4 <- tribble(
  ~x, ~y,
  "a", list(1, "a"),
  "b", list(TRUE, factor("a"), 5)
)

unnest_longer() always keeps the set of columns change, while changing the number of rows. So what happens? How does unnest_longer() produce five rows while keeping everything in y?

df4 |> 
  unnest_longer(y)
#> # A tibble: 5 × 2
#>   x     y        
#>   <chr> <list>   
#> 1 a     <dbl [1]>
#> 2 a     <chr [1]>
#> 3 b     <lgl [1]>
#> 4 b     <fct [1]>
#> 5 b     <dbl [1]>

As you can see, the output contains a list-column, but every element of the list-column contains a single element. Because unnest_longer() can’t find a common type of vector, it keeps the original types in a list-column. You might wonder if this breaks the commandment that every element of a column must be the same type — not quite, because every element is a still a list, and each component of that list contains something different.

What happens if you find this problem in a dataset you’re trying to rectangle? I think there are two basic options. You could use the transform argument to coerce all inputs to a common type. It’s not particularly useful here because there’s only really one class that these five class can be converted to: character.

df4 |> 
  unnest_longer(y, transform = as.character)
#> # A tibble: 5 × 2
#>   x     y    
#>   <chr> <chr>
#> 1 a     1    
#> 2 a     a    
#> 3 b     TRUE 
#> 4 b     a    
#> 5 b     5

Another option would be to filter down to the rows that have values of a specific type:

df4 |> 
  unnest_longer(y) |> 
  rowwise() |> 
  filter(is.numeric(y))
#> # A tibble: 2 × 2
#> # Rowwise: 
#>   x     y        
#>   <chr> <list>   
#> 1 a     <dbl [1]>
#> 2 b     <dbl [1]>

Then you can call unnest_longer() once more:

df4 |> 
  unnest_longer(y) |> 
  rowwise() |> 
  filter(is.numeric(y)) |> 
  unnest_longer(y)
#> # A tibble: 2 × 2
#>   x         y
#>   <chr> <dbl>
#> 1 a         1
#> 2 b         5

25.3.4 Other functions

tidyr has a few other useful rectangling functions that we’re not going to cover in this book:

  • unnest_auto() automatically picks between unnest_longer() and unnest_wider() based on the structure of the list-column. It’s a great for rapid exploration, but I think it’s ultimately a bad idea because it doesn’t force you to understand how your data is structured, and makes your code harder to understand.
  • unnest() expands both rows and columns. It’s useful when you have a list-column that contains a 2d structure like a data frame, which we don’t see in this book.
  • hoist() allows you to reach into a deeply nested list and extract just the components that you need. It’s mostly equivalent to repeated invocations of unnest_wider() + select() so you read up on it if you’re trying to extract just a couple of important variables embedded in a bunch of data that you don’t care about.

25.3.5 Exercises

  1. From time-to-time you encounter data frames with multiple list-columns with aligned values. For example, in the following data frame, the values of y and z are aligned (i.e. y and z will always have the same length within a row, and the first value of y corresponds to the first value of z). What happens if you apply two unnest_longer() calls to this data frame? How can you preserve the relationship between x and y? (Hint: carefully read the docs).

    df4 <- tribble(
      ~x, ~y, ~z,
      "a", list("y-a-1", "y-a-2"), list("z-a-1", "z-a-2"),
      "b", list("y-b-1", "y-b-2", "y-b-3"), list("z-b-1", "z-b-2", "z-b-3")
    )

25.4 Case studies

So far you’ve learned about the simplest case of list-columns, where you need only a single call to unnest_longer() or unnest_wider(). The main difference between real data and these simple examples, is with real data you’ll see multiple levels of nesting. For example, you might see named list nested inside an unnested list, or an unnamed list nested inside of another unnamed list nested inside a named list. To handle these case you’ll need to chain together multiple calls to unnest_wider() and/or unnest_longer().

This section will work through some real rectangling challenges using datasets from the repurrrsive package that are inspired by datasets that we’ve encountered in the wild. These challenges share the common feature that they’re mostly just a sequence of multiple unnest_wider() and/or unnest_longer() calls, with a dash of dplyr where needed.

25.4.1 Very wide data

We’ll start by exploring gh_repos which contains data about some GitHub repositories retrived from the GitHub API. It’s a very deeply nested list so it’s to show the structure in this book; you might want to explore a little on your own with View(gh_repos) before we continue.

gh_repos is a list, but our tools work with list-columns, so we’ll begin by putting it a tibble. I call the column call json for reasons we’ll get to later.

repos <- tibble(json = gh_repos)
repos
#> # A tibble: 6 × 1
#>   json       
#>   <list>     
#> 1 <list [30]>
#> 2 <list [30]>
#> 3 <list [30]>
#> 4 <list [26]>
#> 5 <list [30]>
#> 6 <list [30]>

This tibble contains 6 rows, one row for each child of gh_repos. Each row contains a unnamed list with either 26 or 30 rows. Since these are unnamed, we’ll start with an unnest_longer() to put each child in its own row:

repos |> 
  unnest_longer(json)
#> # A tibble: 176 × 1
#>   json             
#>   <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]>
#> # … with 170 more rows

At first glance, it might seem like we haven’t improved the situation: while we have more rows (176 instead of 6) each element of json is still a list. However, there’s an important difference: now each element is a named list so we can use unnamed_wider() to put each element into its own column:

repos |> 
  unnest_longer(json) |> 
  unnest_wider(json) 
#> # A tibble: 176 × 68
#>         id name  full_name owner        private html_url description fork  url  
#>      <int> <chr> <chr>     <list>       <lgl>   <chr>    <chr>       <lgl> <chr>
#> 1 61160198 after gaborcsa… <named list> FALSE   https:/… Run Code i… FALSE http…
#> 2 40500181 argu… gaborcsa… <named list> FALSE   https:/… Declarativ… FALSE http…
#> 3 36442442 ask   gaborcsa… <named list> FALSE   https:/… Friendly C… FALSE http…
#> 4 34924886 base… gaborcsa… <named list> FALSE   https:/… Do we get … FALSE http…
#> 5 61620661 cite… gaborcsa… <named list> FALSE   https:/… Test R pac… TRUE  http…
#> 6 33907457 clis… gaborcsa… <named list> FALSE   https:/… Unicode sy… FALSE http…
#> # … with 170 more rows, and 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>,
#> #   subscribers_url <chr>, subscription_url <chr>, commits_url <chr>, …

This has worked but the result is a little overwhelming: there are so many columns that tibble doesn’t even print all of them! We can see them all with names():

repos |> 
  unnest_longer(json) |> 
  unnest_wider(json) |> 
  names()
#>  [1] "id"                "name"              "full_name"        
#>  [4] "owner"             "private"           "html_url"         
#>  [7] "description"       "fork"              "url"              
#> [10] "forks_url"         "keys_url"          "collaborators_url"
#> [13] "teams_url"         "hooks_url"         "issue_events_url" 
#> [16] "events_url"        "assignees_url"     "branches_url"     
#> [19] "tags_url"          "blobs_url"         "git_tags_url"     
#> [22] "git_refs_url"      "trees_url"         "statuses_url"     
#> [25] "languages_url"     "stargazers_url"    "contributors_url" 
#> [28] "subscribers_url"   "subscription_url"  "commits_url"      
#> [31] "git_commits_url"   "comments_url"      "issue_comment_url"
#> [34] "contents_url"      "compare_url"       "merges_url"       
#> [37] "archive_url"       "downloads_url"     "issues_url"       
#> [40] "pulls_url"         "milestones_url"    "notifications_url"
#> [43] "labels_url"        "releases_url"      "deployments_url"  
#> [46] "created_at"        "updated_at"        "pushed_at"        
#> [49] "git_url"           "ssh_url"           "clone_url"        
#> [52] "svn_url"           "homepage"          "size"             
#> [55] "stargazers_count"  "watchers_count"    "language"         
#> [58] "has_issues"        "has_downloads"     "has_wiki"         
#> [61] "has_pages"         "forks_count"       "mirror_url"       
#> [64] "open_issues_count" "forks"             "open_issues"      
#> [67] "watchers"          "default_branch"

Let’s select a few that look interesting:

repos |> 
  unnest_longer(json) |> 
  unnest_wider(json) |> 
  select(id, full_name, owner, description)
#> # A tibble: 176 × 4
#>         id full_name               owner             description                
#>      <int> <chr>                   <list>            <chr>                      
#> 1 61160198 gaborcsardi/after       <named list [17]> Run Code in the Background 
#> 2 40500181 gaborcsardi/argufy      <named list [17]> Declarative function argum…
#> 3 36442442 gaborcsardi/ask         <named list [17]> Friendly CLI interaction i…
#> 4 34924886 gaborcsardi/baseimports <named list [17]> Do we get warnings for und…
#> 5 61620661 gaborcsardi/citest      <named list [17]> Test R package and repo fo…
#> 6 33907457 gaborcsardi/clisymbols  <named list [17]> Unicode symbols for CLI ap…
#> # … with 170 more rows

You can use this to work back to understand gh_repos: each child was a GitHub user containing a list of up to 30 GitHub repositories that they created.

owner is another list-column, and since it a contains named list, we can use unnest_wider() to get at the values:

repos |> 
  unnest_longer(json) |> 
  unnest_wider(json) |> 
  select(id, full_name, owner, description) |> 
  unnest_wider(owner)
#> Error in `unpack()`:
#> ! Names must be unique.
#> ✖ These names are duplicated:
#>   * "id" at locations 1 and 4.
#> ℹ Use argument `names_repair` to specify repair strategy.

Uh oh, this list column also contains an id column and we can’t have two id columns in the same data frame. Rather than following the advice to use names_repair (which would also work), I’ll instead use names_sep:

repos |> 
  unnest_longer(json) |> 
  unnest_wider(json) |> 
  select(id, full_name, owner, description) |> 
  unnest_wider(owner, names_sep = "_")
#> # A tibble: 176 × 20
#>         id full_name      owner_login owner_id owner_avatar_url owner_gravatar_…
#>      <int> <chr>          <chr>          <int> <chr>            <chr>           
#> 1 61160198 gaborcsardi/a… gaborcsardi   660288 https://avatars… ""              
#> 2 40500181 gaborcsardi/a… gaborcsardi   660288 https://avatars… ""              
#> 3 36442442 gaborcsardi/a… gaborcsardi   660288 https://avatars… ""              
#> 4 34924886 gaborcsardi/b… gaborcsardi   660288 https://avatars… ""              
#> 5 61620661 gaborcsardi/c… gaborcsardi   660288 https://avatars… ""              
#> 6 33907457 gaborcsardi/c… gaborcsardi   660288 https://avatars… ""              
#> # … with 170 more rows, and 14 more variables: owner_url <chr>,
#> #   owner_html_url <chr>, owner_followers_url <chr>, owner_following_url <chr>,
#> #   owner_gists_url <chr>, owner_starred_url <chr>,
#> #   owner_subscriptions_url <chr>, owner_organizations_url <chr>,
#> #   owner_repos_url <chr>, owner_events_url <chr>,
#> #   owner_received_events_url <chr>, owner_type <chr>, owner_site_admin <lgl>,
#> #   description <chr>

This gives another wide dataset, but you can see that owner appears to contain a lot of additional data about the person who “owns” the repository.

25.4.2 Relational data

When you get nested data, it’s not uncommon for it to contain data that we’d normally spread out into multiple data frames. Take got_chars, for example. Like gh_repos it’s a list, so we start by turning it into a list-column of a tibble:

chars <- tibble(json = got_chars)
chars
#> # A tibble: 30 × 1
#>   json             
#>   <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]>
#> # … with 24 more rows

The json column contains named values, so we’ll start by widening it:

chars |> 
  unnest_wider(json)
#> # A tibble: 30 × 18
#>   url            id name  gender culture born  died  alive titles aliases father
#>   <chr>       <int> <chr> <chr>  <chr>   <chr> <chr> <lgl> <list> <list>  <chr> 
#> 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>   ""    
#> # … with 24 more rows, and 7 more variables: mother <chr>, spouse <chr>,
#> #   allegiances <list>, books <list>, povBooks <list>, tvSeries <list>,
#> #   playedBy <list>

And selecting a few columns just to make it easier to read:

characters <- chars |> 
  unnest_wider(json) |> 
  select(id, name, gender, culture, born, died, alive)
characters
#> # A tibble: 30 × 7
#>      id name              gender culture    born                     died  alive
#>   <int> <chr>             <chr>  <chr>      <chr>                    <chr> <lgl>
#> 1  1022 Theon Greyjoy     Male   "Ironborn" "In 278 AC or 279 AC, a… ""    TRUE 
#> 2  1052 Tyrion Lannister  Male   ""         "In 273 AC, at Casterly… ""    TRUE 
#> 3  1074 Victarion Greyjoy Male   "Ironborn" "In 268 AC or before, a… ""    TRUE 
#> 4  1109 Will              Male   ""         ""                       "In … FALSE
#> 5  1166 Areo Hotah        Male   "Norvoshi" "In 257 AC or before, a… ""    TRUE 
#> 6  1267 Chett             Male   ""         "At Hag's Mire"          "In … FALSE
#> # … with 24 more rows

There are also many list-columns:

chars |> 
  unnest_wider(json) |> 
  select(id, where(is.list))
#> # A tibble: 30 × 8
#>      id titles    aliases    allegiances books     povBooks  tvSeries  playedBy 
#>   <int> <list>    <list>     <list>      <list>    <list>    <list>    <list>   
#> 1  1022 <chr [3]> <chr [4]>  <chr [1]>   <chr [3]> <chr [2]> <chr [6]> <chr [1]>
#> 2  1052 <chr [2]> <chr [11]> <chr [1]>   <chr [2]> <chr [4]> <chr [6]> <chr [1]>
#> 3  1074 <chr [2]> <chr [1]>  <chr [1]>   <chr [3]> <chr [2]> <chr [1]> <chr [1]>
#> 4  1109 <chr [1]> <chr [1]>  <NULL>      <chr [1]> <chr [1]> <chr [1]> <chr [1]>
#> 5  1166 <chr [1]> <chr [1]>  <chr [1]>   <chr [3]> <chr [2]> <chr [2]> <chr [1]>
#> 6  1267 <chr [1]> <chr [1]>  <NULL>      <chr [2]> <chr [1]> <chr [1]> <chr [1]>
#> # … with 24 more rows

Lets explore the titles column. It’s an unnamed list-column, so we’ll unnest it into rows:

chars |> 
  unnest_wider(json) |> 
  select(id, titles) |> 
  unnest_longer(titles)
#> # A tibble: 60 × 2
#>      id titles                                              
#>   <int> <chr>                                               
#> 1  1022 Prince of Winterfell                                
#> 2  1022 Captain of Sea Bitch                                
#> 3  1022 Lord of the Iron Islands (by law of the green lands)
#> 4  1052 Acting Hand of the King (former)                    
#> 5  1052 Master of Coin (former)                             
#> 6  1074 Lord Captain of the Iron Fleet                      
#> # … with 54 more rows

You might expect to see this data in its own table because you could then join back to the characters data as needed. To make this table I’ll do a little cleaning; removing the rows contain empty strings and renaming titles to title since each row now only contains a single title.

titles <- chars |> 
  unnest_wider(json) |> 
  select(id, titles) |> 
  unnest_longer(titles) |> 
  filter(titles != "") |> 
  rename(title = titles)
titles
#> # A tibble: 53 × 2
#>      id title                                               
#>   <int> <chr>                                               
#> 1  1022 Prince of Winterfell                                
#> 2  1022 Captain of Sea Bitch                                
#> 3  1022 Lord of the Iron Islands (by law of the green lands)
#> 4  1052 Acting Hand of the King (former)                    
#> 5  1052 Master of Coin (former)                             
#> 6  1074 Lord Captain of the Iron Fleet                      
#> # … with 47 more rows

Now, for example, we could use this table to all the characters that are captains and see all their titles:

captains <- titles |> filter(str_detect(title, "Captain"))
captains
#> # A tibble: 5 × 2
#>      id title                                 
#>   <int> <chr>                                 
#> 1  1022 Captain of Sea Bitch                  
#> 2  1074 Lord Captain of the Iron Fleet        
#> 3  1166 Captain of the Guard at Sunspear      
#> 4   150 Captain of the Black Wind             
#> 5    60 Captain of the Golden Storm (formerly)

characters |> 
  semi_join(captains) |> 
  select(id, name) |> 
  left_join(titles)
#> Joining, by = "id"
#> Joining, by = "id"
#> # A tibble: 11 × 3
#>      id name              title                                               
#>   <int> <chr>             <chr>                                               
#> 1  1022 Theon Greyjoy     Prince of Winterfell                                
#> 2  1022 Theon Greyjoy     Captain of Sea Bitch                                
#> 3  1022 Theon Greyjoy     Lord of the Iron Islands (by law of the green lands)
#> 4  1074 Victarion Greyjoy Lord Captain of the Iron Fleet                      
#> 5  1074 Victarion Greyjoy Master of the Iron Victory                          
#> 6  1166 Areo Hotah        Captain of the Guard at Sunspear                    
#> # … with 5 more rows

You could imagine creating a table like this for each of the list-columns, then using joins to combine them with the character data as you need it.

25.4.3 A dash of text analysis

What if we wanted to find the most common words in the title? There are plenty of sophisticated ways to do this, but one simple way starts by using str_split() to break each element of title up into words by spitting on " ":

titles |> 
  mutate(word = str_split(title, " "), .keep = "unused")
#> # A tibble: 53 × 2
#>      id word      
#>   <int> <list>    
#> 1  1022 <chr [3]> 
#> 2  1022 <chr [4]> 
#> 3  1022 <chr [11]>
#> 4  1052 <chr [6]> 
#> 5  1052 <chr [4]> 
#> 6  1074 <chr [6]> 
#> # … with 47 more rows

This creates a unnamed variable length list-column, so we can use unnest_longer():

titles |> 
  mutate(word = str_split(title, " "), .keep = "unused") |> 
  unnest_longer(word)
#> # A tibble: 202 × 2
#>      id word      
#>   <int> <chr>     
#> 1  1022 Prince    
#> 2  1022 of        
#> 3  1022 Winterfell
#> 4  1022 Captain   
#> 5  1022 of        
#> 6  1022 Sea       
#> # … with 196 more rows

And then we can count that column to find the most common:

titles |> 
  mutate(word = str_split(title, " "), .keep = "unused") |> 
  unnest_longer(word) |> 
  count(word, sort = TRUE)
#> # A tibble: 78 × 2
#>   word        n
#>   <chr>   <int>
#> 1 of         41
#> 2 the        29
#> 3 Lord        9
#> 4 Hand        6
#> 5 Captain     5
#> 6 King        5
#> # … with 72 more rows

Some of those words are not very interesting so we could create a list of common words to drop. In text analysis this is commonly called stop words.

stop_words <- tribble(
  ~ word,
  "of",
  "the"
)

titles |> 
  mutate(word = str_split(title, " "), .keep = "unused") |> 
  unnest_longer(word) |> 
  anti_join(stop_words) |> 
  count(word, sort = TRUE)
#> Joining, by = "word"
#> # A tibble: 76 × 2
#>   word         n
#>   <chr>    <int>
#> 1 Lord         9
#> 2 Hand         6
#> 3 Captain      5
#> 4 King         5
#> 5 Princess     5
#> 6 Queen        5
#> # … with 70 more rows

Breaking up text into individual fragments is a powerful idea that underlies much of text analysis. If this sounds interesting, I’d recommend reading Text Mining with R by Julia Silge and David Robinson.

25.4.4 Deeply nested

We’ll finish off this case studies with a list-column that’s very deeply nested and requires repeated rounds of unnest_wider() and unnest_longer() to unravel: gmaps_cities. This is a two column tibble containing five city names and the results of using Google’s geocoding API to determine their location:

gmaps_cities
#> # A tibble: 5 × 2
#>   city       json            
#>   <chr>      <list>          
#> 1 Houston    <named list [2]>
#> 2 Washington <named list [2]>
#> 3 New York   <named list [2]>
#> 4 Chicago    <named list [2]>
#> 5 Arlington  <named list [2]>

json is list-column with internal names, so we start with an unnest_wider():

gmaps_cities |> 
  unnest_wider(json)
#> # A tibble: 5 × 3
#>   city       results    status
#>   <chr>      <list>     <chr> 
#> 1 Houston    <list [1]> OK    
#> 2 Washington <list [2]> OK    
#> 3 New York   <list [1]> OK    
#> 4 Chicago    <list [1]> OK    
#> 5 Arlington  <list [2]> OK

This gives us the status and the results. We’ll drop the status column since they’re all OK; in a real analysis, you’d also want capture all the rows where status != "OK" and figure out what went wrong. results is an unnamed list, with either one or two elements (we’ll see why shortly) so we’ll unnest it into rows:

gmaps_cities |> 
  unnest_wider(json) |> 
  select(-status) |> 
  unnest_longer(results)
#> # A tibble: 7 × 2
#>   city       results         
#>   <chr>      <list>          
#> 1 Houston    <named list [5]>
#> 2 Washington <named list [5]>
#> 3 Washington <named list [5]>
#> 4 New York   <named list [5]>
#> 5 Chicago    <named list [5]>
#> 6 Arlington  <named list [5]>
#> # … with 1 more row

Now results is a named list, so we’ll use unnest_wider():

locations <- gmaps_cities |> 
  unnest_wider(json) |> 
  select(-status) |> 
  unnest_longer(results) |> 
  unnest_wider(results)
locations
#> # A tibble: 7 × 6
#>   city       address_components formatted_address   geometry     place_id types 
#>   <chr>      <list>             <chr>               <list>       <chr>    <list>
#> 1 Houston    <list [4]>         Houston, TX, USA    <named list> ChIJAYW… <list>
#> 2 Washington <list [2]>         Washington, USA     <named list> ChIJ-bD… <list>
#> 3 Washington <list [4]>         Washington, DC, USA <named list> ChIJW-T… <list>
#> 4 New York   <list [3]>         New York, NY, USA   <named list> ChIJOwg… <list>
#> 5 Chicago    <list [4]>         Chicago, IL, USA    <named list> ChIJ7cv… <list>
#> 6 Arlington  <list [4]>         Arlington, TX, USA  <named list> ChIJ05g… <list>
#> # … with 1 more row

Now we can see why two cities got two results: Washington matched both the Washington state and Washington, DC, and Arlington matched Arlington, Virginia and Arlington, Texas.

There are few different places we could go from here. We might want to determine the exact location of the match, which is stored in the geometry list-column:

locations |> 
  select(city, formatted_address, geometry) |> 
  unnest_wider(geometry)
#> # A tibble: 7 × 6
#>   city     formatted_addre… bounds       location     location_type viewport    
#>   <chr>    <chr>            <list>       <list>       <chr>         <list>      
#> 1 Houston  Houston, TX, USA <named list> <named list> APPROXIMATE   <named list>
#> 2 Washing… Washington, USA  <named list> <named list> APPROXIMATE   <named list>
#> 3 Washing… Washington, DC,… <named list> <named list> APPROXIMATE   <named list>
#> 4 New York New York, NY, U… <named list> <named list> APPROXIMATE   <named list>
#> 5 Chicago  Chicago, IL, USA <named list> <named list> APPROXIMATE   <named list>
#> 6 Arlingt… Arlington, TX, … <named list> <named list> APPROXIMATE   <named list>
#> # … with 1 more row

That gives us new bounds (which gives a rectangular region) and the midpoint in location, which we can unnest to get latitude (lat) and longitude (lng):

locations |> 
  select(city, formatted_address, geometry) |> 
  unnest_wider(geometry) |> 
  unnest_wider(location)
#> # A tibble: 7 × 7
#>   city     formatted_addre… bounds         lat    lng location_type viewport    
#>   <chr>    <chr>            <list>       <dbl>  <dbl> <chr>         <list>      
#> 1 Houston  Houston, TX, USA <named list>  29.8  -95.4 APPROXIMATE   <named list>
#> 2 Washing… Washington, USA  <named list>  47.8 -121.  APPROXIMATE   <named list>
#> 3 Washing… Washington, DC,… <named list>  38.9  -77.0 APPROXIMATE   <named list>
#> 4 New York New York, NY, U… <named list>  40.7  -74.0 APPROXIMATE   <named list>
#> 5 Chicago  Chicago, IL, USA <named list>  41.9  -87.6 APPROXIMATE   <named list>
#> 6 Arlingt… Arlington, TX, … <named list>  32.7  -97.1 APPROXIMATE   <named list>
#> # … with 1 more row

Extracting the bounds requires a few more steps

locations |> 
  select(city, formatted_address, geometry) |> 
  unnest_wider(geometry) |> 
  # focus on the variables of interest
  select(!location:viewport) |>
  unnest_wider(bounds)
#> # A tibble: 7 × 4
#>   city       formatted_address   northeast        southwest       
#>   <chr>      <chr>               <list>           <list>          
#> 1 Houston    Houston, TX, USA    <named list [2]> <named list [2]>
#> 2 Washington Washington, USA     <named list [2]> <named list [2]>
#> 3 Washington Washington, DC, USA <named list [2]> <named list [2]>
#> 4 New York   New York, NY, USA   <named list [2]> <named list [2]>
#> 5 Chicago    Chicago, IL, USA    <named list [2]> <named list [2]>
#> 6 Arlington  Arlington, TX, USA  <named list [2]> <named list [2]>
#> # … with 1 more row

I then rename southwest and northeast (the corners of the rectangle) so I can use names_sep to create short but evocative names:

locations |> 
  select(city, formatted_address, geometry) |> 
  unnest_wider(geometry) |> 
  select(!location:viewport) |>
  unnest_wider(bounds) |> 
  rename(ne = northeast, sw = southwest) |> 
  unnest_wider(c(ne, sw), names_sep = "_") 
#> # A tibble: 7 × 6
#>   city       formatted_address   ne_lat ne_lng sw_lat sw_lng
#>   <chr>      <chr>                <dbl>  <dbl>  <dbl>  <dbl>
#> 1 Houston    Houston, TX, USA      30.1  -95.0   29.5  -95.8
#> 2 Washington Washington, USA       49.0 -117.    45.5 -125. 
#> 3 Washington Washington, DC, USA   39.0  -76.9   38.8  -77.1
#> 4 New York   New York, NY, USA     40.9  -73.7   40.5  -74.3
#> 5 Chicago    Chicago, IL, USA      42.0  -87.5   41.6  -87.9
#> 6 Arlington  Arlington, TX, USA    32.8  -97.0   32.6  -97.2
#> # … with 1 more row

Note that I unnest the two columns simultaneously by supplying a vector of variable names to unnest_wider().

This one place where hoist(), mentioned briefly above, can be useful. Once you’ve discovered the path to get to the components you’re interested in, you can extract them directly using hoist():

locations |> 
  select(city, formatted_address, geometry) |> 
  hoist(
    geometry,
    ne_lat = c("bounds", "northeast", "lat"),
    sw_lat = c("bounds", "southwest", "lat"),
    ne_lng = c("bounds", "northeast", "lng"),
    sw_lng = c("bounds", "southwest", "lng"),
  )
#> # A tibble: 7 × 7
#>   city       formatted_address   ne_lat sw_lat ne_lng sw_lng geometry        
#>   <chr>      <chr>                <dbl>  <dbl>  <dbl>  <dbl> <list>          
#> 1 Houston    Houston, TX, USA      30.1   29.5  -95.0  -95.8 <named list [4]>
#> 2 Washington Washington, USA       49.0   45.5 -117.  -125.  <named list [4]>
#> 3 Washington Washington, DC, USA   39.0   38.8  -76.9  -77.1 <named list [4]>
#> 4 New York   New York, NY, USA     40.9   40.5  -73.7  -74.3 <named list [4]>
#> 5 Chicago    Chicago, IL, USA      42.0   41.6  -87.5  -87.9 <named list [4]>
#> 6 Arlington  Arlington, TX, USA    32.8   32.6  -97.0  -97.2 <named list [4]>
#> # … with 1 more row

If these case studies have whetted your appetite for more real-life rectangling, you can see a few more examples in vignette("rectangling", package = "tidyr").

25.4.5 Exercises

  1. Roughly estimate when gh_repos was created. Why can you only roughly estimate the date?

  2. The owner column of gh_repo contains a lot of duplicated information because each owner can have many repos. Can you construct a owners data frame that contains one row for each owner? (Hint: does distinct() work with list-cols?)

  3. Explain the following code line-by-line. Why is it interesting? Why does it work for this dataset but might not work in general?

    tibble(json = got_chars) |> 
      unnest_wider(json) |> 
      select(id, where(is.list)) %>% 
      pivot_longer(
        where(is.list), 
        names_to = "name", 
        values_to = "value"
      ) %>% 
      unnest_longer(value)
    #> # A tibble: 486 × 3
    #>      id name    value                                               
    #>   <int> <chr>   <chr>                                               
    #> 1  1022 titles  Prince of Winterfell                                
    #> 2  1022 titles  Captain of Sea Bitch                                
    #> 3  1022 titles  Lord of the Iron Islands (by law of the green lands)
    #> 4  1022 aliases Prince of Fools                                     
    #> 5  1022 aliases Theon Turncloak                                     
    #> 6  1022 aliases Reek                                                
    #> # … with 480 more rows
  4. In gmaps_cities, what does address_components contain? Why does the length vary between rows? Unnest it appropriately to figure it out. (Hint: types always appears to contain two elements. Does unnest_wider() make it easier to work with than unnest_longer()?) .

25.5 JSON

All of the case studies in the previous section came originally as JSON, one of the most common sources of hierarchical data. In this section, you’ll learn more about JSON and some common problems you might have. JSON, short for javascript object notation, is a data format that grew out of the javascript programming language and has become an extremely common way of representing data.

{
  "name1": "value1",
  "name2": "value2"
}

Which in R you might represent as:

list(
  name1 = "value1",
  name2 = "value2"
)
#> $name1
#> [1] "value1"
#> 
#> $name2
#> [1] "value2"

There are five types of things that JSON can represent

{
  "strings": "are surrounded by double doubles",
  "numbers": 123456,
  "boolean": [false, true],
  "arrays": [1, 2, 3, 4, 5],
  "objects": {
    "name1": "value1",
    "name2": "value2"
  },
  "null": null
}

You’ll notice that these types don’t embrace many of the types you’ve learned earlier in the book like factors, and date-times. This is important: typically these data types will be encoded as string, and you’ll need coerce to the correct data type.

Most of the time you won’t deal with JSON directly, instead you’ll use the jsonlite package, by Jeroen Oooms, to load it into R as a nested list.

25.5.1 Data frames

JSON doesn’t have any 2-dimension data structures, so how would you represent a data frame?

df <- tribble(
  ~x, ~y,
  "a", 10,
  "x", 3
)

There are two ways: you can either make an struct of arrays, or an array of structs.

{
  "x": ["a", "x"],
  "y": [10, 3]
}
[
  {"x": "a", "y": 10},
  {"x": "x", "y": 3}
]
df_col <- jsonlite::fromJSON('
  {
    "x": ["a", "x"],
    "y": [10, 3]
  }
')
tibble(json = list(df_col)) |> 
  unnest_wider(json) |> 
  unnest_longer(everything())
#> # A tibble: 2 × 2
#>   x         y
#>   <chr> <int>
#> 1 a        10
#> 2 x         3
df_row <- jsonlite::fromJSON(simplifyVector = FALSE, '
  [
    {"x": "a", "y": 10},
    {"x": "x", "y": 3}
  ]
')
tibble(json = list(df_row)) |> 
  unnest_longer(json) |> 
  unnest_wider(json)
#> # A tibble: 2 × 2
#>   x         y
#>   <chr> <int>
#> 1 a        10
#> 2 x         3

Note that we have to wrap it in a list() because we have a single “thing” to unnest.


  1. This is an RStudio feature.↩︎