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.
Advertisement
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)