4.4 Implied multiples

Implied multiples look like a single table, but many of the headers appear more than once. There is a dominant set of headers that are on the same ‘level’ (e.g. in the same row) as the other headers.

See a real-life case study

In the example, the header “Grade” is repeated, but it really belongs in each case to the header “Classics”, “History”, “Music” or “Drama”. Those subject headers serve two purposes: as title of each small multiple, and as the unstated “Score” header of their columns. The difficulty is in associating a grade with its corresponding score.

  1. Filter for the “Classics”, “History”, “Music” and “Drama” headers, and assign them to a variable to be enhead()ed later. You could think of this as faking a set of headers that doesn’t exist, but is implied.
  2. Meanwhile, behead() the original “Classics”, “History” (etc.) cells and then overwrite them with “Score”.
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "implied-multiples") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric)

Filter for the “Classics”, “History”, “Music” and “Drama” headers, and assign them to a variable to be enhead()ed later.

subjects <-
  all_cells %>%
  dplyr::filter(col >= 2, row == 2, character != "Grade") %>%
  select(row, col, subject = character)
subjects
## # A tibble: 4 x 3
##     row   col subject 
##   <int> <int> <chr>   
## 1     2     2 Classics
## 2     2     4 History 
## 3     2     6 Music   
## 4     2     8 Drama

Meanwhile, behead() the original “Classics”, “History” (etc.) cells and then overwrite them with “Score”.

all_cells %>%
  behead("up-left", "field") %>%
  behead("up", "header") %>%
  behead("left", "name") %>%
  enhead(subjects, "up-left") %>% # Reattach the filtered subject headers
  mutate(header = if_else(header == "Grade", header, "Score")) %>%
  select(-col) %>%
  spatter(header) %>%
  select(-row)
## # A tibble: 8 x 5
##   field       name    subject  Grade Score
##   <chr>       <chr>   <chr>    <chr> <dbl>
## 1 Humanities  Matilda Classics F         1
## 2 Humanities  Matilda History  D         3
## 3 Performance Matilda Drama    A         7
## 4 Performance Matilda Music    B         5
## 5 Humanities  Olivia  Classics D         2
## 6 Humanities  Olivia  History  C         4
## 7 Performance Olivia  Drama    A         8
## 8 Performance Olivia  Music    B         6