Transforming pandas dataframe from multiple series to categorical label

Question

I have a dataframe in set up in this way:

depth	val1	val2
1	0.5	0.6
2	0.4	0.7
3	0.3	0.8

and I want to get it into this layout:

depth	y	category
1	0.5	val1
1	0.6	val2
2	0.4	val1
2	0.7	val2
3	0.3	val1
3	0.8	val2

What's the most efficient way to do this?

Answer 1

You can use pd.melt for this:

pd.melt(df, id_vars=['depth'])

Result:

depth   variable    value
1       val1        0.5
2       val1        0.4
3       val1        0.3
1       val2        0.6
2       val2        0.7
3       val2        0.8

To get to your desired output, add var_name as variable, sort your values, rename 'value'-column and reorder the columns:

pd.melt(
        df,
        id_vars=['depth'], var_name='category'
    ).sort_values(['depth', 'category']
    ).rename({'value':'y'}, axis=1
    ).iloc[:, [0,2,1]]

depth   y       category
1       0.5     val1
1       0.6     val2
2       0.4     val1
2       0.7     val2
3       0.3     val1
3       0.8     val2