rmarkdownr-flextable

How to format rows in a flextable for a Word document

I am using R markdown and flextable to create a Word document, which I would like to contain a table like below, but with some additional formatting. In general, I would like to format the negative numbers in red, which has been completed by using color() function.

However, I cannot find the way to format the last two rows into percentage with %. I tried formattable package but not successful. Can someone provide some suggestions?

Data:

structure(list(X25th.percentile = c(3.01462950348061, -0.349849435071161, 
0.25), X50th.percentile = c(4.1590809972998, -0.103031116474755, 
0.5), X75th.percentile = c(5.71021088599362, 0.231493564666972, 
0.75)), class = c("formattable", "data.frame"), row.names = c(NA, 
-3L), formattable = list(formatter = "format_table", format = list(
    list(area(col = 1:3) ~ function(x) percent(x, digits = 0))), 
    preproc = NULL, postproc = NULL))

  flextable(tbl)%>%
colformat_double(i=1,digit=2 )%>%
color(i=~ X25th.percentile<0, j="X25th.percentile", color="red")%>%
color(i=~X50th.percentile<0, j="X50th.percentile", color="red")%>%
color(i=~X75th.percentile<0, j="X75th.percentile", color="red")

enter image description here

Solution

  • You could slightly refactor your code and use ifelse:

      tbl %>% 
    mutate(across(everything(), ~ifelse(. < 1, paste0(. * 100, "%"), .))) %>% 
    flextable() %>% 
    colformat_double(i=1,digit=2 )%>%
    color(i=~ X25th.percentile<0, j="X25th.percentile", color="red")%>%
    color(i=~X50th.percentile<0, j="X50th.percentile", color="red")%>%
    color(i=~X75th.percentile<0, j="X75th.percentile", color="red")

    This gives:

    Not sure what your use-case is, but you also might want to consider rounding:

      tbl %>% 
    mutate(across(everything(), ~round(., 2))) %>% 
    mutate(across(everything(), ~ifelse(. < 1, paste0(. * 100, "%"), .))) %>% 
    flextable() %>% 
    colformat_double(i=1,digit=2 )%>%
    color(i=~ X25th.percentile<0, j="X25th.percentile", color="red")%>%
    color(i=~X50th.percentile<0, j="X50th.percentile", color="red")%>%
    color(i=~X75th.percentile<0, j="X75th.percentile", color="red")