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.
## # 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
ID column, the first section
"A3" is the product ID.
The second section
"PRODUCTION" is the production readiness, and the
"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.
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