How to select columns from MultiIndex pandas table in a custom order?-CodePudding

How to select columns from MultiIndex pandas table in a custom order? In this case, how can I have Quantity to come before Price (without the use of ascending = false) as well as the size to be in the order: medium, large, small.

Desired output:

                  Quantity                 Price
Size  medium  large  small  medium  large  small
   0      3       4      3      6       6      5
   1      6       7      5      9       9      8
   2      2       2      1      4       5      2

Creation of Dataframe:

df = pd.DataFrame({"Item": ["foo", "foo", "foo", "bar", "bar",
                         "bar", "baz", "baz", "baz"],
                   "Size": ["small", "medium", "large", "small",
                         "medium", "large", "small", "medium",
                         "large"],
                   "Price": [1, 2, 2, 3, 3, 4, 5, 6, 7],
                   "Quantity": [2, 4, 5, 5, 6, 6, 8, 9, 9]})

df = pd.pivot_table(df,index=["Item"],columns=["Size"],values=["Price","Quantity"],aggfunc=np.sum)
df.reset_index(drop=True, inplace=True)

#Dataframe:
                     Price              Quantity
Size  large  medium  small  large  medium  small
   0      4       3      3      6       6      5
   1      7       6      5      9       9      8
   2      2       2      1      5       4      2

I have tried to use dataframe.loc[], however, I have realized .loc[] does not maintain the specific order.

df.loc[:, (['Quantity', 'Price'], ['medium', 'large', 'small'])]

CodePudding user response：

You can use pd.MultiIndex.from_product to generate the indices:

idx = pd.MultiIndex.from_product([['Quantity', 'Price'], ['medium', 'large', 'small']])

idx
MultiIndex([('Quantity', 'medium'),
            ('Quantity',  'large'),
            ('Quantity',  'small'),
            (   'Price', 'medium'),
            (   'Price',  'large'),
            (   'Price',  'small')],
           )

df[idx]

     Quantity              Price            
Size   medium large small medium large small
0           6     6     5      3     4     3
1           9     9     8      6     7     5
2           4     5     2      2     2     1

CodePudding user response：

Passing a list of lists works:

df.loc(axis=1)[['Quantity', 'Price'], ['medium', 'large', 'small']]
 
     Quantity              Price            
Size   medium large small medium large small
0           6     6     5      3     4     3
1           9     9     8      6     7     5
2           4     5     2      2     2     1

pandas version 1.2