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.
Here is Solutions:
We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.
There are a few ways:
>>> 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
>>> df.pivot(index=df.index, columns='key')['val'] key bar baz foo id 2 bananas apples oranges 3 kiwis NaN grapes
>>> df.reset_index().groupby(['id', 'key'])['val'].aggregate('first').unstack() key bar baz foo id 2 bananas apples oranges 3 kiwis NaN grapes
You could use
In : df.set_index([df.index, 'key'])['val'].unstack() Out: key bar baz foo id 2 bananas apples oranges 3 kiwis None grapes
Or, a simplified
In : df.groupby([df.index, 'key'])['val'].first().unstack() Out: key bar baz foo id 2 bananas apples oranges 3 kiwis None grapes
Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂