5.4 Multiple pieces of information in a single cell, with meaningful formatting

The above table of products and their production readiness combines three pieces of information in a single cell. Believe it or not, this is based on a real-life example.

path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
xlsx_cells(path, sheet = "in-cell formatting") %>%
  dplyr::filter(address != "A1") %>%
  rectify()

## # A tibble: 4 x 3
##   `row/col` `1(A)`        `2(B)`
##       <int> <chr>         <chr> 
## 1         3 ID            Count 
## 2         4 A1-TEST       1     
## 3         5 A2-PRODUCTION 2     
## 4         6 A3-PRODUCTION 3

In the ID column, the first section "A1", "A2", "A3" is the product ID. The second section "TEST", "PRODUCTION" is the production readiness, and the formatting of "TEST" and "PRODUCTION" shows whether or not manufacturing failed. In the file, one of those strings is formatted red with a strikethrough, indicating failure.

One way to extract the formatting is by unnesting, as above, but in this case we can get away with mapping over the nested data frames and pulling out a single value.

strikethrough <-
  xlsx_cells(path, sheet = "in-cell formatting") %>%
  dplyr::filter(address != "A1", col == 1) %>%
  mutate(strikethrough = map_lgl(character_formatted, ~ any(.x$strike))) %>%
  select(row, col, character, strikethrough)

This can then be joined onto the rest of the table, in the same way as the section “Already a tidy table but with meaningful formatting of single cells”.

cells <-
  xlsx_cells(path, sheet = "in-cell formatting") %>%
  dplyr::filter(address != "A1") %>%
  select(row, col, data_type, character, numeric)

strikethrough <-
  xlsx_cells(path, sheet = "in-cell formatting") %>%
  dplyr::filter(address != "A1", col == 1) %>%
  mutate(strikethrough = map_lgl(character_formatted, ~ any(.x$strike))) %>%
  select(row, strikethrough)

left_join(cells, strikethrough, by = "row") %>%
  behead("up", header) %>%
  select(-col) %>%
  spatter(header) %>%
  select(ID, strikethrough, Count)

## # A tibble: 3 x 3
##   ID            strikethrough Count
##   <chr>         <lgl>         <dbl>
## 1 A1-TEST       NA                1
## 2 A2-PRODUCTION TRUE              2
## 3 A3-PRODUCTION NA                3