Aggregate each columns in a dataframe based on custom functions in python

Question

here is my dataframe:

df = [{'id': 1, 'name': 'bob', 'apple': 45, 'grape': 10, 'rate':0}, 
      {'id': 1, 'name': 'bob', 'apple': 45, 'grape': 20, 'rate':0},
      {'id': 2, 'name': 'smith', 'apple': 5, 'grape': 30, 'rate':0},
      {'id': 2, 'name': 'smith', 'apple': 10, 'grape': 40, 'rate':0}]

i would like to: where apple= apple.sum() and grape=grape.sum() and rate = grape/apple*100.

       id           name     apple    grape   rate
0       1            bob      90       30      300 
1       2           smith     15       70      21.4

I have attempted this with the following:

df = pd.DataFrame(df)
def cal_rate(rate):
    return df['apple'] / df['grape']*100
agg_funcs = {'apple':'sum',
             'grape':'sum',
             'rate' : cal_rate}
df=df.groupby(['id','name').agg(agg_funcs).reset_index()

But got this result:

       id           name     apple    grape   rate
0       1            bob      90       30      105 
1       2           smith     15       70      105

Can you help me out?thanks in advance.

Balaji Ambresh · Accepted Answer · 2020-07-20 06:02:54Z

1

Here you go:

import pandas as pd

df = [{'id': 1, 'name': 'bob', 'apple': 45, 'grape': 10, 'rate':0},
      {'id': 1, 'name': 'bob', 'apple': 45, 'grape': 20, 'rate':0},
      {'id': 2, 'name': 'smith', 'apple': 5, 'grape': 30, 'rate':0},
      {'id': 2, 'name': 'smith', 'apple': 10, 'grape': 40, 'rate':0}]
df = pd.DataFrame(df)


def cal_rate(group):
    frame = df.loc[group.index]
    return frame['apple'].sum()  / frame['grape'].sum() * 100
agg_funcs = {'apple':'sum',
             'grape':'sum',
             'rate' : cal_rate}
df=df.groupby(['id','name']).agg(agg_funcs).reset_index()
print(df)

Output

   id   name  apple  grape   rate
0   1    bob     90     30  300.0
1   2  smith     15     70   21.4

edited Jul 20, 2020 at 6:02

answered Jul 17, 2020 at 10:17

Balaji Ambresh

5,0022 gold badges7 silver badges17 bronze badges

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

2 Comments

ahmad Over a year ago

but why did you pass group as parameter in cal_rate function? can i do this in one line or something like that? 'apple':'sum', 'grape':'sum', 'rate' : frame['apple'].sum() / frame['grape'].sum() * 100 @balaji

Balaji Ambresh Over a year ago

@ahmad You have others showing you that. I was showing the usage of a custom function from an agg context.

AlexisG · Accepted Answer · 2020-07-17 10:21:14Z

1

You can also do it this way

df = df.groupby(['id', 'name']).agg({'apple':'sum', 'grape':'sum'}).reset_index()
df['rate'] = (df['apple'] / df['grape']) *100

answered Jul 17, 2020 at 10:21

AlexisG

2,4943 gold badges13 silver badges25 bronze badges

1 Comment

Henry Yik Over a year ago

Could simply do df.groupby(['id', 'name'], as_index=False).sum() instead of agg.

Adhun Thalekkara · Accepted Answer · 2020-07-17 10:38:56Z

1

just another way to do this

import pandas as pd
df = [{'id': 1, 'name': 'bob', 'apple': 45, 'grape': 10, 'rate':0},
      {'id': 1, 'name': 'bob', 'apple': 45, 'grape': 20, 'rate':0},
      {'id': 2, 'name': 'smith', 'apple': 5, 'grape': 30, 'rate':0},
      {'id': 2, 'name': 'smith', 'apple': 10, 'grape': 40, 'rate':0}]
df = pd.DataFrame(df)
df=df.groupby(['id','name']).sum().reset_index()
df['rate']=round((df['apple'] / df['grape'])*100,1)
print(df)

output

   id   name  apple  grape   rate
0   1    bob     90     30  300.0
1   2  smith     15     70   21.4

edited Jul 17, 2020 at 10:38

answered Jul 17, 2020 at 10:21

Adhun Thalekkara

72310 silver badges23 bronze badges

2 Comments

Balaji Ambresh Over a year ago

Why sum the id column?

Balaji Ambresh Over a year ago

Did you forget to reset_index() ?

Collectives™ on Stack Overflow

Aggregate each columns in a dataframe based on custom functions in python

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related