8.2 Data embedded in comments

Comment strings are availabe in the comment column, just like character. Comments can have formatting, but tidyxl doesn’t yet import the formatting. If you need this, please open an issue. It would probably be imported into a comment_formatted column, similarly to character_formatted.

path <- system.file("extdata", "worked-examples.xlsx", package = "unpivotr")
xlsx_cells(path, sheet = "comments") %>%
  select(row, col, data_type, character, numeric, comment) %>%
  behead("up", "header")

## # A tibble: 4 x 7
##     row   col data_type character numeric comment       header
##   <int> <int> <chr>     <chr>       <dbl> <chr>         <chr> 
## 1     2     1 character Paul           NA Absent Term 1 Name  
## 2     2     2 numeric   <NA>            9 Predicted     Score 
## 3     3     1 character Matilda        NA <NA>          Name  
## 4     3     2 numeric   <NA>           10 <NA>          Score

Comments apply to single cells, so follow the same procedure as “Already a tidy table but with meaningful formatting of single cells”.

cells <-
  xlsx_cells(path, sheet = "comments") %>%
  select(row, col, data_type, character, numeric, comment)
cells

## # A tibble: 6 x 6
##     row   col data_type character numeric comment      
##   <int> <int> <chr>     <chr>       <dbl> <chr>        
## 1     1     1 character Name           NA <NA>         
## 2     1     2 character Score          NA <NA>         
## 3     2     1 character Paul           NA Absent Term 1
## 4     2     2 numeric   <NA>            9 Predicted    
## 5     3     1 character Matilda        NA <NA>         
## 6     3     2 numeric   <NA>           10 <NA>

values <-
  cells %>%
  select(-comment) %>%
  behead("up", header) %>%
  select(-col) %>%
  spatter(header)
values

## # A tibble: 2 x 3
##     row Name    Score
##   <int> <chr>   <dbl>
## 1     2 Paul        9
## 2     3 Matilda    10

comments <-
  cells %>%
  behead("up", header) %>%
  mutate(header = paste0(header, "_comment")) %>%
  select(row, header, comment) %>%
  spread(header, comment)
comments

## # A tibble: 2 x 3
##     row Name_comment  Score_comment
##   <int> <chr>         <chr>        
## 1     2 Absent Term 1 Predicted    
## 2     3 <NA>          <NA>

left_join(values, comments, by = "row") %>%
  select(-row)

## # A tibble: 2 x 4
##   Name    Score Name_comment  Score_comment
##   <chr>   <dbl> <chr>         <chr>        
## 1 Paul        9 Absent Term 1 Predicted    
## 2 Matilda    10 <NA>          <NA>