CODEWITHSUNDEEP
pythonpandasdataframe
delita
delita

Reputation: 1581

Adding a subtotal column to a multilevel column table

This is my dataframe after pivoting:

Country            London                         Shanghai
PriceRange 100-200 200-300 300-400        100-200 200-300 300-400
Code
A               1      1       1             2        2      2   
B              10      10     10            20       20     20

Is it possible to add columns after every country to achieve the following:

Country            London                                Shanghai                              All  
PriceRange 100-200 200-300 300-400  SubTotal      100-200 200-300 300-400  SubTotal 100-200 200-300 300-400 SubTotal
Code
A               1      1       1       3             2        2      2        6          3     3        3      9
B              10      10     10      30            20       20     20       60         30    30       30     90

I know I can use margins=True, however that just adds a final grand total. Are there any options that I can use to achieve this? THanks.

Upvotes: 0

Views: 103

Answers (1)

BENY
BENY

Reputation: 323286

Let us using sum with join 

s=df.sum(level=0,axis=1)
s.columns=pd.MultiIndex.from_product([list(s),['subgroup']])
df=df.join(s).sort_index(level=0,axis=1).assign(Group=df.sum(axis=1))
df
       A                    B                  Group
       1   2   3 subgroup   1   2   3 subgroup      
Code                                                
A      1   1   1        3   2   2   2        6     9
B     10  10  10       30  20  20  20       60    90

Upvotes: 4

Related Questions