Using attributes in R to hold metadata when pivoting wider for export to xlsx

Question

I have a dataframe that looks something like:

df <- tribble(~date,      ~value, ~analyte, ~quantified,
              01-01-2020, 8.6,    Fe,       TRUE,
              02-06-2020, 10.4,   Ni,       TRUE,
              01-01-2020, 2,      Ni,       FALSE)

For many analytical procedures there is a quantification (or detection or reporting...) limit. The quantified column says whether the value is above or below this limit (T: above, F: below). If a test comes back below the quantification limit, the quantification limit is recorded as the value and the quantified column is set to FALSE. So for the result in the third row the quantification limit is 2 mg/L and the true value for Nickel for the sample is somewhere between 0-2 mg/L, but we treat it as the highest possible value.

I need to transform this into an excel spreadsheet in wide form (analytes as column names, one row for each date) and I would like to provide styling of cells with values that are below the quantification limit, but can't quite see how. I was thinking about adding quantification as an attribute to the value cell and then applying an openxlsx style based on that attribute, but a) I can't figure out how to apply the attributes to the values rather than the whole column and b) I'm not sure this is the right approach anyway.

The following gets me the basic output, but as I said, I can't quite see how to get the quantification into the wide form in a way that is easy to use to then set an xlsx style for the cells below the quantification limit.

library(openxlsx)
df %>%
  dplyr::select(-quantification) %>%
  pivot_wider(id_cols = c(date), names_from = analyte, values_from = value) -> df
dfWorkbook <- createWorkbook()
addWorksheet(dfWorkbook, "test")
writeData(dfWorkbook, sheet = "test", x = df)

Footnote: unfortunately the quantification limit changes between analytes and over time so I can't just say "if value > 2, addStyle(dfWorkbook, "test", BQL)"

Answer 1

All what we need to do is use which(..., arr.ind=T) to get the cell indices add one to the row components to account for the shift made by column names:

# keep the quantified vals in the same structure as the actual values
df %>%
  dplyr::select(-value) %>%
  pivot_wider(id_cols = c(date), names_from = analyte, values_from = quantified)  -> df.atts
df %>%
  dplyr::select(-quantified) %>%
  pivot_wider(id_cols = c(date), names_from = analyte, values_from = value) -> df
dfWorkbook <- createWorkbook()
addWorksheet(dfWorkbook, "test")
writeData(dfWorkbook, sheet = "test", x = df)

## create and add a style to the cells that verify condition
quantified <- createStyle(
  fontSize = 14, fontColour = "#FFFFFF", halign = "center",
  fgFill = "#4F81BD", border = "TopBottom", borderColour = "#4F81BD"
)
# get the cell indices that verify the condition
mat <-  which(df.atts == F, arr.ind = TRUE) 
# add the style for each cell in this matrix
addStyle(dfWorkbook, sheet = 1, headerStyle, rows = mat[,1]+1, cols = mat[,2])
# save the workbook
saveWorkbook(dfWorkbook, "styled.xlsx", overwrite = TRUE)