As I progress with my endeavor to construct a user-friendly transition matrix in R, following up on the previous post regarding adding a vertical line to the first column header in a data table, I am faced with a new challenge.
Upon executing the provided code snippet at the end of this message, a transition table is generated as depicted in the image below (with my annotations overlaid). My goal is to combine the top 2 cells (rows) in the left-most column and align the column header "to_state" vertically at the center. Are there any suggestions on achieving this? Preferably using DT for table rendering.
https://i.sstatic.net/2MJnP.png
It's worth noting that in the comprehensive code from which this example is derived, the table dynamically adjusts its size based on the number of unique states identified in the underlying dataset.
I came across some potentially helpful insights in the discussion about Shiny: Merge cells in DT::datatable on Stack Overflow. However, it appeared that the merging was focused on row cells within the body of the table rather than the header, making it incompatible with my scenario.
Although I lack expertise in HTML and CSS, there are valuable online resources offering tips on structuring HTML tables, including techniques for combining column and row merges. You may refer to these links here and here. This made me contemplate whether abandoning my current DT/html combination in favor of designing the table entirely in html might yield better guidance for someone like me who is relatively inexperienced.
Here is the provided MWE code:
library(DT)
library(shiny)
library(dplyr)
library(htmltools)
library(data.table)
data <-
data.frame(
ID = c(1,1,1,2,2,2,3,3,3),
Period = c(1, 2, 3, 1, 2, 3, 1, 2, 3),
Values = c(5, 10, 15, 0, 2, 4, 3, 6, 9),
State = c("X0","X1","X2","X0","X2","X0", "X2","X1","X0")
)
numTransit <- function(x, from=1, to=3){
setDT(x)
unique_state <- unique(x$State)
all_states <- setDT(expand.grid(list(from_state = unique_state, to_state = unique_state)))
dcast(x[, .(from_state = State[from],
to_state = State[to]),
by = ID]
[,.N, c("from_state", "to_state")]
[all_states,on = c("from_state", "to_state")],
to_state ~ from_state, value.var = "N"
)
}
ui <- fluidPage(
tags$head(tags$style(".datatables .display {margin-left: 0;}")), # < left-align the table
h4(strong("Base data frame:")),
tableOutput("data"),
h4(strong("Transition table inputs:")),
numericInput("transFrom", "From period:", 1, min = 1, max = 3),
numericInput("transTo", "To period:", 2, min = 1, max = 3),
h4(strong("Output transition table:")),
DTOutput("resultsDT"),
)
server <- function(input, output, session) {
results <-
reactive({
results <- numTransit(data, input$transFrom, input$transTo) %>%
replace(is.na(.), 0) %>%
bind_rows(summarise_all(., ~(if(is.numeric(.)) sum(.) else "Sum")))
results <- cbind(results, Sum = rowSums(results[,-1]))
})
output$data <- renderTable(data)
output$resultsDT <- renderDT(server=FALSE, {
req(results())
datatable(
data = results(),
rownames = FALSE,
filter = 'none',
container = tags$table(
class = 'display',
tags$thead(
tags$tr(
tags$th(colspan = 1, '', style = "border-right: solid 1px;"),
tags$th(colspan = 10, sprintf('From state where initial period = %s', input$transFrom))
),
tags$tr(
mapply(tags$th, colnames(results()), style = sprintf("border-right: solid %spx;", c(1L, rep(0, ncol(results())-1L))), SIMPLIFY = FALSE)
)
)
),
options = list(scrollX = F
, dom = 'ft'
, lengthChange = T
, pagingType = "numbers" # hides Next and Previous buttons
, autoWidth = T
, info = FALSE # hide the "Showing 1 of 2..." at bottom of table
, searching = FALSE # removes search box
),
class = "display"
) %>%
formatStyle(c(1), `border-right` = "solid 1px")
})
}
shinyApp(ui, server)