Search code examples
pandasdataframehierarchymulti-indexreindex

Pandas (Python) MultiIndex from Single Index pd.DataFrame

I have a df which looks like:

enter image description here

Let the group name of the individual columns be 'Symbol', so that a list of column names 'Symbol' = ['AAPL US Equity', 'ABT US Equity', 'BDX US Equity', 'BRO US Equity'] Let 'Returns' be the floats. Let 'Dates' be a datetime index.

Question: I need the df to have MultiIndex of ['Ticker', 'Dates'] with the hierarchy of it being the order i.e. 'Ticker' --> 'Dates' --> 'Returns'

Something like:

enter image description here

It is first grouped by 'Symbol', then by 'Date'

Solution

  • Use DataFrame.unstack :

    df = df.set_index('Dates').unstack().rename_axis(['Symbol','Date']).to_frame('Close')

    Or DataFrame.melt:

    df = (df.melt('Dates', var_name='Symbol', value_name='Close')
        .set_index(['Symbol','Dates'])
        .sort_index())