We are going to use two packages for this blog. The first package is
the tidyverse package, which we will use for data
manipulation. The package that we will focus most of our time on which
is the DT package, is known for making data tables more appealing for
shiny apps, or interactive purposes. In Shiny, we can use DT, to react
to selection, create expandable rows, select inputs throughout the
shiny, customize tables, customize input, and sort/filtering. It is
common to see DT in Shiny. Though most of this blog showcases, how to
format and use DT in single use, it is nice to know that it has a higher
functionality.
require(tidyverse)## Loading required package: tidyverse## ── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
## ✔ dplyr 1.1.0 ✔ readr 2.1.4
## ✔ forcats 1.0.0 ✔ stringr 1.5.0
## ✔ ggplot2 3.4.1 ✔ tibble 3.1.8
## ✔ lubridate 1.9.2 ✔ tidyr 1.3.0
## ✔ purrr 1.0.1
## ── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
## ✖ dplyr::filter() masks stats::filter()
## ✖ dplyr::lag() masks stats::lag()
## ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errorsrequire(DT)## Loading required package: DTmtcars$name <- rownames(mtcars)
mtcars1 <- mtcars %>%
mutate(company = as.factor(gsub(" .*$", "", name)),
cyl = as.factor(cyl),
vs = as.factor(vs),
am = as.factor(am),
gear = as.factor(gear),
carb = as.factor(carb)) %>%
mutate(mpg1 = case_when(mpg >= 25 ~ 2,
T ~ 1)) %>%
relocate(c(name, company), .before = mpg)In Datatable, you can interactive filter and search in the table for
answers. Notice: In options(DT.Options) we specify to have
pageLength = 10. We can make this anything, for the case of these
tables, 10 seems reasonable.
Table 1: We do the minimum of having the color of the column -> “cyl” be associated with the value (4, 6, 8). Notice the syntax in this case, we have formatStyle() be the function, with the arguments for column = “cyl” and the backgroundColor = styleEqual, the values within “cyl” and the color we would like to make those cells.
Table 2: We take the first table one step further by adding a new font color to the column, using white so it is legible.
Table 3: The removal of two columns, this occurs by using the options argument, and within there using the columnDefs = argument. We remove the targeted columns of mpg and mpg1. We can also remove them by the indices that are associated with those columns.
Table 4: Like table 3, we use table 4 to hide rows, but the unique
aspect is we are still highlighting the cells by cylinders used, “cyl”.
In this case we are hiding the column “cyl” and highlighting the cells
in the column “name”. Notice we remove all the columns except for the
column “name”. Those cells within that column, are then highlighted by
the values associated with the cylinders of those rows. You can also
notice the rownames = F arguement, removed the first column
of row names.
options(DT.options = list(pageLength = 10))
table1 <- datatable(mtcars1) %>%
formatStyle("cyl",
backgroundColor = styleEqual(c(4, 6, 8),
c("blue", "green", "red")))
table1table2 <- datatable(mtcars1) %>%
formatStyle("cyl",
color = "white",
backgroundColor = styleEqual(c(4, 6, 8),
c("blue", "green", "red")))
table2table3 <- datatable(mtcars1, options = list(columnDefs = list(list(targets = c("mpg", "mpg1"), visible = F)))) %>%
formatStyle("cyl",
color = "white",
backgroundColor = styleEqual(c(4, 6, 8),
c("blue", "green", "red")))
table3table4 <- datatable(mtcars1, rownames = F, options = list(columnDefs = list(list(targets = c(2:13), visible = F)))) %>%
formatStyle("name", "cyl",
color = "white",
backgroundColor = styleEqual(c(4, 6, 8),
c("blue", "green", "red")))
table4When using DT, we can also highlight an entire row.
Table 5: Notice when we add the code, target = "row".
This allows us to color the whole row by a specific column value. In
this case we use the column “vs” which is binary, 0 and 1. If the value
is 0 then the row is highlighted beige, and blue if vs in that row is
equal to 1.
Table 6: We can do the same even if we remove that specific column from being shown. Notice same rows are beige and blue, based on the vs column, even if that column is no longer in the visible data provided.
table5 <- datatable(mtcars1, rownames = F) %>%
formatStyle("vs",
target = "row",
backgroundColor = styleEqual(c(0,1), c("beige", "cornflowerblue")))
table5table6 <- datatable(mtcars1, rownames = F, options = list(columnDefs = list(list(targets = c("vs", "am", "mpg", "mpg1"), visible = F)))) %>%
formatStyle("vs",
target = "row",
backgroundColor = styleEqual(c(0,1), c("beige", "cornflowerblue")))
table6Now that we have highlighted cells and rows, lets take a look at other ways to customize the datatable.
Table 7: In this table we use the same code as in table 6, but add
the argument class = 'cell-border stripe'. This arguement
shows borders to each cell. Notice you can see the lines more clearly
between column and row.
Table 8: We are able to change the names of the column names. Using
colnames = c(new = old).
Table 9: We do the same as in table 8 , but modify all the column names. In order for this to work out percisely, ensure that you are changing all the names for each column.
table7 <- datatable(mtcars1, rownames = F, class = 'cell-border stripe', options = list(columnDefs = list(list(targets = c("vs", "am", "mpg", "mpg1"), visible = F)))) %>%
formatStyle("vs",
target = "row",
backgroundColor = styleEqual(c(0,1), c("beige", "cornflowerblue")))
table7table8 <- datatable(mtcars1,
colnames = c("Name" = "name", "Company" = "company"), rownames = F, class = 'cell-border stripe') %>%
formatStyle("vs",
target = "row",
backgroundColor = styleEqual(c(0,1), c("beige", "cornflowerblue")))
table8table9 <- datatable(mtcars1,
colnames = c("Name", "Company", "MPG", "Cylinder", "Displacement", "Horsepower", "Rear Axel Ratio", "Weight", "QuarterMileTime", "Transmission (0 = automatic, 1 = manual)", "V/S", "Gear", "Carburetors", "MPG (Binary)"), rownames = F, class = 'cell-border stripe') %>%
formatStyle("vs",
target = "row",
backgroundColor = styleEqual(c(0,1), c("beige", "cornflowerblue")))
table9The last unique customization I wanted to showcase was the column
filter. Using the argument, filter = "top", we create
filters for each of the columns, and the filter goes to the top. The
other value can be “bottom” where the filter goes to the bottom of the
datatable. Notice how the numeric, factor and character columns differ
in the filtering.
table10 <- datatable(mtcars1, rownames = F, filter = "top", options = list(columnDefs = list(list(targets = c("vs", "am", "mpg", "mpg1"), visible = F)))) %>%
formatStyle("vs",
target = "row",
backgroundColor = styleEqual(c(0,1), c("beige", "cornflowerblue")))
table10