How do I transform my dataframe to this form?

Question

Suppose I have a dataframe that looks like the following:

df = pd.DataFrame(
     {    'id': [1,2,3,4,5],
        'actual':[10,20,30,15,25],
        'predicted':[8.9, 17.2, 25.7, 12.3, 21.1]
     }
)

df
    id  actual  predicted
0   1    10      8.9
1   2    20      17.2
2   3    30      25.7
3   4    15      12.3
4   5    25      21.1

But I want to transform it to this:

id    value    status  
1       10     actual
1      8.9     predicted
2       20     actual
2     17.2     predicted
3       30     actual
3     25.7     predicted
4       15     actual
4     12.3     predicted
5       25     actual
5     21.1     predicted

Could that be done?

Naveed · Answer 1 · 2022-09-28T20:28:46.820

use melt, and then sort the result

df.melt(id_vars='id', var_name='status', value_name='value').sort_values(['id','status'])

    id  status     value
0   1   actual      10.0
5   1   predicted    8.9
1   2   actual      20.0
6   2   predicted   17.2
2   3   actual      30.0
7   3   predicted   25.7
3   4   actual      15.0
8   4   predicted   12.3
4   5   actual      25.0
9   5   predicted   21.1

alternate solution setindex and stack

df.set_index('id').stack().reset_index().rename(columns={0:'value'})

    id  level_1      value
0   1   actual       10.0
1   1   predicted    8.9
2   2   actual      20.0
3   2   predicted   17.2
4   3   actual      30.0
5   3   predicted   25.7
6   4   actual      15.0
7   4   predicted   12.3
8   5   actual      25.0
9   5   predicted   21.1

How do I transform my dataframe to this form?

1 Answers1