lambda function to calculate weighted average in a group by-CodePudding

I have made the following line of code :

DF.groupby(["Name"], as_index=False).agg({"A": lambda x:sum(abs(x)) ,'B': 'first'}).round(2)

with a DF output :

Name	A	B
Test	6	3
Test	-3

Gives the following output :

Name	A	B
Test	9	3

How would I do to get the following output :

Name	A	B
Test	9	1

1 given by the following formula :

((6*3) (-3*3))/9 = 1

Wondering if it was possible to do this directly within the group by

Biggest DF##

Name    A   B
Test    3   3
Test    -3  2
Test    4   4
Test    5   5
Test1   6   7
Test1   7   8

The output would be :

Name	A	B
Test	15	2.93
Test1	13	7.54

Note :

A = Absolute value : Test = 3   |-3|   4   5 = 15
B = Weighted Average : Test = ( 3*3   (-3)*2   4*4   5*5 ) / 15 = 2.93

CodePudding user response：

You can use modify the columns using assign, then use groupby.sum find the totals; then assign again to find the ratio:

out = df.assign(A=df['A'].abs(), B=df['A']*df['B']).groupby('Name', as_index=False).sum().assign(B=lambda x:x['B']/x['A'])

Output:

    Name   A         B
0   Test  15  2.933333
1  Test1  13  7.538462

CodePudding user response：

I also found an other way to do :

def fn(group):
    group['B'] = group['B'] * group['A'] / abs(group['A']).sum()
    group['A'] = abs(group['A'])
    return group

d_agg = {'A':'sum',
         'B':'sum'
    }

DF.groupby(["Name"], as_index=False).apply(fn).groupby('Name').agg(d_agg).round(2)