Added new category and subtotals columns to Pivoted table

user_931037 0
Viewed 67

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

This is the dtype of my DF:

Country PriceRange
London     100 - 200     float64
           200 - 300     float64
           300 - 400     float64
Shanghai   100 - 200     float64
           200 - 300     float64
           300 - 400     float64
dtype: object

I have tried the following from a user's help:

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))
pandas python
2 Answers

Change the last line of code 

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)
s2=df.sum(level=1,axis=1)
s2.columns=pd.MultiIndex.from_product([['ALL'],list(s2)])
df=df.join(s2)
df
           A                           ...     ALL                         
     100-200 200-300 300-400 subgroup  ... 100-200 200-300 300-400 subgroup
Code                                   ...                                 
A          1       1       1        3  ...       3       3       3        9
B         10      10      10       30  ...      30      30      30       90
[2 rows x 12 columns]
user_79645270

With stack and unstack you can chain all in one:

# toy data
df = pd.DataFrame(np.arange(16).reshape(4,4),
                  columns=pd.MultiIndex.from_product([['a','b'], [0,1]])
                 )

(df.stack(level=0)
   .assign(SubTotal=lambda x: x.sum(1))
   .unstack(level=-1)
   .swaplevel(0,1, axis=1)
   .sort_index(axis=1)
)

Output:

    a                b             
    0   1 SubTotal   0   1 SubTotal
0   0   1        1   2   3        5
1   4   5        9   6   7       13
2   8   9       17  10  11       21
3  12  13       25  14  15       29

Update: at the second look, your problem might be solved by adding margins=True into your pivoting function.

user_42384080
Related