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

user_13947150 0
Viewed 58

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.

pandas dataframe aggregate python
3 Answers

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
user_126114090

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
user_126417170

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