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 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.
This apprach has some drawbacks:
- 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.
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
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: