CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
ghukill
ghukill

Reputation: 1202

pandas dataframe groupby index and convert row values into columns

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.

Upvotes: 2

Views: 1415

Answers (2)

jezrael
jezrael

Reputation: 862641

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

Upvotes: 2

Andrej Kesely
Andrej Kesely

Reputation: 195438

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.

Upvotes: 1

Related Questions