Sorting with NA using datatable function in DT package

Question

I am trying to create an html table using the datatable function in the DT package so that when I sort the data in R markdown, missing rows are sorted after the highest number.

For example, in the following table, when I sort by "age" in the markdown file, I would like the row with NA to be listed last so that the order is 14,15,21,NA.

dat <- data.frame("Age" = c(21,15,NA,14), 
"Name" = c("John","Dora", "Max", "Sam"), 
"Gender" = c("M","F","M",NA))


DT::datatable(dat, filter = c("top"))

I have tried using "na.last = TRUE" and this works when the datatable initially prints, however when clicking the column to sort, NA is still before 14.

Any help would be much appreciated!

Answer 1

With the render columnwise option, you can set the value of the missing values during the sorting:

library(DT)

dat <- data.frame("Age" = c(21,15,NA,14), 
                  "Name" = c("John","Dora", "Max", "Sam"), 
                  "Gender" = c("M","F","M",NA))

render <- JS(
  "function(data, type, row) {",
  "  if(type === 'sort' && data === null) {",
  "    return 999999;",
  "  }",
  "  return data;",
  "}"
)

datatable(
  dat, 
  filter = "top",
  options = list(
    columnDefs = list(
      list(targets = 1, render = render)
    )
  )
)