Pandas reshaping and stacking dataframe

Question

I have an excel sheet in this format:

Source Hour Min1  Min2  Min3
online 0    0     0     0
online 1    1     2     0
online 2    3     4     5

How do I use pandas to transform it to this format?

Hour 0                    1                    2
     Min1   Min2   Min3   Min1   Min2   Min3   Min1   Min2   Min3
     0      0      0      1      2      0      3      4      5

I've tried the following:

df= df.set_index(["Source", "Hour"])
stacked = df.stack()

but I got this which is almost what I need but it essentially needs to be rotated

Source  Hour
online  0     Min1     0
              Min2     0
              Min3     0
        1     Min1     1
              Min2     2
              Min3     0
        2     Min1     3
              Min2     4
              Min3     5

Answer 1

I think you are looking for unstack instead:

out = df.set_index(['Source','Hour']).unstack('Hour')

Or similarly, pivot:

out = df.pivot('Source', 'Hour')

Output

          Min1       Min2       Min3      
Hour      0  1  2    0  1  2    0  1  2
Source                                 
online    0  1  3    0  2  4    0  0  5

To get the correct ordering as the expected output, we can do a swaplevel and sort_index:

out.swaplevel(0,1, axis=1).sort_index(axis=1)

Output:

Hour      0              1              2          
       Min1 Min2 Min3 Min1 Min2 Min3 Min1 Min2 Min3
Source                                             
online    0    0    0    1    2    0    3    4    5

Answer 2

Just do T, notice I will recommend keep the Source as first level in the column

out = stacked.to_frame(0).T