How to append column names into row and leave the column's value next to it?

Question

I'd like to append my dataframe with column names (which will be a date), and to leave its row value next to it. I started with this kind of dataframe:

Attribute | State | Metric | 2020-05-01 | 2020-05-02 | 2020-05-03 | ...
Attribute1| A     | 12     | 360        |  550       | 650        | ....
Attribute1| A     | 14     | 200        |  300       | 900        | ....
Attribute1| B     | 18     | 400        |  700       | 100        | ....
Attribute2| B     | 20     | 120        |  900       | 120        | ....

So I grouped by Attribute and State, made the sum of the rest and received:

Attribute | State | Metric | 2020-05-01 | 2020-05-02 | 2020-05-03 | ...
Attribute1| A     | sum    | sum        |  sum       | sum        | ....
Attribute1| B     | sum    | sum        |  sum       | sum        | ....
Attribute2| B     | sum    | sum        |  sum       | sum        | ....

And right now I need to change this table to fit pivot table in Excel, so I need to put those columns as rows, while maintaining the value. How to do this?

Desired output:

Attribute | State | Metric | Date       | ValueForDate 
Attribute1| A     | sum    | 2020-05-01 |  #       
Attribute1| A     | sum    | 2020-05-02 |  #       
Attribute1| A     | sum    | 2020-05-03 |  #
Attribute1| B     | sum    | 2020-05-01 |  #
.
.
.

Does anyone have a clue how to achieve this output? Thanks in advance and let me know in case my question is misunderstandable.

Have a look at `melt`, e.g. `df.melt(['Attribute','State','Metric'])`? — Quang Hoang, Aug 18 '20 at 13:31
@QuangHoang thanks a lot, a little bit more complicated in my code, but yup! — drk, Aug 18 '20 at 14:07

score 0 · Accepted Answer · answered Aug 18 '20 at 13:45

You can do this using melt:

df = df.groupby(by=['Attribute', 'State'], as_index=False).sum()
df = pd.melt(df, id_vars=['Attribute', 'State', 'Metric'], var_name='Date')
print(df)

    Attribute State  Metric        Date  value
0  Attribute1     A      26  2020-05-01    560
1  Attribute1     B      18  2020-05-01    400
2  Attribute2     B      20  2020-05-01    120
3  Attribute1     A      26  2020-05-02    850
4  Attribute1     B      18  2020-05-02    700
5  Attribute2     B      20  2020-05-02    900
6  Attribute1     A      26  2020-05-03   1550
7  Attribute1     B      18  2020-05-03    100
8  Attribute2     B      20  2020-05-03    120

How to append column names into row and leave the column's value next to it?

1 Answers1