Formatting multiple columns with flextable r package

Question

Is it possible to format multiple columns with one line of script using flexitable?

The example includes variables of two different types of double. I'd like to:

1. round variables greater than 1000 to the nearest 1000 and add thousand comma formating, and
2. round variables less than one to two decimal places

This is relatively simple using flextable, but becomes a bore with a much larger data set. I cannot see a way to do this more efficiently.

I could use dplyr to pre-process the data (although the comma formatting requires changing the variable type from double to character). I'd prefer if possible to do this in flextable. I'm aware multi-column formatting is possible with Huxtable. Might it be possible for users to create bespoke set_formatter_type functions?

MWE

set.seed(100)

tib <- tibble(a = letters[1:4],
              b = runif(4)*sample(c(10000, 1000000, 10000000), 4, replace = TRUE),
              c = runif(4)*sample(c(10000, 1000000, 10000000), 4, replace = TRUE),
              d = runif(4)*sample(c(10000, 1000000, 10000000), 4, replace = TRUE),
              e = runif(4),
              f = runif(4))


  regulartable(tib) %>%
  set_formatter(b = function(x) format(round(x, -3), big.mark = ",")) %>% 
  set_formatter(c = function(x) format(round(x, -3), big.mark = ",")) %>% 
  set_formatter(d = function(x) format(round(x, -3), big.mark = ",")) %>% 
  set_formatter(e = function(x) round(x, 2)) %>%
  set_formatter(f = function(x) round(x, 2)) %>%
  print(preview = "docx")

Lorrielorrimer

Answer 1

Yes, the documentation can be found here : https://davidgohel.github.io/flextable/articles/format.html#set_formatter-function

and here : https://davidgohel.github.io/flextable/reference/set_formatter.html

set_formatter(b = function(x) format(round(x, -3), big.mark = ","), 
              c = function(x) format(round(x, -3), big.mark = ","), 
              d = function(x) format(round(x, -3), big.mark = ","), 
              e = function(x) round(x, 2), 
              f = function(x) round(x, 2))

Scarify

Sorry, I cannot seem to apply the links to format multiple columns without a separate line of code for each column. My question might be better framed could there be a function along these lines: set_formatter(x, col_to_format(b, c, d) = function(x) format(round(x, -3), big.mark = ",")) (I've made up the col_to_format argument) – Lorrielorrimer

No, such function does not exists (or it would have been documented ;). 2 functions are provided: one is columnwise, the other is typewise. You can of course code your set_formatter function. – Scarify

Many thanks for clarification. This is really a feature request. – Lorrielorrimer

@DavidGohel, could you link or explain more about the typewise formatting? I have a similar issue, but I cannot reference the names of the columns specifically as they are dynamic. – Markle

@RichardLusch in the documentation here: davidgohel.github.io/flextable/reference/set_formatter.html. The function name is set_formatter_type. – Scarify

@DavidGohel The package is awesome but the functionality of the display or set_formatter would be even greater, when one could use this for a range of columns and not only one by one. In particular, when one has several columns with numbers which are supposed to be rounded in a similar fashion, then it would be great to be able to do that as a global option. There must be something hidden somewhere in your code because it defaults to 3 - maybe it would be possible to change this as an argument somewhere , maybe even in the main flextable call? – Pibgorn

@Tjebo these new functions should help :) github.com/davidgohel/flextable/blob/master/R/formatters.R#L74 – Scarify

awesome! looks great :) – Pibgorn