How to add stacked bar plot in a subplot in Plotly?

[charles009]

I have a data frame df, it has over 100 columns, I want to plot a stacked bar plot for these 100 columns, plotly.express is very nice, I can just do this

import plotly.express as px
# df.columns = ['date', 'val1', 'val2', ..., 'val100', 'cost', 'numSales']
cols_to_be_stacked = df.columns[1:-2]
px.bar(df, x='time', y=cols_to_be_stacked)

But I want to have a subplots with (numRows=2, numCols=1), where the two rows share the x axis,

fig = make_subplots(rows=2, cols=1)

## Q1: how should I do my_stacked_bar_plot for 100 columns?
fig.add_trace(my_stacked_bar_plot, row=1, col=1)

# Q2: I want to add time-cost plot to the y-axis on the right side, how 
# should I write the line below?
# In matplotlib, i can just use ax.twinx().
fig.add_trace(go.Scatter(x=df['date'].values, y=df['cost'].values), row=1, col=1)

## now plot the time-numSales on 2nd row.
fig.add_trace(go.Scatter(x=df['date'].values, y=df['numSales'].values), row=2, col=1)

Can someone help me with Q1 and Q2 in the comments above? Or is there another way to achieve this other than using ｀add_trace｀?
Thanks!

EDIT (shorter description as below)

As a simple illustration, if I have a df like this (here I have only 6 of val columns instead of 100 val columns

df = pd.DataFrame([[20240502, -2, 3, -3, 7, -9, 6, 6, 8], 
[20240503, 4, -6, -5, 7, -3, -2, 12, 9]], 
columns=["date", 'val1', 'val2', 'val3', 'val4', 
'val5', 'val6', 'cost', 'numSales'])

    
       date  val1  val2  val3  val4  val5  val6  cost  numSales
0  20240502     2     3    -3    -7     9     6     6         8
1  20240503     4    -6     5     7    -3     8    12         9

I want to a plot like below, (x-axis is shared for the top and bottom subplot)

[AIMPED]

Hey @charles009 welcome to the forums. Could you add some data so we can play around?

It’s not clear for me, what the final graph should look like based on your description.

[r-beginners]

Explain the points addressed for your purposes.

• The specification for the subplot is to use a combination of bar and xy graphs with a second axis enabled.

• The x-axis is set to shared.

• The graph is looped to process one line of data using the bar chart’s relative mode.

• Group the legends since there will be duplicates. At the same time, the legend of the second row is hidden.
  Currently the z-order is not adjustable in plotly, so if you want the line chart to show a little, you will need to adjust the transparency of the bar chart colors.

from plotly.subplots import make_subplots
import plotly.graph_objects as go
import plotly.express as px

colors = px.colors.qualitative.Plotly

fig = make_subplots(rows=2, cols=1,
                    shared_xaxes=True,
                    specs=[
                        [{'type': 'bar', 'type': 'xy', 'secondary_y': True}],
                        [{'type': 'xy'}]
                    ])

for i,col in enumerate(df.columns[1:-2]):
    fig.add_trace(go.Bar(x=[df.loc[0,'date']],
                         y=[df.loc[0, col]],
                         name=col,
                         marker_color=colors[i],
                         legendgroup=col)
                  , secondary_y=True, row=1, col=1)
    fig.add_trace(go.Bar(x=[df.loc[1,'date']],
                         y=[df.loc[1, col]],
                         name=col,
                         marker_color=colors[i],
                         legendgroup=col,
                         showlegend=False),
                  secondary_y=True, row=1, col=1)
    fig.update_layout(barmode='relative')

fig.add_trace(go.Scatter(x=df['date'].values,
                         y=df['cost'].values,
                         name='cost',
                         legendgroup='cost'), row=1, col=1)
fig.add_trace(go.Scatter(x=df['date'].values,
                         y=df['numSales'].values,
                         name='numSales',
                         legendgroup='numSales'), row=2, col=1)

fig.update_xaxes(type='category')
fig.update_layout(height=600, width=800, legend_tracegroupgap=5)
fig.show()

[charles009]

Thank you very much for showing this, this indeed plotted the expected plot.
I understand that looping over columns works , I just feel that when you have 100 columns to loop, it’s a bit too much.

Is there a more elegant way which is similar to this
px.bar(df, x='date', y= 100_cols_to_be_stacked) ?

[r-beginners]

I do not have a way tail to elegantly present 100 columns of data. That’s the challenge about visualization. If you have subcategory elements, there are ways to use them. In the end, you need a story in what and how you show it.

[charles009]

I am wondering why go.Bar doesn’t have the same interface as px.bar?

It is just frustrating for plotly beginners that after you think you just learned how to plot bar plots using px.bar, but realized that when doing subplots go.Bar has a completely different interface.

[r-beginners]

Simply put, it is beyond the scope of the Plotly Express API and requires the use of a graph object; the scope of Express support is detailed here. Since the issues raised are design related, I do not have enough information to explain them. Perhaps we can get comments from the developers.