How do I group a pandas column to create a new percentage column

user_15395420 0
Viewed 61

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?

pandas-groupby pandas dataframe python
3 Answers

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
user_29010020

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
user_71757130

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
user_89869750
Related