Extract rows as column from pandas data frame after melt

Question

I'm working with pandas and I have this table:

ID 1-May-2016 1-Jun-2016 20-Jul-2016 Class
1       0.2       0.52      0.1        H
2       0.525     0.20      0.01       L
...

and I'd like to obtain this table:

ID  Date        Value  Class
1   1-May-2016    0.2    H
1   1-Jun-2016    0.52   H
...
2   1-May-2016    0.525  L
...

I tried:

pandas.melt(df,id_vars["ID"], var_name = "Class")

and I obtain almost what I'd like:

ID  Class        Value  
1   1-May-2016    0.2   
1   1-Jun-2016    0.52   
...
1   Class         L
2   Class         H

except that the bottom part of the table contains the information that should be considered as an "extra" column. Is this the right process/approach? If yes, how can I "shift" the bottom part of the table to be a column that contains the class of my samples?

Answer 1

You need add Class to id_vars in melt:

print (pd.melt(df,id_vars=["ID", 'Class'], var_name = "Date", value_name='Vals'))
   ID Class         Date   Vals
0   1     H   1-May-2016  0.200
1   2     L   1-May-2016  0.525
2   1     H   1-Jun-2016  0.520
3   2     L   1-Jun-2016  0.200
4   1     H  20-Jul-2016  0.100
5   2     L  20-Jul-2016  0.010

Then use sort_values if necessary:

print (pd.melt(df,id_vars=["ID", 'Class'], var_name = "Date", value_name='Vals')
         .sort_values(['ID', 'Class']))

   ID Class         Date   Vals
0   1     H   1-May-2016  0.200
2   1     H   1-Jun-2016  0.520
4   1     H  20-Jul-2016  0.100
1   2     L   1-May-2016  0.525
3   2     L   1-Jun-2016  0.200
5   2     L  20-Jul-2016  0.010

Another possible solution with stack:

print (df.set_index(["ID", 'Class'])
         .stack()
         .reset_index(name='Vals')
         .rename(columns={'level_2':'Date'}))

   ID Class         Date   Vals
0   1     H   1-May-2016  0.200
1   1     H   1-Jun-2016  0.520
2   1     H  20-Jul-2016  0.100
3   2     L   1-May-2016  0.525
4   2     L   1-Jun-2016  0.200
5   2     L  20-Jul-2016  0.010