I've got a pandas dataframe that looks like this:
mydict ={
'person': ['Jenny', 'Jenny', 'David', 'David', 'Max', 'Max'],
'fruit': ['Apple', 'Orange', 'Apple', 'Orange', 'Apple', 'Orange'],
'eaten': [25, 75, 15, 5, 10, 10]
}
df = pd.DataFrame(mydict)
person fruit eaten
Jenny Apple 25
Jenny Orange 75
David Apple 15
David Orange 5
Max Apple 10
Max Orange 10
Which I'd like to convert into:
person apple_percentage orange_percentage
Jenny 0.25 0.75
David 0.75 0.25
Max 0.50 0.50
I'm guessing that I'll have to use groupby in some capacity to do this, but can't figure out a clean Pythonic way of doing so?
Use DataFrame.pivot with division by sums:
df = df.pivot('person','fruit','eaten').add_suffix('_percentage')
df = df.div(df.sum(axis=1), axis=0)
print (df)
fruit Apple_percentage Orange_percentage
person
David 0.75 0.25
Jenny 0.25 0.75
Max 0.50 0.50
Another option is pandas' crosstab:
(pd.crosstab(index = df.person,
columns = df.fruit,
values = df.eaten,
aggfunc = 'mean',
normalize='index')
.add_suffix('_percentage')
.rename_axis(columns=None)
)
Apple_percentage Orange_percentage
person
David 0.75 0.25
Jenny 0.25 0.75
Max 0.50 0.50
You could also use the pipe method, although in this case, it does not make the code clearer (which defeats the purpose of the pipe function):
(df.assign(eaten = df.groupby('person')
.pipe(lambda grp: df.eaten /
grp.eaten.transform('sum'))
)
.pivot('person', 'fruit', 'eaten')
.add_suffix('_percentage')
.rename_axis(columns=None)
)
Apple_percentage Orange_percentage
person
David 0.75 0.25
Jenny 0.25 0.75
Max 0.50 0.50
Can stack and unstack() and agg
df=df.set_index(['person','fruit']).stack().unstack('fruit').add_suffix('_percentage')#)
df = df.div(df.sum(axis=1), axis=0).reset_index().drop(columns='level_1')
fruit person Apple_percentage Orange_percentage
0 David 0.75 0.25
1 Jenny 0.25 0.75
2 Max 0.50 0.50