Search code examples
rdata.tableplotlyr-plotlyline-plot

plot line chart with plotly when having always different number of variables/columns

I would like to create a line plot using the following data table (just a counterexample):

dt <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                 Germany = rnorm(365, 2, 1), Austria = rnorm(365, 3, 4), 
                 Czechia = rnorm(365, 2, 3), check.names = FALSE)

The code for the plot looks like this:

p <- plot_ly(dt, x = ~dt$date, y = ~dt$Germany, mode = 'lines', type = 'scatter', 
             line = list(color = "#007d3c"), 
             connectgaps = TRUE, name = dt[, 2],
             hovertemplate = paste("%{xaxis.title.text}:  %{x}<br>",
                                   "%{y} \u20ac/MWh <br>")) %>%
     add_trace(y = ~dt$Austria, name = dt[, 3],
               line = list(color = "#007d3c", width = 3, dash = 'dot')) %>%
     add_trace(y = ~dt$Czechia, 
               line = list(color = "#b4b4b4", width = 3, dash = 'dash'), 
               name = dt[, 4]) %>%
     layout(title = "<b>Coal", xaxis = list(title = "Date"), 
            yaxis = list(title = "EUR/MWh"), showlegend = FALSE)

Basically everything works as expected, but my data table never has to look like this. The first column (date) always remains the same, but each additional column can sometimes have a different name. In addition, it may be that the data table has more or fewer columns than the above data table in dt. I would still like to create a line plot to match such scenarios. For example, the table could (after choosing other inputs) have the following columns:

date | Germany | Netherlands

or: date | Austria | Slovenia | Denmark

Furthermore, it would be great if instead of trace 0, trace 1 etc. the respective column name is used.

How would this work?

Solution

  • For a scenario like this it's best practice to convert your data.table from wide to long format via melt.data.table:

    library(plotly)
library(data.table)


DT <- data.table(date = seq(as.Date('2020-01-01'), by = '1 day', length.out = 365),
                 Germany = rnorm(365, 2, 1), Austria = rnorm(365, 3, 4), 
                 Czechia = rnorm(365, 2, 3), check.names = FALSE)


DT <- melt.data.table(DT, id.vars = "date")

p <- plot_ly(DT, x = ~ date, y = ~ value, color = ~ variable, mode = 'lines', type = 'scatter',
             connectgaps = TRUE, name = DT[, 2],
             hovertemplate = paste("%{xaxis.title.text}:  %{x}<br>",
                                   "%{y} \u20ac/MWh <br>")) %>%
  layout(title = "<b>Coal", xaxis = list(title = "Date"), 
         yaxis = list(title = "EUR/MWh"), showlegend = FALSE)

p

    result