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)

Answer 1

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