2

While I think I could do this naively and poorly, I'm interested to learn a more elegant and efficient approach.

Given the following dataframe:

In [42]: df = pd.DataFrame({'flavor':['goober','tronic','goober','tronic'], 'points':[42,55,31,101]}, index=['foo','foo','bar','bar'])

In [43]: df
Out[43]: 
     flavor  points
foo  goober      42
foo  tronic      55
bar  goober      31
bar  tronic     101

I would like to groupby the index, and convert values from flavor column into column headers themselves, completely throwing away the flavor and points. So the final result would look like:

In [44]: pd.DataFrame({'goober':[42,31], 'tronic':[55,101]}, index=['foo','bar'])
Out[44]: 
     goober  tronic
foo      42      55
bar      31     101

Thanks for any suggestions.

Improve this question

2 Answers 2

Reset to default
2

Use DataFrame.pivot with convert index to column first and then remove index and columns names by DataFrame.rename_axis:

df = df.reset_index().pivot('index', 'flavor','points').rename_axis(index=None,columns=None)
print (df)
     goober  tronic
bar      31     101
foo      42      55

Or use DataFrame.set_index with Series.unstack:

df = (df.set_index('flavor', append=True)['points']
        .unstack()
        .rename_axis(index=None, columns=None))
print (df)
     goober  tronic
bar      31     101
foo      42      55
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This is obviously a toy example, at scale, would these differ in performance and/or memory usage? Appreciate the answer.
@ghukill - hmmm, generally pivoting are really complicated operation, so not easy says what solution is better. The best test with data.
Any advice on when .unstack() is particularly helpful? maybe an SO question/answer? No worries if not, but I'd like to build some intuition around pivot and unstack, and where they shine.
@ghukill - Sure, it is here
Thanks @jezrael! All much appreciated.
1

You can use pandas.pivot_table:

print( pd.pivot_table(df, index=df.index, columns='flavor', values='points') )

Prints:

flavor  goober  tronic
bar         31     101
foo         42      55

Note: If you don't need aggfunc, use .pivot() instead. As @jezrael pointed out in the comments.

Improve this answer

3 Comments

There is aggregation mean, OP need it?
@jezrael You've right, .pivot is preferable here.
ok, rather delete comments here ;) Maybe somebody should be angry ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.