Conditional color for add_segment within plotly depending on value

Question

I'm struggling to find a solution to the problem below - already browsed so many articles and other resources.

What I've got as the data source:

Dataframe being created as reactive data (I've truncated the really long SQL query)

 get_MW_index_vergleich <- reactive({ serverdb = dbConnect(MySQL(),
 user='xx',password='xxx',dbname='xxx',host='localhost')
 on.exit(dbDisconnect(serverdb))
 rs <- dbSendQuery(serverdb, 'set character set "utf8"')
 werte_0022 <- sprintf("select some data from tables",input$var_fak_select)
 dataframe_0022 <- fetch(dbSendQuery(serverdb, werte_0022))
 return(dataframe_0022)
 })

This gives me a correctly populated dataframe.

*'data.frame':  
13 obs. of  13 variables:  
$ Dimension: chr  "Anfrage" "Noten" "Dauer" "Interesse" ...  
$ MW_xU    : num  3.47 3.38 3.4 3.76 3.15 ...  
$ MW_gu    : num  3.32 3.56 3.19 3.52 3.4 ...  
$ MW_EU    : num  3.41 3.74 3.19 3.59 3.41 ...  
$ MW_nEU   : num  3.21 3.35 3.2 3.45 3.42 ...  
$ Index_GU : num  100 100 100 100 100 100 100 100 100 100 ...  
$ Index_xU : num  112.4 75 98.9 113.9 101.6 ...  
$ Index_nEU: num  91 71 99.9 96 99.9 ...  
$ Index_EU : num  107 124 100 104 100 ... 
$ Delta_GU : num  12.39 -24.98 -1.08 13.87 1.57 ...**  
$ Delta_GUP: chr  "12.39 %" "-24.98 %" "-1.08 %" "13.87 %" ...  
$ Delta_eU : num  4.97 -49.38 -1.09 9.83 1.61 ...  
$ Delta_neU: num  21.44 4.05 -1.02 17.84 1.69 ...

Now I'd like to plot this, works without any issues (not 100% if this is the correct approach, but it works). But I haven't found any way to change the color of one element, (as you can see, the value Delta_GU is positive/negative)). I'd like to change the color depending on this).

   output$plot_faktoren_index_xu = renderPlotly({
    data_faktoren_index_xu <- get_MW_index_vergleich()
    str(data_faktoren_index_xu)
    mw_index_xu <- plot_ly (data_faktoren_index_xu,color = "#ffffff") %>%
      add_segments(x = 0, xend = ~Index_GU, y = ~Dimension, yend = ~Dimension, line = list(color = "#eeeeee", width = 15), showlegend = FALSE, textfont = text_schwarz) %>%
      add_segments(x = 0, xend = ~Index_xU, y = ~Dimension, yend = ~Dimension, line = list(color = "#000000", width = 5), showlegend = FALSE, textfont = text_schwarz) %>%
      add_segments(x = 100, xend = as.numeric(data_faktoren_index_xu$Index_xU), y = ~Dimension, yend = ~Dimension, line = list(color = "red", width = 3), showlegend = FALSE, textfont = text_schwarz) %>%
#      add_segments(x = 100, xend = which(as.numeric(data_faktoren_index_xu$Index_xU) < 0) , y = ~Dimension, yend = ~Dimension, line = list(color = "red", width = 3), showlegend = FALSE, textfont = text_schwarz) %>%
      # ^^^This segment - depending on value of Delta_GU should get color red or green
      add_markers(x = ~Index_GU, y = ~Dimension, name = "abc Vergleich <br>Alle Kandidaten", marker = list(color = "orange"), textfont = text_schwarz) %>%
      add_text(x = ~Index_xU, y = ~Dimension, textfont = text_schwarz , text = ~Delta_GUP, textposition = "bottom right", showlegend = FALSE ) %>%      
      layout(
        title = paste("Dimensionen          abc-Vergleich <b>", input$var_kand,"</b> mit Alle Kandidaten"),
        xaxis = list(title = "abc "),
        yaxis = list(title = " "),
        margin = list(l = 165)
      )

    }
)

Any ideas really appreciated. I've tried various approaches, but all have failed (ifelse for the color, which, etc.))

I've received three reviews (fixing grammar) so far. Pardon - I'm not a native english speaker. And - I'm new to stack overflow. Did I miss an important step? Or is the issue I've got far too puzzling? — Andreas Fassl
Do not worry too much about the edits; they are mostly harmless (and one of them should in fact not have been approved!). Improvements to the question will (hopefully!) make it more likely to be answered. — Konrad Rudolph
Thanks for your kind words :-) Looks like this is a real challenge, especially as I've already spend several hours on that. Funny thing is, that the values are interpreted correctly as integers, but not when trying to use the ifelse condition or a which clause. Maybe there are limitations in place for this kind of piping approach. In the meantime I'm trying to create an add. column in my sql query, putting "red" and "green" as output based on the algebraic sign — Andreas Fassl
The approach with the add. column did deliver interesting results, I'm getting now two different colors. But - the color coding is being ignored — Andreas Fassl
add_segments(x = 100, xend = ~Index_xU, y = ~Dimension, yend = ~Dimension, color = ~GU_Color, line = list(width = 5), showlegend = FALSE, textfont = text_schwarz) %>% — Andreas Fassl

Andreas Fassl Andreas Fassl · Accepted Answer · 2018-10-12T08:54:11

As mentioned above - the issue is resolved.

    pal <- c( "#b8e55a","#e59029")

    mw_index_xu <- plot_ly (data_faktoren_index_xu,color = ~Color_xu, colors = pal, showlegend = FALSE) %>%

Conditional color for add_segment within plotly depending on value

1 Answers