0

As a newcomer to nice-looking graphs, I was hoping I could get the crowd's assistance with this one:

I want to create a stacked bar plot with some specifications

  1. The Totals bar is to be labeled with its percentage mix of column Type (see below)
  2. The indices in the plot, Year and Crncy in this case, should be horizontal and preferably not repeated (see picture below)
  3. The (Total, Total) row in the plot below is to have a separate (right) y-axis (see picture below) and a label as in 1. above

Some mock data:

data={'Year':[2021, 2021, 2021, 2021, 2022, 2022, 2023, 2024, 2024, 2025, 2026, 2027, 2027, 2027],'Crncy':['GBP', 'SEK', 'USD', 'USD', 'SEK', 'SEK', 'SEK', 'EUR', 'EUR', 'EUR', 'USD', 'EUR', 'EUR', 'SEK'],'Type':['Fixed', 'Float', 'Fixed', 'Float', 'Fixed', 'Float', 'Float', 'Fixed', 'Float', 'Fixed', 'Float', 'Fixed', 'Float', 'Float'],'Amount':[100, 200, 300, 400, 500, 100, 150, 600, 800, 500.0, 200, 50, 50, 100]}
df = pd.DataFrame(data)
a=df.groupby(['Year','Type'])['Amount'].sum().reset_index()
b=df.groupby(['Type'])['Amount'].sum().reset_index()
a['Crncy']='Total'
b['Crncy']='Total'
b['Year']='Total'
df=pd.concat([df,a,b]).reset_index(drop=True)

So far I have managed to do the following steps, allowing me to create the percentages grouped as the specification above

a=df.groupby(['Year','Crncy','Type'])['Amount'].sum()
b=a.div(a.sum(level=[0,1]))*100

>> 
Year   Crncy  Type 
2021   GBP    Fixed    100.000000
       SEK    Float    100.000000
       Total  Fixed     40.000000
              Float     60.000000
       USD    Fixed     42.857143
              Float     57.142857
2022   SEK    Fixed     83.333333
              Float     16.666667
       Total  Fixed     83.333333
              Float     16.666667
2023   SEK    Float    100.000000
       Total  Float    100.000000
2024   EUR    Fixed     42.857143
              Float     57.142857
       Total  Fixed     42.857143
              Float     57.142857
2025   EUR    Fixed    100.000000
       Total  Fixed    100.000000
2026   Total  Float    100.000000
       USD    Float    100.000000
2027   EUR    Fixed     50.000000
              Float     50.000000
       SEK    Float    100.000000
       Total  Fixed     25.000000
              Float     75.000000
Total  Total  Fixed     50.617284
              Float     49.382716

Now, my solution is to pivot the original DataFrame df and then plot as a stacked barplot:

df.reset_index().sort_values(by=['Year','Crncy','Type']).pivot_table(index=['Year','Crncy'],columns='Type',values='Amount').fillna(0.0).plot.bar(stacked=True)

>>

enter image description here

But what I am after, should look more like this:

enter image description here

Any help is very much appreciated.

Maybe there is a much easier way to go about this, or you guys have some more intuitive way of presenting the data - I am open for suggestions, thank you.

/N

gussilago
  • 922
  • 3
  • 12
  • 27
  • 1
    Based on point 3 (and points 1 and 2 really), you'll need to create your axes in matplotlib rather than plotting just from pandas. – BigBen Aug 18 '21 at 14:23
  • Have you looked into adapting [this answer](https://stackoverflow.com/questions/19184484/how-to-add-group-labels-for-bar-charts-in-matplotlib/39502106#39502106) ? – Brendan Aug 18 '21 at 14:49
  • @Brendan Thanks, will have a look - but was hoping for an easier, back-of-the-envelope, solution. – gussilago Aug 19 '21 at 06:18

0 Answers0