Group by custom aggregation function python

Question

After performing the groupby on two columns (id and category) using the mean aggregation function over a column (col3) I have something like this:

                              col3
id            category        mean
345              A             12
                 B              2
                 C              3
                 D              4
                Total          21

What I would like to do is to add a new column called percentage in which I calculate the percentage of each category over the category Total. This should be done separately for every id. The result should be something like this:

                              col3
id            category        mean    percentage
345              A             12        0.57
                 B              2        0.09
                 C              3        0.14
                 D              4        0.19
                Total          21         1

Obviously i want to do that for every id, that is the first column on which i have done the groupby. Any suggestion on how to do that?

BENY · Accepted Answer · 2018-12-02 03:33:19Z

1

Using get_level_values filter your df, then we using div

s=df[df.index.get_level_values(level=1)!='Total'].sum(level=0)
df['percentage']=df.div(s,level=0,axis=1)
df
Out[422]: 
              mean  percentage
id  category                  
345 A           12    0.571429
    B            2    0.095238
    C            3    0.142857
    D            4    0.190476
    Total       21    1.000000

answered Dec 2, 2018 at 3:33

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cnavarreteliz · Accepted Answer · 2018-12-02 02:13:21Z

0

That's my suggestion:

df['mean'] = df['mean'] / df['mean'].sum()

answered Dec 2, 2018 at 2:13

cnavarreteliz

1391 gold badge3 silver badges9 bronze badges

1 Comment

Marco Over a year ago

This is not working because I need to do that for every ID separately. In this way instead you consider the whole dataset

Collectives™ on Stack Overflow

Group by custom aggregation function python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related