Search code examples
pythonpandasmulti-index

Change a multiindex Pandas

I would like to change my index from how it is seen in image 1 to image 2.

Here is the code to get Image 1:

stocks = pd.DataFrame()

Tickers = ['AAPL', 'TSLA', 'IBM', 'MSFT']

for tick in Tickers:
    df = web.DataReader(tick, "av-daily", start=datetime(2015, 1, 1),end=datetime.today(),api_key='') 
    df['Stock'] = tick
    
    stocks = stocks.append(df)

stocks.index = pd.to_datetime(stocks.index)
stocks = stocks.set_index('Stock', append = True)

vol = stocks[[‘volume’]]

weekly = vol.groupby([pd.Grouper(level=0, freq='W', label = 'left'), pd.Grouper(level='Stock')]).sum()
weekly.index.rename(['Date', 'Stock'], inplace = True)
weekly.unstack()

Image 1

enter image description here

Image 2

enter image description here

Solution

  • After you get the stocks DataFrame, do this:

    weekly = stocks["volume"].unstack().resample("W").sum()
weekly.index = pd.MultiIndex.from_tuples([(dt.year, dt.week) for dt in weekly.index])

>>> weekly
           volume                                
Stock         AAPL       IBM       MSFT       TSLA
2015 1    53204626   5525341   27913852    4764443
     2   282868187  24440360  158596624   22622034
     3   304226647  23272056  157088136   30799137
     4   198737041  31230797  137352632   16215501
     5   465842684  32927307  437786778   15720217
           ...       ...        ...        ...
2021 23  327048055  22042806  107035149  105306562
     24  456667151  23177438  128993727  107296122
     25  354155878  17129373  117966870  153549954
     26  321360130  29077036  104384023  103666230
     27  213093382  12153414   54825591   42076410