Convert one of the column index to rows-CodePudding

I have the following pandas DataFrame:-

                                      Adj Close                   Close                    High                     Low                    Open           Volume
                                   AA      ABUS            AA      ABUS     AA             ABUS            AA      ABUS            AA      ABUS          AA ABUS
    Date
    2001-09-24 00:00:00     55.118465       NaN     72.570602       NaN     72.570602       NaN     68.004898       NaN     68.004898       NaN     2185435  NaN
    2001-09-25 00:00:00     54.826443       NaN     72.186119       NaN     73.531799       NaN     69.927299       NaN     72.089996       NaN     1541074  NaN
    2001-09-26 00:00:00     53.001324       NaN     69.783119       NaN     73.195381       NaN     67.884750       NaN     72.786873       NaN     2157803  NaN
    2001-09-27 00:00:00     55.209713       NaN     72.690750       NaN     72.690750       NaN     68.990128       NaN     70.287750       NaN     1775614  NaN
    2001-09-28 00:00:00     56.596817       NaN     74.517029       NaN     74.613152       NaN     70.167603       NaN     72.690750       NaN     1613774  NaN
    2001-10-01 00:00:00     56.450794       NaN     74.324791       NaN     74.517029       NaN     72.089996       NaN     74.517029       NaN     1260341  NaN
    2001-10-02 00:00:00     58.312424       NaN     76.775848       NaN     76.775848       NaN     73.171349       NaN     74.901512       NaN     1545360  NaN
    2001-10-03 00:00:00     58.567932       NaN     77.112267       NaN     77.809143       NaN     74.012398       NaN     75.213898       NaN     2021681  NaN
    2001-10-04 00:00:00     56.250027       NaN     74.060463       NaN     79.058701       NaN     73.627922       NaN     79.058701       NaN     2036205  NaN
    2001-10-05 00:00:00     57.564083       NaN     75.790619       NaN     76.271217       NaN     74.132553       NaN     74.733299       NaN     1206325  NaN

And I want it to convert the index which has the stock names (AA and ABUS) to rows. Hence, I will get the following DataFrame:-

                         Stock      Adj Close           Close            High             Low            Open        Volume
    Date                                                                                            
    2001-09-24 00:00:00     AA      55.118465       72.570602       72.570602       68.004898       68.004898       2185435
    2001-09-25 00:00:00     AA      54.826443       72.186119       73.531799       69.927299       72.089996       1541074
    2001-09-26 00:00:00     AA      53.001324       69.783119       73.195381       67.884750       72.786873       2157803
    2001-09-27 00:00:00     AA      55.209713       72.690750       72.690750       68.990128       70.287750       1775614
    2001-09-28 00:00:00     AA      56.596817       74.517029       74.613152       70.167603       72.690750       1613774
    2001-10-01 00:00:00     AA      56.450794       74.324791       74.517029       72.089996       74.517029       1260341
    2001-10-02 00:00:00     AA      58.312424       76.775848       76.775848       73.171349       74.901512       1545360
    2001-10-03 00:00:00     AA      58.567932       77.112267       77.809143       74.012398       75.213898       2021681
    2001-10-04 00:00:00     AA      56.250027       74.060463       79.058701       73.627922       79.058701       2036205
    2001-10-05 00:00:00     AA      57.564083       75.790619       76.271217       74.132553       74.733299       1206325
    2001-09-24 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-09-25 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-09-26 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-09-27 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-09-28 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-10-01 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-10-02 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-10-03 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-10-04 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN
    2001-10-05 00:00:00     ABUS          NaN             NaN             NaN             NaN             NaN           NaN

How can I do this?

Thanks in advance.

Below I am putting a dictionary to create this DataFrame

{('Adj Close', 'AA'): {Timestamp('2001-09-24 00:00:00'): 55.118465423583984,
  Timestamp('2001-09-25 00:00:00'): 54.82644271850586,
  Timestamp('2001-09-26 00:00:00'): 53.00132369995117,
  Timestamp('2001-09-27 00:00:00'): 55.209712982177734,
  Timestamp('2001-09-28 00:00:00'): 56.59681701660156,
  Timestamp('2001-10-01 00:00:00'): 56.4507942199707,
  Timestamp('2001-10-02 00:00:00'): 58.31242370605469,
  Timestamp('2001-10-03 00:00:00'): 58.56793212890625,
  Timestamp('2001-10-04 00:00:00'): 56.25002670288086,
  Timestamp('2001-10-05 00:00:00'): 57.564083099365234},
 ('Adj Close', 'ABUS'): {Timestamp('2001-09-24 00:00:00'): nan,
  Timestamp('2001-09-25 00:00:00'): nan,
  Timestamp('2001-09-26 00:00:00'): nan,
  Timestamp('2001-09-27 00:00:00'): nan,
  Timestamp('2001-09-28 00:00:00'): nan,
  Timestamp('2001-10-01 00:00:00'): nan,
  Timestamp('2001-10-02 00:00:00'): nan,
  Timestamp('2001-10-03 00:00:00'): nan,
  Timestamp('2001-10-04 00:00:00'): nan,
  Timestamp('2001-10-05 00:00:00'): nan},
 ('Close', 'AA'): {Timestamp('2001-09-24 00:00:00'): 72.57060241699219,
  Timestamp('2001-09-25 00:00:00'): 72.18611907958984,
  Timestamp('2001-09-26 00:00:00'): 69.78311920166016,
  Timestamp('2001-09-27 00:00:00'): 72.69075012207031,
  Timestamp('2001-09-28 00:00:00'): 74.51702880859375,
  Timestamp('2001-10-01 00:00:00'): 74.32479095458984,
  Timestamp('2001-10-02 00:00:00'): 76.77584838867188,
  Timestamp('2001-10-03 00:00:00'): 77.11226654052734,
  Timestamp('2001-10-04 00:00:00'): 74.06046295166016,
  Timestamp('2001-10-05 00:00:00'): 75.79061889648438},
 ('Close', 'ABUS'): {Timestamp('2001-09-24 00:00:00'): nan,
  Timestamp('2001-09-25 00:00:00'): nan,
  Timestamp('2001-09-26 00:00:00'): nan,
  Timestamp('2001-09-27 00:00:00'): nan,
  Timestamp('2001-09-28 00:00:00'): nan,
  Timestamp('2001-10-01 00:00:00'): nan,
  Timestamp('2001-10-02 00:00:00'): nan,
  Timestamp('2001-10-03 00:00:00'): nan,
  Timestamp('2001-10-04 00:00:00'): nan,
  Timestamp('2001-10-05 00:00:00'): nan},
 ('High', 'AA'): {Timestamp('2001-09-24 00:00:00'): 72.57060241699219,
  Timestamp('2001-09-25 00:00:00'): 73.53179931640625,
  Timestamp('2001-09-26 00:00:00'): 73.19538116455078,
  Timestamp('2001-09-27 00:00:00'): 72.69075012207031,
  Timestamp('2001-09-28 00:00:00'): 74.61315155029297,
  Timestamp('2001-10-01 00:00:00'): 74.51702880859375,
  Timestamp('2001-10-02 00:00:00'): 76.77584838867188,
  Timestamp('2001-10-03 00:00:00'): 77.80914306640625,
  Timestamp('2001-10-04 00:00:00'): 79.05870056152344,
  Timestamp('2001-10-05 00:00:00'): 76.2712173461914},
 ('High', 'ABUS'): {Timestamp('2001-09-24 00:00:00'): nan,
  Timestamp('2001-09-25 00:00:00'): nan,
  Timestamp('2001-09-26 00:00:00'): nan,
  Timestamp('2001-09-27 00:00:00'): nan,
  Timestamp('2001-09-28 00:00:00'): nan,
  Timestamp('2001-10-01 00:00:00'): nan,
  Timestamp('2001-10-02 00:00:00'): nan,
  Timestamp('2001-10-03 00:00:00'): nan,
  Timestamp('2001-10-04 00:00:00'): nan,
  Timestamp('2001-10-05 00:00:00'): nan},
 ('Low', 'AA'): {Timestamp('2001-09-24 00:00:00'): 68.00489807128906,
  Timestamp('2001-09-25 00:00:00'): 69.92729949951172,
  Timestamp('2001-09-26 00:00:00'): 67.88475036621094,
  Timestamp('2001-09-27 00:00:00'): 68.99012756347656,
  Timestamp('2001-09-28 00:00:00'): 70.1676025390625,
  Timestamp('2001-10-01 00:00:00'): 72.08999633789062,
  Timestamp('2001-10-02 00:00:00'): 73.17134857177734,
  Timestamp('2001-10-03 00:00:00'): 74.01239776611328,
  Timestamp('2001-10-04 00:00:00'): 73.62792205810547,
  Timestamp('2001-10-05 00:00:00'): 74.13255310058594},
 ('Low', 'ABUS'): {Timestamp('2001-09-24 00:00:00'): nan,
  Timestamp('2001-09-25 00:00:00'): nan,
  Timestamp('2001-09-26 00:00:00'): nan,
  Timestamp('2001-09-27 00:00:00'): nan,
  Timestamp('2001-09-28 00:00:00'): nan,
  Timestamp('2001-10-01 00:00:00'): nan,
  Timestamp('2001-10-02 00:00:00'): nan,
  Timestamp('2001-10-03 00:00:00'): nan,
  Timestamp('2001-10-04 00:00:00'): nan,
  Timestamp('2001-10-05 00:00:00'): nan},
 ('Open', 'AA'): {Timestamp('2001-09-24 00:00:00'): 68.00489807128906,
  Timestamp('2001-09-25 00:00:00'): 72.08999633789062,
  Timestamp('2001-09-26 00:00:00'): 72.78687286376953,
  Timestamp('2001-09-27 00:00:00'): 70.28775024414062,
  Timestamp('2001-09-28 00:00:00'): 72.69075012207031,
  Timestamp('2001-10-01 00:00:00'): 74.51702880859375,
  Timestamp('2001-10-02 00:00:00'): 74.9015121459961,
  Timestamp('2001-10-03 00:00:00'): 75.21389770507812,
  Timestamp('2001-10-04 00:00:00'): 79.05870056152344,
  Timestamp('2001-10-05 00:00:00'): 74.7332992553711},
 ('Open', 'ABUS'): {Timestamp('2001-09-24 00:00:00'): nan,
  Timestamp('2001-09-25 00:00:00'): nan,
  Timestamp('2001-09-26 00:00:00'): nan,
  Timestamp('2001-09-27 00:00:00'): nan,
  Timestamp('2001-09-28 00:00:00'): nan,
  Timestamp('2001-10-01 00:00:00'): nan,
  Timestamp('2001-10-02 00:00:00'): nan,
  Timestamp('2001-10-03 00:00:00'): nan,
  Timestamp('2001-10-04 00:00:00'): nan,
  Timestamp('2001-10-05 00:00:00'): nan},
 ('Volume', 'AA'): {Timestamp('2001-09-24 00:00:00'): 2185435,
  Timestamp('2001-09-25 00:00:00'): 1541074,
  Timestamp('2001-09-26 00:00:00'): 2157803,
  Timestamp('2001-09-27 00:00:00'): 1775614,
  Timestamp('2001-09-28 00:00:00'): 1613774,
  Timestamp('2001-10-01 00:00:00'): 1260341,
  Timestamp('2001-10-02 00:00:00'): 1545360,
  Timestamp('2001-10-03 00:00:00'): 2021681,
  Timestamp('2001-10-04 00:00:00'): 2036205,
  Timestamp('2001-10-05 00:00:00'): 1206325},
 ('Volume', 'ABUS'): {Timestamp('2001-09-24 00:00:00'): nan,
  Timestamp('2001-09-25 00:00:00'): nan,
  Timestamp('2001-09-26 00:00:00'): nan,
  Timestamp('2001-09-27 00:00:00'): nan,
  Timestamp('2001-09-28 00:00:00'): nan,
  Timestamp('2001-10-01 00:00:00'): nan,
  Timestamp('2001-10-02 00:00:00'): nan,
  Timestamp('2001-10-03 00:00:00'): nan,
  Timestamp('2001-10-04 00:00:00'): nan,
  Timestamp('2001-10-05 00:00:00'): nan}}

CodePudding user response：

Use DataFrame.stack for reshape, then DataFrame.rename_axis for index names, so possible create column from second level of MultiIndex by DataFrame.reset_index:

df = df.stack().rename_axis(['Date','Stock']).reset_index(level=1)
print (df)
           Stock  Adj Close      Close       High        Low       Open  \
Date                                                                      
2001-09-24    AA  55.118465  72.570602  72.570602  68.004898  68.004898   
2001-09-25    AA  54.826443  72.186119  73.531799  69.927299  72.089996   
2001-09-26    AA  53.001324  69.783119  73.195381  67.884750  72.786873   
2001-09-27    AA  55.209713  72.690750  72.690750  68.990128  70.287750   
2001-09-28    AA  56.596817  74.517029  74.613152  70.167603  72.690750   
2001-10-01    AA  56.450794  74.324791  74.517029  72.089996  74.517029   
2001-10-02    AA  58.312424  76.775848  76.775848  73.171349  74.901512   
2001-10-03    AA  58.567932  77.112267  77.809143  74.012398  75.213898   
2001-10-04    AA  56.250027  74.060463  79.058701  73.627922  79.058701   
2001-10-05    AA  57.564083  75.790619  76.271217  74.132553  74.733299   

               Volume  
Date                   
2001-09-24  2185435.0  
2001-09-25  1541074.0  
2001-09-26  2157803.0  
2001-09-27  1775614.0  
2001-09-28  1613774.0  
2001-10-01  1260341.0  
2001-10-02  1545360.0  
2001-10-03  2021681.0  
2001-10-04  2036205.0  
2001-10-05  1206325.0