# Sorting German Numbers in DT datatable

**rstats-tips.net**, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

`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`

.

**leave a comment**for the author, please follow the link and comment on their blog:

**rstats-tips.net**.

R-bloggers.com offers

**daily e-mail updates**about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.