17  Big data formats and hierarchical data

Objectives

  • Understand why CSVs aren’t enough. CSV files are easy to read but inefficient: reading one means scanning every character and re-guessing every column’s type, and they don’t scale well to tens of millions of rows. Learn about the parquet format, an open, column-oriented file format widely used by big-data systems, and why it reads faster, writes smaller, and preserves types a CSV cannot.
  • Use Apache Arrow for larger-than-memory data. The arrow package provides a dplyr backend so you can analyze larger-than-memory datasets using familiar syntax. Learn to open datasets lazily with open_dataset(), and recognize to_duckdb() as a bridge back to the SQL tools from last session.
  • Describe lists and hierarchical data. Hierarchical or tree-like data structures are common, especially from web APIs or JSON. A list is a vector that can store elements of different types; lists underpin hierarchical data. Understand the difference between atomic vectors and lists, and recognize list-columns inside tibbles.
  • Rectangling with tidyr. Data rectangling converts hierarchical data into tidy rectangular tables. Learn to unnest list-columns with unnest_longer() (which repeats rows) and unnest_wider() (which spreads components across columns), plus hoist() for pulling specific fields out directly. Appreciate when each is appropriate, and how each one fails (loudly, or silently) when a list-column isn’t as regular as it looks.
  • Prepare for web scraping. Hierarchical data often comes from the web. Being comfortable with lists and unnesting will prepare you for the next session on scraping HTML pages.

Notes

library(tidyverse)

Why consider big-data formats?

CSV files are simple and human-readable, readable by nearly every tool that exists, but that simplicity has a cost. Reading a large CSV into R means scanning every character and re-deriving every column’s type from scratch, which grows slow and memory-hungry once a dataset reaches tens of millions of rows. Parquet is a column-oriented, compressed, open file format built specifically for this problem. Because it stores data column by column rather than row by row, a query that only needs a few columns can skip reading the rest of the file entirely, and because it records each column’s actual type, reading a parquet file back never involves guessing.

Apache Arrow and the arrow package

Apache Arrow is a multi-language toolkit for efficient in-memory columnar data, and the arrow R package wraps it with a dplyr backend that looks and feels like an ordinary tibble. Instead of reading an entire file into memory, open_dataset() scans just enough of it to learn the schema (column names and types) and then reads data lazily, as you actually request it, the same way tbl() did for a database connection last session.

library(arrow)

write_parquet(starwars, "starwars.parquet")

sw_ds <- open_dataset("starwars.parquet")

sw_ds |>
  group_by(species) |>
  summarise(n = n(), avg_height = mean(height, na.rm = TRUE), .groups = "drop") |>
  arrange(desc(avg_height)) |>
  collect() |>
  head()
# A tibble: 6 × 3
  species      n avg_height
  <chr>    <int>      <dbl>
1 Quermian     1       264 
2 Wookiee      2       231 
3 Kaminoan     2       221 
4 Kaleesh      1       216 
5 Gungan       3       209.
6 Pau'an       1       206

group_by() and summarise() here are recorded, not executed, exactly like a dbplyr query; collect() is what actually reads the necessary columns from disk and computes the result. For a dataset split across many files, write_dataset() can partition the output into a directory of files (commonly one per year, or per some other frequently-filtered variable), and to_duckdb() hands an Arrow dataset off to duckdb for the rare query that dplyr’s Arrow backend doesn’t support, without copying the data.

Lists and hierarchical data

The rest of this session covers hierarchical data, which arises whenever observations are nested inside one another, as they commonly are in web APIs, JSON files, or other tree-shaped documents. Working with hierarchical data in R means working with lists. Unlike an atomic vector, a list’s elements can be of different types and different lengths; you build one with list(), optionally naming each component the way you’d name a tibble’s columns.

x1 <- list(1:4, "a string", TRUE)      # unnamed list
x2 <- list(numbers = 1:3, letters = letters[1:3], flag = FALSE)   # named list
x2
$numbers
[1] 1 2 3

$letters
[1] "a" "b" "c"

$flag
[1] FALSE

Lists get interesting inside a tibble as list-columns. starwars (from dplyr) has three: films, vehicles, and starships each hold a variable-length character vector per row, since one character can appear in several films.

glimpse(starwars)
Rows: 87
Columns: 14
$ name       <chr> "Luke Skywalker", "C-3PO", "R2-D2", "Darth Vader", "Leia Or…
$ height     <int> 172, 167, 96, 202, 150, 178, 165, 97, 183, 182, 188, 180, 2…
$ mass       <dbl> 77.0, 75.0, 32.0, 136.0, 49.0, 120.0, 75.0, 32.0, 84.0, 77.…
$ hair_color <chr> "blond", NA, NA, "none", "brown", "brown, grey", "brown", N…
$ skin_color <chr> "fair", "gold", "white, blue", "white", "light", "light", "…
$ eye_color  <chr> "blue", "yellow", "red", "yellow", "brown", "blue", "blue",…
$ birth_year <dbl> 19.0, 112.0, 33.0, 41.9, 19.0, 52.0, 47.0, NA, 24.0, 57.0, …
$ sex        <chr> "male", "none", "none", "male", "female", "male", "female",…
$ gender     <chr> "masculine", "masculine", "masculine", "masculine", "femini…
$ homeworld  <chr> "Tatooine", "Tatooine", "Naboo", "Tatooine", "Alderaan", "T…
$ species    <chr> "Human", "Droid", "Droid", "Human", "Human", "Human", "Huma…
$ films      <list> <"A New Hope", "The Empire Strikes Back", "Return of the J…
$ vehicles   <list> <"Snowspeeder", "Imperial Speeder Bike">, <>, <>, <>, "Imp…
$ starships  <list> <"X-wing", "Imperial shuttle">, <>, <>, "TIE Advanced x1",…

Each row is still one character, but a cell in films is itself a whole vector of titles rather than a single value, which is exactly the kind of structure that needs rectangling before you can analyze it with the tools from earlier sessions.

Unnesting with unnest_longer(), unnest_wider(), and hoist()

tidyr provides two core rectangling functions. unnest_longer() takes each element of a list-column and gives it its own row, repeating the rest of that row’s values as needed; use it when a list-column holds a plain vector, like films. unnest_wider() instead takes a list of named pieces (a record, essentially) and spreads those names across new columns; use it when each list element looks like a small object with fields, not a plain vector.

characters_by_film <- starwars |>
  select(name, films) |>
  unnest_longer(films) |>
  count(films, name = "n_characters") |>
  arrange(desc(n_characters))

characters_by_film |> head()
# A tibble: 6 × 2
  films                   n_characters
  <chr>                          <int>
1 Attack of the Clones              40
2 Revenge of the Sith               34
3 The Phantom Menace                34
4 Return of the Jedi                20
5 A New Hope                        18
6 The Empire Strikes Back           16

When you only need one or two specific fields out of a list-column of records, hoist() pulls them out directly by name (or by position), skipping the intermediate step of unnesting every field just to select the ones you wanted.

records <- tibble(
  id = 1:2,
  info = list(list(name = "Alice", age = 30), list(name = "Bob", age = 25))
)

records |> hoist(info, name = "name", age = "age")
# A tibble: 2 × 3
     id name    age
  <int> <chr> <dbl>
1     1 Alice    30
2     2 Bob      25

JSON and other hierarchical sources

Much hierarchical data arrives from the web as JSON, a text format for nested objects ({...}, named fields) and arrays ([...], unnamed sequences). jsonlite::parse_json() (for a JSON string already in R) and read_json() (for a JSON file) both convert JSON directly into the same kind of nested list you’ve been working with, ready for the same unnest_wider()/unnest_longer() toolkit.

library(jsonlite)

json_text <- '[
  {"name": "Luke", "films": ["A New Hope", "Empire"]},
  {"name": "Leia", "films": ["A New Hope"]}
]'

parse_json(json_text) |>
  tibble(record = _) |>
  unnest_wider(record) |>
  unnest_longer(films)
# A tibble: 3 × 2
  name  films     
  <chr> <chr>     
1 Luke  A New Hope
2 Luke  Empire    
3 Leia  A New Hope

jsonlite::fromJSON() also exists and tries to simplify nested JSON into a data frame automatically, but that automatic simplification can behave inconsistently depending on how regular the JSON structure happens to be; parsing first and rectangling explicitly, as above, is more predictable and is what the rest of this section assumes.

Fringe cases and common pitfalls

ExampleExample 16.1

unnest_longer() refuses to combine inconsistent types into one column.

mixed <- tibble(id = 1:3, val = list(1, "a", TRUE))
mixed |> unnest_longer(val)
Error in `col_to_long()`:
! Can't combine `..1$val` <double> and `..3$val` <character>.

Each element of val is a different type (a double, a string, a logical), and unnest_longer() needs to combine them into a single ordinary column, which requires them to share a common type the same way if_else() and coalesce() did back in Session 10. Rather than silently coercing everything to character (or leaving a confusing list-column behind), it errors immediately. The fix is to make the types consistent yourself before unnesting, typically by mapping every element through a shared conversion first:

mixed |> mutate(val = map(val, as.character)) |> unnest_longer(val)
# A tibble: 3 × 2
     id val  
  <int> <chr>
1     1 1    
2     2 a    
3     3 TRUE
ExampleExample 16.2

unnest_wider() refuses to create a column with the same name as one that already exists.

df <- tibble(id = 1, info = list(list(id = "x1", name = "Alice")))
df |> unnest_wider(info)
Error in `unnest_wider()`:
! Can't duplicate names between the affected columns and the original
  data.
✖ These names are duplicated:
  ℹ `id`, from `info`.
ℹ Use `names_sep` to disambiguate using the column name.
ℹ Or use `names_repair` to specify a repair strategy.

The list-column info has its own field called id, which collides with the tibble’s existing id column, and unnest_wider() won’t silently overwrite (or silently rename) either one. The error message tells you exactly what to do: names_sep prefixes every new column with the list-column’s name, guaranteeing no collision.

df |> unnest_wider(info, names_sep = "_")
# A tibble: 1 × 3
     id info_id info_name
  <dbl> <chr>   <chr>    
1     1 x1      Alice
ExampleExample 16.3

An empty or NULL list element vanishes entirely by default, rather than becoming a row of NA.

df <- tibble(id = 1:3, val = list(1, NULL, 3))
df |> unnest_longer(val)
# A tibble: 2 × 2
     id   val
  <int> <dbl>
1     1     1
2     3     3

id = 2’s row is simply gone, not present with an NA for val, because unnest_longer()’s default behavior is to drop rows whose list element has nothing to contribute. If losing that row silently would be a problem, for instance if you’re about to count observations per group, keep_empty = TRUE keeps it, filled with NA, instead:

df |> unnest_longer(val, keep_empty = TRUE)
# A tibble: 3 × 2
     id   val
  <int> <dbl>
1     1     1
2     2    NA
3     3     3
ExampleExample 16.4

Unlike CSV, a parquet round trip preserves every type exactly, factor levels included.

original <- tibble(
  id = 1:3,
  grade = factor(c("A", "B", "A"), levels = c("A", "B", "C")),
  test_date = as.Date(c("2026-01-15", "2026-01-16", "2026-01-17"))
)

pq_path <- tempfile(fileext = ".parquet")
write_parquet(original, pq_path)
back <- read_parquet(pq_path)

identical(original, back)
[1] TRUE

Back in Session 7, writing this same kind of tibble to CSV and reading it back turned the factor into a plain character column, silently losing its level order. Parquet’s format records each column’s actual type (including the fact that grade is a factor, and exactly which levels it has, in what order), so read_parquet() doesn’t have to guess anything back, and the round trip is byte-for-byte identical. This is exactly why parquet (or write_rds(), for a single-file, R-only alternative) is the better choice any time you’re saving an intermediate result you plan to read back into R yourself, rather than handing to some other tool.

Recap

Term Definition
Parquet A column-oriented, compressed, typed file format; faster to read selectively and exact on a round trip, unlike CSV.
open_dataset() Opens a (possibly huge, possibly multi-file) dataset lazily, reading only what a later collect() actually requests.
write_dataset() Writes a dataset to disk, optionally partitioned into multiple files by a grouping variable.
to_duckdb() Hands an Arrow dataset to duckdb without copying it, for queries Arrow’s dplyr backend doesn’t support.
List A vector whose elements can be of different types and lengths; the building block of hierarchical data.
List-column A column in a tibble whose cells are themselves lists, holding a variable number of values per row.
unnest_longer() Gives each element of a list-column its own row, repeating the rest of that row; requires all elements to share a compatible type.
unnest_wider() Spreads a list-column of named records into new columns; requires names_sep if a name collides with an existing column.
hoist() Extracts specific named (or positioned) fields out of a list-column directly, without unnesting every field.
keep_empty Controls whether unnest_longer() drops a row with an empty/NULL list element (the default) or keeps it as NA.

Check your understanding

NoteProblems
  1. Why can a query against a partitioned parquet dataset be faster than the same query against an equivalent CSV file, even before considering compression?
  2. You call unnest_longer() on a list-column and get an error about incompatible types instead of a rectangled tibble. What is the most likely cause, and how would you fix it?
  3. After unnest_wider(info), you get an error that a name is duplicated between info and the original data. What does names_sep actually do to fix this?
  4. You unnest_longer() a list-column and notice your result has fewer rows than you expected, with no error or warning. What is the most likely explanation, and how would you confirm it?
  5. Why does a tibble survive a round trip through write_parquet()/read_parquet() with identical() returning TRUE, when the same tibble written to CSV and read back would not be identical to the original?

  1. A column-oriented format like parquet lets a query read only the columns it actually needs, skipping the rest of the file entirely, and partitioning by a frequently-filtered variable (like year) lets a query skip whole files that can’t possibly match the filter. A row-oriented CSV has to scan every character of every row, regardless of which columns or values the query actually cares about.

  2. The list-column’s elements don’t all share a common type (for example, some are numbers and some are strings), and unnest_longer() refuses to guess how to combine them into a single ordinary column, the same way if_else() and coalesce() refuse to combine incompatible types. Converting every element to a shared type first, for example with mutate(col = map(col, as.character)), before unnesting resolves it.

  3. names_sep prefixes every new column created from the list-column with the list-column’s own name (and a separator), so a field called id inside a list-column called info becomes info_id instead of a plain id that collides with an existing id column in the tibble.

  4. By default, unnest_longer() drops any row whose list element is empty or NULL, rather than keeping that row with an NA. Comparing the row count before and after unnesting, or re-running with keep_empty = TRUE and checking for new NA rows, would confirm whether this is what happened.

  5. Parquet records each column’s actual type as part of the file itself (including that a column is a factor, and exactly which levels it has, in what order), so read_parquet() doesn’t need to guess anything back. A CSV file has no way to store that type information at all, so read_csv() has to re-guess every column’s type from scratch, which is exactly how a factor column comes back as plain character text instead.