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
- The
Totals
bar is to be labeled with its percentage mix of columnType
(see below) - The indices in the plot,
Year
andCrncy
in this case, should be horizontal and preferably not repeated (see picture below) - 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)
>>
But what I am after, should look more like this:
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