How to move pandas column under the last row of the first column?

Viewed 212

I'm trying to manipulate my pandas dataframe so that I can:

  1. Create a new column labelled 'Ticker'.
  2. Move column 'AAL' below column 'A'
  3. Label all elements for column 'A' as A in the new 'Ticker' column and AAL for the newly moved 'AAL' column
  4. Rename column 'A' to 'Adj Close'
  5. 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}}
1 Answers

Try this, if your column head is multiIndex:

df.stack(1).reset_index().rename(columns={'level_1': 'Ticker'})

Output:

      Date Ticker  Adj Close
0  1/11/19      A  80.226730
1  1/11/19    AAL  28.541664
2  1/12/19      A  84.736191
3  1/12/19    AAL  28.574404
4   1/1/20      A  82.177856
5   1/1/20    AAL  26.741179
Related