How to reindex and "transpose" dataframe?-CodePudding

I have a dataframe like this:

df = pd.DataFrame({"CLASS":["A","B","C","A","B"],
                   "ID":["X","X","X","Y","Y"],
                   "TOP":[12, 17, 19, 15, 18],
                   "BOT":[16.5, 18, 21, 16, 20]})

df
Out[28]: 
  CLASS ID  TOP   BOT
0     A  X   12  16.5
1     B  X   17  18.0
2     C  X   19  21.0
3     A  Y   15  16.0
4     B  Y   18  20.0

I would like to set CLASS, TOP and BOT as indexes and then create columns based on the unique values of ID, so my new dataframe would be like this:

               |X     Y 
     A    TOP  |12    15  
          BOT  |16.5  16
     B    TOP  |17    18
          BOT  |18    20
     C    TOP  |19    
          BOT  |21

In this way, CLASS should be an index with unique values, and TOP/BOT should be also an index but repeating these strings as shown above. I got confused with pivot_table and transposing the dataframe, and couldn't solve this. Anyone could help me?

CodePudding user response：

Stack, then unstack:

df.set_index(['CLASS','ID']).stack().unstack('ID')

Ouptut:

ID            X     Y
CLASS                
A     TOP  12.0  15.0
      BOT  16.5  16.0
B     TOP  17.0  18.0
      BOT  18.0  20.0
C     TOP  19.0   NaN
      BOT  21.0   NaN

CodePudding user response：

Let us try pivot

out = df.pivot(index='ID',columns = 'CLASS').swaplevel(1,0,axis=1).T.sort_index()