Sorting with formatted numbers in DataTables

[tommy]

Hi,

Does anyone know how to sort numbers that are formatted with comma and decimal separators in a datatable? It is OK on Windows but not on Linux. Sorting does not seem to work if numbers are converted to strings. I have searched a little bit and guess this may be a system locale issue. I am using: Sys.setlocale("LC_COLLATE","C") now, but this is what I get when sorting with a descending order:

183

130

-87

-52

-49

I guess the negative signs are ignored here. How can I fix this? Are there any other alternative methods?

Thanks,

Tommy

[Yihui Xie]

I cannot reproduce the problem with renderDataTable(), although you
did not say how you created the DataTable:

library(shiny)
runApp(list(
ui = fluidPage(dataTableOutput('foo')),
server = function(input, output) {
output$foo = renderDataTable(data.frame(x = c(183, 130, -87, -52, -49)))
}
))


Regards,
Yihui

[tommy]

Hi Yihui,

Sorry, I did not give a good example. Since I want to format my numbers, I use the format() function to add thousand separators. This converts the numbers to characters and sorting won't work.

library(shiny) 

runApp(list( 

  ui = fluidPage(dataTableOutput('foo')), 

  server = function(input, output) { 

    output$foo = renderDataTable(data.frame(x = format(c(18300, 13000, -8700, -5200, -4900),big.mark=',')))

  } 

)) 

I wonder if there's any ways to keep the thousand separators and sort the numbers correctly. DataTables has some options to do that, but I think it's for versions higher than 1.10. Shiny does not seem to support that.

Thanks,

Tommy

[Yihui Xie]

Okay, I see. If DataTables 1.10 supports that, shiny will have to
catch up in the coming weeks (it is on our plan:
https://github.com/rstudio/shiny/issues/487).

Regards,
Yihui

[Dariusz Lesniowski]

Hi Yihui,

if you try this example:

library(shiny) 

runApp(list( 

  ui = fluidPage(dataTableOutput('foo')), 

  server = function(input, output) { 

    output$foo = renderDataTable(data.frame(x = format(c(-3, -1, -13, -14, -21, -250, -231),big.mark=",")))

  } 

)) 

, you will see ,that shiny still has some problems with sorting of character. Sorting seems to work only for number with same length. Any idea how to wix this?

Regards,

Darek

[Yihui Xie]

You may use the new package DT to solve this problem now. See the
section "Format Columns": http://rstudio.github.io/DT/

Regards,
Yihui