Unstack dataframe and keep columns

Question

I have a DataFrame that is in a too much "compact" form. The DataFrame is currently like this :

> import numpy as np
> import pandas as pd

> df = pd.DataFrame({'foo': ['A','B'],
               'bar': ['1', '2'],
               'baz': [np.nan, '3']})
  bar  baz foo
0   1  NaN   A
1   2    3   B

And I need to "unstack" it to be like so :

> df = pd.DataFrame({'foo': ['A','B', 'B'],
               'type': ['bar', 'bar', 'baz'],
               'value': ['1', '2', '3']})

  foo type value
0   A  bar     1
1   B  bar     2
2   B  baz     3

No matter how I try to pivot, I can't get it right.

score 5 · Accepted Answer · answered May 29 '17 at 15:21

Use melt() method:

In [39]: pd.melt(df, id_vars='foo', value_vars=['bar','baz'], var_name='type')
Out[39]:
  foo type value
0   A  bar     1
1   B  bar     2
2   A  baz   NaN
3   B  baz     3

or

In [38]: pd.melt(df, id_vars='foo', value_vars=['bar','baz'], var_name='type').dropna()
Out[38]:
  foo type value
0   A  bar     1
1   B  bar     2
3   B  baz     3

score 3 · Answer 2 · answered May 29 '17 at 15:22

3

set your index to foo, then stack:

df.set_index('foo').stack()

foo     
A    bar    1
B    bar    2
     baz    3
dtype: object

answered May 29 '17 at 15:22

Steven G

16,244
8
53
77

This gives a Series and not a DataFrame – Rémi May 29 '17 at 15:36
@Rémi, adding `.reset_index()` will give you desired result ;-) – MaxU - stand with Ukraine May 29 '17 at 15:37
Indeed, it works too. Sorry @Steven G, I can't mark both answers as correct. – Rémi May 29 '17 at 15:41

Unstack dataframe and keep columns

2 Answers2