Working with a multi-index dataframe to overlay bar charts across time by sites

📊 Plotly Python
1

Reposting with better formatting.

I am trying to create an overlapping bar chart across time and sites. One chart should have the values for ‘weight’ and the other should have the values for ‘diverted_weight’.

I have tried a series of approaches and haven’t landed on the correct and most efficient way to do this.

here is my code:

import pandas as pd
import plotly.graph_objs as go
import plotly.express as px
import plotly.offline as pyo
import numpy as np
from plotly.subplots import make_subplots
from plotly.offline import iplot, init_notebook_mode
import cufflinks

cufflinks.go_offline(connected=True)
init_notebook_mode(connected=True)


df = pd.read_excel('test.xls')

df.columns = map(str.lower, df.columns)
df.columns = df.columns.str.replace(" ", "_")
df.replace(0, np.nan, inplace=True)

df = df.pivot_table(index = 'end_date', values = ['weight', 'diversion_weight'],
                        aggfunc = 'mean',
                        columns = 'site')

df

here is the dataframe structure that I have been working with:

diversion_weight	weight
site	Site 1	Site 2	Site 1	Site 2
end_date				
2019-11-30	5864.38	9523.14	8351.48	11257.84
2019-12-31	4761.47	5521.93	6746.97	6441.53
2020-01-31	8748.83	8753.58	12040.58	9934.43
2020-02-29	900.73	635.23	1193.33	739.73


Any help would be greatly appreciated. I am new to python/plotly and feel like I'm missing an easy solution here.
2

Hi @scoutllc, it looks like you’re using an old (< v3) version of potly, I recommend upgrading your version (pip install -U plotly) since the documentation corresponds to more recent versions (>= v4). We have several bar charts examples in the documentation https://plot.ly/python/bar-charts/. Are you trying to make a bar chart with a multicategory xaxis like https://plot.ly/python/axes/#subcategory-multicategory-axes ?

3

Hi @Emmanuelle-

Thanks so much for helping out. I am trying to do the multicategory xaxis, but I don’t know how to efficiently pull that from my dataset. I want the ‘site’ category to be nested in the ‘date’ category. I am also working with two y variables (‘weight’ and ‘diversion_weight’) that I am hoping to overlay.

We’ll eventually have more sites, so I’m trying to automate this to the extent that is possible.