python: how to melt dataframe retaining specific order / custom sorting

Question

I have a dataframe df

Cat  B_1 A_2 C_3
A      1   2   3
B      4   5   6
C      7   8   9

which I want to convert into a dataframe so that the rows in column Label are following the order of df columns for each category.

Desired output

    Cat Label Value
    A     B_1    1
    A     A_2    2
    A     C_3    3
    B     B_1    4
    B     A_2    5
    B     C_3    6
    C     B_1    7
    C     A_2    8
    C     C_3    9  

when I try the

pd.melt(df, id_vars=["Cat"], var_name="Label",value_name="Value")

I lose the desired order in column Label, the results are sorted like below,

Cat Label Value
A     B_1    1
B     B_1    4
C     B_1    7
A     A_2    2
...

Can the desired order of rows be forced in the melt function? if not, how can be this custom sorting achieved?

---

UPDATE

I renamed the labels as they don't follow the alphabetical order, so that simple sorting doesn't work

Answer 1

IIUC, you can use your exact same code and add .sort_values('Cat'), or more simply:

df.melt('Cat',var_name='Label',value_name='Value').sort_values('Cat')

  Cat Label  Value
0   A   L_1      1
3   A   L_2      2
6   A   L_3      3
1   B   L_1      4
4   B   L_2      5
7   B   L_3      6
2   C   L_1      7
5   C   L_2      8
8   C   L_3      9

If you want to order it in a custom manner (In the example below, B precedes A which precedes C), then you can set Cat to be an ordered categorical:

melted = df.melt('Cat',var_name='Label',value_name='Value')

melted['Cat'] = pd.Categorical(melted['Cat'], categories=['B','A','C'], ordered=True)

melted.sort_values('Cat')

  Cat Label  Value
1   B   L_1      4
4   B   L_2      5
7   B   L_3      6
0   A   L_1      1
3   A   L_2      2
6   A   L_3      3
2   C   L_1      7
5   C   L_2      8
8   C   L_3      9

An alternative is to use stack, but then you have to deal with annoying renaming of columns:

df.set_index('Cat').stack().reset_index().rename(columns={'level_1':'Label', 0:'Value'})

  Cat Label  Value
0   A   L_1      1
1   A   L_2      2
2   A   L_3      3
3   B   L_1      4
4   B   L_2      5
5   B   L_3      6
6   C   L_1      7
7   C   L_2      8
8   C   L_3      9

Answer 2

One option where you do not need to convert to categorical dtype, and maintain the order is with pivot_longer from pyjanitor

# pip install pyjanitor
import pandas as pd
import janitor

df.pivot_longer('Cat', sort_by_appearance=True)

  Cat variable  value
0   A      B_1      1
1   A      A_2      2
2   A      C_3      3
3   B      B_1      4
4   B      A_2      5
5   B      C_3      6
6   C      B_1      7
7   C      A_2      8
8   C      C_3      9