Conditionally assign color and label to heatmap cells based on a secondary sub-grouping not used in x, y, color mappings / plot_ly conditional text

Question

This is an example of how my dataset looks like:

sampleData <- structure(list(LEVELS = structure(c(1L, 2L, 3L, 4L, 1L, 2L, 3L, 
4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L, 2L, 3L, 1L, 
1L), .Label = c("A", "B", "C", "D"), class = "factor"), GROUP = structure(c(1L, 
1L, 1L, 1L, 3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 2L, 2L, 2L, 2L, 5L, 
5L, 5L, 5L, 1L, 1L, 5L, 1L), .Label = c("AUD", "CTO", "KOP", 
"PIL", "POH"), class = "factor"), MEMBER = structure(c(17L, 18L, 
19L, 20L, 6L, 7L, 3L, 11L, 10L, 2L, 8L, 5L, 12L, 9L, 1L, 4L, 
14L, 15L, 13L, 16L, 17L, 17L, 13L, 19L), .Label = c("AS", "Ca", 
"Fc", "FFZ", "Fg", "Fo", "Fp", "Fv", "GH1", "Lp", "Nb", "Qc", 
"Rq1", "Rt", "Rt2", "Rtcz", "T1", "T2", "T3", "T4"), class = "factor"), 
    VALUE = c(0.001, 1, 0.3, 0.04, 0.1, 0.2, 0.06, 0.08, 0.12, 
    1, 1, 0.3, 0.99, 0.56, 0.54, 0.7, 0.09, 0.1, 0.95, 0.001, 
    0.01, 0.15, 0.005, 0.001)), class = "data.frame", row.names = c(NA, 
-24L))

   LEVELS GROUP MEMBER VALUE
1       A   AUD     T1 0.001
2       B   AUD     T2 1.000
3       C   AUD     T3 0.300
4       D   AUD     T4 0.040
5       A   KOP     Fo 0.100
6       B   KOP     Fp 0.200
7       C   KOP     Fc 0.060
8       D   KOP     Nb 0.080
9       A   PIL     Lp 0.120
10      B   PIL     Ca 1.000
11      C   PIL     Fv 1.000
12      D   PIL     Fg 0.300
13      A   CTO     Qc 0.990
14      B   CTO    GH1 0.560
15      C   CTO     AS 0.540
16      D   CTO    FFZ 0.700
17      A   POH     Rt 0.090
18      B   POH    Rt2 0.100
19      C   POH    Rq1 0.950
20      D   POH   Rtcz 0.001
21      B   AUD     T1 0.010
22      C   AUD     T1 0.150
23      A   POH    Rq1 0.005
24      A   AUD     T3 0.001

I want to show LEVELS on y axis and GROUP on x. If VALUE < 0.05 the corresponding cell will be red.

But each GROUP has several MEMBERs. Even if one MEMBER in a particular GROUP:LEVELS pair is < 0.05 the cell should be RED. There is no need for ALL members of a GROUP:LEVELS pair to be smaller than 0.05 for that cell to be assigned red color. But I want to report the name of ALL MEMBERs that are < 0.05 in each GROUP:LEVELS pair.

This is one example of a case that satisfies this condition:

   LEVELS GROUP MEMBER VALUE
1       A   AUD     T1 0.001
24      A   AUD     T3 0.001

Therefore, if I hover over the cell A:AUD I wish to see both T1 and T3 reported.

To reiterate;

• I want to make sure a certain cell is assigned red color even if there is only one MEMBER that has VALUE < 0.05.
• If there is more than one member satisfying the VALUE < 0.05 condition, I want to report all of those MEMBER's names in my plotly text.

How can I do this?

My current code is as follows which seems to be working well in showing red cells even if one MEMBER in a certain GROUP:LEVELS pair has VALUE < 0.05. But plotly only reports the name of one MEMBER even if there are more MEMBERs that have VALUE < 0.05.

library(plotly)
library(dplyr)

vals <- unique(scales::rescale(c(sampleData$VALUE)))
o <- order(vals, decreasing = FALSE)
cols <- scales::col_numeric("Blues", domain = NULL)(vals)
colz <- setNames(data.frame(vals[o], cols[o]), NULL)
names(colz) <- c("var","col")
colz$col <- as.character(colz$col)

colz <- dplyr::mutate(colz, col = replace(col, var < 0.05, "#ff3300"))

plotly::plot_ly(data = sampleData,
                x = ~GROUP, 
                y = ~LEVELS,
                z = ~VALUE, 
                type = "heatmap", 
                xgap = 0.5, ygap = 0.2,
                hoverinfo = 'text',
                text = ~paste('</br> Member: ', MEMBER),
                colorscale = colz
)

Answer 1

A solution is to create a new column in sampleData which will hold the labels, then in the call to plot_ly() refer to this new column rather than MEMBER. This way you can customise the labels to be whatever you like - so in this case we can aggregate multiple labels where necessary, and only display labels for cells having at least one value < 0.05.

The new code is inserted below, plus a slight change to the plot_ly() call. What it does is create a labels dataframe by filtering sampleData to rows having value < 0.05. It then aggregates the labels by grouping on LEVELS:GROUP - e.g. for A:AUD the label will be "T1 T3". Then these labels are merged (or rather joined) back into sampleData, with the " Member: " text prepended, and labels set to empty string where we don't want a label displayed. Then in the plot_ly() call you just need to reference the label column.

library(plotly)
library(dplyr)

vals <- unique(scales::rescale(c(sampleData$VALUE)))
o <- order(vals, decreasing = FALSE)
cols <- scales::col_numeric("Blues", domain = NULL)(vals)
colz <- setNames(data.frame(vals[o], cols[o]), NULL)
names(colz) <- c("var","col")
colz$col <- as.character(colz$col)

colz <- dplyr::mutate(colz, col = replace(col, var < 0.05, "#ff3300"))

# filter data to values < 0.05
labels <- filter(sampleData, VALUE < 0.05)
# aggregate the labels for each unique combination of LEVEL:GROUP
labelsAgg <- aggregate(labels$MEMBER, list(labels$LEVELS, labels$GROUP), paste, collapse = " ")
# set names to match sampleData
labelsAgg <- setNames(labelsAgg, c("LEVELS", "GROUP", "label"))
# prepend "Member" heading, must do this here so that labels we want to be blank are truly blank
labelsAgg$label <- paste("</br> Member: ", labelsAgg$label)
# merge/join labels back with sampleData, using merge() here messed up the factors, so using left_join() is a workaround
sampleData <- left_join(sampleData, labelsAgg,  by = c("LEVELS", "GROUP"))
# replace NA with empty string, otherwise "NA" appears as the label
sampleData[is.na(sampleData$label), "label"] <- ""

# note the text parameter is simply the label now, i.e. the new column sampleData$label
plotly::plot_ly(data = sampleData,
                x = ~GROUP, 
                y = ~LEVELS,
                z = ~VALUE, 
                type = "heatmap", 
                xgap = 0.5, ygap = 0.2,
                hoverinfo = 'text',
                text = ~paste(label),
                colorscale = colz
)

You can easily change the format of the labels using the collapse parameter if, say, you prefer a comma separator rather than a space.

You could certainly tidy the added code a little if you like, though it seems to do the trick.