0

How do you switch the format of a dataframe from a standard single row to multi-index columns? I've tried playing with groupby but it doesn't seem efficient.

+------------+----------+--------+----------+--------+
|  Product   |   Item   | Region | In Stock | Colour |
+------------+----------+--------+----------+--------+
| Electronic | Phone    | Canada | Y        | Black  |
| Electronic | Computer | Canada | N        | Silver |
| Furniture  | Table    | Canada | Y        | Brown  |
| Furniture  | Chair    | Canada | Y        | Black  |
| Electronic | Phone    | USA    | Y        | Black  |
| Electronic | Computer | USA    | Y        | Black  |
| Furniture  | Table    | USA    | N        | Black  |
| Furniture  | Chair    | USA    | Y        | Black  |
| Furniture  | Couch    | USA    | Y        | Black  |
+------------+----------+--------+----------+--------+

to

+------------+----------+----------+--------+----------+--------+
|            |          |       Canada      |         USA       |
+  Product   +   Item   +----------+--------+----------+--------+
|            |          | In Stock | Colour | In Stock | Colour |
+------------+----------+----------+--------+----------+--------+
| Electronic | Phone    | Y        | Black  | Y        | Black  |
|            | Computer | N        | Silver | Y        | Black  |
| Furniture  | Table    | Y        | Brown  | N        | Black  |
|            | Chair    | Y        | Black  | Y        | Black  |
|            | Couch    |          |        | Y        | Black  |
+------------+----------+----------+--------+----------+--------+

Thanks!

Stacknewb
  • 13
  • 3

1 Answers1

0

If you need it in the specific order you have posted, you can use use melt with pivot. Otherwise, you will lose order on the multi-index:

df = (df.melt(id_vars=['Product','Item','Region'])
        .sort_values(['Region', 'variable'], ascending=[True,False])
        .pivot(index=['Product', 'Item'], columns=['Region', 'variable'])
        .droplevel(0, axis=1))
df
Out[1]: 

Region                Canada              USA       
variable            In Stock  Colour In Stock Colour
Product    Item                                     
Electronic Computer        N  Silver        Y  Black
           Phone           Y   Black        Y  Black
Furniture  Chair           Y   Black        Y  Black
           Couch         NaN     NaN        Y  Black
           Table           Y   Brown        N  Black
David Erickson
  • 16,433
  • 2
  • 19
  • 35
  • Do you know why I'm getting a length mismatch with the code? ValueError: Length mismatch: Expected axis has 18 elements, new values have 2 elements – Stacknewb Oct 29 '20 at 13:40
  • Yes @Stacknewb you need to upgrade your version of pandas for this code to work. – David Erickson Oct 29 '20 at 16:57
  • Is there a way to export the multiindex to excel, while colouring the cells "Black" with a black background? – Stacknewb Oct 31 '20 at 00:28
  • @Stacknewb possibly, that is a different problem, so I would create a new questio. You can reference this question in that new question. – David Erickson Oct 31 '20 at 00:45