Pandas Python - How to reorder second level row indexes according to a specific list of labels-CodePudding

How can I sort the second-level row indexes ("Product Code") according to a specific list of labels?

This is the code used to run my sample dataset with 2-level row indexes:

tdf = df.pivot_table(index=["Shop Outlet", "Product Code"],
                     columns="Year",
                     values=["Product Sales Vol","Unique Product"],
                     aggfunc= lambda x: len(x.unique()),
                     margins=True)
tdf

This is sample data of what the dataset structure looks like:

                                      Product Sales Vol         Unique Product  
Shop Outlet    Product Code          2019   2020   2021     2019   2020   2021
 USA                     OL            75     89    102        5      6      8   
                         P1            75     89    102        5      6      8
                         P2            75     89    102        5      6      8
                         P3            75     89    102        5      6      8
                         P4            75     89    102        5      6      8
                         P5            75     89    102        5      6      8
                         P6            75     89    102        5      6      8
                         PP            75     89    102        5      6      8
                         S1            75     89    102        5      6      8
                         S2            75     89    102        5      6      8
                         S3            75     89    102        5      6      8
 Canada                  OL            75     89    102        5      6      8   
                         P1            75     89    102        5      6      8
                         P2            75     89    102        5      6      8
                         P3            75     89    102        5      6      8
                         P4            75     89    102        5      6      8
                         P5            75     89    102        5      6      8
                         P6            75     89    102        5      6      8
                         PP            75     89    102        5      6      8
                         S1            75     89    102        5      6      8
                         S2            75     89    102        5      6      8
                         S3            75     89    102        5      6      8

I want to be able to i) re-order the second-level row index (Product Code) by a specific order according to the list of labels below, ii) and add a "All" category under Product Code for each shop outlet. This would be a sample of what the dataset should look like:

product_codes = ["PP", "P1", "P2", "P3", "P4", "P5", "P6", "S1", "S2", "S3", "OL"]

                                      Product Sales Vol         Unique Product  
Shop Outlet    Product Code          2019   2020   2021     2019   2020   2021
 USA                     PP            75     89    102        5      6      8   
                         P1            75     89    102        5      6      8
                         P2            75     89    102        5      6      8
                         P3            75     89    102        5      6      8
                         P4            75     89    102        5      6      8
                         P5            75     89    102        5      6      8
                         P6            75     89    102        5      6      8
                         S1            75     89    102        5      6      8
                         S2            75     89    102        5      6      8
                         S3            75     89    102        5      6      8
                         OL            75     89    102        5      6      8
                        All           825    979   1122       55     66     88
 Canada                  PP            75     89    102        5      6      8   
                         P1            75     89    102        5      6      8
                         P2            75     89    102        5      6      8
                         P3            75     89    102        5      6      8
                         P4            75     89    102        5      6      8
                         P5            75     89    102        5      6      8
                         P6            75     89    102        5      6      8
                         S1            75     89    102        5      6      8
                         S2            75     89    102        5      6      8
                         S3            75     89    102        5      6      8
                         OL            75     89    102        5      6      8
                        All           825    979   1122       55     66     88

CodePudding user response：

Can you try the following:

tdf.reindex(product_codes, level=1)