Change column to multi-index by using one column as a new level

Question

I've got a DataFrame:

df = pd.DataFrame.from_dict({'Close': {1: 14.03, 3: 14.02, 0: 79.88, 2: 80.31},
 'High': {1: 14.3, 3: 14.33, 0: 80.22, 2: 81.19},
 'Low': {1: 14.03, 3: 13.99, 0: 79.39, 2: 80.25},
 'Open': {1: 14.18, 3: 14.25, 0: 79.79, 2: 80.97},
 'Volume': {1: 1656782.0, 3: 2249159.0, 0: 1416232.0, 2: 1622784.0},
 'Value': {1: 23244651.46, 3: 31533209.18, 0: 113128612.16, 2: 130325783.04},
 'Code': {1: 'A2M', 3: 'A2M', 0: 'CBA', 2: 'CBA'}})
df.index = [0,1,0,1]

    Close   High    Low     Open    Volume      Value           Code
0   14.03   14.30   14.03   14.18   1656782.0   2.324465e+07    A2M
1   14.02   14.33   13.99   14.25   2249159.0   3.153321e+07    A2M
0   79.88   80.22   79.39   79.79   1416232.0   1.131286e+08    CBA
1   80.31   81.19   80.25   80.97   1622784.0   1.303258e+08    CBA

How Can I change the column as a multi index with the values from Code column as the first level and the current column as the second level?

        CBA                                                         A2M
        Close   High    Low     Open    Volume      Value           Close   High    Low     Open    Volume      Value                                           
0       79.88   80.22   79.39   79.79   1416232.0   1.131286e+08    14.02   14.33   13.99   14.25   2249159.0   31533209.18
1       80.31   81.19   80.25   80.97   1622784.0   1.303258e+08    14.03   14.30   14.03   14.18   1656782.0   23244651.46

Thanks.

jezrael · Accepted Answer · 2020-01-22 12:43:11Z

4

Use DataFrame.set_index with DataFrame.unstack, then DataFrame.swaplevel with DataFrame.sort_index:

df = df.set_index('Code', append=True).unstack().swaplevel(1, 0, axis=1).sort_index(axis=1)
print (df)
Code    A2M                                                 CBA                \
      Close   High    Low   Open        Value     Volume  Close   High    Low   
0     14.03  14.30  14.03  14.18  23244651.46  1656782.0  79.88  80.22  79.39   
1     14.02  14.33  13.99  14.25  31533209.18  2249159.0  80.31  81.19  80.25   

Code                                  
       Open         Value     Volume  
0     79.79  1.131286e+08  1416232.0  
1     80.97  1.303258e+08  1622784.0

answered Jan 22, 2020 at 12:43

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ansev · Accepted Answer · 2020-01-22 12:59:14Z

1

We can also use your index to use GroupBy.first:

df.groupby(['Code',df.index]).first().unstack('Code').swaplevel(axis=1).sort_index(axis=1)

Code    A2M                                                 CBA                \
      Close   High    Low   Open        Value     Volume  Close   High    Low   
0     14.03  14.30  14.03  14.18  23244651.46  1656782.0  79.88  80.22  79.39   
1     14.02  14.33  13.99  14.25  31533209.18  2249159.0  80.31  81.19  80.25   

Code                                  
       Open         Value     Volume  
0     79.79  1.131286e+08  1416232.0  
1     80.97  1.303258e+08  1622784.0

edited Jan 22, 2020 at 12:59

answered Jan 22, 2020 at 12:53

ansev

31k5 gold badges21 silver badges33 bronze badges

Collectives™ on Stack Overflow

Change column to multi-index by using one column as a new level

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related