Pandas pivot? pivot_table? melt? stack or unstack?

user_3486773 0
Viewed 780

I have a dataframe that looks like this:

       id    Revenue  Cost  qty  time
  0    A      400       50    2     1
  1    A      900      200    8     2
  2    A      800      100    8     3
  3    B      300       20    1     1
  4    B      600      150    4     2
  5    B      650      155    4     3

And I'm trying to get to this:

       id    Type       1      2      3     
  0    A    Revenue    400    900    800
  1    A    Cost        50    200    100     
  2    A    qty          2      8      8   
  3    B    Revenue    300    600    650
  4    B    Cost        20    150    155
  5    B    qty          1      4      4

Where time will always just be repeated 1-3, so I need to transpose or pivot on just time, with the column for 1-3

Here is what I have tried so far:

  pd.pivot_table(df, values = ['Revenue', 'qty', 'Cost'] , index=['id'], columns='time').reset_index()

But that just makes one really long table that puts everything side by side vs stacked like this:

   Revenue                   qty                Cost
       1     2     3         1     2     3         1    2     3

In that situation I would need to convert the Revenue, qty and Cost to a row and just use the 1, 2, 3 as the column names. So the ID would be duplicated for each 'type' but list it out based on time 1-3.

melt pandas pivot
2 Answers

We can still do unstack and stack

df.set_index(['id','time']).stack().unstack(level=1).reset_index()
Out[24]: 
time id  level_1    1    2    3
0     A  Revenue  400  900  800
1     A     Cost   50  200  100
2     A      qty    2    8    8
3     B  Revenue  300  600  650
4     B     Cost   20  150  155
5     B      qty    1    4    4
user_79645270

An alternative, using melt and pivot on Pandas 1.1.0 :

(df
 .melt(["id", "time"])
 .pivot(["id", "variable"], "time", "value")
 .reset_index()
 .rename_axis(columns=None)
 )

    id  variable    1   2   3
0   A   Cost       50   200 100
1   A   Revenue    400  900 800
2   A   qty        2    8   8
3   B   Cost       20   150 155
4   B   Revenue   300   600 650
5   B   qty        1    4   4
user_71757130
Related