4

Context: I am creating .docx table using awesome package flextable. This package has a function to format numbers in the final output: set_formatter_type(). This function, if i understand correctly, requires a character input that can be used by sprintf(). I was able to acheive the expected output using set_formatter() but it required to explicitely name each column and I can not do that with my real table.

Problem: I can not find how to add big marks (e.g. thousand separator) using sprintf() synthax that works with flextable::set_formatter_type().

It is possible to acheive the right formatting using formatC() but this function does not work with flextable::set_formatter_type()

formatC(x = signif(1715235456.5684, 3), big.mark = " ", digits = 0, format = "f")
[1] "1 720 000 000"

Using sprintf() I was able to acheive:

 sprintf("%.0f", signif(1715235456.5684, 3))
[1] "1720000000"

Reproducible example:

df <- flextable::flextable(iris[1:3]*1000)
flextable::set_formatter_type(df, fmt_double = "%.0f") # works fine but I can't get big mark separator
flextable::set_formatter_type(df, fmt_double = function(x) formatC(x, digits = 0, big.mark = " ")) # does not work (error)
flextable::set_formatter(df, Sepal.Length = function(x) formatC(x, digits = 0, big.mark = " ", format = "f")) # works but I would like not to have to name each column from my real life dataframe...

Expected output:

flextable::set_formatter(df, 
                         Sepal.Length = function(x) formatC(x, digits = 0, big.mark = " ", format = "f"),
                         Sepal.Width = function(x) formatC(x, digits = 0, big.mark = " ", format = "f"),
                         Petal.Lenght = function(x) formatC(x, digits = 0, big.mark = " ", format = "f"))
Paul
  • 2,850
  • 1
  • 12
  • 37
  • 1
    Maybe this could help . I think this link from the mentioned site could be of your interest. – TarJae Apr 18 '21 at 13:23
  • Many thanks for the links, I was able to acheive the wanted result with it. – Paul Apr 18 '21 at 13:41
  • Perfect. You could answer your question in order to help other users in the same situation! – TarJae Apr 18 '21 at 14:05
  • 1
    You can use function `colformat_double` for that. See https://ardata-fr.github.io/flextable-book/cell-content-1.html#simple-formatting-of-cell-content and maybe that example: https://ardata-fr.github.io/flextable-gallery/gallery/2021-04-02-custom-cell-content/ – David Gohel Apr 18 '21 at 14:19
  • @DavidGohel thanks, I did not know about the `colformat_double()` function (even after a lot of reasearch I was not able to find it). Many thanks for your amazing package! – Paul Apr 18 '21 at 14:22

1 Answers1

7

Thanks to @TarJae and @DavidGohel comments, here is how the problem can be solved using flextable::colformat_double() function as described here: https://github.com/davidgohel/flextable/blob/master/R/formatters.R#L135

Up to date information can be found here: https://ardata-fr.github.io/flextable-book/cell-content-1.html#simple-formatting-of-cell-content

df <- flextable::flextable(iris[1:3]*1000)

flextable::colformat_double(df, big.mark = " ", digits = 0)

The output:

enter image description here

Credits: Formatting multiple columns with flextable r package

Paul
  • 2,850
  • 1
  • 12
  • 37
  • Perfect. Good to see that you were able to solve it! – TarJae Apr 18 '21 at 14:33
  • Well I feel a little bit idiot not to have found it after 2 hours of research... but I am glad you and David commented on it! Many many thanks! – Paul Apr 18 '21 at 14:35
  • 1
    Would you mind updating the link you provided to a link to the documentation? https://ardata-fr.github.io/flextable-book/cell-content-1.html#simple-formatting-of-cell-content Because the one you are using are not pointing to the solution but to an old solution and also because linking to sources that can change over time will probably make your link irrelevant later – David Gohel Apr 19 '21 at 07:53