Pandas Melt with Multi Index Data Set and Resetting Index - Why is this working?

Viewed 2531

I created this dataset straight from the pandas documentation:

In [28]: columns = pd.MultiIndex.from_tuples([('A', 'cat'), ('B', 'dog'),
   ....:                                      ('B', 'cat'), ('A', 'dog')],
   ....:                                     names=['exp', 'animal'])
   ....: 

In [29]: index = pd.MultiIndex.from_product([('one', 'two'),
                                             ('bar', 'baz', 'foo', 'qux')
   ....:                                     ],
   ....:                                    names=['first', 'second'])
   ....: 

In [30]: df = pd.DataFrame(np.random.randn(8, 4), index=index, columns=columns)

The MultiIndex dataset (for both columns and rows) looks like this:

enter image description here

I wanted to get to something like this [the image is truncated but you get the point]

enter image description here

There are probably a zillion ways of reshaping this but I want to get it done using unstack() and melt()

These are the two ways I came up with:

    1. pd.melt(df.reset_index(),id_vars=['first','second'])
    2. pd.melt(df.unstack().reset_index(),id_vars=['first'])

So here is where I am stuck: Why does this work?

df.reset_index() gives me this dataframe

enter image description here

with these columns

enter image description here

'first' and 'second' don't show up in the names of columns. They're are infact levels of the column exp. So I wondered what'd happen if I added more levels to the id_vars in the melt

If I change the melt to

pd.melt(df.reset_index(),id_vars=['first','second','A'])

I get the following error:

ValueError: arrays must all be same length

If I change the melt to

pd.melt(df.reset_index(),id_vars=['first','second','dog'])

I get the following error:

KeyError: 'dog'

Can someone explain what's intuitively going on under the hood with the reset_index() and why doesn't melt accept other levels? Why are 'first' and 'second' showing up as levels instead of columns?

1 Answers

There is a function called stack

yourdf=df.stack([0,1]).reset_index(name='value')
Related