DT sorting problem - custom number formating

Question

I try to sort numbers by variables in DT::datatable() when user click to sort button.

I have date like this (B: Billion, M: Million)

a	b	c
1 B	3 B	3 B
99 B	2 M	4 M
3 M	3 B	1 B
99 B	2 M	2 M

I want to sort by special characters B and M. I tried to use format functions in DT but I couldn't fix it.

Any ideas?

The data:

structure(list(Value = c(3.549e+09, 2.95e+09, 3.005e+09, 3.744e+09, 
4.676e+09, 4.882e+09, 4.188e+09, 2.613e+09, 1.37e+09, 1.204e+09, 
1.428e+09, 3.845e+09, 6.251e+09, 6.815e+09, 2.782e+09), CValue = c(3.549e+09, 
-5.99e+08, 5.5e+07, 7.39e+08, 4.676e+09, 2.06e+08, -6.94e+08, 
-1.575e+09, 1.37e+09, -1.66e+08, 2.24e+08, 2.417e+09, 6.251e+09, 
5.64e+08, -4.033e+09)), row.names = 3458:3472, class = c("data.table", 
"data.frame"))

Codes:


    library(DT)
    datatable(data)

You can first convert them into real numbers (without "B" or "M") then you can sort them by quantity with `dplyr::arrange`. — Alvaro Morales, Jan 23 '22 at 15:58
I want to sort them in DT::datatable() when they click to sort button by variables in html file. — Ugur Dar, Jan 23 '22 at 16:02
Could you post an expected output? And could you also provide the data with `dput()` ([see description](https://stackoverflow.com/a/5963610/12242625)). — Marco_CH, Jan 23 '22 at 16:07
Output have a datatable, originally the datatable put sort buttons on the table. But when convert the numbers to character it's only sort by first character in instances. I want to sort them by number and special character, for example, the column like this: 1 B, 3 M, 2 B, 99 B and when the user click sort button it should be sorted like 1 M, 3 M, 99 M, 1 B, 2 B or descending order. — Ugur Dar, Jan 23 '22 at 16:22

score 1 · Answer 1 · answered Jan 27 '22 at 09:14

One can achieve what you wat with the render option:

library(DT)

dat <- data.frame(
  V1 = c("2 B", "13 M", "5.5 M"),
  V2 = c("99 M", "4 B", "1 B")
)

js <- c(
  "function(data, type, row, meta){",
  "  if(type === 'sort' || type === 'type'){",
  "    var factors = {M: 1e6, B: 1e9};",
  "    var split = data.split(' ');",
  "    var number = Number(split[0]);",
  "    var unit = split[1];",
  "    data = number * factors[unit];",
  "  }",
  "  return data;",
  "}"
)

datatable(
  dat,
  options = list(
    columnDefs = list(
      list(
        targets = c(1, 2), render = JS(js)
      )
    )
  )
)

It's working well thank you :) – Ugur Dar Feb 02 '22 at 15:51 — Ugur Dar, Feb 02 '22 at 15:51

DT sorting problem - custom number formating

1 Answers1