How can I compute the absolute sum with a groupby in pandas?
For example, given the DataFrame:
Player Score
0 A 100
1 B -150
2 A -110
3 B 180
4 B 125
I would like to have the total score for player A (100+110=210) as well as the total score for player A (150+180+125=455), ignoring the sign of the score.
I can use the following code to compute the sum:
import pandas as pd
import numpy as np
frame = pd.DataFrame({'Player' : ['A', 'B', 'A', 'B', 'B'],
'Score' : [100, -150, -110, 180, 125]})
print('frame: {0}'.format(frame))
total_scores = frame[['Player','Score']].groupby(['Player']).agg(['sum'])
print('total_scores: {0}'.format(total_scores))
but how can I compute the absolute sum with a groupby?
frame[['Player','Score']].abs().groupby(['Player']).agg(['sum'])
unsurprisingly returns:
Traceback (most recent call last):
File "O:\tests\absolute_count.py", line 10, in <module>
total_scores = frame[['Player','Score']].abs().groupby(['Player']).agg(['sum'])
File "C:\Users\dernoncourt\AppData\Local\Continuum\Anaconda3\lib\site-packages\pandas\core\generic.py", line 5518, in abs
return np.abs(self)
TypeError: bad operand type for abs(): 'str'
I don't want to alter the DataFrame.
You could apply a function that takes the absolute value and then sums it:
>>> frame.groupby('Player').Score.apply(lambda c: c.abs().sum())
Player
A 210
B 455
Name: Score, dtype: int64
You could also create a new column with the absolute values and then sum that:
>>> frame.assign(AbsScore=frame.Score.abs()).groupby('Player').AbsScore.sum()
Player
A 210
B 455
Name: AbsScore, dtype: int64
You can use DataFrameGroupBy.apply
with a lambda:
In [326]: df.groupby('Player').Score.apply(lambda x: np.sum(np.abs(x)))
Out[326]:
Player
A 210
B 455
Name: Score, dtype: int64
To get back the Player
column, use df.reset_index
:
In [371]: df.groupby('Player').Score.apply(lambda x: np.sum(np.abs(x))).reset_index()
Out[371]:
Player Score
0 A 210
1 B 455
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With