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?
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
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
Dateis the index of your dataframe?