R plotly堆叠条形图叠加折线轨迹及次Y轴的实现方法

我正在实现一个生成带附加折线轨迹的堆叠百分比条形图的函数，可通过宽格式（wide-form）或长格式（long-form）dataframe创建堆叠条形图，以下两段代码生成的图表效果基本一致：

宽格式Dataframe实现代码 #

library(dplyr)
library(plotly)  # install.packages("plotly")

# simple example data for SO post
some_dates = c(as.Date('2021-01-01'), as.Date('2021-02-01'),
               as.Date('2021-03-01'), as.Date('2021-04-01'))

bar1 = c(0.25,0.45,0.65,0.75)
bar2 = c(0.60,0.40,0.20,0.10)
bar3 = c(0.15,0.15,0.15,0.15)

line_data = c(0,1,2,3)

# wide form dataframe
df_bars = data.frame("db" = some_dates, "b1" = bar1,
                     "b2" = bar2, "b3" = bar3)

df_line = data.frame("line_dates" = some_dates, "line" = line_data)

plot_so1 = plot_ly(x = df_bars$db,
                   y = df_bars[[colnames(df_bars)[2]]],
                   type = 'bar',
                   name = colnames(df_bars)[2]) %>% 
  layout(title = 'My plot title',
         xaxis = list(title = 'db'),
         yaxis = list(title = 'CatProportions'),
         barmode = 'stack',
         showlegend = TRUE)

# Now loop through the rest of the columns except for the ones already used.
# This is done because "in the wild", the plot is being built in a function
# that has data which is passed to it so the number and names of the columns
# that are used to build the plot are not know in advance.
for (col_index in 3:length(some_dates)) {
  plot_so1 =
    add_trace(plot_so1,
              x = df_bars$db,
              y = df_bars[[colnames(df_bars)[col_index]]],
              name = colnames(df_bars)[col_index])
}

长格式Dataframe实现代码 #

## long form of dataframe #########################
df_bars_long = df_bars %>%
  pivot_longer(!db, names_to = "Categories", values_to = "CatProportions")
# build same plot from long form dataframe
plot_so2 = plot_ly(data = df_bars_long,
                   x = ~db, y = ~CatProportions,
                   color = ~Categories,
                   type = "bar") %>% 
  layout(barmode = "stack")

## above works, now try to add the line trace #####
plot_so2 = plot_ly(data = df_bars_long,
                   x = ~db, y = ~CatProportions,
                   color = ~Categories,
                   type = "bar") %>% 
  # add_trace(x = df_line$line_dates,
  #           y = df_line$line,
  #           type = 'scatter', mode = 'lines', name = 'my line',
  #           line = list(color = '#000000')) %>% 
  layout(title = 'My plot title',
         xaxis = list(title = 'db'),
         yaxis = list(title = 'CatProportions'),
         barmode = 'stack',
         showlegend = TRUE)

我了解使用长格式创建这类图表是最佳实践，但我需要使用另一个仅包含x值列和y值列的dataframe添加折线轨迹，目前仅能在宽格式代码中实现，可通过在宽格式代码中添加如下代码段实现：

plot_so1 = add_trace(plot_so1,
                     x = df_line$line_dates,
                     y = df_line$line,
                     type = 'scatter', mode = 'lines', name = 'my line',
                     line = list(color = '#000000'))

生成的图表效果如下：

待解决问题 #

• 核心问题：如何基于宽格式dataframe的代码为折线轨迹创建次Y轴？
• 次要问题：我所需的最终图表能否使用长格式dataframe实现，如果可以具体如何操作？

此前参考了Stack Overflow帖子《Stacked Bar Chart with Line Chart not working in R with plotly》，但该帖子未涉及堆叠条形图场景，无法解决我的问题。

内容的提问来源于stack exchange，提问作者Michael Szczepaniak