Format multiple flextable columns by user defined function

Question

Is there a way to display the numeric values inside a flextable in millions?

I do not want to divide the numbers in the dataset to 1000000 before creating the flextable.

For example, I want to display the number 1983445678 as 1,983 or 1,983.4 or 1,983.4€

If not, is it possible to implement a new function, maybe similar to set_formatter or colformat_num that allows you to format multiple columns using a user defined function?

Since some columns in my dataset are generated dynamically at runtime (e.g. after using the spread function), I cannot use the function set_formatter to format the new column because I don't know the name of the column yet, when I write the code. Of course, I can detect this name at runtime, so it will be useful to have a function that formats these new columns using a user defined function and accepting in input, the names of the columns to be formatted as strings.

It should be something like:

my_data %>%
regulartable %>%
col_format_by_udf(j = c("Europe", "Asia", "Australia"), million_format)

...or something like:

my_data %>%
regulartable %>%
col_format_num_by_udf(million_format)

All numeric columns are formatted in millions

Thank you!

score 1 · Answer 1 · answered Dec 06 '19 at 12:45

For the moment, I defined a function that solved my problem. It will be usefull if something similar, maybe more elaborated could be inserted into the flextable package directly.

colformat_num_udf <- function(table, j = NULL, udf_function_name = NULL) {

  if (is.null(udf_function_name) | is.null(table))
    return(table)

  if (is.null(j))
    input_columns <- names(table$body$dataset)
  else if(is.numeric(j))
    input_columns <- names(table$body$dataset)[j]
  else
    input_columns <- j

  numeric_columns <- table$body$dataset %>% select_if(is.numeric) %>% names() %>% intersect(input_columns)

  if (length(numeric_columns) == 0)
    return(table)

  rExpression = paste0("table <- set_formatter(table,`", numeric_columns, "` = ", udf_function_name, ")")
  eval(parse(text = rExpression))

  table
}

you should not use internals (.i.e `table$body$dataset`) as there is no guarantee this will not change later... — David Gohel, Dec 06 '19 at 16:10
It would be helpful Popescu if you could add a demo to your provided function. I think your function will help me solve a similar problem but I am not able to adapt it to my needs. Would it be possible to provide an example with a demo dataset? Thank you. — rempsyc, Feb 09 '22 at 22:36

score 0 · Accepted Answer · answered Dec 06 '19 at 16:09

0

You can have a try with this code:


library(magrittr)

dummy_fun <- function(x){
  sprintf("%0.02f", x*1000+pi)
}
funs <- setNames(rep(list(fun), 4), names(iris)[-5])
flextable(head(iris)) %>% 
  set_formatter(values = funs)

answered Dec 06 '19 at 16:09

David Gohel

9,180
2
16
34

Dear David, how can bold the font in `set_caption(ft, "Hello")` also is there a way to display multiple separate tables on a single HTML page? Many thanks, Reza – Dec 18 '19 at 19:37

Format multiple flextable columns by user defined function

2 Answers2