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!

Hi eycramer, can you explain a little bit more what you mean by "clicking the column to sort"? — Dubukay, Jan 26 '21 at 02:58
@Dubukay There are some arrows at the top of each column, which serve to sort a column in descending or ascending order. — Stéphane Laurent, Jan 26 '21 at 08:27

score 4 · Accepted Answer · answered Jan 26 '21 at 08:26

4

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

answered Jan 26 '21 at 08:26

Stéphane Laurent

75,186
15
119
225

Thanks so much! This answer was very helpful – eycramer Jan 26 '21 at 19:54
This is not working for me. I added by target column as `columnDefs = list( list(targets = 5, render = render)` but the column 5 is sorted with missing values at the top. – Lazarus Thurston Mar 22 '23 at 20:36
@LazarusThurston do you take into account column 0 ? – Stéphane Laurent Mar 22 '23 at 21:15
Yes I have taken into account starting column 0 in all my columnDefs list items. Because I hide a few columns (visible = F). – Lazarus Thurston Mar 29 '23 at 02:43

Sorting with NA using datatable function in DT package

1 Answers1