Pandas column values to columns?

Question

I've seen a few variations on the theme of exploding a column/series into multiple columns of a Pandas dataframe, but I've been trying to do something and not really succeeding with the existing approaches.

Given a DataFrame like so:

    key       val id 2   foo   oranges 2   bar   bananas 2   baz    apples 3   foo    grapes 3   bar     kiwis 

I want to convert the items in the key series into columns, with the val values serving as the values, like so:

        foo        bar        baz id 2   oranges    bananas     apples 3    grapes      kiwis        NaN 

I feel like this is something that should be relatively straightforward, but I've been bashing my head against this for a few hours now with increasing levels of convolution, and no success.

Answer 1

There are a few ways:

using .pivot_table:

>>> df.pivot_table(values='val', index=df.index, columns='key', aggfunc='first') key      bar     baz      foo id                            2    bananas  apples  oranges 3      kiwis     NaN   grapes 

using .pivot:

>>> df.pivot(index=df.index, columns='key')['val'] key      bar     baz      foo id                            2    bananas  apples  oranges 3      kiwis     NaN   grapes 

using .groupby followed by .unstack:

>>> df.reset_index().groupby(['id', 'key'])['val'].aggregate('first').unstack() key      bar     baz      foo id                            2    bananas  apples  oranges 3      kiwis     NaN   grapes 

Answer 2

You could use set_index and unstack

In [1923]: df.set_index([df.index, 'key'])['val'].unstack() Out[1923]: key      bar     baz      foo id 2    bananas  apples  oranges 3      kiwis    None   grapes 

Or, a simplified groupby

In [1926]: df.groupby([df.index, 'key'])['val'].first().unstack() Out[1926]: key      bar     baz      foo id 2    bananas  apples  oranges 3      kiwis    None   grapes