Melting in pandas loses index

Question

I have the following dataframe:

        Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
Year                                                
2010    9.8 9.8 9.9 9.9 9.6 9.4 9.4 9.5 9.5 9.4 9.8 9.3
2011    9.1 9.0 9.0 9.1 9.0 9.1 9.0 9.0 9.0 8.8 8.6 8.5
2012    8.3 8.3 8.2 8.2 8.2 8.2 8.2 8.1 7.8 7.8 7.7 7.9

I want to achieve the following result:

      Month Values
Year
2010  Jan    9.8
2010  Feb    9.8
2010  Mar    9.9
etc......

I tried the method proposed here: pandas, melt, unmelt preserve index:

df = pd.melt(df.reset_index(), id_vars='index',value_vars=months)

"Months" is a list I created from by getting a list of column names for the months:

months = df.columns.values.tolist()[1:]

However, the result was:

       index variable  value
   0    NaN   Jan      9.8
   1    NaN   Jan      9.1
   2    NaN   Jan      8.3
   3    NaN   Jan      8.0
   4    NaN   Jan      6.6

So, I want to retain the year value as the index. How can I do this? Thanks.

In pandas version >=1.1, you can use the `ignore_index` parameter :``df.melt(ignore_index=False)`` — sammywemmy, Nov 22 '20 at 00:33

score 1 · Answer 1 · answered May 28 '20 at 16:45

1

If you have a good index like that, you can stack:

df.rename_axis('Month', axis='columns').stack().to_frame('Values')

The rename_axis method is just some sugar to get the index labels nice.

answered May 28 '20 at 16:45

Paul H

65,268
20
159
136

score 0 · Accepted Answer · answered May 28 '20 at 16:41

With melt:

df.reset_index().melt('Year',var_name='Month',value_name='Values').set_index('Year')

    Month  Values
Year              
2010   Jan     9.8
2011   Jan     9.1
2012   Jan     8.3
2010   Feb     9.8
2011   Feb     9.0
.........
.......

With stack():

df.rename_axis('Month',axis=1).stack().reset_index(1,name='Values')

      Month  Values
Year              
2010   Jan     9.8
2010   Feb     9.8
2010   Mar     9.9
2010   Apr     9.9
2010   May     9.6
2010   Jun     9.4
.......
......

Melting in pandas loses index

2 Answers2