Server or client side processing

The server argument determines whether the data is processed on the server side or the client (browser) side. If server = TRUE (the default), the browser receives only the displayed data. If server = FALSE the browser receives all the data, which can slow it down if the dataset is large.

Data download buttons

The combination of extensions = 'Buttons' with buttons = c('csv', 'excel') and dom = 'Bfrtip' (in the list passed to options) creates buttons that allow the user to download the data table in csv or excel format. The data that is downloaded will be either the currently visible data or the entire table, depending on the server option.

Dom

The dom option defines the position of elements around the table. ‘Bfrtip’ is a combination of elements - Button, filter, processing display element, table, table information summary, pagination control. The order of the characters determines the layout of the elements around the table. Full documentation is here.

Scrolling options

scrollX and scrollY enable scrolling when the table wider or longer than the display.

Change page length

When pagination = TRUE, change the number of rows per page with pageLength

Row selection

selection = 'single' enables the user to select a single row at a time by clicking. The default value is multiple, enabling the selection of multiple rows, and none disables selection. By default, the selected rows are highlighted in blue, and within an interactive Shiny app the indices of selected rows can be accessed by using input$tableID_rows_selected in server.R.

Column defs

columnDefs is used to assign specific options to columns. In this example, I set all columns (targets = '_all') to be centre-aligned (className = 'dt-center'). I also hide (visible = FALSE) column 1, 9 and 10 (targets = c(0, 8, 9)).

Column filters

The filter argument determines whether to add a filter (search bar) for columns, and where to put it. Other options are none and top, and you can also control the style (see documentation).

DT and HTML

The Shiny function icon() can be used to generate icons from fontawsome and glyphicons.

icon("thumbs-up", lib = "font-awesome")

as.character() reveals the underlying HTML:

as.character(icon("thumbs-up", lib = "font-awesome"))

## [1] "<i class=\"fa fa-thumbs-up\" role=\"presentation\" aria-label=\"thumbs-up icon\"></i>"

You can put HTML elements, such as these icons, in the dataframe and they will be displayed in the DT datatable, as long as the escape = FALSE option is set. The default, escape = TRUE, escapes the HTML elements and displays the underlying HTML code - this is because unexpected HTML entities can potentially be a security issue.

icon_villagers <- villagers %>%
  mutate(gender = ifelse(gender == "female",
                         as.character(icon("venus", lib = "font-awesome")),
                         as.character(icon("mars", lib = "font-awesome"))))
datatable(icon_villagers[,1:8],
          options = list(scrollX = TRUE),
          escape = FALSE)

I like to include a tooltip with a hoverable explanation over pictures and icons in my tables.

Any image can be a cute icon if you make it small enough! I’m going to use the villager pictures here.

Use the HTML <img> tag to include an image. Include the URL with src="url_here.png, change the size with height, add a tooltop with data-toggle="tooltip", data=placement="right", and the label with title="Text goes here". Remember to escape the special characters.

pic_villagers <- villagers %>%
  mutate(
    picture = paste0(
      "<img src=\"",
      url,
      "\" height=\"30\" data-toggle=\"tooltip\" data-placement=\"right\" title=\"",
      species,
      "\"></img>"
    )
  ) %>%
  select(picture, name, gender, species, birthday, personality, phrase) %>%
  unique()

datatable(pic_villagers,
          options = list(scrollX = TRUE),
          escape = FALSE,
          rownames = FALSE)

Why not both? Combining DT and formattable

If you want the features of both DT and formattable, you can combine them by converting the formattable() output to as.datatable(), and much of the formattable features will be preserved.

However, one problem I had was that when using DT::datatable, missing values (NA) are left blank in the display (which I prefer), but in the converted from formattable() version, NA’s are printed. Also, color_bar columns seem to be converted to character, which can no longer be sorted numerically.

formattable(
  pic_items,
  list(
    `sell_value` = color_tile("white", "pink"),
    `buy_value` = color_bar("lightblue"),
    orderable = true_false_formatter
  )
) %>%
  as.datatable(escape = FALSE,
               options = list(scrollX = TRUE),
               rownames = FALSE)

Heatmap-like fill effect:

First we define the breaks and colours for the fill gradient. The cost of items ranges from 5 to 320,000 (excluding two huge outliers at 1,000,000 and 1,200,000).

## Colour and values for table colour formatting
brks <- seq(5, 320000, 1000)
clrs <- colorRampPalette(c("white", "#6baed6"))(length(brks) + 1)

Then we apply the formatStyle() function to the output of datatable() to set the backgroundColor for selected columns:

datatable(pic_items,
          options = list(scrollX = TRUE,
                         order = list(list(5, 'desc'))),
          escape = FALSE,
          rownames = FALSE) %>%
      formatStyle(c("sell_value", "buy_value"), backgroundColor = styleInterval(brks, clrs))

Abbreviate long cells

Sometimes some cells have a large amount of text that would mess up the table layout if I showed it all. In these cases, I like to abbreviate long values and show the full text in a tooltip.

To do this, you can use JavaScript to format the column to show a substring with “…” and the full string in a tooltip (<span title="Long string">Substring...</span) when values are longer than N characters (in this case 10 characters). You can do this using columnDefs and pass JavaScript with the JS() function:

datatable(pic_items,
          options = list(scrollX = TRUE,
                         columnDefs = list(
                                       list(
                                         targets = 1,
                                         render = JS(
                "function(data, type, row, meta) {",
                "return type === 'display' && data.length > 10 ?",
                "'<span title=\"' + data + '\">' + data.substr(0, 10) + '...</span>' : data;",
                "}")))),
          escape = FALSE,
          rownames = FALSE)

Really plain table

Sometimes I don’t need any of the faff. Here’s how to get rid of it all:

headerCallbackRemoveHeaderFooter <- c(
  "function(thead, data, start, end, display){",
  "  $('th', thead).css('display', 'none');",
  "}"
)

datatable(
  my_pic_villagers,
  options = list(
    dom = "t",
    ordering = FALSE,
    paging = FALSE,
    searching = FALSE,
    headerCallback = JS(headerCallbackRemoveHeaderFooter)
  ),
  selection = 'none',
  callback = JS(
    "$('table.dataTable.no-footer').css('border-bottom', 'none');"
  ),
  class = 'row-border',
  escape = FALSE,
  rownames = FALSE,
  filter = "none",
  width = 500
)