Formattable + DT Sorting Issue

[sdhutchins]

I'm not sure if this is an issue that only I am having, but when I combine the 2 packages, it displays the table properly. However, when I attempt to sort a column, it considers the first number in any column to sort.

For example,

It's considering 0 to be the lowest value. and I have lower values.

and

This should be sorting from greatest to least minutes. Thoughts?

My guess is it could be something I'm doing improperly with DT.

My code:

lineup5 <- drop_read_csv(sprintf("%slineup5.csv", lfrdir), dest = "temp", sep = ',')
lineup4 <- drop_read_csv(sprintf("%slineup4.csv", lfrdir), dest = "temp", sep = ',')
lineup3 <- drop_read_csv(sprintf("%slineup3.csv", lfrdir), dest = "temp", sep = ',')
lineup2 <- drop_read_csv(sprintf("%slineup2.csv", lfrdir), dest = "temp", sep = ',')

colnames(lineup2) <- c('GROUP', 'W', 'L', 'W%', 'MIN', 'OffRtg', 'DefRtg', 'NetRtg',
                       'AST%', 'AST/TOV', 'AST RAT', 'OREB%', 'DREB%', 'REB%',
                       'TM TO%', 'eFG%', 'TS%','PACE', 'PIE')
colnames(lineup3) <- c('GROUP', 'W', 'L', 'W%', 'MIN', 'OffRtg', 'DefRtg', 'NetRtg',
                       'AST%', 'AST/TOV', 'AST RAT', 'OREB%', 'DREB%', 'REB%',
                       'TM TO%', 'eFG%', 'TS%','PACE', 'PIE')
colnames(lineup4) <- c('GROUP', 'W', 'L', 'W%', 'MIN', 'OffRtg', 'DefRtg', 'NetRtg',
                       'AST%', 'AST/TOV', 'AST RAT', 'OREB%', 'DREB%', 'REB%',
                       'TM TO%', 'eFG%', 'TS%','PACE', 'PIE')
colnames(lineup5) <- c('GROUP', 'W', 'L', 'W%', 'MIN', 'OffRtg', 'DefRtg', 'NetRtg',
                       'AST%', 'AST/TOV', 'AST RAT', 'OREB%', 'DREB%', 'REB%',
                       'TM TO%', 'eFG%', 'TS%','PACE', 'PIE')

ui <- navbarPage(title = "",
                 # tabPanel("5 Man Lineups",
                 #          fluidRow(DT::dataTableOutput("table1"))),
                 # tabPanel("4 Man Lineups",
                 #          fluidRow(DT::dataTableOutput("table2"))),
                 # tabPanel("3 Man Lineups",
                 #          fluidRow(DT::dataTableOutput("table3"))),
                 tabPanel("2 Man Lineups",
                          fluidRow(DT::dataTableOutput("table4")))))

server <-function(input, output, session) {
  # Filter data based on selections

  sign_formatter <- formatter("span", 
                              style = x ~ style(color = ifelse(x >= 0, "green",
                                                               ifelse(x < 0, "red", "black"))))
  sign_formatter(c(-1, 0, 1))
  output$table4 <- DT::renderDataTable(
    as.datatable(formattable(lineup2, list(
      NetRtg = sign_formatter
    )))
  )

}

shinyApp(ui = ui, server = server)

@renkun-ken

[Minded13]

Hello,

I've got the same issue. It seems numerical values are considered as characters, which gives this bad sorting.

Here's a reproducible example :

library(shiny)
library(shinydashboard)
library(DT)
library(formattable)

ui <- dashboardPage(
        dashboardHeader(),
        dashboardSidebar(),
        dashboardBody(
                DT::dataTableOutput("tabOut")   
        )
) 

server <- function(input, output) {

    output$tabOut <- DT::renderDataTable({

                tab <- data.frame(A = 1:26, B = letters)
                tab[, 1] <- as.numeric(tab[, 1]) # to be sure it's numerical

                as.datatable(formattable(tab, list("A"  = color_bar("lightblue"))))
            })

}

shinyApp(ui, server)

When just executing the line :

as.datatable(formattable(tab, list("A" = color_bar("lightblue"))))

sorting is made properly. I guess R class of vector get lost somewhere... Is there a way to avoid it?

Have a nice day.

[EdenAnteby]

having the same issue too. anyone find solution?

[sdhutchins]

@EdenAnteby per @Minded13 the solution is something like below....using as.numeric

tab[, 1] <- as.numeric(tab[, 1]) # to be sure it's numerical

[Minded13]

Hi, That works out of shiny, but not inside. My reproducible example above demonstrate it, and it does contain the "as.numerical" try. Any suggestion ?

[mhlinder]

formattable converts all columns to character, so that style can be embedded within the cells.

In my use case, I do not need formatting for the numeric values I would like to sort, so I just convert the underlying data frame in the data table back to numeric:

df <- data.frame(Num=1:10)
ft <- as.datatable(formattable(df))
ft$x$data$Num <- as.numeric(ft$x$data$Num)

This doesn't solve the underlying problem, and it will break if you want to format the numeric values. But, for my limited case it works well enough...

[liyi-1989]

Got the same issue. Any solution? (especially, when the data frame is a reactive value)

Thanks!

[Minded13]

Hello, In my case, it seems to be the _colorbar option which imply the bad sorting. I used _colortile instead and it works well. My reproducible example with the two kind of style to show the behavior :

library(shiny)
library(shinydashboard)
library(DT)
library(formattable)

ui <- dashboardPage(
        dashboardHeader(),
        dashboardSidebar(),
        dashboardBody(
                DT::dataTableOutput("tabOut")   
        )
) 

server <- function(input, output) {

    output$tabOut <- DT::renderDataTable({

                tab <- data.frame(A = 1:26, B = 26:1)
                #tab[, 1] <- as.numeric(tab[, 1]) # Finally it's not necessary
                #tab[, 2] <- as.numeric(tab[, 2]) # I let it there as a reminder...

                out <- as.datatable(
                        formattable(tab, 
                                list(
                                        "A"  = color_tile("white", "lightblue"),
                                        "B"  = color_bar("red")
                                )
                        )
                )

                out
            })

}

shinyApp(ui, server)

I don't know if something change when the df is a reactive value... I guess not. Hope this help !

[abnercasallo]

I have the same problem, somebody found the solution (when the numbers are painted)?

[vituri]

Same problem here. No solution found.

[bartverweire]

Hi, the problem is that formatter replaces numbers with text, which is then sorted by datatable, e.g. using the example from @Minded13

<span style="display: inline-block; direction: rtl; unicode-bidi: plaintext; border-radius: 4px; padding-right: 2px; background-color: lightblue; width: 11.54%">3</span>

In order to sort this correctly, you have to make sure that the string can be sorted correctly, e.g. by left padding numbers with spaces (or zeros when the numbers are used in style properties).

A slightly modified example shows how this can be done, with a custom color_bar (padded_color_bar) and a numeric column. Notes :

1. the sprintf function in the padded_color_bar function, left padding the percent values with zeros
2. the sprint function is used to left pad the numbers in column B with spaces, because no other style properties are changed, and sorting occurs on the content of the span element
3. there are probably better ways to format the results of the percent function

library(shiny)
library(shinydashboard)
library(DT)
library(formattable)

paddedcolor_bar <- function(color = "lightgray", fun = "proportion", ...) {
    fun <- match.fun(fun)
    formatter("span",
              style = function(x) style(
                  display = "inline-block",
                  direction = "rtl",
                  "unicode-bidi" = "plaintext",
                  "border-radius" = "4px",
                  "padding-right" = "2px",
                  "background-color" = csscolor(color),
                  width = sprintf("%010.4f%%", 100 * percent(fun(as.numeric(x), ...)))
              ))
}

ui <- dashboardPage(
    dashboardHeader(),
    dashboardSidebar(),
    dashboardBody(
        DT::dataTableOutput("tabOut")   
    )
) 

server <- function(input, output) {

    output$tabOut <- DT::renderDataTable({

        tab <- data.frame(A = 1:26, B = runif(26,0,10), C = letters)
        tab[, 1] <- as.numeric(tab[, 1]) # to be sure it's numerical

        as.datatable(
            formattable(tab, 
                        list("A"  = paddedcolor_bar("lightblue"),
                             "B" = formatter("span", x ~ sprintf("%10.2f", x, rank(-x)))))
        )
    })

}

shinyApp(ui, server)