How to reshape dataframe in order to get the desired output in python?

Question

I have a dataframe with this format.

I have this dataframe:

   id    2005-01-07   2008-01-07    ...
0  1Y           1.0          1.6
1  5Y           1.0          1.7
2  6Y           6.0          1.0
3  10Y          2.0          7.1
4  30Y          5.5          8.6

And I would like to convert it in:

   id           Date     number
0  1Y     2005-01-07        1.0
1  1Y     2008-01-07        1.6
2  5Y     2005-01-07        1.0
3  5Y     2008-01-07        1.7
4  6Y     2005-01-07        6.0
5  6Y     2008-01-07        1.0
6  10Y    2005-01-07        2.0
7  10Y    2008-01-07        7.1
8  30Y    2005-01-07        5.5
9  30Y    2008-01-07        8.6
...

Is it possible to achieve this output in python? I tried to use transpose() and replace but can't reach the desired output

Answer by @mechanical_meat:

df = pd.melt(df, id_vars=['id'], var_name='Date', value_name='number')

@Datanovice exactly, that was the answer "mechanical_meat" gave. And that function is not addressed in https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe — eduardo2111, May 20 '20 at 23:02
@mechanical_meat: it would help in your answer to explain why this particular case you use `pd.melt` rather than `pd.pivot` — smci, May 21 '20 at 00:00

score 3 · Accepted Answer · edited Jul 14 '20 at 19:53

Using pd.melt we can go from wide- to long-form in one easy step:

df = pd.melt(df, id_vars=['id'], var_name='Date', value_name='number')

The reason .melt() is of particular use here is that you're unpivoting instead of pivoting:

In other words, the remaining columns not identified in id_vars keyword argument are then unpivoted into rows.

If you ever need to go back to the original format you can then use .pivot():

df.pivot(values='number',index='id',columns='Date')

How to reshape dataframe in order to get the desired output in python?

1 Answers1