8

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")

Final table

Peter
  • 11,500
  • 5
  • 21
  • 31

1 Answers1

2

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))
David Gohel
  • 9,180
  • 2
  • 16
  • 34
  • 1
    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) – Peter Mar 25 '18 at 13:44
  • No, such function does not exists (or it would have been documented ;). 2 functions are provided: one is columnwise, the other is *type*wise. You can of course code your *set_formatter* function. – David Gohel Mar 26 '18 at 07:16
  • 1
    Many thanks for clarification. This is really a feature request. – Peter Mar 26 '18 at 20:48
  • @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. – Richard Lusch Mar 27 '18 at 17:38
  • @RichardLusch in the documentation here: https://davidgohel.github.io/flextable/reference/set_formatter.html. The function name is `set_formatter_type`. – David Gohel Mar 27 '18 at 17:48
  • @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? – tjebo Dec 03 '18 at 14:45
  • 3
    @Tjebo these new functions should help :) https://github.com/davidgohel/flextable/blob/master/R/formatters.R#L74 – David Gohel Dec 03 '18 at 16:28
  • awesome! looks great :) – tjebo Dec 03 '18 at 16:29