Unmelt Pandas DataFrame

Question

I have a pandas dataframe with two id variables:

df = pd.DataFrame({'id': [1,1,1,2,2,3],                 'num': [10,10,12,13,14,15],                'q': ['a', 'b', 'd', 'a', 'b', 'z'],                'v': [2,4,6,8,10,12]})     id  num  q   v 0   1   10  a   2 1   1   10  b   4 2   1   12  d   6 3   2   13  a   8 4   2   14  b  10 5   3   15  z  12 

I can pivot the table with:

df.pivot('id','q','v') 

And end up with something close:

q    a   b   d   z id                 1    2   4   6 NaN 2    8  10 NaN NaN 3  NaN NaN NaN  12 

However, what I really want is (the original unmelted form):

id   num   a   b   d   z                1    10   2   4 NaN NaN 1    12 NaN NaN   6 NaN   2    13   8 NaN NaN NaN 2    14 NaN  10 NaN NaN 3    15 NaN NaN NaN  12 

In other words:

1. 'id' and 'num' my indices (normally, I've only seen either 'id' or 'num' being the index but I need both since I'm trying to retrieve the original unmelted form)
2. 'q' are my columns
3. 'v' are my values in the table

Update

I found a close solution from Wes McKinney's blog:

df.pivot_table(index=['id','num'], columns='q')           v             q        a   b   d   z id num                 1  10    2   4 NaN NaN    12  NaN NaN   6 NaN 2  13    8 NaN NaN NaN    14  NaN  10 NaN NaN 3  15  NaN NaN NaN  12 

However, the format is not quite the same as what I want above.

Answer 1

You could use set_index and unstack

In [18]: df.set_index(['id', 'num', 'q'])['v'].unstack().reset_index() Out[18]: q  id  num    a     b    d     z 0   1   10  2.0   4.0  NaN   NaN 1   1   12  NaN   NaN  6.0   NaN 2   2   13  8.0   NaN  NaN   NaN 3   2   14  NaN  10.0  NaN   NaN 4   3   15  NaN   NaN  NaN  12.0