shiny sorting numbers as strings

Question

I'm using shiny and the renderDataTable function from the DT package to show a table in my webapp. When sorting, it seems to be treating the numbers as strings, ordering by first digit then second and so on i.e what should sort to 1, 2, 5, 100, 250 would be sorted to 1, 100, 2, 250, 5

I've tried specifying colClasses when reading the csv, but doesn't seem to work.

server.R shown, my ui.R is just a dataTableOutput

library(DT)
library(dplyr)
date <- format(Sys.Date() - 1, '%Y_%m_%d')
date2 <- format(Sys.Date() - 2, '%Y_%m_%d')

# Read data in
tab1 <- read.csv(paste0(date, '_tabs.csv'), stringsAsFactors = FALSE, colClasses = c('character', rep('integer', 9)))
tab1 <- na.omit(tab1)
tab2 <- read.csv(paste0(date2, '_tabs.csv'), stringsAsFactors = FALSE, colClasses = c('character', rep('numeric', 9)))

# Ensuring both tables have matching values for country
tab3 <- tab2[tab2$X %in% tab1$X, ]
missingr <- setdiff(tab1$X, tab3$X)
for (j in missingr) {
  tab3 <- rbind(tab3, rep(0, length(tab1)))
  tab3[nrow(tab3), 1] <- j
}

# Sorting by country and creating a new dataframe of differences
Country <- tab1$X
tab1 <- arrange(tab1, X)
tab3 <- arrange(tab3, X)
tab1 <- tab1[, !(names(tab1) %in% 'X')]
tab3 <- tab3[, !(names(tab3) %in% 'X')]
tab2 <- tab1 - tab3

# Adding total column and country column to dataframes
c1 <- c('Total', colSums(tab1))
c2 <- c('Total', colSums(tab2))
rownames(tab2) <- Country
tab2 <- data.frame(Country, tab2)
tab1 <- data.frame(Country, tab1)
tab1 <- tab1[tab1$total > 100, ]
tab2 <- tab2[tab2$Country %in% tab1$Country, ]
tab1 <- rbind(tab1, c1)
tab2 <- rbind(tab2, c2)


shinyServer(function(input, output) {
  output$tab1 <- renderDataTable({tab1},
    rownames = FALSE, options = list(lengthMenu = list(c(20, 10, -1), c('20', '10', 'All')), 
    initComplete = JS("function(settings, json) {","$(this.api().table().header()).css({'background-color': '#000', 'color': '#fff'});","}"),
      autoWidth = TRUE,
      columnDefs = list(list(width = '200px', targets = "_all"))
    ))
  output$tab2 <- renderDataTable({tab2},
    rownames = FALSE, options = list(lengthMenu = list(c(20, 10, -1), c('20', '10', 'All')), 
    initComplete = JS("function(settings, json) {","$(this.api().table().header()).css({'background-color': '#000', 'color': '#fff'});","}"),
      autowidth = TRUE,
      columnDefs = list(list(width = '200px', targets = "_all"))
    ))
}
)

Where are you ordering the table? The renderDataTable does not order the table by default. The section 4.1 of rstudio.github.io/DT/options.html explains the order-option. — user5029763
Clicking on a column sorts it, as on the page you just linked, the default option of sorting by the leftmost column is what I want initially (sort by country) and then users can click on a column to sort them. — Azeem Iqbal
Try using str on the tables you've created. It will tell you what sort of data you have And adding the output to your question would be helpfull. — user5029763
I don't have enough rep to post images, but here's the link until someone takes it down i.imgur.com/No7SiJT.png pic shows what happens when you click on the leftmost number column to sort. the X column when reading in the csv files is the country code, so that needs to be character anyway. The rest of the columns should be nums/ints — Azeem Iqbal

Oskar Landgren Oskar Landgren · Accepted Answer · 2019-12-04T11:08:42

We had the same problem which caused a problem when sorting columns on our Rshiny app as well. We discovered that it was caused by cbind converting our numerical values to strings.

> foo=c(111,10,3,4,5)
> bar=c("should","it","order","like","yoda")

> df1 = data.frame(cbind(foo,bar))
> df1[order(df1[,1]),]

  foo    bar
2  10     it
1 111 should
3   3  order
4   4   like
5   5   yoda

You can verify this by checking

> str(df1)
'data.frame':   5 obs. of  2 variables:
 $ foo: Factor w/ 5 levels "10","111","3",..: 2 1 3 4 5
 $ bar: Factor w/ 5 levels "it","like","order",..: 4 1 3 2 5

This is because cbind is called first, and then creates an array of chars. When you think about it is natural because an array needs to have all elements of the same type, and numbers can be converted to chars but not the other way around. This is easily avoided by using cbind on data frame objects instead:

> df2 = cbind(data.frame(foo),data.frame(bar))
> df2[order(df2[,1]),]

  foo    bar
3   3  order
4   4   like
5   5   yoda
2  10     it
1 111 should

Or in this example case perhaps more elegantly by creating a new data frame from the array of strings and numbers directly:

> df3 = data.frame(foo,bar)
> df3[order(df3[,1]),]

  foo    bar
3   3  order
4   4   like
5   5   yoda
2  10     it
1 111 should

shiny sorting numbers as strings

3 Answers