How to create a stacked bar in pandas when the group values aren't uniquely identified

Question

This question may seem easy but I haven't seen this anywhere and since I am new to python I don't know how to approach this.

I am trying to plot a stacked bar chart from this dataframe:

I tried using the following code:

df.set_index('A', inplace=True)
df['R'].plot.bar(legend=True, stacked=True)

Which resulted in

But I want the same values on the x-axis to be stacked like this:

How would I do this?

user_15239951 · Answer

This can't be done without transforming the dataframe to a wide format, which can only be done if each value 'R' in group 'A', is assigned an identifier
Tested in python 3.10, pandas 1.3.4, matplotlib 3.5.0

import pandas as pd
import matplotlib.pyplot as plt

# sample dataframe
data = {'A': [208, 208, 208, 210, 213, 213, 213, 213, 213, 215, 222], 
        'R': [1, 5, 2, 9, 5, 8, 6, 3, 3, 6, 4]}
df = pd.DataFrame(data, index='A')

# create a new column adding a unique identifier
df = df.assign(cols=df.groupby(level='A').cumcount())

# display(df)
     R  cols
A           
208  1     0
208  5     1
208  2     2
210  9     0
213  5     0
213  8     1
213  6     2
213  3     3
213  3     4
215  6     0
222  4     0

# pivot the dataframe into a wide form which easily plots
dfp = df.pivot(columns='cols', values='R')

# display(dfp)
cols    0    1    2    3    4
A                            
208   1.0  5.0  2.0  NaN  NaN
210   9.0  NaN  NaN  NaN  NaN
213   5.0  8.0  6.0  3.0  3.0
215   6.0  NaN  NaN  NaN  NaN
222   4.0  NaN  NaN  NaN  NaN

# plot 
ax = dfp.plot.bar(stacked=True, legend=False, rot=0)
plt.show()

How to create a stacked bar in pandas when the group values aren't uniquely identified

1 Answers