4.1 Small multiples with all headers present for each multiple

The code to import one of these multiples will be simple.

cells %>%
  behead("up-left", subject) %>%
  behead("up", header) %>%
  select(-col, -local_format_id) %>%
  spatter(header) %>%
  select(-row)

The first table is in rows 1 to 4, columns 1 to 3, so we start by writing the code to import only that table.

path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "small-multiples") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric, local_format_id)

table1 <- dplyr::filter(all_cells, row %in% 1:4, col %in% 1:3)

table1 %>%
  behead("up-left", subject) %>%
  behead("up", header) %>%
  select(-col, -local_format_id) %>%
  spatter(header) %>%
  select(-row)

## # A tibble: 2 x 4
##   subject  Grade Name    Score
##   <chr>    <chr> <chr>   <dbl>
## 1 Classics F     Matilda     1
## 2 Classics D     Olivia      2

We wrap that code in a function, to be applied to each separate table.

unpivot <- function(cells) {
  cells %>%
    behead("up-left", subject) %>%
    behead("up", header) %>%
    select(-col, -local_format_id) %>%
    spatter(header) %>%
    select(-row)
}

Now we partition the spreadsheet into the separate tables. This is done by identifying a corner cell in each table.

formats <- xlsx_formats(path)
italic <- which(formats$local$font$italic)

corners <-
  all_cells %>%
  dplyr::filter(local_format_id %in% italic) %>%
  select(row, col)

partitions <- partition(all_cells, corners)
partitions

## # A tibble: 4 x 3
##   corner_row corner_col cells            
##        <dbl>      <dbl> <list>           
## 1          1          1 <tibble [10 × 6]>
## 2          1          5 <tibble [10 × 6]>
## 3          6          1 <tibble [10 × 6]>
## 4          6          5 <tibble [10 × 6]>

Finally, map the unpivoting function over the partitions, and combine the results.

partitions %>%
  mutate(cells = map(cells, unpivot)) %>%
  unnest() %>%
  select(-corner_row, -corner_col)

## Warning: `cols` is now required.
## Please use `cols = c(cells)`

## # A tibble: 8 x 4
##   subject  Grade Name    Score
##   <chr>    <chr> <chr>   <dbl>
## 1 Classics F     Matilda     1
## 2 Classics D     Olivia      2
## 3 History  D     Matilda     3
## 4 History  C     Olivia      4
## 5 Music    B     Matilda     5
## 6 Music    B     Olivia      6
## 7 Drama    A     Matilda     7
## 8 Drama    A     Olivia      8