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
>Solution :
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