how to transpose a dataset, keeping first row as ID python-CodePudding

I have this dataset in python:

statinfo	2021M08	2021M07	2021M06	2021M05	2021M04
AVG,NAC,ALL	121.55	122.32	122.83	123.01	123.1
AVG,NAC,AUD	1.6118	1.5926	1.5761	1.5653	1.5544

And I need to transpose the columns headers as values except the first row (want to keep this as column)

Output expected:

statinfo	Date	Rate
AVG,NAC,ALL	2021M08	121.55
AVG,NAC,ALL	2021M07	122.32
AVG,NAC,ALL	2021M06	122.83
AVG,NAC,ALL	2021M05	123.01
AVG,NAC,ALL	2021M04	123.1
AVG,NAC,AUD	2021M08	1.6118
AVG,NAC,AUD	2021M07	1.5926
AVG,NAC,AUD	2021M06	1.5761
AVG,NAC,AUD	2021M05	1.5653
AVG,NAC,AUD	2021M04	1.5544

I have tried df.transpose() but it transposes all the dataset, I am a new user using python, any ideas how to do this.

CodePudding user response：

Use melt

>>> df.melt(id_vars='statinfo', var_name='Date', value_name='Rate')
      .sort_values('statinfo')

      statinfo     Date      Rate
0  AVG,NAC,ALL  2021M08  121.5500
2  AVG,NAC,ALL  2021M07  122.3200
4  AVG,NAC,ALL  2021M06  122.8300
6  AVG,NAC,ALL  2021M05  123.0100
8  AVG,NAC,ALL  2021M04  123.1000
1  AVG,NAC,AUD  2021M08    1.6118
3  AVG,NAC,AUD  2021M07    1.5926
5  AVG,NAC,AUD  2021M06    1.5761
7  AVG,NAC,AUD  2021M05    1.5653
9  AVG,NAC,AUD  2021M04    1.5544

CodePudding user response：

Try with stack:

>>> df.set_index("statinfo") \
      .stack() \
      .reset_index() \
      .rename(columns={"level_1": "Date", 0: "Rate"})

      statinfo     Date      Rate
0  AVG,NAC,ALL  2021M08  121.5500
1  AVG,NAC,ALL  2021M07  122.3200
2  AVG,NAC,ALL  2021M06  122.8300
3  AVG,NAC,ALL  2021M05  123.0100
4  AVG,NAC,ALL  2021M04  123.1000
5  AVG,NAC,AUD  2021M08    1.6118
6  AVG,NAC,AUD  2021M07    1.5926
7  AVG,NAC,AUD  2021M06    1.5761
8  AVG,NAC,AUD  2021M05    1.5653
9  AVG,NAC,AUD  2021M04    1.5544