pythonpandaspivotyahoo-financepandas-melt

Unpivot yahoo finance multiple ticker download

I am trying to unpivot a yahoo finance download.

import yfinance as yf
import pandas as pd
from datetime import date

today = date.today()

data = yf.download("AAPL WMT", start="2023-05-01", end=today).round(2)

Running this script I get:

Date Adj Close Adj Close Close Close High High Low Low Open Open Volume Volume
AAPL WMT AAPL WMT AAPL WMT AAPL WMT AAPL WMT AAPL WMT
2023-05-01 169.36 151.02 169.59 151.59 170.45 152.54 168.64 150.93 169.28 151.04 52472900 3803200
2023-05-02 168.31 150.61 168.54 151.18 170.35 151.49 167.54 149.46 170.09 151.47 48425700 3776500
2023-05-03 167.22 150.05 167.45 150.62 170.92 152.1 167.16 150.27 169.5 151.56 65136000 4111500
2023-05-04 165.56 150.47 165.79 150.47 167.04 150.99 164.31 149.49 164.89 150.55 81235400 4670400
2023-05-05 173.33 151.77 173.57 151.77 174.3 152.01 170.76 149.83 170.98 150.3 113316400 4850000
2023-05-08 173.26 152.72 173.5 152.72 173.85 152.95 172.11 151.06 172.48 151.27 55962800 4975400
2023-05-09 171.53 152.98 171.77 152.98 173.54 153.18 171.6 152.34 173.05 152.72 45326900 5069900
2023-05-10 173.32 152.55 173.56 152.55 174.03 154.35 171.9 151.23 173.02 153.69 53724500 5275900
2023-05-11 173.51 153.12 173.75 153.12 174.59 153.27 172.17 152.0 173.85 152.55 49514700 3975600
2023-05-12 172.57 153.07 172.57 153.07 174.06 154.07 171.0 152.71 173.62 153.6 45497800 5371800

I am trying to pivot it to this, where the Ticker is now a column:

Date Ticker Close High Low Open Volume Adj Close
5/1/2023 AAPL 169.59 170.45 168.64 169.28 52472900 169.36
5/2/2023 AAPL 168.54 170.35 167.54 170.09 48425700 168.31
5/3/2023 AAPL 167.45 170.92 167.16 169.5 65136000 167.22
5/4/2023 AAPL 165.79 167.04 164.31 164.89 81235400 165.56
5/5/2023 AAPL 173.57 174.3 170.76 170.98 1.13E+08 173.33
5/8/2023 AAPL 173.5 173.85 172.11 172.48 55962800 173.26
5/9/2023 AAPL 171.77 173.54 171.6 173.05 45326900 171.53
5/10/2023 AAPL 173.56 174.03 171.9 173.02 53724500 173.32
5/11/2023 AAPL 173.75 174.59 172.17 173.85 49514700 173.51
5/12/2023 AAPL 172.57 174.06 171 173.62 45497800 172.57
5/1/2023 WMT 151.59 152.54 150.93 151.04 3803200 151.02
5/2/2023 WMT 151.18 151.49 149.46 151.47 3776500 150.61
5/3/2023 WMT 150.62 152.1 150.27 151.56 4111500 150.05
5/4/2023 WMT 150.47 150.99 149.49 150.55 4670400 150.47
5/5/2023 WMT 151.77 152.01 149.83 150.3 4850000 151.77
5/8/2023 WMT 152.72 152.95 151.06 151.27 4975400 152.72
5/9/2023 WMT 152.98 153.18 152.34 152.72 5069900 152.98
5/10/2023 WMT 152.55 154.35 151.23 153.69 5275900 152.55
5/11/2023 WMT 153.12 153.27 152 152.55 3975600 153.12
5/12/2023 WMT 153.07 154.07 152.71 153.6 5371800 153.07

I cannot find any good examples of this here and I am pretty sure it is possible with melt but I cannot get it to work.

Solution

  • You can use :

    out = data.stack().reset_index(level=1, names=["", "Ticker"]).sort_values(by="Ticker")

    Output :

    print(out)

           Ticker  Adj Close  Close   High    Low   Open    Volume
Date                                                              
2023-05-01   AAPL     169.36 169.59 170.45 168.64 169.28  52472900
2023-05-02   AAPL     168.31 168.54 170.35 167.54 170.09  48425700
2023-05-11   AAPL     173.51 173.75 174.59 172.17 173.85  49514700
...           ...        ...    ...    ...    ...    ...       ...
2023-05-01    WMT     151.02 151.59 152.54 150.93 151.04   3803200
2023-05-08    WMT     152.72 152.72 152.95 151.06 151.27   4975400
2023-05-12    WMT     153.07 153.07 154.07 152.71 153.60   5371800

[20 rows x 7 columns]