Flatten multindex columns into separate column-CodePudding

I have a DataFrame that looks similar to this:

Date  Close                  Open
      AAP    AWS    BGG  ... AAP    AWS    BGG   ...
2020  10     50     13   ... 100    500     13   ...
2021  11     41     7    ... 111    41      7    ...
2022  12     50     13   ... 122    50      13   ...

and want to turn it into

Date  Close  Open Index2
2020  10     100   AAP
2021  11     111   AAP
2022  12     122   AAP
2020  50     500   AWS    
...

How can I achieve it using pandas?

CodePudding user response：

You can use set_index and stack to get the expected dataframe:

>>> (df.set_index('Date').stack(level=1)
       .rename_axis(index=['Date', 'Ticker'])
       .reset_index())
   Date Ticker  Close  Open
0  2020    AAP     10   100
1  2020    AWS     50   500
2  2020    BGG     13    13
3  2021    AAP     11   111
4  2021    AWS     41    41
5  2021    BGG      7     7
6  2022    AAP     12   122
7  2022    AWS     50    50
8  2022    BGG     13    13

My input dataframe:

>>> df
   Date Close         Open         
          AAP AWS BGG  AAP  AWS BGG
0  2020    10  50  13  100  500  13
1  2021    11  41   7  111   41   7
2  2022    12  50  13  122   50  13

CodePudding user response：

You could also use wide_to_long

pd.wide_to_long(df.set_axis(df.columns.map('_'.join).str.rstrip('_'),axis=1), 
              ['Close', 'Open'], 'Date', 'Ticker', '_', '\\w ').reset_index()

   Date Ticker  Close  Open
0  2020    AAP     10   100
1  2021    AAP     11   111
2  2022    AAP     12   122
3  2020    AWS     50   500
4  2021    AWS     41    41
5  2022    AWS     50    50
6  2020    BGG     13    13
7  2021    BGG      7     7
8  2022    BGG     13    13