Tranform multindex-key to column

Question

I've trying make the key of the index become a column for a while. I've tried df.to_frame() and other variations without success.

For reproducibility:

nasdaq_list = ["CBAK", "CBAN", "CBAY", "CBDE", "CBF", "CBFV", "CBIN", "CBLI", "CBMG", "CBMX", "CBNJ", "CBNK", "CBOE", "CBPO", "CBRL", "CBRX", "CBSH", "CBSHP", "CBST", "CBSTZ", "CCBG", "CCCL", "CCCR", "CCIH", "CCLP", "CCMP", "CCNE", "CCOI", "CCRN", "CCUR", "CCXI", "CDC", "CDK", "CDNA", "CDNS", "CDTI", "CDW", "CDXS", "CDZI", "CECE", "CECO", "CELG", "CELGZ", "CEMI", "CEMP", "CENT", "CENTA", "CENX", "CERE", "CERN", "CERS", "CERU", "CETV", "CEVA", "CFA", "CFBK", "CFFI", "CFFN", "CFGE", "CFNB", "CFNL", "CFO", "CFRX", "CFRXW", "CFRXZ", "CG", "CGEN", "CGIX", "CGNX", "CGO", "CHCI", "CHCO", "CHDN", "CHEF", "CHEV", "CHFC", "CHFN", "CHI", "CHKE", "CHKP", "CHLN", "CHMG", "CHNR", "CHOP", "CHRS", "CHRW", "CHSCM", "CHSCN", "CHSCO", "CHSCP", "CHTR", "CHUY", "CHW", "CHXF", "CHY", "CHYR", "CIDM", "CIFC", "CIMT", "CINF"]
start = date(2017, 10, 1)
end = date(2020, 6, 25)

import yfinance as yf
df = yf.download(nasdaq_list, start, end)

df.tail()
Out[84]: 
                Adj Close                          Volume                            
                CBAK   CBAN  CBAY CBDE CBF  ...    CHYR     CIDM CIFC CIMT     CINF
Date                                        ...                                   
2020-06-18       NaN  11.76  3.66  NaN NaN  ...    NaN  1518300  NaN  NaN  1166600
2020-06-19       NaN  11.85  3.94  NaN NaN  ...    NaN  4842900  NaN  NaN  6750100
2020-06-22       NaN  12.29  4.13  NaN NaN  ...    NaN  2542600  NaN  NaN  1774600
2020-06-23       NaN  12.62  3.91  NaN NaN  ...    NaN  3552000  NaN  NaN  1342100
2020-06-24       NaN  11.84  3.74  NaN NaN  ...    NaN  3288200  NaN  NaN  1531700

[5 rows x 600 columns]

While I'm looking for something like:

Date       Symbol     Adj Close                  ...    Volume                                   
2020-06-18 CBAK       NaN  11.76  3.66  NaN NaN  ...    NaN  1518300  NaN  NaN  1166600
2020-06-18 CBAN       NaN  11.85  3.94  NaN NaN  ...    NaN  4842900  NaN  NaN  6750100
2020-06-18 CBAY       NaN  12.29  4.13  NaN NaN  ...    NaN  2542600  NaN  NaN  1774600
2020-06-18 CBDE       NaN  12.62  3.91  NaN NaN  ...    NaN  3552000  NaN  NaN  1342100
2020-06-18 CBF        NaN  11.84  3.74  NaN NaN  ...    NaN  3288200  NaN  NaN  1531700

[5 rows x 600 columns]

Notice that the date keep the same because all of these symbols represent the first date.

Any help would be greatly appreciated.

Also tried df.reset_index() – Artur Dutra Jul 27 '20 at 02:03 — Artur Dutra, Jul 27 '20 at 02:03

score 1 · Accepted Answer · answered Jul 27 '20 at 02:11

Just need to alter your index first, then stack.

df.tail().reorder_levels([1,0], 1).stack(0).reset_index()

output

    Date    level_1 Adj Close   Close   High    Low Open    Volume
0   2020-06-18  CBAN    11.760000   11.760000   12.530000   11.170000   12.530000   25000.0
1   2020-06-18  CBAY    3.660000    3.660000    3.770000    3.585000    3.770000    985000.0
2   2020-06-18  CBFV    25.500000   25.500000   26.280001   24.889999   25.410000   7000.0
3   2020-06-18  CBLI    1.740000    1.740000    1.750000    1.590000    1.640000    485700.0

Tranform multindex-key to column

1 Answers1