I am trying to make a conditional background coloring with the flextable package.
First I created this table that contains a 2 line header
Cancer <- read.table("http://users.stat.ufl.edu/~aa/cat/data/Cancer.dat",
header = TRUE, stringsAsFactors = TRUE)
library(dplyr)
library(tidyr)
library(scales)
library(flextable)
cancerCountWide <- Cancer %>%
select(-risktime) %>%
pivot_wider(id = time, names_from = c(histology, stage),
values_from=count) %>%
mutate(`histo` = " ") %>%
select(time, histo, `1_1`, `2_1`, `3_1`, everything())
my_header <- data.frame(
col_keys = c("time", "histo", "blank1", "1_1", "2_1", "3_1", "blank2", "1_2", "2_2", "3_2", "blank3", "1_3", "2_3","3_3"),
line2 = c("Follow-up", "Histology", "", rep("I", 3), "", rep("II", 3), "", rep("III", 3)),
line3 = c("Follow-up", "Disease Stage", rep(c("", "1", "2", "3"), 3))
)
flextable(cancerCountWide, col_keys = my_header$col_keys) %>%
set_header_df(
mapping = my_header,
key = "col_keys"
)%>%
theme_booktabs() %>%
merge_v(part = "header") %>%
merge_h(part = "header") %>%
align(align = "center", part = "all")
With it I obtained the next output:
I would like to color the cell backgrounds based on the first line of the header. Let's say I would like to color orange the diseases stages (1 , 2 and 3) for the histology group II that have values higher than 5. I thus added the bg option to the previous code:
flextable(cancerCountWide, col_keys = my_header$col_keys) %>%
set_header_df(
mapping = my_header,
key = "col_keys"
)%>%
theme_booktabs() %>%
merge_v(part = "header") %>%
merge_h(part = "header") %>%
align(align = "center", part = "all")
%>% bg(i = ~ II >5, j= 'II', bg = "orange", part="body")
but I get the next error:
** Error in eval(as.call(f[[2]]), envir=data) : object 'II' not found**
It seems that since the header is split into different lines the variables cannot be selected. How can I fix this?
Thank you
S