CODEWITHSUNDEEP
pythonpandasgroup-byaggregate-functions
Marco
Marco

Reputation: 1245

Group by custom aggregation function python

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?

Upvotes: 0

Views: 186

Answers (2)

BENY
BENY

Reputation: 323316

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

Upvotes: 1

cnavarreteliz
cnavarreteliz

Reputation: 139

That's my suggestion:

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

Upvotes: 0

Related Questions