Reshape data in Pandas - Python 3

Question

I'm trying to reshape my data from wide to long but I need to consider both vertical indexes and horizontal columns and it's not really working.

My current table looks like this:

  Rate      FY2014   FY 2015   FY 2016   FY 2017 
   1.1       15.87    45.85      72.65     78.54
   2.1       78.54    78.45      95.87     78.94
   3.1       45.87    64.52      37.82     97.57

And I want to reshape this data so that the numbers under each FY can have it's own column. For example, I want something like this as my final table.

  Values     Rate    Year
   15.87     1.1     FY2014
   78.54     2.1     FY2014
   45.87     3.1     FY2014
   45.85     1.1     FY2015
   78.45     2.1     FY2015
   64.52     3.1     FY2015
   72.65     1.1     FY2016
   95.87     2.1     FY2016
   37.82     3.1     FY2016

I'm new to Python and I've tried something like pd.reshape or Transpose but I couldn't get the result I wanted. Could anyone please help me with this?

Answer 1

Use df.melt:

In [1806]: df = df.melt(id_vars='Rate', var_name='Year', value_name='Values')

In [1807]: df
Out[1807]: 
    Rate    Year  Values
0    1.1  FY2014   15.87
1    2.1  FY2014   78.54
2    3.1  FY2014   45.87
3    1.1  FY2015   45.85
4    2.1  FY2015   78.45
5    3.1  FY2015   64.52
6    1.1  FY2016   72.65
7    2.1  FY2016   95.87
8    3.1  FY2016   37.82
9    1.1  FY2017   78.54
10   2.1  FY2017   78.94
11   3.1  FY2017   97.57

OR:

Use df.set_index with stack():

In [1814]: df.set_index('Rate').stack().reset_index(name='Values').rename(columns={'level_1': 'Year'})
Out[1814]: 
    Rate    Year  Values
0    1.1  FY2014   15.87
1    1.1  FY2015   45.85
2    1.1  FY2016   72.65
3    1.1  FY2017   78.54
4    2.1  FY2014   78.54
5    2.1  FY2015   78.45
6    2.1  FY2016   95.87
7    2.1  FY2017   78.94
8    3.1  FY2014   45.87
9    3.1  FY2015   64.52
10   3.1  FY2016   37.82
11   3.1  FY2017   97.57