pivot-tables.Rmd

# Pivot tables {#pivot}

![](images/pivot-annotations.png)

This part introduces pivot tables.  [Tidyxl](https://nacnudus.github.io/tidyxl)
and [unpivotr](https://nacnudus.github.io/unpivotr) come into their own here,
and are (as far as I know) the only packages to acknowledge the intuitive
grammar of pivot tables.

Pivot tables are ones with more than one row of column headers, or more than one
column of row headers, or both (and there can be more complex arrangements).
Tables in that form take up less space on a page or a screen than 'tidy' tables,
and are easier for humans to read. But most software can't interpret or traverse
data in that form; it must first be reshaped into a long, 'tidy' form, with a
single row of column headers.

It takes a lot of code to reshape a pivot table into a 'tidy' one, and the code
has to be bespoke for each table. There's no general solution, because it is
ambiguous whether a given cell is part of a header or part of the data.

There are some ambiguities in 'tidy' tables, too, which is why most functions
for reading csv files allow you to specify whether the first row of the data is
a header, and how many rows to skip before the data begins.  Functions often
guess, but they can never be certain.

Pivot tables, being more complex, are so much more ambiguous that it isn't
reasonable to import them with a single function.  A better way is to break the
problem down into steps:

1. Identify which cells are headers, and which are data.
1. State how the data cells relate to the header cells.

The first step is a matter of traversing the cells, which is *much easier* if
you load them with the [tidyxl](https://nacnudus.github.io/tidyxl) package, or
pass the table through `as_cells()` in the
[unpivotr](https://nacnudus.github.io/unpivotr) package.  This gives you a table
of cells and their properties; one row of the table describes one cell of the
source table or spreadsheet.  The first two properties are the row and column
position of the cell, which makes it easy to filter for cells in a particular
region of the spreadsheet.  If the first row of cells is a header row, then you
can filter for `row == 1`.

Here is an example of a pivot table where the first two rows, and the first two
columns, are headers.  The other cells contain the data.  First, see how the
cells are laid out in the source file by importing it with readxl.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
original <- read_excel(path, sheet = "pivot-annotations", col_names = FALSE)
print(original, n = Inf)
```

Compare that with the long set of cells, one per row, that tidyxl gives.  (Only
a few properties of each cell are shown, to make it easier to read).

```{r}
cells <- xlsx_cells(path, sheets = "pivot-annotations")
select(cells, row, col, data_type, character, numeric) %>%
  print(n = 20)
```

A similar result is obtained via `unpivotr::as_cells()`.

```{r}
original <- read_excel(path, sheet = "pivot-annotations", col_names = FALSE)
as_cells(original) %>%
  arrange(row, col) %>%
  print(n = 20)
```

(One difference is that `read_excel()` has filled in some missing cells with
blanks, which `as_cells()` retains.  Another is that `read_excel()` has
coerced all data types to `character`, whereas `xlsx_cells()` preserved the
original data types.)

The tidyxl version is easier to traverse, because it describes the position of
each cell as well as the value.  To filter for the first row of headers:

```{r}
dplyr::filter(cells, row == 2, !is_blank) %>%
  select(row, col, character, numeric)
```

Or to filter for cells containing data (in this case, we know that only data
cells are numeric)

```{r}
dplyr::filter(cells, data_type == "numeric") %>%
  select(row, col, numeric)
```

By identifying the header cells separately from the data cells, and knowing
exactly where they are on the sheet, we can associated the data cells with the
relevant headers.

To a human it is intuitive that the cells below and to the right of the header
`Male` represent males, and that ones to the right of and below the header
`Postgraduate qualification` represent people with postgraduate qualifications,
but it isn't so obvious to the computer.  How would the computer know that the
header `Male` doesn't also relate to the column of cells below and to the left,
beginning with `2`?

This section shows how you can express the relationships between headers and
data cells, using the [unpivotr](https://nacnudus.github.io/unpivotr) package.

## Simple unpivoting {#pivot-simple}

The `behead()` function takes one level of headers from a pivot table and makes
it part of the data.  Think of it like `tidyr::gather()`, except that it works
when there is more than one row of headers (or more than one column of
row-headers), and it only works on tables that have first come through
`as_cells()` or `tidyxl::xlsx_cells()`.

### Two clear rows of text column headers, left-aligned

![](images/pivot-annotations.png)

Here we have a pivot table with two rows of column headers.  The first row of
headers is left-aligned, so `"Female"` applies to the first two columns of data,
and `"Male"` applies to the next two.  The second row of headers has a header in
every column.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(col >= 4, !is_blank) %>% # Ignore the row headers in this example
  select(row, col, data_type, character, numeric)
all_cells
```

The `behead()` function takes the 'melted' output of `as_cells()`,
`tidyxl::xlsx_cells()`, or a previous `behead()`, and three more arguments to
specify how the header cells relate to the data cells.

The outermost header is the top row, `"Female" NA "Male" NA`.  The `"Female"`
and `"Male"` headers are up-and-to-the-left-of the data cells.  We express
this as `"up-left"`.  We also give the headers a name, `sex`, and say which
column of `all_cells` contains the value of the header cells -- it's usually the
`character` column.

```{r}
all_cells %>%
  behead("up-left", sex)
```

That did half the job.  The value 2 in row 4 column 5 is indeed a score of a
female. But the value `"matilda"` in row 3 column 4 isn't a population -- it's
another header.  The next step is to strip that second level of column headers.
This time, the direction is `"up"`, because the headers are directly
up from the associated data cells, and we call it `name`, because it represents
names of people.

```{r}
all_cells %>%
  behead("up-left", sex) %>%
  behead("up", `name`)
```

A final step is a normal clean-up.  We drop the `row`, `col` and `character`
columns, and we rename the `numeric` column to `score`, which is what it
represents.

```{r}
all_cells %>%
  behead("up-left", sex) %>%
  behead("up", `name`) %>%
  select(score = numeric, sex, `name`)
```

### Two clear rows and columns of text headers, top-aligned and left-aligned

![](images/pivot-annotations.png)

There are no new techniques are used, just more directions: `"left"` for headers
directly to the left of the data cells, and `"left-up"` for headers left-then-up
from the data cells.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric) %>%
  print()

all_cells %>%
  behead("up-left", sex) %>%   # As before
  behead("up", `name`) %>%     # As before
  behead("left-up", field) %>% # Left-and-above
  behead("left", subject) %>%  # Directly left
  rename(score = numeric) %>%
  select(-row, -col, -character)
```

### Multiple rows or columns of headers, with meaningful formatting

![](images/pivot-annotations.png)

This is a combination of the previous section with [meaningfully formatted
rows](tidy-formatted-rows).  The section [meaninfully formatted
cells](tidy-formatted-cells) doesn't work here, because the unpivoting of
multiple rows/columns of headers complicates the relationship between the data
and the formatting.

1. Unpivot the multiple rows/columns of headers, as above, but keep the `row`
   and `col` of each data cell.
1. Collect the `row`, `col` and formatting of each data cell.
1. Join the data to the formatting by the `row` and `col`.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric) %>%
  print()

unpivoted <-
  all_cells %>%
  behead("up-left", sex) %>%   # As before
  behead("up", `name`) %>%     # As before
  behead("left-up", field) %>% # Left-and-above
  behead("left", subject) %>%  # Directly left
  rename(score = numeric) %>%
  select(-character)                # Retain the row and col for now
unpivoted

# `formats` is a pallette of fill colours that can be indexed by the
# `local_format_id` of a given cell to get the fill colour of that cell
fill_colours <- xlsx_formats(path)$local$fill$patternFill$fgColor$rgb
fill_colours

# Import all the cells, filter out the header row, filter for the first column,
# and create a new column `approximate` based on the fill colours, by looking up
# the local_format_id of each cell in the `formats` pallette.
annotations <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(row >= 4, col >= 4) %>% # Omit the headers
  mutate(fill_colour = fill_colours[local_format_id]) %>%
  select(row, col, fill_colour)
annotations

left_join(unpivoted, annotations, by = c("row", "col")) %>%
  select(-row, -col)
```

### Mixed headers and notes in the same row/column, distinguished by formatting

![](images/pivot-notes.png)

This needs two passes over each row/column that contains a mixture.  The first
pass, with `behead_if()` is to deal with the cells that are headers, and the
second pass, with `dplyr::filter()` removes the remaining cells that are notes.

The `behead_if()` function takes predicate functions to choose which cells are
headers.

```{r, eval = FALSE}
# only treat bold cells beginning "Country: " as a header
cells %>%
  behead_if(formats$local$font$bold[local_format_id], # true for bold cells
            str_detect(character, "^Country: "),      # true for "Country: ..."
            direction = "left-up",                    # argument must be named
            name = "country_name") %>%
  dplyr::filter(col != 1L)                            # discard remaining cells
```

Note that the `direction` and `name` arguments must now be named, because they
follow the `...`.

After `behead_if()`, any cells that haven't been treated as headers will still
exist, so if you want to discard them then use `dplyr::filter()` on the column
or row number.

In the screenshot above, cells with italic or red text aren't headers, even
though they are in amongst header cells.

First, identify the IDs of formats that have italic or red text.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
formats <- xlsx_formats(path)

italic <- formats$local$font$italic

# For 'red' we can either look for the RGB code for red "FFFF0000"
red <- "FFFF0000"

# Or we can find out what that code is by starting from a cell that we know is
# red.
red_cell_format_id <-
  xlsx_cells(path, sheets = "pivot-notes") %>%
  dplyr::filter(row == 5, col == 2) %>%
  pull(local_format_id)
red_cell_format_id
red <- formats$local$font$color$rgb[red_cell_format_id]
red
```

Now we use `behead_if()`, filtering out cells with the format IDs of red or
italic cells.

```{r}
cells <-
  xlsx_cells(path, sheets = "pivot-notes") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric, local_format_id) %>%
  print()

cells %>%
  behead_if(!italic[local_format_id],                             # not italic
            direction = "up-left",
            name = "sex") %>%
  dplyr::filter(row != min(row)) %>% # discard non-header cells
  behead("up", "name") %>%
  behead_if(formats$local$font$color$rgb[local_format_id] != red, # not red
            direction = "left-up",
            name = "field") %>%
  dplyr::filter(col != min(col)) %>% # discard non-header cells
  behead("left", "subject") %>%
  select(sex, name, field, subject, score = numeric)
```

### Mixed levels of headers in the same row/column, distinguished by formatting

![](images/pivot-hierarchy.png)

Normally different levels of headers are in different rows, or different
columns, like [Two clear rows of text column headers, left-aligned](2Rl).  But
sometimes they coexist in the same row or column, and are distinguishable by
formatting, e.g. by indentation, or bold for the top level, italic for the mid
level, and plain for the lowest level.

In this example, there is a single column of row headers, where the levels are
shown by different amounts of indentation.  The indentation is done by
formatting, rather than by leading spaces or tabs.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
formats <- xlsx_formats(path)

formats$local$alignment$indent
```

We can use the indentation with `behead_if()` to make two passes over the column
of row headers, first for the unindented headers, then for the indented headers.

```{r}
cells <-
  xlsx_cells(path, sheets = "pivot-hierarchy") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric, local_format_id) %>%
  print()

cells %>%
  behead_if(formats$local$alignment$indent[local_format_id] == 0,
            direction = "left-up",
            name = "field") %>%
  behead("left", "subject") %>%
  behead("up", "name") %>%
  select(field, subject, name, score = numeric)
```

## Complex unpivoting {#pivot-complex}

When `behead()` isn't powerful enough (it makes certain assumptions, and it
doesn't understand formatting), then you can get much more control by using
`enhead()`, which joins together two separate data frames of data cells and
header cells.

This kind of unpivoting is always done in two stages.

1. Identify which cells are headers, and which are data
1. State how the data cells relate to the header cells.

### Two clear rows of text column headers, left-aligned

![](images/pivot-annotations.png)

The first stage, identifying header vs data cells, is simply filtering.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(col >= 4, !is_blank) %>% # Ignore the row headers in this example
  select(row, col, data_type, character, numeric) %>%
  print()

# View the cells in their original positions on the spreadsheet
rectify(all_cells)

first_header_row <-
  dplyr::filter(all_cells, row == 2) %>%
  select(row, col, sex = character)
  # the title of this header is 'sex'
  # the cells are text cells (`"Female"` and `"Male"`) so take the value in the
  # '`character` column.
first_header_row

second_header_row <-
  dplyr::filter(all_cells, row == 3) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
second_header_row

data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is exam scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`
```

The second stage is to declare how the data cells relate to each row of column
headers.

Starting from the point of view of a data cell, the relevant column header from
the second row of headers is the one directly `"up"`.

```{r}
enhead(data_cells, second_header_row, "up")
```

The first row of headers, from the point of view of a data cell, is either
directly up, or up-then-left.

```{r}
enhead(data_cells, first_header_row, "up-left")
```

Piping everything together, we get a complete, tidy dataset, and can finally
drop the `row` and `col` columns.

```{r}
data_cells %>%
  enhead(first_header_row, "up-left") %>%
  enhead(second_header_row, "up") %>%
  select(-row, -col)
```

### Two clear columns of text row headers, top-aligned

![](images/pivot-annotations.png)

This is almost the same as [Two clear rows of text column headers,
left-aligned](2RL), but with different directions: `"left"` for directly
left, and `"left-up"` for left-then-up.

(`"up-left"` and `"left-up"` look like synonyms.  They happen to be synonyms in
`enhead()`, but they aren't in `behead()`.

In this example, the table has no column headers, only row headers.  This is
artificial here, but sometimes table are deliberately laid out in transpose
form: the first column contains the headers, and the data extends in columns
from left to right instead of from top to bottom.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(row >= 3, !is_blank) %>% # Ignore the column headers in this example
  select(row, col, data_type, character, numeric) %>%
  print()

# View the cells in their original positions on the spreadsheet
rectify(all_cells)

first_header_col <-
  dplyr::filter(all_cells, col == 2) %>%
  select(row, col, field = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
first_header_col

second_header_col <-
  dplyr::filter(all_cells, col == 3) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
second_header_col

data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is examp scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`

data_cells %>%
  enhead(first_header_col, "left-up") %>%
  enhead(second_header_col, "left") %>%
  select(-row, -col)
```

### Two clear rows and columns of text headers, top-aligned and left-aligned

![](images/pivot-annotations.png)

This is a combination of the previous two sections.  No new techniques are used.

1. Identify which cells are headers, and which are data
1. State how the data cells relate to the header cells.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric) %>%
  print()

# View the cells in their original positions on the spreadsheet
rectify(all_cells)

first_header_row <-
  dplyr::filter(all_cells, row == 2) %>%
  select(row, col, sex = character)
  # the title of this header is 'sex'
  # the cells are text cells (`"Female"` and `"Male"`) so take the value in the
  # '`character` column.
first_header_row

second_header_row <-
  dplyr::filter(all_cells, row == 3) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
second_header_row

first_header_col <-
  dplyr::filter(all_cells, col == 2) %>%
  select(row, col, field = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
first_header_col

second_header_col <-
  dplyr::filter(all_cells, col == 3) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
second_header_col

data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is examp scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`

data_cells %>%
  enhead(first_header_row, "up-left") %>%
  enhead(second_header_row, "up") %>%
  enhead(first_header_col, "left-up") %>%
  enhead(second_header_col, "left") %>%
  select(-row, -col)
```

### Centre-aligned headers

![](images/pivot-centre-aligned.png)

Headers aren't always aligned to one side of the data cells that they describe.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <- xlsx_cells(path, sheets = "pivot-centre-aligned")
rectify(all_cells)
```

Looking at that table, it's not immediately obvious where the boundary between
`Female` and `Male` falls, or between `Humanities` and `Performance`.  A naive
approach would be to match the inner headers to the outer ones by proximity, and
there are four directions to do so: `"up-ish"`, `"left-ish"`, `"down-ish"`, and
`"right-ish"`.

But in this case, those directions are too naive.

* `Languages` is closest to the `Performance` header, but is a humanity.
* `Lenny` is the same distance from `Female` as from `Male`.

You can fix this by justifying the header cells towards one side of the data
cells that they describe, and then use a direction like `"up-left"` as usual.  Do
this with `justify()`, providing the header cells with a second set of cells
at the positions you want the header cells to move to.

* `header_cells` is the cells whose value will be used as the header
* `corner_cells` is the cells whose position is in one corner of the domain of
  the header (e.g. the top-left-hand corner).

In the original spreadsheet, the borders mark the boundaries.  So the corner
cells of the headers can be found by filtering for cells with a particular
border.

```{r}
all_cells <-
  xlsx_cells(path, sheets = "pivot-centre-aligned") %>%
  select(row, col, is_blank, data_type, character, numeric, local_format_id)

formats <- xlsx_formats(path)
top_borders <- which(!is.na(formats$local$border$top$style))
left_borders <- which(!is.na(formats$local$border$left$style))

first_header_row_corners <-
  dplyr::filter(all_cells, row == 2, local_format_id %in% left_borders) %>%
  select(row, col)
first_header_row_corners

first_header_col_corners <-
  dplyr::filter(all_cells, col == 2, local_format_id %in% top_borders) %>%
  select(row, col)
first_header_col_corners
```

Next, get the first row and first column of header cells as usual.

```{r}
first_header_row <-
  dplyr::filter(all_cells, !is_blank, row == 2) %>%
  select(row, col, sex = character)
  # the title of this header is 'sex'
  # the cells are text cells (`"Female"` and `"Male"`) so take the value in the
  # '`character` column.
first_header_row

first_header_col <-
  dplyr::filter(all_cells, !is_blank, col == 2) %>%
  select(row, col, field = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
first_header_col
```

And now justify the header cells to the same positions as the corner cells.

```{r}
first_header_row <- justify(first_header_row, first_header_row_corners)
first_header_col <- justify(first_header_col, first_header_col_corners)

first_header_row
first_header_col
```

The rest of this example is the same as "Two clear rows and columns of text
headers, top-aligned and left-aligned".

```{r}
second_header_row <-
  dplyr::filter(all_cells, row == 3) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
second_header_row

second_header_col <-
  dplyr::filter(all_cells, col == 3) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
second_header_col

data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is examp scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`

data_cells %>%
  enhead(first_header_row, "up-left") %>%
  enhead(second_header_row, "up") %>%
  enhead(first_header_col, "left-up") %>%
  enhead(second_header_col, "left") %>%
  select(-row, -col)
```

### Multiple rows or columns of headers, with meaningful formatting

![](images/pivot-annotations.png)

This is a combination of the previous section with [Meaningfully formatted
cells](tidy-formatted-cells).  The section [Meaningfully formatted
rows](tidy-formatted-rows) doesn't work here, because the unpivoting of multiple
rows/columns of headers complicates the relationship between the data and the
formatting.

1. Unpivot the multiple rows/columns of headers, as above, but keep the `row`
   and `col` of each data cell.
1. Collect the `row`, `col` and formatting of each data cell.
1. Join the data to the formatting by the `row` and `col`.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric) %>%
  print()

# View the cells in their original positions on the spreadsheet
rectify(all_cells)

first_header_row <-
  dplyr::filter(all_cells, row == 2) %>%
  select(row, col, sex = character)
  # the title of this header is 'sex'
  # the cells are text cells (`"Female"` and `"Male"`) so take the value in the
  # '`character` column.
first_header_row

second_header_row <-
  dplyr::filter(all_cells, row == 3) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
second_header_row

first_header_col <-
  dplyr::filter(all_cells, col == 2) %>%
  select(row, col, field = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
first_header_col

second_header_col <-
  dplyr::filter(all_cells, col == 3) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
second_header_col

data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is exam scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`

unpivoted <-
  data_cells %>%
  enhead(first_header_row, "up-left") %>%
  enhead(second_header_row, "up") %>%
  enhead(first_header_col, "left-up") %>%
  enhead(second_header_col, "left")
  # Don't delet the `row` and `col` columns yet, because we need them to join on
  # the formatting

# `formats` is a pallette of fill colours that can be indexed by the
# `local_format_id` of a given cell to get the fill colour of that cell
fill_colours <- xlsx_formats(path)$local$fill$patternFill$fgColor$rgb

# Import all the cells, filter out the header row, filter for the first column,
# and create a new column `approximate` based on the fill colours, by looking up
# the local_format_id of each cell in the `formats` pallette.
annotations <-
  xlsx_cells(path, sheets = "pivot-annotations") %>%
  dplyr::filter(row >= 4, col >= 4) %>% # Omit the headers
  mutate(fill_colour = fill_colours[local_format_id]) %>%
  select(row, col, fill_colour)
annotations

left_join(unpivoted, annotations, by = c("row", "col")) %>%
  select(-row, -col)
```

### Mixed headers and notes in the same row/column, distinguished by formatting

![](images/pivot-notes.png)

This doesn't use any new techniques.  The trick is, when selecting a row or
column of header cells, to filter out ones that have the 'wrong' formatting
(formatting that shows they aren't really headers).  In this example, cells with
italic or red text aren't headers, even if they are in amongst header cells.

First, identify the IDs of formats that have italic or red text.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
formats <- xlsx_formats(path)

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

# For 'red' we can either look for the RGB code for red "FFFF0000"
red <- which(formats$local$font$color$rgb == "FFFF0000")
red

# Or we can find out what that code is by starting from a cell that we know is
# red.
red_cell_format_id <-
  xlsx_cells(path, sheets = "pivot-notes") %>%
  dplyr::filter(row == 5, col == 2) %>%
  pull(local_format_id)
red_cell_format_id
red_rgb <- formats$local$font$color$rgb[red_cell_format_id]
red <- which(formats$local$font$color$rgb == red_rgb)
red
```

Now we select the headers, filtering out cells with the format IDs of red or
italic cells.

```{r}
all_cells <-
  xlsx_cells(path, sheets = "pivot-notes") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, character, numeric, local_format_id) %>%
  print()

first_header_row <-
  dplyr::filter(all_cells, row == 2, !(local_format_id %in% c(red, italic))) %>%
  select(row, col, sex = character)
  # the title of this header is 'sex'
  # the cells are text cells (`"Female"` and `"Male"`) so take the value in the
  # '`character` column.
first_header_row

first_header_col <-
  dplyr::filter(all_cells, col == 2, !(local_format_id %in% c(red, italic))) %>%
  select(row, col, qualification = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
first_header_col

second_header_col <-
  dplyr::filter(all_cells, col == 3) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.

data_cells %>%
  enhead(first_header_row, "up-left") %>%
  enhead(first_header_col, "left-up") %>%
  select(-row, -col)
```

### Mixed levels of headers in the same row/column, distinguished by formatting

![](images/pivot-hierarchy.png)

Normally different levels of headers are in different rows, or different
columns, like [Two clear rows of text column headers, left-aligned](2Rl).  But
sometimes they coexist in the same row or column, and are distinguishable by
formatting, e.g. bold for the top level, italic for the mid level, and plain for
the lowest level.

In this example, there is a single column of row headers, where the levels are
shown by different amounts of indentation.  The indentation is done by
formatting, rather than by leading spaces or tabs.

The first step is to find the format IDs of all the different levels of
indentation.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
formats <- xlsx_formats(path)

indent0 <- which(formats$local$alignment$indent == 0)
indent1 <- which(formats$local$alignment$indent == 1)

indent0
indent1
```

Now we use these format IDs to indentify the different levels of headers in the
first column.

```{r}
all_cells <-
  xlsx_cells(path, sheets = "pivot-hierarchy") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric, local_format_id) %>%
  print()

field <-
  dplyr::filter(all_cells, col == 2, local_format_id %in% indent0) %>%
  select(row, col, field = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
field

subject <-
  dplyr::filter(all_cells, col == 2, local_format_id %in% indent1) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
subject

name <-
  dplyr::filter(all_cells, row == 2) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
name

data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is exam scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`

data_cells %>%
  enhead(field, "left-up") %>%
  enhead(subject, "left") %>%
  enhead(name, "up") %>%
  select(-row, -col)
```

### Repeated rows/columns of headers within the table

![](images/pivot-repeated-headers.png)

Repetitions can simply be ignored.  Select one of the sets of headers, and use
it for all the data.  In this example, the data cells are easy to distinguish
from the headers mixed in among them, because only the data cells have the
`numeric` data type.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-repeated-headers") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric) %>%
  print()

# View the cells in their original positions on the spreadsheet
rectify(all_cells)

# The 'term' headers appear four times, but only the first one is needed.
term <-
  dplyr::filter(all_cells, row == 2) %>%
  select(row, col, term = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
term

subject <-
  dplyr::filter(all_cells, col == 2) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
subject

name <-
  dplyr::filter(all_cells, col == 3) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
name

# The data cells are distinguished from the 'term' headers by their data type --
# the data cells are numeric, whereas the term headers are character.
data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is exam scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`
data_cells

data_cells %>%
  enhead(term, "up") %>%
  enhead(subject, "up-left") %>%
  enhead(name, "left") %>%
  select(-row, -col)
```

### Headers amongst the data

![](images/pivot-header-within-data.png)

This happens when what is actually a row-header, instead of being presented to
the left of the data, is presented above the data.  (Alternatively, what is
actually a column header, instead of being presented above the data, is
presented to the side.)

The way to handle it is to *pretend* that it is a row header, and use the
`"left-up"` direction as normal.

```{r}
path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
all_cells <-
  xlsx_cells(path, sheets = "pivot-header-within-data") %>%
  dplyr::filter(!is_blank) %>%
  select(row, col, data_type, character, numeric, local_format_id) %>%
  print()

# View the cells in their original positions on the spreadsheet
rectify(all_cells)

bold <- which(xlsx_formats(path)$local$font$bold)

# The subject headers, though mixed with the data and the 'term' headers, are
# distinguishable by the data type "character" and by being bold.
subject <-
  dplyr::filter(all_cells,
         col == 3,
         data_type == "character",
         local_format_id %in% bold) %>%
  select(row, col, subject = character)
  # The title of this header is 'subject'
  # The cells are text cells (`"history"`, etc.) so take the value in the
  # '`character` column.
subject

# We only need one set of the 'term' headers
term <-
  dplyr::filter(all_cells, row == 3, data_type == "character") %>%
  select(row, col, term = character)
  # the title of this header is 'field', meaning 'group of subjects'.
  # The cells are text cells (`"Humanities"`, `"Performance"`) so take the value
  # in the '`character` column.
term

name <-
  dplyr::filter(all_cells, col == 2) %>%
  select(row, col, name = character)
  # The title of this header is 'name'.
  # The cells are text cells, so take the value in the '`character` column.
name

# The data cells are distinguished from the 'subject' headers by their data
# type -- the data cells are numeric, whereas the term headers are character.
data_cells <-
  dplyr::filter(all_cells, data_type == "numeric") %>%
  select(row, col, score = numeric)
  # The data is exam scores in certain subjects, so give the data that title.
  # The data is numeric, so select only that 'value'.  If some of the data was
  # also text or true/false, then you would select the `character` and `logical`
  # columns as well as `numeric`
data_cells

data_cells %>%
  enhead(subject, "left-up") %>%
  enhead(term, "up") %>%
  enhead(name, "left") %>%
  select(-row, -col)
```