Search code examples
pythonpandaspivot-tabletranspose

How to reindex and "transpose" dataframe?


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?


Solution

  • 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