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:
I wanted to get to something like this [the image is truncated but you get the point]
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
with these columns
'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?



