melting a multi index dataframe in pandas

Question

I keep getting stuck with this multi-level dataframe of stock prices that I'm trying to melt from wide to long data.

I'd like to go from this:

Attributes    Close   Close     High          
Symbols        AMZN    ^DJI     AMZN 
Date                                                                 
2020-12-01       32     29         35 
2020-11-30       31     28         36 
2020-11-27       30     27         37

To this:

      Attri   Sym        Date     price
0     Close  AMZN  2020-12-01        32
1     Close  AMZN  2020-11-30        31
2     Close  AMZN  2020-11-27        30
3     Close  ^DJI  2020-12-01        29
4     Close  ^DJI  2020-11-30        28
5     High   AMZN  2020-12-01        35
6     ....

I tried:

df = df.reset_index()
df = df.set_index([('Date', '')]).rename_axis(index=None, columns=('Date', ''))
df = df.fillna('').set_index('Date').T\
    .set_index('',append=True).stack().reset_index()

But I'm not getting it. Any ideas what else I should try?

I've tried for about 3 hours. pd.melt is not working for me. At least, I'm sure I'm missing something. — Adam Schroeder, Dec 02 '20 at 07:17
use pandas 1.1 I got it melted: ``df.melt(ignore_index=False, value_name="price").reset_index()`` — sammywemmy, Dec 02 '20 at 07:20

score 2 · Accepted Answer · answered Dec 02 '20 at 07:22

For me working DataFrame.stack by both levels with Series.reset_index:

df = df.stack([0,1]).reset_index(name='price')
print (df)
         Date Attributes Symbols  price
0  2020-12-01      Close    AMZN   32.0
1  2020-12-01      Close    ^DJI   29.0
2  2020-12-01       High    AMZN   35.0
3  2020-11-30      Close    AMZN   31.0
4  2020-11-30      Close    ^DJI   28.0
5  2020-11-30       High    AMZN   36.0
6  2020-11-27      Close    AMZN   30.0
7  2020-11-27      Close    ^DJI   27.0
8  2020-11-27       High    AMZN   37.0

Another idea is solution from comment by @sammywemmy:

df = df.melt(ignore_index=False, value_name="price").reset_index()
print (df)
         Date Attributes Symbols  price
0  2020-12-01      Close    AMZN     32
1  2020-11-30      Close    AMZN     31
2  2020-11-27      Close    AMZN     30
3  2020-12-01      Close    ^DJI     29
4  2020-11-30      Close    ^DJI     28
5  2020-11-27      Close    ^DJI     27
6  2020-12-01       High    AMZN     35
7  2020-11-30       High    AMZN     36
8  2020-11-27       High    AMZN     37

melting a multi index dataframe in pandas

1 Answers1