pandas sorting by subtotal

I have a multi indexed dataframe like below. I’ m giving the full data consciously, because when I try this solution pandas multi index sort with several conditions with small data it is OK, but when I try with full data, it doesn’t work.

Multi-indexes are: ‘MATERIALNAME’ and ‘CURINGMACHINE’

‘MATERIALNAME’: level 0

‘CURINGMACHINE’: level 1

                            HEADERCOUNTER
MATERIALNAME CURINGMACHINE               
1011         PPU04L                     1
             SubTotal                   1
1033         PPZB12L                    1
             SubTotal                   1
2618         PPZF04L                    1
             SubTotal                   1
2913         PPZB02L                    4
             PPZB02R                    8
             SubTotal                  12
2983         PPZB03L                    1
             SubTotal                   1
3411         PPB01L                     1
             PPC13R                     1
             SubTotal                   2
4444         PPU11L                     1
             SubTotal                   1
4600         PPU10L                     1
             SubTotal                   1
4994         PPV06R                     4
             SubTotal                   4
6161         PPZB14R                    3
             SubTotal                   3
6751         PPZE02L                    1
             SubTotal                   1
6907         PPZC11L                    3
             PPZC11R                    3
             SubTotal                   6
7905         PPY06R                     1
             SubTotal                   1
8594         PPZA10L                    2
             PPZA10R                    6
             SubTotal                   8
9995         PPZF10R                    1
             SubTotal                   1
D250         PPH07                      1
             SubTotal                   1
D560         PPH10                      1
             SubTotal                   1
D570         PPH11                      2
             SubTotal                   2
K188         PPZD09R                    1
             SubTotal                   1
K204         PPB02R                     1
             SubTotal                   1
K314         PPZC12L                    4
             PPZC12R                    1
             SubTotal                   5
K318         PPN12R                     2
             SubTotal                   2
K698         PPZA12L                    1
             SubTotal                   1
K709         PPE11R                     1
             PPE12R                     1
             SubTotal                   2
K902         PPL12L                     1
             PPM02L                     1
             PPM02R                     3
             SubTotal                   5
K934         PPM06L                     2
             SubTotal                   2
K967         PPL09L                     4
             PPL09R                     1
             PPN04L                     2
             SubTotal                   7
K990         PPM04L                     1
             PPM04R                     4
             PPN05R                     2
             SubTotal                   7
Grand        Total                     81

I want to sort this df according to sub-totals of each level 0 ‘MATERIALNAME’ column.

Edit: This shouldn’ t happen.

Answer

First, I’d reset the index so that you have again a data frame (now it looks like a pandas series with multi-index)

df = df.reset_index()

Then you can add new column with the values you want to sort by

df["subtotal"] = df.groupby("MATERIALNAME")["HEADERCOUNTER"].transform(sum)

Finally, just sort it (highest first). If the subtotal is same in multiple groups, it will sort them by group identifier.

df.sort_values(["subtotal", "MATERIALNAME"], ascending=False)