Add comma to numbers every three digits in datatable (R)

Question

Suppose my data looks like this:

df1 = data.frame(A=c(1000000.51,5000.33), B=c(0.565,0.794))

I want to use DataTables and have column A be (1,000,001 ; 5,000)

library(DT)
datatable(df1)  %>%  formatPercentage('B', 2) %>%
  formatRound('A',digits = 0)

I know i can use scales

library(scales)    
comma_format()(1000000)

but I'm not sure how to combine that with DataTables

Thanks!

score 33 · Accepted Answer · answered Jan 25 '16 at 18:28

33

Had this same issue:

Try This:

require(DT)
require(dplyr)

df1 = data.frame(A=c(1000000.51,5000.33, 2500, 251), B=c(0.565,0.794, .685, .456))

df1 <- df1 %>% mutate(A=round(A,digits=0))

datatable(df1)  %>%  formatPercentage('B', 2) %>%
  formatCurrency('A',currency = "", interval = 3, mark = ",")

answered Jan 25 '16 at 18:28

R_User123456789

650
7
9

If you use ' as mark, you have to escape it: ` mark = "\\'" `. – Tinu Jun 13 '18 at 15:52
I am seeing the decimal places in the A column using the solution above. I guess the `digits` option was supposed to fix this, but it appears not to anymore. See my alternate below. – Nicholas G Reich Jul 28 '20 at 22:10

Steven Beaupré · Answer 2 · 2015-03-24T21:18:38.843

4

You could use formatC

library(dplyr)
library(DT)

df1 %>% 
  mutate(A = formatC(round(A), format = "f", big.mark = ",", drop0trailing = TRUE),
         B = paste0(formatC(100 * B, format = "f", digits = 2), "%")) %>%
  datatable()

Which gives:

enter image description here

edited Mar 24 '15 at 21:18

answered Mar 24 '15 at 20:53

Steven Beaupré

21,343
7
57
77

4

the problem is that A and B are now character values and are no longer sorted numerically. Is there a datatable solution to this that retains their numerical value? – daj Jul 07 '15 at 02:32

score 4 · Answer 3 · answered Dec 10 '20 at 16:12

I'm not sure if formatRound() was created after this question was asked and answered, but here's a solution without using formatCurrency() (which seems a little hackish):

library(DT)
library(dplyr)

df1 = data.frame(A=c(1000000.51,5000.33, 2500, 251), B=c(0.565,0.794, .685, .456))

datatable(df1) %>%
  formatPercentage('B', digits = 2) %>%
  formatRound('A', digits = 0)

score 0 · Answer 4 · answered Jul 28 '20 at 22:17

The answer above for me showed the decimal places.

I was able to use a digits option within formatCurrency() and it worked:

df1 = data.frame(A=c(1000000.51,5000.33, 2500, 251), B=c(0.565,0.794, .685, .456))
datatable(df1)  %>%  formatPercentage('B', 2) %>%
    formatCurrency('A',currency = "", interval = 3, mark = ",", digits=0)

Add comma to numbers every three digits in datatable (R)

4 Answers4

Linked