I'm trying to manipulate my pandas dataframe so that I can:
- Create a new column labelled 'Ticker'.
- Move column 'AAL' below column 'A'
- Label all elements for column 'A' as A in the new 'Ticker' column and AAL for the newly moved 'AAL' column
- Rename column 'A' to 'Adj Close'
- Duplicate index values into the left of column 'Adj Close' for the 'AAL' rows.
Actual Dataframe Output:
Adj Close Adj Close
A AAL
Date
1/11/19 80.22673035 28.54166412
1/12/19 84.7361908 28.57440376
1/1/20 82.17785645 26.74117851
Desired Dataframe Output:
Ticker Adj Close
Date
1/11/19 A 80.22673035
1/12/19 A 84.7361908
1/1/20 A 82.17785645
1/11/19 AAL 28.54166412
1/12/19 AAL 28.57440376
1/1/20 AAL 26.74117851
Is this possible and if so what would the best way of doing this be? I've tried using the groupby function as well as pivot but haven't gotten anywhere with it. I'm new to python so I might be doing something wrong.
Thanks for your help and stay safe :)
EDIT (requested output) print(df.to_dict())
{('Adj Close', 'A'):
{Timestamp('2019-10-01 00:00:00'): nan,
Timestamp('2019-11-01 00:00:00'): 80.22673034667969,
Timestamp('2019-12-01 00:00:00'): 84.73619079589844,
Timestamp('2020-01-01 00:00:00'): 82.1778564453125,
Timestamp('2020-02-01 00:00:00'): 76.71327209472656,
Timestamp('2020-03-01 00:00:00'): 71.28850555419922,
Timestamp('2020-04-01 00:00:00'): 76.4993667602539,
Timestamp('2020-05-01 00:00:00'): 87.95530700683594,
Timestamp('2020-06-01 00:00:00'): 88.18482971191406,
Timestamp('2020-07-01 00:00:00'): 96.33000183105469,
Timestamp('2020-08-01 00:00:00'): 100.41999816894531,
Timestamp('2020-09-01 00:00:00'): 100.94000244140625,
Timestamp('2020-10-01 00:00:00'): 100.01000213623047,
Timestamp('2020-10-02 00:00:00'): 100.01000213623047},
('Adj Close', 'AAL'):
{Timestamp('2019-10-01 00:00:00'): nan,
Timestamp('2019-11-01 00:00:00'): 28.541664123535156,
Timestamp('2019-12-01 00:00:00'): 28.574403762817383,
Timestamp('2020-01-01 00:00:00'): 26.741178512573242,
Timestamp('2020-02-01 00:00:00'): 18.9798583984375,
Timestamp('2020-03-01 00:00:00'): 12.1899995803833,
Timestamp('2020-04-01 00:00:00'): 12.010000228881836,
Timestamp('2020-05-01 00:00:00'): 10.5,
Timestamp('2020-06-01 00:00:00'): 13.069999694824219,
Timestamp('2020-07-01 00:00:00'): 11.119999885559082,
Timestamp('2020-08-01 00:00:00'): 13.050000190734863,
Timestamp('2020-09-01 00:00:00'): 12.289999961853027,
Timestamp('2020-10-01 00:00:00'): 13.0,
Timestamp('2020-10-02 00:00:00'): 13.0}}