R Rhansontable - Color Specific Cells Conditionally On Other Table

Question

This is my first post on SO, so please bear with me if my post is not fully aligned with the rules. Though I will do my best to give a clear description of my problem, the resources I already checked and provide a reproducible example.

First of all, let me explain the problem: I would like to highlight specific cells of a Rhandsontable filled with numeric values in Shiny using different colors (red and green) according to a condition from another table (same nb of columns/rows filled with boolean values, where TRUE=green, FALSE=red).

Let's say I want to start from the following two tables:

DF = data.frame(val = 1:3, big = LETTERS[1:3])

DF_condition = data.frame(val = c(TRUE, FALSE,FALSE), big = c(FALSE,TRUE,FALSE))

I would like the cells at (1,1) and (2,2), i.e where the other table is set at TRUE to be green and all other cells to be red.

I have looked at multiple other posts within SO (and other) like these:

However, none of them solve my problem. Indeed, I am facing the three following main issues:

I am NOT trying to highlight an entire row / column but only specific cells
The conditional formatting should be done using a condition from another table
My database has hundred thousands of rows and around 20 columns (and I would like if possible to use a vectorized approach and not a loop through columns/rows)

As I am not familiar with JScript, used within rhandsontable, I am a bit stuck.

Please find below a minimal reproduceable example:

ui <- shinyUI(bootstrapPage(
  rHandsontableOutput("hot")
))

server <- shinyServer(function(input, output) {
  output$hot <- renderRHandsontable({
    DF = data.frame(val = 1:3, big = LETTERS[1:3])
    DF_condition = data.frame(val = c(TRUE, FALSE,FALSE), big = c(FALSE,TRUE,FALSE))
    col_highlight = c(1,2)
    row_highlight = c(1,3)

    rhandsontable(DF, col_highlight = col_highlight-1, row_highlight = row_highlight-1) %>%
      hot_cols(renderer = "
               function(instance, td, row, col, prop, value, cellProperties) {
               Handsontable.renderers.NumericRenderer.apply(this, arguments);
               if (instance.params) {
               hcols = instance.params.col_highlight
               hcols = hcols instanceof Array ? hcols : [hcols]
               hrows = instance.params.row_highlight
               hrows = hrows instanceof Array ? hrows : [hrows]
               }
               if (instance.params && hcols.includes(col) && hrows.includes(row)) td.style.background = 'red';
               }")
    })
})
shinyApp(ui, server)

Which highlights the two first columns and the two first rows, whereas I would need only cells (1,1) and (2,2).

Thank you very much in advance for your kind help.

Updating question to see if anyone who can help ? – Adrien P. Dec 09 '19 at 16:26 — Adrien P., Dec 09 '19 at 16:26

score 0 · Answer 1 · answered Dec 20 '19 at 11:09

You are currently checking whether the column is in the Array [1,2] and whether the row is in the Array [1, 2]. All four combinations (1,1), (1,2), (2,1) and (2,2) satisfy this requirement, which is why all those cells are colored red. Instead, you want to make sure that if the column is the first element of the Array [1,2], then the row should also be the first element of the Array [1,2], and not the second.
I'm no JavaScript expert, but something like this might work:

if (instance.params && hcols.includes(col) && hrows.includes(row) && (hcols.indexOf(col) == hrows.indexOf(row))) {
td.style.background = 'red';
}

R Rhansontable - Color Specific Cells Conditionally On Other Table

1 Answers1