I came across a practical case a couple of days ago where the row and column ordering provided out of the box by the R DT package was not enough.

The DT package provides a wrapper around the DataTable javascript library powered by jQuery. DT can be used in Rmarkdown documents as well as in Shiny.

The goal was to display a table containing columns reporting the bandwidth consumption for some sites. The bandwidth is expressed in bits per second (bps). When quantifying large bit rates, decimal prefixes are used. For example:

• 1,000 bps = 1 Kbps (one kilobit or one thousand bits per second)
• 1,000,000 bps = 1 Mbps (one megabit or one million bits per second)
• 1,000,000,000 bps = 1 Gbps (one gigabit or one billion bits per second)

The first approch was to take the numeric value and convert it as a string using R. This is a basic implementation:

The underlying values (variable `v`) are as follows:

The converted values (variable `output`) are as follows:

The data are then combined in a data frame:

When the data frame is rendered with DT with the formatted bandwidth pre-sorted, the ordering is done on the character values. So 980kbps will appear above 1.5Mpbs despite representing a smaller amount. One way to fix it would be to sort the dataframe in R and disable sorting with DT. This approach could be frustrating for the users if the table is being viewed through Shiny. We could use column rendering, where the raw values will be passed to the datatable and the conversion will be done in javascript. In this approach, we are basically replicating the formatting code done in R for javascript (code inspired by the following gist. This apprach has some drawbacks:

• javacript needs to be used. Implementing JavaScript functions for people coding in R may take some times.
• Code may be duplicated as the R implementation still needs to be required (for example, if I want to include )

Another approach would be do pass to DT two values within a cell:

• the underlying raw value (an integer)
• the formatted value regarding the bandwidth consumption
•  the pair can be separated by a special character such as * *,

The underlying raw value is used for sorting, while the formatted value will be used for display. The trick here is to use the different values of the `type` parameter of the column rendering. Based on the documentation, the value can be:

• `filter`. This is being used by the search box. In the implementation below, the value being searched is the same as being displayed.
• `display`. Here, we used the second component of the string as the displayed value.
• `type`
• `sort`. Here, we used the first componen

This approach is purely client-side. When the datatable is included in Rmarkdown document or is generated by Shiny with the server-side disabled, it will work.

But when the datatable is generated using Shiny such as:

it will not work. When looking at the implementation, the filtering and the sorting is done in R. As you can see, when sorting an integer converted in string, R will consider that the value `"10"` is lower than the value `"2"`.

One way to fix it is adjust the first value of the pair by padding it with zero. It works well only for integer value. If you deal with numeric values, you need to make sure that all the numbers have the same number of characters to the left and to the right of the digit.

The following adjusted code should now work in a shiny-based environment: 