DT::datatable() is a great way to show interactive tables in RMarkdown documents. (Unfortunately it’s a little bit tricky to use them with blogdown in combination with some hugo themes. But that’s another story.)

The user can sort any column as he wants to. But it’s an English tool. Numbers are formated in the American or English way: As decimal mark a point is used and between thousands a comma is used as separator. So it’s $ 1,234,567.89 $

In Germany both characters are switched: It’s $ 1.234.567,89 $.

You can format columns in DT::datatable() using the function formatRound() and its parameters interval, mark and dec.mark. But this breaks the sorting. Let’s have a look.

First load the libraries.

1
2
3
4

options(tidyverse.quiet = TRUE)
library(tidyverse)
library(DT)
library(widgetframe)

## Loading required package: htmlwidgets

widgetframe is used to embed the the table.

Default output

That’s the simple output with English formatting:

I’ve added the column example so we can test negative numbers and numbers which absolute values has one (0 – 999,999) or two thousend-delimeters (1,000,000 – 999,999,999).

1
2
3
4
5

out <- mtcars %>% 
  select(hp, wt, qsec, gear) %>%
  mutate(example = (wt * qsec * 1000 * (-1) ** gear -4000) * 1000) %>% 
  DT::datatable() 
frameWidget(out)

English Formatting

1
2
3
4
5
6
7
8
9

out <- mtcars %>% 
  select(hp, wt, qsec, gear) %>%
  mutate(example = (wt * qsec * 1000 * (-1) ** gear -4000) * 1000) %>% 
  DT::datatable() %>% 
    formatRound(
      columns = c("hp", "wt", "qsec", "example"),
      interval = 3, mark = ',', digits=2, dec.mark = "."
    )
frameWidget(out)

Sorting along example is okay.

German Formatting

1
2
3
4
5
6
7
8
9

out <- mtcars %>% 
  select(hp, wt, qsec, gear) %>%
  mutate(example = (wt * qsec * 1000 * (-1) ** gear -4000) * 1000) %>% 
  DT::datatable() %>% 
    formatRound(
      columns = c("hp", "wt", "qsec", "example"),
      interval = 3, mark = '.', digits=2, dec.mark = ","
    )
frameWidget(out)

When you try to sort this table along the column example you’ll see that the ordering is wrong.

Correct sorting

Adding the language option (see line 5 below) solves our problem:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

out <- mtcars %>%
  select(hp, wt, qsec, gear) %>%
  mutate(example = (wt * qsec * 1000 * (-1) ** gear -4000) * 1000) %>% 
  DT::datatable(
    options = list(language = list(thousands = '.', decimal = ','))) %>%
  formatRound(
    columns = c("hp", "wt", "qsec", "example"),
    interval = 3, mark = '.', digits=2, dec.mark = ","
  )
frameWidget(out)

Now you can order along example.